无需临时表实现Pandas DataFrame与SQL Server表关联并插入目标表

核心思路说明 #

你原逻辑的问题在于SQL侧无法直接读取Python内存中的DataFrame对象，所以才需要先导入临时表。我们可以通过两种路径绕过临时表需求：要么把DF数据直接嵌入SQL查询构造虚拟表，要么把SQL表数据拉到Pandas侧完成关联后直接写入目标表。

方案1：SQL侧嵌入VALUES虚拟表（适用于DF数据量<1万行的场景） #

直接将DF的所有行格式化为SQL的VALUES列表，作为关联的左表，直接执行关联插入逻辑，全程不创建物理临时表。
Python+SQLAlchemy实现代码如下：

from sqlalchemy import create_engine
import pandas as pd

# 初始化数据库连接
engine = create_engine("mssql+pyodbc://账号:密码@DSN名称?driver=ODBC+Driver+17+for+SQL+Server")

# 构造VALUES子句，处理字符串转义
values_list = []
for _, row in df.iterrows():
    # 可根据实际字段类型调整转义逻辑
    c1_val = f"'{row['c1'].replace("'", "''")}'" if isinstance(row['c1'], str) else str(row['c1'])
    c2_val = f"'{row['c2'].replace("'", "''")}'" if isinstance(row['c2'], str) else str(row['c2'])
    values_list.append(f"({c1_val}, {c2_val})")
values_sql = ", ".join(values_list)

# 拼接完整插入SQL
insert_sql = f"""
INSERT INTO target_table (c1, c2, c3, c4, c5)
SELECT df.c1, df.c2, t1.c3, t2.c4, 
       COALESCE(t1.c5, t2.c5, NULL) AS c5
FROM (VALUES {values_sql}) AS df(c1, c2)
LEFT JOIN sql_table AS t1 ON df.c1 = t1.c3 
LEFT JOIN sql_table AS t2 ON df.c2 = t2.c4;
"""

# 执行SQL
with engine.connect() as conn:
    conn.execute(insert_sql)
    conn.commit()

注：这里用COALESCE替换了你原来的CASE语句，逻辑完全一致写法更简洁

方案2：Pandas侧完成关联（适用于sql_table数据量<10万行的场景） #

将SQL Server中的sql_table全量读取到本地内存，用Pandas原生的merge完成左关联，处理完字段逻辑后直接写入目标表，全程不需要操作SQL侧的临时表。
实现代码如下：

from sqlalchemy import create_engine
import pandas as pd

# 初始化数据库连接
engine = create_engine("mssql+pyodbc://账号:密码@DSN名称?driver=ODBC+Driver+17+for+SQL+Server")

# 读取SQL侧的关联表
sql_df = pd.read_sql("SELECT c3, c4, c5 FROM sql_table", con=engine)

# 第一次左关联：匹配c1和c3
merge_df = df.merge(sql_df, left_on="c1", right_on="c3", how="left", suffixes=("", "_t1"))
# 第二次左关联：匹配c2和c4
merge_df = merge_df.merge(sql_df, left_on="c2", right_on="c4", how="left", suffixes=("", "_t2"))

# 按优先级取c5的值，等价于SQL的COALESCE逻辑
merge_df["c5"] = merge_df["c5"].combine_first(merge_df["c5_t2"])

# 筛选目标字段
final_df = merge_df[["c1", "c2", "c3", "c4", "c5"]]

# 直接写入目标表，append模式追加数据
final_df.to_sql("target_table", con=engine, if_exists="append", index=False)

方案选型建议 #

• 若DF数据量小、sql_table数据量大：选择方案1，避免拉取全量SQL表数据到本地
• 若sql_table数据量小、DF数据量大：选择方案2，实现更简单，性能更稳定
• 若两边数据量都很大，建议还是保留临时表方案，性能最优且不会触发查询长度限制

内容的提问来源于stack exchange，提问作者Basir Miah