Pyodbc无法在SQL Server 2008中执行proc

Question

上下文：我有一个Python函数，它应该通过Pyodbc连接到SQL Server 2008，并执行一个存储过程来解析XML文件并将其加载到表中。我使用的是Python 3.8.1，我安装了更新的ODBC驱动程序(ODBC驱动程序13.1(

这是Pyhton函数：

import pyodbc
def load_to_database():
username = 'REDACTED'
password = 'REDACTED'
connection = pyodbc.connect("Driver={ODBC Driver 13 for SQL Server};"
"Server=REDACTED;"
"Database=REDACTED;"
"Username="+username+";"
"Password="+password+";"
"Trusted_Connection=yes;")
connection_cursor = connection.cursor()
executesp = """EXEC [REDACTED].[dbo].[get_cp_api_data_part_search]"""
connection.autocommit = True
connection_cursor.execute(executesp)
connection.close()
load_to_database()

问题：Python脚本似乎已成功连接到数据库，并且执行时没有任何错误。但是当我检查底层数据库表时，数据还没有加载，这对我来说表明存储过程没有被执行。当我手动运行存储过程时，它会毫无问题地运行并加载数据。

问题：是否有人可以帮助我提供故障排除技巧，并帮助我理解为什么没有执行该过程？我想这个过程可能正在执行，但无论出于什么原因，并不是所有的SQL都在运行？或者，在过程中进行EXEC调用后，光标可能会退出/返回？

以下是SQL Server中的存储过程：

IF OBJECT_ID('tempdb..##search') IS NOT NULL DROP TABLE ##earch
DECLARE @xml_data XML
--Use OPENROWSET to extract the data from the xml file. 
SELECT @xml_data=O
FROM OPENROWSET(BULK N'C:UsersebDesktopImportant Docs & LinksImportant Documentsxml_data_removed_tags.xml', SINGLE_BLOB) as file_output(O)
--Variable below will be used to create a recognizeable XML document within SQL Server memory
DECLARE @xml_doc int
--Procedure below takes 2 parameters: 1) output parameter to store handle to xml document and 2) the xml document itself 
EXEC sp_xml_preparedocument @xml_doc OUTPUT, @xml_data
--Function below parses the XML based on the hierarchy path used, list the attributes and elements you want to query
--Queried into temptable
SELECT *
INTO ##search
FROM OPENXML(@xml_doc,'REDACTED/*',2)
WITH (
REDACTED nvarchar(10),
REDACTED int, 
REDACTED int, 
REDACTED nvarchar(60),
REDACTED nvarchar(10), 
REDACTED int, 
REDACTED nvarchar(30),
REDACTED nvarchar(20),
REDACTED nvarchar(20), 
REDACTED int, 
REDACTED nvarchar(5), 
REDACTED nvarchar(5), 
REDACTED int, 
REDACTED nvarchar(50)'REDACTED',
REDACTED nvarchar(25)'REDACTED',
REDACTED int 'REDACTED', 
REDACTED nvarchar(60) 'REDACTED', 
REDACTED int 'REDACTED', 
REDACTED nvarchar(50) 'REDACTED', 
REDACTED nvarchar(20) 'REDACTED'
)
--This procedure removes the saved prepared xml document 

Answer 1

@Parfait，您是对的——当使用普通表而不是临时表时，问题似乎得到了解决。