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?

To use `?` instead of `%x` in the prepared statement you could `cursor = cnx.cursor(prepared=true)` — Bob Stein, May 18 '15 at 21:14

unutbu · Accepted Answer · 2013-12-28T19:05:37.287

139

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.

edited Dec 28 '13 at 19:05

answered Dec 28 '13 at 19:00

unutbu

842,883
184
1,785
1,677

I thought they would be %d since they were numbers. But still same error when using %s and even when removing the two year-variables. – Enthuziast Dec 28 '13 at 19:05
2

That's really curious. To debug, you could stop the `mysqld` server, then restart it with logging enabled: `mysqld --log=/path=/to/myquery.log`. Rerun the script, then look inside `/path/to/myquery.log` to see what SQL statement is being sent to the server. That should give us a clue as to what is going wrong. – unutbu Dec 28 '13 at 19:11
Thanks a lot! Restarting the server seems to have done the work... Sorry about wasting your time! – Enthuziast Dec 28 '13 at 19:24
Can you take a look here? https://stackoverflow.com/questions/72684970/execution-failed-on-sql-select-name-from-sqlite-master-where-type-table-and-n – Jnl Jun 20 '22 at 11:18

score 1 · Answer 2 · answered Feb 12 '21 at 18:45

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

Can you take a look here? https://stackoverflow.com/questions/72684970/execution-failed-on-sql-select-name-from-sqlite-master-where-type-table-and-n — Jnl, Jun 20 '22 at 11:18

dark_coder · Answer 3 · 2023-07-21T18:05:36.867

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

score -1 · Answer 4 · answered May 12 '20 at 08:17

-1

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

answered May 12 '20 at 08:17

Sujan Poudel

1

1

it is not exactly clear what you're trying to do. Are you trying to insert a single line or multi line using this SQL query? Where are you using this query? along with the or in the IDE? You can modify your code like the following, add the parameter and execute the SQL sql = "INSERT INTO DB.tbluser (username, department, startyear, currentpos, link) VALUES ('full name', 'new dept', '2020','http://xyz')" – Nava Bogatee May 12 '20 at 08:30
Can you take a look here? https://stackoverflow.com/questions/72684970/execution-failed-on-sql-select-name-from-sqlite-master-where-type-table-and-n – Jnl Jun 20 '22 at 11:18

score -2 · Answer 5 · edited Apr 03 '21 at 08:12

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

edited Apr 03 '21 at 08:12

azro

53,056
7
34
70

answered Apr 03 '21 at 04:34

Pawar Jay

1

It may be more helpful if you could briefly explain why/how your solution works. – Anurag A S Apr 03 '21 at 06:34
Can you take a look here? https://stackoverflow.com/questions/72684970/execution-failed-on-sql-select-name-from-sqlite-master-where-type-table-and-n – Jnl Jun 20 '22 at 11:19

score -3 · Answer 6 · edited May 01 '20 at 10:52

-3

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

edited May 01 '20 at 10:52

Kuldeep Singh Sidhu

3,748
2
12
22

answered Jun 29 '19 at 15:50

Gaddafi adamu

7
2

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

6 Answers6

Linked

Related