Python实现Exccel数据批量导入数据库

准备

  • Python3
  • import xlrd
  • from xlwt import *
  • import pymysql

用Python创建Excel模板

使用Py创建一个表头固定的Excel:

1
2
3
4
5
6
7
8
def createExcel():
w = Workbook(encoding='utf-8')
sheet1 = w.add_sheet('problem_sheet')
row0 = [u'num', u'title', u'category', u'difficulty', u'answer', u'analysis', u'teacher', u'add_time', u'score', u'complete_time', u'status', u'direction_id', u'stage_id', u'course_id', u'days_id', u'gongsi_id', u'zhiwei_id', u'type', u'url', u'orderd']
# 生成第一行
for i in range(0, len(row0)):
sheet1.write(0, i, row0[i])
w.save('Problems.xls')

注意一定要设置好编码,然后再去创建子sheet。可以创建多个sheet。创建表头即生成第一行,操作见上。
默认的存储路径在与程序同级目录下。 关于Py操作Excel的还有很多,此处不再叙述;

Python操作Mysql写入数据

连接数据库

1
2
3
4
5
6
def sonmenu():
dbname = input("请输入已创建的数据库的名称:\n")
dbuser = input("请输入mysql用户名:\n")
dbpwd = input("请输入mysql密码:\n")
global db
db = pymysql.connect("localhost", dbuser, dbpwd, dbname, use_unicode=True, charset="utf8")

注意charset的设置,存在创建的数据库和表编码格式很可能是只支持英文的Latin,会导致含中文的数据写入失败,同样字符编码格式不一致也会导致写入失败

1
2
3
4
5
6
7
8
9
def insertData():
ename = input("请输入要导入的Excel文件名称:\n")
workbook = xlrd.open_workbook(ename)
print("导入全部sheet...\n")
for i in workbook.sheet_names():
print("表名====>>" + i + "/")
print("\n")
a = len(workbook.sheets())
cursor = db.cursor()

导入excel全部sheet,并获取数据库游标。

1
2
3
4
5
6
7
8
9
10
11
for j in range(0, a):
sheet1 = workbook.sheet_by_index(j)
print("打开了表" + sheet1.name + "\n")
title = ""
# 获取表头
for l in range(0, int(sheet1.ncols)):
if l == sheet1.ncols - 1:
title += sheet1.cell_value(0, l)
break
title += sheet1.cell_value(0, l) + ","
print("表头是"+title)

遍历sheetlist,打开一个sheet并获取它的表头(此处创建的模板表头与数据库中的字段相同),表名与数据库表名相同,方便导入。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
# 遍历sheet的多行
for nrows_one in range(1, int(sheet1.nrows)):
#con用于存放sql语句中的value值
con = ""
#遍历一行中的单元格
for s in range(0, int(sheet1.ncols)):
try:
values = sheet1.cell_value(nrows_one, s)
if sheet1.cell_value(0, s) == "score" or sheet1.cell_value(0, s) == "complete_time":
try:
#这里要注意Excel中的数值型是不能直接被拼接的,一定要转换为str
con = con + str(int(values)) + ","
except:
con = con + str(1)+","
continue
#此处orderd为最后一个字段,拼接不加逗号
elif sheet1.cell_value(0, s) == "orderd":
try:
con = con + str(int(values))
except:
con = con + str(0)
continue
#表中字段为ENUM型时一定要给值加上单引号
elif sheet1.cell_value(0, s) == "category"or sheet1.cell_value(0, s) == "difficulty"or sheet1.cell_value(0, s) == "status"or sheet1.cell_value(0, s) == "type":
try:
con = con + "'"+str(int(values))+"'"+","
except:
con = con + "'"+ str(0)+"'"+","
continue
#此处transferContent做了一次字符串的处理,以正确保留单引号,双引号,反斜杠等
values = transferContent(str(values))
if s == int(sheet1.ncols) - 1:
con = con + "'" + str(values) + "'"
break
con = con + "'" + str(values) + "',"
except:
print("出问题的是" + str(s) + "," + str(values))
#拼接sql语句
sql = "insert into"+ sheet1.name"(" + title + ") values(" + con + ")"
# 将数据存入数据库
try:
# 使用 cursor() 方法创建一个游标对象 cursor
cursor.execute(sql)
db.commit() # 事务提交
print(str(nrows_one) + '行存入成功')
except Exception as e:
# 发生错误时回滚
db.rollback()
print(str(nrows_one) + "行错了")
print(str(e))
# 关闭数据库连接
db.close()

Python打包为exe

此处采用pyinstaller,pip install pyinstaller,解压以后安装pyinstaller,进入解压包目录,python setup.py install,将你的py程序复制到解压包目录下,pyinstaller -F 文件名.py ,跑完以后将生成两个文件夹,在dist目录下就是生成的exe文件
完整源码地址:Github