conditionals inside column_property

[niuji...@gmail.com]

I have a mapped class:

class Model(sqlalchemy.declarative_base()):

    attr_a = Column(String)

    attr_b = Column(Integer)

    attr_c = Column(Integer)

    aggr = column_property(attr_b + attr_c IF attr_a=='go' ELSE attr_b - attr_c)

Last line is pseoudo code that requires some conditional logic. Is such logic allowed inside column_property? Thanks a lot!

[Simon King]

You can do it, but you need to use an SQL conditional rather than a
python one. In this case that would probably be a CASE expression:

https://docs.sqlalchemy.org/en/13/core/sqlelement.html#sqlalchemy.sql.expression.case

I think it would look something like this:

from sqlalchemy.sql import case

aggr = column_property(
case([(attr_a == 'go', attr_b + attr_c)],
else_=attr_b - attr_c)
)

Hope that helps,

Simon

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
> ---
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/736f0ded-e39a-47fd-a0db-8ed33057d2a3n%40googlegroups.com.

[Jinghui Niu]

Thanks. One thing to clarify, I noticed that here you used `case` without using in a context of `select`. Is this considered a shorthand within sqlalchemy?

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/a_-Bqhh5wY0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAFHwexeO2qgWeKeWPN-WH9LD8_Zch4jQRYeiB-WNbZrcMBrZFQ%40mail.gmail.com.

[Simon King]

I think this page might explain it:

https://docs.sqlalchemy.org/en/13/core/sqlelement.html#column-elements-and-expressions

The "case" function, like many other SQLAlchemy functions, returns an
instance of a ClauseElement subclass. This instance is used later when
building SQL queries. Columns themselves are also ClauseElement
subclasses. You can construct them at any time and pass them around
like any other python object. When it comes time to query the
database, the SQLAlchemy compiler will build the query by traversing
all the ClauseElements and converting them to the appropriate SQL.

The column_property mechanism accepts a ClauseElement and causes it to
be added to the SQL when you later query the table.

Simon

> To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/CAOQAhNeTVkH5m-XXotNyHV_wBrb3Ev9SJnJpTYF2sRuJvxmOFA%40mail.gmail.com.

[niuji...@gmail.com]

extremely helpful, like a beacon for a lost ship:) thanks