I am using mysql.connector in Python 3.
This works the first time it is called, but not the second.
def execute (self, query, *args):
c = self.connection.cursor (prepared = True)
return c.execute (query, args)
I was expecting it to create a new cursor for each query, but I am getting surprising errors.
print (dbobject.execute ("CALL MyProc(%s);", 1))
print (dbobject.execute ("CALL MyProc(%s);", 1))
The output is
MySQLCursorPrepared: CALL MyProc(%s);
Traceback (most recent call last):
File "py/DB.py", line 348, in <module>
print (db.execute ("CALL MyPro (%s);", 1))
File "py/DB.py", line 156, in execute
c = self.connection.cursor (prepared = True)
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 810, in cursor
self.handle_unread_result()
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 1063, in handle_unread_result
raise errors.InternalError("Unread result found")
If instead I
print (list (dbobject.execute ("CALL MyProc(%s);", 1)))
print (list (dbobject.execute ("CALL MyProc(%s);", 1)))
then the first one prints a list of results, the second one throws
mysql.connector.errors.ProgrammingError: 1210: Incorrect number of arguments executing prepared statement
Assuming a single thread owns the database object, I would like to have one compiled prepared statement object (is that what MySQLCursor represents?) for each of the fixed set of predefined queries, and generate a new prepared statement for each dynamically-build query on demand. I expected dbobject.connection.cursor() to return a new object each time it is called, but the above errors hint that state is being shared in some way and the subsequent execute calls are not being handled independently.
How should I use prepared statement objects/cursors in this situation?