Incorrect number of bindings in Python Sqlite3 executemany

Question

So I have an sqlite3 database in Python where is a table to which I am trying to add 1000 strings. The problem is, when I use executemany command I get an error

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

Here is my code simplified:

db = sqlite3.connect("service.db")
db.isolation_level = None
c = db.cursor()

c.execute("CREATE TABLE Places (id INTEGER PRIMARY KEY, name TEXT)")

toBeAdded = [0]*1000
i = 0
while i < 1000:
    toBeAdded[i] = ("P"+str(i+1))
    i += 1

c.executemany("INSERT INTO Places(name) VALUES (?)",[toBeAdded])

I have also tried different forms of that last command, but no luck. This was the only way I could find on Google to do this.

roganjosh · Accepted Answer · 2020-03-08T19:33:56.417

You have provided a flat list to executemany. Instead, the method expects a nested structure, with each inner sequence representing a set of parameters to be added to the query.

So, you want ['P0', 'P1', 'P2', ...] to be [['P0'], ['P1'], ['P2'], ...]. You can fix that by adding square brackets while creating your list, to make it nested:

toBeAdded = [0]*1000
i = 0
while i < 1000:
    toBeAdded[i] = [("P"+str(i+1))] # Note the surrounding square brackets
    i += 1

Additional feedback

A better way to generate your data would be to use a for loop and get rid of the while loop - you have a pre-determined number of iterations to perform, so it's idiomatic to use a for. You also don't need to initialise the list beforehand.

to_be_added = []
for i in range(1000):
    to_be_added.append([("P"+str(i+1))])

Or, using a list comprehension:

to_be_added = [[("P"+str(x+1))] for x in range(1000)]

You'll note that I've removed the camelCase from the variable name; this is in line with the Python style guide - PEP8

Incorrect number of bindings in Python Sqlite3 executemany

1 Answers1