excel数据导入SQL数据库2021.8.23

# -*- coding:utf-8 -*-
import pymysql
from openpyxl import load_workbook
 
filename = '日志2021.8.23.xlsx'
workbook = load_workbook(filename, read_only=False, data_only=True)
ws = workbook.active

data = []
for row in ws.iter_rows(min_row=2, max_row=10444, values_only=True): #min_row=2是否有标题栏，从2行开始
    print(row)
    data.append(row)
 
# 插入数据库
conn = pymysql.Connection(host="192.168.XX.XX", user='XXX', password='XXXXX', database='XXXX', charset="utf8")
#填入SQL数据库的地址、用户名、密码、库名
cour = conn.cursor()
sql_str = "INSERT INTO test2021082301(`序号`, `网址`, `项目名称`, `附件名称`, `附件地址`, `附近重命名`) VALUES (%s, %s, %s, %s, %s, %s)"
 #编辑SQL语句，写入表名例如testXX,写入要写的字段
cour.executemany(sql_str, data)
conn.commit()
 
cour.close()
conn.close()