[Tutor] CSV -> sqlite tables with foreign keys

[Monte Milanuk]

Hello,

I'm working on a python script to take the sql script, create a sqlite3
database, create the tables, and then populate them using the info from the
csv file.
The sticking point seems to be creating the foreign keys between the tables

I've got a data file with lines like this:

"John","G.","Smith","1972-11-10","123 Any
Place","Somewhere","Missouri","58932"

I have an SQL script set up to create the tables with the appropriate
fields.

What I have so far that works is something like this:

try:
    data = open(CSV_FILE, 'rb')
    reader = csv.reader(data)
    for row in reader:
        cursor.execute('''INSERT INTO people (first_name, mid_init,
last_name, birth_date)
                VALUES (?,?,?,?)''', row[0:4])

        person = cursor.lastrowid
        address = list(row)
        address.append(person)
        row = tuple(address)

        cursor.execute('''INSERT INTO physical_address (street, city,
state, zip_code,person)
                VALUES (?,?,?,?,?)''', row[4:])
finally:
    data.close()


It works... but from what I've found on the web, I get the distinct
impression that converting from a tuple to a list and back is considered
poor practice at best and generally to be avoided.

I'm not really sure how else to go about this, though, when I need to split
one row from a CSV file across two (or more) tables in a database, and
maintain some sort of relation between them.

Any suggestions?


Thanks,

Monte
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20121016/f108ca5c/attachment-0001.html>