Python: Not all parameters were used in the SQL statement

# Python: Not all parameters were used in the SQL statement

The Python error "mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement" occurs for multiple reasons:

Using %d for the parameter markers instead of %s.
Having a mismatch between the number of columns and the number of values provided.

Here is an example of how the error occurs.

main.py

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

cnx = mysql.connector.connect(
    user='bobbyhadz',
    password='dogsandcats123',
    host='127.0.0.1',
    database='employees'
)

cursor = cnx.cursor()

# ⛔️ Incorrect: Should not use %d as parameter marker
add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %d)")

tomorrow = datetime.now().date() + timedelta(days=1)
data_employee = ('Bobby', 'Hadz', tomorrow, 'M', date(1990, 2, 15))

# ⛔️ mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement
cursor.execute(add_employee, data_employee)
emp_no = cursor.lastrowid

cnx.close()

Notice that I used %d instead of %s for the parameter marker.

This causes the error because all parameter markers should be specified using %s (regardless of the type).

The following is correct.

main.py

Copied!
# ✅ Correct syntax (using %s for parameter markers)
add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")

Note that the parameter markers that are used by mysql.connector (%s) are not the same as the ones used for old string formatting operations.

All mysql.connector parameter markers are specified as %s.

# Specify the same number of column names and parameter markers

Notice that we've specified 5 column names and we have 5 parameter markers.

main.py

Copied!
# ✅ The number of column names matches the number of parameter markers
add_employee = ("INSERT INTO employees "
               "(first_name, last_name, hire_date, gender, birth_date) "
               "VALUES (%s, %s, %s, %s, %s)")

The column names are: first_name, last_name, hire_date, gender, birth_date.

The parameter markers are specified in parentheses after the VALUES keyword.

If the number of column names doesn't match the number of parameter markers, the error is raised.

You have to also make sure that the number of supplied values matches your parameter markers and column names.

main.py

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

tomorrow = datetime.now().date() + timedelta(days=1)
data_employee = ('Bobby', 'Hadz', tomorrow, 'M', date(1990, 2, 15))

The data_employee tuple stores a value for each parameter marker.

The values in the tuple must be in the same order in which the column names are specified.

In the example, the values must be:

first_name
last_name
hire_date
gender
birth_date

The last step is to pass the add_employee string literal and the data_employee tuple to the cursor.execute() method.

main.py

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

tomorrow = datetime.now().date() + timedelta(days=1)
data_employee = ('Bobby', 'Hadz', tomorrow, 'M', date(1990, 2, 15))

# ✅ Call cursor.execute() with SQL and values
cursor.execute(add_employee, data_employee)

If the error persists, try to restart your MySQL server.

For example, on Ubuntu, you can issue the following command.

shell

Copied!
sudo systemctl restart mysqld

On macOS, you can issue the following command.

shell

Copied!
mysql.server restart

# Or using brew
brew services restart mysql

If you are on Windows:

Press the Windows key + R to open the Run window.
Type services.msc and press OK.
In the Services window, find the MySQL server service and select it.
Under Services (Local), you will see 3 options:

Stop the service
Pause the service
Restart the service

Click on the "Restart a service" option.

You can also restart your MySQL server on Windows by using Task Manager.

Open the Task Manager application.
Click on the Services tab.
Select the MySQL service.
Right-click on it and select Restart.

# Additional Resources

You can learn more about the related topics by checking out the following tutorials:

I wrote a book in which I share everything I know about how to become a better, more efficient programmer.

You can use the search field on my Home Page to filter through all of my articles.

Python: Not all parameters were used in the SQL statement