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)

Solution 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)

Solution 2

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': '"[email protected]"'
    }
  },
  {
    '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 list
for query in data:
  generate_insert_query(query)

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

Comments

[TheGoblinPopper]

I do know that some of the csv files do not have headers that contain the variable names... however this is definitely something I would consider and just state in the 'raw_input()' that the program will assume the document contains those headers.