Why Doesn't My Python Script Update My MySQL Database Despite a Success Message?

MySQL and Python: Database Update Anomaly

Problem:

A Python script is failing to update a row in a MySQL database, despite executing a "UPDATE" command and retrieving a success message. However, when manually querying the database via the CLI, the row remains unchanged.

Code Snippet:

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

Output:

4
Number of rows updated: 1

Observations:

• The script executes the "UPDATE" command and reports a successful update.
• However, querying the database via the CLI reveals that the row has not been updated.
• Executing the "UPDATE" command directly from the CLI updates the row as expected.

Solution:

The issue is likely related to missing transaction handling. MySQLdb disables autocommit by default, which means that any changes to the database are not persisted until a commit is explicitly issued.

To resolve the problem, add the following line before closing the connection:

conn.commit()

This will force the changes to be committed to the database, ensuring that the row is updated as intended.

The above is the detailed content of Why Doesn't My Python Script Update My MySQL Database Despite a Success Message?. For more information, please follow other related articles on the PHP Chinese website!