如何基于PaymentID将Pandas DataFrame列合并至SQL表？

我有一个包含PaymentID和WasDenied两列的Pandas DataFrame，想要基于PaymentID列把WasDenied列合并到SQL表Payments中。to_sql()的replace或append选项满足不了需求，因为需要的是关联更新而非追加/替换。同时使用SQLite和MS SQL，需要区分不同数据库的解决方案。

原SQL表Payments结构及数据：

Pandas DataFrame数据：

对应DataFrame代码：

import pandas as pd
df = pd.DataFrame([[123, True],[456, False], [789, False], [101, True]], columns=['PaymentID', 'WasDenied'])

期望更新后的Payments表：

一、SQLite 实现步骤 #

SQLite不支持直接的UPDATE...JOIN语法，需通过临时表+子查询完成：

1. 添加目标列
   执行SQL语句给Payments表新增WasDenied列：

ALTER TABLE Payments ADD COLUMN WasDenied BOOLEAN;

若需要默认值，可改为：

ALTER TABLE Payments ADD COLUMN WasDenied BOOLEAN DEFAULT False;

2. 写入临时表
   将DataFrame内容写入SQLite临时表：

# 假设conn为已建立的SQLite数据库连接
df.to_sql('temp_denied', conn, if_exists='replace', index=False)

3. 关联更新数据
   通过子查询匹配PaymentID完成更新：

UPDATE Payments
SET WasDenied = (SELECT WasDenied FROM temp_denied WHERE temp_denied.PaymentID = Payments.PaymentID)
WHERE EXISTS (SELECT 1 FROM temp_denied WHERE temp_denied.PaymentID = Payments.PaymentID);

4. 清理资源
   提交更改并删除临时表：

conn.commit()
conn.execute("DROP TABLE temp_denied")

二、MS SQL 实现步骤 #

MS SQL支持UPDATE...JOIN语法，操作更简洁：

1. 添加目标列
   执行SQL语句新增列：

ALTER TABLE Payments ADD WasDenied BIT;

若需要默认值，可改为：

ALTER TABLE Payments ADD WasDenied BIT DEFAULT 0;

2. 写入临时表
   将DataFrame写入MS SQL临时表：

# 假设conn为已建立的MS SQL数据库连接（依赖pyodbc或sqlalchemy）
df.to_sql('#temp_denied', conn, if_exists='replace', index=False)

3. JOIN方式更新
   直接通过关联匹配更新数据：

UPDATE p
SET p.WasDenied = td.WasDenied
FROM Payments p
INNER JOIN #temp_denied td ON p.PaymentID = td.PaymentID;

4. 清理资源
   提交更改并删除临时表：

conn.commit()
conn.execute("DROP TABLE #temp_denied")

补充说明 #

• 若Payments表中存在DataFrame未覆盖的PaymentID，上述操作会保留WasDenied为NULL，可通过添加列时指定默认值处理
• 确保数据库连接拥有ALTER TABLE、UPDATE及临时表操作的权限

内容的提问来源于stack exchange，提问作者Yisroel Len