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

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

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.

Update: you can use RETURNING in SQLite 3.35:

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0