pyodbc，使用表变量调用存储过程

我必须用一个表变量参数调用一个MSSQLServer存储过程。

/* Declare a variable that references the type. */
DECLARE @TableVariable AS [AList];
/* Add data to the table variable. */
INSERT INTO @TableVariable (val) VALUES ('value-1');
INSERT INTO @TableVariable (val) VALUES ('value-2');

EXEC  [dbo].[sp_MyProc]
@param = @TableVariable

在SQL Sv管理工作室中运行良好。我使用PyOdbc在python中尝试了以下操作：

cursor.execute("declare @TableVariable AS [AList]")
for a in mylist:
cursor.execute("INSERT INTO @TableVariable (val) VALUES (?)", a)
cursor.execute("{call dbo.sp_MyProc(@TableVariable)}")

出现以下错误：错误42000：必须声明表变量。该变量在不同的执行步骤中不存在。我也试过：

sql = "DECLARE @TableVariable AS [AList]; "
for a in mylist:
sql = sql + "INSERT INTO @TableVariable (val) VALUES ('{}'); ".format(a)
sql = sql + "EXEC  [dbo].[sp_MyProc]   @param = @TableVariable"
cursor.execute(sql)

出现以下错误：没有结果。以前的SQL不是查询。不再有机会

sql = sql + "{call dbo.sp_MyProc(@TableVariable)}"

有人知道如何使用Pyodbc处理这个问题吗？

现在问题的根源是SQL Server变量的作用域是在批处理中定义的。对cursor.execute的每次调用都是一个单独的批处理，即使它们在同一事务中也是如此。

有几种方法可以解决这个问题。最直接的方法是重写Python代码，使其将所有内容作为一个批处理发送。(我在测试服务器上测试了这一点，只要您添加set nocount，或者使用nextset跳过中间结果，它就会工作。)

一种更间接的方法是重写过程以查找临时表而不是表变量，然后只创建和填充临时表而非表变量。不是在存储过程中创建的临时表具有在其中创建的会话的作用域。

我相信这个错误与sql忘记表变量无关。我最近遇到过这种情况，问题是如果SP也返回受影响事物的计数，pyodbc不知道如何从存储过程中获取结果集。

在我的情况下，解决方法是在SP的开头简单地设置"SET NOCOUNT ON"。

我希望这能有所帮助。

我不确定这是否有效，我无法测试它，因为我没有MS SQL Server，但你是否尝试过在一条语句中执行所有内容：

cursor.execute("""
DECLARE @TableVariable AS [AList];
INSERT INTO @TableVariable (val) VALUES ('value-1');
INSERT INTO @TableVariable (val) VALUES ('value-2');
EXEC [dbo].[sp_MyProc] @param = @TableVariable;
""");

我也遇到了同样的问题，但这里的答案都没有解决。我无法让"SET NOCOUNT ON"工作，也无法对表变量进行单个批处理操作。实际工作是分两批使用一个临时表，但要花一整天的时间才能找到正确的语法。下面的代码在第一批中创建并填充一个临时表，然后在第二批中，它使用数据库名称执行一个存储的proc，该数据库名称前加两个点。此语法对于避免出现错误"找不到存储过程'x'.(2812)(SQLExecDirectW)"非常重要。

def create_incidents(db_config, create_table, columns, tuples_list, upg_date):
"""Executes trackerdb-dev mssql stored proc.
Args:
config (dict): config .ini file with mssqldb conn.
create_table (string): temporary table definition to be inserted into 'CREATE TABLE #TempTable ()'
columns (tuple): columns of the table table into which values will be inserted.
tuples_list (list): list of tuples where each describes a row of data to insert into the table.
upg_date (string): date on which the items in the list will be upgraded.
Returns:
None
"""
sql_create = """IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable;
CREATE TABLE #TempTable ({});
INSERT INTO #TempTable ({}) VALUES {};
"""
columns = '"{}"'.format('", "'.join(item for item in columns))
# this "params" variable is an egregious offense against security professionals everywhere. Replace it with parameterized queries asap.
params = ', '.join([str(tupl) for tupl in tuples_list])
sql_create = sql_create.format(
create_table
, columns
, params)
msconn.autocommit = True
cur = msconn.cursor()
try:
cur.execute(sql_create)
cur.execute("DatabaseName..TempTable_StoredProcedure ?", upg_date)
except pyodbc.DatabaseError as err:
print(err)
else:
cur.close()
return
create_table = """
int_column int
, name varchar(255)
, datacenter varchar(25)
"""
create_incidents(
db_config    = db_config
, create_table = create_table
, columns      = ('int_column', 'name', 'datacenter')
, cloud_list   = tuples_list
, upg_date     = '2017-09-08')

