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 cursor
s, 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
All credits to @Martijn Pieters in the comments:
You can use the function last_insert_rowid()
:
The
last_insert_rowid()
function returns theROWID
of the last row insert from the database connection which invoked the function. Thelast_insert_rowid()
SQL function is a wrapper around thesqlite3_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