MYSQL 1064(42000) Qustions-CodePudding

I have a problem with "1064(42000): You have an error in you SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s' at line.". Even though there are solutions already, I tried and it's still not working.

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)
        else:
            name = self.var_name.get()
            search = cur.execute("SELECT * FROM dealer WHERE name = %s ")
            cur.execute(search, name)
            row= cur.fetchone()
            if row!=None:
                messagebox.showerror("Error", "This Dealer Name already assigned, try a different one", parent = self.root)
            else: 
                name = self.var_name.get()
                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 = ("INSERT INTO dealer (name, companyname, contact, address1, address2, address3) VALUES(%s,%s,%s,%s,%s,%s)")
                cur.execute(add,(name, companyname, contact, address1, address2, address3))
                con.commit()
                messagebox.showinfo("Success","Dealer Addedd Successfully", parent=self.root)    
                self.show()
                self.clear()
                cur.close()
                con.close()
    except Exception as ex:
        messagebox.showerror("Error",f"Error due to : {str(ex)}", parent = self.root)

CodePudding user response：

You shouldn't call cur.execute() when setting search. The variable should be set to the SQL string, then you use that when calling cur.execute() on the next line.

You also have to put the parameters in a list or tuple.

            search = "SELECT * FROM dealer WHERE name = %s "
            cur.execute(search, (name, ))