Skip to main content

How to Resolve sqlite3.ProgrammingError: Incorrect number of bindings supplied

When working with SQLite databases in Python using the sqlite3 module, a common error encountered during query execution is sqlite3.ProgrammingError: Incorrect number of bindings supplied. This error typically indicates a mismatch between the number of placeholders (e.g., ?) in your SQL query and the number of values you've provided for those placeholders.

This guide explains the primary causes of this error and provides clear solutions, focusing on the correct way to pass parameters to cursor.execute().

Understanding the "Incorrect number of bindings" Error​

In sqlite3, when you execute a parameterized SQL query (a query with placeholders like ? or named placeholders like :name), you provide the values for these placeholders as a second argument to the cursor.execute() method. This second argument must be a sequence (typically a tuple or a list).

The error message: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses X, and there are Y supplied. means:

  • Your SQL statement has X placeholders (e.g., X question marks ?).
  • You provided Y values in the sequence passed to execute(). The error occurs because X and Y are not equal.

Common Cause 1: Passing a String Instead of a Sequence (Tuple/List)​

This is the most frequent reason for the error, especially when your SQL statement has only one placeholder.

The Problem: cursor.execute(sql, "single_value")​

If your SQL query has one placeholder, like INSERT INTO users VALUES (?), and you try to pass a single string value directly, Python might interpret the string itself as a sequence of characters, leading to the error.

import sqlite3

# Setup a dummy database for demonstration
conn = sqlite3.connect(':memory:') # In-memory database
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS mytable (name TEXT)")
conn.commit()

name_to_insert = "Alice" # This is a string

try:
    # ⛔️ sqlite3.ProgrammingError: Incorrect number of bindings supplied.
    # The current statement uses 1, and there are 5 supplied. (because "Alice" has 5 chars)
    cursor.execute("INSERT INTO mytable VALUES (?)", (name_to_insert)) # WRONG for single string
except sqlite3.ProgrammingError as e:
    print(f"Caught ProgrammingError: {e}")
    print(f"Type of second argument passed: {type((name_to_insert))}") # Output: <class 'str'>
finally:
    conn.close()

Even though (name_to_insert) uses parentheses, if name_to_insert is a string, the parentheses don't make it a tuple. ("Alice") is still just the string "Alice".

Why "single_value" is Treated as Multiple Bindings​

When sqlite3 expects a sequence for bindings and receives a string (which is a sequence of characters), it iterates over the string. So, if your string is "Alice" (5 characters) and your SQL has 1 placeholder (?), sqlite3 sees 5 bindings ('A', 'l', 'i', 'c', 'e') where it expected 1.

Solution: Use a Single-Element Tuple (("single_value",))​

To correctly pass a single value for a single placeholder, wrap it in a single-element tuple. A tuple is created by including a comma , even if there's only one element.

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS mytable (name TEXT)")
conn.commit()

name_to_insert = "Alice"

# ✅ Correct: Pass a single-element tuple
# Notice the trailing comma after name_to_insert
parameters = (name_to_insert,) # This creates a tuple: ('Alice',)

print(f"Type of parameters: {type(parameters)}")    # Output: Type of parameters: <class 'tuple'>

try:
    cursor.execute("INSERT INTO mytable VALUES (?)", parameters)
    conn.commit()
    print(f"Successfully inserted '{name_to_insert}'")

    # Verify
    cursor.execute("SELECT * FROM mytable")
    print(f"Data in table: {cursor.fetchall()}")    # Output: Data in table: [('Alice',)]
except sqlite3.ProgrammingError as e:
    print(f"Error during correct execution: {e}")   # Should not happen now
finally:
    conn.close()

The key is the trailing comma: (value,).

Alternative Solution: Use a List (["single_value"])​

You can also pass the single value as an element in a list.

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS mytable (name TEXT)")
conn.commit()

name_to_insert = "Tom"
parameters_list = [name_to_insert] # A list containing one string