存储过程使用IF OBJECT_ID('tempdb..#TempTable') IS NULL语法来验证是否已创建临时表。如果有，该过程将从中选择数据并继续。如果尚未创建临时表，进程将中止。这将强制存储过程使用在存储过程本身之外但在同一会话中创建的#TempTable的副本。pyodbc会话一直持续到游标或连接关闭，并且pyodbc创建的临时表具有整个会话的范围。

IF OBJECT_ID('tempdb..#TempTable') IS NULL
BEGIN
-- #TempTable gets created here only because SQL Server Management Studio throws errors if it isn't.
CREATE TABLE #TempTable (
int_column int
, name varchar(255)
, datacenter varchar(25)
);
-- This error is thrown so that the stored procedure requires a temporary table created *outside* the stored proc
THROW 50000, '#TempTable table not found in tempdb', 1;
END
ELSE
BEGIN
-- the stored procedure has now validated that the temporary table being used is coming from outside the stored procedure
SELECT * FROM  #TempTable;
END;

最后，请注意，"tempdb"并不像我第一次看到它时所想的那样是一个占位符。"tempdb"是一个实际的MSSQLServer数据库系统对象。

设置connection.autocommit = True并仅使用cursor.execute()一次，而不是多次。传递给cursor.execute()的SQL字符串必须包含所有3个步骤：

声明表变量
用数据填充表变量
执行将该表变量用作输入的存储过程

这三个步骤之间不需要分号。

这是一个功能齐全的演示。我没有考虑参数传递，因为它是无关的，但它也可以很好地处理这个问题。

SQL安装程序(提前执行)

CREATE TYPE dbo.type_MyTableType AS TABLE(
a INT,
b INT,
c INT
)
GO
CREATE PROCEDURE dbo.CopyTable
@MyTable type_MyTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT * INTO MyResultTable FROM @MyTable
END

python

import pyodbc
CONN_STRING = (
'Driver={SQL Server Native Client 11.0};'
'Server=...;Database=...;UID=...;PWD=...'
)
class DatabaseConnection(object):
def __init__(self, connection_string):
self.conn = pyodbc.connect(connection_string)
self.conn.autocommit = True
self.cursor = self.conn.cursor()
def __enter__(self):
return self.cursor
def __exit__(self, *args):
self.cursor.close()
self.conn.close()
sql = (
'DECLARE @MyTable type_MyTableType'
'nINSERT INTO @MyTable VALUES'
'n(11, 12, 13),'
'n(21, 22, 23)'
'nEXEC CopyTable @MyTable'
)
with DatabaseConnection(CONN_STRING) as cursor:
cursor.execute(sql)

如果要将SQL分布在对cursor.execute()的多个调用中，则需要使用临时表。请注意，在这种情况下，您仍然需要connection.autocommit = True。

正如Timothy所指出的，问题在于使用nextset()。

我发现，当您执行()多语句查询时，pyodbc只检查(是否有语法错误)并执行批处理中的第一条语句，而不执行整个批处理，除非您明确指定nextset()。

假设您的查询是：

cursor.execute('select 1 '
'select 1/0') 
print(cursor.fetchall())

你的结果是：

[(1, )]

但一旦你通过命令指示它在批次中进一步移动，这就是语法错误的部分

cursor.nextset()

给你：

pyodbc.DataError: ('22012', '[22012] [Microsoft][ODBC SQL Server Driver][SQL Server]Divide by zero error encountered. (8134) (SQLMoreResults)')

因此解决了我在多语句查询中使用变量表时遇到的问题。

相关内容

最新更新

热门标签：