#!/usr/bin/env python
# convert-csv-to-sql-inserts -- SQL INSERT statement generator from CSV data
# > convert-csv-to-sql-inserts  CVSFILE  INSERT_STMT  ID_PREFIX  SQL_TYPE...
# where SQL_TYPE is either TEXT or NUM,
#            and can optionally have a suffix .SANITIZED
#
# Author: Jaeho Shin <netj@cs.stanford.edu>
# Created: 2014-01-28
import sys, re
import csv

BATCH_SIZE = 256

def formatString(v):
    if v is not None:
        return "'%s'" % re.sub(r"'", r"''", v)
    else:
        return "NULL"
def formatNumber(v):
    try:
        return str(float(str(v)))
    except ValueError:
        return "NULL"
formatterByType = {
    "TEXT": formatString,
    "NUM" : formatNumber,
}
def sanitize(v):
    if v is not None:
        return re.sub(r"[^A-Za-z0-9@%:.+_-]", r"_",
                re.sub(r"_", r"__", str(v)))
    else:
        return v

csvfile = sys.argv[1]
sql_insert_stmt = sys.argv[2]
row_id_prefix = sys.argv[3]
typeArgs = []
optionArgs = []
for arg in sys.argv[4:] + ["TEXT"]: # additional TEXT is for runId field
    try:
        [typeArg, option] = arg.split(".", 1)
    except ValueError:
        typeArg = arg
        option = None
    typeArgs.append(typeArg)
    optionArgs.append(option)

formatter = [formatterByType[ty] for ty in typeArgs]
for i, fmt in enumerate(formatter):
    if optionArgs[i] == "SANITIZED":
        formatter[i] = lambda v: fmt(sanitize(v))

with open(csvfile, "rb") as csvfile:
    # take a sample of the CSV file, and guess some things
    sample = csvfile.read(1024)
    csvfile.seek(0)
    csvsniffer = csv.Sniffer()
    dialect = csvsniffer.sniff(sample)
    has_header = csvsniffer.has_header(sample)
    # read each row of the CSV data
    csvdata = csv.reader(csvfile, dialect)
    first = True
    row_num = 1
    for row in csvdata:
        if has_header:
            has_header = False
            continue
        if first:
            print sql_insert_stmt
            first = False
        else:
            if row_num % BATCH_SIZE == 0:
                print ";"
                print sql_insert_stmt
            else:
                print ","
        row += [row_id_prefix + str(row_num)]
        formatted = (fmt(row[i]) if i < len(row) else "NULL"
                for i, fmt in enumerate(formatter))
        print ("(%s)" % (", ".join(formatted))),
        row_num += 1
    print ";"
