Python+MySQL人脸识别考勤系统数据更新SQL语法错误求助

问题概述 #

开发基于Python和MySQL的人脸识别考勤系统时，执行学生信息更新操作触发MySQL 1064(42000)语法错误，错误提示如下：

在'Div='a', Roll No='29', Gender='Male', DOB='8/5/2002', Email='ab@gmail.com', Teac'附近存在SQL语法问题。

期望点击更新按钮后，系统弹出成功提示并完成数据库数据更新，当前使用的Python代码如下：

def update_data(self):
    global conn
    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 Student Details", parent=self.root)
            if Update > 0:
                conn = mysql.connector.connect(host="localhost", user="root", password="08052002H@ck",
                                               database="face_recognizer")
                my_cursor = conn.cursor()
                my_cursor.execute(
                    "update student set Dep=%s, Course=%s, Year=%s, Sem=%s, Name=%s, Div=%s, Roll No=%s, Gender=%s, DOB=%s, Email=%s, Teacher Name=%s, Phone No=%s, Address=%s, Photo=%s where ID=%s",
                    (
                        self.var_dep.get(), self.var_course.get(), self.var_year.get(), self.var_semester.get(),
                        self.var_std_name.get(), self.var_div.get(), self.var_roll.get(), self.var_gender.get(),
                        self.var_dob.get(), self.var_email.get(), self.var_teacher.get(), self.var_phone.get(),
                        self.var_address.get(), self.var_radio1.get(), self.var_std_id.get()
                    ))
            else:
                if not Update:
                    return
            messagebox.showinfo("Success", "Student Details Successfully Updated", parent=self.root)
            conn.commit()
            self.fetch_data()
            conn.close()
        except Exception as es:
            messagebox.showerror("Error", f"Due to: {str(es)}", parent=self.root)

错误原因分析 #

1. 字段名语法问题：MySQL中，包含空格的字段名（如Roll No、Teacher Name、Phone No）必须用反引号(`)包裹才能被正确解析，直接书写会触发语法错误。
2. 布尔值判断错误：messagebox.askyesno()返回布尔值True/False，用Update > 0判断属于类型错误。
3. 逻辑执行范围问题：更新成功后的提交、数据刷新、连接关闭等操作被放在判断分支外，用户选择不更新时也会执行，可能引发异常。

修复后的代码 #

def update_data(self):
    global conn
    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 Student Details", parent=self.root)
            if Update:
                conn = mysql.connector.connect(host="localhost", user="root", password="08052002H@ck",
                                               database="face_recognizer")
                my_cursor = conn.cursor()
                # 带空格的字段名用反引号包裹
                my_cursor.execute(
                    "update student set Dep=%s, Course=%s, Year=%s, Sem=%s, Name=%s, Div=%s, `Roll No`=%s, Gender=%s, DOB=%s, Email=%s, `Teacher Name`=%s, `Phone No`=%s, Address=%s, Photo=%s where ID=%s",
                    (
                        self.var_dep.get(), self.var_course.get(), self.var_year.get(), self.var_semester.get(),
                        self.var_std_name.get(), self.var_div.get(), self.var_roll.get(), self.var_gender.get(),
                        self.var_dob.get(), self.var_email.get(), self.var_teacher.get(), self.var_phone.get(),
                        self.var_address.get(), self.var_radio1.get(), self.var_std_id.get()
                    ))
                conn.commit()
                self.fetch_data()
                messagebox.showinfo("Success", "Student Details Successfully Updated", parent=self.root)
                conn.close()
            else:
                return
        except Exception as es:
            messagebox.showerror("Error", f"Due to: {str(es)}", parent=self.root)
            # 异常时确保数据库连接关闭
            if 'conn' in locals() and conn.is_connected():
                conn.close()

修复说明 #

• 给带空格的字段名添加反引号包裹，解决MySQL语法解析错误。
• 将if Update > 0:改为if Update:，适配askyesno()返回的布尔值类型。
• 将更新成功后的提交、数据刷新、提示弹窗、连接关闭等操作移到if Update:分支内，确保仅在用户确认更新时执行。
• 添加异常分支中的连接关闭逻辑，避免数据库连接泄漏。

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