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.")

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.