Python 快速读取 Excel 内容写入 Mysql
前置准备：四个文件分别如下：1. testdata.xls文件2. 数据库配置信息3. mysql数据库操作4. excel读取 数据库配置信息PageCount=10
dbhost="10.10.1.248"
dbport=3306
dbuser="root"
dbpasswd="123456"
dbname="testdb"
mysqldb.py文件内容# 数据库操作
import pymysql
from configs import dbhost,dbpasswd,dbport,dbuser,dbname
def get_cur():
conn = pymysql.connect(host=dbhost, port=dbport, user=dbuser, passwd=dbpasswd, database=dbname)
cur = conn.cursor()
return cur,conn
def get_count(sql):
cur,conn = get_cur()
cur.execute(sql)
count = cur.fetchall()
cur.close()
conn.close()
return count
def get_table_colum():
cur,conn = get_cur()
cur.execute("select * from xxljob_info")
col_name_list = for tuple in cur.description]
cur.close()
conn.close()
return col_name_list
def get_data(sql1):
cur,conn = get_cur()
cur.execute(sql1)
results = cur.fetchall()
cloumn = get_table_colum()
res = {}
reslist = []
for r in range(len(list(results))):
for m in range(len(list(cloumn))):
res)] = str(list(results))
reslist.append(res)
res = {}
cur.close()
conn.close()
return reslist
def execute_sql(sql):
cur, conn = get_cur()
cur.execute(sql)
cur.close()
conn.commit()
conn.close()
if __name__ == '__main__':
get_table_colum()
sql = "insert into xxljob_info (executer,describes,router,block,cron,is_repeater,leader,status,mark,create_time) VALUES ('testabc','testabc','第一个','单机串行','testabc','否','testabc','testabc','testabc','2022-06-16 13:14:19')"
for i in range(55):
execute_sql(sql)
# get_count("select count(*) from xxljob_info")readExcel.py文件内容# 读取excel表数据,并执行sql
import xlrd
from mysqldb import execute_sql
file = xlrd.open_workbook("testdata.xls")
table1 = file.sheet_by_name("数据表")
sql = "insert into testdata values "
for row in range(table1.nrows - 1):
list1 = table1.row_values(row + 1)
list1 = + list1
execute_sql(sql + str(tuple(list1)))上 4 个文件即可通过 Python 实现 Excel 内容自动读取并入库 Mysql。
頁:
[1]