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

Monte Milanuk memilanuk at gmail.com
Sat Dec 24 01:07:55 CET 2011


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.  

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



More information about the Tutor mailing list