Module « sqlalchemy »

Fonction case - module sqlalchemy

Signature de la fonction case

def case(*whens, **kw) 

Description

case.__doc__

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::

            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,
        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

        :param \*whens: The criteria to be compared against,
         :paramref:`.case.whens` accepts two different forms, based on
         whether or not :paramref:`.case.value` is used.

         .. versionchanged:: 1.4 the :func:`_sql.case`
            function now accepts the series of WHEN conditions positionally;
            passing the expressions within a list is deprecated.

         In the first form, it accepts a list of 2-tuples; each 2-tuple
         consists of ``(<sql expression>, <value>)``, where the SQL
         expression is a boolean expression and "value" is a resulting value,
         e.g.::

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

         In the second form, it accepts a Python dictionary of comparison
         values mapped to a resulting value; this form requires
         :paramref:`.case.value` to be present, and values will be compared
         using the ``==`` operator, e.g.::

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

        :param value: An optional SQL expression which will be used as a
          fixed "comparison point" for candidate values within a dictionary
          passed to :paramref:`.case.whens`.

        :param else\_: An optional SQL expression which will be the evaluated
          result of the ``CASE`` construct if all expressions within
          :paramref:`.case.whens` evaluate to false.  When omitted, most
          databases will produce a result of NULL if none of the "when"
          expressions evaluate to true.