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

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?

1

6 Answers

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.

4

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() 
1

If Number of column and number of value mismatch than throw this error Error Not all parameters were used in the SQL statement

you should avoid %d inside value

Incorrect Code

data = [('sundar','pichai','343434343','CEO'),('elon','musk','3434354545433','FOUNDER')] sql = "INSERT INTO user(firstname,lastname,phone_number,profession) VALUES(%s,%s,%s)" 

Correct code

data = [('sundar','pichai','343434343','CEO'),('elon','musk','3434354545433','FOUNDER')] sql = "INSERT INTO user(firstname,lastname,phone_number,profession) VALUES(%s,%s,%s,%s)" 

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

=> you are using multi line statement so use triple single quotation marks here and use %s to represent passing value as string then that will works because %d is not supported by mysql to pass value

2
import mysql.connector def login(self,name,password): db = mysql.connector.connect(host="localhost",user="root",passwd="Cyber@123",database="mydata") if(db): print("connection successfull") else: print("faild") query = db.cursor() syntext = """insert into login(use_name,psd) values(%s,%s)""" value = (name,password) query.execute(syntext,value) db.commit() 
2

use single quotations

sql = 'insert into Student (id, fname, lname, school) values(%s, %s, %s , %s)' values = (4, "Gaddafi", "Adamu", "Informatic") a.execute(sql, values) mydb.commit() print(a.rowcount, "record inserted.") 

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoaWlwYW1%2BdoGOp6atZZGhuW68wKuYpp2kmr%2B0edaeqZ5lpaiypXnIp2StoJViwLK4jKyrmqyVorKvwIypsK2gn6N6rsXSqqM%3D