Not all parameters were used in the SQL statement (Python, MySQL)

Question

I am getting an error on the following Python code:

import mysql.connector

cnx = mysql.connector.connect(user='root', password='',

                          host='127.0.0.1',

                          database='DB')

cursor = cnx.cursor()

Name = "James"

Department = "Finance"

StartYear = 2001

CurrentPos = 2001

Link = ""

add_user = ("INSERT INTO DB.tbluser "

       "(username, department, startyear, currentpos, link) "

       "VALUES (%s, %s, %d, %d, %s)")

data_user = (Name, Department, StartYear, CurrentPos, Link)

cursor.execute(add_user, data_user)

cnx.commit()

cursor.close()

cnx.close() 

The error message is, 

mysql.connector.errors.ProgrammingError: Not all parameters were used in the SQL statement 

 Can anyone help with this?

Answer 1

The parameter marker is %s not %d.

add_user = """INSERT INTO DB.tbluser 

              (username, department, startyear, currentpos, link) 

              VALUES (%s, %s, %s, %s, %s)"""

Note that the parameter markers used by mysql.connector may seem to be the same like the %s used in the Python string formatting however the relationship is only coincidental. Some database adapters like oursql and sqlite3 use the  ? as the parameter marker instead of the %s.

Want to be a SQL expert? Join this SQL Certification course by Intellipaat.

You can check out the below MySQL Tutorial video for better understanding.

For more information, kindly refer to our Python course.

Answer 2

try the below code
import mysql.connector

cnx = mysql.connector.connect(user='root', password='', host='127.0.0.1', database='DB')
cursor = cnx.cursor()

Name = "James"
Department = "Finance"
StartYear = 2001
CurrentPos = 2001
Link = ""

add_user = ("INSERT INTO DB.tbluser "
"(username, department, startyear, currentpos, link) "
"VALUES (%s, %s, %s, %s, %s)")

data_user = (Name, Department, StartYear, CurrentPos, Link)

cursor.execute(add_user, data_user)
cnx.commit()
cursor.close()
cnx.close()

Error message “ mysql.connector.errors” is caused by mismatch between placeholders in your sql statement and data types in “data_user” tuple. You have used “%d” placeholders, which is incorrect . Instead you should use %s for all datatypes.

Above code will resolve the "Not all parameters were used" error and data will be inserted correctly.