使用pymysql 存储xls

#-*- coding: utf-8 -*-
__author__ = ‘Work‘
import sys
reload(sys)
sys.setdefaultencoding("utf-8")
import xlrd
import pymysql

conn = pymysql.connect(host=‘192.168.12.115‘, port=3306, user=‘root‘, passwd=‘123456‘, db=‘captchadb‘,charset=‘utf8‘)
cur = conn.cursor()

data = xlrd.open_workbook(‘E:\Captcha.xls‘)
table = data.sheets()[0]

for row in range(table.nrows):
    cellQ = table.cell(row, 0).value
    cellA = table.cell(row, 1).value
    cellB = table.cell(row, 2).value
    cellC = table.cell(row, 3).value
    cellD = table.cell(row, 4).value
    print row,cellQ,cellA,cellB,cellC,cellD
    strsql = ‘insert into captchatable(question, choiceA, choiceB, choiceC, answer) value (\‘%s\‘,\‘%s\‘,\‘%s\‘,\‘%s\‘,\‘%d\‘)‘ % (cellQ,cellA,cellB,cellC,cellD)
    print strsql
    cur.execute(strsql)
conn.commit()
cur.close()
conn.close()

使用pymysql 存储xls

标签：