如何用Python调用存储过程后通过BCP导出全局临时表数据？

问题核心：全局临时表##openpendreportingdata由Python的pyodbc会话创建，单独执行BCP时会新建独立SQL会话，原会话若关闭或临时表被标记删除，BCP就无法访问。以下是可行方案：

方案1：在存储过程内部执行BCP（推荐） #

将BCP命令整合到ExtractData存储过程，借助xp_cmdshell在同一会话执行，直接访问全局临时表。

1. 开启xp_cmdshell（若未开启）：

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

2. 修改存储过程：

ALTER PROCEDURE dbo.ExtractData
AS
BEGIN
    -- 原逻辑：填充全局临时表
    SELECT * INTO ##openpendreportingdata FROM YourSourceTable;

    -- 执行BCP导出
    DECLARE @bcpCmd NVARCHAR(4000);
    SET @bcpCmd = 'bcp "SELECT * FROM TestDatabase.dbo.##openpendreportingdata" queryout "C:\ExportPath\data.csv" -S YourSQLServer -T -c -t,';
    EXEC xp_cmdshell @bcpCmd;

    -- 清理临时表
    DROP TABLE IF EXISTS ##openpendreportingdata;
END

3. Python端仅需调用存储过程：

import pyodbc

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=YourSQLServer;DATABASE=TestDatabase;Trusted_Connection=yes;')
cursor = conn.cursor()
cursor.execute("EXEC dbo.ExtractData")
conn.commit()
conn.close()

方案2：改用物理临时表替代全局临时表 #

创建普通物理表存储数据，Python调用存储过程后执行BCP导出，完成后清理表。

1. 修改存储过程：

ALTER PROCEDURE dbo.ExtractData
AS
BEGIN
    DROP TABLE IF EXISTS dbo.tmp_openpendreportingdata;
    SELECT * INTO dbo.tmp_openpendreportingdata FROM YourSourceTable;
END

2. Python端逻辑：

import pyodbc
import subprocess

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=YourSQLServer;DATABASE=TestDatabase;Trusted_Connection=yes;')
cursor = conn.cursor()

# 调用存储过程生成数据
cursor.execute("EXEC dbo.ExtractData")
conn.commit()

# 执行BCP导出
bcp_cmd = [
    'bcp',
    'TestDatabase.dbo.tmp_openpendreportingdata',
    'out',
    'C:\\ExportPath\\data.csv',
    '-S', 'YourSQLServer',
    '-T',
    '-c',
    '-t,'
]
subprocess.run(bcp_cmd, check=True)

# 清理物理表
cursor.execute("DROP TABLE IF EXISTS dbo.tmp_openpendreportingdata")
conn.commit()
conn.close()

方案3：Python直接读取数据并导出（绕开BCP） #

通过pyodbc获取存储过程返回的数据，用Python原生模块写入文件，彻底绕开临时表问题。

1. 修改存储过程，直接返回数据：

ALTER PROCEDURE dbo.ExtractData
AS
BEGIN
    SELECT * FROM YourSourceTable;
END

2. Python端导出逻辑：

import pyodbc
import csv

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=YourSQLServer;DATABASE=TestDatabase;Trusted_Connection=yes;')
cursor = conn.cursor()

# 获取数据
cursor.execute("EXEC dbo.ExtractData")
rows = cursor.fetchall()
columns = [col[0] for col in cursor.description]

# 写入CSV
with open('C:\\ExportPath\\data.csv', 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(columns)
    writer.writerows(rows)

conn.close()

方案4：保持会话打开让BCP复用上下文（不推荐） #

全局临时表在创建会话关闭后才会删除，保持Python连接打开，BCP使用相同Windows身份即可访问临时表，但依赖会话状态，稳定性差，不适合生产环境。

示例代码：

import pyodbc
import subprocess

# 保持连接不关闭
conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=YourSQLServer;DATABASE=TestDatabase;Trusted_Connection=yes;')
cursor = conn.cursor()

# 创建全局临时表
cursor.execute("EXEC dbo.ExtractData")

# 执行BCP
bcp_cmd = [
    'bcp',
    'TestDatabase.dbo.##openpendreportingdata',
    'out',
    'C:\\ExportPath\\data.csv',
    '-S', 'YourSQLServer',
    '-T',
    '-c',
    '-t,'
]
subprocess.run(bcp_cmd, check=True)

# 清理并关闭连接
cursor.execute("DROP TABLE IF EXISTS ##openpendreportingdata")
conn.commit()
conn.close()

内容的提问来源于stack exchange，提问作者King of NES