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