Python messagebox exception handling

Question

I have been working on a python project using sqlite3, which allows the user to update, delete, and search for a data in database. For the selection process, the user is asked to choose the column name from the database table according to which selection would be done. I am choosing the column and typing the value for which I want to search, but the program message box shows exceptional error.T he database name is criminal_records.

How should I update the code to cope with the exception?

def search_data(self): 
    if self.var_search.get()=="":
        messagebox.showerror('Error','Please enter a search term')
    else:
        try:
            conn=sqlite3.connect('criminal_records.db')
            my_cursor=conn.cursor()
            my_cursor.execute("SELECT * FROM criminal_records WHERE "+str(self.var_com_search.get())+" LIKE '%"+str(self.var_search.get()+"'%"))
            rows=my_cursor.fetchall()
  
            if len(rows)!= 0:
                self.criminal_table.delete(*self.criminal_table.get_children())
                for i in rows:
                    self.criminal_table.insert('',END,values=i)
            conn.commit()
            conn.close()
        except Exception as es:
            messagebox.showerror('Error',f'Due to{str(es)}')    

Answer 1

If you save the final SQL statement to a variable and print it out:

sql = "SELECT * FROM criminal_records WHERE "+str(self.var_com_search.get())+" LIKE '%"+str(self.var_search.get()+"'%")
print(sql)

You will get (based on your provided image):

SELECT * FROM criminal_records WHERE Case ID LIKE '%12789250'%

The above SQL statement has two issues:

• column name Case ID not quoted
• last % is not within the single quoted string

It is better to use placeholder for the search string (note that you cannot use placeholder on column name) and embed the column name within double-quotes:

sql = f"""SELECT * FROM criminal_records WHERE "{self.var_com_search.get()}" LIKE ?"""
my_cursor.execute(sql, (f"%{self.var_search.get()}%",))

Note that the value of self.var_com_search comes from pre-defined values of a combobox widget which does not cause SQL injection.