How to retrieve inserted id after inserting row in SQLite using Python?

Question

How to retrieve inserted id after inserting row in SQLite using Python? I have table like this:

id INT AUTOINCREMENT PRIMARY KEY,
username VARCHAR(50),
password VARCHAR(50)

I insert a new row with example data username="test" and password="test". How do I retrieve the generated id in a transaction safe way? This is for a website solution, where two people may be inserting data at the same time. I know I can get the last read row, but I don't think that is transaction safe. Can somebody give me some advice?

Chris · Accepted Answer · 2021-01-11 20:31:08Z

370

You could use cursor.lastrowid (see "Optional DB API Extensions"):

connection=sqlite3.connect(':memory:')
cursor=connection.cursor()
cursor.execute('''CREATE TABLE foo (id integer primary key autoincrement ,
                                    username varchar(50),
                                    password varchar(50))''')
cursor.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('test','test'))
print(cursor.lastrowid)
# 1

If two people are inserting at the same time, as long as they are using different cursors, cursor.lastrowid will return the id for the last row that cursor inserted:

cursor.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('blah','blah'))

cursor2=connection.cursor()
cursor2.execute('INSERT INTO foo (username,password) VALUES (?,?)',
               ('blah','blah'))

print(cursor2.lastrowid)        
# 3
print(cursor.lastrowid)
# 2

cursor.execute('INSERT INTO foo (id,username,password) VALUES (?,?,?)',
               (100,'blah','blah'))
print(cursor.lastrowid)
# 100

Note that lastrowid returns None when you insert more than one row at a time with executemany:

cursor.executemany('INSERT INTO foo (username,password) VALUES (?,?)',
               (('baz','bar'),('bing','bop')))
print(cursor.lastrowid)
# None

edited Jan 11, 2021 at 20:31

Chris

139k139 gold badges316 silver badges293 bronze badges

answered Jun 5, 2011 at 12:09

unutbu

886k197 gold badges1.9k silver badges1.7k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Martijn Pieters Over a year ago

There is also the last_insert_rowid() SQL function, allowing you to insert the last row id as a foreign key in a next insert statement, entirely in SQL.

rogerdpack Over a year ago

appears Python's sqlite3 lastrowid uses last_insert_rowid underneath github.com/ghaering/pysqlite/blob/… (which isn't guaranteed threadsafe but seems the only option FWIW). See also stackoverflow.com/q/2127138/32453

stefanbschneider Over a year ago

Here's the direct link to cursor.lastrowid

stefanbschneider Over a year ago

Btw, this works for INSERT but does not work for UPDATE. But when updating a row, you probably already have the corresponding row id anyways (just took me a while to figure this out...).

gamesguru Over a year ago

What is meant by "id"? Is it referring to an id column as integer? How does it know, what if I don't have an id column for a certain table?

|

3 revs, 2 users 55% · Accepted Answer · 2022-09-01 00:40:30Z

65

All credits to @Martijn Pieters in the comments:

You can use the function last_insert_rowid():

The last_insert_rowid() function returns the ROWID of the last row insert from the database connection which invoked the function. The last_insert_rowid() SQL function is a wrapper around the sqlite3_last_insert_rowid() C/C++ interface function.

SQLite 3.35's `RETURNING` clause:

CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  first_name TEXT,
  last_name TEXT
);

INSERT INTO users (first_name, last_name)
VALUES ('Jane', 'Doe')
RETURNING id;

returns requested columns of the inserted row in INSERT, UPDATE and DELETE statements. Python usage:

cursor.execute('INSERT INTO users (first_name, last_name) VALUES (?,?)'
               ' RETURNING id',
               ('Jane', 'Doe'))
row = cursor.fetchone()
(inserted_id, ) = row if row else None

edited Sep 1, 2022 at 0:40

community wiki

3 revs, 2 users 55%
Stefan van den Akker

5 Comments

Bill Weinman Over a year ago

The LAST_INSERT_ROWID() function is specific to SQLite. The cursor.lastrowid property is part of the Python DB-API specification so it should be more portable.

Dominik Over a year ago

Note that the RETURNING clause works with cursor.execute but won't work with cursor.executemany. Python will complain that "executemany() can only execute DML statements."

laurent Over a year ago

Depending on your program you should also be careful with race conditions, if multiple threads might be writing to the database in parallel. In that case you can't assume that last_insert_rowid will the row you've just inserted. Thankfully RETURNING can now be used to solve this, although it's not yet available everywhere.

Pieter-Jan Briers Over a year ago

SQLite only allows a single writer at once, so that shouldn't be possible @laurent. No?

laurent Over a year ago

That would be possible if something writes to the database between your write and the moment you call last_insert_rowid.

Collectives™ on Stack Overflow

How to retrieve inserted id after inserting row in SQLite using Python?

2 Answers 2

6 Comments

SQLite 3.35's `RETURNING` clause:

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

SQLite 3.35's RETURNING clause:

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

SQLite 3.35's `RETURNING` clause: