使用Python将excel里的数据插入到数据库

使用Python将excel里的数据插入到数据库
具体实现代码如下：

import pymysql
from openpyxl import load_workbook

wb = load_workbook(‘需要导入的excel文件路径’)
sheet = wb.active

conn = pymysql.connect(
user=“用户名”,
password=“密码”,
port=3306,
host=“地址”,
db=“数据库名”,
charset=“utf8”#字符集
)
cur = conn.cursor() # 获取对应的操作游标
query = ‘insert into test_data(merchantId, requestId, transactionDate, orderSn, billemail, paySn) values (%s, %s, %s, %s, %s, %s)’#插入的sql，字段值用s%表示
for i in range(2, sheet.max_row+1):#因为有表头所以从第二行开始取值
studentId = (sheet.cell(row=i, column=1)).value
cardId = (sheet.cell(row=i, column=2)).value
address = (sheet.cell(row=i, column=4)).value
Jdate = (sheet.cell(row=i, column=5)).value
Jtime = (sheet.cell(row=i, column=6)).value
money = (sheet.cell(row=i, column=7)).value
values = (studentId, cardId, address, Jdate, Jtime, money)#从excel里面读逐行获取需要插入的字段
# 执行sql语句
cur.execute(query, values)
cur.close()
conn.commit()
conn.close()
print(“运行结束本次导入数据:”+str(sheet.max_row-1))