insert statements get id

[woif00]

Hello,

I wanted to ask if it's possible to get the id of a row after inserting without doing a select after?
Like if it's possible to get the id of the inserted row for the cursor?

I couldn't find anything in the docs.
Thanks in advance.

Also, keywords for other users who might search this topic:
"INSERT INTO"; inserted id;

[gordthompson]

If your DBMS supports an OUTPUT clause (or similar) for DML then you can use that. For example, with MS SQL Server:

import pyodbc

cnxn = pyodbc.connect(
    "DSN=mssql_199;UID=scott;PWD=tiger^5HHH", autocommit=True
)
crsr = cnxn.cursor()

table_name = "discussion_1162"
crsr.execute(f"DROP TABLE IF EXISTS {table_name}")
crsr.execute(
    f"CREATE TABLE {table_name} (id int identity primary key, txt varchar(50))"
)

sql = f"""\
INSERT INTO {table_name} (txt) OUTPUT inserted.id VALUES (?)
"""
new_id = crsr.execute(sql, "foo").fetchval()
print(new_id)  # 1
new_id = crsr.execute(sql, "bar").fetchval()
print(new_id)  # 2

[MulleOne]

@gordthompson this worked for me, thank you so much.

[nathant1234567]

Thanks so much