Function case

lib/sqlalchemy/sql/_elements_constructors.py:794–910

r"""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 :class:`.Case`. :func:`.case` in its usual form is passed a series of "when" construc

(
    *whens: Union[
        typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
    ],
    value: Optional[Any] = None,
    else_: Optional[Any] = None,
)

def case(
    *whens: Union[
        typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
    ],
    value: Optional[Any] = None,
    else_: Optional[Any] = None,
) -> Case[Any]:
    r"""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 :class:`.Case`.

    :func:`.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:

    .. sourcecode:: sql

        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, :func:`.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 :func:`.bindparam` constructs.
    SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
    are accepted
    as well.  To coerce a literal string expression into a constant
    expression rendered inline, use the :func:`_expression.literal_column`
    construct,