CODEWITHSUNDEEP
pythonsql-serverpyodbc
Alex
Alex

Reputation: 4180

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

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

Upvotes: 20

Views: 19118

Answers (5)

Christian Navelot
Christian Navelot

Reputation: 1164

the given accepted answer works for me with SQL Express on local and Pyodbc, I just want to stress something that eluded me (as I'm rather familiar with C# and EF) : I forgot at first to add :

OUTPUT inserted.id

in the SQL clause, and it is mandatory to get the IDENTITY result.

Upvotes: 0

heradsinn
heradsinn

Reputation: 113

For other people who are googling how to find the latest auto-generated ID, but for some reason it doesn't work or returns None as it did for me, using Azure SQL. An alternative solution to having it auto-generate the ID and then retrieving it is to use the uuid module in Python (https://docs.python.org/3/library/uuid.html) and generate an ID yourself, then using that to insert.

import uuid
row_id = uuid.uuid4()
cursor.execute(f"INSERT INTO Person(ID,name) VALUES ({row_id}, 'Graham Smith')")

Upvotes: 0

Blink
Blink

Reputation: 1556

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]

Upvotes: 17

TPPZ
TPPZ

Reputation: 4901

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)

Upvotes: 2

Gord Thompson
Gord Thompson

Reputation: 123829

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

Upvotes: 14

Related Questions