[Tutor] CSV -> sqlite tables with foreign keys

eryksun eryksun at gmail.com
Wed Oct 17 11:19:27 CEST 2012

On Tue, Oct 16, 2012 at 11:59 PM, Monte Milanuk <memilanuk at gmail.com> wrote:
>         address = list(row)
>         address.append(person)
>         row = tuple(address)

The rows from the csv.reader are already lists. Also, the parameter
list in the 2nd argument only needs to be a sequence (e.g. tuple,
list, string), or it can also be a dict if the statement uses named

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

To update a record in a tuple you can use slicing and concatenation
(+) as an alternative to creating a temporary list.  A list is more
applicable to homogenous data (e.g. a list of tuples, each a data
record). If you want a container for a record that you can modify more
efficiently, use a dict or a custom object. For the latter, look into
ORMs such as Storm:


> Any suggestions?

I found a Stack Overflow answer that uses a table "view" combined with
an "instead of" trigger to update two tables with one insert.


Here's my meager attempt at an adaptation (some names have been
changed to protect the innocent...):

    import csv
    import sqlite3

    con = sqlite3.connect(':memory:')
    cur = con.cursor()

    cur.execute('''create table person (
      id integer primary key autoincrement,
      firstname, midinit, lastname, birthdate)

    cur.execute('''create table address (
      id integer primary key autoincrement,
      person_id integer references person not null,
      street, city, state, zipcode)

    cur.execute('''create view person_view as
        person.firstname, person.midinit, person.lastname,
        person.birthdate, address.street, address.city,
        address.state, address.zipcode
        person inner join address on person.id = address.person_id

    cur.execute('''create trigger person_view_insert
      instead of insert on person_view
        insert into
          person (firstname, midinit, lastname, birthdate)
          values (new.firstname, new.midinit, new.lastname,
        insert into
          address (person_id, street, city, state, zipcode)
          values ((select last_insert_rowid()),
                  new.street, new.city, new.state, new.zipcode);

    import io
    data = io.BytesIO(b'''\
    John,G.,Smith,1972-11-10,123 Any Place,Somewhere,Missouri,58932
    Jane,L.,Jones,1971-12-20,321 Some Place,Anywhere,Kansas,12345

    reader = csv.reader(data)
    for row in reader:
        cur.execute('''insert into
          person_view (firstname, midinit, lastname, birthdate,
                       street, city, state, zipcode)
          values (?,?,?,?,?,?,?,?)''', row)

    # output
    for row in cur.execute('select * from person'):
        print row
    for row in cur.execute('select * from address'):
        print row

person table:

    (1, u'John', u'G.', u'Smith', u'1972-11-10')
    (2, u'Jane', u'L.', u'Jones', u'1971-12-20')

address table:

    (1, 1, u'123 Any Place', u'Somewhere', u'Missouri', u'58932')
    (2, 2, u'321 Some Place', u'Anywhere', u'Kansas', u'12345')

More information about the Tutor mailing list