Of course! Here is a comprehensive guide on how to update data in a MySQL database using Python, covering the best practices, security, and common scenarios.

We will use the mysql-connector-python library, which is the official driver from Oracle. The principles shown here are also applicable to other libraries like PyMySQL.

Prerequisites

Before you start, make sure you have:

1. Python installed on your system.

2. A MySQL server running and accessible.

   
   （图片来源网络，侵删）

3. A database and table to work with. Let's use this example for our code samples:

   -- Create a database (if it doesn't exist)
   CREATE DATABASE IF NOT EXISTS company_db;
   -- Use the database
   USE company_db;
   -- Create an 'employees' table
   CREATE TABLE IF NOT EXISTS employees (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100) NOT NULL,
       department VARCHAR(100),
       salary DECIMAL(10, 2)
   );
   -- Insert some sample data
   INSERT INTO employees (name, department, salary) VALUES
   ('Alice', 'Engineering', 90000.00),
   ('Bob', 'Marketing', 65000.00),
   ('Charlie', 'Engineering', 110000.00);

4. Install the MySQL Connector library:

   pip install mysql-connector-python

The Basic UPDATE Statement in Python

The core idea is to:

1. Establish a connection to the database.
2. Create a cursor object.
3. Execute an UPDATE SQL query.
4. Commit the transaction to save the changes.
5. Close the cursor and connection.

Here is a basic, functional example.

import mysql.connector
from mysql.connector import Error
def update_employee_salary(employee_id, new_salary):
    """Updates the salary of a specific employee."""
    connection = None
    cursor = None
    try:
        # Establish the database connection
        # Replace with your actual database credentials
        connection = mysql.connector.connect(
            host='localhost',
            database='company_db',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            # --- The SQL UPDATE statement ---
            # Use a WHERE clause to specify which row(s) to update.
            # Omitting the WHERE clause will update ALL rows!
            sql_update_query = "UPDATE employees SET salary = %s WHERE id = %s"
            # Data to be updated
            data = (new_salary, employee_id)
            # Execute the query
            cursor.execute(sql_update_query, data)
            # Commit the transaction to make the change permanent
            connection.commit()
            print(f"{cursor.rowcount} record(s) updated successfully.")
    except Error as e:
        print(f"Error while connecting to MySQL or updating data: {e}")
    finally:
        # Closing the cursor and connection
        if connection and connection.is_connected():
            if cursor:
                cursor.close()
            connection.close()
            print("MySQL connection is closed.")
# --- Example Usage ---
# Update Bob's salary (he has id = 2)
update_employee_salary(employee_id=2, new_salary=70000.00)

Explanation of Key Parts:

• sql_update_query = "UPDATE employees SET salary = %s WHERE id = %s": This is the SQL query.
  • UPDATE employees: Specifies the table to modify.
  • SET salary = %s: Specifies the column to change and its new value. We use a placeholder %s.
  • WHERE id = %s: This is critical. It ensures that only the row with the matching id is updated. If you forget this, you will update every employee's salary to the new value!
• data = (new_salary, employee_id): This is a tuple containing the values for the placeholders. The order must match the order of the %s placeholders in the query.
• cursor.execute(sql_update_query, data): This safely executes the query. The library handles the proper escaping of the data, preventing SQL injection.
• connection.commit(): This is a crucial step. In database transactions, changes are made in a temporary state. commit() makes the change permanent. If you were to roll back the transaction, the change would be discarded.
• cursor.rowcount: This attribute tells you how many rows were affected by the last execute() statement. It's useful for confirming that your WHERE clause matched a record.

Security: Preventing SQL Injection

Never format your queries by directly inserting variables into the string, like this:

# ❌ DANGEROUS - DO NOT DO THIS!
sql_query = f"UPDATE employees SET salary = {new_salary} WHERE id = {employee_id}"
cursor.execute(sql_query)

This makes your application vulnerable to SQL Injection attacks. The %s placeholder method is the standard, secure way to pass data to a query. The MySQL connector automatically sanitizes the input data.

Updating Multiple Columns

To update multiple columns, simply add them to the SET clause.

def update_employee_details(employee_id, new_name, new_department):
    """Updates the name and department of an employee."""
    connection = None
    cursor = None
    try:
        connection = mysql.connector.connect(
            host='localhost',
            database='company_db',
            user='your_username',
            password='your_password'
        )
        if connection.is_connected():
            cursor = connection.cursor()
            # SQL query to update multiple columns
            sql_update_query = "UPDATE employees SET name = %s, department = %s WHERE id = %s"
            # Data tuple with values in the correct order
            data = (new_name, new_department, employee_id)
            cursor.execute(sql_update_query, data)
            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():
            if cursor:
                cursor.close()
            connection.close()
            print("MySQL connection is closed.")
# --- Example Usage ---
# Update Charlie's name and department
update_employee_details(employee_id=3, new_name='Charles', new_department='Product')

Best Practice: Using a Context Manager (with statement)

Manually opening and closing connections and cursors can be tedious and error-prone. A better way is to use a context manager, which handles cleanup automatically.

Here is a more robust and reusable function using a with statement.

import mysql.connector
from mysql.connector import Error
def update_record_with_context_manager():
    """A more robust way to handle database connections."""
    # Configuration dictionary for better readability
    db_config = {
        'host': 'localhost',
        'database': 'company_db',
        'user': 'your_username',
        'password': 'your_password'
    }
    update_query = "UPDATE employees SET salary = %s WHERE name = %s"
    data_to_update = (95000.00, 'Alice') # Update Alice's salary
    try:
        # The 'with' statement ensures the connection is closed automatically
        with mysql.connector.connect(**db_config) as connection:
            if connection.is_connected():
                with connection.cursor() as cursor:
                    cursor.execute(update_query, data_to_update)
                    connection.commit()
                    print(f"{cursor.rowcount} record(s) updated successfully.")
    except Error as e:
        print(f"Error while connecting to MySQL: {e}")
# --- Example Usage ---
update_record_with_context_manager()

Why this is better:

• Automatic Resource Management: The with statement guarantees that connection.close() and cursor.close() are called, even if errors occur.
• Cleaner Code: It reduces the amount of boilerplate try...finally code.
• Readability: Separating the configuration into a dictionary makes the code cleaner and easier to manage.

Summary of Best Practices

1. Always use placeholders (%s) for your data to prevent SQL injection.
2. Always use a WHERE clause in your UPDATE statement unless you intentionally want to update every row.
3. Always commit() your transaction to save the changes to the database.
4. Always close your connections using try...finally blocks or, preferably, with statements.
5. Handle exceptions gracefully to avoid your program crashing due to database errors.
6. Check cursor.rowcount to verify that your update affected the expected number of rows. If it's 0, it means your WHERE clause didn't match any records.