如何在jupyter笔记本中使用pyodbc从sqlserver存储过程中检索多行

2o7dmzc5  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(354)

我在SQLServer中有一个存储过程,它接受3个输入参数,可以生成多行作为输出。实际上,在我当前的例子中,它返回了20行。例如,如果我从ssms手动执行存储过程,我将分别获得以下代码和部分输出:
代码:

DECLARE @return_value int
EXEC    @return_value = [Coverage-Source].[ReadCoverageMapping]
        @client = N'Capital BlueCross',
        @lineOfBusiness = N'Commercial',
        @distributionChannel = N'Retail'
SELECT  'Return Value' = @return_value
GO

输出:

ID   Attribute   Value      CoverageName
-----------------------------------------     
1      Copay      Yes        Retail Base
2      Copay       No        Retail
.        .         .            .
.        .         .            .

Return Value
20

现在,当我尝试使用pyodbc从jupyter笔记本读取存储过程时,我得到了一个错误
过程或函数readcoveragemapping指定了太多参数
我想要这样的输出:

ID   Attribute   Value      CoverageName
 ------------------------------------------
  1      Copay      Yes        Retail Base
  2      Copay       No        Retail
  .        .         .            .
  .        .         .            .

我试过这个代码:

client = 'Capital BlueCross'
lineOfBusiness = 'Commercial'
distributionChannel = 'Retail'

 cnxn = pyodbc.connect(r'Driver={SQL 
        Server};Server=MyServer;Database=COV_SRCE_TEST;Trusted_Connection=yes;')

 sql = """\
 DECLARE @out nvarchar(max);
 EXEC [cov_srce_test].[coverage-source].ReadCoverageMapping @client = ?, @lineOfBusiness = ?, 
 @distributionChannel = ?, @param_out = @out OUTPUT;
 SELECT @out AS the_output;
 """
 values = (client, lineOfBusiness, distributionChannel)
 cnxn.execute(sql, values)
 rows = cnxn.fetchall()
 while rows:
 print(rows)
 if cnxn.nextset():
    rows = cnxn.fetchall()
 else:
    rows = None

有没有办法做到这一点?我尝试了多种方法,但都找不到解决办法。

rvpgvaaj

rvpgvaaj1#

这些批次是不同的。看起来应该是:

sql = """\
DECLARE @return_value int
EXEC    @return_value = [Coverage-Source].[ReadCoverageMapping]
        @client = ?,
        @lineOfBusiness = ?,
        @distributionChannel = ?
SELECT  'Return Value' = @return_value
 """

它将输出两个结果集,一个来自存储过程,另一个来自 Return value 你可以省略。

相关问题