python pyodbc: Closing the cursor before conn.commit()?

user_18406886 0
Viewed 253

I have an issue with inserting data into a database using the python package pyodbc and since I am pretty new to pyodbc & databases in general, I might lack some basic understanding.

I open a connection, and then I want the execute my query. Actually, in this query I call a stored procedure (which I didn't write and I am not allowed to change!). This procedure does "one or two" inserts. When I use pyodbc like this

conn = pyodbc.connect(connection_string)
with conn:
    c = conn.cursor()
    c.execute("{call input_procedure('some','parameters','to','insert')}")

OR

conn = pyodbc.connect(connection_string)
c = conn.cursor()
c.execute("{call input_procedure('some','parameters','to','insert')}")
conn.commit()

I get the following error message: pyodbc.Error: ('HY000', "[HY000] [MySQL][ODBC 8.0(a) Driver]Commands out of sync; you can't run this command now (2014) (SQLEndTran(SQL_COMMIT))")

As far as I understood, this error message might be due to executing more than one insert within the called procedure. When I print the return of the execute command I become the following: (' ', )

When I instead close the cursor, before doing the commit, everything works fine. Like this:

conn = pyodbc.connect(connection_string)
c = conn.cursor()
c.execute("{call input_procedure('some','parameters','to','insert')}")
c.close()
conn.commit()

I really don't understand what's happening here. Is there an explanation for this behaviour? Is closing the cursor before doing the commit save?

Thanks a lot for your help!

sql-insert pyodbc odbc mysql python
1 Answers

You seem to have encountered a quirk in MySQL Connector/ODBC's handling of result sets from a stored procedure. For this example procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `input_procedure`(IN `p1` VARCHAR(50))
   MODIFIES SQL DATA
BEGIN
INSERT INTO table1 (txt) VALUES (p1);
SELECT '' AS foo;
END

this Python code

crsr = cnxn.cursor()
crsr.execute("{call input_procedure('thing')}")
cnxn.commit()

throws the "Commands out of sync" error, whereas this code

crsr = cnxn.cursor()
crsr.execute("{call input_procedure('thing')}")
while crsr.nextset():
    pass
cnxn.commit()

does not.

user_21443900
Related