Vous êtes un professionnel et vous avez besoin d'une formation ? Sensibilisation à
l'Intelligence Artificielle Voir le programme détaillé

Module « sqlalchemy »

Fonction case - module sqlalchemy

Signature de la fonction case

def case(*whens: 'Union[typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]]', value: 'Optional[Any]' = None, else_: 'Optional[Any]' = None) -> 'Case[Any]' 

Description

help(sqlalchemy.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 :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,
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:

.. sourcecode:: sql

    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

 In the first form, it accepts multiple 2-tuples passed as positional
 arguments; 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.

Vous êtes un professionnel et vous avez besoin d'une formation ? Machine Learning
avec Scikit-Learn Voir le programme détaillé