On Wed, Oct 17, 2012 at 5:59 AM, Monte Milanuk <memila...@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
_______________________________________________ Tutor maillist - Tutor@python.org To unsubscribe or change subscription options: http://mail.python.org/mailman/listinfo/tutor