root/galaxy-central/eggs/SQLAlchemy-0.5.6_dev_r6498-py2.6.egg/sqlalchemy/sql/expression.py

リビジョン 3, 125.7 KB (コミッタ: kohda, 14 年 前)

Install Unix tools  http://hannonlab.cshl.edu/galaxy_unix_tools/galaxy.html

行番号 
1# expression.py
2# Copyright (C) 2005, 2006, 2007, 2008, 2009 Michael Bayer mike_mp@zzzcomputing.com
3#
4# This module is part of SQLAlchemy and is released under
5# the MIT License: http://www.opensource.org/licenses/mit-license.php
6
7"""Defines the base components of SQL expression trees.
8
9All components are derived from a common base class
10:class:`~sqlalchemy.sql.expression.ClauseElement`.  Common behaviors are organized
11based on class hierarchies, in some cases via mixins.
12
13All object construction from this package occurs via functions which
14in some cases will construct composite ``ClauseElement`` structures
15together, and in other cases simply return a single ``ClauseElement``
16constructed directly.  The function interface affords a more "DSL-ish"
17feel to constructing SQL expressions and also allows future class
18reorganizations.
19
20Even though classes are not constructed directly from the outside,
21most classes which have additional public methods are considered to be
22public (i.e. have no leading underscore).  Other classes which are
23"semi-public" are marked with a single leading underscore; these
24classes usually have few or no public methods and are less guaranteed
25to stay the same in future releases.
26
27"""
28
29import itertools, re
30from operator import attrgetter
31
32from sqlalchemy import util, exc
33from sqlalchemy.sql import operators
34from sqlalchemy.sql.visitors import Visitable, cloned_traverse
35from sqlalchemy import types as sqltypes
36import operator
37
38functions, schema, sql_util = None, None, None
39DefaultDialect, ClauseAdapter, Annotated = None, None, None
40
41__all__ = [
42    'Alias', 'ClauseElement',
43    'ColumnCollection', 'ColumnElement',
44    'CompoundSelect', 'Delete', 'FromClause', 'Insert', 'Join',
45    'Select', 'Selectable', 'TableClause', 'Update', 'alias', 'and_', 'asc',
46    'between', 'bindparam', 'case', 'cast', 'column', 'delete',
47    'desc', 'distinct', 'except_', 'except_all', 'exists', 'extract', 'func',
48    'modifier', 'collate',
49    'insert', 'intersect', 'intersect_all', 'join', 'label', 'literal',
50    'literal_column', 'not_', 'null', 'or_', 'outparam', 'outerjoin', 'select',
51    'subquery', 'table', 'text', 'union', 'union_all', 'update', ]
52
53
54
55def desc(column):
56    """Return a descending ``ORDER BY`` clause element.
57
58    e.g.::
59
60      order_by = [desc(table1.mycol)]
61
62    """
63    return _UnaryExpression(column, modifier=operators.desc_op)
64
65def asc(column):
66    """Return an ascending ``ORDER BY`` clause element.
67
68    e.g.::
69
70      order_by = [asc(table1.mycol)]
71
72    """
73    return _UnaryExpression(column, modifier=operators.asc_op)
74
75def outerjoin(left, right, onclause=None):
76    """Return an ``OUTER JOIN`` clause element.
77
78    The returned object is an instance of :class:`~sqlalchemy.sql.expression.Join`.
79
80    Similar functionality is also available via the ``outerjoin()``
81    method on any :class:`~sqlalchemy.sql.expression.FromClause`.
82
83    left
84      The left side of the join.
85
86    right
87      The right side of the join.
88
89    onclause
90      Optional criterion for the ``ON`` clause, is derived from
91      foreign key relationships established between left and right
92      otherwise.
93
94    To chain joins together, use the ``join()`` or ``outerjoin()``
95    methods on the resulting ``Join`` object.
96
97    """
98    return Join(left, right, onclause, isouter=True)
99
100def join(left, right, onclause=None, isouter=False):
101    """Return a ``JOIN`` clause element (regular inner join).
102
103    The returned object is an instance of :class:`~sqlalchemy.sql.expression.Join`.
104
105    Similar functionality is also available via the ``join()`` method
106    on any :class:`~sqlalchemy.sql.expression.FromClause`.
107
108    left
109      The left side of the join.
110
111    right
112      The right side of the join.
113
114    onclause
115      Optional criterion for the ``ON`` clause, is derived from
116      foreign key relationships established between left and right
117      otherwise.
118
119    To chain joins together, use the ``join()`` or ``outerjoin()``
120    methods on the resulting ``Join`` object.
121
122    """
123    return Join(left, right, onclause, isouter)
124
125def select(columns=None, whereclause=None, from_obj=[], **kwargs):
126    """Returns a ``SELECT`` clause element.
127
128    Similar functionality is also available via the ``select()``
129    method on any :class:`~sqlalchemy.sql.expression.FromClause`.
130
131    The returned object is an instance of :class:`~sqlalchemy.sql.expression.Select`.
132
133    All arguments which accept ``ClauseElement`` arguments also accept
134    string arguments, which will be converted as appropriate into
135    either ``text()`` or ``literal_column()`` constructs.
136
137    columns
138      A list of ``ClauseElement`` objects, typically ``ColumnElement``
139      objects or subclasses, which will form the columns clause of the
140      resulting statement.  For all members which are instances of
141      ``Selectable``, the individual ``ColumnElement`` members of the
142      ``Selectable`` will be added individually to the columns clause.
143      For example, specifying a ``Table`` instance will result in all
144      the contained ``Column`` objects within to be added to the
145      columns clause.
146
147      This argument is not present on the form of ``select()``
148      available on ``Table``.
149
150    whereclause
151      A ``ClauseElement`` expression which will be used to form the
152      ``WHERE`` clause.
153
154    from_obj
155      A list of ``ClauseElement`` objects which will be added to the
156      ``FROM`` clause of the resulting statement.  Note that "from"
157      objects are automatically located within the columns and
158      whereclause ClauseElements.  Use this parameter to explicitly
159      specify "from" objects which are not automatically locatable.
160      This could include ``Table`` objects that aren't otherwise
161      present, or ``Join`` objects whose presence will supercede that
162      of the ``Table`` objects already located in the other clauses.
163
164    \**kwargs
165      Additional parameters include:
166
167      autocommit
168        indicates this SELECT statement modifies the database, and
169        should be subject to autocommit behavior if no transaction
170        has been started.
171
172      prefixes
173        a list of strings or ``ClauseElement`` objects to include
174        directly after the SELECT keyword in the generated statement,
175        for dialect-specific query features.
176
177      distinct=False
178        when ``True``, applies a ``DISTINCT`` qualifier to the columns
179        clause of the resulting statement.
180
181      use_labels=False
182        when ``True``, the statement will be generated using labels
183        for each column in the columns clause, which qualify each
184        column with its parent table's (or aliases) name so that name
185        conflicts between columns in different tables don't occur.
186        The format of the label is <tablename>_<column>.  The "c"
187        collection of the resulting ``Select`` object will use these
188        names as well for targeting column members.
189
190      for_update=False
191        when ``True``, applies ``FOR UPDATE`` to the end of the
192        resulting statement.  Certain database dialects also support
193        alternate values for this parameter, for example mysql
194        supports "read" which translates to ``LOCK IN SHARE MODE``,
195        and oracle supports "nowait" which translates to ``FOR UPDATE
196        NOWAIT``.
197
198      correlate=True
199        indicates that this ``Select`` object should have its
200        contained ``FromClause`` elements "correlated" to an enclosing
201        ``Select`` object.  This means that any ``ClauseElement``
202        instance within the "froms" collection of this ``Select``
203        which is also present in the "froms" collection of an
204        enclosing select will not be rendered in the ``FROM`` clause
205        of this select statement.
206
207      group_by
208        a list of ``ClauseElement`` objects which will comprise the
209        ``GROUP BY`` clause of the resulting select.
210
211      having
212        a ``ClauseElement`` that will comprise the ``HAVING`` clause
213        of the resulting select when ``GROUP BY`` is used.
214
215      order_by
216        a scalar or list of ``ClauseElement`` objects which will
217        comprise the ``ORDER BY`` clause of the resulting select.
218
219      limit=None
220        a numerical value which usually compiles to a ``LIMIT``
221        expression in the resulting select.  Databases that don't
222        support ``LIMIT`` will attempt to provide similar
223        functionality.
224
225      offset=None
226        a numeric value which usually compiles to an ``OFFSET``
227        expression in the resulting select.  Databases that don't
228        support ``OFFSET`` will attempt to provide similar
229        functionality.
230
231      bind=None
232        an ``Engine`` or ``Connection`` instance to which the
233        resulting ``Select ` object will be bound.  The ``Select``
234        object will otherwise automatically bind to whatever
235        ``Connectable`` instances can be located within its contained
236        ``ClauseElement`` members.
237
238      scalar=False
239        deprecated.  Use select(...).as_scalar() to create a "scalar
240        column" proxy for an existing Select object.
241
242    """
243    if 'scalar' in kwargs:
244        util.warn_deprecated('scalar option is deprecated; see docs for details')
245    scalar = kwargs.pop('scalar', False)
246    s = Select(columns, whereclause=whereclause, from_obj=from_obj, **kwargs)
247    if scalar:
248        return s.as_scalar()
249    else:
250        return s
251
252def subquery(alias, *args, **kwargs):
253    """Return an :class:`~sqlalchemy.sql.expression.Alias` object derived from a :class:`~sqlalchemy.sql.expression.Select`.
254
255    name
256      alias name
257
258    \*args, \**kwargs
259
260      all other arguments are delivered to the :func:`~sqlalchemy.sql.expression.select`
261      function.
262
263    """
264    return Select(*args, **kwargs).alias(alias)
265
266def insert(table, values=None, inline=False, **kwargs):
267    """Return an :class:`~sqlalchemy.sql.expression.Insert` clause element.
268
269    Similar functionality is available via the ``insert()`` method on
270    :class:`~sqlalchemy.schema.Table`.
271
272    :param table: The table to be inserted into.
273
274    :param values: A dictionary which specifies the column specifications of the
275      ``INSERT``, and is optional.  If left as None, the column
276      specifications are determined from the bind parameters used
277      during the compile phase of the ``INSERT`` statement.  If the
278      bind parameters also are None during the compile phase, then the
279      column specifications will be generated from the full list of
280      table columns.  Note that the :meth:`~Insert.values()` generative method
281      may also be used for this.
282
283    :param prefixes: A list of modifier keywords to be inserted between INSERT and INTO.
284      Alternatively, the :meth:`~Insert.prefix_with` generative method may be used.
285
286    :param inline:
287      if True, SQL defaults will be compiled 'inline' into the statement
288      and not pre-executed.
289
290    If both `values` and compile-time bind parameters are present, the
291    compile-time bind parameters override the information specified
292    within `values` on a per-key basis.
293
294    The keys within `values` can be either ``Column`` objects or their
295    string identifiers.  Each key may reference one of:
296
297    * a literal data value (i.e. string, number, etc.);
298    * a Column object;
299    * a SELECT statement.
300
301    If a ``SELECT`` statement is specified which references this
302    ``INSERT`` statement's table, the statement will be correlated
303    against the ``INSERT`` statement.
304
305    """
306    return Insert(table, values, inline=inline, **kwargs)
307
308def update(table, whereclause=None, values=None, inline=False, **kwargs):
309    """Return an :class:`~sqlalchemy.sql.expression.Update` clause element.
310
311    Similar functionality is available via the ``update()`` method on
312    :class:`~sqlalchemy.schema.Table`.
313
314    :param table: The table to be updated.
315
316    :param whereclause: A ``ClauseElement`` describing the ``WHERE`` condition of the
317      ``UPDATE`` statement.  Note that the :meth:`~Update.where()` generative
318      method may also be used for this.
319
320    :param values:
321      A dictionary which specifies the ``SET`` conditions of the
322      ``UPDATE``, and is optional. If left as None, the ``SET``
323      conditions are determined from the bind parameters used during
324      the compile phase of the ``UPDATE`` statement.  If the bind
325      parameters also are None during the compile phase, then the
326      ``SET`` conditions will be generated from the full list of table
327      columns.  Note that the :meth:`~Update.values()` generative method may
328      also be used for this.
329
330    :param inline:
331      if True, SQL defaults will be compiled 'inline' into the statement
332      and not pre-executed.
333
334    If both `values` and compile-time bind parameters are present, the
335    compile-time bind parameters override the information specified
336    within `values` on a per-key basis.
337
338    The keys within `values` can be either ``Column`` objects or their
339    string identifiers. Each key may reference one of:
340
341    * a literal data value (i.e. string, number, etc.);
342    * a Column object;
343    * a SELECT statement.
344
345    If a ``SELECT`` statement is specified which references this
346    ``UPDATE`` statement's table, the statement will be correlated
347    against the ``UPDATE`` statement.
348
349    """
350    return Update(table, whereclause=whereclause, values=values, inline=inline, **kwargs)
351
352def delete(table, whereclause = None, **kwargs):
353    """Return a :class:`~sqlalchemy.sql.expression.Delete` clause element.
354
355    Similar functionality is available via the ``delete()`` method on
356    :class:`~sqlalchemy.schema.Table`.
357
358    :param table: The table to be updated.
359
360    :param whereclause: A :class:`ClauseElement` describing the ``WHERE`` condition of the
361      ``UPDATE`` statement.  Note that the :meth:`~Delete.where()` generative method
362      may be used instead.
363
364    """
365    return Delete(table, whereclause, **kwargs)
366
367def and_(*clauses):
368    """Join a list of clauses together using the ``AND`` operator.
369
370    The ``&`` operator is also overloaded on all
371    :class:`~sqlalchemy.sql.expression._CompareMixin` subclasses to produce the same
372    result.
373
374    """
375    if len(clauses) == 1:
376        return clauses[0]
377    return BooleanClauseList(operator=operators.and_, *clauses)
378
379def or_(*clauses):
380    """Join a list of clauses together using the ``OR`` operator.
381
382    The ``|`` operator is also overloaded on all
383    :class:`~sqlalchemy.sql.expression._CompareMixin` subclasses to produce the same
384    result.
385
386    """
387    if len(clauses) == 1:
388        return clauses[0]
389    return BooleanClauseList(operator=operators.or_, *clauses)
390
391def not_(clause):
392    """Return a negation of the given clause, i.e. ``NOT(clause)``.
393
394    The ``~`` operator is also overloaded on all
395    :class:`~sqlalchemy.sql.expression._CompareMixin` subclasses to produce the same
396    result.
397
398    """
399    return operators.inv(_literal_as_binds(clause))
400
401def distinct(expr):
402    """Return a ``DISTINCT`` clause."""
403    expr = _literal_as_binds(expr)
404    return _UnaryExpression(expr, operator=operators.distinct_op, type_=expr.type)
405
406def between(ctest, cleft, cright):
407    """Return a ``BETWEEN`` predicate clause.
408
409    Equivalent of SQL ``clausetest BETWEEN clauseleft AND clauseright``.
410
411    The ``between()`` method on all :class:`~sqlalchemy.sql.expression._CompareMixin` subclasses
412    provides similar functionality.
413
414    """
415    ctest = _literal_as_binds(ctest)
416    return _BinaryExpression(
417        ctest,
418        ClauseList(
419            _literal_as_binds(cleft, type_=ctest.type),
420            _literal_as_binds(cright, type_=ctest.type),
421            operator=operators.and_,
422            group=False),
423        operators.between_op)
424
425
426def case(whens, value=None, else_=None):
427    """Produce a ``CASE`` statement.
428
429    whens
430      A sequence of pairs, or alternatively a dict,
431      to be translated into "WHEN / THEN" clauses.
432
433    value
434      Optional for simple case statements, produces
435      a column expression as in "CASE <expr> WHEN ..."
436
437    else\_
438      Optional as well, for case defaults produces
439      the "ELSE" portion of the "CASE" statement.
440
441    The expressions used for THEN and ELSE,
442    when specified as strings, will be interpreted
443    as bound values. To specify textual SQL expressions
444    for these, use the text(<string>) construct.
445
446    The expressions used for the WHEN criterion
447    may only be literal strings when "value" is
448    present, i.e. CASE table.somecol WHEN "x" THEN "y".
449    Otherwise, literal strings are not accepted
450    in this position, and either the text(<string>)
451    or literal(<string>) constructs must be used to
452    interpret raw string values.
453
454    Usage examples::
455
456      case([(orderline.c.qty > 100, item.c.specialprice),
457            (orderline.c.qty > 10, item.c.bulkprice)
458          ], else_=item.c.regularprice)
459      case(value=emp.c.type, whens={
460              'engineer': emp.c.salary * 1.1,
461              'manager':  emp.c.salary * 3,
462          })
463
464    """
465   
466    return _Case(whens, value=value, else_=else_)
467
468def cast(clause, totype, **kwargs):
469    """Return a ``CAST`` function.
470
471    Equivalent of SQL ``CAST(clause AS totype)``.
472
473    Use with a :class:`~sqlalchemy.types.TypeEngine` subclass, i.e::
474
475      cast(table.c.unit_price * table.c.qty, Numeric(10,4))
476
477    or::
478
479      cast(table.c.timestamp, DATE)
480
481    """
482    return _Cast(clause, totype, **kwargs)
483
484def extract(field, expr):
485    """Return the clause ``extract(field FROM expr)``."""
486
487    return _Extract(field, expr)
488
489def collate(expression, collation):
490    """Return the clause ``expression COLLATE collation``."""
491
492    expr = _literal_as_binds(expression)
493    return _BinaryExpression(
494        expr,
495        _literal_as_text(collation),
496        operators.collate, type_=expr.type)
497
498def exists(*args, **kwargs):
499    """Return an ``EXISTS`` clause as applied to a :class:`~sqlalchemy.sql.expression.Select` object.
500
501    Calling styles are of the following forms::
502
503        # use on an existing select()
504        s = select([table.c.col1]).where(table.c.col2==5)
505        s = exists(s)
506
507        # construct a select() at once
508        exists(['*'], **select_arguments).where(criterion)
509
510        # columns argument is optional, generates "EXISTS (SELECT *)"
511        # by default.
512        exists().where(table.c.col2==5)
513
514    """
515    return _Exists(*args, **kwargs)
516
517def union(*selects, **kwargs):
518    """Return a ``UNION`` of multiple selectables.
519
520    The returned object is an instance of :class:`~sqlalchemy.sql.expression.CompoundSelect`.
521
522    A similar ``union()`` method is available on all
523    :class:`~sqlalchemy.sql.expression.FromClause` subclasses.
524
525    \*selects
526      a list of :class:`~sqlalchemy.sql.expression.Select` instances.
527
528    \**kwargs
529       available keyword arguments are the same as those of
530       :func:`~sqlalchemy.sql.expression.select`.
531
532    """
533    return _compound_select('UNION', *selects, **kwargs)
534
535def union_all(*selects, **kwargs):
536    """Return a ``UNION ALL`` of multiple selectables.
537
538    The returned object is an instance of :class:`~sqlalchemy.sql.expression.CompoundSelect`.
539
540    A similar ``union_all()`` method is available on all
541    :class:`~sqlalchemy.sql.expression.FromClause` subclasses.
542
543    \*selects
544      a list of :class:`~sqlalchemy.sql.expression.Select` instances.
545
546    \**kwargs
547      available keyword arguments are the same as those of
548      :func:`~sqlalchemy.sql.expression.select`.
549
550    """
551    return _compound_select('UNION ALL', *selects, **kwargs)
552
553def except_(*selects, **kwargs):
554    """Return an ``EXCEPT`` of multiple selectables.
555
556    The returned object is an instance of :class:`~sqlalchemy.sql.expression.CompoundSelect`.
557
558    \*selects
559      a list of :class:`~sqlalchemy.sql.expression.Select` instances.
560
561    \**kwargs
562      available keyword arguments are the same as those of
563      :func:`~sqlalchemy.sql.expression.select`.
564
565    """
566    return _compound_select('EXCEPT', *selects, **kwargs)
567
568def except_all(*selects, **kwargs):
569    """Return an ``EXCEPT ALL`` of multiple selectables.
570
571    The returned object is an instance of :class:`~sqlalchemy.sql.expression.CompoundSelect`.
572
573    \*selects
574      a list of :class:`~sqlalchemy.sql.expression.Select` instances.
575
576    \**kwargs
577      available keyword arguments are the same as those of
578      :func:`~sqlalchemy.sql.expression.select`.
579
580    """
581    return _compound_select('EXCEPT ALL', *selects, **kwargs)
582
583def intersect(*selects, **kwargs):
584    """Return an ``INTERSECT`` of multiple selectables.
585
586    The returned object is an instance of :class:`~sqlalchemy.sql.expression.CompoundSelect`.
587
588    \*selects
589      a list of :class:`~sqlalchemy.sql.expression.Select` instances.
590
591    \**kwargs
592      available keyword arguments are the same as those of
593      :func:`~sqlalchemy.sql.expression.select`.
594
595    """
596    return _compound_select('INTERSECT', *selects, **kwargs)
597
598def intersect_all(*selects, **kwargs):
599    """Return an ``INTERSECT ALL`` of multiple selectables.
600
601    The returned object is an instance of :class:`~sqlalchemy.sql.expression.CompoundSelect`.
602
603    \*selects
604      a list of :class:`~sqlalchemy.sql.expression.Select` instances.
605
606    \**kwargs
607      available keyword arguments are the same as those of
608      :func:`~sqlalchemy.sql.expression.select`.
609
610    """
611    return _compound_select('INTERSECT ALL', *selects, **kwargs)
612
613def alias(selectable, alias=None):
614    """Return an :class:`~sqlalchemy.sql.expression.Alias` object.
615
616    An ``Alias`` represents any :class:`~sqlalchemy.sql.expression.FromClause` with
617    an alternate name assigned within SQL, typically using the ``AS``
618    clause when generated, e.g. ``SELECT * FROM table AS aliasname``.
619
620    Similar functionality is available via the ``alias()`` method
621    available on all ``FromClause`` subclasses.
622
623      selectable
624        any ``FromClause`` subclass, such as a table, select
625        statement, etc..
626
627      alias
628        string name to be assigned as the alias.  If ``None``, a
629        random name will be generated.
630
631    """
632    return Alias(selectable, alias=alias)
633
634
635def literal(value, type_=None):
636    """Return a literal clause, bound to a bind parameter.
637
638    Literal clauses are created automatically when non-
639    ``ClauseElement`` objects (such as strings, ints, dates, etc.) are
640    used in a comparison operation with a
641    :class:`~sqlalchemy.sql.expression._CompareMixin` subclass, such as a ``Column``
642    object.  Use this function to force the generation of a literal
643    clause, which will be created as a
644    :class:`~sqlalchemy.sql.expression._BindParamClause` with a bound value.
645
646    value
647      the value to be bound.  Can be any Python object supported by
648      the underlying DB-API, or is translatable via the given type
649      argument.
650
651    type\_
652      an optional :class:`~sqlalchemy.types.TypeEngine` which will provide
653      bind-parameter translation for this literal.
654
655    """
656    return _BindParamClause(None, value, type_=type_, unique=True)
657
658def label(name, obj):
659    """Return a :class:`~sqlalchemy.sql.expression._Label` object for the given :class:`~sqlalchemy.sql.expression.ColumnElement`.
660
661    A label changes the name of an element in the columns clause of a
662    ``SELECT`` statement, typically via the ``AS`` SQL keyword.
663
664    This functionality is more conveniently available via the
665    ``label()`` method on ``ColumnElement``.
666
667    name
668      label name
669
670    obj
671      a ``ColumnElement``.
672
673    """
674    return _Label(name, obj)
675
676def column(text, type_=None):
677    """Return a textual column clause, as would be in the columns clause of a ``SELECT`` statement.
678
679    The object returned is an instance of :class:`~sqlalchemy.sql.expression.ColumnClause`,
680    which represents the "syntactical" portion of the schema-level
681    :class:`~sqlalchemy.schema.Column` object.
682
683    text
684      the name of the column.  Quoting rules will be applied to the
685      clause like any other column name.  For textual column
686      constructs that are not to be quoted, use the
687      :func:`~sqlalchemy.sql.expression.literal_column` function.
688
689    type\_
690      an optional :class:`~sqlalchemy.types.TypeEngine` object which will
691      provide result-set translation for this column.
692
693    """
694    return ColumnClause(text, type_=type_)
695
696def literal_column(text, type_=None):
697    """Return a textual column expression, as would be in the columns
698    clause of a ``SELECT`` statement.
699
700    The object returned supports further expressions in the same way as any
701    other column object, including comparison, math and string operations.
702    The type\_ parameter is important to determine proper expression behavior
703    (such as, '+' means string concatenation or numerical addition based on
704    the type).
705
706    text
707      the text of the expression; can be any SQL expression.  Quoting rules
708      will not be applied.  To specify a column-name expression which should
709      be subject to quoting rules, use the
710      :func:`~sqlalchemy.sql.expression.column` function.
711
712    type\_
713      an optional :class:`~sqlalchemy.types.TypeEngine` object which will provide
714      result-set translation and additional expression semantics for this
715      column.  If left as None the type will be NullType.
716
717    """
718    return ColumnClause(text, type_=type_, is_literal=True)
719
720def table(name, *columns):
721    """Return a :class:`~sqlalchemy.sql.expression.TableClause` object.
722
723    This is a primitive version of the :class:`~sqlalchemy.schema.Table` object,
724    which is a subclass of this object.
725
726    """
727    return TableClause(name, *columns)
728
729def bindparam(key, value=None, shortname=None, type_=None, unique=False):
730    """Create a bind parameter clause with the given key.
731
732    value
733      a default value for this bind parameter.  a bindparam with a
734      value is called a ``value-based bindparam``.
735
736    type\_
737      a sqlalchemy.types.TypeEngine object indicating the type of this
738      bind param, will invoke type-specific bind parameter processing
739
740    shortname
741      deprecated.
742
743    unique
744      if True, bind params sharing the same name will have their
745      underlying ``key`` modified to a uniquely generated name.
746      mostly useful with value-based bind params.
747
748    """
749    if isinstance(key, ColumnClause):
750        return _BindParamClause(key.name, value, type_=key.type, unique=unique, shortname=shortname)
751    else:
752        return _BindParamClause(key, value, type_=type_, unique=unique, shortname=shortname)
753
754def outparam(key, type_=None):
755    """Create an 'OUT' parameter for usage in functions (stored procedures), for databases which support them.
756
757    The ``outparam`` can be used like a regular function parameter.
758    The "output" value will be available from the
759    :class:`~sqlalchemy.engine.ResultProxy` object via its ``out_parameters``
760    attribute, which returns a dictionary containing the values.
761
762    """
763    return _BindParamClause(key, None, type_=type_, unique=False, isoutparam=True)
764
765def text(text, bind=None, *args, **kwargs):
766    """Create literal text to be inserted into a query.
767
768    When constructing a query from a ``select()``, ``update()``,
769    ``insert()`` or ``delete()``, using plain strings for argument
770    values will usually result in text objects being created
771    automatically.  Use this function when creating textual clauses
772    outside of other ``ClauseElement`` objects, or optionally wherever
773    plain text is to be used.
774
775    text
776      the text of the SQL statement to be created.  use ``:<param>``
777      to specify bind parameters; they will be compiled to their
778      engine-specific format.
779
780    bind
781      an optional connection or engine to be used for this text query.
782
783    autocommit=True
784      indicates this SELECT statement modifies the database, and
785      should be subject to autocommit behavior if no transaction
786      has been started.
787
788    bindparams
789      a list of ``bindparam()`` instances which can be used to define
790      the types and/or initial values for the bind parameters within
791      the textual statement; the keynames of the bindparams must match
792      those within the text of the statement.  The types will be used
793      for pre-processing on bind values.
794
795    typemap
796      a dictionary mapping the names of columns represented in the
797      ``SELECT`` clause of the textual statement to type objects,
798      which will be used to perform post-processing on columns within
799      the result set (for textual statements that produce result
800      sets).
801
802    """
803    return _TextClause(text, bind=bind, *args, **kwargs)
804
805def null():
806    """Return a :class:`_Null` object, which compiles to ``NULL`` in a sql statement."""
807
808    return _Null()
809
810class _FunctionGenerator(object):
811    """Generate :class:`Function` objects based on getattr calls."""
812
813    def __init__(self, **opts):
814        self.__names = []
815        self.opts = opts
816
817    def __getattr__(self, name):
818        # passthru __ attributes; fixes pydoc
819        if name.startswith('__'):
820            try:
821                return self.__dict__[name]
822            except KeyError:
823                raise AttributeError(name)
824
825        elif name.endswith('_'):
826            name = name[0:-1]
827        f = _FunctionGenerator(**self.opts)
828        f.__names = list(self.__names) + [name]
829        return f
830
831    def __call__(self, *c, **kwargs):
832        o = self.opts.copy()
833        o.update(kwargs)
834        if len(self.__names) == 1:
835            global functions
836            if functions is None:
837                from sqlalchemy.sql import functions
838            func = getattr(functions, self.__names[-1].lower(), None)
839            if func is not None:
840                return func(*c, **o)
841
842        return Function(self.__names[-1], packagenames=self.__names[0:-1], *c, **o)
843
844# "func" global - i.e. func.count()
845func = _FunctionGenerator()
846
847# "modifier" global - i.e. modifier.distinct
848# TODO: use UnaryExpression for this instead ?
849modifier = _FunctionGenerator(group=False)
850
851class _generated_label(unicode):
852    """A unicode subclass used to identify dynamically generated names."""
853
854def _escape_for_generated(x):
855    if isinstance(x, _generated_label):
856        return x
857    else:
858        return x.replace('%', '%%')
859       
860def _clone(element):
861    return element._clone()
862
863def _expand_cloned(elements):
864    """expand the given set of ClauseElements to be the set of all 'cloned' predecessors."""
865
866    return itertools.chain(*[x._cloned_set for x in elements])
867
868def _cloned_intersection(a, b):
869    """return the intersection of sets a and b, counting
870    any overlap between 'cloned' predecessors.
871
872    The returned set is in terms of the enties present within 'a'.
873
874    """
875    all_overlap = set(_expand_cloned(a)).intersection(_expand_cloned(b))
876    return set(elem for elem in a if all_overlap.intersection(elem._cloned_set))
877
878def _compound_select(keyword, *selects, **kwargs):
879    return CompoundSelect(keyword, *selects, **kwargs)
880
881def _is_literal(element):
882    return not isinstance(element, Visitable) and not hasattr(element, '__clause_element__')
883
884def _from_objects(*elements):
885    return itertools.chain(*[element._from_objects for element in elements])
886
887def _labeled(element):
888    if not hasattr(element, 'name'):
889        return element.label(None)
890    else:
891        return element
892
893def _column_as_key(element):
894    if isinstance(element, basestring):
895        return element
896    if hasattr(element, '__clause_element__'):
897        element = element.__clause_element__()
898    return element.key
899   
900def _literal_as_text(element):
901    if hasattr(element, '__clause_element__'):
902        return element.__clause_element__()
903    elif not isinstance(element, Visitable):
904        return _TextClause(unicode(element))
905    else:
906        return element
907
908def _clause_element_as_expr(element):
909    if hasattr(element, '__clause_element__'):
910        return element.__clause_element__()
911    else:
912        return element
913       
914def _literal_as_column(element):
915    if hasattr(element, '__clause_element__'):
916        return element.__clause_element__()
917    elif not isinstance(element, Visitable):
918        return literal_column(str(element))
919    else:
920        return element
921
922def _literal_as_binds(element, name=None, type_=None):
923    if hasattr(element, '__clause_element__'):
924        return element.__clause_element__()
925    elif not isinstance(element, Visitable):
926        if element is None:
927            return null()
928        else:
929            return _BindParamClause(name, element, type_=type_, unique=True)
930    else:
931        return element
932
933def _no_literals(element):
934    if hasattr(element, '__clause_element__'):
935        return element.__clause_element__()
936    elif not isinstance(element, Visitable):
937        raise exc.ArgumentError("Ambiguous literal: %r.  Use the 'text()' function "
938                "to indicate a SQL expression literal, or 'literal()' to indicate a bound value." % element)
939    else:
940        return element
941
942def _corresponding_column_or_error(fromclause, column, require_embedded=False):
943    c = fromclause.corresponding_column(column, require_embedded=require_embedded)
944    if not c:
945        raise exc.InvalidRequestError("Given column '%s', attached to table '%s', "
946                "failed to locate a corresponding column from table '%s'"
947                % (column, getattr(column, 'table', None), fromclause.description))
948    return c
949
950def is_column(col):
951    """True if ``col`` is an instance of ``ColumnElement``."""
952    return isinstance(col, ColumnElement)
953
954
955class ClauseElement(Visitable):
956    """Base class for elements of a programmatically constructed SQL expression."""
957
958    __visit_name__ = 'clause'
959
960    _annotations = {}
961    supports_execution = False
962    _from_objects = []
963
964    def _clone(self):
965        """Create a shallow copy of this ClauseElement.
966
967        This method may be used by a generative API.  Its also used as
968        part of the "deep" copy afforded by a traversal that combines
969        the _copy_internals() method.
970
971        """
972        c = self.__class__.__new__(self.__class__)
973        c.__dict__ = self.__dict__.copy()
974        c.__dict__.pop('_cloned_set', None)
975
976        # this is a marker that helps to "equate" clauses to each other
977        # when a Select returns its list of FROM clauses.  the cloning
978        # process leaves around a lot of remnants of the previous clause
979        # typically in the form of column expressions still attached to the
980        # old table.
981        c._is_clone_of = self
982
983        return c
984
985    @util.memoized_property
986    def _cloned_set(self):
987        """Return the set consisting all cloned anscestors of this ClauseElement.
988
989        Includes this ClauseElement.  This accessor tends to be used for
990        FromClause objects to identify 'equivalent' FROM clauses, regardless
991        of transformative operations.
992
993        """
994        s = util.column_set()
995        f = self
996        while f is not None:
997            s.add(f)
998            f = getattr(f, '_is_clone_of', None)
999        return s
1000
1001    def __getstate__(self):
1002        d = self.__dict__.copy()
1003        d.pop('_is_clone_of', None)
1004        return d
1005
1006    def _annotate(self, values):
1007        """return a copy of this ClauseElement with the given annotations dictionary."""
1008
1009        global Annotated
1010        if Annotated is None:
1011            from sqlalchemy.sql.util import Annotated
1012        return Annotated(self, values)
1013
1014    def _deannotate(self):
1015        """return a copy of this ClauseElement with an empty annotations dictionary."""
1016        return self._clone()
1017
1018    def unique_params(self, *optionaldict, **kwargs):
1019        """Return a copy with ``bindparam()`` elments replaced.
1020
1021        Same functionality as ``params()``, except adds `unique=True`
1022        to affected bind parameters so that multiple statements can be
1023        used.
1024
1025        """
1026        return self._params(True, optionaldict, kwargs)
1027
1028    def params(self, *optionaldict, **kwargs):
1029        """Return a copy with ``bindparam()`` elments replaced.
1030
1031        Returns a copy of this ClauseElement with ``bindparam()``
1032        elements replaced with values taken from the given dictionary::
1033
1034          >>> clause = column('x') + bindparam('foo')
1035          >>> print clause.compile().params
1036          {'foo':None}
1037          >>> print clause.params({'foo':7}).compile().params
1038          {'foo':7}
1039
1040        """
1041        return self._params(False, optionaldict, kwargs)
1042
1043    def _params(self, unique, optionaldict, kwargs):
1044        if len(optionaldict) == 1:
1045            kwargs.update(optionaldict[0])
1046        elif len(optionaldict) > 1:
1047            raise exc.ArgumentError("params() takes zero or one positional dictionary argument")
1048
1049        def visit_bindparam(bind):
1050            if bind.key in kwargs:
1051                bind.value = kwargs[bind.key]
1052            if unique:
1053                bind._convert_to_unique()
1054        return cloned_traverse(self, {}, {'bindparam':visit_bindparam})
1055
1056    def compare(self, other):
1057        """Compare this ClauseElement to the given ClauseElement.
1058
1059        Subclasses should override the default behavior, which is a
1060        straight identity comparison.
1061
1062        """
1063        return self is other
1064
1065    def _copy_internals(self, clone=_clone):
1066        """Reassign internal elements to be clones of themselves.
1067
1068        Called during a copy-and-traverse operation on newly
1069        shallow-copied elements to create a deep copy.
1070
1071        """
1072        pass
1073
1074    def get_children(self, **kwargs):
1075        """Return immediate child elements of this ``ClauseElement``.
1076
1077        This is used for visit traversal.
1078
1079        \**kwargs may contain flags that change the collection that is
1080        returned, for example to return a subset of items in order to
1081        cut down on larger traversals, or to return child items from a
1082        different context (such as schema-level collections instead of
1083        clause-level).
1084
1085        """
1086        return []
1087
1088    def self_group(self, against=None):
1089        return self
1090
1091    @property
1092    def bind(self):
1093        """Returns the Engine or Connection to which this ClauseElement is bound, or None if none found."""
1094
1095        try:
1096            if self._bind is not None:
1097                return self._bind
1098        except AttributeError:
1099            pass
1100        for f in _from_objects(self):
1101            if f is self:
1102                continue
1103            engine = f.bind
1104            if engine is not None:
1105                return engine
1106        else:
1107            return None
1108
1109    def execute(self, *multiparams, **params):
1110        """Compile and execute this ``ClauseElement``."""
1111
1112        e = self.bind
1113        if e is None:
1114            label = getattr(self, 'description', self.__class__.__name__)
1115            msg = ('This %s is not bound and does not support direct '
1116                   'execution. Supply this statement to a Connection or '
1117                   'Engine for execution. Or, assign a bind to the statement '
1118                   'or the Metadata of its underlying tables to enable '
1119                   'implicit execution via this method.' % label)
1120            raise exc.UnboundExecutionError(msg)
1121        return e._execute_clauseelement(self, multiparams, params)
1122
1123    def scalar(self, *multiparams, **params):
1124        """Compile and execute this ``ClauseElement``, returning the result's scalar representation."""
1125
1126        return self.execute(*multiparams, **params).scalar()
1127
1128    def compile(self, bind=None, column_keys=None, compiler=None, dialect=None, inline=False):
1129        """Compile this SQL expression.
1130
1131        The return value is a :class:`~sqlalchemy.engine.Compiled` object.
1132        Calling `str()` or `unicode()` on the returned value will yield
1133        a string representation of the result.   The :class:`~sqlalchemy.engine.Compiled`
1134        object also can return a dictionary of bind parameter names and
1135        values using the `params` accessor.
1136
1137        :param bind: An ``Engine`` or ``Connection`` from which a
1138          ``Compiled`` will be acquired.  This argument
1139          takes precedence over this ``ClauseElement``'s
1140          bound engine, if any.
1141
1142        :param column_keys: Used for INSERT and UPDATE statements, a list of
1143          column names which should be present in the VALUES clause
1144          of the compiled statement.  If ``None``, all columns
1145          from the target table object are rendered.
1146
1147        :param compiler: A ``Compiled`` instance which will be used to compile
1148          this expression.  This argument takes precedence
1149          over the `bind` and `dialect` arguments as well as
1150          this ``ClauseElement``'s bound engine, if
1151          any.
1152
1153        :param dialect: A ``Dialect`` instance frmo which a ``Compiled``
1154          will be acquired.  This argument takes precedence
1155          over the `bind` argument as well as this
1156          ``ClauseElement``'s bound engine, if any.
1157
1158        :param inline: Used for INSERT statements, for a dialect which does
1159          not support inline retrieval of newly generated
1160          primary key columns, will force the expression used
1161          to create the new primary key value to be rendered
1162          inline within the INSERT statement's VALUES clause.
1163          This typically refers to Sequence execution but
1164          may also refer to any server-side default generation
1165          function associated with a primary key `Column`.
1166
1167        """
1168        if compiler is None:
1169            if dialect is not None:
1170                compiler = dialect.statement_compiler(dialect, self, column_keys=column_keys, inline=inline)
1171            elif bind is not None:
1172                compiler = bind.statement_compiler(self, column_keys=column_keys, inline=inline)
1173            elif self.bind is not None:
1174                compiler = self.bind.statement_compiler(self, column_keys=column_keys, inline=inline)
1175            else:
1176                global DefaultDialect
1177                if DefaultDialect is None:
1178                    from sqlalchemy.engine.default import DefaultDialect
1179                dialect = DefaultDialect()
1180                compiler = dialect.statement_compiler(dialect, self, column_keys=column_keys, inline=inline)
1181        compiler.compile()
1182        return compiler
1183
1184    def __str__(self):
1185        return unicode(self.compile()).encode('ascii', 'backslashreplace')
1186
1187    def __and__(self, other):
1188        return and_(self, other)
1189
1190    def __or__(self, other):
1191        return or_(self, other)
1192
1193    def __invert__(self):
1194        return self._negate()
1195
1196    def _negate(self):
1197        if hasattr(self, 'negation_clause'):
1198            return self.negation_clause
1199        else:
1200            return _UnaryExpression(self.self_group(against=operators.inv), operator=operators.inv, negate=None)
1201
1202    def __repr__(self):
1203        friendly = getattr(self, 'description', None)
1204        if friendly is None:
1205            return object.__repr__(self)
1206        else:
1207            return '<%s.%s at 0x%x; %s>' % (
1208                self.__module__, self.__class__.__name__, id(self), friendly)
1209
1210
1211class _Immutable(object):
1212    """mark a ClauseElement as 'immutable' when expressions are cloned."""
1213
1214    def _clone(self):
1215        return self
1216
1217class Operators(object):
1218    def __and__(self, other):
1219        return self.operate(operators.and_, other)
1220
1221    def __or__(self, other):
1222        return self.operate(operators.or_, other)
1223
1224    def __invert__(self):
1225        return self.operate(operators.inv)
1226
1227    def op(self, opstring):
1228        def op(b):
1229            return self.operate(operators.op, opstring, b)
1230        return op
1231
1232    def operate(self, op, *other, **kwargs):
1233        raise NotImplementedError(str(op))
1234
1235    def reverse_operate(self, op, other, **kwargs):
1236        raise NotImplementedError(str(op))
1237
1238class ColumnOperators(Operators):
1239    """Defines comparison and math operations."""
1240
1241    timetuple = None
1242    """Hack, allows datetime objects to be compared on the LHS."""
1243
1244    def __lt__(self, other):
1245        return self.operate(operators.lt, other)
1246
1247    def __le__(self, other):
1248        return self.operate(operators.le, other)
1249
1250    __hash__ = Operators.__hash__
1251   
1252    def __eq__(self, other):
1253        return self.operate(operators.eq, other)
1254
1255    def __ne__(self, other):
1256        return self.operate(operators.ne, other)
1257
1258    def __gt__(self, other):
1259        return self.operate(operators.gt, other)
1260
1261    def __ge__(self, other):
1262        return self.operate(operators.ge, other)
1263
1264    def concat(self, other):
1265        return self.operate(operators.concat_op, other)
1266
1267    def like(self, other, escape=None):
1268        return self.operate(operators.like_op, other, escape=escape)
1269
1270    def ilike(self, other, escape=None):
1271        return self.operate(operators.ilike_op, other, escape=escape)
1272
1273    def in_(self, other):
1274        return self.operate(operators.in_op, other)
1275
1276    def startswith(self, other, **kwargs):
1277        return self.operate(operators.startswith_op, other, **kwargs)
1278
1279    def endswith(self, other, **kwargs):
1280        return self.operate(operators.endswith_op, other, **kwargs)
1281
1282    def contains(self, other, **kwargs):
1283        return self.operate(operators.contains_op, other, **kwargs)
1284
1285    def match(self, other, **kwargs):
1286        return self.operate(operators.match_op, other, **kwargs)
1287
1288    def desc(self):
1289        return self.operate(operators.desc_op)
1290
1291    def asc(self):
1292        return self.operate(operators.asc_op)
1293
1294    def collate(self, collation):
1295        return self.operate(operators.collate, collation)
1296
1297    def __radd__(self, other):
1298        return self.reverse_operate(operators.add, other)
1299
1300    def __rsub__(self, other):
1301        return self.reverse_operate(operators.sub, other)
1302
1303    def __rmul__(self, other):
1304        return self.reverse_operate(operators.mul, other)
1305
1306    def __rdiv__(self, other):
1307        return self.reverse_operate(operators.div, other)
1308
1309    def between(self, cleft, cright):
1310        return self.operate(operators.between_op, cleft, cright)
1311
1312    def distinct(self):
1313        return self.operate(operators.distinct_op)
1314
1315    def __add__(self, other):
1316        return self.operate(operators.add, other)
1317
1318    def __sub__(self, other):
1319        return self.operate(operators.sub, other)
1320
1321    def __mul__(self, other):
1322        return self.operate(operators.mul, other)
1323
1324    def __div__(self, other):
1325        return self.operate(operators.div, other)
1326
1327    def __mod__(self, other):
1328        return self.operate(operators.mod, other)
1329
1330    def __truediv__(self, other):
1331        return self.operate(operators.truediv, other)
1332
1333class _CompareMixin(ColumnOperators):
1334    """Defines comparison and math operations for ``ClauseElement`` instances."""
1335
1336    def __compare(self, op, obj, negate=None, reverse=False, **kwargs):
1337        if obj is None or isinstance(obj, _Null):
1338            if op == operators.eq:
1339                return _BinaryExpression(self, null(), operators.is_, negate=operators.isnot)
1340            elif op == operators.ne:
1341                return _BinaryExpression(self, null(), operators.isnot, negate=operators.is_)
1342            else:
1343                raise exc.ArgumentError("Only '='/'!=' operators can be used with NULL")
1344        else:
1345            obj = self._check_literal(obj)
1346
1347        if reverse:
1348            return _BinaryExpression(obj, self, op, type_=sqltypes.Boolean, negate=negate, modifiers=kwargs)
1349        else:
1350            return _BinaryExpression(self, obj, op, type_=sqltypes.Boolean, negate=negate, modifiers=kwargs)
1351
1352    def __operate(self, op, obj, reverse=False):
1353        obj = self._check_literal(obj)
1354
1355        type_ = self._compare_type(obj)
1356
1357        if reverse:
1358            return _BinaryExpression(obj, self, type_.adapt_operator(op), type_=type_)
1359        else:
1360            return _BinaryExpression(self, obj, type_.adapt_operator(op), type_=type_)
1361
1362    # a mapping of operators with the method they use, along with their negated
1363    # operator for comparison operators
1364    operators = {
1365        operators.add : (__operate,),
1366        operators.mul : (__operate,),
1367        operators.sub : (__operate,),
1368        operators.div : (__operate,),
1369        operators.mod : (__operate,),
1370        operators.truediv : (__operate,),
1371        operators.lt : (__compare, operators.ge),
1372        operators.le : (__compare, operators.gt),
1373        operators.ne : (__compare, operators.eq),
1374        operators.gt : (__compare, operators.le),
1375        operators.ge : (__compare, operators.lt),
1376        operators.eq : (__compare, operators.ne),
1377        operators.like_op : (__compare, operators.notlike_op),
1378        operators.ilike_op : (__compare, operators.notilike_op),
1379    }
1380
1381    def operate(self, op, *other, **kwargs):
1382        o = _CompareMixin.operators[op]
1383        return o[0](self, op, other[0], *o[1:], **kwargs)
1384
1385    def reverse_operate(self, op, other, **kwargs):
1386        o = _CompareMixin.operators[op]
1387        return o[0](self, op, other, reverse=True, *o[1:], **kwargs)
1388
1389    def in_(self, other):
1390        return self._in_impl(operators.in_op, operators.notin_op, other)
1391
1392    def _in_impl(self, op, negate_op, seq_or_selectable):
1393        seq_or_selectable = _clause_element_as_expr(seq_or_selectable)
1394           
1395        if isinstance(seq_or_selectable, _ScalarSelect):
1396            return self.__compare( op, seq_or_selectable, negate=negate_op)
1397
1398        elif isinstance(seq_or_selectable, _SelectBaseMixin):
1399            # TODO: if we ever want to support (x, y, z) IN (select x, y, z from table),
1400            # we would need a multi-column version of as_scalar() to produce a multi-
1401            # column selectable that does not export itself as a FROM clause
1402            return self.__compare( op, seq_or_selectable.as_scalar(), negate=negate_op)
1403
1404        elif isinstance(seq_or_selectable, Selectable):
1405            return self.__compare( op, seq_or_selectable, negate=negate_op)
1406
1407        # Handle non selectable arguments as sequences
1408        args = []
1409        for o in seq_or_selectable:
1410            if not _is_literal(o):
1411                if not isinstance( o, _CompareMixin):
1412                    raise exc.InvalidRequestError(
1413                        "in() function accepts either a list of non-selectable values, or a selectable: %r" % o)
1414            else:
1415                o = self._bind_param(o)
1416            args.append(o)
1417
1418        if len(args) == 0:
1419            # Special case handling for empty IN's, behave like comparison against zero row selectable
1420            return self != self
1421
1422        return self.__compare(op, ClauseList(*args).self_group(against=op), negate=negate_op)
1423
1424    def startswith(self, other, escape=None):
1425        """Produce the clause ``LIKE '<other>%'``"""
1426
1427        # use __radd__ to force string concat behavior
1428        return self.__compare(
1429            operators.like_op,
1430            literal_column("'%'", type_=sqltypes.String).__radd__(self._check_literal(other)),
1431            escape=escape)
1432
1433    def endswith(self, other, escape=None):
1434        """Produce the clause ``LIKE '%<other>'``"""
1435
1436        return self.__compare(
1437            operators.like_op,
1438            literal_column("'%'", type_=sqltypes.String) + self._check_literal(other),
1439            escape=escape)
1440
1441    def contains(self, other, escape=None):
1442        """Produce the clause ``LIKE '%<other>%'``"""
1443
1444        return self.__compare(
1445            operators.like_op,
1446            literal_column("'%'", type_=sqltypes.String) +
1447                self._check_literal(other) +
1448                literal_column("'%'", type_=sqltypes.String),
1449            escape=escape)
1450
1451    def match(self, other):
1452        """Produce a MATCH clause, i.e. ``MATCH '<other>'``
1453
1454        The allowed contents of ``other`` are database backend specific.
1455
1456        """
1457        return self.__compare(operators.match_op, self._check_literal(other))
1458
1459    def label(self, name):
1460        """Produce a column label, i.e. ``<columnname> AS <name>``.
1461
1462        if 'name' is None, an anonymous label name will be generated.
1463
1464        """
1465        return _Label(name, self, self.type)
1466
1467    def desc(self):
1468        """Produce a DESC clause, i.e. ``<columnname> DESC``"""
1469
1470        return desc(self)
1471
1472    def asc(self):
1473        """Produce a ASC clause, i.e. ``<columnname> ASC``"""
1474
1475        return asc(self)
1476
1477    def distinct(self):
1478        """Produce a DISTINCT clause, i.e. ``DISTINCT <columnname>``"""
1479        return _UnaryExpression(self, operator=operators.distinct_op, type_=self.type)
1480
1481    def between(self, cleft, cright):
1482        """Produce a BETWEEN clause, i.e. ``<column> BETWEEN <cleft> AND <cright>``"""
1483
1484        return _BinaryExpression(
1485                self,
1486                ClauseList(
1487                    self._check_literal(cleft),
1488                    self._check_literal(cright),
1489                    operator=operators.and_,
1490                    group=False),
1491                operators.between_op)
1492
1493    def collate(self, collation):
1494        """Produce a COLLATE clause, i.e. ``<column> COLLATE utf8_bin``"""
1495
1496        return collate(self, collation)
1497
1498    def op(self, operator):
1499        """produce a generic operator function.
1500
1501        e.g.::
1502
1503          somecolumn.op("*")(5)
1504
1505        produces::
1506
1507          somecolumn * 5
1508
1509        operator
1510          a string which will be output as the infix operator between
1511          this ``ClauseElement`` and the expression passed to the
1512          generated function.
1513
1514        """
1515        return lambda other: self.__operate(operator, other)
1516
1517    def _bind_param(self, obj):
1518        return _BindParamClause(None, obj, type_=self.type, unique=True)
1519
1520    def _check_literal(self, other):
1521        if isinstance(other, _BindParamClause) and isinstance(other.type, sqltypes.NullType):
1522            other.type = self.type
1523            return other
1524        elif hasattr(other, '__clause_element__'):
1525            return other.__clause_element__()
1526        elif not isinstance(other, ClauseElement):
1527            return self._bind_param(other)
1528        elif isinstance(other, (_SelectBaseMixin, Alias)):
1529            return other.as_scalar()
1530        else:
1531            return other
1532
1533    def _compare_type(self, obj):
1534        """Allow subclasses to override the type used in constructing
1535        ``_BinaryExpression`` objects.
1536
1537        Default return value is the type of the given object.
1538
1539        """
1540        return obj.type
1541
1542class ColumnElement(ClauseElement, _CompareMixin):
1543    """Represent an element that is usable within the "column clause" portion of a ``SELECT`` statement.
1544
1545    This includes columns associated with tables, aliases, and
1546    subqueries, expressions, function calls, SQL keywords such as
1547    ``NULL``, literals, etc.  ``ColumnElement`` is the ultimate base
1548    class for all such elements.
1549
1550    ``ColumnElement`` supports the ability to be a *proxy* element,
1551    which indicates that the ``ColumnElement`` may be associated with
1552    a ``Selectable`` which was derived from another ``Selectable``.
1553    An example of a "derived" ``Selectable`` is an ``Alias`` of a
1554    ``Table``.
1555
1556    A ``ColumnElement``, by subclassing the ``_CompareMixin`` mixin
1557    class, provides the ability to generate new ``ClauseElement``
1558    objects using Python expressions.  See the ``_CompareMixin``
1559    docstring for more details.
1560
1561    """
1562
1563    __visit_name__ = 'column'
1564    primary_key = False
1565    foreign_keys = []
1566    quote = None
1567    _label = None
1568   
1569    @property
1570    def _select_iterable(self):
1571        return (self, )
1572
1573    @util.memoized_property
1574    def base_columns(self):
1575        return util.column_set(c for c in self.proxy_set
1576                                     if not hasattr(c, 'proxies'))
1577
1578    @util.memoized_property
1579    def proxy_set(self):
1580        s = util.column_set([self])
1581        if hasattr(self, 'proxies'):
1582            for c in self.proxies:
1583                s.update(c.proxy_set)
1584        return s
1585
1586    def shares_lineage(self, othercolumn):
1587        """Return True if the given ``ColumnElement`` has a common ancestor to this ``ColumnElement``."""
1588
1589        return bool(self.proxy_set.intersection(othercolumn.proxy_set))
1590
1591    def _make_proxy(self, selectable, name=None):
1592        """Create a new ``ColumnElement`` representing this
1593        ``ColumnElement`` as it appears in the select list of a
1594        descending selectable.
1595
1596        """
1597
1598        if name:
1599            co = ColumnClause(name, selectable, type_=getattr(self, 'type', None))
1600        else:
1601            name = str(self)
1602            co = ColumnClause(self.anon_label, selectable, type_=getattr(self, 'type', None))
1603
1604        co.proxies = [self]
1605        selectable.columns[name] = co
1606        return co
1607
1608    @util.memoized_property
1609    def anon_label(self):
1610        """provides a constant 'anonymous label' for this ColumnElement.
1611
1612        This is a label() expression which will be named at compile time.
1613        The same label() is returned each time anon_label is called so
1614        that expressions can reference anon_label multiple times, producing
1615        the same label name at compile time.
1616
1617        the compiler uses this function automatically at compile time
1618        for expressions that are known to be 'unnamed' like binary
1619        expressions and function calls.
1620
1621        """
1622        return _generated_label("%%(%d %s)s" % (id(self), getattr(self, 'name', 'anon')))
1623
1624class ColumnCollection(util.OrderedProperties):
1625    """An ordered dictionary that stores a list of ColumnElement
1626    instances.
1627
1628    Overrides the ``__eq__()`` method to produce SQL clauses between
1629    sets of correlated columns.
1630
1631    """
1632
1633    def __init__(self, *cols):
1634        super(ColumnCollection, self).__init__()
1635        [self.add(c) for c in cols]
1636
1637    def __str__(self):
1638        return repr([str(c) for c in self])
1639
1640    def replace(self, column):
1641        """add the given column to this collection, removing unaliased versions of this column
1642           as well as existing columns with the same key.
1643
1644            e.g.::
1645
1646                t = Table('sometable', metadata, Column('col1', Integer))
1647                t.columns.replace(Column('col1', Integer, key='columnone'))
1648
1649            will remove the original 'col1' from the collection, and add
1650            the new column under the name 'columnname'.
1651
1652           Used by schema.Column to override columns during table reflection.
1653
1654        """
1655        if column.name in self and column.key != column.name:
1656            other = self[column.name]
1657            if other.name == other.key:
1658                del self[other.name]
1659        util.OrderedProperties.__setitem__(self, column.key, column)
1660
1661    def add(self, column):
1662        """Add a column to this collection.
1663
1664        The key attribute of the column will be used as the hash key
1665        for this dictionary.
1666
1667        """
1668        self[column.key] = column
1669
1670    def __setitem__(self, key, value):
1671        if key in self:
1672            # this warning is primarily to catch select() statements which have conflicting
1673            # column names in their exported columns collection
1674            existing = self[key]
1675            if not existing.shares_lineage(value):
1676                table = getattr(existing, 'table', None) and existing.table.description
1677                util.warn(("Column %r on table %r being replaced by another "
1678                           "column with the same key.  Consider use_labels "
1679                           "for select() statements.")  % (key, table))
1680        util.OrderedProperties.__setitem__(self, key, value)
1681
1682    def remove(self, column):
1683        del self[column.key]
1684
1685    def extend(self, iter):
1686        for c in iter:
1687            self.add(c)
1688
1689    __hash__ = None
1690   
1691    def __eq__(self, other):
1692        l = []
1693        for c in other:
1694            for local in self:
1695                if c.shares_lineage(local):
1696                    l.append(c==local)
1697        return and_(*l)
1698
1699    def __contains__(self, other):
1700        if not isinstance(other, basestring):
1701            raise exc.ArgumentError("__contains__ requires a string argument")
1702        return util.OrderedProperties.__contains__(self, other)
1703
1704    def contains_column(self, col):
1705        # have to use a Set here, because it will compare the identity
1706        # of the column, not just using "==" for comparison which will always return a
1707        # "True" value (i.e. a BinaryClause...)
1708        return col in util.column_set(self)
1709
1710class ColumnSet(util.ordered_column_set):
1711    def contains_column(self, col):
1712        return col in self
1713
1714    def extend(self, cols):
1715        for col in cols:
1716            self.add(col)
1717
1718    def __add__(self, other):
1719        return list(self) + list(other)
1720
1721    def __eq__(self, other):
1722        l = []
1723        for c in other:
1724            for local in self:
1725                if c.shares_lineage(local):
1726                    l.append(c==local)
1727        return and_(*l)
1728
1729    def __hash__(self):
1730        return hash(tuple(x for x in self))
1731
1732class Selectable(ClauseElement):
1733    """mark a class as being selectable"""
1734    __visit_name__ = 'selectable'
1735
1736class FromClause(Selectable):
1737    """Represent an element that can be used within the ``FROM`` clause of a ``SELECT`` statement."""
1738
1739    __visit_name__ = 'fromclause'
1740    named_with_column = False
1741    _hide_froms = []
1742    quote = None
1743    schema = None
1744
1745    def count(self, whereclause=None, **params):
1746        """return a SELECT COUNT generated against this ``FromClause``."""
1747
1748        if self.primary_key:
1749            col = list(self.primary_key)[0]
1750        else:
1751            col = list(self.columns)[0]
1752        return select([func.count(col).label('tbl_row_count')], whereclause, from_obj=[self], **params)
1753
1754    def select(self, whereclause=None, **params):
1755        """return a SELECT of this ``FromClause``."""
1756
1757        return select([self], whereclause, **params)
1758
1759    def join(self, right, onclause=None, isouter=False):
1760        """return a join of this ``FromClause`` against another ``FromClause``."""
1761
1762        return Join(self, right, onclause, isouter)
1763
1764    def outerjoin(self, right, onclause=None):
1765        """return an outer join of this ``FromClause`` against another ``FromClause``."""
1766
1767        return Join(self, right, onclause, True)
1768
1769    def alias(self, name=None):
1770        """return an alias of this ``FromClause``.
1771       
1772        For table objects, this has the effect of the table being rendered
1773        as ``tablename AS aliasname`` in a SELECT statement. 
1774        For select objects, the effect is that of creating a named
1775        subquery, i.e. ``(select ...) AS aliasname``.
1776        The ``alias()`` method is the general way to create
1777        a "subquery" out of an existing SELECT.
1778       
1779        The ``name`` parameter is optional, and if left blank an
1780        "anonymous" name will be generated at compile time, guaranteed
1781        to be unique against other anonymous constructs used in the
1782        same statement.
1783       
1784        """
1785
1786        return Alias(self, name)
1787
1788    def is_derived_from(self, fromclause):
1789        """Return True if this FromClause is 'derived' from the given FromClause.
1790
1791        An example would be an Alias of a Table is derived from that Table.
1792
1793        """
1794        return fromclause in self._cloned_set
1795
1796    def replace_selectable(self, old, alias):
1797        """replace all occurences of FromClause 'old' with the given Alias object, returning a copy of this ``FromClause``."""
1798
1799        global ClauseAdapter
1800        if ClauseAdapter is None:
1801            from sqlalchemy.sql.util import ClauseAdapter
1802        return ClauseAdapter(alias).traverse(self)
1803
1804    def correspond_on_equivalents(self, column, equivalents):
1805        """Return corresponding_column for the given column, or if None
1806        search for a match in the given dictionary.
1807       
1808        """
1809        col = self.corresponding_column(column, require_embedded=True)
1810        if col is None and col in equivalents:
1811            for equiv in equivalents[col]:
1812                nc = self.corresponding_column(equiv, require_embedded=True)
1813                if nc:
1814                    return nc
1815        return col
1816
1817    def corresponding_column(self, column, require_embedded=False):
1818        """Given a ``ColumnElement``, return the exported ``ColumnElement``
1819        object from this ``Selectable`` which corresponds to that
1820        original ``Column`` via a common anscestor column.
1821
1822        :param column: the target ``ColumnElement`` to be matched
1823
1824        :param require_embedded: only return corresponding columns for the given
1825          ``ColumnElement``, if the given ``ColumnElement`` is
1826          actually present within a sub-element of this
1827          ``FromClause``.  Normally the column will match if it merely
1828          shares a common anscestor with one of the exported columns
1829          of this ``FromClause``.
1830
1831        """
1832        # dont dig around if the column is locally present
1833        if self.c.contains_column(column):
1834            return column
1835
1836        col, intersect = None, None
1837        target_set = column.proxy_set
1838        cols = self.c
1839        for c in cols:
1840            i = c.proxy_set.intersection(target_set)
1841            if i and \
1842                (not require_embedded or c.proxy_set.issuperset(target_set)):
1843               
1844                if col is None:
1845                    # no corresponding column yet, pick this one.
1846                    col, intersect = c, i
1847                elif len(i) > len(intersect):
1848                    # 'c' has a larger field of correspondence than 'col'.
1849                    # i.e. selectable.c.a1_x->a1.c.x->table.c.x matches a1.c.x->table.c.x better than
1850                    # selectable.c.x->table.c.x does.
1851                    col, intersect = c, i
1852                elif i == intersect:
1853                    # they have the same field of correspondence.
1854                    # see which proxy_set has fewer columns in it, which indicates a
1855                    # closer relationship with the root column.  Also take into account the
1856                    # "weight" attribute which CompoundSelect() uses to give higher precedence to
1857                    # columns based on vertical position in the compound statement, and discard columns
1858                    # that have no reference to the target column (also occurs with CompoundSelect)
1859                    col_distance = util.reduce(operator.add,
1860                                        [sc._annotations.get('weight', 1) for sc in col.proxy_set if sc.shares_lineage(column)]
1861                                    )
1862                    c_distance = util.reduce(operator.add,
1863                                        [sc._annotations.get('weight', 1) for sc in c.proxy_set if sc.shares_lineage(column)]
1864                                    )
1865                    if \
1866                        c_distance < col_distance:
1867                        col, intersect = c, i
1868        return col
1869
1870    @property
1871    def description(self):
1872        """a brief description of this FromClause.
1873
1874        Used primarily for error message formatting.
1875
1876        """
1877        return getattr(self, 'name', self.__class__.__name__ + " object")
1878
1879    def _reset_exported(self):
1880        """delete memoized collections when a FromClause is cloned."""
1881
1882        for attr in ('_columns', '_primary_key' '_foreign_keys', 'locate_all_froms'):
1883            self.__dict__.pop(attr, None)
1884
1885    @util.memoized_property
1886    def _columns(self):
1887        """Return the collection of Column objects contained by this FromClause."""
1888
1889        self._export_columns()
1890        return self._columns
1891
1892    @util.memoized_property
1893    def _primary_key(self):
1894        """Return the collection of Column objects which comprise the primary key of this FromClause."""
1895
1896        self._export_columns()
1897        return self._primary_key
1898
1899    @util.memoized_property
1900    def _foreign_keys(self):
1901        """Return the collection of ForeignKey objects which this FromClause references."""
1902
1903        self._export_columns()
1904        return self._foreign_keys
1905
1906    columns = property(attrgetter('_columns'), doc=_columns.__doc__)
1907    primary_key = property(attrgetter('_primary_key'), doc=_primary_key.__doc__)
1908    foreign_keys = property(attrgetter('_foreign_keys'), doc=_foreign_keys.__doc__)
1909
1910    # synonyms for 'columns'
1911    c = _select_iterable = property(attrgetter('columns'), doc=_columns.__doc__)
1912
1913    def _export_columns(self):
1914        """Initialize column collections."""
1915
1916        self._columns = ColumnCollection()
1917        self._primary_key = ColumnSet()
1918        self._foreign_keys = set()
1919        self._populate_column_collection()
1920
1921    def _populate_column_collection(self):
1922        pass
1923
1924class _BindParamClause(ColumnElement):
1925    """Represent a bind parameter.
1926
1927    Public constructor is the ``bindparam()`` function.
1928
1929    """
1930
1931    __visit_name__ = 'bindparam'
1932    quote = None
1933
1934    def __init__(self, key, value, type_=None, unique=False, isoutparam=False, shortname=None):
1935        """Construct a _BindParamClause.
1936
1937        key
1938          the key for this bind param.  Will be used in the generated
1939          SQL statement for dialects that use named parameters.  This
1940          value may be modified when part of a compilation operation,
1941          if other ``_BindParamClause`` objects exist with the same
1942          key, or if its length is too long and truncation is
1943          required.
1944
1945        value
1946          Initial value for this bind param.  This value may be
1947          overridden by the dictionary of parameters sent to statement
1948          compilation/execution.
1949
1950        shortname
1951          deprecated.
1952
1953        type\_
1954          A ``TypeEngine`` object that will be used to pre-process the
1955          value corresponding to this ``_BindParamClause`` at
1956          execution time.
1957
1958        unique
1959          if True, the key name of this BindParamClause will be
1960          modified if another ``_BindParamClause`` of the same name
1961          already has been located within the containing
1962          ``ClauseElement``.
1963
1964        isoutparam
1965          if True, the parameter should be treated like a stored procedure "OUT"
1966          parameter.
1967
1968        """
1969        if unique:
1970            self.key = _generated_label("%%(%d %s)s" % (id(self), key or 'param'))
1971        else:
1972            self.key = key or _generated_label("%%(%d param)s" % id(self))
1973        self._orig_key = key or 'param'
1974        self.unique = unique
1975        self.value = value
1976        self.isoutparam = isoutparam
1977        self.shortname = shortname
1978
1979        if type_ is None:
1980            self.type = sqltypes.type_map.get(type(value), sqltypes.NullType)()
1981        elif isinstance(type_, type):
1982            self.type = type_()
1983        else:
1984            self.type = type_
1985
1986    def _clone(self):
1987        c = ClauseElement._clone(self)
1988        if self.unique:
1989            c.key = _generated_label("%%(%d %s)s" % (id(c), c._orig_key or 'param'))
1990        return c
1991
1992    def _convert_to_unique(self):
1993        if not self.unique:
1994            self.unique = True
1995            self.key = _generated_label("%%(%d %s)s" % (id(self), self._orig_key or 'param'))
1996
1997    def bind_processor(self, dialect):
1998        return self.type.dialect_impl(dialect).bind_processor(dialect)
1999
2000    def _compare_type(self, obj):
2001        if not isinstance(self.type, sqltypes.NullType):
2002            return self.type
2003        else:
2004            return obj.type
2005
2006    def compare(self, other):
2007        """Compare this ``_BindParamClause`` to the given clause.
2008
2009        Since ``compare()`` is meant to compare statement syntax, this
2010        method returns True if the two ``_BindParamClauses`` have just
2011        the same type.
2012
2013        """
2014        return isinstance(other, _BindParamClause) and other.type.__class__ == self.type.__class__ and self.value == other.value
2015
2016    def __getstate__(self):
2017        """execute a deferred value for serialization purposes."""
2018
2019        d = self.__dict__.copy()
2020        v = self.value
2021        if util.callable(v):
2022            v = v()
2023        d['value'] = v
2024        return d
2025
2026    def __repr__(self):
2027        return "_BindParamClause(%s, %s, type_=%s)" % (repr(self.key), repr(self.value), repr(self.type))
2028
2029class _TypeClause(ClauseElement):
2030    """Handle a type keyword in a SQL statement.
2031
2032    Used by the ``Case`` statement.
2033
2034    """
2035
2036    __visit_name__ = 'typeclause'
2037
2038    def __init__(self, type):
2039        self.type = type
2040
2041
2042class _TextClause(ClauseElement):
2043    """Represent a literal SQL text fragment.
2044
2045    Public constructor is the ``text()`` function.
2046
2047    """
2048
2049    __visit_name__ = 'textclause'
2050
2051    _bind_params_regex = re.compile(r'(?<![:\w\x5c]):(\w+)(?!:)', re.UNICODE)
2052    supports_execution = True
2053
2054    @property
2055    def _select_iterable(self):
2056        return (self,)
2057
2058    _hide_froms = []
2059
2060    def __init__(self, text = "", bind=None, bindparams=None, typemap=None, autocommit=False):
2061        self._bind = bind
2062        self.bindparams = {}
2063        self.typemap = typemap
2064        self._autocommit = autocommit
2065        if typemap is not None:
2066            for key in typemap.keys():
2067                typemap[key] = sqltypes.to_instance(typemap[key])
2068
2069        def repl(m):
2070            self.bindparams[m.group(1)] = bindparam(m.group(1))
2071            return ":%s" % m.group(1)
2072
2073        # scan the string and search for bind parameter names, add them
2074        # to the list of bindparams
2075        self.text = self._bind_params_regex.sub(repl, text)
2076        if bindparams is not None:
2077            for b in bindparams:
2078                self.bindparams[b.key] = b
2079               
2080    @property
2081    def type(self):
2082        if self.typemap is not None and len(self.typemap) == 1:
2083            return list(self.typemap)[0]
2084        else:
2085            return None
2086
2087    def _copy_internals(self, clone=_clone):
2088        self.bindparams = dict((b.key, clone(b))
2089                               for b in self.bindparams.values())
2090
2091    def get_children(self, **kwargs):
2092        return self.bindparams.values()
2093
2094
2095class _Null(ColumnElement):
2096    """Represent the NULL keyword in a SQL statement.
2097
2098    Public constructor is the ``null()`` function.
2099
2100    """
2101
2102    __visit_name__ = 'null'
2103
2104    def __init__(self):
2105        self.type = sqltypes.NULLTYPE
2106
2107
2108class ClauseList(ClauseElement):
2109    """Describe a list of clauses, separated by an operator.
2110
2111    By default, is comma-separated, such as a column listing.
2112
2113    """
2114    __visit_name__ = 'clauselist'
2115
2116    def __init__(self, *clauses, **kwargs):
2117        self.operator = kwargs.pop('operator', operators.comma_op)
2118        self.group = kwargs.pop('group', True)
2119        self.group_contents = kwargs.pop('group_contents', True)
2120        if self.group_contents:
2121            self.clauses = [
2122                _literal_as_text(clause).self_group(against=self.operator)
2123                for clause in clauses if clause is not None]
2124        else:
2125            self.clauses = [
2126                _literal_as_text(clause)
2127                for clause in clauses if clause is not None]
2128
2129    def __iter__(self):
2130        return iter(self.clauses)
2131
2132    def __len__(self):
2133        return len(self.clauses)
2134
2135    @property
2136    def _select_iterable(self):
2137        return iter(self)
2138
2139    def append(self, clause):
2140        # TODO: not sure if i like the 'group_contents' flag.  need to
2141        # define the difference between a ClauseList of ClauseLists,
2142        # and a "flattened" ClauseList of ClauseLists.  flatten()
2143        # method ?
2144        if self.group_contents:
2145            self.clauses.append(_literal_as_text(clause).self_group(against=self.operator))
2146        else:
2147            self.clauses.append(_literal_as_text(clause))
2148
2149    def _copy_internals(self, clone=_clone):
2150        self.clauses = [clone(clause) for clause in self.clauses]
2151
2152    def get_children(self, **kwargs):
2153        return self.clauses
2154
2155    @property
2156    def _from_objects(self):
2157        return list(itertools.chain(*[c._from_objects for c in self.clauses]))
2158
2159    def self_group(self, against=None):
2160        if self.group and self.operator is not against and operators.is_precedent(self.operator, against):
2161            return _Grouping(self)
2162        else:
2163            return self
2164
2165    def compare(self, other):
2166        """Compare this ``ClauseList`` to the given ``ClauseList``,
2167        including a comparison of all the clause items.
2168
2169        """
2170        if not isinstance(other, ClauseList) and len(self.clauses) == 1:
2171            return self.clauses[0].compare(other)
2172        elif isinstance(other, ClauseList) and len(self.clauses) == len(other.clauses):
2173            for i in range(0, len(self.clauses)):
2174                if not self.clauses[i].compare(other.clauses[i]):
2175                    return False
2176            else:
2177                return self.operator == other.operator
2178        else:
2179            return False
2180
2181class BooleanClauseList(ClauseList, ColumnElement):
2182    __visit_name__ = 'clauselist'
2183
2184    def __init__(self, *clauses, **kwargs):
2185        super(BooleanClauseList, self).__init__(*clauses, **kwargs)
2186        self.type = sqltypes.to_instance(kwargs.get('type_', sqltypes.Boolean))
2187
2188    @property
2189    def _select_iterable(self):
2190        return (self, )
2191
2192
2193class _Case(ColumnElement):
2194    __visit_name__ = 'case'
2195
2196    def __init__(self, whens, value=None, else_=None):
2197        try:
2198            whens = util.dictlike_iteritems(whens)
2199        except TypeError:
2200            pass
2201
2202        if value:
2203            whenlist = [(_literal_as_binds(c).self_group(), _literal_as_binds(r)) for (c, r) in whens]
2204        else:
2205            whenlist = [(_no_literals(c).self_group(), _literal_as_binds(r)) for (c, r) in whens]
2206
2207        if whenlist:
2208            type_ = list(whenlist[-1])[-1].type
2209        else:
2210            type_ = None
2211
2212        if value is None:
2213            self.value = None
2214        else:
2215            self.value = _literal_as_binds(value)
2216
2217        self.type = type_
2218        self.whens = whenlist
2219        if else_ is not None:
2220            self.else_ = _literal_as_binds(else_)
2221        else:
2222            self.else_ = None
2223
2224    def _copy_internals(self, clone=_clone):
2225        if self.value:
2226            self.value = clone(self.value)
2227        self.whens = [(clone(x), clone(y)) for x, y in self.whens]
2228        if self.else_:
2229            self.else_ = clone(self.else_)
2230
2231    def get_children(self, **kwargs):
2232        if self.value:
2233            yield self.value
2234        for x, y in self.whens:
2235            yield x
2236            yield y
2237        if self.else_:
2238            yield self.else_
2239
2240    @property
2241    def _from_objects(self):
2242        return list(itertools.chain(*[x._from_objects for x in self.get_children()]))
2243
2244class Function(ColumnElement, FromClause):
2245    """Describe a SQL function."""
2246
2247    __visit_name__ = 'function'
2248
2249    def __init__(self, name, *clauses, **kwargs):
2250        self.packagenames = kwargs.get('packagenames', None) or []
2251        self.name = name
2252        self._bind = kwargs.get('bind', None)
2253        args = [_literal_as_binds(c, self.name) for c in clauses]
2254        self.clause_expr = ClauseList(operator=operators.comma_op, group_contents=True, *args).self_group()
2255        self.type = sqltypes.to_instance(kwargs.get('type_', None))
2256
2257    @property
2258    def key(self):
2259        return self.name
2260
2261    @property
2262    def columns(self):
2263        return [self]
2264
2265    @util.memoized_property
2266    def clauses(self):
2267        return self.clause_expr.element
2268       
2269    @property
2270    def _from_objects(self):
2271        return self.clauses._from_objects
2272
2273    def get_children(self, **kwargs):
2274        return self.clause_expr,
2275
2276    def _copy_internals(self, clone=_clone):
2277        self.clause_expr = clone(self.clause_expr)
2278        self._reset_exported()
2279        util.reset_memoized(self, 'clauses')
2280       
2281    def _bind_param(self, obj):
2282        return _BindParamClause(self.name, obj, type_=self.type, unique=True)
2283
2284    def select(self):
2285        return select([self])
2286
2287    def scalar(self):
2288        return select([self]).execute().scalar()
2289
2290    def execute(self):
2291        return select([self]).execute()
2292
2293    def _compare_type(self, obj):
2294        return self.type
2295
2296
2297class _Cast(ColumnElement):
2298
2299    __visit_name__ = 'cast'
2300
2301    def __init__(self, clause, totype, **kwargs):
2302        self.type = sqltypes.to_instance(totype)
2303        self.clause = _literal_as_binds(clause, None)
2304        self.typeclause = _TypeClause(self.type)
2305
2306    def _copy_internals(self, clone=_clone):
2307        self.clause = clone(self.clause)
2308        self.typeclause = clone(self.typeclause)
2309
2310    def get_children(self, **kwargs):
2311        return self.clause, self.typeclause
2312
2313    @property
2314    def _from_objects(self):
2315        return self.clause._from_objects
2316
2317
2318class _Extract(ColumnElement):
2319
2320    __visit_name__ = 'extract'
2321
2322    def __init__(self, field, expr, **kwargs):
2323        self.type = sqltypes.Integer()
2324        self.field = field
2325        self.expr = _literal_as_binds(expr, None)
2326
2327    def _copy_internals(self, clone=_clone):
2328        self.expr = clone(self.expr)
2329
2330    def get_children(self, **kwargs):
2331        return self.expr,
2332
2333    @property
2334    def _from_objects(self):
2335        return self.expr._from_objects
2336
2337
2338class _UnaryExpression(ColumnElement):
2339
2340    __visit_name__ = 'unary'
2341
2342    def __init__(self, element, operator=None, modifier=None, type_=None, negate=None):
2343        self.operator = operator
2344        self.modifier = modifier
2345
2346        self.element = _literal_as_text(element).self_group(against=self.operator or self.modifier)
2347        self.type = sqltypes.to_instance(type_)
2348        self.negate = negate
2349
2350    @property
2351    def _from_objects(self):
2352        return self.element._from_objects
2353
2354    def _copy_internals(self, clone=_clone):
2355        self.element = clone(self.element)
2356
2357    def get_children(self, **kwargs):
2358        return self.element,
2359
2360    def compare(self, other):
2361        """Compare this ``_UnaryExpression`` against the given ``ClauseElement``."""
2362
2363        return (
2364            isinstance(other, _UnaryExpression) and
2365            self.operator == other.operator and
2366            self.modifier == other.modifier and
2367            self.element.compare(other.element)
2368        )
2369
2370    def _negate(self):
2371        if self.negate is not None:
2372            return _UnaryExpression(
2373                self.element,
2374                operator=self.negate,
2375                negate=self.operator,
2376                modifier=self.modifier,
2377                type_=self.type)
2378        else:
2379            return super(_UnaryExpression, self)._negate()
2380
2381    def self_group(self, against):
2382        if self.operator and operators.is_precedent(self.operator, against):
2383            return _Grouping(self)
2384        else:
2385            return self
2386
2387
2388class _BinaryExpression(ColumnElement):
2389    """Represent an expression that is ``LEFT <operator> RIGHT``."""
2390
2391    __visit_name__ = 'binary'
2392
2393    def __init__(self, left, right, operator, type_=None, negate=None, modifiers=None):
2394        self.left = _literal_as_text(left).self_group(against=operator)
2395        self.right = _literal_as_text(right).self_group(against=operator)
2396        self.operator = operator
2397        self.type = sqltypes.to_instance(type_)
2398        self.negate = negate
2399        if modifiers is None:
2400            self.modifiers = {}
2401        else:
2402            self.modifiers = modifiers
2403
2404    @property
2405    def _from_objects(self):
2406        return self.left._from_objects + self.right._from_objects
2407
2408    def _copy_internals(self, clone=_clone):
2409        self.left = clone(self.left)
2410        self.right = clone(self.right)
2411
2412    def get_children(self, **kwargs):
2413        return self.left, self.right
2414
2415    def compare(self, other):
2416        """Compare this ``_BinaryExpression`` against the given ``_BinaryExpression``."""
2417
2418        return (
2419            isinstance(other, _BinaryExpression) and
2420            self.operator == other.operator and
2421            (
2422                self.left.compare(other.left) and
2423                self.right.compare(other.right) or
2424                (
2425                    operators.is_commutative(self.operator) and
2426                    self.left.compare(other.right) and
2427                    self.right.compare(other.left)
2428                )
2429            )
2430        )
2431
2432    def self_group(self, against=None):
2433        # use small/large defaults for comparison so that unknown
2434        # operators are always parenthesized
2435        if self.operator is not against and operators.is_precedent(self.operator, against):
2436            return _Grouping(self)
2437        else:
2438            return self
2439
2440    def _negate(self):
2441        if self.negate is not None:
2442            return _BinaryExpression(
2443                self.left,
2444                self.right,
2445                self.negate,
2446                negate=self.operator,
2447                type_=self.type,
2448                modifiers=self.modifiers)
2449        else:
2450            return super(_BinaryExpression, self)._negate()
2451
2452class _Exists(_UnaryExpression):
2453    __visit_name__ = _UnaryExpression.__visit_name__
2454    _from_objects = []
2455
2456    def __init__(self, *args, **kwargs):
2457        if args and isinstance(args[0], _SelectBaseMixin):
2458            s = args[0]
2459        else:
2460            if not args:
2461                args = ([literal_column('*')],)
2462            s = select(*args, **kwargs).as_scalar().self_group()
2463
2464        _UnaryExpression.__init__(self, s, operator=operators.exists, type_=sqltypes.Boolean)
2465
2466    def select(self, whereclause=None, **params):
2467        return select([self], whereclause, **params)
2468
2469    def correlate(self, fromclause):
2470        e = self._clone()
2471        e.element = self.element.correlate(fromclause).self_group()
2472        return e
2473
2474    def select_from(self, clause):
2475        """return a new exists() construct with the given expression set as its FROM clause."""
2476
2477        e = self._clone()
2478        e.element = self.element.select_from(clause).self_group()
2479        return e
2480
2481    def where(self, clause):
2482        """return a new exists() construct with the given expression added to its WHERE clause, joined
2483        to the existing clause via AND, if any."""
2484
2485        e = self._clone()
2486        e.element = self.element.where(clause).self_group()
2487        return e
2488
2489class Join(FromClause):
2490    """represent a ``JOIN`` construct between two ``FromClause`` elements.
2491
2492    The public constructor function for ``Join`` is the module-level
2493    ``join()`` function, as well as the ``join()`` method available
2494    off all ``FromClause`` subclasses.
2495
2496    """
2497    __visit_name__ = 'join'
2498
2499    def __init__(self, left, right, onclause=None, isouter=False):
2500        self.left = _literal_as_text(left)
2501        self.right = _literal_as_text(right).self_group()
2502
2503        if onclause is None:
2504            self.onclause = self._match_primaries(self.left, self.right)
2505        else:
2506            self.onclause = onclause
2507
2508        self.isouter = isouter
2509        self.__folded_equivalents = None
2510
2511    @property
2512    def description(self):
2513        return "Join object on %s(%d) and %s(%d)" % (
2514            self.left.description,
2515            id(self.left),
2516            self.right.description,
2517            id(self.right))
2518
2519    def is_derived_from(self, fromclause):
2520        return fromclause is self or self.left.is_derived_from(fromclause) or self.right.is_derived_from(fromclause)
2521
2522    def self_group(self, against=None):
2523        return _FromGrouping(self)
2524
2525    def _populate_column_collection(self):
2526        columns = [c for c in self.left.columns] + [c for c in self.right.columns]
2527
2528        global sql_util
2529        if not sql_util:
2530            from sqlalchemy.sql import util as sql_util
2531        self._primary_key.extend(sql_util.reduce_columns(
2532                (c for c in columns if c.primary_key), self.onclause))
2533        self._columns.update((col._label, col) for col in columns)
2534        self._foreign_keys.update(itertools.chain(*[col.foreign_keys for col in columns]))
2535
2536    def _copy_internals(self, clone=_clone):
2537        self._reset_exported()
2538        self.left = clone(self.left)
2539        self.right = clone(self.right)
2540        self.onclause = clone(self.onclause)
2541        self.__folded_equivalents = None
2542
2543    def get_children(self, **kwargs):
2544        return self.left, self.right, self.onclause
2545
2546    def _match_primaries(self, primary, secondary):
2547        global sql_util
2548        if not sql_util:
2549            from sqlalchemy.sql import util as sql_util
2550        return sql_util.join_condition(primary, secondary)
2551
2552    def select(self, whereclause=None, fold_equivalents=False, **kwargs):
2553        """Create a :class:`Select` from this :class:`Join`.
2554
2555        :param whereclause: the WHERE criterion that will be sent to
2556          the :func:`select()` function
2557
2558        :param fold_equivalents: based on the join criterion of this
2559          :class:`Join`, do not include
2560          repeat column names in the column list of the resulting
2561          select, for columns that are calculated to be "equivalent"
2562          based on the join criterion of this :class:`Join`. This will
2563          recursively apply to any joins directly nested by this one
2564          as well.  This flag is specific to a particular use case
2565          by the ORM and will be deprecated in 0.6.
2566
2567        :param \**kwargs: all other kwargs are sent to the
2568          underlying :func:`select()` function.
2569
2570        """
2571        if fold_equivalents:
2572            global sql_util
2573            if not sql_util:
2574                from sqlalchemy.sql import util as sql_util
2575            collist = sql_util.folded_equivalents(self)
2576        else:
2577            collist = [self.left, self.right]
2578
2579        return select(collist, whereclause, from_obj=[self], **kwargs)
2580
2581    @property
2582    def bind(self):
2583        return self.left.bind or self.right.bind
2584
2585    def alias(self, name=None):
2586        """Create a ``Select`` out of this ``Join`` clause and return an ``Alias`` of it.
2587
2588        The ``Select`` is not correlating.
2589
2590        """
2591        return self.select(use_labels=True, correlate=False).alias(name)
2592
2593    @property
2594    def _hide_froms(self):
2595        return itertools.chain(*[_from_objects(x.left, x.right) for x in self._cloned_set])
2596
2597    @property
2598    def _from_objects(self):
2599        return [self] + \
2600                self.onclause._from_objects + \
2601                self.left._from_objects + \
2602                self.right._from_objects
2603
2604class Alias(FromClause):
2605    """Represents an table or selectable alias (AS).
2606
2607    Represents an alias, as typically applied to any table or
2608    sub-select within a SQL statement using the ``AS`` keyword (or
2609    without the keyword on certain databases such as Oracle).
2610
2611    This object is constructed from the ``alias()`` module level
2612    function as well as the ``alias()`` method available on all
2613    ``FromClause`` subclasses.
2614
2615    """
2616
2617    __visit_name__ = 'alias'
2618    named_with_column = True
2619
2620    def __init__(self, selectable, alias=None):
2621        baseselectable = selectable
2622        while isinstance(baseselectable, Alias):
2623            baseselectable = baseselectable.element
2624        self.original = baseselectable
2625        self.supports_execution = baseselectable.supports_execution
2626        if self.supports_execution:
2627            self._autocommit = baseselectable._autocommit
2628        self.element = selectable
2629        if alias is None:
2630            if self.original.named_with_column:
2631                alias = getattr(self.original, 'name', None)
2632            alias = _generated_label('%%(%d %s)s' % (id(self), alias or 'anon'))
2633        self.name = alias
2634
2635    @property
2636    def description(self):
2637        return self.name.encode('ascii', 'backslashreplace')
2638
2639    def as_scalar(self):
2640        try:
2641            return self.element.as_scalar()
2642        except AttributeError:
2643            raise AttributeError("Element %s does not support 'as_scalar()'" % self.element)
2644       
2645    def is_derived_from(self, fromclause):
2646        if fromclause in self._cloned_set:
2647            return True
2648        return self.element.is_derived_from(fromclause)
2649
2650    def _populate_column_collection(self):
2651        for col in self.element.columns:
2652            col._make_proxy(self)
2653
2654    def _copy_internals(self, clone=_clone):
2655        self._reset_exported()
2656        self.element = _clone(self.element)
2657        baseselectable = self.element
2658        while isinstance(baseselectable, Alias):
2659            baseselectable = baseselectable.selectable
2660        self.original = baseselectable
2661
2662    def get_children(self, column_collections=True, aliased_selectables=True, **kwargs):
2663        if column_collections:
2664            for c in self.c:
2665                yield c
2666        if aliased_selectables:
2667            yield self.element
2668
2669    @property
2670    def _from_objects(self):
2671        return [self]
2672
2673    @property
2674    def bind(self):
2675        return self.element.bind
2676
2677class _Grouping(ColumnElement):
2678    """Represent a grouping within a column expression"""
2679
2680    __visit_name__ = 'grouping'
2681
2682    def __init__(self, element):
2683        self.element = element
2684        self.type = getattr(element, 'type', None)
2685
2686    @property
2687    def key(self):
2688        return self.element.key
2689
2690    @property
2691    def _label(self):
2692        return getattr(self.element, '_label', None) or self.anon_label
2693
2694    def _copy_internals(self, clone=_clone):
2695        self.element = clone(self.element)
2696
2697    def get_children(self, **kwargs):
2698        return self.element,
2699
2700    @property
2701    def _from_objects(self):
2702        return self.element._from_objects
2703
2704    def __getattr__(self, attr):
2705        return getattr(self.element, attr)
2706
2707    def __getstate__(self):
2708        return {'element':self.element, 'type':self.type}
2709
2710    def __setstate__(self, state):
2711        self.element = state['element']
2712        self.type = state['type']
2713
2714class _FromGrouping(FromClause):
2715    """Represent a grouping of a FROM clause"""
2716    __visit_name__ = 'grouping'
2717
2718    def __init__(self, element):
2719        self.element = element
2720
2721    @property
2722    def columns(self):
2723        return self.element.columns
2724
2725    @property
2726    def _hide_froms(self):
2727        return self.element._hide_froms
2728
2729    def get_children(self, **kwargs):
2730        return self.element,
2731
2732    def _copy_internals(self, clone=_clone):
2733        self.element = clone(self.element)
2734
2735    @property
2736    def _from_objects(self):
2737        return self.element._from_objects
2738
2739    def __getattr__(self, attr):
2740        return getattr(self.element, attr)
2741
2742    def __getstate__(self):
2743        return {'element':self.element}
2744
2745    def __setstate__(self, state):
2746        self.element = state['element']
2747
2748class _Label(ColumnElement):
2749    """Represents a column label (AS).
2750
2751    Represent a label, as typically applied to any column-level
2752    element using the ``AS`` sql keyword.
2753
2754    This object is constructed from the ``label()`` module level
2755    function as well as the ``label()`` method available on all
2756    ``ColumnElement`` subclasses.
2757
2758    """
2759
2760    __visit_name__ = 'label'
2761
2762    def __init__(self, name, element, type_=None):
2763        while isinstance(element, _Label):
2764            element = element.element
2765        self.name = self.key = self._label = name or _generated_label("%%(%d %s)s" % (id(self), getattr(element, 'name', 'anon')))
2766        self._element = element
2767        self._type = type_
2768        self.quote = element.quote
2769
2770    @util.memoized_property
2771    def type(self):
2772        return sqltypes.to_instance(self._type or getattr(self._element, 'type', None))
2773
2774    @util.memoized_property
2775    def element(self):
2776        return self._element.self_group(against=operators.as_)
2777
2778    def _proxy_attr(name):
2779        get = attrgetter(name)
2780        def attr(self):
2781            return get(self.element)
2782        return property(attr)
2783
2784    proxies = _proxy_attr('proxies')
2785    base_columns = _proxy_attr('base_columns')
2786    proxy_set = _proxy_attr('proxy_set')
2787    primary_key = _proxy_attr('primary_key')
2788    foreign_keys = _proxy_attr('foreign_keys')
2789
2790    def get_children(self, **kwargs):
2791        return self.element,
2792
2793    def _copy_internals(self, clone=_clone):
2794        self.element = clone(self.element)
2795
2796    @property
2797    def _from_objects(self):
2798        return self.element._from_objects
2799
2800    def _make_proxy(self, selectable, name = None):
2801        if isinstance(self.element, (Selectable, ColumnElement)):
2802            e = self.element._make_proxy(selectable, name=self.name)
2803        else:
2804            e = column(self.name)._make_proxy(selectable=selectable)
2805        e.proxies.append(self)
2806        return e
2807
2808class ColumnClause(_Immutable, ColumnElement):
2809    """Represents a generic column expression from any textual string.
2810
2811    This includes columns associated with tables, aliases and select
2812    statements, but also any arbitrary text.  May or may not be bound
2813    to an underlying ``Selectable``.  ``ColumnClause`` is usually
2814    created publically via the ``column()`` function or the
2815    ``literal_column()`` function.
2816
2817    text
2818      the text of the element.
2819
2820    selectable
2821      parent selectable.
2822
2823    type
2824      ``TypeEngine`` object which can associate this ``ColumnClause``
2825      with a type.
2826
2827    is_literal
2828      if True, the ``ColumnClause`` is assumed to be an exact
2829      expression that will be delivered to the output with no quoting
2830      rules applied regardless of case sensitive settings.  the
2831      ``literal_column()`` function is usually used to create such a
2832      ``ColumnClause``.
2833
2834    """
2835    __visit_name__ = 'column'
2836
2837    def __init__(self, text, selectable=None, type_=None, is_literal=False):
2838        self.key = self.name = text
2839        self.table = selectable
2840        self.type = sqltypes.to_instance(type_)
2841        self.is_literal = is_literal
2842
2843    @util.memoized_property
2844    def description(self):
2845        return self.name.encode('ascii', 'backslashreplace')
2846
2847    @util.memoized_property
2848    def _label(self):
2849        if self.is_literal:
2850            return None
2851
2852        elif self.table and self.table.named_with_column:
2853            if getattr(self.table, 'schema', None):
2854                label = self.table.schema + "_" + \
2855                            _escape_for_generated(self.table.name) + "_" + \
2856                            _escape_for_generated(self.name)
2857            else:
2858                label = _escape_for_generated(self.table.name) + "_" + \
2859                            _escape_for_generated(self.name)
2860
2861            if label in self.table.c:
2862                # TODO: coverage does not seem to be present for this
2863                _label = label
2864                counter = 1
2865                while _label in self.table.c:
2866                    _label = label + "_" + str(counter)
2867                    counter += 1
2868                label = _label
2869            return _generated_label(label)
2870
2871        else:
2872            return self.name
2873
2874    def label(self, name):
2875        if name is None:
2876            return self
2877        else:
2878            return super(ColumnClause, self).label(name)
2879
2880    @property
2881    def _from_objects(self):
2882        if self.table:
2883            return [self.table]
2884        else:
2885            return []
2886
2887    def _bind_param(self, obj):
2888        return _BindParamClause(self.name, obj, type_=self.type, unique=True)
2889
2890    def _make_proxy(self, selectable, name=None, attach=True):
2891        # propagate the "is_literal" flag only if we are keeping our name,
2892        # otherwise its considered to be a label
2893        is_literal = self.is_literal and (name is None or name == self.name)
2894        c = ColumnClause(name or self.name, selectable=selectable, type_=self.type, is_literal=is_literal)
2895        c.proxies = [self]
2896        if attach:
2897            selectable.columns[c.name] = c
2898        return c
2899
2900    def _compare_type(self, obj):
2901        return self.type
2902
2903class TableClause(_Immutable, FromClause):
2904    """Represents a "table" construct.
2905
2906    Note that this represents tables only as another syntactical
2907    construct within SQL expressions; it does not provide schema-level
2908    functionality.
2909
2910    """
2911
2912    __visit_name__ = 'table'
2913
2914    named_with_column = True
2915
2916    def __init__(self, name, *columns):
2917        super(TableClause, self).__init__()
2918        self.name = self.fullname = name
2919        self._columns = ColumnCollection()
2920        self._primary_key = ColumnSet()
2921        self._foreign_keys = set()
2922        for c in columns:
2923            self.append_column(c)
2924
2925    def _export_columns(self):
2926        raise NotImplementedError()
2927
2928    @util.memoized_property
2929    def description(self):
2930        return self.name.encode('ascii', 'backslashreplace')
2931
2932    def append_column(self, c):
2933        self._columns[c.name] = c
2934        c.table = self
2935
2936    def get_children(self, column_collections=True, **kwargs):
2937        if column_collections:
2938            return [c for c in self.c]
2939        else:
2940            return []
2941
2942    def count(self, whereclause=None, **params):
2943        if self.primary_key:
2944            col = list(self.primary_key)[0]
2945        else:
2946            col = list(self.columns)[0]
2947        return select([func.count(col).label('tbl_row_count')], whereclause, from_obj=[self], **params)
2948
2949    def insert(self, values=None, inline=False, **kwargs):
2950        """Generate an :func:`~sqlalchemy.sql.expression.insert()` construct."""
2951
2952        return insert(self, values=values, inline=inline, **kwargs)
2953
2954    def update(self, whereclause=None, values=None, inline=False, **kwargs):
2955        """Generate an :func:`~sqlalchemy.sql.expression.update()` construct."""
2956
2957        return update(self, whereclause=whereclause, values=values, inline=inline, **kwargs)
2958
2959    def delete(self, whereclause=None, **kwargs):
2960        """Generate a :func:`~sqlalchemy.sql.expression.delete()` construct."""
2961
2962        return delete(self, whereclause, **kwargs)
2963
2964    @property
2965    def _from_objects(self):
2966        return [self]
2967
2968@util.decorator
2969def _generative(fn, *args, **kw):
2970    """Mark a method as generative."""
2971
2972    self = args[0]._generate()
2973    fn(self, *args[1:], **kw)
2974    return self
2975
2976class _SelectBaseMixin(object):
2977    """Base class for ``Select`` and ``CompoundSelects``."""
2978
2979    supports_execution = True
2980
2981    def __init__(self,
2982            use_labels=False,
2983            for_update=False,
2984            limit=None,
2985            offset=None,
2986            order_by=None,
2987            group_by=None,
2988            bind=None,
2989            autocommit=False):
2990        self.use_labels = use_labels
2991        self.for_update = for_update
2992        self._autocommit = autocommit
2993        self._limit = limit
2994        self._offset = offset
2995        self._bind = bind
2996
2997        self._order_by_clause = ClauseList(*util.to_list(order_by) or [])
2998        self._group_by_clause = ClauseList(*util.to_list(group_by) or [])
2999
3000    def as_scalar(self):
3001        """return a 'scalar' representation of this selectable, which can be used
3002        as a column expression.
3003
3004        Typically, a select statement which has only one column in its columns clause
3005        is eligible to be used as a scalar expression.
3006
3007        The returned object is an instance of :class:`~sqlalchemy.sql.expression._ScalarSelect`.
3008
3009        """
3010        return _ScalarSelect(self)
3011
3012    @_generative
3013    def apply_labels(self):
3014        """return a new selectable with the 'use_labels' flag set to True.
3015
3016        This will result in column expressions being generated using labels against their table
3017        name, such as "SELECT somecolumn AS tablename_somecolumn".  This allows selectables which
3018        contain multiple FROM clauses to produce a unique set of column names regardless of name conflicts
3019        among the individual FROM clauses.
3020
3021        """
3022        self.use_labels = True
3023
3024    def label(self, name):
3025        """return a 'scalar' representation of this selectable, embedded as a subquery
3026        with a label.
3027
3028        See also ``as_scalar()``.
3029
3030        """
3031        return self.as_scalar().label(name)
3032
3033    @_generative
3034    def autocommit(self):
3035        """return a new selectable with the 'autocommit' flag set to True."""
3036
3037        self._autocommit = True
3038
3039    def _generate(self):
3040        s = self.__class__.__new__(self.__class__)
3041        s.__dict__ = self.__dict__.copy()
3042        s._reset_exported()
3043        return s
3044
3045    @_generative
3046    def limit(self, limit):
3047        """return a new selectable with the given LIMIT criterion applied."""
3048
3049        self._limit = limit
3050
3051    @_generative
3052    def offset(self, offset):
3053        """return a new selectable with the given OFFSET criterion applied."""
3054
3055        self._offset = offset
3056
3057    @_generative
3058    def order_by(self, *clauses):
3059        """return a new selectable with the given list of ORDER BY criterion applied.
3060
3061        The criterion will be appended to any pre-existing ORDER BY criterion.
3062
3063        """
3064        self.append_order_by(*clauses)
3065
3066    @_generative
3067    def group_by(self, *clauses):
3068        """return a new selectable with the given list of GROUP BY criterion applied.
3069
3070        The criterion will be appended to any pre-existing GROUP BY criterion.
3071
3072        """
3073        self.append_group_by(*clauses)
3074
3075    def append_order_by(self, *clauses):
3076        """Append the given ORDER BY criterion applied to this selectable.
3077
3078        The criterion will be appended to any pre-existing ORDER BY criterion.
3079
3080        """
3081        if len(clauses) == 1 and clauses[0] is None:
3082            self._order_by_clause = ClauseList()
3083        else:
3084            if getattr(self, '_order_by_clause', None):
3085                clauses = list(self._order_by_clause) + list(clauses)
3086            self._order_by_clause = ClauseList(*clauses)
3087
3088    def append_group_by(self, *clauses):
3089        """Append the given GROUP BY criterion applied to this selectable.
3090
3091        The criterion will be appended to any pre-existing GROUP BY criterion.
3092
3093        """
3094        if len(clauses) == 1 and clauses[0] is None:
3095            self._group_by_clause = ClauseList()
3096        else:
3097            if getattr(self, '_group_by_clause', None):
3098                clauses = list(self._group_by_clause) + list(clauses)
3099            self._group_by_clause = ClauseList(*clauses)
3100
3101    @property
3102    def _from_objects(self):
3103        return [self]
3104
3105
3106class _ScalarSelect(_Grouping):
3107    _from_objects = []
3108
3109    def __init__(self, element):
3110        self.element = element
3111        cols = list(element.c)
3112        if len(cols) != 1:
3113            raise exc.InvalidRequestError("Scalar select can only be created "
3114                    "from a Select object that has exactly one column expression.")
3115        self.type = cols[0].type
3116
3117    @property
3118    def columns(self):
3119        raise exc.InvalidRequestError("Scalar Select expression has no columns; "
3120                    "use this object directly within a column-level expression.")
3121    c  = columns
3122
3123    def self_group(self, **kwargs):
3124        return self
3125
3126    def _make_proxy(self, selectable, name):
3127        return list(self.inner_columns)[0]._make_proxy(selectable, name)
3128
3129class CompoundSelect(_SelectBaseMixin, FromClause):
3130    """Forms the basis of ``UNION``, ``UNION ALL``, and other SELECT-based set operations."""
3131
3132    __visit_name__ = 'compound_select'
3133
3134    def __init__(self, keyword, *selects, **kwargs):
3135        self._should_correlate = kwargs.pop('correlate', False)
3136        self.keyword = keyword
3137        self.selects = []
3138
3139        numcols = None
3140
3141        # some DBs do not like ORDER BY in the inner queries of a UNION, etc.
3142        for n, s in enumerate(selects):
3143            s = _clause_element_as_expr(s)
3144           
3145            if not numcols:
3146                numcols = len(s.c)
3147            elif len(s.c) != numcols:
3148                raise exc.ArgumentError(
3149                        "All selectables passed to CompoundSelect must "
3150                        "have identical numbers of columns; select #%d has %d columns, select #%d has %d" %
3151                        (1, len(self.selects[0].c), n+1, len(s.c))
3152                )
3153
3154            # unions group from left to right, so don't group first select
3155            if n:
3156                self.selects.append(s.self_group(self))
3157            else:
3158                self.selects.append(s)
3159
3160        _SelectBaseMixin.__init__(self, **kwargs)
3161
3162    def self_group(self, against=None):
3163        return _FromGrouping(self)
3164
3165    def is_derived_from(self, fromclause):
3166        for s in self.selects:
3167            if s.is_derived_from(fromclause):
3168                return True
3169        return False
3170
3171    def _populate_column_collection(self):
3172        for cols in zip(*[s.c for s in self.selects]):
3173            # this is a slightly hacky thing - the union exports a column that
3174            # resembles just that of the *first* selectable.  to get at a "composite" column,
3175            # particularly foreign keys, you have to dig through the proxies collection
3176            # which we generate below.  We may want to improve upon this,
3177            # such as perhaps _make_proxy can accept a list of other columns that
3178            # are "shared" - schema.column can then copy all the ForeignKeys in.
3179            # this would allow the union() to have all those fks too.
3180            proxy = cols[0]._make_proxy(
3181                            self, name=self.use_labels and cols[0]._label or None)
3182           
3183            # hand-construct the "proxies" collection to include all derived columns
3184            # place a 'weight' annotation corresponding to how low in the list of
3185            # select()s the column occurs, so that the corresponding_column() operation
3186            # can resolve conflicts
3187            proxy.proxies = [c._annotate({'weight':i + 1}) for i, c in enumerate(cols)]
3188           
3189    def _copy_internals(self, clone=_clone):
3190        self._reset_exported()
3191        self.selects = [clone(s) for s in self.selects]
3192        if hasattr(self, '_col_map'):
3193            del self._col_map
3194        for attr in ('_order_by_clause', '_group_by_clause'):
3195            if getattr(self, attr) is not None:
3196                setattr(self, attr, clone(getattr(self, attr)))
3197
3198    def get_children(self, column_collections=True, **kwargs):
3199        return (column_collections and list(self.c) or []) + \
3200            [self._order_by_clause, self._group_by_clause] + list(self.selects)
3201
3202    def bind(self):
3203        if self._bind:
3204            return self._bind
3205        for s in self.selects:
3206            e = s.bind
3207            if e:
3208                return e
3209        else:
3210            return None
3211    def _set_bind(self, bind):
3212        self._bind = bind
3213    bind = property(bind, _set_bind)
3214
3215class Select(_SelectBaseMixin, FromClause):
3216    """Represents a ``SELECT`` statement.
3217
3218    Select statements support appendable clauses, as well as the
3219    ability to execute themselves and return a result set.
3220
3221    """
3222
3223    __visit_name__ = 'select'
3224
3225    def __init__(self, columns, whereclause=None, from_obj=None, distinct=False, having=None, correlate=True, prefixes=None, **kwargs):
3226        """Construct a Select object.
3227
3228        The public constructor for Select is the
3229        :func:`~sqlalchemy.sql.expression.select` function; see that function for
3230        argument descriptions.
3231
3232        Additional generative and mutator methods are available on the
3233        :class:`~sqlalchemy.sql.expression._SelectBaseMixin` superclass.
3234
3235        """
3236        self._should_correlate = correlate
3237        self._distinct = distinct
3238
3239        self._correlate = set()
3240        self._froms = util.OrderedSet()
3241
3242        if columns:
3243            self._raw_columns = [
3244                isinstance(c, _ScalarSelect) and c.self_group(against=operators.comma_op) or c
3245                for c in
3246                [_literal_as_column(c) for c in columns]
3247            ]
3248
3249            self._froms.update(_from_objects(*self._raw_columns))
3250        else:
3251            self._raw_columns = []
3252
3253        if whereclause:
3254            self._whereclause = _literal_as_text(whereclause)
3255            self._froms.update(_from_objects(self._whereclause))
3256        else:
3257            self._whereclause = None
3258
3259        if from_obj:
3260            self._froms.update(
3261                _is_literal(f) and _TextClause(f) or f
3262                for f in util.to_list(from_obj))
3263
3264        if having:
3265            self._having = _literal_as_text(having)
3266        else:
3267            self._having = None
3268
3269        if prefixes:
3270            self._prefixes = [_literal_as_text(p) for p in prefixes]
3271        else:
3272            self._prefixes = []
3273
3274        _SelectBaseMixin.__init__(self, **kwargs)
3275
3276    def _get_display_froms(self, existing_froms=None):
3277        """Return the full list of 'from' clauses to be displayed.
3278
3279        Takes into account a set of existing froms which may be
3280        rendered in the FROM clause of enclosing selects; this Select
3281        may want to leave those absent if it is automatically
3282        correlating.
3283
3284        """
3285        froms = self._froms
3286
3287        toremove = itertools.chain(*[f._hide_froms for f in froms])
3288        if toremove:
3289            froms = froms.difference(toremove)
3290
3291        if len(froms) > 1 or self._correlate:
3292            if self._correlate:
3293                froms = froms.difference(_cloned_intersection(froms, self._correlate))
3294
3295            if self._should_correlate and existing_froms:
3296                froms = froms.difference(_cloned_intersection(froms, existing_froms))
3297
3298                if not len(froms):
3299                    raise exc.InvalidRequestError(
3300                            "Select statement '%s' returned no FROM clauses "
3301                            "due to auto-correlation; specify correlate(<tables>) "
3302                            "to control correlation manually." % self)
3303
3304        return froms
3305
3306    @property
3307    def froms(self):
3308        """Return the displayed list of FromClause elements."""
3309
3310        return self._get_display_froms()
3311
3312    @property
3313    def type(self):
3314        raise exc.InvalidRequestError("Select objects don't have a type.  "
3315                    "Call as_scalar() on this Select object "
3316                    "to return a 'scalar' version of this Select.")
3317
3318    @util.memoized_instancemethod
3319    def locate_all_froms(self):
3320        """return a Set of all FromClause elements referenced by this Select.
3321
3322        This set is a superset of that returned by the ``froms`` property, which
3323        is specifically for those FromClause elements that would actually be rendered.
3324
3325        """
3326        return self._froms.union(_from_objects(*list(self._froms)))
3327
3328    @property
3329    def inner_columns(self):
3330        """an iterator of all ColumnElement expressions which would
3331        be rendered into the columns clause of the resulting SELECT statement.
3332
3333        """
3334
3335        return itertools.chain(*[c._select_iterable for c in self._raw_columns])
3336
3337    def is_derived_from(self, fromclause):
3338        if self in fromclause._cloned_set:
3339            return True
3340
3341        for f in self.locate_all_froms():
3342            if f.is_derived_from(fromclause):
3343                return True
3344        return False
3345
3346    def _copy_internals(self, clone=_clone):
3347        self._reset_exported()
3348        from_cloned = dict((f, clone(f))
3349                           for f in self._froms.union(self._correlate))
3350        self._froms = set(from_cloned[f] for f in self._froms)
3351        self._correlate = set(from_cloned[f] for f in self._correlate)
3352        self._raw_columns = [clone(c) for c in self._raw_columns]
3353        for attr in ('_whereclause', '_having', '_order_by_clause', '_group_by_clause'):
3354            if getattr(self, attr) is not None:
3355                setattr(self, attr, clone(getattr(self, attr)))
3356
3357    def get_children(self, column_collections=True, **kwargs):
3358        """return child elements as per the ClauseElement specification."""
3359
3360        return (column_collections and list(self.columns) or []) + \
3361            self._raw_columns + list(self._froms) + \
3362            [x for x in (self._whereclause, self._having, self._order_by_clause, self._group_by_clause) if x is not None]
3363
3364    @_generative
3365    def column(self, column):
3366        """return a new select() construct with the given column expression added to its columns clause."""
3367
3368        column = _literal_as_column(column)
3369
3370        if isinstance(column, _ScalarSelect):
3371            column = column.self_group(against=operators.comma_op)
3372
3373        self._raw_columns = self._raw_columns + [column]
3374        self._froms = self._froms.union(_from_objects(column))
3375
3376    @_generative
3377    def with_only_columns(self, columns):
3378        """return a new select() construct with its columns clause replaced with the given columns."""
3379
3380        self._raw_columns = [
3381                isinstance(c, _ScalarSelect) and c.self_group(against=operators.comma_op) or c
3382                for c in
3383                [_literal_as_column(c) for c in columns]
3384            ]
3385
3386    @_generative
3387    def where(self, whereclause):
3388        """return a new select() construct with the given expression added to its WHERE clause, joined
3389        to the existing clause via AND, if any."""
3390
3391        self.append_whereclause(whereclause)
3392
3393    @_generative
3394    def having(self, having):
3395        """return a new select() construct with the given expression added to its HAVING clause, joined
3396        to the existing clause via AND, if any."""
3397
3398        self.append_having(having)
3399
3400    @_generative
3401    def distinct(self):
3402        """return a new select() construct which will apply DISTINCT to its columns clause."""
3403
3404        self._distinct = True
3405
3406    @_generative
3407    def prefix_with(self, clause):
3408        """return a new select() construct which will apply the given expression to the start of its
3409        columns clause, not using any commas."""
3410
3411        clause = _literal_as_text(clause)
3412        self._prefixes = self._prefixes + [clause]
3413
3414    @_generative
3415    def select_from(self, fromclause):
3416        """return a new select() construct with the given FROM expression applied to its list of
3417        FROM objects."""
3418
3419        fromclause = _literal_as_text(fromclause)
3420        self._froms = self._froms.union([fromclause])
3421
3422    @_generative
3423    def correlate(self, *fromclauses):
3424        """return a new select() construct which will correlate the given FROM clauses to that
3425        of an enclosing select(), if a match is found.
3426
3427        By "match", the given fromclause must be present in this select's list of FROM objects
3428        and also present in an enclosing select's list of FROM objects.
3429
3430        Calling this method turns off the select's default behavior of "auto-correlation".  Normally,
3431        select() auto-correlates all of its FROM clauses to those of an embedded select when
3432        compiled.
3433
3434        If the fromclause is None, correlation is disabled for the returned select().
3435
3436        """
3437        self._should_correlate = False
3438        if fromclauses == (None,):
3439            self._correlate = set()
3440        else:
3441            self._correlate = self._correlate.union(fromclauses)
3442
3443    def append_correlation(self, fromclause):
3444        """append the given correlation expression to this select() construct."""
3445
3446        self._should_correlate = False
3447        self._correlate = self._correlate.union([fromclause])
3448
3449    def append_column(self, column):
3450        """append the given column expression to the columns clause of this select() construct."""
3451
3452        column = _literal_as_column(column)
3453
3454        if isinstance(column, _ScalarSelect):
3455            column = column.self_group(against=operators.comma_op)
3456
3457        self._raw_columns = self._raw_columns + [column]
3458        self._froms = self._froms.union(_from_objects(column))
3459        self._reset_exported()
3460
3461    def append_prefix(self, clause):
3462        """append the given columns clause prefix expression to this select() construct."""
3463
3464        clause = _literal_as_text(clause)
3465        self._prefixes = self._prefixes.union([clause])
3466
3467    def append_whereclause(self, whereclause):
3468        """append the given expression to this select() construct's WHERE criterion.
3469
3470        The expression will be joined to existing WHERE criterion via AND.
3471
3472        """
3473        whereclause = _literal_as_text(whereclause)
3474        self._froms = self._froms.union(_from_objects(whereclause))
3475
3476        if self._whereclause is not None:
3477            self._whereclause = and_(self._whereclause, whereclause)
3478        else:
3479            self._whereclause = whereclause
3480
3481    def append_having(self, having):
3482        """append the given expression to this select() construct's HAVING criterion.
3483
3484        The expression will be joined to existing HAVING criterion via AND.
3485
3486        """
3487        if self._having is not None:
3488            self._having = and_(self._having, _literal_as_text(having))
3489        else:
3490            self._having = _literal_as_text(having)
3491
3492    def append_from(self, fromclause):
3493        """append the given FromClause expression to this select() construct's FROM clause.
3494
3495        """
3496        if _is_literal(fromclause):
3497            fromclause = _TextClause(fromclause)
3498
3499        self._froms = self._froms.union([fromclause])
3500
3501    def __exportable_columns(self):
3502        for column in self._raw_columns:
3503            if isinstance(column, Selectable):
3504                for co in column.columns:
3505                    yield co
3506            elif isinstance(column, ColumnElement):
3507                yield column
3508            else:
3509                continue
3510
3511    def _populate_column_collection(self):
3512        for c in self.__exportable_columns():
3513            c._make_proxy(self, name=self.use_labels and c._label or None)
3514
3515    def self_group(self, against=None):
3516        """return a 'grouping' construct as per the ClauseElement specification.
3517
3518        This produces an element that can be embedded in an expression.  Note that
3519        this method is called automatically as needed when constructing expressions.
3520
3521        """
3522        if isinstance(against, CompoundSelect):
3523            return self
3524        return _FromGrouping(self)
3525
3526    def union(self, other, **kwargs):
3527        """return a SQL UNION of this select() construct against the given selectable."""
3528
3529        return union(self, other, **kwargs)
3530
3531    def union_all(self, other, **kwargs):
3532        """return a SQL UNION ALL of this select() construct against the given selectable."""
3533
3534        return union_all(self, other, **kwargs)
3535
3536    def except_(self, other, **kwargs):
3537        """return a SQL EXCEPT of this select() construct against the given selectable."""
3538
3539        return except_(self, other, **kwargs)
3540
3541    def except_all(self, other, **kwargs):
3542        """return a SQL EXCEPT ALL of this select() construct against the given selectable."""
3543
3544        return except_all(self, other, **kwargs)
3545
3546    def intersect(self, other, **kwargs):
3547        """return a SQL INTERSECT of this select() construct against the given selectable."""
3548
3549        return intersect(self, other, **kwargs)
3550
3551    def intersect_all(self, other, **kwargs):
3552        """return a SQL INTERSECT ALL of this select() construct against the given selectable."""
3553
3554        return intersect_all(self, other, **kwargs)
3555
3556    def bind(self):
3557        if self._bind:
3558            return self._bind
3559        if not self._froms:
3560            for c in self._raw_columns:
3561                e = c.bind
3562                if e:
3563                    self._bind = e
3564                    return e
3565        else:
3566            e = list(self._froms)[0].bind
3567            if e:
3568                self._bind = e
3569                return e
3570
3571        return None
3572
3573    def _set_bind(self, bind):
3574        self._bind = bind
3575    bind = property(bind, _set_bind)
3576
3577class _UpdateBase(ClauseElement):
3578    """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
3579
3580    __visit_name__ = 'update_base'
3581
3582    supports_execution = True
3583    _autocommit = True
3584
3585    def _generate(self):
3586        s = self.__class__.__new__(self.__class__)
3587        s.__dict__ = self.__dict__.copy()
3588        return s
3589
3590    def _process_colparams(self, parameters):
3591        if isinstance(parameters, (list, tuple)):
3592            pp = {}
3593            for i, c in enumerate(self.table.c):
3594                pp[c.key] = parameters[i]
3595            return pp
3596        else:
3597            return parameters
3598
3599    def params(self, *arg, **kw):
3600        raise NotImplementedError("params() is not supported for INSERT/UPDATE/DELETE statements."
3601            "  To set the values for an INSERT or UPDATE statement, use stmt.values(**parameters).")
3602
3603    def bind(self):
3604        return self._bind or self.table.bind
3605
3606    def _set_bind(self, bind):
3607        self._bind = bind
3608    bind = property(bind, _set_bind)
3609
3610class _ValuesBase(_UpdateBase):
3611
3612    __visit_name__ = 'values_base'
3613
3614    def __init__(self, table, values):
3615        self.table = table
3616        self.parameters = self._process_colparams(values)
3617
3618    @_generative
3619    def values(self, *args, **kwargs):
3620        """specify the VALUES clause for an INSERT statement, or the SET clause for an UPDATE.
3621
3622            \**kwargs
3623                key=<somevalue> arguments
3624
3625            \*args
3626                A single dictionary can be sent as the first positional argument.  This allows
3627                non-string based keys, such as Column objects, to be used.
3628
3629        """
3630        if args:
3631            v = args[0]
3632        else:
3633            v = {}
3634
3635        if self.parameters is None:
3636            self.parameters = self._process_colparams(v)
3637            self.parameters.update(kwargs)
3638        else:
3639            self.parameters = self.parameters.copy()
3640            self.parameters.update(self._process_colparams(v))
3641            self.parameters.update(kwargs)
3642
3643class Insert(_ValuesBase):
3644    """Represent an INSERT construct.
3645
3646    The ``Insert`` object is created using the :func:`insert()` function.
3647
3648    """
3649    __visit_name__ = 'insert'
3650
3651    def __init__(self, table, values=None, inline=False, bind=None, prefixes=None, **kwargs):
3652        _ValuesBase.__init__(self, table, values)
3653        self._bind = bind
3654        self.select = None
3655        self.inline = inline
3656        if prefixes:
3657            self._prefixes = [_literal_as_text(p) for p in prefixes]
3658        else:
3659            self._prefixes = []
3660        self.kwargs = kwargs
3661
3662    def get_children(self, **kwargs):
3663        if self.select is not None:
3664            return self.select,
3665        else:
3666            return ()
3667
3668    def _copy_internals(self, clone=_clone):
3669        # TODO: coverage
3670        self.parameters = self.parameters.copy()
3671
3672    @_generative
3673    def prefix_with(self, clause):
3674        """Add a word or expression between INSERT and INTO. Generative.
3675
3676        If multiple prefixes are supplied, they will be separated with
3677        spaces.
3678
3679        """
3680        clause = _literal_as_text(clause)
3681        self._prefixes = self._prefixes + [clause]
3682
3683class Update(_ValuesBase):
3684    """Represent an Update construct.
3685
3686    The ``Update`` object is created using the :func:`update()` function.
3687
3688    """
3689    __visit_name__ = 'update'
3690
3691    def __init__(self, table, whereclause, values=None, inline=False, bind=None, **kwargs):
3692        _ValuesBase.__init__(self, table, values)
3693        self._bind = bind
3694        if whereclause:
3695            self._whereclause = _literal_as_text(whereclause)
3696        else:
3697            self._whereclause = None
3698        self.inline = inline
3699        self.kwargs = kwargs
3700
3701    def get_children(self, **kwargs):
3702        if self._whereclause is not None:
3703            return self._whereclause,
3704        else:
3705            return ()
3706
3707    def _copy_internals(self, clone=_clone):
3708        # TODO: coverage
3709        self._whereclause = clone(self._whereclause)
3710        self.parameters = self.parameters.copy()
3711
3712    @_generative
3713    def where(self, whereclause):
3714        """return a new update() construct with the given expression added to its WHERE clause, joined
3715        to the existing clause via AND, if any."""
3716
3717        if self._whereclause is not None:
3718            self._whereclause = and_(self._whereclause, _literal_as_text(whereclause))
3719        else:
3720            self._whereclause = _literal_as_text(whereclause)
3721
3722
3723class Delete(_UpdateBase):
3724    """Represent a DELETE construct.
3725
3726    The ``Delete`` object is created using the :func:`delete()` function.
3727
3728    """
3729
3730    __visit_name__ = 'delete'
3731
3732    def __init__(self, table, whereclause, bind=None, **kwargs):
3733        self._bind = bind
3734        self.table = table
3735        if whereclause:
3736            self._whereclause = _literal_as_text(whereclause)
3737        else:
3738            self._whereclause = None
3739
3740        self.kwargs = kwargs
3741
3742    def get_children(self, **kwargs):
3743        if self._whereclause is not None:
3744            return self._whereclause,
3745        else:
3746            return ()
3747
3748    @_generative
3749    def where(self, whereclause):
3750        """Add the given WHERE clause to a newly returned delete construct."""
3751
3752        if self._whereclause is not None:
3753            self._whereclause = and_(self._whereclause, _literal_as_text(whereclause))
3754        else:
3755            self._whereclause = _literal_as_text(whereclause)
3756
3757    def _copy_internals(self, clone=_clone):
3758        # TODO: coverage
3759        self._whereclause = clone(self._whereclause)
3760
3761class _IdentifiedClause(ClauseElement):
3762    __visit_name__ = 'identified'
3763    supports_execution = True
3764    _autocommit = False
3765    quote = None
3766
3767    def __init__(self, ident):
3768        self.ident = ident
3769
3770class SavepointClause(_IdentifiedClause):
3771    __visit_name__ = 'savepoint'
3772
3773class RollbackToSavepointClause(_IdentifiedClause):
3774    __visit_name__ = 'rollback_to_savepoint'
3775
3776class ReleaseSavepointClause(_IdentifiedClause):
3777    __visit_name__ = 'release_savepoint'
Note: リポジトリブラウザについてのヘルプは TracBrowser を参照してください。