Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
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
8"""Core SQL expression elements, including :class:`_expression.ClauseElement`,
9:class:`_expression.ColumnElement`, and derived classes.
11"""
13from __future__ import unicode_literals
15import itertools
16import operator
17import re
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
47def collate(expression, collation):
48 """Return the clause ``expression COLLATE collation``.
50 e.g.::
52 collate(mycolumn, 'utf8_bin')
54 produces::
56 mycolumn COLLATE utf8_bin
58 The collation expression is also quoted if it is a case sensitive
59 identifier, e.g. contains uppercase characters.
61 .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
62 expressions if they are case sensitive.
64 """
66 expr = coercions.expect(roles.ExpressionElementRole, expression)
67 return BinaryExpression(
68 expr, CollationClause(collation), operators.collate, type_=expr.type
69 )
72def between(expr, lower_bound, upper_bound, symmetric=False):
73 """Produce a ``BETWEEN`` predicate clause.
75 E.g.::
77 from sqlalchemy import between
78 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
80 Would produce SQL resembling::
82 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
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::
88 stmt = select(users_table).where(users_table.c.id.between(5, 7))
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::
96 print(between(5, 3, 7))
98 Which would produce::
100 :param_1 BETWEEN :param_2 AND :param_3
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.
108 :param lower_bound: a column or Python scalar expression serving as the
109 lower bound of the right side of the ``BETWEEN`` expression.
111 :param upper_bound: a column or Python scalar expression serving as the
112 upper bound of the right side of the ``BETWEEN`` expression.
114 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
115 that not all databases support this syntax.
117 .. versionadded:: 0.9.5
119 .. seealso::
121 :meth:`_expression.ColumnElement.between`
123 """
124 expr = coercions.expect(roles.ExpressionElementRole, expr)
125 return expr.between(lower_bound, upper_bound, symmetric=symmetric)
128def literal(value, type_=None):
129 r"""Return a literal clause, bound to a bind parameter.
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.
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.
143 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which
144 will provide bind-parameter translation for this literal.
146 """
147 return coercions.expect(roles.LiteralValueRole, value, type_=type_)
150def outparam(key, type_=None):
151 r"""Create an 'OUT' parameter for usage in functions (stored procedures),
152 for databases which support them.
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.
159 """
160 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
163def not_(clause):
164 """Return a negation of the given clause, i.e. ``NOT(clause)``.
166 The ``~`` operator is also overloaded on all
167 :class:`_expression.ColumnElement` subclasses to produce the
168 same result.
170 """
171 return operators.inv(coercions.expect(roles.ExpressionElementRole, clause))
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.
185 """
187 __visit_name__ = "clause"
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.
193 """
195 supports_execution = False
197 stringify_dialect = "default"
199 _from_objects = []
200 bind = None
201 description = None
202 _is_clone_of = None
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
222 _order_by_label_element = None
224 _cache_key_traversal = None
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
231 # assert not self._propagate_attrs
233 self._propagate_attrs = util.immutabledict(values)
234 return self
236 def _clone(self, **kw):
237 """Create a shallow copy of this ClauseElement.
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.
243 """
244 skip = self._memoized_keys
245 c = self.__class__.__new__(self.__class__)
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()
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
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.
268 Used for the special case of expanding bind parameter with IN.
270 """
271 return self
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.
277 applies only to :class:`_expression.ColumnElement` classes.
279 """
280 return self
282 @property
283 def _constructor(self):
284 """return the 'constructor' for this ClauseElement.
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.
291 """
292 return self.__class__
294 @HasMemoized.memoized_attribute
295 def _cloned_set(self):
296 """Return the set consisting all cloned ancestors of this
297 ClauseElement.
299 Includes this ClauseElement. This accessor tends to be used for
300 FromClause objects to identify 'equivalent' FROM clauses, regardless
301 of transformative operations.
303 """
304 s = util.column_set()
305 f = self
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
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 )
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
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)
340 def unique_params(self, *optionaldict, **kwargs):
341 """Return a copy with :func:`_expression.bindparam` elements
342 replaced.
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.
349 """
350 return self._replace_params(True, optionaldict, kwargs)
352 def params(self, *optionaldict, **kwargs):
353 """Return a copy with :func:`_expression.bindparam` elements
354 replaced.
356 Returns a copy of this ClauseElement with
357 :func:`_expression.bindparam`
358 elements replaced with values taken from the given dictionary::
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}
366 """
367 return self._replace_params(False, optionaldict, kwargs)
369 def _replace_params(self, unique, optionaldict, kwargs):
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 )
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()
385 return cloned_traverse(
386 self,
387 {"maintain_key": True, "detect_subquery_cols": True},
388 {"bindparam": visit_bindparam},
389 )
391 def compare(self, other, **kw):
392 r"""Compare this :class:`_expression.ClauseElement` to
393 the given :class:`_expression.ClauseElement`.
395 Subclasses should override the default behavior, which is a
396 straight identity comparison.
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`).
402 """
403 return traversals.compare(self, other, **kw)
405 def self_group(self, against=None):
406 """Apply a 'grouping' to this :class:`_expression.ClauseElement`.
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).
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.
426 The base :meth:`self_group` method of
427 :class:`_expression.ClauseElement`
428 just returns self.
429 """
430 return self
432 def _ungroup(self):
433 """Return this :class:`_expression.ClauseElement`
434 without any groupings.
435 """
437 return self
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.
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.
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.
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.
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.
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::
472 from sqlalchemy.sql import table, column, select
474 t = table('t', column('x'))
476 s = select(t).where(t.c.x == 5)
478 print(s.compile(compile_kwargs={"literal_binds": True}))
480 .. versionadded:: 0.9.0
482 .. seealso::
484 :ref:`faq_sql_expression_string`
486 """
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()()
503 return self._compiler(dialect, **kw)
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
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)
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 )
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
561 return compiled_sql, extracted_params, cache_hit
563 def _compiler(self, dialect, **kw):
564 """Return a compiler appropriate for this ClauseElement, given a
565 Dialect."""
567 return dialect.statement_compiler(dialect, self, **kw)
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
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()
585 def _negate(self):
586 return UnaryExpression(
587 self.self_group(against=operators.inv), operator=operators.inv
588 )
590 def __bool__(self):
591 raise TypeError("Boolean value of this clause is not defined")
593 __nonzero__ = __bool__
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 )
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.
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.
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:
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.
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.
668 * The Python ``None`` value is typically interpreted as ``NULL``,
669 which in SQLAlchemy Core produces an instance of :func:`.null`.
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`::
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
691 .. seealso::
693 :class:`_schema.Column`
695 :func:`_expression.column`
697 """
699 __visit_name__ = "column_element"
700 primary_key = False
701 foreign_keys = []
702 _proxies = ()
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.
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.
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.
717 .. versionchanged:: 1.4.21 renamed from ``._label``
719 """
721 key = None
722 """The 'key' that in some circumstances refers to this object in a
723 Python namespace.
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".
729 """
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.
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.
744 .. versionchanged:: 1.4.21 renamed from ``._key_label``
746 """
747 return self._proxy_key
749 @property
750 def _key_label(self):
751 """legacy; renamed to _tq_key_label"""
752 return self._tq_key_label
754 @property
755 def _label(self):
756 """legacy; renamed to _tq_label"""
757 return self._tq_label
759 @property
760 def _non_anon_label(self):
761 """the 'name' that naturally applies this element when rendered in
762 SQL.
764 Concretely, this is the "name" of a column or a label in a
765 SELECT statement; ``<columnname>`` and ``<labelname>`` below::
767 SELECT <columnmame> FROM table
769 SELECT column AS <labelname> FROM table
771 Above, the two names noted will be what's present in the DBAPI
772 ``cursor.description`` as the names.
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.
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``.
788 .. versionadded:: 1.4.21
792 """
793 return getattr(self, "name", None)
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.
803 """
805 _allow_label_resolve = True
806 """A flag that can be flipped to prevent a column from being resolvable
807 by string label name.
809 The joined eager loader strategy in the ORM uses this, for example.
811 """
813 _is_implicitly_boolean = False
815 _alt_names = ()
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
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()
834 @util.memoized_property
835 def type(self):
836 return type_api.NULLTYPE
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)
853 def __setstate__(self, state):
854 self.__dict__.update(state)
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 )
872 def operate(self, op, *other, **kwargs):
873 return op(self.comparator, *other, **kwargs)
875 def reverse_operate(self, op, other, **kwargs):
876 return op(other, self.comparator, **kwargs)
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 )
889 @property
890 def expression(self):
891 """Return a column expression.
893 Part of the inspection interface; returns self.
895 """
896 return self
898 @property
899 def _select_iterable(self):
900 return (self,)
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)
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
913 def _uncached_proxy_list(self):
914 """An 'uncached' version of proxy set.
916 This is so that we can read annotations from the list of columns
917 without breaking the caching of the above proxy_set.
919 """
920 s = [self]
921 for c in self._proxies:
922 s.extend(c._uncached_proxy_list())
923 return s
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`."""
929 return bool(self.proxy_set.intersection(othercolumn.proxy_set))
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."""
935 return (
936 hasattr(other, "name")
937 and hasattr(self, "name")
938 and other.name == self.name
939 )
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"]
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
954 if isinstance(name, _anonymous_label):
955 return None
956 else:
957 return name
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.
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.
968 """
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
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.
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
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 )
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
1006 def cast(self, type_):
1007 """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.
1009 This is a shortcut to the :func:`_expression.cast` function.
1011 .. seealso::
1013 :ref:`tutorial_casts`
1015 :func:`_expression.cast`
1017 :func:`_expression.type_coerce`
1019 .. versionadded:: 1.0.7
1021 """
1022 return Cast(self, type_)
1024 def label(self, name):
1025 """Produce a column label, i.e. ``<columnname> AS <name>``.
1027 This is a shortcut to the :func:`_expression.label` function.
1029 If 'name' is ``None``, an anonymous label name will be generated.
1031 """
1032 return Label(name, self, self.type)
1034 def _anon_label(self, seed, add_hash=None):
1035 while self._is_clone_of is not None:
1036 self = self._is_clone_of
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)
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.
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
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 + "_"
1061 if isinstance(seed, _anonymous_label):
1062 return _anonymous_label.safe_construct(
1063 hash_value, "", enclosing_label=seed
1064 )
1066 return _anonymous_label.safe_construct(hash_value, seed or "anon")
1068 @util.memoized_property
1069 def _anon_name_label(self):
1070 """Provides a constant 'anonymous label' for this ColumnElement.
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.
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.
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
1085 """
1086 name = getattr(self, "name", None)
1087 return self._anon_label(name)
1089 @util.memoized_property
1090 def _anon_key_label(self):
1091 """Provides a constant 'anonymous key label' for this ColumnElement.
1093 Compare to ``anon_label``, except that the "key" of the column,
1094 if available, is used to generate the label.
1096 This is used when a deduplicating key is placed into the columns
1097 collection of a selectable.
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
1103 """
1104 return self._anon_label(self._proxy_key)
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
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
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.
1129 these labels come out like "foo_bar_id__1" and have double underscores
1130 in them.
1132 """
1133 label = getattr(self, "name", None)
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"
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)
1147 @util.memoized_property
1148 def _anon_tq_label(self):
1149 return self._anon_label(getattr(self, "_tq_label", None))
1151 @util.memoized_property
1152 def _anon_tq_key_label(self):
1153 return self._anon_label(getattr(self, "_tq_key_label", None))
1155 def _dedupe_anon_tq_label_idx(self, idx):
1156 label = getattr(self, "_tq_label", None) or "anon"
1158 return self._anon_label(label, add_hash=idx)
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.
1166 .. versionadded:: 1.4
1168 .. seealso::
1170 :ref:`change_4449`
1173 """
1175 @property
1176 def wrapped_column_expression(self):
1177 raise NotImplementedError()
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
1187 _label = _tq_label
1189 @property
1190 def _non_anon_label(self):
1191 return None
1193 @property
1194 def _anon_name_label(self):
1195 wce = self.wrapped_column_expression
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
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)
1216 @property
1217 def _proxy_key(self):
1218 wce = self.wrapped_column_expression
1220 if not wce._is_text_clause:
1221 return wce._proxy_key
1222 return super(WrapsColumnExpression, self)._proxy_key
1225class BindParameter(roles.InElementRole, ColumnElement):
1226 r"""Represent a "bound expression".
1228 :class:`.BindParameter` is invoked explicitly using the
1229 :func:`.bindparam` function, as in::
1231 from sqlalchemy import bindparam
1233 stmt = select(users_table).\
1234 where(users_table.c.name == bindparam('username'))
1236 Detailed discussion of how :class:`.BindParameter` is used is
1237 at :func:`.bindparam`.
1239 .. seealso::
1241 :func:`.bindparam`
1243 """
1245 __visit_name__ = "bindparam"
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 ]
1255 _is_crud = False
1256 _is_bind_parameter = True
1257 _key_is_anon = False
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
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".
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.
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.
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::
1302 from sqlalchemy import bindparam
1304 stmt = select(users_table).\
1305 where(users_table.c.name == bindparam('username'))
1307 The above statement, when rendered, will produce SQL similar to::
1309 SELECT id, name FROM user WHERE name = :username
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`::
1315 result = connection.execute(stmt, username='wendy')
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::
1322 stmt = (users_table.update().
1323 where(user_table.c.name == bindparam('username')).
1324 values(fullname=bindparam('fullname'))
1325 )
1327 connection.execute(
1328 stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
1329 {"username": "jack", "fullname": "Jack Jones"},
1330 ]
1331 )
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::
1339 expr = users_table.c.name == 'Wendy'
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::
1346 print(repr(expr.right))
1347 BindParameter('%(4327771088 name)s', 'Wendy', type_=String())
1349 The expression above will render SQL such as::
1351 user.name = :name_1
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::
1358 stmt = select(users_table).where(users_table.c.name == 'Wendy')
1359 result = connection.execute(stmt)
1361 We would see SQL logging output as::
1363 SELECT "user".id, "user".name
1364 FROM "user"
1365 WHERE "user".name = %(name_1)s
1366 {'name_1': 'Wendy'}
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.
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::
1378 stmt = users_table.insert()
1379 result = connection.execute(stmt, name='Wendy')
1381 The above will produce SQL output as::
1383 INSERT INTO "user" (name) VALUES (%(name)s)
1384 {'name': 'Wendy'}
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.
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.
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``.
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.
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.
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.
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.
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``.
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.
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.
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.
1466 .. seealso::
1468 :meth:`.ColumnOperators.in_`
1470 :ref:`baked_in` - with baked queries
1472 .. note:: The "expanding" feature does not support "executemany"-
1473 style parameter sets.
1475 .. versionadded:: 1.2
1477 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
1478 supports empty lists.
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.
1495 .. versionadded:: 1.4 Added "post compile" bound parameters
1497 .. seealso::
1499 :ref:`change_4808`.
1501 .. seealso::
1503 :ref:`tutorial_sending_parameters` - in the
1504 :ref:`unified_tutorial`
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
1512 if quote is not None:
1513 key = quoted_name(key, quote)
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
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
1535 # key that was passed in the first place, used to
1536 # generate new keys
1537 self._orig_key = key or "param"
1539 self.unique = unique
1540 self.value = value
1541 self.callable = callable_
1542 self.isoutparam = isoutparam
1543 self.required = required
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
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
1555 self.literal_execute = literal_execute
1556 if _is_crud:
1557 self._is_crud = True
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_
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
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.
1599 The ``callable`` value will be evaluated
1600 and returned if present, else ``value``.
1602 """
1603 if self.callable:
1604 return self.callable()
1605 else:
1606 return self.value
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.
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.
1622 .. versionadded:: 1.4.5
1624 .. seealso::
1626 :ref:`engine_thirdparty_caching`
1628 """
1629 return self.__class__(
1630 self.key,
1631 self.value,
1632 type_=self.type,
1633 literal_execute=True,
1634 )
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
1644 def _with_binary_element_type(self, type_):
1645 c = ClauseElement._clone(self)
1646 c.type = type_
1647 return c
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
1666 def _gen_cache_key(self, anon_map, bindparams):
1667 _gen_cache_ok = self.__class__.__dict__.get("inherit_cache", False)
1669 if not _gen_cache_ok:
1670 if anon_map is not None:
1671 anon_map[NO_CACHE] = True
1672 return None
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
1683 if bindparams is not None:
1684 bindparams.append(self)
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 )
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 )
1701 def __getstate__(self):
1702 """execute a deferred value for serialization purposes."""
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
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)
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 )
1728class TypeClause(ClauseElement):
1729 """Handle a type keyword in a SQL statement.
1731 Used by the ``Case`` statement.
1733 """
1735 __visit_name__ = "typeclause"
1737 _traverse_internals = [("type", InternalTraversal.dp_type)]
1739 def __init__(self, type_):
1740 self.type = type_
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.
1758 E.g.::
1760 from sqlalchemy import text
1762 t = text("SELECT * FROM users")
1763 result = connection.execute(t)
1766 The :class:`_expression.TextClause` construct is produced using the
1767 :func:`_expression.text`
1768 function; see that function for full documentation.
1770 .. seealso::
1772 :func:`_expression.text`
1774 """
1776 __visit_name__ = "textclause"
1778 _traverse_internals = [
1779 ("_bindparams", InternalTraversal.dp_string_clauseelement_dict),
1780 ("text", InternalTraversal.dp_string),
1781 ]
1783 _is_text_clause = True
1785 _is_textual = True
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
1793 _render_label_in_columns_clause = False
1795 _hide_froms = ()
1797 def __and__(self, other):
1798 # support use in select.where(), query.filter()
1799 return and_(self, other)
1801 @property
1802 def _select_iterable(self):
1803 return (self,)
1805 # help in those cases where text() is
1806 # interpreted in a column expression situation
1807 key = _label = None
1809 _allow_label_resolve = False
1811 @property
1812 def _is_star(self):
1813 return self.text == "*"
1815 def __init__(self, text, bind=None):
1816 self._bind = bind
1817 self._bindparams = {}
1819 def repl(m):
1820 self._bindparams[m.group(1)] = BindParameter(m.group(1))
1821 return ":%s" % m.group(1)
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)
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.
1841 E.g.::
1843 from sqlalchemy import text
1845 t = text("SELECT * FROM users")
1846 result = connection.execute(t)
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.
1858 Bind parameters are specified by name, using the format ``:name``.
1859 E.g.::
1861 t = text("SELECT * FROM users WHERE id=:user_id")
1862 result = connection.execute(t, user_id=12)
1864 For SQL statements where a colon is required verbatim, as within
1865 an inline string, use a backslash to escape::
1867 t = text("SELECT * FROM users WHERE name='\:username'")
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::
1880 t = text("SELECT * FROM users WHERE id=:user_id").\
1881 bindparams(user_id=7).\
1882 columns(id=Integer, name=String)
1884 for id, name in connection.execute(t):
1885 print(id, name)
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::
1891 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1892 result = connection.execute(s, user_id=12)
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::
1904 t = text("EXEC my_procedural_thing()").\
1905 execution_options(autocommit=True)
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.
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.
1916 :param bind:
1917 an optional connection or engine to be used for this text query.
1919 .. seealso::
1921 :ref:`tutorial_select_arbitrary_text`
1924 """
1925 return TextClause(text, bind=bind)
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.
1932 Given a text construct such as::
1934 from sqlalchemy import text
1935 stmt = text("SELECT id, name FROM user WHERE name=:name "
1936 "AND timestamp=:timestamp")
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::
1943 stmt = stmt.bindparams(name='jack',
1944 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
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`.
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::
1958 from sqlalchemy import bindparam
1959 stmt = stmt.bindparams(
1960 bindparam('name', value='jack', type_=String),
1961 bindparam('timestamp', type_=DateTime)
1962 )
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"``.
1969 Additional bound parameters can be supplied at statement execution
1970 time, e.g.::
1972 result = connection.execute(stmt,
1973 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
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::
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 )
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::
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 )
2011 union = union_all(
2012 stmt1.columns(column("id")),
2013 stmt2.columns(column("id"))
2014 )
2016 The above statement will render as::
2018 select id from table where name=:name_1
2019 UNION ALL select id from table where name=:name_2
2021 .. versionadded:: 1.3.11 Added support for the
2022 :paramref:`.BindParameter.unique` flag to work with
2023 :func:`_expression.text`
2024 constructs.
2026 """
2027 self._bindparams = new_params = self._bindparams.copy()
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
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)
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.
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.
2074 This function essentially bridges the gap between an entirely
2075 textual SELECT statement and the SQL expression language concept
2076 of a "selectable"::
2078 from sqlalchemy.sql import column, text
2080 stmt = text("SELECT id, name FROM some_table")
2081 stmt = stmt.columns(column('id'), column('name')).subquery('st')
2083 stmt = select(mytable).\
2084 select_from(
2085 mytable.join(stmt, mytable.c.name == stmt.c.name)
2086 ).where(stmt.c.id > 5)
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.
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::
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 )
2112 for id, name, timestamp in connection.execute(stmt):
2113 print(id, name, timestamp)
2115 As a shortcut to the above syntax, keyword arguments referring to
2116 types alone may be used, if only type conversion is needed::
2118 stmt = text("SELECT id, name, timestamp FROM some_table")
2119 stmt = stmt.columns(
2120 id=Integer,
2121 name=Unicode,
2122 timestamp=DateTime
2123 )
2125 for id, name, timestamp in connection.execute(stmt):
2126 print(id, name, timestamp)
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::
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 )
2148 query = session.query(User).from_statement(stmt).options(
2149 contains_eager(User.addresses))
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.
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::
2161 stmt = stmt.columns(id=Integer, name=String).cte('st')
2163 stmt = select(sometable).where(sometable.c.id == stmt.c.id)
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.
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.
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 ]
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 )
2195 @property
2196 def type(self):
2197 return type_api.NULLTYPE
2199 @property
2200 def comparator(self):
2201 return self.type.comparator_factory(self)
2203 def self_group(self, against=None):
2204 if against is operators.in_op:
2205 return Grouping(self)
2206 else:
2207 return self
2210class Null(SingletonConstant, roles.ConstExprRole, ColumnElement):
2211 """Represent the NULL keyword in a SQL statement.
2213 :class:`.Null` is accessed as a constant via the
2214 :func:`.null` function.
2216 """
2218 __visit_name__ = "null"
2220 _traverse_internals = []
2222 @util.memoized_property
2223 def type(self):
2224 return type_api.NULLTYPE
2226 @classmethod
2227 def _instance(cls):
2228 """Return a constant :class:`.Null` construct."""
2230 return Null()
2233Null._create_singleton()
2236class False_(SingletonConstant, roles.ConstExprRole, ColumnElement):
2237 """Represent the ``false`` keyword, or equivalent, in a SQL statement.
2239 :class:`.False_` is accessed as a constant via the
2240 :func:`.false` function.
2242 """
2244 __visit_name__ = "false"
2245 _traverse_internals = []
2247 @util.memoized_property
2248 def type(self):
2249 return type_api.BOOLEANTYPE
2251 def _negate(self):
2252 return True_()
2254 @classmethod
2255 def _instance(cls):
2256 """Return a :class:`.False_` construct.
2258 E.g.::
2260 >>> from sqlalchemy import false
2261 >>> print(select(t.c.x).where(false()))
2262 SELECT x FROM t WHERE false
2264 A backend which does not support true/false constants will render as
2265 an expression against 1 or 0::
2267 >>> print(select(t.c.x).where(false()))
2268 SELECT x FROM t WHERE 0 = 1
2270 The :func:`.true` and :func:`.false` constants also feature
2271 "short circuit" operation within an :func:`.and_` or :func:`.or_`
2272 conjunction::
2274 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
2275 SELECT x FROM t WHERE true
2277 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
2278 SELECT x FROM t WHERE false
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.
2284 .. seealso::
2286 :func:`.true`
2288 """
2290 return False_()
2293False_._create_singleton()
2296class True_(SingletonConstant, roles.ConstExprRole, ColumnElement):
2297 """Represent the ``true`` keyword, or equivalent, in a SQL statement.
2299 :class:`.True_` is accessed as a constant via the
2300 :func:`.true` function.
2302 """
2304 __visit_name__ = "true"
2306 _traverse_internals = []
2308 @util.memoized_property
2309 def type(self):
2310 return type_api.BOOLEANTYPE
2312 def _negate(self):
2313 return False_()
2315 @classmethod
2316 def _ifnone(cls, other):
2317 if other is None:
2318 return cls._instance()
2319 else:
2320 return other
2322 @classmethod
2323 def _instance(cls):
2324 """Return a constant :class:`.True_` construct.
2326 E.g.::
2328 >>> from sqlalchemy import true
2329 >>> print(select(t.c.x).where(true()))
2330 SELECT x FROM t WHERE true
2332 A backend which does not support true/false constants will render as
2333 an expression against 1 or 0::
2335 >>> print(select(t.c.x).where(true()))
2336 SELECT x FROM t WHERE 1 = 1
2338 The :func:`.true` and :func:`.false` constants also feature
2339 "short circuit" operation within an :func:`.and_` or :func:`.or_`
2340 conjunction::
2342 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
2343 SELECT x FROM t WHERE true
2345 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
2346 SELECT x FROM t WHERE false
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.
2352 .. seealso::
2354 :func:`.false`
2356 """
2358 return True_()
2361True_._create_singleton()
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.
2373 By default, is comma-separated, such as a column listing.
2375 """
2377 __visit_name__ = "clauselist"
2379 _is_clause_list = True
2381 _traverse_internals = [
2382 ("clauses", InternalTraversal.dp_clauseelement_list),
2383 ("operator", InternalTraversal.dp_operator),
2384 ]
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)
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
2421 def __iter__(self):
2422 return iter(self.clauses)
2424 def __len__(self):
2425 return len(self.clauses)
2427 @property
2428 def _select_iterable(self):
2429 return itertools.chain.from_iterable(
2430 [elem._select_iterable for elem in self.clauses]
2431 )
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 )
2445 @property
2446 def _from_objects(self):
2447 return list(itertools.chain(*[c._from_objects for c in self.clauses]))
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
2456class BooleanClauseList(ClauseList, ColumnElement):
2457 __visit_name__ = "clauselist"
2458 inherit_cache = True
2460 def __init__(self, *arg, **kw):
2461 raise NotImplementedError(
2462 "BooleanClauseList has a private constructor"
2463 )
2465 @classmethod
2466 def _process_clauses_for_boolean(
2467 cls, operator, continue_on, skip_on, clauses
2468 ):
2469 has_continue_on = None
2471 convert_clauses = []
2473 against = operators._asbool
2474 lcc = 0
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)
2498 if not convert_clauses and has_continue_on is not None:
2499 convert_clauses = [has_continue_on]
2500 lcc = 1
2502 return lcc, [c.self_group(against=against) for c in convert_clauses]
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 )
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)
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 )
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 )
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
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
2579 @classmethod
2580 def and_(cls, *clauses):
2581 r"""Produce a conjunction of expressions joined by ``AND``.
2583 E.g.::
2585 from sqlalchemy import and_
2587 stmt = select(users_table).where(
2588 and_(
2589 users_table.c.name == 'wendy',
2590 users_table.c.enrolled == True
2591 )
2592 )
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)::
2599 stmt = select(users_table).where(
2600 (users_table.c.name == 'wendy') &
2601 (users_table.c.enrolled == True)
2602 )
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_`::
2610 stmt = select(users_table).\
2611 where(users_table.c.name == 'wendy').\
2612 where(users_table.c.enrolled == True)
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::
2620 criteria = and_(True, *expressions)
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.
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.
2633 .. seealso::
2635 :func:`.or_`
2637 """
2638 return cls._construct(
2639 operators.and_, True_._singleton, False_._singleton, *clauses
2640 )
2642 @classmethod
2643 def or_(cls, *clauses):
2644 """Produce a conjunction of expressions joined by ``OR``.
2646 E.g.::
2648 from sqlalchemy import or_
2650 stmt = select(users_table).where(
2651 or_(
2652 users_table.c.name == 'wendy',
2653 users_table.c.name == 'jack'
2654 )
2655 )
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)::
2662 stmt = select(users_table).where(
2663 (users_table.c.name == 'wendy') |
2664 (users_table.c.name == 'jack')
2665 )
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::
2673 or_criteria = or_(False, *expressions)
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.
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.
2686 .. seealso::
2688 :func:`.and_`
2690 """
2691 return cls._construct(
2692 operators.or_, False_._singleton, True_._singleton, *clauses
2693 )
2695 @property
2696 def _select_iterable(self):
2697 return (self,)
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)
2705 def _negate(self):
2706 return ClauseList._negate(self)
2709and_ = BooleanClauseList.and_
2710or_ = BooleanClauseList.or_
2713class Tuple(ClauseList, ColumnElement):
2714 """Represent a SQL tuple."""
2716 __visit_name__ = "tuple"
2718 _traverse_internals = ClauseList._traverse_internals + []
2720 @util.preload_module("sqlalchemy.sql.sqltypes")
2721 def __init__(self, *clauses, **kw):
2722 """Return a :class:`.Tuple`.
2724 Main usage is to produce a composite IN construct using
2725 :meth:`.ColumnOperators.in_` ::
2727 from sqlalchemy import tuple_
2729 tuple_(table.c.col1, table.c.col2).in_(
2730 [(1, 2), (5, 12), (10, 19)]
2731 )
2733 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
2735 .. warning::
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.
2743 """
2744 sqltypes = util.preloaded.sql_sqltypes
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 ]
2767 self.type = sqltypes.TupleType(*[arg.type for arg in clauses])
2768 super(Tuple, self).__init__(*clauses, **kw)
2770 @property
2771 def _select_iterable(self):
2772 return (self,)
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 )
2799 def self_group(self, against=None):
2800 # Tuple is parenthesized by definition.
2801 return self
2804class Case(ColumnElement):
2805 """Represent a ``CASE`` expression.
2807 :class:`.Case` is produced using the :func:`.case` factory function,
2808 as in::
2810 from sqlalchemy import case
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 )
2821 Details on :class:`.Case` usage is at :func:`.case`.
2823 .. seealso::
2825 :func:`.case`
2827 """
2829 __visit_name__ = "case"
2831 _traverse_internals = [
2832 ("value", InternalTraversal.dp_clauseelement),
2833 ("whens", InternalTraversal.dp_clauseelement_tuples),
2834 ("else_", InternalTraversal.dp_clauseelement),
2835 ]
2837 # TODO: for Py2k removal, this will be:
2838 # def __init__(self, *whens, value=None, else_=None):
2840 def __init__(self, *whens, **kw):
2841 r"""Produce a ``CASE`` expression.
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`.
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::
2850 from sqlalchemy import case
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 )
2861 The above statement will produce SQL resembling::
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
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::
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 )
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::
2898 from sqlalchemy import case, literal_column
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 )
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::
2916 CASE
2917 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
2918 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
2919 ELSE 'lessthan10'
2920 END
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.
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.
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.::
2935 case(
2936 (users_table.c.name == 'wendy', 'W'),
2937 (users_table.c.name == 'jack', 'J')
2938 )
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.::
2945 case(
2946 {"wendy": "W", "jack": "J"},
2947 value=users_table.c.name
2948 )
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`.
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.
2961 """
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"),)
2970 whens = coercions._expression_collection_was_a_list(
2971 "whens", "case", whens
2972 )
2974 try:
2975 whens = util.dictlike_iteritems(whens)
2976 except TypeError:
2977 pass
2979 value = kw.pop("value", None)
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 ]
2993 if whenlist:
2994 type_ = list(whenlist[-1])[-1].type
2995 else:
2996 type_ = None
2998 if value is None:
2999 self.value = None
3000 else:
3001 self.value = coercions.expect(roles.ExpressionElementRole, value)
3003 self.type = type_
3004 self.whens = whenlist
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
3012 if kw:
3013 raise TypeError("unknown arguments: %s" % (", ".join(sorted(kw))))
3015 @property
3016 def _from_objects(self):
3017 return list(
3018 itertools.chain(*[x._from_objects for x in self.get_children()])
3019 )
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.
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.
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.
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`.
3046 .. seealso::
3048 :func:`_expression.column`
3050 :func:`_expression.text`
3052 :ref:`sqlexpression_literal_column`
3054 """
3055 return ColumnClause(text, type_=type_, is_literal=True)
3058class Cast(WrapsColumnExpression, ColumnElement):
3059 """Represent a ``CAST`` expression.
3061 :class:`.Cast` is produced using the :func:`.cast` factory function,
3062 as in::
3064 from sqlalchemy import cast, Numeric
3066 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
3068 Details on :class:`.Cast` usage is at :func:`.cast`.
3070 .. seealso::
3072 :ref:`tutorial_casts`
3074 :func:`.cast`
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.
3080 """
3082 __visit_name__ = "cast"
3084 _traverse_internals = [
3085 ("clause", InternalTraversal.dp_clauseelement),
3086 ("type", InternalTraversal.dp_type),
3087 ]
3089 def __init__(self, expression, type_):
3090 r"""Produce a ``CAST`` expression.
3092 :func:`.cast` returns an instance of :class:`.Cast`.
3094 E.g.::
3096 from sqlalchemy import cast, Numeric
3098 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
3100 The above statement will produce SQL resembling::
3102 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
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.
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.
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.
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.
3128 :param type\_: A :class:`.TypeEngine` class or instance indicating
3129 the type to which the ``CAST`` should apply.
3131 .. seealso::
3133 :ref:`tutorial_casts`
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.
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)
3150 @property
3151 def _from_objects(self):
3152 return self.clause._from_objects
3154 @property
3155 def wrapped_column_expression(self):
3156 return self.clause
3159class TypeCoerce(WrapsColumnExpression, ColumnElement):
3160 """Represent a Python-side type-coercion wrapper.
3162 :class:`.TypeCoerce` supplies the :func:`_expression.type_coerce`
3163 function; see that function for usage details.
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.
3169 .. seealso::
3171 :func:`_expression.type_coerce`
3173 :func:`.cast`
3175 """
3177 __visit_name__ = "type_coerce"
3179 _traverse_internals = [
3180 ("clause", InternalTraversal.dp_clauseelement),
3181 ("type", InternalTraversal.dp_type),
3182 ]
3184 def __init__(self, expression, type_):
3185 r"""Associate a SQL expression with a particular type, without rendering
3186 ``CAST``.
3188 E.g.::
3190 from sqlalchemy import type_coerce
3192 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
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::
3199 SELECT date_string AS date_string FROM log
3201 When result rows are fetched, the ``StringDateTime`` type processor
3202 will be applied to result rows on behalf of the ``date_string`` column.
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.
3209 In order to provide a named label for the expression, use
3210 :meth:`_expression.ColumnElement.label`::
3212 stmt = select(
3213 type_coerce(log_table.date_string, StringDateTime()).label('date')
3214 )
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::
3226 # bound-value handling of MyStringType will be applied to the
3227 # literal value "some string"
3228 stmt = select(type_coerce("some string", MyStringType))
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::
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
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.
3249 :param type\_: A :class:`.TypeEngine` class or instance indicating
3250 the type to which the expression is coerced.
3252 .. seealso::
3254 :ref:`tutorial_casts`
3256 :func:`.cast`
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 )
3267 @property
3268 def _from_objects(self):
3269 return self.clause._from_objects
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
3280 @property
3281 def wrapped_column_expression(self):
3282 return self.clause
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
3292class Extract(ColumnElement):
3293 """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``."""
3295 __visit_name__ = "extract"
3297 _traverse_internals = [
3298 ("expr", InternalTraversal.dp_clauseelement),
3299 ("field", InternalTraversal.dp_string),
3300 ]
3302 def __init__(self, field, expr, **kwargs):
3303 """Return a :class:`.Extract` construct.
3305 This is typically available as :func:`.extract`
3306 as well as ``func.extract`` from the
3307 :data:`.func` namespace.
3309 :param field: The field to extract.
3311 :param expr: A column or Python scalar expression serving as the
3312 right side of the ``EXTRACT`` expression.
3314 E.g.::
3316 from sqlalchemy import extract
3317 from sqlalchemy import table, column
3319 logged_table = table("user",
3320 column("id"),
3321 column("date_created"),
3322 )
3324 stmt = select(logged_table.c.id).where(
3325 extract("YEAR", logged_table.c.date_created) == 2021
3326 )
3328 In the above example, the statement is used to select ids from the
3329 database where the ``YEAR`` component matches a specific value.
3331 Similarly, one can also select an extracted component::
3333 stmt = select(
3334 extract("YEAR", logged_table.c.date_created)
3335 ).where(logged_table.c.id == 1)
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)
3344 @property
3345 def _from_objects(self):
3346 return self.expr._from_objects
3349class _label_reference(ColumnElement):
3350 """Wrap a column expression as it appears in a 'reference' context.
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.
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.
3360 """
3362 __visit_name__ = "label_reference"
3364 _traverse_internals = [("element", InternalTraversal.dp_clauseelement)]
3366 def __init__(self, element):
3367 self.element = element
3369 @property
3370 def _from_objects(self):
3371 return ()
3374class _textual_label_reference(ColumnElement):
3375 __visit_name__ = "textual_label_reference"
3377 _traverse_internals = [("element", InternalTraversal.dp_string)]
3379 def __init__(self, element):
3380 self.element = element
3382 @util.memoized_property
3383 def _text_clause(self):
3384 return TextClause._create_text(self.element)
3387class UnaryExpression(ColumnElement):
3388 """Define a 'unary' expression.
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.
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`.
3399 """
3401 __visit_name__ = "unary"
3403 _traverse_internals = [
3404 ("element", InternalTraversal.dp_clauseelement),
3405 ("operator", InternalTraversal.dp_operator),
3406 ("modifier", InternalTraversal.dp_operator),
3407 ]
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
3426 @classmethod
3427 def _create_nulls_first(cls, column):
3428 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
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::
3435 from sqlalchemy import desc, nulls_first
3437 stmt = select(users_table).order_by(
3438 nulls_first(desc(users_table.c.name)))
3440 The SQL expression from the above would resemble::
3442 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
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::
3450 stmt = select(users_table).order_by(
3451 users_table.c.name.desc().nulls_first())
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.
3457 .. seealso::
3459 :func:`.asc`
3461 :func:`.desc`
3463 :func:`.nulls_last`
3465 :meth:`_expression.Select.order_by`
3467 """
3468 return UnaryExpression(
3469 coercions.expect(roles.ByOfRole, column),
3470 modifier=operators.nulls_first_op,
3471 wraps_column_expression=False,
3472 )
3474 @classmethod
3475 def _create_nulls_last(cls, column):
3476 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
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::
3483 from sqlalchemy import desc, nulls_last
3485 stmt = select(users_table).order_by(
3486 nulls_last(desc(users_table.c.name)))
3488 The SQL expression from the above would resemble::
3490 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
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::
3498 stmt = select(users_table).order_by(
3499 users_table.c.name.desc().nulls_last())
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.
3505 .. seealso::
3507 :func:`.asc`
3509 :func:`.desc`
3511 :func:`.nulls_first`
3513 :meth:`_expression.Select.order_by`
3515 """
3516 return UnaryExpression(
3517 coercions.expect(roles.ByOfRole, column),
3518 modifier=operators.nulls_last_op,
3519 wraps_column_expression=False,
3520 )
3522 @classmethod
3523 def _create_desc(cls, column):
3524 """Produce a descending ``ORDER BY`` clause element.
3526 e.g.::
3528 from sqlalchemy import desc
3530 stmt = select(users_table).order_by(desc(users_table.c.name))
3532 will produce SQL as::
3534 SELECT id, name FROM user ORDER BY name DESC
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.::
3542 stmt = select(users_table).order_by(users_table.c.name.desc())
3544 :param column: A :class:`_expression.ColumnElement` (e.g.
3545 scalar SQL expression)
3546 with which to apply the :func:`.desc` operation.
3548 .. seealso::
3550 :func:`.asc`
3552 :func:`.nulls_first`
3554 :func:`.nulls_last`
3556 :meth:`_expression.Select.order_by`
3558 """
3559 return UnaryExpression(
3560 coercions.expect(roles.ByOfRole, column),
3561 modifier=operators.desc_op,
3562 wraps_column_expression=False,
3563 )
3565 @classmethod
3566 def _create_asc(cls, column):
3567 """Produce an ascending ``ORDER BY`` clause element.
3569 e.g.::
3571 from sqlalchemy import asc
3572 stmt = select(users_table).order_by(asc(users_table.c.name))
3574 will produce SQL as::
3576 SELECT id, name FROM user ORDER BY name ASC
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.::
3584 stmt = select(users_table).order_by(users_table.c.name.asc())
3586 :param column: A :class:`_expression.ColumnElement` (e.g.
3587 scalar SQL expression)
3588 with which to apply the :func:`.asc` operation.
3590 .. seealso::
3592 :func:`.desc`
3594 :func:`.nulls_first`
3596 :func:`.nulls_last`
3598 :meth:`_expression.Select.order_by`
3600 """
3601 return UnaryExpression(
3602 coercions.expect(roles.ByOfRole, column),
3603 modifier=operators.asc_op,
3604 wraps_column_expression=False,
3605 )
3607 @classmethod
3608 def _create_distinct(cls, expr):
3609 """Produce an column-expression-level unary ``DISTINCT`` clause.
3611 This applies the ``DISTINCT`` keyword to an individual column
3612 expression, and is typically contained within an aggregate function,
3613 as in::
3615 from sqlalchemy import distinct, func
3616 stmt = select(func.count(distinct(users_table.c.name)))
3618 The above would produce an expression resembling::
3620 SELECT COUNT(DISTINCT name) FROM user
3622 The :func:`.distinct` function is also available as a column-level
3623 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
3625 stmt = select(func.count(users_table.c.name.distinct()))
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.
3635 .. seealso::
3637 :meth:`_expression.ColumnElement.distinct`
3639 :meth:`_expression.Select.distinct`
3641 :data:`.func`
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 )
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
3659 @property
3660 def _from_objects(self):
3661 return self.element._from_objects
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)
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
3681class CollectionAggregate(UnaryExpression):
3682 """Forms the basis for right-hand collection operator modifiers
3683 ANY and ALL.
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.
3689 """
3691 inherit_cache = True
3693 @classmethod
3694 def _create_any(cls, expr):
3695 """Produce an ANY expression.
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.::
3701 # renders on PostgreSQL:
3702 # '5 = ANY (somearray)'
3703 expr = 5 == any_(mytable.c.somearray)
3705 # renders on MySQL:
3706 # '5 = ANY (SELECT value FROM table)'
3707 expr = 5 == any_(select(table.c.value))
3709 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
3711 None == any_(mytable.c.somearray)
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::
3719 # would render '5 = ANY (column)`
3720 any_(mytable.c.column) == 5
3722 Or with ``None``, which note will not perform
3723 the usual step of rendering "IS" as is normally the case for NULL::
3725 # would render 'NULL = ANY(somearray)'
3726 any_(mytable.c.somearray) == None
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.
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)``::
3736 5 = mytable.c.somearray.any_()
3738 .. seealso::
3740 :meth:`_sql.ColumnOperators.any_`
3742 :func:`_expression.all_`
3744 """
3746 expr = coercions.expect(roles.ExpressionElementRole, expr)
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 )
3756 @classmethod
3757 def _create_all(cls, expr):
3758 """Produce an ALL expression.
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.::
3764 # renders on PostgreSQL:
3765 # '5 = ALL (somearray)'
3766 expr = 5 == all_(mytable.c.somearray)
3768 # renders on MySQL:
3769 # '5 = ALL (SELECT value FROM table)'
3770 expr = 5 == all_(select(table.c.value))
3772 Comparison to NULL may work using ``None``::
3774 None == all_(mytable.c.somearray)
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::
3782 # would render '5 = ALL (column)`
3783 all_(mytable.c.column) == 5
3785 Or with ``None``, which note will not perform
3786 the usual step of rendering "IS" as is normally the case for NULL::
3788 # would render 'NULL = ALL(somearray)'
3789 all_(mytable.c.somearray) == None
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.
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)``::
3799 5 == mytable.c.somearray.all_()
3801 .. seealso::
3803 :meth:`_sql.ColumnOperators.all_`
3805 :func:`_expression.any_`
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 )
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)
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 )
3836class AsBoolean(WrapsColumnExpression, UnaryExpression):
3837 inherit_cache = True
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
3848 @property
3849 def wrapped_column_expression(self):
3850 return self.element
3852 def self_group(self, against=None):
3853 return self
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)
3862class BinaryExpression(ColumnElement):
3863 """Represent an expression that is ``LEFT <operator> RIGHT``.
3865 A :class:`.BinaryExpression` is generated automatically
3866 whenever two column expressions are used in a Python binary expression::
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
3874 """
3876 __visit_name__ = "binary"
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 ]
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 ]
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.
3907 """
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)
3925 if modifiers is None:
3926 self.modifiers = {}
3927 else:
3928 self.modifiers = modifiers
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")
3936 __nonzero__ = __bool__
3938 @property
3939 def is_comparison(self):
3940 return operators.is_comparison(self.operator)
3942 @property
3943 def _from_objects(self):
3944 return self.left._from_objects + self.right._from_objects
3946 def self_group(self, against=None):
3948 if operators.is_precedent(self.operator, against):
3949 return Grouping(self)
3950 else:
3951 return self
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()
3967class Slice(ColumnElement):
3968 """Represent SQL for a Python array-slice object.
3970 This is not a specific SQL construct at this level, but
3971 may be interpreted by specific dialects, e.g. PostgreSQL.
3973 """
3975 __visit_name__ = "slice"
3977 _traverse_internals = [
3978 ("start", InternalTraversal.dp_clauseelement),
3979 ("stop", InternalTraversal.dp_clauseelement),
3980 ("step", InternalTraversal.dp_clauseelement),
3981 ]
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
4004 def self_group(self, against=None):
4005 assert against is operator.getitem
4006 return self
4009class IndexExpression(BinaryExpression):
4010 """Represent the class of expressions that are like an "index"
4011 operation."""
4013 inherit_cache = True
4016class GroupedElement(ClauseElement):
4017 """Represent any parenthesized expression"""
4019 __visit_name__ = "grouping"
4021 def self_group(self, against=None):
4022 return self
4024 def _ungroup(self):
4025 return self.element._ungroup()
4028class Grouping(GroupedElement, ColumnElement):
4029 """Represent a grouping within a column expression"""
4031 _traverse_internals = [
4032 ("element", InternalTraversal.dp_clauseelement),
4033 ("type", InternalTraversal.dp_type),
4034 ]
4036 _cache_key_traversal = [
4037 ("element", InternalTraversal.dp_clauseelement),
4038 ]
4040 def __init__(self, element):
4041 self.element = element
4042 self.type = getattr(element, "type", type_api.NULLTYPE)
4044 def _with_binary_element_type(self, type_):
4045 return self.__class__(self.element._with_binary_element_type(type_))
4047 @util.memoized_property
4048 def _is_implicitly_boolean(self):
4049 return self.element._is_implicitly_boolean
4051 @property
4052 def _tq_label(self):
4053 return (
4054 getattr(self.element, "_tq_label", None) or self._anon_name_label
4055 )
4057 @property
4058 def _proxies(self):
4059 if isinstance(self.element, ColumnElement):
4060 return [self.element]
4061 else:
4062 return []
4064 @property
4065 def _from_objects(self):
4066 return self.element._from_objects
4068 def __getattr__(self, attr):
4069 return getattr(self.element, attr)
4071 def __getstate__(self):
4072 return {"element": self.element, "type": self.type}
4074 def __setstate__(self, state):
4075 self.element = state["element"]
4076 self.type = state["type"]
4079RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED")
4080RANGE_CURRENT = util.symbol("RANGE_CURRENT")
4083class Over(ColumnElement):
4084 """Represent an OVER clause.
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.
4091 """
4093 __visit_name__ = "over"
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 ]
4103 order_by = None
4104 partition_by = None
4106 element = None
4107 """The underlying expression object to which this :class:`.Over`
4108 object refers towards."""
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.
4115 Used against aggregate or so-called "window" functions,
4116 for database backends that support window functions.
4118 :func:`_expression.over` is usually called using
4119 the :meth:`.FunctionElement.over` method, e.g.::
4121 func.row_number().over(order_by=mytable.c.some_column)
4123 Would produce::
4125 ROW_NUMBER() OVER(ORDER BY some_column)
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::
4132 func.row_number().over(
4133 order_by=my_table.c.some_column, range_=(None, 0))
4135 The above would produce::
4137 ROW_NUMBER() OVER(ORDER BY some_column
4138 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
4140 A value of ``None`` indicates "unbounded", a
4141 value of zero indicates "current row", and negative / positive
4142 integers indicate "preceding" and "following":
4144 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
4146 func.row_number().over(order_by='x', range_=(-5, 10))
4148 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
4150 func.row_number().over(order_by='x', rows=(None, 0))
4152 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
4154 func.row_number().over(order_by='x', range_=(-2, None))
4156 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
4158 func.row_number().over(order_by='x', range_=(1, 3))
4160 .. versionadded:: 1.1 support for RANGE / ROWS within a window
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.
4175 .. versionadded:: 1.1
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.
4181 .. versionadded:: 1.1
4183 This function is also available from the :data:`~.expression.func`
4184 construct itself via the :meth:`.FunctionElement.over` method.
4186 .. seealso::
4188 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
4190 :data:`.expression.func`
4192 :func:`_expression.within_group`
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 )
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
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 )
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")
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
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
4265 return lower, upper
4267 @util.memoized_property
4268 def type(self):
4269 return self.element.type
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 )
4284class WithinGroup(ColumnElement):
4285 """Represent a WITHIN GROUP (ORDER BY) clause.
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.
4292 It's supported only by certain database backends, such as PostgreSQL,
4293 Oracle and MS SQL Server.
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.
4299 """
4301 __visit_name__ = "withingroup"
4303 _traverse_internals = [
4304 ("element", InternalTraversal.dp_clauseelement),
4305 ("order_by", InternalTraversal.dp_clauseelement),
4306 ]
4308 order_by = None
4310 def __init__(self, element, *order_by):
4311 r"""Produce a :class:`.WithinGroup` object against a function.
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.
4317 :func:`_expression.within_group` is usually called using
4318 the :meth:`.FunctionElement.within_group` method, e.g.::
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 )
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)``.
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.
4337 .. versionadded:: 1.1
4339 .. seealso::
4341 :ref:`tutorial_functions_within_group` - in the
4342 :ref:`unified_tutorial`
4344 :data:`.expression.func`
4346 :func:`_expression.over`
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 )
4355 def __reduce__(self):
4356 return self.__class__, (self.element,) + tuple(self.order_by)
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.
4362 This function has the same signature as that of
4363 :meth:`.FunctionElement.over`.
4365 """
4366 return Over(
4367 self,
4368 partition_by=partition_by,
4369 order_by=order_by,
4370 range_=range_,
4371 rows=rows,
4372 )
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
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 )
4395class FunctionFilter(ColumnElement):
4396 """Represent a function FILTER clause.
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.
4402 Invocation of :class:`.FunctionFilter` is via
4403 :meth:`.FunctionElement.filter`::
4405 func.count(1).filter(True)
4407 .. versionadded:: 1.0.0
4409 .. seealso::
4411 :meth:`.FunctionElement.filter`
4413 """
4415 __visit_name__ = "funcfilter"
4417 _traverse_internals = [
4418 ("func", InternalTraversal.dp_clauseelement),
4419 ("criterion", InternalTraversal.dp_clauseelement),
4420 ]
4422 criterion = None
4424 def __init__(self, func, *criterion):
4425 """Produce a :class:`.FunctionFilter` object against a function.
4427 Used against aggregate and window functions,
4428 for database backends that support the "FILTER" clause.
4430 E.g.::
4432 from sqlalchemy import funcfilter
4433 funcfilter(func.count(1), MyClass.name == 'some name')
4435 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
4437 This function is also available from the :data:`~.expression.func`
4438 construct itself via the :meth:`.FunctionElement.filter` method.
4440 .. versionadded:: 1.0.0
4442 .. seealso::
4444 :ref:`tutorial_functions_within_group` - in the
4445 :ref:`unified_tutorial`
4447 :meth:`.FunctionElement.filter`
4449 """
4450 self.func = func
4451 self.filter(*criterion)
4453 def filter(self, *criterion):
4454 """Produce an additional FILTER against the function.
4456 This method adds additional criteria to the initial criteria
4457 set up by :meth:`.FunctionElement.filter`.
4459 Multiple criteria are joined together at SQL render time
4460 via ``AND``.
4463 """
4465 for criterion in list(criterion):
4466 criterion = coercions.expect(roles.WhereHavingRole, criterion)
4468 if self.criterion is not None:
4469 self.criterion = self.criterion & criterion
4470 else:
4471 self.criterion = criterion
4473 return self
4475 def over(self, partition_by=None, order_by=None, range_=None, rows=None):
4476 """Produce an OVER clause against this filtered function.
4478 Used against aggregate or so-called "window" functions,
4479 for database backends that support window functions.
4481 The expression::
4483 func.rank().filter(MyClass.y > 5).over(order_by='x')
4485 is shorthand for::
4487 from sqlalchemy import over, funcfilter
4488 over(funcfilter(func.rank(), MyClass.y > 5), order_by='x')
4490 See :func:`_expression.over` for a full description.
4492 """
4493 return Over(
4494 self,
4495 partition_by=partition_by,
4496 order_by=order_by,
4497 range_=range_,
4498 rows=rows,
4499 )
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
4507 @util.memoized_property
4508 def type(self):
4509 return self.func.type
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 )
4524class Label(roles.LabeledColumnExprRole, ColumnElement):
4525 """Represents a column label (AS).
4527 Represent a label, as typically applied to any column-level
4528 element using the ``AS`` sql keyword.
4530 """
4532 __visit_name__ = "label"
4534 _traverse_internals = [
4535 ("name", InternalTraversal.dp_anon_name),
4536 ("_type", InternalTraversal.dp_type),
4537 ("_element", InternalTraversal.dp_clauseelement),
4538 ]
4540 _cache_key_traversal = [
4541 ("name", InternalTraversal.dp_anon_name),
4542 ("_element", InternalTraversal.dp_clauseelement),
4543 ]
4545 def __init__(self, name, element, type_=None):
4546 """Return a :class:`Label` object for the
4547 given :class:`_expression.ColumnElement`.
4549 A label changes the name of an element in the columns clause of a
4550 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
4552 This functionality is more conveniently available via the
4553 :meth:`_expression.ColumnElement.label` method on
4554 :class:`_expression.ColumnElement`.
4556 :param name: label name
4558 :param obj: a :class:`_expression.ColumnElement`.
4560 """
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
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
4584 self.key = self._tq_label = self._tq_key_label = self.name
4585 self._element = element
4586 self._type = type_
4587 self._proxies = [element]
4589 def __reduce__(self):
4590 return self.__class__, (self.name, self._element, self._type)
4592 @util.memoized_property
4593 def _is_implicitly_boolean(self):
4594 return self.element._is_implicitly_boolean
4596 @HasMemoized.memoized_attribute
4597 def _allow_label_resolve(self):
4598 return self.element._allow_label_resolve
4600 @property
4601 def _order_by_label_element(self):
4602 return self
4604 @util.memoized_property
4605 def type(self):
4606 return type_api.to_instance(
4607 self._type or getattr(self._element, "type", None)
4608 )
4610 @HasMemoized.memoized_attribute
4611 def element(self):
4612 return self._element.self_group(against=operators.as_)
4614 def self_group(self, against=None):
4615 return self._apply_to_inner(self._element.self_group, against=against)
4617 def _negate(self):
4618 return self._apply_to_inner(self._element._negate)
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
4627 @property
4628 def primary_key(self):
4629 return self.element.primary_key
4631 @property
4632 def foreign_keys(self):
4633 return self.element.foreign_keys
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
4644 @property
4645 def _from_objects(self):
4646 return self.element._from_objects
4648 def _make_proxy(self, selectable, name=None, **kw):
4649 name = self.name if not name else name
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 )
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 )
4673 e._propagate_attrs = selectable._propagate_attrs
4674 e._proxies.append(self)
4675 if self._type is not None:
4676 e.type = self._type
4678 return self.key, e
4681class NamedColumn(ColumnElement):
4682 is_literal = False
4683 table = None
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 )
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")
4697 @HasMemoized.memoized_attribute
4698 def _tq_key_label(self):
4699 """table qualified label based on column key.
4701 for table-bound columns this is <tablename>_<column key/proxy key>;
4703 all other expressions it resolves to key/proxy key.
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
4712 @HasMemoized.memoized_attribute
4713 def _tq_label(self):
4714 """table qualified label based on column name.
4716 for table-bound columns this is <tablename>_<columnname>; all other
4717 expressions it resolves to .name.
4719 """
4720 return self._gen_tq_label(self.name)
4722 @HasMemoized.memoized_attribute
4723 def _render_label_in_columns_clause(self):
4724 return True
4726 @HasMemoized.memoized_attribute
4727 def _non_anon_label(self):
4728 return self.name
4730 def _gen_tq_label(self, name, dedupe_on_key=True):
4731 return name
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 )
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
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.
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::
4782 from sqlalchemy import column
4784 id, name = column("id"), column("name")
4785 stmt = select(id, name).select_from("user")
4787 The above statement would produce SQL like::
4789 SELECT id, name FROM user
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`.
4802 Full details on :class:`.ColumnClause` usage is at
4803 :func:`_expression.column`.
4805 .. seealso::
4807 :func:`_expression.column`
4809 :class:`_schema.Column`
4811 """
4813 table = None
4814 is_literal = False
4816 __visit_name__ = "column"
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 ]
4825 onupdate = default = server_default = server_onupdate = None
4827 _is_multiparam_column = False
4829 @property
4830 def _is_star(self):
4831 return self.is_literal and self.name == "*"
4833 def __init__(self, text, type_=None, is_literal=False, _selectable=None):
4834 """Produce a :class:`.ColumnClause` object.
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::
4841 from sqlalchemy import column
4843 id, name = column("id"), column("name")
4844 stmt = select(id, name).select_from("user")
4846 The above statement would produce SQL like::
4848 SELECT id, name FROM user
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::
4855 from sqlalchemy.sql import column
4857 id, name = column("id"), column("name")
4858 stmt = select(id, name).select_from("user")
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.
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::
4880 from sqlalchemy import table, column, select
4882 user = table("user",
4883 column("id"),
4884 column("name"),
4885 column("description"),
4886 )
4888 stmt = select(user.c.description).where(user.c.name == 'wendy')
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.
4897 .. versionchanged:: 1.0.0 :func:`_expression.column` can now
4898 be imported from the plain ``sqlalchemy`` namespace like any
4899 other SQL element.
4901 :param text: the text of the element.
4903 :param type: :class:`_types.TypeEngine` object which can associate
4904 this :class:`.ColumnClause` with a type.
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``.
4912 .. seealso::
4914 :class:`_schema.Column`
4916 :func:`_expression.literal_column`
4918 :func:`.table`
4920 :func:`_expression.text`
4922 :ref:`tutorial_select_arbitrary_text`
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
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 []
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
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
4954 return super(ColumnClause, self)._clone(**kw)
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 []
4964 @HasMemoized.memoized_attribute
4965 def _render_label_in_columns_clause(self):
4966 return self.table is not None
4968 @property
4969 def _ddl_label(self):
4970 return self._gen_tq_label(self.name, dedupe_on_key=False)
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)
4994 def _gen_tq_label(self, name, dedupe_on_key=True):
4995 """generate table-qualified label
4997 for a table-bound column this is <tablename>_<columnname>.
4999 used primarily for LABEL_STYLE_TABLENAME_PLUS_COL
5000 as well as the .columns collection on a Join object.
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
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)
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
5041 return coercions.expect(roles.TruncatedLabelRole, label)
5043 else:
5044 return name
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
5087class TableValuedColumn(NamedColumn):
5088 __visit_name__ = "table_valued_column"
5090 _traverse_internals = [
5091 ("name", InternalTraversal.dp_anon_name),
5092 ("type", InternalTraversal.dp_type),
5093 ("scalar_alias", InternalTraversal.dp_clauseelement),
5094 ]
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_
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
5105 @property
5106 def _from_objects(self):
5107 return [self.scalar_alias]
5110class CollationClause(ColumnElement):
5111 __visit_name__ = "collation"
5113 _traverse_internals = [("collation", InternalTraversal.dp_string)]
5115 def __init__(self, collation):
5116 self.collation = collation
5119class _IdentifiedClause(Executable, ClauseElement):
5121 __visit_name__ = "identified"
5122 _execution_options = Executable._execution_options.union(
5123 {"autocommit": False}
5124 )
5126 def __init__(self, ident):
5127 self.ident = ident
5130class SavepointClause(_IdentifiedClause):
5131 __visit_name__ = "savepoint"
5132 inherit_cache = False
5135class RollbackToSavepointClause(_IdentifiedClause):
5136 __visit_name__ = "rollback_to_savepoint"
5137 inherit_cache = False
5140class ReleaseSavepointClause(_IdentifiedClause):
5141 __visit_name__ = "release_savepoint"
5142 inherit_cache = False
5145class quoted_name(util.MemoizedSlots, util.text_type):
5146 """Represent a SQL identifier combined with quoting preferences.
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.
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.
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::
5177 from sqlalchemy import create_engine
5178 from sqlalchemy import inspect
5179 from sqlalchemy.sql import quoted_name
5181 engine = create_engine("oracle+cx_oracle://some_dsn")
5182 print(inspect(engine).has_table(quoted_name("some_table", True)))
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.
5188 .. versionadded:: 0.9.0
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``.
5194 """
5196 __slots__ = "quote", "lower", "upper"
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)
5212 self.quote = quote
5213 return self
5215 def __reduce__(self):
5216 return quoted_name, (util.text_type(self), self.quote)
5218 def _memoized_method_lower(self):
5219 if self.quote:
5220 return self
5221 else:
5222 return util.text_type(self).lower()
5224 def _memoized_method_upper(self):
5225 if self.quote:
5226 return self
5227 else:
5228 return util.text_type(self).upper()
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)
5240def _find_columns(clause):
5241 """locate Column objects within the given expression."""
5243 cols = util.column_set()
5244 traverse(clause, {}, {"column": cols.add})
5245 return cols
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
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
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)
5284 def _with_annotations(self, values):
5285 clone = super(AnnotatedColumnElement, self)._with_annotations(values)
5286 clone.__dict__.pop("comparator", None)
5287 return clone
5289 @util.memoized_property
5290 def name(self):
5291 """pull 'name' from parent, if not present"""
5292 return self._Annotated__element.name
5294 @util.memoized_property
5295 def table(self):
5296 """pull 'table' from parent, if not present"""
5297 return self._Annotated__element.table
5299 @util.memoized_property
5300 def key(self):
5301 """pull 'key' from parent, if not present"""
5302 return self._Annotated__element.key
5304 @util.memoized_property
5305 def info(self):
5306 return self._Annotated__element.info
5308 @util.memoized_property
5309 def _anon_name_label(self):
5310 return self._Annotated__element._anon_name_label
5313class _truncated_label(quoted_name):
5314 """A unicode subclass used to identify symbolic "
5315 "names that may require truncation."""
5317 __slots__ = ()
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)
5324 def __reduce__(self):
5325 return self.__class__, (util.text_type(self), self.quote)
5327 def apply_map(self, map_):
5328 return self
5331class conv(_truncated_label):
5332 """Mark a string indicating that a name has already been converted
5333 by a naming convention.
5335 This is a string subclass that indicates a name that should not be
5336 subject to any further naming conventions.
5338 E.g. when we create a :class:`.Constraint` using a naming convention
5339 as follows::
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'))
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.
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::
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')))
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"``
5368 .. versionadded:: 0.9.4
5370 .. seealso::
5372 :ref:`constraint_naming_conventions`
5374 """
5376 __slots__ = ()
5379_NONE_NAME = util.symbol("NONE_NAME")
5380"""indicate a 'deferred' name that was ultimately the value None."""
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
5389class _anonymous_label(_truncated_label):
5390 """A unicode subclass used to identify anonymously
5391 generated names."""
5393 __slots__ = ()
5395 @classmethod
5396 def safe_construct(
5397 cls, seed, body, enclosing_label=None, sanitize_key=False
5398 ):
5400 # need to escape chars that interfere with format
5401 # strings in any case, issue #8724
5402 body = re.sub(r"[%\(\) \$]+", "_", body)
5404 if sanitize_key:
5405 # sanitize_key is then an extra step used by BindParameter
5406 body = body.strip("_")
5408 label = "%%(%d %s)s" % (seed, body.replace("%", "%%"))
5409 if enclosing_label:
5410 label = "%s%s" % (enclosing_label, label)
5412 return _anonymous_label(label)
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)
5420 return _anonymous_label(
5421 quoted_name(
5422 util.text_type.__add__(self, other),
5423 self.quote,
5424 )
5425 )
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)
5433 return _anonymous_label(
5434 quoted_name(
5435 util.text_type.__add__(other, self),
5436 self.quote,
5437 )
5438 )
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_