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

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]

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

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)