blcArmadillo blcArmadillo - 1 year ago 94
Python Question

Database does not update automatically with MySQL and Python

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

import MySQLdb

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

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


The output I get when I run this program is:

Number of rows updated: 1

It seems like it's working but if I query the database from the MySQL command line interface (CLI) I find that it was not updated at all. However, if from the CLI I enter
UPDATE compinfo SET Co_num=4 WHERE ID=100;
the database is updated as expected.

What is my problem? I'm running Python 2.5.2 with MySQL 5.1.30 on a Windows box.

Answer Source

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.

Call conn.commit() before calling close.

From the FAQ: Starting with 1.2.0, MySQLdb disables autocommit by default

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download