sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 74 supplied
PythonSqlitePython Problem Overview
def insert(array):
connection=sqlite3.connect('images.db')
cursor=connection.cursor()
cnt=0
while cnt != len(array):
img = array[cnt]
print(array[cnt])
cursor.execute('INSERT INTO images VALUES(?)', (img))
cnt+= 1
connection.commit()
connection.close()
I cannot figure out why this is giving me the error, The actual string I am trying to insert is 74 chars long, it's: "/gifs/epic-fail-photos-there-i-fixed-it-aww-man-the-tire-pressures-low.gif"
I've tried to str(array[cnt]) before inserting it, but the same issue is happening, the database only has one column, which is a TEXT value.
I've been at it for hours and I cannot figure out what is going on.
Python Solutions
Solution 1 - Python
You need to pass in a sequence, but you forgot the comma to make your parameters a tuple:
cursor.execute('INSERT INTO images VALUES(?)', (img,))
Without the comma, (img)
is just a grouped expression, not a tuple, and thus the img
string is treated as the input sequence. If that string is 74 characters long, then Python sees that as 74 separate bind values, each one character long.
>>> len(img)
74
>>> len((img,))
1
If you find it easier to read, you can also use a list literal:
cursor.execute('INSERT INTO images VALUES(?)', [img])
Solution 2 - Python
You get confused by the fact that you are dealing with a single column data only but the syntax is compatible to deal with several columns at once, hence the reason to cast your string into a, i.e., tuple.
You could fix it with zip
: cursor.execute('INSERT INTO images VALUES(?)', zip(img))
.
To avoid multiple execute
-calls you can store the strings in a list and update your table in a single call with executemany
:
def insert(array):
connection = sqlite3.connect('images.db')
cursor = connection.cursor()
cnt = 0
imgs = []
while cnt != len(array):
img = array[cnt]
print(array[cnt])
imgs.append(img)
cnt += 1
cursor.executemany('INSERT INTO images VALUES(?)', zip(imgs))