Python, convert CSV file to SQL table

Question

I have a CSV file without headers, and I'm trying to create an SQL table from specific columns in a file. I tried the solutions given here: Import a CSV file into a sqlite3 database table using Python , but keep getting an error that is not defined. Then I tried pasting the headers into my CSV file and still get a KeyError.

Any help is appreciated! (I'm generally not very familiar with SQL)

Answer 1

If there are no headers in the .csv file, you do not want to use DictReader; DictReader assumes line 1 is a collection of headers and uses them as keys for each subsequent line. This is probably why you get KeyError s.

A modified version of the example from this link:

 import csv, sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() cur.execute("CREATE TABLE t (col1, col2);") with open('data.csv','rb') as fin: dr = csv.reader(fin) dicts = ({'col1': line[0], 'col2': line[1]} for line in dr) to_db = ((i['col1'], i['col2']) for i in dicts) cur.executemany("INSERT INTO t (col1, col2) VALUES (?, ?);", to_db) con.commit() 

Answer 2

This code below will read all the csv files from the path and load all the data into the table contained in the sqllite 3 database.

  import sqllite3 import io import os.path import glob cnx = sqlite3.connect(user='user', host='localhost', password='password', database='dbname') cursor=cnx.cursor(buffered= True); path ='path/*/csv' for files in glob.glob(path + "/*.csv"): add_csv_file="""LOAD DATA LOCAL INFILE '%s' INTO TABLE tabkename FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES;;;""" %(files) print ("add_csv_file: %s" % files) cursor.execute(add_csv_file) cnx.commit() cursor.close(); cnx.close(); 

Let me know if this works.