This is my list of tuples, called "searchterms"
[('2014', '100908'), ('2014', '101049'), ('2014', '101007'), ('2014', '100881'), ('2014', '100866')]`
Here is the code I am trying to use to insert these tuples into an sqlite database with only two columns named caseYR and caseNUM
conn = sqlite3.connect('searchdata.db')
cur = conn.cursor()
cur.execute('''CREATE TABLE IF NOT EXISTS Cases (caseYR TEXT, caseNUM TEXT)''')
cur.executemany("INSERT INTO Cases(caseYR, caseNUM) VALUES (?,?)", ("searchterms"))
conn.commit()
I have seen this question on StackOverFlow and numerous places online and the suggested solutions do not work for some reason. Persistently getting this error:
cur.executemany("INSERT INTO Cases(caseYR, caseNUM) VALUES (?,?)", ("searchterms"))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 2, and there are 1 supplied.
Please note I have seen I think all the similar questions on StackOverFlow and tried those options and obtained no success. Any new ideas?
cursor.executemany expects two parameters. The first parameter is the query. The second parameter should be an iterable of iterables of size equal to the number of bindings in the query. There are two ? in the query, so that means there are two bindings. searchterms is an iterable (list) of iterables (tuples) of size 2, so it is a valid second parameter for cursor.executemany.
Originally, the second parameter was ("searchterms") which failed because the string "searchterms" doesn't refer to the variable searchterms. Instead it was intepreted as an iterable (string) and since a string only has one character in each index, it gave the unhelpful error about supplying only 1 binding.
When you changed the second parameter to searchterms, it seems your searchterms variable was not a list of tuples, but rather a list of strings, which is why you got the error message stating you supplied 4 bindings. The string '2014' must've been the first item and has 4 characters in it.
The fix is to make sure that the second parameter you pass to cursor.executemany is indeed an iterable of iterables of size 2. Then MiltoxBeyond's suggestion will work.
I ran this and it worked:
import sqlite3
searchterms = [('2014', '100908'), ('2014', '101049'), ('2014', '101007'), ('2014', '100881'), ('2014', '100866')]
conn = sqlite3.connect('searchdata.db')
cur = conn.cursor()
cur.execute('''CREATE TABLE IF NOT EXISTS Cases (caseYR TEXT, caseNUM TEXT)''')
cur.executemany("INSERT INTO Cases(caseYR, caseNUM) VALUES (?,?)", searchterms)
conn.commit()