The Python Oracle

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

Become part of the top 3% of the developers by applying to Toptal https://topt.al/25cXVn

--

Music by Eric Matyas
https://www.soundimage.org
Track title: Realization

--

Chapters
00:00 Question
00:47 Accepted answer (Score 336)
01:49 Answer 2 (Score 38)
02:55 Thank you

--

Full question
https://stackoverflow.com/questions/6242...

Accepted answer links:
[cursor.lastrowid]: http://www.python.org/dev/peps/pep-0249/

Answer 2 links:
[@Martijn Pieters]: https://stackoverflow.com/users/100297/m...
[last_insert_rowid()]: https://www.sqlite.org/lang_corefunc.htm...
[SQLite 3.35]: https://www3.sqlite.org/lang_returning.h...

--

Content licensed under CC BY-SA
https://meta.stackexchange.com/help/lice...

--

Tags
#python #sqlite

#avk47


ACCEPTED ANSWER

Score 353


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


ANSWER 2

Score 60


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