python处理csv文件 sql,Python，将CSV文件转换为SQL表

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)

解决方案

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()