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

Question

I get 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 

Do you understand why?

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 look the same as the %s used in Python string formatting but the relationship is only coincidental. Some database adapters like oursql and sqlite3 use ? as the parameter marker instead of %s.

Answer 2

Ok here is my solution:

the %d's need to be %s's

the ''' works for multiple lines so does a +" at the start of a line and a "\ at the end but that is not this issue here

I find sometimes the cursor.execute(query, values) or in your case the cursor.execute(add_user, data_user) buggy sometimes.. so I attach the values directly to the query.

You do this by first moving your data_user above the add_user. Then you add %values at the end of the query string (in your case %data_user). This will allow you to preform a nice little print(add_user) before the cursor.exec(add_user) command to look for other bugs.

         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 = ""          data_user = (Name, Department, StartYear, CurrentPos, Link)          add_user = '''(INSERT INTO DB.tbluser          (username, department, startyear, currentpos, link)          VALUES (%s, %s, %s, %s, %s))'''%data_user           cursor.execute(add_user)         cnx.commit()         cursor.close()         cnx.close()