
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?
16 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
.
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()
1If 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
2import 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()
2use 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