What is Python commit() method in MySQL?

The commit() method in Python's MySQL connector is essential for confirming database changes. When you perform INSERT, UPDATE, or DELETE operations, these changes exist only in memory until you call commit() to save them permanently to the database.

Syntax

db.commit()

Where db refers to the database connection object created using mysql.connector.connect().

Why commit() is Required

MySQL uses transaction control to ensure data integrity. Without calling commit(), your changes remain as pending transactions and will be lost when the connection closes.

Steps to Update Data with commit()

Follow these steps to safely update MySQL data using Python ?

• Import MySQL connector
• Establish database connection
• Create cursor object
• Execute UPDATE/INSERT/DELETE query
• Call commit() to save changes
• Close the connection

Example

Let's update a student's city in our database and commit the changes ?

Sample Students Table:

+--------+-------+----------+-------+
| Name   | Class | City     | Marks |
+--------+-------+----------+-------+
| Karan  |   4   | Amritsar |  95   |
| Sahil  |   6   | Amritsar |  93   |
| Kriti  |   3   | Batala   |  88   |
| Khushi |   9   | Delhi    |  90   |
| Kirat  |   5   | Delhi    |  85   |
+--------+-------+----------+-------+

import mysql.connector

# Establish connection
db = mysql.connector.connect(
    host="localhost",
    user="your_username", 
    password="your_password",
    database="school_db"
)

cursor = db.cursor()

# Update Kriti's city from Batala to Kolkata
query = "UPDATE Students SET City='Kolkata' WHERE Name='Kriti'"
cursor.execute(query)

# Commit the changes - THIS IS CRUCIAL!
db.commit()

# Verify the update
cursor.execute("SELECT * FROM Students")
for row in cursor:
    print(row)

db.close()

Output after committing the changes ?

('Karan', 4, 'Amritsar', 95)
('Sahil', 6, 'Amritsar', 93)
('Kriti', 3, 'Kolkata', 88)
('Khushi', 9, 'Delhi', 90)
('Kirat', 5, 'Delhi', 85)

What Happens Without commit()

If you remove db.commit() from the above code, the UPDATE operation will execute successfully, but changes won't be saved permanently. The database will revert to its original state once the connection closes.

Best Practices

• Always call commit() after data modification operations
• Use try-except blocks to handle errors and rollback if needed
• Commit after related operations to group logical changes together

Conclusion

The commit() method is essential for making database changes permanent in MySQL. Without it, your INSERT, UPDATE, and DELETE operations will be lost when the connection closes, ensuring data integrity through transaction control.