在 Python 中使用 SQLite 将 .csv 文件导入 SQL 数据库 [英] Import .csv files into SQL database using SQLite in Python

问题描述

我有 2 个 .txt 文件，我使用 https://convertio 将它们转换为 .csv 文件.co/csv-xlsx/.现在，我想使用 Python 中的 SQLite(UI 是 Jupyter Notebook)将这两个 .csv 文件导入到两个数据库中.这两个 .csv 文件标记为 person.csv 和 person_votes.csv.所以，我按照这里给出的代码(使用 Python 将 CSV 文件导入 sqlite3 数据库表):

I have 2 .txt files, and I converted them into .csv files using https://convertio.co/csv-xlsx/. Now, I would like to import these two .csv files into two databases using SQLite in Python (UI is Jupyter Notebook). These two .csv files are labeled person.csv and person_votes.csv. So, I did it by following the code given here (Importing a CSV file into a sqlite3 database table using Python):

import sqlite3, csv

con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.execute("CREATE TABLE person (personid STR,age STR,sex STR,primary_voting_address_id STR,state_code STR,state_fips STR,county_name STR,county_fips STR,city STR,zipcode STR, zip4 STR,  PRIMARY KEY(personid))") 

with open('person.csv','r') as person_table: # `with` statement available in 2.5+
    # csv.DictReader uses first line in file for column headings by default
    dr = csv.DictReader(person_table) # comma is default delimiter
#personid   age sex primary_voting_address_id   state_code  state_fips  county_name county_fips city    zipcode zip4
    to_db = [(i['personid'], i['age'], i['sex'], i['primary_voting_address_id'], i['state_code'], i['state_flips'], i['county_name'], i['county_fips'], i['city'], i['zipcode'], i['zip4']) for i in dr]

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

我不明白为什么当我尝试执行上面的代码时，我不断收到错误消息:KeyError: 'personid'".有人可以帮忙吗?

I don't understand why when I tried executing the code above, I keep getting the error message: "KeyError: 'personid'". Could someone please help?

此外，如果我在同一个 Python 文件中为文件 person_votes.csv 创建另一个名为 to_db2 的数据库表，下面的查询是否会给我所有的 两个表之间的公共元素:

Also, if I create another database table named to_db2 for the file person_votes.csv in the same Python file, would the following query give me all the common elements between two tables:

select ID from to_db, to_db2 WHERE to_db.ID ==  to_db2

上面两个 .csv 文件的链接在这里:https://drive.google.com/open?id=0B-cyvC6eCsyCQThUeEtGcWdBbXc.

The link to the two .csv files above is here: https://drive.google.com/open?id=0B-cyvC6eCsyCQThUeEtGcWdBbXc.

推荐答案

这对我在 Windows 10 上有效，但在 Linux/Unix 下也应该有效.有几个问题:

This works for me on Windows 10, but should work under Linux/Unix too. There are several problems:

1. person.csv 的最后两行格式不正确，但这并不妨碍程序运行.您可以使用文本编辑器解决此问题.
2. person.csv 使用制表符作为分隔符而不是逗号.
3. 在以to_db ="开头的行中有一个错字(拼写)
4. 要导入的列数不匹配(2 而不是 11)
5. executemany 上的表名错误.

此外，我在文件中而不是在内存中创建数据库.它足够小，性能应该不是问题，而且您所做的任何更改都将被保存.

In addition, I create the database in a file rather than in memory. It is small enough that performance should not be a problem and also any changes you make will be saved.

这是我更正后的文件(你可以自己做另一个表):

Here is my corrected file (you can do the other table yourself):

import sqlite3, csv

# con = sqlite3.connect(":memory:")
con = sqlite3.connect("person.db")
cur = con.cursor()
cur.execute("CREATE TABLE person (personid STR,age STR,sex STR,primary_voting_address_id STR,state_code STR,state_fips STR,county_name STR,county_fips STR,city STR,zipcode STR, zip4 STR,  PRIMARY KEY(personid))") 

with open('person.csv','r') as person_table:
    dr = csv.DictReader(person_table, delimiter='\t') # comma is default delimiter
    to_db = [(i['personid'], i['age'], i['sex'], i['primary_voting_address_id'], i['state_code'], i['state_fips'], i['county_name'], i['county_fips'], i['city'], i['zipcode'], i['zip4']) for i in dr]

cur.executemany("INSERT INTO person VALUES (?,?,?,?,?,?,?,?,?,?,?);", to_db)
con.commit()

这篇关于在 Python 中使用 SQLite 将 .csv 文件导入 SQL 数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！