expression.py

SQLAlchemy. 经典的Python ORM框架。学习必看。

    result.
    """

    if len(clauses) == 1:
        return clauses[0]
    return ClauseList(operator=operators.or_, *clauses)

def not_(clause):
    """Return a negation of the given clause, i.e. ``NOT(clause)``.

    The ``~`` operator is also overloaded on all
    [sqlalchemy.sql.expression#_CompareMixin] subclasses to produce the same
    result.
    """

    return operators.inv(clause)

def distinct(expr):
    """Return a ``DISTINCT`` clause."""

    return _UnaryExpression(expr, operator=operators.distinct_op)

def between(ctest, cleft, cright):
    """Return a ``BETWEEN`` predicate clause.

    Equivalent of SQL ``clausetest BETWEEN clauseleft AND clauseright``.

    The ``between()`` method on all [sqlalchemy.sql.expression#_CompareMixin] subclasses
    provides similar functionality.
    """

    ctest = _literal_as_binds(ctest)
    return _BinaryExpression(ctest, ClauseList(_literal_as_binds(cleft, type_=ctest.type), _literal_as_binds(cright, type_=ctest.type), operator=operators.and_, group=False), operators.between_op)


def case(whens, value=None, else_=None):
    """Produce a ``CASE`` statement.

    whens
      A sequence of pairs to be translated into "when / then" clauses.

    value
      Optional for simple case statements.

    else\_
      Optional as well, for case defaults.
    """

    whenlist = [ClauseList('WHEN', c, 'THEN', r, operator=None)
                for (c,r) in whens]
    if not else_ is None:
        whenlist.append(ClauseList('ELSE', else_, operator=None))
    if whenlist:
        type = list(whenlist[-1])[-1].type
    else:
        type = None
    cc = _CalculatedClause(None, 'CASE', value, type_=type, operator=None, group_contents=False, *whenlist + ['END'])
    return cc

def cast(clause, totype, **kwargs):
    """Return a ``CAST`` function.

    Equivalent of SQL ``CAST(clause AS totype)``.

    Use with a [sqlalchemy.types#TypeEngine] subclass, i.e::

      cast(table.c.unit_price * table.c.qty, Numeric(10,4))

    or::

      cast(table.c.timestamp, DATE)
    """

    return _Cast(clause, totype, **kwargs)

def extract(field, expr):
    """Return the clause ``extract(field FROM expr)``."""

    expr = _BinaryExpression(text(field), expr, operators.from_)
    return func.extract(expr)

def exists(*args, **kwargs):
    """Return an ``EXISTS`` clause as applied to a [sqlalchemy.sql.expression#Select] object.

    The resulting [sqlalchemy.sql.expression#_Exists] object can be executed by
    itself or used as a subquery within an enclosing select.

    \*args, \**kwargs
      all arguments are sent directly to the [sqlalchemy.sql.expression#select()]
      function to produce a ``SELECT`` statement.
    """

    return _Exists(*args, **kwargs)

def union(*selects, **kwargs):
    """Return a ``UNION`` of multiple selectables.

    The returned object is an instance of [sqlalchemy.sql.expression#CompoundSelect].

    A similar ``union()`` method is available on all
    [sqlalchemy.sql.expression#FromClause] subclasses.

    \*selects
      a list of [sqlalchemy.sql.expression#Select] instances.

    \**kwargs
       available keyword arguments are the same as those of
       [sqlalchemy.sql.expression#select()].
    """

    return _compound_select('UNION', *selects, **kwargs)

def union_all(*selects, **kwargs):
    """Return a ``UNION ALL`` of multiple selectables.

    The returned object is an instance of [sqlalchemy.sql.expression#CompoundSelect].

    A similar ``union_all()`` method is available on all
    [sqlalchemy.sql.expression#FromClause] subclasses.

    \*selects
      a list of [sqlalchemy.sql.expression#Select] instances.

    \**kwargs
      available keyword arguments are the same as those of
      [sqlalchemy.sql.expression#select()].
    """

    return _compound_select('UNION ALL', *selects, **kwargs)

def except_(*selects, **kwargs):
    """Return an ``EXCEPT`` of multiple selectables.

    The returned object is an instance of [sqlalchemy.sql.expression#CompoundSelect].

    \*selects
      a list of [sqlalchemy.sql.expression#Select] instances.

    \**kwargs
      available keyword arguments are the same as those of
      [sqlalchemy.sql.expression#select()].
    """
    return _compound_select('EXCEPT', *selects, **kwargs)

def except_all(*selects, **kwargs):
    """Return an ``EXCEPT ALL`` of multiple selectables.

    The returned object is an instance of [sqlalchemy.sql.expression#CompoundSelect].

    \*selects
      a list of [sqlalchemy.sql.expression#Select] instances.

    \**kwargs
      available keyword arguments are the same as those of
      [sqlalchemy.sql.expression#select()].
    """
    return _compound_select('EXCEPT ALL', *selects, **kwargs)

def intersect(*selects, **kwargs):
    """Return an ``INTERSECT`` of multiple selectables.

    The returned object is an instance of [sqlalchemy.sql.expression#CompoundSelect].

    \*selects
      a list of [sqlalchemy.sql.expression#Select] instances.

    \**kwargs
      available keyword arguments are the same as those of
      [sqlalchemy.sql.expression#select()].
    """
    return _compound_select('INTERSECT', *selects, **kwargs)

def intersect_all(*selects, **kwargs):
    """Return an ``INTERSECT ALL`` of multiple selectables.

    The returned object is an instance of [sqlalchemy.sql.expression#CompoundSelect].

    \*selects
      a list of [sqlalchemy.sql.expression#Select] instances.

    \**kwargs
      available keyword arguments are the same as those of
      [sqlalchemy.sql.expression#select()].
    """
    return _compound_select('INTERSECT ALL', *selects, **kwargs)

def alias(selectable, alias=None):
    """Return an [sqlalchemy.sql.expression#Alias] object.

    An ``Alias`` represents any [sqlalchemy.sql.expression#FromClause] with
    an alternate name assigned within SQL, typically using the ``AS``
    clause when generated, e.g. ``SELECT * FROM table AS aliasname``.

    Similar functionality is available via the ``alias()`` method
    available on all ``FromClause`` subclasses.

      selectable
        any ``FromClause`` subclass, such as a table, select
        statement, etc..

      alias
        string name to be assigned as the alias.  If ``None``, a
        random name will be generated.
    """

    return Alias(selectable, alias=alias)


def literal(value, type_=None):
    """Return a literal clause, bound to a bind parameter.

    Literal clauses are created automatically when non-
    ``ClauseElement`` objects (such as strings, ints, dates, etc.) are
    used in a comparison operation with a
    [sqlalchemy.sql.expression#_CompareMixin] subclass, such as a ``Column``
    object.  Use this function to force the generation of a literal
    clause, which will be created as a
    [sqlalchemy.sql.expression#_BindParamClause] with a bound value.

    value
      the value to be bound.  Can be any Python object supported by
      the underlying DB-API, or is translatable via the given type
      argument.

    type\_
      an optional [sqlalchemy.types#TypeEngine] which will provide
      bind-parameter translation for this literal.
    """

    return _BindParamClause(None, value, type_=type_, unique=True)

def label(name, obj):
    """Return a [sqlalchemy.sql.expression#_Label] object for the given [sqlalchemy.sql.expression#ColumnElement].

    A label changes the name of an element in the columns clause of a
    ``SELECT`` statement, typically via the ``AS`` SQL keyword.

    This functionality is more conveniently available via the
    ``label()`` method on ``ColumnElement``.

    name
      label name

    obj
      a ``ColumnElement``.
    """

    return _Label(name, obj)

def column(text, type_=None):
    """Return a textual column clause, as would be in the columns clause of a ``SELECT`` statement.

    The object returned is an instance of [sqlalchemy.sql.expression#_ColumnClause],
    which represents the "syntactical" portion of the schema-level
    [sqlalchemy.schema#Column] object.

    text
      the name of the column.  Quoting rules will be applied to the
      clause like any other column name.  For textual column
      constructs that are not to be quoted, use the
      [sqlalchemy.sql.expression#literal_column()] function.

    type\_
      an optional [sqlalchemy.types#TypeEngine] object which will
      provide result-set translation for this column.

    """

    return _ColumnClause(text, type_=type_)

def literal_column(text, type_=None):
    """Return a textual column expression, as would be in the columns
    clause of a ``SELECT`` statement.

    The object returned supports further expressions in the same way as any
    other column object, including comparison, math and string operations.
    The type\_ parameter is important to determine proper expression behavior
    (such as, '+' means string concatenation or numerical addition based on
    the type).

    text
      the text of the expression; can be any SQL expression.  Quoting rules
      will not be applied.  To specify a column-name expression which should
      be subject to quoting rules, use the
      [sqlalchemy.sql.expression#column()] function.

    type\_
      an optional [sqlalchemy.types#TypeEngine] object which will provide
      result-set translation and additional expression semantics for this
      column.  If left as None the type will be NullType.
    """

    return _ColumnClause(text, type_=type_, is_literal=True)

def table(name, *columns):
    """Return a [sqlalchemy.sql.expression#Table] object.

    This is a primitive version of the [sqlalchemy.schema#Table] object,
    which is a subclass of this object.
    """

    return TableClause(name, *columns)

def bindparam(key, value=None, shortname=None, type_=None, unique=False):
    """Create a bind parameter clause with the given key.

    value
      a default value for this bind parameter.  a bindparam with a
      value is called a ``value-based bindparam``.

    type\_
      a sqlalchemy.types.TypeEngine object indicating the type of this
      bind param, will invoke type-specific bind parameter processing

    shortname
      deprecated.

    unique
      if True, bind params sharing the same name will have their
      underlying ``key`` modified to a uniquely generated name.
      mostly useful with value-based bind params.
    """

    if isinstance(key, _ColumnClause):
        return _BindParamClause(key.name, value, type_=key.type, unique=unique, shortname=shortname)
    else:
        return _BindParamClause(key, value, type_=type_, unique=unique, shortname=shortname)

def outparam(key, type_=None):
    """Create an 'OUT' parameter for usage in functions (stored procedures), for databases which support them.

    The ``outparam`` can be used like a regular function parameter.
    The "output" value will be available from the
    [sqlalchemy.engine#ResultProxy] object via its ``out_parameters``
    attribute, which returns a dictionary containing the values.
    """

    return _BindParamClause(key, None, type_=type_, unique=False, isoutparam=True)

def text(text, bind=None, *args, **kwargs):
    """Create literal text to be inserted into a query.

    When constructing a query from a ``select()``, ``update()``,
    ``insert()`` or ``delete()``, using plain strings for argument
    values will usually result in text objects being created
    automatically.  Use this function when creating textual clauses
    outside of other ``ClauseElement`` objects, or optionally wherever
    plain text is to be used.

    text
      the text of the SQL statement to be created.  use ``:<param>``
      to specify bind parameters; they will be compiled to their
      engine-specific format.

    bind
      an optional connection or engine to be used for this text query.

    autocommit=True
      indicates this SELECT statement modifies the database, and
      should be subject to autocommit behavior if no transaction
      has been started.

    bindparams
      a list of ``bindparam()`` instances which can be used to define
      the types and/or initial values for the bind parameters within
      the textual statement; the keynames of the bindparams must match
      those within the text of the statement.  The types will be used
      for pre-processing on bind values.

    typemap
      a dictionary mapping the names of columns represented in the
      ``SELECT`` clause of the textual statement to type objects,
      which will be used to perform post-processing on columns within
      the result set (for textual statements that produce result
      sets).