用python将excel数据写入mysql

先在mysql创建好数据库以及数据表

以下为自己参考多个网站写的代码

# -*- coding: UTF-8 -*-
import xlrd,pymysql,sys
reload(sys)
sys.setdefaultencoding('utf-8')


def open_excel(file):
try:
data = xlrd.open_workbook(file)
return data
except Exception,e:
print str(e)


def excel_table_byname(file='/Users/apple/Desktop/test_excel.xlsx',by_name=u'Sheet1'):
data = open_excel(file)
table = data.sheet_by_name(by_name)
return table

def main():
insert_sql =  'insert into test2 (Clinic_Code, Dept_Code, Doct_Code, Diag_Name) values (%s, %s, %s, %s)'
conn = pymysql.connect(host='localhost',user='root',passwd='abysmaN169',db='test',charset='utf8')
cur = conn.cursor()
table = excel_table_byname()
nrows = table.nrows
print nrows
ncols = table.ncols
print ncols
data = []
for i in range(1,nrows):#只取第二行到最后一行
for j in range(1,ncols-2):#只取第二三四六列数据
data.append(str(table.cell(i,j).value))
print str(table.cell(i,j).value)
if j==3:
data.append(str(table.cell(i,j+2).value))
cur.execute(insert_sql,[data[0],data[1],data[2],data[3]])
data = []#重置
conn.commit() #提交
cur.close()
conn.close()

if __name__ == '__main__':
  main()

xlrd使用:http://www.cnblogs.com/lhj588/archive/2012/01/06/2314181.html