Python MySQLdb executemany的使用和遇到的问题

最新推荐文章于 2022-11-16 16:15:33 发布

原创最新推荐文章于 2022-11-16 16:15:33 发布 · 3.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#python #mysql

python 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用Python的executemany方法快速将大量数据插入MySQL数据库，并解决过程中遇到的max_allowed_packet限制问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前几天遇到一个问题，有1700万条以文本保存的数据，要在一个小时内插入到mysql数据库中。按照普通方法1700万条数据需要一天多的时间才能导入，但是一想到大佬们肯定帮我们解决了这个问题，就去百度了一下，果然Python MySQLdb中有一个executemany方法可以快速导入。

使用方法如这位博主介绍的一样，大家自己点进去看，我就直接贴一下我自己用的代码

def get_xls_table():  
    FILE_NAME = 'test.xls'  
    data = xlrd.open_workbook(FILE_NAME)  
    table = data.sheets()[0]  
    return table 

def insert_by_many(table,param):
    nrows = table.nrows

    try:
        sql = 'INSERT INTO test1 values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        cur.executemany(sql, param)
        conn.commit()
    except Exception as e:
        print e
        conn.rollback()    
    print '[insert_by_many executemany] total:',table.nrows-1    

conn = MySQLdb.connect(host="127.0.0.1", port=3306, user="root", passwd="", db="test")
cur = conn.cursor()

table = get_xls_table()

nrows = table.nrows
param = []
for i in xrange(1,nrows):
        param.append([table.cell(i, 0).value, table.cell(i, 1).value,table.cell(i, 2).value,str(table.cell(i, 3).value), table.cell(i, 4).value, table.cell(i, 5).value, table.cell(i, 6).value, table.cell(i, 7).value, table.cell(i, 8).value, table.cell(i, 9).value, table.cell(i, 10).value, table.cell(i, 11).value, table.cell(i, 12).value])

start = time.clock()
insert_by_many(table,param)
end = time.clock()
print '[insert_by_many executemany] Time Usage:',end-start


if cur:
    cur.close()
if conn:
    conn.close()

和原文不同之处在于没有和execute的对比，以及我的数据列数比较多（不用在意），数据量65535条（为什么是这样一个数字后面再说），耗时3.44259751051秒。这个效果完全可以满足要求。

这里遇到一个问题：经测试，当数据量大于30000条（其实是一定大小容量，这里只是根据我的数据说个大概）时会出现MySQL server has gone away 的问题。

按照这个方法，这个问题属于原因四：Your SQL statement was too large.

可以临时设定一下，mysql重启后失效

mysql> set global max_allowed_packet=1024*1024*16;
mysql> show global variables like 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in set (0.00 sec)

或者修改my.ini，重启后生效

max_allowed_packet = 100M

修改my.ini时又遇到一个问题（我真是麻烦不断）：修改后重启并没有生效。
确认了一下my.ini确实是被导入的配置文件，百度许久也没找到相应的解决方案，最后将要放弃之际，突然撇到

#别人的
[mysqld]  
basedir=D:/Program Files (x86)/MySql
#我的，wamp集成安装
# The MySQL server
[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
#修改后的
# The MySQL server
[wampmysqld64]
port        = 3306
socket      = /tmp/mysql.sock

看起来好像没什么不对，但是之前确认是不是被导入文件时看到的服务名称是wampmysqld64，好像不太一样，修改之后重启服务就OK了，好神奇，还可以这样0.0 。

回到原来的问题上来，生成模拟数据test.xls的代码我就不附上了，和最上面那位博主基本一致，只是这里又遇到一个问题。我本来想生成100万条测试数据，但是遇到报错

ValueError: row index was 65536, not allowed by .xls format

这是因为是用的模块xlwt单个sheet最大行数是65535，如需要更大的，建议使用openpyxl模块，pip安装就是了。参考

写了看着挺长一大段，直接和executemany相关的并不多，以上都是我在测试使用时遇到的问题和解决的方法，记录下来供大家参考。

最后的最后，那1700万条数据其实也是用最上面链接博客的最下面的方法，MySQL自带的load data infile解决的，确实方便好用，嘿嘿嘿。链接在此，注意一下分隔符用’\n’还是’/n’的区别。