python mysql delete statement not working

Question

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!

score 28 · Accepted Answer · edited Apr 24 '21 at 12:29

28

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

edited Apr 24 '21 at 12:29

The Assignment Nerd

3
2

answered Aug 22 '12 at 22:41

Colin Dunklau

3,001
1
20
19

If someone tries to inject a query, won't the client fail if execute isn't called with `multi=True` in the unsafe approach ? – user666412 Mar 18 '16 at 18:54
1

@user666412 Always do it the right way and you won't allow anyone to play with your query logic. Consider the above unsafe method, where the id is `123' OR TRUE--` – Colin Dunklau Mar 18 '16 at 20:27

score 1 · Answer 2 · answered Jul 19 '20 at 09:47

1

cursor.execute("DELETE FROM maillist_subscription WHERE id = '"+id+"'")

conn.commit()

answered Jul 19 '20 at 09:47

Sanjay Rai

27
3

score 0 · Answer 3 · edited Dec 02 '22 at 12:28

0

I am trying to execute the following Redshift SQL in a Python script, but records are not being deleted. There is no error, either.

sql_del = "DELETE FROM table_name where id in (select id from table2)"
cursor.execute(sql_del)
conn.commit()`

edited Dec 02 '22 at 12:28

LSeu

630
7
17

answered Nov 30 '22 at 02:23

user2104898

1
1

If you have a new question, please ask it by clicking the [Ask Question](https://stackoverflow.com/questions/ask) button. Include a link to this question if it helps provide context. - [From Review](/review/late-answers/33301559) – Antoine Dec 04 '22 at 09:10

python mysql delete statement not working

3 Answers3