How to get the IDENTITY value when using INSERT ... OUTPUT with pyodbc

Question

I am trying to get the ID of a newly inserted row by using OUTPUT. However, I encountered the HY010 error. The following query/code is what I use:

string = """
         SET NOCOUNT ON;
         DECLARE @NEWID TABLE(ID INT);

         INSERT INTO dbo.t1 (Username, Age)
         OUTPUT inserted.id INTO @NEWID(ID)
         VALUES(?, ?)

         SELECT ID FROM @NEWID
         """

cursor.execute(string, "John Doe", 35)
cursor.commit()
id = cursor.fetchone()[0]

the last line id = cursor.fetchone()[0] led to a HY010 error (see below). Any advice would be greatly appreciated!

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

Answer 1

I was able to reproduce your issue, and I was able to avoid it by retrieving the id value immediately after the INSERT and before the commit. That is, instead of

cursor.execute(string, "John Doe", 35)
cursor.commit()
id = cursor.fetchone()[0]

I did

cursor.execute(string, "John Doe", 35)
id = cursor.fetchone()[0]  # although cursor.fetchval() would be preferred
cursor.commit()

Answer 2

For me only this worked with Azure SQL Serverless (using pyodbc==4.0.28):

cursor.execute(insert_statement, param_value_list)
cursor.execute("SELECT @@IDENTITY AS ID;")
return cursor.fetchone()[0]