Dynamic INSERT Statement in Python
Question: I am working on updating some Python code I have been working with and was
looking for some advice on the best way to deal with an idea I am working
with. The part of my code that I wish to change is :

    
    
    my_reader = csv.reader(input, delimiter = ',',quotechar='|')
    mouse.executemany("INSERT INTO Example_Input (ID,Name,Job,Salary) VALUES (?,?,?,?)", my_reader)
    

The code works. My question is, can I change the "(?,?,?,?)" into something
more dynamic like 'range()' to allow user input. I understand that I would
also have to have a dynamic create table statement, so another solution might
be to count the number of inputs.

To be a little more clear: for example if I had raw_input("How many variables
does the table contain?: ") and the input was 2, the program would know to run
as if (?,?).

Thoughts?

(also I am using SQLite3 and python 2.7)


Answer: Assuming your csv had a header field, you could use a
[DictReader](http://docs.python.org/2/library/csv.html#csv.DictReader) and
generate the field names and parameters its
[fieldnames](http://docs.python.org/2/library/csv.html#csv.csvreader.fieldnames)
property.

The constructor for
[DictReader](http://docs.python.org/2/library/csv.html#csv.DictReader) allows
you to specify the fieldnames if they are not in the file, so you could ask
the user for that information if required.

Assuming the headers are in the file, this example code should work:

    
    
    import csv
    import sqlite3
    
    #Give the table a name and use it for the file as well
    table_name = 'Example'
    a = open(table_name + '.csv', 'r')
    
    #Use a dict reader
    my_reader = csv.DictReader(a)
    print my_reader.fieldnames # Use this to create table and get number of field values,etc.
    
    # create statement
    create_sql = 'CREATE TABLE ' + table_name + '(' + ','.join(my_reader.fieldnames) + ')'
    print create_sql
    
    #open the db
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    # Create table using field names
    c.execute(create_sql)
    insert_sql = 'insert into ' + table_name + ' (' + ','.join(my_reader.fieldnames) + ') VALUES (' + ','.join(['?'] * len(my_reader.fieldnames))+ ')'
    print insert_sql
    
    values = []
    for row in my_reader:
        row_values = []
        for field in my_reader.fieldnames:
            row_values.append(row[field])
        values.append(row_values)
    
    c.executemany(insert_sql, values)
    

