expression.py

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

# sql.py
# Copyright (C) 2005, 2006, 2007, 2008 Michael Bayer [e-mail]
#
# This module is part of SQLAlchemy and is released under
# the MIT License: http://www.opensource.org/licenses/mit-license.php

"""Defines the base components of SQL expression trees.

All components are derived from a common base class
[sqlalchemy.sql.expression#ClauseElement].  Common behaviors are organized
based on class hierarchies, in some cases via mixins.

All object construction from this package occurs via functions which
in some cases will construct composite ``ClauseElement`` structures
together, and in other cases simply return a single ``ClauseElement``
constructed directly.  The function interface affords a more "DSL-ish"
feel to constructing SQL expressions and also allows future class
reorganizations.

Even though classes are not constructed directly from the outside,
most classes which have additional public methods are considered to be
public (i.e. have no leading underscore).  Other classes which are
"semi-public" are marked with a single leading underscore; these
classes usually have few or no public methods and are less guaranteed
to stay the same in future releases.
"""

import itertools, re
from sqlalchemy import util, exceptions
from sqlalchemy.sql import operators, visitors
from sqlalchemy import types as sqltypes

functions, schema, sql_util = None, None, None
DefaultDialect, ClauseAdapter = None, None

__all__ = [
    'Alias', 'ClauseElement',
    'ColumnCollection', 'ColumnElement',
    'CompoundSelect', 'Delete', 'FromClause', 'Insert', 'Join',
    'Select', 'Selectable', 'TableClause', 'Update', 'alias', 'and_', 'asc',
    'between', 'bindparam', 'case', 'cast', 'column', 'delete',
    'desc', 'distinct', 'except_', 'except_all', 'exists', 'extract', 'func',
    'modifier',
    'insert', 'intersect', 'intersect_all', 'join', 'literal',
    'literal_column', 'not_', 'null', 'or_', 'outparam', 'outerjoin', 'select',
    'subquery', 'table', 'text', 'union', 'union_all', 'update', ]



def desc(column):
    """Return a descending ``ORDER BY`` clause element.

    e.g.::

      order_by = [desc(table1.mycol)]
    """
    return _UnaryExpression(column, modifier=operators.desc_op)

def asc(column):
    """Return an ascending ``ORDER BY`` clause element.

    e.g.::

      order_by = [asc(table1.mycol)]
    """
    return _UnaryExpression(column, modifier=operators.asc_op)

def outerjoin(left, right, onclause=None, **kwargs):
    """Return an ``OUTER JOIN`` clause element.

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

    Similar functionality is also available via the ``outerjoin()``
    method on any [sqlalchemy.sql.expression#FromClause].

    left
      The left side of the join.

    right
      The right side of the join.

    onclause
      Optional criterion for the ``ON`` clause, is derived from
      foreign key relationships established between left and right
      otherwise.

    To chain joins together, use the ``join()`` or ``outerjoin()``
    methods on the resulting ``Join`` object.
    """

    return Join(left, right, onclause, isouter = True, **kwargs)

def join(left, right, onclause=None, **kwargs):
    """Return a ``JOIN`` clause element (regular inner join).

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

    Similar functionality is also available via the ``join()`` method
    on any [sqlalchemy.sql.expression#FromClause].

    left
      The left side of the join.

    right
      The right side of the join.

    onclause
      Optional criterion for the ``ON`` clause, is derived from
      foreign key relationships established between left and right
      otherwise.

    To chain joins together, use the ``join()`` or ``outerjoin()``
    methods on the resulting ``Join`` object.
    """

    return Join(left, right, onclause, **kwargs)

