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

2021-12-30 00:00:00 python sqlalchemy flask-sqlalchemy sql-server

我在 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 的驱动程序:Pytds.它不受官方支持，但有一个第三方方言实现: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.

相关文章