使用Python把Excel里的数据导入MYSQL

使用Python把Excel里的数据导入MYSQL

大概有几百万行的数据，Excel分类7个文件保存，想要汇总起来，Excel肯定是用不了的，之前用Access做，但是Access不支持超过2G的文件，所以只好用MYSQL，想要把数据导入MYSQL，想来想去还是Python最方便

踩过的坑

先讲踩过了哪些坑：

1. def定义函数的时候，一直不知道可以用return返回值，在这里折腾了很久，所幸最后发现了，于是第一次用了自定义函数，其实没有也可以跑，只是代码显得太难看；
2. 数据中有日期字段，一开始读取过来的时候，一直只能读取value，导入到MYSQL的时候也只能以43322这样的形式导入，研究了很久，终于找到了便捷的转换函数；
3. 数据中有时间字段，比如0.375123这样的，一直没找到合适的转换方法，只能以float的形式储存；
4. 里面有价格字段，设置了允许null，字段为float格式，但是源文件里有null的时候还是会报错，这个问题困扰了我很久，最蠢的方式是设为VARCHAR格式，不会报错了，但是文本格式储存对后续的使用影响太大，后来用if函数来解决，勉强能用，但是显得代码很蠢，尝试用def，就是第1点说的问题，不知道用return，想要的值一直取不到；
5. 增加了时间函数，用来看跑完这577万行数据需要用多少时间；
6. 要注意Python里的缩进功能，在这里也吃了亏；
7. 由于文件较大，所以在每次读取完一个文件后，显示读取情况，便于知道是否还在进行中。

参考文章

https://www.jianshu.com/p/2baf86abe57c

代码如下

import pymysql
import xlrd
import os
from datetime import datetime
from xlrd import xldate_as_tuple
from time import *
start_time = time()

# 先定义如果遇到NULL值如何处理,封装一个函数trans()
def trans(b):
    if b.ctype == 0:
        a = 0
    else:
        a = b.value
    return a
# 定义路径
path = r'F:\19'
# 首先打开文件
files = os.listdir(path)
for i in files:
    path1 = path + '\\'+i
    book = xlrd.open_workbook(path1)
    sheet = book.sheet_by_index(0)
    # 建立与MYSQL连接
    conn = pymysql.connect(
        host = 'localhost',
        user = 'root',
        passwd = '123456',
        db = 'db',
        port = 3306,
        charset = 'utf8mb4'
    )
    # 获得游标
    cur = conn.cursor()

    # 创建插入语句
    query = 'insert into apporder() values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'

   # 创建一个for循环读取excel内的每行数据
   # 从第二行开始是要跳过标题行
   # 括号里1代表从第二行开始(python计数从0开始)
    for r in range(1,sheet.nrows):
        x0 = sheet.cell(r,0).value
        x1 = sheet.cell(r,1).value
        x2 = sheet.cell(r,2).value
        x3 = sheet.cell(r, 3).value
        x4 = sheet.cell(r, 4).value
        x5 = sheet.cell(r, 5).value
        x6 = sheet.cell(r, 6).value
        x7 = sheet.cell(r, 7).value
        x8 = sheet.cell(r, 8).value
        x9 = sheet.cell(r, 9).value
        # x10 是日期数值，该数值不得为空
        x10 = datetime(*xldate_as_tuple(int(trans(sheet.cell(r, 10))), 0))
        x11 = sheet.cell(r, 11).value
        x12 = sheet.cell(r, 12).value
        x13 = sheet.cell(r, 13).value
        x14 = sheet.cell(r, 14).value
        x15 = sheet.cell(r, 15).value
        x16 = sheet.cell(r, 16).value
        x17 = sheet.cell(r, 17).value
        x18 = sheet.cell(r, 18).value
        x19 = sheet.cell(r, 19).value
        x20 = sheet.cell(r, 20).value
        x21 = sheet.cell(r, 21).value
        x22 = sheet.cell(r, 22).value
        x23 = trans(sheet.cell(r, 23))
        x24 = trans(sheet.cell(r, 24))
        x25 = trans(sheet.cell(r, 25))
        x26 = trans(sheet.cell(r, 26))
        x27 = sheet.cell(r, 27).value
        x28 = sheet.cell(r, 28).value
        x29 = trans(sheet.cell(r, 29))
        x30 = trans(sheet.cell(r, 30))
        x31 = sheet.cell(r, 31).value
        x32 = sheet.cell(r, 32).value
        x33 = sheet.cell(r, 33).value
        x34 = sheet.cell(r, 34).value
        x35 = sheet.cell(r, 35).value
        x36 = sheet.cell(r, 36).value
        x37 = sheet.cell(r, 37).value
        x38 = sheet.cell(r, 38).value
        x39 = sheet.cell(r, 39).value
        x40 = sheet.cell(r, 40).value

        values = (x0, x1, x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, x14, x15, x16, x17, x18, x19, x20, x21, x22, x23,
        x24, x25, x26, x27, x28, x29, x30, x31, x32, x33, x34, x35, x36, x37, x38, x39, x40)
        # 执行query语句
        cur.execute(query, values)
    print('导入' + i + '成功')



    # close 关闭文档
    cur.close()
    # commit 提交
    conn.commit()
    # 关闭mysql链接
    conn.close()
# 显示导入的行数和列数
endtime = time()
ordertime = start_time - endtime
print(ordertime)