Skip to main content

How to Resolve "mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement" in Python

The mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement is a common error when executing parameterized SQL queries with the mysql.connector library in Python.

This usually happens due to incorrect parameter markers or a mismatch between placeholders and provided values.

This guide explains the causes and provides clear solutions.

Understanding the Error: Parameter Mismatches​

This error indicates that the number of placeholders (parameter markers) in your SQL statement doesn't align with the number of values you provided to the cursor.execute() method, or that you've used an incorrect type of placeholder. mysql.connector uses a specific placeholder style (%s) and requires a precise match.

Cause 1: Incorrect Parameter Marker (Using %d, %f, etc.)​

A very common mistake is using Python's old string formatting placeholders like %d (for integers) or %f (for floats) in the SQL statement. mysql.connector requires you to use %s for all parameter types.

Error Example:

# ... (database connection code) ...
cursor = cnx.cursor()

# ⛔️ Incorrect: Using %d for the last parameter
add_employee = ("INSERT INTO employees "
                "(first_name, last_name, hire_date, gender, birth_date) "
                "VALUES (%s, %s, %s, %s, %d)") # <--- Problem here

# ... (data definition) ...
# data_employee = ('Tom', 'Nolan', tomorrow, 'M', date(1990, 2, 15))
# cursor.execute(add_employee, data_employee) # Raises the error

Solution: Use %s for All Parameters​

Always use %s as the placeholder for every parameter you pass to cursor.execute(), regardless of the actual data type (string, integer, date, etc.). The connector handles the type conversion correctly.

# ✅ Correct syntax: Using %s for all parameter markers
add_employee = ("INSERT INTO employees "
                "(first_name, last_name, hire_date, gender, birth_date) "
                "VALUES (%s, %s, %s, %s, %s)") # <--- All are %s

Cause 2: Mismatch Between Placeholders and Values​

The error also occurs if the number of %s placeholders in your SQL query doesn't exactly match the number of values in the tuple (or list) you pass as the second argument to cursor.execute().

Error Example:

# ... (connection code) ...
cursor = cnx.cursor()

# SQL has 5 placeholders
add_employee = ("INSERT INTO employees "
                "(first_name, last_name, hire_date, gender, birth_date) "
                "VALUES (%s, %s, %s, %s, %s)")

# Data tuple only has 4 values
# data_employee = ('Tom', 'Nolan', tomorrow, 'M') # ⛔️ Missing birth_date value

# cursor.execute(add_employee, data_employee) # Raises the error

Solution: Ensure Counts Match​

Carefully count the number of %s placeholders in your SQL string and make sure the tuple (or list) of data you provide to execute() has the exact same number of elements. The order of values in the tuple must also correspond to the order of the %s placeholders in the SQL statement.

Correct Usage Example​

Here's a complete, correct example putting it all together:

from datetime import date, datetime, timedelta
import mysql.connector

try:
    cnx = mysql.connector.connect(
        user='your_user',           # Replace with your details
        password='your_password',   # Replace with your details
        host='127.0.0.1',
        database='employees'        # Replace with your database
    )
    cursor = cnx.cursor()

    add_employee = ("INSERT INTO employees "
                    "(first_name, last_name, hire_date, gender, birth_date) "
                    "VALUES (%s, %s, %s, %s, %s)") # All %s

    tomorrow = datetime.now().date() + timedelta(days=1)

    # Ensure data tuple has 5 values, matching the 5 %s placeholders
    data_employee = ('Tom', 'Nolan', tomorrow, 'M', date(1990, 2, 15))

    cursor.execute(add_employee, data_employee)
    emp_no = cursor.lastrowid
    print(f"Successfully inserted employee with ID: {emp_no}")

    cnx.commit() # Commit the transaction

except mysql.connector.Error as err:
    print(f"Error: {err}")
finally:
    if 'cursor' in locals() and cursor is not None:
        cursor.close()
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()
        print("MySQL connection is closed")

Troubleshooting: Restarting MySQL Server (Rarely Needed)​

In very rare cases, if you're certain your Python code is correct, a persistent issue might stem from the MySQL server itself. Restarting the server might help, but this should be a last resort after thoroughly checking your code.

  • Ubuntu/Debian: sudo systemctl restart mysql (or mysqld)
  • macOS (Homebrew): brew services restart mysql
  • macOS (Direct Install): sudo /usr/local/mysql/support-files/mysql.server restart (path may vary)
  • Windows (Services): Open services.msc, find the MySQL service, right-click, and select "Restart".
  • Windows (Task Manager): Go to the Services tab, find MySQL, right-click, and select "Restart".
warning

Restarting the MySQL server will interrupt any active connections and operations. Only do this if necessary and ideally during a maintenance window.

This is unlikely to be the cause of the specific Python TypeError.

Conclusion​

The mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement typically boils down to two main causes: using incorrect placeholders (like %d instead of %s) or having a mismatch between the number of %s placeholders in your SQL and the number of values supplied to cursor.execute().

Always use %s for all parameters and ensure the counts match precisely to avoid this error.

Table of Contents