CODEWITHSUNDEEP
pythonsqltransactional
Cmag
Cmag

Reputation: 15750

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!

Upvotes: 15

Views: 29170

Answers (3)

user2104898
user2104898

Reputation: 1

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

Upvotes: 0

Colin Dunklau
Colin Dunklau

Reputation: 3111

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

Upvotes: 29

Sanjay Rai
Sanjay Rai

Reputation: 27

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

conn.commit()

Upvotes: 1

Related Questions