Updating records in a MySQL table is a common task when building applications that manage data. In this tutorial, you'll learn how to use Python to update existing records in a MySQL database using the mysql-connector-python library.

Table of Contents

1. What is the UPDATE Statement?

2. Prerequisites

3. Install MySQL Connector

4. Connect to MySQL Using Python

5. Update a Single Record

6. Update Multiple Records

7. Use Variables in Update Queries

8. Full Working Example

9. Tips and Common Pitfalls

✅ 1. What is the UPDATE Statement?

The UPDATE SQL statement is used to modify existing data in one or more rows of a table.

Syntax:

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

⚠️ Always use a WHERE clause to avoid updating all rows in the table.

⚙️ 2. Prerequisites

Before you begin, make sure you have:

• Python installed

• MySQL Server running

• A MySQL database and a table with data

• The mysql-connector-python library installed

3. Install MySQL Connector

Install the official MySQL connector using pip:

pip install mysql-connector-python

4. Connect to MySQL Using Python

import mysql.connector

db = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

cursor = db.cursor()

5. Update a Single Record

To update a single record:

sql = "UPDATE users SET age = %s WHERE id = %s"
val = (28, 3)

cursor.execute(sql, val)
db.commit()

print(cursor.rowcount, "record(s) updated")

Explanation:

• %s is a placeholder for parameters.

• val is a tuple with the new age and the user ID.

• db.commit() saves the changes to the database.

6. Update Multiple Records with Conditions

Update multiple rows that match a condition:

sql = "UPDATE users SET city = %s WHERE country = %s"
val = ("Toronto", "Canada")

cursor.execute(sql, val)
db.commit()

print(cursor.rowcount, "record(s) updated")

This updates the city for all users in Canada.

7. Use Variables Dynamically in Queries

You can create dynamic queries with user input:

user_id = 4
new_email = "[email protected]"

sql = "UPDATE users SET email = %s WHERE id = %s"
val = (new_email, user_id)

cursor.execute(sql, val)
db.commit()

print("Email updated successfully.")

8. Full Working Example

import mysql.connector
from mysql.connector import Error

def update_user_email(user_id, new_email):
    try:
        db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="your_password",
            database="mydatabase"
        )

        cursor = db.cursor()

        sql = "UPDATE users SET email = %s WHERE id = %s"
        val = (new_email, user_id)

        cursor.execute(sql, val)
        db.commit()

        print(f"{cursor.rowcount} record(s) updated.")

    except Error as e:
        print("Error updating data:", e)

    finally:
        if db.is_connected():
            db.close()
            print("Database connection closed.")

# Call the function
update_user_email(2, "[email protected]")

⚠️ 9. Tips and Common Pitfalls

✅ Best Practices

• Always use WHERE to target specific rows.

• Use placeholders (%s) to prevent SQL injection.

• Check cursor.rowcount to see how many records were affected.

• Test queries in MySQL first before using them in Python.

Summary Table

Final Thoughts

Updating MySQL tables using Python is a fundamental task when working with databases. By using parameterized queries and committing changes properly, you can safely and efficiently update your data. Always back up your data and test your queries before applying them to production.