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

4

20

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)')
Rump answered 8/11, 2017 at 20:24 Comment(2)
Have you tried just performing the insert and then accessing cursor.lastrowid? python.org/dev/peps/pep-0249/#lastrowidNeighborly
I got an error message AttributeError: pyodbc.Cursor object has no attribute lastrowid.Rump
R
14

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()
Ranunculus answered 8/11, 2017 at 23:49 Comment(0)
N
16

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]
Nf answered 10/9, 2020 at 8:7 Comment(0)
R
14

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()
Ranunculus answered 8/11, 2017 at 23:49 Comment(0)
W
2

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)
Wellmannered answered 26/3, 2020 at 16:40 Comment(0)
H
0

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')")
Hypogynous answered 31/7, 2023 at 19:8 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.