Python 3 SQLite3 - Incorrect number of bindings

Question

I understand there are MANY questions on Stack Overflow about this error but I have tried MANY solutions and obviously they have all failed.

Here's a list:

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

SQLite parameter substitution problem

sqlite3.ProgrammingError: Incorrect number of bindings supplied

Reading from database with SQLite and Python: Incorrect number of binding supplied

SQLite Python Insert - Incorrect Number of Bindings Supplied

I am trying to store a username and hashed password (created by PassLib - https://pythonhosted.org/passlib/ ) in a SQLite3 database. These are stored in the variables "targetusername" and "password" respectively. My problem is that when I actually try to insert these 2 variables into a table of a database called "Passwords", it gives this error:

Incorrect number of bindings supplied. The current statement uses 1, and there are 11 supplied.

Here's an example of what targetusername and password would store:

targetusername = "user4884072"
password = "$5$rounds=535000$ySH31paWMjEDCUUY$jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4"

This line gives the error:

c.executemany("INSERT INTO {tn} ({idf}, {cn}) VALUES(targetusername, %s" % str(password).\
format(tn="Passwords"))

It has been changed multiple times to try and fix the issue (which apparently is caused by how Python stores variables), but here is what it was originally:

c.execute("INSERT OR IGNORE INTO {tn} ({idf}, {cn}) VALUES (targetusername, password)".\
format(tn="Passwords", idf="Username", cn="Password"))

Answer 1

Use c.execute(), not c.executemany(), to insert a single row of data. This is the immediate cause of the error that you experience.

In addition to that, don't use string substitution, use parameterised queries. Here is a complete working example:

import sqlite3

connection = sqlite3.connect(':memory:')    # in memory database
c = connection.cursor()

c.execute('create table Passwords (Username text, Password text)')

targetusername = "user4884072"
password = "$5$rounds=535000$ySH31paWMjEDCUUY$jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4"
c.execute('insert into Passwords (Username, Password) values (?, ?)', (targetusername, password))
print c.execute('select * from Passwords').fetchall()

Output:

[(u'user4884072', u'$5$rounds=535000$ySH31paWMjEDCUUY$jdrBVGsoYnSMkdVBtjCaxQy2f0g3MX1Wts4vSYz7m.4')]

In the code that you have posted there is no point in substituting values for the table or column names, so just put them in the query string as shown.

This uses a parameterised query where the API inserts the values for username and password into the query at the places denoted by ?. This is safer than using string substitution because the DB API knows how to properly and safely escape the values passed to it, and this avoids SQL injection attacks on your code.

And it uses execute() rather than executemany() since there is only one row of data being inserted.