MySQL and Python

Question

I'm having some troubles updating a row in a MySQL db. Here is the code I'm trying to run:

import MySQLdb

conn=MySQLdb.connect(host="localhost", user="root", passwd="pass", db="dbname")
cursor=conn.cursor()

cursor.execute("UPDATE compinfo SET Co_num=4 WHERE ID=100")
cursor.execute("SELECT Co_num FROM compinfo WHERE ID=100")
results = cursor.fetchall()

for row in results:
    print row[0]

print "Number of rows updated: %d" % cursor.rowcount

cursor.close()
conn.close()

The output I get when I run this program is:

4
Number of rows updated: 1

It seems like its working but if I query the MySQL db from a MySQL Command Line I find that db was not updated at all. If however from the MySQL Command Line I enter "UPDATE compinfo SET Co_num=4 WHERE ID=100;" (without the quotes) the database is updated as expected. Do any of you have any suggestions as to what my problem may be? I'm running Python 2.5.2 with MySQL 5.1.30 on a windows box. Thanks for any help.

Answer 1

I am not certain, but I am going to guess you are using a INNODB table, and you haven't done a commit. I believe MySQLdb enable transactions automatically.

Try putting a conn.commit() in there before your close.

---

Update

Confirmation from the FAQ: My data disappeared

Answer 2

You need to commit changes manually or turn auto-commit on.

The reason SELECT returns the modified (but not persisted) data is because the connection is still in the same transaction.

Answer 3

I've found that Python's connector automatically turns autocommit off, and there doesn't appear to be any way to change this behaviour. Of course you can turn it back on, but then looking at the query logs, it stupidly does two pointless queries after connect to turn autocommit off then back on.

Answer 4

MySQLdb has autocommit off by default, which may be confusing at first. Your connection exists in its own transaction and you will not be able to see the changes you make from other connections until you commit that transaction.

You can either do conn.commit() after the update statement as other have pointed out, or disable this functionality altogether by setting conn.autocommit(True) right after you create the connection object.

Answer 5

I did a lot of work with MySQL database in Python and I used to always forget to commit the query. SO Zoredashe is correct.