python mysql connection.cursor().execute fails on the second query

Viewed 264

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?

1 Answers

You are trying to pass an integer instead of an argument array.

Instead of

dbobject.execute("CALL MyProc(%s);", 1)

Use

dbobject.execute("CALL MyProc(%s);", (1,))

It should work.

Related