如何解决‘Not all parameters were used in the SQL statement’ SQL报错？

错误根源 #

报错Not all parameters were used in the SQL statement的核心问题是SQL占位符数量与传入参数数量不匹配，同时存在参数顺序与字段对应错位：

• SQL语句中UPDATE部分有13个字段（Dep到PhotoSample），加上WHERE条件的Student_id，总共需要14个参数。
• 你传入的参数列表多了1个值（错误加入了self.var_std_name.get()），且参数顺序和SQL字段顺序完全混乱（比如把学生姓名对应到了Division字段的位置）。

修复方案 #

1. 修正参数顺序与数量 #

严格按照SQL字段顺序传入参数，移除多余的self.var_std_name.get()（如果不需要更新姓名）：

my_cursor.execute(
    "update student set Dep=%s,course=%s,Year=%s,Semester=%s,Division=%s,Roll=%s,Gender=%s,Dob=%s,Email=%s,Phone=%s,Address=%s,Teacher=%s,PhotoSample=%s where Student_id=%s",
    (
        self.var_dep.get(),          # 对应Dep
        self.var_course.get(),       # 对应course
        self.var_year.get(),         # 对应Year
        self.var_semester.get(),     # 对应Semester
        self.var_div.get(),          # 对应Division
        self.var_roll.get(),         # 对应Roll
        self.var_gender.get(),       # 对应Gender
        self.var_dob.get(),          # 对应Dob
        self.var_email.get(),        # 对应Email
        self.var_phone.get(),        # 对应Phone
        self.var_address.get(),      # 对应Address
        self.var_teacher.get(),      # 对应Teacher
        self.var_radio1.get(),       # 对应PhotoSample
        self.var_std_id.get()        # 对应WHERE条件的Student_id
    )
)

2. 若需更新学生姓名 #

如果业务需要更新学生姓名，要先把std_name加入SQL的SET部分，再对应添加参数：

my_cursor.execute(
    "update student set Dep=%s,course=%s,Year=%s,Semester=%s,std_name=%s,Division=%s,Roll=%s,Gender=%s,Dob=%s,Email=%s,Phone=%s,Address=%s,Teacher=%s,PhotoSample=%s where Student_id=%s",
    (
        self.var_dep.get(),
        self.var_course.get(),
        self.var_year.get(),
        self.var_semester.get(),
        self.var_std_name.get(),  # 新增对应std_name的参数
        self.var_div.get(),
        self.var_roll.get(),
        self.var_gender.get(),
        self.var_dob.get(),
        self.var_email.get(),
        self.var_phone.get(),
        self.var_address.get(),
        self.var_teacher.get(),
        self.var_radio1.get(),
        self.var_std_id.get()
    )
)

3. 细节优化 #

• messagebox.askyesno()返回布尔值True/False，判断条件改为if update:而非if update>0:
• 把conn.commit()、fetch_data()等操作放在if update:代码块内，避免用户取消更新后执行无效操作

修复后的完整函数：

def update_data(self):
    if self.var_dep.get()=="Select Department" or self.var_std_name.get()=="" or self.var_std_id.get()=="":
        messagebox.showerror("Error","All Fields are required",parent=self.root)
    else:
        try:
            update=messagebox.askyesno("Update","Do you want to update this student details",parent=self.root)
            if update:
                conn=mysql.connector.connect(host="localhost", username="root", password="Umer@123", database="face_recognizer")
                my_cursor=conn.cursor()
                my_cursor.execute(
                    "update student set Dep=%s,course=%s,Year=%s,Semester=%s,Division=%s,Roll=%s,Gender=%s,Dob=%s,Email=%s,Phone=%s,Address=%s,Teacher=%s,PhotoSample=%s where Student_id=%s",
                    (
                        self.var_dep.get(),
                        self.var_course.get(),
                        self.var_year.get(),
                        self.var_semester.get(),
                        self.var_div.get(),
                        self.var_roll.get(),
                        self.var_gender.get(),
                        self.var_dob.get(),
                        self.var_email.get(),
                        self.var_phone.get(),
                        self.var_address.get(),
                        self.var_teacher.get(),
                        self.var_radio1.get(),
                        self.var_std_id.get()
                    )
                )
                conn.commit()
                self.fetch_data()
                conn.close()
                messagebox.showinfo("Success","Student details successfully updated",parent=self.root)
            else:
                return
        except Exception as es:
            messagebox.showerror("Error",f"Due To:{str(es)}",parent=self.root)

内容的提问来源于stack exchange，提问作者Umer Khayam Mir