[Tutor] CSV -> sqlite tables with foreign keys

Hugo Arts hugo.yoshi at gmail.com
Wed Oct 17 08:07:20 CEST 2012


On Wed, Oct 17, 2012 at 5:59 AM, Monte Milanuk <memilanuk at gmail.com> wrote:

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

Well, converting to a list and back is a little superfluous if all you need
to do is add a single element. You could just construct a new tuple like so:

    # now address would look like ("123 Any
Place","Somewhere","Missouri","58932", cursor.lastrowid)
    address_tuple = row[4:] + (cursor.lastrowid,)
    cursor.execute('''INSERT INTO physical_address (street, city, state,
zip_code,person) VALUES (?,?,?,?,?)''', address_tuple)

Note that we put cursor.lastrowid into a 1-element tuple so we can + the
two together. It might look a tad clunky but it's pretty easy to read.

Hugo
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/tutor/attachments/20121017/b56f954e/attachment.html>


More information about the Tutor mailing list