Not Working filter search using python and mysql

February 5, 2022

it is showing me no record after writing the 1, I have data on my database for 1 can someone please help me to know where I am wrong here!

    def searchDB():
        try:
           sqlCon = mysql.connect(host="localhost",user="root",password="*********",database="mydatabase") 
           cur =sqlCon.cursor ()
           cur.execute ("select categoryname from category where "+ str(searchby.get())+ "Like '%" + str(search.get()) + "Like '%'")
           result = cur.fetchall ()
           if len(result) !=0:
              self.category_records.delete(*self.category_records.get_children())
              for row in result:
                  self.category_records.insert('',END,values =row)
           sqlCon.commit()
           sqlCon.close()
          
     
        except:
           tkinter.messagebox.showinfo("Data Search Form", "No such record Found")
           Reset()

        sqlCon.close()

>Solution :

You have an extra Like after str(search.get()) in the query. So if the user types foo, it will search for fooLike, which doesn’t exist.

You’re also missing a space before the first LIKE.

You shouldn’t concatenate the search string directly into the query, that’s wide open for SQL injection. You should use parameters in the query.

cur.execute ("select categoryname from category where "+ searchby.get() + " Like %s", ('%' + search.get() + '%',)

There’s no need to call str() around these .get() calls, because you’re getting from text variables.

You should display the message about no matching results found in the else: for the if that tests the length. except: only runs when there’s an error.

    def searchDB():
        try:
           sqlCon = mysql.connect(host="localhost",user="root",password="*********",database="mydatabase") 
           cur =sqlCon.cursor ()
           cur.execute ("select categoryname from category where "+ searchby.get() + " Like %s", ('%' + search.get() + '%',)
           result = cur.fetchall ()
           if len(result) !=0:
              self.category_records.delete(*self.category_records.get_children())
              for row in result:
                  self.category_records.insert('',END,values =row)
           else:
               tkinter.messagebox.showinfo("Data Search Form", "No such record Found")
               Reset()
           sqlCon.commit()          
     
        except Exception as e:
           tkinter.messagebox.showinfo("Data Search Form", "Error during search " + str(e))
           Reset()

        sqlCon.close()