[Tutor] CSV -> sqlite tables with foreign keys

[eryksun]

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

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

https://storm.canonical.com/

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

http://stackoverflow.com/a/11715983/205580

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
      select
        person.firstname, person.midinit, person.lastname,
        person.birthdate, address.street, address.city,
        address.state, address.zipcode
      from
        person inner join address on person.id = address.person_id
    ''')

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

    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')