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

import pandas as pd
import pyodbc as odbc

# 1. 连接到数据库
# 请替换 <connection_stuff> 为您的实际数据库连接字符串
# 示例：'DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_password'
try:
    sql_conn = odbc.connect("<connection_stuff>")
    print("数据库连接成功！")
except odbc.Error as ex:
    sqlstate = ex.args[0]
    print(f"数据库连接失败: {sqlstate}")
    exit()

# 2. 从数据库读取数据到DataFrame
query = "SELECT <PrimaryKeyColumn>, myColumn FROM myTable" # 确保选择主键列
df = pd.read_sql(query, sql_conn)

# 3. 在DataFrame中修改数据
# 假设我们有一个新的值列表来更新 'myColumn'
myNewValueList = [11, 12, 13, 14, 15, 16, 17, 18, 19, 20] # 示例值，实际应与DataFrame行数匹配
if len(myNewValueList) == len(df):
    df['myColumn'] = myNewValueList
else:
    print("警告：新值列表长度与DataFrame行数不匹配，请检查数据。")
    # 这里可以根据实际情况处理，例如截断或填充
    # 为了示例，我们假设它们匹配

# 4. 准备UPDATE语句
# 使用问号 '?' 作为参数占位符，适用于 pyodbc
update_sql = "UPDATE myTable SET myColumn = ? WHERE <PrimaryKeyColumn> = ?"

# 5. 遍历DataFrame并执行更新
cursor = sql_conn.cursor()
try:
    for index, row in df.iterrows():
        # 确保 'myColumn' 和 '<PrimaryKeyColumn>' 存在于 row 中
        cursor.execute(update_sql, (row['myColumn'], row['<PrimaryKeyColumn>']))

    # 6. 提交更改并关闭连接
    sql_conn.commit()
    print(f"成功更新了 {len(df)} 行数据。")

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

import pandas as pd
import pyodbc as odbc
from sqlalchemy import create_engine, text # 引入 text 函数来执行原始SQL

# 1. 使用 SQLAlchemy 创建数据库引擎 (to_sql 方法需要)
# 请替换 <connection_stuff> 为您的实际数据库连接字符串
# 示例：'mssql+pyodbc://user:password@server_name/database_name?driver=ODBC+Driver+17+for+SQL+Server'
# 注意：连接字符串格式与pyodbc直接连接可能略有不同
try:
    engine = create_engine('mssql+pyodbc://<connection_stuff>')
    print("SQLAlchemy 引擎创建成功！")
except Exception as e:
    print(f"SQLAlchemy 引擎创建失败: {e}")
    exit()

# 2. 使用 pyodbc 连接并读取数据到DataFrame (如果需要，也可以用 SQLAlchemy)
# 保持与方法一相同的读取方式，方便代码复用
try:
    sql_conn = odbc.connect("<connection_stuff>") # 这里的连接字符串可能与上面略有不同
    print("pyodbc 数据库连接成功！")
except odbc.Error as ex:
    sqlstate = ex.args[0]
    print(f"pyodbc 数据库连接失败: {sqlstate}")
    exit()

query = "SELECT <PrimaryKeyColumn>, myColumn FROM myTable" # 确保选择主键列
df = pd.read_sql(query, sql_conn)
sql_conn.close() # 读取完数据后可以关闭 pyodbc 连接

# 3. 在DataFrame中修改数据
myNewValueList = [11, 12, 13, 14, 15, 16, 17, 18, 19, 20] # 示例值
if len(myNewValueList) == len(df):
    df['newColumnValues'] = myNewValueList # 创建一个新列来存储新值
else:
    print("警告：新值列表长度与DataFrame行数不匹配，请检查数据。")
    # 同样，根据实际情况处理

# 4. 将修改后的DataFrame写入一个临时表
temp_table_name = 'temp_myTable_update_data' # 临时表的名称
try:
    df.to_sql(temp_table_name, engine, if_exists='replace', index=False)
    print(f"DataFrame 已成功写入临时表 '{temp_table_name}'。")
except Exception as e:
    print(f"写入临时表失败: {e}")
    exit()

# 5. 执行 SQL 语句，从临时表更新原始表
with engine.connect() as conn:
    try:
        # 假设 'id' 是你的主键列，请替换为实际的主键列名 <PrimaryKeyColumn>
        update_query = text(f"""
        UPDATE myTable
        SET myColumn = temp.newColumnValues
        FROM myTable
        INNER JOIN {temp_table_name} AS temp
        ON myTable.<PrimaryKeyColumn> = temp.<PrimaryKeyColumn>;
        """)
        conn.execute(update_query)
        conn.commit() # 提交事务
        print(f"原始表 'myTable' 已从临时表 '{temp_table_name}' 批量更新成功。")

    except Exception as e:
        print(f"批量更新失败: {e}")
        conn.rollback() # 回滚事务

    finally:
        # 6. 删除临时表
        try:
            drop_table_query = text(f"DROP TABLE {temp_table_name};")
            conn.execute(drop_table_query)
            conn.commit() # 提交删除操作
            print(f"临时表 '{temp_table_name}' 已删除。")
        except Exception as e:
            print(f"删除临时表失败: {e}")
            conn.rollback() # 回滚删除操作（如果可能）