0

I'm working on building a database from several excel spreadsheets. I've extracted the excel data into lists of column names and row values and want to build functions to automate the process of inserting the data into a sqlite3 database using python. I'm trying to insert row values into existing tables and columns, but am running into problems. I'd like to be able to insert the names and values as variables to help automate the process, but can't find a way to use variables to specify the names and values at the same time. Here's what I have so far:

#setting up the code
import sqlite3
conn = sqlite3.connect(databaseName)
c=conn.cursor()
tableName = 'exampleTable'
columnName = 'exampleColumn'
rowValue = 123456

Assuming tables and columns have already been created, let's move onto inserting the row values. So I know you can insert values in these two ways:

c.execute("INSERT OR IGNORE INTO {tn} ({cn}) VALUES (123456)" .format(tn=tableName, cn=columnName))

or

c.execute("INSERT OR IGNORE INTO exampleTable (exampleColumn) VALUES (?)" , rowValue)

Does anyone know a way to combine these two methods?

If I try:

c.execute("INSERT OR IGNORE INTO {tn} ({cn}) VALUES ({rv})".format(
    tn=tableName, cn=columnName, rv=rowValue))

I get an error that reads:

sqlite3.OperationalError: no such column: 123456 #(rowValue)

Alternatively, if I try:

c.execute("INSERT OR IGNORE INTO ? (?) VALUES (?)", 
          tableName, columnName, rowValue)

I get the error:

TypeError: function takes at most 2 arguments (4 given)

And trying to combine the two methods:

c.execute(
    "INSERT OR IGNORE INTO {tn} ({cn}) VALUES (?)".format(
        tn=tableName, cn=columnName),
    rowValue)

gives the error:

sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.

Does anyone have a solution for this?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Vivian
  • 9
  • 1
  • Your last statement is exactly correct, but you created a *new* problem for yourself. That argument must be a tuple or list; use `(rowValue,)` or `[rowValue]`. See the duplicate post. – Martijn Pieters Jun 25 '15 at 17:42
  • Also see [sqlite3.OperationalError: near "?": syntax error](https://stackoverflow.com/q/25387537) for another example where I use both `str.format()` and SQL placeholders. – Martijn Pieters Jun 25 '15 at 17:51

0 Answers0