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

本文介绍如何使用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’的区别。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值