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

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)        # 3print(cursor.lastrowid)# 2cursor.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

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;