思路:
1)将excle中的数据先取出来放入数组中li
2)进入数据库
3)直接批量插入数据
4)关闭所有连接
import MySQLdb
import xlrd
li = []
wb = xlrd.open_workbook("D:\\Program Files\\subpy\\sql2.xlsx")
ws = wb.sheet_by_index(0)
nrows = ws.nrows #行数
ncols = ws.ncols #列数
for i in range(1,nrows):
li.append(ws.row_values(i))
conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='123456',db='student')
cursor=conn.cursor()
val=''
for i in range(0,ncols): #格式化符
val = val+'%s,'
print val
print li #调试代码
cursor.executemany("insert into test21 values("+val[:-1]+");",li) #批量插入数据
conn.commit() #写入数据库
# workbook.close()
cursor.close()
conn.close()