python excel 內容寫入mysql

import xlrd

import pymysql

data = xlrd.open_workbook('C:\Users\yanghui\Desktop\11.xls')

sheet = data.sheet_by_index(0)

conn = pymysql.connect(

host='127.0.0.1',

port=3306,

user='root',

passwd='',

db='gp',

charset='utf8'

)

cursor = conn.cursor()

# ['證券程式碼', '證券名稱', '成交日期', '成交價格', '成交數量', '剩餘數量', '業務名稱', '手續費', '印花稅', '過戶費']

# ['002594', '比亞迪', '2018-8-2', 40.71, -100.0, 100.0, '證券賣出', 5.0, 4.07, 0.0]

query = """INSERT INTO deal (code,name,rq,price,sl1,sl2,mm,charge,stamp_duty,transfer_fee) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

for r in range(1, sheet.nrows):

code = sheet.cell(r, 0).value

name = sheet.cell(r, 1).value

rq = sheet.cell(r, 2).value

price = sheet.cell(r, 3).value

sl1 = sheet.cell(r, 4).value

sl2 = sheet.cell(r, 5).value

mm = sheet.cell(r, 6).value

charge = sheet.cell(r, 7).value

stamp_duty = sheet.cell(r, 8).value

transfer_fee = sheet.cell(r, 9).value

values = (code, name, rq, price, sl1, sl2, mm,

charge, stamp_duty, transfer_fee)

cursor.execute(query, values)

cursor.close()

conn.commit()

conn.close()

print('Done!')