Python, converting CSV file to SQL table

Question

I have a CSV file without headers and am trying to create a SQL table from certain columns in the file. I tried the solutions given here: Importing a CSV file into a sqlite3 database table using Python, but keep getting the error that col1 is not defined. I then tried inserting headers in my CSV file and am still getting a KeyError.

Any help is appreciated! (I am not very familiar with SQL at all)

Answer 1

If the .csv file has no headers, you don't want to use DictReader; DictReader assumes line 1 is a set of headers and uses them as keys for every subsequent line. This is probably why you're getting KeyErrors.

A modified version of the example from that link:

import csv, sqlite3

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE t (col1, col2);")

with open('data.csv','rb') as fin:
    dr = csv.reader(fin)
    dicts = ({'col1': line[0], 'col2': line[1]} for line in dr)
    to_db = ((i['col1'], i['col2']) for i in dicts)

cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db)
con.commit()