CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
marcogh
marcogh

Reputation: 33

Why sqlalchemy result dict ignores text() columns?

I'm trying the new sqlalchemy 1.4 select() syntax, but when executing this simple query, the result dict misses the column 'graph', but the result list includes it.

import sqlalchemy as sa
from source.Models import Quadrant, Graph

engine = sa.create_engine("postgresql://blahblahblah")

dbsession = sa.orm.Session(engine)

stmt = sa.select(
    Quadrant.id,
    sa.text("jsonb_agg(graph.data order by graph.id) as graphs")
).outerjoin(
    Graph, Quadrant.id == Graph.quadrant_id
).group_by(
    Quadrant.id
)

with dbsession.begin():
    res = dbsession.execute(stmt)
    for r in res:
        print(r.keys())   # -> ['id'] **no graphs here!**
        print(list(r))    # -> [0, {aggregated data}]
        print(r[1])       # -> {aggregated data}

Is there a way to let the result as dict includes the text column?

I'm not interested in aggregating the result in python: I want to aggregate in postgresql.

Upvotes: 0

Views: 94

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52929

Use literal_column instead of text, if you want to use the text fragment:

sa.literal_column("jsonb_agg(graph.data order by graph.id)").label("graph")

You can also produce the expression using SQLA constructs:

from sqlalchemy.dialects.postgresql import aggregate_order_by

sa.func.jsonb_agg(aggregate_order_by(Graph.data, Graph.id)).label('graph')

Upvotes: 3

Related Questions