I can't get Python's executemany for sqlite3 to work properly

Question

I was trying to use executemany to insert values into a database, but it just won't work for me. Here is a sample:

clist = []
clist.append("abc")
clist.append("def")
clist.append("ghi")
cursor.executemany("INSERT INTO myTable(data) values (?) ", clist)

This gives me the following error:

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

However, when I change the list, it works fine:

clist = ["a", "b"]
cursor.executemany("INSERT INTO myTable(data) values (?) ", clist)

It works as expected! I can see the data in the database. Why does the first list not work and second one does ?

(PS: This is just a sample and not the actual code. I made a small test case for simplicity).

Chercherbourg

Answer 1

From what I know of executemany, you meant,

clist = [("abc", ), ("def", ), ("ghi", )]
cursor.executemany("INSERT INTO myTable(data) values(?)", clist)

Or something similar. Don't quote me on the syntax for sqlite, I haven't used it in an app in a while, but you need an iterable of tuples (more generally iterables).

It looks like the error you're getting is that it's trying to iterate through each string you're providing, so your statement works like:

clist = [('a', 'b', 'c'), ('d', 'e', 'f'), ('g', 'h', 'i')]

I don't know what your second query is trying to accomplish, but it appears to address a different table, so I'm guessing off of no schema info, but if you change the single character strings to multicharacter strings, it will fail too.

Saffian

They both were same tables. My bad for the typo. Fixed it in the question now. Its a simple table with one field in it. – Chercherbourg

Can you please post that comment here again, about the second clist. I think, the person who had posted his answer, deleted it and its gone with that. – Chercherbourg

I don't know what exactly you're asking. Strings are iterable in python, so a string acts a lot like a tuple of characters. The execute call (and by extension, executemany) expects an iterable of parameters, for example, (1, 2, 3, 'abc') or range(3) or any object that supports iteration. So, since strings support iteration, they work as a collection of parameters, too. Your execute statement is expecting an iterable with one item, which is fulfilled by the second clist (a list of strings with one character) but not fulfilled by the first clist (a list of strings with 3 characters). – Saffian

After executemany, conn.commit() is necessary to update the table. – Lucienlucienne

Answer 2

Just to complement the context: In a closely related situation I meant to insert a list of poly-tuples into a table using executemany as such:

res = [("John", "2j4o1h2n"), ("Paula", "lsohvoeemsy"), ("Ben", "l8ers")]

cur.executemany("INSERT INTO users (user, password) VALUES (?)", res)

Expecting SQLite to to take one tuple at a time (hence the single ? parameter-substitution in the VALUES field) and splitting it up into its encapsulated attributes (<username>, <password> in this case), it failed with a sqlite3.ProgrammingError exception The current statement uses 1, and there are 2 supplied. as well, as SQLite expects separately substituted attributes in the VALUES (...) field. So this fixes it:

cur.executemany("INSERT INTO users (user, password) VALUES (?, ?)", res)

This is a trivial case but might confuse a little, I hope it might help whoever's stuck.

Dogtired