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

Question

I get an error in the following Python code: My Mini Project

def dbconnect():
    try:
      db=mysql.connector.connect(host='localhost',user='SYSTEM', password='123456', database='billing_system')
      mycursor=db.cursor()
      mycursor.execute('CREATE TABLE IF NOT EXISTS bill( bill_no int NOT NULL PRIMARY KEY, c_name varchar(20) DEFAULT NULL, c_phone varchar(10) DEFAULT NULL, item varchar(20) DEFAULT NULL, rate int DEFAULT NULL, quantity int DEFAULT NULL, total int DEFAULT NULL);')
      mycursor.execute('INSERT INTO bill (bill_no, c_name, c_phone, item, rate, quantity, total) VALUES (%i,%s,%s,%s,%i,%i,%i);', (b,a,c,i,r,q,s))
      db.commit()

    except mysql.connector.Error as err:
      if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
      elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
      else:
        print(err)
    else:
      db.close()

The error says:

384
<class 'int'>
Not all parameters were used in the SQL statement 
PY_VAR0

The code runs without any issue.

But the data I enter does not store in my table.

In your INSERT change the %i with %s. All placeholders should be %s — nacho, Apr 28 '23 at 17:57
I have intitialized the following variables as IntVar (To help my calculation part) I have attached my full program's GitHub link too — Krishnakumar R, Apr 28 '23 at 18:01
That's not an issue, you just need to use the `%s` instead of `%i` — A l w a y s S u n n y, Apr 28 '23 at 18:15
@KrishnakumarR it doesn't matter the data types of your variables. The placeholder is always %s and it is not a string formatting — nacho, Apr 28 '23 at 18:50

score 0 · Answer 1 · answered Apr 28 '23 at 18:03

0

Try using %s for all parameters: mycursor.execute('INSERT INTO bill (bill_no, c_name, c_phone, item, rate, quantity, total) VALUES (%s,%s,%s,%s,%s,%s,%s);', (b,a,c,i,r,q,s))

answered Apr 28 '23 at 18:03

ryan zalewski

1

I have intitialized the following variables as IntVar (To help my calculation part) I have attached my full program's GitHub link too. – Krishnakumar R Apr 28 '23 at 18:04
check out this response, I believe the %s is not the same as a string formatting. https://stackoverflow.com/a/20818201/20602568 – ryan zalewski Apr 28 '23 at 18:07
So are you trying to say me that i should change to %s and if it does not work, I should change the same to just '? ' ? @ryan zalewski – Krishnakumar R Apr 29 '23 at 01:24

score 0 · Answer 2 · answered Apr 28 '23 at 18:17

You should use %s as the placeholder instead of %i for all data types when using parameterized queries with MySQL.So your code will be like this-

def dbconnect():
    try:
        db=mysql.connector.connect(host='localhost',user='SYSTEM', password='123456', database='billing_system')
        mycursor=db.cursor()
        mycursor.execute('CREATE TABLE IF NOT EXISTS bill( bill_no int NOT NULL PRIMARY KEY, c_name varchar(20) DEFAULT NULL, c_phone varchar(10) DEFAULT NULL, item varchar(20) DEFAULT NULL, rate int DEFAULT NULL, quantity int DEFAULT NULL, total int DEFAULT NULL);')
        mycursor.execute('INSERT INTO bill (bill_no, c_name, c_phone, item, rate, quantity, total) VALUES (%s,%s,%s,%s,%s,%s,%s);', (b,a,c,i,r,q,s))
        db.commit()
    except mysql.connector.Error as err:
        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
            print("Something is wrong with your user name or password")
        elif err.errno == errorcode.ER_BAD_DB_ERROR:
            print("Database does not exist")
        else:
            print(err)
    else:
        db.close()

score 0 · Answer 3 · answered May 01 '23 at 11:59

0

I've resolved my issue.. The key was to initialize the variables just before inserting the data in DB... Thank you all for these wonderful suggestions..

answered May 01 '23 at 11:59

Krishnakumar R

1

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

3 Answers3