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

by

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

More Related Contents:

  1. Variable table name in sqlite
  2. SQLite parameter substitution problem
  3. Importing a CSV file into a sqlite3 database table using Python
  4. Django: Deploying an application on Heroku with sqlite3 as the database
  5. How do you escape strings for SQLite table/column names in Python?
  6. How to load existing db file to memory in Python sqlite3?
  7. Sqlite insert query not working with python?
  8. Python SQLite parameter substitution with wildcards in LIKE
  9. Inserting a table name into a query gives sqlite3.OperationalError: near “?”: syntax error
  10. Using a WHERE ___ IN ___ statement
  11. Python insert numpy array into sqlite3 database
  12. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  13. Python SQLite: database is locked
  14. How to discover table properties from SQLAlchemy mapped object
  15. Auto Increment on Composite Primary Key – Sqlite3 + Python
  16. How to get a single result from a SQL query in python?
  17. Parameter unsupported when inserting int
  18. Executing an SQL query over a pandas dataset
  19. SQLite Performance Benchmark — why is :memory: so slow…only 1.5X as fast as disk?
  20. Being that string substitution is frowned upon with forming SQL queries, how do you assign the table name dynamically?
  21. How to upgrade sqlite 3.8.2 to >= 3.8.3
  22. creating a database outside the application context
  23. sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings
  24. How to delete a table in SQLAlchemy?
  25. SQLite, python, unicode, and non-utf data
  26. Python and SQLite: insert into table
  27. How to check the existence of a row in SQLite with Python?
  28. Pycharm environment different than command line
  29. sqlite3.OperationalError: no such column – but I’m not asking for a column?
  30. Django: sudden DB reset after Heroku sleep

Leave a Comment