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](http://stackoverflow.com/questions/16856647/sqlite3-programmingerror-
incorrect-number-of-bindings-supplied-the-current-sta)

[SQLite parameter substitution
problem](http://stackoverflow.com/questions/228912/sqlite-parameter-
substitution-problem)

[sqlite3.ProgrammingError: Incorrect number of bindings
supplied](http://stackoverflow.com/questions/19473281/sqlite3-programmingerror-
incorrect-number-of-bindings-supplied)

[Reading from database with SQLite and Python: Incorrect number of binding
supplied](http://stackoverflow.com/questions/9986578/reading-from-database-
with-sqlite-and-python-incorrect-number-of-binding-suppli)

[SQLite Python Insert - Incorrect Number of Bindings
Supplied](http://stackoverflow.com/questions/4654513/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: 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.

