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:

cursor = cnx.cursor()

add_employee = ("INSERT INTO employees "
                "(first_name, last_name, hire_date, gender, birth_date) "
                "VALUES (%s, %s, %s, %s, %d)") # <--- Problem here

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.

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:

cursor = cnx.cursor()

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

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)

    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".

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.