-3

I'm coding a program that is about the management of the products in a grocery store. I coded a couple of part of my program but I have started getting an error. As I said on the title, I have a button that is in charge of deleting the selected data. And the error the program gives me: Sometimes: no such column: SALMON Sometimes: no such column: COLA

That's exactly the error I'm getting. I checked my codes a lot of times but I cannot find the reason why this is being happened. I'm gonna give you the part of my program that is in charge of deleting the selected data below because of my codes are too long.

Here are my codes:

    def DeleteTheProduct(self):
        question = askyesno("","ARE YOU SURE YOU WANT TO DELETE THE PRODUCT?")
        if question:
            selected_product = self.ui2.ProductTable.selectedItems()
            product_to_be_deleted = selected_product[1].text()
            try:
                self.curs.execute("DELETE FROM Products WHERE productname=%s"%(product_to_be_deleted))
                self.conn.commit()
                self.interface2()
                self.ui2.statusbar.showMessage("THE PRODUCT HAS BEEN DELETED.",10000)
            except Exception as error:
                print(error)
        else:
            pass
Bugra Pakir
  • 15
  • 6
  • Well, for starters you should ***never*** use string formatting for database queries, since it's an extremely serious security issue. Instead, use the two-arguments query syntax `execute('... WHERE xyz=?', (value, ...))`. – musicamante Jul 26 '22 at 10:28

1 Answers1

1

There is an error in SQL syntax. You need to quote productname in your query:

"DELETE FROM Products WHERE productname='%s'"

otherwise, it tries to find rows where productname column equals with column with given name, but they doesn't exist

svfat
  • 3,273
  • 1
  • 15
  • 34
  • 3
    Using quotes will only partially solve the problem. The fact is that string formatting should **never** be used for database queries. – musicamante Jul 26 '22 at 10:31
  • [Official docs](https://docs.python.org/3/library/sqlite3.html#) recommend that you use one of two syntaxes for parameter substitution instead of Python string formatting functions. – bfris Jul 26 '22 at 20:18