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 1

Assuming your csv had a header field, you could use a DictReader and generate the field names and parameters its fieldnames property.

The constructor for 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)