封装mysql批量更新方法

import mysql.connector

def batch_update(table_name, data, id_column):
    """
    批量更新 MySQL 表中的记录

    :param table_name: 表名
    :param data: 包含更新数据的列表，每个元素是一个字典，包含 id 和要更新的字段
    :param id_column: 主键列名
    """
    if not data:
        return

    # 连接数据库
    conn = mysql.connector.connect(
        host="your_host",
        user="your_user",
        password="your_password",
        database="your_database"
    )
    cursor = conn.cursor()

    # 构建批量更新语句
    set_clause = ", ".join(f"{key} = VALUES({key})" for key in data[0].keys() if key != id_column)
    query = f"UPDATE {table_name} SET {set_clause} WHERE {id_column} = VALUES({id_column})"
    values = [tuple(item.values()) for item in data]

    # 执行批量更新
    cursor.executemany(query, values)
    conn.commit()

    # 关闭连接
    cursor.close()
    conn.close()

# 示例数据
data = [
    {"id": 1, "name": "Alice", "age": 30},
    {"id": 2, "name": "Bob", "age": 35},
    {"id": 3, "name": "Charlie", "age": 40}
]

# 调用批量更新函数
batch_update("users", data, "id")