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

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!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source