How to call stored procedure with SQLAlchemy that requires a user-defined-type Table parameter

Question

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)

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']})

which leads to:

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)

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.

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

Answer 1

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',)

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',)]

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.

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.

_{Disclaimer: I've not really used MS SQL Server before.}

Answer 2

I think you can use callproc() method and pass the input argument as a list.http://docs.sqlalchemy.org/en/latest/core/connections.html#calling-stored-procedures