SQLAlchemy how to select flat array when I use function instead of nested object with key func_name_1

user_11234907 0
Viewed 247

I have some category table, and I'm trying to get flat array something like this

select([psql.array_agg(category.c.name)])

Expected result:

["foo", "bar", "etc"]

Actual result:

[
  {
    "array_agg_1": ["foo", "bar", "etc"]
  }
]

I have tried many different options. But they are all more like workaround. What is most interesting, everything looks good in postgres console.

select array_agg(name) from category
{foo,bar,etc}

Why is this happening and how to solve it without additional subselecting, getting item by key and etc?

sqlalchemy python
1 Answers

Unless you are running psql with the tuples_only setting enabled, the output of your query is

   array_agg   
═══════════════
 {foo,bar,etc}
(1 row)

From this we can see that in the absence of an explicit label, Postgresql assigns a label of array_agg to the output column.

SQLAlchemy row objects "know" about the column names that they contain, so if you convert them to mappings, for example like this:

>>> with engine.connect() as conn:
...     result = conn.execute(sa.select(array_agg(category.c.name)))
...     print(list(result.mappings()))

you get

[{'array_agg_1': ['foo', 'bar', 'etc']}]

But if I convert the result to a list directly, while calling the result's scalar_one() method to indicate that I expect a single row, I get the desired output:

>>> with engine.connect() as conn:
...     result = conn.execute(sa.select(array_agg(category.c.name)))
...     print(list(result.scalar_one()))
... 
['foo', 'bar', 'etc']
>>>
user_53209060
Related