pythonsqlalchemyassociations

sqlalchemy - how to get attributes from the query response into an object

I am trying to get the some calculated value from a sqlquery into the object that should be instantiated, however access to those values seems impossible.

given three tables: A, B, A_to_B

I need to do a query like

select a.*, sum(a_to_b.count) as totalsum from A, A_to_B where a.id=A_to_B.a_id;

and other variations... sqlalchemy throws an error

Label name totalsum is being renamed to an anonymous label due to disambiguation which is not supported right now.  Please use unique names for explicit labels

is there any way to get sqlalchemy to bind the values of the associationtable to a model ?

thanks in advance!

Solution

  • You can use orm.query_expression() to create a placeholder attribute on your model which you can populate using arbitrary SQL expressions via orm.with_expression().

    The value of the query_expression column is NULL by default, but this can be overridden with the default_expr keyword argument. Since the code in the question is summing values zero is a sensible default value, unless we need to distinguish between counts of zero and counts that are absent.

    The expression itself is passed as an option to the query via orm.with_expression, like this:

    select(some_statement).options(orm.with_expression(Model.target_column, expression)

    In this case the target column is ModelA.totalsum and the expression is sa.func.sum(a_to_b.c.count), assuming that a_to_b is a Table instance.

    Putting it the parts together, we get this:

    import sqlalchemy as sa
from sqlalchemy import orm
...
class ModelA(Base):
    ...
    totalsum: Mapped[int] = orm.query_expression(default_expr=0)
    
...

with Session() as s:
    # Set the value of totalsum in the query.
    model_as = s.scalars(
        sa.select(ModelA)
        .join(a_to_b, ModelA.id == a_to_b.c.a_id)
        .group_by(ModelA.id)
        .options(orm.with_expression(ModelA.totalsum, sa.func.sum(a_to_b.c.count)))
    )
    # Observe that ModelA.totalsum has the expected value.
    for model_a in model_as:
        print(f'{model_a.id = }, {model_a.totalsum = }')

    See the documentation for Loading Arbitrary Expressions onto Objects.