在工作中,经常会遇到需要准备批量的数据,现将基于python语言,读取excel数据并将数据插入数据库表的操作作简单记录:
# coding:utf-8
import pymysql
import xlrd
# 从excel读取数据写入mysql
def excel_to_mysql(filename):
conn = pymysql.connect(host="localhost",user="user",password="pwd",database="连接的数据库名",charset="utf8")
# 连接数据库
cur = conn.cursor()
book = xlrd.open_workbook(filename)
sheet = book.sheet_by_name('Sheet1')
# 获取行数
rows = sheet.nrows
print(rows)
# 将标题之外的其他行写入数据库
for r in range(1, rows):
r_values = sheet.row_values(r)
print(r_values)
sql = "insert into USER(name,age) values(%s,%s);"
# 将每一行插入sql
data = cur.execute(sql,(r_values[0],r_values[1]))
# 插入所有数据后提交
conn.commit()
cur.close()
conn.close()
excel_to_mysql(r"****\USER.xlsx")