def select(columns=None, whereclause=None, from_obj=[], **kwargs):
    """Returns a ``SELECT`` clause element.

    Similar functionality is also available via the ``select()``
    method on any [sqlalchemy.sql.expression#FromClause].

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

    All arguments which accept ``ClauseElement`` arguments also accept
    string arguments, which will be converted as appropriate into
    either ``text()`` or ``literal_column()`` constructs.

    columns
      A list of ``ClauseElement`` objects, typically ``ColumnElement``
      objects or subclasses, which will form the columns clause of the
      resulting statement.  For all members which are instances of
      ``Selectable``, the individual ``ColumnElement`` members of the
      ``Selectable`` will be added individually to the columns clause.
      For example, specifying a ``Table`` instance will result in all
      the contained ``Column`` objects within to be added to the
      columns clause.

      This argument is not present on the form of ``select()``
      available on ``Table``.

    whereclause
      A ``ClauseElement`` expression which will be used to form the
      ``WHERE`` clause.

    from_obj
      A list of ``ClauseElement`` objects which will be added to the
      ``FROM`` clause of the resulting statement.  Note that "from"
      objects are automatically located within the columns and
      whereclause ClauseElements.  Use this parameter to explicitly
      specify "from" objects which are not automatically locatable.
      This could include ``Table`` objects that aren't otherwise
      present, or ``Join`` objects whose presence will supercede that
      of the ``Table`` objects already located in the other clauses.

    \**kwargs
      Additional parameters include:

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

      prefixes
        a list of strings or ``ClauseElement`` objects to include
        directly after the SELECT keyword in the generated statement,
        for dialect-specific query features.

      distinct=False
        when ``True``, applies a ``DISTINCT`` qualifier to the columns
        clause of the resulting statement.

      use_labels=False
        when ``True``, the statement will be generated using labels
        for each column in the columns clause, which qualify each
        column with its parent table's (or aliases) name so that name
        conflicts between columns in different tables don't occur.
        The format of the label is <tablename>_<column>.  The "c"
        collection of the resulting ``Select`` object will use these
        names as well for targeting column members.

      for_update=False
        when ``True``, applies ``FOR UPDATE`` to the end of the
        resulting statement.  Certain database dialects also support
        alternate values for this parameter, for example mysql
        supports "read" which translates to ``LOCK IN SHARE MODE``,
        and oracle supports "nowait" which translates to ``FOR UPDATE
        NOWAIT``.

      correlate=True
        indicates that this ``Select`` object should have its
        contained ``FromClause`` elements "correlated" to an enclosing
        ``Select`` object.  This means that any ``ClauseElement``
        instance within the "froms" collection of this ``Select``
        which is also present in the "froms" collection of an
        enclosing select will not be rendered in the ``FROM`` clause
        of this select statement.

      group_by
        a list of ``ClauseElement`` objects which will comprise the
        ``GROUP BY`` clause of the resulting select.

      having
        a ``ClauseElement`` that will comprise the ``HAVING`` clause
        of the resulting select when ``GROUP BY`` is used.

      order_by
        a scalar or list of ``ClauseElement`` objects which will
        comprise the ``ORDER BY`` clause of the resulting select.

      limit=None
        a numerical value which usually compiles to a ``LIMIT``
        expression in the resulting select.  Databases that don't
        support ``LIMIT`` will attempt to provide similar
        functionality.

      offset=None
        a numeric value which usually compiles to an ``OFFSET``
        expression in the resulting select.  Databases that don't
        support ``OFFSET`` will attempt to provide similar
        functionality.

      bind=None
        an ``Engine`` or ``Connection`` instance to which the
        resulting ``Select ` object will be bound.  The ``Select``
        object will otherwise automatically bind to whatever
        ``Connectable`` instances can be located within its contained
        ``ClauseElement`` members.

      scalar=False
        deprecated.  Use select(...).as_scalar() to create a "scalar
        column" proxy for an existing Select object.
    """

    if 'scalar' in kwargs:
        util.warn_deprecated('scalar option is deprecated; see docs for details')
    scalar = kwargs.pop('scalar', False)
    s = Select(columns, whereclause=whereclause, from_obj=from_obj, **kwargs)
    if scalar:
        return s.as_scalar()
    else:
        return s

