python - 大量行的 MySQLdb 错误的 executemany

我目前正在运行一个脚本，使用 execute many 函数将值(元组列表)插入到 MySQL 数据库中。当我使用少量行(`1000)时，脚本运行良好。

当我使用大约 40,000 行时，我收到以下错误:

cursor.executemany( stmt, trans_frame)
Traceback (most recent call last):
  File "C:\Python27\lib\site-packages\IPython\core\interactiveshell.py", line 2538, in run_code
    exec code_obj in self.user_global_ns, self.user_ns
  File "<ipython-input-1-66b44e71cf5a>", line 1, in <module>
    cursor.executemany( stmt, trans_frame)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 253, in executemany
    r = self._query('\n'.join([query[:p], ',\n'.join(q), query[e:]]))
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 346, in _query
    rowcount = self._do_query(q)
  File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 310, in _do_query
    db.query(q)
OperationalError: (2006, 'MySQL server has gone away')

有什么建议么？

最佳答案

您可以尝试设置 max_allowed_packet 参数 just for one session :

sql ='SET SESSION max_allowed_packet=500M'
cursor.execute(sql)
sql = ...
args = ...
cursor.executemany(sql, args)

如果这有效，您可以保留代码原样，或更改 my.cnf 文件(知道这可以解决 executemany 问题)。