Dynamic INSERT Statement in Python

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 csvimport sqlite3#Give the table a name and use it for the file as welltable_name = 'Example'a = open(table_name + '.csv', 'r')#Use a dict readermy_reader = csv.DictReader(a)print my_reader.fieldnames # Use this to create table and get number of field values,etc.# create statementcreate_sql = 'CREATE TABLE ' + table_name + '(' + ','.join(my_reader.fieldnames) + ')'print create_sql#open the dbconn = sqlite3.connect('example.db')c = conn.cursor()# Create table using field namesc.execute(create_sql)insert_sql = 'insert into ' + table_name + ' (' + ','.join(my_reader.fieldnames) + ') VALUES (' + ','.join(['?'] * len(my_reader.fieldnames))+ ')'print insert_sqlvalues = []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)

On python3, using list comprehensions and dictionaries I came up with the following simple code that is capable of building a Dynamic SQLite insert string based on a given dictionary:

# Data to be inserted:data = [  {    'table': 'customers',    'values': {      'name': '"Doctor Who"',      'email': '"doctorwho@timelords.com"'    }  },  {    'table': 'orders',    'values': {      'customer_id': '1',      'item': '"Sonic Screwdriver"',      'price': '1000.00'    }  }]def generate_insert_query(dictionary):  table = dictionary["table"]  # Get name of the table  # Get all "keys" inside "values" key of dictionary (column names)  columns = ', '.join(dictionary["values"].keys())    # Get all "values" inside "values" key of dictionary (insert values)  values = ', '.join(dictionary["values"].values())  # Generate INSERT query  print(f"INSERT INTO {table} ({columns}) VALUES ({values})" + "\n")# Generate QUERY for each dictionary inside data listfor query in data:  generate_insert_query(query)

Try the code on: https://repl.it/KNZg/2

I recently wrote a function to quickly populate a table based on table name and a list of tuples with the row data that I wanted to insert. I am using the tuple to identify how many columns are expected:

def populate_table(table_name, data):    #Grab first tuple and find out how many columns are expected to be edited    num_columns = len(data[0])    try:        cursor.executemany("INSERT INTO {} VALUES({})".format(table_name, ','.join(['?' for s in xrange(num_columns)]), data)        conn.commit()    except sqlite3.OperationalError:        ...