'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 |
|---|
