经过查资料,解决了乱码、各种报错问题,做一下笔记记录。
#coding=utf-8
import xlrd
import MySQLdb
import sys
reload(sys)
sys.setdefaultencoding('utf8')
exceldir = "E:\\ry.xlsx"
exceldata = xlrd.open_workbook(exceldir)
table1 = exceldata.sheets()[0] #获取到工作表sheet
nrows = table1.nrows #获取行数
ncols = table1.ncols #获取列数
sql = "INSERT INTO excel_db (user_id,yg_name,is_del,work_adress,first_part,second_part,third_part,role) VALUES (%s,%s,%s,%s,%s,%s,%s,%s)"
db = MySQLdb.connect("192.168.121.128","zyz","123456","python",charset="utf8")
cursor = db.cursor()
data = []
for rx in range(1,nrows):
for cx in range(0,ncols):
data.append(str(table1.cell(rx,cx).value))
cursor.execute(sql,(data[0],data[1],data[2],data[3],data[4],data[5],data[6],data[7]))
data =[]
db.commit()
cursor.close()
db.close()