executemany for MySQLdb error for large number of rows

Question

I'm currently running a script to insert values (a list of tuples) into a MySQL database, using the execute many function. When I use a small number of rows (`1000), the script runs fine.

When I use around 40,000 rows, I receive the following errors:

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')

Any suggestions?

Did you read [this](http://stackoverflow.com/a/9479681/510937) answer? It may be helpful. — Bakuriu, Nov 08 '12 at 15:12
That looks like the problem. I'm rather new to python and not sure how to adjust that parameter in python (max_allowed_packet). any suggestions? Thanks so much!! — Matt, Nov 08 '12 at 15:22
actually i found that my.cnf file... not sure if i'll mess with it. might just use execute() with a loop which is much slower — Matt, Nov 08 '12 at 15:28

score 4 · Answer 1 · edited Nov 15 '16 at 09:21

4

sql ='SET GLOBAL max_allowed_packet=500*1024*1024'
cursor.execute(sql)

edited Nov 15 '16 at 09:21

Petter Friberg

21,252
9
60
109

answered Nov 15 '16 at 09:20

CurryChen

553
4
10

this solves the problem much better than inserting blocks of rows. When in doubt, add more memory! – rocketman Jul 27 '22 at 18:07

score 3 · Answer 2 · edited May 23 '17 at 10:32

3

You could try setting the max_allowed_packet parameter just for one session:

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

If this works, you could leave the code as it is, or change your my.cnf file (knowing that that solves the executemany problem).

edited May 23 '17 at 10:32

Community

1
1

answered Nov 08 '12 at 15:37

unutbu

842,883
184
1,785
1,677

2

Thank you for your help everyone. In the end, I am not able to adjust those global variables because I do not have permission. However, I created a loop to insert 500 rows at a time, which is still significantly faster (*1000) than my loop with just an execute statement – Matt Nov 08 '12 at 16:25

executemany for MySQLdb error for large number of rows

2 Answers2

Linked