Function sequence error in PYODBC

Question

I use pyodbc to connect to a database and extract certain data from it.

Here is my code:

 con = pyodbc.connect("driver={SQL Server};server= MyServer;database= MyDatabase;trusted_connection=true") cursor = con.cursor() SQL_command = """ SELECT RowID = ISNULL ( ( SELECT TOP 1 RowID FROM [MyDatabase].[admin].[MyTable] WHERE [queue] = ? and processed IS NULL ) ,-1 ) """ cursor.execute(SQL_command, queueNumber) cursor.commit() con.commit() result_set = cursor.fetchall() 

And I got the following error after running the code:

pyodbc.Error: ('HY010', '[HY010] [Microsoft] [ODBC SQL Server Driver] Function sequence error (0) (SQLFetch)')

Can I find out what caused this problem, and how can I fix it?

Thanks.

Answer 1

I believe your problem is with strange commit statements. You only need to commit when inserting or updating not entries.

 cursor.execute(SQL_command, queueNumber) result_set = cursor.fetchall() 

In addition, in the future when using commit, and cursor.commit and con.commit do the same thing, you will only need one.

Finally, I'm used to calling execute with the second argument as a tuple:

 cursor.execute(SQL_command, (queueNumber,)) 

The way it works, pyodbc works, but is not a DB API standard.