Azure SQL存储过程中sp_execute_external_script如何生成带表头DataFrame并写入临时表

问题场景 #

通过Azure SQL Database存储过程中的sp_execute_external_script调用Python脚本获取API返回的CSV数据时，执行结果的列显示为(No column name)，无法得到带正确表头的数据集，同时需要将数据写入临时表供后续使用。

核心原因 #

1. SQL无法识别Python返回的OutputDataSet列结构，未明确指定返回结果的列名和数据类型
2. 原代码使用了多余的@df输出参数，未直接利用OutputDataSet返回结构化数据集
3. 列名处理逻辑零散，可能存在未完全清理的特殊字符，导致SQL无法识别合法表头

解决方案 #

1. 优化Python脚本，确保表头合法且正确读取 #

修正重复导入，统一列名处理逻辑，生成符合SQL命名规范的表头，同时明确指定CSV的表头行：

import requests
import numpy as np
import pandas as pd
import csv, io, sys
from datetime import datetime, timedelta
from time import time

client_id       = "XXXXXXXXXX"
client_secret   = "f2aazc28ae"
username        = "xxx.com"
password        = "ddddd"
tokenUrl        = "https://id.com/connect/token"
tokenHeader     = {"content-type": "application/x-www-form-urlencoded"}
# 用f-string简化字符串拼接
tokenData       = f"client_id={client_id}&client_secret={client_secret}&grant_type=password&scope=pre.feeds.default&username={username}&password={password}"

r = requests.post(url=tokenUrl, headers=tokenHeader, data=tokenData)
token = r.json()["access_token"]

payload = """{...}"""
requestHeaders = {"Accept": "text/csv", "Authorization": f"Bearer {token}"} 
r = requests.request("get", "https://feed.com/api/", headers=requestHeaders, data=payload, verify=False)
r.encoding = "utf-8-sig"

# 明确指定header=0，确保CSV第一行作为表头
df = pd.read_csv(io.StringIO(r.text), sep="|", low_memory=False, header=0)

# 统一清理列名，符合SQL标识符规则
df.columns = (df.columns
              .str.replace(r"[-/ :()&;?%]", "_", regex=True)  # 替换所有特殊字符为下划线
              .str.replace("Pct_", "Pct")  # 修正%替换后的多余下划线
              .str.title()  # 转为首字母大写格式，如Column_Name
              .str.replace("__", "_")  # 合并连续下划线
              .str.strip())  # 去除首尾空格

# 处理字符串列的空值
stringcols = df.select_dtypes(include="object").columns
df[stringcols] = df[stringcols].replace({np.nan: ""}).astype(str)

# 输出结构化数据集
OutputDataSet = df

2. 修改存储过程，指定返回结构并写入临时表 #

移除多余参数，通过WITH RESULT SETS明确指定返回的列名和数据类型，将结果直接插入临时表：

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_GetPreData] AS
BEGIN
    -- 清理已存在的临时表
    DROP TABLE IF EXISTS #TempPreData;

    EXEC sp_execute_external_script 
        @language=N'Python',
        @script=N'
-- 此处粘贴上述优化后的Python脚本代码
'
    -- 指定返回结果的列结构，需根据实际CSV列名和数据类型调整
    WITH RESULT SETS (
        (
            [FundName] NVARCHAR(MAX),
            [FundType] NVARCHAR(100),
            [LaunchDate] DATE,
            [FundSizePct] FLOAT,
            [ManagerName] NVARCHAR(MAX)
            -- 按实际列依次添加，确保列数和顺序与Python返回的DataFrame一致
        )
    )
    -- 将结果插入临时表
    INTO #TempPreData;

    -- 验证临时表数据（可选）
    SELECT * FROM #TempPreData;
END
GO

关键注意事项 #

• 表头确认：确保API返回的CSV第一行确实是表头，如果CSV无表头，需设置header=None并手动指定names参数定义列名
• 列类型匹配：SQL临时表的列类型需与Python DataFrame的数据类型对应（如float对应SQL的FLOAT/DECIMAL，datetime对应DATE/DATETIME）
• 列名合法性：SQL列名不能以数字开头，不能包含除下划线外的特殊字符，长度不超过128字符，需通过Python的列名处理逻辑保障
• 动态列处理：如果CSV列不固定，可先在Python中打印df.columns和df.dtypes，再动态生成SQL的列定义语句

内容的提问来源于stack exchange，提问作者Selaire Kollou N'dri