Home > Mobile >  "Not all parameters were used in the SQL statement"
"Not all parameters were used in the SQL statement"

Time:12-23

i am not able to find what is error in this code. help!!!

class accountManage:
    def connection(self):
        db=mysql.connector.connect(username="root",password="jhanguman",database="test")
    def createTable(self):
        cursor=db.cursor()
        #cursor.execute("drop table if exists")
        cursor.execute('''create table if not exists ACCOUNT (
              Account_ID int PRIMARY KEY,
              Account_Name varchar(250),
              Account_Size int,
              Account_Duration int,
              Account_Budget float,
              Status char(10))''')
        cursor.close()
    def insert(self):
        n=int(input("No of rows to insert: "))
        for i in range(n):
            Id=int(input())
            name=input()
            size=int(input())
            duration=int(input())
            budget=float(input())
            status=input()
            cursor=db.cursor()
            cursor.execute("""INSERT INTO ACCOUNT (Account_ID,Account_Name,Account_Size,Account_Duration,Account_Budget,Status) VALUES(?,?,?,?,?,?)""", (Id,name,size,duration,budget,status))
            #db.commit()
            #cursor.close()
            
account=accountManage()
choice=int(input("enter choice....1:connection, 2.create table 3. insert no of rows"))
if(choice==1):
    account.connection()
elif(choice==2):
    account.createTable()
elif(choice==3):
    account.insert()

Output:

enter choice....1:connection, 2.create table 3. insert no of rows3
No of rows to insert: 1
2
rohan
10
100
12000
active
---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-21-8850a7d4652a> in <module>
     34     account.createTable()
     35 elif(choice==3):
---> 36     account.insert()
     37 

<ipython-input-21-8850a7d4652a> in insert(self)
     23             status=input()
     24             cursor=db.cursor()
---> 25             cursor.execute("""INSERT INTO ACCOUNT (Account_ID,Account_Name,Account_Size,Account_Duration,Account_Budget,Status) VALUES(?,?,?,?,?,?)""", (Id,name,size,duration,budget,status))
     26             #db.commit()
     27             #cursor.close()

~\anaconda3\lib\site-packages\mysql\connector\cursor_cext.py in execute(self, operation, params, multi)
    272                 stmt = RE_PY_PARAM.sub(psub, stmt)
    273                 if psub.remaining != 0:
--> 274                     raise ProgrammingError(
    275                         "Not all parameters were used in the SQL statement"
    276                     )

ProgrammingError: Not all parameters were used in the SQL statement

i have tried doing the same without using class and its running smoothly. ......................................... .................................................................................................................................................................................................................................................................................. ................. ............. ......... ........ ..... ...

CodePudding user response:

You are using '?' insted of '%s' in the insert. The insert should be:

cursor.execute("""INSERT INTO ACCOUNT (Account_ID,Account_Name,Account_Size,Account_Duration,Account_Budget,Status) VALUES(%s,%s,%s,%s,%s,%s)""", (Id,name,size,duration,budget,status))
  • Related