INSERT and SELECT in one step with JOIN

Question

I am using SQLite3 and Python 3. I need to select the primary key value of a newly inserted record. The primary keys are autoincremented, I prefer to keep it that way (rather than generating primary keys myself and keeping track of them in Python). I do this in two steps (SELECT after INSERT) but want it in a single step for efficiency, using a JOIN.

Using cursor.lastrowid is faster than two execute statements (timeit uses linked example):

$ 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
$ 

How to do this as a JOIN instead, so it is still involves a single execute but is strictly limited to SQL commands?

Answer 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.

Answer 2

it did work ok for me. i'm using in a trigger:

insert into tbl(currency)
select instrument.currency, price.lastprice 
from instrument
inner join price on price.instrument=instrument.symbol
where instrument.symbol=new.instrument
on conflict do nothing;