csv into sqlite table python

Question

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.

Answer 1

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!

Answer 2

You haven't marked your answer solved yet so here goes.

Connect to the database just once, and create a cursor just once. You can read the csv records only once. I've added code that creates a crude form of the database table based on the column names alone. Again, this is done just once in the loop. Your insertion code works fine.

import sqlite3
import csv

con = sqlite3.connect("city_spec.sqlite") ## these statements belong outside the loop
cursor = con.cursor()  ## execute them just once

first = True
with open ('dict_output.csv', 'r') as f:
    reader = csv.reader(f)
    columns = next(reader)
    columns = [h.strip() for h in columns]
    if first:
        sql = 'CREATE TABLE IF NOT EXISTS MyTable (%s)' % ', '.join(['%s text'%column for column in columns])
        print (sql)
        cursor.execute(sql)
        first = False
    #~ for row in reader:    ## we will read the rows later in the loop
        #~ print(row)

    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)
    con.commit()
    con.close()

Answer 3

You can also do it easy with peewee orm. For this you only use an extension from peewee, the playhouse.csv_loader:

from playhouse.csv_loader import *

db = SqliteDatabase('city_spec.db')

Test = load_csv(db, 'dict_output.csv')

You created the database city_spec.db with the headers as fields and the data from the dict_output.csv

If you don't have peewee you can install it with

pip install peewee