sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type?

Question

I am getting an error when trying to compile data from Python(3.9.1) to SQLite3.

import sqlite3

conn = sqlite3.connect('newdB.db')

#Creates the table 'tbl_newdb' with an incrementing ID, and 'db_type' columns.
with conn:
    cur = conn.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS tbl_newdb( \
        ID INTEGER PRIMARY KEY AUTOINCREMENT, \
        db_type TEXT)")
    conn.commit()
conn.close()

conn = sqlite3.connect('newdB.db')

#Our list of data we will be grabbing from to fill the tables.
with conn:
    cur = conn.cursor()
    fileList = "information.docx","Hello.txt","myImage.png","myMovie.mpg","World.txt","data.pdf","myPhoto.jpg"
    cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)",(fileList,))
    conn.commit()
conn.close()

Here is the error message I am getting from the IDLE shell:

 line 22, in <module>
    cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)",(str(fileList,)))
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 103 supplied.

The error message does not match your code. In the error message the fileList tuple is being converted to a string, but the posted code does not do that. Also the question title is different. You've got an answer from @pop, but try to keep things in sync when posting input, output and code in future questions. — mhawke

pop pop · Accepted Answer · 2021-02-20T01:50:46

TLDR: execute takes two arguments, a SQL query and a list of inputs with one element for each (?) arguments in your SQL.

What you want is executemany which accepts a list of a list of inputs. The solution looks like this:

import sqlite3

# Create a database in memory for testing
conn = sqlite3.connect('newdB.db')

# Execute with the databse connection
with conn:
    cur = conn.cursor()

    # Create your table in the in-memory database
    cur.execute("""
    CREATE TABLE IF NOT EXISTS tbl_newdb(
        ID INTEGER PRIMARY KEY AUTOINCREMENT,
        db_type TEXT
    )
    """)
    conn.commit()

# A list of parameters to pass to queries
# Notice we are making a list of lists
# `execute` takes a list of inputs for a single execution
parameterList = [
    ["information.docx"],
    ["Hello.txt"],
    ["myImage.png"],
    ["myMovie.mpg"],
    ["World.txt"],
    ["data.pdf"],
    ["myPhoto.jpg"],
]

with conn:
    # Execute the same query multiple times
    # Because we are passing one value to our sql, each list in parameterList
    # only has one element.
    cur.executemany(
        "INSERT INTO tbl_newdb(db_type) VALUES(?)",
        parameterList
    )
    conn.commit()

    # Print debugging information to prove we inserted things correctly
    cur.execute("SELECT db_type FROM tbl_newdb")
    print(cur.fetchall())

# Close the connection after creating the table
conn.close()

Note I have use [...] syntax instead of tuple (...,) syntax. Both are OK, I just prefer square braces for clarity, and because we have a lot of one-element lists.

You can also substitute cur.exeutemany(...) with a for loop like this:

A bit of explanation: execute takes two arguments, an SQL query and a list of parameters. You can think of the list of parameters as substitutions for every (?) in your SQL query. The first (?) is replaced with the first element in your parameter list, the second (?) with the second element in your list, and so on.

Let's figure out how we got here. When I first ran your code I got this output:

Traceback (most recent call last):
  File "alt.py", line 20, in <module>
    cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)",(fileList,))
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

unsupported type was a hint to me that you were putting fileList which is already a tuple, and wrapping it in another tuple. So I changed that execute line to:

cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)", fileList)

Next we get this error:

Traceback (most recent call last):
  File "alt.py", line 20, in <module>
    cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)", fileList)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 7 supplied.

That's weird. It's taking my list of 7 things and trying to stuff them into one binding? Interesting. What if we shorten fileList to just one element? Start small and see where we get. I change the line above the execute to

fileList = "information.docx"

and we get a similar but different error:

Traceback (most recent call last):
  File "alt.py", line 20, in <module>
    cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)", fileList)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 16 supplied.

Hmm. 16 is the number of characters in "information.docx" so Python must be treating that string as a list of parameters. Let's wrap that in a list and see what happens:

fileList = ["information.docx"]

And with that we get our first success!

What happens if we add another element to the list?

fileList = ["information.docx", "other_file.txt"]

Traceback (most recent call last):
  File "alt.py", line 20, in <module>
    cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)", fileList)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied.

Science! It was successful with one element, but fails with two. This means that the second argument to execute needs to be a list with as many elements as (?) parameters in our SQL query.

Now we have a few options. We can either read the SQLite Python docs and discover executemany, or we can do a for loop:

for f in fileList:
    parameters = [f]
    cur.execute("INSERT INTO tbl_newdb (db_type) VALUES (?)", parameters)
conn.commit()

SQLite is very fast, so executemany and a for loop both yield similar performance.

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type?

1 Answers