def subquery(alias, *args, **kwargs):
    """Return an [sqlalchemy.sql.expression#Alias] object derived from a [sqlalchemy.sql.expression#Select].

    name
      alias name

    \*args, \**kwargs

      all other arguments are delivered to the [sqlalchemy.sql.expression#select()]
      function.
    """

    return Select(*args, **kwargs).alias(alias)

def insert(table, values=None, inline=False, **kwargs):
    """Return an [sqlalchemy.sql.expression#Insert] clause element.

    Similar functionality is available via the ``insert()`` method on
    [sqlalchemy.schema#Table].

    table
      The table to be inserted into.

    values
      A dictionary which specifies the column specifications of the
      ``INSERT``, and is optional.  If left as None, the column
      specifications are determined from the bind parameters used
      during the compile phase of the ``INSERT`` statement.  If the
      bind parameters also are None during the compile phase, then the
      column specifications will be generated from the full list of
      table columns.

    inline
      if True, SQL defaults will be compiled 'inline' into the statement
      and not pre-executed.

    If both `values` and compile-time bind parameters are present, the
    compile-time bind parameters override the information specified
    within `values` on a per-key basis.

    The keys within `values` can be either ``Column`` objects or their
    string identifiers.  Each key may reference one of:

    * a literal data value (i.e. string, number, etc.);
    * a Column object;
    * a SELECT statement.

    If a ``SELECT`` statement is specified which references this
    ``INSERT`` statement's table, the statement will be correlated
    against the ``INSERT`` statement.
    """

    return Insert(table, values, inline=inline, **kwargs)

def update(table, whereclause=None, values=None, inline=False, **kwargs):
    """Return an [sqlalchemy.sql.expression#Update] clause element.

    Similar functionality is available via the ``update()`` method on
    [sqlalchemy.schema#Table].

    table
      The table to be updated.

    whereclause
      A ``ClauseElement`` describing the ``WHERE`` condition of the
      ``UPDATE`` statement.

    values
      A dictionary which specifies the ``SET`` conditions of the
      ``UPDATE``, and is optional. If left as None, the ``SET``
      conditions are determined from the bind parameters used during
      the compile phase of the ``UPDATE`` statement.  If the bind
      parameters also are None during the compile phase, then the
      ``SET`` conditions will be generated from the full list of table
      columns.

    inline
      if True, SQL defaults will be compiled 'inline' into the statement
      and not pre-executed.


    If both `values` and compile-time bind parameters are present, the
    compile-time bind parameters override the information specified
    within `values` on a per-key basis.

    The keys within `values` can be either ``Column`` objects or their
    string identifiers. Each key may reference one of:

    * a literal data value (i.e. string, number, etc.);
    * a Column object;
    * a SELECT statement.

    If a ``SELECT`` statement is specified which references this
    ``UPDATE`` statement's table, the statement will be correlated
    against the ``UPDATE`` statement.
    """

    return Update(table, whereclause=whereclause, values=values, inline=inline, **kwargs)

def delete(table, whereclause = None, **kwargs):
    """Return a [sqlalchemy.sql.expression#Delete] clause element.

    Similar functionality is available via the ``delete()`` method on
    [sqlalchemy.schema#Table].

    table
      The table to be updated.

    whereclause
      A ``ClauseElement`` describing the ``WHERE`` condition of the
      ``UPDATE`` statement.
    """

    return Delete(table, whereclause, **kwargs)

def and_(*clauses):
    """Join a list of clauses together using the ``AND`` operator.

    The ``&`` operator is also overloaded on all
    [sqlalchemy.sql.expression#_CompareMixin] subclasses to produce the same
    result.
    """
    if len(clauses) == 1:
        return clauses[0]
    return ClauseList(operator=operators.and_, *clauses)

def or_(*clauses):
    """Join a list of clauses together using the ``OR`` operator.

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