diff options
Diffstat (limited to 'lib/sqlalchemy/sql.py')
| -rw-r--r-- | lib/sqlalchemy/sql.py | 1612 |
1 files changed, 817 insertions, 795 deletions
diff --git a/lib/sqlalchemy/sql.py b/lib/sqlalchemy/sql.py index 2f1cd4b91..7c73f7cb7 100644 --- a/lib/sqlalchemy/sql.py +++ b/lib/sqlalchemy/sql.py @@ -4,46 +4,50 @@ # This module is part of SQLAlchemy and is released under # the MIT License: http://www.opensource.org/licenses/mit-license.php -"""Define the base components of SQL expression trees. - -All components are derived from a common base class [sqlalchemy.sql#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. - +"""Defines the base components of SQL expression trees. + + +All components are derived from a common base class +[sqlalchemy.sql#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. """ from sqlalchemy import util, exceptions, operators from sqlalchemy import types as sqltypes import re -__all__ = ['Alias', 'ClauseElement', 'ClauseParameters', - 'ClauseVisitor', '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',] +__all__ = [ + 'Alias', 'ClauseElement', 'ClauseParameters', + 'ClauseVisitor', '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', ] BIND_PARAMS = re.compile(r'(?<![:\w\x5c]):(\w+)(?!:)', re.UNICODE) def desc(column): """Return a descending ``ORDER BY`` clause element. - E.g.:: + e.g.:: order_by = [desc(table1.mycol)] """ @@ -52,7 +56,7 @@ def desc(column): def asc(column): """Return an ascending ``ORDER BY`` clause element. - E.g.:: + e.g.:: order_by = [asc(table1.mycol)] """ @@ -60,22 +64,22 @@ def asc(column): def outerjoin(left, right, onclause=None, **kwargs): """Return an ``OUTER JOIN`` clause element. - + The returned object is an instance of [sqlalchemy.sql#Join]. - Similar functionality is also available via the ``outerjoin()`` method on any - [sqlalchemy.sql#FromClause]. + Similar functionality is also available via the ``outerjoin()`` + method on any [sqlalchemy.sql#FromClause]. - left - The left side of the join. + left + The left side of the join. - right - The right 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. + 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. @@ -88,19 +92,19 @@ def join(left, right, onclause=None, **kwargs): The returned object is an instance of [sqlalchemy.sql#Join]. - Similar functionality is also available via the ``join()`` method on any - [sqlalchemy.sql#FromClause]. + Similar functionality is also available via the ``join()`` method + on any [sqlalchemy.sql#FromClause]. - left - The left side of the join. + left + The left side of the join. - right - The right 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. + 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. @@ -111,109 +115,116 @@ def join(left, right, onclause=None, **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#FromClause]. - + Similar functionality is also available via the ``select()`` + method on any [sqlalchemy.sql#FromClause]. + The returned object is an instance of [sqlalchemy.sql#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: - - order_by - a scalar or list of ``ClauseElement`` objects - which will comprise the ``ORDER BY`` clause of the resulting - select. - - 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. - - 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. - - distinct=False - when ``True``, applies a ``DISTINCT`` qualifier to the - columns clause of the resulting statement. - - 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``. - - 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. - - 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 numerical value which usually compiles to an ``OFFSET`` expression - in the resulting select. Databases that don't support ``OFFSET`` - will attempt to provide similar functionality. - - scalar=False - deprecated. use select(...).as_scalar() to create a "scalar column" - proxy for an existing Select object. - - 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. - + 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: + + 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) @@ -225,34 +236,35 @@ def select(columns=None, whereclause=None, from_obj=[], **kwargs): def subquery(alias, *args, **kwargs): """Return an [sqlalchemy.sql#Alias] object derived from a [sqlalchemy.sql#Select]. - - name - alias name - \*args, \**kwargs - all other arguments are delivered to the [sqlalchemy.sql#select()] function. - + name + alias name + + \*args, \**kwargs + + all other arguments are delivered to the [sqlalchemy.sql#select()] + function. """ - + return Select(*args, **kwargs).alias(alias) def insert(table, values = None, **kwargs): """Return an [sqlalchemy.sql#Insert] clause element. - Similar functionality is available via the ``insert()`` - method on [sqlalchemy.schema#Table]. + Similar functionality is available via the ``insert()`` method on + [sqlalchemy.schema#Table]. - table - The table to be inserted into. + 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. + 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. If both `values` and compile-time bind parameters are present, the compile-time bind parameters override the information specified @@ -275,24 +287,24 @@ def insert(table, values = None, **kwargs): def update(table, whereclause = None, values = None, **kwargs): """Return an [sqlalchemy.sql#Update] clause element. - Similar functionality is available via the ``update()`` - method on [sqlalchemy.schema#Table]. + Similar functionality is available via the ``update()`` method on + [sqlalchemy.schema#Table]. - table - The table to be updated. + table + The table to be updated. - whereclause - A ``ClauseElement`` describing the ``WHERE`` condition of the - ``UPDATE`` statement. + 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. + 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. If both `values` and compile-time bind parameters are present, the compile-time bind parameters override the information specified @@ -315,16 +327,15 @@ def update(table, whereclause = None, values = None, **kwargs): def delete(table, whereclause = None, **kwargs): """Return a [sqlalchemy.sql#Delete] clause element. - Similar functionality is available via the ``delete()`` - method on [sqlalchemy.schema#Table]. - - table - The table to be updated. + Similar functionality is available via the ``delete()`` method on + [sqlalchemy.schema#Table]. - whereclause - A ``ClauseElement`` describing the ``WHERE`` condition of the - ``UPDATE`` statement. + table + The table to be updated. + whereclause + A ``ClauseElement`` describing the ``WHERE`` condition of the + ``UPDATE`` statement. """ return Delete(table, whereclause, **kwargs) @@ -332,8 +343,9 @@ def delete(table, whereclause = None, **kwargs): def and_(*clauses): """Join a list of clauses together using the ``AND`` operator. - The ``&`` operator is also overloaded on all [sqlalchemy.sql#_CompareMixin] - subclasses to produce the same result. + The ``&`` operator is also overloaded on all + [sqlalchemy.sql#_CompareMixin] subclasses to produce the same + result. """ if len(clauses) == 1: return clauses[0] @@ -342,8 +354,9 @@ def and_(*clauses): def or_(*clauses): """Join a list of clauses together using the ``OR`` operator. - The ``|`` operator is also overloaded on all [sqlalchemy.sql#_CompareMixin] - subclasses to produce the same result. + The ``|`` operator is also overloaded on all + [sqlalchemy.sql#_CompareMixin] subclasses to produce the same + result. """ if len(clauses) == 1: @@ -353,15 +366,16 @@ def 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#_CompareMixin] - subclasses to produce the same result. + The ``~`` operator is also overloaded on all + [sqlalchemy.sql#_CompareMixin] subclasses to produce the same + result. """ return operators.inv(clause) def distinct(expr): - """return a ``DISTINCT`` clause.""" - + """Return a ``DISTINCT`` clause.""" + return _UnaryExpression(expr, operator=operators.distinct_op) def between(ctest, cleft, cright): @@ -380,18 +394,18 @@ def between(ctest, cleft, cright): 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. + whens + A sequence of pairs to be translated into "when / then" clauses. - else\_ - Optional as well, for case defaults. + 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] + 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: @@ -425,128 +439,128 @@ def extract(field, expr): def exists(*args, **kwargs): """Return an ``EXISTS`` clause as applied to a [sqlalchemy.sql#Select] object. - - The resulting [sqlalchemy.sql#_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#select()] function - to produce a ``SELECT`` statement. - + + The resulting [sqlalchemy.sql#_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#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#CompoundSelect]. - - A similar ``union()`` method is available on all [sqlalchemy.sql#FromClause] - subclasses. - - \*selects - a list of [sqlalchemy.sql#Select] instances. - - \**kwargs - available keyword arguments are the same as those of [sqlalchemy.sql#select()]. - + + A similar ``union()`` method is available on all + [sqlalchemy.sql#FromClause] subclasses. + + \*selects + a list of [sqlalchemy.sql#Select] instances. + + \**kwargs + available keyword arguments are the same as those of + [sqlalchemy.sql#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#CompoundSelect]. - - A similar ``union_all()`` method is available on all [sqlalchemy.sql#FromClause] - subclasses. - \*selects - a list of [sqlalchemy.sql#Select] instances. - - \**kwargs - available keyword arguments are the same as those of [sqlalchemy.sql#select()]. - + A similar ``union_all()`` method is available on all + [sqlalchemy.sql#FromClause] subclasses. + + \*selects + a list of [sqlalchemy.sql#Select] instances. + + \**kwargs + available keyword arguments are the same as those of + [sqlalchemy.sql#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#CompoundSelect]. - \*selects - a list of [sqlalchemy.sql#Select] instances. - - \**kwargs - available keyword arguments are the same as those of [sqlalchemy.sql#select()]. - + \*selects + a list of [sqlalchemy.sql#Select] instances. + + \**kwargs + available keyword arguments are the same as those of + [sqlalchemy.sql#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#CompoundSelect]. - \*selects - a list of [sqlalchemy.sql#Select] instances. - - \**kwargs - available keyword arguments are the same as those of [sqlalchemy.sql#select()]. - + \*selects + a list of [sqlalchemy.sql#Select] instances. + + \**kwargs + available keyword arguments are the same as those of + [sqlalchemy.sql#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#CompoundSelect]. - \*selects - a list of [sqlalchemy.sql#Select] instances. - - \**kwargs - available keyword arguments are the same as those of [sqlalchemy.sql#select()]. - + \*selects + a list of [sqlalchemy.sql#Select] instances. + + \**kwargs + available keyword arguments are the same as those of + [sqlalchemy.sql#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#CompoundSelect]. - \*selects - a list of [sqlalchemy.sql#Select] instances. - - \**kwargs - available keyword arguments are the same as those of [sqlalchemy.sql#select()]. - + \*selects + a list of [sqlalchemy.sql#Select] instances. + + \**kwargs + available keyword arguments are the same as those of + [sqlalchemy.sql#select()]. """ return _compound_select('INTERSECT ALL', *selects, **kwargs) def alias(selectable, alias=None): """Return an [sqlalchemy.sql#Alias] object. - + An ``Alias`` represents any [sqlalchemy.sql#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 + + Similar functionality is available via the ``alias()`` method available on all ``FromClause`` subclasses. - + selectable - any ``FromClause`` subclass, such as a table, select statement, etc.. - + 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. - + string name to be assigned as the alias. If ``None``, a + random name will be generated. """ - + return Alias(selectable, alias=alias) @@ -554,87 +568,80 @@ 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#_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#_BindParamClause] with a bound - value. - - value - the value to be bound. can be any Python object supported by - the underlying DBAPI, or is translatable via the given type - argument. - - type\_ - an optional [sqlalchemy.types#TypeEngine] which will provide - bind-parameter translation for this literal. - + ``ClauseElement`` objects (such as strings, ints, dates, etc.) are + used in a comparison operation with a + [sqlalchemy.sql#_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#_BindParamClause] with a bound value. + + value + the value to be bound. Can be any Python object supported by + the underlying DBAPI, 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('literal', value, type_=type_, unique=True) def label(name, obj): """Return a [sqlalchemy.sql#_Label] object for the given [sqlalchemy.sql#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``. - + + 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. - + """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#_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#literal_column()] - function. - - type\_ - an optional [sqlalchemy.types#TypeEngine] object which will provide - result-set translation for this column. - + + 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#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 clause, as would be in the columns - clause of a ``SELECT`` statement. - + """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#_ColumnClause], which represents the "syntactical" portion of the schema-level [sqlalchemy.schema#Column] object. - - - text - the name of the column. Quoting rules will not be applied - to the column. For textual column - constructs that should be quoted like any other column - construct, use the [sqlalchemy.sql#column()] - function. - - type - an optional [sqlalchemy.types#TypeEngine] object which will provide - result-set translation for this column. - + + text + the name of the column. Quoting rules will not be applied to + the column. For textual column constructs that should be quoted + like any other column construct, use the + [sqlalchemy.sql#column()] function. + + type + an optional [sqlalchemy.types#TypeEngine] object which will + provide result-set translation for this column. """ return _ColumnClause(text, type_=type_, is_literal=True) @@ -642,8 +649,8 @@ def literal_column(text, type_=None): def table(name, *columns): """Return a [sqlalchemy.sql#Table] object. - This is a primitive version of the [sqlalchemy.schema#Table] object, which - is a subclass of this object. + This is a primitive version of the [sqlalchemy.schema#Table] object, + which is a subclass of this object. """ return TableClause(name, *columns) @@ -651,22 +658,23 @@ def table(name, *columns): def bindparam(key, value=None, type_=None, shortname=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``. + value + a default value for this bind parameter. a bindparam with a + value is called a ``value-based bindparam``. - shortname - an ``alias`` for this bind parameter. usually used to alias the ``key`` and - ``label`` of a column, i.e. ``somecolname`` and ``sometable_somecolname`` + shortname + an ``alias`` for this bind parameter. usually used to alias the + ``key`` nd ``label`` of a column, i.e. ``somecolname`` and + ``sometable_somecolname`` - type - a sqlalchemy.types.TypeEngine object indicating the type of this bind param, will - invoke type-specific bind parameter processing + type + a sqlalchemy.types.TypeEngine object indicating the type of this + bind param, will invoke type-specific bind parameter processing - 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. - + 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): @@ -675,16 +683,16 @@ def bindparam(key, value=None, type_=None, shortname=None, unique=False): return _BindParamClause(key, value, type_=type_, shortname=shortname, unique=unique) def outparam(key, type_=None): - """create an 'OUT' parameter for usage in functions (stored procedures), for databases - whith 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`` + """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. @@ -695,28 +703,27 @@ def text(text, bind=None, *args, **kwargs): 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. - - 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). - + 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. + + 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). """ return _TextClause(text, bind=bind, *args, **kwargs) @@ -740,7 +747,7 @@ class _FunctionGenerator(object): return self.__dict__[name] except KeyError: raise AttributeError(name) - + elif name.startswith('_'): name = name[0:-1] f = _FunctionGenerator(**self.opts) @@ -757,7 +764,7 @@ func = _FunctionGenerator() # TODO: use UnaryExpression for this instead ? modifier = _FunctionGenerator(group=False) - + def _compound_select(keyword, *selects, **kwargs): return CompoundSelect(keyword, *selects, **kwargs) @@ -779,7 +786,7 @@ def _literal_as_column(element): return literal_column(str(element)) else: return element - + def _literal_as_binds(element, name='literal', type_=None): if isinstance(element, Operators): return element.expression_element() @@ -798,17 +805,19 @@ def _selectable(element): return element else: raise exceptions.ArgumentError("Object '%s' is not a Selectable and does not implement `__selectable__()`" % repr(element)) - + def is_column(col): + """True if ``col`` is an instance of ``ColumnElement``.""" return isinstance(col, ColumnElement) class ClauseParameters(object): """Represent a dictionary/iterator of bind parameter key names/values. - Tracks the original [sqlalchemy.sql#_BindParamClause] objects as well as the - keys/position of each parameter, and can return parameters as a - dictionary or a list. Will process parameter values according to - the ``TypeEngine`` objects present in the ``_BindParamClause`` instances. + Tracks the original [sqlalchemy.sql#_BindParamClause] objects + as well as the keys/position of each parameter, and can return + parameters as a dictionary or a list. Will process parameter + values according to the ``TypeEngine`` objects present in the + ``_BindParamClause`` instances. """ def __init__(self, dialect, positional=None): @@ -821,7 +830,7 @@ class ClauseParameters(object): def set_parameter(self, bindparam, value, name): self.__binds[name] = [bindparam, name, value] - + def get_original(self, key): return self.__binds[key][2] @@ -831,34 +840,34 @@ class ClauseParameters(object): def get_processors(self): """return a dictionary of bind 'processing' functions""" return dict([ - (key, value) for key, value in + (key, value) for key, value in [( key, self.__binds[key][0].bind_processor(self.dialect) ) for key in self.__binds] if value is not None ]) - + def get_processed(self, key, processors): return key in processors and processors[key](self.__binds[key][2]) or self.__binds[key][2] - + def keys(self): return self.__binds.keys() def __iter__(self): return iter(self.keys()) - + def __getitem__(self, key): (bind, name, value) = self.__binds[key] processor = bind.bind_processor(self.dialect) return processor is not None and processor(value) or value - + def __contains__(self, key): return key in self.__binds - + def set_value(self, key, value): self.__binds[key][2] = value - + def get_original_dict(self): return dict([(name, value) for (b, name, value) in self.__binds.values()]) @@ -867,7 +876,7 @@ class ClauseParameters(object): return processors[key](self.__binds[key][2]) else: return self.__binds[key][2] - + def get_raw_list(self, processors): return [self.__get_processed(key, processors) for key in self.positional] @@ -893,32 +902,29 @@ class ClauseParameters(object): return self.__class__.__name__ + ":" + repr(self.get_original_dict()) class ClauseVisitor(object): - """A class that knows how to traverse and visit - ``ClauseElements``. - - Calls visit_XXX() methods dynamically generated for each particualr - ``ClauseElement`` subclass encountered. Traversal of a - hierarchy of ``ClauseElements`` is achieved via the - ``traverse()`` method, which is passed the lead - ``ClauseElement``. - - By default, ``ClauseVisitor`` traverses all elements - fully. Options can be specified at the class level via the - ``__traverse_options__`` dictionary which will be passed - to the ``get_children()`` method of each ``ClauseElement``; - these options can indicate modifications to the set of - elements returned, such as to not return column collections - (column_collections=False) or to return Schema-level items - (schema_visitor=True). - + """A class that knows how to traverse and visit ``ClauseElements``. + + Calls visit_XXX() methods dynamically generated for each + particualr ``ClauseElement`` subclass encountered. Traversal of a + hierarchy of ``ClauseElements`` is achieved via the ``traverse()`` + method, which is passed the lead ``ClauseElement``. + + By default, ``ClauseVisitor`` traverses all elements fully. + Options can be specified at the class level via the + ``__traverse_options__`` dictionary which will be passed to the + ``get_children()`` method of each ``ClauseElement``; these options + can indicate modifications to the set of elements returned, such + as to not return column collections (column_collections=False) or + to return Schema-level items (schema_visitor=True). + ``ClauseVisitor`` also supports a simultaneous copy-and-traverse operation, which will produce a copy of a given ``ClauseElement`` - structure while at the same time allowing ``ClauseVisitor`` subclasses - to modify the new structure in-place. - + structure while at the same time allowing ``ClauseVisitor`` + subclasses to modify the new structure in-place. """ + __traverse_options__ = {} - + def traverse_single(self, obj, **kwargs): meth = getattr(self, "visit_%s" % obj.__visit_name__, None) if meth: @@ -934,11 +940,11 @@ class ClauseVisitor(object): traversal.insert(0, t) for c in t.get_children(**self.__traverse_options__): stack.append(c) - + def traverse(self, obj, stop_on=None, clone=False): if clone: obj = obj._clone() - + stack = [obj] traversal = [] while len(stack) > 0: @@ -960,8 +966,10 @@ class ClauseVisitor(object): def chain(self, visitor): """'chain' an additional ClauseVisitor onto this ClauseVisitor. - - the chained visitor will receive all visit events after this one.""" + + The chained visitor will receive all visit events after this one. + """ + tail = self while getattr(tail, '_next', None) is not None: tail = tail._next @@ -969,16 +977,19 @@ class ClauseVisitor(object): return self class NoColumnVisitor(ClauseVisitor): - """a ClauseVisitor that will not traverse the exported Column - collections on Table, Alias, Select, and CompoundSelect objects - (i.e. their 'columns' or 'c' attribute). - - this is useful because most traversals don't need those columns, or - in the case of ANSICompiler it traverses them explicitly; so - skipping their traversal here greatly cuts down on method call overhead. + """A ClauseVisitor that will not traverse exported column collections. + + Will not traverse the exported Column collections on Table, Alias, + Select, and CompoundSelect objects (i.e. their 'columns' or 'c' + attribute). + + This is useful because most traversals don't need those columns, + or in the case of ANSICompiler it traverses them explicitly; so + skipping their traversal here greatly cuts down on method call + overhead. """ - - __traverse_options__ = {'column_collections':False} + + __traverse_options__ = {'column_collections': False} class _FigureVisitName(type): @@ -989,20 +1000,19 @@ class _FigureVisitName(type): x = re.sub(r'(?!^)[A-Z]', lambda m:'_'+m.group(0).lower(), x) cls.__visit_name__ = x.lower() super(_FigureVisitName, cls).__init__(clsname, bases, dict) - + class ClauseElement(object): - """Base class for elements of a programmatically constructed SQL - expression. - """ + """Base class for elements of a programmatically constructed SQL expression.""" __metaclass__ = _FigureVisitName - + def _clone(self): - """create a shallow copy of this ClauseElement. - - This method may be used by a generative API. - Its also used as part of the "deep" copy afforded - by a traversal that combines the _copy_internals() - method.""" + """Create a shallow copy of this ClauseElement. + + This method may be used by a generative API. Its also used as + part of the "deep" copy afforded by a traversal that combines + the _copy_internals() method. + """ + c = self.__class__.__new__(self.__class__) c.__dict__ = self.__dict__.copy() return c @@ -1017,41 +1027,40 @@ class ClauseElement(object): raise NotImplementedError(repr(self)) def _hide_froms(self, **modifiers): - """Return a list of ``FROM`` clause elements which this - ``ClauseElement`` replaces. - """ + """Return a list of ``FROM`` clause elements which this ``ClauseElement`` replaces.""" return [] def unique_params(self, *optionaldict, **kwargs): - """same functionality as ``params()``, except adds `unique=True` to affected - bind parameters so that multiple statements can be used. + """Return a copy with ``bindparam()`` elments replaced. + + Same functionality as ``params()``, except adds `unique=True` + to affected bind parameters so that multiple statements can be + used. """ - + return self._params(True, optionaldict, kwargs) def params(self, *optionaldict, **kwargs): - """return a copy of this ClauseElement, with ``bindparam()`` elements - replaced with values taken from the given dictionary. - - e.g.:: - - >>> clause = column('x') + bindparam('foo') - >>> print clause.compile().params - {'foo':None} - - >>> print clause.params({'foo':7}).compile().params - {'foo':7} - + """Return a copy with ``bindparam()`` elments replaced. + + Returns a copy of this ClauseElement with ``bindparam()`` + elements replaced with values taken from the given dictionary:: + + >>> clause = column('x') + bindparam('foo') + >>> print clause.compile().params + {'foo':None} + >>> print clause.params({'foo':7}).compile().params + {'foo':7} """ - + return self._params(False, optionaldict, kwargs) - + def _params(self, unique, optionaldict, kwargs): if len(optionaldict) == 1: kwargs.update(optionaldict[0]) elif len(optionaldict) > 1: raise exceptions.ArgumentError("params() takes zero or one positional dictionary argument") - + class Vis(ClauseVisitor): def visit_bindparam(self, bind): if bind.key in kwargs: @@ -1059,7 +1068,7 @@ class ClauseElement(object): if unique: bind.unique=True return Vis().traverse(self, clone=True) - + def compare(self, other): """Compare this ClauseElement to the given ClauseElement. @@ -1070,32 +1079,32 @@ class ClauseElement(object): return self is other def _copy_internals(self): - """reassign internal elements to be clones of themselves. - - called during a copy-and-traverse operation on newly - shallow-copied elements to create a deep copy.""" - + """Reassign internal elements to be clones of themselves. + + Called during a copy-and-traverse operation on newly + shallow-copied elements to create a deep copy. + """ + pass - + def get_children(self, **kwargs): - """return immediate child elements of this ``ClauseElement``. - - this is used for visit traversal. - - \**kwargs may contain flags that change the collection - that is returned, for example to return a subset of items - in order to cut down on larger traversals, or to return - child items from a different context (such as schema-level - collections instead of clause-level).""" + """Return immediate child elements of this ``ClauseElement``. + + This is used for visit traversal. + + \**kwargs may contain flags that change the collection that is + returned, for example to return a subset of items in order to + cut down on larger traversals, or to return child items from a + different context (such as schema-level collections instead of + clause-level). + """ return [] - + def self_group(self, against=None): return self def supports_execution(self): - """Return True if this clause element represents a complete - executable statement. - """ + """Return True if this clause element represents a complete executable statement.""" return False @@ -1120,7 +1129,7 @@ class ClauseElement(object): return engine else: return None - + bind = property(lambda s:s._find_engine(), doc="""Returns the Engine or Connection to which this ClauseElement is bound, or None if none found.""") def execute(self, *multiparams, **params): @@ -1133,9 +1142,7 @@ class ClauseElement(object): return self.compile(bind=self.bind, parameters=compile_params).execute(*multiparams, **params) def scalar(self, *multiparams, **params): - """Compile and execute this ``ClauseElement``, returning the - result's scalar representation. - """ + """Compile and execute this ``ClauseElement``, returning the result's scalar representation.""" return self.execute(*multiparams, **params).scalar() @@ -1144,8 +1151,9 @@ class ClauseElement(object): Uses the given ``Compiler``, or the given ``AbstractDialect`` or ``Engine`` to create a ``Compiler``. If no `compiler` - arguments are given, tries to use the underlying ``Engine`` this - ``ClauseElement`` is bound to to create a ``Compiler``, if any. + arguments are given, tries to use the underlying ``Engine`` + this ``ClauseElement`` is bound to to create a ``Compiler``, + if any. Finally, if there is no bound ``Engine``, uses an ``ANSIDialect`` to create a default ``Compiler``. @@ -1218,7 +1226,8 @@ class Operators(object): raise NotImplementedError() class ColumnOperators(Operators): - """defines comparison and math operations""" + """Defines comparison and math operations.""" + def __lt__(self, other): return self.operate(operators.lt, other) @@ -1239,25 +1248,25 @@ class ColumnOperators(Operators): def concat(self, other): return self.operate(operators.concat_op, other) - + def like(self, other): return self.operate(operators.like_op, other) - + def in_(self, *other): return self.operate(operators.in_op, other) - + def startswith(self, other): return self.operate(operators.startswith_op, other) def endswith(self, other): return self.operate(operators.endswith_op, other) - + def desc(self): return self.operate(operators.desc_op) - + def asc(self): return self.operate(operators.asc_op) - + def __radd__(self, other): return self.reverse_operate(operators.add, other) @@ -1275,7 +1284,7 @@ class ColumnOperators(Operators): def distinct(self): return self.operate(operators.distinct_op) - + def __add__(self, other): return self.operate(operators.add, other) @@ -1294,8 +1303,9 @@ class ColumnOperators(Operators): def __truediv__(self, other): return self.operate(operators.truediv, other) -# precedence ordering for common operators. if an operator is not present in this list, -# it will be parenthesized when grouped against other operators +# precedence ordering for common operators. if an operator is not +# present in this list, it will be parenthesized when grouped against +# other operators _smallest = object() _largest = object() @@ -1346,21 +1356,18 @@ class _CompareMixin(ColumnOperators): raise exceptions.ArgumentError("Only '='/'!=' operators can be used with NULL") else: obj = self._check_literal(obj) - - return _BinaryExpression(self.expression_element(), obj, op, type_=sqltypes.Boolean, negate=negate) def __operate(self, op, obj): obj = self._check_literal(obj) type_ = self._compare_type(obj) - - # TODO: generalize operator overloading like this out into the types module + + # TODO: generalize operator overloading like this out into the + # types module if op == operators.add and isinstance(type_, (sqltypes.Concatenable)): op = operators.concat_op - return _BinaryExpression(self.expression_element(), obj, op, type_=type_) - operators = { operators.add : (__operate,), operators.mul : (__operate,), @@ -1380,13 +1387,13 @@ class _CompareMixin(ColumnOperators): def operate(self, op, *other): o = _CompareMixin.operators[op] return o[0](self, op, other[0], *o[1:]) - + def reverse_operate(self, op, other): return self._bind_param(other).operate(op, self) def in_(self, *other): return self._in_impl(operators.in_op, operators.notin_op, *other) - + def _in_impl(self, op, negate_op, *other): if len(other) == 0: return _Grouping(case([(self.__eq__(None), text('NULL'))], else_=text('0')).__eq__(text('1'))) @@ -1409,14 +1416,14 @@ class _CompareMixin(ColumnOperators): return self.__compare(op, ClauseList(*args).self_group(against=op), negate=negate_op) def startswith(self, other): - """produce the clause ``LIKE '<other>%'``""" + """Produce the clause ``LIKE '<other>%'``""" perc = isinstance(other,(str,unicode)) and '%' or literal('%',type_= sqltypes.String) return self.__compare(operators.like_op, other + perc) def endswith(self, other): - """produce the clause ``LIKE '%<other>'``""" - + """Produce the clause ``LIKE '%<other>'``""" + if isinstance(other,(str,unicode)): po = '%' + other else: po = literal('%', type_=sqltypes.String) + other @@ -1424,44 +1431,43 @@ class _CompareMixin(ColumnOperators): return self.__compare(operators.like_op, po) def label(self, name): - """produce a column label, i.e. ``<columnname> AS <name>``""" + """Produce a column label, i.e. ``<columnname> AS <name>``""" return _Label(name, self, self.type) - + def desc(self): - """produce a DESC clause, i.e. ``<columnname> DESC``""" - + """Produce a DESC clause, i.e. ``<columnname> DESC``""" + return desc(self) - + def asc(self): - """produce a ASC clause, i.e. ``<columnname> ASC``""" - + """Produce a ASC clause, i.e. ``<columnname> ASC``""" + return asc(self) - + def distinct(self): - """produce a DISTINCT clause, i.e. ``DISTINCT <columnname>``""" + """Produce a DISTINCT clause, i.e. ``DISTINCT <columnname>``""" return _UnaryExpression(self, operator=operators.distinct_op) def between(self, cleft, cright): - """produce a BETWEEN clause, i.e. ``<column> BETWEEN <cleft> AND <cright>``""" + """Produce a BETWEEN clause, i.e. ``<column> BETWEEN <cleft> AND <cright>``""" return _BinaryExpression(self, ClauseList(self._check_literal(cleft), self._check_literal(cright), operator=operators.and_, group=False), operators.between_op) def op(self, operator): """produce a generic operator function. - - e.g. - - somecolumn.op("*")(5) - - produces - - somecolumn * 5 - + + e.g.:: + + somecolumn.op("*")(5) + + produces:: + + somecolumn * 5 + operator - a string which will be output as the infix operator - between this ``ClauseElement`` and the expression - passed to the generated function. - + a string which will be output as the infix operator between + this ``ClauseElement`` and the expression passed to the + generated function. """ return lambda other: self.__operate(operator, other) @@ -1475,7 +1481,7 @@ class _CompareMixin(ColumnOperators): return self._bind_param(other) else: return other - + def clause_element(self): """Allow ``_CompareMixins`` to return the underlying ``ClauseElement``, for non-``ClauseElement`` ``_CompareMixins``.""" return self @@ -1496,13 +1502,12 @@ class _CompareMixin(ColumnOperators): class Selectable(ClauseElement): """Represent a column list-holding object. - - this is the common base class of [sqlalchemy.sql#ColumnElement] - and [sqlalchemy.sql#FromClause]. The reason ``ColumnElement`` - is marked as a "list-holding" object is so that it can be treated - similarly to ``FromClause`` in column-selection scenarios; it + + This is the common base class of [sqlalchemy.sql#ColumnElement] + and [sqlalchemy.sql#FromClause]. The reason ``ColumnElement`` is + marked as a "list-holding" object is so that it can be treated + similarly to ``FromClause`` in column-selection scenarios; it contains a list of columns consisting of itself. - """ columns = util.NotImplProperty("""a [sqlalchemy.sql#ColumnCollection] containing ``ColumnElement`` instances.""") @@ -1511,23 +1516,22 @@ class Selectable(ClauseElement): return select([self], whereclauses, **params) class ColumnElement(ClauseElement, _CompareMixin): - """Represent an element that is useable within the - "column clause" portion of a ``SELECT`` statement. - - This includes columns associated with tables, aliases, - and subqueries, expressions, function calls, SQL keywords - such as ``NULL``, literals, etc. ``ColumnElement`` is the - ultimate base class for all such elements. + """Represent an element that is usable within the "column clause" portion of a ``SELECT`` statement. + + This includes columns associated with tables, aliases, and + subqueries, expressions, function calls, SQL keywords such as + ``NULL``, literals, etc. ``ColumnElement`` is the ultimate base + class for all such elements. ``ColumnElement`` supports the ability to be a *proxy* element, which indicates that the ``ColumnElement`` may be associated with - a ``Selectable`` which was derived from another ``Selectable``. - An example of a "derived" ``Selectable`` is an ``Alias`` of - a ``Table``. - - a ``ColumnElement``, by subclassing the ``_CompareMixin`` mixin - class, provides the ability to generate new ``ClauseElement`` - objects using Python expressions. See the ``_CompareMixin`` + a ``Selectable`` which was derived from another ``Selectable``. + An example of a "derived" ``Selectable`` is an ``Alias`` of a + ``Table``. + + A ``ColumnElement``, by subclassing the ``_CompareMixin`` mixin + class, provides the ability to generate new ``ClauseElement`` + objects using Python expressions. See the ``_CompareMixin`` docstring for more details. """ @@ -1562,7 +1566,7 @@ class ColumnElement(ClauseElement, _CompareMixin): if len(s) == 0: s.add(self) self.__orig_set = s - + orig_set = property(_get_orig_set, _set_orig_set, doc=\ """A Set containing TableClause-bound, non-proxied ColumnElements @@ -1570,10 +1574,9 @@ class ColumnElement(ClauseElement, _CompareMixin): for a column proxied from a Union (i.e. CompoundSelect), this set will be just one element. """) - + def shares_lineage(self, othercolumn): - """Return True if the given ``ColumnElement`` has a common - ancestor to this ``ColumnElement``. + """Return True if the given ``ColumnElement`` has a common ancestor to this ``ColumnElement``. """ for c in self.orig_set: @@ -1581,7 +1584,7 @@ class ColumnElement(ClauseElement, _CompareMixin): return True else: return False - + def _make_proxy(self, selectable, name=None): """Create a new ``ColumnElement`` representing this ``ColumnElement`` as it appears in the select list of a @@ -1613,7 +1616,7 @@ class ColumnCollection(util.OrderedProperties): def __str__(self): return repr([str(c) for c in self]) - + def add(self, column): """Add a column to this collection. @@ -1628,14 +1631,14 @@ class ColumnCollection(util.OrderedProperties): if other.name == other.key: del self[other.name] self[column.key] = column - + def remove(self, column): del self[column.key] - + def extend(self, iter): for c in iter: self.add(c) - + def __eq__(self, other): l = [] for c in other: @@ -1648,7 +1651,7 @@ class ColumnCollection(util.OrderedProperties): if not isinstance(other, basestring): raise exceptions.ArgumentError("__contains__ requires a string argument") return util.OrderedProperties.__contains__(self, other) - + def contains_column(self, col): # have to use a Set here, because it will compare the identity # of the column, not just using "==" for comparison which will always return a @@ -1658,7 +1661,7 @@ class ColumnCollection(util.OrderedProperties): class ColumnSet(util.OrderedSet): def contains_column(self, col): return col in self - + def extend(self, cols): for col in cols: self.add(col) @@ -1673,14 +1676,12 @@ class ColumnSet(util.OrderedSet): if c.shares_lineage(local): l.append(c==local) return and_(*l) - + class FromClause(Selectable): - """Represent an element that can be used within the ``FROM`` - clause of a ``SELECT`` statement. - """ + """Represent an element that can be used within the ``FROM`` clause of a ``SELECT`` statement.""" __visit_name__ = 'fromclause' - + def __init__(self, name=None): self.name = name @@ -1706,7 +1707,7 @@ class FromClause(Selectable): def alias(self, name=None): return Alias(self, name) - + def named_with_column(self): """True if the name of this FromClause may be prepended to a column in a generated SQL statement. @@ -1733,47 +1734,45 @@ class FromClause(Selectable): return ret def is_derived_from(self, fromclause): - """return True if this FromClause is 'derived' from the given FromClause. - - An example would be an Alias of a Table is derived from that Table.""" - + """Return True if this FromClause is 'derived' from the given FromClause. + + An example would be an Alias of a Table is derived from that Table. + """ + return False - + def replace_selectable(self, old, alias): - """replace all occurences of FromClause 'old' with the given Alias object, returning a - copy of this ``FromClause``.""" - + """replace all occurences of FromClause 'old' with the given Alias object, returning a copy of this ``FromClause``.""" + from sqlalchemy import sql_util return sql_util.ClauseAdapter(alias).traverse(self, clone=True) - + def corresponding_column(self, column, raiseerr=True, keys_ok=False, require_embedded=False): - """Given a ``ColumnElement``, return the exported - ``ColumnElement`` object from this ``Selectable`` which - corresponds to that original ``Column`` via a common - anscestor column. - + """Given a ``ColumnElement``, return the exported ``ColumnElement`` + object from this ``Selectable`` which corresponds to that + original ``Column`` via a common anscestor column. + column the target ``ColumnElement`` to be matched - + raiseerr - if True, raise an error if the given ``ColumnElement`` - could not be matched. if False, non-matches will - return None. - + if True, raise an error if the given ``ColumnElement`` could + not be matched. if False, non-matches will return None. + keys_ok - if the ``ColumnElement`` cannot be matched, attempt to - match based on the string "key" property of the column - alone. This makes the search much more liberal. - + if the ``ColumnElement`` cannot be matched, attempt to match + based on the string "key" property of the column alone. This + makes the search much more liberal. + require_embedded only return corresponding columns for the given ``ColumnElement``, if the given ``ColumnElement`` is actually present within a sub-element of this - ``FromClause``. Normally the column will match if - it merely shares a common anscestor with one of - the exported columns of this ``FromClause``. + ``FromClause``. Normally the column will match if it merely + shares a common anscestor with one of the exported columns + of this ``FromClause``. """ - + if self.c.contains_column(column): return column @@ -1806,10 +1805,11 @@ class FromClause(Selectable): return getattr(self, name) def _clone_from_clause(self): - # delete all the "generated" collections of columns for a newly cloned FromClause, - # so that they will be re-derived from the item. - # this is because FromClause subclasses, when cloned, need to reestablish new "proxied" - # columns that are linked to the new item + # delete all the "generated" collections of columns for a + # newly cloned FromClause, so that they will be re-derived + # from the item. this is because FromClause subclasses, when + # cloned, need to reestablish new "proxied" columns that are + # linked to the new item for attr in ('_columns', '_primary_key' '_foreign_keys', '_orig_cols', '_oid_column'): if hasattr(self, attr): delattr(self, attr) @@ -1851,17 +1851,19 @@ class FromClause(Selectable): cp = self._proxy_column(co) for ci in cp.orig_set: cx = self._orig_cols.get(ci) - # TODO: the '=' thing here relates to the order of columns as they are placed in the - # "columns" collection of a CompositeSelect, illustrated in test/sql/selectable.SelectableTest.testunion - # make this relationship less brittle + # TODO: the '=' thing here relates to the order of + # columns as they are placed in the "columns" + # collection of a CompositeSelect, illustrated in + # test/sql/selectable.SelectableTest.testunion make + # this relationship less brittle if cx is None or cp._distance <= cx._distance: self._orig_cols[ci] = cp if self.oid_column is not None: for ci in self.oid_column.orig_set: self._orig_cols[ci] = self.oid_column - + def _flatten_exportable_columns(self): - """return the list of ColumnElements represented within this FromClause's _exportable_columns""" + """Return the list of ColumnElements represented within this FromClause's _exportable_columns""" export = self._exportable_columns() for column in export: if isinstance(column, Selectable): @@ -1871,7 +1873,7 @@ class FromClause(Selectable): yield column else: continue - + def _exportable_columns(self): return [] @@ -1885,7 +1887,7 @@ class _BindParamClause(ClauseElement, _CompareMixin): """ __visit_name__ = 'bindparam' - + def __init__(self, key, value, shortname=None, type_=None, unique=False, isoutparam=False): """Construct a _BindParamClause. @@ -1915,11 +1917,11 @@ class _BindParamClause(ClauseElement, _CompareMixin): execution time. unique - if True, the key name of this BindParamClause will be - modified if another ``_BindParamClause`` of the same - name already has been located within the containing + if True, the key name of this BindParamClause will be + modified if another ``_BindParamClause`` of the same name + already has been located within the containing ``ClauseElement``. - + isoutparam if True, the parameter should be treated like a stored procedure "OUT" parameter. @@ -1935,7 +1937,7 @@ class _BindParamClause(ClauseElement, _CompareMixin): self.type = sqltypes.to_instance(_BindParamClause.type_map[type(value)]) else: self.type = type_ - + # TODO: move to types module, obviously type_map = { str : sqltypes.String, @@ -1943,7 +1945,7 @@ class _BindParamClause(ClauseElement, _CompareMixin): int : sqltypes.Integer, float : sqltypes.Numeric } - + def _get_from_objects(self, **modifiers): return [] @@ -1976,7 +1978,7 @@ class _TypeClause(ClauseElement): """ __visit_name__ = 'typeclause' - + def __init__(self, type): self.type = type @@ -1990,7 +1992,7 @@ class _TextClause(ClauseElement): """ __visit_name__ = 'textclause' - + def __init__(self, text = "", bind=None, bindparams=None, typemap=None): self._bind = bind self.bindparams = {} @@ -2002,7 +2004,7 @@ class _TextClause(ClauseElement): def repl(m): self.bindparams[m.group(1)] = bindparam(m.group(1)) return ":%s" % m.group(1) - + # scan the string and search for bind parameter names, add them # to the list of bindparams self.text = BIND_PARAMS.sub(repl, text) @@ -2052,7 +2054,7 @@ class ClauseList(ClauseElement): By default, is comma-separated, such as a column listing. """ __visit_name__ = 'clauselist' - + def __init__(self, *clauses, **kwargs): self.clauses = [] self.operator = kwargs.pop('operator', operators.comma_op) @@ -2067,10 +2069,12 @@ class ClauseList(ClauseElement): return iter(self.clauses) def __len__(self): return len(self.clauses) - + def append(self, clause): - # TODO: not sure if i like the 'group_contents' flag. need to define the difference between - # a ClauseList of ClauseLists, and a "flattened" ClauseList of ClauseLists. flatten() method ? + # TODO: not sure if i like the 'group_contents' flag. need to + # define the difference between a ClauseList of ClauseLists, + # and a "flattened" ClauseList of ClauseLists. flatten() + # method ? if self.group_contents: self.clauses.append(_literal_as_text(clause).self_group(against=self.operator)) else: @@ -2116,8 +2120,9 @@ class _CalculatedClause(ColumnElement): Extends ``ColumnElement`` to provide column-level comparison operators. """ + __visit_name__ = 'calculatedclause' - + def __init__(self, name, *clauses, **kwargs): self.name = name self.type = sqltypes.to_instance(kwargs.get('type_', None)) @@ -2128,22 +2133,22 @@ class _CalculatedClause(ColumnElement): self.clause_expr = clauses.self_group() else: self.clause_expr = clauses - + key = property(lambda self:self.name or "_calc_") def _copy_internals(self): self.clause_expr = self.clause_expr._clone() - + def clauses(self): if isinstance(self.clause_expr, _Grouping): return self.clause_expr.elem else: return self.clause_expr clauses = property(clauses) - + def get_children(self, **kwargs): return self.clause_expr, - + def _get_from_objects(self, **modifiers): return self.clauses._get_from_objects(**modifiers) @@ -2178,17 +2183,18 @@ class _Function(_CalculatedClause, FromClause): key = property(lambda self:self.name) columns = property(lambda self:[self]) - + def _copy_internals(self): _CalculatedClause._copy_internals(self) self._clone_from_clause() def get_children(self, **kwargs): return _CalculatedClause.get_children(self, **kwargs) - + def append(self, clause): self.clauses.append(_literal_as_binds(clause, self.name)) + class _Cast(ColumnElement): def __init__(self, clause, totype, **kwargs): @@ -2198,7 +2204,7 @@ class _Cast(ColumnElement): self.clause = clause self.typeclause = _TypeClause(self.type) self._distance = 0 - + def _copy_internals(self): self.clause = self.clause._clone() self.typeclause = self.typeclause._clone() @@ -2224,11 +2230,11 @@ class _UnaryExpression(ColumnElement): def __init__(self, element, operator=None, modifier=None, type_=None, negate=None): self.operator = operator self.modifier = modifier - + self.element = _literal_as_text(element).self_group(against=self.operator or self.modifier) self.type = sqltypes.to_instance(type_) self.negate = negate - + def _get_from_objects(self, **modifiers): return self.element._get_from_objects(**modifiers) @@ -2242,7 +2248,8 @@ class _UnaryExpression(ColumnElement): """Compare this ``_UnaryExpression`` against the given ``ClauseElement``.""" return ( - isinstance(other, _UnaryExpression) and self.operator == other.operator and + isinstance(other, _UnaryExpression) and + self.operator == other.operator and self.modifier == other.modifier and self.element.compare(other.element) ) @@ -2252,7 +2259,7 @@ class _UnaryExpression(ColumnElement): return _UnaryExpression(self.element, operator=self.negate, negate=self.operator, modifier=self.modifier, type_=self.type) else: return super(_UnaryExpression, self)._negate() - + def self_group(self, against): if self.operator and PRECEDENCE.get(self.operator, PRECEDENCE[_smallest]) <= PRECEDENCE.get(against, PRECEDENCE[_largest]): return _Grouping(self) @@ -2262,7 +2269,7 @@ class _UnaryExpression(ColumnElement): class _BinaryExpression(ColumnElement): """Represent an expression that is ``LEFT <operator> RIGHT``.""" - + def __init__(self, left, right, operator, type_=None, negate=None): self.left = _literal_as_text(left).self_group(against=operator) self.right = _literal_as_text(right).self_group(against=operator) @@ -2284,23 +2291,28 @@ class _BinaryExpression(ColumnElement): """Compare this ``_BinaryExpression`` against the given ``_BinaryExpression``.""" return ( - isinstance(other, _BinaryExpression) and self.operator == other.operator and + isinstance(other, _BinaryExpression) and + self.operator == other.operator and + ( + self.left.compare(other.left) and + self.right.compare(other.right) or ( - self.left.compare(other.left) and self.right.compare(other.right) - or ( - self.operator in [operators.eq, operators.ne, operators.add, operators.mul] and - self.left.compare(other.right) and self.right.compare(other.left) - ) + self.operator in [operators.eq, operators.ne, + operators.add, operators.mul] and + self.left.compare(other.right) and + self.right.compare(other.left) ) + ) ) - + def self_group(self, against=None): - # use small/large defaults for comparison so that unknown operators are always parenthesized + # use small/large defaults for comparison so that unknown + # operators are always parenthesized if self.operator != against and (PRECEDENCE.get(self.operator, PRECEDENCE[_smallest]) <= PRECEDENCE.get(against, PRECEDENCE[_largest])): return _Grouping(self) else: return self - + def _negate(self): if self.negate is not None: return _BinaryExpression(self.left, self.right, self.negate, negate=self.operator, type_=self.type) @@ -2309,7 +2321,7 @@ class _BinaryExpression(ColumnElement): class _Exists(_UnaryExpression): __visit_name__ = _UnaryExpression.__visit_name__ - + def __init__(self, *args, **kwargs): kwargs['correlate'] = True s = select(*args, **kwargs).as_scalar().self_group() @@ -2322,24 +2334,23 @@ class _Exists(_UnaryExpression): e = self._clone() e.element = self.element.correlate(fromclause).self_group() return e - + def where(self, clause): e = self._clone() e.element = self.element.where(clause).self_group() return e - + def _hide_froms(self, **modifiers): return self._get_from_objects(**modifiers) class Join(FromClause): - """represent a ``JOIN`` construct between two ``FromClause`` - elements. - - the public constructor function for ``Join`` is the module-level + """represent a ``JOIN`` construct between two ``FromClause`` elements. + + The public constructor function for ``Join`` is the module-level ``join()`` function, as well as the ``join()`` method available off all ``FromClause`` subclasses. - """ + def __init__(self, left, right, onclause=None, isouter = False): self.left = _selectable(left) self.right = _selectable(right).self_group() @@ -2350,13 +2361,13 @@ class Join(FromClause): self.isouter = isouter self.__folded_equivalents = None self._init_primary_key() - + name = property(lambda s: "Join object on " + s.left.name + " " + s.right.name) encodedname = property(lambda s: s.name.encode('ascii', 'backslashreplace')) def _init_primary_key(self): pkcol = util.Set([c for c in self._flatten_exportable_columns() if c.primary_key]) - + equivs = {} def add_equiv(a, b): for x, y in ((a, b), (b, a)): @@ -2364,18 +2375,18 @@ class Join(FromClause): equivs[x].add(y) else: equivs[x] = util.Set([y]) - + class BinaryVisitor(ClauseVisitor): def visit_binary(self, binary): if binary.operator == operators.eq: add_equiv(binary.left, binary.right) BinaryVisitor().traverse(self.onclause) - + for col in pkcol: for fk in col.foreign_keys: if fk.column in pkcol: add_equiv(col, fk.column) - + omit = util.Set() for col in pkcol: p = col @@ -2383,14 +2394,14 @@ class Join(FromClause): if p.references(c) or (c.primary_key and not p.primary_key): omit.add(p) p = c - + self.__primary_key = ColumnSet([c for c in self._flatten_exportable_columns() if c.primary_key and c not in omit]) primary_key = property(lambda s:s.__primary_key) def self_group(self, against=None): return _FromGrouping(self) - + def _locate_oid_column(self): return self.left.oid_column @@ -2429,14 +2440,16 @@ class Join(FromClause): constraints.add(fk.constraint) self.foreignkey = fk.parent if len(crit) == 0: - raise exceptions.ArgumentError("Can't find any foreign key relationships " - "between '%s' and '%s'" % (primary.name, secondary.name)) + raise exceptions.ArgumentError( + "Can't find any foreign key relationships " + "between '%s' and '%s'" % (primary.name, secondary.name)) elif len(constraints) > 1: - raise exceptions.ArgumentError("Can't determine join between '%s' and '%s'; " - "tables have more than one foreign key " - "constraint relationship between them. " - "Please specify the 'onclause' of this " - "join explicitly." % (primary.name, secondary.name)) + raise exceptions.ArgumentError( + "Can't determine join between '%s' and '%s'; " + "tables have more than one foreign key " + "constraint relationship between them. " + "Please specify the 'onclause' of this " + "join explicitly." % (primary.name, secondary.name)) elif len(crit) == 1: return (crit[0]) else: @@ -2475,31 +2488,33 @@ class Join(FromClause): folded_equivalents = property(_get_folded_equivalents, doc="Returns the column list of this Join with all equivalently-named, " "equated columns folded into one column, where 'equated' means they are " - "equated to each other in the ON clause of this join.") - + "equated to each other in the ON clause of this join.") + def select(self, whereclause = None, fold_equivalents=False, **kwargs): """Create a ``Select`` from this ``Join``. - + whereclause - the WHERE criterion that will be sent to the ``select()`` function - + the WHERE criterion that will be sent to the ``select()`` + function + fold_equivalents - based on the join criterion of this ``Join``, do not include repeat - column names in the column list of the resulting select, for columns that - are calculated to be "equivalent" based on the join criterion of this - ``Join``. this will recursively apply to any joins directly nested by - this one as well. - + based on the join criterion of this ``Join``, do not include + repeat column names in the column list of the resulting + select, for columns that are calculated to be "equivalent" + based on the join criterion of this ``Join``. This will + recursively apply to any joins directly nested by this one + as well. + \**kwargs all other kwargs are sent to the underlying ``select()`` function. See the ``select()`` module level function for details. - """ + if fold_equivalents: collist = self.folded_equivalents else: collist = [self.left, self.right] - + return select(collist, whereclause, from_obj=[self], **kwargs) bind = property(lambda s:s.left.bind or s.right.bind) @@ -2519,16 +2534,17 @@ class Join(FromClause): return [self] + self.onclause._get_from_objects(**modifiers) + self.left._get_from_objects(**modifiers) + self.right._get_from_objects(**modifiers) class Alias(FromClause): - """represent an alias, as typically applied to any - table or sub-select within a SQL statement using the - ``AS`` keyword (or without the keyword on certain databases - such as Oracle). - - this object is constructed from the ``alias()`` module level function - as well as the ``alias()`` method available on all ``FromClause`` - subclasses. - + """Represents an table or selectable alias (AS). + + Represents an alias, as typically applied to any table or + sub-select within a SQL statement using the ``AS`` keyword (or + without the keyword on certain databases such as Oracle). + + This object is constructed from the ``alias()`` module level + function as well as the ``alias()`` method available on all + ``FromClause`` subclasses. """ + def __init__(self, selectable, alias=None): baseselectable = selectable while isinstance(baseselectable, Alias): @@ -2541,7 +2557,7 @@ class Alias(FromClause): alias = '{ANON %d %s}' % (id(self), alias or 'anon') self.name = alias self.encodedname = alias.encode('ascii', 'backslashreplace') - + def is_derived_from(self, fromclause): x = self.selectable while True: @@ -2584,23 +2600,23 @@ class Alias(FromClause): for c in self.c: yield c yield self.selectable - + def _get_from_objects(self): return [self] bind = property(lambda s: s.selectable.bind) class _ColumnElementAdapter(ColumnElement): - """adapts a ClauseElement which may or may not be a + """Adapts a ClauseElement which may or may not be a ColumnElement subclass itself into an object which acts like a ColumnElement. """ - + def __init__(self, elem): self.elem = elem self.type = getattr(elem, 'type', None) self.orig_set = getattr(elem, 'orig_set', util.Set()) - + key = property(lambda s: s.elem.key) _label = property(lambda s: s.elem._label) @@ -2620,11 +2636,11 @@ class _ColumnElementAdapter(ColumnElement): return getattr(self.elem, attr) class _Grouping(_ColumnElementAdapter): - """represent a grouping within a column expression""" + """Represent a grouping within a column expression""" pass class _FromGrouping(FromClause): - """represent a grouping of a FROM clause""" + """Represent a grouping of a FROM clause""" __visit_name__ = 'grouping' def __init__(self, elem): @@ -2646,17 +2662,18 @@ class _FromGrouping(FromClause): def __getattr__(self, attr): return getattr(self.elem, attr) - + class _Label(ColumnElement): - """represent a label, as typically applied to any column-level element - using the ``AS`` sql keyword. - - this object is constructed from the ``label()`` module level function - as well as the ``label()`` method available on all ``ColumnElement`` - subclasses. - + """Represents a column label (AS). + + Represent a label, as typically applied to any column-level + element using the ``AS`` sql keyword. + + This object is constructed from the ``label()`` module level + function as well as the ``label()`` method available on all + ``ColumnElement`` subclasses. """ - + def __init__(self, name, obj, type_=None): while isinstance(obj, _Label): obj = obj.obj @@ -2671,7 +2688,7 @@ class _Label(ColumnElement): def expression_element(self): return self.obj - + def _copy_internals(self): self.obj = self.obj._clone() @@ -2683,7 +2700,7 @@ class _Label(ColumnElement): def _hide_froms(self, **modifiers): return self.obj._hide_froms(**modifiers) - + def _make_proxy(self, selectable, name = None): if isinstance(self.obj, (Selectable, ColumnElement)): return self.obj._make_proxy(selectable, name=self.name) @@ -2692,28 +2709,29 @@ class _Label(ColumnElement): class _ColumnClause(ColumnElement): """Represents a generic column expression from any textual string. + This includes columns associated with tables, aliases and select - statements, but also any arbitrary text. May or may not be bound + statements, but also any arbitrary text. May or may not be bound to an underlying ``Selectable``. ``_ColumnClause`` is usually - created publically via the ``column()`` function or the + created publically via the ``column()`` function or the ``literal_column()`` function. - - text - the text of the element. - - selectable - parent selectable. - - type - ``TypeEngine`` object which can associate this ``_ColumnClause`` - with a type. - - is_literal - if True, the ``_ColumnClause`` is assumed to be an exact expression - that will be delivered to the output with no quoting rules applied - regardless of case sensitive settings. the ``literal_column()`` function is - usually used to create such a ``_ColumnClause``. - + + text + the text of the element. + + selectable + parent selectable. + + type + ``TypeEngine`` object which can associate this ``_ColumnClause`` + with a type. + + is_literal + if True, the ``_ColumnClause`` is assumed to be an exact + expression that will be delivered to the output with no quoting + rules applied regardless of case sensitive settings. the + ``literal_column()`` function is usually used to create such a + ``_ColumnClause``. """ def __init__(self, text, selectable=None, type_=None, _is_oid=False, is_literal=False): @@ -2725,20 +2743,20 @@ class _ColumnClause(ColumnElement): self._distance = 0 self.__label = None self.is_literal = is_literal - + def _clone(self): # ColumnClause is immutable return self - + def _get_label(self): """Generate a 'label' for this column. - + The label is a product of the parent table name and column name, and is treated as a unique identifier of this ``Column`` across all ``Tables`` and derived selectables for a particular metadata collection. """ - + # for a "literal" column, we've no idea what the text is # therefore no 'label' can be automatically generated if self.is_literal: @@ -2765,7 +2783,7 @@ class _ColumnClause(ColumnElement): return self else: return super(_ColumnClause, self).label(name) - + def _get_from_objects(self, **modifiers): if self.table is not None: return [self.table] @@ -2790,21 +2808,20 @@ class _ColumnClause(ColumnElement): return self.type class TableClause(FromClause): - """represents a "table" construct. - - Note that this represents tables only as another - syntactical construct within SQL expressions; it - does not provide schema-level functionality. - + """Represents a "table" construct. + + Note that this represents tables only as another syntactical + construct within SQL expressions; it does not provide schema-level + functionality. """ - + def __init__(self, name, *columns): super(TableClause, self).__init__(name) self.name = self.fullname = name self.encodedname = self.name.encode('ascii', 'backslashreplace') self._oid_column = _ColumnClause('oid', self, _is_oid=True) self._export_columns(columns) - + def _clone(self): # TableClause is immutable return self @@ -2875,7 +2892,7 @@ class TableClause(FromClause): def _get_from_objects(self, **modifiers): return [self] - + class _SelectBaseMixin(object): """Base class for ``Select`` and ``CompoundSelects``.""" @@ -2885,21 +2902,21 @@ class _SelectBaseMixin(object): self._limit = limit self._offset = offset self._bind = bind - + self.append_order_by(*util.to_list(order_by, [])) self.append_group_by(*util.to_list(group_by, [])) - + def as_scalar(self): return _ScalarSelect(self) - + def apply_labels(self): s = self._generate() s.use_labels = True return s - + def label(self, name): return self.as_scalar().label(name) - + def supports_execution(self): return True @@ -2907,17 +2924,17 @@ class _SelectBaseMixin(object): s = self._clone() s._clone_from_clause() return s - + def limit(self, limit): s = self._generate() s._limit = limit return s - + def offset(self, offset): s = self._generate() s._offset = offset return s - + def order_by(self, *clauses): s = self._generate() s.append_order_by(*clauses) @@ -2943,7 +2960,7 @@ class _SelectBaseMixin(object): if getattr(self, '_group_by_clause', None): clauses = list(self._group_by_clause) + list(clauses) self._group_by_clause = ClauseList(*clauses) - + def select(self, whereclauses = None, **params): return select([self], whereclauses, **params) @@ -2964,7 +2981,7 @@ class _ScalarSelect(_Grouping): raise exceptions.InvalidRequestError("Scalar Select expression has no columns; use this object directly within a column-level expression.") c = property(_no_cols) columns = c - + def self_group(self, **kwargs): return self @@ -3037,7 +3054,7 @@ class CompoundSelect(_SelectBaseMixin, FromClause): for s in self.selects: for t in s._table_iterator(): yield t - + def _find_engine(self): for s in self.selects: e = s._find_engine() @@ -3047,18 +3064,20 @@ class CompoundSelect(_SelectBaseMixin, FromClause): return None class Select(_SelectBaseMixin, FromClause): - """Represent a ``SELECT`` statement, with appendable clauses, as - well as the ability to execute itself and return a result set. - + """Represents a ``SELECT`` statement. + + Select statements support appendable clauses, as well as the + ability to execute themselves and return a result set. """ def __init__(self, columns, whereclause=None, from_obj=None, distinct=False, having=None, correlate=True, prefixes=None, **kwargs): - """construct a Select object. - - The public constructor for Select is the [sqlalchemy.sql#select()] function; - see that function for argument descriptions. + """Construct a Select object. + + The public constructor for Select is the + [sqlalchemy.sql#select()] function; see that function for + argument descriptions. """ - + self._should_correlate = correlate self._distinct = distinct @@ -3068,7 +3087,7 @@ class Select(_SelectBaseMixin, FromClause): self._whereclause = None self._having = None self._prefixes = [] - + if columns is not None: for c in columns: self.append_column(c) @@ -3079,24 +3098,28 @@ class Select(_SelectBaseMixin, FromClause): if whereclause is not None: self.append_whereclause(whereclause) - + if having is not None: self.append_having(having) + if prefixes is not None: + for p in prefixes: + self.append_prefix(p) + _SelectBaseMixin.__init__(self, **kwargs) def _get_display_froms(self, existing_froms=None): - """return the full list of 'from' clauses to be displayed. - - takes into account a set of existing froms which - may be rendered in the FROM clause of enclosing selects; - this Select may want to leave those absent if it is automatically + """Return the full list of 'from' clauses to be displayed. + + Takes into account a set of existing froms which may be + rendered in the FROM clause of enclosing selects; this Select + may want to leave those absent if it is automatically correlating. """ froms = util.OrderedSet() hide_froms = util.Set() - + for col in self._raw_columns: for f in col._hide_froms(): hide_froms.add(f) @@ -3106,7 +3129,7 @@ class Select(_SelectBaseMixin, FromClause): if self._whereclause is not None: for f in self._whereclause._get_from_objects(is_where=True): froms.add(f) - + for elem in self._froms: froms.add(elem) for f in elem._get_from_objects(): @@ -3117,7 +3140,7 @@ class Select(_SelectBaseMixin, FromClause): hide_froms.add(f) froms = froms.difference(hide_froms) - + if len(froms) > 1: corr = self.__correlate if self._should_correlate and existing_froms is not None: @@ -3128,7 +3151,7 @@ class Select(_SelectBaseMixin, FromClause): return f else: return froms - + froms = property(_get_display_froms, doc="""Return a list of all FromClause elements which will be applied to the FROM clause of the resulting statement.""") name = property(lambda self:"Select statement") @@ -3142,7 +3165,7 @@ class Select(_SelectBaseMixin, FromClause): if self._whereclause is not None: for f in self._whereclause._get_from_objects(is_where=True): froms.add(f) - + for elem in self._froms: froms.add(elem) for f in elem._get_from_objects(): @@ -3156,9 +3179,9 @@ class Select(_SelectBaseMixin, FromClause): yield co else: yield c - + inner_columns = property(_get_inner_columns) - + def _copy_internals(self): self._clone_from_clause() self._raw_columns = [c._clone() for c in self._raw_columns] @@ -3185,22 +3208,22 @@ class Select(_SelectBaseMixin, FromClause): oldfroms.remove(old) self.__correlate = self.__correlate.union(newcorrelate) self._froms = [f for f in oldfroms.union(newfroms)] - + def column(self, column): s = self._generate() s.append_column(column) return s - + def where(self, whereclause): s = self._generate() s.append_whereclause(whereclause) return s - + def having(self, having): s = self._generate() s.append_having(having) return s - + def distinct(self): s = self._generate() s._distinct = True @@ -3210,17 +3233,17 @@ class Select(_SelectBaseMixin, FromClause): s = self._generate() s.append_prefix(clause) return s - + def select_from(self, fromclause): s = self._generate() s.append_from(fromclause) return s - + def __dont_correlate(self): s = self._generate() s._should_correlate = False return s - + def correlate(self, fromclause): s = self._generate() s._should_correlate=False @@ -3229,10 +3252,10 @@ class Select(_SelectBaseMixin, FromClause): else: s.append_correlation(fromclause) return s - + def append_correlation(self, fromclause): self.__correlate.add(fromclause) - + def append_column(self, column): column = _literal_as_column(column) @@ -3240,17 +3263,17 @@ class Select(_SelectBaseMixin, FromClause): column = column.self_group(against=operators.comma_op) self._raw_columns.append(column) - + def append_prefix(self, clause): clause = _literal_as_text(clause) self._prefixes.append(clause) - + def append_whereclause(self, whereclause): if self._whereclause is not None: self._whereclause = and_(self._whereclause, _literal_as_text(whereclause)) else: self._whereclause = _literal_as_text(whereclause) - + def append_having(self, having): if self._having is not None: self._having = and_(self._having, _literal_as_text(having)) @@ -3264,7 +3287,7 @@ class Select(_SelectBaseMixin, FromClause): def _exportable_columns(self): return [c for c in self._raw_columns if isinstance(c, (Selectable, ColumnElement))] - + def _proxy_column(self, column): if self.use_labels: return column._make_proxy(self, name=column._label) @@ -3279,8 +3302,9 @@ class Select(_SelectBaseMixin, FromClause): def _locate_oid_column(self): for f in self.locate_all_froms(): if f is self: - # we might be in our own _froms list if a column with us as the parent is attached, - # which includes textual columns. + # we might be in our own _froms list if a column with + # us as the parent is attached, which includes textual + # columns. continue oid = f.oid_column if oid is not None: @@ -3310,7 +3334,7 @@ class Select(_SelectBaseMixin, FromClause): for t in NoColumnVisitor().iterate(self): if isinstance(t, TableClause): yield t - + def _find_engine(self): """Try to return a Engine, either explicitly set in this object, or searched within the from clauses for one. @@ -3344,11 +3368,9 @@ class _UpdateBase(ClauseElement): def _table_iterator(self): return iter([self.table]) - + def _process_colparams(self, parameters): - """Receive the *values* of an ``INSERT`` or ``UPDATE`` - statement and construct appropriate bind parameters. - """ + """Receive the *values* of an ``INSERT`` or ``UPDATE`` statement and construct appropriate bind parameters.""" if parameters is None: return None @@ -3404,7 +3426,7 @@ class Insert(_UpdateBase): u.parameters = self.parameters.copy() u.parameters.update(u._process_colparams(v)) return u - + class Update(_UpdateBase): def __init__(self, table, whereclause, values=None): self.table = table @@ -3420,7 +3442,7 @@ class Update(_UpdateBase): def _copy_internals(self): self._whereclause = self._whereclause._clone() self.parameters = self.parameters.copy() - + def values(self, v): if len(v) == 0: return self |