python操作excel导入数据到mysql

需求：excel的兑换码导入mysql中,总共有30W条数据。Navicat有自带功能，可以直接将excel的数据导入到mysql中，数据库太多，手动操作非常麻烦，使用python写了一个脚本。

使用pip导入xlrd,pymysql库。

第一版:

import xlrd
import pymysql
import math

# book = xlrd.open_workbook('activity_password1.xlsx')
# sheet = book.sheet_by_name('@activity_password')

filelist = ['activity_password1.xlsx', 'activity_password2.xlsx', 'activity_password3.xlsx', \
            'activity_password4.xlsx', 'activity_password5.xlsx', 'activity_password6.xlsx', \
            'activity_password6_1.xlsx', 'activity_password7.xlsx', 'activity_password8.xlsx']

for i in range(1, 100):
    # 建立mysql连接
    conn = pymysql.connect(
            host = '127.0.0.1',
            user='root',
            passwd='123456',
            db='youxi' + str(i),
            port=3306,
            charset='utf8'
        )

    # 获得游标
    cur = conn.cursor()

    sql = 'insert into gm_password (id, type, code, status) values (%s, %s, %s, %s)'

    for filename in filelist:
        book = xlrd.open_workbook(filename)
        sheet = book.sheet_by_name('@activity_password')
        for r in range(1, sheet.nrows):
            values = (sheet.cell(r, 0).value, sheet.cell(r, 1).value, sheet.cell(r, 2).value, sheet.cell(r, 3).value)
            cur.execute(sql, values)
        conn.commit()
    cur.close()
    conn.close()
    print ('youxi'+str(i)+' sucess')

执行了一下，速度满的简直不能忍。并且如果往外网导入数据，就是超时。进行优化。

优化之后的代码：

import xlrd
import pymysql
import math

# book = xlrd.open_workbook('activity_password1.xlsx')
# sheet = book.sheet_by_name('@activity_password')

filelist = ['activity_password1.xlsx', 'activity_password2.xlsx', 'activity_password3.xlsx', \
            'activity_password4.xlsx', 'activity_password5.xlsx', 'activity_password6.xlsx', \
            'activity_password6_1.xlsx', 'activity_password7.xlsx', 'activity_password8.xlsx']

for i in range(1， 100):
    # 建立mysql连接
    conn = pymysql.connect(
            host = '127.0.0.1',
            user='root',
            passwd = '123456',
            db='youxi' + str(i),
            port=3306,
            charset='utf8'
        )

    # 获得游标
    cur = conn.cursor()

    for filename in filelist:
        book = xlrd.open_workbook(filename)
        sheet = book.sheet_by_name('@activity_password')
        ops = []
        for r in range(1, sheet.nrows):
            values = (sheet.cell(r, 0).value, sheet.cell(r, 1).value, sheet.cell(r, 2).value, sheet.cell(r, 3).value)
            ops.append(values)
        n = math.ceil(len(ops) / 5000)
        for n1 in range(0, n):
            cur.executemany('insert into gm_password (id, type, code, status) values (%s, %s, %s, %s)', ops[5000*n1:5000*(n1+1)])
    cur.close()
    conn.commit()
    conn.close()
    print ('youxi'+str(i)+' sucess')

优化点总结：
1、批量插入，然后再提交，而不是插一条提交一条。
2、将execute替换为executemany,但是也不要插入太多行，我的设置是5000。
3、不要自己拼接 SQL 语句。直接在executemany方法中执行。
4、减少commit次数，非常重要。

结果：一个数据库插入需要30s，已经满足需求，没有继续优化，应该还有可以继续优化的点。