Python MySQL - SELECTs work but not DELETEs?

Question

Hey -- I'm new to Python and Python's MySQL adapter. I'm not sure if I'm missing something obvious here:

db = MySQLdb.connect(# db details omitted)
cursor = self.db.cursor()

# WORKS
cursor.execute("SELECT site_id FROM users WHERE username=%s", (username))
record = cursor.fetchone()

# DOES NOT SEEM TO WORK
cursor.execute("DELETE FROM users WHERE username=%s", (username))

Any ideas?

Answer 1

I'd guess that you are using a storage engine that supports transactions (e.g. InnoDB) but you don't call db.commit() after the DELETE. The effect of the DELETE is discarded if you don't commit.

See http://mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away:

Starting with 1.2.0, MySQLdb disables autocommit by default, as required by the DB-API standard (PEP-249). If you are using InnoDB tables or some other type of transactional table type, you'll need to do connection.commit() before closing the connection, or else none of your changes will be written to the database.

See also this similar SO question: http://stackoverflow.com/questions/1028671/python-mysqldb-update-query-fails

Answer 2

To your code above, just add a call to self.db.commit().

The feature is far from an annoyance:

It saves you from data corruption issues when there are errors in your queries.

Answer 3

Perhaps you are violating a foreign key constraint.