10

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?

Improve this question
4
  • 3
    what privileges do you have on the databases? Commented Sep 20, 2009 at 19:26
  • yes be sure that the user you are using has the rights to delete rows. Commented Sep 20, 2009 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. Commented Sep 20, 2009 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. Commented Sep 20, 2009 at 19:45

4 Answers 4

Reset to default
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

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

3 Comments

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!
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?
Yes, there's definitely a performance gain. For example, the default config for InnoDB does an fsync() after every transaction commit. See dev.mysql.com/doc/refman/5.5/en/…
1

Perhaps you are violating a foreign key constraint.

Improve this answer

Comments

0

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.

Improve this answer

Comments

0

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

read more on this here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.