« back to all changes in this revision
Viewing changes to lib/sqlalchemy/sql/expression.py
- browse files at revision 34
- compare with another revision
- download diff
- download tarball
- view history from revision 34
- Committer: Package Import Robot
- Author(s): Piotr Ożarowski, Jakub Wilk, Piotr Ożarowski
- Date: 2013-07-06 20:53:52 UTC
- mfrom: (1.4.23) (16.1.17 experimental)
- Revision ID: package-import@ubuntu.com-20130706205352-ryppl1eto3illd79
Tags: 0.8.2-1
[ Jakub Wilk ]
* Use canonical URIs for Vcs-* fields.
[ Piotr Ożarowski ]
* New upstream release
* Upload to unstable
* Build depend on python3-all instead of -dev, extensions are not built for
Python 3.X
* Use canonical URIs for Vcs-* fields.
[ Piotr Ożarowski ]
* New upstream release
* Upload to unstable
* Build depend on python3-all instead of -dev, extensions are not built for
Python 3.X
- files added:
- README.dialects.rst
- doc/_sources/changelog
- doc/build/changelog
- doc/changelog
- examples/dogpile_caching
- lib/sqlalchemy/ext/declarative
- lib/sqlalchemy/testing
- lib/sqlalchemy/testing/plugin
- lib/sqlalchemy/testing/suite
- test/dialect/mssql
- test/dialect/mysql
- test/dialect/postgresql
- test/ext/declarative
- files removed:
- CHANGES_PRE_06
- examples/beaker_caching
- examples/declarative_reflection
- lib/sqlalchemy/dialects/access
- lib/sqlalchemy/dialects/maxdb
- test/bootstrap
- test/ex
- test/lib
- files modified:
- CHANGES
added
removed
lib/sqlalchemy/sql/expression.py
1
1
# sql/expression.py
2
# Copyright (C) 2005-2012 the SQLAlchemy authors and contributors <see AUTHORS file>
2
# Copyright (C) 2005-2013 the SQLAlchemy authors and contributors <see AUTHORS file>
3
3
#
4
4
# This module is part of SQLAlchemy and is released under
5
5
# the MIT License: http://www.opensource.org/licenses/mit-license.php
26
26
27
27
"""
28
28
29
import itertools, re
29
30
import itertools
31
import re
30
32
from operator import attrgetter
31
33
32
from sqlalchemy import util, exc
33
from sqlalchemy.sql import operators
34
from sqlalchemy.sql.operators import Operators, ColumnOperators
35
from sqlalchemy.sql.visitors import Visitable, cloned_traverse
34
from .. import util, exc, inspection
35
from . import operators
36
from .operators import ColumnOperators
37
from .visitors import Visitable, cloned_traverse
36
38
import operator
37
39
38
40
functions = util.importlater("sqlalchemy.sql", "functions")
48
50
'except_', 'except_all', 'exists', 'extract', 'func', 'modifier',
49
51
'collate', 'insert', 'intersect', 'intersect_all', 'join', 'label',
50
52
'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast',
51
'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery', 'table', 'text',
53
'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery',
54
'table', 'text',
52
55
'tuple_', 'type_coerce', 'union', 'union_all', 'update', ]
53
56
54
57
PARSE_AUTOCOMMIT = util.symbol('PARSE_AUTOCOMMIT')
58
NO_ARG = util.symbol('NO_ARG')
59
55
60
56
61
def nullsfirst(column):
57
62
"""Return a NULLS FIRST ``ORDER BY`` clause element.
65
70
ORDER BY mycol DESC NULLS FIRST
66
71
67
72
"""
68
return _UnaryExpression(column, modifier=operators.nullsfirst_op)
73
return UnaryExpression(column, modifier=operators.nullsfirst_op)
74
69
75
70
76
def nullslast(column):
71
77
"""Return a NULLS LAST ``ORDER BY`` clause element.
79
85
ORDER BY mycol DESC NULLS LAST
80
86
81
87
"""
82
return _UnaryExpression(column, modifier=operators.nullslast_op)
88
return UnaryExpression(column, modifier=operators.nullslast_op)
89
83
90
84
91
def desc(column):
85
92
"""Return a descending ``ORDER BY`` clause element.
93
100
ORDER BY mycol DESC
94
101
95
102
"""
96
return _UnaryExpression(column, modifier=operators.desc_op)
103
return UnaryExpression(column, modifier=operators.desc_op)
104
97
105
98
106
def asc(column):
99
107
"""Return an ascending ``ORDER BY`` clause element.
107
115
ORDER BY mycol ASC
108
116
109
117
"""
110
return _UnaryExpression(column, modifier=operators.asc_op)
118
return UnaryExpression(column, modifier=operators.asc_op)
119
111
120
112
121
def outerjoin(left, right, onclause=None):
113
122
"""Return an ``OUTER JOIN`` clause element.
133
142
"""
134
143
return Join(left, right, onclause, isouter=True)
135
144
145
136
146
def join(left, right, onclause=None, isouter=False):
137
147
"""Return a ``JOIN`` clause element (regular inner join).
138
148
158
168
"""
159
169
return Join(left, right, onclause, isouter)
160
170
171
161
172
def select(columns=None, whereclause=None, from_obj=[], **kwargs):
162
173
"""Returns a ``SELECT`` clause element.
163
174
170
181
string arguments, which will be converted as appropriate into
171
182
either :func:`text()` or :func:`literal_column()` constructs.
172
183
173
See also:
184
.. seealso::
174
185
175
:ref:`coretutorial_selecting` - Core Tutorial description of :func:`.select`.
186
:ref:`coretutorial_selecting` - Core Tutorial description of
187
:func:`.select`.
176
188
177
189
:param columns:
178
190
A list of :class:`.ClauseElement` objects, typically
236
248
``distinct`` is also available via the :meth:`~.Select.distinct`
237
249
generative method.
238
250
239
.. note::
240
241
The ``distinct`` keyword's acceptance of a string
242
argument for usage with MySQL is deprecated. Use
243
the ``prefixes`` argument or :meth:`~.Select.prefix_with`.
244
245
251
:param for_update=False:
246
252
when ``True``, applies ``FOR UPDATE`` to the end of the
247
253
resulting statement.
282
288
a scalar or list of :class:`.ClauseElement` objects which will
283
289
comprise the ``ORDER BY`` clause of the resulting select.
284
290
285
:param prefixes:
286
a list of strings or :class:`.ClauseElement` objects to include
287
directly after the SELECT keyword in the generated statement,
288
for dialect-specific query features. ``prefixes`` is
289
also available via the :meth:`~.Select.prefix_with`
290
generative method.
291
292
291
:param use_labels=False:
293
292
when ``True``, the statement will be generated using labels
294
293
for each column in the columns clause, which qualify each
298
297
collection of the resulting :class:`.Select` object will use these
299
298
names as well for targeting column members.
300
299
301
use_labels is also available via the :meth:`~._SelectBase.apply_labels`
300
use_labels is also available via the :meth:`~.SelectBase.apply_labels`
302
301
generative method.
303
302
304
303
"""
305
304
return Select(columns, whereclause=whereclause, from_obj=from_obj,
306
305
**kwargs)
307
306
307
308
308
def subquery(alias, *args, **kwargs):
309
309
"""Return an :class:`.Alias` object derived
310
310
from a :class:`.Select`.
320
320
"""
321
321
return Select(*args, **kwargs).alias(alias)
322
322
323
323
324
def insert(table, values=None, inline=False, **kwargs):
324
325
"""Represent an ``INSERT`` statement via the :class:`.Insert` SQL
325
326
construct.
326
327
327
Similar functionality is available via the :meth:`~.TableClause.insert` method on
328
Similar functionality is available via the
329
:meth:`~.TableClause.insert` method on
328
330
:class:`~.schema.Table`.
329
331
330
332
331
:param table: The table to be inserted into.
332
333
:param values: A dictionary which specifies the column specifications of
334
the ``INSERT``, and is optional. If left as None, the column
335
specifications are determined from the bind parameters used during the
336
compile phase of the ``INSERT`` statement. If the bind parameters also
337
are None during the compile phase, then the column specifications will be
338
generated from the full list of table columns. Note that the
339
:meth:`~Insert.values()` generative method may also be used for this.
340
341
:param prefixes: A list of modifier keywords to be inserted between INSERT
342
and INTO. Alternatively, the :meth:`~Insert.prefix_with` generative
343
method may be used.
333
:param table: :class:`.TableClause` which is the subject of the insert.
334
335
:param values: collection of values to be inserted; see
336
:meth:`.Insert.values` for a description of allowed formats here.
337
Can be omitted entirely; a :class:`.Insert` construct will also
338
dynamically render the VALUES clause at execution time based on
339
the parameters passed to :meth:`.Connection.execute`.
344
340
345
341
:param inline: if True, SQL defaults will be compiled 'inline' into the
346
342
statement and not pre-executed.
360
356
``INSERT`` statement's table, the statement will be correlated
361
357
against the ``INSERT`` statement.
362
358
363
See also:
359
.. seealso::
364
360
365
361
:ref:`coretutorial_insert_expressions` - SQL Expression Tutorial
366
362
369
365
"""
370
366
return Insert(table, values, inline=inline, **kwargs)
371
367
368
372
369
def update(table, whereclause=None, values=None, inline=False, **kwargs):
373
370
"""Represent an ``UPDATE`` statement via the :class:`.Update` SQL
374
371
construct.
380
377
stmt = update(users).where(users.c.id==5).\\
381
378
values(name='user #5')
382
379
383
Similar functionality is available via the :meth:`~.TableClause.update` method on
380
Similar functionality is available via the
381
:meth:`~.TableClause.update` method on
384
382
:class:`.Table`::
385
383
386
384
466
464
as_scalar()
467
465
)
468
466
469
See also:
467
.. seealso::
470
468
471
469
:ref:`inserts_and_updates` - SQL Expression
472
470
Language Tutorial
480
478
inline=inline,
481
479
**kwargs)
482
480
483
def delete(table, whereclause = None, **kwargs):
481
482
def delete(table, whereclause=None, **kwargs):
484
483
"""Represent a ``DELETE`` statement via the :class:`.Delete` SQL
485
484
construct.
486
485
487
Similar functionality is available via the :meth:`~.TableClause.delete` method on
486
Similar functionality is available via the
487
:meth:`~.TableClause.delete` method on
488
488
:class:`~.schema.Table`.
489
489
490
490
:param table: The table to be updated.
493
493
condition of the ``UPDATE`` statement. Note that the
494
494
:meth:`~Delete.where()` generative method may be used instead.
495
495
496
See also:
496
.. seealso::
497
497
498
498
:ref:`deletes` - SQL Expression Tutorial
499
499
500
500
"""
501
501
return Delete(table, whereclause, **kwargs)
502
502
503
503
504
def and_(*clauses):
504
505
"""Join a list of clauses together using the ``AND`` operator.
505
506
506
The ``&`` operator is also overloaded on all
507
:class:`_CompareMixin` subclasses to produce the
507
The ``&`` operator is also overloaded on all :class:`.ColumnElement`
508
subclasses to produce the
508
509
same result.
509
510
510
511
"""
512
513
return clauses[0]
513
514
return BooleanClauseList(operator=operators.and_, *clauses)
514
515
516
515
517
def or_(*clauses):
516
518
"""Join a list of clauses together using the ``OR`` operator.
517
519
518
520
The ``|`` operator is also overloaded on all
519
:class:`_CompareMixin` subclasses to produce the
521
:class:`.ColumnElement` subclasses to produce the
520
522
same result.
521
523
522
524
"""
524
526
return clauses[0]
525
527
return BooleanClauseList(operator=operators.or_, *clauses)
526
528
529
527
530
def not_(clause):
528
531
"""Return a negation of the given clause, i.e. ``NOT(clause)``.
529
532
530
533
The ``~`` operator is also overloaded on all
531
:class:`_CompareMixin` subclasses to produce the
534
:class:`.ColumnElement` subclasses to produce the
532
535
same result.
533
536
534
537
"""
535
538
return operators.inv(_literal_as_binds(clause))
536
539
540
537
541
def distinct(expr):
538
542
"""Return a ``DISTINCT`` clause.
539
543
547
551
548
552
"""
549
553
expr = _literal_as_binds(expr)
550
return _UnaryExpression(expr, operator=operators.distinct_op, type_=expr.type)
554
return UnaryExpression(expr,
555
operator=operators.distinct_op, type_=expr.type)
556
551
557
552
558
def between(ctest, cleft, cright):
553
559
"""Return a ``BETWEEN`` predicate clause.
555
561
Equivalent of SQL ``clausetest BETWEEN clauseleft AND clauseright``.
556
562
557
563
The :func:`between()` method on all
558
:class:`_CompareMixin` subclasses provides
564
:class:`.ColumnElement` subclasses provides
559
565
similar functionality.
560
566
561
567
"""
615
621
616
622
"""
617
623
618
return _Case(whens, value=value, else_=else_)
624
return Case(whens, value=value, else_=else_)
625
619
626
620
627
def cast(clause, totype, **kwargs):
621
628
"""Return a ``CAST`` function.
631
638
cast(table.c.timestamp, DATE)
632
639
633
640
"""
634
return _Cast(clause, totype, **kwargs)
641
return Cast(clause, totype, **kwargs)
642
635
643
636
644
def extract(field, expr):
637
645
"""Return the clause ``extract(field FROM expr)``."""
638
646
639
return _Extract(field, expr)
647
return Extract(field, expr)
648
640
649
641
650
def collate(expression, collation):
642
651
"""Return the clause ``expression COLLATE collation``.
652
661
"""
653
662
654
663
expr = _literal_as_binds(expression)
655
return _BinaryExpression(
664
return BinaryExpression(
656
665
expr,
657
666
_literal_as_text(collation),
658
667
operators.collate, type_=expr.type)
659
668
669
660
670
def exists(*args, **kwargs):
661
671
"""Return an ``EXISTS`` clause as applied to a :class:`.Select` object.
662
672
674
684
exists().where(table.c.col2==5)
675
685
676
686
"""
677
return _Exists(*args, **kwargs)
687
return Exists(*args, **kwargs)
688
678
689
679
690
def union(*selects, **kwargs):
680
691
"""Return a ``UNION`` of multiple selectables.
695
706
"""
696
707
return CompoundSelect(CompoundSelect.UNION, *selects, **kwargs)
697
708
709
698
710
def union_all(*selects, **kwargs):
699
711
"""Return a ``UNION ALL`` of multiple selectables.
700
712
714
726
"""
715
727
return CompoundSelect(CompoundSelect.UNION_ALL, *selects, **kwargs)
716
728
729
717
730
def except_(*selects, **kwargs):
718
731
"""Return an ``EXCEPT`` of multiple selectables.
719
732
730
743
"""
731
744
return CompoundSelect(CompoundSelect.EXCEPT, *selects, **kwargs)
732
745
746
733
747
def except_all(*selects, **kwargs):
734
748
"""Return an ``EXCEPT ALL`` of multiple selectables.
735
749
746
760
"""
747
761
return CompoundSelect(CompoundSelect.EXCEPT_ALL, *selects, **kwargs)
748
762
763
749
764
def intersect(*selects, **kwargs):
750
765
"""Return an ``INTERSECT`` of multiple selectables.
751
766
762
777
"""
763
778
return CompoundSelect(CompoundSelect.INTERSECT, *selects, **kwargs)
764
779
780
765
781
def intersect_all(*selects, **kwargs):
766
782
"""Return an ``INTERSECT ALL`` of multiple selectables.
767
783
778
794
"""
779
795
return CompoundSelect(CompoundSelect.INTERSECT_ALL, *selects, **kwargs)
780
796
797
781
798
def alias(selectable, name=None):
782
799
"""Return an :class:`.Alias` object.
783
800
820
837
821
838
Literal clauses are created automatically when non- :class:`.ClauseElement`
822
839
objects (such as strings, ints, dates, etc.) are used in a comparison
823
operation with a :class:`_CompareMixin`
824
subclass, such as a :class:`~sqlalchemy.schema.Column` object. Use this function to force the
840
operation with a :class:`.ColumnElement`
841
subclass, such as a :class:`~sqlalchemy.schema.Column` object.
842
Use this function to force the
825
843
generation of a literal clause, which will be created as a
826
:class:`_BindParamClause` with a bound value.
844
:class:`BindParameter` with a bound value.
827
845
828
846
:param value: the value to be bound. Can be any Python object supported by
829
847
the underlying DB-API, or is translatable via the given type argument.
832
850
will provide bind-parameter translation for this literal.
833
851
834
852
"""
835
return _BindParamClause(None, value, type_=type_, unique=True)
853
return BindParameter(None, value, type_=type_, unique=True)
854
836
855
837
856
def tuple_(*expr):
838
857
"""Return a SQL tuple.
852
871
an expression is invoked.
853
872
854
873
"""
855
return _Tuple(*expr)
874
return Tuple(*expr)
875
856
876
857
877
def type_coerce(expr, type_):
858
"""Coerce the given expression into the given type, on the Python side only.
878
"""Coerce the given expression into the given type,
879
on the Python side only.
859
880
860
881
:func:`.type_coerce` is roughly similar to :func:`.cast`, except no
861
882
"CAST" expression is rendered - the given type is only applied towards
890
911
)
891
912
892
913
"""
914
type_ = sqltypes.to_instance(type_)
915
893
916
if hasattr(expr, '__clause_expr__'):
894
917
return type_coerce(expr.__clause_expr__())
895
918
elif isinstance(expr, BindParameter):
919
bp = expr._clone()
920
bp.type = type_
921
return bp
896
922
elif not isinstance(expr, Visitable):
897
923
if expr is None:
898
924
return null()
899
925
else:
900
926
return literal(expr, type_=type_)
901
927
else:
902
return _Label(None, expr, type_=type_)
928
return Label(None, expr, type_=type_)
903
929
904
930
905
931
def label(name, obj):
906
"""Return a :class:`_Label` object for the
932
"""Return a :class:`Label` object for the
907
933
given :class:`.ColumnElement`.
908
934
909
935
A label changes the name of an element in the columns clause of a
919
945
a :class:`.ColumnElement`.
920
946
921
947
"""
922
return _Label(name, obj)
948
return Label(name, obj)
949
923
950
924
951
def column(text, type_=None):
925
952
"""Return a textual column clause, as would be in the columns clause of a
928
955
The object returned is an instance of :class:`.ColumnClause`, which
929
956
represents the "syntactical" portion of the schema-level
930
957
:class:`~sqlalchemy.schema.Column` object. It is often used directly
931
within :func:`~.expression.select` constructs or with lightweight :func:`~.expression.table`
932
constructs.
958
within :func:`~.expression.select` constructs or with lightweight
959
:func:`~.expression.table` constructs.
933
960
934
961
Note that the :func:`~.expression.column` function is not part of
935
the ``sqlalchemy`` namespace. It must be imported from the ``sql`` package::
962
the ``sqlalchemy`` namespace. It must be imported from the
963
``sql`` package::
936
964
937
965
from sqlalchemy.sql import table, column
938
966
948
976
"""
949
977
return ColumnClause(text, type_=type_)
950
978
979
951
980
def literal_column(text, type_=None):
952
981
"""Return a textual column expression, as would be in the columns
953
982
clause of a ``SELECT`` statement.
963
992
which should be subject to quoting rules, use the :func:`column`
964
993
function.
965
994
966
:param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` object which will
995
:param type\_: an optional :class:`~sqlalchemy.types.TypeEngine`
996
object which will
967
997
provide result-set translation and additional expression semantics for
968
998
this column. If left as None the type will be NullType.
969
999
970
1000
"""
971
1001
return ColumnClause(text, type_=type_, is_literal=True)
972
1002
1003
973
1004
def table(name, *columns):
974
1005
"""Represent a textual table clause.
975
1006
976
The object returned is an instance of :class:`.TableClause`, which represents the
977
"syntactical" portion of the schema-level :class:`~.schema.Table` object.
1007
The object returned is an instance of :class:`.TableClause`, which
1008
represents the "syntactical" portion of the schema-level
1009
:class:`~.schema.Table` object.
978
1010
It may be used to construct lightweight table constructs.
979
1011
980
1012
Note that the :func:`~.expression.table` function is not part of
981
the ``sqlalchemy`` namespace. It must be imported from the ``sql`` package::
1013
the ``sqlalchemy`` namespace. It must be imported from the
1014
``sql`` package::
982
1015
983
1016
from sqlalchemy.sql import table, column
984
1017
991
1024
"""
992
1025
return TableClause(name, *columns)
993
1026
994
def bindparam(key, value=None, type_=None, unique=False, required=False, callable_=None):
1027
1028
def bindparam(key, value=NO_ARG, type_=None, unique=False, required=NO_ARG,
1029
quote=None, callable_=None):
995
1030
"""Create a bind parameter clause with the given key.
996
1031
997
1032
:param key:
998
1033
the key for this bind param. Will be used in the generated
999
1034
SQL statement for dialects that use named parameters. This
1000
1035
value may be modified when part of a compilation operation,
1001
if other :class:`_BindParamClause` objects exist with the same
1036
if other :class:`BindParameter` objects exist with the same
1002
1037
key, or if its length is too long and truncation is
1003
1038
required.
1004
1039
1007
1042
overridden by the dictionary of parameters sent to statement
1008
1043
compilation/execution.
1009
1044
1045
Defaults to ``None``, however if neither ``value`` nor
1046
``callable`` are passed explicitly, the ``required`` flag will be
1047
set to ``True`` which has the effect of requiring a value be present
1048
when the statement is actually executed.
1049
1050
.. versionchanged:: 0.8 The ``required`` flag is set to ``True``
1051
automatically if ``value`` or ``callable`` is not passed.
1052
1010
1053
:param callable\_:
1011
1054
A callable function that takes the place of "value". The function
1012
1055
will be called at statement execution time to determine the
1016
1059
1017
1060
:param type\_:
1018
1061
A ``TypeEngine`` object that will be used to pre-process the
1019
value corresponding to this :class:`_BindParamClause` at
1062
value corresponding to this :class:`BindParameter` at
1020
1063
execution time.
1021
1064
1022
1065
:param unique:
1023
1066
if True, the key name of this BindParamClause will be
1024
modified if another :class:`_BindParamClause` of the same name
1067
modified if another :class:`BindParameter` of the same name
1025
1068
already has been located within the containing
1026
1069
:class:`.ClauseElement`.
1027
1070
1028
1071
:param required:
1029
a value is required at execution time.
1072
If ``True``, a value is required at execution time. If not passed,
1073
is set to ``True`` or ``False`` based on whether or not
1074
one of ``value`` or ``callable`` were passed..
1075
1076
.. versionchanged:: 0.8 If the ``required`` flag is not specified,
1077
it will be set automatically to ``True`` or ``False`` depending
1078
on whether or not the ``value`` or ``callable`` parameters
1079
were specified.
1080
1081
:param quote:
1082
True if this parameter name requires quoting and is not
1083
currently known as a SQLAlchemy reserved word; this currently
1084
only applies to the Oracle backend.
1030
1085
1031
1086
"""
1032
1087
if isinstance(key, ColumnClause):
1033
return _BindParamClause(key.name, value, type_=key.type,
1034
callable_=callable_,
1035
unique=unique, required=required)
1036
else:
1037
return _BindParamClause(key, value, type_=type_,
1038
callable_=callable_,
1039
unique=unique, required=required)
1088
type_ = key.type
1089
key = key.name
1090
if required is NO_ARG:
1091
required = (value is NO_ARG and callable_ is None)
1092
if value is NO_ARG:
1093
value = None
1094
return BindParameter(key, value, type_=type_,
1095
callable_=callable_,
1096
unique=unique, required=required,
1097
quote=quote)
1098
1040
1099
1041
1100
def outparam(key, type_=None):
1042
1101
"""Create an 'OUT' parameter for usage in functions (stored procedures),
1048
1107
attribute, which returns a dictionary containing the values.
1049
1108
1050
1109
"""
1051
return _BindParamClause(
1110
return BindParameter(
1052
1111
key, None, type_=type_, unique=False, isoutparam=True)
1053
1112
1113
1054
1114
def text(text, bind=None, *args, **kwargs):
1055
1115
"""Create a SQL construct that is represented by a literal string.
1056
1116
1145
1205
that returns result sets.
1146
1206
1147
1207
"""
1148
return _TextClause(text, bind=bind, *args, **kwargs)
1208
return TextClause(text, bind=bind, *args, **kwargs)
1209
1149
1210
1150
1211
def over(func, partition_by=None, order_by=None):
1151
1212
"""Produce an OVER clause against a function.
1161
1222
Would produce "ROW_NUMBER() OVER(ORDER BY x)".
1162
1223
1163
1224
:param func: a :class:`.FunctionElement` construct, typically
1164
generated by :attr:`~.expression.func`.
1225
generated by :data:`~.expression.func`.
1165
1226
:param partition_by: a column element or string, or a list
1166
1227
of such, that will be used as the PARTITION BY clause
1167
1228
of the OVER construct.
1169
1230
of such, that will be used as the ORDER BY clause
1170
1231
of the OVER construct.
1171
1232
1172
This function is also available from the :attr:`~.expression.func`
1233
This function is also available from the :data:`~.expression.func`
1173
1234
construct itself via the :meth:`.FunctionElement.over` method.
1174
1235
1175
1236
.. versionadded:: 0.7
1176
1237
1177
1238
"""
1178
return _Over(func, partition_by=partition_by, order_by=order_by)
1239
return Over(func, partition_by=partition_by, order_by=order_by)
1240
1179
1241
1180
1242
def null():
1181
"""Return a :class:`_Null` object, which compiles to ``NULL``.
1243
"""Return a :class:`Null` object, which compiles to ``NULL``.
1182
1244
1183
1245
"""
1184
return _Null()
1246
return Null()
1247
1185
1248
1186
1249
def true():
1187
"""Return a :class:`_True` object, which compiles to ``true``, or the
1250
"""Return a :class:`True_` object, which compiles to ``true``, or the
1188
1251
boolean equivalent for the target dialect.
1189
1252
1190
1253
"""
1191
return _True()
1254
return True_()
1255
1192
1256
1193
1257
def false():
1194
"""Return a :class:`_False` object, which compiles to ``false``, or the
1258
"""Return a :class:`False_` object, which compiles to ``false``, or the
1195
1259
boolean equivalent for the target dialect.
1196
1260
1197
1261
"""
1198
return _False()
1262
return False_()
1263
1199
1264
1200
1265
class _FunctionGenerator(object):
1201
1266
"""Generate :class:`.Function` objects based on getattr calls."""
1221
1286
def __call__(self, *c, **kwargs):
1222
1287
o = self.opts.copy()
1223
1288
o.update(kwargs)
1224
if len(self.__names) == 1:
1225
func = getattr(functions, self.__names[-1].lower(), None)
1226
if func is not None and \
1227
isinstance(func, type) and \
1228
issubclass(func, Function):
1229
return func(*c, **o)
1289
1290
tokens = len(self.__names)
1291
1292
if tokens == 2:
1293
package, fname = self.__names
1294
elif tokens == 1:
1295
package, fname = "_default", self.__names[0]
1296
else:
1297
package = None
1298
1299
if package is not None and \
1300
package in functions._registry and \
1301
fname in functions._registry[package]:
1302
func = functions._registry[package][fname]
1303
return func(*c, **o)
1230
1304
1231
1305
return Function(self.__names[-1],
1232
1306
packagenames=self.__names[0:-1], *c, **o)
1235
1309
func = _FunctionGenerator()
1236
1310
"""Generate SQL function expressions.
1237
1311
1238
``func`` is a special object instance which generates SQL functions based on name-based attributes, e.g.::
1312
:data:`.func` is a special object instance which generates SQL
1313
functions based on name-based attributes, e.g.::
1239
1314
1240
1315
>>> print func.count(1)
1241
1316
count(:param_1)
1246
1321
>>> print select([func.count(table.c.id)])
1247
1322
SELECT count(sometable.id) FROM sometable
1248
1323
1249
Any name can be given to ``func``. If the function name is unknown to
1324
Any name can be given to :data:`.func`. If the function name is unknown to
1250
1325
SQLAlchemy, it will be rendered exactly as is. For common SQL functions
1251
1326
which SQLAlchemy is aware of, the name may be interpreted as a *generic
1252
1327
function* which will be compiled appropriately to the target database::
1254
1329
>>> print func.current_timestamp()
1255
1330
CURRENT_TIMESTAMP
1256
1331
1257
To call functions which are present in dot-separated packages, specify them in the same manner::
1332
To call functions which are present in dot-separated packages,
1333
specify them in the same manner::
1258
1334
1259
1335
>>> print func.stats.yield_curve(5, 10)
1260
1336
stats.yield_curve(:yield_curve_1, :yield_curve_2)
1269
1345
... func.my_string(u'there', type_=Unicode)
1270
1346
my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
1271
1347
1272
The object returned by a ``func`` call is an instance of :class:`.Function`.
1348
The object returned by a :data:`.func` call is usually an instance of
1349
:class:`.Function`.
1273
1350
This object meets the "column" interface, including comparison and labeling
1274
1351
functions. The object can also be passed the :meth:`~.Connectable.execute`
1275
1352
method of a :class:`.Connection` or :class:`.Engine`, where it will be
1277
1354
1278
1355
print connection.execute(func.current_timestamp()).scalar()
1279
1356
1280
A function can also be "bound" to a :class:`.Engine` or :class:`.Connection`
1281
using the ``bind`` keyword argument, providing an execute() as well
1282
as a scalar() method::
1357
In a few exception cases, the :data:`.func` accessor
1358
will redirect a name to a built-in expression such as :func:`.cast`
1359
or :func:`.extract`, as these names have well-known meaning
1360
but are not exactly the same as "functions" from a SQLAlchemy
1361
perspective.
1283
1362
1284
myfunc = func.current_timestamp(bind=some_engine)
1285
print myfunc.scalar()
1363
.. versionadded:: 0.8 :data:`.func` can return non-function expression
1364
constructs for common quasi-functional names like :func:`.cast`
1365
and :func:`.extract`.
1286
1366
1287
1367
Functions which are interpreted as "generic" functions know how to
1288
1368
calculate their return type automatically. For a listing of known generic
1294
1374
# TODO: use UnaryExpression for this instead ?
1295
1375
modifier = _FunctionGenerator(group=False)
1296
1376
1377
1297
1378
class _truncated_label(unicode):
1298
1379
"""A unicode subclass used to identify symbolic "
1299
1380
"names that may require truncation."""
1307
1388
# compiler
1308
1389
_generated_label = _truncated_label
1309
1390
1391
1310
1392
class _anonymous_label(_truncated_label):
1311
1393
"""A unicode subclass used to identify anonymously
1312
1394
generated names."""
1324
1406
def apply_map(self, map_):
1325
1407
return self % map_
1326
1408
1409
1327
1410
def _as_truncated(value):
1328
1411
"""coerce the given value to :class:`._truncated_label`.
1329
1412
1337
1420
else:
1338
1421
return _truncated_label(value)
1339
1422
1423
1340
1424
def _string_or_unprintable(element):
1341
1425
if isinstance(element, basestring):
1342
1426
return element
1346
1430
except:
1347
1431
return "unprintable element %r" % element
1348
1432
1433
1349
1434
def _clone(element, **kw):
1350
1435
return element._clone()
1351
1436
1437
1352
1438
def _expand_cloned(elements):
1353
1439
"""expand the given set of ClauseElements to be the set of all 'cloned'
1354
1440
predecessors.
1356
1442
"""
1357
1443
return itertools.chain(*[x._cloned_set for x in elements])
1358
1444
1445
1359
1446
def _select_iterables(elements):
1360
1447
"""expand tables into individual columns in the
1361
1448
given list of column expressions.
1363
1450
"""
1364
1451
return itertools.chain(*[c._select_iterable for c in elements])
1365
1452
1453
1366
1454
def _cloned_intersection(a, b):
1367
1455
"""return the intersection of sets a and b, counting
1368
1456
any overlap between 'cloned' predecessors.
1374
1462
return set(elem for elem in a
1375
1463
if all_overlap.intersection(elem._cloned_set))
1376
1464
1377
1378
def _is_literal(element):
1379
return not isinstance(element, Visitable) and \
1380
not hasattr(element, '__clause_element__')
1465
def _cloned_difference(a, b):
1466
all_overlap = set(_expand_cloned(a)).intersection(_expand_cloned(b))
1467
return set(elem for elem in a
1468
if not all_overlap.intersection(elem._cloned_set))
1381
1469
1382
1470
def _from_objects(*elements):
1383
1471
return itertools.chain(*[element._from_objects for element in elements])
1384
1472
1473
1385
1474
def _labeled(element):
1386
1475
if not hasattr(element, 'name'):
1387
1476
return element.label(None)
1388
1477
else:
1389
1478
return element
1390
1479
1480
1481
# there is some inconsistency here between the usage of
1482
# inspect() vs. checking for Visitable and __clause_element__.
1483
# Ideally all functions here would derive from inspect(),
1484
# however the inspect() versions add significant callcount
1485
# overhead for critical functions like _interpret_as_column_or_from().
1486
# Generally, the column-based functions are more performance critical
1487
# and are fine just checking for __clause_element__(). it's only
1488
# _interpret_as_from() where we'd like to be able to receive ORM entities
1489
# that have no defined namespace, hence inspect() is needed there.
1490
1491
1391
1492
def _column_as_key(element):
1392
1493
if isinstance(element, basestring):
1393
1494
return element
1394
1495
if hasattr(element, '__clause_element__'):
1395
1496
element = element.__clause_element__()
1396
return element.key
1497
try:
1498
return element.key
1499
except AttributeError:
1500
return None
1501
1502
1503
def _clause_element_as_expr(element):
1504
if hasattr(element, '__clause_element__'):
1505
return element.__clause_element__()
1506
else:
1507
return element
1508
1397
1509
1398
1510
def _literal_as_text(element):
1399
1511
if isinstance(element, Visitable):
1401
1513
elif hasattr(element, '__clause_element__'):
1402
1514
return element.__clause_element__()
1403
1515
elif isinstance(element, basestring):
1404
return _TextClause(unicode(element))
1516
return TextClause(unicode(element))
1405
1517
elif isinstance(element, (util.NoneType, bool)):
1406
1518
return _const_expr(element)
1407
1519
else:
1409
1521
"SQL expression object or string expected."
1410
1522
)
1411
1523
1524
1525
def _no_literals(element):
1526
if hasattr(element, '__clause_element__'):
1527
return element.__clause_element__()
1528
elif not isinstance(element, Visitable):
1529
raise exc.ArgumentError("Ambiguous literal: %r. Use the 'text()' "
1530
"function to indicate a SQL expression "
1531
"literal, or 'literal()' to indicate a "
1532
"bound value." % element)
1533
else:
1534
return element
1535
1536
1537
def _is_literal(element):
1538
return not isinstance(element, Visitable) and \
1539
not hasattr(element, '__clause_element__')
1540
1541
1542
def _only_column_elements_or_none(element, name):
1543
if element is None:
1544
return None
1545
else:
1546
return _only_column_elements(element, name)
1547
1548
1549
def _only_column_elements(element, name):
1550
if hasattr(element, '__clause_element__'):
1551
element = element.__clause_element__()
1552
if not isinstance(element, ColumnElement):
1553
raise exc.ArgumentError(
1554
"Column-based expression object expected for argument "
1555
"'%s'; got: '%s', type %s" % (name, element, type(element)))
1556
return element
1557
1558
1559
def _literal_as_binds(element, name=None, type_=None):
1560
if hasattr(element, '__clause_element__'):
1561
return element.__clause_element__()
1562
elif not isinstance(element, Visitable):
1563
if element is None:
1564
return null()
1565
else:
1566
return _BindParamClause(name, element, type_=type_, unique=True)
1567
else:
1568
return element
1569
1570
1571
def _interpret_as_column_or_from(element):
1572
if isinstance(element, Visitable):
1573
return element
1574
elif hasattr(element, '__clause_element__'):
1575
return element.__clause_element__()
1576
1577
insp = inspection.inspect(element, raiseerr=False)
1578
if insp is None:
1579
if isinstance(element, (util.NoneType, bool)):
1580
return _const_expr(element)
1581
elif hasattr(insp, "selectable"):
1582
return insp.selectable
1583
1584
return literal_column(str(element))
1585
1586
1587
def _interpret_as_from(element):
1588
insp = inspection.inspect(element, raiseerr=False)
1589
if insp is None:
1590
if isinstance(element, basestring):
1591
return TextClause(unicode(element))
1592
elif hasattr(insp, "selectable"):
1593
return insp.selectable
1594
raise exc.ArgumentError("FROM expression expected")
1595
1596
1412
1597
def _const_expr(element):
1413
if element is None:
1598
if isinstance(element, (Null, False_, True_)):
1599
return element
1600
elif element is None:
1414
1601
return null()
1415
1602
elif element is False:
1416
1603
return false()
1421
1608
"Expected None, False, or True"
1422
1609
)
1423
1610
1424
def _clause_element_as_expr(element):
1425
if hasattr(element, '__clause_element__'):
1426
return element.__clause_element__()
1427
else:
1428
return element
1429
1430
def _literal_as_column(element):
1431
if isinstance(element, Visitable):
1432
return element
1433
elif hasattr(element, '__clause_element__'):
1434
return element.__clause_element__()
1435
else:
1436
return literal_column(str(element))
1437
1438
def _literal_as_binds(element, name=None, type_=None):
1439
if hasattr(element, '__clause_element__'):
1440
return element.__clause_element__()
1441
elif not isinstance(element, Visitable):
1442
if element is None:
1443
return null()
1444
else:
1445
return _BindParamClause(name, element, type_=type_, unique=True)
1446
else:
1447
return element
1448
1611
1449
1612
def _type_from_args(args):
1450
1613
for a in args:
1453
1616
else:
1454
1617
return sqltypes.NullType
1455
1618
1456
def _no_literals(element):
1457
if hasattr(element, '__clause_element__'):
1458
return element.__clause_element__()
1459
elif not isinstance(element, Visitable):
1460
raise exc.ArgumentError("Ambiguous literal: %r. Use the 'text()' "
1461
"function to indicate a SQL expression "
1462
"literal, or 'literal()' to indicate a "
1463
"bound value." % element)
1464
else:
1465
return element
1466
1467
def _only_column_elements_or_none(element, name):
1468
if element is None:
1469
return None
1470
else:
1471
return _only_column_elements(element, name)
1472
1473
def _only_column_elements(element, name):
1474
if hasattr(element, '__clause_element__'):
1475
element = element.__clause_element__()
1476
if not isinstance(element, ColumnElement):
1477
raise exc.ArgumentError(
1478
"Column-based expression object expected for argument "
1479
"'%s'; got: '%s', type %s" % (name, element, type(element)))
1480
return element
1481
1619
1482
1620
def _corresponding_column_or_error(fromclause, column,
1483
1621
require_embedded=False):
1489
1627
"failed to locate a corresponding column from table '%s'"
1490
1628
%
1491
1629
(column,
1492
getattr(column, 'table', None),fromclause.description)
1630
getattr(column, 'table', None),
1631
fromclause.description)
1493
1632
)
1494
1633
return c
1495
1634
1635
1496
1636
@util.decorator
1497
1637
def _generative(fn, *args, **kw):
1498
1638
"""Mark a method as generative."""
1520
1660
_from_objects = []
1521
1661
bind = None
1522
1662
_is_clone_of = None
1663
is_selectable = False
1664
is_clause_element = True
1523
1665
1524
1666
def _clone(self):
1525
1667
"""Create a shallow copy of this ClauseElement.
1531
1673
"""
1532
1674
c = self.__class__.__new__(self.__class__)
1533
1675
c.__dict__ = self.__dict__.copy()
1534
c.__dict__.pop('_cloned_set', None)
1676
ClauseElement._cloned_set._reset(c)
1677
ColumnElement.comparator._reset(c)
1535
1678
1536
1679
# this is a marker that helps to "equate" clauses to each other
1537
1680
# when a Select returns its list of FROM clauses. the cloning
1588
1731
return id(self)
1589
1732
1590
1733
def _annotate(self, values):
1591
"""return a copy of this ClauseElement with the given annotations
1592
dictionary.
1593
1594
"""
1595
return sqlutil.Annotated(self, values)
1596
1597
def _deannotate(self):
1598
"""return a copy of this ClauseElement with an empty annotations
1599
dictionary.
1600
1601
"""
1602
return self._clone()
1734
"""return a copy of this ClauseElement with annotations
1735
updated by the given dictionary.
1736
1737
"""
1738
return sqlutil.Annotated(self, values)
1739
1740
def _with_annotations(self, values):
1741
"""return a copy of this ClauseElement with annotations
1742
replaced by the given dictionary.
1743
1744
"""
1745
return sqlutil.Annotated(self, values)
1746
1747
def _deannotate(self, values=None, clone=False):
1748
"""return a copy of this :class:`.ClauseElement` with annotations
1749
removed.
1750
1751
:param values: optional tuple of individual values
1752
to remove.
1753
1754
"""
1755
if clone:
1756
# clone is used when we are also copying
1757
# the expression for a deep deannotation
1758
return self._clone()
1759
else:
1760
# if no clone, since we have no annotations we return
1761
# self
1762
return self
1603
1763
1604
1764
def unique_params(self, *optionaldict, **kwargs):
1605
1765
"""Return a copy with :func:`bindparam()` elements replaced.
1636
1796
def visit_bindparam(bind):
1637
1797
if bind.key in kwargs:
1638
1798
bind.value = kwargs[bind.key]
1799
bind.required = False
1639
1800
if unique:
1640
1801
bind._convert_to_unique()
1641
return cloned_traverse(self, {}, {'bindparam':visit_bindparam})
1802
return cloned_traverse(self, {}, {'bindparam': visit_bindparam})
1642
1803
1643
1804
def compare(self, other, **kw):
1644
1805
"""Compare this ClauseElement to the given ClauseElement.
1706
1867
"""
1707
1868
return self
1708
1869
1709
1710
@util.deprecated('0.7',
1711
'Only SQL expressions which subclass '
1712
':class:`.Executable` may provide the '
1713
':func:`.execute` method.')
1714
def execute(self, *multiparams, **params):
1715
"""Compile and execute this :class:`.ClauseElement`.
1716
1717
"""
1718
e = self.bind
1719
if e is None:
1720
label = getattr(self, 'description', self.__class__.__name__)
1721
msg = ('This %s does not support direct execution.' % label)
1722
raise exc.UnboundExecutionError(msg)
1723
return e._execute_clauseelement(self, multiparams, params)
1724
1725
@util.deprecated('0.7',
1726
'Only SQL expressions which subclass '
1727
':class:`.Executable` may provide the '
1728
':func:`.scalar` method.')
1729
def scalar(self, *multiparams, **params):
1730
"""Compile and execute this :class:`.ClauseElement`, returning
1731
the result's scalar representation.
1732
1733
"""
1734
return self.execute(*multiparams, **params).scalar()
1735
1736
1870
def compile(self, bind=None, dialect=None, **kw):
1737
1871
"""Compile this SQL expression.
1738
1872
1739
The return value is a :class:`~sqlalchemy.engine.Compiled` object.
1873
The return value is a :class:`~.Compiled` object.
1740
1874
Calling ``str()`` or ``unicode()`` on the returned value will yield a
1741
1875
string representation of the result. The
1742
:class:`~sqlalchemy.engine.Compiled` object also can return a
1876
:class:`~.Compiled` object also can return a
1743
1877
dictionary of bind parameter names and values
1744
1878
using the ``params`` accessor.
1745
1879
1806
1940
if hasattr(self, 'negation_clause'):
1807
1941
return self.negation_clause
1808
1942
else:
1809
return _UnaryExpression(
1943
return UnaryExpression(
1810
1944
self.self_group(against=operators.inv),
1811
1945
operator=operators.inv,
1812
1946
negate=None)
1819
1953
return '<%s.%s at 0x%x; %s>' % (
1820
1954
self.__module__, self.__class__.__name__, id(self), friendly)
1821
1955
1822
1823
class _Immutable(object):
1956
inspection._self_inspects(ClauseElement)
1957
1958
1959
class Immutable(object):
1824
1960
"""mark a ClauseElement as 'immutable' when expressions are cloned."""
1825
1961
1826
1962
def unique_params(self, *optionaldict, **kwargs):
1833
1969
return self
1834
1970
1835
1971
1836
class _CompareMixin(ColumnOperators):
1837
"""Defines comparison and math operations for :class:`.ClauseElement`
1838
instances.
1972
class _DefaultColumnComparator(operators.ColumnOperators):
1973
"""Defines comparison and math operations.
1839
1974
1840
1975
See :class:`.ColumnOperators` and :class:`.Operators` for descriptions
1841
1976
of all operations.
1842
1977
1843
1978
"""
1844
1979
1845
def __compare(self, op, obj, negate=None, reverse=False,
1846
**kwargs
1847
):
1848
if obj is None or isinstance(obj, _Null):
1849
if op in (operators.eq, operators.is_):
1850
return _BinaryExpression(self, null(), operators.is_,
1851
negate=operators.isnot)
1852
elif op in (operators.ne, operators.isnot):
1853
return _BinaryExpression(self, null(), operators.isnot,
1854
negate=operators.is_)
1980
@util.memoized_property
1981
def type(self):
1982
return self.expr.type
1983
1984
def operate(self, op, *other, **kwargs):
1985
o = self.operators[op.__name__]
1986
return o[0](self, self.expr, op, *(other + o[1:]), **kwargs)
1987
1988
def reverse_operate(self, op, other, **kwargs):
1989
o = self.operators[op.__name__]
1990
return o[0](self, self.expr, op, other, reverse=True, *o[1:], **kwargs)
1991
1992
def _adapt_expression(self, op, other_comparator):
1993
"""evaluate the return type of <self> <op> <othertype>,
1994
and apply any adaptations to the given operator.
1995
1996
This method determines the type of a resulting binary expression
1997
given two source types and an operator. For example, two
1998
:class:`.Column` objects, both of the type :class:`.Integer`, will
1999
produce a :class:`.BinaryExpression` that also has the type
2000
:class:`.Integer` when compared via the addition (``+``) operator.
2001
However, using the addition operator with an :class:`.Integer`
2002
and a :class:`.Date` object will produce a :class:`.Date`, assuming
2003
"days delta" behavior by the database (in reality, most databases
2004
other than Postgresql don't accept this particular operation).
2005
2006
The method returns a tuple of the form <operator>, <type>.
2007
The resulting operator and type will be those applied to the
2008
resulting :class:`.BinaryExpression` as the final operator and the
2009
right-hand side of the expression.
2010
2011
Note that only a subset of operators make usage of
2012
:meth:`._adapt_expression`,
2013
including math operators and user-defined operators, but not
2014
boolean comparison or special SQL keywords like MATCH or BETWEEN.
2015
2016
"""
2017
return op, other_comparator.type
2018
2019
def _boolean_compare(self, expr, op, obj, negate=None, reverse=False,
2020
_python_is_types=(util.NoneType, bool),
2021
**kwargs):
2022
2023
if isinstance(obj, _python_is_types + (Null, True_, False_)):
2024
2025
# allow x ==/!= True/False to be treated as a literal.
2026
# this comes out to "== / != true/false" or "1/0" if those
2027
# constants aren't supported and works on all platforms
2028
if op in (operators.eq, operators.ne) and \
2029
isinstance(obj, (bool, True_, False_)):
2030
return BinaryExpression(expr,
2031
obj,
2032
op,
2033
type_=sqltypes.BOOLEANTYPE,
2034
negate=negate, modifiers=kwargs)
1855
2035
else:
1856
raise exc.ArgumentError("Only '='/'!=' operators can "
1857
"be used with NULL")
2036
# all other None/True/False uses IS, IS NOT
2037
if op in (operators.eq, operators.is_):
2038
return BinaryExpression(expr, _const_expr(obj),
2039
operators.is_,
2040
negate=operators.isnot)
2041
elif op in (operators.ne, operators.isnot):
2042
return BinaryExpression(expr, _const_expr(obj),
2043
operators.isnot,
2044
negate=operators.is_)
2045
else:
2046
raise exc.ArgumentError(
2047
"Only '=', '!=', 'is_()', 'isnot()' operators can "
2048
"be used with None/True/False")
1858
2049
else:
1859
obj = self._check_literal(op, obj)
2050
obj = self._check_literal(expr, op, obj)
1860
2051
1861
2052
if reverse:
1862
return _BinaryExpression(obj,
1863
self,
2053
return BinaryExpression(obj,
2054
expr,
1864
2055
op,
1865
2056
type_=sqltypes.BOOLEANTYPE,
1866
2057
negate=negate, modifiers=kwargs)
1867
2058
else:
1868
return _BinaryExpression(self,
2059
return BinaryExpression(expr,
1869
2060
obj,
1870
2061
op,
1871
2062
type_=sqltypes.BOOLEANTYPE,
1872
2063
negate=negate, modifiers=kwargs)
1873
2064
1874
def __operate(self, op, obj, reverse=False):
1875
obj = self._check_literal(op, obj)
2065
def _binary_operate(self, expr, op, obj, reverse=False, result_type=None,
2066
**kw):
2067
obj = self._check_literal(expr, op, obj)
1876
2068
1877
2069
if reverse:
1878
left, right = obj, self
1879
else:
1880
left, right = self, obj
1881
1882
if left.type is None:
1883
op, result_type = sqltypes.NULLTYPE._adapt_expression(op,
1884
right.type)
1885
elif right.type is None:
1886
op, result_type = left.type._adapt_expression(op,
1887
sqltypes.NULLTYPE)
1888
else:
1889
op, result_type = left.type._adapt_expression(op,
1890
right.type)
1891
return _BinaryExpression(left, right, op, type_=result_type)
1892
1893
1894
# a mapping of operators with the method they use, along with their negated
1895
# operator for comparison operators
1896
operators = {
1897
operators.add : (__operate,),
1898
operators.mul : (__operate,),
1899
operators.sub : (__operate,),
1900
# Py2K
1901
operators.div : (__operate,),
1902
# end Py2K
1903
operators.mod : (__operate,),
1904
operators.truediv : (__operate,),
1905
operators.lt : (__compare, operators.ge),
1906
operators.le : (__compare, operators.gt),
1907
operators.ne : (__compare, operators.eq),
1908
operators.gt : (__compare, operators.le),
1909
operators.ge : (__compare, operators.lt),
1910
operators.eq : (__compare, operators.ne),
1911
operators.like_op : (__compare, operators.notlike_op),
1912
operators.ilike_op : (__compare, operators.notilike_op),
1913
operators.is_ : (__compare, operators.is_),
1914
operators.isnot : (__compare, operators.isnot),
1915
}
1916
1917
def operate(self, op, *other, **kwargs):
1918
o = _CompareMixin.operators[op]
1919
return o[0](self, op, other[0], *o[1:], **kwargs)
1920
1921
def reverse_operate(self, op, other, **kwargs):
1922
o = _CompareMixin.operators[op]
1923
return o[0](self, op, other, reverse=True, *o[1:], **kwargs)
1924
1925
def in_(self, other):
1926
"""See :meth:`.ColumnOperators.in_`."""
1927
return self._in_impl(operators.in_op, operators.notin_op, other)
1928
1929
def _in_impl(self, op, negate_op, seq_or_selectable):
2070
left, right = obj, expr
2071
else:
2072
left, right = expr, obj
2073
2074
if result_type is None:
2075
op, result_type = left.comparator._adapt_expression(
2076
op, right.comparator)
2077
2078
return BinaryExpression(left, right, op, type_=result_type)
2079
2080
def _scalar(self, expr, op, fn, **kw):
2081
return fn(expr)
2082
2083
def _in_impl(self, expr, op, seq_or_selectable, negate_op, **kw):
1930
2084
seq_or_selectable = _clause_element_as_expr(seq_or_selectable)
1931
2085
1932
if isinstance(seq_or_selectable, _ScalarSelect):
1933
return self.__compare(op, seq_or_selectable,
2086
if isinstance(seq_or_selectable, ScalarSelect):
2087
return self._boolean_compare(expr, op, seq_or_selectable,
1934
2088
negate=negate_op)
1935
elif isinstance(seq_or_selectable, _SelectBase):
2089
elif isinstance(seq_or_selectable, SelectBase):
1936
2090
1937
2091
# TODO: if we ever want to support (x, y, z) IN (select x,
1938
2092
# y, z from table), we would need a multi-column version of
1939
2093
# as_scalar() to produce a multi- column selectable that
1940
2094
# does not export itself as a FROM clause
1941
2095
1942
return self.__compare(op, seq_or_selectable.as_scalar(),
1943
negate=negate_op)
1944
elif isinstance(seq_or_selectable, (Selectable, _TextClause)):
1945
return self.__compare(op, seq_or_selectable,
1946
negate=negate_op)
1947
2096
return self._boolean_compare(
2097
expr, op, seq_or_selectable.as_scalar(),
2098
negate=negate_op, **kw)
2099
elif isinstance(seq_or_selectable, (Selectable, TextClause)):
2100
return self._boolean_compare(expr, op, seq_or_selectable,
2101
negate=negate_op, **kw)
1948
2102
1949
2103
# Handle non selectable arguments as sequences
1950
1951
2104
args = []
1952
2105
for o in seq_or_selectable:
1953
2106
if not _is_literal(o):
1954
if not isinstance(o, _CompareMixin):
2107
if not isinstance(o, ColumnOperators):
1955
2108
raise exc.InvalidRequestError('in() function accept'
1956
2109
's either a list of non-selectable values, '
1957
2110
'or a selectable: %r' % o)
2111
elif o is None:
2112
o = null()
1958
2113
else:
1959
o = self._bind_param(op, o)
2114
o = expr._bind_param(op, o)
1960
2115
args.append(o)
1961
2116
if len(args) == 0:
1962
2117
1970
2125
'empty sequence. This results in a '
1971
2126
'contradiction, which nonetheless can be '
1972
2127
'expensive to evaluate. Consider alternative '
1973
'strategies for improved performance.' % self)
1974
return self != self
2128
'strategies for improved performance.' % expr)
2129
return expr != expr
1975
2130
1976
return self.__compare(op,
2131
return self._boolean_compare(expr, op,
1977
2132
ClauseList(*args).self_group(against=op),
1978
2133
negate=negate_op)
1979
2134
1980
def __neg__(self):
2135
def _unsupported_impl(self, expr, op, *arg, **kw):
2136
raise NotImplementedError("Operator '%s' is not supported on "
2137
"this expression" % op.__name__)
2138
2139
def _neg_impl(self, expr, op, **kw):
1981
2140
"""See :meth:`.ColumnOperators.__neg__`."""
1982
return _UnaryExpression(self, operator=operators.neg)
1983
1984
def startswith(self, other, escape=None):
1985
"""See :meth:`.ColumnOperators.startswith`."""
1986
# use __radd__ to force string concat behavior
1987
return self.__compare(
1988
operators.like_op,
1989
literal_column("'%'", type_=sqltypes.String).__radd__(
1990
self._check_literal(operators.like_op, other)
1991
),
1992
escape=escape)
1993
1994
def endswith(self, other, escape=None):
1995
"""See :meth:`.ColumnOperators.endswith`."""
1996
return self.__compare(
1997
operators.like_op,
1998
literal_column("'%'", type_=sqltypes.String) +
1999
self._check_literal(operators.like_op, other),
2000
escape=escape)
2001
2002
def contains(self, other, escape=None):
2003
"""See :meth:`.ColumnOperators.contains`."""
2004
return self.__compare(
2005
operators.like_op,
2006
literal_column("'%'", type_=sqltypes.String) +
2007
self._check_literal(operators.like_op, other) +
2008
literal_column("'%'", type_=sqltypes.String),
2009
escape=escape)
2010
2011
def match(self, other):
2141
return UnaryExpression(expr, operator=operators.neg)
2142
2143
def _match_impl(self, expr, op, other, **kw):
2012
2144
"""See :meth:`.ColumnOperators.match`."""
2013
return self.__compare(operators.match_op,
2014
self._check_literal(operators.match_op,
2145
return self._boolean_compare(expr, operators.match_op,
2146
self._check_literal(expr, operators.match_op,
2015
2147
other))
2016
2148
2017
def label(self, name):
2018
"""Produce a column label, i.e. ``<columnname> AS <name>``.
2019
2020
This is a shortcut to the :func:`~.expression.label` function.
2021
2022
if 'name' is None, an anonymous label name will be generated.
2023
2024
"""
2025
return _Label(name, self, self.type)
2026
2027
def desc(self):
2028
"""See :meth:`.ColumnOperators.desc`."""
2029
return desc(self)
2030
2031
def asc(self):
2032
"""See :meth:`.ColumnOperators.asc`."""
2033
return asc(self)
2034
2035
def nullsfirst(self):
2036
"""See :meth:`.ColumnOperators.nullsfirst`."""
2037
return nullsfirst(self)
2038
2039
def nullslast(self):
2040
"""See :meth:`.ColumnOperators.nullslast`."""
2041
return nullslast(self)
2042
2043
def distinct(self):
2149
def _distinct_impl(self, expr, op, **kw):
2044
2150
"""See :meth:`.ColumnOperators.distinct`."""
2045
return _UnaryExpression(self, operator=operators.distinct_op,
2046
type_=self.type)
2151
return UnaryExpression(expr, operator=operators.distinct_op,
2152
type_=expr.type)
2047
2153
2048
def between(self, cleft, cright):
2154
def _between_impl(self, expr, op, cleft, cright, **kw):
2049
2155
"""See :meth:`.ColumnOperators.between`."""
2050
return _BinaryExpression(
2051
self,
2156
return BinaryExpression(
2157
expr,
2052
2158
ClauseList(
2053
self._check_literal(operators.and_, cleft),
2054
self._check_literal(operators.and_, cright),
2159
self._check_literal(expr, operators.and_, cleft),
2160
self._check_literal(expr, operators.and_, cright),
2055
2161
operator=operators.and_,
2056
2162
group=False),
2057
2163
operators.between_op)
2058
2164
2059
def collate(self, collation):
2060
"""See :meth:`.ColumnOperators.collate`."""
2061
2062
return collate(self, collation)
2063
2064
def op(self, operator):
2065
"""See :meth:`.ColumnOperators.op`."""
2066
2067
return lambda other: self.__operate(operator, other)
2068
2069
def _bind_param(self, operator, obj):
2070
return _BindParamClause(None, obj,
2071
_compared_to_operator=operator,
2072
_compared_to_type=self.type, unique=True)
2073
2074
def _check_literal(self, operator, other):
2075
if isinstance(other, _BindParamClause) and \
2076
isinstance(other.type, sqltypes.NullType):
2077
# TODO: perhaps we should not mutate the incoming bindparam()
2078
# here and instead make a copy of it. this might
2079
# be the only place that we're mutating an incoming construct.
2080
other.type = self.type
2165
def _collate_impl(self, expr, op, other, **kw):
2166
return collate(expr, other)
2167
2168
# a mapping of operators with the method they use, along with
2169
# their negated operator for comparison operators
2170
operators = {
2171
"add": (_binary_operate,),
2172
"mul": (_binary_operate,),
2173
"sub": (_binary_operate,),
2174
"div": (_binary_operate,),
2175
"mod": (_binary_operate,),
2176
"truediv": (_binary_operate,),
2177
"custom_op": (_binary_operate,),
2178
"concat_op": (_binary_operate,),
2179
"lt": (_boolean_compare, operators.ge),
2180
"le": (_boolean_compare, operators.gt),
2181
"ne": (_boolean_compare, operators.eq),
2182
"gt": (_boolean_compare, operators.le),
2183
"ge": (_boolean_compare, operators.lt),
2184
"eq": (_boolean_compare, operators.ne),
2185
"like_op": (_boolean_compare, operators.notlike_op),
2186
"ilike_op": (_boolean_compare, operators.notilike_op),
2187
"notlike_op": (_boolean_compare, operators.like_op),
2188
"notilike_op": (_boolean_compare, operators.ilike_op),
2189
"contains_op": (_boolean_compare, operators.notcontains_op),
2190
"startswith_op": (_boolean_compare, operators.notstartswith_op),
2191
"endswith_op": (_boolean_compare, operators.notendswith_op),
2192
"desc_op": (_scalar, desc),
2193
"asc_op": (_scalar, asc),
2194
"nullsfirst_op": (_scalar, nullsfirst),
2195
"nullslast_op": (_scalar, nullslast),
2196
"in_op": (_in_impl, operators.notin_op),
2197
"notin_op": (_in_impl, operators.in_op),
2198
"is_": (_boolean_compare, operators.is_),
2199
"isnot": (_boolean_compare, operators.isnot),
2200
"collate": (_collate_impl,),
2201
"match_op": (_match_impl,),
2202
"distinct_op": (_distinct_impl,),
2203
"between_op": (_between_impl, ),
2204
"neg": (_neg_impl,),
2205
"getitem": (_unsupported_impl,),
2206
"lshift": (_unsupported_impl,),
2207
"rshift": (_unsupported_impl,),
2208
}
2209
2210
def _check_literal(self, expr, operator, other):
2211
if isinstance(other, (ColumnElement, TextClause)):
2212
if isinstance(other, BindParameter) and \
2213
isinstance(other.type, sqltypes.NullType):
2214
# TODO: perhaps we should not mutate the incoming
2215
# bindparam() here and instead make a copy of it.
2216
# this might be the only place that we're mutating
2217
# an incoming construct.
2218
other.type = expr.type
2081
2219
return other
2082
2220
elif hasattr(other, '__clause_element__'):
2083
2221
other = other.__clause_element__()
2084
if isinstance(other, (_SelectBase, Alias)):
2085
other = other.as_scalar()
2086
return other
2087
elif not isinstance(other, ClauseElement):
2088
return self._bind_param(operator, other)
2089
elif isinstance(other, (_SelectBase, Alias)):
2222
elif isinstance(other, sqltypes.TypeEngine.Comparator):
2223
other = other.expr
2224
2225
if isinstance(other, (SelectBase, Alias)):
2090
2226
return other.as_scalar()
2227
elif not isinstance(other, (ColumnElement, TextClause)):
2228
return expr._bind_param(operator, other)
2091
2229
else:
2092
2230
return other
2093
2231
2094
2232
2095
class ColumnElement(ClauseElement, _CompareMixin):
2096
"""Represent an element that is usable within the "column clause" portion
2097
of a ``SELECT`` statement.
2098
2099
This includes columns associated with tables, aliases, and
2100
subqueries, expressions, function calls, SQL keywords such as
2101
``NULL``, literals, etc. :class:`.ColumnElement` is the ultimate base
2102
class for all such elements.
2233
class ColumnElement(ClauseElement, ColumnOperators):
2234
"""Represent a column-oriented SQL expression suitable for usage in the
2235
"columns" clause, WHERE clause etc. of a statement.
2236
2237
While the most familiar kind of :class:`.ColumnElement` is the
2238
:class:`.Column` object, :class:`.ColumnElement` serves as the basis
2239
for any unit that may be present in a SQL expression, including
2240
the expressions themselves, SQL functions, bound parameters,
2241
literal expressions, keywords such as ``NULL``, etc.
2242
:class:`.ColumnElement` is the ultimate base class for all such elements.
2243
2244
A :class:`.ColumnElement` provides the ability to generate new
2245
:class:`.ColumnElement`
2246
objects using Python expressions. This means that Python operators
2247
such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations,
2248
and allow the instantiation of further :class:`.ColumnElement` instances
2249
which are composed from other, more fundamental :class:`.ColumnElement`
2250
objects. For example, two :class:`.ColumnClause` objects can be added
2251
together with the addition operator ``+`` to produce
2252
a :class:`.BinaryExpression`.
2253
Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses
2254
of :class:`.ColumnElement`::
2255
2256
>>> from sqlalchemy.sql import column
2257
>>> column('a') + column('b')
2258
<sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
2259
>>> print column('a') + column('b')
2260
a + b
2103
2261
2104
2262
:class:`.ColumnElement` supports the ability to be a *proxy* element,
2105
2263
which indicates that the :class:`.ColumnElement` may be associated with
2106
2264
a :class:`.Selectable` which was derived from another :class:`.Selectable`.
2107
2265
An example of a "derived" :class:`.Selectable` is an :class:`.Alias` of a
2108
:class:`~sqlalchemy.schema.Table`.
2109
2110
A :class:`.ColumnElement`, by subclassing the :class:`_CompareMixin` mixin
2111
class, provides the ability to generate new :class:`.ClauseElement`
2112
objects using Python expressions. See the :class:`_CompareMixin`
2113
docstring for more details.
2266
:class:`~sqlalchemy.schema.Table`. For the ambitious, an in-depth
2267
discussion of this concept can be found at
2268
`Expression Transformations <http://techspot.zzzeek.org/2008/01/23/expression-transformations/>`_.
2114
2269
2115
2270
"""
2116
2271
2122
2277
_key_label = None
2123
2278
_alt_names = ()
2124
2279
2280
@util.memoized_property
2281
def type(self):
2282
return sqltypes.NULLTYPE
2283
2284
@util.memoized_property
2285
def comparator(self):
2286
return self.type.comparator_factory(self)
2287
2288
def __getattr__(self, key):
2289
try:
2290
return getattr(self.comparator, key)
2291
except AttributeError:
2292
raise AttributeError(
2293
'Neither %r object nor %r object has an attribute %r' % (
2294
type(self).__name__,
2295
type(self.comparator).__name__,
2296
key)
2297
)
2298
2299
def operate(self, op, *other, **kwargs):
2300
return op(self.comparator, *other, **kwargs)
2301
2302
def reverse_operate(self, op, other, **kwargs):
2303
return op(other, self.comparator, **kwargs)
2304
2305
def _bind_param(self, operator, obj):
2306
return BindParameter(None, obj,
2307
_compared_to_operator=operator,
2308
_compared_to_type=self.type, unique=True)
2309
2310
@property
2311
def expression(self):
2312
"""Return a column expression.
2313
2314
Part of the inspection interface; returns self.
2315
2316
"""
2317
return self
2318
2125
2319
@property
2126
2320
def _select_iterable(self):
2127
2321
return (self, )
2129
2323
@util.memoized_property
2130
2324
def base_columns(self):
2131
2325
return util.column_set(c for c in self.proxy_set
2132
if not hasattr(c, 'proxies'))
2326
if not hasattr(c, '_proxies'))
2133
2327
2134
2328
@util.memoized_property
2135
2329
def proxy_set(self):
2136
2330
s = util.column_set([self])
2137
if hasattr(self, 'proxies'):
2138
for c in self.proxies:
2331
if hasattr(self, '_proxies'):
2332
for c in self._proxies:
2139
2333
s.update(c.proxy_set)
2140
2334
return s
2141
2335
2152
2346
return hasattr(other, 'name') and hasattr(self, 'name') and \
2153
2347
other.name == self.name
2154
2348
2155
def _make_proxy(self, selectable, name=None):
2349
def _make_proxy(self, selectable, name=None, name_is_truncatable=False, **kw):
2156
2350
"""Create a new :class:`.ColumnElement` representing this
2157
2351
:class:`.ColumnElement` as it appears in the select list of a
2158
2352
descending selectable.
2160
2354
"""
2161
2355
if name is None:
2162
2356
name = self.anon_label
2163
# TODO: may want to change this to anon_label,
2164
# or some value that is more useful than the
2165
# compiled form of the expression
2166
2357
key = str(self)
2167
2358
else:
2168
2359
key = name
2169
2170
co = ColumnClause(_as_truncated(name),
2360
co = ColumnClause(_as_truncated(name) if name_is_truncatable else name,
2171
2361
selectable,
2172
2362
type_=getattr(self,
2173
2363
'type', None))
2174
co.proxies = [self]
2364
co._proxies = [self]
2175
2365
if selectable._is_clone_of is not None:
2176
2366
co._is_clone_of = \
2177
2367
selectable._is_clone_of.columns.get(key)
2201
2391
for oth in to_compare:
2202
2392
if use_proxies and self.shares_lineage(oth):
2203
2393
return True
2204
elif oth is self:
2394
elif hash(oth) == hash(self):
2205
2395
return True
2206
2396
else:
2207
2397
return False
2208
2398
2399
def label(self, name):
2400
"""Produce a column label, i.e. ``<columnname> AS <name>``.
2401
2402
This is a shortcut to the :func:`~.expression.label` function.
2403
2404
if 'name' is None, an anonymous label name will be generated.
2405
2406
"""
2407
return Label(name, self, self.type)
2408
2209
2409
@util.memoized_property
2210
2410
def anon_label(self):
2211
2411
"""provides a constant 'anonymous label' for this ColumnElement.
2223
2423
return _anonymous_label('%%(%d %s)s' % (id(self), getattr(self,
2224
2424
'name', 'anon')))
2225
2425
2426
2226
2427
class ColumnCollection(util.OrderedProperties):
2227
2428
"""An ordered dictionary that stores a list of ColumnElement
2228
2429
instances.
2291
2492
existing = self[key]
2292
2493
if not existing.shares_lineage(value):
2293
2494
util.warn('Column %r on table %r being replaced by '
2294
'another column with the same key. Consider '
2495
'%r, which has the same key. Consider '
2295
2496
'use_labels for select() statements.' % (key,
2296
getattr(existing, 'table', None)))
2497
getattr(existing, 'table', None), value))
2297
2498
self._all_cols.remove(existing)
2298
2499
# pop out memoized proxy_set as this
2299
2500
# operation may very well be occurring
2300
2501
# in a _make_proxy operation
2301
value.__dict__.pop('proxy_set', None)
2502
ColumnElement.proxy_set._reset(value)
2302
2503
self._all_cols.add(value)
2303
2504
self._data[key] = value
2304
2505
2325
2526
for c in other:
2326
2527
for local in self:
2327
2528
if c.shares_lineage(local):
2328
l.append(c==local)
2529
l.append(c == local)
2329
2530
return and_(*l)
2330
2531
2331
2532
def __contains__(self, other):
2344
2545
def as_immutable(self):
2345
2546
return ImmutableColumnCollection(self._data, self._all_cols)
2346
2547
2548
2347
2549
class ImmutableColumnCollection(util.ImmutableProperties, ColumnCollection):
2348
2550
def __init__(self, data, colset):
2349
2551
util.ImmutableProperties.__init__(self, data)
2368
2570
for c in other:
2369
2571
for local in self:
2370
2572
if c.shares_lineage(local):
2371
l.append(c==local)
2573
l.append(c == local)
2372
2574
return and_(*l)
2373
2575
2374
2576
def __hash__(self):
2375
2577
return hash(tuple(x for x in self))
2376
2578
2579
2377
2580
class Selectable(ClauseElement):
2378
2581
"""mark a class as being selectable"""
2379
2582
__visit_name__ = 'selectable'
2380
2583
2584
is_selectable = True
2585
2586
@property
2587
def selectable(self):
2588
return self
2589
2590
2381
2591
class FromClause(Selectable):
2382
2592
"""Represent an element that can be used within the ``FROM``
2383
2593
clause of a ``SELECT`` statement.
2384
2594
2595
The most common forms of :class:`.FromClause` are the
2596
:class:`.Table` and the :func:`.select` constructs. Key
2597
features common to all :class:`.FromClause` objects include:
2598
2599
* a :attr:`.c` collection, which provides per-name access to a collection
2600
of :class:`.ColumnElement` objects.
2601
* a :attr:`.primary_key` attribute, which is a collection of all those
2602
:class:`.ColumnElement` objects that indicate the ``primary_key`` flag.
2603
* Methods to generate various derivations of a "from" clause, including
2604
:meth:`.FromClause.alias`, :meth:`.FromClause.join`,
2605
:meth:`.FromClause.select`.
2606
2607
2385
2608
"""
2386
2609
__visit_name__ = 'fromclause'
2387
2610
named_with_column = False
2405
2628
**params)
2406
2629
2407
2630
def select(self, whereclause=None, **params):
2408
"""return a SELECT of this :class:`.FromClause`."""
2631
"""return a SELECT of this :class:`.FromClause`.
2632
2633
.. seealso::
2634
2635
:func:`~.sql.expression.select` - general purpose
2636
method which allows for arbitrary column lists.
2637
2638
"""
2409
2639
2410
2640
return select([self], whereclause, **params)
2411
2641
2565
2795
2566
2796
@_memoized_property
2567
2797
def columns(self):
2568
"""Return the collection of Column objects contained by this
2569
FromClause."""
2798
"""A named-based collection of :class:`.ColumnElement` objects
2799
maintained by this :class:`.FromClause`.
2800
2801
The :attr:`.columns`, or :attr:`.c` collection, is the gateway
2802
to the construction of SQL expressions using table-bound or
2803
other selectable-bound columns::
2804
2805
select([mytable]).where(mytable.c.somecolumn == 5)
2806
2807
"""
2570
2808
2571
2809
if '_columns' not in self.__dict__:
2572
2810
self._init_collections()
2591
2829
self._populate_column_collection()
2592
2830
return self.foreign_keys
2593
2831
2594
c = property(attrgetter('columns'))
2832
c = property(attrgetter('columns'),
2833
doc="An alias for the :attr:`.columns` attribute.")
2595
2834
_select_iterable = property(attrgetter('columns'))
2596
2835
2597
2836
def _init_collections(self):
2603
2842
self.primary_key = ColumnSet()
2604
2843
self.foreign_keys = set()
2605
2844
2845
@property
2846
def _cols_populated(self):
2847
return '_columns' in self.__dict__
2848
2606
2849
def _populate_column_collection(self):
2607
pass
2608
2609
class _BindParamClause(ColumnElement):
2850
"""Called on subclasses to establish the .c collection.
2851
2852
Each implementation has a different way of establishing
2853
this collection.
2854
2855
"""
2856
2857
def _refresh_for_new_column(self, column):
2858
"""Given a column added to the .c collection of an underlying
2859
selectable, produce the local version of that column, assuming this
2860
selectable ultimately should proxy this column.
2861
2862
this is used to "ping" a derived selectable to add a new column
2863
to its .c. collection when a Column has been added to one of the
2864
Table objects it ultimtely derives from.
2865
2866
If the given selectable hasn't populated it's .c. collection yet,
2867
it should at least pass on the message to the contained selectables,
2868
but it will return None.
2869
2870
This method is currently used by Declarative to allow Table
2871
columns to be added to a partially constructed inheritance
2872
mapping that may have already produced joins. The method
2873
isn't public right now, as the full span of implications
2874
and/or caveats aren't yet clear.
2875
2876
It's also possible that this functionality could be invoked by
2877
default via an event, which would require that
2878
selectables maintain a weak referencing collection of all
2879
derivations.
2880
2881
"""
2882
if not self._cols_populated:
2883
return None
2884
elif column.key in self.columns and self.columns[column.key] is column:
2885
return column
2886
else:
2887
return None
2888
2889
2890
class BindParameter(ColumnElement):
2610
2891
"""Represent a bind parameter.
2611
2892
2612
2893
Public constructor is the :func:`bindparam()` function.
2616
2897
__visit_name__ = 'bindparam'
2617
2898
quote = None
2618
2899
2900
_is_crud = False
2901
2619
2902
def __init__(self, key, value, type_=None, unique=False,
2620
2903
callable_=None,
2621
2904
isoutparam=False, required=False,
2905
quote=None,
2622
2906
_compared_to_operator=None,
2623
2907
_compared_to_type=None):
2624
"""Construct a _BindParamClause.
2908
"""Construct a BindParameter.
2625
2909
2626
2910
:param key:
2627
2911
the key for this bind param. Will be used in the generated
2628
2912
SQL statement for dialects that use named parameters. This
2629
2913
value may be modified when part of a compilation operation,
2630
if other :class:`_BindParamClause` objects exist with the same
2914
if other :class:`BindParameter` objects exist with the same
2631
2915
key, or if its length is too long and truncation is
2632
2916
required.
2633
2917
2645
2929
2646
2930
:param type\_:
2647
2931
A ``TypeEngine`` object that will be used to pre-process the
2648
value corresponding to this :class:`_BindParamClause` at
2932
value corresponding to this :class:`BindParameter` at
2649
2933
execution time.
2650
2934
2651
2935
:param unique:
2652
2936
if True, the key name of this BindParamClause will be
2653
modified if another :class:`_BindParamClause` of the same name
2937
modified if another :class:`BindParameter` of the same name
2654
2938
already has been located within the containing
2655
2939
:class:`.ClauseElement`.
2656
2940
2941
:param quote:
2942
True if this parameter name requires quoting and is not
2943
currently known as a SQLAlchemy reserved word; this currently
2944
only applies to the Oracle backend.
2945
2657
2946
:param required:
2658
2947
a value is required at execution time.
2659
2948
2683
2972
self.callable = callable_
2684
2973
self.isoutparam = isoutparam
2685
2974
self.required = required
2975
self.quote = quote
2686
2976
if type_ is None:
2687
2977
if _compared_to_type is not None:
2688
2978
self.type = \
2689
_compared_to_type._coerce_compared_value(
2979
_compared_to_type.coerce_compared_value(
2690
2980
_compared_to_operator, value)
2691
2981
else:
2692
2982
self.type = sqltypes._type_map.get(type(value),
2725
3015
self._orig_key or 'param'))
2726
3016
2727
3017
def compare(self, other, **kw):
2728
"""Compare this :class:`_BindParamClause` to the given
3018
"""Compare this :class:`BindParameter` to the given
2729
3019
clause."""
2730
3020
2731
return isinstance(other, _BindParamClause) \
3021
return isinstance(other, BindParameter) \
2732
3022
and self.type._compare_type_affinity(other.type) \
2733
3023
and self.value == other.value
2734
3024
2744
3034
return d
2745
3035
2746
3036
def __repr__(self):
2747
return '_BindParamClause(%r, %r, type_=%r)' % (self.key,
3037
return 'BindParameter(%r, %r, type_=%r)' % (self.key,
2748
3038
self.value, self.type)
2749
3039
2750
class _TypeClause(ClauseElement):
3040
3041
class TypeClause(ClauseElement):
2751
3042
"""Handle a type keyword in a SQL statement.
2752
3043
2753
3044
Used by the ``Case`` statement.
2760
3051
self.type = type
2761
3052
2762
3053
2763
class _Generative(object):
3054
class Generative(object):
2764
3055
"""Allow a ClauseElement to generate itself via the
2765
3056
@_generative decorator.
2766
3057
2772
3063
return s
2773
3064
2774
3065
2775
class Executable(_Generative):
3066
class Executable(Generative):
2776
3067
"""Mark a ClauseElement as supporting execution.
2777
3068
2778
3069
:class:`.Executable` is a superclass for all "statement" types
2792
3083
2793
3084
Execution options can be set on a per-statement or
2794
3085
per :class:`.Connection` basis. Additionally, the
2795
:class:`.Engine` and ORM :class:`~.orm.query.Query` objects provide access
2796
to execution options which they in turn configure upon connections.
3086
:class:`.Engine` and ORM :class:`~.orm.query.Query` objects provide
3087
access to execution options which they in turn configure upon
3088
connections.
2797
3089
2798
3090
The :meth:`execution_options` method is generative. A new
2799
3091
instance of this statement is returned that contains the options::
2807
3099
See :meth:`.Connection.execution_options` for a full list of
2808
3100
possible options.
2809
3101
2810
See also:
3102
.. seealso::
2811
3103
2812
3104
:meth:`.Connection.execution_options()`
2813
3105
2873
3165
# legacy, some outside users may be calling this
2874
3166
_Executable = Executable
2875
3167
2876
class _TextClause(Executable, ClauseElement):
3168
3169
class TextClause(Executable, ClauseElement):
2877
3170
"""Represent a literal SQL text fragment.
2878
3171
2879
3172
Public constructor is the :func:`text()` function.
2884
3177
2885
3178
_bind_params_regex = re.compile(r'(?<![:\w\x5c]):(\w+)(?!:)', re.UNICODE)
2886
3179
_execution_options = \
2887
Executable._execution_options.union({'autocommit'
2888
: PARSE_AUTOCOMMIT})
3180
Executable._execution_options.union(
3181
{'autocommit': PARSE_AUTOCOMMIT})
2889
3182
2890
3183
@property
2891
3184
def _select_iterable(self):
2892
3185
return (self,)
2893
3186
3187
@property
3188
def selectable(self):
3189
return self
3190
2894
3191
_hide_froms = []
2895
3192
2896
3193
def __init__(
2909
3206
'Use .execution_options(autocommit=Tru'
2910
3207
'e)')
2911
3208
self._execution_options = \
2912
self._execution_options.union({'autocommit'
2913
: autocommit})
3209
self._execution_options.union(
3210
{'autocommit': autocommit})
2914
3211
if typemap is not None:
2915
3212
for key in typemap.keys():
2916
3213
typemap[key] = sqltypes.to_instance(typemap[key])
2934
3231
else:
2935
3232
return sqltypes.NULLTYPE
2936
3233
3234
@property
3235
def comparator(self):
3236
return self.type.comparator_factory(self)
3237
2937
3238
def self_group(self, against=None):
2938
3239
if against is operators.in_op:
2939
return _Grouping(self)
3240
return Grouping(self)
2940
3241
else:
2941
3242
return self
2942
3243
2948
3249
return self.bindparams.values()
2949
3250
2950
3251
2951
class _Null(ColumnElement):
3252
class Null(ColumnElement):
2952
3253
"""Represent the NULL keyword in a SQL statement.
2953
3254
2954
3255
Public constructor is the :func:`null()` function.
2956
3257
"""
2957
3258
2958
3259
__visit_name__ = 'null'
3260
2959
3261
def __init__(self):
2960
3262
self.type = sqltypes.NULLTYPE
2961
3263
2962
3264
def compare(self, other):
2963
return isinstance(other, _Null)
2964
2965
2966
class _False(ColumnElement):
3265
return isinstance(other, Null)
3266
3267
3268
class False_(ColumnElement):
2967
3269
"""Represent the ``false`` keyword in a SQL statement.
2968
3270
2969
3271
Public constructor is the :func:`false()` function.
2971
3273
"""
2972
3274
2973
3275
__visit_name__ = 'false'
3276
2974
3277
def __init__(self):
2975
3278
self.type = sqltypes.BOOLEANTYPE
2976
3279
2977
class _True(ColumnElement):
3280
def compare(self, other):
3281
return isinstance(other, False_)
3282
3283
class True_(ColumnElement):
2978
3284
"""Represent the ``true`` keyword in a SQL statement.
2979
3285
2980
3286
Public constructor is the :func:`true()` function.
2982
3288
"""
2983
3289
2984
3290
__visit_name__ = 'true'
3291
2985
3292
def __init__(self):
2986
3293
self.type = sqltypes.BOOLEANTYPE
2987
3294
3295
def compare(self, other):
3296
return isinstance(other, True_)
3297
2988
3298
2989
3299
class ClauseList(ClauseElement):
2990
3300
"""Describe a list of clauses, separated by an operator.
3007
3317
_literal_as_text(clause)
3008
3318
for clause in clauses if clause is not None]
3009
3319
3010
@util.memoized_property
3011
def type(self):
3012
if self.clauses:
3013
return self.clauses[0].type
3014
else:
3015
return sqltypes.NULLTYPE
3016
3017
3320
def __iter__(self):
3018
3321
return iter(self.clauses)
3019
3322
3047
3350
3048
3351
def self_group(self, against=None):
3049
3352
if self.group and operators.is_precedent(self.operator, against):
3050
return _Grouping(self)
3353
return Grouping(self)
3051
3354
else:
3052
3355
return self
3053
3356
3068
3371
else:
3069
3372
return False
3070
3373
3374
3071
3375
class BooleanClauseList(ClauseList, ColumnElement):
3072
3376
__visit_name__ = 'clauselist'
3073
3377
3086
3390
else:
3087
3391
return super(BooleanClauseList, self).self_group(against=against)
3088
3392
3089
class _Tuple(ClauseList, ColumnElement):
3393
3394
class Tuple(ClauseList, ColumnElement):
3090
3395
3091
3396
def __init__(self, *clauses, **kw):
3092
3397
clauses = [_literal_as_binds(c) for c in clauses]
3093
super(_Tuple, self).__init__(*clauses, **kw)
3094
self.type = _type_from_args(clauses)
3398
self.type = kw.pop('type_', None)
3399
if self.type is None:
3400
self.type = _type_from_args(clauses)
3401
super(Tuple, self).__init__(*clauses, **kw)
3095
3402
3096
3403
@property
3097
3404
def _select_iterable(self):
3098
3405
return (self, )
3099
3406
3100
3407
def _bind_param(self, operator, obj):
3101
return _Tuple(*[
3102
_BindParamClause(None, o, _compared_to_operator=operator,
3408
return Tuple(*[
3409
BindParameter(None, o, _compared_to_operator=operator,
3103
3410
_compared_to_type=self.type, unique=True)
3104
3411
for o in obj
3105
3412
]).self_group()
3106
3413
3107
3414
3108
class _Case(ColumnElement):
3415
class Case(ColumnElement):
3109
3416
__visit_name__ = 'case'
3110
3417
3111
3418
def __init__(self, whens, value=None, else_=None):
3164
3471
return list(itertools.chain(*[x._from_objects for x in
3165
3472
self.get_children()]))
3166
3473
3474
3167
3475
class FunctionElement(Executable, ColumnElement, FromClause):
3168
"""Base for SQL function-oriented constructs."""
3476
"""Base for SQL function-oriented constructs.
3477
3478
.. seealso::
3479
3480
:class:`.Function` - named SQL function.
3481
3482
:data:`.func` - namespace which produces registered or ad-hoc
3483
:class:`.Function` instances.
3484
3485
:class:`.GenericFunction` - allows creation of registered function
3486
types.
3487
3488
"""
3169
3489
3170
3490
packagenames = ()
3171
3491
3227
3547
def _copy_internals(self, clone=_clone, **kw):
3228
3548
self.clause_expr = clone(self.clause_expr, **kw)
3229
3549
self._reset_exported()
3230
util.reset_memoized(self, 'clauses')
3550
FunctionElement.clauses._reset(self)
3231
3551
3232
3552
def select(self):
3233
3553
"""Produce a :func:`~.expression.select` construct
3272
3592
return self.select().execute()
3273
3593
3274
3594
def _bind_param(self, operator, obj):
3275
return _BindParamClause(None, obj, _compared_to_operator=operator,
3595
return BindParameter(None, obj, _compared_to_operator=operator,
3276
3596
_compared_to_type=self.type, unique=True)
3277
3597
3278
3598
3282
3602
See the superclass :class:`.FunctionElement` for a description
3283
3603
of public methods.
3284
3604
3605
.. seealso::
3606
3607
:data:`.func` - namespace which produces registered or ad-hoc
3608
:class:`.Function` instances.
3609
3610
:class:`.GenericFunction` - allows creation of registered function
3611
types.
3612
3285
3613
"""
3286
3614
3287
3615
__visit_name__ = 'function'
3289
3617
def __init__(self, name, *clauses, **kw):
3290
3618
"""Construct a :class:`.Function`.
3291
3619
3292
The :attr:`.func` construct is normally used to construct
3620
The :data:`.func` construct is normally used to construct
3293
3621
new :class:`.Function` instances.
3294
3622
3295
3623
"""
3301
3629
FunctionElement.__init__(self, *clauses, **kw)
3302
3630
3303
3631
def _bind_param(self, operator, obj):
3304
return _BindParamClause(self.name, obj,
3632
return BindParameter(self.name, obj,
3305
3633
_compared_to_operator=operator,
3306
3634
_compared_to_type=self.type,
3307
3635
unique=True)
3308
3636
3309
3637
3310
class _Cast(ColumnElement):
3638
class Cast(ColumnElement):
3311
3639
3312
3640
__visit_name__ = 'cast'
3313
3641
3314
3642
def __init__(self, clause, totype, **kwargs):
3315
3643
self.type = sqltypes.to_instance(totype)
3316
3644
self.clause = _literal_as_binds(clause, None)
3317
self.typeclause = _TypeClause(self.type)
3645
self.typeclause = TypeClause(self.type)
3318
3646
3319
3647
def _copy_internals(self, clone=_clone, **kw):
3320
3648
self.clause = clone(self.clause, **kw)
3328
3656
return self.clause._from_objects
3329
3657
3330
3658
3331
class _Extract(ColumnElement):
3659
class Extract(ColumnElement):
3332
3660
3333
3661
__visit_name__ = 'extract'
3334
3662
3348
3676
return self.expr._from_objects
3349
3677
3350
3678
3351
class _UnaryExpression(ColumnElement):
3352
3679
class UnaryExpression(ColumnElement):
3680
"""Define a 'unary' expression.
3681
3682
A unary expression has a single column expression
3683
and an operator. The operator can be placed on the left
3684
(where it is called the 'operator') or right (where it is called the
3685
'modifier') of the column expression.
3686
3687
"""
3353
3688
__visit_name__ = 'unary'
3354
3689
3355
3690
def __init__(self, element, operator=None, modifier=None,
3373
3708
return self.element,
3374
3709
3375
3710
def compare(self, other, **kw):
3376
"""Compare this :class:`_UnaryExpression` against the given
3711
"""Compare this :class:`UnaryExpression` against the given
3377
3712
:class:`.ClauseElement`."""
3378
3713
3379
3714
return (
3380
isinstance(other, _UnaryExpression) and
3715
isinstance(other, UnaryExpression) and
3381
3716
self.operator == other.operator and
3382
3717
self.modifier == other.modifier and
3383
3718
self.element.compare(other.element, **kw)
3385
3720
3386
3721
def _negate(self):
3387
3722
if self.negate is not None:
3388
return _UnaryExpression(
3723
return UnaryExpression(
3389
3724
self.element,
3390
3725
operator=self.negate,
3391
3726
negate=self.operator,
3392
3727
modifier=self.modifier,
3393
3728
type_=self.type)
3394
3729
else:
3395
return super(_UnaryExpression, self)._negate()
3730
return super(UnaryExpression, self)._negate()
3396
3731
3397
3732
def self_group(self, against=None):
3398
3733
if self.operator and operators.is_precedent(self.operator,
3399
3734
against):
3400
return _Grouping(self)
3735
return Grouping(self)
3401
3736
else:
3402
3737
return self
3403
3738
3404
3739
3405
class _BinaryExpression(ColumnElement):
3406
"""Represent an expression that is ``LEFT <operator> RIGHT``."""
3740
class BinaryExpression(ColumnElement):
3741
"""Represent an expression that is ``LEFT <operator> RIGHT``.
3742
3743
A :class:`.BinaryExpression` is generated automatically
3744
whenever two column expressions are used in a Python binary expresion::
3745
3746
>>> from sqlalchemy.sql import column
3747
>>> column('a') + column('b')
3748
<sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
3749
>>> print column('a') + column('b')
3750
a + b
3751
3752
"""
3407
3753
3408
3754
__visit_name__ = 'binary'
3409
3755
3410
3756
def __init__(self, left, right, operator, type_=None,
3411
3757
negate=None, modifiers=None):
3758
# allow compatibility with libraries that
3759
# refer to BinaryExpression directly and pass strings
3760
if isinstance(operator, basestring):
3761
operator = operators.custom_op(operator)
3762
self._orig = (left, right)
3412
3763
self.left = _literal_as_text(left).self_group(against=operator)
3413
3764
self.right = _literal_as_text(right).self_group(against=operator)
3414
3765
self.operator = operator
3415
3766
self.type = sqltypes.to_instance(type_)
3416
3767
self.negate = negate
3768
3417
3769
if modifiers is None:
3418
3770
self.modifiers = {}
3419
3771
else:
3420
3772
self.modifiers = modifiers
3421
3773
3422
3774
def __nonzero__(self):
3423
try:
3424
return self.operator(hash(self.left), hash(self.right))
3425
except:
3775
if self.operator in (operator.eq, operator.ne):
3776
return self.operator(hash(self._orig[0]), hash(self._orig[1]))
3777
else:
3426
3778
raise TypeError("Boolean value of this clause is not defined")
3427
3779
3428
3780
@property
3781
def is_comparison(self):
3782
return operators.is_comparison(self.operator)
3783
3784
@property
3429
3785
def _from_objects(self):
3430
3786
return self.left._from_objects + self.right._from_objects
3431
3787
3437
3793
return self.left, self.right
3438
3794
3439
3795
def compare(self, other, **kw):
3440
"""Compare this :class:`_BinaryExpression` against the
3441
given :class:`_BinaryExpression`."""
3796
"""Compare this :class:`BinaryExpression` against the
3797
given :class:`BinaryExpression`."""
3442
3798
3443
3799
return (
3444
isinstance(other, _BinaryExpression) and
3800
isinstance(other, BinaryExpression) and
3445
3801
self.operator == other.operator and
3446
3802
(
3447
3803
self.left.compare(other.left, **kw) and
3456
3812
3457
3813
def self_group(self, against=None):
3458
3814
if operators.is_precedent(self.operator, against):
3459
return _Grouping(self)
3815
return Grouping(self)
3460
3816
else:
3461
3817
return self
3462
3818
3463
3819
def _negate(self):
3464
3820
if self.negate is not None:
3465
return _BinaryExpression(
3821
return BinaryExpression(
3466
3822
self.left,
3467
3823
self.right,
3468
3824
self.negate,
3470
3826
type_=sqltypes.BOOLEANTYPE,
3471
3827
modifiers=self.modifiers)
3472
3828
else:
3473
return super(_BinaryExpression, self)._negate()
3474
3475
class _Exists(_UnaryExpression):
3476
__visit_name__ = _UnaryExpression.__visit_name__
3829
return super(BinaryExpression, self)._negate()
3830
3831
3832
class Exists(UnaryExpression):
3833
__visit_name__ = UnaryExpression.__visit_name__
3477
3834
_from_objects = []
3478
3835
3479
3836
def __init__(self, *args, **kwargs):
3480
if args and isinstance(args[0], (_SelectBase, _ScalarSelect)):
3837
if args and isinstance(args[0], (SelectBase, ScalarSelect)):
3481
3838
s = args[0]
3482
3839
else:
3483
3840
if not args:
3484
3841
args = ([literal_column('*')],)
3485
3842
s = select(*args, **kwargs).as_scalar().self_group()
3486
3843
3487
_UnaryExpression.__init__(self, s, operator=operators.exists,
3844
UnaryExpression.__init__(self, s, operator=operators.exists,
3488
3845
type_=sqltypes.Boolean)
3489
3846
3490
3847
def select(self, whereclause=None, **params):
3491
3848
return select([self], whereclause, **params)
3492
3849
3493
def correlate(self, fromclause):
3494
e = self._clone()
3495
e.element = self.element.correlate(fromclause).self_group()
3850
def correlate(self, *fromclause):
3851
e = self._clone()
3852
e.element = self.element.correlate(*fromclause).self_group()
3853
return e
3854
3855
def correlate_except(self, *fromclause):
3856
e = self._clone()
3857
e.element = self.element.correlate_except(*fromclause).self_group()
3496
3858
return e
3497
3859
3498
3860
def select_from(self, clause):
3499
"""return a new :class:`._Exists` construct, applying the given expression
3500
to the :meth:`.Select.select_from` method of the select statement
3501
contained.
3861
"""return a new :class:`.Exists` construct, applying the given
3862
expression to the :meth:`.Select.select_from` method of the select
3863
statement contained.
3502
3864
3503
3865
"""
3504
3866
e = self._clone()
3514
3876
e.element = self.element.where(clause).self_group()
3515
3877
return e
3516
3878
3879
3517
3880
class Join(FromClause):
3518
3881
"""represent a ``JOIN`` construct between two :class:`.FromClause`
3519
3882
elements.
3533
3896
:class:`.FromClause` object.
3534
3897
3535
3898
"""
3536
self.left = _literal_as_text(left)
3537
self.right = _literal_as_text(right).self_group()
3899
self.left = _interpret_as_from(left)
3900
self.right = _interpret_as_from(right).self_group()
3538
3901
3539
3902
if onclause is None:
3540
3903
self.onclause = self._match_primaries(self.left, self.right)
3542
3905
self.onclause = onclause
3543
3906
3544
3907
self.isouter = isouter
3545
self.__folded_equivalents = None
3546
3908
3547
3909
@property
3548
3910
def description(self):
3554
3916
3555
3917
def is_derived_from(self, fromclause):
3556
3918
return fromclause is self or \
3557
self.left.is_derived_from(fromclause) or\
3919
self.left.is_derived_from(fromclause) or \
3558
3920
self.right.is_derived_from(fromclause)
3559
3921
3560
3922
def self_group(self, against=None):
3561
return _FromGrouping(self)
3923
return FromGrouping(self)
3562
3924
3563
3925
def _populate_column_collection(self):
3564
3926
columns = [c for c in self.left.columns] + \
3570
3932
self.foreign_keys.update(itertools.chain(
3571
3933
*[col.foreign_keys for col in columns]))
3572
3934
3935
def _refresh_for_new_column(self, column):
3936
col = self.left._refresh_for_new_column(column)
3937
if col is None:
3938
col = self.right._refresh_for_new_column(column)
3939
if col is not None:
3940
if self._cols_populated:
3941
self._columns[col._label] = col
3942
self.foreign_keys.add(col)
3943
if col.primary_key:
3944
self.primary_key.add(col)
3945
return col
3946
return None
3947
3573
3948
def _copy_internals(self, clone=_clone, **kw):
3574
3949
self._reset_exported()
3575
3950
self.left = clone(self.left, **kw)
3576
3951
self.right = clone(self.right, **kw)
3577
3952
self.onclause = clone(self.onclause, **kw)
3578
self.__folded_equivalents = None
3579
3953
3580
3954
def get_children(self, **kwargs):
3581
3955
return self.left, self.right, self.onclause
3587
3961
left_right = None
3588
3962
return sqlutil.join_condition(left, right, a_subset=left_right)
3589
3963
3590
def select(self, whereclause=None, fold_equivalents=False, **kwargs):
3964
def select(self, whereclause=None, **kwargs):
3591
3965
"""Create a :class:`.Select` from this :class:`.Join`.
3592
3966
3593
3967
The equivalent long-hand form, given a :class:`.Join` object
3601
3975
:param whereclause: the WHERE criterion that will be sent to
3602
3976
the :func:`select()` function
3603
3977
3604
:param fold_equivalents: based on the join criterion of this
3605
:class:`.Join`, do not include
3606
repeat column names in the column list of the resulting
3607
select, for columns that are calculated to be "equivalent"
3608
based on the join criterion of this :class:`.Join`. This will
3609
recursively apply to any joins directly nested by this one
3610
as well.
3611
3612
3978
:param \**kwargs: all other kwargs are sent to the
3613
3979
underlying :func:`select()` function.
3614
3980
3615
3981
"""
3616
if fold_equivalents:
3617
collist = sqlutil.folded_equivalents(self)
3618
else:
3619
collist = [self.left, self.right]
3982
collist = [self.left, self.right]
3620
3983
3621
3984
return select(collist, whereclause, from_obj=[self], **kwargs)
3622
3985
3666
4029
self.left._from_objects + \
3667
4030
self.right._from_objects
3668
4031
4032
3669
4033
class Alias(FromClause):
3670
4034
"""Represents an table or selectable alias (AS).
3671
4035
3722
4086
for col in self.element.columns:
3723
4087
col._make_proxy(self)
3724
4088
4089
def _refresh_for_new_column(self, column):
4090
col = self.element._refresh_for_new_column(column)
4091
if col is not None:
4092
if not self._cols_populated:
4093
return None
4094
else:
4095
return col._make_proxy(self)
4096
else:
4097
return None
4098
3725
4099
def _copy_internals(self, clone=_clone, **kw):
3726
4100
# don't apply anything to an aliased Table
3727
4101
# for now. May want to drive this from
3749
4123
def bind(self):
3750
4124
return self.element.bind
3751
4125
4126
3752
4127
class CTE(Alias):
3753
4128
"""Represent a Common Table Expression.
3754
4129
3755
4130
The :class:`.CTE` object is obtained using the
3756
:meth:`._SelectBase.cte` method from any selectable.
4131
:meth:`.SelectBase.cte` method from any selectable.
3757
4132
See that method for complete examples.
3758
4133
3759
4134
.. versionadded:: 0.7.6
3776
4151
self.original,
3777
4152
name=name,
3778
4153
recursive=self.recursive,
3779
cte_alias = self.name
4154
cte_alias=self.name
3780
4155
)
3781
4156
3782
4157
def union(self, other):
3796
4171
)
3797
4172
3798
4173
3799
class _Grouping(ColumnElement):
4174
class Grouping(ColumnElement):
3800
4175
"""Represent a grouping within a column expression"""
3801
4176
3802
4177
__visit_name__ = 'grouping'
3803
4178
3804
4179
def __init__(self, element):
3805
4180
self.element = element
3806
self.type = getattr(element, 'type', None)
4181
self.type = getattr(element, 'type', sqltypes.NULLTYPE)
3807
4182
3808
4183
@property
3809
4184
def _label(self):
3823
4198
return getattr(self.element, attr)
3824
4199
3825
4200
def __getstate__(self):
3826
return {'element':self.element, 'type':self.type}
4201
return {'element': self.element, 'type': self.type}
3827
4202
3828
4203
def __setstate__(self, state):
3829
4204
self.element = state['element']
3830
4205
self.type = state['type']
3831
4206
3832
class _FromGrouping(FromClause):
4207
def compare(self, other, **kw):
4208
return isinstance(other, Grouping) and \
4209
self.element.compare(other.element)
4210
4211
4212
class FromGrouping(FromClause):
3833
4213
"""Represent a grouping of a FROM clause"""
3834
4214
__visit_name__ = 'grouping'
3835
4215
3872
4252
return getattr(self.element, attr)
3873
4253
3874
4254
def __getstate__(self):
3875
return {'element':self.element}
4255
return {'element': self.element}
3876
4256
3877
4257
def __setstate__(self, state):
3878
4258
self.element = state['element']
3879
4259
3880
class _Over(ColumnElement):
4260
4261
class Over(ColumnElement):
3881
4262
"""Represent an OVER clause.
3882
4263
3883
4264
This is a special operator against a so-called
3923
4304
if c is not None]
3924
4305
))
3925
4306
3926
class _Label(ColumnElement):
4307
4308
class Label(ColumnElement):
3927
4309
"""Represents a column label (AS).
3928
4310
3929
4311
Represent a label, as typically applied to any column-level
3938
4320
__visit_name__ = 'label'
3939
4321
3940
4322
def __init__(self, name, element, type_=None):
3941
while isinstance(element, _Label):
4323
while isinstance(element, Label):
3942
4324
element = element.element
3943
4325
if name:
3944
4326
self.name = name
3949
4331
self._element = element
3950
4332
self._type = type_
3951
4333
self.quote = element.quote
3952
self.proxies = [element]
4334
self._proxies = [element]
3953
4335
3954
4336
@util.memoized_property
3955
4337
def type(self):
3964
4346
def self_group(self, against=None):
3965
4347
sub_element = self._element.self_group(against=against)
3966
4348
if sub_element is not self._element:
3967
return _Label(self.name,
4349
return Label(self.name,
3968
4350
sub_element,
3969
4351
type_=self._type)
3970
4352
else:
3988
4370
def _from_objects(self):
3989
4371
return self.element._from_objects
3990
4372
3991
def _make_proxy(self, selectable, name = None):
3992
e = self.element._make_proxy(selectable, name=name or self.name)
3993
e.proxies.append(self)
4373
def _make_proxy(self, selectable, name=None, **kw):
4374
e = self.element._make_proxy(selectable,
4375
name=name if name else self.name)
4376
e._proxies.append(self)
4377
if self._type is not None:
4378
e.type = self._type
3994
4379
return e
3995
4380
3996
class ColumnClause(_Immutable, ColumnElement):
4381
4382
class ColumnClause(Immutable, ColumnElement):
3997
4383
"""Represents a generic column expression from any textual string.
3998
4384
3999
4385
This includes columns associated with tables, aliases and select
4049
4435
:func:`literal_column()` function is usually used to create such a
4050
4436
:class:`.ColumnClause`.
4051
4437
4438
4052
4439
"""
4053
4440
__visit_name__ = 'column'
4054
4441
4075
4462
4076
4463
def _get_table(self):
4077
4464
return self.__dict__['table']
4465
4078
4466
def _set_table(self, table):
4079
4467
self._memoized_property.expire_instance(self)
4080
4468
self.__dict__['table'] = table
4134
4522
else:
4135
4523
return name
4136
4524
4137
def label(self, name):
4138
# currently, anonymous labels don't occur for
4139
# ColumnClause. The use at the moment
4140
# is that they do not generate nicely for
4141
# is_literal clauses. We would like to change
4142
# this so that label(None) acts as would be expected.
4143
# See [ticket:2168].
4144
if name is None:
4145
return self
4146
else:
4147
return super(ColumnClause, self).label(name)
4148
4149
4150
4525
def _bind_param(self, operator, obj):
4151
return _BindParamClause(self.name, obj,
4526
return BindParameter(self.name, obj,
4152
4527
_compared_to_operator=operator,
4153
4528
_compared_to_type=self.type,
4154
4529
unique=True)
4155
4530
4156
def _make_proxy(self, selectable, name=None, attach=True):
4531
def _make_proxy(self, selectable, name=None, attach=True,
4532
name_is_truncatable=False, **kw):
4157
4533
# propagate the "is_literal" flag only if we are keeping our name,
4158
4534
# otherwise its considered to be a label
4159
4535
is_literal = self.is_literal and (name is None or name == self.name)
4160
4536
c = self._constructor(
4161
_as_truncated(name or self.name),
4537
_as_truncated(name or self.name) if \
4538
name_is_truncatable else \
4539
(name or self.name),
4162
4540
selectable=selectable,
4163
4541
type_=self.type,
4164
4542
is_literal=is_literal
4165
4543
)
4166
c.proxies = [self]
4544
if name is None:
4545
c.key = self.key
4546
c._proxies = [self]
4167
4547
if selectable._is_clone_of is not None:
4168
4548
c._is_clone_of = \
4169
selectable._is_clone_of.columns.get(c.name)
4549
selectable._is_clone_of.columns.get(c.key)
4170
4550
4171
4551
if attach:
4172
selectable._columns[c.name] = c
4552
selectable._columns[c.key] = c
4173
4553
return c
4174
4554
4175
class TableClause(_Immutable, FromClause):
4555
4556
class TableClause(Immutable, FromClause):
4176
4557
"""Represents a minimal "table" construct.
4177
4558
4178
4559
The constructor for :class:`.TableClause` is the
4198
4579
of :class:`~.schema.Table`, including constraints, references to other
4199
4580
tables, or support for :class:`.MetaData`-level services. It's useful
4200
4581
on its own as an ad-hoc construct used to generate quick SQL
4201
statements when a more fully fledged :class:`~.schema.Table` is not on hand.
4582
statements when a more fully fledged :class:`~.schema.Table`
4583
is not on hand.
4202
4584
4203
4585
"""
4204
4586
4206
4588
4207
4589
named_with_column = True
4208
4590
4591
implicit_returning = False
4592
""":class:`.TableClause` doesn't support having a primary key or column
4593
-level defaults, so implicit returning doesn't apply."""
4594
4595
_autoincrement_column = None
4596
"""No PK or default support so no autoincrement column."""
4597
4209
4598
def __init__(self, name, *columns):
4210
4599
super(TableClause, self).__init__()
4211
4600
self.name = self.fullname = name
4227
4616
# end Py2K
4228
4617
4229
4618
def append_column(self, c):
4230
self._columns[c.name] = c
4619
self._columns[c.key] = c
4231
4620
c.table = self
4232
4621
4233
4622
def get_children(self, column_collections=True, **kwargs):
4297
4686
def _from_objects(self):
4298
4687
return [self]
4299
4688
4300
class _SelectBase(Executable, FromClause):
4689
4690
class SelectBase(Executable, FromClause):
4301
4691
"""Base class for :class:`.Select` and ``CompoundSelects``."""
4302
4692
4303
4693
_order_by_clause = ClauseList()
4321
4711
'deprecated. Use .execution_options(a'
4322
4712
'utocommit=True)')
4323
4713
self._execution_options = \
4324
self._execution_options.union({'autocommit'
4325
: autocommit})
4714
self._execution_options.union(
4715
{'autocommit': autocommit})
4326
4716
if limit is not None:
4327
4717
self._limit = util.asint(limit)
4328
4718
if offset is not None:
4342
4732
clause is eligible to be used as a scalar expression.
4343
4733
4344
4734
The returned object is an instance of
4345
:class:`_ScalarSelect`.
4735
:class:`ScalarSelect`.
4346
4736
4347
4737
"""
4348
return _ScalarSelect(self)
4738
return ScalarSelect(self)
4349
4739
4350
4740
@_generative
4351
4741
def apply_labels(self):
4364
4754
"""return a 'scalar' representation of this selectable, embedded as a
4365
4755
subquery with a label.
4366
4756
4367
See also :meth:`~._SelectBase.as_scalar`.
4757
.. seealso::
4758
4759
:meth:`~.SelectBase.as_scalar`.
4368
4760
4369
4761
"""
4370
4762
return self.as_scalar().label(name)
4472
4864
4473
4865
statement = select([
4474
4866
included_parts.c.sub_part,
4475
func.sum(included_parts.c.quantity).label('total_quantity')
4867
func.sum(included_parts.c.quantity).
4868
label('total_quantity')
4476
4869
]).\
4477
4870
select_from(included_parts.join(parts,
4478
4871
included_parts.c.part==parts.c.part)).\\
4481
4874
result = conn.execute(statement).fetchall()
4482
4875
4483
4876
4484
See also:
4877
.. seealso::
4485
4878
4486
:meth:`.orm.query.Query.cte` - ORM version of :meth:`._SelectBase.cte`.
4879
:meth:`.orm.query.Query.cte` - ORM version of :meth:`.SelectBase.cte`.
4487
4880
4488
4881
"""
4489
4882
return CTE(self, name=name, recursive=recursive)
4552
4945
4553
4946
The criterion will be appended to any pre-existing ORDER BY criterion.
4554
4947
4948
This is an **in-place** mutation method; the
4949
:meth:`~.SelectBase.order_by` method is preferred, as it provides standard
4950
:term:`method chaining`.
4951
4555
4952
"""
4556
4953
if len(clauses) == 1 and clauses[0] is None:
4557
4954
self._order_by_clause = ClauseList()
4565
4962
4566
4963
The criterion will be appended to any pre-existing GROUP BY criterion.
4567
4964
4965
This is an **in-place** mutation method; the
4966
:meth:`~.SelectBase.group_by` method is preferred, as it provides standard
4967
:term:`method chaining`.
4968
4568
4969
"""
4569
4970
if len(clauses) == 1 and clauses[0] is None:
4570
4971
self._group_by_clause = ClauseList()
4578
4979
return [self]
4579
4980
4580
4981
4581
class _ScalarSelect(_Grouping):
4982
class ScalarSelect(Generative, Grouping):
4582
4983
_from_objects = []
4583
4984
4584
4985
def __init__(self, element):
4592
4993
'column-level expression.')
4593
4994
c = columns
4594
4995
4996
@_generative
4997
def where(self, crit):
4998
"""Apply a WHERE clause to the SELECT statement referred to
4999
by this :class:`.ScalarSelect`.
5000
5001
"""
5002
self.element = self.element.where(crit)
5003
4595
5004
def self_group(self, **kwargs):
4596
5005
return self
4597
5006
4598
def _make_proxy(self, selectable, name):
4599
return list(self.inner_columns)[0]._make_proxy(selectable, name)
4600
5007
4601
class CompoundSelect(_SelectBase):
5008
class CompoundSelect(SelectBase):
4602
5009
"""Forms the basis of ``UNION``, ``UNION ALL``, and other
4603
5010
SELECT-based set operations."""
4604
5011
4612
5019
INTERSECT_ALL = util.symbol('INTERSECT ALL')
4613
5020
4614
5021
def __init__(self, keyword, *selects, **kwargs):
4615
self._should_correlate = kwargs.pop('correlate', False)
5022
self._auto_correlate = kwargs.pop('correlate', False)
4616
5023
self.keyword = keyword
4617
5024
self.selects = []
4618
5025
4633
5040
4634
5041
self.selects.append(s.self_group(self))
4635
5042
4636
_SelectBase.__init__(self, **kwargs)
5043
SelectBase.__init__(self, **kwargs)
4637
5044
4638
5045
def _scalar_type(self):
4639
5046
return self.selects[0]._scalar_type()
4640
5047
4641
5048
def self_group(self, against=None):
4642
return _FromGrouping(self)
5049
return FromGrouping(self)
4643
5050
4644
5051
def is_derived_from(self, fromclause):
4645
5052
for s in self.selects:
4660
5067
# ForeignKeys in. this would allow the union() to have all
4661
5068
# those fks too.
4662
5069
4663
proxy = cols[0]._make_proxy(self, name=self.use_labels
4664
and cols[0]._label or None)
5070
proxy = cols[0]._make_proxy(self,
5071
name=cols[0]._label if self.use_labels else None,
5072
key=cols[0]._key_label if self.use_labels else None)
4665
5073
4666
# hand-construct the "proxies" collection to include all
5074
# hand-construct the "_proxies" collection to include all
4667
5075
# derived columns place a 'weight' annotation corresponding
4668
5076
# to how low in the list of select()s the column occurs, so
4669
5077
# that the corresponding_column() operation can resolve
4670
5078
# conflicts
4671
5079
4672
proxy.proxies = [c._annotate({'weight': i + 1}) for (i,
5080
proxy._proxies = [c._annotate({'weight': i + 1}) for (i,
4673
5081
c) in enumerate(cols)]
4674
5082
5083
def _refresh_for_new_column(self, column):
5084
for s in self.selects:
5085
s._refresh_for_new_column(column)
5086
5087
if not self._cols_populated:
5088
return None
5089
5090
raise NotImplementedError("CompoundSelect constructs don't support "
5091
"addition of columns to underlying selectables")
5092
4675
5093
def _copy_internals(self, clone=_clone, **kw):
4676
5094
self._reset_exported()
4677
5095
self.selects = [clone(s, **kw) for s in self.selects]
4695
5113
return e
4696
5114
else:
4697
5115
return None
5116
4698
5117
def _set_bind(self, bind):
4699
5118
self._bind = bind
4700
5119
bind = property(bind, _set_bind)
4701
5120
4702
class Select(_SelectBase):
5121
5122
class HasPrefixes(object):
5123
_prefixes = ()
5124
5125
@_generative
5126
def prefix_with(self, *expr, **kw):
5127
"""Add one or more expressions following the statement keyword, i.e.
5128
SELECT, INSERT, UPDATE, or DELETE. Generative.
5129
5130
This is used to support backend-specific prefix keywords such as those
5131
provided by MySQL.
5132
5133
E.g.::
5134
5135
stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
5136
5137
Multiple prefixes can be specified by multiple calls
5138
to :meth:`.prefix_with`.
5139
5140
:param \*expr: textual or :class:`.ClauseElement` construct which
5141
will be rendered following the INSERT, UPDATE, or DELETE
5142
keyword.
5143
:param \**kw: A single keyword 'dialect' is accepted. This is an
5144
optional string dialect name which will
5145
limit rendering of this prefix to only that dialect.
5146
5147
"""
5148
dialect = kw.pop('dialect', None)
5149
if kw:
5150
raise exc.ArgumentError("Unsupported argument(s): %s" %
5151
",".join(kw))
5152
self._setup_prefixes(expr, dialect)
5153
5154
def _setup_prefixes(self, prefixes, dialect=None):
5155
self._prefixes = self._prefixes + tuple(
5156
[(_literal_as_text(p), dialect) for p in prefixes])
5157
5158
5159
class Select(HasPrefixes, SelectBase):
4703
5160
"""Represents a ``SELECT`` statement.
4704
5161
4705
See also:
4706
4707
:func:`~.expression.select` - the function which creates a :class:`.Select` object.
4708
4709
:ref:`coretutorial_selecting` - Core Tutorial description of :func:`.select`.
5162
.. seealso::
5163
5164
:func:`~.expression.select` - the function which creates
5165
a :class:`.Select` object.
5166
5167
:ref:`coretutorial_selecting` - Core Tutorial description
5168
of :func:`.select`.
4710
5169
4711
5170
"""
4712
5171
4716
5175
_hints = util.immutabledict()
4717
5176
_distinct = False
4718
5177
_from_cloned = None
4719
4720
_memoized_property = _SelectBase._memoized_property
5178
_correlate = ()
5179
_correlate_except = None
5180
_memoized_property = SelectBase._memoized_property
4721
5181
4722
5182
def __init__(self,
4723
5183
columns,
4735
5195
argument descriptions.
4736
5196
4737
5197
Additional generative and mutator methods are available on the
4738
:class:`_SelectBase` superclass.
5198
:class:`SelectBase` superclass.
4739
5199
4740
5200
"""
4741
self._should_correlate = correlate
5201
self._auto_correlate = correlate
4742
5202
if distinct is not False:
4743
if isinstance(distinct, basestring):
4744
util.warn_deprecated(
4745
"A string argument passed to the 'distinct' "
4746
"keyword argument of 'select()' is deprecated "
4747
"- please use 'prefixes' or 'prefix_with()' "
4748
"to specify additional prefixes")
4749
if prefixes:
4750
prefixes = util.to_list(prefixes) + [distinct]
4751
else:
4752
prefixes = [distinct]
4753
elif distinct is True:
5203
if distinct is True:
4754
5204
self._distinct = True
4755
5205
else:
4756
5206
self._distinct = [
4758
5208
for e in util.to_list(distinct)
4759
5209
]
4760
5210
4761
self._correlate = set()
4762
5211
if from_obj is not None:
4763
5212
self._from_obj = util.OrderedSet(
4764
_literal_as_text(f)
5213
_interpret_as_from(f)
4765
5214
for f in util.to_list(from_obj))
4766
5215
else:
4767
5216
self._from_obj = util.OrderedSet()
4775
5224
if cols_present:
4776
5225
self._raw_columns = []
4777
5226
for c in columns:
4778
c = _literal_as_column(c)
4779
if isinstance(c, _ScalarSelect):
5227
c = _interpret_as_column_or_from(c)
5228
if isinstance(c, ScalarSelect):
4780
5229
c = c.self_group(against=operators.comma_op)
4781
5230
self._raw_columns.append(c)
4782
5231
else:
4793
5242
self._having = None
4794
5243
4795
5244
if prefixes:
4796
self._prefixes = tuple([_literal_as_text(p) for p in prefixes])
5245
self._setup_prefixes(prefixes)
4797
5246
4798
_SelectBase.__init__(self, **kwargs)
5247
SelectBase.__init__(self, **kwargs)
4799
5248
4800
5249
@property
4801
5250
def _froms(self):
4806
5255
froms = []
4807
5256
seen = set()
4808
5257
translate = self._from_cloned
5258
4809
5259
def add(items):
4810
5260
for item in items:
4811
5261
if translate and item in translate:
4821
5271
4822
5272
return froms
4823
5273
4824
def _get_display_froms(self, existing_froms=None):
5274
def _get_display_froms(self, explicit_correlate_froms=None,
5275
implicit_correlate_froms=None):
4825
5276
"""Return the full list of 'from' clauses to be displayed.
4826
5277
4827
5278
Takes into account a set of existing froms which may be
4832
5283
"""
4833
5284
froms = self._froms
4834
5285
4835
toremove = set(itertools.chain(*[f._hide_froms for f in froms]))
5286
toremove = set(itertools.chain(*[
5287
_expand_cloned(f._hide_froms)
5288
for f in froms]))
4836
5289
if toremove:
4837
5290
# if we're maintaining clones of froms,
4838
5291
# add the copies out to the toremove list. only include
4847
5300
# using a list to maintain ordering
4848
5301
froms = [f for f in froms if f not in toremove]
4849
5302
4850
if len(froms) > 1 or self._correlate:
4851
if self._correlate:
4852
froms = [f for f in froms if f not in _cloned_intersection(froms,
4853
self._correlate)]
4854
if self._should_correlate and existing_froms:
4855
froms = [f for f in froms if f not in _cloned_intersection(froms,
4856
existing_froms)]
4857
4858
if not len(froms):
4859
raise exc.InvalidRequestError("Select statement '%s"
4860
"' returned no FROM clauses due to "
4861
"auto-correlation; specify "
4862
"correlate(<tables>) to control "
4863
"correlation manually." % self)
5303
if self._correlate:
5304
to_correlate = self._correlate
5305
if to_correlate:
5306
froms = [
5307
f for f in froms if f not in
5308
_cloned_intersection(
5309
_cloned_intersection(froms, explicit_correlate_froms or ()),
5310
to_correlate
5311
)
5312
]
5313
5314
if self._correlate_except is not None:
5315
5316
froms = [
5317
f for f in froms if f not in
5318
_cloned_difference(
5319
_cloned_intersection(froms, explicit_correlate_froms or ()),
5320
self._correlate_except
5321
)
5322
]
5323
5324
if self._auto_correlate and \
5325
implicit_correlate_froms and \
5326
len(froms) > 1:
5327
5328
froms = [
5329
f for f in froms if f not in
5330
_cloned_intersection(froms, implicit_correlate_froms)
5331
]
5332
5333
if not len(froms):
5334
raise exc.InvalidRequestError("Select statement '%s"
5335
"' returned no FROM clauses due to "
5336
"auto-correlation; specify "
5337
"correlate(<tables>) to control "
5338
"correlation manually." % self)
4864
5339
4865
5340
return froms
4866
5341
4905
5380
with_hint(mytable, "WITH INDEX ix_mytable", 'sybase')
4906
5381
4907
5382
"""
4908
self._hints = self._hints.union({(selectable, dialect_name):text})
5383
self._hints = self._hints.union(
5384
{(selectable, dialect_name): text})
4909
5385
4910
5386
@property
4911
5387
def type(self):
5004
5480
"""
5005
5481
self.append_column(column)
5006
5482
5483
def reduce_columns(self, only_synonyms=True):
5484
"""Return a new :func`.select` construct with redundantly
5485
named, equivalently-valued columns removed from the columns clause.
5486
5487
"Redundant" here means two columns where one refers to the
5488
other either based on foreign key, or via a simple equality
5489
comparison in the WHERE clause of the statement. The primary purpose
5490
of this method is to automatically construct a select statement
5491
with all uniquely-named columns, without the need to use
5492
table-qualified labels as :meth:`.apply_labels` does.
5493
5494
When columns are omitted based on foreign key, the referred-to
5495
column is the one that's kept. When columns are omitted based on
5496
WHERE eqivalence, the first column in the columns clause is the
5497
one that's kept.
5498
5499
:param only_synonyms: when True, limit the removal of columns
5500
to those which have the same name as the equivalent. Otherwise,
5501
all columns that are equivalent to another are removed.
5502
5503
.. versionadded:: 0.8
5504
5505
"""
5506
return self.with_only_columns(
5507
sqlutil.reduce_columns(
5508
self.inner_columns,
5509
only_synonyms=only_synonyms,
5510
*(self._whereclause, ) + tuple(self._from_obj)
5511
)
5512
)
5513
5007
5514
@_generative
5008
5515
def with_only_columns(self, columns):
5009
5516
"""Return a new :func:`.select` construct with its columns
5058
5565
:meth:`.Select.select_from`::
5059
5566
5060
5567
>>> s1 = select([table1.c.a, table2.c.b]).\\
5061
... select_from(table1.join(table2, table1.c.a==table2.c.a))
5568
... select_from(table1.join(table2,
5569
... table1.c.a==table2.c.a))
5062
5570
>>> s2 = s1.with_only_columns([table2.c.b])
5063
5571
>>> print s2
5064
5572
SELECT t2.b FROM t1 JOIN t2 ON t1.a=t2.a
5094
5602
self._reset_exported()
5095
5603
rc = []
5096
5604
for c in columns:
5097
c = _literal_as_column(c)
5098
if isinstance(c, _ScalarSelect):
5605
c = _interpret_as_column_or_from(c)
5606
if isinstance(c, ScalarSelect):
5099
5607
c = c.self_group(against=operators.comma_op)
5100
5608
rc.append(c)
5101
5609
self._raw_columns = rc
5137
5645
self._distinct = True
5138
5646
5139
5647
@_generative
5140
def prefix_with(self, *expr):
5141
"""return a new select() construct which will apply the given
5142
expressions, typically strings, to the start of its columns clause,
5143
not using any commas. In particular is useful for MySQL
5144
keywords.
5145
5146
e.g.::
5147
5148
select(['a', 'b']).prefix_with('HIGH_PRIORITY',
5149
'SQL_SMALL_RESULT',
5150
'ALL')
5151
5152
Would render::
5153
5154
SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL a, b
5155
5156
"""
5157
expr = tuple(_literal_as_text(e) for e in expr)
5158
self._prefixes = self._prefixes + expr
5159
5160
@_generative
5161
5648
def select_from(self, fromclause):
5162
"""return a new :func:`.select` construct with the given FROM expression
5649
"""return a new :func:`.select` construct with the
5650
given FROM expression
5163
5651
merged into its list of FROM objects.
5164
5652
5165
5653
E.g.::
5176
5664
will have no effect. Passing a :class:`.Join` that refers
5177
5665
to an already present :class:`.Table` or other selectable will have
5178
5666
the effect of concealing the presence of that selectable as
5179
an individual element in the rendered FROM list, instead rendering it into a
5180
JOIN clause.
5667
an individual element in the rendered FROM list, instead
5668
rendering it into a JOIN clause.
5181
5669
5182
While the typical purpose of :meth:`.Select.select_from` is to replace
5183
the default, derived FROM clause with a join, it can also be called with
5184
individual table elements, multiple times if desired, in the case that the
5185
FROM clause cannot be fully derived from the columns clause::
5670
While the typical purpose of :meth:`.Select.select_from` is to
5671
replace the default, derived FROM clause with a join, it can
5672
also be called with individual table elements, multiple times
5673
if desired, in the case that the FROM clause cannot be fully
5674
derived from the columns clause::
5186
5675
5187
5676
select([func.count('*')]).select_from(table1)
5188
5677
5191
5680
5192
5681
@_generative
5193
5682
def correlate(self, *fromclauses):
5194
"""return a new select() construct which will correlate the given FROM
5195
clauses to that of an enclosing select(), if a match is found.
5196
5197
By "match", the given fromclause must be present in this select's
5198
list of FROM objects and also present in an enclosing select's list of
5199
FROM objects.
5200
5201
Calling this method turns off the select's default behavior of
5202
"auto-correlation". Normally, select() auto-correlates all of its FROM
5203
clauses to those of an embedded select when compiled.
5204
5205
If the fromclause is None, correlation is disabled for the returned
5206
select().
5207
5208
"""
5209
self._should_correlate = False
5210
if fromclauses and fromclauses[0] is None:
5211
self._correlate = set()
5212
else:
5213
self._correlate = self._correlate.union(fromclauses)
5683
"""return a new :class:`.Select` which will correlate the given FROM
5684
clauses to that of an enclosing :class:`.Select`.
5685
5686
Calling this method turns off the :class:`.Select` object's
5687
default behavior of "auto-correlation". Normally, FROM elements
5688
which appear in a :class:`.Select` that encloses this one via
5689
its :term:`WHERE clause`, ORDER BY, HAVING or
5690
:term:`columns clause` will be omitted from this :class:`.Select`
5691
object's :term:`FROM clause`.
5692
Setting an explicit correlation collection using the
5693
:meth:`.Select.correlate` method provides a fixed list of FROM objects
5694
that can potentially take place in this process.
5695
5696
When :meth:`.Select.correlate` is used to apply specific FROM clauses
5697
for correlation, the FROM elements become candidates for
5698
correlation regardless of how deeply nested this :class:`.Select`
5699
object is, relative to an enclosing :class:`.Select` which refers to
5700
the same FROM object. This is in contrast to the behavior of
5701
"auto-correlation" which only correlates to an immediate enclosing
5702
:class:`.Select`. Multi-level correlation ensures that the link
5703
between enclosed and enclosing :class:`.Select` is always via
5704
at least one WHERE/ORDER BY/HAVING/columns clause in order for
5705
correlation to take place.
5706
5707
If ``None`` is passed, the :class:`.Select` object will correlate
5708
none of its FROM entries, and all will render unconditionally
5709
in the local FROM clause.
5710
5711
:param \*fromclauses: a list of one or more :class:`.FromClause`
5712
constructs, or other compatible constructs (i.e. ORM-mapped
5713
classes) to become part of the correlate collection.
5714
5715
.. versionchanged:: 0.8.0 ORM-mapped classes are accepted by
5716
:meth:`.Select.correlate`.
5717
5718
.. versionchanged:: 0.8.0 The :meth:`.Select.correlate` method no
5719
longer unconditionally removes entries from the FROM clause; instead,
5720
the candidate FROM entries must also be matched by a FROM entry
5721
located in an enclosing :class:`.Select`, which ultimately encloses
5722
this one as present in the WHERE clause, ORDER BY clause, HAVING
5723
clause, or columns clause of an enclosing :meth:`.Select`.
5724
5725
.. versionchanged:: 0.8.2 explicit correlation takes place
5726
via any level of nesting of :class:`.Select` objects; in previous
5727
0.8 versions, correlation would only occur relative to the immediate
5728
enclosing :class:`.Select` construct.
5729
5730
.. seealso::
5731
5732
:meth:`.Select.correlate_except`
5733
5734
:ref:`correlated_subqueries`
5735
5736
"""
5737
self._auto_correlate = False
5738
if fromclauses and fromclauses[0] is None:
5739
self._correlate = ()
5740
else:
5741
self._correlate = set(self._correlate).union(
5742
_interpret_as_from(f) for f in fromclauses)
5743
5744
@_generative
5745
def correlate_except(self, *fromclauses):
5746
"""return a new :class:`.Select` which will omit the given FROM
5747
clauses from the auto-correlation process.
5748
5749
Calling :meth:`.Select.correlate_except` turns off the
5750
:class:`.Select` object's default behavior of
5751
"auto-correlation" for the given FROM elements. An element
5752
specified here will unconditionally appear in the FROM list, while
5753
all other FROM elements remain subject to normal auto-correlation
5754
behaviors.
5755
5756
.. versionchanged:: 0.8.2 The :meth:`.Select.correlate_except`
5757
method was improved to fully prevent FROM clauses specified here
5758
from being omitted from the immediate FROM clause of this
5759
:class:`.Select`.
5760
5761
If ``None`` is passed, the :class:`.Select` object will correlate
5762
all of its FROM entries.
5763
5764
.. versionchanged:: 0.8.2 calling ``correlate_except(None)`` will
5765
correctly auto-correlate all FROM clauses.
5766
5767
:param \*fromclauses: a list of one or more :class:`.FromClause`
5768
constructs, or other compatible constructs (i.e. ORM-mapped
5769
classes) to become part of the correlate-exception collection.
5770
5771
.. seealso::
5772
5773
:meth:`.Select.correlate`
5774
5775
:ref:`correlated_subqueries`
5776
5777
"""
5778
5779
self._auto_correlate = False
5780
if fromclauses and fromclauses[0] is None:
5781
self._correlate_except = ()
5782
else:
5783
self._correlate_except = set(self._correlate_except or ()).union(
5784
_interpret_as_from(f) for f in fromclauses)
5214
5785
5215
5786
def append_correlation(self, fromclause):
5216
5787
"""append the given correlation expression to this select()
5217
construct."""
5218
5219
self._should_correlate = False
5220
self._correlate = self._correlate.union([fromclause])
5788
construct.
5789
5790
This is an **in-place** mutation method; the
5791
:meth:`~.Select.correlate` method is preferred, as it provides standard
5792
:term:`method chaining`.
5793
5794
"""
5795
5796
self._auto_correlate = False
5797
self._correlate = set(self._correlate).union(
5798
_interpret_as_from(f) for f in fromclause)
5221
5799
5222
5800
def append_column(self, column):
5223
5801
"""append the given column expression to the columns clause of this
5224
5802
select() construct.
5225
5803
5804
This is an **in-place** mutation method; the
5805
:meth:`~.Select.column` method is preferred, as it provides standard
5806
:term:`method chaining`.
5807
5226
5808
"""
5227
5809
self._reset_exported()
5228
column = _literal_as_column(column)
5810
column = _interpret_as_column_or_from(column)
5229
5811
5230
if isinstance(column, _ScalarSelect):
5812
if isinstance(column, ScalarSelect):
5231
5813
column = column.self_group(against=operators.comma_op)
5232
5814
5233
5815
self._raw_columns = self._raw_columns + [column]
5236
5818
"""append the given columns clause prefix expression to this select()
5237
5819
construct.
5238
5820
5821
This is an **in-place** mutation method; the
5822
:meth:`~.Select.prefix_with` method is preferred, as it provides standard
5823
:term:`method chaining`.
5824
5239
5825
"""
5240
5826
clause = _literal_as_text(clause)
5241
5827
self._prefixes = self._prefixes + (clause,)
5246
5832
5247
5833
The expression will be joined to existing WHERE criterion via AND.
5248
5834
5835
This is an **in-place** mutation method; the
5836
:meth:`~.Select.where` method is preferred, as it provides standard
5837
:term:`method chaining`.
5838
5249
5839
"""
5250
5840
self._reset_exported()
5251
5841
whereclause = _literal_as_text(whereclause)
5261
5851
5262
5852
The expression will be joined to existing HAVING criterion via AND.
5263
5853
5854
This is an **in-place** mutation method; the
5855
:meth:`~.Select.having` method is preferred, as it provides standard
5856
:term:`method chaining`.
5857
5264
5858
"""
5265
5859
if self._having is not None:
5266
5860
self._having = and_(self._having, _literal_as_text(having))
5271
5865
"""append the given FromClause expression to this select() construct's
5272
5866
FROM clause.
5273
5867
5868
This is an **in-place** mutation method; the
5869
:meth:`~.Select.select_from` method is preferred, as it provides standard
5870
:term:`method chaining`.
5871
5274
5872
"""
5275
5873
self._reset_exported()
5276
fromclause = _literal_as_text(fromclause)
5874
fromclause = _interpret_as_from(fromclause)
5277
5875
self._from_obj = self._from_obj.union([fromclause])
5278
5876
5877
5878
@_memoized_property
5879
def _columns_plus_names(self):
5880
if self.use_labels:
5881
names = set()
5882
def name_for_col(c):
5883
if c._label is None:
5884
return (None, c)
5885
name = c._label
5886
if name in names:
5887
name = c.anon_label
5888
else:
5889
names.add(name)
5890
return name, c
5891
5892
return [
5893
name_for_col(c)
5894
for c in util.unique_list(_select_iterables(self._raw_columns))
5895
]
5896
else:
5897
return [
5898
(None, c)
5899
for c in util.unique_list(_select_iterables(self._raw_columns))
5900
]
5901
5279
5902
def _populate_column_collection(self):
5280
for c in self.inner_columns:
5281
if hasattr(c, '_make_proxy'):
5282
c._make_proxy(self,
5283
name=self.use_labels
5284
and c._label or None)
5903
for name, c in self._columns_plus_names:
5904
if not hasattr(c, '_make_proxy'):
5905
continue
5906
if name is None:
5907
key = None
5908
elif self.use_labels:
5909
key = c._key_label
5910
if key is not None and key in self.c:
5911
key = c.anon_label
5912
else:
5913
key = None
5914
5915
c._make_proxy(self, key=key,
5916
name=name,
5917
name_is_truncatable=True)
5918
5919
def _refresh_for_new_column(self, column):
5920
for fromclause in self._froms:
5921
col = fromclause._refresh_for_new_column(column)
5922
if col is not None:
5923
if col in self.inner_columns and self._cols_populated:
5924
our_label = col._key_label if self.use_labels else col.key
5925
if our_label not in self.c:
5926
return col._make_proxy(self,
5927
name=col._label if self.use_labels else None,
5928
key=col._key_label if self.use_labels else None,
5929
name_is_truncatable=True)
5930
return None
5931
return None
5285
5932
5286
5933
def self_group(self, against=None):
5287
5934
"""return a 'grouping' construct as per the ClauseElement
5294
5941
"""
5295
5942
if isinstance(against, CompoundSelect):
5296
5943
return self
5297
return _FromGrouping(self)
5944
return FromGrouping(self)
5298
5945
5299
5946
def union(self, other, **kwargs):
5300
5947
"""return a SQL UNION of this select() construct against the given
5358
6005
self._bind = bind
5359
6006
bind = property(bind, _set_bind)
5360
6007
5361
class UpdateBase(Executable, ClauseElement):
6008
6009
class UpdateBase(HasPrefixes, Executable, ClauseElement):
5362
6010
"""Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements.
5363
6011
5364
6012
"""
5369
6017
Executable._execution_options.union({'autocommit': True})
5370
6018
kwargs = util.immutabledict()
5371
6019
_hints = util.immutabledict()
6020
_prefixes = ()
5372
6021
5373
6022
def _process_colparams(self, parameters):
5374
if isinstance(parameters, (list, tuple)):
5375
pp = {}
5376
for i, c in enumerate(self.table.c):
5377
pp[c.key] = parameters[i]
5378
return pp
6023
def process_single(p):
6024
if isinstance(p, (list, tuple)):
6025
return dict(
6026
(c.key, pval)
6027
for c, pval in zip(self.table.c, p)
6028
)
6029
else:
6030
return p
6031
6032
if isinstance(parameters, (list, tuple)) and \
6033
isinstance(parameters[0], (list, tuple, dict)):
6034
6035
if not self._supports_multi_parameters:
6036
raise exc.InvalidRequestError(
6037
"This construct does not support "
6038
"multiple parameter sets.")
6039
6040
return [process_single(p) for p in parameters], True
5379
6041
else:
5380
return parameters
6042
return process_single(parameters), False
5381
6043
5382
6044
def params(self, *arg, **kw):
5383
6045
"""Set the parameters for the statement.
5403
6065
self._bind = bind
5404
6066
bind = property(bind, _set_bind)
5405
6067
5406
_returning_re = re.compile(r'(?:firebird|postgres(?:ql)?)_returning')
5407
def _process_deprecated_kw(self, kwargs):
5408
for k in list(kwargs):
5409
m = self._returning_re.match(k)
5410
if m:
5411
self._returning = kwargs.pop(k)
5412
util.warn_deprecated(
5413
"The %r argument is deprecated. Please "
5414
"use statement.returning(col1, col2, ...)" % k
5415
)
5416
return kwargs
5417
5418
6068
@_generative
5419
6069
def returning(self, *cols):
5420
6070
"""Add a RETURNING or equivalent clause to this statement.
5457
6107
.. note::
5458
6108
5459
6109
:meth:`.UpdateBase.with_hint` currently applies only to
5460
Microsoft SQL Server. For MySQL INSERT hints, use
5461
:meth:`.Insert.prefix_with`. UPDATE/DELETE hints for
5462
MySQL will be added in a future release.
6110
Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
6111
:meth:`.UpdateBase.prefix_with`.
5463
6112
5464
6113
The text of the hint is rendered in the appropriate
5465
6114
location for the database backend in use, relative
5486
6135
if selectable is None:
5487
6136
selectable = self.table
5488
6137
5489
self._hints = self._hints.union({(selectable, dialect_name):text})
6138
self._hints = self._hints.union(
6139
{(selectable, dialect_name): text})
6140
5490
6141
5491
6142
class ValuesBase(UpdateBase):
5492
"""Supplies support for :meth:`.ValuesBase.values` to INSERT and UPDATE constructs."""
6143
"""Supplies support for :meth:`.ValuesBase.values` to
6144
INSERT and UPDATE constructs."""
5493
6145
5494
6146
__visit_name__ = 'values_base'
5495
6147
5496
def __init__(self, table, values):
6148
_supports_multi_parameters = False
6149
_has_multi_parameters = False
6150
6151
def __init__(self, table, values, prefixes):
5497
6152
self.table = table
5498
self.parameters = self._process_colparams(values)
6153
self.parameters, self._has_multi_parameters = \
6154
self._process_colparams(values)
6155
if prefixes:
6156
self._setup_prefixes(prefixes)
5499
6157
5500
6158
@_generative
5501
6159
def values(self, *args, **kwargs):
5502
"""specify the VALUES clause for an INSERT statement, or the SET
6160
"""specify a fixed VALUES clause for an INSERT statement, or the SET
5503
6161
clause for an UPDATE.
5504
6162
6163
Note that the :class:`.Insert` and :class:`.Update` constructs support
6164
per-execution time formatting of the VALUES and/or SET clauses,
6165
based on the arguments passed to :meth:`.Connection.execute`. However,
6166
the :meth:`.ValuesBase.values` method can be used to "fix" a particular
6167
set of parameters into the statement.
6168
6169
Multiple calls to :meth:`.ValuesBase.values` will produce a new
6170
construct, each one with the parameter list modified to include
6171
the new parameters sent. In the typical case of a single
6172
dictionary of parameters, the newly passed keys will replace
6173
the same keys in the previous construct. In the case of a list-based
6174
"multiple values" construct, each new list of values is extended
6175
onto the existing list of values.
6176
5505
6177
:param \**kwargs: key value pairs representing the string key
5506
6178
of a :class:`.Column` mapped to the value to be rendered into the
5507
6179
VALUES or SET clause::
5510
6182
5511
6183
users.update().where(users.c.id==5).values(name="some name")
5512
6184
5513
:param \*args: A single dictionary can be sent as the first positional
5514
argument. This allows non-string based keys, such as Column
5515
objects, to be used::
5516
5517
users.insert().values({users.c.name : "some name"})
5518
5519
users.update().where(users.c.id==5).values({users.c.name : "some name"})
5520
5521
See also:
6185
:param \*args: Alternatively, a dictionary, tuple or list
6186
of dictionaries or tuples can be passed as a single positional
6187
argument in order to form the VALUES or
6188
SET clause of the statement. The single dictionary form
6189
works the same as the kwargs form::
6190
6191
users.insert().values({"name": "some name"})
6192
6193
If a tuple is passed, the tuple should contain the same number
6194
of columns as the target :class:`.Table`::
6195
6196
users.insert().values((5, "some name"))
6197
6198
The :class:`.Insert` construct also supports multiply-rendered VALUES
6199
construct, for those backends which support this SQL syntax
6200
(SQLite, Postgresql, MySQL). This mode is indicated by passing a list
6201
of one or more dictionaries/tuples::
6202
6203
users.insert().values([
6204
{"name": "some name"},
6205
{"name": "some other name"},
6206
{"name": "yet another name"},
6207
])
6208
6209
In the case of an :class:`.Update`
6210
construct, only the single dictionary/tuple form is accepted,
6211
else an exception is raised. It is also an exception case to
6212
attempt to mix the single-/multiple- value styles together,
6213
either through multiple :meth:`.ValuesBase.values` calls
6214
or by sending a list + kwargs at the same time.
6215
6216
.. note::
6217
6218
Passing a multiple values list is *not* the same
6219
as passing a multiple values list to the :meth:`.Connection.execute`
6220
method. Passing a list of parameter sets to :meth:`.ValuesBase.values`
6221
produces a construct of this form::
6222
6223
INSERT INTO table (col1, col2, col3) VALUES
6224
(col1_0, col2_0, col3_0),
6225
(col1_1, col2_1, col3_1),
6226
...
6227
6228
whereas a multiple list passed to :meth:`.Connection.execute`
6229
has the effect of using the DBAPI
6230
`executemany() <http://www.python.org/dev/peps/pep-0249/#id18>`_
6231
method, which provides a high-performance system of invoking
6232
a single-row INSERT statement many times against a series
6233
of parameter sets. The "executemany" style is supported by
6234
all database backends, as it does not depend on a special SQL
6235
syntax.
6236
6237
.. versionadded:: 0.8
6238
Support for multiple-VALUES INSERT statements.
6239
6240
6241
.. seealso::
5522
6242
5523
6243
:ref:`inserts_and_updates` - SQL Expression
5524
6244
Language Tutorial
5528
6248
:func:`~.expression.update` - produce an ``UPDATE`` statement
5529
6249
5530
6250
"""
6251
if self._has_multi_parameters and kwargs:
6252
raise exc.InvalidRequestError(
6253
"This construct already has multiple parameter sets.")
6254
5531
6255
if args:
6256
if len(args) > 1:
6257
raise exc.ArgumentError(
6258
"Only a single dictionary/tuple or list of "
6259
"dictionaries/tuples is accepted positionally.")
5532
6260
v = args[0]
5533
6261
else:
5534
6262
v = {}
5535
6263
5536
6264
if self.parameters is None:
5537
self.parameters = self._process_colparams(v)
5538
self.parameters.update(kwargs)
6265
self.parameters, self._has_multi_parameters = \
6266
self._process_colparams(v)
5539
6267
else:
5540
self.parameters = self.parameters.copy()
5541
self.parameters.update(self._process_colparams(v))
5542
self.parameters.update(kwargs)
6268
if self._has_multi_parameters:
6269
self.parameters = list(self.parameters)
6270
p, self._has_multi_parameters = self._process_colparams(v)
6271
if not self._has_multi_parameters:
6272
raise exc.ArgumentError(
6273
"Can't mix single-values and multiple values "
6274
"formats in one statement")
6275
6276
self.parameters.extend(p)
6277
else:
6278
self.parameters = self.parameters.copy()
6279
p, self._has_multi_parameters = self._process_colparams(v)
6280
if self._has_multi_parameters:
6281
raise exc.ArgumentError(
6282
"Can't mix single-values and multiple values "
6283
"formats in one statement")
6284
self.parameters.update(p)
6285
6286
if kwargs:
6287
if self._has_multi_parameters:
6288
raise exc.ArgumentError(
6289
"Can't pass kwargs and multiple parameter sets "
6290
"simultaenously")
6291
else:
6292
self.parameters.update(kwargs)
6293
5543
6294
5544
6295
class Insert(ValuesBase):
5545
6296
"""Represent an INSERT construct.
5546
6297
5547
The :class:`.Insert` object is created using the :func:`~.expression.insert()` function.
5548
5549
See also:
5550
5551
:ref:`coretutorial_insert_expressions`
6298
The :class:`.Insert` object is created using the
6299
:func:`~.expression.insert()` function.
6300
6301
.. seealso::
6302
6303
:ref:`coretutorial_insert_expressions`
5552
6304
5553
6305
"""
5554
6306
__visit_name__ = 'insert'
5555
6307
5556
_prefixes = ()
6308
_supports_multi_parameters = True
5557
6309
5558
6310
def __init__(self,
5559
6311
table,
5563
6315
prefixes=None,
5564
6316
returning=None,
5565
6317
**kwargs):
5566
ValuesBase.__init__(self, table, values)
6318
ValuesBase.__init__(self, table, values, prefixes)
5567
6319
self._bind = bind
5568
6320
self.select = None
5569
6321
self.inline = inline
5570
6322
self._returning = returning
5571
if prefixes:
5572
self._prefixes = tuple([_literal_as_text(p) for p in prefixes])
5573
5574
if kwargs:
5575
self.kwargs = self._process_deprecated_kw(kwargs)
6323
self.kwargs = kwargs
5576
6324
5577
6325
def get_children(self, **kwargs):
5578
6326
if self.select is not None:
5584
6332
# TODO: coverage
5585
6333
self.parameters = self.parameters.copy()
5586
6334
5587
@_generative
5588
def prefix_with(self, clause):
5589
"""Add a word or expression between INSERT and INTO. Generative.
5590
5591
If multiple prefixes are supplied, they will be separated with
5592
spaces.
5593
5594
"""
5595
clause = _literal_as_text(clause)
5596
self._prefixes = self._prefixes + (clause,)
5597
6335
5598
6336
class Update(ValuesBase):
5599
6337
"""Represent an Update construct.
5609
6347
values=None,
5610
6348
inline=False,
5611
6349
bind=None,
6350
prefixes=None,
5612
6351
returning=None,
5613
6352
**kwargs):
5614
ValuesBase.__init__(self, table, values)
6353
ValuesBase.__init__(self, table, values, prefixes)
5615
6354
self._bind = bind
5616
6355
self._returning = returning
5617
6356
if whereclause is not None:
5619
6358
else:
5620
6359
self._whereclause = None
5621
6360
self.inline = inline
6361
self.kwargs = kwargs
5622
6362
5623
if kwargs:
5624
self.kwargs = self._process_deprecated_kw(kwargs)
5625
6363
5626
6364
def get_children(self, **kwargs):
5627
6365
if self._whereclause is not None:
5661
6399
5662
6400
return froms
5663
6401
6402
5664
6403
class Delete(UpdateBase):
5665
6404
"""Represent a DELETE construct.
5666
6405
5674
6413
table,
5675
6414
whereclause,
5676
6415
bind=None,
5677
returning =None,
6416
returning=None,
6417
prefixes=None,
5678
6418
**kwargs):
5679
6419
self._bind = bind
5680
6420
self.table = table
5681
6421
self._returning = returning
5682
6422
6423
if prefixes:
6424
self._setup_prefixes(prefixes)
6425
5683
6426
if whereclause is not None:
5684
6427
self._whereclause = _literal_as_text(whereclause)
5685
6428
else:
5686
6429
self._whereclause = None
5687
6430
5688
if kwargs:
5689
self.kwargs = self._process_deprecated_kw(kwargs)
6431
self.kwargs = kwargs
5690
6432
5691
6433
def get_children(self, **kwargs):
5692
6434
if self._whereclause is not None:
5708
6450
# TODO: coverage
5709
6451
self._whereclause = clone(self._whereclause, **kw)
5710
6452
6453
5711
6454
class _IdentifiedClause(Executable, ClauseElement):
5712
6455
5713
6456
__visit_name__ = 'identified'
5718
6461
def __init__(self, ident):
5719
6462
self.ident = ident
5720
6463
6464
5721
6465
class SavepointClause(_IdentifiedClause):
5722
6466
__visit_name__ = 'savepoint'
5723
6467
6468
5724
6469
class RollbackToSavepointClause(_IdentifiedClause):
5725
6470
__visit_name__ = 'rollback_to_savepoint'
5726
6471
6472
5727
6473
class ReleaseSavepointClause(_IdentifiedClause):
5728
6474
__visit_name__ = 'release_savepoint'
5729
6475
5730
6476
# old names for compatibility
6477
_BindParamClause = BindParameter
6478
_Label = Label
6479
_SelectBase = SelectBase
6480
_BinaryExpression = BinaryExpression
6481
_Cast = Cast
6482
_Null = Null
6483
_False = False_
6484
_True = True_
6485
_TextClause = TextClause
6486
_UnaryExpression = UnaryExpression
6487
_Case = Case
6488
_Tuple = Tuple
6489
_Over = Over
6490
_Generative = Generative
6491
_TypeClause = TypeClause
6492
_Extract = Extract
6493
_Exists = Exists
6494
_Grouping = Grouping
6495
_FromGrouping = FromGrouping
6496
_ScalarSelect = ScalarSelect
Loggerhead is a web-based interface for Breezy
Version: 2.0.1