CSV File to SQL Insert Statement

Question

I have a CSV file that looks something like this:

Date,Person,Time Out,Time Back,Restaurant,Calories,Delicious?
6/20/2016,August,11:58,12:45,Black Bear,850,Y
6/20/2016,Marcellus,12:00,12:30,Brought Lunch,,Y
6/20/2016,Jessica,11:30,12:30,Wendy's,815,N
6/21/2016,August,12:05,1:01,Brought Lunch,,Y

So far I have managed to print each row into a list of strings (ex. - ['Date', 'Person', 'Time Out', etc.] or ['6/20/2016', 'August', '11:58' etc.]).

Now I need to do 2 more things:

1. Add an ID header and sequential numeric string to each row (for ex. - ['ID', 'Date', 'Person', etc.] and ['1', '6/20/2016', 'August', etc.])
2. Separate each row so that they can be formatted into insert statements rather than just having the program print out every single row one after another (for ex. - INSERT INTO Table ['ID', 'Date', 'Person', etc.] VALUES ['1', '6/20/2016', 'August', etc.])

Here is the code that has gotten me as far as I am now:

import csv

openFile = open('test.csv', 'r')
csvFile = csv.reader(openFile)
for row in csvFile:
    print (row)
openFile.close()

Answer 1

Try this (I ignored the ID part since you can use the mySQL auto_increment)

import csv

openFile = open('test.csv', 'r')
csvFile = csv.reader(openFile)
header = next(csvFile)
headers = map((lambda x: '`'+x+'`'), header)
insert = 'INSERT INTO Table (' + ", ".join(headers) + ") VALUES "
for row in csvFile:
    values = map((lambda x: '"'+x+'"'), row)
    print (insert +"("+ ", ".join(values) +");" )
openFile.close()

Answer 2

You can use this functions if you want to mantain type conversion, i have used it to put data into google big query with a string sql statement.

PS: You can put other types on the function

import csv

def convert(value):
    for type in [int, float]:
        try:
            return type(value)
        except ValueError:
            continue
    # All other types failed it is a string
    return value


def construct_string_sql(file_path, table_name, schema_name):
    string_SQL = ''
    try:
        with open(file_path, 'r') as file:            
            reader = csv.reader(file)
            headers = ','.join(next(reader))
            for row in reader:
                row = [convert(x) for x in row].__str__()[1:-1]
                string_SQL += f'INSERT INTO {schema_name}.{table_name}({headers}) VALUES ({row});'
    except:
        return ''

    return string_SQL