尝试将csv转换为数据库[Python]时遇到了困难

Question

我正在尝试将csv转换为数据库。我在网上找到了这个代码：

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

但当我运行它时，我会得到以下错误：

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'

有人知道我做错了什么吗？

编辑：

这就是我的csv文件看起来像的样子

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

Answer 1

使用csv.reader

演示：

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

Answer 2

问题是csv.DictReader需要列标题来将值映射到键。您的数据需要有一个标题行，或者您可以手动为DictReader指定字段名。

指定标题的示例：

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]

@Rakesh显示的另一种方法是使用csv.reader，您可以通过列的索引而不是列名来访问列。

按索引访问示例：

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]

或者更简单地转换为包含所有字段的元组：

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