1

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?

ballade4op52
  • 2,142
  • 5
  • 27
  • 42
bman
  • 5,016
  • 4
  • 36
  • 69

1 Answers1

0

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.

sid1408
  • 404
  • 3
  • 10