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.