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!

The Assignment Nerd · Accepted Answer · 2021-04-24 12:29:09Z

29

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, 2021 at 12:29

The Assignment Nerd

32 bronze badges

answered Aug 22, 2012 at 22:41

Colin Dunklau

3,1111 gold badge24 silver badges20 bronze badges

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

2 Comments

user666412 Over a year ago

If someone tries to inject a query, won't the client fail if execute isn't called with multi=True in the unsafe approach ?

Colin Dunklau Over a year ago

@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--

Sanjay Rai · Accepted Answer · 2020-07-19 09:47:57Z

1

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

conn.commit()

answered Jul 19, 2020 at 9:47

Sanjay Rai

273 bronze badges

Comments

LSeu · Accepted Answer · 2022-12-02 12:28:14Z

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 2, 2022 at 12:28

LSeu

68010 silver badges18 bronze badges

answered Nov 30, 2022 at 2:23

user2104898

12 bronze badges

1 Comment

Antoine Over a year ago

If you have a new question, please ask it by clicking the Ask Question button. Include a link to this question if it helps provide context. - From Review

Collectives™ on Stack Overflow

python mysql delete statement not working

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related