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?

Gastro

check the error log of the mysql server – Nl

Did you read this answer? It may be helpful. – Dickson

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!! – Gastro

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 – Gastro

Answer 1

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

Unnumbered

this solves the problem much better than inserting blocks of rows. When in doubt, add more memory! – Cytotaxonomy

Answer 2

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

Mihe

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 – Gastro