print(f"Type of parameters_list: {type(parameters_list)}") # Output: <class 'list'>

try:
    cursor.execute("INSERT INTO mytable VALUES (?)", parameters_list)
    conn.commit()
    print(f"Successfully inserted '{name_to_insert}' using a list.")
except sqlite3.ProgrammingError as e:
    print(f"Error with list parameter: {e}")
finally:
    conn.close()
note

Both single-element tuples and lists are valid sequences for cursor.execute().

Common Cause 2: Mismatch Between Placeholders and Values​

This occurs when the number of ? placeholders in your SQL query doesn't match the number of items in the tuple or list you provide.

The Problem: Number of ? vs. Number of Values​

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS products (id INTEGER, name TEXT)")
conn.commit()

# SQL has 2 placeholders: (?, ?)
sql_query = "INSERT INTO products VALUES (?, ?)"

# Parameters list has 3 values
product_data = [1, "Laptop", 1200.00] # ID, Name, Price (but SQL only expects ID, Name)

try:
    # ⛔️ sqlite3.ProgrammingError: Incorrect number of bindings supplied.
    # The current statement uses 2, and there are 3 supplied.
    cursor.execute(sql_query, product_data)
    conn.commit()
except sqlite3.ProgrammingError as e:
    print(f"Caught ProgrammingError: {e}")
finally:
    conn.close()

Solution: Ensure Placeholders Match Values​

Carefully count the ? placeholders in your SQL query and ensure the tuple/list of parameters has exactly that many elements, in the correct order.

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# Assume table with 3 columns: id, name, price
cursor.execute("CREATE TABLE IF NOT EXISTS products (id INTEGER, name TEXT, price REAL)")
conn.commit()

# ✅ SQL has 3 placeholders: (?, ?, ?)
sql_query = "INSERT INTO products VALUES (?, ?, ?)"

# ✅ Parameters list has 3 values
product_data_correct = (2, "Mouse", 25.99) # (ID, Name, Price)

try:
    cursor.execute(sql_query, product_data_correct)
    conn.commit()
    print(f"Successfully inserted product with data: {product_data_correct}")
    cursor.execute("SELECT * FROM products WHERE id=?", (product_data_correct[0],))
    print(f"Fetched product: {cursor.fetchone()}")
except sqlite3.ProgrammingError as e:
    print(f"Error during correct execution: {e}")
finally:
    conn.close()

Best Practices for Parameterized Queries​

  • Always use parameterization (? or named styles): Never use Python string formatting (like f-strings or % formatting) to insert values directly into your SQL query string. This is a major security risk (SQL injection).
  • Pass parameters as a tuple or list: Even for a single parameter, use (value,) or [value].
  • Match placeholder count: Ensure the number of placeholders exactly matches the number of elements in your parameter sequence.

Debugging the Error​

  1. Print the SQL query: print(your_sql_query)
  2. Print the parameters and their type: print(your_parameters) and print(type(your_parameters))
  3. Manually count:
    • Count the ? placeholders in your SQL.
    • Count the elements in your parameters tuple/list.
    • If the error message says "...current statement uses 1, and there are X supplied" where X > 1, and you intended to pass a single string, you almost certainly forgot the trailing comma for a single-element tuple: ("my_string",)

Conclusion​

The sqlite3.ProgrammingError: Incorrect number of bindings supplied error in Python arises from discrepancies between SQL query placeholders and the provided parameters. The most common pitfalls are:

  1. Passing a string directly for a single parameter instead of a single-element tuple (value,) or list [value]. This causes the string to be iterated by character.
  2. Having a mismatch between the number of ? placeholders in the SQL and the number of items in the parameter sequence.

By ensuring your parameters are always passed as a tuple or list, and that the count of values matches the count of placeholders, you can effectively prevent this error and write secure, correct SQLite database interactions in Python.

Table of Contents