sqlalchemy.sql.elements.Case

Produce a CASE expression.

The CASE construct in SQL is a conditional object that acts somewhat analogously to an "if/then" construct in other languages. It returns an instance of .Case.

.case in its usual form is passed a series of "when" constructs, that is, a list of conditions and results as tuples:

from sqlalchemy import case

stmt = select(users_table).\
            where(
                case(
                    (users_table.c.name == 'wendy', 'W'),
                    (users_table.c.name == 'jack', 'J'),
                    else_='E'
                )
            )

The above statement will produce SQL resembling:

SELECT id, name FROM user
WHERE CASE
    WHEN (name = :name_1) THEN :param_1
    WHEN (name = :name_2) THEN :param_2
    ELSE :param_3
END

When simple equality expressions of several values against a single parent column are needed, .case also has a "shorthand" format used via the :paramref:`.case.value` parameter, which is passed a column expression to be compared. In this form, the :paramref:`.case.whens` parameter is passed as a dictionary containing expressions to be compared against keyed to result expressions. The statement below is equivalent to the preceding statement:

stmt = select(users_table).\
            where(
                case(
                    {"wendy": "W", "jack": "J"},
                    value=users_table.c.name,
                    else_='E'
                )
            )

The values which are accepted as result values in :paramref:`.case.whens` as well as with :paramref:`.case.else_` are coerced from Python literals into .bindparam constructs. SQL expressions, e.g. _expression.ColumnElement constructs, are accepted as well. To coerce a literal string expression into a constant expression rendered inline, use the _expression.literal_column construct, as in:

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 > :qty_1) THEN 'greaterthan100'
    WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
    ELSE 'lessthan10'
END

case(
    (users_table.c.name == 'wendy', 'W'),
    (users_table.c.name == 'jack', 'J')
)

case(
    {"wendy": "W", "jack": "J"},
    value=users_table.c.name
)