CODEWITHSUNDEEP
pythonsql-serverpyodbc
iammdi
iammdi

Reputation: 41

Pyodbc: how can I get the primary key after a successful insert into the SQL Server database?

I have a table Employee in SQL Server as follows:

ID (AUTO, PK),
firstname (varchar),
lastname (varchar)

I want to insert data like ('John', 'Myers') into the table.

I used the following code in Python using pyodbc:

connection = pyodbc.connect(...)
cursor = connection.cursor()
cursor.execute("insert into employee(firstname, lastname) values(?, ?)", ['John','Myers'])

Is it possible to get the ID value of this newly inserted row without having to write a select query?

Upvotes: 1

Views: 2022

Answers (1)

Charlieface
Charlieface

Reputation: 72153

You can use the OUTPUT clause

cursor.execute("insert into employee(firstname, lastname) output inserted.ID values(?, ?);", ['John','Myers'])
id = cursor.fetchone()

Alternatively, use SCOPE_IDENTITY()

cursor.execute("insert into employee(firstname, lastname) values(?, ?); select SCOPE_IDENTITY();", ['John','Myers'])
id = cursor.fetchone()

Upvotes: 5

Related Questions