SQL Server正常运行的查询在SQLAlchemy执行时无返回结果

我有一段在SQL Server中可正常运行的复杂查询，但使用Python的SQLAlchemy调用时无法返回记录。简单SELECT查询可正常执行，说明数据库连接无问题，且已用text()包裹查询语句。我是SQLAlchemy新手，原以为SQL Server中可用的查询都能在SQLAlchemy中运行，已查阅大量资料但未找到有效方案。

报错信息 #

从.sql文件读取查询执行时，出现如下报错：

fetchall
    return self._allrows()
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\result.py", line 555, in _allrows
    make_row = self._row_getter
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1257, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\result.py", line 478, in _row_getter
    key_to_index = metadata._key_to_index
  File "C:\Program Files\Python310\lib\site-packages\sqlalchemy\engine\cursor.py", line 1363, in _key_to_index
    self._we_dont_return_rows()

可正常运行的查询 #

SELECT * FROM [dbo].[Customer_Orders_Daily]

执行失败的复杂查询（模拟实际结构） #

该查询在SQL Server中可正常运行，但通过SQLAlchemy从文件读取执行时失败：

DECLARE @query NVARCHAR(MAX);

SET @query = 
'
SELECT * FROM [dbo].[Customer_Orders_Daily] 
'
EXEC sp_executesql @query;

Python代码片段 #

import sqlalchemy
from sqlalchemy import text
import pandas as pd

def runSQLScript(SQLScript_file):
    server_name='xxxx'
    database_name='yyy'

    connection_url = sqlalchemy.engine.URL.create(
        "mssql+pyodbc",
        database=database_name, 
        host=server_name, 
        query={'driver':'SQL Server'}
    )
    engine = sqlalchemy.create_engine(connection_url)

    try:
        with open(SQLScript_file, 'r') as file:
            sql_script = file.read()
        with engine.connect() as conn:
            # 读取结果到DataFrame
            result = conn.execute(text(sql_script))
            df = pd.DataFrame(result.fetchall(), columns=result.keys())
    except:
        print("Error running query")

    # 关闭连接
    conn.close()

更新情况 #

直接在Python代码中写入该复杂查询可正常运行，但从文件读取仍失败：

with engine.connect() as conn:
    # 尝试执行语句并提交
    try:
        sql_command="""
        DECLARE @query NVARCHAR(MAX);
        SET @query = 'SELECT * FROM [server].[dbo].[Customer_Orders_Daily]'
        EXEC sp_executesql @query;
        """
        result = conn.execute(text(sql_command))
        print(result.fetchall())
        exit()
    # 错误捕获
    except:
        print('Error executing Query')

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