将csv导入python_将CSV导入sqlite表python

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

Thanks in advance for any help.

解决方案

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!