Python类操作MySQL报错：Not all parameters were used in the SQL statement

我写了一个用于MySQL数据库操作的accountManage类，包含connection、createTable和insert方法。执行插入数据操作时触发ProgrammingError，错误信息为“Not all parameters were used in the SQL statement”，但不使用类实现相同功能时运行正常，找不到错误原因，求帮助。

代码实现 #

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

报错输出 #

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

你的代码存在三个核心问题，逐一修复即可解决报错：

1. SQL参数占位符格式错误 #

MySQL Connector/Python 标准参数占位符是%s，而非你代码中使用的?（?是SQLite等其他数据库的占位符格式）。修改插入语句的占位符：

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

2. 数据库连接未绑定为实例属性 #

connection方法中创建的db是局部变量，其他方法无法访问（实际运行中若存在全局db变量可能暂时规避报错，但逻辑完全错误）。需要将db保存为类的实例属性：

def connection(self):
    self.db = mysql.connector.connect(username="root",password="jhanguman",database="test")

后续所有用到db的地方都要改为self.db，比如创建游标时：

cursor = self.db.cursor()

3. 缺少事务提交与资源清理 #

插入操作后必须调用self.db.commit()提交事务，否则数据不会写入数据库；同时每次操作完游标后应关闭，避免资源泄漏。修改insert方法：

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=self.db.cursor()
        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))
        self.db.commit()
        cursor.close()

修复后的完整代码 #

import mysql.connector

class accountManage:
    def connection(self):
        self.db = mysql.connector.connect(username="root",password="jhanguman",database="test")
    
    def createTable(self):
        cursor = self.db.cursor()
        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=self.db.cursor()
            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))
            self.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()

内容的提问来源于stack exchange，提问作者ghostatpost