Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

python mysql delete statement not working

here I am trying to remove any users which containt a " in their email/username.

    def removeQuote(self, tbl,record):
            """ Updates the record """
            statmt="select id from %s WHERE `email` LIKE '%%\"%%'" % (tbl)
            self.cursor.execute(statmt)
            rows=list(self.cursor.fetchall())
            for idx, val in enumerate(rows):
                    id= val[0]
                    delstatmt = "DELETE FROM `maillist_subscription` WHERE id = '%s'" % id
                    print delstatmt
                    self.cursor.execute(delstatmt)

The output of this shows as if the action completed successfully, but the record remains in the database. Output also shows a correct mysql statement:

DELETE FROM `maillist_subscription` WHERE id = '8288754'

Thanks for all your help!

like image 945
Cmag Avatar asked Aug 22 '12 22:08

Cmag


People also ask

How do you delete a statement in Python?

The del keyword in python is primarily used to delete objects in Python. Since everything in python represents an object in one way or another, The del keyword can also be used to delete a list, slice a list, delete a dictionaries, remove key-value pairs from a dictionary, delete variables, etc.

How do I delete a row in MySQL?

To delete rows in a MySQL table, use the DELETE FROM statement: DELETE FROM products WHERE product_id=1; The WHERE clause is optional, but you'll usually want it, unless you really want to delete every row from the table.


1 Answers

You need to commit the change, using the commit() method on the connection object. Most DBAPI interfaces use implicit transactions.

Also, don't use string formatting for SQL query generation! It will open you up to SQL injections:

UNSAFE!!

# What happens if id = "1'; DROP DATABASE somedb" ?
delstatmt = "DELETE FROM `maillist_subscription` WHERE id = '%s'" % (id,)
cursor.execute(delstatmt)
conn.commit()

SAFE!

delstatmt = "DELETE FROM `maillist_subscription` WHERE id = ?"
cursor.execute(delstatmt, (id,))
conn.commit()
like image 186
Colin Dunklau Avatar answered Sep 17 '22 16:09

Colin Dunklau