如何使用需要用户定义类型Table参数的SQLAlchemy调用存储过程 [英] How to call stored procedure with SQLAlchemy that requires a user-defined-type Table parameter

问题描述

我在MSSQL服务器"prc_add_names"上有一个存储过程，该存储过程带有表值参数.参数本身是自定义类型"StringTable"，定义如下:

I have a stored procedure on MSSQL server, "prc_add_names", that takes a table-value parameter. The parameter itself is of a custom type "StringTable" defined like so:

CREATE TYPE [dbo].[StringTable] AS TABLE([strValue] [nvarchar](max) NULL)

我不知道如何使用SQLAlchemy执行此过程.我习惯像这样使用session.execute调用带有参数的过程:

I have no idea how to execute this procedure using SQLAlchemy. I am used to calling procedures with arguments using session.execute like this:

result = session.execute('prc_do_something :pArg', {pArg:'foo'})

但是，如果我只是简单地传递一个字符串列表作为参数，这将不起作用:

However, this does not work if I simply pass a list of strings as the argument:

result = session.execute('prc_add_names :pArg', {pArg: ['Name One', 'Name Two']})

导致:

sqlalchemy.exc.ProgrammingError: (pymssql.ProgrammingError) (102, "Incorrect syntax near 'Name One'.DB-Lib error message 20018, severity 15:
General SQL Server error: Check messages from the SQL Server
") [SQL: 'prc_add_names %(pArg)s'] [parameters: {'pArg': ['Name One', 'Name Two']}] (Background on this error at: http://sqlalche.me/e/f405)

很明显，SQLAlchemy不能理解我的字符串列表作为创建我的StringTable类型参数的尝试，但是经过几个小时的谷歌搜索和阅读文档之后，我仍未弄清楚应该如何处理.

Obviously, SQLAlchemy does not understand my list of strings as an attempt to create my StringTable-type argument, but after a couple hours of googling and reading through the documentation, I haven't figured out how I should be handling this.

仅供参考，我不受此数据库的控制，因此修改存储过程或其他任何操作都没有选择.

FYI, I am not in control of this database, so modifying the stored procedure or anything else there is not an option.

编辑:我没有嫁给SQLAlchemy.如果还有另一个可以处理此问题的库，我很乐意使用它.

EDIT: I'm not married to SQLAlchemy. If there is another library that can handle this, I'd be happy to use it instead.

推荐答案

有一个真正支持TVP的驱动程序: sqlalchemy-pytds .使用它们，您可以像这样调用存储过程:

There is a driver that really supports TVPs: Pytds. It's not officially supported, but there's a 3rd party dialect implementation for it: sqlalchemy-pytds. Using them you could call your stored procedure like so:

In [1]: engine.execute(DDL("CREATE TYPE [dbo].[StringTable] AS TABLE([strValue] [nvarchar](max) NULL)"))
Out[1]: <sqlalchemy.engine.result.ResultProxy at 0x7f235809ae48>

In [2]: engine.execute(DDL("CREATE PROC test_proc (@pArg [StringTable] READONLY) AS BEGIN SELECT * FROM @pArg END"))
Out[2]: <sqlalchemy.engine.result.ResultProxy at 0x7f2358027b70>

In [3]: arg = ['Name One', 'Name Two']

In [4]: import pytds

In [5]: tvp = pytds.TableValuedParam(type_name='StringTable',
   ...:                              rows=((x,) for x in arg))

In [6]: engine.execute('EXEC test_proc %s', (tvp,))
Out[6]: <sqlalchemy.engine.result.ResultProxy at 0x7f294e699e10>

In [7]: _.fetchall()
Out[7]: [('Name One',), ('Name Two',)]

通过这种方式，您可以传递大量数据作为参数:

This way you can pass potentially large amounts of data as params:

In [21]: tvp = pytds.TableValuedParam(type_name='StringTable',
    ...:                              rows=((str(x),) for x in range(100000)))

In [22]: engine.execute('EXEC test_proc %s', (tvp,))
Out[22]: <sqlalchemy.engine.result.ResultProxy at 0x7f294c6e9f98>

In [23]: _.fetchall()[-1]
Out[23]: ('99999',)

如果另一方面，您正在使用不支持TVP的驱动程序，则可以将该参数作为参数转到您的程序:

If on the other hand you're using a driver that does not support TVPs, you could declare a table variable, insert the values, and pass that as the argument to your procedure:

In [12]: engine.execute(
    ...:     """
    ...:     DECLARE @pArg AS [StringTable];
    ...:     INSERT INTO @pArg VALUES {placeholders};
    ...:     EXEC test_proc @pArg;
    ...:     """.format(placeholders=",".join(["(%s)"] * len(arg))),
    ...:     tuple(arg))
    ...:     
Out[12]: <sqlalchemy.engine.result.ResultProxy at 0x7f23580f2908>

In [15]: _.fetchall()
Out[15]: [('Name One',), ('Name Two',)]

请注意，您不能使用任何executemany方法，否则最终将分别为每个表值调用该过程.这就是为什么占位符是手动构造的，并且表值作为单独的参数传递的原因.必须注意不要直接在查询中设置任何参数的格式，而应使用正确数量的DB-API占位符.行值限制为最多1000 .

Note that you cannot use any executemany methods, or you'll end up calling the procedure for each table value separately. That is why the placeholders are constructed manually and the table values passed as individual arguments. Care must be taken not to format any arguments directly in to the query, but the correct amount of placeholders for the DB-API instead. Row values are limited to a maximum of 1000.

如果底层的DB-API驱动程序为表值参数提供了适当的支持，那当然很好，但是至少我找不到使用FreeTDS的pymssql的方法. 对邮件列表中的TVP的引用清楚表明它们是:不支持.情况是对于PyODBC来说并没有什么好.

It'd of course be nice, if the underlying DB-API driver provided proper support for table valued parameters, but at least I could not find a way for pymssql, which uses FreeTDS. A reference to TVPs on the mailing list makes it clear that they're not supported. The situation is not much better for PyODBC.

_{免责声明:我以前从未真正使用过MS SQL Server.}

这篇关于如何使用需要用户定义类型Table参数的SQLAlchemy调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！