python 操作 excel 表格 使用sql 语句将数据插入mysql数据库

#导包
import xlrd
import pymysql

ret=0
#链接mysql
conn = pymysql.connect(host='127.0.0.1',3306, user='root', passwd='111',db=test,charset='utf-8')
#读取excel
data = xlrd.open_workbook(path)
#获取表格对象
table = data.sheets()[0]
t = table.col_values(1)
#获取所有行数
nrows = table.nrows

for i in range(nrows):
    if i == 0:
        continue
    try:
        r1 = table.row_values(i)
        cur = conn.cursor()
        cur.execute("insert into %s"%tablename +" values('%s','%s','%s','%s')"%(r1[0],r1[1],int(r1[2]),int(r1[3])))
        conn.commit()
        ret+=1
    except Exception as e:
        # 事务回滚
        conn.rollback()