Not Working filter search using python and mysql

Question

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()

Are you using Python 2 or 3? Why do you have both tags? Python 2.x is EOL. — Barmar
– Barmar, Commented Feb 5, 2022 at 8:21
You have an extra Like in your query after str(search.get()) — Barmar
– Barmar, Commented Feb 5, 2022 at 8:22

Barmar · Accepted Answer · 2022-02-05 08:46:02Z

2

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()

edited Feb 5, 2022 at 8:46

answered Feb 5, 2022 at 8:26

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Barmar Over a year ago

What do you expect to happen? You only display results when len(result) is not 0.

Barmar Over a year ago

You display "No such record found" in the exception handler. But there's no exception when a query returns 0 rows. You should do it in the else: for if len(result) == 0:

Barmar Over a year ago

What do you mean by wrong data?

SOLO Over a year ago

stackoverflow.com/q/71094412/18119310 can you plzz help me on this one

Collectives™ on Stack Overflow

Not Working filter search using python and mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related