SqlAlchemy: case statement (case - if - then -else)

Question

I'm wondering if there's a way to create a case statement with SqlAlchemy, e.g. the postgresql version

Maybe literal SQL is the way to go if there is no easy way of doing it?

Answer 1

Check out the documentation about the case statement here: http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.case

Answer 2

Reference from the Official doc of SQLAlchemy

from sqlalchemy import case, literal_column

case(
    [
        (
            orderline.c.qty > 100,
            literal_column("'greaterthan100'")
        ),
        (
            orderline.c.qty > 10,
            literal_column("'greaterthan10'")
        )
    ],
    else_=literal_column("'lessthan10'")
)

The above will render the given constants without using bound parameters for the result values (but still for the comparison values), as in:

CASE
    WHEN (orderline.qty > 100) THEN 'greaterthan100'
    WHEN (orderline.qty > 10) THEN 'greaterthan10'
    ELSE 'lessthan10'
END