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

前几天遇到一个问题，有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.nrowsparam = []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-startif 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        = 3306socket      = /tmp/mysql.sock#修改后的# The MySQL server[wampmysqld64]port        = 3306socket      = /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’的区别。