python通过excel对数据库插入数据
1.需要有两个包文件xlrd及MySQLdb(其他数据库可以另外找)
2.读取excel文件信息
book = xlrd.open_workbook(文件地址)
3.建立MySQL链接
database = MySQLdb.connect (host="ip", user = "用户名", passwd = "密码", db = "数据库名字")
cursor = database.cursor()
4.创建插入的SQL语句
query = 'INSERT INTO ecm_js_order (id, order_snt)VALUES (%s, %s)'
5.创建一个for循环迭代读取xls文件每行数据的, 从第start行开始是要跳过标题
start = 26
for r in range(start, sheet.nrows):
id = sheet.cell(r,0).value
order_sn = sheet.cell(r,1).value
values = (id, order_sn)
cursor.execute(query, values)
6.# 关闭游标
cursor.close()
7.# 提交
database.commit()
8.# 关闭数据库连接
database.close()