I cannot delete a data from my database in Python, SQLite3 [duplicate]

Question

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

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
– musicamante, Commented Jul 26, 2022 at 10:28

svfat · Accepted Answer · 2022-07-26 10:28:11Z

2

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

answered Jul 26, 2022 at 10:28

svfat

3,3931 gold badge18 silver badges34 bronze badges

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

2 Comments

musicamante Over a year ago

Using quotes will only partially solve the problem. The fact is that string formatting should never be used for database queries.

bfris Over a year ago

Official docs recommend that you use one of two syntaxes for parameter substitution instead of Python string formatting functions.

Collectives™ on Stack Overflow

I cannot delete a data from my database in Python, SQLite3 [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related