Don’t use “CALL StoredProcedure()” in Python :)

This issue is related to MySQL (I’m using mysql-connector-python). Surprisingly, when I was changed my query to a stored procedure, my code stopped working. It looked like this:

cursor = cnx.cursor()
cursor.execute("CALL myStoredProcedure()")
for row in cursor:
    print(row)
cursor.close()

# other piece of application

cursor = cnx.cursor()
cursor.execute("SELECT something FROM someTable")
for row in cursor:
    print(row)
cursor.close()

And it ended up with such an error on the second cursor creation:

mysql.connector.errors.OperationalError: MySQL Connection not available.

It is a known MySQL issue, that the connection is not available if not all data is read from an unbuffered cursor. It is even stated on the page here: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-fetchall.html – You must fetch all rows for the current query before executing new statements using the same connection.

In the Stored Procedure case, however, there is no way to fetch all data using the execute function on the “CALL StoredProcedure()” statement. It will simply not work.

The proper way

The proper method of calling the stored procedure is to use “callproc” like this:

cursor = cnx.cursor()
cursor.callproc("myStoredProcedure")
for result in cursor.stored_results():
    for row in result.fetchall():
        print(row)
cursor.close()

# other piece of application

cursor = cnx.cursor()
cursor.execute("SELECT something FROM someTable")
for row in cursor:
    print(row)
cursor.close()

It is described in more detail in the documentation: https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-callproc.html

In my case, the stored procedure has no parameters. If the result is provided via parameters, you can catch the result like this:

args = (1, 2, 3)
result_args = cursor.callproc("myStoredProcedure", args)
print(result_args)

The piece I’m using (cursor.stored_results()) is fetching the data that is provided from the stored procedure directly (as the SELECT statement in the procedure).