CODEWITHSUNDEEP
pythondatabasecsv
Dirk Koomen
Dirk Koomen

Reputation: 135

struggle trying to convert a csv into a database [Python]

I'm trying to convert a csv into a database. And I found this code on the internet:

import csv, sqlite3

conn = sqlite3.connect('data.db')
curs = conn.cursor()
curs.execute('''DROP TABLE IF EXISTS data''')
curs.execute('''CREATE TABLE data(col1 INT, col2 TEXT, col3 INT, mar,
            loc, inc, iscr, escr)''')

with open('sample.csv', 'r') as fin:
    reader = csv.DictReader(fin)
    to_db = [(i['col1'], i['col2'], i['col3'], i['col4'], i['col5'], i['col6'], i['col7'], i['col8'], ) for i in reader]

curs.executemany('''INSERT INTO data (col1, col2, col3, col4, col5, col6, col7, col8) 
VALUES (?, ?, ?, ?, ?, ?, ?, ?)''', to_db)
conn.commit
conn.close()

But when I run it, I get the following error:

Traceback (most recent call last):
File "/Users/dirkkoomen/Desktop/Python cursus/inzendopgave 4/opgave 5/xxxxx.py", line 20, in <module>
to_db = [(i['col1'], i['col2'], i['col3'], i['mar'], i['loc'], i['inc'], i['iscr'], i['escr'], ) for i in reader]
  File "/Users/dirkkoomen/Desktop/Python cursus/inzendopgave 4/opgave 5/xxxxx.py", line 20, in <listcomp>
to_db = [(i['col1'], i['col2'], i['col3'], i['mar'], i['loc'], i['inc'], i['iscr'], i['escr'], ) for i in reader]
KeyError: 'col1'

Does anyone know what I'm doing wrong?

EDIT:

this is how my csv file looks like

51,F,46,M,0,15100,531,555
52,M,29,M,2,14200,673,633
53,M,25,S,0,22200,742,998
54,M,36,M,2,1000,677,646
55,F,99,S,0,10600,608,998
56,F,45,M,2,6100,710,743
57,M,99,M,2,16500,679,646
58,F,37,M,0,7400,637,683
59,M,45,S,0,22800,683,998
60,M,22,S,0,6400,699,998
61,M,32,S,0,3100,721,998

Upvotes: 1

Views: 153

Answers (2)

Karl
Karl

Reputation: 1714

The problem is that csv.DictReader needs column headers to map the values to keys. Your data either needs to have a header row, or you can manually specify the field names to the DictReader.

Example specifying the headers:

with open('sample.csv', 'r') as fin:
    headers = ['col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8']
    reader = csv.DictReader(fin, fieldnames=headers)
    to_db = [(line['col1'], line['col2'], line['col3'], line['col4'], line['col5'], line['col6'], line['col7'], line['col8'], ) for line in reader]

Another way as shown by @Rakesh is to use csv.reader which you access the columns by their index instead of a column name.

Example accessing by index:

with open('sample.csv', 'r') as fin:
    reader = csv.reader(fin)
    to_db = [(line[0], line[1], line[2], line[3], line[4], line[5], line[6], line[7]) for line in reader]

Or the simpler conversion to a tuple which will contain all the fields:

with open('sample.csv', 'r') as fin:
    reader = csv.reader(fin)
    to_db = [tuple(line) for line in reader]

Upvotes: 1

Rakesh
Rakesh

Reputation: 82765

Using csv.reader

Demo:

import csv, sqlite3

conn = sqlite3.connect('data.db')
curs = conn.cursor()
curs.execute('''DROP TABLE IF EXISTS data''')
curs.execute('''CREATE TABLE data(col1 INT, col2 TEXT, col3 INT, mar,
            loc, inc, iscr, escr)''')

with open('sample.csv', 'r') as fin:
    reader = csv.reader(fin)                           #Update!!
    to_db = [tuple(line) for line in reader]

curs.executemany('''INSERT INTO data (col1, col2, col3, mar, loc, inc, iscr, escr) VALUES (?, ?, ?, ?, ?, ?, ?, ?)''', to_db)
conn.commit
conn.close()

Upvotes: 2

Related Questions