1# sql/elements.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8"""Core SQL expression elements, including :class:`_expression.ClauseElement`,
9:class:`_expression.ColumnElement`, and derived classes.
10
11"""
12
13from __future__ import unicode_literals
14
15import itertools
16import operator
17import re
18
19from . import coercions
20from . import operators
21from . import roles
22from . import traversals
23from . import type_api
24from .annotation import Annotated
25from .annotation import SupportsWrappingAnnotations
26from .base import _clone
27from .base import _generative
28from .base import Executable
29from .base import HasMemoized
30from .base import Immutable
31from .base import NO_ARG
32from .base import PARSE_AUTOCOMMIT
33from .base import SingletonConstant
34from .coercions import _document_text_coercion
35from .traversals import HasCopyInternals
36from .traversals import MemoizedHasCacheKey
37from .traversals import NO_CACHE
38from .visitors import cloned_traverse
39from .visitors import InternalTraversal
40from .visitors import traverse
41from .visitors import Traversible
42from .. import exc
43from .. import inspection
44from .. import util
45
46
47def collate(expression, collation):
48 """Return the clause ``expression COLLATE collation``.
49
50 e.g.::
51
52 collate(mycolumn, 'utf8_bin')
53
54 produces::
55
56 mycolumn COLLATE utf8_bin
57
58 The collation expression is also quoted if it is a case sensitive
59 identifier, e.g. contains uppercase characters.
60
61 .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
62 expressions if they are case sensitive.
63
64 """
65
66 expr = coercions.expect(roles.ExpressionElementRole, expression)
67 return BinaryExpression(
68 expr, CollationClause(collation), operators.collate, type_=expr.type
69 )
70
71
72def between(expr, lower_bound, upper_bound, symmetric=False):
73 """Produce a ``BETWEEN`` predicate clause.
74
75 E.g.::
76
77 from sqlalchemy import between
78 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
79
80 Would produce SQL resembling::
81
82 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
83
84 The :func:`.between` function is a standalone version of the
85 :meth:`_expression.ColumnElement.between` method available on all
86 SQL expressions, as in::
87
88 stmt = select(users_table).where(users_table.c.id.between(5, 7))
89
90 All arguments passed to :func:`.between`, including the left side
91 column expression, are coerced from Python scalar values if a
92 the value is not a :class:`_expression.ColumnElement` subclass.
93 For example,
94 three fixed values can be compared as in::
95
96 print(between(5, 3, 7))
97
98 Which would produce::
99
100 :param_1 BETWEEN :param_2 AND :param_3
101
102 :param expr: a column expression, typically a
103 :class:`_expression.ColumnElement`
104 instance or alternatively a Python scalar expression to be coerced
105 into a column expression, serving as the left side of the ``BETWEEN``
106 expression.
107
108 :param lower_bound: a column or Python scalar expression serving as the
109 lower bound of the right side of the ``BETWEEN`` expression.
110
111 :param upper_bound: a column or Python scalar expression serving as the
112 upper bound of the right side of the ``BETWEEN`` expression.
113
114 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
115 that not all databases support this syntax.
116
117 .. versionadded:: 0.9.5
118
119 .. seealso::
120
121 :meth:`_expression.ColumnElement.between`
122
123 """
124 expr = coercions.expect(roles.ExpressionElementRole, expr)
125 return expr.between(lower_bound, upper_bound, symmetric=symmetric)
126
127
128def literal(value, type_=None):
129 r"""Return a literal clause, bound to a bind parameter.
130
131 Literal clauses are created automatically when non-
132 :class:`_expression.ClauseElement` objects (such as strings, ints, dates,
133 etc.) are
134 used in a comparison operation with a :class:`_expression.ColumnElement`
135 subclass,
136 such as a :class:`~sqlalchemy.schema.Column` object. Use this function
137 to force the generation of a literal clause, which will be created as a
138 :class:`BindParameter` with a bound value.
139
140 :param value: the value to be bound. Can be any Python object supported by
141 the underlying DB-API, or is translatable via the given type argument.
142
143 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which
144 will provide bind-parameter translation for this literal.
145
146 """
147 return coercions.expect(roles.LiteralValueRole, value, type_=type_)
148
149
150def outparam(key, type_=None):
151 r"""Create an 'OUT' parameter for usage in functions (stored procedures),
152 for databases which support them.
153
154 The ``outparam`` can be used like a regular function parameter.
155 The "output" value will be available from the
156 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
157 attribute, which returns a dictionary containing the values.
158
159 """
160 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
161
162
163def not_(clause):
164 """Return a negation of the given clause, i.e. ``NOT(clause)``.
165
166 The ``~`` operator is also overloaded on all
167 :class:`_expression.ColumnElement` subclasses to produce the
168 same result.
169
170 """
171 return operators.inv(coercions.expect(roles.ExpressionElementRole, clause))
172
173
174@inspection._self_inspects
175class ClauseElement(
176 roles.SQLRole,
177 SupportsWrappingAnnotations,
178 MemoizedHasCacheKey,
179 HasCopyInternals,
180 Traversible,
181):
182 """Base class for elements of a programmatically constructed SQL
183 expression.
184
185 """
186
187 __visit_name__ = "clause"
188
189 _propagate_attrs = util.immutabledict()
190 """like annotations, however these propagate outwards liberally
191 as SQL constructs are built, and are set up at construction time.
192
193 """
194
195 supports_execution = False
196
197 stringify_dialect = "default"
198
199 _from_objects = []
200 bind = None
201 description = None
202 _is_clone_of = None
203
204 is_clause_element = True
205 is_selectable = False
206 _gen_static_annotations_cache_key = False
207 _is_table = False
208 _is_textual = False
209 _is_from_clause = False
210 _is_returns_rows = False
211 _is_text_clause = False
212 _is_from_container = False
213 _is_select_container = False
214 _is_select_statement = False
215 _is_bind_parameter = False
216 _is_clause_list = False
217 _is_lambda_element = False
218 _is_singleton_constant = False
219 _is_immutable = False
220 _is_star = False
221
222 _order_by_label_element = None
223
224 _cache_key_traversal = None
225
226 def _set_propagate_attrs(self, values):
227 # usually, self._propagate_attrs is empty here. one case where it's
228 # not is a subquery against ORM select, that is then pulled as a
229 # property of an aliased class. should all be good
230
231 # assert not self._propagate_attrs
232
233 self._propagate_attrs = util.immutabledict(values)
234 return self
235
236 def _clone(self, **kw):
237 """Create a shallow copy of this ClauseElement.
238
239 This method may be used by a generative API. Its also used as
240 part of the "deep" copy afforded by a traversal that combines
241 the _copy_internals() method.
242
243 """
244 skip = self._memoized_keys
245 c = self.__class__.__new__(self.__class__)
246
247 if skip:
248 # ensure this iteration remains atomic
249 c.__dict__ = {
250 k: v for k, v in self.__dict__.copy().items() if k not in skip
251 }
252 else:
253 c.__dict__ = self.__dict__.copy()
254
255 # this is a marker that helps to "equate" clauses to each other
256 # when a Select returns its list of FROM clauses. the cloning
257 # process leaves around a lot of remnants of the previous clause
258 # typically in the form of column expressions still attached to the
259 # old table.
260 cc = self._is_clone_of
261 c._is_clone_of = cc if cc is not None else self
262 return c
263
264 def _negate_in_binary(self, negated_op, original_op):
265 """a hook to allow the right side of a binary expression to respond
266 to a negation of the binary expression.
267
268 Used for the special case of expanding bind parameter with IN.
269
270 """
271 return self
272
273 def _with_binary_element_type(self, type_):
274 """in the context of binary expression, convert the type of this
275 object to the one given.
276
277 applies only to :class:`_expression.ColumnElement` classes.
278
279 """
280 return self
281
282 @property
283 def _constructor(self):
284 """return the 'constructor' for this ClauseElement.
285
286 This is for the purposes for creating a new object of
287 this type. Usually, its just the element's __class__.
288 However, the "Annotated" version of the object overrides
289 to return the class of its proxied element.
290
291 """
292 return self.__class__
293
294 @HasMemoized.memoized_attribute
295 def _cloned_set(self):
296 """Return the set consisting all cloned ancestors of this
297 ClauseElement.
298
299 Includes this ClauseElement. This accessor tends to be used for
300 FromClause objects to identify 'equivalent' FROM clauses, regardless
301 of transformative operations.
302
303 """
304 s = util.column_set()
305 f = self
306
307 # note this creates a cycle, asserted in test_memusage. however,
308 # turning this into a plain @property adds tends of thousands of method
309 # calls to Core / ORM performance tests, so the small overhead
310 # introduced by the relatively small amount of short term cycles
311 # produced here is preferable
312 while f is not None:
313 s.add(f)
314 f = f._is_clone_of
315 return s
316
317 @property
318 def entity_namespace(self):
319 raise AttributeError(
320 "This SQL expression has no entity namespace "
321 "with which to filter from."
322 )
323
324 def __getstate__(self):
325 d = self.__dict__.copy()
326 d.pop("_is_clone_of", None)
327 d.pop("_generate_cache_key", None)
328 return d
329
330 def _execute_on_connection(
331 self, connection, multiparams, params, execution_options, _force=False
332 ):
333 if _force or self.supports_execution:
334 return connection._execute_clauseelement(
335 self, multiparams, params, execution_options
336 )
337 else:
338 raise exc.ObjectNotExecutableError(self)
339
340 def unique_params(self, *optionaldict, **kwargs):
341 """Return a copy with :func:`_expression.bindparam` elements
342 replaced.
343
344 Same functionality as :meth:`_expression.ClauseElement.params`,
345 except adds `unique=True`
346 to affected bind parameters so that multiple statements can be
347 used.
348
349 """
350 return self._replace_params(True, optionaldict, kwargs)
351
352 def params(self, *optionaldict, **kwargs):
353 """Return a copy with :func:`_expression.bindparam` elements
354 replaced.
355
356 Returns a copy of this ClauseElement with
357 :func:`_expression.bindparam`
358 elements replaced with values taken from the given dictionary::
359
360 >>> clause = column('x') + bindparam('foo')
361 >>> print(clause.compile().params)
362 {'foo':None}
363 >>> print(clause.params({'foo':7}).compile().params)
364 {'foo':7}
365
366 """
367 return self._replace_params(False, optionaldict, kwargs)
368
369 def _replace_params(self, unique, optionaldict, kwargs):
370
371 if len(optionaldict) == 1:
372 kwargs.update(optionaldict[0])
373 elif len(optionaldict) > 1:
374 raise exc.ArgumentError(
375 "params() takes zero or one positional dictionary argument"
376 )
377
378 def visit_bindparam(bind):
379 if bind.key in kwargs:
380 bind.value = kwargs[bind.key]
381 bind.required = False
382 if unique:
383 bind._convert_to_unique()
384
385 return cloned_traverse(
386 self,
387 {"maintain_key": True, "detect_subquery_cols": True},
388 {"bindparam": visit_bindparam},
389 )
390
391 def compare(self, other, **kw):
392 r"""Compare this :class:`_expression.ClauseElement` to
393 the given :class:`_expression.ClauseElement`.
394
395 Subclasses should override the default behavior, which is a
396 straight identity comparison.
397
398 \**kw are arguments consumed by subclass ``compare()`` methods and
399 may be used to modify the criteria for comparison
400 (see :class:`_expression.ColumnElement`).
401
402 """
403 return traversals.compare(self, other, **kw)
404
405 def self_group(self, against=None):
406 """Apply a 'grouping' to this :class:`_expression.ClauseElement`.
407
408 This method is overridden by subclasses to return a "grouping"
409 construct, i.e. parenthesis. In particular it's used by "binary"
410 expressions to provide a grouping around themselves when placed into a
411 larger expression, as well as by :func:`_expression.select`
412 constructs when placed into the FROM clause of another
413 :func:`_expression.select`. (Note that subqueries should be
414 normally created using the :meth:`_expression.Select.alias` method,
415 as many
416 platforms require nested SELECT statements to be named).
417
418 As expressions are composed together, the application of
419 :meth:`self_group` is automatic - end-user code should never
420 need to use this method directly. Note that SQLAlchemy's
421 clause constructs take operator precedence into account -
422 so parenthesis might not be needed, for example, in
423 an expression like ``x OR (y AND z)`` - AND takes precedence
424 over OR.
425
426 The base :meth:`self_group` method of
427 :class:`_expression.ClauseElement`
428 just returns self.
429 """
430 return self
431
432 def _ungroup(self):
433 """Return this :class:`_expression.ClauseElement`
434 without any groupings.
435 """
436
437 return self
438
439 @util.preload_module("sqlalchemy.engine.default")
440 @util.preload_module("sqlalchemy.engine.url")
441 def compile(self, bind=None, dialect=None, **kw):
442 """Compile this SQL expression.
443
444 The return value is a :class:`~.Compiled` object.
445 Calling ``str()`` or ``unicode()`` on the returned value will yield a
446 string representation of the result. The
447 :class:`~.Compiled` object also can return a
448 dictionary of bind parameter names and values
449 using the ``params`` accessor.
450
451 :param bind: An ``Engine`` or ``Connection`` from which a
452 ``Compiled`` will be acquired. This argument takes precedence over
453 this :class:`_expression.ClauseElement`'s bound engine, if any.
454
455 :param column_keys: Used for INSERT and UPDATE statements, a list of
456 column names which should be present in the VALUES clause of the
457 compiled statement. If ``None``, all columns from the target table
458 object are rendered.
459
460 :param dialect: A ``Dialect`` instance from which a ``Compiled``
461 will be acquired. This argument takes precedence over the `bind`
462 argument as well as this :class:`_expression.ClauseElement`
463 's bound engine,
464 if any.
465
466 :param compile_kwargs: optional dictionary of additional parameters
467 that will be passed through to the compiler within all "visit"
468 methods. This allows any custom flag to be passed through to
469 a custom compilation construct, for example. It is also used
470 for the case of passing the ``literal_binds`` flag through::
471
472 from sqlalchemy.sql import table, column, select
473
474 t = table('t', column('x'))
475
476 s = select(t).where(t.c.x == 5)
477
478 print(s.compile(compile_kwargs={"literal_binds": True}))
479
480 .. versionadded:: 0.9.0
481
482 .. seealso::
483
484 :ref:`faq_sql_expression_string`
485
486 """
487
488 if not dialect:
489 if bind:
490 dialect = bind.dialect
491 elif self.bind:
492 dialect = self.bind.dialect
493 else:
494 if self.stringify_dialect == "default":
495 default = util.preloaded.engine_default
496 dialect = default.StrCompileDialect()
497 else:
498 url = util.preloaded.engine_url
499 dialect = url.URL.create(
500 self.stringify_dialect
501 ).get_dialect()()
502
503 return self._compiler(dialect, **kw)
504
505 def _compile_w_cache(
506 self,
507 dialect,
508 compiled_cache=None,
509 column_keys=None,
510 for_executemany=False,
511 schema_translate_map=None,
512 **kw
513 ):
514 if compiled_cache is not None and dialect._supports_statement_cache:
515 elem_cache_key = self._generate_cache_key()
516 else:
517 elem_cache_key = None
518
519 if elem_cache_key:
520 cache_key, extracted_params = elem_cache_key
521 key = (
522 dialect,
523 cache_key,
524 tuple(column_keys),
525 bool(schema_translate_map),
526 for_executemany,
527 )
528 compiled_sql = compiled_cache.get(key)
529
530 if compiled_sql is None:
531 cache_hit = dialect.CACHE_MISS
532 compiled_sql = self._compiler(
533 dialect,
534 cache_key=elem_cache_key,
535 column_keys=column_keys,
536 for_executemany=for_executemany,
537 schema_translate_map=schema_translate_map,
538 **kw
539 )
540 compiled_cache[key] = compiled_sql
541 else:
542 cache_hit = dialect.CACHE_HIT
543 else:
544 extracted_params = None
545 compiled_sql = self._compiler(
546 dialect,
547 cache_key=elem_cache_key,
548 column_keys=column_keys,
549 for_executemany=for_executemany,
550 schema_translate_map=schema_translate_map,
551 **kw
552 )
553
554 if not dialect._supports_statement_cache:
555 cache_hit = dialect.NO_DIALECT_SUPPORT
556 elif compiled_cache is None:
557 cache_hit = dialect.CACHING_DISABLED
558 else:
559 cache_hit = dialect.NO_CACHE_KEY
560
561 return compiled_sql, extracted_params, cache_hit
562
563 def _compiler(self, dialect, **kw):
564 """Return a compiler appropriate for this ClauseElement, given a
565 Dialect."""
566
567 return dialect.statement_compiler(dialect, self, **kw)
568
569 def __str__(self):
570 if util.py3k:
571 return str(self.compile())
572 else:
573 return unicode(self.compile()).encode( # noqa
574 "ascii", "backslashreplace"
575 ) # noqa
576
577 def __invert__(self):
578 # undocumented element currently used by the ORM for
579 # relationship.contains()
580 if hasattr(self, "negation_clause"):
581 return self.negation_clause
582 else:
583 return self._negate()
584
585 def _negate(self):
586 return UnaryExpression(
587 self.self_group(against=operators.inv), operator=operators.inv
588 )
589
590 def __bool__(self):
591 raise TypeError("Boolean value of this clause is not defined")
592
593 __nonzero__ = __bool__
594
595 def __repr__(self):
596 friendly = self.description
597 if friendly is None:
598 return object.__repr__(self)
599 else:
600 return "<%s.%s at 0x%x; %s>" % (
601 self.__module__,
602 self.__class__.__name__,
603 id(self),
604 friendly,
605 )
606
607
608class ColumnElement(
609 roles.ColumnArgumentOrKeyRole,
610 roles.StatementOptionRole,
611 roles.WhereHavingRole,
612 roles.BinaryElementRole,
613 roles.OrderByRole,
614 roles.ColumnsClauseRole,
615 roles.LimitOffsetRole,
616 roles.DMLColumnRole,
617 roles.DDLConstraintColumnRole,
618 roles.DDLExpressionRole,
619 operators.ColumnOperators,
620 ClauseElement,
621):
622 """Represent a column-oriented SQL expression suitable for usage in the
623 "columns" clause, WHERE clause etc. of a statement.
624
625 While the most familiar kind of :class:`_expression.ColumnElement` is the
626 :class:`_schema.Column` object, :class:`_expression.ColumnElement`
627 serves as the basis
628 for any unit that may be present in a SQL expression, including
629 the expressions themselves, SQL functions, bound parameters,
630 literal expressions, keywords such as ``NULL``, etc.
631 :class:`_expression.ColumnElement`
632 is the ultimate base class for all such elements.
633
634 A wide variety of SQLAlchemy Core functions work at the SQL expression
635 level, and are intended to accept instances of
636 :class:`_expression.ColumnElement` as
637 arguments. These functions will typically document that they accept a
638 "SQL expression" as an argument. What this means in terms of SQLAlchemy
639 usually refers to an input which is either already in the form of a
640 :class:`_expression.ColumnElement` object,
641 or a value which can be **coerced** into
642 one. The coercion rules followed by most, but not all, SQLAlchemy Core
643 functions with regards to SQL expressions are as follows:
644
645 * a literal Python value, such as a string, integer or floating
646 point value, boolean, datetime, ``Decimal`` object, or virtually
647 any other Python object, will be coerced into a "literal bound
648 value". This generally means that a :func:`.bindparam` will be
649 produced featuring the given value embedded into the construct; the
650 resulting :class:`.BindParameter` object is an instance of
651 :class:`_expression.ColumnElement`.
652 The Python value will ultimately be sent
653 to the DBAPI at execution time as a parameterized argument to the
654 ``execute()`` or ``executemany()`` methods, after SQLAlchemy
655 type-specific converters (e.g. those provided by any associated
656 :class:`.TypeEngine` objects) are applied to the value.
657
658 * any special object value, typically ORM-level constructs, which
659 feature an accessor called ``__clause_element__()``. The Core
660 expression system looks for this method when an object of otherwise
661 unknown type is passed to a function that is looking to coerce the
662 argument into a :class:`_expression.ColumnElement` and sometimes a
663 :class:`_expression.SelectBase` expression.
664 It is used within the ORM to
665 convert from ORM-specific objects like mapped classes and
666 mapped attributes into Core expression objects.
667
668 * The Python ``None`` value is typically interpreted as ``NULL``,
669 which in SQLAlchemy Core produces an instance of :func:`.null`.
670
671 A :class:`_expression.ColumnElement` provides the ability to generate new
672 :class:`_expression.ColumnElement`
673 objects using Python expressions. This means that Python operators
674 such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations,
675 and allow the instantiation of further :class:`_expression.ColumnElement`
676 instances
677 which are composed from other, more fundamental
678 :class:`_expression.ColumnElement`
679 objects. For example, two :class:`.ColumnClause` objects can be added
680 together with the addition operator ``+`` to produce
681 a :class:`.BinaryExpression`.
682 Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses
683 of :class:`_expression.ColumnElement`::
684
685 >>> from sqlalchemy.sql import column
686 >>> column('a') + column('b')
687 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
688 >>> print(column('a') + column('b'))
689 a + b
690
691 .. seealso::
692
693 :class:`_schema.Column`
694
695 :func:`_expression.column`
696
697 """
698
699 __visit_name__ = "column_element"
700 primary_key = False
701 foreign_keys = []
702 _proxies = ()
703
704 _tq_label = None
705 """The named label that can be used to target
706 this column in a result set in a "table qualified" context.
707
708 This label is almost always the label used when
709 rendering <expr> AS <label> in a SELECT statement when using
710 the LABEL_STYLE_TABLENAME_PLUS_COL label style, which is what the legacy
711 ORM ``Query`` object uses as well.
712
713 For a regular Column bound to a Table, this is typically the label
714 <tablename>_<columnname>. For other constructs, different rules
715 may apply, such as anonymized labels and others.
716
717 .. versionchanged:: 1.4.21 renamed from ``._label``
718
719 """
720
721 key = None
722 """The 'key' that in some circumstances refers to this object in a
723 Python namespace.
724
725 This typically refers to the "key" of the column as present in the
726 ``.c`` collection of a selectable, e.g. ``sometable.c["somekey"]`` would
727 return a :class:`_schema.Column` with a ``.key`` of "somekey".
728
729 """
730
731 @HasMemoized.memoized_attribute
732 def _tq_key_label(self):
733 """A label-based version of 'key' that in some circumstances refers
734 to this object in a Python namespace.
735
736
737 _tq_key_label comes into play when a select() statement is constructed
738 with apply_labels(); in this case, all Column objects in the ``.c``
739 collection are rendered as <tablename>_<columnname> in SQL; this is
740 essentially the value of ._label. But to locate those columns in the
741 ``.c`` collection, the name is along the lines of <tablename>_<key>;
742 that's the typical value of .key_label.
743
744 .. versionchanged:: 1.4.21 renamed from ``._key_label``
745
746 """
747 return self._proxy_key
748
749 @property
750 def _key_label(self):
751 """legacy; renamed to _tq_key_label"""
752 return self._tq_key_label
753
754 @property
755 def _label(self):
756 """legacy; renamed to _tq_label"""
757 return self._tq_label
758
759 @property
760 def _non_anon_label(self):
761 """the 'name' that naturally applies this element when rendered in
762 SQL.
763
764 Concretely, this is the "name" of a column or a label in a
765 SELECT statement; ``<columnname>`` and ``<labelname>`` below::
766
767 SELECT <columnmame> FROM table
768
769 SELECT column AS <labelname> FROM table
770
771 Above, the two names noted will be what's present in the DBAPI
772 ``cursor.description`` as the names.
773
774 If this attribute returns ``None``, it means that the SQL element as
775 written does not have a 100% fully predictable "name" that would appear
776 in the ``cursor.description``. Examples include SQL functions, CAST
777 functions, etc. While such things do return names in
778 ``cursor.description``, they are only predictable on a
779 database-specific basis; e.g. an expression like ``MAX(table.col)`` may
780 appear as the string ``max`` on one database (like PostgreSQL) or may
781 appear as the whole expression ``max(table.col)`` on SQLite.
782
783 The default implementation looks for a ``.name`` attribute on the
784 object, as has been the precedent established in SQLAlchemy for many
785 years. An exception is made on the ``FunctionElement`` subclass
786 so that the return value is always ``None``.
787
788 .. versionadded:: 1.4.21
789
790
791
792 """
793 return getattr(self, "name", None)
794
795 _render_label_in_columns_clause = True
796 """A flag used by select._columns_plus_names that helps to determine
797 we are actually going to render in terms of "SELECT <col> AS <label>".
798 This flag can be returned as False for some Column objects that want
799 to be rendered as simple "SELECT <col>"; typically columns that don't have
800 any parent table and are named the same as what the label would be
801 in any case.
802
803 """
804
805 _allow_label_resolve = True
806 """A flag that can be flipped to prevent a column from being resolvable
807 by string label name.
808
809 The joined eager loader strategy in the ORM uses this, for example.
810
811 """
812
813 _is_implicitly_boolean = False
814
815 _alt_names = ()
816
817 def self_group(self, against=None):
818 if (
819 against in (operators.and_, operators.or_, operators._asbool)
820 and self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity
821 ):
822 return AsBoolean(self, operators.is_true, operators.is_false)
823 elif against in (operators.any_op, operators.all_op):
824 return Grouping(self)
825 else:
826 return self
827
828 def _negate(self):
829 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity:
830 return AsBoolean(self, operators.is_false, operators.is_true)
831 else:
832 return super(ColumnElement, self)._negate()
833
834 @util.memoized_property
835 def type(self):
836 return type_api.NULLTYPE
837
838 @HasMemoized.memoized_attribute
839 def comparator(self):
840 try:
841 comparator_factory = self.type.comparator_factory
842 except AttributeError as err:
843 util.raise_(
844 TypeError(
845 "Object %r associated with '.type' attribute "
846 "is not a TypeEngine class or object" % self.type
847 ),
848 replace_context=err,
849 )
850 else:
851 return comparator_factory(self)
852
853 def __setstate__(self, state):
854 self.__dict__.update(state)
855
856 def __getattr__(self, key):
857 try:
858 return getattr(self.comparator, key)
859 except AttributeError as err:
860 util.raise_(
861 AttributeError(
862 "Neither %r object nor %r object has an attribute %r"
863 % (
864 type(self).__name__,
865 type(self.comparator).__name__,
866 key,
867 )
868 ),
869 replace_context=err,
870 )
871
872 def operate(self, op, *other, **kwargs):
873 return op(self.comparator, *other, **kwargs)
874
875 def reverse_operate(self, op, other, **kwargs):
876 return op(other, self.comparator, **kwargs)
877
878 def _bind_param(self, operator, obj, type_=None, expanding=False):
879 return BindParameter(
880 None,
881 obj,
882 _compared_to_operator=operator,
883 type_=type_,
884 _compared_to_type=self.type,
885 unique=True,
886 expanding=expanding,
887 )
888
889 @property
890 def expression(self):
891 """Return a column expression.
892
893 Part of the inspection interface; returns self.
894
895 """
896 return self
897
898 @property
899 def _select_iterable(self):
900 return (self,)
901
902 @util.memoized_property
903 def base_columns(self):
904 return util.column_set(c for c in self.proxy_set if not c._proxies)
905
906 @util.memoized_property
907 def proxy_set(self):
908 s = util.column_set([self._deannotate()])
909 for c in self._proxies:
910 s.update(c.proxy_set)
911 return s
912
913 def _uncached_proxy_list(self):
914 """An 'uncached' version of proxy set.
915
916 This is so that we can read annotations from the list of columns
917 without breaking the caching of the above proxy_set.
918
919 """
920 s = [self]
921 for c in self._proxies:
922 s.extend(c._uncached_proxy_list())
923 return s
924
925 def shares_lineage(self, othercolumn):
926 """Return True if the given :class:`_expression.ColumnElement`
927 has a common ancestor to this :class:`_expression.ColumnElement`."""
928
929 return bool(self.proxy_set.intersection(othercolumn.proxy_set))
930
931 def _compare_name_for_result(self, other):
932 """Return True if the given column element compares to this one
933 when targeting within a result row."""
934
935 return (
936 hasattr(other, "name")
937 and hasattr(self, "name")
938 and other.name == self.name
939 )
940
941 @HasMemoized.memoized_attribute
942 def _proxy_key(self):
943 if self._annotations and "proxy_key" in self._annotations:
944 return self._annotations["proxy_key"]
945
946 name = self.key
947 if not name:
948 # there's a bit of a seeming contradiction which is that the
949 # "_non_anon_label" of a column can in fact be an
950 # "_anonymous_label"; this is when it's on a column that is
951 # proxying for an anonymous expression in a subquery.
952 name = self._non_anon_label
953
954 if isinstance(name, _anonymous_label):
955 return None
956 else:
957 return name
958
959 @HasMemoized.memoized_attribute
960 def _expression_label(self):
961 """a suggested label to use in the case that the column has no name,
962 which should be used if possible as the explicit 'AS <label>'
963 where this expression would normally have an anon label.
964
965 this is essentially mostly what _proxy_key does except it returns
966 None if the column has a normal name that can be used.
967
968 """
969
970 if getattr(self, "name", None) is not None:
971 return None
972 elif self._annotations and "proxy_key" in self._annotations:
973 return self._annotations["proxy_key"]
974 else:
975 return None
976
977 def _make_proxy(
978 self, selectable, name=None, key=None, name_is_truncatable=False, **kw
979 ):
980 """Create a new :class:`_expression.ColumnElement` representing this
981 :class:`_expression.ColumnElement` as it appears in the select list of
982 a descending selectable.
983
984 """
985 if name is None:
986 name = self._anon_name_label
987 if key is None:
988 key = self._proxy_key
989 else:
990 key = name
991
992 co = ColumnClause(
993 coercions.expect(roles.TruncatedLabelRole, name)
994 if name_is_truncatable
995 else name,
996 type_=getattr(self, "type", None),
997 _selectable=selectable,
998 )
999
1000 co._propagate_attrs = selectable._propagate_attrs
1001 co._proxies = [self]
1002 if selectable._is_clone_of is not None:
1003 co._is_clone_of = selectable._is_clone_of.columns.get(key)
1004 return key, co
1005
1006 def cast(self, type_):
1007 """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.
1008
1009 This is a shortcut to the :func:`_expression.cast` function.
1010
1011 .. seealso::
1012
1013 :ref:`tutorial_casts`
1014
1015 :func:`_expression.cast`
1016
1017 :func:`_expression.type_coerce`
1018
1019 .. versionadded:: 1.0.7
1020
1021 """
1022 return Cast(self, type_)
1023
1024 def label(self, name):
1025 """Produce a column label, i.e. ``<columnname> AS <name>``.
1026
1027 This is a shortcut to the :func:`_expression.label` function.
1028
1029 If 'name' is ``None``, an anonymous label name will be generated.
1030
1031 """
1032 return Label(name, self, self.type)
1033
1034 def _anon_label(self, seed, add_hash=None):
1035 while self._is_clone_of is not None:
1036 self = self._is_clone_of
1037
1038 # as of 1.4 anonymous label for ColumnElement uses hash(), not id(),
1039 # as the identifier, because a column and its annotated version are
1040 # the same thing in a SQL statement
1041 hash_value = hash(self)
1042
1043 if add_hash:
1044 # this path is used for disambiguating anon labels that would
1045 # otherwise be the same name for the same element repeated.
1046 # an additional numeric value is factored in for each label.
1047
1048 # shift hash(self) (which is id(self), typically 8 byte integer)
1049 # 16 bits leftward. fill extra add_hash on right
1050 assert add_hash < (2 << 15)
1051 assert seed
1052 hash_value = (hash_value << 16) | add_hash
1053
1054 # extra underscore is added for labels with extra hash
1055 # values, to isolate the "deduped anon" namespace from the
1056 # regular namespace. eliminates chance of these
1057 # manufactured hash values overlapping with regular ones for some
1058 # undefined python interpreter
1059 seed = seed + "_"
1060
1061 if isinstance(seed, _anonymous_label):
1062 return _anonymous_label.safe_construct(
1063 hash_value, "", enclosing_label=seed
1064 )
1065
1066 return _anonymous_label.safe_construct(hash_value, seed or "anon")
1067
1068 @util.memoized_property
1069 def _anon_name_label(self):
1070 """Provides a constant 'anonymous label' for this ColumnElement.
1071
1072 This is a label() expression which will be named at compile time.
1073 The same label() is returned each time ``anon_label`` is called so
1074 that expressions can reference ``anon_label`` multiple times,
1075 producing the same label name at compile time.
1076
1077 The compiler uses this function automatically at compile time
1078 for expressions that are known to be 'unnamed' like binary
1079 expressions and function calls.
1080
1081 .. versionchanged:: 1.4.9 - this attribute was not intended to be
1082 public and is renamed to _anon_name_label. anon_name exists
1083 for backwards compat
1084
1085 """
1086 name = getattr(self, "name", None)
1087 return self._anon_label(name)
1088
1089 @util.memoized_property
1090 def _anon_key_label(self):
1091 """Provides a constant 'anonymous key label' for this ColumnElement.
1092
1093 Compare to ``anon_label``, except that the "key" of the column,
1094 if available, is used to generate the label.
1095
1096 This is used when a deduplicating key is placed into the columns
1097 collection of a selectable.
1098
1099 .. versionchanged:: 1.4.9 - this attribute was not intended to be
1100 public and is renamed to _anon_key_label. anon_key_label exists
1101 for backwards compat
1102
1103 """
1104 return self._anon_label(self._proxy_key)
1105
1106 @property
1107 @util.deprecated(
1108 "1.4",
1109 "The :attr:`_expression.ColumnElement.anon_label` attribute is now "
1110 "private, and the public accessor is deprecated.",
1111 )
1112 def anon_label(self):
1113 return self._anon_name_label
1114
1115 @property
1116 @util.deprecated(
1117 "1.4",
1118 "The :attr:`_expression.ColumnElement.anon_key_label` attribute is "
1119 "now private, and the public accessor is deprecated.",
1120 )
1121 def anon_key_label(self):
1122 return self._anon_key_label
1123
1124 def _dedupe_anon_label_idx(self, idx):
1125 """label to apply to a column that is anon labeled, but repeated
1126 in the SELECT, so that we have to make an "extra anon" label that
1127 disambiguates it from the previous appearance.
1128
1129 these labels come out like "foo_bar_id__1" and have double underscores
1130 in them.
1131
1132 """
1133 label = getattr(self, "name", None)
1134
1135 # current convention is that if the element doesn't have a
1136 # ".name" (usually because it is not NamedColumn), we try to
1137 # use a "table qualified" form for the "dedupe anon" label,
1138 # based on the notion that a label like
1139 # "CAST(casttest.v1 AS DECIMAL) AS casttest_v1__1" looks better than
1140 # "CAST(casttest.v1 AS DECIMAL) AS anon__1"
1141
1142 if label is None:
1143 return self._dedupe_anon_tq_label_idx(idx)
1144 else:
1145 return self._anon_label(label, add_hash=idx)
1146
1147 @util.memoized_property
1148 def _anon_tq_label(self):
1149 return self._anon_label(getattr(self, "_tq_label", None))
1150
1151 @util.memoized_property
1152 def _anon_tq_key_label(self):
1153 return self._anon_label(getattr(self, "_tq_key_label", None))
1154
1155 def _dedupe_anon_tq_label_idx(self, idx):
1156 label = getattr(self, "_tq_label", None) or "anon"
1157
1158 return self._anon_label(label, add_hash=idx)
1159
1160
1161class WrapsColumnExpression(object):
1162 """Mixin that defines a :class:`_expression.ColumnElement`
1163 as a wrapper with special
1164 labeling behavior for an expression that already has a name.
1165
1166 .. versionadded:: 1.4
1167
1168 .. seealso::
1169
1170 :ref:`change_4449`
1171
1172
1173 """
1174
1175 @property
1176 def wrapped_column_expression(self):
1177 raise NotImplementedError()
1178
1179 @property
1180 def _tq_label(self):
1181 wce = self.wrapped_column_expression
1182 if hasattr(wce, "_tq_label"):
1183 return wce._tq_label
1184 else:
1185 return None
1186
1187 _label = _tq_label
1188
1189 @property
1190 def _non_anon_label(self):
1191 return None
1192
1193 @property
1194 def _anon_name_label(self):
1195 wce = self.wrapped_column_expression
1196
1197 # this logic tries to get the WrappedColumnExpression to render
1198 # with "<expr> AS <name>", where "<name>" is the natural name
1199 # within the expression itself. e.g. "CAST(table.foo) AS foo".
1200 if not wce._is_text_clause:
1201 nal = wce._non_anon_label
1202 if nal:
1203 return nal
1204 elif hasattr(wce, "_anon_name_label"):
1205 return wce._anon_name_label
1206 return super(WrapsColumnExpression, self)._anon_name_label
1207
1208 def _dedupe_anon_label_idx(self, idx):
1209 wce = self.wrapped_column_expression
1210 nal = wce._non_anon_label
1211 if nal:
1212 return self._anon_label(nal + "_")
1213 else:
1214 return self._dedupe_anon_tq_label_idx(idx)
1215
1216 @property
1217 def _proxy_key(self):
1218 wce = self.wrapped_column_expression
1219
1220 if not wce._is_text_clause:
1221 return wce._proxy_key
1222 return super(WrapsColumnExpression, self)._proxy_key
1223
1224
1225class BindParameter(roles.InElementRole, ColumnElement):
1226 r"""Represent a "bound expression".
1227
1228 :class:`.BindParameter` is invoked explicitly using the
1229 :func:`.bindparam` function, as in::
1230
1231 from sqlalchemy import bindparam
1232
1233 stmt = select(users_table).\
1234 where(users_table.c.name == bindparam('username'))
1235
1236 Detailed discussion of how :class:`.BindParameter` is used is
1237 at :func:`.bindparam`.
1238
1239 .. seealso::
1240
1241 :func:`.bindparam`
1242
1243 """
1244
1245 __visit_name__ = "bindparam"
1246
1247 _traverse_internals = [
1248 ("key", InternalTraversal.dp_anon_name),
1249 ("type", InternalTraversal.dp_type),
1250 ("callable", InternalTraversal.dp_plain_dict),
1251 ("value", InternalTraversal.dp_plain_obj),
1252 ("literal_execute", InternalTraversal.dp_boolean),
1253 ]
1254
1255 _is_crud = False
1256 _is_bind_parameter = True
1257 _key_is_anon = False
1258
1259 # bindparam implements its own _gen_cache_key() method however
1260 # we check subclasses for this flag, else no cache key is generated
1261 inherit_cache = True
1262
1263 def __init__(
1264 self,
1265 key,
1266 value=NO_ARG,
1267 type_=None,
1268 unique=False,
1269 required=NO_ARG,
1270 quote=None,
1271 callable_=None,
1272 expanding=False,
1273 isoutparam=False,
1274 literal_execute=False,
1275 _compared_to_operator=None,
1276 _compared_to_type=None,
1277 _is_crud=False,
1278 ):
1279 r"""Produce a "bound expression".
1280
1281 The return value is an instance of :class:`.BindParameter`; this
1282 is a :class:`_expression.ColumnElement`
1283 subclass which represents a so-called
1284 "placeholder" value in a SQL expression, the value of which is
1285 supplied at the point at which the statement in executed against a
1286 database connection.
1287
1288 In SQLAlchemy, the :func:`.bindparam` construct has
1289 the ability to carry along the actual value that will be ultimately
1290 used at expression time. In this way, it serves not just as
1291 a "placeholder" for eventual population, but also as a means of
1292 representing so-called "unsafe" values which should not be rendered
1293 directly in a SQL statement, but rather should be passed along
1294 to the :term:`DBAPI` as values which need to be correctly escaped
1295 and potentially handled for type-safety.
1296
1297 When using :func:`.bindparam` explicitly, the use case is typically
1298 one of traditional deferment of parameters; the :func:`.bindparam`
1299 construct accepts a name which can then be referred to at execution
1300 time::
1301
1302 from sqlalchemy import bindparam
1303
1304 stmt = select(users_table).\
1305 where(users_table.c.name == bindparam('username'))
1306
1307 The above statement, when rendered, will produce SQL similar to::
1308
1309 SELECT id, name FROM user WHERE name = :username
1310
1311 In order to populate the value of ``:username`` above, the value
1312 would typically be applied at execution time to a method
1313 like :meth:`_engine.Connection.execute`::
1314
1315 result = connection.execute(stmt, username='wendy')
1316
1317 Explicit use of :func:`.bindparam` is also common when producing
1318 UPDATE or DELETE statements that are to be invoked multiple times,
1319 where the WHERE criterion of the statement is to change on each
1320 invocation, such as::
1321
1322 stmt = (users_table.update().
1323 where(user_table.c.name == bindparam('username')).
1324 values(fullname=bindparam('fullname'))
1325 )
1326
1327 connection.execute(
1328 stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
1329 {"username": "jack", "fullname": "Jack Jones"},
1330 ]
1331 )
1332
1333 SQLAlchemy's Core expression system makes wide use of
1334 :func:`.bindparam` in an implicit sense. It is typical that Python
1335 literal values passed to virtually all SQL expression functions are
1336 coerced into fixed :func:`.bindparam` constructs. For example, given
1337 a comparison operation such as::
1338
1339 expr = users_table.c.name == 'Wendy'
1340
1341 The above expression will produce a :class:`.BinaryExpression`
1342 construct, where the left side is the :class:`_schema.Column` object
1343 representing the ``name`` column, and the right side is a
1344 :class:`.BindParameter` representing the literal value::
1345
1346 print(repr(expr.right))
1347 BindParameter('%(4327771088 name)s', 'Wendy', type_=String())
1348
1349 The expression above will render SQL such as::
1350
1351 user.name = :name_1
1352
1353 Where the ``:name_1`` parameter name is an anonymous name. The
1354 actual string ``Wendy`` is not in the rendered string, but is carried
1355 along where it is later used within statement execution. If we
1356 invoke a statement like the following::
1357
1358 stmt = select(users_table).where(users_table.c.name == 'Wendy')
1359 result = connection.execute(stmt)
1360
1361 We would see SQL logging output as::
1362
1363 SELECT "user".id, "user".name
1364 FROM "user"
1365 WHERE "user".name = %(name_1)s
1366 {'name_1': 'Wendy'}
1367
1368 Above, we see that ``Wendy`` is passed as a parameter to the database,
1369 while the placeholder ``:name_1`` is rendered in the appropriate form
1370 for the target database, in this case the PostgreSQL database.
1371
1372 Similarly, :func:`.bindparam` is invoked automatically when working
1373 with :term:`CRUD` statements as far as the "VALUES" portion is
1374 concerned. The :func:`_expression.insert` construct produces an
1375 ``INSERT`` expression which will, at statement execution time, generate
1376 bound placeholders based on the arguments passed, as in::
1377
1378 stmt = users_table.insert()
1379 result = connection.execute(stmt, name='Wendy')
1380
1381 The above will produce SQL output as::
1382
1383 INSERT INTO "user" (name) VALUES (%(name)s)
1384 {'name': 'Wendy'}
1385
1386 The :class:`_expression.Insert` construct, at
1387 compilation/execution time, rendered a single :func:`.bindparam`
1388 mirroring the column name ``name`` as a result of the single ``name``
1389 parameter we passed to the :meth:`_engine.Connection.execute` method.
1390
1391 :param key:
1392 the key (e.g. the name) for this bind param.
1393 Will be used in the generated
1394 SQL statement for dialects that use named parameters. This
1395 value may be modified when part of a compilation operation,
1396 if other :class:`BindParameter` objects exist with the same
1397 key, or if its length is too long and truncation is
1398 required.
1399
1400 :param value:
1401 Initial value for this bind param. Will be used at statement
1402 execution time as the value for this parameter passed to the
1403 DBAPI, if no other value is indicated to the statement execution
1404 method for this particular parameter name. Defaults to ``None``.
1405
1406 :param callable\_:
1407 A callable function that takes the place of "value". The function
1408 will be called at statement execution time to determine the
1409 ultimate value. Used for scenarios where the actual bind
1410 value cannot be determined at the point at which the clause
1411 construct is created, but embedded bind values are still desirable.
1412
1413 :param type\_:
1414 A :class:`.TypeEngine` class or instance representing an optional
1415 datatype for this :func:`.bindparam`. If not passed, a type
1416 may be determined automatically for the bind, based on the given
1417 value; for example, trivial Python types such as ``str``,
1418 ``int``, ``bool``
1419 may result in the :class:`.String`, :class:`.Integer` or
1420 :class:`.Boolean` types being automatically selected.
1421
1422 The type of a :func:`.bindparam` is significant especially in that
1423 the type will apply pre-processing to the value before it is
1424 passed to the database. For example, a :func:`.bindparam` which
1425 refers to a datetime value, and is specified as holding the
1426 :class:`.DateTime` type, may apply conversion needed to the
1427 value (such as stringification on SQLite) before passing the value
1428 to the database.
1429
1430 :param unique:
1431 if True, the key name of this :class:`.BindParameter` will be
1432 modified if another :class:`.BindParameter` of the same name
1433 already has been located within the containing
1434 expression. This flag is used generally by the internals
1435 when producing so-called "anonymous" bound expressions, it
1436 isn't generally applicable to explicitly-named :func:`.bindparam`
1437 constructs.
1438
1439 :param required:
1440 If ``True``, a value is required at execution time. If not passed,
1441 it defaults to ``True`` if neither :paramref:`.bindparam.value`
1442 or :paramref:`.bindparam.callable` were passed. If either of these
1443 parameters are present, then :paramref:`.bindparam.required`
1444 defaults to ``False``.
1445
1446 :param quote:
1447 True if this parameter name requires quoting and is not
1448 currently known as a SQLAlchemy reserved word; this currently
1449 only applies to the Oracle backend, where bound names must
1450 sometimes be quoted.
1451
1452 :param isoutparam:
1453 if True, the parameter should be treated like a stored procedure
1454 "OUT" parameter. This applies to backends such as Oracle which
1455 support OUT parameters.
1456
1457 :param expanding:
1458 if True, this parameter will be treated as an "expanding" parameter
1459 at execution time; the parameter value is expected to be a sequence,
1460 rather than a scalar value, and the string SQL statement will
1461 be transformed on a per-execution basis to accommodate the sequence
1462 with a variable number of parameter slots passed to the DBAPI.
1463 This is to allow statement caching to be used in conjunction with
1464 an IN clause.
1465
1466 .. seealso::
1467
1468 :meth:`.ColumnOperators.in_`
1469
1470 :ref:`baked_in` - with baked queries
1471
1472 .. note:: The "expanding" feature does not support "executemany"-
1473 style parameter sets.
1474
1475 .. versionadded:: 1.2
1476
1477 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
1478 supports empty lists.
1479
1480 :param literal_execute:
1481 if True, the bound parameter will be rendered in the compile phase
1482 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
1483 render the final value of the parameter into the SQL statement at
1484 statement execution time, omitting the value from the parameter
1485 dictionary / list passed to DBAPI ``cursor.execute()``. This
1486 produces a similar effect as that of using the ``literal_binds``,
1487 compilation flag, however takes place as the statement is sent to
1488 the DBAPI ``cursor.execute()`` method, rather than when the statement
1489 is compiled. The primary use of this
1490 capability is for rendering LIMIT / OFFSET clauses for database
1491 drivers that can't accommodate for bound parameters in these
1492 contexts, while allowing SQL constructs to be cacheable at the
1493 compilation level.
1494
1495 .. versionadded:: 1.4 Added "post compile" bound parameters
1496
1497 .. seealso::
1498
1499 :ref:`change_4808`.
1500
1501 .. seealso::
1502
1503 :ref:`tutorial_sending_parameters` - in the
1504 :ref:`unified_tutorial`
1505
1506 """
1507 if required is NO_ARG:
1508 required = value is NO_ARG and callable_ is None
1509 if value is NO_ARG:
1510 value = None
1511
1512 if quote is not None:
1513 key = quoted_name(key, quote)
1514
1515 if unique:
1516 self.key = _anonymous_label.safe_construct(
1517 id(self),
1518 key
1519 if key is not None and not isinstance(key, _anonymous_label)
1520 else "param",
1521 sanitize_key=True,
1522 )
1523 self._key_is_anon = True
1524 elif key:
1525 self.key = key
1526 else:
1527 self.key = _anonymous_label.safe_construct(id(self), "param")
1528 self._key_is_anon = True
1529
1530 # identifying key that won't change across
1531 # clones, used to identify the bind's logical
1532 # identity
1533 self._identifying_key = self.key
1534
1535 # key that was passed in the first place, used to
1536 # generate new keys
1537 self._orig_key = key or "param"
1538
1539 self.unique = unique
1540 self.value = value
1541 self.callable = callable_
1542 self.isoutparam = isoutparam
1543 self.required = required
1544
1545 # indicate an "expanding" parameter; the compiler sets this
1546 # automatically in the compiler _render_in_expr_w_bindparam method
1547 # for an IN expression
1548 self.expanding = expanding
1549
1550 # this is another hint to help w/ expanding and is typically
1551 # set in the compiler _render_in_expr_w_bindparam method for an
1552 # IN expression
1553 self.expand_op = None
1554
1555 self.literal_execute = literal_execute
1556 if _is_crud:
1557 self._is_crud = True
1558
1559 if type_ is None:
1560 if expanding and value:
1561 check_value = value[0]
1562 else:
1563 check_value = value
1564 if _compared_to_type is not None:
1565 self.type = _compared_to_type.coerce_compared_value(
1566 _compared_to_operator, check_value
1567 )
1568 else:
1569 self.type = type_api._resolve_value_to_type(check_value)
1570 elif isinstance(type_, type):
1571 self.type = type_()
1572 elif type_._is_tuple_type and value:
1573 if expanding:
1574 check_value = value[0]
1575 else:
1576 check_value = value
1577 self.type = type_._resolve_values_to_types(check_value)
1578 else:
1579 self.type = type_
1580
1581 def _with_value(self, value, maintain_key=False, required=NO_ARG):
1582 """Return a copy of this :class:`.BindParameter` with the given value
1583 set.
1584 """
1585 cloned = self._clone(maintain_key=maintain_key)
1586 cloned.value = value
1587 cloned.callable = None
1588 cloned.required = required if required is not NO_ARG else self.required
1589 if cloned.type is type_api.NULLTYPE:
1590 cloned.type = type_api._resolve_value_to_type(value)
1591 return cloned
1592
1593 @property
1594 def effective_value(self):
1595 """Return the value of this bound parameter,
1596 taking into account if the ``callable`` parameter
1597 was set.
1598
1599 The ``callable`` value will be evaluated
1600 and returned if present, else ``value``.
1601
1602 """
1603 if self.callable:
1604 return self.callable()
1605 else:
1606 return self.value
1607
1608 def render_literal_execute(self):
1609 """Produce a copy of this bound parameter that will enable the
1610 :paramref:`_sql.BindParameter.literal_execute` flag.
1611
1612 The :paramref:`_sql.BindParameter.literal_execute` flag will
1613 have the effect of the parameter rendered in the compiled SQL
1614 string using ``[POSTCOMPILE]`` form, which is a special form that
1615 is converted to be a rendering of the literal value of the parameter
1616 at SQL execution time. The rationale is to support caching
1617 of SQL statement strings that can embed per-statement literal values,
1618 such as LIMIT and OFFSET parameters, in the final SQL string that
1619 is passed to the DBAPI. Dialects in particular may want to use
1620 this method within custom compilation schemes.
1621
1622 .. versionadded:: 1.4.5
1623
1624 .. seealso::
1625
1626 :ref:`engine_thirdparty_caching`
1627
1628 """
1629 return self.__class__(
1630 self.key,
1631 self.value,
1632 type_=self.type,
1633 literal_execute=True,
1634 )
1635
1636 def _negate_in_binary(self, negated_op, original_op):
1637 if self.expand_op is original_op:
1638 bind = self._clone()
1639 bind.expand_op = negated_op
1640 return bind
1641 else:
1642 return self
1643
1644 def _with_binary_element_type(self, type_):
1645 c = ClauseElement._clone(self)
1646 c.type = type_
1647 return c
1648
1649 def _clone(self, maintain_key=False, **kw):
1650 c = ClauseElement._clone(self, **kw)
1651 # ensure all the BindParameter objects stay in cloned set.
1652 # in #7823, we changed "clone" so that a clone only keeps a reference
1653 # to the "original" element, since for column correspondence, that's
1654 # all we need. However, for BindParam, _cloned_set is used by
1655 # the "cache key bind match" lookup, which means if any of those
1656 # interim BindParameter objects became part of a cache key in the
1657 # cache, we need it. So here, make sure all clones keep carrying
1658 # forward.
1659 c._cloned_set.update(self._cloned_set)
1660 if not maintain_key and self.unique:
1661 c.key = _anonymous_label.safe_construct(
1662 id(c), c._orig_key or "param", sanitize_key=True
1663 )
1664 return c
1665
1666 def _gen_cache_key(self, anon_map, bindparams):
1667 _gen_cache_ok = self.__class__.__dict__.get("inherit_cache", False)
1668
1669 if not _gen_cache_ok:
1670 if anon_map is not None:
1671 anon_map[NO_CACHE] = True
1672 return None
1673
1674 idself = id(self)
1675 if idself in anon_map:
1676 return (anon_map[idself], self.__class__)
1677 else:
1678 # inline of
1679 # id_ = anon_map[idself]
1680 anon_map[idself] = id_ = str(anon_map.index)
1681 anon_map.index += 1
1682
1683 if bindparams is not None:
1684 bindparams.append(self)
1685
1686 return (
1687 id_,
1688 self.__class__,
1689 self.type._static_cache_key,
1690 self.key % anon_map if self._key_is_anon else self.key,
1691 self.literal_execute,
1692 )
1693
1694 def _convert_to_unique(self):
1695 if not self.unique:
1696 self.unique = True
1697 self.key = _anonymous_label.safe_construct(
1698 id(self), self._orig_key or "param", sanitize_key=True
1699 )
1700
1701 def __getstate__(self):
1702 """execute a deferred value for serialization purposes."""
1703
1704 d = self.__dict__.copy()
1705 v = self.value
1706 if self.callable:
1707 v = self.callable()
1708 d["callable"] = None
1709 d["value"] = v
1710 return d
1711
1712 def __setstate__(self, state):
1713 if state.get("unique", False):
1714 state["key"] = _anonymous_label.safe_construct(
1715 id(self), state.get("_orig_key", "param"), sanitize_key=True
1716 )
1717 self.__dict__.update(state)
1718
1719 def __repr__(self):
1720 return "%s(%r, %r, type_=%r)" % (
1721 self.__class__.__name__,
1722 self.key,
1723 self.value,
1724 self.type,
1725 )
1726
1727
1728class TypeClause(ClauseElement):
1729 """Handle a type keyword in a SQL statement.
1730
1731 Used by the ``Case`` statement.
1732
1733 """
1734
1735 __visit_name__ = "typeclause"
1736
1737 _traverse_internals = [("type", InternalTraversal.dp_type)]
1738
1739 def __init__(self, type_):
1740 self.type = type_
1741
1742
1743class TextClause(
1744 roles.DDLConstraintColumnRole,
1745 roles.DDLExpressionRole,
1746 roles.StatementOptionRole,
1747 roles.WhereHavingRole,
1748 roles.OrderByRole,
1749 roles.FromClauseRole,
1750 roles.SelectStatementRole,
1751 roles.BinaryElementRole,
1752 roles.InElementRole,
1753 Executable,
1754 ClauseElement,
1755):
1756 """Represent a literal SQL text fragment.
1757
1758 E.g.::
1759
1760 from sqlalchemy import text
1761
1762 t = text("SELECT * FROM users")
1763 result = connection.execute(t)
1764
1765
1766 The :class:`_expression.TextClause` construct is produced using the
1767 :func:`_expression.text`
1768 function; see that function for full documentation.
1769
1770 .. seealso::
1771
1772 :func:`_expression.text`
1773
1774 """
1775
1776 __visit_name__ = "textclause"
1777
1778 _traverse_internals = [
1779 ("_bindparams", InternalTraversal.dp_string_clauseelement_dict),
1780 ("text", InternalTraversal.dp_string),
1781 ]
1782
1783 _is_text_clause = True
1784
1785 _is_textual = True
1786
1787 _bind_params_regex = re.compile(r"(?<![:\w\x5c]):(\w+)(?!:)", re.UNICODE)
1788 _execution_options = Executable._execution_options.union(
1789 {"autocommit": PARSE_AUTOCOMMIT}
1790 )
1791 _is_implicitly_boolean = False
1792
1793 _render_label_in_columns_clause = False
1794
1795 _hide_froms = ()
1796
1797 def __and__(self, other):
1798 # support use in select.where(), query.filter()
1799 return and_(self, other)
1800
1801 @property
1802 def _select_iterable(self):
1803 return (self,)
1804
1805 # help in those cases where text() is
1806 # interpreted in a column expression situation
1807 key = _label = None
1808
1809 _allow_label_resolve = False
1810
1811 @property
1812 def _is_star(self):
1813 return self.text == "*"
1814
1815 def __init__(self, text, bind=None):
1816 self._bind = bind
1817 self._bindparams = {}
1818
1819 def repl(m):
1820 self._bindparams[m.group(1)] = BindParameter(m.group(1))
1821 return ":%s" % m.group(1)
1822
1823 # scan the string and search for bind parameter names, add them
1824 # to the list of bindparams
1825 self.text = self._bind_params_regex.sub(repl, text)
1826
1827 @classmethod
1828 @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1829 @util.deprecated_params(
1830 bind=(
1831 "2.0",
1832 "The :paramref:`_sql.text.bind` argument is deprecated and "
1833 "will be removed in SQLAlchemy 2.0.",
1834 ),
1835 )
1836 def _create_text(cls, text, bind=None):
1837 r"""Construct a new :class:`_expression.TextClause` clause,
1838 representing
1839 a textual SQL string directly.
1840
1841 E.g.::
1842
1843 from sqlalchemy import text
1844
1845 t = text("SELECT * FROM users")
1846 result = connection.execute(t)
1847
1848 The advantages :func:`_expression.text`
1849 provides over a plain string are
1850 backend-neutral support for bind parameters, per-statement
1851 execution options, as well as
1852 bind parameter and result-column typing behavior, allowing
1853 SQLAlchemy type constructs to play a role when executing
1854 a statement that is specified literally. The construct can also
1855 be provided with a ``.c`` collection of column elements, allowing
1856 it to be embedded in other SQL expression constructs as a subquery.
1857
1858 Bind parameters are specified by name, using the format ``:name``.
1859 E.g.::
1860
1861 t = text("SELECT * FROM users WHERE id=:user_id")
1862 result = connection.execute(t, user_id=12)
1863
1864 For SQL statements where a colon is required verbatim, as within
1865 an inline string, use a backslash to escape::
1866
1867 t = text("SELECT * FROM users WHERE name='\:username'")
1868
1869 The :class:`_expression.TextClause`
1870 construct includes methods which can
1871 provide information about the bound parameters as well as the column
1872 values which would be returned from the textual statement, assuming
1873 it's an executable SELECT type of statement. The
1874 :meth:`_expression.TextClause.bindparams`
1875 method is used to provide bound
1876 parameter detail, and :meth:`_expression.TextClause.columns`
1877 method allows
1878 specification of return columns including names and types::
1879
1880 t = text("SELECT * FROM users WHERE id=:user_id").\
1881 bindparams(user_id=7).\
1882 columns(id=Integer, name=String)
1883
1884 for id, name in connection.execute(t):
1885 print(id, name)
1886
1887 The :func:`_expression.text` construct is used in cases when
1888 a literal string SQL fragment is specified as part of a larger query,
1889 such as for the WHERE clause of a SELECT statement::
1890
1891 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1892 result = connection.execute(s, user_id=12)
1893
1894 :func:`_expression.text` is also used for the construction
1895 of a full, standalone statement using plain text.
1896 As such, SQLAlchemy refers
1897 to it as an :class:`.Executable` object, and it supports
1898 the :meth:`Executable.execution_options` method. For example,
1899 a :func:`_expression.text`
1900 construct that should be subject to "autocommit"
1901 can be set explicitly so using the
1902 :paramref:`.Connection.execution_options.autocommit` option::
1903
1904 t = text("EXEC my_procedural_thing()").\
1905 execution_options(autocommit=True)
1906
1907 .. deprecated:: 1.4 The "autocommit" execution option is deprecated
1908 and will be removed in SQLAlchemy 2.0. See
1909 :ref:`migration_20_autocommit` for discussion.
1910
1911 :param text:
1912 the text of the SQL statement to be created. Use ``:<param>``
1913 to specify bind parameters; they will be compiled to their
1914 engine-specific format.
1915
1916 :param bind:
1917 an optional connection or engine to be used for this text query.
1918
1919 .. seealso::
1920
1921 :ref:`tutorial_select_arbitrary_text`
1922
1923
1924 """
1925 return TextClause(text, bind=bind)
1926
1927 @_generative
1928 def bindparams(self, *binds, **names_to_values):
1929 """Establish the values and/or types of bound parameters within
1930 this :class:`_expression.TextClause` construct.
1931
1932 Given a text construct such as::
1933
1934 from sqlalchemy import text
1935 stmt = text("SELECT id, name FROM user WHERE name=:name "
1936 "AND timestamp=:timestamp")
1937
1938 the :meth:`_expression.TextClause.bindparams`
1939 method can be used to establish
1940 the initial value of ``:name`` and ``:timestamp``,
1941 using simple keyword arguments::
1942
1943 stmt = stmt.bindparams(name='jack',
1944 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
1945
1946 Where above, new :class:`.BindParameter` objects
1947 will be generated with the names ``name`` and ``timestamp``, and
1948 values of ``jack`` and ``datetime.datetime(2012, 10, 8, 15, 12, 5)``,
1949 respectively. The types will be
1950 inferred from the values given, in this case :class:`.String` and
1951 :class:`.DateTime`.
1952
1953 When specific typing behavior is needed, the positional ``*binds``
1954 argument can be used in which to specify :func:`.bindparam` constructs
1955 directly. These constructs must include at least the ``key``
1956 argument, then an optional value and type::
1957
1958 from sqlalchemy import bindparam
1959 stmt = stmt.bindparams(
1960 bindparam('name', value='jack', type_=String),
1961 bindparam('timestamp', type_=DateTime)
1962 )
1963
1964 Above, we specified the type of :class:`.DateTime` for the
1965 ``timestamp`` bind, and the type of :class:`.String` for the ``name``
1966 bind. In the case of ``name`` we also set the default value of
1967 ``"jack"``.
1968
1969 Additional bound parameters can be supplied at statement execution
1970 time, e.g.::
1971
1972 result = connection.execute(stmt,
1973 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
1974
1975 The :meth:`_expression.TextClause.bindparams`
1976 method can be called repeatedly,
1977 where it will re-use existing :class:`.BindParameter` objects to add
1978 new information. For example, we can call
1979 :meth:`_expression.TextClause.bindparams`
1980 first with typing information, and a
1981 second time with value information, and it will be combined::
1982
1983 stmt = text("SELECT id, name FROM user WHERE name=:name "
1984 "AND timestamp=:timestamp")
1985 stmt = stmt.bindparams(
1986 bindparam('name', type_=String),
1987 bindparam('timestamp', type_=DateTime)
1988 )
1989 stmt = stmt.bindparams(
1990 name='jack',
1991 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)
1992 )
1993
1994 The :meth:`_expression.TextClause.bindparams`
1995 method also supports the concept of
1996 **unique** bound parameters. These are parameters that are
1997 "uniquified" on name at statement compilation time, so that multiple
1998 :func:`_expression.text`
1999 constructs may be combined together without the names
2000 conflicting. To use this feature, specify the
2001 :paramref:`.BindParameter.unique` flag on each :func:`.bindparam`
2002 object::
2003
2004 stmt1 = text("select id from table where name=:name").bindparams(
2005 bindparam("name", value='name1', unique=True)
2006 )
2007 stmt2 = text("select id from table where name=:name").bindparams(
2008 bindparam("name", value='name2', unique=True)
2009 )
2010
2011 union = union_all(
2012 stmt1.columns(column("id")),
2013 stmt2.columns(column("id"))
2014 )
2015
2016 The above statement will render as::
2017
2018 select id from table where name=:name_1
2019 UNION ALL select id from table where name=:name_2
2020
2021 .. versionadded:: 1.3.11 Added support for the
2022 :paramref:`.BindParameter.unique` flag to work with
2023 :func:`_expression.text`
2024 constructs.
2025
2026 """
2027 self._bindparams = new_params = self._bindparams.copy()
2028
2029 for bind in binds:
2030 try:
2031 # the regex used for text() currently will not match
2032 # a unique/anonymous key in any case, so use the _orig_key
2033 # so that a text() construct can support unique parameters
2034 existing = new_params[bind._orig_key]
2035 except KeyError as err:
2036 util.raise_(
2037 exc.ArgumentError(
2038 "This text() construct doesn't define a "
2039 "bound parameter named %r" % bind._orig_key
2040 ),
2041 replace_context=err,
2042 )
2043 else:
2044 new_params[existing._orig_key] = bind
2045
2046 for key, value in names_to_values.items():
2047 try:
2048 existing = new_params[key]
2049 except KeyError as err:
2050 util.raise_(
2051 exc.ArgumentError(
2052 "This text() construct doesn't define a "
2053 "bound parameter named %r" % key
2054 ),
2055 replace_context=err,
2056 )
2057 else:
2058 new_params[key] = existing._with_value(value, required=False)
2059
2060 @util.preload_module("sqlalchemy.sql.selectable")
2061 def columns(self, *cols, **types):
2062 r"""Turn this :class:`_expression.TextClause` object into a
2063 :class:`_expression.TextualSelect`
2064 object that serves the same role as a SELECT
2065 statement.
2066
2067 The :class:`_expression.TextualSelect` is part of the
2068 :class:`_expression.SelectBase`
2069 hierarchy and can be embedded into another statement by using the
2070 :meth:`_expression.TextualSelect.subquery` method to produce a
2071 :class:`.Subquery`
2072 object, which can then be SELECTed from.
2073
2074 This function essentially bridges the gap between an entirely
2075 textual SELECT statement and the SQL expression language concept
2076 of a "selectable"::
2077
2078 from sqlalchemy.sql import column, text
2079
2080 stmt = text("SELECT id, name FROM some_table")
2081 stmt = stmt.columns(column('id'), column('name')).subquery('st')
2082
2083 stmt = select(mytable).\
2084 select_from(
2085 mytable.join(stmt, mytable.c.name == stmt.c.name)
2086 ).where(stmt.c.id > 5)
2087
2088 Above, we pass a series of :func:`_expression.column` elements to the
2089 :meth:`_expression.TextClause.columns` method positionally. These
2090 :func:`_expression.column`
2091 elements now become first class elements upon the
2092 :attr:`_expression.TextualSelect.selected_columns` column collection,
2093 which then
2094 become part of the :attr:`.Subquery.c` collection after
2095 :meth:`_expression.TextualSelect.subquery` is invoked.
2096
2097 The column expressions we pass to
2098 :meth:`_expression.TextClause.columns` may
2099 also be typed; when we do so, these :class:`.TypeEngine` objects become
2100 the effective return type of the column, so that SQLAlchemy's
2101 result-set-processing systems may be used on the return values.
2102 This is often needed for types such as date or boolean types, as well
2103 as for unicode processing on some dialect configurations::
2104
2105 stmt = text("SELECT id, name, timestamp FROM some_table")
2106 stmt = stmt.columns(
2107 column('id', Integer),
2108 column('name', Unicode),
2109 column('timestamp', DateTime)
2110 )
2111
2112 for id, name, timestamp in connection.execute(stmt):
2113 print(id, name, timestamp)
2114
2115 As a shortcut to the above syntax, keyword arguments referring to
2116 types alone may be used, if only type conversion is needed::
2117
2118 stmt = text("SELECT id, name, timestamp FROM some_table")
2119 stmt = stmt.columns(
2120 id=Integer,
2121 name=Unicode,
2122 timestamp=DateTime
2123 )
2124
2125 for id, name, timestamp in connection.execute(stmt):
2126 print(id, name, timestamp)
2127
2128 The positional form of :meth:`_expression.TextClause.columns`
2129 also provides the
2130 unique feature of **positional column targeting**, which is
2131 particularly useful when using the ORM with complex textual queries. If
2132 we specify the columns from our model to
2133 :meth:`_expression.TextClause.columns`,
2134 the result set will match to those columns positionally, meaning the
2135 name or origin of the column in the textual SQL doesn't matter::
2136
2137 stmt = text("SELECT users.id, addresses.id, users.id, "
2138 "users.name, addresses.email_address AS email "
2139 "FROM users JOIN addresses ON users.id=addresses.user_id "
2140 "WHERE users.id = 1").columns(
2141 User.id,
2142 Address.id,
2143 Address.user_id,
2144 User.name,
2145 Address.email_address
2146 )
2147
2148 query = session.query(User).from_statement(stmt).options(
2149 contains_eager(User.addresses))
2150
2151 .. versionadded:: 1.1 the :meth:`_expression.TextClause.columns`
2152 method now
2153 offers positional column targeting in the result set when
2154 the column expressions are passed purely positionally.
2155
2156 The :meth:`_expression.TextClause.columns` method provides a direct
2157 route to calling :meth:`_expression.FromClause.subquery` as well as
2158 :meth:`_expression.SelectBase.cte`
2159 against a textual SELECT statement::
2160
2161 stmt = stmt.columns(id=Integer, name=String).cte('st')
2162
2163 stmt = select(sometable).where(sometable.c.id == stmt.c.id)
2164
2165 :param \*cols: A series of :class:`_expression.ColumnElement` objects,
2166 typically
2167 :class:`_schema.Column` objects from a :class:`_schema.Table`
2168 or ORM level
2169 column-mapped attributes, representing a set of columns that this
2170 textual string will SELECT from.
2171
2172 :param \**types: A mapping of string names to :class:`.TypeEngine`
2173 type objects indicating the datatypes to use for names that are
2174 SELECTed from the textual string. Prefer to use the ``*cols``
2175 argument as it also indicates positional ordering.
2176
2177 """
2178 selectable = util.preloaded.sql_selectable
2179 positional_input_cols = [
2180 ColumnClause(col.key, types.pop(col.key))
2181 if col.key in types
2182 else col
2183 for col in cols
2184 ]
2185 keyed_input_cols = [
2186 ColumnClause(key, type_) for key, type_ in types.items()
2187 ]
2188
2189 return selectable.TextualSelect(
2190 self,
2191 positional_input_cols + keyed_input_cols,
2192 positional=bool(positional_input_cols) and not keyed_input_cols,
2193 )
2194
2195 @property
2196 def type(self):
2197 return type_api.NULLTYPE
2198
2199 @property
2200 def comparator(self):
2201 return self.type.comparator_factory(self)
2202
2203 def self_group(self, against=None):
2204 if against is operators.in_op:
2205 return Grouping(self)
2206 else:
2207 return self
2208
2209
2210class Null(SingletonConstant, roles.ConstExprRole, ColumnElement):
2211 """Represent the NULL keyword in a SQL statement.
2212
2213 :class:`.Null` is accessed as a constant via the
2214 :func:`.null` function.
2215
2216 """
2217
2218 __visit_name__ = "null"
2219
2220 _traverse_internals = []
2221
2222 @util.memoized_property
2223 def type(self):
2224 return type_api.NULLTYPE
2225
2226 @classmethod
2227 def _instance(cls):
2228 """Return a constant :class:`.Null` construct."""
2229
2230 return Null()
2231
2232
2233Null._create_singleton()
2234
2235
2236class False_(SingletonConstant, roles.ConstExprRole, ColumnElement):
2237 """Represent the ``false`` keyword, or equivalent, in a SQL statement.
2238
2239 :class:`.False_` is accessed as a constant via the
2240 :func:`.false` function.
2241
2242 """
2243
2244 __visit_name__ = "false"
2245 _traverse_internals = []
2246
2247 @util.memoized_property
2248 def type(self):
2249 return type_api.BOOLEANTYPE
2250
2251 def _negate(self):
2252 return True_()
2253
2254 @classmethod
2255 def _instance(cls):
2256 """Return a :class:`.False_` construct.
2257
2258 E.g.::
2259
2260 >>> from sqlalchemy import false
2261 >>> print(select(t.c.x).where(false()))
2262 SELECT x FROM t WHERE false
2263
2264 A backend which does not support true/false constants will render as
2265 an expression against 1 or 0::
2266
2267 >>> print(select(t.c.x).where(false()))
2268 SELECT x FROM t WHERE 0 = 1
2269
2270 The :func:`.true` and :func:`.false` constants also feature
2271 "short circuit" operation within an :func:`.and_` or :func:`.or_`
2272 conjunction::
2273
2274 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
2275 SELECT x FROM t WHERE true
2276
2277 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
2278 SELECT x FROM t WHERE false
2279
2280 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature
2281 better integrated behavior within conjunctions and on dialects
2282 that don't support true/false constants.
2283
2284 .. seealso::
2285
2286 :func:`.true`
2287
2288 """
2289
2290 return False_()
2291
2292
2293False_._create_singleton()
2294
2295
2296class True_(SingletonConstant, roles.ConstExprRole, ColumnElement):
2297 """Represent the ``true`` keyword, or equivalent, in a SQL statement.
2298
2299 :class:`.True_` is accessed as a constant via the
2300 :func:`.true` function.
2301
2302 """
2303
2304 __visit_name__ = "true"
2305
2306 _traverse_internals = []
2307
2308 @util.memoized_property
2309 def type(self):
2310 return type_api.BOOLEANTYPE
2311
2312 def _negate(self):
2313 return False_()
2314
2315 @classmethod
2316 def _ifnone(cls, other):
2317 if other is None:
2318 return cls._instance()
2319 else:
2320 return other
2321
2322 @classmethod
2323 def _instance(cls):
2324 """Return a constant :class:`.True_` construct.
2325
2326 E.g.::
2327
2328 >>> from sqlalchemy import true
2329 >>> print(select(t.c.x).where(true()))
2330 SELECT x FROM t WHERE true
2331
2332 A backend which does not support true/false constants will render as
2333 an expression against 1 or 0::
2334
2335 >>> print(select(t.c.x).where(true()))
2336 SELECT x FROM t WHERE 1 = 1
2337
2338 The :func:`.true` and :func:`.false` constants also feature
2339 "short circuit" operation within an :func:`.and_` or :func:`.or_`
2340 conjunction::
2341
2342 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
2343 SELECT x FROM t WHERE true
2344
2345 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
2346 SELECT x FROM t WHERE false
2347
2348 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature
2349 better integrated behavior within conjunctions and on dialects
2350 that don't support true/false constants.
2351
2352 .. seealso::
2353
2354 :func:`.false`
2355
2356 """
2357
2358 return True_()
2359
2360
2361True_._create_singleton()
2362
2363
2364class ClauseList(
2365 roles.InElementRole,
2366 roles.OrderByRole,
2367 roles.ColumnsClauseRole,
2368 roles.DMLColumnRole,
2369 ClauseElement,
2370):
2371 """Describe a list of clauses, separated by an operator.
2372
2373 By default, is comma-separated, such as a column listing.
2374
2375 """
2376
2377 __visit_name__ = "clauselist"
2378
2379 _is_clause_list = True
2380
2381 _traverse_internals = [
2382 ("clauses", InternalTraversal.dp_clauseelement_list),
2383 ("operator", InternalTraversal.dp_operator),
2384 ]
2385
2386 def __init__(self, *clauses, **kwargs):
2387 self.operator = kwargs.pop("operator", operators.comma_op)
2388 self.group = kwargs.pop("group", True)
2389 self.group_contents = kwargs.pop("group_contents", True)
2390 if kwargs.pop("_flatten_sub_clauses", False):
2391 clauses = util.flatten_iterator(clauses)
2392 self._text_converter_role = text_converter_role = kwargs.pop(
2393 "_literal_as_text_role", roles.WhereHavingRole
2394 )
2395 if self.group_contents:
2396 self.clauses = [
2397 coercions.expect(
2398 text_converter_role, clause, apply_propagate_attrs=self
2399 ).self_group(against=self.operator)
2400 for clause in clauses
2401 ]
2402 else:
2403 self.clauses = [
2404 coercions.expect(
2405 text_converter_role, clause, apply_propagate_attrs=self
2406 )
2407 for clause in clauses
2408 ]
2409 self._is_implicitly_boolean = operators.is_boolean(self.operator)
2410
2411 @classmethod
2412 def _construct_raw(cls, operator, clauses=None):
2413 self = cls.__new__(cls)
2414 self.clauses = clauses if clauses else []
2415 self.group = True
2416 self.operator = operator
2417 self.group_contents = True
2418 self._is_implicitly_boolean = False
2419 return self
2420
2421 def __iter__(self):
2422 return iter(self.clauses)
2423
2424 def __len__(self):
2425 return len(self.clauses)
2426
2427 @property
2428 def _select_iterable(self):
2429 return itertools.chain.from_iterable(
2430 [elem._select_iterable for elem in self.clauses]
2431 )
2432
2433 def append(self, clause):
2434 if self.group_contents:
2435 self.clauses.append(
2436 coercions.expect(self._text_converter_role, clause).self_group(
2437 against=self.operator
2438 )
2439 )
2440 else:
2441 self.clauses.append(
2442 coercions.expect(self._text_converter_role, clause)
2443 )
2444
2445 @property
2446 def _from_objects(self):
2447 return list(itertools.chain(*[c._from_objects for c in self.clauses]))
2448
2449 def self_group(self, against=None):
2450 if self.group and operators.is_precedent(self.operator, against):
2451 return Grouping(self)
2452 else:
2453 return self
2454
2455
2456class BooleanClauseList(ClauseList, ColumnElement):
2457 __visit_name__ = "clauselist"
2458 inherit_cache = True
2459
2460 def __init__(self, *arg, **kw):
2461 raise NotImplementedError(
2462 "BooleanClauseList has a private constructor"
2463 )
2464
2465 @classmethod
2466 def _process_clauses_for_boolean(
2467 cls, operator, continue_on, skip_on, clauses
2468 ):
2469 has_continue_on = None
2470
2471 convert_clauses = []
2472
2473 against = operators._asbool
2474 lcc = 0
2475
2476 for clause in clauses:
2477 if clause is continue_on:
2478 # instance of continue_on, like and_(x, y, True, z), store it
2479 # if we didn't find one already, we will use it if there
2480 # are no other expressions here.
2481 has_continue_on = clause
2482 elif clause is skip_on:
2483 # instance of skip_on, e.g. and_(x, y, False, z), cancels
2484 # the rest out
2485 convert_clauses = [clause]
2486 lcc = 1
2487 break
2488 else:
2489 if not lcc:
2490 lcc = 1
2491 else:
2492 against = operator
2493 # technically this would be len(convert_clauses) + 1
2494 # however this only needs to indicate "greater than one"
2495 lcc = 2
2496 convert_clauses.append(clause)
2497
2498 if not convert_clauses and has_continue_on is not None:
2499 convert_clauses = [has_continue_on]
2500 lcc = 1
2501
2502 return lcc, [c.self_group(against=against) for c in convert_clauses]
2503
2504 @classmethod
2505 def _construct(cls, operator, continue_on, skip_on, *clauses, **kw):
2506 lcc, convert_clauses = cls._process_clauses_for_boolean(
2507 operator,
2508 continue_on,
2509 skip_on,
2510 [
2511 coercions.expect(roles.WhereHavingRole, clause)
2512 for clause in util.coerce_generator_arg(clauses)
2513 ],
2514 )
2515
2516 if lcc > 1:
2517 # multiple elements. Return regular BooleanClauseList
2518 # which will link elements against the operator.
2519 return cls._construct_raw(operator, convert_clauses)
2520 elif lcc == 1:
2521 # just one element. return it as a single boolean element,
2522 # not a list and discard the operator.
2523 return convert_clauses[0]
2524 else:
2525 # no elements period. deprecated use case. return an empty
2526 # ClauseList construct that generates nothing unless it has
2527 # elements added to it.
2528 util.warn_deprecated(
2529 "Invoking %(name)s() without arguments is deprecated, and "
2530 "will be disallowed in a future release. For an empty "
2531 "%(name)s() construct, use %(name)s(%(continue_on)s, *args)."
2532 % {
2533 "name": operator.__name__,
2534 "continue_on": "True"
2535 if continue_on is True_._singleton
2536 else "False",
2537 },
2538 version="1.4",
2539 )
2540 return cls._construct_raw(operator)
2541
2542 @classmethod
2543 def _construct_for_whereclause(cls, clauses):
2544 operator, continue_on, skip_on = (
2545 operators.and_,
2546 True_._singleton,
2547 False_._singleton,
2548 )
2549
2550 lcc, convert_clauses = cls._process_clauses_for_boolean(
2551 operator,
2552 continue_on,
2553 skip_on,
2554 clauses, # these are assumed to be coerced already
2555 )
2556
2557 if lcc > 1:
2558 # multiple elements. Return regular BooleanClauseList
2559 # which will link elements against the operator.
2560 return cls._construct_raw(operator, convert_clauses)
2561 elif lcc == 1:
2562 # just one element. return it as a single boolean element,
2563 # not a list and discard the operator.
2564 return convert_clauses[0]
2565 else:
2566 return None
2567
2568 @classmethod
2569 def _construct_raw(cls, operator, clauses=None):
2570 self = cls.__new__(cls)
2571 self.clauses = clauses if clauses else []
2572 self.group = True
2573 self.operator = operator
2574 self.group_contents = True
2575 self.type = type_api.BOOLEANTYPE
2576 self._is_implicitly_boolean = True
2577 return self
2578
2579 @classmethod
2580 def and_(cls, *clauses):
2581 r"""Produce a conjunction of expressions joined by ``AND``.
2582
2583 E.g.::
2584
2585 from sqlalchemy import and_
2586
2587 stmt = select(users_table).where(
2588 and_(
2589 users_table.c.name == 'wendy',
2590 users_table.c.enrolled == True
2591 )
2592 )
2593
2594 The :func:`.and_` conjunction is also available using the
2595 Python ``&`` operator (though note that compound expressions
2596 need to be parenthesized in order to function with Python
2597 operator precedence behavior)::
2598
2599 stmt = select(users_table).where(
2600 (users_table.c.name == 'wendy') &
2601 (users_table.c.enrolled == True)
2602 )
2603
2604 The :func:`.and_` operation is also implicit in some cases;
2605 the :meth:`_expression.Select.where`
2606 method for example can be invoked multiple
2607 times against a statement, which will have the effect of each
2608 clause being combined using :func:`.and_`::
2609
2610 stmt = select(users_table).\
2611 where(users_table.c.name == 'wendy').\
2612 where(users_table.c.enrolled == True)
2613
2614 The :func:`.and_` construct must be given at least one positional
2615 argument in order to be valid; a :func:`.and_` construct with no
2616 arguments is ambiguous. To produce an "empty" or dynamically
2617 generated :func:`.and_` expression, from a given list of expressions,
2618 a "default" element of ``True`` should be specified::
2619
2620 criteria = and_(True, *expressions)
2621
2622 The above expression will compile to SQL as the expression ``true``
2623 or ``1 = 1``, depending on backend, if no other expressions are
2624 present. If expressions are present, then the ``True`` value is
2625 ignored as it does not affect the outcome of an AND expression that
2626 has other elements.
2627
2628 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
2629 least one argument is passed; creating the :func:`.and_` construct
2630 with no arguments is deprecated, and will emit a deprecation warning
2631 while continuing to produce a blank SQL string.
2632
2633 .. seealso::
2634
2635 :func:`.or_`
2636
2637 """
2638 return cls._construct(
2639 operators.and_, True_._singleton, False_._singleton, *clauses
2640 )
2641
2642 @classmethod
2643 def or_(cls, *clauses):
2644 """Produce a conjunction of expressions joined by ``OR``.
2645
2646 E.g.::
2647
2648 from sqlalchemy import or_
2649
2650 stmt = select(users_table).where(
2651 or_(
2652 users_table.c.name == 'wendy',
2653 users_table.c.name == 'jack'
2654 )
2655 )
2656
2657 The :func:`.or_` conjunction is also available using the
2658 Python ``|`` operator (though note that compound expressions
2659 need to be parenthesized in order to function with Python
2660 operator precedence behavior)::
2661
2662 stmt = select(users_table).where(
2663 (users_table.c.name == 'wendy') |
2664 (users_table.c.name == 'jack')
2665 )
2666
2667 The :func:`.or_` construct must be given at least one positional
2668 argument in order to be valid; a :func:`.or_` construct with no
2669 arguments is ambiguous. To produce an "empty" or dynamically
2670 generated :func:`.or_` expression, from a given list of expressions,
2671 a "default" element of ``False`` should be specified::
2672
2673 or_criteria = or_(False, *expressions)
2674
2675 The above expression will compile to SQL as the expression ``false``
2676 or ``0 = 1``, depending on backend, if no other expressions are
2677 present. If expressions are present, then the ``False`` value is
2678 ignored as it does not affect the outcome of an OR expression which
2679 has other elements.
2680
2681 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
2682 least one argument is passed; creating the :func:`.or_` construct
2683 with no arguments is deprecated, and will emit a deprecation warning
2684 while continuing to produce a blank SQL string.
2685
2686 .. seealso::
2687
2688 :func:`.and_`
2689
2690 """
2691 return cls._construct(
2692 operators.or_, False_._singleton, True_._singleton, *clauses
2693 )
2694
2695 @property
2696 def _select_iterable(self):
2697 return (self,)
2698
2699 def self_group(self, against=None):
2700 if not self.clauses:
2701 return self
2702 else:
2703 return super(BooleanClauseList, self).self_group(against=against)
2704
2705 def _negate(self):
2706 return ClauseList._negate(self)
2707
2708
2709and_ = BooleanClauseList.and_
2710or_ = BooleanClauseList.or_
2711
2712
2713class Tuple(ClauseList, ColumnElement):
2714 """Represent a SQL tuple."""
2715
2716 __visit_name__ = "tuple"
2717
2718 _traverse_internals = ClauseList._traverse_internals + []
2719
2720 @util.preload_module("sqlalchemy.sql.sqltypes")
2721 def __init__(self, *clauses, **kw):
2722 """Return a :class:`.Tuple`.
2723
2724 Main usage is to produce a composite IN construct using
2725 :meth:`.ColumnOperators.in_` ::
2726
2727 from sqlalchemy import tuple_
2728
2729 tuple_(table.c.col1, table.c.col2).in_(
2730 [(1, 2), (5, 12), (10, 19)]
2731 )
2732
2733 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
2734
2735 .. warning::
2736
2737 The composite IN construct is not supported by all backends, and is
2738 currently known to work on PostgreSQL, MySQL, and SQLite.
2739 Unsupported backends will raise a subclass of
2740 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
2741 invoked.
2742
2743 """
2744 sqltypes = util.preloaded.sql_sqltypes
2745
2746 types = kw.pop("types", None)
2747 if types is None:
2748 clauses = [
2749 coercions.expect(roles.ExpressionElementRole, c)
2750 for c in clauses
2751 ]
2752 else:
2753 if len(types) != len(clauses):
2754 raise exc.ArgumentError(
2755 "Wrong number of elements for %d-tuple: %r "
2756 % (len(types), clauses)
2757 )
2758 clauses = [
2759 coercions.expect(
2760 roles.ExpressionElementRole,
2761 c,
2762 type_=typ if not typ._isnull else None,
2763 )
2764 for typ, c in zip(types, clauses)
2765 ]
2766
2767 self.type = sqltypes.TupleType(*[arg.type for arg in clauses])
2768 super(Tuple, self).__init__(*clauses, **kw)
2769
2770 @property
2771 def _select_iterable(self):
2772 return (self,)
2773
2774 def _bind_param(self, operator, obj, type_=None, expanding=False):
2775 if expanding:
2776 return BindParameter(
2777 None,
2778 value=obj,
2779 _compared_to_operator=operator,
2780 unique=True,
2781 expanding=True,
2782 type_=self.type,
2783 )
2784 else:
2785 return Tuple(
2786 *[
2787 BindParameter(
2788 None,
2789 o,
2790 _compared_to_operator=operator,
2791 _compared_to_type=compared_to_type,
2792 unique=True,
2793 type_=type_,
2794 )
2795 for o, compared_to_type in zip(obj, self.type.types)
2796 ]
2797 )
2798
2799 def self_group(self, against=None):
2800 # Tuple is parenthesized by definition.
2801 return self
2802
2803
2804class Case(ColumnElement):
2805 """Represent a ``CASE`` expression.
2806
2807 :class:`.Case` is produced using the :func:`.case` factory function,
2808 as in::
2809
2810 from sqlalchemy import case
2811
2812 stmt = select(users_table).\
2813 where(
2814 case(
2815 (users_table.c.name == 'wendy', 'W'),
2816 (users_table.c.name == 'jack', 'J'),
2817 else_='E'
2818 )
2819 )
2820
2821 Details on :class:`.Case` usage is at :func:`.case`.
2822
2823 .. seealso::
2824
2825 :func:`.case`
2826
2827 """
2828
2829 __visit_name__ = "case"
2830
2831 _traverse_internals = [
2832 ("value", InternalTraversal.dp_clauseelement),
2833 ("whens", InternalTraversal.dp_clauseelement_tuples),
2834 ("else_", InternalTraversal.dp_clauseelement),
2835 ]
2836
2837 # TODO: for Py2k removal, this will be:
2838 # def __init__(self, *whens, value=None, else_=None):
2839
2840 def __init__(self, *whens, **kw):
2841 r"""Produce a ``CASE`` expression.
2842
2843 The ``CASE`` construct in SQL is a conditional object that
2844 acts somewhat analogously to an "if/then" construct in other
2845 languages. It returns an instance of :class:`.Case`.
2846
2847 :func:`.case` in its usual form is passed a series of "when"
2848 constructs, that is, a list of conditions and results as tuples::
2849
2850 from sqlalchemy import case
2851
2852 stmt = select(users_table).\
2853 where(
2854 case(
2855 (users_table.c.name == 'wendy', 'W'),
2856 (users_table.c.name == 'jack', 'J'),
2857 else_='E'
2858 )
2859 )
2860
2861 The above statement will produce SQL resembling::
2862
2863 SELECT id, name FROM user
2864 WHERE CASE
2865 WHEN (name = :name_1) THEN :param_1
2866 WHEN (name = :name_2) THEN :param_2
2867 ELSE :param_3
2868 END
2869
2870 When simple equality expressions of several values against a single
2871 parent column are needed, :func:`.case` also has a "shorthand" format
2872 used via the
2873 :paramref:`.case.value` parameter, which is passed a column
2874 expression to be compared. In this form, the :paramref:`.case.whens`
2875 parameter is passed as a dictionary containing expressions to be
2876 compared against keyed to result expressions. The statement below is
2877 equivalent to the preceding statement::
2878
2879 stmt = select(users_table).\
2880 where(
2881 case(
2882 {"wendy": "W", "jack": "J"},
2883 value=users_table.c.name,
2884 else_='E'
2885 )
2886 )
2887
2888 The values which are accepted as result values in
2889 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
2890 coerced from Python literals into :func:`.bindparam` constructs.
2891 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
2892 are accepted
2893 as well. To coerce a literal string expression into a constant
2894 expression rendered inline, use the :func:`_expression.literal_column`
2895 construct,
2896 as in::
2897
2898 from sqlalchemy import case, literal_column
2899
2900 case(
2901 (
2902 orderline.c.qty > 100,
2903 literal_column("'greaterthan100'")
2904 ),
2905 (
2906 orderline.c.qty > 10,
2907 literal_column("'greaterthan10'")
2908 ),
2909 else_=literal_column("'lessthan10'")
2910 )
2911
2912 The above will render the given constants without using bound
2913 parameters for the result values (but still for the comparison
2914 values), as in::
2915
2916 CASE
2917 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
2918 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
2919 ELSE 'lessthan10'
2920 END
2921
2922 :param \*whens: The criteria to be compared against,
2923 :paramref:`.case.whens` accepts two different forms, based on
2924 whether or not :paramref:`.case.value` is used.
2925
2926 .. versionchanged:: 1.4 the :func:`_sql.case`
2927 function now accepts the series of WHEN conditions positionally;
2928 passing the expressions within a list is deprecated.
2929
2930 In the first form, it accepts a list of 2-tuples; each 2-tuple
2931 consists of ``(<sql expression>, <value>)``, where the SQL
2932 expression is a boolean expression and "value" is a resulting value,
2933 e.g.::
2934
2935 case(
2936 (users_table.c.name == 'wendy', 'W'),
2937 (users_table.c.name == 'jack', 'J')
2938 )
2939
2940 In the second form, it accepts a Python dictionary of comparison
2941 values mapped to a resulting value; this form requires
2942 :paramref:`.case.value` to be present, and values will be compared
2943 using the ``==`` operator, e.g.::
2944
2945 case(
2946 {"wendy": "W", "jack": "J"},
2947 value=users_table.c.name
2948 )
2949
2950 :param value: An optional SQL expression which will be used as a
2951 fixed "comparison point" for candidate values within a dictionary
2952 passed to :paramref:`.case.whens`.
2953
2954 :param else\_: An optional SQL expression which will be the evaluated
2955 result of the ``CASE`` construct if all expressions within
2956 :paramref:`.case.whens` evaluate to false. When omitted, most
2957 databases will produce a result of NULL if none of the "when"
2958 expressions evaluate to true.
2959
2960
2961 """
2962
2963 if "whens" in kw:
2964 util.warn_deprecated_20(
2965 'The "whens" argument to case() is now passed using '
2966 "positional style only, not as a keyword argument."
2967 )
2968 whens = (kw.pop("whens"),)
2969
2970 whens = coercions._expression_collection_was_a_list(
2971 "whens", "case", whens
2972 )
2973
2974 try:
2975 whens = util.dictlike_iteritems(whens)
2976 except TypeError:
2977 pass
2978
2979 value = kw.pop("value", None)
2980
2981 whenlist = [
2982 (
2983 coercions.expect(
2984 roles.ExpressionElementRole,
2985 c,
2986 apply_propagate_attrs=self,
2987 ).self_group(),
2988 coercions.expect(roles.ExpressionElementRole, r),
2989 )
2990 for (c, r) in whens
2991 ]
2992
2993 if whenlist:
2994 type_ = list(whenlist[-1])[-1].type
2995 else:
2996 type_ = None
2997
2998 if value is None:
2999 self.value = None
3000 else:
3001 self.value = coercions.expect(roles.ExpressionElementRole, value)
3002
3003 self.type = type_
3004 self.whens = whenlist
3005
3006 else_ = kw.pop("else_", None)
3007 if else_ is not None:
3008 self.else_ = coercions.expect(roles.ExpressionElementRole, else_)
3009 else:
3010 self.else_ = None
3011
3012 if kw:
3013 raise TypeError("unknown arguments: %s" % (", ".join(sorted(kw))))
3014
3015 @property
3016 def _from_objects(self):
3017 return list(
3018 itertools.chain(*[x._from_objects for x in self.get_children()])
3019 )
3020
3021
3022def literal_column(text, type_=None):
3023 r"""Produce a :class:`.ColumnClause` object that has the
3024 :paramref:`_expression.column.is_literal` flag set to True.
3025
3026 :func:`_expression.literal_column` is similar to
3027 :func:`_expression.column`, except that
3028 it is more often used as a "standalone" column expression that renders
3029 exactly as stated; while :func:`_expression.column`
3030 stores a string name that
3031 will be assumed to be part of a table and may be quoted as such,
3032 :func:`_expression.literal_column` can be that,
3033 or any other arbitrary column-oriented
3034 expression.
3035
3036 :param text: the text of the expression; can be any SQL expression.
3037 Quoting rules will not be applied. To specify a column-name expression
3038 which should be subject to quoting rules, use the :func:`column`
3039 function.
3040
3041 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine`
3042 object which will
3043 provide result-set translation and additional expression semantics for
3044 this column. If left as ``None`` the type will be :class:`.NullType`.
3045
3046 .. seealso::
3047
3048 :func:`_expression.column`
3049
3050 :func:`_expression.text`
3051
3052 :ref:`sqlexpression_literal_column`
3053
3054 """
3055 return ColumnClause(text, type_=type_, is_literal=True)
3056
3057
3058class Cast(WrapsColumnExpression, ColumnElement):
3059 """Represent a ``CAST`` expression.
3060
3061 :class:`.Cast` is produced using the :func:`.cast` factory function,
3062 as in::
3063
3064 from sqlalchemy import cast, Numeric
3065
3066 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
3067
3068 Details on :class:`.Cast` usage is at :func:`.cast`.
3069
3070 .. seealso::
3071
3072 :ref:`tutorial_casts`
3073
3074 :func:`.cast`
3075
3076 :func:`.type_coerce` - an alternative to CAST that coerces the type
3077 on the Python side only, which is often sufficient to generate the
3078 correct SQL and data coercion.
3079
3080 """
3081
3082 __visit_name__ = "cast"
3083
3084 _traverse_internals = [
3085 ("clause", InternalTraversal.dp_clauseelement),
3086 ("type", InternalTraversal.dp_type),
3087 ]
3088
3089 def __init__(self, expression, type_):
3090 r"""Produce a ``CAST`` expression.
3091
3092 :func:`.cast` returns an instance of :class:`.Cast`.
3093
3094 E.g.::
3095
3096 from sqlalchemy import cast, Numeric
3097
3098 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
3099
3100 The above statement will produce SQL resembling::
3101
3102 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
3103
3104 The :func:`.cast` function performs two distinct functions when
3105 used. The first is that it renders the ``CAST`` expression within
3106 the resulting SQL string. The second is that it associates the given
3107 type (e.g. :class:`.TypeEngine` class or instance) with the column
3108 expression on the Python side, which means the expression will take
3109 on the expression operator behavior associated with that type,
3110 as well as the bound-value handling and result-row-handling behavior
3111 of the type.
3112
3113 .. versionchanged:: 0.9.0 :func:`.cast` now applies the given type
3114 to the expression such that it takes effect on the bound-value,
3115 e.g. the Python-to-database direction, in addition to the
3116 result handling, e.g. database-to-Python, direction.
3117
3118 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
3119 This function performs the second task of associating an expression
3120 with a specific type, but does not render the ``CAST`` expression
3121 in SQL.
3122
3123 :param expression: A SQL expression, such as a
3124 :class:`_expression.ColumnElement`
3125 expression or a Python string which will be coerced into a bound
3126 literal value.
3127
3128 :param type\_: A :class:`.TypeEngine` class or instance indicating
3129 the type to which the ``CAST`` should apply.
3130
3131 .. seealso::
3132
3133 :ref:`tutorial_casts`
3134
3135 :func:`.type_coerce` - an alternative to CAST that coerces the type
3136 on the Python side only, which is often sufficient to generate the
3137 correct SQL and data coercion.
3138
3139
3140 """
3141 self.type = type_api.to_instance(type_)
3142 self.clause = coercions.expect(
3143 roles.ExpressionElementRole,
3144 expression,
3145 type_=self.type,
3146 apply_propagate_attrs=self,
3147 )
3148 self.typeclause = TypeClause(self.type)
3149
3150 @property
3151 def _from_objects(self):
3152 return self.clause._from_objects
3153
3154 @property
3155 def wrapped_column_expression(self):
3156 return self.clause
3157
3158
3159class TypeCoerce(WrapsColumnExpression, ColumnElement):
3160 """Represent a Python-side type-coercion wrapper.
3161
3162 :class:`.TypeCoerce` supplies the :func:`_expression.type_coerce`
3163 function; see that function for usage details.
3164
3165 .. versionchanged:: 1.1 The :func:`.type_coerce` function now produces
3166 a persistent :class:`.TypeCoerce` wrapper object rather than
3167 translating the given object in place.
3168
3169 .. seealso::
3170
3171 :func:`_expression.type_coerce`
3172
3173 :func:`.cast`
3174
3175 """
3176
3177 __visit_name__ = "type_coerce"
3178
3179 _traverse_internals = [
3180 ("clause", InternalTraversal.dp_clauseelement),
3181 ("type", InternalTraversal.dp_type),
3182 ]
3183
3184 def __init__(self, expression, type_):
3185 r"""Associate a SQL expression with a particular type, without rendering
3186 ``CAST``.
3187
3188 E.g.::
3189
3190 from sqlalchemy import type_coerce
3191
3192 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
3193
3194 The above construct will produce a :class:`.TypeCoerce` object, which
3195 does not modify the rendering in any way on the SQL side, with the
3196 possible exception of a generated label if used in a columns clause
3197 context::
3198
3199 SELECT date_string AS date_string FROM log
3200
3201 When result rows are fetched, the ``StringDateTime`` type processor
3202 will be applied to result rows on behalf of the ``date_string`` column.
3203
3204 .. note:: the :func:`.type_coerce` construct does not render any
3205 SQL syntax of its own, including that it does not imply
3206 parenthesization. Please use :meth:`.TypeCoerce.self_group`
3207 if explicit parenthesization is required.
3208
3209 In order to provide a named label for the expression, use
3210 :meth:`_expression.ColumnElement.label`::
3211
3212 stmt = select(
3213 type_coerce(log_table.date_string, StringDateTime()).label('date')
3214 )
3215
3216
3217 A type that features bound-value handling will also have that behavior
3218 take effect when literal values or :func:`.bindparam` constructs are
3219 passed to :func:`.type_coerce` as targets.
3220 For example, if a type implements the
3221 :meth:`.TypeEngine.bind_expression`
3222 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
3223 these functions will take effect at statement compilation/execution
3224 time when a literal value is passed, as in::
3225
3226 # bound-value handling of MyStringType will be applied to the
3227 # literal value "some string"
3228 stmt = select(type_coerce("some string", MyStringType))
3229
3230 When using :func:`.type_coerce` with composed expressions, note that
3231 **parenthesis are not applied**. If :func:`.type_coerce` is being
3232 used in an operator context where the parenthesis normally present from
3233 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method::
3234
3235 >>> some_integer = column("someint", Integer)
3236 >>> some_string = column("somestr", String)
3237 >>> expr = type_coerce(some_integer + 5, String) + some_string
3238 >>> print(expr)
3239 someint + :someint_1 || somestr
3240 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
3241 >>> print(expr)
3242 (someint + :someint_1) || somestr
3243
3244 :param expression: A SQL expression, such as a
3245 :class:`_expression.ColumnElement`
3246 expression or a Python string which will be coerced into a bound
3247 literal value.
3248
3249 :param type\_: A :class:`.TypeEngine` class or instance indicating
3250 the type to which the expression is coerced.
3251
3252 .. seealso::
3253
3254 :ref:`tutorial_casts`
3255
3256 :func:`.cast`
3257
3258 """ # noqa
3259 self.type = type_api.to_instance(type_)
3260 self.clause = coercions.expect(
3261 roles.ExpressionElementRole,
3262 expression,
3263 type_=self.type,
3264 apply_propagate_attrs=self,
3265 )
3266
3267 @property
3268 def _from_objects(self):
3269 return self.clause._from_objects
3270
3271 @HasMemoized.memoized_attribute
3272 def typed_expression(self):
3273 if isinstance(self.clause, BindParameter):
3274 bp = self.clause._clone()
3275 bp.type = self.type
3276 return bp
3277 else:
3278 return self.clause
3279
3280 @property
3281 def wrapped_column_expression(self):
3282 return self.clause
3283
3284 def self_group(self, against=None):
3285 grouped = self.clause.self_group(against=against)
3286 if grouped is not self.clause:
3287 return TypeCoerce(grouped, self.type)
3288 else:
3289 return self
3290
3291
3292class Extract(ColumnElement):
3293 """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``."""
3294
3295 __visit_name__ = "extract"
3296
3297 _traverse_internals = [
3298 ("expr", InternalTraversal.dp_clauseelement),
3299 ("field", InternalTraversal.dp_string),
3300 ]
3301
3302 def __init__(self, field, expr, **kwargs):
3303 """Return a :class:`.Extract` construct.
3304
3305 This is typically available as :func:`.extract`
3306 as well as ``func.extract`` from the
3307 :data:`.func` namespace.
3308
3309 :param field: The field to extract.
3310
3311 :param expr: A column or Python scalar expression serving as the
3312 right side of the ``EXTRACT`` expression.
3313
3314 E.g.::
3315
3316 from sqlalchemy import extract
3317 from sqlalchemy import table, column
3318
3319 logged_table = table("user",
3320 column("id"),
3321 column("date_created"),
3322 )
3323
3324 stmt = select(logged_table.c.id).where(
3325 extract("YEAR", logged_table.c.date_created) == 2021
3326 )
3327
3328 In the above example, the statement is used to select ids from the
3329 database where the ``YEAR`` component matches a specific value.
3330
3331 Similarly, one can also select an extracted component::
3332
3333 stmt = select(
3334 extract("YEAR", logged_table.c.date_created)
3335 ).where(logged_table.c.id == 1)
3336
3337 The implementation of ``EXTRACT`` may vary across database backends.
3338 Users are reminded to consult their database documentation.
3339 """
3340 self.type = type_api.INTEGERTYPE
3341 self.field = field
3342 self.expr = coercions.expect(roles.ExpressionElementRole, expr)
3343
3344 @property
3345 def _from_objects(self):
3346 return self.expr._from_objects
3347
3348
3349class _label_reference(ColumnElement):
3350 """Wrap a column expression as it appears in a 'reference' context.
3351
3352 This expression is any that includes an _order_by_label_element,
3353 which is a Label, or a DESC / ASC construct wrapping a Label.
3354
3355 The production of _label_reference() should occur when an expression
3356 is added to this context; this includes the ORDER BY or GROUP BY of a
3357 SELECT statement, as well as a few other places, such as the ORDER BY
3358 within an OVER clause.
3359
3360 """
3361
3362 __visit_name__ = "label_reference"
3363
3364 _traverse_internals = [("element", InternalTraversal.dp_clauseelement)]
3365
3366 def __init__(self, element):
3367 self.element = element
3368
3369 @property
3370 def _from_objects(self):
3371 return ()
3372
3373
3374class _textual_label_reference(ColumnElement):
3375 __visit_name__ = "textual_label_reference"
3376
3377 _traverse_internals = [("element", InternalTraversal.dp_string)]
3378
3379 def __init__(self, element):
3380 self.element = element
3381
3382 @util.memoized_property
3383 def _text_clause(self):
3384 return TextClause._create_text(self.element)
3385
3386
3387class UnaryExpression(ColumnElement):
3388 """Define a 'unary' expression.
3389
3390 A unary expression has a single column expression
3391 and an operator. The operator can be placed on the left
3392 (where it is called the 'operator') or right (where it is called the
3393 'modifier') of the column expression.
3394
3395 :class:`.UnaryExpression` is the basis for several unary operators
3396 including those used by :func:`.desc`, :func:`.asc`, :func:`.distinct`,
3397 :func:`.nulls_first` and :func:`.nulls_last`.
3398
3399 """
3400
3401 __visit_name__ = "unary"
3402
3403 _traverse_internals = [
3404 ("element", InternalTraversal.dp_clauseelement),
3405 ("operator", InternalTraversal.dp_operator),
3406 ("modifier", InternalTraversal.dp_operator),
3407 ]
3408
3409 def __init__(
3410 self,
3411 element,
3412 operator=None,
3413 modifier=None,
3414 type_=None,
3415 wraps_column_expression=False,
3416 ):
3417 self.operator = operator
3418 self.modifier = modifier
3419 self._propagate_attrs = element._propagate_attrs
3420 self.element = element.self_group(
3421 against=self.operator or self.modifier
3422 )
3423 self.type = type_api.to_instance(type_)
3424 self.wraps_column_expression = wraps_column_expression
3425
3426 @classmethod
3427 def _create_nulls_first(cls, column):
3428 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
3429
3430 :func:`.nulls_first` is intended to modify the expression produced
3431 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
3432 should be handled when they are encountered during ordering::
3433
3434
3435 from sqlalchemy import desc, nulls_first
3436
3437 stmt = select(users_table).order_by(
3438 nulls_first(desc(users_table.c.name)))
3439
3440 The SQL expression from the above would resemble::
3441
3442 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
3443
3444 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
3445 invoked from the column expression itself using
3446 :meth:`_expression.ColumnElement.nulls_first`,
3447 rather than as its standalone
3448 function version, as in::
3449
3450 stmt = select(users_table).order_by(
3451 users_table.c.name.desc().nulls_first())
3452
3453 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
3454 :func:`.nullsfirst` in previous releases.
3455 The previous name remains available for backwards compatibility.
3456
3457 .. seealso::
3458
3459 :func:`.asc`
3460
3461 :func:`.desc`
3462
3463 :func:`.nulls_last`
3464
3465 :meth:`_expression.Select.order_by`
3466
3467 """
3468 return UnaryExpression(
3469 coercions.expect(roles.ByOfRole, column),
3470 modifier=operators.nulls_first_op,
3471 wraps_column_expression=False,
3472 )
3473
3474 @classmethod
3475 def _create_nulls_last(cls, column):
3476 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
3477
3478 :func:`.nulls_last` is intended to modify the expression produced
3479 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
3480 should be handled when they are encountered during ordering::
3481
3482
3483 from sqlalchemy import desc, nulls_last
3484
3485 stmt = select(users_table).order_by(
3486 nulls_last(desc(users_table.c.name)))
3487
3488 The SQL expression from the above would resemble::
3489
3490 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
3491
3492 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
3493 invoked from the column expression itself using
3494 :meth:`_expression.ColumnElement.nulls_last`,
3495 rather than as its standalone
3496 function version, as in::
3497
3498 stmt = select(users_table).order_by(
3499 users_table.c.name.desc().nulls_last())
3500
3501 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
3502 :func:`.nullslast` in previous releases.
3503 The previous name remains available for backwards compatibility.
3504
3505 .. seealso::
3506
3507 :func:`.asc`
3508
3509 :func:`.desc`
3510
3511 :func:`.nulls_first`
3512
3513 :meth:`_expression.Select.order_by`
3514
3515 """
3516 return UnaryExpression(
3517 coercions.expect(roles.ByOfRole, column),
3518 modifier=operators.nulls_last_op,
3519 wraps_column_expression=False,
3520 )
3521
3522 @classmethod
3523 def _create_desc(cls, column):
3524 """Produce a descending ``ORDER BY`` clause element.
3525
3526 e.g.::
3527
3528 from sqlalchemy import desc
3529
3530 stmt = select(users_table).order_by(desc(users_table.c.name))
3531
3532 will produce SQL as::
3533
3534 SELECT id, name FROM user ORDER BY name DESC
3535
3536 The :func:`.desc` function is a standalone version of the
3537 :meth:`_expression.ColumnElement.desc`
3538 method available on all SQL expressions,
3539 e.g.::
3540
3541
3542 stmt = select(users_table).order_by(users_table.c.name.desc())
3543
3544 :param column: A :class:`_expression.ColumnElement` (e.g.
3545 scalar SQL expression)
3546 with which to apply the :func:`.desc` operation.
3547
3548 .. seealso::
3549
3550 :func:`.asc`
3551
3552 :func:`.nulls_first`
3553
3554 :func:`.nulls_last`
3555
3556 :meth:`_expression.Select.order_by`
3557
3558 """
3559 return UnaryExpression(
3560 coercions.expect(roles.ByOfRole, column),
3561 modifier=operators.desc_op,
3562 wraps_column_expression=False,
3563 )
3564
3565 @classmethod
3566 def _create_asc(cls, column):
3567 """Produce an ascending ``ORDER BY`` clause element.
3568
3569 e.g.::
3570
3571 from sqlalchemy import asc
3572 stmt = select(users_table).order_by(asc(users_table.c.name))
3573
3574 will produce SQL as::
3575
3576 SELECT id, name FROM user ORDER BY name ASC
3577
3578 The :func:`.asc` function is a standalone version of the
3579 :meth:`_expression.ColumnElement.asc`
3580 method available on all SQL expressions,
3581 e.g.::
3582
3583
3584 stmt = select(users_table).order_by(users_table.c.name.asc())
3585
3586 :param column: A :class:`_expression.ColumnElement` (e.g.
3587 scalar SQL expression)
3588 with which to apply the :func:`.asc` operation.
3589
3590 .. seealso::
3591
3592 :func:`.desc`
3593
3594 :func:`.nulls_first`
3595
3596 :func:`.nulls_last`
3597
3598 :meth:`_expression.Select.order_by`
3599
3600 """
3601 return UnaryExpression(
3602 coercions.expect(roles.ByOfRole, column),
3603 modifier=operators.asc_op,
3604 wraps_column_expression=False,
3605 )
3606
3607 @classmethod
3608 def _create_distinct(cls, expr):
3609 """Produce an column-expression-level unary ``DISTINCT`` clause.
3610
3611 This applies the ``DISTINCT`` keyword to an individual column
3612 expression, and is typically contained within an aggregate function,
3613 as in::
3614
3615 from sqlalchemy import distinct, func
3616 stmt = select(func.count(distinct(users_table.c.name)))
3617
3618 The above would produce an expression resembling::
3619
3620 SELECT COUNT(DISTINCT name) FROM user
3621
3622 The :func:`.distinct` function is also available as a column-level
3623 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
3624
3625 stmt = select(func.count(users_table.c.name.distinct()))
3626
3627 The :func:`.distinct` operator is different from the
3628 :meth:`_expression.Select.distinct` method of
3629 :class:`_expression.Select`,
3630 which produces a ``SELECT`` statement
3631 with ``DISTINCT`` applied to the result set as a whole,
3632 e.g. a ``SELECT DISTINCT`` expression. See that method for further
3633 information.
3634
3635 .. seealso::
3636
3637 :meth:`_expression.ColumnElement.distinct`
3638
3639 :meth:`_expression.Select.distinct`
3640
3641 :data:`.func`
3642
3643 """
3644 expr = coercions.expect(roles.ExpressionElementRole, expr)
3645 return UnaryExpression(
3646 expr,
3647 operator=operators.distinct_op,
3648 type_=expr.type,
3649 wraps_column_expression=False,
3650 )
3651
3652 @property
3653 def _order_by_label_element(self):
3654 if self.modifier in (operators.desc_op, operators.asc_op):
3655 return self.element._order_by_label_element
3656 else:
3657 return None
3658
3659 @property
3660 def _from_objects(self):
3661 return self.element._from_objects
3662
3663 def _negate(self):
3664 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity:
3665 return UnaryExpression(
3666 self.self_group(against=operators.inv),
3667 operator=operators.inv,
3668 type_=type_api.BOOLEANTYPE,
3669 wraps_column_expression=self.wraps_column_expression,
3670 )
3671 else:
3672 return ClauseElement._negate(self)
3673
3674 def self_group(self, against=None):
3675 if self.operator and operators.is_precedent(self.operator, against):
3676 return Grouping(self)
3677 else:
3678 return self
3679
3680
3681class CollectionAggregate(UnaryExpression):
3682 """Forms the basis for right-hand collection operator modifiers
3683 ANY and ALL.
3684
3685 The ANY and ALL keywords are available in different ways on different
3686 backends. On PostgreSQL, they only work for an ARRAY type. On
3687 MySQL, they only work for subqueries.
3688
3689 """
3690
3691 inherit_cache = True
3692
3693 @classmethod
3694 def _create_any(cls, expr):
3695 """Produce an ANY expression.
3696
3697 For dialects such as that of PostgreSQL, this operator applies
3698 to usage of the :class:`_types.ARRAY` datatype, for that of
3699 MySQL, it may apply to a subquery. e.g.::
3700
3701 # renders on PostgreSQL:
3702 # '5 = ANY (somearray)'
3703 expr = 5 == any_(mytable.c.somearray)
3704
3705 # renders on MySQL:
3706 # '5 = ANY (SELECT value FROM table)'
3707 expr = 5 == any_(select(table.c.value))
3708
3709 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
3710
3711 None == any_(mytable.c.somearray)
3712
3713 The any_() / all_() operators also feature a special "operand flipping"
3714 behavior such that if any_() / all_() are used on the left side of a
3715 comparison using a standalone operator such as ``==``, ``!=``, etc.
3716 (not including operator methods such as
3717 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
3718
3719 # would render '5 = ANY (column)`
3720 any_(mytable.c.column) == 5
3721
3722 Or with ``None``, which note will not perform
3723 the usual step of rendering "IS" as is normally the case for NULL::
3724
3725 # would render 'NULL = ANY(somearray)'
3726 any_(mytable.c.somearray) == None
3727
3728 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
3729 comparing to NULL on the right side to be flipped to the left.
3730
3731 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
3732 confused with :class:`_types.ARRAY` level
3733 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
3734 ``any_(col)``::
3735
3736 5 = mytable.c.somearray.any_()
3737
3738 .. seealso::
3739
3740 :meth:`_sql.ColumnOperators.any_`
3741
3742 :func:`_expression.all_`
3743
3744 """
3745
3746 expr = coercions.expect(roles.ExpressionElementRole, expr)
3747
3748 expr = expr.self_group()
3749 return CollectionAggregate(
3750 expr,
3751 operator=operators.any_op,
3752 type_=type_api.NULLTYPE,
3753 wraps_column_expression=False,
3754 )
3755
3756 @classmethod
3757 def _create_all(cls, expr):
3758 """Produce an ALL expression.
3759
3760 For dialects such as that of PostgreSQL, this operator applies
3761 to usage of the :class:`_types.ARRAY` datatype, for that of
3762 MySQL, it may apply to a subquery. e.g.::
3763
3764 # renders on PostgreSQL:
3765 # '5 = ALL (somearray)'
3766 expr = 5 == all_(mytable.c.somearray)
3767
3768 # renders on MySQL:
3769 # '5 = ALL (SELECT value FROM table)'
3770 expr = 5 == all_(select(table.c.value))
3771
3772 Comparison to NULL may work using ``None``::
3773
3774 None == all_(mytable.c.somearray)
3775
3776 The any_() / all_() operators also feature a special "operand flipping"
3777 behavior such that if any_() / all_() are used on the left side of a
3778 comparison using a standalone operator such as ``==``, ``!=``, etc.
3779 (not including operator methods such as
3780 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
3781
3782 # would render '5 = ALL (column)`
3783 all_(mytable.c.column) == 5
3784
3785 Or with ``None``, which note will not perform
3786 the usual step of rendering "IS" as is normally the case for NULL::
3787
3788 # would render 'NULL = ALL(somearray)'
3789 all_(mytable.c.somearray) == None
3790
3791 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
3792 comparing to NULL on the right side to be flipped to the left.
3793
3794 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
3795 confused with :class:`_types.ARRAY` level
3796 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
3797 ``all_(col)``::
3798
3799 5 == mytable.c.somearray.all_()
3800
3801 .. seealso::
3802
3803 :meth:`_sql.ColumnOperators.all_`
3804
3805 :func:`_expression.any_`
3806
3807 """
3808 expr = coercions.expect(roles.ExpressionElementRole, expr)
3809 expr = expr.self_group()
3810 return CollectionAggregate(
3811 expr,
3812 operator=operators.all_op,
3813 type_=type_api.NULLTYPE,
3814 wraps_column_expression=False,
3815 )
3816
3817 # operate and reverse_operate are hardwired to
3818 # dispatch onto the type comparator directly, so that we can
3819 # ensure "reversed" behavior.
3820 def operate(self, op, *other, **kwargs):
3821 if not operators.is_comparison(op):
3822 raise exc.ArgumentError(
3823 "Only comparison operators may be used with ANY/ALL"
3824 )
3825 kwargs["reverse"] = kwargs["_any_all_expr"] = True
3826 return self.comparator.operate(operators.mirror(op), *other, **kwargs)
3827
3828 def reverse_operate(self, op, other, **kwargs):
3829 # comparison operators should never call reverse_operate
3830 assert not operators.is_comparison(op)
3831 raise exc.ArgumentError(
3832 "Only comparison operators may be used with ANY/ALL"
3833 )
3834
3835
3836class AsBoolean(WrapsColumnExpression, UnaryExpression):
3837 inherit_cache = True
3838
3839 def __init__(self, element, operator, negate):
3840 self.element = element
3841 self.type = type_api.BOOLEANTYPE
3842 self.operator = operator
3843 self.negate = negate
3844 self.modifier = None
3845 self.wraps_column_expression = True
3846 self._is_implicitly_boolean = element._is_implicitly_boolean
3847
3848 @property
3849 def wrapped_column_expression(self):
3850 return self.element
3851
3852 def self_group(self, against=None):
3853 return self
3854
3855 def _negate(self):
3856 if isinstance(self.element, (True_, False_)):
3857 return self.element._negate()
3858 else:
3859 return AsBoolean(self.element, self.negate, self.operator)
3860
3861
3862class BinaryExpression(ColumnElement):
3863 """Represent an expression that is ``LEFT <operator> RIGHT``.
3864
3865 A :class:`.BinaryExpression` is generated automatically
3866 whenever two column expressions are used in a Python binary expression::
3867
3868 >>> from sqlalchemy.sql import column
3869 >>> column('a') + column('b')
3870 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
3871 >>> print(column('a') + column('b'))
3872 a + b
3873
3874 """
3875
3876 __visit_name__ = "binary"
3877
3878 _traverse_internals = [
3879 ("left", InternalTraversal.dp_clauseelement),
3880 ("right", InternalTraversal.dp_clauseelement),
3881 ("operator", InternalTraversal.dp_operator),
3882 ("negate", InternalTraversal.dp_operator),
3883 ("modifiers", InternalTraversal.dp_plain_dict),
3884 (
3885 "type",
3886 InternalTraversal.dp_type,
3887 ),
3888 ]
3889
3890 _cache_key_traversal = [
3891 ("left", InternalTraversal.dp_clauseelement),
3892 ("right", InternalTraversal.dp_clauseelement),
3893 ("operator", InternalTraversal.dp_operator),
3894 ("modifiers", InternalTraversal.dp_plain_dict),
3895 # "type" affects JSON CAST operators, so while redundant in most cases,
3896 # is needed for that one
3897 (
3898 "type",
3899 InternalTraversal.dp_type,
3900 ),
3901 ]
3902
3903 _is_implicitly_boolean = True
3904 """Indicates that any database will know this is a boolean expression
3905 even if the database does not have an explicit boolean datatype.
3906
3907 """
3908
3909 def __init__(
3910 self, left, right, operator, type_=None, negate=None, modifiers=None
3911 ):
3912 # allow compatibility with libraries that
3913 # refer to BinaryExpression directly and pass strings
3914 if isinstance(operator, util.string_types):
3915 operator = operators.custom_op(operator)
3916 self._orig = (left.__hash__(), right.__hash__())
3917 self._propagate_attrs = left._propagate_attrs or right._propagate_attrs
3918 self.left = left.self_group(against=operator)
3919 self.right = right.self_group(against=operator)
3920 self.operator = operator
3921 self.type = type_api.to_instance(type_)
3922 self.negate = negate
3923 self._is_implicitly_boolean = operators.is_boolean(operator)
3924
3925 if modifiers is None:
3926 self.modifiers = {}
3927 else:
3928 self.modifiers = modifiers
3929
3930 def __bool__(self):
3931 if self.operator in (operator.eq, operator.ne):
3932 return self.operator(*self._orig)
3933 else:
3934 raise TypeError("Boolean value of this clause is not defined")
3935
3936 __nonzero__ = __bool__
3937
3938 @property
3939 def is_comparison(self):
3940 return operators.is_comparison(self.operator)
3941
3942 @property
3943 def _from_objects(self):
3944 return self.left._from_objects + self.right._from_objects
3945
3946 def self_group(self, against=None):
3947
3948 if operators.is_precedent(self.operator, against):
3949 return Grouping(self)
3950 else:
3951 return self
3952
3953 def _negate(self):
3954 if self.negate is not None:
3955 return BinaryExpression(
3956 self.left,
3957 self.right._negate_in_binary(self.negate, self.operator),
3958 self.negate,
3959 negate=self.operator,
3960 type_=self.type,
3961 modifiers=self.modifiers,
3962 )
3963 else:
3964 return super(BinaryExpression, self)._negate()
3965
3966
3967class Slice(ColumnElement):
3968 """Represent SQL for a Python array-slice object.
3969
3970 This is not a specific SQL construct at this level, but
3971 may be interpreted by specific dialects, e.g. PostgreSQL.
3972
3973 """
3974
3975 __visit_name__ = "slice"
3976
3977 _traverse_internals = [
3978 ("start", InternalTraversal.dp_clauseelement),
3979 ("stop", InternalTraversal.dp_clauseelement),
3980 ("step", InternalTraversal.dp_clauseelement),
3981 ]
3982
3983 def __init__(self, start, stop, step, _name=None):
3984 self.start = coercions.expect(
3985 roles.ExpressionElementRole,
3986 start,
3987 name=_name,
3988 type_=type_api.INTEGERTYPE,
3989 )
3990 self.stop = coercions.expect(
3991 roles.ExpressionElementRole,
3992 stop,
3993 name=_name,
3994 type_=type_api.INTEGERTYPE,
3995 )
3996 self.step = coercions.expect(
3997 roles.ExpressionElementRole,
3998 step,
3999 name=_name,
4000 type_=type_api.INTEGERTYPE,
4001 )
4002 self.type = type_api.NULLTYPE
4003
4004 def self_group(self, against=None):
4005 assert against is operator.getitem
4006 return self
4007
4008
4009class IndexExpression(BinaryExpression):
4010 """Represent the class of expressions that are like an "index"
4011 operation."""
4012
4013 inherit_cache = True
4014
4015
4016class GroupedElement(ClauseElement):
4017 """Represent any parenthesized expression"""
4018
4019 __visit_name__ = "grouping"
4020
4021 def self_group(self, against=None):
4022 return self
4023
4024 def _ungroup(self):
4025 return self.element._ungroup()
4026
4027
4028class Grouping(GroupedElement, ColumnElement):
4029 """Represent a grouping within a column expression"""
4030
4031 _traverse_internals = [
4032 ("element", InternalTraversal.dp_clauseelement),
4033 ("type", InternalTraversal.dp_type),
4034 ]
4035
4036 _cache_key_traversal = [
4037 ("element", InternalTraversal.dp_clauseelement),
4038 ]
4039
4040 def __init__(self, element):
4041 self.element = element
4042 self.type = getattr(element, "type", type_api.NULLTYPE)
4043
4044 def _with_binary_element_type(self, type_):
4045 return self.__class__(self.element._with_binary_element_type(type_))
4046
4047 @util.memoized_property
4048 def _is_implicitly_boolean(self):
4049 return self.element._is_implicitly_boolean
4050
4051 @property
4052 def _tq_label(self):
4053 return (
4054 getattr(self.element, "_tq_label", None) or self._anon_name_label
4055 )
4056
4057 @property
4058 def _proxies(self):
4059 if isinstance(self.element, ColumnElement):
4060 return [self.element]
4061 else:
4062 return []
4063
4064 @property
4065 def _from_objects(self):
4066 return self.element._from_objects
4067
4068 def __getattr__(self, attr):
4069 return getattr(self.element, attr)
4070
4071 def __getstate__(self):
4072 return {"element": self.element, "type": self.type}
4073
4074 def __setstate__(self, state):
4075 self.element = state["element"]
4076 self.type = state["type"]
4077
4078
4079RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED")
4080RANGE_CURRENT = util.symbol("RANGE_CURRENT")
4081
4082
4083class Over(ColumnElement):
4084 """Represent an OVER clause.
4085
4086 This is a special operator against a so-called
4087 "window" function, as well as any aggregate function,
4088 which produces results relative to the result set
4089 itself. Most modern SQL backends now support window functions.
4090
4091 """
4092
4093 __visit_name__ = "over"
4094
4095 _traverse_internals = [
4096 ("element", InternalTraversal.dp_clauseelement),
4097 ("order_by", InternalTraversal.dp_clauseelement),
4098 ("partition_by", InternalTraversal.dp_clauseelement),
4099 ("range_", InternalTraversal.dp_plain_obj),
4100 ("rows", InternalTraversal.dp_plain_obj),
4101 ]
4102
4103 order_by = None
4104 partition_by = None
4105
4106 element = None
4107 """The underlying expression object to which this :class:`.Over`
4108 object refers towards."""
4109
4110 def __init__(
4111 self, element, partition_by=None, order_by=None, range_=None, rows=None
4112 ):
4113 r"""Produce an :class:`.Over` object against a function.
4114
4115 Used against aggregate or so-called "window" functions,
4116 for database backends that support window functions.
4117
4118 :func:`_expression.over` is usually called using
4119 the :meth:`.FunctionElement.over` method, e.g.::
4120
4121 func.row_number().over(order_by=mytable.c.some_column)
4122
4123 Would produce::
4124
4125 ROW_NUMBER() OVER(ORDER BY some_column)
4126
4127 Ranges are also possible using the :paramref:`.expression.over.range_`
4128 and :paramref:`.expression.over.rows` parameters. These
4129 mutually-exclusive parameters each accept a 2-tuple, which contains
4130 a combination of integers and None::
4131
4132 func.row_number().over(
4133 order_by=my_table.c.some_column, range_=(None, 0))
4134
4135 The above would produce::
4136
4137 ROW_NUMBER() OVER(ORDER BY some_column
4138 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
4139
4140 A value of ``None`` indicates "unbounded", a
4141 value of zero indicates "current row", and negative / positive
4142 integers indicate "preceding" and "following":
4143
4144 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
4145
4146 func.row_number().over(order_by='x', range_=(-5, 10))
4147
4148 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
4149
4150 func.row_number().over(order_by='x', rows=(None, 0))
4151
4152 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
4153
4154 func.row_number().over(order_by='x', range_=(-2, None))
4155
4156 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
4157
4158 func.row_number().over(order_by='x', range_=(1, 3))
4159
4160 .. versionadded:: 1.1 support for RANGE / ROWS within a window
4161
4162
4163 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
4164 or other compatible construct.
4165 :param partition_by: a column element or string, or a list
4166 of such, that will be used as the PARTITION BY clause
4167 of the OVER construct.
4168 :param order_by: a column element or string, or a list
4169 of such, that will be used as the ORDER BY clause
4170 of the OVER construct.
4171 :param range\_: optional range clause for the window. This is a
4172 tuple value which can contain integer values or ``None``,
4173 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
4174
4175 .. versionadded:: 1.1
4176
4177 :param rows: optional rows clause for the window. This is a tuple
4178 value which can contain integer values or None, and will render
4179 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
4180
4181 .. versionadded:: 1.1
4182
4183 This function is also available from the :data:`~.expression.func`
4184 construct itself via the :meth:`.FunctionElement.over` method.
4185
4186 .. seealso::
4187
4188 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
4189
4190 :data:`.expression.func`
4191
4192 :func:`_expression.within_group`
4193
4194 """
4195 self.element = element
4196 if order_by is not None:
4197 self.order_by = ClauseList(
4198 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole
4199 )
4200 if partition_by is not None:
4201 self.partition_by = ClauseList(
4202 *util.to_list(partition_by),
4203 _literal_as_text_role=roles.ByOfRole
4204 )
4205
4206 if range_:
4207 self.range_ = self._interpret_range(range_)
4208 if rows:
4209 raise exc.ArgumentError(
4210 "'range_' and 'rows' are mutually exclusive"
4211 )
4212 else:
4213 self.rows = None
4214 elif rows:
4215 self.rows = self._interpret_range(rows)
4216 self.range_ = None
4217 else:
4218 self.rows = self.range_ = None
4219
4220 def __reduce__(self):
4221 return self.__class__, (
4222 self.element,
4223 self.partition_by,
4224 self.order_by,
4225 self.range_,
4226 self.rows,
4227 )
4228
4229 def _interpret_range(self, range_):
4230 if not isinstance(range_, tuple) or len(range_) != 2:
4231 raise exc.ArgumentError("2-tuple expected for range/rows")
4232
4233 if range_[0] is None:
4234 lower = RANGE_UNBOUNDED
4235 else:
4236 try:
4237 lower = int(range_[0])
4238 except ValueError as err:
4239 util.raise_(
4240 exc.ArgumentError(
4241 "Integer or None expected for range value"
4242 ),
4243 replace_context=err,
4244 )
4245 else:
4246 if lower == 0:
4247 lower = RANGE_CURRENT
4248
4249 if range_[1] is None:
4250 upper = RANGE_UNBOUNDED
4251 else:
4252 try:
4253 upper = int(range_[1])
4254 except ValueError as err:
4255 util.raise_(
4256 exc.ArgumentError(
4257 "Integer or None expected for range value"
4258 ),
4259 replace_context=err,
4260 )
4261 else:
4262 if upper == 0:
4263 upper = RANGE_CURRENT
4264
4265 return lower, upper
4266
4267 @util.memoized_property
4268 def type(self):
4269 return self.element.type
4270
4271 @property
4272 def _from_objects(self):
4273 return list(
4274 itertools.chain(
4275 *[
4276 c._from_objects
4277 for c in (self.element, self.partition_by, self.order_by)
4278 if c is not None
4279 ]
4280 )
4281 )
4282
4283
4284class WithinGroup(ColumnElement):
4285 """Represent a WITHIN GROUP (ORDER BY) clause.
4286
4287 This is a special operator against so-called
4288 "ordered set aggregate" and "hypothetical
4289 set aggregate" functions, including ``percentile_cont()``,
4290 ``rank()``, ``dense_rank()``, etc.
4291
4292 It's supported only by certain database backends, such as PostgreSQL,
4293 Oracle and MS SQL Server.
4294
4295 The :class:`.WithinGroup` construct extracts its type from the
4296 method :meth:`.FunctionElement.within_group_type`. If this returns
4297 ``None``, the function's ``.type`` is used.
4298
4299 """
4300
4301 __visit_name__ = "withingroup"
4302
4303 _traverse_internals = [
4304 ("element", InternalTraversal.dp_clauseelement),
4305 ("order_by", InternalTraversal.dp_clauseelement),
4306 ]
4307
4308 order_by = None
4309
4310 def __init__(self, element, *order_by):
4311 r"""Produce a :class:`.WithinGroup` object against a function.
4312
4313 Used against so-called "ordered set aggregate" and "hypothetical
4314 set aggregate" functions, including :class:`.percentile_cont`,
4315 :class:`.rank`, :class:`.dense_rank`, etc.
4316
4317 :func:`_expression.within_group` is usually called using
4318 the :meth:`.FunctionElement.within_group` method, e.g.::
4319
4320 from sqlalchemy import within_group
4321 stmt = select(
4322 department.c.id,
4323 func.percentile_cont(0.5).within_group(
4324 department.c.salary.desc()
4325 )
4326 )
4327
4328 The above statement would produce SQL similar to
4329 ``SELECT department.id, percentile_cont(0.5)
4330 WITHIN GROUP (ORDER BY department.salary DESC)``.
4331
4332 :param element: a :class:`.FunctionElement` construct, typically
4333 generated by :data:`~.expression.func`.
4334 :param \*order_by: one or more column elements that will be used
4335 as the ORDER BY clause of the WITHIN GROUP construct.
4336
4337 .. versionadded:: 1.1
4338
4339 .. seealso::
4340
4341 :ref:`tutorial_functions_within_group` - in the
4342 :ref:`unified_tutorial`
4343
4344 :data:`.expression.func`
4345
4346 :func:`_expression.over`
4347
4348 """
4349 self.element = element
4350 if order_by is not None:
4351 self.order_by = ClauseList(
4352 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole
4353 )
4354
4355 def __reduce__(self):
4356 return self.__class__, (self.element,) + tuple(self.order_by)
4357
4358 def over(self, partition_by=None, order_by=None, range_=None, rows=None):
4359 """Produce an OVER clause against this :class:`.WithinGroup`
4360 construct.
4361
4362 This function has the same signature as that of
4363 :meth:`.FunctionElement.over`.
4364
4365 """
4366 return Over(
4367 self,
4368 partition_by=partition_by,
4369 order_by=order_by,
4370 range_=range_,
4371 rows=rows,
4372 )
4373
4374 @util.memoized_property
4375 def type(self):
4376 wgt = self.element.within_group_type(self)
4377 if wgt is not None:
4378 return wgt
4379 else:
4380 return self.element.type
4381
4382 @property
4383 def _from_objects(self):
4384 return list(
4385 itertools.chain(
4386 *[
4387 c._from_objects
4388 for c in (self.element, self.order_by)
4389 if c is not None
4390 ]
4391 )
4392 )
4393
4394
4395class FunctionFilter(ColumnElement):
4396 """Represent a function FILTER clause.
4397
4398 This is a special operator against aggregate and window functions,
4399 which controls which rows are passed to it.
4400 It's supported only by certain database backends.
4401
4402 Invocation of :class:`.FunctionFilter` is via
4403 :meth:`.FunctionElement.filter`::
4404
4405 func.count(1).filter(True)
4406
4407 .. versionadded:: 1.0.0
4408
4409 .. seealso::
4410
4411 :meth:`.FunctionElement.filter`
4412
4413 """
4414
4415 __visit_name__ = "funcfilter"
4416
4417 _traverse_internals = [
4418 ("func", InternalTraversal.dp_clauseelement),
4419 ("criterion", InternalTraversal.dp_clauseelement),
4420 ]
4421
4422 criterion = None
4423
4424 def __init__(self, func, *criterion):
4425 """Produce a :class:`.FunctionFilter` object against a function.
4426
4427 Used against aggregate and window functions,
4428 for database backends that support the "FILTER" clause.
4429
4430 E.g.::
4431
4432 from sqlalchemy import funcfilter
4433 funcfilter(func.count(1), MyClass.name == 'some name')
4434
4435 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
4436
4437 This function is also available from the :data:`~.expression.func`
4438 construct itself via the :meth:`.FunctionElement.filter` method.
4439
4440 .. versionadded:: 1.0.0
4441
4442 .. seealso::
4443
4444 :ref:`tutorial_functions_within_group` - in the
4445 :ref:`unified_tutorial`
4446
4447 :meth:`.FunctionElement.filter`
4448
4449 """
4450 self.func = func
4451 self.filter(*criterion)
4452
4453 def filter(self, *criterion):
4454 """Produce an additional FILTER against the function.
4455
4456 This method adds additional criteria to the initial criteria
4457 set up by :meth:`.FunctionElement.filter`.
4458
4459 Multiple criteria are joined together at SQL render time
4460 via ``AND``.
4461
4462
4463 """
4464
4465 for criterion in list(criterion):
4466 criterion = coercions.expect(roles.WhereHavingRole, criterion)
4467
4468 if self.criterion is not None:
4469 self.criterion = self.criterion & criterion
4470 else:
4471 self.criterion = criterion
4472
4473 return self
4474
4475 def over(self, partition_by=None, order_by=None, range_=None, rows=None):
4476 """Produce an OVER clause against this filtered function.
4477
4478 Used against aggregate or so-called "window" functions,
4479 for database backends that support window functions.
4480
4481 The expression::
4482
4483 func.rank().filter(MyClass.y > 5).over(order_by='x')
4484
4485 is shorthand for::
4486
4487 from sqlalchemy import over, funcfilter
4488 over(funcfilter(func.rank(), MyClass.y > 5), order_by='x')
4489
4490 See :func:`_expression.over` for a full description.
4491
4492 """
4493 return Over(
4494 self,
4495 partition_by=partition_by,
4496 order_by=order_by,
4497 range_=range_,
4498 rows=rows,
4499 )
4500
4501 def self_group(self, against=None):
4502 if operators.is_precedent(operators.filter_op, against):
4503 return Grouping(self)
4504 else:
4505 return self
4506
4507 @util.memoized_property
4508 def type(self):
4509 return self.func.type
4510
4511 @property
4512 def _from_objects(self):
4513 return list(
4514 itertools.chain(
4515 *[
4516 c._from_objects
4517 for c in (self.func, self.criterion)
4518 if c is not None
4519 ]
4520 )
4521 )
4522
4523
4524class Label(roles.LabeledColumnExprRole, ColumnElement):
4525 """Represents a column label (AS).
4526
4527 Represent a label, as typically applied to any column-level
4528 element using the ``AS`` sql keyword.
4529
4530 """
4531
4532 __visit_name__ = "label"
4533
4534 _traverse_internals = [
4535 ("name", InternalTraversal.dp_anon_name),
4536 ("_type", InternalTraversal.dp_type),
4537 ("_element", InternalTraversal.dp_clauseelement),
4538 ]
4539
4540 _cache_key_traversal = [
4541 ("name", InternalTraversal.dp_anon_name),
4542 ("_element", InternalTraversal.dp_clauseelement),
4543 ]
4544
4545 def __init__(self, name, element, type_=None):
4546 """Return a :class:`Label` object for the
4547 given :class:`_expression.ColumnElement`.
4548
4549 A label changes the name of an element in the columns clause of a
4550 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
4551
4552 This functionality is more conveniently available via the
4553 :meth:`_expression.ColumnElement.label` method on
4554 :class:`_expression.ColumnElement`.
4555
4556 :param name: label name
4557
4558 :param obj: a :class:`_expression.ColumnElement`.
4559
4560 """
4561
4562 orig_element = element
4563 element = coercions.expect(
4564 roles.ExpressionElementRole,
4565 element,
4566 apply_propagate_attrs=self,
4567 )
4568 while isinstance(element, Label):
4569 # TODO: this is only covered in test_text.py, but nothing
4570 # fails if it's removed. determine rationale
4571 element = element.element
4572
4573 if name:
4574 self.name = name
4575 else:
4576 self.name = _anonymous_label.safe_construct(
4577 id(self), getattr(element, "name", "anon")
4578 )
4579 if isinstance(orig_element, Label):
4580 # TODO: no coverage for this block, again would be in
4581 # test_text.py where the resolve_label concept is important
4582 self._resolve_label = orig_element._label
4583
4584 self.key = self._tq_label = self._tq_key_label = self.name
4585 self._element = element
4586 self._type = type_
4587 self._proxies = [element]
4588
4589 def __reduce__(self):
4590 return self.__class__, (self.name, self._element, self._type)
4591
4592 @util.memoized_property
4593 def _is_implicitly_boolean(self):
4594 return self.element._is_implicitly_boolean
4595
4596 @HasMemoized.memoized_attribute
4597 def _allow_label_resolve(self):
4598 return self.element._allow_label_resolve
4599
4600 @property
4601 def _order_by_label_element(self):
4602 return self
4603
4604 @util.memoized_property
4605 def type(self):
4606 return type_api.to_instance(
4607 self._type or getattr(self._element, "type", None)
4608 )
4609
4610 @HasMemoized.memoized_attribute
4611 def element(self):
4612 return self._element.self_group(against=operators.as_)
4613
4614 def self_group(self, against=None):
4615 return self._apply_to_inner(self._element.self_group, against=against)
4616
4617 def _negate(self):
4618 return self._apply_to_inner(self._element._negate)
4619
4620 def _apply_to_inner(self, fn, *arg, **kw):
4621 sub_element = fn(*arg, **kw)
4622 if sub_element is not self._element:
4623 return Label(self.name, sub_element, type_=self._type)
4624 else:
4625 return self
4626
4627 @property
4628 def primary_key(self):
4629 return self.element.primary_key
4630
4631 @property
4632 def foreign_keys(self):
4633 return self.element.foreign_keys
4634
4635 def _copy_internals(self, clone=_clone, anonymize_labels=False, **kw):
4636 self._reset_memoizations()
4637 self._element = clone(self._element, **kw)
4638 if anonymize_labels:
4639 self.name = _anonymous_label.safe_construct(
4640 id(self), getattr(self.element, "name", "anon")
4641 )
4642 self.key = self._tq_label = self._tq_key_label = self.name
4643
4644 @property
4645 def _from_objects(self):
4646 return self.element._from_objects
4647
4648 def _make_proxy(self, selectable, name=None, **kw):
4649 name = self.name if not name else name
4650
4651 key, e = self.element._make_proxy(
4652 selectable,
4653 name=name,
4654 disallow_is_literal=True,
4655 name_is_truncatable=isinstance(name, _truncated_label),
4656 )
4657
4658 # there was a note here to remove this assertion, which was here
4659 # to determine if we later could support a use case where
4660 # the key and name of a label are separate. But I don't know what
4661 # that case was. For now, this is an unexpected case that occurs
4662 # when a label name conflicts with other columns and select()
4663 # is attempting to disambiguate an explicit label, which is not what
4664 # the user would want. See issue #6090.
4665 if key != self.name and not isinstance(self.name, _anonymous_label):
4666 raise exc.InvalidRequestError(
4667 "Label name %s is being renamed to an anonymous label due "
4668 "to disambiguation "
4669 "which is not supported right now. Please use unique names "
4670 "for explicit labels." % (self.name)
4671 )
4672
4673 e._propagate_attrs = selectable._propagate_attrs
4674 e._proxies.append(self)
4675 if self._type is not None:
4676 e.type = self._type
4677
4678 return self.key, e
4679
4680
4681class NamedColumn(ColumnElement):
4682 is_literal = False
4683 table = None
4684
4685 def _compare_name_for_result(self, other):
4686 return (hasattr(other, "name") and self.name == other.name) or (
4687 hasattr(other, "_label") and self._label == other._label
4688 )
4689
4690 @util.memoized_property
4691 def description(self):
4692 if util.py3k:
4693 return self.name
4694 else:
4695 return self.name.encode("ascii", "backslashreplace")
4696
4697 @HasMemoized.memoized_attribute
4698 def _tq_key_label(self):
4699 """table qualified label based on column key.
4700
4701 for table-bound columns this is <tablename>_<column key/proxy key>;
4702
4703 all other expressions it resolves to key/proxy key.
4704
4705 """
4706 proxy_key = self._proxy_key
4707 if proxy_key and proxy_key != self.name:
4708 return self._gen_tq_label(proxy_key)
4709 else:
4710 return self._tq_label
4711
4712 @HasMemoized.memoized_attribute
4713 def _tq_label(self):
4714 """table qualified label based on column name.
4715
4716 for table-bound columns this is <tablename>_<columnname>; all other
4717 expressions it resolves to .name.
4718
4719 """
4720 return self._gen_tq_label(self.name)
4721
4722 @HasMemoized.memoized_attribute
4723 def _render_label_in_columns_clause(self):
4724 return True
4725
4726 @HasMemoized.memoized_attribute
4727 def _non_anon_label(self):
4728 return self.name
4729
4730 def _gen_tq_label(self, name, dedupe_on_key=True):
4731 return name
4732
4733 def _bind_param(self, operator, obj, type_=None, expanding=False):
4734 return BindParameter(
4735 self.key,
4736 obj,
4737 _compared_to_operator=operator,
4738 _compared_to_type=self.type,
4739 type_=type_,
4740 unique=True,
4741 expanding=expanding,
4742 )
4743
4744 def _make_proxy(
4745 self,
4746 selectable,
4747 name=None,
4748 name_is_truncatable=False,
4749 disallow_is_literal=False,
4750 **kw
4751 ):
4752 c = ColumnClause(
4753 coercions.expect(roles.TruncatedLabelRole, name or self.name)
4754 if name_is_truncatable
4755 else (name or self.name),
4756 type_=self.type,
4757 _selectable=selectable,
4758 is_literal=False,
4759 )
4760 c._propagate_attrs = selectable._propagate_attrs
4761 if name is None:
4762 c.key = self.key
4763 c._proxies = [self]
4764 if selectable._is_clone_of is not None:
4765 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
4766 return c.key, c
4767
4768
4769class ColumnClause(
4770 roles.DDLReferredColumnRole,
4771 roles.LabeledColumnExprRole,
4772 roles.StrAsPlainColumnRole,
4773 Immutable,
4774 NamedColumn,
4775):
4776 """Represents a column expression from any textual string.
4777
4778 The :class:`.ColumnClause`, a lightweight analogue to the
4779 :class:`_schema.Column` class, is typically invoked using the
4780 :func:`_expression.column` function, as in::
4781
4782 from sqlalchemy import column
4783
4784 id, name = column("id"), column("name")
4785 stmt = select(id, name).select_from("user")
4786
4787 The above statement would produce SQL like::
4788
4789 SELECT id, name FROM user
4790
4791 :class:`.ColumnClause` is the immediate superclass of the schema-specific
4792 :class:`_schema.Column` object. While the :class:`_schema.Column`
4793 class has all the
4794 same capabilities as :class:`.ColumnClause`, the :class:`.ColumnClause`
4795 class is usable by itself in those cases where behavioral requirements
4796 are limited to simple SQL expression generation. The object has none of
4797 the associations with schema-level metadata or with execution-time
4798 behavior that :class:`_schema.Column` does,
4799 so in that sense is a "lightweight"
4800 version of :class:`_schema.Column`.
4801
4802 Full details on :class:`.ColumnClause` usage is at
4803 :func:`_expression.column`.
4804
4805 .. seealso::
4806
4807 :func:`_expression.column`
4808
4809 :class:`_schema.Column`
4810
4811 """
4812
4813 table = None
4814 is_literal = False
4815
4816 __visit_name__ = "column"
4817
4818 _traverse_internals = [
4819 ("name", InternalTraversal.dp_anon_name),
4820 ("type", InternalTraversal.dp_type),
4821 ("table", InternalTraversal.dp_clauseelement),
4822 ("is_literal", InternalTraversal.dp_boolean),
4823 ]
4824
4825 onupdate = default = server_default = server_onupdate = None
4826
4827 _is_multiparam_column = False
4828
4829 @property
4830 def _is_star(self):
4831 return self.is_literal and self.name == "*"
4832
4833 def __init__(self, text, type_=None, is_literal=False, _selectable=None):
4834 """Produce a :class:`.ColumnClause` object.
4835
4836 The :class:`.ColumnClause` is a lightweight analogue to the
4837 :class:`_schema.Column` class. The :func:`_expression.column`
4838 function can
4839 be invoked with just a name alone, as in::
4840
4841 from sqlalchemy import column
4842
4843 id, name = column("id"), column("name")
4844 stmt = select(id, name).select_from("user")
4845
4846 The above statement would produce SQL like::
4847
4848 SELECT id, name FROM user
4849
4850 Once constructed, :func:`_expression.column`
4851 may be used like any other SQL
4852 expression element such as within :func:`_expression.select`
4853 constructs::
4854
4855 from sqlalchemy.sql import column
4856
4857 id, name = column("id"), column("name")
4858 stmt = select(id, name).select_from("user")
4859
4860 The text handled by :func:`_expression.column`
4861 is assumed to be handled
4862 like the name of a database column; if the string contains mixed case,
4863 special characters, or matches a known reserved word on the target
4864 backend, the column expression will render using the quoting
4865 behavior determined by the backend. To produce a textual SQL
4866 expression that is rendered exactly without any quoting,
4867 use :func:`_expression.literal_column` instead,
4868 or pass ``True`` as the
4869 value of :paramref:`_expression.column.is_literal`. Additionally,
4870 full SQL
4871 statements are best handled using the :func:`_expression.text`
4872 construct.
4873
4874 :func:`_expression.column` can be used in a table-like
4875 fashion by combining it with the :func:`.table` function
4876 (which is the lightweight analogue to :class:`_schema.Table`
4877 ) to produce
4878 a working table construct with minimal boilerplate::
4879
4880 from sqlalchemy import table, column, select
4881
4882 user = table("user",
4883 column("id"),
4884 column("name"),
4885 column("description"),
4886 )
4887
4888 stmt = select(user.c.description).where(user.c.name == 'wendy')
4889
4890 A :func:`_expression.column` / :func:`.table`
4891 construct like that illustrated
4892 above can be created in an
4893 ad-hoc fashion and is not associated with any
4894 :class:`_schema.MetaData`, DDL, or events, unlike its
4895 :class:`_schema.Table` counterpart.
4896
4897 .. versionchanged:: 1.0.0 :func:`_expression.column` can now
4898 be imported from the plain ``sqlalchemy`` namespace like any
4899 other SQL element.
4900
4901 :param text: the text of the element.
4902
4903 :param type: :class:`_types.TypeEngine` object which can associate
4904 this :class:`.ColumnClause` with a type.
4905
4906 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
4907 be an exact expression that will be delivered to the output with no
4908 quoting rules applied regardless of case sensitive settings. the
4909 :func:`_expression.literal_column()` function essentially invokes
4910 :func:`_expression.column` while passing ``is_literal=True``.
4911
4912 .. seealso::
4913
4914 :class:`_schema.Column`
4915
4916 :func:`_expression.literal_column`
4917
4918 :func:`.table`
4919
4920 :func:`_expression.text`
4921
4922 :ref:`tutorial_select_arbitrary_text`
4923
4924 """
4925 self.key = self.name = text
4926 self.table = _selectable
4927 self.type = type_api.to_instance(type_)
4928 self.is_literal = is_literal
4929
4930 def get_children(self, column_tables=False, **kw):
4931 # override base get_children() to not return the Table
4932 # or selectable that is parent to this column. Traversals
4933 # expect the columns of tables and subqueries to be leaf nodes.
4934 return []
4935
4936 @property
4937 def entity_namespace(self):
4938 if self.table is not None:
4939 return self.table.entity_namespace
4940 else:
4941 return super(ColumnClause, self).entity_namespace
4942
4943 def _clone(self, detect_subquery_cols=False, **kw):
4944 if (
4945 detect_subquery_cols
4946 and self.table is not None
4947 and self.table._is_subquery
4948 ):
4949 clone = kw.pop("clone")
4950 table = clone(self.table, **kw)
4951 new = table.c.corresponding_column(self)
4952 return new
4953
4954 return super(ColumnClause, self)._clone(**kw)
4955
4956 @HasMemoized.memoized_attribute
4957 def _from_objects(self):
4958 t = self.table
4959 if t is not None:
4960 return [t]
4961 else:
4962 return []
4963
4964 @HasMemoized.memoized_attribute
4965 def _render_label_in_columns_clause(self):
4966 return self.table is not None
4967
4968 @property
4969 def _ddl_label(self):
4970 return self._gen_tq_label(self.name, dedupe_on_key=False)
4971
4972 def _compare_name_for_result(self, other):
4973 if (
4974 self.is_literal
4975 or self.table is None
4976 or self.table._is_textual
4977 or not hasattr(other, "proxy_set")
4978 or (
4979 isinstance(other, ColumnClause)
4980 and (
4981 other.is_literal
4982 or other.table is None
4983 or other.table._is_textual
4984 )
4985 )
4986 ):
4987 return (hasattr(other, "name") and self.name == other.name) or (
4988 hasattr(other, "_tq_label")
4989 and self._tq_label == other._tq_label
4990 )
4991 else:
4992 return other.proxy_set.intersection(self.proxy_set)
4993
4994 def _gen_tq_label(self, name, dedupe_on_key=True):
4995 """generate table-qualified label
4996
4997 for a table-bound column this is <tablename>_<columnname>.
4998
4999 used primarily for LABEL_STYLE_TABLENAME_PLUS_COL
5000 as well as the .columns collection on a Join object.
5001
5002 """
5003 t = self.table
5004 if self.is_literal:
5005 return None
5006 elif t is not None and t.named_with_column:
5007 if getattr(t, "schema", None):
5008 label = t.schema.replace(".", "_") + "_" + t.name + "_" + name
5009 else:
5010 label = t.name + "_" + name
5011
5012 # propagate name quoting rules for labels.
5013 if getattr(name, "quote", None) is not None:
5014 if isinstance(label, quoted_name):
5015 label.quote = name.quote
5016 else:
5017 label = quoted_name(label, name.quote)
5018 elif getattr(t.name, "quote", None) is not None:
5019 # can't get this situation to occur, so let's
5020 # assert false on it for now
5021 assert not isinstance(label, quoted_name)
5022 label = quoted_name(label, t.name.quote)
5023
5024 if dedupe_on_key:
5025 # ensure the label name doesn't conflict with that of an
5026 # existing column. note that this implies that any Column
5027 # must **not** set up its _label before its parent table has
5028 # all of its other Column objects set up. There are several
5029 # tables in the test suite which will fail otherwise; example:
5030 # table "owner" has columns "name" and "owner_name". Therefore
5031 # column owner.name cannot use the label "owner_name", it has
5032 # to be "owner_name_1".
5033 if label in t.c:
5034 _label = label
5035 counter = 1
5036 while _label in t.c:
5037 _label = label + "_" + str(counter)
5038 counter += 1
5039 label = _label
5040
5041 return coercions.expect(roles.TruncatedLabelRole, label)
5042
5043 else:
5044 return name
5045
5046 def _make_proxy(
5047 self,
5048 selectable,
5049 name=None,
5050 name_is_truncatable=False,
5051 disallow_is_literal=False,
5052 **kw
5053 ):
5054 # the "is_literal" flag normally should never be propagated; a proxied
5055 # column is always a SQL identifier and never the actual expression
5056 # being evaluated. however, there is a case where the "is_literal" flag
5057 # might be used to allow the given identifier to have a fixed quoting
5058 # pattern already, so maintain the flag for the proxy unless a
5059 # :class:`.Label` object is creating the proxy. See [ticket:4730].
5060 is_literal = (
5061 not disallow_is_literal
5062 and self.is_literal
5063 and (
5064 # note this does not accommodate for quoted_name differences
5065 # right now
5066 name is None
5067 or name == self.name
5068 )
5069 )
5070 c = self._constructor(
5071 coercions.expect(roles.TruncatedLabelRole, name or self.name)
5072 if name_is_truncatable
5073 else (name or self.name),
5074 type_=self.type,
5075 _selectable=selectable,
5076 is_literal=is_literal,
5077 )
5078 c._propagate_attrs = selectable._propagate_attrs
5079 if name is None:
5080 c.key = self.key
5081 c._proxies = [self]
5082 if selectable._is_clone_of is not None:
5083 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
5084 return c.key, c
5085
5086
5087class TableValuedColumn(NamedColumn):
5088 __visit_name__ = "table_valued_column"
5089
5090 _traverse_internals = [
5091 ("name", InternalTraversal.dp_anon_name),
5092 ("type", InternalTraversal.dp_type),
5093 ("scalar_alias", InternalTraversal.dp_clauseelement),
5094 ]
5095
5096 def __init__(self, scalar_alias, type_):
5097 self.scalar_alias = scalar_alias
5098 self.key = self.name = scalar_alias.name
5099 self.type = type_
5100
5101 def _copy_internals(self, clone=_clone, **kw):
5102 self.scalar_alias = clone(self.scalar_alias, **kw)
5103 self.key = self.name = self.scalar_alias.name
5104
5105 @property
5106 def _from_objects(self):
5107 return [self.scalar_alias]
5108
5109
5110class CollationClause(ColumnElement):
5111 __visit_name__ = "collation"
5112
5113 _traverse_internals = [("collation", InternalTraversal.dp_string)]
5114
5115 def __init__(self, collation):
5116 self.collation = collation
5117
5118
5119class _IdentifiedClause(Executable, ClauseElement):
5120
5121 __visit_name__ = "identified"
5122 _execution_options = Executable._execution_options.union(
5123 {"autocommit": False}
5124 )
5125
5126 def __init__(self, ident):
5127 self.ident = ident
5128
5129
5130class SavepointClause(_IdentifiedClause):
5131 __visit_name__ = "savepoint"
5132 inherit_cache = False
5133
5134
5135class RollbackToSavepointClause(_IdentifiedClause):
5136 __visit_name__ = "rollback_to_savepoint"
5137 inherit_cache = False
5138
5139
5140class ReleaseSavepointClause(_IdentifiedClause):
5141 __visit_name__ = "release_savepoint"
5142 inherit_cache = False
5143
5144
5145class quoted_name(util.MemoizedSlots, util.text_type):
5146 """Represent a SQL identifier combined with quoting preferences.
5147
5148 :class:`.quoted_name` is a Python unicode/str subclass which
5149 represents a particular identifier name along with a
5150 ``quote`` flag. This ``quote`` flag, when set to
5151 ``True`` or ``False``, overrides automatic quoting behavior
5152 for this identifier in order to either unconditionally quote
5153 or to not quote the name. If left at its default of ``None``,
5154 quoting behavior is applied to the identifier on a per-backend basis
5155 based on an examination of the token itself.
5156
5157 A :class:`.quoted_name` object with ``quote=True`` is also
5158 prevented from being modified in the case of a so-called
5159 "name normalize" option. Certain database backends, such as
5160 Oracle, Firebird, and DB2 "normalize" case-insensitive names
5161 as uppercase. The SQLAlchemy dialects for these backends
5162 convert from SQLAlchemy's lower-case-means-insensitive convention
5163 to the upper-case-means-insensitive conventions of those backends.
5164 The ``quote=True`` flag here will prevent this conversion from occurring
5165 to support an identifier that's quoted as all lower case against
5166 such a backend.
5167
5168 The :class:`.quoted_name` object is normally created automatically
5169 when specifying the name for key schema constructs such as
5170 :class:`_schema.Table`, :class:`_schema.Column`, and others.
5171 The class can also be
5172 passed explicitly as the name to any function that receives a name which
5173 can be quoted. Such as to use the :meth:`_engine.Engine.has_table`
5174 method with
5175 an unconditionally quoted name::
5176
5177 from sqlalchemy import create_engine
5178 from sqlalchemy import inspect
5179 from sqlalchemy.sql import quoted_name
5180
5181 engine = create_engine("oracle+cx_oracle://some_dsn")
5182 print(inspect(engine).has_table(quoted_name("some_table", True)))
5183
5184 The above logic will run the "has table" logic against the Oracle backend,
5185 passing the name exactly as ``"some_table"`` without converting to
5186 upper case.
5187
5188 .. versionadded:: 0.9.0
5189
5190 .. versionchanged:: 1.2 The :class:`.quoted_name` construct is now
5191 importable from ``sqlalchemy.sql``, in addition to the previous
5192 location of ``sqlalchemy.sql.elements``.
5193
5194 """
5195
5196 __slots__ = "quote", "lower", "upper"
5197
5198 def __new__(cls, value, quote):
5199 if value is None:
5200 return None
5201 # experimental - don't bother with quoted_name
5202 # if quote flag is None. doesn't seem to make any dent
5203 # in performance however
5204 # elif not sprcls and quote is None:
5205 # return value
5206 elif isinstance(value, cls) and (
5207 quote is None or value.quote == quote
5208 ):
5209 return value
5210 self = super(quoted_name, cls).__new__(cls, value)
5211
5212 self.quote = quote
5213 return self
5214
5215 def __reduce__(self):
5216 return quoted_name, (util.text_type(self), self.quote)
5217
5218 def _memoized_method_lower(self):
5219 if self.quote:
5220 return self
5221 else:
5222 return util.text_type(self).lower()
5223
5224 def _memoized_method_upper(self):
5225 if self.quote:
5226 return self
5227 else:
5228 return util.text_type(self).upper()
5229
5230 def __repr__(self):
5231 if util.py2k:
5232 backslashed = self.encode("ascii", "backslashreplace")
5233 if not util.py2k:
5234 backslashed = backslashed.decode("ascii")
5235 return "'%s'" % backslashed
5236 else:
5237 return str.__repr__(self)
5238
5239
5240def _find_columns(clause):
5241 """locate Column objects within the given expression."""
5242
5243 cols = util.column_set()
5244 traverse(clause, {}, {"column": cols.add})
5245 return cols
5246
5247
5248def _type_from_args(args):
5249 for a in args:
5250 if not a.type._isnull:
5251 return a.type
5252 else:
5253 return type_api.NULLTYPE
5254
5255
5256def _corresponding_column_or_error(fromclause, column, require_embedded=False):
5257 c = fromclause.corresponding_column(
5258 column, require_embedded=require_embedded
5259 )
5260 if c is None:
5261 raise exc.InvalidRequestError(
5262 "Given column '%s', attached to table '%s', "
5263 "failed to locate a corresponding column from table '%s'"
5264 % (column, getattr(column, "table", None), fromclause.description)
5265 )
5266 return c
5267
5268
5269class AnnotatedColumnElement(Annotated):
5270 def __init__(self, element, values):
5271 Annotated.__init__(self, element, values)
5272 for attr in (
5273 "comparator",
5274 "_proxy_key",
5275 "_tq_key_label",
5276 "_tq_label",
5277 "_non_anon_label",
5278 ):
5279 self.__dict__.pop(attr, None)
5280 for attr in ("name", "key", "table"):
5281 if self.__dict__.get(attr, False) is None:
5282 self.__dict__.pop(attr)
5283
5284 def _with_annotations(self, values):
5285 clone = super(AnnotatedColumnElement, self)._with_annotations(values)
5286 clone.__dict__.pop("comparator", None)
5287 return clone
5288
5289 @util.memoized_property
5290 def name(self):
5291 """pull 'name' from parent, if not present"""
5292 return self._Annotated__element.name
5293
5294 @util.memoized_property
5295 def table(self):
5296 """pull 'table' from parent, if not present"""
5297 return self._Annotated__element.table
5298
5299 @util.memoized_property
5300 def key(self):
5301 """pull 'key' from parent, if not present"""
5302 return self._Annotated__element.key
5303
5304 @util.memoized_property
5305 def info(self):
5306 return self._Annotated__element.info
5307
5308 @util.memoized_property
5309 def _anon_name_label(self):
5310 return self._Annotated__element._anon_name_label
5311
5312
5313class _truncated_label(quoted_name):
5314 """A unicode subclass used to identify symbolic "
5315 "names that may require truncation."""
5316
5317 __slots__ = ()
5318
5319 def __new__(cls, value, quote=None):
5320 quote = getattr(value, "quote", quote)
5321 # return super(_truncated_label, cls).__new__(cls, value, quote, True)
5322 return super(_truncated_label, cls).__new__(cls, value, quote)
5323
5324 def __reduce__(self):
5325 return self.__class__, (util.text_type(self), self.quote)
5326
5327 def apply_map(self, map_):
5328 return self
5329
5330
5331class conv(_truncated_label):
5332 """Mark a string indicating that a name has already been converted
5333 by a naming convention.
5334
5335 This is a string subclass that indicates a name that should not be
5336 subject to any further naming conventions.
5337
5338 E.g. when we create a :class:`.Constraint` using a naming convention
5339 as follows::
5340
5341 m = MetaData(naming_convention={
5342 "ck": "ck_%(table_name)s_%(constraint_name)s"
5343 })
5344 t = Table('t', m, Column('x', Integer),
5345 CheckConstraint('x > 5', name='x5'))
5346
5347 The name of the above constraint will be rendered as ``"ck_t_x5"``.
5348 That is, the existing name ``x5`` is used in the naming convention as the
5349 ``constraint_name`` token.
5350
5351 In some situations, such as in migration scripts, we may be rendering
5352 the above :class:`.CheckConstraint` with a name that's already been
5353 converted. In order to make sure the name isn't double-modified, the
5354 new name is applied using the :func:`_schema.conv` marker. We can
5355 use this explicitly as follows::
5356
5357
5358 m = MetaData(naming_convention={
5359 "ck": "ck_%(table_name)s_%(constraint_name)s"
5360 })
5361 t = Table('t', m, Column('x', Integer),
5362 CheckConstraint('x > 5', name=conv('ck_t_x5')))
5363
5364 Where above, the :func:`_schema.conv` marker indicates that the constraint
5365 name here is final, and the name will render as ``"ck_t_x5"`` and not
5366 ``"ck_t_ck_t_x5"``
5367
5368 .. versionadded:: 0.9.4
5369
5370 .. seealso::
5371
5372 :ref:`constraint_naming_conventions`
5373
5374 """
5375
5376 __slots__ = ()
5377
5378
5379_NONE_NAME = util.symbol("NONE_NAME")
5380"""indicate a 'deferred' name that was ultimately the value None."""
5381
5382# for backwards compatibility in case
5383# someone is re-implementing the
5384# _truncated_identifier() sequence in a custom
5385# compiler
5386_generated_label = _truncated_label
5387
5388
5389class _anonymous_label(_truncated_label):
5390 """A unicode subclass used to identify anonymously
5391 generated names."""
5392
5393 __slots__ = ()
5394
5395 @classmethod
5396 def safe_construct(
5397 cls, seed, body, enclosing_label=None, sanitize_key=False
5398 ):
5399
5400 # need to escape chars that interfere with format
5401 # strings in any case, issue #8724
5402 body = re.sub(r"[%\(\) \$]+", "_", body)
5403
5404 if sanitize_key:
5405 # sanitize_key is then an extra step used by BindParameter
5406 body = body.strip("_")
5407
5408 label = "%%(%d %s)s" % (seed, body.replace("%", "%%"))
5409 if enclosing_label:
5410 label = "%s%s" % (enclosing_label, label)
5411
5412 return _anonymous_label(label)
5413
5414 def __add__(self, other):
5415 if "%" in other and not isinstance(other, _anonymous_label):
5416 other = util.text_type(other).replace("%", "%%")
5417 else:
5418 other = util.text_type(other)
5419
5420 return _anonymous_label(
5421 quoted_name(
5422 util.text_type.__add__(self, other),
5423 self.quote,
5424 )
5425 )
5426
5427 def __radd__(self, other):
5428 if "%" in other and not isinstance(other, _anonymous_label):
5429 other = util.text_type(other).replace("%", "%%")
5430 else:
5431 other = util.text_type(other)
5432
5433 return _anonymous_label(
5434 quoted_name(
5435 util.text_type.__add__(other, self),
5436 self.quote,
5437 )
5438 )
5439
5440 def apply_map(self, map_):
5441 if self.quote is not None:
5442 # preserve quoting only if necessary
5443 return quoted_name(self % map_, self.quote)
5444 else:
5445 # else skip the constructor call
5446 return self % map_