On Wed, Oct 17, 2012 at 5:59 AM, Monte Milanuk <span dir="ltr"><<a href="mailto:memilanuk@gmail.com" target="_blank">memilanuk@gmail.com</a>></span> wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hello,<br><br>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.<br>The sticking point seems to be creating the foreign keys between the tables<br><br>I've got a data file with lines like this:<br><br>"John","G.","Smith","1972-11-10","123 Any Place","Somewhere","Missouri","58932"<br>
<br>I have an SQL script set up to create the tables with the appropriate fields.<br><br>What I have so far that works is something like this:<br><br>try:<br> data = open(CSV_FILE, 'rb')<br> reader = csv.reader(data)<br>
for row in reader:<br> cursor.execute('''INSERT INTO people (first_name, mid_init, last_name, birth_date)<br> VALUES (?,?,?,?)''', row[0:4])<br> <br> person = cursor.lastrowid<br>
address = list(row)<br> address.append(person)<br> row = tuple(address)<br> <br> cursor.execute('''INSERT INTO physical_address (street, city, state, zip_code,person)<br> VALUES (?,?,?,?,?)''', row[4:])<br>
finally:<br> data.close()<br><br><br>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.<br>
<br>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.<br><br>Any suggestions?<br>
<br><br>Thanks,<br><br>Monte<br></blockquote><div><br></div><div>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:</div>
<div><br></div><div> # now address would look like ("123 Any Place","Somewhere","Missouri","58932", cursor.lastrowid)</div><div> address_tuple = row[4:] + (cursor.lastrowid,)</div>
<div> cursor.execute('''INSERT INTO physical_address (street, city, state, zip_code,person) VALUES (?,?,?,?,?)''', address_tuple)</div><div><br></div><div>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.</div>
<div><br></div><div>Hugo</div><div><br></div></div>