importing csv file to sqlite tables

Question

I am trying to import my csv file to my table. I followed similar steps found here however I am getting an error :

test.csv

"a",1,1
"b",2,2
"c",3,3

Error:

Traceback (most recent call last):
  File "C:/Users/Sam/Desktop/big data/workspace/yelpdatabase.py", line 19, in <module>
    to_db = [(i['letter'], i['num1'], i['num2']) for i in dr]
  File "C:/Users/Sam/Desktop/big data/workspace/yelpdatabase.py", line 19, in <listcomp>
    to_db = [(i['letter'], i['num1'], i['num2']) for i in dr]
KeyError: 'letter'

code:

import csv
import sqlite3

#Create The Database
connection = sqlite3.connect('test.db')

#create cursor
cursor = connection.cursor()

#Create table
cursor.execute('DROP TABLE IF EXISTS testtable')
cursor.execute('CREATE TABLE testtable ( letter text, num1 int, num2, int)')

#Load the CSV file into CSV reader
path = r"C:\Users\Sam\Desktop\big data\workspace\test.csv"
with open(path,'r') as fin: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(fin)  # comma is default delimiter
    to_db = [(i['letter'], i['num1'], i['num2']) for i in dr]

cursor.executemany("INSERT INTO t (letter, num1, num2) VALUES (?, ?, ?);", to_db)

#commit changes, and close the connection
connection.commit()
connection.close()

EDIT: I uploaded the test.csv file

Please, add the head of the CSV you are importing. – Javier Dec 13 '16 at 02:45 — Javier, Dec 13 '16 at 02:45

iUknwn · Accepted Answer · 2016-12-13T04:34:54.720

Without looking at the CSV, it's hard to know for certain what's wrong, but the "KeyError" indicates that the dictionary generated by DictReader didn't have a key named "letter".

Double check that the first row of your CSV has 'letter'. For example, if my CSV looked like this:

head1,head2,head3

1,2,3

Then the correct line to extract the data would be:

to_db = [(i["head1"], i["head2"], i["head3"]) for i in dr]

Note - keys are case sensitive, so in the example above, "head1" is valid but "Head1" would generate a KeyError.

Edit: Thanks for posting the CSV. The issue appears to be that you don't have a header row, so the DictReader is getting field names ["a","1","1"] (you can check this by calling dr.fieldnames).

To make the code work without modifying the CSV, you can change your DictReader line to:

csv.DictReader(fin, ["letter", "num1", "num2"])

This will manually assign the headers "letter", "num1", and "num2" to your three columns. Then you can do:

to_db = [(i["letter"], i["num1"], i["num2"]) for i in dr]

importing csv file to sqlite tables

1 Answers1