excel导入到mysql数据库

#coding:utf-8
import  MySQLdb
import  xlwt
import  xlrd

book = xlrd.open_workbook('D:\sd.xlsx')
sheet = book.sheets()[0]
#本机数据库连接
dataconn = MySQLdb.connect(host = '192.168.96.150',port = 3306,user = 'root',passwd = 'mysql',db = 'db_book',charset='utf8')

cursor = dataconn.cursor()
query = '''insert into books (id,name) VALUES (%d,'%s')'''
create_sql = "create table books("+ sheet.cell_value(0,0) +" int(2),"+sheet.cell_value(0,1)+" varchar(100))"
print  create_sql
#建表
cursor.execute(create_sql)

for r in range(1,sheet.nrows):
    id = sheet.cell(r,0).value
    name = sheet.cell(r,1).value
    print id
    print name
    values = (id,name)
    query_sql = query %values
    #print query_sql
    cursor.execute(query_sql)

#关闭游标
cursor.close()
#提交事务
dataconn.commit()
print "it's done"

转载于:https://www.cnblogs.com/tao-yuan/p/7955600.html