Pandas与SQL数据库：高效更新表列的实践指南

import pandas as pd
import pyodbc as odbc

# 数据库连接字符串，请根据您的实际情况替换
# 示例：'DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_password'
CONNECTION_STRING = "<your_connection_string>" 
TABLE_NAME = "myTable"
COLUMN_TO_UPDATE = "myColumn"
PRIMARY_KEY_COLUMN = "id" # 假设您的表有一个名为'id'的主键列

try:
    # 1. 连接到数据库
    sql_conn = odbc.connect(CONNECTION_STRING)
    cursor = sql_conn.cursor()

    # 2. 从数据库读取数据到DataFrame
    query = f"SELECT * FROM {TABLE_NAME}"
    df = pd.read_sql(query, sql_conn)

    print(f"原始DataFrame（前5行）:\n{df.head()}")

    # 3. 更新DataFrame中的指定列
    # 假设我们有一个新的值列表来更新'myColumn'
    # 实际应用中，myNewValueList可能来自更复杂的计算或外部数据源
    myNewValueList = list(range(100, 100 + len(df))) # 示例：生成新的递增值
    df[COLUMN_TO_UPDATE] = myNewValueList

    print(f"\n更新后的DataFrame（前5行）:\n{df.head()}")

    # 4. 逐行更新数据库表
    # SQL UPDATE语句，使用参数化查询防止SQL注入
    update_sql = f"UPDATE {TABLE_NAME} SET {COLUMN_TO_UPDATE} = ? WHERE {PRIMARY_KEY_COLUMN} = ?"

    for index, row in df.iterrows():
        # 执行UPDATE语句，row[COLUMN_TO_UPDATE]是新值，row[PRIMARY_KEY_COLUMN]是主键值
        cursor.execute(update_sql, (row[COLUMN_TO_UPDATE], row[PRIMARY_KEY_COLUMN]))

    # 5. 提交更改并关闭连接
    sql_conn.commit()
    print(f"\n成功逐行更新了 {len(df)} 条记录。")

except odbc.Error as ex:
    sqlstate = ex.args[0]
    print(f"数据库操作失败: {sqlstate}")
    if sql_conn:
        sql_conn.rollback() # 发生错误时回滚事务
finally:
    if cursor:
        cursor.close()
    if sql_conn:
        sql_conn.close()
    print("数据库连接已关闭。")

pip install sqlalchemy pandas pyodbc

import pandas as pd
import pyodbc as odbc
from sqlalchemy import create_engine, text

# 数据库连接字符串，请根据您的实际情况替换
# SQLAlchemy连接字符串格式通常为：'dialect+driver://user:password@host:port/database'
# 示例（SQL Server with pyodbc）：'mssql+pyodbc://user:password@server_name/database_name?driver=ODBC+Driver+17+for+SQL+Server'
# 请确保您的ODBC驱动名称正确
SQLALCHEMY_CONNECTION_STRING = "mssql+pyodbc://<user>:<password>@<server_name>/<database_name>?driver=ODBC+Driver+17+for+SQL+Server"
PYODBC_CONNECTION_STRING = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=<server_name>;DATABASE=<database_name>;UID=<user>;PWD=<password>"

TABLE_NAME = "myTable"
COLUMN_TO_UPDATE = "myColumn"
PRIMARY_KEY_COLUMN = "id" # 假设您的表有一个名为'id'的主键列
TEMP_TABLE_NAME = "temp_myTable_update" # 临时表名称

try:
    # 1. 使用SQLAlchemy创建数据库引擎 (用于to_sql方法)
    engine = create_engine(SQLALCHEMY_CONNECTION_STRING)

    # 使用pyodbc连接读取数据（to_sql也可以直接使用engine，但read_sql通常更灵活）
    sql_conn_pyodbc = odbc.connect(PYODBC_CONNECTION_STRING)

    # 2. 从数据库读取数据到DataFrame
    query = f"SELECT * FROM {TABLE_NAME}"
    df = pd.read_sql(query, sql_conn_pyodbc)
    sql_conn_pyodbc.close() # 读取完即可关闭pyodbc连接

    print(f"原始DataFrame（前5行）:\n{df.head()}")

    # 3. 更新DataFrame中的指定列
    # 假设我们有一个新的值列表来更新'myColumn'
    myNewValueList = list(range(200, 200 + len(df))) # 示例：生成新的递增值
    df[COLUMN_TO_UPDATE] = myNewValueList

    print(f"\n更新后的DataFrame（前5行）:\n{df.head()}")

    # 4. 将更新后的DataFrame写入一个临时表
    # if_exists='replace' 会在每次运行时替换旧的临时表
    df.to_sql(TEMP_TABLE_NAME, engine, if_exists='replace', index=False)
    print(f"\nDataFrame已成功写入临时表: {TEMP_TABLE_NAME}")

    # 5. 执行SQL UPDATE语句，从临时表更新目标表
    # 注意：SQL Server的UPDATE FROM语法，其他数据库可能略有不同
    update_query = f"""
    UPDATE {TABLE_NAME}
    SET {TABLE_NAME}.{COLUMN_TO_UPDATE} = temp.{COLUMN_TO_UPDATE}
    FROM {TABLE_NAME}
    INNER JOIN {TEMP_TABLE_NAME} AS temp
    ON {TABLE_NAME}.{PRIMARY_KEY_COLUMN} = temp.{PRIMARY_KEY_COLUMN};
    """

    # 6. 执行更新并删除临时表
    with engine.connect() as conn:
        # 执行更新操作
        result = conn.execute(text(update_query))
        print(f"成功更新了 {result.rowcount} 条记录。")

        # 删除临时表
        conn.execute(text(f"DROP TABLE {TEMP_TABLE_NAME}"))
        print(f"临时表 {TEMP_TABLE_NAME} 已删除。")
        conn.commit() # 提交事务

except Exception as e:
    print(f"操作失败: {e}")
    # SQLAlchemy的引擎连接上下文管理器会自动处理回滚或提交
finally:
    if 'engine' in locals() and engine:
        engine.dispose() # 确保关闭所有连接池中的连接
    print("数据库连接已关闭。")