1

Task at hand is to aggregate the values into comma-separated string using SqlAlchemy with SQL Server 2017:

enter image description here

So I use string_agg() to do so:

query = session.query(Table.key, func.string_agg(Table.value, ',')).group_by(Table.key).all()

But the query always returns an error:

Argument data type nvarchar is invalid for argument 2 of string_agg function

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Argument data type nvarchar is invalid for argument 2 of string_agg function. (8116) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)')

I tried a number of methods to avoid this error (e.g. cast the constant parameter "," to string, or using sub_query() to add the separator as a constant column) but still no luck.

So, what is the proper way to invoke string_agg() in SqlAlchemy?

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Mario
  • 55
  • 1
  • 8
  • You will probably have to use a raw query, [see here](https://stackoverflow.com/questions/17972020/how-to-execute-raw-sql-in-flask-sqlalchemy-app). – Tim Biegeleisen Jun 30 '20 at 04:35
  • Raw query can solve the problem, but I want to know whether raw query is the only solution, or there exists some workaround in sqlalchemy. – Mario Jun 30 '20 at 04:49
  • What is the type of `Table.key`? From [the documentation](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15): "If the input expression is type VARCHAR, the separator cannot be type NVARCHAR." – Ilja Everilä Jun 30 '20 at 07:49
  • Table.key is of String, which is translated to varchar by sqlalchemy. I think the issue is that sqlalchemy translate the separator in nvarchar, which violates the syntax. – Mario Jul 02 '20 at 01:47

1 Answers1

1

Use a literal_column and escape the single quotes

from sqlalchemy import literal_column

query = session.query(
            Table.key, 
            func.string_agg(Table.value, literal_column("','"))
        ).group_by(Table.key).all()
riptusk331
  • 369
  • 4
  • 9