将CSV导入sqlite表python [英] csv into sqlite table python

问题描述

我正在尝试使用python将csv导入sqlite表中，并使用csv文件中的标头成为sqlite表中的标头.该代码运行，但表"MyTable"似乎未创建.这是代码:

Using python, I am trying to import a csv into an sqlite table and use the headers in the csv file to become the headers in the sqlite table. The code runs but the table "MyTable" does not appear to be created. Here is the code:

with open ('dict_output.csv', 'r') as f:
    reader = csv.reader(f)
    columns = next(reader)

#Strips white space in header
    columns = [h.strip() for h in columns]


#reader = csv.DictReader(f, fieldnames=columns)
    for row in reader:
        print(row)

    con = sqlite3.connect("city_spec.db")
    cursor = con.cursor()

#Inserts data from csv into table in sql database.
    query = 'insert into MyTable({0}) values ({1})'
    query = query.format(','.join(columns), ','.join('?' * len(columns)))
    print(query)
    cursor = con.cursor()
    for row in reader:
        cursor.execute(query, row)
#cursor.commit()

    con.commit()

    con.close()

在此先感谢您的帮助.

推荐答案

您可以使用Pandas简化此操作(您可能需要先pip install pandas):

You can use Pandas to make this easy (you may need to pip install pandas first):

import sqlite3
import pandas as pd

# load data
df = pd.read_csv('dict_output.csv')

# strip whitespace from headers
df.columns = df.columns.str.strip()

con = sqlite3.connect("city_spec.db")

# drop data into database
df.to_sql("MyTable", con)

con.close()

熊猫会为您完成所有艰苦的工作，包括创建实际的表格！

Pandas will do all of the hard work for you, including create the actual table!

这篇关于将CSV导入sqlite表python的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！