Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/elements.py: 49%
1417 statements
« prev ^ index » next coverage.py v7.0.1, created at 2022-12-25 06:11 +0000
1# sql/elements.py
2# Copyright (C) 2005-2022 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 __getattr__(self, key):
854 try:
855 return getattr(self.comparator, key)
856 except AttributeError as err:
857 util.raise_(
858 AttributeError(
859 "Neither %r object nor %r object has an attribute %r"
860 % (
861 type(self).__name__,
862 type(self.comparator).__name__,
863 key,
864 )
865 ),
866 replace_context=err,
867 )
869 def operate(self, op, *other, **kwargs):
870 return op(self.comparator, *other, **kwargs)
872 def reverse_operate(self, op, other, **kwargs):
873 return op(other, self.comparator, **kwargs)
875 def _bind_param(self, operator, obj, type_=None, expanding=False):
876 return BindParameter(
877 None,
878 obj,
879 _compared_to_operator=operator,
880 type_=type_,
881 _compared_to_type=self.type,
882 unique=True,
883 expanding=expanding,
884 )
886 @property
887 def expression(self):
888 """Return a column expression.
890 Part of the inspection interface; returns self.
892 """
893 return self
895 @property
896 def _select_iterable(self):
897 return (self,)
899 @util.memoized_property
900 def base_columns(self):
901 return util.column_set(c for c in self.proxy_set if not c._proxies)
903 @util.memoized_property
904 def proxy_set(self):
905 s = util.column_set([self._deannotate()])
906 for c in self._proxies:
907 s.update(c.proxy_set)
908 return s
910 def _uncached_proxy_list(self):
911 """An 'uncached' version of proxy set.
913 This is so that we can read annotations from the list of columns
914 without breaking the caching of the above proxy_set.
916 """
917 s = [self]
918 for c in self._proxies:
919 s.extend(c._uncached_proxy_list())
920 return s
922 def shares_lineage(self, othercolumn):
923 """Return True if the given :class:`_expression.ColumnElement`
924 has a common ancestor to this :class:`_expression.ColumnElement`."""
926 return bool(self.proxy_set.intersection(othercolumn.proxy_set))
928 def _compare_name_for_result(self, other):
929 """Return True if the given column element compares to this one
930 when targeting within a result row."""
932 return (
933 hasattr(other, "name")
934 and hasattr(self, "name")
935 and other.name == self.name
936 )
938 @HasMemoized.memoized_attribute
939 def _proxy_key(self):
940 if self._annotations and "proxy_key" in self._annotations:
941 return self._annotations["proxy_key"]
943 name = self.key
944 if not name:
945 # there's a bit of a seeming contradiction which is that the
946 # "_non_anon_label" of a column can in fact be an
947 # "_anonymous_label"; this is when it's on a column that is
948 # proxying for an anonymous expression in a subquery.
949 name = self._non_anon_label
951 if isinstance(name, _anonymous_label):
952 return None
953 else:
954 return name
956 @HasMemoized.memoized_attribute
957 def _expression_label(self):
958 """a suggested label to use in the case that the column has no name,
959 which should be used if possible as the explicit 'AS <label>'
960 where this expression would normally have an anon label.
962 this is essentially mostly what _proxy_key does except it returns
963 None if the column has a normal name that can be used.
965 """
967 if getattr(self, "name", None) is not None:
968 return None
969 elif self._annotations and "proxy_key" in self._annotations:
970 return self._annotations["proxy_key"]
971 else:
972 return None
974 def _make_proxy(
975 self, selectable, name=None, key=None, name_is_truncatable=False, **kw
976 ):
977 """Create a new :class:`_expression.ColumnElement` representing this
978 :class:`_expression.ColumnElement` as it appears in the select list of
979 a descending selectable.
981 """
982 if name is None:
983 name = self._anon_name_label
984 if key is None:
985 key = self._proxy_key
986 else:
987 key = name
989 co = ColumnClause(
990 coercions.expect(roles.TruncatedLabelRole, name)
991 if name_is_truncatable
992 else name,
993 type_=getattr(self, "type", None),
994 _selectable=selectable,
995 )
997 co._propagate_attrs = selectable._propagate_attrs
998 co._proxies = [self]
999 if selectable._is_clone_of is not None:
1000 co._is_clone_of = selectable._is_clone_of.columns.get(key)
1001 return key, co
1003 def cast(self, type_):
1004 """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.
1006 This is a shortcut to the :func:`_expression.cast` function.
1008 .. seealso::
1010 :ref:`tutorial_casts`
1012 :func:`_expression.cast`
1014 :func:`_expression.type_coerce`
1016 .. versionadded:: 1.0.7
1018 """
1019 return Cast(self, type_)
1021 def label(self, name):
1022 """Produce a column label, i.e. ``<columnname> AS <name>``.
1024 This is a shortcut to the :func:`_expression.label` function.
1026 If 'name' is ``None``, an anonymous label name will be generated.
1028 """
1029 return Label(name, self, self.type)
1031 def _anon_label(self, seed, add_hash=None):
1032 while self._is_clone_of is not None:
1033 self = self._is_clone_of
1035 # as of 1.4 anonymous label for ColumnElement uses hash(), not id(),
1036 # as the identifier, because a column and its annotated version are
1037 # the same thing in a SQL statement
1038 hash_value = hash(self)
1040 if add_hash:
1041 # this path is used for disambiguating anon labels that would
1042 # otherwise be the same name for the same element repeated.
1043 # an additional numeric value is factored in for each label.
1045 # shift hash(self) (which is id(self), typically 8 byte integer)
1046 # 16 bits leftward. fill extra add_hash on right
1047 assert add_hash < (2 << 15)
1048 assert seed
1049 hash_value = (hash_value << 16) | add_hash
1051 # extra underscore is added for labels with extra hash
1052 # values, to isolate the "deduped anon" namespace from the
1053 # regular namespace. eliminates chance of these
1054 # manufactured hash values overlapping with regular ones for some
1055 # undefined python interpreter
1056 seed = seed + "_"
1058 if isinstance(seed, _anonymous_label):
1059 return _anonymous_label.safe_construct(
1060 hash_value, "", enclosing_label=seed
1061 )
1063 return _anonymous_label.safe_construct(hash_value, seed or "anon")
1065 @util.memoized_property
1066 def _anon_name_label(self):
1067 """Provides a constant 'anonymous label' for this ColumnElement.
1069 This is a label() expression which will be named at compile time.
1070 The same label() is returned each time ``anon_label`` is called so
1071 that expressions can reference ``anon_label`` multiple times,
1072 producing the same label name at compile time.
1074 The compiler uses this function automatically at compile time
1075 for expressions that are known to be 'unnamed' like binary
1076 expressions and function calls.
1078 .. versionchanged:: 1.4.9 - this attribute was not intended to be
1079 public and is renamed to _anon_name_label. anon_name exists
1080 for backwards compat
1082 """
1083 name = getattr(self, "name", None)
1084 return self._anon_label(name)
1086 @util.memoized_property
1087 def _anon_key_label(self):
1088 """Provides a constant 'anonymous key label' for this ColumnElement.
1090 Compare to ``anon_label``, except that the "key" of the column,
1091 if available, is used to generate the label.
1093 This is used when a deduplicating key is placed into the columns
1094 collection of a selectable.
1096 .. versionchanged:: 1.4.9 - this attribute was not intended to be
1097 public and is renamed to _anon_key_label. anon_key_label exists
1098 for backwards compat
1100 """
1101 return self._anon_label(self._proxy_key)
1103 @property
1104 @util.deprecated(
1105 "1.4",
1106 "The :attr:`_expression.ColumnElement.anon_label` attribute is now "
1107 "private, and the public accessor is deprecated.",
1108 )
1109 def anon_label(self):
1110 return self._anon_name_label
1112 @property
1113 @util.deprecated(
1114 "1.4",
1115 "The :attr:`_expression.ColumnElement.anon_key_label` attribute is "
1116 "now private, and the public accessor is deprecated.",
1117 )
1118 def anon_key_label(self):
1119 return self._anon_key_label
1121 def _dedupe_anon_label_idx(self, idx):
1122 """label to apply to a column that is anon labeled, but repeated
1123 in the SELECT, so that we have to make an "extra anon" label that
1124 disambiguates it from the previous appearance.
1126 these labels come out like "foo_bar_id__1" and have double underscores
1127 in them.
1129 """
1130 label = getattr(self, "name", None)
1132 # current convention is that if the element doesn't have a
1133 # ".name" (usually because it is not NamedColumn), we try to
1134 # use a "table qualified" form for the "dedupe anon" label,
1135 # based on the notion that a label like
1136 # "CAST(casttest.v1 AS DECIMAL) AS casttest_v1__1" looks better than
1137 # "CAST(casttest.v1 AS DECIMAL) AS anon__1"
1139 if label is None:
1140 return self._dedupe_anon_tq_label_idx(idx)
1141 else:
1142 return self._anon_label(label, add_hash=idx)
1144 @util.memoized_property
1145 def _anon_tq_label(self):
1146 return self._anon_label(getattr(self, "_tq_label", None))
1148 @util.memoized_property
1149 def _anon_tq_key_label(self):
1150 return self._anon_label(getattr(self, "_tq_key_label", None))
1152 def _dedupe_anon_tq_label_idx(self, idx):
1153 label = getattr(self, "_tq_label", None) or "anon"
1155 return self._anon_label(label, add_hash=idx)
1158class WrapsColumnExpression(object):
1159 """Mixin that defines a :class:`_expression.ColumnElement`
1160 as a wrapper with special
1161 labeling behavior for an expression that already has a name.
1163 .. versionadded:: 1.4
1165 .. seealso::
1167 :ref:`change_4449`
1170 """
1172 @property
1173 def wrapped_column_expression(self):
1174 raise NotImplementedError()
1176 @property
1177 def _tq_label(self):
1178 wce = self.wrapped_column_expression
1179 if hasattr(wce, "_tq_label"):
1180 return wce._tq_label
1181 else:
1182 return None
1184 _label = _tq_label
1186 @property
1187 def _non_anon_label(self):
1188 return None
1190 @property
1191 def _anon_name_label(self):
1192 wce = self.wrapped_column_expression
1194 # this logic tries to get the WrappedColumnExpression to render
1195 # with "<expr> AS <name>", where "<name>" is the natural name
1196 # within the expression itself. e.g. "CAST(table.foo) AS foo".
1197 if not wce._is_text_clause:
1198 nal = wce._non_anon_label
1199 if nal:
1200 return nal
1201 elif hasattr(wce, "_anon_name_label"):
1202 return wce._anon_name_label
1203 return super(WrapsColumnExpression, self)._anon_name_label
1205 def _dedupe_anon_label_idx(self, idx):
1206 wce = self.wrapped_column_expression
1207 nal = wce._non_anon_label
1208 if nal:
1209 return self._anon_label(nal + "_")
1210 else:
1211 return self._dedupe_anon_tq_label_idx(idx)
1213 @property
1214 def _proxy_key(self):
1215 wce = self.wrapped_column_expression
1217 if not wce._is_text_clause:
1218 return wce._proxy_key
1219 return super(WrapsColumnExpression, self)._proxy_key
1222class BindParameter(roles.InElementRole, ColumnElement):
1223 r"""Represent a "bound expression".
1225 :class:`.BindParameter` is invoked explicitly using the
1226 :func:`.bindparam` function, as in::
1228 from sqlalchemy import bindparam
1230 stmt = select(users_table).\
1231 where(users_table.c.name == bindparam('username'))
1233 Detailed discussion of how :class:`.BindParameter` is used is
1234 at :func:`.bindparam`.
1236 .. seealso::
1238 :func:`.bindparam`
1240 """
1242 __visit_name__ = "bindparam"
1244 _traverse_internals = [
1245 ("key", InternalTraversal.dp_anon_name),
1246 ("type", InternalTraversal.dp_type),
1247 ("callable", InternalTraversal.dp_plain_dict),
1248 ("value", InternalTraversal.dp_plain_obj),
1249 ("literal_execute", InternalTraversal.dp_boolean),
1250 ]
1252 _is_crud = False
1253 _is_bind_parameter = True
1254 _key_is_anon = False
1256 # bindparam implements its own _gen_cache_key() method however
1257 # we check subclasses for this flag, else no cache key is generated
1258 inherit_cache = True
1260 def __init__(
1261 self,
1262 key,
1263 value=NO_ARG,
1264 type_=None,
1265 unique=False,
1266 required=NO_ARG,
1267 quote=None,
1268 callable_=None,
1269 expanding=False,
1270 isoutparam=False,
1271 literal_execute=False,
1272 _compared_to_operator=None,
1273 _compared_to_type=None,
1274 _is_crud=False,
1275 ):
1276 r"""Produce a "bound expression".
1278 The return value is an instance of :class:`.BindParameter`; this
1279 is a :class:`_expression.ColumnElement`
1280 subclass which represents a so-called
1281 "placeholder" value in a SQL expression, the value of which is
1282 supplied at the point at which the statement in executed against a
1283 database connection.
1285 In SQLAlchemy, the :func:`.bindparam` construct has
1286 the ability to carry along the actual value that will be ultimately
1287 used at expression time. In this way, it serves not just as
1288 a "placeholder" for eventual population, but also as a means of
1289 representing so-called "unsafe" values which should not be rendered
1290 directly in a SQL statement, but rather should be passed along
1291 to the :term:`DBAPI` as values which need to be correctly escaped
1292 and potentially handled for type-safety.
1294 When using :func:`.bindparam` explicitly, the use case is typically
1295 one of traditional deferment of parameters; the :func:`.bindparam`
1296 construct accepts a name which can then be referred to at execution
1297 time::
1299 from sqlalchemy import bindparam
1301 stmt = select(users_table).\
1302 where(users_table.c.name == bindparam('username'))
1304 The above statement, when rendered, will produce SQL similar to::
1306 SELECT id, name FROM user WHERE name = :username
1308 In order to populate the value of ``:username`` above, the value
1309 would typically be applied at execution time to a method
1310 like :meth:`_engine.Connection.execute`::
1312 result = connection.execute(stmt, username='wendy')
1314 Explicit use of :func:`.bindparam` is also common when producing
1315 UPDATE or DELETE statements that are to be invoked multiple times,
1316 where the WHERE criterion of the statement is to change on each
1317 invocation, such as::
1319 stmt = (users_table.update().
1320 where(user_table.c.name == bindparam('username')).
1321 values(fullname=bindparam('fullname'))
1322 )
1324 connection.execute(
1325 stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
1326 {"username": "jack", "fullname": "Jack Jones"},
1327 ]
1328 )
1330 SQLAlchemy's Core expression system makes wide use of
1331 :func:`.bindparam` in an implicit sense. It is typical that Python
1332 literal values passed to virtually all SQL expression functions are
1333 coerced into fixed :func:`.bindparam` constructs. For example, given
1334 a comparison operation such as::
1336 expr = users_table.c.name == 'Wendy'
1338 The above expression will produce a :class:`.BinaryExpression`
1339 construct, where the left side is the :class:`_schema.Column` object
1340 representing the ``name`` column, and the right side is a
1341 :class:`.BindParameter` representing the literal value::
1343 print(repr(expr.right))
1344 BindParameter('%(4327771088 name)s', 'Wendy', type_=String())
1346 The expression above will render SQL such as::
1348 user.name = :name_1
1350 Where the ``:name_1`` parameter name is an anonymous name. The
1351 actual string ``Wendy`` is not in the rendered string, but is carried
1352 along where it is later used within statement execution. If we
1353 invoke a statement like the following::
1355 stmt = select(users_table).where(users_table.c.name == 'Wendy')
1356 result = connection.execute(stmt)
1358 We would see SQL logging output as::
1360 SELECT "user".id, "user".name
1361 FROM "user"
1362 WHERE "user".name = %(name_1)s
1363 {'name_1': 'Wendy'}
1365 Above, we see that ``Wendy`` is passed as a parameter to the database,
1366 while the placeholder ``:name_1`` is rendered in the appropriate form
1367 for the target database, in this case the PostgreSQL database.
1369 Similarly, :func:`.bindparam` is invoked automatically when working
1370 with :term:`CRUD` statements as far as the "VALUES" portion is
1371 concerned. The :func:`_expression.insert` construct produces an
1372 ``INSERT`` expression which will, at statement execution time, generate
1373 bound placeholders based on the arguments passed, as in::
1375 stmt = users_table.insert()
1376 result = connection.execute(stmt, name='Wendy')
1378 The above will produce SQL output as::
1380 INSERT INTO "user" (name) VALUES (%(name)s)
1381 {'name': 'Wendy'}
1383 The :class:`_expression.Insert` construct, at
1384 compilation/execution time, rendered a single :func:`.bindparam`
1385 mirroring the column name ``name`` as a result of the single ``name``
1386 parameter we passed to the :meth:`_engine.Connection.execute` method.
1388 :param key:
1389 the key (e.g. the name) for this bind param.
1390 Will be used in the generated
1391 SQL statement for dialects that use named parameters. This
1392 value may be modified when part of a compilation operation,
1393 if other :class:`BindParameter` objects exist with the same
1394 key, or if its length is too long and truncation is
1395 required.
1397 :param value:
1398 Initial value for this bind param. Will be used at statement
1399 execution time as the value for this parameter passed to the
1400 DBAPI, if no other value is indicated to the statement execution
1401 method for this particular parameter name. Defaults to ``None``.
1403 :param callable\_:
1404 A callable function that takes the place of "value". The function
1405 will be called at statement execution time to determine the
1406 ultimate value. Used for scenarios where the actual bind
1407 value cannot be determined at the point at which the clause
1408 construct is created, but embedded bind values are still desirable.
1410 :param type\_:
1411 A :class:`.TypeEngine` class or instance representing an optional
1412 datatype for this :func:`.bindparam`. If not passed, a type
1413 may be determined automatically for the bind, based on the given
1414 value; for example, trivial Python types such as ``str``,
1415 ``int``, ``bool``
1416 may result in the :class:`.String`, :class:`.Integer` or
1417 :class:`.Boolean` types being automatically selected.
1419 The type of a :func:`.bindparam` is significant especially in that
1420 the type will apply pre-processing to the value before it is
1421 passed to the database. For example, a :func:`.bindparam` which
1422 refers to a datetime value, and is specified as holding the
1423 :class:`.DateTime` type, may apply conversion needed to the
1424 value (such as stringification on SQLite) before passing the value
1425 to the database.
1427 :param unique:
1428 if True, the key name of this :class:`.BindParameter` will be
1429 modified if another :class:`.BindParameter` of the same name
1430 already has been located within the containing
1431 expression. This flag is used generally by the internals
1432 when producing so-called "anonymous" bound expressions, it
1433 isn't generally applicable to explicitly-named :func:`.bindparam`
1434 constructs.
1436 :param required:
1437 If ``True``, a value is required at execution time. If not passed,
1438 it defaults to ``True`` if neither :paramref:`.bindparam.value`
1439 or :paramref:`.bindparam.callable` were passed. If either of these
1440 parameters are present, then :paramref:`.bindparam.required`
1441 defaults to ``False``.
1443 :param quote:
1444 True if this parameter name requires quoting and is not
1445 currently known as a SQLAlchemy reserved word; this currently
1446 only applies to the Oracle backend, where bound names must
1447 sometimes be quoted.
1449 :param isoutparam:
1450 if True, the parameter should be treated like a stored procedure
1451 "OUT" parameter. This applies to backends such as Oracle which
1452 support OUT parameters.
1454 :param expanding:
1455 if True, this parameter will be treated as an "expanding" parameter
1456 at execution time; the parameter value is expected to be a sequence,
1457 rather than a scalar value, and the string SQL statement will
1458 be transformed on a per-execution basis to accommodate the sequence
1459 with a variable number of parameter slots passed to the DBAPI.
1460 This is to allow statement caching to be used in conjunction with
1461 an IN clause.
1463 .. seealso::
1465 :meth:`.ColumnOperators.in_`
1467 :ref:`baked_in` - with baked queries
1469 .. note:: The "expanding" feature does not support "executemany"-
1470 style parameter sets.
1472 .. versionadded:: 1.2
1474 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
1475 supports empty lists.
1477 :param literal_execute:
1478 if True, the bound parameter will be rendered in the compile phase
1479 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
1480 render the final value of the parameter into the SQL statement at
1481 statement execution time, omitting the value from the parameter
1482 dictionary / list passed to DBAPI ``cursor.execute()``. This
1483 produces a similar effect as that of using the ``literal_binds``,
1484 compilation flag, however takes place as the statement is sent to
1485 the DBAPI ``cursor.execute()`` method, rather than when the statement
1486 is compiled. The primary use of this
1487 capability is for rendering LIMIT / OFFSET clauses for database
1488 drivers that can't accommodate for bound parameters in these
1489 contexts, while allowing SQL constructs to be cacheable at the
1490 compilation level.
1492 .. versionadded:: 1.4 Added "post compile" bound parameters
1494 .. seealso::
1496 :ref:`change_4808`.
1498 .. seealso::
1500 :ref:`tutorial_sending_parameters` - in the
1501 :ref:`unified_tutorial`
1503 """
1504 if required is NO_ARG:
1505 required = value is NO_ARG and callable_ is None
1506 if value is NO_ARG:
1507 value = None
1509 if quote is not None:
1510 key = quoted_name(key, quote)
1512 if unique:
1513 self.key = _anonymous_label.safe_construct(
1514 id(self),
1515 key
1516 if key is not None and not isinstance(key, _anonymous_label)
1517 else "param",
1518 sanitize_key=True,
1519 )
1520 self._key_is_anon = True
1521 elif key:
1522 self.key = key
1523 else:
1524 self.key = _anonymous_label.safe_construct(id(self), "param")
1525 self._key_is_anon = True
1527 # identifying key that won't change across
1528 # clones, used to identify the bind's logical
1529 # identity
1530 self._identifying_key = self.key
1532 # key that was passed in the first place, used to
1533 # generate new keys
1534 self._orig_key = key or "param"
1536 self.unique = unique
1537 self.value = value
1538 self.callable = callable_
1539 self.isoutparam = isoutparam
1540 self.required = required
1542 # indicate an "expanding" parameter; the compiler sets this
1543 # automatically in the compiler _render_in_expr_w_bindparam method
1544 # for an IN expression
1545 self.expanding = expanding
1547 # this is another hint to help w/ expanding and is typically
1548 # set in the compiler _render_in_expr_w_bindparam method for an
1549 # IN expression
1550 self.expand_op = None
1552 self.literal_execute = literal_execute
1553 if _is_crud:
1554 self._is_crud = True
1556 if type_ is None:
1557 if expanding and value:
1558 check_value = value[0]
1559 else:
1560 check_value = value
1561 if _compared_to_type is not None:
1562 self.type = _compared_to_type.coerce_compared_value(
1563 _compared_to_operator, check_value
1564 )
1565 else:
1566 self.type = type_api._resolve_value_to_type(check_value)
1567 elif isinstance(type_, type):
1568 self.type = type_()
1569 elif type_._is_tuple_type and value:
1570 if expanding:
1571 check_value = value[0]
1572 else:
1573 check_value = value
1574 self.type = type_._resolve_values_to_types(check_value)
1575 else:
1576 self.type = type_
1578 def _with_value(self, value, maintain_key=False, required=NO_ARG):
1579 """Return a copy of this :class:`.BindParameter` with the given value
1580 set.
1581 """
1582 cloned = self._clone(maintain_key=maintain_key)
1583 cloned.value = value
1584 cloned.callable = None
1585 cloned.required = required if required is not NO_ARG else self.required
1586 if cloned.type is type_api.NULLTYPE:
1587 cloned.type = type_api._resolve_value_to_type(value)
1588 return cloned
1590 @property
1591 def effective_value(self):
1592 """Return the value of this bound parameter,
1593 taking into account if the ``callable`` parameter
1594 was set.
1596 The ``callable`` value will be evaluated
1597 and returned if present, else ``value``.
1599 """
1600 if self.callable:
1601 return self.callable()
1602 else:
1603 return self.value
1605 def render_literal_execute(self):
1606 """Produce a copy of this bound parameter that will enable the
1607 :paramref:`_sql.BindParameter.literal_execute` flag.
1609 The :paramref:`_sql.BindParameter.literal_execute` flag will
1610 have the effect of the parameter rendered in the compiled SQL
1611 string using ``[POSTCOMPILE]`` form, which is a special form that
1612 is converted to be a rendering of the literal value of the parameter
1613 at SQL execution time. The rationale is to support caching
1614 of SQL statement strings that can embed per-statement literal values,
1615 such as LIMIT and OFFSET parameters, in the final SQL string that
1616 is passed to the DBAPI. Dialects in particular may want to use
1617 this method within custom compilation schemes.
1619 .. versionadded:: 1.4.5
1621 .. seealso::
1623 :ref:`engine_thirdparty_caching`
1625 """
1626 return self.__class__(
1627 self.key,
1628 self.value,
1629 type_=self.type,
1630 literal_execute=True,
1631 )
1633 def _negate_in_binary(self, negated_op, original_op):
1634 if self.expand_op is original_op:
1635 bind = self._clone()
1636 bind.expand_op = negated_op
1637 return bind
1638 else:
1639 return self
1641 def _with_binary_element_type(self, type_):
1642 c = ClauseElement._clone(self)
1643 c.type = type_
1644 return c
1646 def _clone(self, maintain_key=False, **kw):
1647 c = ClauseElement._clone(self, **kw)
1648 # ensure all the BindParameter objects stay in cloned set.
1649 # in #7823, we changed "clone" so that a clone only keeps a reference
1650 # to the "original" element, since for column correspondence, that's
1651 # all we need. However, for BindParam, _cloned_set is used by
1652 # the "cache key bind match" lookup, which means if any of those
1653 # interim BindParameter objects became part of a cache key in the
1654 # cache, we need it. So here, make sure all clones keep carrying
1655 # forward.
1656 c._cloned_set.update(self._cloned_set)
1657 if not maintain_key and self.unique:
1658 c.key = _anonymous_label.safe_construct(
1659 id(c), c._orig_key or "param", sanitize_key=True
1660 )
1661 return c
1663 def _gen_cache_key(self, anon_map, bindparams):
1664 _gen_cache_ok = self.__class__.__dict__.get("inherit_cache", False)
1666 if not _gen_cache_ok:
1667 if anon_map is not None:
1668 anon_map[NO_CACHE] = True
1669 return None
1671 idself = id(self)
1672 if idself in anon_map:
1673 return (anon_map[idself], self.__class__)
1674 else:
1675 # inline of
1676 # id_ = anon_map[idself]
1677 anon_map[idself] = id_ = str(anon_map.index)
1678 anon_map.index += 1
1680 if bindparams is not None:
1681 bindparams.append(self)
1683 return (
1684 id_,
1685 self.__class__,
1686 self.type._static_cache_key,
1687 self.key % anon_map if self._key_is_anon else self.key,
1688 self.literal_execute,
1689 )
1691 def _convert_to_unique(self):
1692 if not self.unique:
1693 self.unique = True
1694 self.key = _anonymous_label.safe_construct(
1695 id(self), self._orig_key or "param", sanitize_key=True
1696 )
1698 def __getstate__(self):
1699 """execute a deferred value for serialization purposes."""
1701 d = self.__dict__.copy()
1702 v = self.value
1703 if self.callable:
1704 v = self.callable()
1705 d["callable"] = None
1706 d["value"] = v
1707 return d
1709 def __setstate__(self, state):
1710 if state.get("unique", False):
1711 state["key"] = _anonymous_label.safe_construct(
1712 id(self), state.get("_orig_key", "param"), sanitize_key=True
1713 )
1714 self.__dict__.update(state)
1716 def __repr__(self):
1717 return "%s(%r, %r, type_=%r)" % (
1718 self.__class__.__name__,
1719 self.key,
1720 self.value,
1721 self.type,
1722 )
1725class TypeClause(ClauseElement):
1726 """Handle a type keyword in a SQL statement.
1728 Used by the ``Case`` statement.
1730 """
1732 __visit_name__ = "typeclause"
1734 _traverse_internals = [("type", InternalTraversal.dp_type)]
1736 def __init__(self, type_):
1737 self.type = type_
1740class TextClause(
1741 roles.DDLConstraintColumnRole,
1742 roles.DDLExpressionRole,
1743 roles.StatementOptionRole,
1744 roles.WhereHavingRole,
1745 roles.OrderByRole,
1746 roles.FromClauseRole,
1747 roles.SelectStatementRole,
1748 roles.BinaryElementRole,
1749 roles.InElementRole,
1750 Executable,
1751 ClauseElement,
1752):
1753 """Represent a literal SQL text fragment.
1755 E.g.::
1757 from sqlalchemy import text
1759 t = text("SELECT * FROM users")
1760 result = connection.execute(t)
1763 The :class:`_expression.TextClause` construct is produced using the
1764 :func:`_expression.text`
1765 function; see that function for full documentation.
1767 .. seealso::
1769 :func:`_expression.text`
1771 """
1773 __visit_name__ = "textclause"
1775 _traverse_internals = [
1776 ("_bindparams", InternalTraversal.dp_string_clauseelement_dict),
1777 ("text", InternalTraversal.dp_string),
1778 ]
1780 _is_text_clause = True
1782 _is_textual = True
1784 _bind_params_regex = re.compile(r"(?<![:\w\x5c]):(\w+)(?!:)", re.UNICODE)
1785 _execution_options = Executable._execution_options.union(
1786 {"autocommit": PARSE_AUTOCOMMIT}
1787 )
1788 _is_implicitly_boolean = False
1790 _render_label_in_columns_clause = False
1792 _hide_froms = ()
1794 def __and__(self, other):
1795 # support use in select.where(), query.filter()
1796 return and_(self, other)
1798 @property
1799 def _select_iterable(self):
1800 return (self,)
1802 # help in those cases where text() is
1803 # interpreted in a column expression situation
1804 key = _label = None
1806 _allow_label_resolve = False
1808 @property
1809 def _is_star(self):
1810 return self.text == "*"
1812 def __init__(self, text, bind=None):
1813 self._bind = bind
1814 self._bindparams = {}
1816 def repl(m):
1817 self._bindparams[m.group(1)] = BindParameter(m.group(1))
1818 return ":%s" % m.group(1)
1820 # scan the string and search for bind parameter names, add them
1821 # to the list of bindparams
1822 self.text = self._bind_params_regex.sub(repl, text)
1824 @classmethod
1825 @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1826 @util.deprecated_params(
1827 bind=(
1828 "2.0",
1829 "The :paramref:`_sql.text.bind` argument is deprecated and "
1830 "will be removed in SQLAlchemy 2.0.",
1831 ),
1832 )
1833 def _create_text(cls, text, bind=None):
1834 r"""Construct a new :class:`_expression.TextClause` clause,
1835 representing
1836 a textual SQL string directly.
1838 E.g.::
1840 from sqlalchemy import text
1842 t = text("SELECT * FROM users")
1843 result = connection.execute(t)
1845 The advantages :func:`_expression.text`
1846 provides over a plain string are
1847 backend-neutral support for bind parameters, per-statement
1848 execution options, as well as
1849 bind parameter and result-column typing behavior, allowing
1850 SQLAlchemy type constructs to play a role when executing
1851 a statement that is specified literally. The construct can also
1852 be provided with a ``.c`` collection of column elements, allowing
1853 it to be embedded in other SQL expression constructs as a subquery.
1855 Bind parameters are specified by name, using the format ``:name``.
1856 E.g.::
1858 t = text("SELECT * FROM users WHERE id=:user_id")
1859 result = connection.execute(t, user_id=12)
1861 For SQL statements where a colon is required verbatim, as within
1862 an inline string, use a backslash to escape::
1864 t = text("SELECT * FROM users WHERE name='\:username'")
1866 The :class:`_expression.TextClause`
1867 construct includes methods which can
1868 provide information about the bound parameters as well as the column
1869 values which would be returned from the textual statement, assuming
1870 it's an executable SELECT type of statement. The
1871 :meth:`_expression.TextClause.bindparams`
1872 method is used to provide bound
1873 parameter detail, and :meth:`_expression.TextClause.columns`
1874 method allows
1875 specification of return columns including names and types::
1877 t = text("SELECT * FROM users WHERE id=:user_id").\
1878 bindparams(user_id=7).\
1879 columns(id=Integer, name=String)
1881 for id, name in connection.execute(t):
1882 print(id, name)
1884 The :func:`_expression.text` construct is used in cases when
1885 a literal string SQL fragment is specified as part of a larger query,
1886 such as for the WHERE clause of a SELECT statement::
1888 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1889 result = connection.execute(s, user_id=12)
1891 :func:`_expression.text` is also used for the construction
1892 of a full, standalone statement using plain text.
1893 As such, SQLAlchemy refers
1894 to it as an :class:`.Executable` object, and it supports
1895 the :meth:`Executable.execution_options` method. For example,
1896 a :func:`_expression.text`
1897 construct that should be subject to "autocommit"
1898 can be set explicitly so using the
1899 :paramref:`.Connection.execution_options.autocommit` option::
1901 t = text("EXEC my_procedural_thing()").\
1902 execution_options(autocommit=True)
1904 .. deprecated:: 1.4 The "autocommit" execution option is deprecated
1905 and will be removed in SQLAlchemy 2.0. See
1906 :ref:`migration_20_autocommit` for discussion.
1908 :param text:
1909 the text of the SQL statement to be created. Use ``:<param>``
1910 to specify bind parameters; they will be compiled to their
1911 engine-specific format.
1913 :param bind:
1914 an optional connection or engine to be used for this text query.
1916 .. seealso::
1918 :ref:`tutorial_select_arbitrary_text`
1921 """
1922 return TextClause(text, bind=bind)
1924 @_generative
1925 def bindparams(self, *binds, **names_to_values):
1926 """Establish the values and/or types of bound parameters within
1927 this :class:`_expression.TextClause` construct.
1929 Given a text construct such as::
1931 from sqlalchemy import text
1932 stmt = text("SELECT id, name FROM user WHERE name=:name "
1933 "AND timestamp=:timestamp")
1935 the :meth:`_expression.TextClause.bindparams`
1936 method can be used to establish
1937 the initial value of ``:name`` and ``:timestamp``,
1938 using simple keyword arguments::
1940 stmt = stmt.bindparams(name='jack',
1941 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
1943 Where above, new :class:`.BindParameter` objects
1944 will be generated with the names ``name`` and ``timestamp``, and
1945 values of ``jack`` and ``datetime.datetime(2012, 10, 8, 15, 12, 5)``,
1946 respectively. The types will be
1947 inferred from the values given, in this case :class:`.String` and
1948 :class:`.DateTime`.
1950 When specific typing behavior is needed, the positional ``*binds``
1951 argument can be used in which to specify :func:`.bindparam` constructs
1952 directly. These constructs must include at least the ``key``
1953 argument, then an optional value and type::
1955 from sqlalchemy import bindparam
1956 stmt = stmt.bindparams(
1957 bindparam('name', value='jack', type_=String),
1958 bindparam('timestamp', type_=DateTime)
1959 )
1961 Above, we specified the type of :class:`.DateTime` for the
1962 ``timestamp`` bind, and the type of :class:`.String` for the ``name``
1963 bind. In the case of ``name`` we also set the default value of
1964 ``"jack"``.
1966 Additional bound parameters can be supplied at statement execution
1967 time, e.g.::
1969 result = connection.execute(stmt,
1970 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5))
1972 The :meth:`_expression.TextClause.bindparams`
1973 method can be called repeatedly,
1974 where it will re-use existing :class:`.BindParameter` objects to add
1975 new information. For example, we can call
1976 :meth:`_expression.TextClause.bindparams`
1977 first with typing information, and a
1978 second time with value information, and it will be combined::
1980 stmt = text("SELECT id, name FROM user WHERE name=:name "
1981 "AND timestamp=:timestamp")
1982 stmt = stmt.bindparams(
1983 bindparam('name', type_=String),
1984 bindparam('timestamp', type_=DateTime)
1985 )
1986 stmt = stmt.bindparams(
1987 name='jack',
1988 timestamp=datetime.datetime(2012, 10, 8, 15, 12, 5)
1989 )
1991 The :meth:`_expression.TextClause.bindparams`
1992 method also supports the concept of
1993 **unique** bound parameters. These are parameters that are
1994 "uniquified" on name at statement compilation time, so that multiple
1995 :func:`_expression.text`
1996 constructs may be combined together without the names
1997 conflicting. To use this feature, specify the
1998 :paramref:`.BindParameter.unique` flag on each :func:`.bindparam`
1999 object::
2001 stmt1 = text("select id from table where name=:name").bindparams(
2002 bindparam("name", value='name1', unique=True)
2003 )
2004 stmt2 = text("select id from table where name=:name").bindparams(
2005 bindparam("name", value='name2', unique=True)
2006 )
2008 union = union_all(
2009 stmt1.columns(column("id")),
2010 stmt2.columns(column("id"))
2011 )
2013 The above statement will render as::
2015 select id from table where name=:name_1
2016 UNION ALL select id from table where name=:name_2
2018 .. versionadded:: 1.3.11 Added support for the
2019 :paramref:`.BindParameter.unique` flag to work with
2020 :func:`_expression.text`
2021 constructs.
2023 """
2024 self._bindparams = new_params = self._bindparams.copy()
2026 for bind in binds:
2027 try:
2028 # the regex used for text() currently will not match
2029 # a unique/anonymous key in any case, so use the _orig_key
2030 # so that a text() construct can support unique parameters
2031 existing = new_params[bind._orig_key]
2032 except KeyError as err:
2033 util.raise_(
2034 exc.ArgumentError(
2035 "This text() construct doesn't define a "
2036 "bound parameter named %r" % bind._orig_key
2037 ),
2038 replace_context=err,
2039 )
2040 else:
2041 new_params[existing._orig_key] = bind
2043 for key, value in names_to_values.items():
2044 try:
2045 existing = new_params[key]
2046 except KeyError as err:
2047 util.raise_(
2048 exc.ArgumentError(
2049 "This text() construct doesn't define a "
2050 "bound parameter named %r" % key
2051 ),
2052 replace_context=err,
2053 )
2054 else:
2055 new_params[key] = existing._with_value(value, required=False)
2057 @util.preload_module("sqlalchemy.sql.selectable")
2058 def columns(self, *cols, **types):
2059 r"""Turn this :class:`_expression.TextClause` object into a
2060 :class:`_expression.TextualSelect`
2061 object that serves the same role as a SELECT
2062 statement.
2064 The :class:`_expression.TextualSelect` is part of the
2065 :class:`_expression.SelectBase`
2066 hierarchy and can be embedded into another statement by using the
2067 :meth:`_expression.TextualSelect.subquery` method to produce a
2068 :class:`.Subquery`
2069 object, which can then be SELECTed from.
2071 This function essentially bridges the gap between an entirely
2072 textual SELECT statement and the SQL expression language concept
2073 of a "selectable"::
2075 from sqlalchemy.sql import column, text
2077 stmt = text("SELECT id, name FROM some_table")
2078 stmt = stmt.columns(column('id'), column('name')).subquery('st')
2080 stmt = select(mytable).\
2081 select_from(
2082 mytable.join(stmt, mytable.c.name == stmt.c.name)
2083 ).where(stmt.c.id > 5)
2085 Above, we pass a series of :func:`_expression.column` elements to the
2086 :meth:`_expression.TextClause.columns` method positionally. These
2087 :func:`_expression.column`
2088 elements now become first class elements upon the
2089 :attr:`_expression.TextualSelect.selected_columns` column collection,
2090 which then
2091 become part of the :attr:`.Subquery.c` collection after
2092 :meth:`_expression.TextualSelect.subquery` is invoked.
2094 The column expressions we pass to
2095 :meth:`_expression.TextClause.columns` may
2096 also be typed; when we do so, these :class:`.TypeEngine` objects become
2097 the effective return type of the column, so that SQLAlchemy's
2098 result-set-processing systems may be used on the return values.
2099 This is often needed for types such as date or boolean types, as well
2100 as for unicode processing on some dialect configurations::
2102 stmt = text("SELECT id, name, timestamp FROM some_table")
2103 stmt = stmt.columns(
2104 column('id', Integer),
2105 column('name', Unicode),
2106 column('timestamp', DateTime)
2107 )
2109 for id, name, timestamp in connection.execute(stmt):
2110 print(id, name, timestamp)
2112 As a shortcut to the above syntax, keyword arguments referring to
2113 types alone may be used, if only type conversion is needed::
2115 stmt = text("SELECT id, name, timestamp FROM some_table")
2116 stmt = stmt.columns(
2117 id=Integer,
2118 name=Unicode,
2119 timestamp=DateTime
2120 )
2122 for id, name, timestamp in connection.execute(stmt):
2123 print(id, name, timestamp)
2125 The positional form of :meth:`_expression.TextClause.columns`
2126 also provides the
2127 unique feature of **positional column targeting**, which is
2128 particularly useful when using the ORM with complex textual queries. If
2129 we specify the columns from our model to
2130 :meth:`_expression.TextClause.columns`,
2131 the result set will match to those columns positionally, meaning the
2132 name or origin of the column in the textual SQL doesn't matter::
2134 stmt = text("SELECT users.id, addresses.id, users.id, "
2135 "users.name, addresses.email_address AS email "
2136 "FROM users JOIN addresses ON users.id=addresses.user_id "
2137 "WHERE users.id = 1").columns(
2138 User.id,
2139 Address.id,
2140 Address.user_id,
2141 User.name,
2142 Address.email_address
2143 )
2145 query = session.query(User).from_statement(stmt).options(
2146 contains_eager(User.addresses))
2148 .. versionadded:: 1.1 the :meth:`_expression.TextClause.columns`
2149 method now
2150 offers positional column targeting in the result set when
2151 the column expressions are passed purely positionally.
2153 The :meth:`_expression.TextClause.columns` method provides a direct
2154 route to calling :meth:`_expression.FromClause.subquery` as well as
2155 :meth:`_expression.SelectBase.cte`
2156 against a textual SELECT statement::
2158 stmt = stmt.columns(id=Integer, name=String).cte('st')
2160 stmt = select(sometable).where(sometable.c.id == stmt.c.id)
2162 :param \*cols: A series of :class:`_expression.ColumnElement` objects,
2163 typically
2164 :class:`_schema.Column` objects from a :class:`_schema.Table`
2165 or ORM level
2166 column-mapped attributes, representing a set of columns that this
2167 textual string will SELECT from.
2169 :param \**types: A mapping of string names to :class:`.TypeEngine`
2170 type objects indicating the datatypes to use for names that are
2171 SELECTed from the textual string. Prefer to use the ``*cols``
2172 argument as it also indicates positional ordering.
2174 """
2175 selectable = util.preloaded.sql_selectable
2176 positional_input_cols = [
2177 ColumnClause(col.key, types.pop(col.key))
2178 if col.key in types
2179 else col
2180 for col in cols
2181 ]
2182 keyed_input_cols = [
2183 ColumnClause(key, type_) for key, type_ in types.items()
2184 ]
2186 return selectable.TextualSelect(
2187 self,
2188 positional_input_cols + keyed_input_cols,
2189 positional=bool(positional_input_cols) and not keyed_input_cols,
2190 )
2192 @property
2193 def type(self):
2194 return type_api.NULLTYPE
2196 @property
2197 def comparator(self):
2198 return self.type.comparator_factory(self)
2200 def self_group(self, against=None):
2201 if against is operators.in_op:
2202 return Grouping(self)
2203 else:
2204 return self
2207class Null(SingletonConstant, roles.ConstExprRole, ColumnElement):
2208 """Represent the NULL keyword in a SQL statement.
2210 :class:`.Null` is accessed as a constant via the
2211 :func:`.null` function.
2213 """
2215 __visit_name__ = "null"
2217 _traverse_internals = []
2219 @util.memoized_property
2220 def type(self):
2221 return type_api.NULLTYPE
2223 @classmethod
2224 def _instance(cls):
2225 """Return a constant :class:`.Null` construct."""
2227 return Null()
2230Null._create_singleton()
2233class False_(SingletonConstant, roles.ConstExprRole, ColumnElement):
2234 """Represent the ``false`` keyword, or equivalent, in a SQL statement.
2236 :class:`.False_` is accessed as a constant via the
2237 :func:`.false` function.
2239 """
2241 __visit_name__ = "false"
2242 _traverse_internals = []
2244 @util.memoized_property
2245 def type(self):
2246 return type_api.BOOLEANTYPE
2248 def _negate(self):
2249 return True_()
2251 @classmethod
2252 def _instance(cls):
2253 """Return a :class:`.False_` construct.
2255 E.g.::
2257 >>> from sqlalchemy import false
2258 >>> print(select(t.c.x).where(false()))
2259 SELECT x FROM t WHERE false
2261 A backend which does not support true/false constants will render as
2262 an expression against 1 or 0::
2264 >>> print(select(t.c.x).where(false()))
2265 SELECT x FROM t WHERE 0 = 1
2267 The :func:`.true` and :func:`.false` constants also feature
2268 "short circuit" operation within an :func:`.and_` or :func:`.or_`
2269 conjunction::
2271 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
2272 SELECT x FROM t WHERE true
2274 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
2275 SELECT x FROM t WHERE false
2277 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature
2278 better integrated behavior within conjunctions and on dialects
2279 that don't support true/false constants.
2281 .. seealso::
2283 :func:`.true`
2285 """
2287 return False_()
2290False_._create_singleton()
2293class True_(SingletonConstant, roles.ConstExprRole, ColumnElement):
2294 """Represent the ``true`` keyword, or equivalent, in a SQL statement.
2296 :class:`.True_` is accessed as a constant via the
2297 :func:`.true` function.
2299 """
2301 __visit_name__ = "true"
2303 _traverse_internals = []
2305 @util.memoized_property
2306 def type(self):
2307 return type_api.BOOLEANTYPE
2309 def _negate(self):
2310 return False_()
2312 @classmethod
2313 def _ifnone(cls, other):
2314 if other is None:
2315 return cls._instance()
2316 else:
2317 return other
2319 @classmethod
2320 def _instance(cls):
2321 """Return a constant :class:`.True_` construct.
2323 E.g.::
2325 >>> from sqlalchemy import true
2326 >>> print(select(t.c.x).where(true()))
2327 SELECT x FROM t WHERE true
2329 A backend which does not support true/false constants will render as
2330 an expression against 1 or 0::
2332 >>> print(select(t.c.x).where(true()))
2333 SELECT x FROM t WHERE 1 = 1
2335 The :func:`.true` and :func:`.false` constants also feature
2336 "short circuit" operation within an :func:`.and_` or :func:`.or_`
2337 conjunction::
2339 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
2340 SELECT x FROM t WHERE true
2342 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
2343 SELECT x FROM t WHERE false
2345 .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature
2346 better integrated behavior within conjunctions and on dialects
2347 that don't support true/false constants.
2349 .. seealso::
2351 :func:`.false`
2353 """
2355 return True_()
2358True_._create_singleton()
2361class ClauseList(
2362 roles.InElementRole,
2363 roles.OrderByRole,
2364 roles.ColumnsClauseRole,
2365 roles.DMLColumnRole,
2366 ClauseElement,
2367):
2368 """Describe a list of clauses, separated by an operator.
2370 By default, is comma-separated, such as a column listing.
2372 """
2374 __visit_name__ = "clauselist"
2376 _is_clause_list = True
2378 _traverse_internals = [
2379 ("clauses", InternalTraversal.dp_clauseelement_list),
2380 ("operator", InternalTraversal.dp_operator),
2381 ]
2383 def __init__(self, *clauses, **kwargs):
2384 self.operator = kwargs.pop("operator", operators.comma_op)
2385 self.group = kwargs.pop("group", True)
2386 self.group_contents = kwargs.pop("group_contents", True)
2387 if kwargs.pop("_flatten_sub_clauses", False):
2388 clauses = util.flatten_iterator(clauses)
2389 self._text_converter_role = text_converter_role = kwargs.pop(
2390 "_literal_as_text_role", roles.WhereHavingRole
2391 )
2392 if self.group_contents:
2393 self.clauses = [
2394 coercions.expect(
2395 text_converter_role, clause, apply_propagate_attrs=self
2396 ).self_group(against=self.operator)
2397 for clause in clauses
2398 ]
2399 else:
2400 self.clauses = [
2401 coercions.expect(
2402 text_converter_role, clause, apply_propagate_attrs=self
2403 )
2404 for clause in clauses
2405 ]
2406 self._is_implicitly_boolean = operators.is_boolean(self.operator)
2408 @classmethod
2409 def _construct_raw(cls, operator, clauses=None):
2410 self = cls.__new__(cls)
2411 self.clauses = clauses if clauses else []
2412 self.group = True
2413 self.operator = operator
2414 self.group_contents = True
2415 self._is_implicitly_boolean = False
2416 return self
2418 def __iter__(self):
2419 return iter(self.clauses)
2421 def __len__(self):
2422 return len(self.clauses)
2424 @property
2425 def _select_iterable(self):
2426 return itertools.chain.from_iterable(
2427 [elem._select_iterable for elem in self.clauses]
2428 )
2430 def append(self, clause):
2431 if self.group_contents:
2432 self.clauses.append(
2433 coercions.expect(self._text_converter_role, clause).self_group(
2434 against=self.operator
2435 )
2436 )
2437 else:
2438 self.clauses.append(
2439 coercions.expect(self._text_converter_role, clause)
2440 )
2442 @property
2443 def _from_objects(self):
2444 return list(itertools.chain(*[c._from_objects for c in self.clauses]))
2446 def self_group(self, against=None):
2447 if self.group and operators.is_precedent(self.operator, against):
2448 return Grouping(self)
2449 else:
2450 return self
2453class BooleanClauseList(ClauseList, ColumnElement):
2454 __visit_name__ = "clauselist"
2455 inherit_cache = True
2457 def __init__(self, *arg, **kw):
2458 raise NotImplementedError(
2459 "BooleanClauseList has a private constructor"
2460 )
2462 @classmethod
2463 def _process_clauses_for_boolean(
2464 cls, operator, continue_on, skip_on, clauses
2465 ):
2466 has_continue_on = None
2468 convert_clauses = []
2470 against = operators._asbool
2471 lcc = 0
2473 for clause in clauses:
2474 if clause is continue_on:
2475 # instance of continue_on, like and_(x, y, True, z), store it
2476 # if we didn't find one already, we will use it if there
2477 # are no other expressions here.
2478 has_continue_on = clause
2479 elif clause is skip_on:
2480 # instance of skip_on, e.g. and_(x, y, False, z), cancels
2481 # the rest out
2482 convert_clauses = [clause]
2483 lcc = 1
2484 break
2485 else:
2486 if not lcc:
2487 lcc = 1
2488 else:
2489 against = operator
2490 # technically this would be len(convert_clauses) + 1
2491 # however this only needs to indicate "greater than one"
2492 lcc = 2
2493 convert_clauses.append(clause)
2495 if not convert_clauses and has_continue_on is not None:
2496 convert_clauses = [has_continue_on]
2497 lcc = 1
2499 return lcc, [c.self_group(against=against) for c in convert_clauses]
2501 @classmethod
2502 def _construct(cls, operator, continue_on, skip_on, *clauses, **kw):
2503 lcc, convert_clauses = cls._process_clauses_for_boolean(
2504 operator,
2505 continue_on,
2506 skip_on,
2507 [
2508 coercions.expect(roles.WhereHavingRole, clause)
2509 for clause in util.coerce_generator_arg(clauses)
2510 ],
2511 )
2513 if lcc > 1:
2514 # multiple elements. Return regular BooleanClauseList
2515 # which will link elements against the operator.
2516 return cls._construct_raw(operator, convert_clauses)
2517 elif lcc == 1:
2518 # just one element. return it as a single boolean element,
2519 # not a list and discard the operator.
2520 return convert_clauses[0]
2521 else:
2522 # no elements period. deprecated use case. return an empty
2523 # ClauseList construct that generates nothing unless it has
2524 # elements added to it.
2525 util.warn_deprecated(
2526 "Invoking %(name)s() without arguments is deprecated, and "
2527 "will be disallowed in a future release. For an empty "
2528 "%(name)s() construct, use %(name)s(%(continue_on)s, *args)."
2529 % {
2530 "name": operator.__name__,
2531 "continue_on": "True"
2532 if continue_on is True_._singleton
2533 else "False",
2534 },
2535 version="1.4",
2536 )
2537 return cls._construct_raw(operator)
2539 @classmethod
2540 def _construct_for_whereclause(cls, clauses):
2541 operator, continue_on, skip_on = (
2542 operators.and_,
2543 True_._singleton,
2544 False_._singleton,
2545 )
2547 lcc, convert_clauses = cls._process_clauses_for_boolean(
2548 operator,
2549 continue_on,
2550 skip_on,
2551 clauses, # these are assumed to be coerced already
2552 )
2554 if lcc > 1:
2555 # multiple elements. Return regular BooleanClauseList
2556 # which will link elements against the operator.
2557 return cls._construct_raw(operator, convert_clauses)
2558 elif lcc == 1:
2559 # just one element. return it as a single boolean element,
2560 # not a list and discard the operator.
2561 return convert_clauses[0]
2562 else:
2563 return None
2565 @classmethod
2566 def _construct_raw(cls, operator, clauses=None):
2567 self = cls.__new__(cls)
2568 self.clauses = clauses if clauses else []
2569 self.group = True
2570 self.operator = operator
2571 self.group_contents = True
2572 self.type = type_api.BOOLEANTYPE
2573 self._is_implicitly_boolean = True
2574 return self
2576 @classmethod
2577 def and_(cls, *clauses):
2578 r"""Produce a conjunction of expressions joined by ``AND``.
2580 E.g.::
2582 from sqlalchemy import and_
2584 stmt = select(users_table).where(
2585 and_(
2586 users_table.c.name == 'wendy',
2587 users_table.c.enrolled == True
2588 )
2589 )
2591 The :func:`.and_` conjunction is also available using the
2592 Python ``&`` operator (though note that compound expressions
2593 need to be parenthesized in order to function with Python
2594 operator precedence behavior)::
2596 stmt = select(users_table).where(
2597 (users_table.c.name == 'wendy') &
2598 (users_table.c.enrolled == True)
2599 )
2601 The :func:`.and_` operation is also implicit in some cases;
2602 the :meth:`_expression.Select.where`
2603 method for example can be invoked multiple
2604 times against a statement, which will have the effect of each
2605 clause being combined using :func:`.and_`::
2607 stmt = select(users_table).\
2608 where(users_table.c.name == 'wendy').\
2609 where(users_table.c.enrolled == True)
2611 The :func:`.and_` construct must be given at least one positional
2612 argument in order to be valid; a :func:`.and_` construct with no
2613 arguments is ambiguous. To produce an "empty" or dynamically
2614 generated :func:`.and_` expression, from a given list of expressions,
2615 a "default" element of ``True`` should be specified::
2617 criteria = and_(True, *expressions)
2619 The above expression will compile to SQL as the expression ``true``
2620 or ``1 = 1``, depending on backend, if no other expressions are
2621 present. If expressions are present, then the ``True`` value is
2622 ignored as it does not affect the outcome of an AND expression that
2623 has other elements.
2625 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
2626 least one argument is passed; creating the :func:`.and_` construct
2627 with no arguments is deprecated, and will emit a deprecation warning
2628 while continuing to produce a blank SQL string.
2630 .. seealso::
2632 :func:`.or_`
2634 """
2635 return cls._construct(
2636 operators.and_, True_._singleton, False_._singleton, *clauses
2637 )
2639 @classmethod
2640 def or_(cls, *clauses):
2641 """Produce a conjunction of expressions joined by ``OR``.
2643 E.g.::
2645 from sqlalchemy import or_
2647 stmt = select(users_table).where(
2648 or_(
2649 users_table.c.name == 'wendy',
2650 users_table.c.name == 'jack'
2651 )
2652 )
2654 The :func:`.or_` conjunction is also available using the
2655 Python ``|`` operator (though note that compound expressions
2656 need to be parenthesized in order to function with Python
2657 operator precedence behavior)::
2659 stmt = select(users_table).where(
2660 (users_table.c.name == 'wendy') |
2661 (users_table.c.name == 'jack')
2662 )
2664 The :func:`.or_` construct must be given at least one positional
2665 argument in order to be valid; a :func:`.or_` construct with no
2666 arguments is ambiguous. To produce an "empty" or dynamically
2667 generated :func:`.or_` expression, from a given list of expressions,
2668 a "default" element of ``False`` should be specified::
2670 or_criteria = or_(False, *expressions)
2672 The above expression will compile to SQL as the expression ``false``
2673 or ``0 = 1``, depending on backend, if no other expressions are
2674 present. If expressions are present, then the ``False`` value is
2675 ignored as it does not affect the outcome of an OR expression which
2676 has other elements.
2678 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
2679 least one argument is passed; creating the :func:`.or_` construct
2680 with no arguments is deprecated, and will emit a deprecation warning
2681 while continuing to produce a blank SQL string.
2683 .. seealso::
2685 :func:`.and_`
2687 """
2688 return cls._construct(
2689 operators.or_, False_._singleton, True_._singleton, *clauses
2690 )
2692 @property
2693 def _select_iterable(self):
2694 return (self,)
2696 def self_group(self, against=None):
2697 if not self.clauses:
2698 return self
2699 else:
2700 return super(BooleanClauseList, self).self_group(against=against)
2702 def _negate(self):
2703 return ClauseList._negate(self)
2706and_ = BooleanClauseList.and_
2707or_ = BooleanClauseList.or_
2710class Tuple(ClauseList, ColumnElement):
2711 """Represent a SQL tuple."""
2713 __visit_name__ = "tuple"
2715 _traverse_internals = ClauseList._traverse_internals + []
2717 @util.preload_module("sqlalchemy.sql.sqltypes")
2718 def __init__(self, *clauses, **kw):
2719 """Return a :class:`.Tuple`.
2721 Main usage is to produce a composite IN construct using
2722 :meth:`.ColumnOperators.in_` ::
2724 from sqlalchemy import tuple_
2726 tuple_(table.c.col1, table.c.col2).in_(
2727 [(1, 2), (5, 12), (10, 19)]
2728 )
2730 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
2732 .. warning::
2734 The composite IN construct is not supported by all backends, and is
2735 currently known to work on PostgreSQL, MySQL, and SQLite.
2736 Unsupported backends will raise a subclass of
2737 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
2738 invoked.
2740 """
2741 sqltypes = util.preloaded.sql_sqltypes
2743 types = kw.pop("types", None)
2744 if types is None:
2745 clauses = [
2746 coercions.expect(roles.ExpressionElementRole, c)
2747 for c in clauses
2748 ]
2749 else:
2750 if len(types) != len(clauses):
2751 raise exc.ArgumentError(
2752 "Wrong number of elements for %d-tuple: %r "
2753 % (len(types), clauses)
2754 )
2755 clauses = [
2756 coercions.expect(
2757 roles.ExpressionElementRole,
2758 c,
2759 type_=typ if not typ._isnull else None,
2760 )
2761 for typ, c in zip(types, clauses)
2762 ]
2764 self.type = sqltypes.TupleType(*[arg.type for arg in clauses])
2765 super(Tuple, self).__init__(*clauses, **kw)
2767 @property
2768 def _select_iterable(self):
2769 return (self,)
2771 def _bind_param(self, operator, obj, type_=None, expanding=False):
2772 if expanding:
2773 return BindParameter(
2774 None,
2775 value=obj,
2776 _compared_to_operator=operator,
2777 unique=True,
2778 expanding=True,
2779 type_=self.type,
2780 )
2781 else:
2782 return Tuple(
2783 *[
2784 BindParameter(
2785 None,
2786 o,
2787 _compared_to_operator=operator,
2788 _compared_to_type=compared_to_type,
2789 unique=True,
2790 type_=type_,
2791 )
2792 for o, compared_to_type in zip(obj, self.type.types)
2793 ]
2794 )
2796 def self_group(self, against=None):
2797 # Tuple is parenthesized by definition.
2798 return self
2801class Case(ColumnElement):
2802 """Represent a ``CASE`` expression.
2804 :class:`.Case` is produced using the :func:`.case` factory function,
2805 as in::
2807 from sqlalchemy import case
2809 stmt = select(users_table).\
2810 where(
2811 case(
2812 (users_table.c.name == 'wendy', 'W'),
2813 (users_table.c.name == 'jack', 'J'),
2814 else_='E'
2815 )
2816 )
2818 Details on :class:`.Case` usage is at :func:`.case`.
2820 .. seealso::
2822 :func:`.case`
2824 """
2826 __visit_name__ = "case"
2828 _traverse_internals = [
2829 ("value", InternalTraversal.dp_clauseelement),
2830 ("whens", InternalTraversal.dp_clauseelement_tuples),
2831 ("else_", InternalTraversal.dp_clauseelement),
2832 ]
2834 # TODO: for Py2k removal, this will be:
2835 # def __init__(self, *whens, value=None, else_=None):
2837 def __init__(self, *whens, **kw):
2838 r"""Produce a ``CASE`` expression.
2840 The ``CASE`` construct in SQL is a conditional object that
2841 acts somewhat analogously to an "if/then" construct in other
2842 languages. It returns an instance of :class:`.Case`.
2844 :func:`.case` in its usual form is passed a series of "when"
2845 constructs, that is, a list of conditions and results as tuples::
2847 from sqlalchemy import case
2849 stmt = select(users_table).\
2850 where(
2851 case(
2852 (users_table.c.name == 'wendy', 'W'),
2853 (users_table.c.name == 'jack', 'J'),
2854 else_='E'
2855 )
2856 )
2858 The above statement will produce SQL resembling::
2860 SELECT id, name FROM user
2861 WHERE CASE
2862 WHEN (name = :name_1) THEN :param_1
2863 WHEN (name = :name_2) THEN :param_2
2864 ELSE :param_3
2865 END
2867 When simple equality expressions of several values against a single
2868 parent column are needed, :func:`.case` also has a "shorthand" format
2869 used via the
2870 :paramref:`.case.value` parameter, which is passed a column
2871 expression to be compared. In this form, the :paramref:`.case.whens`
2872 parameter is passed as a dictionary containing expressions to be
2873 compared against keyed to result expressions. The statement below is
2874 equivalent to the preceding statement::
2876 stmt = select(users_table).\
2877 where(
2878 case(
2879 {"wendy": "W", "jack": "J"},
2880 value=users_table.c.name,
2881 else_='E'
2882 )
2883 )
2885 The values which are accepted as result values in
2886 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
2887 coerced from Python literals into :func:`.bindparam` constructs.
2888 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
2889 are accepted
2890 as well. To coerce a literal string expression into a constant
2891 expression rendered inline, use the :func:`_expression.literal_column`
2892 construct,
2893 as in::
2895 from sqlalchemy import case, literal_column
2897 case(
2898 (
2899 orderline.c.qty > 100,
2900 literal_column("'greaterthan100'")
2901 ),
2902 (
2903 orderline.c.qty > 10,
2904 literal_column("'greaterthan10'")
2905 ),
2906 else_=literal_column("'lessthan10'")
2907 )
2909 The above will render the given constants without using bound
2910 parameters for the result values (but still for the comparison
2911 values), as in::
2913 CASE
2914 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
2915 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
2916 ELSE 'lessthan10'
2917 END
2919 :param \*whens: The criteria to be compared against,
2920 :paramref:`.case.whens` accepts two different forms, based on
2921 whether or not :paramref:`.case.value` is used.
2923 .. versionchanged:: 1.4 the :func:`_sql.case`
2924 function now accepts the series of WHEN conditions positionally;
2925 passing the expressions within a list is deprecated.
2927 In the first form, it accepts a list of 2-tuples; each 2-tuple
2928 consists of ``(<sql expression>, <value>)``, where the SQL
2929 expression is a boolean expression and "value" is a resulting value,
2930 e.g.::
2932 case(
2933 (users_table.c.name == 'wendy', 'W'),
2934 (users_table.c.name == 'jack', 'J')
2935 )
2937 In the second form, it accepts a Python dictionary of comparison
2938 values mapped to a resulting value; this form requires
2939 :paramref:`.case.value` to be present, and values will be compared
2940 using the ``==`` operator, e.g.::
2942 case(
2943 {"wendy": "W", "jack": "J"},
2944 value=users_table.c.name
2945 )
2947 :param value: An optional SQL expression which will be used as a
2948 fixed "comparison point" for candidate values within a dictionary
2949 passed to :paramref:`.case.whens`.
2951 :param else\_: An optional SQL expression which will be the evaluated
2952 result of the ``CASE`` construct if all expressions within
2953 :paramref:`.case.whens` evaluate to false. When omitted, most
2954 databases will produce a result of NULL if none of the "when"
2955 expressions evaluate to true.
2958 """
2960 if "whens" in kw:
2961 util.warn_deprecated_20(
2962 'The "whens" argument to case() is now passed using '
2963 "positional style only, not as a keyword argument."
2964 )
2965 whens = (kw.pop("whens"),)
2967 whens = coercions._expression_collection_was_a_list(
2968 "whens", "case", whens
2969 )
2971 try:
2972 whens = util.dictlike_iteritems(whens)
2973 except TypeError:
2974 pass
2976 value = kw.pop("value", None)
2978 whenlist = [
2979 (
2980 coercions.expect(
2981 roles.ExpressionElementRole,
2982 c,
2983 apply_propagate_attrs=self,
2984 ).self_group(),
2985 coercions.expect(roles.ExpressionElementRole, r),
2986 )
2987 for (c, r) in whens
2988 ]
2990 if whenlist:
2991 type_ = list(whenlist[-1])[-1].type
2992 else:
2993 type_ = None
2995 if value is None:
2996 self.value = None
2997 else:
2998 self.value = coercions.expect(roles.ExpressionElementRole, value)
3000 self.type = type_
3001 self.whens = whenlist
3003 else_ = kw.pop("else_", None)
3004 if else_ is not None:
3005 self.else_ = coercions.expect(roles.ExpressionElementRole, else_)
3006 else:
3007 self.else_ = None
3009 if kw:
3010 raise TypeError("unknown arguments: %s" % (", ".join(sorted(kw))))
3012 @property
3013 def _from_objects(self):
3014 return list(
3015 itertools.chain(*[x._from_objects for x in self.get_children()])
3016 )
3019def literal_column(text, type_=None):
3020 r"""Produce a :class:`.ColumnClause` object that has the
3021 :paramref:`_expression.column.is_literal` flag set to True.
3023 :func:`_expression.literal_column` is similar to
3024 :func:`_expression.column`, except that
3025 it is more often used as a "standalone" column expression that renders
3026 exactly as stated; while :func:`_expression.column`
3027 stores a string name that
3028 will be assumed to be part of a table and may be quoted as such,
3029 :func:`_expression.literal_column` can be that,
3030 or any other arbitrary column-oriented
3031 expression.
3033 :param text: the text of the expression; can be any SQL expression.
3034 Quoting rules will not be applied. To specify a column-name expression
3035 which should be subject to quoting rules, use the :func:`column`
3036 function.
3038 :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine`
3039 object which will
3040 provide result-set translation and additional expression semantics for
3041 this column. If left as ``None`` the type will be :class:`.NullType`.
3043 .. seealso::
3045 :func:`_expression.column`
3047 :func:`_expression.text`
3049 :ref:`sqlexpression_literal_column`
3051 """
3052 return ColumnClause(text, type_=type_, is_literal=True)
3055class Cast(WrapsColumnExpression, ColumnElement):
3056 """Represent a ``CAST`` expression.
3058 :class:`.Cast` is produced using the :func:`.cast` factory function,
3059 as in::
3061 from sqlalchemy import cast, Numeric
3063 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
3065 Details on :class:`.Cast` usage is at :func:`.cast`.
3067 .. seealso::
3069 :ref:`tutorial_casts`
3071 :func:`.cast`
3073 :func:`.type_coerce` - an alternative to CAST that coerces the type
3074 on the Python side only, which is often sufficient to generate the
3075 correct SQL and data coercion.
3077 """
3079 __visit_name__ = "cast"
3081 _traverse_internals = [
3082 ("clause", InternalTraversal.dp_clauseelement),
3083 ("type", InternalTraversal.dp_type),
3084 ]
3086 def __init__(self, expression, type_):
3087 r"""Produce a ``CAST`` expression.
3089 :func:`.cast` returns an instance of :class:`.Cast`.
3091 E.g.::
3093 from sqlalchemy import cast, Numeric
3095 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
3097 The above statement will produce SQL resembling::
3099 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
3101 The :func:`.cast` function performs two distinct functions when
3102 used. The first is that it renders the ``CAST`` expression within
3103 the resulting SQL string. The second is that it associates the given
3104 type (e.g. :class:`.TypeEngine` class or instance) with the column
3105 expression on the Python side, which means the expression will take
3106 on the expression operator behavior associated with that type,
3107 as well as the bound-value handling and result-row-handling behavior
3108 of the type.
3110 .. versionchanged:: 0.9.0 :func:`.cast` now applies the given type
3111 to the expression such that it takes effect on the bound-value,
3112 e.g. the Python-to-database direction, in addition to the
3113 result handling, e.g. database-to-Python, direction.
3115 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
3116 This function performs the second task of associating an expression
3117 with a specific type, but does not render the ``CAST`` expression
3118 in SQL.
3120 :param expression: A SQL expression, such as a
3121 :class:`_expression.ColumnElement`
3122 expression or a Python string which will be coerced into a bound
3123 literal value.
3125 :param type\_: A :class:`.TypeEngine` class or instance indicating
3126 the type to which the ``CAST`` should apply.
3128 .. seealso::
3130 :ref:`tutorial_casts`
3132 :func:`.type_coerce` - an alternative to CAST that coerces the type
3133 on the Python side only, which is often sufficient to generate the
3134 correct SQL and data coercion.
3137 """
3138 self.type = type_api.to_instance(type_)
3139 self.clause = coercions.expect(
3140 roles.ExpressionElementRole,
3141 expression,
3142 type_=self.type,
3143 apply_propagate_attrs=self,
3144 )
3145 self.typeclause = TypeClause(self.type)
3147 @property
3148 def _from_objects(self):
3149 return self.clause._from_objects
3151 @property
3152 def wrapped_column_expression(self):
3153 return self.clause
3156class TypeCoerce(WrapsColumnExpression, ColumnElement):
3157 """Represent a Python-side type-coercion wrapper.
3159 :class:`.TypeCoerce` supplies the :func:`_expression.type_coerce`
3160 function; see that function for usage details.
3162 .. versionchanged:: 1.1 The :func:`.type_coerce` function now produces
3163 a persistent :class:`.TypeCoerce` wrapper object rather than
3164 translating the given object in place.
3166 .. seealso::
3168 :func:`_expression.type_coerce`
3170 :func:`.cast`
3172 """
3174 __visit_name__ = "type_coerce"
3176 _traverse_internals = [
3177 ("clause", InternalTraversal.dp_clauseelement),
3178 ("type", InternalTraversal.dp_type),
3179 ]
3181 def __init__(self, expression, type_):
3182 r"""Associate a SQL expression with a particular type, without rendering
3183 ``CAST``.
3185 E.g.::
3187 from sqlalchemy import type_coerce
3189 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
3191 The above construct will produce a :class:`.TypeCoerce` object, which
3192 does not modify the rendering in any way on the SQL side, with the
3193 possible exception of a generated label if used in a columns clause
3194 context::
3196 SELECT date_string AS date_string FROM log
3198 When result rows are fetched, the ``StringDateTime`` type processor
3199 will be applied to result rows on behalf of the ``date_string`` column.
3201 .. note:: the :func:`.type_coerce` construct does not render any
3202 SQL syntax of its own, including that it does not imply
3203 parenthesization. Please use :meth:`.TypeCoerce.self_group`
3204 if explicit parenthesization is required.
3206 In order to provide a named label for the expression, use
3207 :meth:`_expression.ColumnElement.label`::
3209 stmt = select(
3210 type_coerce(log_table.date_string, StringDateTime()).label('date')
3211 )
3214 A type that features bound-value handling will also have that behavior
3215 take effect when literal values or :func:`.bindparam` constructs are
3216 passed to :func:`.type_coerce` as targets.
3217 For example, if a type implements the
3218 :meth:`.TypeEngine.bind_expression`
3219 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
3220 these functions will take effect at statement compilation/execution
3221 time when a literal value is passed, as in::
3223 # bound-value handling of MyStringType will be applied to the
3224 # literal value "some string"
3225 stmt = select(type_coerce("some string", MyStringType))
3227 When using :func:`.type_coerce` with composed expressions, note that
3228 **parenthesis are not applied**. If :func:`.type_coerce` is being
3229 used in an operator context where the parenthesis normally present from
3230 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method::
3232 >>> some_integer = column("someint", Integer)
3233 >>> some_string = column("somestr", String)
3234 >>> expr = type_coerce(some_integer + 5, String) + some_string
3235 >>> print(expr)
3236 someint + :someint_1 || somestr
3237 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
3238 >>> print(expr)
3239 (someint + :someint_1) || somestr
3241 :param expression: A SQL expression, such as a
3242 :class:`_expression.ColumnElement`
3243 expression or a Python string which will be coerced into a bound
3244 literal value.
3246 :param type\_: A :class:`.TypeEngine` class or instance indicating
3247 the type to which the expression is coerced.
3249 .. seealso::
3251 :ref:`tutorial_casts`
3253 :func:`.cast`
3255 """ # noqa
3256 self.type = type_api.to_instance(type_)
3257 self.clause = coercions.expect(
3258 roles.ExpressionElementRole,
3259 expression,
3260 type_=self.type,
3261 apply_propagate_attrs=self,
3262 )
3264 @property
3265 def _from_objects(self):
3266 return self.clause._from_objects
3268 @HasMemoized.memoized_attribute
3269 def typed_expression(self):
3270 if isinstance(self.clause, BindParameter):
3271 bp = self.clause._clone()
3272 bp.type = self.type
3273 return bp
3274 else:
3275 return self.clause
3277 @property
3278 def wrapped_column_expression(self):
3279 return self.clause
3281 def self_group(self, against=None):
3282 grouped = self.clause.self_group(against=against)
3283 if grouped is not self.clause:
3284 return TypeCoerce(grouped, self.type)
3285 else:
3286 return self
3289class Extract(ColumnElement):
3290 """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``."""
3292 __visit_name__ = "extract"
3294 _traverse_internals = [
3295 ("expr", InternalTraversal.dp_clauseelement),
3296 ("field", InternalTraversal.dp_string),
3297 ]
3299 def __init__(self, field, expr, **kwargs):
3300 """Return a :class:`.Extract` construct.
3302 This is typically available as :func:`.extract`
3303 as well as ``func.extract`` from the
3304 :data:`.func` namespace.
3306 :param field: The field to extract.
3308 :param expr: A column or Python scalar expression serving as the
3309 right side of the ``EXTRACT`` expression.
3311 E.g.::
3313 from sqlalchemy import extract
3314 from sqlalchemy import table, column
3316 logged_table = table("user",
3317 column("id"),
3318 column("date_created"),
3319 )
3321 stmt = select(logged_table.c.id).where(
3322 extract("YEAR", logged_table.c.date_created) == 2021
3323 )
3325 In the above example, the statement is used to select ids from the
3326 database where the ``YEAR`` component matches a specific value.
3328 Similarly, one can also select an extracted component::
3330 stmt = select(
3331 extract("YEAR", logged_table.c.date_created)
3332 ).where(logged_table.c.id == 1)
3334 The implementation of ``EXTRACT`` may vary across database backends.
3335 Users are reminded to consult their database documentation.
3336 """
3337 self.type = type_api.INTEGERTYPE
3338 self.field = field
3339 self.expr = coercions.expect(roles.ExpressionElementRole, expr)
3341 @property
3342 def _from_objects(self):
3343 return self.expr._from_objects
3346class _label_reference(ColumnElement):
3347 """Wrap a column expression as it appears in a 'reference' context.
3349 This expression is any that includes an _order_by_label_element,
3350 which is a Label, or a DESC / ASC construct wrapping a Label.
3352 The production of _label_reference() should occur when an expression
3353 is added to this context; this includes the ORDER BY or GROUP BY of a
3354 SELECT statement, as well as a few other places, such as the ORDER BY
3355 within an OVER clause.
3357 """
3359 __visit_name__ = "label_reference"
3361 _traverse_internals = [("element", InternalTraversal.dp_clauseelement)]
3363 def __init__(self, element):
3364 self.element = element
3366 @property
3367 def _from_objects(self):
3368 return ()
3371class _textual_label_reference(ColumnElement):
3372 __visit_name__ = "textual_label_reference"
3374 _traverse_internals = [("element", InternalTraversal.dp_string)]
3376 def __init__(self, element):
3377 self.element = element
3379 @util.memoized_property
3380 def _text_clause(self):
3381 return TextClause._create_text(self.element)
3384class UnaryExpression(ColumnElement):
3385 """Define a 'unary' expression.
3387 A unary expression has a single column expression
3388 and an operator. The operator can be placed on the left
3389 (where it is called the 'operator') or right (where it is called the
3390 'modifier') of the column expression.
3392 :class:`.UnaryExpression` is the basis for several unary operators
3393 including those used by :func:`.desc`, :func:`.asc`, :func:`.distinct`,
3394 :func:`.nulls_first` and :func:`.nulls_last`.
3396 """
3398 __visit_name__ = "unary"
3400 _traverse_internals = [
3401 ("element", InternalTraversal.dp_clauseelement),
3402 ("operator", InternalTraversal.dp_operator),
3403 ("modifier", InternalTraversal.dp_operator),
3404 ]
3406 def __init__(
3407 self,
3408 element,
3409 operator=None,
3410 modifier=None,
3411 type_=None,
3412 wraps_column_expression=False,
3413 ):
3414 self.operator = operator
3415 self.modifier = modifier
3416 self._propagate_attrs = element._propagate_attrs
3417 self.element = element.self_group(
3418 against=self.operator or self.modifier
3419 )
3420 self.type = type_api.to_instance(type_)
3421 self.wraps_column_expression = wraps_column_expression
3423 @classmethod
3424 def _create_nulls_first(cls, column):
3425 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
3427 :func:`.nulls_first` is intended to modify the expression produced
3428 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
3429 should be handled when they are encountered during ordering::
3432 from sqlalchemy import desc, nulls_first
3434 stmt = select(users_table).order_by(
3435 nulls_first(desc(users_table.c.name)))
3437 The SQL expression from the above would resemble::
3439 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
3441 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
3442 invoked from the column expression itself using
3443 :meth:`_expression.ColumnElement.nulls_first`,
3444 rather than as its standalone
3445 function version, as in::
3447 stmt = select(users_table).order_by(
3448 users_table.c.name.desc().nulls_first())
3450 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
3451 :func:`.nullsfirst` in previous releases.
3452 The previous name remains available for backwards compatibility.
3454 .. seealso::
3456 :func:`.asc`
3458 :func:`.desc`
3460 :func:`.nulls_last`
3462 :meth:`_expression.Select.order_by`
3464 """
3465 return UnaryExpression(
3466 coercions.expect(roles.ByOfRole, column),
3467 modifier=operators.nulls_first_op,
3468 wraps_column_expression=False,
3469 )
3471 @classmethod
3472 def _create_nulls_last(cls, column):
3473 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
3475 :func:`.nulls_last` is intended to modify the expression produced
3476 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
3477 should be handled when they are encountered during ordering::
3480 from sqlalchemy import desc, nulls_last
3482 stmt = select(users_table).order_by(
3483 nulls_last(desc(users_table.c.name)))
3485 The SQL expression from the above would resemble::
3487 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
3489 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
3490 invoked from the column expression itself using
3491 :meth:`_expression.ColumnElement.nulls_last`,
3492 rather than as its standalone
3493 function version, as in::
3495 stmt = select(users_table).order_by(
3496 users_table.c.name.desc().nulls_last())
3498 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
3499 :func:`.nullslast` in previous releases.
3500 The previous name remains available for backwards compatibility.
3502 .. seealso::
3504 :func:`.asc`
3506 :func:`.desc`
3508 :func:`.nulls_first`
3510 :meth:`_expression.Select.order_by`
3512 """
3513 return UnaryExpression(
3514 coercions.expect(roles.ByOfRole, column),
3515 modifier=operators.nulls_last_op,
3516 wraps_column_expression=False,
3517 )
3519 @classmethod
3520 def _create_desc(cls, column):
3521 """Produce a descending ``ORDER BY`` clause element.
3523 e.g.::
3525 from sqlalchemy import desc
3527 stmt = select(users_table).order_by(desc(users_table.c.name))
3529 will produce SQL as::
3531 SELECT id, name FROM user ORDER BY name DESC
3533 The :func:`.desc` function is a standalone version of the
3534 :meth:`_expression.ColumnElement.desc`
3535 method available on all SQL expressions,
3536 e.g.::
3539 stmt = select(users_table).order_by(users_table.c.name.desc())
3541 :param column: A :class:`_expression.ColumnElement` (e.g.
3542 scalar SQL expression)
3543 with which to apply the :func:`.desc` operation.
3545 .. seealso::
3547 :func:`.asc`
3549 :func:`.nulls_first`
3551 :func:`.nulls_last`
3553 :meth:`_expression.Select.order_by`
3555 """
3556 return UnaryExpression(
3557 coercions.expect(roles.ByOfRole, column),
3558 modifier=operators.desc_op,
3559 wraps_column_expression=False,
3560 )
3562 @classmethod
3563 def _create_asc(cls, column):
3564 """Produce an ascending ``ORDER BY`` clause element.
3566 e.g.::
3568 from sqlalchemy import asc
3569 stmt = select(users_table).order_by(asc(users_table.c.name))
3571 will produce SQL as::
3573 SELECT id, name FROM user ORDER BY name ASC
3575 The :func:`.asc` function is a standalone version of the
3576 :meth:`_expression.ColumnElement.asc`
3577 method available on all SQL expressions,
3578 e.g.::
3581 stmt = select(users_table).order_by(users_table.c.name.asc())
3583 :param column: A :class:`_expression.ColumnElement` (e.g.
3584 scalar SQL expression)
3585 with which to apply the :func:`.asc` operation.
3587 .. seealso::
3589 :func:`.desc`
3591 :func:`.nulls_first`
3593 :func:`.nulls_last`
3595 :meth:`_expression.Select.order_by`
3597 """
3598 return UnaryExpression(
3599 coercions.expect(roles.ByOfRole, column),
3600 modifier=operators.asc_op,
3601 wraps_column_expression=False,
3602 )
3604 @classmethod
3605 def _create_distinct(cls, expr):
3606 """Produce an column-expression-level unary ``DISTINCT`` clause.
3608 This applies the ``DISTINCT`` keyword to an individual column
3609 expression, and is typically contained within an aggregate function,
3610 as in::
3612 from sqlalchemy import distinct, func
3613 stmt = select(func.count(distinct(users_table.c.name)))
3615 The above would produce an expression resembling::
3617 SELECT COUNT(DISTINCT name) FROM user
3619 The :func:`.distinct` function is also available as a column-level
3620 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
3622 stmt = select(func.count(users_table.c.name.distinct()))
3624 The :func:`.distinct` operator is different from the
3625 :meth:`_expression.Select.distinct` method of
3626 :class:`_expression.Select`,
3627 which produces a ``SELECT`` statement
3628 with ``DISTINCT`` applied to the result set as a whole,
3629 e.g. a ``SELECT DISTINCT`` expression. See that method for further
3630 information.
3632 .. seealso::
3634 :meth:`_expression.ColumnElement.distinct`
3636 :meth:`_expression.Select.distinct`
3638 :data:`.func`
3640 """
3641 expr = coercions.expect(roles.ExpressionElementRole, expr)
3642 return UnaryExpression(
3643 expr,
3644 operator=operators.distinct_op,
3645 type_=expr.type,
3646 wraps_column_expression=False,
3647 )
3649 @property
3650 def _order_by_label_element(self):
3651 if self.modifier in (operators.desc_op, operators.asc_op):
3652 return self.element._order_by_label_element
3653 else:
3654 return None
3656 @property
3657 def _from_objects(self):
3658 return self.element._from_objects
3660 def _negate(self):
3661 if self.type._type_affinity is type_api.BOOLEANTYPE._type_affinity:
3662 return UnaryExpression(
3663 self.self_group(against=operators.inv),
3664 operator=operators.inv,
3665 type_=type_api.BOOLEANTYPE,
3666 wraps_column_expression=self.wraps_column_expression,
3667 )
3668 else:
3669 return ClauseElement._negate(self)
3671 def self_group(self, against=None):
3672 if self.operator and operators.is_precedent(self.operator, against):
3673 return Grouping(self)
3674 else:
3675 return self
3678class CollectionAggregate(UnaryExpression):
3679 """Forms the basis for right-hand collection operator modifiers
3680 ANY and ALL.
3682 The ANY and ALL keywords are available in different ways on different
3683 backends. On PostgreSQL, they only work for an ARRAY type. On
3684 MySQL, they only work for subqueries.
3686 """
3688 inherit_cache = True
3690 @classmethod
3691 def _create_any(cls, expr):
3692 """Produce an ANY expression.
3694 For dialects such as that of PostgreSQL, this operator applies
3695 to usage of the :class:`_types.ARRAY` datatype, for that of
3696 MySQL, it may apply to a subquery. e.g.::
3698 # renders on PostgreSQL:
3699 # '5 = ANY (somearray)'
3700 expr = 5 == any_(mytable.c.somearray)
3702 # renders on MySQL:
3703 # '5 = ANY (SELECT value FROM table)'
3704 expr = 5 == any_(select(table.c.value))
3706 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
3708 None == any_(mytable.c.somearray)
3710 The any_() / all_() operators also feature a special "operand flipping"
3711 behavior such that if any_() / all_() are used on the left side of a
3712 comparison using a standalone operator such as ``==``, ``!=``, etc.
3713 (not including operator methods such as
3714 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
3716 # would render '5 = ANY (column)`
3717 any_(mytable.c.column) == 5
3719 Or with ``None``, which note will not perform
3720 the usual step of rendering "IS" as is normally the case for NULL::
3722 # would render 'NULL = ANY(somearray)'
3723 any_(mytable.c.somearray) == None
3725 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
3726 comparing to NULL on the right side to be flipped to the left.
3728 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
3729 confused with :class:`_types.ARRAY` level
3730 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
3731 ``any_(col)``::
3733 5 = mytable.c.somearray.any_()
3735 .. seealso::
3737 :meth:`_sql.ColumnOperators.any_`
3739 :func:`_expression.all_`
3741 """
3743 expr = coercions.expect(roles.ExpressionElementRole, expr)
3745 expr = expr.self_group()
3746 return CollectionAggregate(
3747 expr,
3748 operator=operators.any_op,
3749 type_=type_api.NULLTYPE,
3750 wraps_column_expression=False,
3751 )
3753 @classmethod
3754 def _create_all(cls, expr):
3755 """Produce an ALL expression.
3757 For dialects such as that of PostgreSQL, this operator applies
3758 to usage of the :class:`_types.ARRAY` datatype, for that of
3759 MySQL, it may apply to a subquery. e.g.::
3761 # renders on PostgreSQL:
3762 # '5 = ALL (somearray)'
3763 expr = 5 == all_(mytable.c.somearray)
3765 # renders on MySQL:
3766 # '5 = ALL (SELECT value FROM table)'
3767 expr = 5 == all_(select(table.c.value))
3769 Comparison to NULL may work using ``None``::
3771 None == all_(mytable.c.somearray)
3773 The any_() / all_() operators also feature a special "operand flipping"
3774 behavior such that if any_() / all_() are used on the left side of a
3775 comparison using a standalone operator such as ``==``, ``!=``, etc.
3776 (not including operator methods such as
3777 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
3779 # would render '5 = ALL (column)`
3780 all_(mytable.c.column) == 5
3782 Or with ``None``, which note will not perform
3783 the usual step of rendering "IS" as is normally the case for NULL::
3785 # would render 'NULL = ALL(somearray)'
3786 all_(mytable.c.somearray) == None
3788 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
3789 comparing to NULL on the right side to be flipped to the left.
3791 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
3792 confused with :class:`_types.ARRAY` level
3793 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
3794 ``all_(col)``::
3796 5 == mytable.c.somearray.all_()
3798 .. seealso::
3800 :meth:`_sql.ColumnOperators.all_`
3802 :func:`_expression.any_`
3804 """
3805 expr = coercions.expect(roles.ExpressionElementRole, expr)
3806 expr = expr.self_group()
3807 return CollectionAggregate(
3808 expr,
3809 operator=operators.all_op,
3810 type_=type_api.NULLTYPE,
3811 wraps_column_expression=False,
3812 )
3814 # operate and reverse_operate are hardwired to
3815 # dispatch onto the type comparator directly, so that we can
3816 # ensure "reversed" behavior.
3817 def operate(self, op, *other, **kwargs):
3818 if not operators.is_comparison(op):
3819 raise exc.ArgumentError(
3820 "Only comparison operators may be used with ANY/ALL"
3821 )
3822 kwargs["reverse"] = kwargs["_any_all_expr"] = True
3823 return self.comparator.operate(operators.mirror(op), *other, **kwargs)
3825 def reverse_operate(self, op, other, **kwargs):
3826 # comparison operators should never call reverse_operate
3827 assert not operators.is_comparison(op)
3828 raise exc.ArgumentError(
3829 "Only comparison operators may be used with ANY/ALL"
3830 )
3833class AsBoolean(WrapsColumnExpression, UnaryExpression):
3834 inherit_cache = True
3836 def __init__(self, element, operator, negate):
3837 self.element = element
3838 self.type = type_api.BOOLEANTYPE
3839 self.operator = operator
3840 self.negate = negate
3841 self.modifier = None
3842 self.wraps_column_expression = True
3843 self._is_implicitly_boolean = element._is_implicitly_boolean
3845 @property
3846 def wrapped_column_expression(self):
3847 return self.element
3849 def self_group(self, against=None):
3850 return self
3852 def _negate(self):
3853 if isinstance(self.element, (True_, False_)):
3854 return self.element._negate()
3855 else:
3856 return AsBoolean(self.element, self.negate, self.operator)
3859class BinaryExpression(ColumnElement):
3860 """Represent an expression that is ``LEFT <operator> RIGHT``.
3862 A :class:`.BinaryExpression` is generated automatically
3863 whenever two column expressions are used in a Python binary expression::
3865 >>> from sqlalchemy.sql import column
3866 >>> column('a') + column('b')
3867 <sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
3868 >>> print(column('a') + column('b'))
3869 a + b
3871 """
3873 __visit_name__ = "binary"
3875 _traverse_internals = [
3876 ("left", InternalTraversal.dp_clauseelement),
3877 ("right", InternalTraversal.dp_clauseelement),
3878 ("operator", InternalTraversal.dp_operator),
3879 ("negate", InternalTraversal.dp_operator),
3880 ("modifiers", InternalTraversal.dp_plain_dict),
3881 (
3882 "type",
3883 InternalTraversal.dp_type,
3884 ),
3885 ]
3887 _cache_key_traversal = [
3888 ("left", InternalTraversal.dp_clauseelement),
3889 ("right", InternalTraversal.dp_clauseelement),
3890 ("operator", InternalTraversal.dp_operator),
3891 ("modifiers", InternalTraversal.dp_plain_dict),
3892 # "type" affects JSON CAST operators, so while redundant in most cases,
3893 # is needed for that one
3894 (
3895 "type",
3896 InternalTraversal.dp_type,
3897 ),
3898 ]
3900 _is_implicitly_boolean = True
3901 """Indicates that any database will know this is a boolean expression
3902 even if the database does not have an explicit boolean datatype.
3904 """
3906 def __init__(
3907 self, left, right, operator, type_=None, negate=None, modifiers=None
3908 ):
3909 # allow compatibility with libraries that
3910 # refer to BinaryExpression directly and pass strings
3911 if isinstance(operator, util.string_types):
3912 operator = operators.custom_op(operator)
3913 self._orig = (left.__hash__(), right.__hash__())
3914 self._propagate_attrs = left._propagate_attrs or right._propagate_attrs
3915 self.left = left.self_group(against=operator)
3916 self.right = right.self_group(against=operator)
3917 self.operator = operator
3918 self.type = type_api.to_instance(type_)
3919 self.negate = negate
3920 self._is_implicitly_boolean = operators.is_boolean(operator)
3922 if modifiers is None:
3923 self.modifiers = {}
3924 else:
3925 self.modifiers = modifiers
3927 def __bool__(self):
3928 if self.operator in (operator.eq, operator.ne):
3929 return self.operator(*self._orig)
3930 else:
3931 raise TypeError("Boolean value of this clause is not defined")
3933 __nonzero__ = __bool__
3935 @property
3936 def is_comparison(self):
3937 return operators.is_comparison(self.operator)
3939 @property
3940 def _from_objects(self):
3941 return self.left._from_objects + self.right._from_objects
3943 def self_group(self, against=None):
3945 if operators.is_precedent(self.operator, against):
3946 return Grouping(self)
3947 else:
3948 return self
3950 def _negate(self):
3951 if self.negate is not None:
3952 return BinaryExpression(
3953 self.left,
3954 self.right._negate_in_binary(self.negate, self.operator),
3955 self.negate,
3956 negate=self.operator,
3957 type_=self.type,
3958 modifiers=self.modifiers,
3959 )
3960 else:
3961 return super(BinaryExpression, self)._negate()
3964class Slice(ColumnElement):
3965 """Represent SQL for a Python array-slice object.
3967 This is not a specific SQL construct at this level, but
3968 may be interpreted by specific dialects, e.g. PostgreSQL.
3970 """
3972 __visit_name__ = "slice"
3974 _traverse_internals = [
3975 ("start", InternalTraversal.dp_clauseelement),
3976 ("stop", InternalTraversal.dp_clauseelement),
3977 ("step", InternalTraversal.dp_clauseelement),
3978 ]
3980 def __init__(self, start, stop, step, _name=None):
3981 self.start = coercions.expect(
3982 roles.ExpressionElementRole,
3983 start,
3984 name=_name,
3985 type_=type_api.INTEGERTYPE,
3986 )
3987 self.stop = coercions.expect(
3988 roles.ExpressionElementRole,
3989 stop,
3990 name=_name,
3991 type_=type_api.INTEGERTYPE,
3992 )
3993 self.step = coercions.expect(
3994 roles.ExpressionElementRole,
3995 step,
3996 name=_name,
3997 type_=type_api.INTEGERTYPE,
3998 )
3999 self.type = type_api.NULLTYPE
4001 def self_group(self, against=None):
4002 assert against is operator.getitem
4003 return self
4006class IndexExpression(BinaryExpression):
4007 """Represent the class of expressions that are like an "index"
4008 operation."""
4010 inherit_cache = True
4013class GroupedElement(ClauseElement):
4014 """Represent any parenthesized expression"""
4016 __visit_name__ = "grouping"
4018 def self_group(self, against=None):
4019 return self
4021 def _ungroup(self):
4022 return self.element._ungroup()
4025class Grouping(GroupedElement, ColumnElement):
4026 """Represent a grouping within a column expression"""
4028 _traverse_internals = [
4029 ("element", InternalTraversal.dp_clauseelement),
4030 ("type", InternalTraversal.dp_type),
4031 ]
4033 _cache_key_traversal = [
4034 ("element", InternalTraversal.dp_clauseelement),
4035 ]
4037 def __init__(self, element):
4038 self.element = element
4039 self.type = getattr(element, "type", type_api.NULLTYPE)
4041 def _with_binary_element_type(self, type_):
4042 return self.__class__(self.element._with_binary_element_type(type_))
4044 @util.memoized_property
4045 def _is_implicitly_boolean(self):
4046 return self.element._is_implicitly_boolean
4048 @property
4049 def _tq_label(self):
4050 return (
4051 getattr(self.element, "_tq_label", None) or self._anon_name_label
4052 )
4054 @property
4055 def _proxies(self):
4056 if isinstance(self.element, ColumnElement):
4057 return [self.element]
4058 else:
4059 return []
4061 @property
4062 def _from_objects(self):
4063 return self.element._from_objects
4065 def __getattr__(self, attr):
4066 return getattr(self.element, attr)
4068 def __getstate__(self):
4069 return {"element": self.element, "type": self.type}
4071 def __setstate__(self, state):
4072 self.element = state["element"]
4073 self.type = state["type"]
4076RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED")
4077RANGE_CURRENT = util.symbol("RANGE_CURRENT")
4080class Over(ColumnElement):
4081 """Represent an OVER clause.
4083 This is a special operator against a so-called
4084 "window" function, as well as any aggregate function,
4085 which produces results relative to the result set
4086 itself. Most modern SQL backends now support window functions.
4088 """
4090 __visit_name__ = "over"
4092 _traverse_internals = [
4093 ("element", InternalTraversal.dp_clauseelement),
4094 ("order_by", InternalTraversal.dp_clauseelement),
4095 ("partition_by", InternalTraversal.dp_clauseelement),
4096 ("range_", InternalTraversal.dp_plain_obj),
4097 ("rows", InternalTraversal.dp_plain_obj),
4098 ]
4100 order_by = None
4101 partition_by = None
4103 element = None
4104 """The underlying expression object to which this :class:`.Over`
4105 object refers towards."""
4107 def __init__(
4108 self, element, partition_by=None, order_by=None, range_=None, rows=None
4109 ):
4110 r"""Produce an :class:`.Over` object against a function.
4112 Used against aggregate or so-called "window" functions,
4113 for database backends that support window functions.
4115 :func:`_expression.over` is usually called using
4116 the :meth:`.FunctionElement.over` method, e.g.::
4118 func.row_number().over(order_by=mytable.c.some_column)
4120 Would produce::
4122 ROW_NUMBER() OVER(ORDER BY some_column)
4124 Ranges are also possible using the :paramref:`.expression.over.range_`
4125 and :paramref:`.expression.over.rows` parameters. These
4126 mutually-exclusive parameters each accept a 2-tuple, which contains
4127 a combination of integers and None::
4129 func.row_number().over(
4130 order_by=my_table.c.some_column, range_=(None, 0))
4132 The above would produce::
4134 ROW_NUMBER() OVER(ORDER BY some_column
4135 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
4137 A value of ``None`` indicates "unbounded", a
4138 value of zero indicates "current row", and negative / positive
4139 integers indicate "preceding" and "following":
4141 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
4143 func.row_number().over(order_by='x', range_=(-5, 10))
4145 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
4147 func.row_number().over(order_by='x', rows=(None, 0))
4149 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
4151 func.row_number().over(order_by='x', range_=(-2, None))
4153 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
4155 func.row_number().over(order_by='x', range_=(1, 3))
4157 .. versionadded:: 1.1 support for RANGE / ROWS within a window
4160 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
4161 or other compatible construct.
4162 :param partition_by: a column element or string, or a list
4163 of such, that will be used as the PARTITION BY clause
4164 of the OVER construct.
4165 :param order_by: a column element or string, or a list
4166 of such, that will be used as the ORDER BY clause
4167 of the OVER construct.
4168 :param range\_: optional range clause for the window. This is a
4169 tuple value which can contain integer values or ``None``,
4170 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
4172 .. versionadded:: 1.1
4174 :param rows: optional rows clause for the window. This is a tuple
4175 value which can contain integer values or None, and will render
4176 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
4178 .. versionadded:: 1.1
4180 This function is also available from the :data:`~.expression.func`
4181 construct itself via the :meth:`.FunctionElement.over` method.
4183 .. seealso::
4185 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
4187 :data:`.expression.func`
4189 :func:`_expression.within_group`
4191 """
4192 self.element = element
4193 if order_by is not None:
4194 self.order_by = ClauseList(
4195 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole
4196 )
4197 if partition_by is not None:
4198 self.partition_by = ClauseList(
4199 *util.to_list(partition_by),
4200 _literal_as_text_role=roles.ByOfRole
4201 )
4203 if range_:
4204 self.range_ = self._interpret_range(range_)
4205 if rows:
4206 raise exc.ArgumentError(
4207 "'range_' and 'rows' are mutually exclusive"
4208 )
4209 else:
4210 self.rows = None
4211 elif rows:
4212 self.rows = self._interpret_range(rows)
4213 self.range_ = None
4214 else:
4215 self.rows = self.range_ = None
4217 def __reduce__(self):
4218 return self.__class__, (
4219 self.element,
4220 self.partition_by,
4221 self.order_by,
4222 self.range_,
4223 self.rows,
4224 )
4226 def _interpret_range(self, range_):
4227 if not isinstance(range_, tuple) or len(range_) != 2:
4228 raise exc.ArgumentError("2-tuple expected for range/rows")
4230 if range_[0] is None:
4231 lower = RANGE_UNBOUNDED
4232 else:
4233 try:
4234 lower = int(range_[0])
4235 except ValueError as err:
4236 util.raise_(
4237 exc.ArgumentError(
4238 "Integer or None expected for range value"
4239 ),
4240 replace_context=err,
4241 )
4242 else:
4243 if lower == 0:
4244 lower = RANGE_CURRENT
4246 if range_[1] is None:
4247 upper = RANGE_UNBOUNDED
4248 else:
4249 try:
4250 upper = int(range_[1])
4251 except ValueError as err:
4252 util.raise_(
4253 exc.ArgumentError(
4254 "Integer or None expected for range value"
4255 ),
4256 replace_context=err,
4257 )
4258 else:
4259 if upper == 0:
4260 upper = RANGE_CURRENT
4262 return lower, upper
4264 @util.memoized_property
4265 def type(self):
4266 return self.element.type
4268 @property
4269 def _from_objects(self):
4270 return list(
4271 itertools.chain(
4272 *[
4273 c._from_objects
4274 for c in (self.element, self.partition_by, self.order_by)
4275 if c is not None
4276 ]
4277 )
4278 )
4281class WithinGroup(ColumnElement):
4282 """Represent a WITHIN GROUP (ORDER BY) clause.
4284 This is a special operator against so-called
4285 "ordered set aggregate" and "hypothetical
4286 set aggregate" functions, including ``percentile_cont()``,
4287 ``rank()``, ``dense_rank()``, etc.
4289 It's supported only by certain database backends, such as PostgreSQL,
4290 Oracle and MS SQL Server.
4292 The :class:`.WithinGroup` construct extracts its type from the
4293 method :meth:`.FunctionElement.within_group_type`. If this returns
4294 ``None``, the function's ``.type`` is used.
4296 """
4298 __visit_name__ = "withingroup"
4300 _traverse_internals = [
4301 ("element", InternalTraversal.dp_clauseelement),
4302 ("order_by", InternalTraversal.dp_clauseelement),
4303 ]
4305 order_by = None
4307 def __init__(self, element, *order_by):
4308 r"""Produce a :class:`.WithinGroup` object against a function.
4310 Used against so-called "ordered set aggregate" and "hypothetical
4311 set aggregate" functions, including :class:`.percentile_cont`,
4312 :class:`.rank`, :class:`.dense_rank`, etc.
4314 :func:`_expression.within_group` is usually called using
4315 the :meth:`.FunctionElement.within_group` method, e.g.::
4317 from sqlalchemy import within_group
4318 stmt = select(
4319 department.c.id,
4320 func.percentile_cont(0.5).within_group(
4321 department.c.salary.desc()
4322 )
4323 )
4325 The above statement would produce SQL similar to
4326 ``SELECT department.id, percentile_cont(0.5)
4327 WITHIN GROUP (ORDER BY department.salary DESC)``.
4329 :param element: a :class:`.FunctionElement` construct, typically
4330 generated by :data:`~.expression.func`.
4331 :param \*order_by: one or more column elements that will be used
4332 as the ORDER BY clause of the WITHIN GROUP construct.
4334 .. versionadded:: 1.1
4336 .. seealso::
4338 :ref:`tutorial_functions_within_group` - in the
4339 :ref:`unified_tutorial`
4341 :data:`.expression.func`
4343 :func:`_expression.over`
4345 """
4346 self.element = element
4347 if order_by is not None:
4348 self.order_by = ClauseList(
4349 *util.to_list(order_by), _literal_as_text_role=roles.ByOfRole
4350 )
4352 def __reduce__(self):
4353 return self.__class__, (self.element,) + tuple(self.order_by)
4355 def over(self, partition_by=None, order_by=None, range_=None, rows=None):
4356 """Produce an OVER clause against this :class:`.WithinGroup`
4357 construct.
4359 This function has the same signature as that of
4360 :meth:`.FunctionElement.over`.
4362 """
4363 return Over(
4364 self,
4365 partition_by=partition_by,
4366 order_by=order_by,
4367 range_=range_,
4368 rows=rows,
4369 )
4371 @util.memoized_property
4372 def type(self):
4373 wgt = self.element.within_group_type(self)
4374 if wgt is not None:
4375 return wgt
4376 else:
4377 return self.element.type
4379 @property
4380 def _from_objects(self):
4381 return list(
4382 itertools.chain(
4383 *[
4384 c._from_objects
4385 for c in (self.element, self.order_by)
4386 if c is not None
4387 ]
4388 )
4389 )
4392class FunctionFilter(ColumnElement):
4393 """Represent a function FILTER clause.
4395 This is a special operator against aggregate and window functions,
4396 which controls which rows are passed to it.
4397 It's supported only by certain database backends.
4399 Invocation of :class:`.FunctionFilter` is via
4400 :meth:`.FunctionElement.filter`::
4402 func.count(1).filter(True)
4404 .. versionadded:: 1.0.0
4406 .. seealso::
4408 :meth:`.FunctionElement.filter`
4410 """
4412 __visit_name__ = "funcfilter"
4414 _traverse_internals = [
4415 ("func", InternalTraversal.dp_clauseelement),
4416 ("criterion", InternalTraversal.dp_clauseelement),
4417 ]
4419 criterion = None
4421 def __init__(self, func, *criterion):
4422 """Produce a :class:`.FunctionFilter` object against a function.
4424 Used against aggregate and window functions,
4425 for database backends that support the "FILTER" clause.
4427 E.g.::
4429 from sqlalchemy import funcfilter
4430 funcfilter(func.count(1), MyClass.name == 'some name')
4432 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
4434 This function is also available from the :data:`~.expression.func`
4435 construct itself via the :meth:`.FunctionElement.filter` method.
4437 .. versionadded:: 1.0.0
4439 .. seealso::
4441 :ref:`tutorial_functions_within_group` - in the
4442 :ref:`unified_tutorial`
4444 :meth:`.FunctionElement.filter`
4446 """
4447 self.func = func
4448 self.filter(*criterion)
4450 def filter(self, *criterion):
4451 """Produce an additional FILTER against the function.
4453 This method adds additional criteria to the initial criteria
4454 set up by :meth:`.FunctionElement.filter`.
4456 Multiple criteria are joined together at SQL render time
4457 via ``AND``.
4460 """
4462 for criterion in list(criterion):
4463 criterion = coercions.expect(roles.WhereHavingRole, criterion)
4465 if self.criterion is not None:
4466 self.criterion = self.criterion & criterion
4467 else:
4468 self.criterion = criterion
4470 return self
4472 def over(self, partition_by=None, order_by=None, range_=None, rows=None):
4473 """Produce an OVER clause against this filtered function.
4475 Used against aggregate or so-called "window" functions,
4476 for database backends that support window functions.
4478 The expression::
4480 func.rank().filter(MyClass.y > 5).over(order_by='x')
4482 is shorthand for::
4484 from sqlalchemy import over, funcfilter
4485 over(funcfilter(func.rank(), MyClass.y > 5), order_by='x')
4487 See :func:`_expression.over` for a full description.
4489 """
4490 return Over(
4491 self,
4492 partition_by=partition_by,
4493 order_by=order_by,
4494 range_=range_,
4495 rows=rows,
4496 )
4498 def self_group(self, against=None):
4499 if operators.is_precedent(operators.filter_op, against):
4500 return Grouping(self)
4501 else:
4502 return self
4504 @util.memoized_property
4505 def type(self):
4506 return self.func.type
4508 @property
4509 def _from_objects(self):
4510 return list(
4511 itertools.chain(
4512 *[
4513 c._from_objects
4514 for c in (self.func, self.criterion)
4515 if c is not None
4516 ]
4517 )
4518 )
4521class Label(roles.LabeledColumnExprRole, ColumnElement):
4522 """Represents a column label (AS).
4524 Represent a label, as typically applied to any column-level
4525 element using the ``AS`` sql keyword.
4527 """
4529 __visit_name__ = "label"
4531 _traverse_internals = [
4532 ("name", InternalTraversal.dp_anon_name),
4533 ("_type", InternalTraversal.dp_type),
4534 ("_element", InternalTraversal.dp_clauseelement),
4535 ]
4537 _cache_key_traversal = [
4538 ("name", InternalTraversal.dp_anon_name),
4539 ("_element", InternalTraversal.dp_clauseelement),
4540 ]
4542 def __init__(self, name, element, type_=None):
4543 """Return a :class:`Label` object for the
4544 given :class:`_expression.ColumnElement`.
4546 A label changes the name of an element in the columns clause of a
4547 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
4549 This functionality is more conveniently available via the
4550 :meth:`_expression.ColumnElement.label` method on
4551 :class:`_expression.ColumnElement`.
4553 :param name: label name
4555 :param obj: a :class:`_expression.ColumnElement`.
4557 """
4559 orig_element = element
4560 element = coercions.expect(
4561 roles.ExpressionElementRole,
4562 element,
4563 apply_propagate_attrs=self,
4564 )
4565 while isinstance(element, Label):
4566 # TODO: this is only covered in test_text.py, but nothing
4567 # fails if it's removed. determine rationale
4568 element = element.element
4570 if name:
4571 self.name = name
4572 else:
4573 self.name = _anonymous_label.safe_construct(
4574 id(self), getattr(element, "name", "anon")
4575 )
4576 if isinstance(orig_element, Label):
4577 # TODO: no coverage for this block, again would be in
4578 # test_text.py where the resolve_label concept is important
4579 self._resolve_label = orig_element._label
4581 self.key = self._tq_label = self._tq_key_label = self.name
4582 self._element = element
4583 self._type = type_
4584 self._proxies = [element]
4586 def __reduce__(self):
4587 return self.__class__, (self.name, self._element, self._type)
4589 @util.memoized_property
4590 def _is_implicitly_boolean(self):
4591 return self.element._is_implicitly_boolean
4593 @HasMemoized.memoized_attribute
4594 def _allow_label_resolve(self):
4595 return self.element._allow_label_resolve
4597 @property
4598 def _order_by_label_element(self):
4599 return self
4601 @util.memoized_property
4602 def type(self):
4603 return type_api.to_instance(
4604 self._type or getattr(self._element, "type", None)
4605 )
4607 @HasMemoized.memoized_attribute
4608 def element(self):
4609 return self._element.self_group(against=operators.as_)
4611 def self_group(self, against=None):
4612 return self._apply_to_inner(self._element.self_group, against=against)
4614 def _negate(self):
4615 return self._apply_to_inner(self._element._negate)
4617 def _apply_to_inner(self, fn, *arg, **kw):
4618 sub_element = fn(*arg, **kw)
4619 if sub_element is not self._element:
4620 return Label(self.name, sub_element, type_=self._type)
4621 else:
4622 return self
4624 @property
4625 def primary_key(self):
4626 return self.element.primary_key
4628 @property
4629 def foreign_keys(self):
4630 return self.element.foreign_keys
4632 def _copy_internals(self, clone=_clone, anonymize_labels=False, **kw):
4633 self._reset_memoizations()
4634 self._element = clone(self._element, **kw)
4635 if anonymize_labels:
4636 self.name = _anonymous_label.safe_construct(
4637 id(self), getattr(self.element, "name", "anon")
4638 )
4639 self.key = self._tq_label = self._tq_key_label = self.name
4641 @property
4642 def _from_objects(self):
4643 return self.element._from_objects
4645 def _make_proxy(self, selectable, name=None, **kw):
4646 name = self.name if not name else name
4648 key, e = self.element._make_proxy(
4649 selectable,
4650 name=name,
4651 disallow_is_literal=True,
4652 name_is_truncatable=isinstance(name, _truncated_label),
4653 )
4655 # there was a note here to remove this assertion, which was here
4656 # to determine if we later could support a use case where
4657 # the key and name of a label are separate. But I don't know what
4658 # that case was. For now, this is an unexpected case that occurs
4659 # when a label name conflicts with other columns and select()
4660 # is attempting to disambiguate an explicit label, which is not what
4661 # the user would want. See issue #6090.
4662 if key != self.name and not isinstance(self.name, _anonymous_label):
4663 raise exc.InvalidRequestError(
4664 "Label name %s is being renamed to an anonymous label due "
4665 "to disambiguation "
4666 "which is not supported right now. Please use unique names "
4667 "for explicit labels." % (self.name)
4668 )
4670 e._propagate_attrs = selectable._propagate_attrs
4671 e._proxies.append(self)
4672 if self._type is not None:
4673 e.type = self._type
4675 return self.key, e
4678class NamedColumn(ColumnElement):
4679 is_literal = False
4680 table = None
4682 def _compare_name_for_result(self, other):
4683 return (hasattr(other, "name") and self.name == other.name) or (
4684 hasattr(other, "_label") and self._label == other._label
4685 )
4687 @util.memoized_property
4688 def description(self):
4689 if util.py3k:
4690 return self.name
4691 else:
4692 return self.name.encode("ascii", "backslashreplace")
4694 @HasMemoized.memoized_attribute
4695 def _tq_key_label(self):
4696 """table qualified label based on column key.
4698 for table-bound columns this is <tablename>_<column key/proxy key>;
4700 all other expressions it resolves to key/proxy key.
4702 """
4703 proxy_key = self._proxy_key
4704 if proxy_key and proxy_key != self.name:
4705 return self._gen_tq_label(proxy_key)
4706 else:
4707 return self._tq_label
4709 @HasMemoized.memoized_attribute
4710 def _tq_label(self):
4711 """table qualified label based on column name.
4713 for table-bound columns this is <tablename>_<columnname>; all other
4714 expressions it resolves to .name.
4716 """
4717 return self._gen_tq_label(self.name)
4719 @HasMemoized.memoized_attribute
4720 def _render_label_in_columns_clause(self):
4721 return True
4723 @HasMemoized.memoized_attribute
4724 def _non_anon_label(self):
4725 return self.name
4727 def _gen_tq_label(self, name, dedupe_on_key=True):
4728 return name
4730 def _bind_param(self, operator, obj, type_=None, expanding=False):
4731 return BindParameter(
4732 self.key,
4733 obj,
4734 _compared_to_operator=operator,
4735 _compared_to_type=self.type,
4736 type_=type_,
4737 unique=True,
4738 expanding=expanding,
4739 )
4741 def _make_proxy(
4742 self,
4743 selectable,
4744 name=None,
4745 name_is_truncatable=False,
4746 disallow_is_literal=False,
4747 **kw
4748 ):
4749 c = ColumnClause(
4750 coercions.expect(roles.TruncatedLabelRole, name or self.name)
4751 if name_is_truncatable
4752 else (name or self.name),
4753 type_=self.type,
4754 _selectable=selectable,
4755 is_literal=False,
4756 )
4757 c._propagate_attrs = selectable._propagate_attrs
4758 if name is None:
4759 c.key = self.key
4760 c._proxies = [self]
4761 if selectable._is_clone_of is not None:
4762 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
4763 return c.key, c
4766class ColumnClause(
4767 roles.DDLReferredColumnRole,
4768 roles.LabeledColumnExprRole,
4769 roles.StrAsPlainColumnRole,
4770 Immutable,
4771 NamedColumn,
4772):
4773 """Represents a column expression from any textual string.
4775 The :class:`.ColumnClause`, a lightweight analogue to the
4776 :class:`_schema.Column` class, is typically invoked using the
4777 :func:`_expression.column` function, as in::
4779 from sqlalchemy import column
4781 id, name = column("id"), column("name")
4782 stmt = select(id, name).select_from("user")
4784 The above statement would produce SQL like::
4786 SELECT id, name FROM user
4788 :class:`.ColumnClause` is the immediate superclass of the schema-specific
4789 :class:`_schema.Column` object. While the :class:`_schema.Column`
4790 class has all the
4791 same capabilities as :class:`.ColumnClause`, the :class:`.ColumnClause`
4792 class is usable by itself in those cases where behavioral requirements
4793 are limited to simple SQL expression generation. The object has none of
4794 the associations with schema-level metadata or with execution-time
4795 behavior that :class:`_schema.Column` does,
4796 so in that sense is a "lightweight"
4797 version of :class:`_schema.Column`.
4799 Full details on :class:`.ColumnClause` usage is at
4800 :func:`_expression.column`.
4802 .. seealso::
4804 :func:`_expression.column`
4806 :class:`_schema.Column`
4808 """
4810 table = None
4811 is_literal = False
4813 __visit_name__ = "column"
4815 _traverse_internals = [
4816 ("name", InternalTraversal.dp_anon_name),
4817 ("type", InternalTraversal.dp_type),
4818 ("table", InternalTraversal.dp_clauseelement),
4819 ("is_literal", InternalTraversal.dp_boolean),
4820 ]
4822 onupdate = default = server_default = server_onupdate = None
4824 _is_multiparam_column = False
4826 @property
4827 def _is_star(self):
4828 return self.is_literal and self.name == "*"
4830 def __init__(self, text, type_=None, is_literal=False, _selectable=None):
4831 """Produce a :class:`.ColumnClause` object.
4833 The :class:`.ColumnClause` is a lightweight analogue to the
4834 :class:`_schema.Column` class. The :func:`_expression.column`
4835 function can
4836 be invoked with just a name alone, as in::
4838 from sqlalchemy import column
4840 id, name = column("id"), column("name")
4841 stmt = select(id, name).select_from("user")
4843 The above statement would produce SQL like::
4845 SELECT id, name FROM user
4847 Once constructed, :func:`_expression.column`
4848 may be used like any other SQL
4849 expression element such as within :func:`_expression.select`
4850 constructs::
4852 from sqlalchemy.sql import column
4854 id, name = column("id"), column("name")
4855 stmt = select(id, name).select_from("user")
4857 The text handled by :func:`_expression.column`
4858 is assumed to be handled
4859 like the name of a database column; if the string contains mixed case,
4860 special characters, or matches a known reserved word on the target
4861 backend, the column expression will render using the quoting
4862 behavior determined by the backend. To produce a textual SQL
4863 expression that is rendered exactly without any quoting,
4864 use :func:`_expression.literal_column` instead,
4865 or pass ``True`` as the
4866 value of :paramref:`_expression.column.is_literal`. Additionally,
4867 full SQL
4868 statements are best handled using the :func:`_expression.text`
4869 construct.
4871 :func:`_expression.column` can be used in a table-like
4872 fashion by combining it with the :func:`.table` function
4873 (which is the lightweight analogue to :class:`_schema.Table`
4874 ) to produce
4875 a working table construct with minimal boilerplate::
4877 from sqlalchemy import table, column, select
4879 user = table("user",
4880 column("id"),
4881 column("name"),
4882 column("description"),
4883 )
4885 stmt = select(user.c.description).where(user.c.name == 'wendy')
4887 A :func:`_expression.column` / :func:`.table`
4888 construct like that illustrated
4889 above can be created in an
4890 ad-hoc fashion and is not associated with any
4891 :class:`_schema.MetaData`, DDL, or events, unlike its
4892 :class:`_schema.Table` counterpart.
4894 .. versionchanged:: 1.0.0 :func:`_expression.column` can now
4895 be imported from the plain ``sqlalchemy`` namespace like any
4896 other SQL element.
4898 :param text: the text of the element.
4900 :param type: :class:`_types.TypeEngine` object which can associate
4901 this :class:`.ColumnClause` with a type.
4903 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
4904 be an exact expression that will be delivered to the output with no
4905 quoting rules applied regardless of case sensitive settings. the
4906 :func:`_expression.literal_column()` function essentially invokes
4907 :func:`_expression.column` while passing ``is_literal=True``.
4909 .. seealso::
4911 :class:`_schema.Column`
4913 :func:`_expression.literal_column`
4915 :func:`.table`
4917 :func:`_expression.text`
4919 :ref:`tutorial_select_arbitrary_text`
4921 """
4922 self.key = self.name = text
4923 self.table = _selectable
4924 self.type = type_api.to_instance(type_)
4925 self.is_literal = is_literal
4927 def get_children(self, column_tables=False, **kw):
4928 # override base get_children() to not return the Table
4929 # or selectable that is parent to this column. Traversals
4930 # expect the columns of tables and subqueries to be leaf nodes.
4931 return []
4933 @property
4934 def entity_namespace(self):
4935 if self.table is not None:
4936 return self.table.entity_namespace
4937 else:
4938 return super(ColumnClause, self).entity_namespace
4940 def _clone(self, detect_subquery_cols=False, **kw):
4941 if (
4942 detect_subquery_cols
4943 and self.table is not None
4944 and self.table._is_subquery
4945 ):
4946 clone = kw.pop("clone")
4947 table = clone(self.table, **kw)
4948 new = table.c.corresponding_column(self)
4949 return new
4951 return super(ColumnClause, self)._clone(**kw)
4953 @HasMemoized.memoized_attribute
4954 def _from_objects(self):
4955 t = self.table
4956 if t is not None:
4957 return [t]
4958 else:
4959 return []
4961 @HasMemoized.memoized_attribute
4962 def _render_label_in_columns_clause(self):
4963 return self.table is not None
4965 @property
4966 def _ddl_label(self):
4967 return self._gen_tq_label(self.name, dedupe_on_key=False)
4969 def _compare_name_for_result(self, other):
4970 if (
4971 self.is_literal
4972 or self.table is None
4973 or self.table._is_textual
4974 or not hasattr(other, "proxy_set")
4975 or (
4976 isinstance(other, ColumnClause)
4977 and (
4978 other.is_literal
4979 or other.table is None
4980 or other.table._is_textual
4981 )
4982 )
4983 ):
4984 return (hasattr(other, "name") and self.name == other.name) or (
4985 hasattr(other, "_tq_label")
4986 and self._tq_label == other._tq_label
4987 )
4988 else:
4989 return other.proxy_set.intersection(self.proxy_set)
4991 def _gen_tq_label(self, name, dedupe_on_key=True):
4992 """generate table-qualified label
4994 for a table-bound column this is <tablename>_<columnname>.
4996 used primarily for LABEL_STYLE_TABLENAME_PLUS_COL
4997 as well as the .columns collection on a Join object.
4999 """
5000 t = self.table
5001 if self.is_literal:
5002 return None
5003 elif t is not None and t.named_with_column:
5004 if getattr(t, "schema", None):
5005 label = t.schema.replace(".", "_") + "_" + t.name + "_" + name
5006 else:
5007 label = t.name + "_" + name
5009 # propagate name quoting rules for labels.
5010 if getattr(name, "quote", None) is not None:
5011 if isinstance(label, quoted_name):
5012 label.quote = name.quote
5013 else:
5014 label = quoted_name(label, name.quote)
5015 elif getattr(t.name, "quote", None) is not None:
5016 # can't get this situation to occur, so let's
5017 # assert false on it for now
5018 assert not isinstance(label, quoted_name)
5019 label = quoted_name(label, t.name.quote)
5021 if dedupe_on_key:
5022 # ensure the label name doesn't conflict with that of an
5023 # existing column. note that this implies that any Column
5024 # must **not** set up its _label before its parent table has
5025 # all of its other Column objects set up. There are several
5026 # tables in the test suite which will fail otherwise; example:
5027 # table "owner" has columns "name" and "owner_name". Therefore
5028 # column owner.name cannot use the label "owner_name", it has
5029 # to be "owner_name_1".
5030 if label in t.c:
5031 _label = label
5032 counter = 1
5033 while _label in t.c:
5034 _label = label + "_" + str(counter)
5035 counter += 1
5036 label = _label
5038 return coercions.expect(roles.TruncatedLabelRole, label)
5040 else:
5041 return name
5043 def _make_proxy(
5044 self,
5045 selectable,
5046 name=None,
5047 name_is_truncatable=False,
5048 disallow_is_literal=False,
5049 **kw
5050 ):
5051 # the "is_literal" flag normally should never be propagated; a proxied
5052 # column is always a SQL identifier and never the actual expression
5053 # being evaluated. however, there is a case where the "is_literal" flag
5054 # might be used to allow the given identifier to have a fixed quoting
5055 # pattern already, so maintain the flag for the proxy unless a
5056 # :class:`.Label` object is creating the proxy. See [ticket:4730].
5057 is_literal = (
5058 not disallow_is_literal
5059 and self.is_literal
5060 and (
5061 # note this does not accommodate for quoted_name differences
5062 # right now
5063 name is None
5064 or name == self.name
5065 )
5066 )
5067 c = self._constructor(
5068 coercions.expect(roles.TruncatedLabelRole, name or self.name)
5069 if name_is_truncatable
5070 else (name or self.name),
5071 type_=self.type,
5072 _selectable=selectable,
5073 is_literal=is_literal,
5074 )
5075 c._propagate_attrs = selectable._propagate_attrs
5076 if name is None:
5077 c.key = self.key
5078 c._proxies = [self]
5079 if selectable._is_clone_of is not None:
5080 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
5081 return c.key, c
5084class TableValuedColumn(NamedColumn):
5085 __visit_name__ = "table_valued_column"
5087 _traverse_internals = [
5088 ("name", InternalTraversal.dp_anon_name),
5089 ("type", InternalTraversal.dp_type),
5090 ("scalar_alias", InternalTraversal.dp_clauseelement),
5091 ]
5093 def __init__(self, scalar_alias, type_):
5094 self.scalar_alias = scalar_alias
5095 self.key = self.name = scalar_alias.name
5096 self.type = type_
5098 def _copy_internals(self, clone=_clone, **kw):
5099 self.scalar_alias = clone(self.scalar_alias, **kw)
5100 self.key = self.name = self.scalar_alias.name
5102 @property
5103 def _from_objects(self):
5104 return [self.scalar_alias]
5107class CollationClause(ColumnElement):
5108 __visit_name__ = "collation"
5110 _traverse_internals = [("collation", InternalTraversal.dp_string)]
5112 def __init__(self, collation):
5113 self.collation = collation
5116class _IdentifiedClause(Executable, ClauseElement):
5118 __visit_name__ = "identified"
5119 _execution_options = Executable._execution_options.union(
5120 {"autocommit": False}
5121 )
5123 def __init__(self, ident):
5124 self.ident = ident
5127class SavepointClause(_IdentifiedClause):
5128 __visit_name__ = "savepoint"
5129 inherit_cache = False
5132class RollbackToSavepointClause(_IdentifiedClause):
5133 __visit_name__ = "rollback_to_savepoint"
5134 inherit_cache = False
5137class ReleaseSavepointClause(_IdentifiedClause):
5138 __visit_name__ = "release_savepoint"
5139 inherit_cache = False
5142class quoted_name(util.MemoizedSlots, util.text_type):
5143 """Represent a SQL identifier combined with quoting preferences.
5145 :class:`.quoted_name` is a Python unicode/str subclass which
5146 represents a particular identifier name along with a
5147 ``quote`` flag. This ``quote`` flag, when set to
5148 ``True`` or ``False``, overrides automatic quoting behavior
5149 for this identifier in order to either unconditionally quote
5150 or to not quote the name. If left at its default of ``None``,
5151 quoting behavior is applied to the identifier on a per-backend basis
5152 based on an examination of the token itself.
5154 A :class:`.quoted_name` object with ``quote=True`` is also
5155 prevented from being modified in the case of a so-called
5156 "name normalize" option. Certain database backends, such as
5157 Oracle, Firebird, and DB2 "normalize" case-insensitive names
5158 as uppercase. The SQLAlchemy dialects for these backends
5159 convert from SQLAlchemy's lower-case-means-insensitive convention
5160 to the upper-case-means-insensitive conventions of those backends.
5161 The ``quote=True`` flag here will prevent this conversion from occurring
5162 to support an identifier that's quoted as all lower case against
5163 such a backend.
5165 The :class:`.quoted_name` object is normally created automatically
5166 when specifying the name for key schema constructs such as
5167 :class:`_schema.Table`, :class:`_schema.Column`, and others.
5168 The class can also be
5169 passed explicitly as the name to any function that receives a name which
5170 can be quoted. Such as to use the :meth:`_engine.Engine.has_table`
5171 method with
5172 an unconditionally quoted name::
5174 from sqlalchemy import create_engine
5175 from sqlalchemy import inspect
5176 from sqlalchemy.sql import quoted_name
5178 engine = create_engine("oracle+cx_oracle://some_dsn")
5179 print(inspect(engine).has_table(quoted_name("some_table", True)))
5181 The above logic will run the "has table" logic against the Oracle backend,
5182 passing the name exactly as ``"some_table"`` without converting to
5183 upper case.
5185 .. versionadded:: 0.9.0
5187 .. versionchanged:: 1.2 The :class:`.quoted_name` construct is now
5188 importable from ``sqlalchemy.sql``, in addition to the previous
5189 location of ``sqlalchemy.sql.elements``.
5191 """
5193 __slots__ = "quote", "lower", "upper"
5195 def __new__(cls, value, quote):
5196 if value is None:
5197 return None
5198 # experimental - don't bother with quoted_name
5199 # if quote flag is None. doesn't seem to make any dent
5200 # in performance however
5201 # elif not sprcls and quote is None:
5202 # return value
5203 elif isinstance(value, cls) and (
5204 quote is None or value.quote == quote
5205 ):
5206 return value
5207 self = super(quoted_name, cls).__new__(cls, value)
5209 self.quote = quote
5210 return self
5212 def __reduce__(self):
5213 return quoted_name, (util.text_type(self), self.quote)
5215 def _memoized_method_lower(self):
5216 if self.quote:
5217 return self
5218 else:
5219 return util.text_type(self).lower()
5221 def _memoized_method_upper(self):
5222 if self.quote:
5223 return self
5224 else:
5225 return util.text_type(self).upper()
5227 def __repr__(self):
5228 if util.py2k:
5229 backslashed = self.encode("ascii", "backslashreplace")
5230 if not util.py2k:
5231 backslashed = backslashed.decode("ascii")
5232 return "'%s'" % backslashed
5233 else:
5234 return str.__repr__(self)
5237def _find_columns(clause):
5238 """locate Column objects within the given expression."""
5240 cols = util.column_set()
5241 traverse(clause, {}, {"column": cols.add})
5242 return cols
5245def _type_from_args(args):
5246 for a in args:
5247 if not a.type._isnull:
5248 return a.type
5249 else:
5250 return type_api.NULLTYPE
5253def _corresponding_column_or_error(fromclause, column, require_embedded=False):
5254 c = fromclause.corresponding_column(
5255 column, require_embedded=require_embedded
5256 )
5257 if c is None:
5258 raise exc.InvalidRequestError(
5259 "Given column '%s', attached to table '%s', "
5260 "failed to locate a corresponding column from table '%s'"
5261 % (column, getattr(column, "table", None), fromclause.description)
5262 )
5263 return c
5266class AnnotatedColumnElement(Annotated):
5267 def __init__(self, element, values):
5268 Annotated.__init__(self, element, values)
5269 for attr in (
5270 "comparator",
5271 "_proxy_key",
5272 "_tq_key_label",
5273 "_tq_label",
5274 "_non_anon_label",
5275 ):
5276 self.__dict__.pop(attr, None)
5277 for attr in ("name", "key", "table"):
5278 if self.__dict__.get(attr, False) is None:
5279 self.__dict__.pop(attr)
5281 def _with_annotations(self, values):
5282 clone = super(AnnotatedColumnElement, self)._with_annotations(values)
5283 clone.__dict__.pop("comparator", None)
5284 return clone
5286 @util.memoized_property
5287 def name(self):
5288 """pull 'name' from parent, if not present"""
5289 return self._Annotated__element.name
5291 @util.memoized_property
5292 def table(self):
5293 """pull 'table' from parent, if not present"""
5294 return self._Annotated__element.table
5296 @util.memoized_property
5297 def key(self):
5298 """pull 'key' from parent, if not present"""
5299 return self._Annotated__element.key
5301 @util.memoized_property
5302 def info(self):
5303 return self._Annotated__element.info
5305 @util.memoized_property
5306 def _anon_name_label(self):
5307 return self._Annotated__element._anon_name_label
5310class _truncated_label(quoted_name):
5311 """A unicode subclass used to identify symbolic "
5312 "names that may require truncation."""
5314 __slots__ = ()
5316 def __new__(cls, value, quote=None):
5317 quote = getattr(value, "quote", quote)
5318 # return super(_truncated_label, cls).__new__(cls, value, quote, True)
5319 return super(_truncated_label, cls).__new__(cls, value, quote)
5321 def __reduce__(self):
5322 return self.__class__, (util.text_type(self), self.quote)
5324 def apply_map(self, map_):
5325 return self
5328class conv(_truncated_label):
5329 """Mark a string indicating that a name has already been converted
5330 by a naming convention.
5332 This is a string subclass that indicates a name that should not be
5333 subject to any further naming conventions.
5335 E.g. when we create a :class:`.Constraint` using a naming convention
5336 as follows::
5338 m = MetaData(naming_convention={
5339 "ck": "ck_%(table_name)s_%(constraint_name)s"
5340 })
5341 t = Table('t', m, Column('x', Integer),
5342 CheckConstraint('x > 5', name='x5'))
5344 The name of the above constraint will be rendered as ``"ck_t_x5"``.
5345 That is, the existing name ``x5`` is used in the naming convention as the
5346 ``constraint_name`` token.
5348 In some situations, such as in migration scripts, we may be rendering
5349 the above :class:`.CheckConstraint` with a name that's already been
5350 converted. In order to make sure the name isn't double-modified, the
5351 new name is applied using the :func:`_schema.conv` marker. We can
5352 use this explicitly as follows::
5355 m = MetaData(naming_convention={
5356 "ck": "ck_%(table_name)s_%(constraint_name)s"
5357 })
5358 t = Table('t', m, Column('x', Integer),
5359 CheckConstraint('x > 5', name=conv('ck_t_x5')))
5361 Where above, the :func:`_schema.conv` marker indicates that the constraint
5362 name here is final, and the name will render as ``"ck_t_x5"`` and not
5363 ``"ck_t_ck_t_x5"``
5365 .. versionadded:: 0.9.4
5367 .. seealso::
5369 :ref:`constraint_naming_conventions`
5371 """
5373 __slots__ = ()
5376_NONE_NAME = util.symbol("NONE_NAME")
5377"""indicate a 'deferred' name that was ultimately the value None."""
5379# for backwards compatibility in case
5380# someone is re-implementing the
5381# _truncated_identifier() sequence in a custom
5382# compiler
5383_generated_label = _truncated_label
5386class _anonymous_label(_truncated_label):
5387 """A unicode subclass used to identify anonymously
5388 generated names."""
5390 __slots__ = ()
5392 @classmethod
5393 def safe_construct(
5394 cls, seed, body, enclosing_label=None, sanitize_key=False
5395 ):
5397 # need to escape chars that interfere with format
5398 # strings in any case, issue #8724
5399 body = re.sub(r"[%\(\) \$]+", "_", body)
5401 if sanitize_key:
5402 # sanitize_key is then an extra step used by BindParameter
5403 body = body.strip("_")
5405 label = "%%(%d %s)s" % (seed, body.replace("%", "%%"))
5406 if enclosing_label:
5407 label = "%s%s" % (enclosing_label, label)
5409 return _anonymous_label(label)
5411 def __add__(self, other):
5412 if "%" in other and not isinstance(other, _anonymous_label):
5413 other = util.text_type(other).replace("%", "%%")
5414 else:
5415 other = util.text_type(other)
5417 return _anonymous_label(
5418 quoted_name(
5419 util.text_type.__add__(self, other),
5420 self.quote,
5421 )
5422 )
5424 def __radd__(self, other):
5425 if "%" in other and not isinstance(other, _anonymous_label):
5426 other = util.text_type(other).replace("%", "%%")
5427 else:
5428 other = util.text_type(other)
5430 return _anonymous_label(
5431 quoted_name(
5432 util.text_type.__add__(other, self),
5433 self.quote,
5434 )
5435 )
5437 def apply_map(self, map_):
5438 if self.quote is not None:
5439 # preserve quoting only if necessary
5440 return quoted_name(self % map_, self.quote)
5441 else:
5442 # else skip the constructor call
5443 return self % map_