[Tutor] CSV -> sqlite tables with foreign keys
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:
> 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
> I have an SQL script set up to create the tables with the appropriate
> What I have so far that works is something like this:
> 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)
> row = tuple(address)
> cursor.execute('''INSERT INTO physical_address (street, city,
> state, zip_code,person)
> VALUES (?,?,?,?,?)''', row[4:])
> 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?
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
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.
-------------- next part --------------
An HTML attachment was scrubbed...
More information about the Tutor