Skip to content

Instantly share code, notes, and snippets.

@ngopal
Last active December 17, 2019 04:57
Show Gist options
  • Save ngopal/9659c8313789cf461a1ec70df809ee81 to your computer and use it in GitHub Desktop.
Save ngopal/9659c8313789cf461a1ec70df809ee81 to your computer and use it in GitHub Desktop.
quick and dirty script to convert csv to sqlite
import csv, sqlite3, sys
file_to_read = sys.argv[1]
db_name = sys.argv[2]
table_name = sys.argv[3]
manifest = sys.argv[4] # lists header names and data types
con = sqlite3.connect("./"+db_name)
cur = con.cursor()
# Read Manifest
# Make create table query using manifest
# manifest_headers = ["hood", "squarefootage", "bedrooms", "bathrooms", "price"]
# manifest_header_types = ["TEXT", "INTEGER", "INTEGER", "INTEGER", "INTEGER"]
manifest_file = open(manifest, 'r')
manifest_headers = []
manifest_header_types = []
for l in manifest_file.readlines():
l = l.split(',')
manifest_headers.append(l[0].lstrip().strip())
manifest_header_types.append(l[1].lstrip().strip())
print(manifest_headers)
print(manifest_header_types)
sys.exit()
command = ' ('
for k in zip(manifest_headers, manifest_header_types):
command += k[0]+' '+k[1]+', '
command = command[:-2] + ');'
print(command)
cur.execute("CREATE TABLE "+table_name+command) # use your column names here
with open(file_to_read,'r') as fin: # `with` statement available in 2.5+
# csv.DictReader uses first line in file for column headings by default
print(fin)
dr = csv.DictReader(fin, delimiter=';') # comma is default delimiter
print(dr)
# to_db = [tuple([m for m in manifest_headers]) for i in dr]
to_db = [tuple(i.values()) for i in dr]
print(to_db)
insert_step = "INSERT INTO "+table_name+" ("+', '.join(manifest_headers)+") VALUES ("+', '.join(["?" for i in manifest_headers])+");"
print(insert_step)
cur.executemany(insert_step, to_db)
con.commit()
con.close()
hood, TEXT
squarefootage, INTEGER
bedrooms, INTEGER
bathrooms, INTEGER
price, INTEGER
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment