Why Aren't My MySQL Database Updates with Python Reflecting in the Database?

Troubleshooting Database Updates with MySQL and Python

Your issue arises when updating a MySQL database using Python's MySQLdb library. The following code is being executed:

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()

Upon running this code, it appears successful, indicating that the database has been updated. However, subsequent querying from the MySQL command line interface reveals that the update was not applied.

Cause

The issue lies in the lack of a commit operation. MySQLdb enables transactions by default, and updates require an explicit commit to make them permanent. Without a commit, changes are not saved to the database.

Solution

To resolve this, include conn.commit() before closing the connection, as seen below:

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

**conn.commit()**  # Commit changes to database

cursor.close()
conn.close()

By incorporating conn.commit(), the changes made to the database will be successfully persisted.

The above is the detailed content of Why Aren't My MySQL Database Updates with Python Reflecting in the Database?. For more information, please follow other related articles on the PHP Chinese website!