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.
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
