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!')