Of course! Here is a comprehensive guide on how to update data in a MySQL database using Python. We'll cover the most common and recommended method using the mysql-connector-python library.

Summary of Methods

Method 1: Using mysql-connector-python (Recommended)

This is the official driver from Oracle, making it a reliable choice.

Step 1: Install the Library

First, you need to install the library. Open your terminal or command prompt and run:

pip install mysql-connector-python

Step 2: Connect to the Database

You'll need your database connection details:

• Hostname (e.g., localhost or your server IP)
• Username
• Password
• Database Name

Step 3: Write the Python Code to Update

The key to safely updating data is to use parameterized queries. This prevents SQL injection attacks and handles data types correctly.

Here is a complete, step-by-step example.

import mysql.connector
from mysql.connector import Error
def update_record():
    """Connects to MySQL and updates a record in the 'employees' table."""
    # --- Database Connection Details ---
    # IMPORTANT: Replace with your actual database details
    db_config = {
        'host': 'localhost',
        'user': 'your_username',
        'password': 'your_password',
        'database': 'your_database'
    }
    connection = None  # Initialize connection to None
    cursor = None      # Initialize cursor to None
    try:
        # 1. Establish a connection to the database
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            print("Successfully connected to the database")
            # 2. Create a cursor object
            # A cursor is used to execute SQL commands
            cursor = connection.cursor()
            # --- The UPDATE Statement ---
            # We use a placeholder %s for the value we want to update.
            # This is a parameterized query, which is crucial for security.
            sql_update_query = "UPDATE employees SET salary = %s WHERE id = %s"
            # 3. Define the data to be updated
            # The data must be in a tuple, in the same order as the placeholders
            new_salary = 75000
            employee_id_to_update = 101
            # 4. Execute the query
            # The execute method takes the query and a tuple of parameters
            cursor.execute(sql_update_query, (new_salary, employee_id_to_update))
            # 5. Commit the transaction to make the change permanent
            # Without commit(), the changes are only temporary and will be rolled back.
            connection.commit()
            print(f"{cursor.rowcount} record(s) updated successfully.")
    except Error as e:
        # If an error occurs, print it
        print(f"Error while connecting to MySQL or updating data: {e}")
    finally:
        # 6. Close the cursor and connection
        # This is very important to free up resources
        if connection and connection.is_connected():
            if cursor:
                cursor.close()
            connection.close()
            print("MySQL connection is closed")
# --- Run the function ---
if __name__ == "__main__":
    update_record()

Breakdown of the Code

1. `mysql.connector.connect(db_config)**: This establishes the connection. The**` unpacks the dictionary into keyword arguments.
2. connection.cursor(): Creates a cursor object, which acts as a pointer to execute commands.
3. sql_update_query = "UPDATE employees SET salary = %s WHERE id = %s": This is our SQL command.
   • UPDATE employees: Specifies the table to modify.
   • SET salary = %s: Specifies the column to change and uses a placeholder (%s) for the new value.
   • WHERE id = %s: This is critical. It specifies which row(s) to update. If you omit the WHERE clause, you will update every single row in the table! Use the primary key (like id) to target a specific record.
4. cursor.execute(sql_update_query, (new_salary, employee_id_to_update)): This is the safe way to execute the query. It takes the SQL string and a tuple of values. The driver safely substitutes the %s placeholders with the values from the tuple.
5. connection.commit(): This saves the changes to the database. All changes are held in a transaction until commit() is called.
6. cursor.rowcount: This attribute tells you how many rows were affected by the last execute() call. It's useful for confirming the update worked as expected.
7. finally block: This block always runs, whether an error occurred or not. It's the perfect place to close your cursor and connection to prevent resource leaks.

Advanced Scenarios

Updating Multiple Rows with Different Values

You can update multiple rows in a single query by providing a list of tuples to executemany().

Let's say you want to give all employees in the 'Sales' department a 5% raise.

import mysql.connector
from mysql.connector import Error
def update_multiple_records():
    db_config = {
        'host': 'localhost',
        'user': 'your_username',
        'password': 'your_password',
        'database': 'your_database'
    }
    try:
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            cursor = connection.cursor()
            # Get the IDs and current salaries of all sales employees
            cursor.execute("SELECT id, salary FROM employees WHERE department = 'Sales'")
            sales_employees = cursor.fetchall() # This returns a list of tuples: [(id1, sal1), (id2, sal2), ...]
            # Prepare the data for executemany
            # Each inner tuple is a set of parameters for one UPDATE statement
            data_to_update = []
            for emp_id, current_salary in sales_employees:
                new_salary = int(current_salary * 1.05) # 5% raise
                data_to_update.append((new_salary, emp_id))
            # The SQL query template
            sql_update_query = "UPDATE employees SET salary = %s WHERE id = %s"
            # Execute the query for all records in the list
            cursor.executemany(sql_update_query, data_to_update)
            connection.commit()
            print(f"{cursor.rowcount} record(s) updated successfully.")
    except Error as e:
        print(f"Error: {e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")
# --- Run the function ---
if __name__ == "__main__":
    update_multiple_records()

Updating Based on a Subquery

Sometimes you need to update rows based on data from another table. You can do this directly in the SQL statement.

Example: Promote all employees who have a higher salary than the average salary of their department.

import mysql.connector
from mysql.connector import Error
def update_with_subquery():
    db_config = {
        'host': 'localhost',
        'user': 'your_username',
        'password': 'your_password',
        'database': 'your_database'
    }
    try:
        connection = mysql.connector.connect(**db_config)
        if connection.is_connected():
            cursor = connection.cursor()
            # Update employees whose salary is above the average for their department
            sql_update_query = """
                UPDATE employees 
                SET job_title = 'Senior ' || job_title 
                WHERE salary > (
                    SELECT AVG(salary) 
                    FROM employees AS dept_avg 
                    WHERE dept_avg.department = employees.department
                )
            """
            # Note: The concatenation operator is `||` in standard SQL. 
            # For MySQL specifically, you should use CONCAT():
            # sql_update_query = """
            #     UPDATE employees 
            #     SET job_title = CONCAT('Senior ', job_title) 
            #     WHERE salary > (...)
            # """
            cursor.execute(sql_update_query)
            connection.commit()
            print(f"{cursor.rowcount} record(s) updated successfully.")
    except Error as e:
        print(f"Error: {e}")
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")
# --- Run the function ---
if __name__ == "__main__":
    update_with_subquery()

Best Practices

1. Always Use Parameterized Queries (%s): Never format your SQL strings with f-strings or the operator. This is the #1 rule for preventing SQL injection.

   # WRONG (Vulnerable to SQL Injection)
   sql = f"UPDATE users SET name = '{user_input}' WHERE id = {user_id}"
   # RIGHT (Secure)
   sql = "UPDATE users SET name = %s WHERE id = %s"
   cursor.execute(sql, (user_input, user_id))

2. Handle Errors Gracefully: Use try...except Error to catch potential database connection or query errors.

3. Always Close Connections: Use a finally block to ensure your database connections and cursors are always closed, even if an error occurs.

4. Commit Transactions: Remember to call connection.commit() after making changes (INSERT, UPDATE, DELETE). Otherwise, the changes will not be saved.