Python调用带有表值参数的sql-server存储过程 [英] Python call sql-server stored procedure with table valued parameter

问题描述

我有一个 Python 脚本，可以加载、转换和计算数据.在 sql-server 中有一个存储过程，它需要一个表值参数、2 个必需参数和 2 个可选参数.在 sql server 中，我可以调用这个 SP:

I have a python script that loads , transform and calculates data. In sql-server there's a stored procedure that requires a table valued parameter, 2 required parameters and 2 optional parameters. In sql server I can call this SP:

USE [InstName]
GO

DECLARE @return_value int
DECLARE @MergeOnColumn core.MatchColumnTable

INSERT INTO @MergeOnColumn
SELECT 'foo.ExternalInput','bar.ExternalInput'

EXEC    @return_value = [core].[_TableData]
        @Target = N'[dbname].[tablename1]',
        @Source = N'[dbname].[table2]',
        @MergeOnColumn  = @MergeOnColumn,
        @Opt1Param = False,
        @Opt2Param = False

SELECT  'Return Value' = @return_value

GO

经过全面搜索，我找到了以下帖子:

after a comprehensive search I found the following post:

如何使用需要用户定义类型表参数的 SQLAlchemy 调用存储过程

它建议使用 PYTDS 和 sql-alchemy 的方言 'sql alchemy pytds' 来调用具有表值参数的 SP.通过这篇文章和文档，我创建了以下 Python 脚本:

it suggests to use PYTDS and the sql-alchemy 's dialect 'sql alchemy pytds' to call a SP with table valued parameters. with this post and the documentation I created the following Python script:

import pandas as pd
import pytds
from pytds import login
import sqlalchemy as sa
from sqlalchemy import create_engine
import sqlalchemy_pytds

def connect():
    return pytds.connect(dsn='ServerName',database='DBName', auth=login.SspiAuth())

engine = sa.create_engine('mssql+pytds://[ServerName]', creator=connect)
conn = engine.raw_connection()
with conn.cursor() as cur:
    arg = ("foo.ExternalInput","bar.ExternalInput")
    tvp = pytds.TableValuedParam(type_name="MergeOnColumn", rows=(arg))
cur.execute('EXEC test_proc %s', ("[dbname].[table2]", "[dbname].[table1]", tvp,))
cur.fetchall()

当我运行此代码时，我收到以下错误消息:

When I run this code I get the following error message:

TypeError: not all arguments converted during string formatting

有谁知道如何正确传递多个参数，或者有什么建议我可以直接处理这个调用 SP?

Doe anyone know how to pass in the multiple arguments correctly or has a suggestion how I could handle this call SP directly?

推荐答案

根据对我的问题的评论，我设法使用表值参数运行存储过程(并从 SP 获取返回值)最终脚本如下:

On the basis of the comments to my question i've managed to get the stored procedure running with table valued parameters (and get the return values from the SP) The final script is as follows:

import pandas as pd
import pytds
from pytds import login
import sqlalchemy as sa
from sqlalchemy import create_engine
import sqlalchemy_pytds

def connect():
    return pytds.connect(dsn='ServerName',database='DBName',autocommit=True, auth=login.SspiAuth())

engine = sa.create_engine('mssql+pytds://[ServerName]', creator=connect)
conn = engine.raw_connection()

with conn.cursor() as cur:
    arg = [["foo.ExternalInput","bar.ExternalInput"]]
    tvp = pytds.TableValuedParam(type_name="core.MatchColumnTable", rows=arg)
    cur.execute("EXEC test_proc @Target = N'[dbname].[tablename1]', @Source = N'[dbname].[table2]', @CleanTarget = 0, @UseColumnsFromTarget = 0, @MergeOnColumn = %s", (tvp,))
    result = cur.fetchall()
    print(result)

在连接中添加了自动提交(以提交游标中的事务)，表值参数(marchcolumntable)需要 2 列，因此修改了 arg 以适合 2 列.

The autocommit is added in the connection (to commit the transaction in the cursor), the table valued parameter (marchcolumntable) expects 2 columns, so the arg is modified to fit 2 columns.

exec 字符串中包含除 tvp 之外所需的参数.执行字符串中的最后一个参数是用 tvp 填充的 tvp 参数(mergeoncolumn)的名称.

The parameters that are required besides the tvp are included in the exec string. The last param in the execute string is the name of the tvp parameter(mergeoncolumn) that is filled with the tvp.

您可以选择添加 pytds 文档中描述的结果状态或行数:https://python-tds.readthedocs.io/en/latest/index.html

optionally you can add the result status or row count as descripted in the pytds documentation: https://python-tds.readthedocs.io/en/latest/index.html

注意！:在存储过程中，您必须确保添加了 SET NOCOUNT ON 否则你不会将任何结果返回给 Python

Note!: in the stored procedure you have to make sure that the SET NOCOUNT ON is added otherwise you wont get any results back to Python

这篇关于Python调用带有表值参数的sql-server存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！