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

user_1368195 0
Viewed 11375

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)')
pyodbc sql-server python
3 Answers

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()
user_21443900

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]
user_7655400

If you're using SQLAlchemy with an engine, then you can retrieve the PyODBC cursor like this before running the query and fetching the table ID.

    connection = sql_alchemy_engine.raw_connection()
    cursor = connection.cursor()
    result = cursor.execute(
        """
        INSERT INTO MySchema.MyTable (Col1, Col2) OUTPUT INSERTED.MyTableId 
        VALUES (?, ?);
        """,
        col1_value,
        col2_value,
    )
    myTableId = cursor.fetchone()[0]
    cursor.commit()
    print("my ID is:", myTableId)
user_12649200
Related