1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
iammdi
  • 41
  • 6
  • This one may help you.. https://stackoverflow.com/questions/2548493/how-do-i-get-the-id-after-insert-into-mysql-database-with-python – Dulaj Umansha Jun 09 '23 at 15:40

1 Answers1

5

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()
Charlieface
  • 52,284
  • 6
  • 19
  • 43
  • I know this is an old post, however, in my case, the value is returned into id, but the inserted record does NOT show up in the table. Please advise! – NoRestartOnUpdate May 24 '23 at 20:12
  • Sounds like you have `autocommit=false` (ie `IMPLICIT_TRANSACTIONS ON`) or you have an explicit transaction which you are not committing – Charlieface May 24 '23 at 22:10