Python MySQL - SELECTs work but not DELETEs?

Question

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?

yes be sure that the user you are using has the rights to delete rows. — Miha Hribar, Sep 20 '09 at 19:30
When you say "DOES NOT SEEM TO WORK": how do you know? Do you get some kind of error message? If so, which one? Please report all details upfront. — Martin v. Löwis, Sep 20 '09 at 19:32
Apologies. By does not seem to work, I mean that if I run SELECT * FROM users; at the mysql console, I still see the row there. There is no error message at all. The user has full privileges. — , Sep 20 '09 at 19:45

score 13 · Accepted Answer · edited May 23 '17 at 12:25

13

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: Python MySQLdb update query fails

edited May 23 '17 at 12:25

Community

1
1

answered Sep 20 '09 at 20:14

Bill Karwin

538,548
86
673
828

Bingo! This is the Python-specific thing I was looking for, because the above would have worked in straight up PHP or Ruby with no problem. Thanks guys! – Sep 25 '09 at 00:01
broke my head with this yesterday... took a while to figure out why. Do you think there is a performance gain of committing everything at once? ie, instead of deleting for every instance in a for loop, committing at the end before close? – Cmag Jan 02 '12 at 19:15
1

Yes, there's definitely a performance gain. For example, the default config for InnoDB does an `fsync()` after every transaction commit. See http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit – Bill Karwin Jan 02 '12 at 19:35

score 1 · Answer 2 · answered Sep 22 '09 at 01:22

1

Perhaps you are violating a foreign key constraint.

answered Sep 22 '09 at 01:22

recursive

83,943
34
151
241

score 0 · Answer 3 · answered Nov 02 '12 at 06:14

0

The problem might be that you are not committing the changes. it can be done by conn.commit()

Python MySQL - SELECTs work but not DELETEs?

4 Answers4

Linked