[Tutor] insert queries into related tables referencing foreign keys using python

[Lie Ryan]

On 12/24/2011 11:07 AM, Monte Milanuk wrote:
> So... most python-sqlite tutorials concentrate on single tables.  The few that
> deal with multiple tables and that mention foreign keys and such seem to
> demonstrate mainly using hard-coded data instead of parameterized insert queries
> into tables with auto-increment primary keys.  For the most part I'm able to
> figure things out as I go using a variety of documents both print and
> electronic... but when I don't *know* the pk number (because its automatically
> assigned) it makes it tough to supply it as a foreign key for another insert
> query into related tables.

In sqlite, if a table contains a column of type INTEGER PRIMARY KEY, 
then that column becomes an alias for the ROWID 
(http://www.sqlite.org/autoinc.html). In python-sqlite, the rowid of the 
last insert operation can be queried using cursor.lastrowid. Therefore, 
you can query the lastrowid, right after the insert, to find the primary 
key of the value you had just inserted. So, in code:

...
cur = conn.execute('INSERT ... ')
pk = cur.lastrowid
...

or even:

...
pk = conn.execute('INSERT ... ').lastrowid
...

Be careful that in multithreaded program, each thread should have their 
own cursors, or otherwise another thread could possibly do another 
insert before you can query the lastrowid.

> Whats the 'right' way to do this sort of record insert or update query?  Insert
> into the main table first, then do a select query to find the last rowid and
> store it in a python variable and then use that as a parameter for the rest of
> the insert queries to related tables?  Pull the value from the seq column of the
> sqlite-sequence table for the table with the primary key, and use that (not sure
> how robust that would be down the road, or how portable it would be if I later
> moved to MySQL for the DB)?  Or is this something an ORM like SQLalchemy would
> smooth over for me?  In part I'm (also) wondering if this may be an artificial
> problem, as I'm trying to import data from a csv file i.e. one big table and
> then break it up and insert it into multiple tables in the sqlite database...

In general, despite the superficial similarities, most database engine 
wrappers have their own ways of doing stuffs. Generally, you need a 
full-blown ORM to smooth out the differences.