python读取excel文件导入MySQL数据库建好的表中

import xlrd
import pymysql

book = xlrd.open_workbook("D:/tmp/xub/xwbdxm.xlsx")
sheet = book.sheet_by_name("Sheet1")

# 建立一个MySQL连接
database = pymysql.connect(host="localhost", user="root", passwd="123456", db="jdshop", charset='utf8')

# 获得游标对象, 用于逐行遍历数据库数据
cursor = database.cursor()

# 创建插入SQL语句
query = """INSERT INTO dgoodshops (province, city, user_id, iphone, attribute, own_or_other,goods_title, goods_money,coupon_moy,goods_id,order_id,order_type,create_time,end_time,shop_name,shop_num,jy_type,jy_bl,ygj_money,yg_money,sj_money,ygf_cash,sjf_cash,ygf_jf,sjf_jf,jfzf,hfzf,channel_type,spreadcode_id,spreadcode_name,login_num,onegrade_type,twograde_type,threegrade_type) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"""

# 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题
for r in range(3, sheet.nrows):
    province = sheet.cell(r, 0).value
    city = sheet.cell(r, 1).value
    user_id = sheet.cell(r, 2).value
    iphone = sheet.cell(r, 3).value
    attribute = sheet.cell(r, 4).value
    own_or_other = sheet.cell(r, 5).value
    goods_title = sheet.cell(r, 6).value
    goods_money = sheet.cell(r, 7).value
    coupon_moy = sheet.cell(r, 8).value
    goods_id = sheet.cell(r, 9).value
    order_id = sheet.cell(r, 10).value
    order_type = sheet.cell(r, 11).value
    create_time = sheet.cell(r, 12).value
    end_time = sheet.cell(r, 13).value
    shop_name = sheet.cell(r, 14).value
    shop_num = sheet.cell(r, 15).value
    jy_type = sheet.cell(r, 16).value
    jy_bl = sheet.cell(r, 17).value
    ygj_money = sheet.cell(r, 18).value
    yg_money = sheet.cell(r, 19).value
    sj_money = sheet.cell(r, 20).value
    ygf_cash = sheet.cell(r, 21).value
    sjf_cash = sheet.cell(r, 22).value
    ygf_jf = sheet.cell(r, 23).value
    sjf_jf = sheet.cell(r, 24).value
    jfzf = sheet.cell(r, 25).value
    hfzf = sheet.cell(r, 26).value
    channel_type = sheet.cell(r, 27).value
    spreadcode_id = sheet.cell(r, 28).value
    spreadcode_name = sheet.cell(r, 29).value
    login_num = sheet.cell(r, 30).value
    onegrade_type = sheet.cell(r, 31).value
    twograde_type = sheet.cell(r, 32).value
    threegrade_type = sheet.cell(r, 33).value

    values = (province, city, user_id, iphone, attribute, own_or_other, goods_title, goods_money, coupon_moy, goods_id,
              order_id, order_type, create_time, end_time, shop_name, shop_num, jy_type, jy_bl, ygj_money, yg_money,
              sj_money, ygf_cash, sjf_cash, ygf_jf, sjf_jf, jfzf, hfzf, channel_type, spreadcode_id, spreadcode_name,
              login_num, onegrade_type, twograde_type, threegrade_type)

# 执行sq1l语句
str_insert = (query % values).replace('\'NULL\'','NULL').replace('\'\'','NULL')
print(str_insert)
cursor.execute(str_insert)
# 提交
database.commit()

# 关闭游标
cursor.close()

# 提交
database.commit()

# 关闭数据库连接
database.close()