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

Question

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?

User · Answer

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

User · Answer

You could use cursor lastrowid  see  quot Optional DB API Extensions quot    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

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

The answer is

Examples related to python

Examples related to sqlite

Tags