Updating Records in MySQL Using Python

This document explains how to update existing records in a MySQL database using Python. It includes steps for connection setup, cursor creation, writing update queries, executing them safely, committing changes, and closing the connection properly. A real working example is also provided.

Install MySQL Connector

To work with MySQL in Python, install the MySQL connector package: pip install mysql-connector-python

This library allows Python to interact with MySQL databases.

Establish Connection To update any record, Python must first connect to the MySQL database. Use mysql.connector.connect() and provide:

host

user

password

database

If the connection succeeds, Python will be able to run SQL queries inside that database.

Create a Cursor

A cursor is required to execute SQL commands. Example: cursor = connection.cursor()

Write UPDATE Query

An UPDATE command is used to modify existing records. A basic query looks like: UPDATE table_name SET column1 = value1 WHERE condition;

The WHERE condition is extremely important. Without it, all rows in the table may get updated accidentally.

Example: UPDATE students SET age = %s WHERE id = %s

Execute the Query Use cursor.execute(query, values) to run the update operation. Example values: values = (21, 1) This means: set age = 21 where id = 1

Commit the Changes MySQL does not save changes permanently until commit() is called. Use: connection.commit()

Without calling commit(), the updated value will not be saved.

Close the Connection After completing all operations, close the cursor and the database connection: cursor.close() connection.close()

This is a good practice to free resources and avoid leaving open connections.

Complete Example Code

import mysql.connector

try: connection = mysql.connector.connect( host="localhost", user="root", password="yourpassword", database="testdb" )

if connection.is_connected(): print("Connected to MySQL database")

cursor = connection.cursor()

query = "UPDATE students SET age = %s WHERE id = %s"
values = (23, 1)

cursor.execute(query, values)
connection.commit()

print("Record updated successfully")

except mysql.connector.Error as error: print("Error:", error)

finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection closed")

Best Practices

Always use WHERE clause for targeted updates

Use parameterized queries (%s) to prevent SQL injection

Wrap code in try–except blocks for safe error handling

Use commit() after any insert, update, or delete operation

Close the connection inside a finally block for reliability

This README provides a complete guide to updating records in MySQL using Python in a clean and structured manner.