237

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?

gustafbstrom
  • 1,622
  • 4
  • 25
  • 44
Jane
  • 2,373
  • 2
  • 14
  • 4

2 Answers2

338

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
ChrisGPT was on strike
  • 127,765
  • 105
  • 273
  • 257
unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • 51
    There is also the [`last_insert_rowid()` SQL function](http://www.sqlite.org/lang_corefunc.html#last_insert_rowid), allowing you to insert the last row id as a foreign key in a *next* insert statement, entirely in SQL. – Martijn Pieters Feb 03 '14 at 12:36
  • 3
    appears Python's sqlite3 lastrowid uses `last_insert_rowid` underneath https://github.com/ghaering/pysqlite/blob/e728ffbcaeb7bfae1d6b7165369bd0ae16cabf95/src/cursor.c (which isn't guaranteed threadsafe but seems the only option FWIW). See also http://stackoverflow.com/q/2127138/32453 – rogerdpack Jan 26 '17 at 20:41
  • Here's the direct link to [`cursor.lastrowid`](https://www.python.org/dev/peps/pep-0249/#lastrowid) – stefanbschneider Oct 25 '18 at 16:15
  • 3
    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...). – stefanbschneider Oct 25 '18 at 16:16
  • 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? – gamesguru Oct 02 '20 at 14:07
45

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
legends2k
  • 31,634
  • 25
  • 118
  • 222
Stefan van den Akker
  • 6,661
  • 7
  • 48
  • 63
  • 6
    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. – Bill Weinman Mar 05 '21 at 03:50
  • 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." – Dominik Oct 27 '21 at 09:47
  • 4
    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. – laurent Dec 04 '21 at 19:46
  • SQLite only allows a single writer at once, so that shouldn't be possible @laurent. No? – Pieter-Jan Briers Jan 17 '22 at 20:08
  • 2
    That would be possible if something writes to the database between your write and the moment you call last_insert_rowid. – laurent Jan 18 '22 at 14:27