如何在Python中将含临时表的SQL脚本结果转为DataFrame？

问题背景 #

我有一个包含注释、临时表及其他内容的SQL文件，需要将文件中最后一条查询语句生成的表转为DataFrame。使用pd.read_sql或pd.read_sql_query执行整个脚本时出现报错，且当文件不含临时表或其他查询时，仅会运行第一条查询后结束，无法获取最后一条结果。

现有环境配置 #

import pandas as pd
from pandas import DataFrame
import sqlalchemy
from sqlalchemy import create_engine
from sqlalchemy import text

server = "servername"
database = "databasename"
driver = "driver=ODBC Driver 17 for SQL Server"
engine = create_engine('mssql+pyodbc://' + server + '/' + database + '?trusted_connection=yes&' + driver)
print(engine)

已尝试的方案 #

方案1：复制粘贴SQL脚本 #

query = """ 
long SQL 
script copied from 
SQL file 
"""
pd.read_sql(query, engine)

方案2：从文件读取SQL脚本 #

sql_file = r"C:\Users\username\Documents\Sample Script.sql"

with open(sql_file, 'r') as query:
    query_string = query.read()

pd.read_sql_query(query_string, engine)

遇到的问题 #

执行上述方案时出现报错：

ResourceClosedError: This result object does not return rows. It has been closed automatically.

当SQL文件不含临时表或其他查询时，两种方案均可成功执行，但仅返回第一条查询的结果，无法获取最后一条查询的输出。

可行解决方案 #

方法1：修改SQL脚本，确保仅最后一条语句返回结果 #

在SQL脚本开头添加SET NOCOUNT ON;，该语句会抑制SQL Server返回INSERT/UPDATE/CREATE等操作的行数计数结果，让数据库仅返回最后一条SELECT语句的结果集，这样pd.read_sql就能正确读取目标数据：

修改后的SQL脚本示例：

SET NOCOUNT ON;

-- 这里是注释
CREATE TABLE #TempTable (ID INT, Name VARCHAR(50));
INSERT INTO #TempTable VALUES (1, 'Test');

-- 最后一条查询，返回目标数据
SELECT * FROM #TempTable;

然后使用原有的读取方式即可：

sql_file = r"C:\Users\username\Documents\Sample Script.sql"

with open(sql_file, 'r') as query:
    query_string = query.read()

final_df = pd.read_sql(query_string, engine)

方法2：通过SQLAlchemy手动遍历结果集，提取最后一个结果 #

如果无法修改SQL脚本，可以通过SQLAlchemy的连接对象执行脚本，遍历所有结果集并提取最后一个转为DataFrame：

from sqlalchemy.exc import ResourceClosedError

sql_file = r"C:\Users\username\Documents\Sample Script.sql"

with open(sql_file, 'r') as query:
    query_string = query.read()

with engine.connect() as conn:
    result = conn.execute(text(query_string))
    all_result_sets = []
    while True:
        try:
            # 将当前结果集转为DataFrame
            df = pd.DataFrame(result.fetchall(), columns=result.keys())
            all_result_sets.append(df)
            # 切换到下一个结果集
            result.nextset()
        except ResourceClosedError:
            # 没有更多结果集时退出循环
            break

# 获取最后一个结果集
final_df = all_result_sets[-1] if all_result_sets else pd.DataFrame()

这种方法会捕获脚本执行过程中所有的结果集，无论前面的语句是否返回计数，都能准确提取最后一条查询的结果。

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