Search code examples
pythonmysqlcursormysql-python

Execute multiple statement on MySQLdb cursor

I want to execute multiple statements on a MySQLdb cursor over a database with MyISAM storage. I followed every other way explained in this answer and elsewhere, unsuccessfully.

>>> import MySQLdb as mdb
>>> from contextlib import closing

>>> con = mdb.connect('host', 'user', 'password', 'database')

>>> with closing(con.cursor()) as cur:
>>>     cur.callproc('add_date', [1418112000])
>>>     cur.callproc('add_date', [1418119000])
>>>     #con.commit()
>>>     cur.close()

_mysql_exceptions.ProgrammingError: (2014, "Commands out of sync; you can't run this command now")

Since the storage of tables is MyISAM, I should not need to call commit(). In fact if I call commit(), I will encounter an error.

What is the correct way to call multiple statements?

Solution

  • After calling the procedure, you have to close the cursor and open it again before using it to execute another statement:

    cur.close()

cur = con.cursor()

    The cursor can be closed immediately after fetchall(). The result set still remains and can be looped through. You have to clean/close the cursor after calling stored procecude in order to execute further sql code. This is not an issue with Python but with MySQL.