# sql/elements.py # Copyright (C) 2005-2024 the SQLAlchemy authors and contributors # # # This module is part of SQLAlchemy and is released under # the MIT License: https://www.opensource.org/licenses/mit-license.php # mypy: allow-untyped-defs, allow-untyped-calls """Core SQL expression elements, including :class:`_expression.ClauseElement`, :class:`_expression.ColumnElement`, and derived classes. """ from __future__ import annotations from decimal import Decimal from enum import Enum import itertools import operator import re import typing from typing import AbstractSet from typing import Any from typing import Callable from typing import cast from typing import Dict from typing import FrozenSet from typing import Generic from typing import Iterable from typing import Iterator from typing import List from typing import Mapping from typing import Optional from typing import overload from typing import Sequence from typing import Set from typing import Tuple as typing_Tuple from typing import Type from typing import TYPE_CHECKING from typing import TypeVar from typing import Union from . import coercions from . import operators from . import roles from . import traversals from . import type_api from ._typing import has_schema_attr from ._typing import is_named_from_clause from ._typing import is_quoted_name from ._typing import is_tuple_type from .annotation import Annotated from .annotation import SupportsWrappingAnnotations from .base import _clone from .base import _expand_cloned from .base import _generative from .base import _NoArg from .base import Executable from .base import Generative from .base import HasMemoized from .base import Immutable from .base import NO_ARG from .base import SingletonConstant from .cache_key import MemoizedHasCacheKey from .cache_key import NO_CACHE from .coercions import _document_text_coercion # noqa from .operators import ColumnOperators from .traversals import HasCopyInternals from .visitors import cloned_traverse from .visitors import ExternallyTraversible from .visitors import InternalTraversal from .visitors import traverse from .visitors import Visitable from .. import exc from .. import inspection from .. import util from ..util import HasMemoized_ro_memoized_attribute from ..util import TypingOnly from ..util.typing import Literal from ..util.typing import ParamSpec from ..util.typing import Self if typing.TYPE_CHECKING: from ._typing import _ByArgument from ._typing import _ColumnExpressionArgument from ._typing import _ColumnExpressionOrStrLabelArgument from ._typing import _HasDialect from ._typing import _InfoType from ._typing import _PropagateAttrsType from ._typing import _TypeEngineArgument from .cache_key import _CacheKeyTraversalType from .cache_key import CacheKey from .compiler import Compiled from .compiler import SQLCompiler from .functions import FunctionElement from .operators import OperatorType from .schema import Column from .schema import DefaultGenerator from .schema import FetchedValue from .schema import ForeignKey from .selectable import _SelectIterable from .selectable import FromClause from .selectable import NamedFromClause from .selectable import TextualSelect from .sqltypes import TupleType from .type_api import TypeEngine from .visitors import _CloneCallableType from .visitors import _TraverseInternalsType from .visitors import anon_map from ..engine import Connection from ..engine import Dialect from ..engine.interfaces import _CoreMultiExecuteParams from ..engine.interfaces import CacheStats from ..engine.interfaces import CompiledCacheType from ..engine.interfaces import CoreExecuteOptionsParameter from ..engine.interfaces import SchemaTranslateMapType from ..engine.result import Result _NUMERIC = Union[float, Decimal] _NUMBER = Union[float, int, Decimal] _T = TypeVar("_T", bound="Any") _T_co = TypeVar("_T_co", bound=Any, covariant=True) _OPT = TypeVar("_OPT", bound="Any") _NT = TypeVar("_NT", bound="_NUMERIC") _NMT = TypeVar("_NMT", bound="_NUMBER") @overload def literal( value: Any, type_: _TypeEngineArgument[_T], literal_execute: bool = False, ) -> BindParameter[_T]: ... @overload def literal( value: _T, type_: None = None, literal_execute: bool = False, ) -> BindParameter[_T]: ... @overload def literal( value: Any, type_: Optional[_TypeEngineArgument[Any]] = None, literal_execute: bool = False, ) -> BindParameter[Any]: ... def literal( value: Any, type_: Optional[_TypeEngineArgument[Any]] = None, literal_execute: bool = False, ) -> BindParameter[Any]: r"""Return a literal clause, bound to a bind parameter. Literal clauses are created automatically when non- :class:`_expression.ClauseElement` objects (such as strings, ints, dates, etc.) are used in a comparison operation with a :class:`_expression.ColumnElement` subclass, such as a :class:`~sqlalchemy.schema.Column` object. Use this function to force the generation of a literal clause, which will be created as a :class:`BindParameter` with a bound value. :param value: the value to be bound. Can be any Python object supported by the underlying DB-API, or is translatable via the given type argument. :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` which will provide bind-parameter translation for this literal. :param literal_execute: optional bool, when True, the SQL engine will attempt to render the bound value directly in the SQL statement at execution time rather than providing as a parameter value. .. versionadded:: 2.0 """ return coercions.expect( roles.LiteralValueRole, value, type_=type_, literal_execute=literal_execute, ) def literal_column( text: str, type_: Optional[_TypeEngineArgument[_T]] = None ) -> ColumnClause[_T]: r"""Produce a :class:`.ColumnClause` object that has the :paramref:`_expression.column.is_literal` flag set to True. :func:`_expression.literal_column` is similar to :func:`_expression.column`, except that it is more often used as a "standalone" column expression that renders exactly as stated; while :func:`_expression.column` stores a string name that will be assumed to be part of a table and may be quoted as such, :func:`_expression.literal_column` can be that, or any other arbitrary column-oriented expression. :param text: the text of the expression; can be any SQL expression. Quoting rules will not be applied. To specify a column-name expression which should be subject to quoting rules, use the :func:`column` function. :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` object which will provide result-set translation and additional expression semantics for this column. If left as ``None`` the type will be :class:`.NullType`. .. seealso:: :func:`_expression.column` :func:`_expression.text` :ref:`tutorial_select_arbitrary_text` """ return ColumnClause(text, type_=type_, is_literal=True) class CompilerElement(Visitable): """base class for SQL elements that can be compiled to produce a SQL string. .. versionadded:: 2.0 """ __slots__ = () __visit_name__ = "compiler_element" supports_execution = False stringify_dialect = "default" @util.preload_module("sqlalchemy.engine.default") @util.preload_module("sqlalchemy.engine.url") def compile( self, bind: Optional[_HasDialect] = None, dialect: Optional[Dialect] = None, **kw: Any, ) -> Compiled: """Compile this SQL expression. The return value is a :class:`~.Compiled` object. Calling ``str()`` or ``unicode()`` on the returned value will yield a string representation of the result. The :class:`~.Compiled` object also can return a dictionary of bind parameter names and values using the ``params`` accessor. :param bind: An :class:`.Connection` or :class:`.Engine` which can provide a :class:`.Dialect` in order to generate a :class:`.Compiled` object. If the ``bind`` and ``dialect`` parameters are both omitted, a default SQL compiler is used. :param column_keys: Used for INSERT and UPDATE statements, a list of column names which should be present in the VALUES clause of the compiled statement. If ``None``, all columns from the target table object are rendered. :param dialect: A :class:`.Dialect` instance which can generate a :class:`.Compiled` object. This argument takes precedence over the ``bind`` argument. :param compile_kwargs: optional dictionary of additional parameters that will be passed through to the compiler within all "visit" methods. This allows any custom flag to be passed through to a custom compilation construct, for example. It is also used for the case of passing the ``literal_binds`` flag through:: from sqlalchemy.sql import table, column, select t = table('t', column('x')) s = select(t).where(t.c.x == 5) print(s.compile(compile_kwargs={"literal_binds": True})) .. seealso:: :ref:`faq_sql_expression_string` """ if dialect is None: if bind: dialect = bind.dialect elif self.stringify_dialect == "default": default = util.preloaded.engine_default dialect = default.StrCompileDialect() else: url = util.preloaded.engine_url dialect = url.URL.create( self.stringify_dialect ).get_dialect()() return self._compiler(dialect, **kw) def _compiler(self, dialect: Dialect, **kw: Any) -> Compiled: """Return a compiler appropriate for this ClauseElement, given a Dialect.""" if TYPE_CHECKING: assert isinstance(self, ClauseElement) return dialect.statement_compiler(dialect, self, **kw) def __str__(self) -> str: return str(self.compile()) @inspection._self_inspects class ClauseElement( SupportsWrappingAnnotations, MemoizedHasCacheKey, HasCopyInternals, ExternallyTraversible, CompilerElement, ): """Base class for elements of a programmatically constructed SQL expression. """ __visit_name__ = "clause" if TYPE_CHECKING: @util.memoized_property def _propagate_attrs(self) -> _PropagateAttrsType: """like annotations, however these propagate outwards liberally as SQL constructs are built, and are set up at construction time. """ ... else: _propagate_attrs = util.EMPTY_DICT @util.ro_memoized_property def description(self) -> Optional[str]: return None _is_clone_of: Optional[Self] = None is_clause_element = True is_selectable = False is_dml = False _is_column_element = False _is_keyed_column_element = False _is_table = False _gen_static_annotations_cache_key = False _is_textual = False _is_from_clause = False _is_returns_rows = False _is_text_clause = False _is_from_container = False _is_select_container = False _is_select_base = False _is_select_statement = False _is_bind_parameter = False _is_clause_list = False _is_lambda_element = False _is_singleton_constant = False _is_immutable = False _is_star = False @property def _order_by_label_element(self) -> Optional[Label[Any]]: return None _cache_key_traversal: _CacheKeyTraversalType = None negation_clause: ColumnElement[bool] if typing.TYPE_CHECKING: def get_children( self, *, omit_attrs: typing_Tuple[str, ...] = ..., **kw: Any ) -> Iterable[ClauseElement]: ... @util.ro_non_memoized_property def _from_objects(self) -> List[FromClause]: return [] def _set_propagate_attrs(self, values: Mapping[str, Any]) -> Self: # usually, self._propagate_attrs is empty here. one case where it's # not is a subquery against ORM select, that is then pulled as a # property of an aliased class. should all be good # assert not self._propagate_attrs self._propagate_attrs = util.immutabledict(values) return self def _clone(self, **kw: Any) -> Self: """Create a shallow copy of this ClauseElement. This method may be used by a generative API. Its also used as part of the "deep" copy afforded by a traversal that combines the _copy_internals() method. """ skip = self._memoized_keys c = self.__class__.__new__(self.__class__) if skip: # ensure this iteration remains atomic c.__dict__ = { k: v for k, v in self.__dict__.copy().items() if k not in skip } else: c.__dict__ = self.__dict__.copy() # this is a marker that helps to "equate" clauses to each other # when a Select returns its list of FROM clauses. the cloning # process leaves around a lot of remnants of the previous clause # typically in the form of column expressions still attached to the # old table. cc = self._is_clone_of c._is_clone_of = cc if cc is not None else self return c def _negate_in_binary(self, negated_op, original_op): """a hook to allow the right side of a binary expression to respond to a negation of the binary expression. Used for the special case of expanding bind parameter with IN. """ return self def _with_binary_element_type(self, type_): """in the context of binary expression, convert the type of this object to the one given. applies only to :class:`_expression.ColumnElement` classes. """ return self @property def _constructor(self): """return the 'constructor' for this ClauseElement. This is for the purposes for creating a new object of this type. Usually, its just the element's __class__. However, the "Annotated" version of the object overrides to return the class of its proxied element. """ return self.__class__ @HasMemoized.memoized_attribute def _cloned_set(self): """Return the set consisting all cloned ancestors of this ClauseElement. Includes this ClauseElement. This accessor tends to be used for FromClause objects to identify 'equivalent' FROM clauses, regardless of transformative operations. """ s = util.column_set() f: Optional[ClauseElement] = self # note this creates a cycle, asserted in test_memusage. however, # turning this into a plain @property adds tends of thousands of method # calls to Core / ORM performance tests, so the small overhead # introduced by the relatively small amount of short term cycles # produced here is preferable while f is not None: s.add(f) f = f._is_clone_of return s def _de_clone(self): while self._is_clone_of is not None: self = self._is_clone_of return self @property def entity_namespace(self): raise AttributeError( "This SQL expression has no entity namespace " "with which to filter from." ) def __getstate__(self): d = self.__dict__.copy() d.pop("_is_clone_of", None) d.pop("_generate_cache_key", None) return d def _execute_on_connection( self, connection: Connection, distilled_params: _CoreMultiExecuteParams, execution_options: CoreExecuteOptionsParameter, ) -> Result[Any]: if self.supports_execution: if TYPE_CHECKING: assert isinstance(self, Executable) return connection._execute_clauseelement( self, distilled_params, execution_options ) else: raise exc.ObjectNotExecutableError(self) def _execute_on_scalar( self, connection: Connection, distilled_params: _CoreMultiExecuteParams, execution_options: CoreExecuteOptionsParameter, ) -> Any: """an additional hook for subclasses to provide a different implementation for connection.scalar() vs. connection.execute(). .. versionadded:: 2.0 """ return self._execute_on_connection( connection, distilled_params, execution_options ).scalar() def _get_embedded_bindparams(self) -> Sequence[BindParameter[Any]]: """Return the list of :class:`.BindParameter` objects embedded in the object. This accomplishes the same purpose as ``visitors.traverse()`` or similar would provide, however by making use of the cache key it takes advantage of memoization of the key to result in fewer net method calls, assuming the statement is also going to be executed. """ key = self._generate_cache_key() if key is None: bindparams: List[BindParameter[Any]] = [] traverse(self, {}, {"bindparam": bindparams.append}) return bindparams else: return key.bindparams def unique_params( self, __optionaldict: Optional[Dict[str, Any]] = None, **kwargs: Any, ) -> Self: """Return a copy with :func:`_expression.bindparam` elements replaced. Same functionality as :meth:`_expression.ClauseElement.params`, except adds `unique=True` to affected bind parameters so that multiple statements can be used. """ return self._replace_params(True, __optionaldict, kwargs) def params( self, __optionaldict: Optional[Mapping[str, Any]] = None, **kwargs: Any, ) -> Self: """Return a copy with :func:`_expression.bindparam` elements replaced. Returns a copy of this ClauseElement with :func:`_expression.bindparam` elements replaced with values taken from the given dictionary:: >>> clause = column('x') + bindparam('foo') >>> print(clause.compile().params) {'foo':None} >>> print(clause.params({'foo':7}).compile().params) {'foo':7} """ return self._replace_params(False, __optionaldict, kwargs) def _replace_params( self, unique: bool, optionaldict: Optional[Mapping[str, Any]], kwargs: Dict[str, Any], ) -> Self: if optionaldict: kwargs.update(optionaldict) def visit_bindparam(bind: BindParameter[Any]) -> None: if bind.key in kwargs: bind.value = kwargs[bind.key] bind.required = False if unique: bind._convert_to_unique() return cloned_traverse( self, {"maintain_key": True, "detect_subquery_cols": True}, {"bindparam": visit_bindparam}, ) def compare(self, other: ClauseElement, **kw: Any) -> bool: r"""Compare this :class:`_expression.ClauseElement` to the given :class:`_expression.ClauseElement`. Subclasses should override the default behavior, which is a straight identity comparison. \**kw are arguments consumed by subclass ``compare()`` methods and may be used to modify the criteria for comparison (see :class:`_expression.ColumnElement`). """ return traversals.compare(self, other, **kw) def self_group( self, against: Optional[OperatorType] = None ) -> ClauseElement: """Apply a 'grouping' to this :class:`_expression.ClauseElement`. This method is overridden by subclasses to return a "grouping" construct, i.e. parenthesis. In particular it's used by "binary" expressions to provide a grouping around themselves when placed into a larger expression, as well as by :func:`_expression.select` constructs when placed into the FROM clause of another :func:`_expression.select`. (Note that subqueries should be normally created using the :meth:`_expression.Select.alias` method, as many platforms require nested SELECT statements to be named). As expressions are composed together, the application of :meth:`self_group` is automatic - end-user code should never need to use this method directly. Note that SQLAlchemy's clause constructs take operator precedence into account - so parenthesis might not be needed, for example, in an expression like ``x OR (y AND z)`` - AND takes precedence over OR. The base :meth:`self_group` method of :class:`_expression.ClauseElement` just returns self. """ return self def _ungroup(self) -> ClauseElement: """Return this :class:`_expression.ClauseElement` without any groupings. """ return self def _compile_w_cache( self, dialect: Dialect, *, compiled_cache: Optional[CompiledCacheType], column_keys: List[str], for_executemany: bool = False, schema_translate_map: Optional[SchemaTranslateMapType] = None, **kw: Any, ) -> typing_Tuple[ Compiled, Optional[Sequence[BindParameter[Any]]], CacheStats ]: elem_cache_key: Optional[CacheKey] if compiled_cache is not None and dialect._supports_statement_cache: elem_cache_key = self._generate_cache_key() else: elem_cache_key = None if elem_cache_key is not None: if TYPE_CHECKING: assert compiled_cache is not None cache_key, extracted_params = elem_cache_key key = ( dialect, cache_key, tuple(column_keys), bool(schema_translate_map), for_executemany, ) compiled_sql = compiled_cache.get(key) if compiled_sql is None: cache_hit = dialect.CACHE_MISS compiled_sql = self._compiler( dialect, cache_key=elem_cache_key, column_keys=column_keys, for_executemany=for_executemany, schema_translate_map=schema_translate_map, **kw, ) compiled_cache[key] = compiled_sql else: cache_hit = dialect.CACHE_HIT else: extracted_params = None compiled_sql = self._compiler( dialect, cache_key=elem_cache_key, column_keys=column_keys, for_executemany=for_executemany, schema_translate_map=schema_translate_map, **kw, ) if not dialect._supports_statement_cache: cache_hit = dialect.NO_DIALECT_SUPPORT elif compiled_cache is None: cache_hit = dialect.CACHING_DISABLED else: cache_hit = dialect.NO_CACHE_KEY return compiled_sql, extracted_params, cache_hit def __invert__(self): # undocumented element currently used by the ORM for # relationship.contains() if hasattr(self, "negation_clause"): return self.negation_clause else: return self._negate() def _negate(self) -> ClauseElement: grouped = self.self_group(against=operators.inv) assert isinstance(grouped, ColumnElement) return UnaryExpression(grouped, operator=operators.inv) def __bool__(self): raise TypeError("Boolean value of this clause is not defined") def __repr__(self): friendly = self.description if friendly is None: return object.__repr__(self) else: return "<%s.%s at 0x%x; %s>" % ( self.__module__, self.__class__.__name__, id(self), friendly, ) class DQLDMLClauseElement(ClauseElement): """represents a :class:`.ClauseElement` that compiles to a DQL or DML expression, not DDL. .. versionadded:: 2.0 """ if typing.TYPE_CHECKING: def _compiler(self, dialect: Dialect, **kw: Any) -> SQLCompiler: """Return a compiler appropriate for this ClauseElement, given a Dialect.""" ... def compile( # noqa: A001 self, bind: Optional[_HasDialect] = None, dialect: Optional[Dialect] = None, **kw: Any, ) -> SQLCompiler: ... class CompilerColumnElement( roles.DMLColumnRole, roles.DDLConstraintColumnRole, roles.ColumnsClauseRole, CompilerElement, ): """A compiler-only column element used for ad-hoc string compilations. .. versionadded:: 2.0 """ __slots__ = () _propagate_attrs = util.EMPTY_DICT _is_collection_aggregate = False # SQLCoreOperations should be suiting the ExpressionElementRole # and ColumnsClauseRole. however the MRO issues become too elaborate # at the moment. class SQLCoreOperations(Generic[_T_co], ColumnOperators, TypingOnly): __slots__ = () # annotations for comparison methods # these are from operators->Operators / ColumnOperators, # redefined with the specific types returned by ColumnElement hierarchies if typing.TYPE_CHECKING: @util.non_memoized_property def _propagate_attrs(self) -> _PropagateAttrsType: ... def operate( self, op: OperatorType, *other: Any, **kwargs: Any ) -> ColumnElement[Any]: ... def reverse_operate( self, op: OperatorType, other: Any, **kwargs: Any ) -> ColumnElement[Any]: ... @overload def op( self, opstring: str, precedence: int = ..., is_comparison: bool = ..., *, return_type: _TypeEngineArgument[_OPT], python_impl: Optional[Callable[..., Any]] = None, ) -> Callable[[Any], BinaryExpression[_OPT]]: ... @overload def op( self, opstring: str, precedence: int = ..., is_comparison: bool = ..., return_type: Optional[_TypeEngineArgument[Any]] = ..., python_impl: Optional[Callable[..., Any]] = ..., ) -> Callable[[Any], BinaryExpression[Any]]: ... def op( self, opstring: str, precedence: int = 0, is_comparison: bool = False, return_type: Optional[_TypeEngineArgument[Any]] = None, python_impl: Optional[Callable[..., Any]] = None, ) -> Callable[[Any], BinaryExpression[Any]]: ... def bool_op( self, opstring: str, precedence: int = 0, python_impl: Optional[Callable[..., Any]] = None, ) -> Callable[[Any], BinaryExpression[bool]]: ... def __and__(self, other: Any) -> BooleanClauseList: ... def __or__(self, other: Any) -> BooleanClauseList: ... def __invert__(self) -> ColumnElement[_T_co]: ... def __lt__(self, other: Any) -> ColumnElement[bool]: ... def __le__(self, other: Any) -> ColumnElement[bool]: ... # declare also that this class has an hash method otherwise # it may be assumed to be None by type checkers since the # object defines __eq__ and python sets it to None in that case: # https://docs.python.org/3/reference/datamodel.html#object.__hash__ def __hash__(self) -> int: ... def __eq__(self, other: Any) -> ColumnElement[bool]: # type: ignore[override] # noqa: E501 ... def __ne__(self, other: Any) -> ColumnElement[bool]: # type: ignore[override] # noqa: E501 ... def is_distinct_from(self, other: Any) -> ColumnElement[bool]: ... def is_not_distinct_from(self, other: Any) -> ColumnElement[bool]: ... def __gt__(self, other: Any) -> ColumnElement[bool]: ... def __ge__(self, other: Any) -> ColumnElement[bool]: ... def __neg__(self) -> UnaryExpression[_T_co]: ... def __contains__(self, other: Any) -> ColumnElement[bool]: ... def __getitem__(self, index: Any) -> ColumnElement[Any]: ... @overload def __lshift__(self: _SQO[int], other: Any) -> ColumnElement[int]: ... @overload def __lshift__(self, other: Any) -> ColumnElement[Any]: ... def __lshift__(self, other: Any) -> ColumnElement[Any]: ... @overload def __rshift__(self: _SQO[int], other: Any) -> ColumnElement[int]: ... @overload def __rshift__(self, other: Any) -> ColumnElement[Any]: ... def __rshift__(self, other: Any) -> ColumnElement[Any]: ... @overload def concat(self: _SQO[str], other: Any) -> ColumnElement[str]: ... @overload def concat(self, other: Any) -> ColumnElement[Any]: ... def concat(self, other: Any) -> ColumnElement[Any]: ... def like( self, other: Any, escape: Optional[str] = None ) -> BinaryExpression[bool]: ... def ilike( self, other: Any, escape: Optional[str] = None ) -> BinaryExpression[bool]: ... def bitwise_xor(self, other: Any) -> BinaryExpression[Any]: ... def bitwise_or(self, other: Any) -> BinaryExpression[Any]: ... def bitwise_and(self, other: Any) -> BinaryExpression[Any]: ... def bitwise_not(self) -> UnaryExpression[_T_co]: ... def bitwise_lshift(self, other: Any) -> BinaryExpression[Any]: ... def bitwise_rshift(self, other: Any) -> BinaryExpression[Any]: ... def in_( self, other: Union[ Iterable[Any], BindParameter[Any], roles.InElementRole ], ) -> BinaryExpression[bool]: ... def not_in( self, other: Union[ Iterable[Any], BindParameter[Any], roles.InElementRole ], ) -> BinaryExpression[bool]: ... def notin_( self, other: Union[ Iterable[Any], BindParameter[Any], roles.InElementRole ], ) -> BinaryExpression[bool]: ... def not_like( self, other: Any, escape: Optional[str] = None ) -> BinaryExpression[bool]: ... def notlike( self, other: Any, escape: Optional[str] = None ) -> BinaryExpression[bool]: ... def not_ilike( self, other: Any, escape: Optional[str] = None ) -> BinaryExpression[bool]: ... def notilike( self, other: Any, escape: Optional[str] = None ) -> BinaryExpression[bool]: ... def is_(self, other: Any) -> BinaryExpression[bool]: ... def is_not(self, other: Any) -> BinaryExpression[bool]: ... def isnot(self, other: Any) -> BinaryExpression[bool]: ... def startswith( self, other: Any, escape: Optional[str] = None, autoescape: bool = False, ) -> ColumnElement[bool]: ... def istartswith( self, other: Any, escape: Optional[str] = None, autoescape: bool = False, ) -> ColumnElement[bool]: ... def endswith( self, other: Any, escape: Optional[str] = None, autoescape: bool = False, ) -> ColumnElement[bool]: ... def iendswith( self, other: Any, escape: Optional[str] = None, autoescape: bool = False, ) -> ColumnElement[bool]: ... def contains(self, other: Any, **kw: Any) -> ColumnElement[bool]: ... def icontains(self, other: Any, **kw: Any) -> ColumnElement[bool]: ... def match(self, other: Any, **kwargs: Any) -> ColumnElement[bool]: ... def regexp_match( self, pattern: Any, flags: Optional[str] = None ) -> ColumnElement[bool]: ... def regexp_replace( self, pattern: Any, replacement: Any, flags: Optional[str] = None ) -> ColumnElement[str]: ... def desc(self) -> UnaryExpression[_T_co]: ... def asc(self) -> UnaryExpression[_T_co]: ... def nulls_first(self) -> UnaryExpression[_T_co]: ... def nullsfirst(self) -> UnaryExpression[_T_co]: ... def nulls_last(self) -> UnaryExpression[_T_co]: ... def nullslast(self) -> UnaryExpression[_T_co]: ... def collate(self, collation: str) -> CollationClause: ... def between( self, cleft: Any, cright: Any, symmetric: bool = False ) -> BinaryExpression[bool]: ... def distinct(self: _SQO[_T_co]) -> UnaryExpression[_T_co]: ... def any_(self) -> CollectionAggregate[Any]: ... def all_(self) -> CollectionAggregate[Any]: ... # numeric overloads. These need more tweaking # in particular they all need to have a variant for Optiona[_T] # because Optional only applies to the data side, not the expression # side @overload def __add__( self: _SQO[_NMT], other: Any, ) -> ColumnElement[_NMT]: ... @overload def __add__( self: _SQO[str], other: Any, ) -> ColumnElement[str]: ... @overload def __add__(self, other: Any) -> ColumnElement[Any]: ... def __add__(self, other: Any) -> ColumnElement[Any]: ... @overload def __radd__(self: _SQO[_NMT], other: Any) -> ColumnElement[_NMT]: ... @overload def __radd__(self: _SQO[str], other: Any) -> ColumnElement[str]: ... def __radd__(self, other: Any) -> ColumnElement[Any]: ... @overload def __sub__( self: _SQO[_NMT], other: Any, ) -> ColumnElement[_NMT]: ... @overload def __sub__(self, other: Any) -> ColumnElement[Any]: ... def __sub__(self, other: Any) -> ColumnElement[Any]: ... @overload def __rsub__( self: _SQO[_NMT], other: Any, ) -> ColumnElement[_NMT]: ... @overload def __rsub__(self, other: Any) -> ColumnElement[Any]: ... def __rsub__(self, other: Any) -> ColumnElement[Any]: ... @overload def __mul__( self: _SQO[_NMT], other: Any, ) -> ColumnElement[_NMT]: ... @overload def __mul__(self, other: Any) -> ColumnElement[Any]: ... def __mul__(self, other: Any) -> ColumnElement[Any]: ... @overload def __rmul__( self: _SQO[_NMT], other: Any, ) -> ColumnElement[_NMT]: ... @overload def __rmul__(self, other: Any) -> ColumnElement[Any]: ... def __rmul__(self, other: Any) -> ColumnElement[Any]: ... @overload def __mod__(self: _SQO[_NMT], other: Any) -> ColumnElement[_NMT]: ... @overload def __mod__(self, other: Any) -> ColumnElement[Any]: ... def __mod__(self, other: Any) -> ColumnElement[Any]: ... @overload def __rmod__(self: _SQO[_NMT], other: Any) -> ColumnElement[_NMT]: ... @overload def __rmod__(self, other: Any) -> ColumnElement[Any]: ... def __rmod__(self, other: Any) -> ColumnElement[Any]: ... @overload def __truediv__( self: _SQO[int], other: Any ) -> ColumnElement[_NUMERIC]: ... @overload def __truediv__(self: _SQO[_NT], other: Any) -> ColumnElement[_NT]: ... @overload def __truediv__(self, other: Any) -> ColumnElement[Any]: ... def __truediv__(self, other: Any) -> ColumnElement[Any]: ... @overload def __rtruediv__( self: _SQO[_NMT], other: Any ) -> ColumnElement[_NUMERIC]: ... @overload def __rtruediv__(self, other: Any) -> ColumnElement[Any]: ... def __rtruediv__(self, other: Any) -> ColumnElement[Any]: ... @overload def __floordiv__( self: _SQO[_NMT], other: Any ) -> ColumnElement[_NMT]: ... @overload def __floordiv__(self, other: Any) -> ColumnElement[Any]: ... def __floordiv__(self, other: Any) -> ColumnElement[Any]: ... @overload def __rfloordiv__( self: _SQO[_NMT], other: Any ) -> ColumnElement[_NMT]: ... @overload def __rfloordiv__(self, other: Any) -> ColumnElement[Any]: ... def __rfloordiv__(self, other: Any) -> ColumnElement[Any]: ... class SQLColumnExpression( SQLCoreOperations[_T_co], roles.ExpressionElementRole[_T_co], TypingOnly ): """A type that may be used to indicate any SQL column element or object that acts in place of one. :class:`.SQLColumnExpression` is a base of :class:`.ColumnElement`, as well as within the bases of ORM elements such as :class:`.InstrumentedAttribute`, and may be used in :pep:`484` typing to indicate arguments or return values that should behave as column expressions. .. versionadded:: 2.0.0b4 """ __slots__ = () _SQO = SQLCoreOperations class ColumnElement( roles.ColumnArgumentOrKeyRole, roles.StatementOptionRole, roles.WhereHavingRole, roles.BinaryElementRole[_T], roles.OrderByRole, roles.ColumnsClauseRole, roles.LimitOffsetRole, roles.DMLColumnRole, roles.DDLConstraintColumnRole, roles.DDLExpressionRole, SQLColumnExpression[_T], DQLDMLClauseElement, ): """Represent a column-oriented SQL expression suitable for usage in the "columns" clause, WHERE clause etc. of a statement. While the most familiar kind of :class:`_expression.ColumnElement` is the :class:`_schema.Column` object, :class:`_expression.ColumnElement` serves as the basis for any unit that may be present in a SQL expression, including the expressions themselves, SQL functions, bound parameters, literal expressions, keywords such as ``NULL``, etc. :class:`_expression.ColumnElement` is the ultimate base class for all such elements. A wide variety of SQLAlchemy Core functions work at the SQL expression level, and are intended to accept instances of :class:`_expression.ColumnElement` as arguments. These functions will typically document that they accept a "SQL expression" as an argument. What this means in terms of SQLAlchemy usually refers to an input which is either already in the form of a :class:`_expression.ColumnElement` object, or a value which can be **coerced** into one. The coercion rules followed by most, but not all, SQLAlchemy Core functions with regards to SQL expressions are as follows: * a literal Python value, such as a string, integer or floating point value, boolean, datetime, ``Decimal`` object, or virtually any other Python object, will be coerced into a "literal bound value". This generally means that a :func:`.bindparam` will be produced featuring the given value embedded into the construct; the resulting :class:`.BindParameter` object is an instance of :class:`_expression.ColumnElement`. The Python value will ultimately be sent to the DBAPI at execution time as a parameterized argument to the ``execute()`` or ``executemany()`` methods, after SQLAlchemy type-specific converters (e.g. those provided by any associated :class:`.TypeEngine` objects) are applied to the value. * any special object value, typically ORM-level constructs, which feature an accessor called ``__clause_element__()``. The Core expression system looks for this method when an object of otherwise unknown type is passed to a function that is looking to coerce the argument into a :class:`_expression.ColumnElement` and sometimes a :class:`_expression.SelectBase` expression. It is used within the ORM to convert from ORM-specific objects like mapped classes and mapped attributes into Core expression objects. * The Python ``None`` value is typically interpreted as ``NULL``, which in SQLAlchemy Core produces an instance of :func:`.null`. A :class:`_expression.ColumnElement` provides the ability to generate new :class:`_expression.ColumnElement` objects using Python expressions. This means that Python operators such as ``==``, ``!=`` and ``<`` are overloaded to mimic SQL operations, and allow the instantiation of further :class:`_expression.ColumnElement` instances which are composed from other, more fundamental :class:`_expression.ColumnElement` objects. For example, two :class:`.ColumnClause` objects can be added together with the addition operator ``+`` to produce a :class:`.BinaryExpression`. Both :class:`.ColumnClause` and :class:`.BinaryExpression` are subclasses of :class:`_expression.ColumnElement`: .. sourcecode:: pycon+sql >>> from sqlalchemy.sql import column >>> column('a') + column('b') >>> print(column('a') + column('b')) {printsql}a + b .. seealso:: :class:`_schema.Column` :func:`_expression.column` """ __visit_name__ = "column_element" primary_key: bool = False _is_clone_of: Optional[ColumnElement[_T]] _is_column_element = True _insert_sentinel: bool = False _omit_from_statements = False _is_collection_aggregate = False foreign_keys: AbstractSet[ForeignKey] = frozenset() @util.memoized_property def _proxies(self) -> List[ColumnElement[Any]]: return [] @util.non_memoized_property def _tq_label(self) -> Optional[str]: """The named label that can be used to target this column in a result set in a "table qualified" context. This label is almost always the label used when rendering AS