1

I have a sqlite-database table with 3 three columns (X,Y,Z). Additionally I have a csv file (with header) having 10 columns but also the same X and Y. I'd like to join the the csv file to the database table using a python script.

So far I managed to establish the db-connection and to create the new columns in the database like:

import csv, sqlite3
database = sqlite3.connect("my_sqlitedatabase")
db = database.cursor()
db.execute("ALTER TABLE dbtable ADD COLUMN 'col1' float")
db.execute("ALTER TABLE dbtable ADD COLUMN 'col2' float")
db.execute("ALTER TABLE dbtable ADD COLUMN 'col3' float")

So far I haven't found anything to use .import in python instead I probably need to read in the csv file directly (see Importing a CSV file into a sqlite3 database table using Python)

csvtable = csv.reader(open('csvtable.txt', 'r'), delimiter=',')

For joining I'd like to use something like:

UPDATE dbtable SET csvtable_col1=(SELECT dbtable.col1 FROM csvtable AS c 
... WHERE csvtable.X=dbtable.X AND csvtable.Y=dbtable.Y)

How can I join the csv table to my database table using pyhton? The both share X and Y as common columns and a combination of both can be used as unique key for joining. Both tables have several 100000 rows. Do I need to loop over the rows of csv file? Any suggestions?

Community
  • 1
  • 1
Johannes
  • 1,024
  • 13
  • 32
  • 1
    You'll need to create a table in your SQLite3 database for the CVS data and insert the rows from the file there. Then you'll be able to perform queries joining the two tables. – Pedro Romano Oct 23 '12 at 09:40
  • Yes either you can loop over rows of csv in python and insert. Or you can follow @PedroRomano 's suggestion. You probably can't do it the way you want. – Himanshu Oct 23 '12 at 11:04
  • I think I'll go for importing the data from csv to sqlite and then joining them afterwards... Thanks – Johannes Oct 23 '12 at 11:09

0 Answers0