Using string_agg() function in sqlalchemcy with SQL Server 2017

Question

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

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?

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
– Mario, Commented Jun 30, 2020 at 4:49
What is the type of Table.key? From the documentation: "If the input expression is type VARCHAR, the separator cannot be type NVARCHAR." — Ilja Everilä
– Ilja Everilä, Commented Jun 30, 2020 at 7: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
– Mario, Commented Jul 2, 2020 at 1:47

riptusk331 · Accepted Answer · 2021-03-05 19:52:10Z

2

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()

answered Mar 5, 2021 at 19:52

riptusk331

3904 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using string_agg() function in sqlalchemcy with SQL Server 2017

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related