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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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.

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

Method 3

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

Method 4

import mysql.connector

def login(self,name,password):
    db = mysql.connector.connect(host="localhost",user="root",passwd="<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="0d4e746f687f4d3c3f3e">[email protected]</a>",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()

Method 5

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0