SQLite3 — INSERT and SELECT in one step with JOIN

Question

I am using SQLite3 and Python 3. There are a lot of places in my current code in which I need to select the primary key value of a newly inserted record. The primary keys are autoincremented, and I would prefer to keep it that way (rather than generating the primary keys myself and keeping track of them on the Python side).

I do this in two steps — SELECT after INSERT — but I wonder if there is not a way of doing it in a single step for greater efficiency, using a JOIN.

Edit: It has been pointed out to me that there is already a solution in an earlier question, using cursor.lastrowid, which is indeed much faster than two execute statements (timeit code below uses the example in the solution linked-to above):

$ python -m timeit -s "\
> import sqlite3;\
> 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'));\
> found = cursor.execute('''SELECT id FROM foo \
>                                     WHERE username='test' \
>                                     AND password='test' ''')"
100000 loops, best of 3: 10.1 usec per loop
$
$ python -m timeit -s "\
> import sqlite3;\
> 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'));\
> found = cursor.lastrowid"
100000 loops, best of 3: 5.74 usec per loop
$

Can anyone speak to the possibility of doing this as a JOIN, instead, so that it is still involves a single execute but is strictly limited to SQL commands?

possible duplicate of [How to retrieve inserted id after inserting row in SQLite using Python?](http://stackoverflow.com/questions/6242756/how-to-retrieve-inserted-id-after-inserting-row-in-sqlite-using-python) — CL., Aug 11 '13 at 14:39
Thanks for pointing that out; I've revised my question to be narrower. — brannerchinese, Aug 11 '13 at 14:59
I guess I should have said "strictly using SQL instructions." — brannerchinese, Aug 11 '13 at 20:39

CL. · Answer 1 · 2013-08-11T21:35:07.323

1

It is not possible to do joins in INSERT statements.

If you want to use SQL commands, you could use the last_insert_rowid SQL function. However, the same value is already returned by the INSERT command and can be read with the Cursor object's lastrowid property in Python, or the equivalent in other languages' database wrappers.

edited Aug 11 '13 at 21:35

answered Aug 11 '13 at 14:37

CL.

165,803
15
203
239

SQLite3 — INSERT and SELECT in one step with JOIN

1 Answers1