MySQL 1064(42000)语法错误求助：尝试现有方案仍未解决

错误根源 #

你的代码中查询经销商名称是否重复的部分存在逻辑错误：

• search = cur.execute("SELECT * FROM dealer WHERE name = %s ") 这行里，cur.execute()返回的是受影响的行数（整数），并非SQL语句对象
• 后续调用cur.execute(search, name)相当于把整数当作SQL语句执行，直接触发了语法错误near '%s'

修正后的代码 #

def add(self):
    con = mysql.connector.connect(
        host="localhost",
        user="root",
        database="ims"
    )
    cur = con.cursor()
    try:
        if self.var_name.get() == "":
            messagebox.showerror("Error", "Name Must be required", parent=self.root)
            return  # 提前终止，避免后续无效执行
        
        name = self.var_name.get()
        # 直接执行SELECT语句并传入参数
        cur.execute("SELECT * FROM dealer WHERE name = %s", (name,))
        row = cur.fetchone()
        
        if row is not None:
            messagebox.showerror("Error", "This Dealer Name already assigned, try a different one", parent=self.root)
        else: 
            companyname = self.var_companyname.get()
            contact = self.var_contact.get()
            address1 = self.var_address1.get()
            address2 = self.var_address2.get()
            address3 = self.var_address3.get()
            
            add_sql = "INSERT INTO dealer (name, companyname, contact, address1, address2, address3) VALUES(%s,%s,%s,%s,%s,%s)"
            cur.execute(add_sql, (name, companyname, contact, address1, address2, address3))
            con.commit()
            
            messagebox.showinfo("Success","Dealer Added Successfully", parent=self.root)    
            self.show()
            self.clear()
    except Exception as ex:
        messagebox.showerror("Error",f"Error due to : {str(ex)}", parent=self.root)
    finally:
        # 确保游标和连接始终被关闭，避免资源泄漏
        cur.close()
        con.close()

额外优化说明 #

• 添加return语句，在名称为空时直接终止函数，减少无效代码执行
• 用finally块统一管理游标和连接的关闭操作，避免异常场景下的资源泄漏
• 修正拼写错误："Dealer Addedd Successfully" → "Dealer Added Successfully"

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