CODEWITHSUNDEEP
pythonsqlalchemypymssql
bren
bren

Reputation: 31

Get MSSQL autogenerated id from INSERT Statement using SQLAlchemy

I am using SQLAlchemy and MSSQL. I am simply inserting a row and I would like to get back the id of the newly created record.

The SQL query that works with MSSQL and returns the id is:

INSERT INTO [table](...) OUTPUT inserted.ID VALUES (...)

and the alchemy statement I am using is:

with connection() as conn:
    query = ...
    result_proxy = conn.execute(query)

If you run the query in a sql console, you get back the ID, but I cannot figure out how to get it from the result_proxy returned by the execute statement.

things I have tried and did not work:

result_proxy.lastrowid
result_proxy.last_inserted_id
result_proxy.fetchone()
result_proxy.fetchall()

Upvotes: 1

Views: 1137

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123829

You are on the right track. I just tried this and it works for me:

import sqlalchemy as sa

# ...

# set up test environment
with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS team")
    conn.exec_driver_sql(
        "CREATE TABLE team ("
        "id int identity primary key, "
        "prov nvarchar(5), "
        "city nvarchar(50), "
        "team_name nvarchar(50)"
        ")"
    )

# perform the test
sql = """\
INSERT INTO team (prov, city, team_name) 
OUTPUT inserted.id
VALUES (:prov, :city, :team_name)
"""
with engine.begin() as conn:
    new_id = conn.execute(
        sa.text(sql), 
        {"prov": "AB", "city": "Calgary", "team_name": "Flames"}
    ).scalar()
    print(new_id)  # 1

Upvotes: 3

Related Questions