Expressions

plateforme.core.database.expressions

This module provides utilities for managing database expressions within the Plateforme framework using SQLAlchemy features.

LABEL_STYLE_DEFAULT module-attribute

LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

func module-attribute

func = _FunctionGenerator()

modifier module-attribute

modifier = _FunctionGenerator(group=False)

nullsfirst module-attribute

nullsfirst = nulls_first

Synonym for the :func:.nulls_first function.

nullslast module-attribute

nullslast = nulls_last

Synonym for the :func:.nulls_last function.

BinaryExpression

BinaryExpression(
    left: ColumnElement[Any],
    right: ColumnElement[Any],
    operator: OperatorType,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    negate: Optional[OperatorType] = None,
    modifiers: Optional[Mapping[str, Any]] = None,
)

Bases: OperatorExpression[_T]

Represent an expression that is LEFT <operator> RIGHT.

A :class:.BinaryExpression is generated automatically whenever two column expressions are used in a Python binary expression:

.. sourcecode:: pycon+sql

>>> from sqlalchemy.sql import column
>>> column('a') + column('b')
<sqlalchemy.sql.expression.BinaryExpression object at 0x101029dd0>
>>> print(column('a') + column('b'))
{printsql}a + b

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def __init__(
    self,
    left: ColumnElement[Any],
    right: ColumnElement[Any],
    operator: OperatorType,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    negate: Optional[OperatorType] = None,
    modifiers: Optional[Mapping[str, Any]] = None,
):
    # allow compatibility with libraries that
    # refer to BinaryExpression directly and pass strings
    if isinstance(operator, str):
        operator = operators.custom_op(operator)
    self._orig = (left.__hash__(), right.__hash__())
    self._propagate_attrs = left._propagate_attrs or right._propagate_attrs
    self.left = left.self_group(against=operator)
    self.right = right.self_group(against=operator)
    self.operator = operator

    # if type is None, we get NULLTYPE, which is our _T.  But I don't
    # know how to get the overloads to express that correctly
    self.type = type_api.to_instance(type_)  # type: ignore

    self.negate = negate
    self._is_implicitly_boolean = operators.is_boolean(operator)

    if modifiers is None:
        self.modifiers = {}
    else:
        self.modifiers = modifiers

inherit_cache class-attribute instance-attribute

inherit_cache: Optional[bool] = None

Indicate if this :class:.HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

The attribute defaults to None, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value to False, except that a warning is also emitted.

This flag can be set to True on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.

.. seealso::

:ref:`compilerext_caching` - General guideslines for setting the
:attr:`.HasCacheKey.inherit_cache` attribute for third-party or user
defined SQL constructs.

timetuple class-attribute instance-attribute

timetuple: Literal[None] = None

Hack, allows datetime objects to be compared on the LHS.

key class-attribute instance-attribute

key: Optional[str] = None

The 'key' that in some circumstances refers to this object in a Python namespace.

This typically refers to the "key" of the column as present in the .c collection of a selectable, e.g. sometable.c["somekey"] would return a :class:_schema.Column with a .key of "somekey".

expression property

expression: ColumnElement[Any]

Return a column expression.

Part of the inspection interface; returns self.

memoized_attribute

memoized_attribute(
    fget: Callable[..., _T], doc: Optional[str] = None
)

Bases: memoized_property[_T]

A read-only @property that is only evaluated once.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

def __init__(self, fget: Callable[..., _T], doc: Optional[str] = None):
    self.fget = fget
    self.__doc__ = doc or fget.__doc__
    self.__name__ = fget.__name__

memoized_instancemethod classmethod

memoized_instancemethod(fn: _F) -> _F

Decorate a method memoize its return value.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

@classmethod
def memoized_instancemethod(cls, fn: _F) -> _F:
    """Decorate a method memoize its return value.

    :meta private:

    """

    def oneshot(self: Any, *args: Any, **kw: Any) -> Any:
        result = fn(self, *args, **kw)

        def memo(*a, **kw):
            return result

        memo.__name__ = fn.__name__
        memo.__doc__ = fn.__doc__
        self.__dict__[fn.__name__] = memo
        self._memoized_keys |= {fn.__name__}
        return result

    return update_wrapper(oneshot, fn)  # type: ignore

proxy_set

proxy_set() -> FrozenSet[ColumnElement[Any]]

set of all columns we are proxying

as of 2.0 this is explicitly deannotated columns. previously it was effectively deannotated columns but wasn't enforced. annotated columns should basically not go into sets if at all possible because their hashing behavior is very non-performant.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

@util.memoized_property
def proxy_set(self) -> FrozenSet[ColumnElement[Any]]:
    """set of all columns we are proxying

    as of 2.0 this is explicitly deannotated columns.  previously it was
    effectively deannotated columns but wasn't enforced.  annotated
    columns should basically not go into sets if at all possible because
    their hashing behavior is very non-performant.

    """
    return frozenset([self._deannotate()]).union(
        itertools.chain(*[c.proxy_set for c in self._proxies])
    )

unique_params

unique_params(
    __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.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

params

params(
    __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)

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

compare

compare(other: ClauseElement, **kw: Any) -> bool

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).

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

label

label(name: Optional[str]) -> Label[_T]

Produce a column label, i.e. <columnname> AS <name>.

This is a shortcut to the :func:_expression.label function.

If 'name' is None, an anonymous label name will be generated.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def label(self, name: Optional[str]) -> Label[_T]:
    """Produce a column label, i.e. ``<columnname> AS <name>``.

    This is a shortcut to the :func:`_expression.label` function.

    If 'name' is ``None``, an anonymous label name will be generated.

    """
    return Label(name, self, self.type)

shares_lineage

shares_lineage(othercolumn: ColumnElement[Any]) -> bool

Return True if the given :class:_expression.ColumnElement has a common ancestor to this :class:_expression.ColumnElement.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def shares_lineage(self, othercolumn: ColumnElement[Any]) -> bool:
    """Return True if the given :class:`_expression.ColumnElement`
    has a common ancestor to this :class:`_expression.ColumnElement`."""

    return bool(self.proxy_set.intersection(othercolumn.proxy_set))

cast

cast(type_: _TypeEngineArgument[_OPT]) -> Cast[_OPT]

Produce a type cast, i.e. CAST(<expression> AS <type>).

This is a shortcut to the :func:_expression.cast function.

.. seealso::

:ref:`tutorial_casts`

:func:`_expression.cast`

:func:`_expression.type_coerce`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def cast(self, type_: _TypeEngineArgument[_OPT]) -> Cast[_OPT]:
    """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.

    This is a shortcut to the :func:`_expression.cast` function.

    .. seealso::

        :ref:`tutorial_casts`

        :func:`_expression.cast`

        :func:`_expression.type_coerce`

    """
    return Cast(self, type_)

Executable

Bases: StatementRole

Mark a :class:_expression.ClauseElement as supporting execution.

:class:.Executable is a superclass for all "statement" types of objects, including :func:select, :func:delete, :func:update, :func:insert, :func:text.

options

options(*options: ExecutableOption) -> Self

Apply options to this statement.

In the general sense, options are any kind of Python object that can be interpreted by the SQL compiler for the statement. These options can be consumed by specific dialects or specific kinds of compilers.

The most commonly known kind of option are the ORM level options that apply "eager load" and other loading behaviors to an ORM query. However, options can theoretically be used for many other purposes.

For background on specific kinds of options for specific kinds of statements, refer to the documentation for those option objects.

.. versionchanged:: 1.4 - added :meth:.Executable.options to Core statement objects towards the goal of allowing unified Core / ORM querying capabilities.

.. seealso::

:ref:`loading_columns` - refers to options specific to the usage
of ORM queries

:ref:`relationship_loader_options` - refers to options specific
to the usage of ORM queries

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py

@_generative
def options(self, *options: ExecutableOption) -> Self:
    """Apply options to this statement.

    In the general sense, options are any kind of Python object
    that can be interpreted by the SQL compiler for the statement.
    These options can be consumed by specific dialects or specific kinds
    of compilers.

    The most commonly known kind of option are the ORM level options
    that apply "eager load" and other loading behaviors to an ORM
    query.   However, options can theoretically be used for many other
    purposes.

    For background on specific kinds of options for specific kinds of
    statements, refer to the documentation for those option objects.

    .. versionchanged:: 1.4 - added :meth:`.Executable.options` to
       Core statement objects towards the goal of allowing unified
       Core / ORM querying capabilities.

    .. seealso::

        :ref:`loading_columns` - refers to options specific to the usage
        of ORM queries

        :ref:`relationship_loader_options` - refers to options specific
        to the usage of ORM queries

    """
    self._with_options += tuple(
        coercions.expect(roles.ExecutableOptionRole, opt)
        for opt in options
    )
    return self

execution_options

execution_options(
    *,
    compiled_cache: Optional[CompiledCacheType] = ...,
    logging_token: str = ...,
    isolation_level: IsolationLevel = ...,
    no_parameters: bool = False,
    stream_results: bool = False,
    max_row_buffer: int = ...,
    yield_per: int = ...,
    insertmanyvalues_page_size: int = ...,
    schema_translate_map: Optional[
        SchemaTranslateMapType
    ] = ...,
    populate_existing: bool = False,
    autoflush: bool = False,
    synchronize_session: SynchronizeSessionArgument = ...,
    dml_strategy: DMLStrategyArgument = ...,
    render_nulls: bool = ...,
    is_delete_using: bool = ...,
    is_update_from: bool = ...,
    **opt: Any,
) -> Self

execution_options(**opt: Any) -> Self

execution_options(**kw: Any) -> Self

Set non-SQL options for the statement which take effect during execution.

Execution options can be set at many scopes, including per-statement, per-connection, or per execution, using methods such as :meth:_engine.Connection.execution_options and parameters which accept a dictionary of options such as :paramref:_engine.Connection.execute.execution_options and :paramref:_orm.Session.execute.execution_options.

The primary characteristic of an execution option, as opposed to other kinds of options such as ORM loader options, is that execution options never affect the compiled SQL of a query, only things that affect how the SQL statement itself is invoked or how results are fetched. That is, execution options are not part of what's accommodated by SQL compilation nor are they considered part of the cached state of a statement.

The :meth:_sql.Executable.execution_options method is :term:generative, as is the case for the method as applied to the :class:_engine.Engine and :class:_orm.Query objects, which means when the method is called, a copy of the object is returned, which applies the given parameters to that new copy, but leaves the original unchanged::

statement = select(table.c.x, table.c.y)
new_statement = statement.execution_options(my_option=True)

An exception to this behavior is the :class:_engine.Connection object, where the :meth:_engine.Connection.execution_options method is explicitly not generative.

The kinds of options that may be passed to :meth:_sql.Executable.execution_options and other related methods and parameter dictionaries include parameters that are explicitly consumed by SQLAlchemy Core or ORM, as well as arbitrary keyword arguments not defined by SQLAlchemy, which means the methods and/or parameter dictionaries may be used for user-defined parameters that interact with custom code, which may access the parameters using methods such as :meth:_sql.Executable.get_execution_options and :meth:_engine.Connection.get_execution_options, or within selected event hooks using a dedicated execution_options event parameter such as :paramref:_events.ConnectionEvents.before_execute.execution_options or :attr:_orm.ORMExecuteState.execution_options, e.g.::

 from sqlalchemy import event

 @event.listens_for(some_engine, "before_execute")
 def _process_opt(conn, statement, multiparams, params, execution_options):
     "run a SQL function before invoking a statement"

     if execution_options.get("do_special_thing", False):
         conn.exec_driver_sql("run_special_function()")

Within the scope of options that are explicitly recognized by SQLAlchemy, most apply to specific classes of objects and not others. The most common execution options include:

• :paramref:_engine.Connection.execution_options.isolation_level - sets the isolation level for a connection or a class of connections via an :class:_engine.Engine. This option is accepted only by :class:_engine.Connection or :class:_engine.Engine.

• :paramref:_engine.Connection.execution_options.stream_results - indicates results should be fetched using a server side cursor; this option is accepted by :class:_engine.Connection, by the :paramref:_engine.Connection.execute.execution_options parameter on :meth:_engine.Connection.execute, and additionally by :meth:_sql.Executable.execution_options on a SQL statement object, as well as by ORM constructs like :meth:_orm.Session.execute.

• :paramref:_engine.Connection.execution_options.compiled_cache - indicates a dictionary that will serve as the :ref:SQL compilation cache <sql_caching> for a :class:_engine.Connection or :class:_engine.Engine, as well as for ORM methods like :meth:_orm.Session.execute. Can be passed as None to disable caching for statements. This option is not accepted by :meth:_sql.Executable.execution_options as it is inadvisable to carry along a compilation cache within a statement object.

• :paramref:_engine.Connection.execution_options.schema_translate_map

• a mapping of schema names used by the :ref:Schema Translate Map <schema_translating> feature, accepted by :class:_engine.Connection, :class:_engine.Engine, :class:_sql.Executable, as well as by ORM constructs like :meth:_orm.Session.execute.

.. seealso::

:meth:`_engine.Connection.execution_options`

:paramref:`_engine.Connection.execute.execution_options`

:paramref:`_orm.Session.execute.execution_options`

:ref:`orm_queryguide_execution_options` - documentation on all
ORM-specific execution options

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py

@_generative
def execution_options(self, **kw: Any) -> Self:
    """Set non-SQL options for the statement which take effect during
    execution.

    Execution options can be set at many scopes, including per-statement,
    per-connection, or per execution, using methods such as
    :meth:`_engine.Connection.execution_options` and parameters which
    accept a dictionary of options such as
    :paramref:`_engine.Connection.execute.execution_options` and
    :paramref:`_orm.Session.execute.execution_options`.

    The primary characteristic of an execution option, as opposed to
    other kinds of options such as ORM loader options, is that
    **execution options never affect the compiled SQL of a query, only
    things that affect how the SQL statement itself is invoked or how
    results are fetched**.  That is, execution options are not part of
    what's accommodated by SQL compilation nor are they considered part of
    the cached state of a statement.

    The :meth:`_sql.Executable.execution_options` method is
    :term:`generative`, as
    is the case for the method as applied to the :class:`_engine.Engine`
    and :class:`_orm.Query` objects, which means when the method is called,
    a copy of the object is returned, which applies the given parameters to
    that new copy, but leaves the original unchanged::

        statement = select(table.c.x, table.c.y)
        new_statement = statement.execution_options(my_option=True)

    An exception to this behavior is the :class:`_engine.Connection`
    object, where the :meth:`_engine.Connection.execution_options` method
    is explicitly **not** generative.

    The kinds of options that may be passed to
    :meth:`_sql.Executable.execution_options` and other related methods and
    parameter dictionaries include parameters that are explicitly consumed
    by SQLAlchemy Core or ORM, as well as arbitrary keyword arguments not
    defined by SQLAlchemy, which means the methods and/or parameter
    dictionaries may be used for user-defined parameters that interact with
    custom code, which may access the parameters using methods such as
    :meth:`_sql.Executable.get_execution_options` and
    :meth:`_engine.Connection.get_execution_options`, or within selected
    event hooks using a dedicated ``execution_options`` event parameter
    such as
    :paramref:`_events.ConnectionEvents.before_execute.execution_options`
    or :attr:`_orm.ORMExecuteState.execution_options`, e.g.::

         from sqlalchemy import event

         @event.listens_for(some_engine, "before_execute")
         def _process_opt(conn, statement, multiparams, params, execution_options):
             "run a SQL function before invoking a statement"

             if execution_options.get("do_special_thing", False):
                 conn.exec_driver_sql("run_special_function()")

    Within the scope of options that are explicitly recognized by
    SQLAlchemy, most apply to specific classes of objects and not others.
    The most common execution options include:

    * :paramref:`_engine.Connection.execution_options.isolation_level` -
      sets the isolation level for a connection or a class of connections
      via an :class:`_engine.Engine`.  This option is accepted only
      by :class:`_engine.Connection` or :class:`_engine.Engine`.

    * :paramref:`_engine.Connection.execution_options.stream_results` -
      indicates results should be fetched using a server side cursor;
      this option is accepted by :class:`_engine.Connection`, by the
      :paramref:`_engine.Connection.execute.execution_options` parameter
      on :meth:`_engine.Connection.execute`, and additionally by
      :meth:`_sql.Executable.execution_options` on a SQL statement object,
      as well as by ORM constructs like :meth:`_orm.Session.execute`.

    * :paramref:`_engine.Connection.execution_options.compiled_cache` -
      indicates a dictionary that will serve as the
      :ref:`SQL compilation cache <sql_caching>`
      for a :class:`_engine.Connection` or :class:`_engine.Engine`, as
      well as for ORM methods like :meth:`_orm.Session.execute`.
      Can be passed as ``None`` to disable caching for statements.
      This option is not accepted by
      :meth:`_sql.Executable.execution_options` as it is inadvisable to
      carry along a compilation cache within a statement object.

    * :paramref:`_engine.Connection.execution_options.schema_translate_map`
      - a mapping of schema names used by the
      :ref:`Schema Translate Map <schema_translating>` feature, accepted
      by :class:`_engine.Connection`, :class:`_engine.Engine`,
      :class:`_sql.Executable`, as well as by ORM constructs
      like :meth:`_orm.Session.execute`.

    .. seealso::

        :meth:`_engine.Connection.execution_options`

        :paramref:`_engine.Connection.execute.execution_options`

        :paramref:`_orm.Session.execute.execution_options`

        :ref:`orm_queryguide_execution_options` - documentation on all
        ORM-specific execution options

    """  # noqa: E501
    if "isolation_level" in kw:
        raise exc.ArgumentError(
            "'isolation_level' execution option may only be specified "
            "on Connection.execution_options(), or "
            "per-engine using the isolation_level "
            "argument to create_engine()."
        )
    if "compiled_cache" in kw:
        raise exc.ArgumentError(
            "'compiled_cache' execution option may only be specified "
            "on Connection.execution_options(), not per statement."
        )
    self._execution_options = self._execution_options.union(kw)
    return self

get_execution_options

get_execution_options() -> _ExecuteOptions

Get the non-SQL options which will take effect during execution.

.. versionadded:: 1.3

.. seealso::

:meth:`.Executable.execution_options`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py

def get_execution_options(self) -> _ExecuteOptions:
    """Get the non-SQL options which will take effect during execution.

    .. versionadded:: 1.3

    .. seealso::

        :meth:`.Executable.execution_options`
    """
    return self._execution_options

OperatorExpression

Bases: ColumnElement[_T]

base for expressions that contain an operator and operands

.. versionadded:: 2.0

inherit_cache class-attribute instance-attribute

inherit_cache: Optional[bool] = None

Indicate if this :class:.HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

The attribute defaults to None, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value to False, except that a warning is also emitted.

This flag can be set to True on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.

.. seealso::

:ref:`compilerext_caching` - General guideslines for setting the
:attr:`.HasCacheKey.inherit_cache` attribute for third-party or user
defined SQL constructs.

timetuple class-attribute instance-attribute

timetuple: Literal[None] = None

Hack, allows datetime objects to be compared on the LHS.

key class-attribute instance-attribute

key: Optional[str] = None

The 'key' that in some circumstances refers to this object in a Python namespace.

This typically refers to the "key" of the column as present in the .c collection of a selectable, e.g. sometable.c["somekey"] would return a :class:_schema.Column with a .key of "somekey".

expression property

expression: ColumnElement[Any]

Return a column expression.

Part of the inspection interface; returns self.

memoized_attribute

memoized_attribute(
    fget: Callable[..., _T], doc: Optional[str] = None
)

Bases: memoized_property[_T]

A read-only @property that is only evaluated once.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

def __init__(self, fget: Callable[..., _T], doc: Optional[str] = None):
    self.fget = fget
    self.__doc__ = doc or fget.__doc__
    self.__name__ = fget.__name__

memoized_instancemethod classmethod

memoized_instancemethod(fn: _F" optional hover>_F) -> _F

Decorate a method memoize its return value.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

@classmethod
def memoized_instancemethod(cls, fn: _F) -> _F:
    """Decorate a method memoize its return value.

    :meta private:

    """

    def oneshot(self: Any, *args: Any, **kw: Any) -> Any:
        result = fn(self, *args, **kw)

        def memo(*a, **kw):
            return result

        memo.__name__ = fn.__name__
        memo.__doc__ = fn.__doc__
        self.__dict__[fn.__name__] = memo
        self._memoized_keys |= {fn.__name__}
        return result

    return update_wrapper(oneshot, fn)  # type: ignore

proxy_set

proxy_set() -> FrozenSet[ColumnElement[Any]]

set of all columns we are proxying

as of 2.0 this is explicitly deannotated columns. previously it was effectively deannotated columns but wasn't enforced. annotated columns should basically not go into sets if at all possible because their hashing behavior is very non-performant.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

@util.memoized_property
def proxy_set(self) -> FrozenSet[ColumnElement[Any]]:
    """set of all columns we are proxying

    as of 2.0 this is explicitly deannotated columns.  previously it was
    effectively deannotated columns but wasn't enforced.  annotated
    columns should basically not go into sets if at all possible because
    their hashing behavior is very non-performant.

    """
    return frozenset([self._deannotate()]).union(
        itertools.chain(*[c.proxy_set for c in self._proxies])
    )

unique_params

unique_params(
    __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.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

params

params(
    __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)

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

compare

compare(other: ClauseElement, **kw: Any) -> bool

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).

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

label

label(name: Optional[str]) -> Label[_T]

Produce a column label, i.e. <columnname> AS <name>.

This is a shortcut to the :func:_expression.label function.

If 'name' is None, an anonymous label name will be generated.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def label(self, name: Optional[str]) -> Label[_T]:
    """Produce a column label, i.e. ``<columnname> AS <name>``.

    This is a shortcut to the :func:`_expression.label` function.

    If 'name' is ``None``, an anonymous label name will be generated.

    """
    return Label(name, self, self.type)

shares_lineage

shares_lineage(othercolumn: ColumnElement[Any]) -> bool

Return True if the given :class:_expression.ColumnElement has a common ancestor to this :class:_expression.ColumnElement.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def shares_lineage(self, othercolumn: ColumnElement[Any]) -> bool:
    """Return True if the given :class:`_expression.ColumnElement`
    has a common ancestor to this :class:`_expression.ColumnElement`."""

    return bool(self.proxy_set.intersection(othercolumn.proxy_set))

cast

cast(type_: _TypeEngineArgument[_OPT]) -> Cast[_OPT]

Produce a type cast, i.e. CAST(<expression> AS <type>).

This is a shortcut to the :func:_expression.cast function.

.. seealso::

:ref:`tutorial_casts`

:func:`_expression.cast`

:func:`_expression.type_coerce`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def cast(self, type_: _TypeEngineArgument[_OPT]) -> Cast[_OPT]:
    """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.

    This is a shortcut to the :func:`_expression.cast` function.

    .. seealso::

        :ref:`tutorial_casts`

        :func:`_expression.cast`

        :func:`_expression.type_coerce`

    """
    return Cast(self, type_)

Select

Select(*entities: _ColumnsClauseArgument[Any])

Bases: HasPrefixes, HasSuffixes, HasHints, HasCompileState, _SelectFromElements, GenerativeSelect, TypedReturnsRows[_TP]

Represents a SELECT statement.

The :class:_sql.Select object is normally constructed using the :func:_sql.select function. See that function for details.

.. seealso::

:func:`_sql.select`

:ref:`tutorial_selecting_data` - in the 2.0 tutorial

Construct a new :class:_expression.Select.

The public constructor for :class:_expression.Select is the :func:_sql.select function.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def __init__(self, *entities: _ColumnsClauseArgument[Any]):
    r"""Construct a new :class:`_expression.Select`.

    The public constructor for :class:`_expression.Select` is the
    :func:`_sql.select` function.

    """
    self._raw_columns = [
        coercions.expect(
            roles.ColumnsClauseRole, ent, apply_propagate_attrs=self
        )
        for ent in entities
    ]

    GenerativeSelect.__init__(self)

inherit_cache class-attribute instance-attribute

inherit_cache: Optional[bool] = None

Indicate if this :class:.HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

The attribute defaults to None, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value to False, except that a warning is also emitted.

This flag can be set to True on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.

.. seealso::

:ref:`compilerext_caching` - General guideslines for setting the
:attr:`.HasCacheKey.inherit_cache` attribute for third-party or user
defined SQL constructs.

exported_columns property

exported_columns: ReadOnlyColumnCollection[
    str, ColumnElement[Any]
]

A :class:_expression.ColumnCollection that represents the "exported" columns of this :class:_expression.Selectable, not including :class:_sql.TextClause constructs.

The "exported" columns for a :class:_expression.SelectBase object are synonymous with the :attr:_expression.SelectBase.selected_columns collection.

.. versionadded:: 1.4

.. seealso::

:attr:`_expression.Select.exported_columns`

:attr:`_expression.Selectable.exported_columns`

:attr:`_expression.FromClause.exported_columns`

column_descriptions property

column_descriptions: Any

Return a :term:plugin-enabled 'column descriptions' structure referring to the columns which are SELECTed by this statement.

This attribute is generally useful when using the ORM, as an extended structure which includes information about mapped entities is returned. The section :ref:queryguide_inspection contains more background.

For a Core-only statement, the structure returned by this accessor is derived from the same objects that are returned by the :attr:.Select.selected_columns accessor, formatted as a list of dictionaries which contain the keys name, type and expr, which indicate the column expressions to be selected::

>>> stmt = select(user_table)
>>> stmt.column_descriptions
[
    {
        'name': 'id',
        'type': Integer(),
        'expr': Column('id', Integer(), ...)},
    {
        'name': 'name',
        'type': String(length=30),
        'expr': Column('name', String(length=30), ...)}
]

.. versionchanged:: 1.4.33 The :attr:.Select.column_descriptions attribute returns a structure for a Core-only set of entities, not just ORM-only entities.

.. seealso::

:attr:`.UpdateBase.entity_description` - entity information for
an :func:`.insert`, :func:`.update`, or :func:`.delete`

:ref:`queryguide_inspection` - ORM background

froms property

froms: Sequence[FromClause]

Return the displayed list of :class:_expression.FromClause elements.

columns_clause_froms property

columns_clause_froms: List[FromClause]

Return the set of :class:_expression.FromClause objects implied by the columns clause of this SELECT statement.

.. versionadded:: 1.4.23

.. seealso::

:attr:`_sql.Select.froms` - "final" FROM list taking the full
statement into account

:meth:`_sql.Select.with_only_columns` - makes use of this
collection to set up a new FROM list

inner_columns property

inner_columns: _SelectIterable

An iterator of all :class:_expression.ColumnElement expressions which would be rendered into the columns clause of the resulting SELECT statement.

This method is legacy as of 1.4 and is superseded by the :attr:_expression.Select.exported_columns collection.

whereclause property

whereclause: Optional[ColumnElement[Any]]

Return the completed WHERE clause for this :class:_expression.Select statement.

This assembles the current collection of WHERE criteria into a single :class:_expression.BooleanClauseList construct.

.. versionadded:: 1.4

memoized_attribute

memoized_attribute(
    fget: Callable[..., _T], doc: Optional[str] = None
)

Bases: memoized_property[_T]

A read-only @property that is only evaluated once.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

def __init__(self, fget: Callable[..., _T], doc: Optional[str] = None):
    self.fget = fget
    self.__doc__ = doc or fget.__doc__
    self.__name__ = fget.__name__

memoized_instancemethod classmethod

memoized_instancemethod(fn: _F) -> _F

Decorate a method memoize its return value.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

@classmethod
def memoized_instancemethod(cls, fn: _F) -> _F:
    """Decorate a method memoize its return value.

    :meta private:

    """

    def oneshot(self: Any, *args: Any, **kw: Any) -> Any:
        result = fn(self, *args, **kw)

        def memo(*a, **kw):
            return result

        memo.__name__ = fn.__name__
        memo.__doc__ = fn.__doc__
        self.__dict__[fn.__name__] = memo
        self._memoized_keys |= {fn.__name__}
        return result

    return update_wrapper(oneshot, fn)  # type: ignore

unique_params

unique_params(
    __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.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

params

params(
    __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)

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

compare

compare(other: ClauseElement, **kw: Any) -> bool

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).

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

lateral

lateral(name: Optional[str] = None) -> LateralFromClause

Return a LATERAL alias of this :class:_expression.Selectable.

The return value is the :class:_expression.Lateral construct also provided by the top-level :func:_expression.lateral function.

.. seealso::

:ref:`tutorial_lateral_correlation` -  overview of usage.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def lateral(self, name: Optional[str] = None) -> LateralFromClause:
    """Return a LATERAL alias of this :class:`_expression.Selectable`.

    The return value is the :class:`_expression.Lateral` construct also
    provided by the top-level :func:`_expression.lateral` function.

    .. seealso::

        :ref:`tutorial_lateral_correlation` -  overview of usage.

    """
    return Lateral._factory(self, name)

replace_selectable

replace_selectable(old: FromClause, alias: Alias) -> Self

Replace all occurrences of :class:_expression.FromClause 'old' with the given :class:_expression.Alias object, returning a copy of this :class:_expression.FromClause.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@util.deprecated(
    "1.4",
    message="The :meth:`.Selectable.replace_selectable` method is "
    "deprecated, and will be removed in a future release.  Similar "
    "functionality is available via the sqlalchemy.sql.visitors module.",
)
@util.preload_module("sqlalchemy.sql.util")
def replace_selectable(self, old: FromClause, alias: Alias) -> Self:
    """Replace all occurrences of :class:`_expression.FromClause`
    'old' with the given :class:`_expression.Alias`
    object, returning a copy of this :class:`_expression.FromClause`.

    """
    return util.preloaded.sql_util.ClauseAdapter(alias).traverse(self)

corresponding_column

corresponding_column(
    column: KeyedColumnElement[Any],
    require_embedded: bool = False,
) -> Optional[KeyedColumnElement[Any]]

Given a :class:_expression.ColumnElement, return the exported :class:_expression.ColumnElement object from the :attr:_expression.Selectable.exported_columns collection of this :class:_expression.Selectable which corresponds to that original :class:_expression.ColumnElement via a common ancestor column.

:param column: the target :class:_expression.ColumnElement to be matched.

:param require_embedded: only return corresponding columns for the given :class:_expression.ColumnElement, if the given :class:_expression.ColumnElement is actually present within a sub-element of this :class:_expression.Selectable. Normally the column will match if it merely shares a common ancestor with one of the exported columns of this :class:_expression.Selectable.

.. seealso::

:attr:`_expression.Selectable.exported_columns` - the
:class:`_expression.ColumnCollection`
that is used for the operation.

:meth:`_expression.ColumnCollection.corresponding_column`
- implementation
method.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def corresponding_column(
    self, column: KeyedColumnElement[Any], require_embedded: bool = False
) -> Optional[KeyedColumnElement[Any]]:
    """Given a :class:`_expression.ColumnElement`, return the exported
    :class:`_expression.ColumnElement` object from the
    :attr:`_expression.Selectable.exported_columns`
    collection of this :class:`_expression.Selectable`
    which corresponds to that
    original :class:`_expression.ColumnElement` via a common ancestor
    column.

    :param column: the target :class:`_expression.ColumnElement`
                  to be matched.

    :param require_embedded: only return corresponding columns for
     the given :class:`_expression.ColumnElement`, if the given
     :class:`_expression.ColumnElement`
     is actually present within a sub-element
     of this :class:`_expression.Selectable`.
     Normally the column will match if
     it merely shares a common ancestor with one of the exported
     columns of this :class:`_expression.Selectable`.

    .. seealso::

        :attr:`_expression.Selectable.exported_columns` - the
        :class:`_expression.ColumnCollection`
        that is used for the operation.

        :meth:`_expression.ColumnCollection.corresponding_column`
        - implementation
        method.

    """

    return self.exported_columns.corresponding_column(
        column, require_embedded
    )

add_cte

add_cte(*ctes: CTE, nest_here: bool = False) -> Self

Add one or more :class:_sql.CTE constructs to this statement.

This method will associate the given :class:_sql.CTE constructs with the parent statement such that they will each be unconditionally rendered in the WITH clause of the final statement, even if not referenced elsewhere within the statement or any sub-selects.

The optional :paramref:.HasCTE.add_cte.nest_here parameter when set to True will have the effect that each given :class:_sql.CTE will render in a WITH clause rendered directly along with this statement, rather than being moved to the top of the ultimate rendered statement, even if this statement is rendered as a subquery within a larger statement.

This method has two general uses. One is to embed CTE statements that serve some purpose without being referenced explicitly, such as the use case of embedding a DML statement such as an INSERT or UPDATE as a CTE inline with a primary statement that may draw from its results indirectly. The other is to provide control over the exact placement of a particular series of CTE constructs that should remain rendered directly in terms of a particular statement that may be nested in a larger statement.

E.g.::

from sqlalchemy import table, column, select
t = table('t', column('c1'), column('c2'))

ins = t.insert().values({"c1": "x", "c2": "y"}).cte()

stmt = select(t).add_cte(ins)

Would render::

WITH anon_1 AS
(INSERT INTO t (c1, c2) VALUES (:param_1, :param_2))
SELECT t.c1, t.c2
FROM t

Above, the "anon_1" CTE is not referenced in the SELECT statement, however still accomplishes the task of running an INSERT statement.

Similarly in a DML-related context, using the PostgreSQL :class:_postgresql.Insert construct to generate an "upsert"::

from sqlalchemy import table, column
from sqlalchemy.dialects.postgresql import insert

t = table("t", column("c1"), column("c2"))

delete_statement_cte = (
    t.delete().where(t.c.c1 < 1).cte("deletions")
)

insert_stmt = insert(t).values({"c1": 1, "c2": 2})
update_statement = insert_stmt.on_conflict_do_update(
    index_elements=[t.c.c1],
    set_={
        "c1": insert_stmt.excluded.c1,
        "c2": insert_stmt.excluded.c2,
    },
).add_cte(delete_statement_cte)

print(update_statement)

The above statement renders as::

WITH deletions AS
(DELETE FROM t WHERE t.c1 < %(c1_1)s)
INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s)
ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2

.. versionadded:: 1.4.21

:param *ctes: zero or more :class:.CTE constructs.

.. versionchanged:: 2.0 Multiple CTE instances are accepted

:param nest_here: if True, the given CTE or CTEs will be rendered as though they specified the :paramref:.HasCTE.cte.nesting flag to True when they were added to this :class:.HasCTE. Assuming the given CTEs are not referenced in an outer-enclosing statement as well, the CTEs given should render at the level of this statement when this flag is given.

.. versionadded:: 2.0

.. seealso::

:paramref:`.HasCTE.cte.nesting`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def add_cte(self, *ctes: CTE, nest_here: bool = False) -> Self:
    r"""Add one or more :class:`_sql.CTE` constructs to this statement.

    This method will associate the given :class:`_sql.CTE` constructs with
    the parent statement such that they will each be unconditionally
    rendered in the WITH clause of the final statement, even if not
    referenced elsewhere within the statement or any sub-selects.

    The optional :paramref:`.HasCTE.add_cte.nest_here` parameter when set
    to True will have the effect that each given :class:`_sql.CTE` will
    render in a WITH clause rendered directly along with this statement,
    rather than being moved to the top of the ultimate rendered statement,
    even if this statement is rendered as a subquery within a larger
    statement.

    This method has two general uses. One is to embed CTE statements that
    serve some purpose without being referenced explicitly, such as the use
    case of embedding a DML statement such as an INSERT or UPDATE as a CTE
    inline with a primary statement that may draw from its results
    indirectly.  The other is to provide control over the exact placement
    of a particular series of CTE constructs that should remain rendered
    directly in terms of a particular statement that may be nested in a
    larger statement.

    E.g.::

        from sqlalchemy import table, column, select
        t = table('t', column('c1'), column('c2'))

        ins = t.insert().values({"c1": "x", "c2": "y"}).cte()

        stmt = select(t).add_cte(ins)

    Would render::

        WITH anon_1 AS
        (INSERT INTO t (c1, c2) VALUES (:param_1, :param_2))
        SELECT t.c1, t.c2
        FROM t

    Above, the "anon_1" CTE is not referenced in the SELECT
    statement, however still accomplishes the task of running an INSERT
    statement.

    Similarly in a DML-related context, using the PostgreSQL
    :class:`_postgresql.Insert` construct to generate an "upsert"::

        from sqlalchemy import table, column
        from sqlalchemy.dialects.postgresql import insert

        t = table("t", column("c1"), column("c2"))

        delete_statement_cte = (
            t.delete().where(t.c.c1 < 1).cte("deletions")
        )

        insert_stmt = insert(t).values({"c1": 1, "c2": 2})
        update_statement = insert_stmt.on_conflict_do_update(
            index_elements=[t.c.c1],
            set_={
                "c1": insert_stmt.excluded.c1,
                "c2": insert_stmt.excluded.c2,
            },
        ).add_cte(delete_statement_cte)

        print(update_statement)

    The above statement renders as::

        WITH deletions AS
        (DELETE FROM t WHERE t.c1 < %(c1_1)s)
        INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s)
        ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2

    .. versionadded:: 1.4.21

    :param \*ctes: zero or more :class:`.CTE` constructs.

     .. versionchanged:: 2.0  Multiple CTE instances are accepted

    :param nest_here: if True, the given CTE or CTEs will be rendered
     as though they specified the :paramref:`.HasCTE.cte.nesting` flag
     to ``True`` when they were added to this :class:`.HasCTE`.
     Assuming the given CTEs are not referenced in an outer-enclosing
     statement as well, the CTEs given should render at the level of
     this statement when this flag is given.

     .. versionadded:: 2.0

     .. seealso::

        :paramref:`.HasCTE.cte.nesting`


    """
    opt = _CTEOpts(
        nest_here,
    )
    for cte in ctes:
        cte = coercions.expect(roles.IsCTERole, cte)
        self._independent_ctes += (cte,)
        self._independent_ctes_opts += (opt,)
    return self

cte

cte(
    name: Optional[str] = None,
    recursive: bool = False,
    nesting: bool = False,
) -> CTE

Return a new :class:_expression.CTE, or Common Table Expression instance.

Common table expressions are a SQL standard whereby SELECT statements can draw upon secondary statements specified along with the primary statement, using a clause called "WITH". Special semantics regarding UNION can also be employed to allow "recursive" queries, where a SELECT statement can draw upon the set of rows that have previously been selected.

CTEs can also be applied to DML constructs UPDATE, INSERT and DELETE on some databases, both as a source of CTE rows when combined with RETURNING, as well as a consumer of CTE rows.

SQLAlchemy detects :class:_expression.CTE objects, which are treated similarly to :class:_expression.Alias objects, as special elements to be delivered to the FROM clause of the statement as well as to a WITH clause at the top of the statement.

For special prefixes such as PostgreSQL "MATERIALIZED" and "NOT MATERIALIZED", the :meth:_expression.CTE.prefix_with method may be used to establish these.

.. versionchanged:: 1.3.13 Added support for prefixes. In particular - MATERIALIZED and NOT MATERIALIZED.

:param name: name given to the common table expression. Like :meth:_expression.FromClause.alias, the name can be left as None in which case an anonymous symbol will be used at query compile time. :param recursive: if True, will render WITH RECURSIVE. A recursive common table expression is intended to be used in conjunction with UNION ALL in order to derive rows from those already selected. :param nesting: if True, will render the CTE locally to the statement in which it is referenced. For more complex scenarios, the :meth:.HasCTE.add_cte method using the :paramref:.HasCTE.add_cte.nest_here parameter may also be used to more carefully control the exact placement of a particular CTE.

.. versionadded:: 1.4.24

.. seealso::

:meth:`.HasCTE.add_cte`

The following examples include two from PostgreSQL's documentation at https://www.postgresql.org/docs/current/static/queries-with.html, as well as additional examples.

Example 1, non recursive::

from sqlalchemy import (Table, Column, String, Integer,
                        MetaData, select, func)

metadata = MetaData()

orders = Table('orders', metadata,
    Column('region', String),
    Column('amount', Integer),
    Column('product', String),
    Column('quantity', Integer)
)

regional_sales = select(
                    orders.c.region,
                    func.sum(orders.c.amount).label('total_sales')
                ).group_by(orders.c.region).cte("regional_sales")


top_regions = select(regional_sales.c.region).\
        where(
            regional_sales.c.total_sales >
            select(
                func.sum(regional_sales.c.total_sales) / 10
            )
        ).cte("top_regions")

statement = select(
            orders.c.region,
            orders.c.product,
            func.sum(orders.c.quantity).label("product_units"),
            func.sum(orders.c.amount).label("product_sales")
    ).where(orders.c.region.in_(
        select(top_regions.c.region)
    )).group_by(orders.c.region, orders.c.product)

result = conn.execute(statement).fetchall()

Example 2, WITH RECURSIVE::

from sqlalchemy import (Table, Column, String, Integer,
                        MetaData, select, func)

metadata = MetaData()

parts = Table('parts', metadata,
    Column('part', String),
    Column('sub_part', String),
    Column('quantity', Integer),
)

included_parts = select(\
    parts.c.sub_part, parts.c.part, parts.c.quantity\
    ).\
    where(parts.c.part=='our part').\
    cte(recursive=True)


incl_alias = included_parts.alias()
parts_alias = parts.alias()
included_parts = included_parts.union_all(
    select(
        parts_alias.c.sub_part,
        parts_alias.c.part,
        parts_alias.c.quantity
    ).\
    where(parts_alias.c.part==incl_alias.c.sub_part)
)

statement = select(
            included_parts.c.sub_part,
            func.sum(included_parts.c.quantity).
              label('total_quantity')
        ).\
        group_by(included_parts.c.sub_part)

result = conn.execute(statement).fetchall()

Example 3, an upsert using UPDATE and INSERT with CTEs::

from datetime import date
from sqlalchemy import (MetaData, Table, Column, Integer,
                        Date, select, literal, and_, exists)

metadata = MetaData()

visitors = Table('visitors', metadata,
    Column('product_id', Integer, primary_key=True),
    Column('date', Date, primary_key=True),
    Column('count', Integer),
)

# add 5 visitors for the product_id == 1
product_id = 1
day = date.today()
count = 5

update_cte = (
    visitors.update()
    .where(and_(visitors.c.product_id == product_id,
                visitors.c.date == day))
    .values(count=visitors.c.count + count)
    .returning(literal(1))
    .cte('update_cte')
)

upsert = visitors.insert().from_select(
    [visitors.c.product_id, visitors.c.date, visitors.c.count],
    select(literal(product_id), literal(day), literal(count))
        .where(~exists(update_cte.select()))
)

connection.execute(upsert)

Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above)::

value_a = select(
    literal("root").label("n")
).cte("value_a")

# A nested CTE with the same name as the root one
value_a_nested = select(
    literal("nesting").label("n")
).cte("value_a", nesting=True)

# Nesting CTEs takes ascendency locally
# over the CTEs at a higher level
value_b = select(value_a_nested.c.n).cte("value_b")

value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

The above query will render the second CTE nested inside the first, shown with inline parameters below as::

WITH
    value_a AS
        (SELECT 'root' AS n),
    value_b AS
        (WITH value_a AS
            (SELECT 'nesting' AS n)
        SELECT value_a.n AS n FROM value_a)
SELECT value_a.n AS a, value_b.n AS b
FROM value_a, value_b

The same CTE can be set up using the :meth:.HasCTE.add_cte method as follows (SQLAlchemy 2.0 and above)::

value_a = select(
    literal("root").label("n")
).cte("value_a")

# A nested CTE with the same name as the root one
value_a_nested = select(
    literal("nesting").label("n")
).cte("value_a")

# Nesting CTEs takes ascendency locally
# over the CTEs at a higher level
value_b = (
    select(value_a_nested.c.n).
    add_cte(value_a_nested, nest_here=True).
    cte("value_b")
)

value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above)::

edge = Table(
    "edge",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("left", Integer),
    Column("right", Integer),
)

root_node = select(literal(1).label("node")).cte(
    "nodes", recursive=True
)

left_edge = select(edge.c.left).join(
    root_node, edge.c.right == root_node.c.node
)
right_edge = select(edge.c.right).join(
    root_node, edge.c.left == root_node.c.node
)

subgraph_cte = root_node.union(left_edge, right_edge)

subgraph = select(subgraph_cte)

The above query will render 2 UNIONs inside the recursive CTE::

WITH RECURSIVE nodes(node) AS (
        SELECT 1 AS node
    UNION
        SELECT edge."left" AS "left"
        FROM edge JOIN nodes ON edge."right" = nodes.node
    UNION
        SELECT edge."right" AS "right"
        FROM edge JOIN nodes ON edge."left" = nodes.node
)
SELECT nodes.node FROM nodes

.. seealso::

:meth:`_orm.Query.cte` - ORM version of
:meth:`_expression.HasCTE.cte`.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def cte(
    self,
    name: Optional[str] = None,
    recursive: bool = False,
    nesting: bool = False,
) -> CTE:
    r"""Return a new :class:`_expression.CTE`,
    or Common Table Expression instance.

    Common table expressions are a SQL standard whereby SELECT
    statements can draw upon secondary statements specified along
    with the primary statement, using a clause called "WITH".
    Special semantics regarding UNION can also be employed to
    allow "recursive" queries, where a SELECT statement can draw
    upon the set of rows that have previously been selected.

    CTEs can also be applied to DML constructs UPDATE, INSERT
    and DELETE on some databases, both as a source of CTE rows
    when combined with RETURNING, as well as a consumer of
    CTE rows.

    SQLAlchemy detects :class:`_expression.CTE` objects, which are treated
    similarly to :class:`_expression.Alias` objects, as special elements
    to be delivered to the FROM clause of the statement as well
    as to a WITH clause at the top of the statement.

    For special prefixes such as PostgreSQL "MATERIALIZED" and
    "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`
    method may be
    used to establish these.

    .. versionchanged:: 1.3.13 Added support for prefixes.
       In particular - MATERIALIZED and NOT MATERIALIZED.

    :param name: name given to the common table expression.  Like
     :meth:`_expression.FromClause.alias`, the name can be left as
     ``None`` in which case an anonymous symbol will be used at query
     compile time.
    :param recursive: if ``True``, will render ``WITH RECURSIVE``.
     A recursive common table expression is intended to be used in
     conjunction with UNION ALL in order to derive rows
     from those already selected.
    :param nesting: if ``True``, will render the CTE locally to the
     statement in which it is referenced.   For more complex scenarios,
     the :meth:`.HasCTE.add_cte` method using the
     :paramref:`.HasCTE.add_cte.nest_here`
     parameter may also be used to more carefully
     control the exact placement of a particular CTE.

     .. versionadded:: 1.4.24

     .. seealso::

        :meth:`.HasCTE.add_cte`

    The following examples include two from PostgreSQL's documentation at
    https://www.postgresql.org/docs/current/static/queries-with.html,
    as well as additional examples.

    Example 1, non recursive::

        from sqlalchemy import (Table, Column, String, Integer,
                                MetaData, select, func)

        metadata = MetaData()

        orders = Table('orders', metadata,
            Column('region', String),
            Column('amount', Integer),
            Column('product', String),
            Column('quantity', Integer)
        )

        regional_sales = select(
                            orders.c.region,
                            func.sum(orders.c.amount).label('total_sales')
                        ).group_by(orders.c.region).cte("regional_sales")


        top_regions = select(regional_sales.c.region).\
                where(
                    regional_sales.c.total_sales >
                    select(
                        func.sum(regional_sales.c.total_sales) / 10
                    )
                ).cte("top_regions")

        statement = select(
                    orders.c.region,
                    orders.c.product,
                    func.sum(orders.c.quantity).label("product_units"),
                    func.sum(orders.c.amount).label("product_sales")
            ).where(orders.c.region.in_(
                select(top_regions.c.region)
            )).group_by(orders.c.region, orders.c.product)

        result = conn.execute(statement).fetchall()

    Example 2, WITH RECURSIVE::

        from sqlalchemy import (Table, Column, String, Integer,
                                MetaData, select, func)

        metadata = MetaData()

        parts = Table('parts', metadata,
            Column('part', String),
            Column('sub_part', String),
            Column('quantity', Integer),
        )

        included_parts = select(\
            parts.c.sub_part, parts.c.part, parts.c.quantity\
            ).\
            where(parts.c.part=='our part').\
            cte(recursive=True)


        incl_alias = included_parts.alias()
        parts_alias = parts.alias()
        included_parts = included_parts.union_all(
            select(
                parts_alias.c.sub_part,
                parts_alias.c.part,
                parts_alias.c.quantity
            ).\
            where(parts_alias.c.part==incl_alias.c.sub_part)
        )

        statement = select(
                    included_parts.c.sub_part,
                    func.sum(included_parts.c.quantity).
                      label('total_quantity')
                ).\
                group_by(included_parts.c.sub_part)

        result = conn.execute(statement).fetchall()

    Example 3, an upsert using UPDATE and INSERT with CTEs::

        from datetime import date
        from sqlalchemy import (MetaData, Table, Column, Integer,
                                Date, select, literal, and_, exists)

        metadata = MetaData()

        visitors = Table('visitors', metadata,
            Column('product_id', Integer, primary_key=True),
            Column('date', Date, primary_key=True),
            Column('count', Integer),
        )

        # add 5 visitors for the product_id == 1
        product_id = 1
        day = date.today()
        count = 5

        update_cte = (
            visitors.update()
            .where(and_(visitors.c.product_id == product_id,
                        visitors.c.date == day))
            .values(count=visitors.c.count + count)
            .returning(literal(1))
            .cte('update_cte')
        )

        upsert = visitors.insert().from_select(
            [visitors.c.product_id, visitors.c.date, visitors.c.count],
            select(literal(product_id), literal(day), literal(count))
                .where(~exists(update_cte.select()))
        )

        connection.execute(upsert)

    Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above)::

        value_a = select(
            literal("root").label("n")
        ).cte("value_a")

        # A nested CTE with the same name as the root one
        value_a_nested = select(
            literal("nesting").label("n")
        ).cte("value_a", nesting=True)

        # Nesting CTEs takes ascendency locally
        # over the CTEs at a higher level
        value_b = select(value_a_nested.c.n).cte("value_b")

        value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

    The above query will render the second CTE nested inside the first,
    shown with inline parameters below as::

        WITH
            value_a AS
                (SELECT 'root' AS n),
            value_b AS
                (WITH value_a AS
                    (SELECT 'nesting' AS n)
                SELECT value_a.n AS n FROM value_a)
        SELECT value_a.n AS a, value_b.n AS b
        FROM value_a, value_b

    The same CTE can be set up using the :meth:`.HasCTE.add_cte` method
    as follows (SQLAlchemy 2.0 and above)::

        value_a = select(
            literal("root").label("n")
        ).cte("value_a")

        # A nested CTE with the same name as the root one
        value_a_nested = select(
            literal("nesting").label("n")
        ).cte("value_a")

        # Nesting CTEs takes ascendency locally
        # over the CTEs at a higher level
        value_b = (
            select(value_a_nested.c.n).
            add_cte(value_a_nested, nest_here=True).
            cte("value_b")
        )

        value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

    Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above)::

        edge = Table(
            "edge",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("left", Integer),
            Column("right", Integer),
        )

        root_node = select(literal(1).label("node")).cte(
            "nodes", recursive=True
        )

        left_edge = select(edge.c.left).join(
            root_node, edge.c.right == root_node.c.node
        )
        right_edge = select(edge.c.right).join(
            root_node, edge.c.left == root_node.c.node
        )

        subgraph_cte = root_node.union(left_edge, right_edge)

        subgraph = select(subgraph_cte)

    The above query will render 2 UNIONs inside the recursive CTE::

        WITH RECURSIVE nodes(node) AS (
                SELECT 1 AS node
            UNION
                SELECT edge."left" AS "left"
                FROM edge JOIN nodes ON edge."right" = nodes.node
            UNION
                SELECT edge."right" AS "right"
                FROM edge JOIN nodes ON edge."left" = nodes.node
        )
        SELECT nodes.node FROM nodes

    .. seealso::

        :meth:`_orm.Query.cte` - ORM version of
        :meth:`_expression.HasCTE.cte`.

    """
    return CTE._construct(
        self, name=name, recursive=recursive, nesting=nesting
    )

options

options(*options: ExecutableOption) -> Self

Apply options to this statement.

In the general sense, options are any kind of Python object that can be interpreted by the SQL compiler for the statement. These options can be consumed by specific dialects or specific kinds of compilers.

The most commonly known kind of option are the ORM level options that apply "eager load" and other loading behaviors to an ORM query. However, options can theoretically be used for many other purposes.

For background on specific kinds of options for specific kinds of statements, refer to the documentation for those option objects.

.. versionchanged:: 1.4 - added :meth:.Executable.options to Core statement objects towards the goal of allowing unified Core / ORM querying capabilities.

.. seealso::

:ref:`loading_columns` - refers to options specific to the usage
of ORM queries

:ref:`relationship_loader_options` - refers to options specific
to the usage of ORM queries

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py

@_generative
def options(self, *options: ExecutableOption) -> Self:
    """Apply options to this statement.

    In the general sense, options are any kind of Python object
    that can be interpreted by the SQL compiler for the statement.
    These options can be consumed by specific dialects or specific kinds
    of compilers.

    The most commonly known kind of option are the ORM level options
    that apply "eager load" and other loading behaviors to an ORM
    query.   However, options can theoretically be used for many other
    purposes.

    For background on specific kinds of options for specific kinds of
    statements, refer to the documentation for those option objects.

    .. versionchanged:: 1.4 - added :meth:`.Executable.options` to
       Core statement objects towards the goal of allowing unified
       Core / ORM querying capabilities.

    .. seealso::

        :ref:`loading_columns` - refers to options specific to the usage
        of ORM queries

        :ref:`relationship_loader_options` - refers to options specific
        to the usage of ORM queries

    """
    self._with_options += tuple(
        coercions.expect(roles.ExecutableOptionRole, opt)
        for opt in options
    )
    return self

execution_options

execution_options(
    *,
    compiled_cache: Optional[CompiledCacheType] = ...,
    logging_token: str = ...,
    isolation_level: IsolationLevel = ...,
    no_parameters: bool = False,
    stream_results: bool = False,
    max_row_buffer: int = ...,
    yield_per: int = ...,
    insertmanyvalues_page_size: int = ...,
    schema_translate_map: Optional[
        SchemaTranslateMapType
    ] = ...,
    populate_existing: bool = False,
    autoflush: bool = False,
    synchronize_session: SynchronizeSessionArgument = ...,
    dml_strategy: DMLStrategyArgument = ...,
    render_nulls: bool = ...,
    is_delete_using: bool = ...,
    is_update_from: bool = ...,
    **opt: Any,
) -> Self

execution_options(**opt: Any) -> Self

execution_options(**kw: Any) -> Self

Set non-SQL options for the statement which take effect during execution.

Execution options can be set at many scopes, including per-statement, per-connection, or per execution, using methods such as :meth:_engine.Connection.execution_options and parameters which accept a dictionary of options such as :paramref:_engine.Connection.execute.execution_options and :paramref:_orm.Session.execute.execution_options.

The primary characteristic of an execution option, as opposed to other kinds of options such as ORM loader options, is that execution options never affect the compiled SQL of a query, only things that affect how the SQL statement itself is invoked or how results are fetched. That is, execution options are not part of what's accommodated by SQL compilation nor are they considered part of the cached state of a statement.

The :meth:_sql.Executable.execution_options method is :term:generative, as is the case for the method as applied to the :class:_engine.Engine and :class:_orm.Query objects, which means when the method is called, a copy of the object is returned, which applies the given parameters to that new copy, but leaves the original unchanged::

statement = select(table.c.x, table.c.y)
new_statement = statement.execution_options(my_option=True)

An exception to this behavior is the :class:_engine.Connection object, where the :meth:_engine.Connection.execution_options method is explicitly not generative.

The kinds of options that may be passed to :meth:_sql.Executable.execution_options and other related methods and parameter dictionaries include parameters that are explicitly consumed by SQLAlchemy Core or ORM, as well as arbitrary keyword arguments not defined by SQLAlchemy, which means the methods and/or parameter dictionaries may be used for user-defined parameters that interact with custom code, which may access the parameters using methods such as :meth:_sql.Executable.get_execution_options and :meth:_engine.Connection.get_execution_options, or within selected event hooks using a dedicated execution_options event parameter such as :paramref:_events.ConnectionEvents.before_execute.execution_options or :attr:_orm.ORMExecuteState.execution_options, e.g.::

 from sqlalchemy import event

 @event.listens_for(some_engine, "before_execute")
 def _process_opt(conn, statement, multiparams, params, execution_options):
     "run a SQL function before invoking a statement"

     if execution_options.get("do_special_thing", False):
         conn.exec_driver_sql("run_special_function()")

Within the scope of options that are explicitly recognized by SQLAlchemy, most apply to specific classes of objects and not others. The most common execution options include:

• :paramref:_engine.Connection.execution_options.isolation_level - sets the isolation level for a connection or a class of connections via an :class:_engine.Engine. This option is accepted only by :class:_engine.Connection or :class:_engine.Engine.

• :paramref:_engine.Connection.execution_options.stream_results - indicates results should be fetched using a server side cursor; this option is accepted by :class:_engine.Connection, by the :paramref:_engine.Connection.execute.execution_options parameter on :meth:_engine.Connection.execute, and additionally by :meth:_sql.Executable.execution_options on a SQL statement object, as well as by ORM constructs like :meth:_orm.Session.execute.

• :paramref:_engine.Connection.execution_options.compiled_cache - indicates a dictionary that will serve as the :ref:SQL compilation cache <sql_caching> for a :class:_engine.Connection or :class:_engine.Engine, as well as for ORM methods like :meth:_orm.Session.execute. Can be passed as None to disable caching for statements. This option is not accepted by :meth:_sql.Executable.execution_options as it is inadvisable to carry along a compilation cache within a statement object.

• :paramref:_engine.Connection.execution_options.schema_translate_map

• a mapping of schema names used by the :ref:Schema Translate Map <schema_translating> feature, accepted by :class:_engine.Connection, :class:_engine.Engine, :class:_sql.Executable, as well as by ORM constructs like :meth:_orm.Session.execute.

.. seealso::

:meth:`_engine.Connection.execution_options`

:paramref:`_engine.Connection.execute.execution_options`

:paramref:`_orm.Session.execute.execution_options`

:ref:`orm_queryguide_execution_options` - documentation on all
ORM-specific execution options

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py

@_generative
def execution_options(self, **kw: Any) -> Self:
    """Set non-SQL options for the statement which take effect during
    execution.

    Execution options can be set at many scopes, including per-statement,
    per-connection, or per execution, using methods such as
    :meth:`_engine.Connection.execution_options` and parameters which
    accept a dictionary of options such as
    :paramref:`_engine.Connection.execute.execution_options` and
    :paramref:`_orm.Session.execute.execution_options`.

    The primary characteristic of an execution option, as opposed to
    other kinds of options such as ORM loader options, is that
    **execution options never affect the compiled SQL of a query, only
    things that affect how the SQL statement itself is invoked or how
    results are fetched**.  That is, execution options are not part of
    what's accommodated by SQL compilation nor are they considered part of
    the cached state of a statement.

    The :meth:`_sql.Executable.execution_options` method is
    :term:`generative`, as
    is the case for the method as applied to the :class:`_engine.Engine`
    and :class:`_orm.Query` objects, which means when the method is called,
    a copy of the object is returned, which applies the given parameters to
    that new copy, but leaves the original unchanged::

        statement = select(table.c.x, table.c.y)
        new_statement = statement.execution_options(my_option=True)

    An exception to this behavior is the :class:`_engine.Connection`
    object, where the :meth:`_engine.Connection.execution_options` method
    is explicitly **not** generative.

    The kinds of options that may be passed to
    :meth:`_sql.Executable.execution_options` and other related methods and
    parameter dictionaries include parameters that are explicitly consumed
    by SQLAlchemy Core or ORM, as well as arbitrary keyword arguments not
    defined by SQLAlchemy, which means the methods and/or parameter
    dictionaries may be used for user-defined parameters that interact with
    custom code, which may access the parameters using methods such as
    :meth:`_sql.Executable.get_execution_options` and
    :meth:`_engine.Connection.get_execution_options`, or within selected
    event hooks using a dedicated ``execution_options`` event parameter
    such as
    :paramref:`_events.ConnectionEvents.before_execute.execution_options`
    or :attr:`_orm.ORMExecuteState.execution_options`, e.g.::

         from sqlalchemy import event

         @event.listens_for(some_engine, "before_execute")
         def _process_opt(conn, statement, multiparams, params, execution_options):
             "run a SQL function before invoking a statement"

             if execution_options.get("do_special_thing", False):
                 conn.exec_driver_sql("run_special_function()")

    Within the scope of options that are explicitly recognized by
    SQLAlchemy, most apply to specific classes of objects and not others.
    The most common execution options include:

    * :paramref:`_engine.Connection.execution_options.isolation_level` -
      sets the isolation level for a connection or a class of connections
      via an :class:`_engine.Engine`.  This option is accepted only
      by :class:`_engine.Connection` or :class:`_engine.Engine`.

    * :paramref:`_engine.Connection.execution_options.stream_results` -
      indicates results should be fetched using a server side cursor;
      this option is accepted by :class:`_engine.Connection`, by the
      :paramref:`_engine.Connection.execute.execution_options` parameter
      on :meth:`_engine.Connection.execute`, and additionally by
      :meth:`_sql.Executable.execution_options` on a SQL statement object,
      as well as by ORM constructs like :meth:`_orm.Session.execute`.

    * :paramref:`_engine.Connection.execution_options.compiled_cache` -
      indicates a dictionary that will serve as the
      :ref:`SQL compilation cache <sql_caching>`
      for a :class:`_engine.Connection` or :class:`_engine.Engine`, as
      well as for ORM methods like :meth:`_orm.Session.execute`.
      Can be passed as ``None`` to disable caching for statements.
      This option is not accepted by
      :meth:`_sql.Executable.execution_options` as it is inadvisable to
      carry along a compilation cache within a statement object.

    * :paramref:`_engine.Connection.execution_options.schema_translate_map`
      - a mapping of schema names used by the
      :ref:`Schema Translate Map <schema_translating>` feature, accepted
      by :class:`_engine.Connection`, :class:`_engine.Engine`,
      :class:`_sql.Executable`, as well as by ORM constructs
      like :meth:`_orm.Session.execute`.

    .. seealso::

        :meth:`_engine.Connection.execution_options`

        :paramref:`_engine.Connection.execute.execution_options`

        :paramref:`_orm.Session.execute.execution_options`

        :ref:`orm_queryguide_execution_options` - documentation on all
        ORM-specific execution options

    """  # noqa: E501
    if "isolation_level" in kw:
        raise exc.ArgumentError(
            "'isolation_level' execution option may only be specified "
            "on Connection.execution_options(), or "
            "per-engine using the isolation_level "
            "argument to create_engine()."
        )
    if "compiled_cache" in kw:
        raise exc.ArgumentError(
            "'compiled_cache' execution option may only be specified "
            "on Connection.execution_options(), not per statement."
        )
    self._execution_options = self._execution_options.union(kw)
    return self

get_execution_options

get_execution_options() -> _ExecuteOptions

Get the non-SQL options which will take effect during execution.

.. versionadded:: 1.3

.. seealso::

:meth:`.Executable.execution_options`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/base.py

def get_execution_options(self) -> _ExecuteOptions:
    """Get the non-SQL options which will take effect during execution.

    .. versionadded:: 1.3

    .. seealso::

        :meth:`.Executable.execution_options`
    """
    return self._execution_options

subquery

subquery(name: Optional[str] = None) -> Subquery

Return a subquery of this :class:_expression.SelectBase.

A subquery is from a SQL perspective a parenthesized, named construct that can be placed in the FROM clause of another SELECT statement.

Given a SELECT statement such as::

stmt = select(table.c.id, table.c.name)

The above statement might look like::

SELECT table.id, table.name FROM table

The subquery form by itself renders the same way, however when embedded into the FROM clause of another SELECT statement, it becomes a named sub-element::

subq = stmt.subquery()
new_stmt = select(subq)

The above renders as::

SELECT anon_1.id, anon_1.name
FROM (SELECT table.id, table.name FROM table) AS anon_1

Historically, :meth:_expression.SelectBase.subquery is equivalent to calling the :meth:_expression.FromClause.alias method on a FROM object; however, as a :class:_expression.SelectBase object is not directly FROM object, the :meth:_expression.SelectBase.subquery method provides clearer semantics.

.. versionadded:: 1.4

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def subquery(self, name: Optional[str] = None) -> Subquery:
    """Return a subquery of this :class:`_expression.SelectBase`.

    A subquery is from a SQL perspective a parenthesized, named
    construct that can be placed in the FROM clause of another
    SELECT statement.

    Given a SELECT statement such as::

        stmt = select(table.c.id, table.c.name)

    The above statement might look like::

        SELECT table.id, table.name FROM table

    The subquery form by itself renders the same way, however when
    embedded into the FROM clause of another SELECT statement, it becomes
    a named sub-element::

        subq = stmt.subquery()
        new_stmt = select(subq)

    The above renders as::

        SELECT anon_1.id, anon_1.name
        FROM (SELECT table.id, table.name FROM table) AS anon_1

    Historically, :meth:`_expression.SelectBase.subquery`
    is equivalent to calling
    the :meth:`_expression.FromClause.alias`
    method on a FROM object; however,
    as a :class:`_expression.SelectBase`
    object is not directly  FROM object,
    the :meth:`_expression.SelectBase.subquery`
    method provides clearer semantics.

    .. versionadded:: 1.4

    """

    return Subquery._construct(
        self._ensure_disambiguated_names(), name=name
    )

get_label_style

get_label_style() -> SelectLabelStyle

Retrieve the current label style.

.. versionadded:: 1.4

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def get_label_style(self) -> SelectLabelStyle:
    """
    Retrieve the current label style.

    .. versionadded:: 1.4

    """
    return self._label_style

set_label_style

set_label_style(style: SelectLabelStyle) -> Self

Return a new selectable with the specified label style.

There are three "label styles" available, :attr:_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY, :attr:_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL, and :attr:_sql.SelectLabelStyle.LABEL_STYLE_NONE. The default style is :attr:_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL.

In modern SQLAlchemy, there is not generally a need to change the labeling style, as per-expression labels are more effectively used by making use of the :meth:_sql.ColumnElement.label method. In past versions, :data:_sql.LABEL_STYLE_TABLENAME_PLUS_COL was used to disambiguate same-named columns from different tables, aliases, or subqueries; the newer :data:_sql.LABEL_STYLE_DISAMBIGUATE_ONLY now applies labels only to names that conflict with an existing name so that the impact of this labeling is minimal.

The rationale for disambiguation is mostly so that all column expressions are available from a given :attr:_sql.FromClause.c collection when a subquery is created.

.. versionadded:: 1.4 - the :meth:_sql.GenerativeSelect.set_label_style method replaces the previous combination of .apply_labels(), .with_labels() and use_labels=True methods and/or parameters.

.. seealso::

:data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

:data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

:data:`_sql.LABEL_STYLE_NONE`

:data:`_sql.LABEL_STYLE_DEFAULT`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def set_label_style(self, style: SelectLabelStyle) -> Self:
    """Return a new selectable with the specified label style.

    There are three "label styles" available,
    :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`,
    :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`, and
    :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`.   The default style is
    :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`.

    In modern SQLAlchemy, there is not generally a need to change the
    labeling style, as per-expression labels are more effectively used by
    making use of the :meth:`_sql.ColumnElement.label` method. In past
    versions, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` was used to
    disambiguate same-named columns from different tables, aliases, or
    subqueries; the newer :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` now
    applies labels only to names that conflict with an existing name so
    that the impact of this labeling is minimal.

    The rationale for disambiguation is mostly so that all column
    expressions are available from a given :attr:`_sql.FromClause.c`
    collection when a subquery is created.

    .. versionadded:: 1.4 - the
        :meth:`_sql.GenerativeSelect.set_label_style` method replaces the
        previous combination of ``.apply_labels()``, ``.with_labels()`` and
        ``use_labels=True`` methods and/or parameters.

    .. seealso::

        :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

        :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

        :data:`_sql.LABEL_STYLE_NONE`

        :data:`_sql.LABEL_STYLE_DEFAULT`

    """
    if self._label_style is not style:
        self = self._generate()
        self._label_style = style
    return self

exists

exists() -> Exists

Return an :class:_sql.Exists representation of this selectable, which can be used as a column expression.

The returned object is an instance of :class:_sql.Exists.

.. seealso::

:func:`_sql.exists`

:ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.

.. versionadded:: 1.4

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def exists(self) -> Exists:
    """Return an :class:`_sql.Exists` representation of this selectable,
    which can be used as a column expression.

    The returned object is an instance of :class:`_sql.Exists`.

    .. seealso::

        :func:`_sql.exists`

        :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.

    .. versionadded:: 1.4

    """
    return Exists(self)

label

label(name: Optional[str]) -> Label[Any]

Return a 'scalar' representation of this selectable, embedded as a subquery with a label.

.. seealso::

:meth:`_expression.SelectBase.scalar_subquery`.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def label(self, name: Optional[str]) -> Label[Any]:
    """Return a 'scalar' representation of this selectable, embedded as a
    subquery with a label.

    .. seealso::

        :meth:`_expression.SelectBase.scalar_subquery`.

    """
    return self.scalar_subquery().label(name)

alias

alias(
    name: Optional[str] = None, flat: bool = False
) -> Subquery

Return a named subquery against this :class:_expression.SelectBase.

For a :class:_expression.SelectBase (as opposed to a :class:_expression.FromClause), this returns a :class:.Subquery object which behaves mostly the same as the :class:_expression.Alias object that is used with a :class:_expression.FromClause.

.. versionchanged:: 1.4 The :meth:_expression.SelectBase.alias method is now a synonym for the :meth:_expression.SelectBase.subquery method.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def alias(
    self, name: Optional[str] = None, flat: bool = False
) -> Subquery:
    """Return a named subquery against this
    :class:`_expression.SelectBase`.

    For a :class:`_expression.SelectBase` (as opposed to a
    :class:`_expression.FromClause`),
    this returns a :class:`.Subquery` object which behaves mostly the
    same as the :class:`_expression.Alias` object that is used with a
    :class:`_expression.FromClause`.

    .. versionchanged:: 1.4 The :meth:`_expression.SelectBase.alias`
       method is now
       a synonym for the :meth:`_expression.SelectBase.subquery` method.

    """
    return self.subquery(name=name)

with_for_update

with_for_update(
    *,
    nowait: bool = False,
    read: bool = False,
    of: Optional[_ForUpdateOfArgument] = None,
    skip_locked: bool = False,
    key_share: bool = False,
) -> Self

Specify a FOR UPDATE clause for this :class:_expression.GenerativeSelect.

E.g.::

stmt = select(table).with_for_update(nowait=True)

On a database like PostgreSQL or Oracle, the above would render a statement like::

SELECT table.a, table.b FROM table FOR UPDATE NOWAIT

on other backends, the nowait option is ignored and instead would produce::

SELECT table.a, table.b FROM table FOR UPDATE

When called with no arguments, the statement will render with the suffix FOR UPDATE. Additional arguments can then be provided which allow for common database-specific variants.

:param nowait: boolean; will render FOR UPDATE NOWAIT on Oracle and PostgreSQL dialects.

:param read: boolean; will render LOCK IN SHARE MODE on MySQL, FOR SHARE on PostgreSQL. On PostgreSQL, when combined with nowait, will render FOR SHARE NOWAIT.

:param of: SQL expression or list of SQL expression elements, (typically :class:_schema.Column objects or a compatible expression, for some backends may also be a table expression) which will render into a FOR UPDATE OF clause; supported by PostgreSQL, Oracle, some MySQL versions and possibly others. May render as a table or as a column depending on backend.

:param skip_locked: boolean, will render FOR UPDATE SKIP LOCKED on Oracle and PostgreSQL dialects or FOR SHARE SKIP LOCKED if read=True is also specified.

:param key_share: boolean, will render FOR NO KEY UPDATE, or if combined with read=True will render FOR KEY SHARE, on the PostgreSQL dialect.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def with_for_update(
    self,
    *,
    nowait: bool = False,
    read: bool = False,
    of: Optional[_ForUpdateOfArgument] = None,
    skip_locked: bool = False,
    key_share: bool = False,
) -> Self:
    """Specify a ``FOR UPDATE`` clause for this
    :class:`_expression.GenerativeSelect`.

    E.g.::

        stmt = select(table).with_for_update(nowait=True)

    On a database like PostgreSQL or Oracle, the above would render a
    statement like::

        SELECT table.a, table.b FROM table FOR UPDATE NOWAIT

    on other backends, the ``nowait`` option is ignored and instead
    would produce::

        SELECT table.a, table.b FROM table FOR UPDATE

    When called with no arguments, the statement will render with
    the suffix ``FOR UPDATE``.   Additional arguments can then be
    provided which allow for common database-specific
    variants.

    :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle
     and PostgreSQL dialects.

    :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,
     ``FOR SHARE`` on PostgreSQL.  On PostgreSQL, when combined with
     ``nowait``, will render ``FOR SHARE NOWAIT``.

    :param of: SQL expression or list of SQL expression elements,
     (typically :class:`_schema.Column` objects or a compatible expression,
     for some backends may also be a table expression) which will render
     into a ``FOR UPDATE OF`` clause; supported by PostgreSQL, Oracle, some
     MySQL versions and possibly others. May render as a table or as a
     column depending on backend.

    :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED``
     on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if
     ``read=True`` is also specified.

    :param key_share: boolean, will render ``FOR NO KEY UPDATE``,
     or if combined with ``read=True`` will render ``FOR KEY SHARE``,
     on the PostgreSQL dialect.

    """
    self._for_update_arg = ForUpdateArg(
        nowait=nowait,
        read=read,
        of=of,
        skip_locked=skip_locked,
        key_share=key_share,
    )
    return self

limit

limit(limit: _LimitOffsetType) -> Self

Return a new selectable with the given LIMIT criterion applied.

This is a numerical value which usually renders as a LIMIT expression in the resulting select. Backends that don't support LIMIT will attempt to provide similar functionality.

.. note::

The :meth:_sql.GenerativeSelect.limit method will replace any clause applied with :meth:_sql.GenerativeSelect.fetch.

:param limit: an integer LIMIT parameter, or a SQL expression that provides an integer result. Pass None to reset it.

.. seealso::

:meth:_sql.GenerativeSelect.fetch

:meth:_sql.GenerativeSelect.offset

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def limit(self, limit: _LimitOffsetType) -> Self:
    """Return a new selectable with the given LIMIT criterion
    applied.

    This is a numerical value which usually renders as a ``LIMIT``
    expression in the resulting select.  Backends that don't
    support ``LIMIT`` will attempt to provide similar
    functionality.

    .. note::

       The :meth:`_sql.GenerativeSelect.limit` method will replace
       any clause applied with :meth:`_sql.GenerativeSelect.fetch`.

    :param limit: an integer LIMIT parameter, or a SQL expression
     that provides an integer result. Pass ``None`` to reset it.

    .. seealso::

       :meth:`_sql.GenerativeSelect.fetch`

       :meth:`_sql.GenerativeSelect.offset`

    """

    self._fetch_clause = self._fetch_clause_options = None
    self._limit_clause = self._offset_or_limit_clause(limit)
    return self

fetch

fetch(
    count: _LimitOffsetType,
    with_ties: bool = False,
    percent: bool = False,
) -> Self

Return a new selectable with the given FETCH FIRST criterion applied.

This is a numeric value which usually renders as FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES} expression in the resulting select. This functionality is is currently implemented for Oracle, PostgreSQL, MSSQL.

Use :meth:_sql.GenerativeSelect.offset to specify the offset.

.. note::

The :meth:_sql.GenerativeSelect.fetch method will replace any clause applied with :meth:_sql.GenerativeSelect.limit.

.. versionadded:: 1.4

:param count: an integer COUNT parameter, or a SQL expression that provides an integer result. When percent=True this will represent the percentage of rows to return, not the absolute value. Pass None to reset it.

:param with_ties: When True, the WITH TIES option is used to return any additional rows that tie for the last place in the result set according to the ORDER BY clause. The ORDER BY may be mandatory in this case. Defaults to False

:param percent: When True, count represents the percentage of the total number of selected rows to return. Defaults to False

.. seealso::

:meth:_sql.GenerativeSelect.limit

:meth:_sql.GenerativeSelect.offset

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def fetch(
    self,
    count: _LimitOffsetType,
    with_ties: bool = False,
    percent: bool = False,
) -> Self:
    """Return a new selectable with the given FETCH FIRST criterion
    applied.

    This is a numeric value which usually renders as
    ``FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES}``
    expression in the resulting select. This functionality is
    is currently implemented for Oracle, PostgreSQL, MSSQL.

    Use :meth:`_sql.GenerativeSelect.offset` to specify the offset.

    .. note::

       The :meth:`_sql.GenerativeSelect.fetch` method will replace
       any clause applied with :meth:`_sql.GenerativeSelect.limit`.

    .. versionadded:: 1.4

    :param count: an integer COUNT parameter, or a SQL expression
     that provides an integer result. When ``percent=True`` this will
     represent the percentage of rows to return, not the absolute value.
     Pass ``None`` to reset it.

    :param with_ties: When ``True``, the WITH TIES option is used
     to return any additional rows that tie for the last place in the
     result set according to the ``ORDER BY`` clause. The
     ``ORDER BY`` may be mandatory in this case. Defaults to ``False``

    :param percent: When ``True``, ``count`` represents the percentage
     of the total number of selected rows to return. Defaults to ``False``

    .. seealso::

       :meth:`_sql.GenerativeSelect.limit`

       :meth:`_sql.GenerativeSelect.offset`

    """

    self._limit_clause = None
    if count is None:
        self._fetch_clause = self._fetch_clause_options = None
    else:
        self._fetch_clause = self._offset_or_limit_clause(count)
        self._fetch_clause_options = {
            "with_ties": with_ties,
            "percent": percent,
        }
    return self

offset

offset(offset: _LimitOffsetType) -> Self

Return a new selectable with the given OFFSET criterion applied.

This is a numeric value which usually renders as an OFFSET expression in the resulting select. Backends that don't support OFFSET will attempt to provide similar functionality.

:param offset: an integer OFFSET parameter, or a SQL expression that provides an integer result. Pass None to reset it.

.. seealso::

:meth:_sql.GenerativeSelect.limit

:meth:_sql.GenerativeSelect.fetch

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def offset(self, offset: _LimitOffsetType) -> Self:
    """Return a new selectable with the given OFFSET criterion
    applied.


    This is a numeric value which usually renders as an ``OFFSET``
    expression in the resulting select.  Backends that don't
    support ``OFFSET`` will attempt to provide similar
    functionality.

    :param offset: an integer OFFSET parameter, or a SQL expression
     that provides an integer result. Pass ``None`` to reset it.

    .. seealso::

       :meth:`_sql.GenerativeSelect.limit`

       :meth:`_sql.GenerativeSelect.fetch`

    """

    self._offset_clause = self._offset_or_limit_clause(offset)
    return self

slice

slice(start: int, stop: int) -> Self

Apply LIMIT / OFFSET to this statement based on a slice.

The start and stop indices behave like the argument to Python's built-in :func:range function. This method provides an alternative to using LIMIT/OFFSET to get a slice of the query.

For example, ::

stmt = select(User).order_by(User).id.slice(1, 3)

renders as

.. sourcecode:: sql

SELECT users.id AS users_id, users.name AS users_name FROM users ORDER BY users.id LIMIT ? OFFSET ? (2, 1)

.. note::

The :meth:_sql.GenerativeSelect.slice method will replace any clause applied with :meth:_sql.GenerativeSelect.fetch.

.. versionadded:: 1.4 Added the :meth:_sql.GenerativeSelect.slice method generalized from the ORM.

.. seealso::

:meth:_sql.GenerativeSelect.limit

:meth:_sql.GenerativeSelect.offset

:meth:_sql.GenerativeSelect.fetch

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
@util.preload_module("sqlalchemy.sql.util")
def slice(
    self,
    start: int,
    stop: int,
) -> Self:
    """Apply LIMIT / OFFSET to this statement based on a slice.

    The start and stop indices behave like the argument to Python's
    built-in :func:`range` function. This method provides an
    alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
    query.

    For example, ::

        stmt = select(User).order_by(User).id.slice(1, 3)

    renders as

    .. sourcecode:: sql

       SELECT users.id AS users_id,
              users.name AS users_name
       FROM users ORDER BY users.id
       LIMIT ? OFFSET ?
       (2, 1)

    .. note::

       The :meth:`_sql.GenerativeSelect.slice` method will replace
       any clause applied with :meth:`_sql.GenerativeSelect.fetch`.

    .. versionadded:: 1.4  Added the :meth:`_sql.GenerativeSelect.slice`
       method generalized from the ORM.

    .. seealso::

       :meth:`_sql.GenerativeSelect.limit`

       :meth:`_sql.GenerativeSelect.offset`

       :meth:`_sql.GenerativeSelect.fetch`

    """
    sql_util = util.preloaded.sql_util
    self._fetch_clause = self._fetch_clause_options = None
    self._limit_clause, self._offset_clause = sql_util._make_slice(
        self._limit_clause, self._offset_clause, start, stop
    )
    return self

order_by

order_by(
    __first: Union[
        Literal[None, NO_ARG],
        _ColumnExpressionOrStrLabelArgument[Any],
    ] = NO_ARG,
    *clauses: _ColumnExpressionOrStrLabelArgument[Any],
) -> Self

Return a new selectable with the given list of ORDER BY criteria applied.

e.g.::

stmt = select(table).order_by(table.c.id, table.c.name)

Calling this method multiple times is equivalent to calling it once with all the clauses concatenated. All existing ORDER BY criteria may be cancelled by passing None by itself. New ORDER BY criteria may then be added by invoking :meth:_orm.Query.order_by again, e.g.::

# will erase all ORDER BY and ORDER BY new_col alone
stmt = stmt.order_by(None).order_by(new_col)

:param *clauses: a series of :class:_expression.ColumnElement constructs which will be used to generate an ORDER BY clause.

.. seealso::

:ref:`tutorial_order_by` - in the :ref:`unified_tutorial`

:ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def order_by(
    self,
    __first: Union[
        Literal[None, _NoArg.NO_ARG],
        _ColumnExpressionOrStrLabelArgument[Any],
    ] = _NoArg.NO_ARG,
    *clauses: _ColumnExpressionOrStrLabelArgument[Any],
) -> Self:
    r"""Return a new selectable with the given list of ORDER BY
    criteria applied.

    e.g.::

        stmt = select(table).order_by(table.c.id, table.c.name)

    Calling this method multiple times is equivalent to calling it once
    with all the clauses concatenated. All existing ORDER BY criteria may
    be cancelled by passing ``None`` by itself.  New ORDER BY criteria may
    then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::

        # will erase all ORDER BY and ORDER BY new_col alone
        stmt = stmt.order_by(None).order_by(new_col)

    :param \*clauses: a series of :class:`_expression.ColumnElement`
     constructs
     which will be used to generate an ORDER BY clause.

    .. seealso::

        :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`

        :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

    """

    if not clauses and __first is None:
        self._order_by_clauses = ()
    elif __first is not _NoArg.NO_ARG:
        self._order_by_clauses += tuple(
            coercions.expect(
                roles.OrderByRole, clause, apply_propagate_attrs=self
            )
            for clause in (__first,) + clauses
        )
    return self

group_by

group_by(
    __first: Union[
        Literal[None, NO_ARG],
        _ColumnExpressionOrStrLabelArgument[Any],
    ] = NO_ARG,
    *clauses: _ColumnExpressionOrStrLabelArgument[Any],
) -> Self

Return a new selectable with the given list of GROUP BY criterion applied.

All existing GROUP BY settings can be suppressed by passing None.

e.g.::

stmt = select(table.c.name, func.max(table.c.stat)).\
group_by(table.c.name)

:param *clauses: a series of :class:_expression.ColumnElement constructs which will be used to generate an GROUP BY clause.

.. seealso::

:ref:`tutorial_group_by_w_aggregates` - in the
:ref:`unified_tutorial`

:ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def group_by(
    self,
    __first: Union[
        Literal[None, _NoArg.NO_ARG],
        _ColumnExpressionOrStrLabelArgument[Any],
    ] = _NoArg.NO_ARG,
    *clauses: _ColumnExpressionOrStrLabelArgument[Any],
) -> Self:
    r"""Return a new selectable with the given list of GROUP BY
    criterion applied.

    All existing GROUP BY settings can be suppressed by passing ``None``.

    e.g.::

        stmt = select(table.c.name, func.max(table.c.stat)).\
        group_by(table.c.name)

    :param \*clauses: a series of :class:`_expression.ColumnElement`
     constructs
     which will be used to generate an GROUP BY clause.

    .. seealso::

        :ref:`tutorial_group_by_w_aggregates` - in the
        :ref:`unified_tutorial`

        :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

    """

    if not clauses and __first is None:
        self._group_by_clauses = ()
    elif __first is not _NoArg.NO_ARG:
        self._group_by_clauses += tuple(
            coercions.expect(
                roles.GroupByRole, clause, apply_propagate_attrs=self
            )
            for clause in (__first,) + clauses
        )
    return self

with_statement_hint

with_statement_hint(
    text: str, dialect_name: str = "*"
) -> Self

Add a statement hint to this :class:_expression.Select or other selectable object.

This method is similar to :meth:_expression.Select.with_hint except that it does not require an individual table, and instead applies to the statement as a whole.

Hints here are specific to the backend database and may include directives such as isolation levels, file directives, fetch directives, etc.

.. seealso::

:meth:`_expression.Select.with_hint`

:meth:`_expression.Select.prefix_with` - generic SELECT prefixing
which also can suit some database-specific HINT syntaxes such as
MySQL optimizer hints

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def with_statement_hint(self, text: str, dialect_name: str = "*") -> Self:
    """Add a statement hint to this :class:`_expression.Select` or
    other selectable object.

    This method is similar to :meth:`_expression.Select.with_hint`
    except that
    it does not require an individual table, and instead applies to the
    statement as a whole.

    Hints here are specific to the backend database and may include
    directives such as isolation levels, file directives, fetch directives,
    etc.

    .. seealso::

        :meth:`_expression.Select.with_hint`

        :meth:`_expression.Select.prefix_with` - generic SELECT prefixing
        which also can suit some database-specific HINT syntaxes such as
        MySQL optimizer hints

    """
    return self._with_hint(None, text, dialect_name)

with_hint

with_hint(
    selectable: _FromClauseArgument,
    text: str,
    dialect_name: str = "*",
) -> Self

Add an indexing or other executional context hint for the given selectable to this :class:_expression.Select or other selectable object.

The text of the hint is rendered in the appropriate location for the database backend in use, relative to the given :class:_schema.Table or :class:_expression.Alias passed as the selectable argument. The dialect implementation typically uses Python string substitution syntax with the token %(name)s to render the name of the table or alias. E.g. when using Oracle, the following::

select(mytable).\
    with_hint(mytable, "index(%(name)s ix_mytable)")

Would render SQL as::

select /*+ index(mytable ix_mytable) */ ... from mytable

The dialect_name option will limit the rendering of a particular hint to a particular backend. Such as, to add hints for both Oracle and Sybase simultaneously::

select(mytable).\
    with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
    with_hint(mytable, "WITH INDEX ix_mytable", 'mssql')

.. seealso::

:meth:`_expression.Select.with_statement_hint`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def with_hint(
    self,
    selectable: _FromClauseArgument,
    text: str,
    dialect_name: str = "*",
) -> Self:
    r"""Add an indexing or other executional context hint for the given
    selectable to this :class:`_expression.Select` or other selectable
    object.

    The text of the hint is rendered in the appropriate
    location for the database backend in use, relative
    to the given :class:`_schema.Table` or :class:`_expression.Alias`
    passed as the
    ``selectable`` argument. The dialect implementation
    typically uses Python string substitution syntax
    with the token ``%(name)s`` to render the name of
    the table or alias. E.g. when using Oracle, the
    following::

        select(mytable).\
            with_hint(mytable, "index(%(name)s ix_mytable)")

    Would render SQL as::

        select /*+ index(mytable ix_mytable) */ ... from mytable

    The ``dialect_name`` option will limit the rendering of a particular
    hint to a particular backend. Such as, to add hints for both Oracle
    and Sybase simultaneously::

        select(mytable).\
            with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\
            with_hint(mytable, "WITH INDEX ix_mytable", 'mssql')

    .. seealso::

        :meth:`_expression.Select.with_statement_hint`

    """

    return self._with_hint(selectable, text, dialect_name)

suffix_with

suffix_with(
    *suffixes: _TextCoercedExpressionArgument[Any],
    dialect: str = "*",
) -> Self

Add one or more expressions following the statement as a whole.

This is used to support backend-specific suffix keywords on certain constructs.

E.g.::

stmt = select(col1, col2).cte().suffix_with(
    "cycle empno set y_cycle to 1 default 0", dialect="oracle")

Multiple suffixes can be specified by multiple calls to :meth:_expression.HasSuffixes.suffix_with.

:param *suffixes: textual or :class:_expression.ClauseElement construct which will be rendered following the target clause. :param dialect: Optional string dialect name which will limit rendering of this suffix to only that dialect.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
@_document_text_coercion(
    "suffixes",
    ":meth:`_expression.HasSuffixes.suffix_with`",
    ":paramref:`.HasSuffixes.suffix_with.*suffixes`",
)
def suffix_with(
    self,
    *suffixes: _TextCoercedExpressionArgument[Any],
    dialect: str = "*",
) -> Self:
    r"""Add one or more expressions following the statement as a whole.

    This is used to support backend-specific suffix keywords on
    certain constructs.

    E.g.::

        stmt = select(col1, col2).cte().suffix_with(
            "cycle empno set y_cycle to 1 default 0", dialect="oracle")

    Multiple suffixes can be specified by multiple calls
    to :meth:`_expression.HasSuffixes.suffix_with`.

    :param \*suffixes: textual or :class:`_expression.ClauseElement`
     construct which
     will be rendered following the target clause.
    :param dialect: Optional string dialect name which will
     limit rendering of this suffix to only that dialect.

    """
    self._suffixes = self._suffixes + tuple(
        [
            (coercions.expect(roles.StatementOptionRole, p), dialect)
            for p in suffixes
        ]
    )
    return self

prefix_with

prefix_with(
    *prefixes: _TextCoercedExpressionArgument[Any],
    dialect: str = "*",
) -> Self

Add one or more expressions following the statement keyword, i.e. SELECT, INSERT, UPDATE, or DELETE. Generative.

This is used to support backend-specific prefix keywords such as those provided by MySQL.

E.g.::

stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")

# MySQL 5.7 optimizer hints
stmt = select(table).prefix_with(
    "/*+ BKA(t1) */", dialect="mysql")

Multiple prefixes can be specified by multiple calls to :meth:_expression.HasPrefixes.prefix_with.

:param *prefixes: textual or :class:_expression.ClauseElement construct which will be rendered following the INSERT, UPDATE, or DELETE keyword. :param dialect: optional string dialect name which will limit rendering of this prefix to only that dialect.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
@_document_text_coercion(
    "prefixes",
    ":meth:`_expression.HasPrefixes.prefix_with`",
    ":paramref:`.HasPrefixes.prefix_with.*prefixes`",
)
def prefix_with(
    self,
    *prefixes: _TextCoercedExpressionArgument[Any],
    dialect: str = "*",
) -> Self:
    r"""Add one or more expressions following the statement keyword, i.e.
    SELECT, INSERT, UPDATE, or DELETE. Generative.

    This is used to support backend-specific prefix keywords such as those
    provided by MySQL.

    E.g.::

        stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")

        # MySQL 5.7 optimizer hints
        stmt = select(table).prefix_with(
            "/*+ BKA(t1) */", dialect="mysql")

    Multiple prefixes can be specified by multiple calls
    to :meth:`_expression.HasPrefixes.prefix_with`.

    :param \*prefixes: textual or :class:`_expression.ClauseElement`
     construct which
     will be rendered following the INSERT, UPDATE, or DELETE
     keyword.
    :param dialect: optional string dialect name which will
     limit rendering of this prefix to only that dialect.

    """
    self._prefixes = self._prefixes + tuple(
        [
            (coercions.expect(roles.StatementOptionRole, p), dialect)
            for p in prefixes
        ]
    )
    return self

filter

filter(*criteria: _ColumnExpressionArgument[bool]) -> Self

A synonym for the :meth:_sql.Select.where method.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:
    """A synonym for the :meth:`_sql.Select.where` method."""

    return self.where(*criteria)

filter_by

filter_by(**kwargs: Any) -> Self

apply the given filtering criterion as a WHERE clause to this select.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def filter_by(self, **kwargs: Any) -> Self:
    r"""apply the given filtering criterion as a WHERE clause
    to this select.

    """
    from_entity = self._filter_by_zero()

    clauses = [
        _entity_namespace_key(from_entity, key) == value
        for key, value in kwargs.items()
    ]
    return self.filter(*clauses)

from_statement

from_statement(
    statement: ReturnsRowsRole,
) -> ExecutableReturnsRows

Apply the columns which this :class:.Select would select onto another statement.

This operation is :term:plugin-specific and will raise a not supported exception if this :class:_sql.Select does not select from plugin-enabled entities.

The statement is typically either a :func:_expression.text or :func:_expression.select construct, and should return the set of columns appropriate to the entities represented by this :class:.Select.

.. seealso::

:ref:`orm_queryguide_selecting_text` - usage examples in the
ORM Querying Guide

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def from_statement(
    self, statement: roles.ReturnsRowsRole
) -> ExecutableReturnsRows:
    """Apply the columns which this :class:`.Select` would select
    onto another statement.

    This operation is :term:`plugin-specific` and will raise a not
    supported exception if this :class:`_sql.Select` does not select from
    plugin-enabled entities.


    The statement is typically either a :func:`_expression.text` or
    :func:`_expression.select` construct, and should return the set of
    columns appropriate to the entities represented by this
    :class:`.Select`.

    .. seealso::

        :ref:`orm_queryguide_selecting_text` - usage examples in the
        ORM Querying Guide

    """
    meth = SelectState.get_plugin_class(self).from_statement
    return meth(self, statement)

join

join(
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    isouter: bool = False,
    full: bool = False,
) -> Self

Create a SQL JOIN against this :class:_expression.Select object's criterion and apply generatively, returning the newly resulting :class:_expression.Select.

E.g.::

stmt = select(user_table).join(address_table, user_table.c.id == address_table.c.user_id)

The above statement generates SQL similar to::

SELECT user.id, user.name FROM user JOIN address ON user.id = address.user_id

.. versionchanged:: 1.4 :meth:_expression.Select.join now creates a :class:_sql.Join object between a :class:_sql.FromClause source that is within the FROM clause of the existing SELECT, and a given target :class:_sql.FromClause, and then adds this :class:_sql.Join to the FROM clause of the newly generated SELECT statement. This is completely reworked from the behavior in 1.3, which would instead create a subquery of the entire :class:_expression.Select and then join that subquery to the target.

This is a backwards incompatible change as the previous behavior was mostly useless, producing an unnamed subquery rejected by most databases in any case. The new behavior is modeled after that of the very successful :meth:_orm.Query.join method in the ORM, in order to support the functionality of :class:_orm.Query being available by using a :class:_sql.Select object with an :class:_orm.Session.

See the notes for this change at :ref:change_select_join.

:param target: target table to join towards

:param onclause: ON clause of the join. If omitted, an ON clause is generated automatically based on the :class:_schema.ForeignKey linkages between the two tables, if one can be unambiguously determined, otherwise an error is raised.

:param isouter: if True, generate LEFT OUTER join. Same as :meth:_expression.Select.outerjoin.

:param full: if True, generate FULL OUTER join.

.. seealso::

:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

:meth:`_expression.Select.join_from`

:meth:`_expression.Select.outerjoin`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def join(
    self,
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    isouter: bool = False,
    full: bool = False,
) -> Self:
    r"""Create a SQL JOIN against this :class:`_expression.Select`
    object's criterion
    and apply generatively, returning the newly resulting
    :class:`_expression.Select`.

    E.g.::

        stmt = select(user_table).join(address_table, user_table.c.id == address_table.c.user_id)

    The above statement generates SQL similar to::

        SELECT user.id, user.name FROM user JOIN address ON user.id = address.user_id

    .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates
       a :class:`_sql.Join` object between a :class:`_sql.FromClause`
       source that is within the FROM clause of the existing SELECT,
       and a given target :class:`_sql.FromClause`, and then adds
       this :class:`_sql.Join` to the FROM clause of the newly generated
       SELECT statement.    This is completely reworked from the behavior
       in 1.3, which would instead create a subquery of the entire
       :class:`_expression.Select` and then join that subquery to the
       target.

       This is a **backwards incompatible change** as the previous behavior
       was mostly useless, producing an unnamed subquery rejected by
       most databases in any case.   The new behavior is modeled after
       that of the very successful :meth:`_orm.Query.join` method in the
       ORM, in order to support the functionality of :class:`_orm.Query`
       being available by using a :class:`_sql.Select` object with an
       :class:`_orm.Session`.

       See the notes for this change at :ref:`change_select_join`.


    :param target: target table to join towards

    :param onclause: ON clause of the join.  If omitted, an ON clause
     is generated automatically based on the :class:`_schema.ForeignKey`
     linkages between the two tables, if one can be unambiguously
     determined, otherwise an error is raised.

    :param isouter: if True, generate LEFT OUTER join.  Same as
     :meth:`_expression.Select.outerjoin`.

    :param full: if True, generate FULL OUTER join.

    .. seealso::

        :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

        :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

        :meth:`_expression.Select.join_from`

        :meth:`_expression.Select.outerjoin`

    """  # noqa: E501
    join_target = coercions.expect(
        roles.JoinTargetRole, target, apply_propagate_attrs=self
    )
    if onclause is not None:
        onclause_element = coercions.expect(roles.OnClauseRole, onclause)
    else:
        onclause_element = None

    self._setup_joins += (
        (
            join_target,
            onclause_element,
            None,
            {"isouter": isouter, "full": full},
        ),
    )
    return self

outerjoin_from

outerjoin_from(
    from_: _FromClauseArgument,
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    full: bool = False,
) -> Self

Create a SQL LEFT OUTER JOIN against this :class:_expression.Select object's criterion and apply generatively, returning the newly resulting :class:_expression.Select.

Usage is the same as that of :meth:_selectable.Select.join_from.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def outerjoin_from(
    self,
    from_: _FromClauseArgument,
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    full: bool = False,
) -> Self:
    r"""Create a SQL LEFT OUTER JOIN against this
    :class:`_expression.Select` object's criterion and apply generatively,
    returning the newly resulting :class:`_expression.Select`.

    Usage is the same as that of :meth:`_selectable.Select.join_from`.

    """
    return self.join_from(
        from_, target, onclause=onclause, isouter=True, full=full
    )

join_from

join_from(
    from_: _FromClauseArgument,
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    isouter: bool = False,
    full: bool = False,
) -> Self

Create a SQL JOIN against this :class:_expression.Select object's criterion and apply generatively, returning the newly resulting :class:_expression.Select.

E.g.::

stmt = select(user_table, address_table).join_from(
    user_table, address_table, user_table.c.id == address_table.c.user_id
)

The above statement generates SQL similar to::

SELECT user.id, user.name, address.id, address.email, address.user_id
FROM user JOIN address ON user.id = address.user_id

.. versionadded:: 1.4

:param from_: the left side of the join, will be rendered in the FROM clause and is roughly equivalent to using the :meth:.Select.select_from method.

:param target: target table to join towards

:param onclause: ON clause of the join.

:param isouter: if True, generate LEFT OUTER join. Same as :meth:_expression.Select.outerjoin.

:param full: if True, generate FULL OUTER join.

.. seealso::

:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

:meth:`_expression.Select.join`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def join_from(
    self,
    from_: _FromClauseArgument,
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    isouter: bool = False,
    full: bool = False,
) -> Self:
    r"""Create a SQL JOIN against this :class:`_expression.Select`
    object's criterion
    and apply generatively, returning the newly resulting
    :class:`_expression.Select`.

    E.g.::

        stmt = select(user_table, address_table).join_from(
            user_table, address_table, user_table.c.id == address_table.c.user_id
        )

    The above statement generates SQL similar to::

        SELECT user.id, user.name, address.id, address.email, address.user_id
        FROM user JOIN address ON user.id = address.user_id

    .. versionadded:: 1.4

    :param from\_: the left side of the join, will be rendered in the
     FROM clause and is roughly equivalent to using the
     :meth:`.Select.select_from` method.

    :param target: target table to join towards

    :param onclause: ON clause of the join.

    :param isouter: if True, generate LEFT OUTER join.  Same as
     :meth:`_expression.Select.outerjoin`.

    :param full: if True, generate FULL OUTER join.

    .. seealso::

        :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

        :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

        :meth:`_expression.Select.join`

    """  # noqa: E501

    # note the order of parsing from vs. target is important here, as we
    # are also deriving the source of the plugin (i.e. the subject mapper
    # in an ORM query) which should favor the "from_" over the "target"

    from_ = coercions.expect(
        roles.FromClauseRole, from_, apply_propagate_attrs=self
    )
    join_target = coercions.expect(
        roles.JoinTargetRole, target, apply_propagate_attrs=self
    )
    if onclause is not None:
        onclause_element = coercions.expect(roles.OnClauseRole, onclause)
    else:
        onclause_element = None

    self._setup_joins += (
        (
            join_target,
            onclause_element,
            from_,
            {"isouter": isouter, "full": full},
        ),
    )
    return self

outerjoin

outerjoin(
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    full: bool = False,
) -> Self

Create a left outer join.

Parameters are the same as that of :meth:_expression.Select.join.

.. versionchanged:: 1.4 :meth:_expression.Select.outerjoin now creates a :class:_sql.Join object between a :class:_sql.FromClause source that is within the FROM clause of the existing SELECT, and a given target :class:_sql.FromClause, and then adds this :class:_sql.Join to the FROM clause of the newly generated SELECT statement. This is completely reworked from the behavior in 1.3, which would instead create a subquery of the entire :class:_expression.Select and then join that subquery to the target.

This is a backwards incompatible change as the previous behavior was mostly useless, producing an unnamed subquery rejected by most databases in any case. The new behavior is modeled after that of the very successful :meth:_orm.Query.join method in the ORM, in order to support the functionality of :class:_orm.Query being available by using a :class:_sql.Select object with an :class:_orm.Session.

See the notes for this change at :ref:change_select_join.

.. seealso::

:ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

:ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

:meth:`_expression.Select.join`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def outerjoin(
    self,
    target: _JoinTargetArgument,
    onclause: Optional[_OnClauseArgument] = None,
    *,
    full: bool = False,
) -> Self:
    """Create a left outer join.

    Parameters are the same as that of :meth:`_expression.Select.join`.

    .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now
       creates a :class:`_sql.Join` object between a
       :class:`_sql.FromClause` source that is within the FROM clause of
       the existing SELECT, and a given target :class:`_sql.FromClause`,
       and then adds this :class:`_sql.Join` to the FROM clause of the
       newly generated SELECT statement.    This is completely reworked
       from the behavior in 1.3, which would instead create a subquery of
       the entire
       :class:`_expression.Select` and then join that subquery to the
       target.

       This is a **backwards incompatible change** as the previous behavior
       was mostly useless, producing an unnamed subquery rejected by
       most databases in any case.   The new behavior is modeled after
       that of the very successful :meth:`_orm.Query.join` method in the
       ORM, in order to support the functionality of :class:`_orm.Query`
       being available by using a :class:`_sql.Select` object with an
       :class:`_orm.Session`.

       See the notes for this change at :ref:`change_select_join`.

    .. seealso::

        :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

        :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

        :meth:`_expression.Select.join`

    """
    return self.join(target, onclause=onclause, isouter=True, full=full)

get_final_froms

get_final_froms() -> Sequence[FromClause]

Compute the final displayed list of :class:_expression.FromClause elements.

This method will run through the full computation required to determine what FROM elements will be displayed in the resulting SELECT statement, including shadowing individual tables with JOIN objects, as well as full computation for ORM use cases including eager loading clauses.

For ORM use, this accessor returns the post compilation list of FROM objects; this collection will include elements such as eagerly loaded tables and joins. The objects will not be ORM enabled and not work as a replacement for the :meth:_sql.Select.select_froms collection; additionally, the method is not well performing for an ORM enabled statement as it will incur the full ORM construction process.

To retrieve the FROM list that's implied by the "columns" collection passed to the :class:_sql.Select originally, use the :attr:_sql.Select.columns_clause_froms accessor.

To select from an alternative set of columns while maintaining the FROM list, use the :meth:_sql.Select.with_only_columns method and pass the :paramref:_sql.Select.with_only_columns.maintain_column_froms parameter.

.. versionadded:: 1.4.23 - the :meth:_sql.Select.get_final_froms method replaces the previous :attr:_sql.Select.froms accessor, which is deprecated.

.. seealso::

:attr:`_sql.Select.columns_clause_froms`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def get_final_froms(self) -> Sequence[FromClause]:
    """Compute the final displayed list of :class:`_expression.FromClause`
    elements.

    This method will run through the full computation required to
    determine what FROM elements will be displayed in the resulting
    SELECT statement, including shadowing individual tables with
    JOIN objects, as well as full computation for ORM use cases including
    eager loading clauses.

    For ORM use, this accessor returns the **post compilation**
    list of FROM objects; this collection will include elements such as
    eagerly loaded tables and joins.  The objects will **not** be
    ORM enabled and not work as a replacement for the
    :meth:`_sql.Select.select_froms` collection; additionally, the
    method is not well performing for an ORM enabled statement as it
    will incur the full ORM construction process.

    To retrieve the FROM list that's implied by the "columns" collection
    passed to the :class:`_sql.Select` originally, use the
    :attr:`_sql.Select.columns_clause_froms` accessor.

    To select from an alternative set of columns while maintaining the
    FROM list, use the :meth:`_sql.Select.with_only_columns` method and
    pass the
    :paramref:`_sql.Select.with_only_columns.maintain_column_froms`
    parameter.

    .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`
       method replaces the previous :attr:`_sql.Select.froms` accessor,
       which is deprecated.

    .. seealso::

        :attr:`_sql.Select.columns_clause_froms`

    """

    return self._compile_state_factory(self, None)._get_display_froms()

add_columns

add_columns(
    *entities: _ColumnsClauseArgument[Any],
) -> Select[Any]

Return a new :func:_expression.select construct with the given entities appended to its columns clause.

E.g.::

my_select = my_select.add_columns(table.c.new_column)

The original expressions in the columns clause remain in place. To replace the original expressions with new ones, see the method :meth:_expression.Select.with_only_columns.

:param *entities: column, table, or other entity expressions to be added to the columns clause

.. seealso::

:meth:`_expression.Select.with_only_columns` - replaces existing
expressions rather than appending.

:ref:`orm_queryguide_select_multiple_entities` - ORM-centric
example

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def add_columns(
    self, *entities: _ColumnsClauseArgument[Any]
) -> Select[Any]:
    r"""Return a new :func:`_expression.select` construct with
    the given entities appended to its columns clause.

    E.g.::

        my_select = my_select.add_columns(table.c.new_column)

    The original expressions in the columns clause remain in place.
    To replace the original expressions with new ones, see the method
    :meth:`_expression.Select.with_only_columns`.

    :param \*entities: column, table, or other entity expressions to be
     added to the columns clause

    .. seealso::

        :meth:`_expression.Select.with_only_columns` - replaces existing
        expressions rather than appending.

        :ref:`orm_queryguide_select_multiple_entities` - ORM-centric
        example

    """
    self._reset_memoizations()

    self._raw_columns = self._raw_columns + [
        coercions.expect(
            roles.ColumnsClauseRole, column, apply_propagate_attrs=self
        )
        for column in entities
    ]
    return self

column

column(column: _ColumnsClauseArgument[Any]) -> Select[Any]

Return a new :func:_expression.select construct with the given column expression added to its columns clause.

E.g.::

my_select = my_select.column(table.c.new_column)

See the documentation for :meth:_expression.Select.with_only_columns for guidelines on adding /replacing the columns of a :class:_expression.Select object.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@util.deprecated(
    "1.4",
    "The :meth:`_expression.Select.column` method is deprecated and will "
    "be removed in a future release.  Please use "
    ":meth:`_expression.Select.add_columns`",
)
def column(self, column: _ColumnsClauseArgument[Any]) -> Select[Any]:
    """Return a new :func:`_expression.select` construct with
    the given column expression added to its columns clause.

    E.g.::

        my_select = my_select.column(table.c.new_column)

    See the documentation for
    :meth:`_expression.Select.with_only_columns`
    for guidelines on adding /replacing the columns of a
    :class:`_expression.Select` object.

    """
    return self.add_columns(column)

reduce_columns

reduce_columns(only_synonyms: bool = True) -> Select[Any]

Return a new :func:_expression.select construct with redundantly named, equivalently-valued columns removed from the columns clause.

"Redundant" here means two columns where one refers to the other either based on foreign key, or via a simple equality comparison in the WHERE clause of the statement. The primary purpose of this method is to automatically construct a select statement with all uniquely-named columns, without the need to use table-qualified labels as :meth:_expression.Select.set_label_style does.

When columns are omitted based on foreign key, the referred-to column is the one that's kept. When columns are omitted based on WHERE equivalence, the first column in the columns clause is the one that's kept.

:param only_synonyms: when True, limit the removal of columns to those which have the same name as the equivalent. Otherwise, all columns that are equivalent to another are removed.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@util.preload_module("sqlalchemy.sql.util")
def reduce_columns(self, only_synonyms: bool = True) -> Select[Any]:
    """Return a new :func:`_expression.select` construct with redundantly
    named, equivalently-valued columns removed from the columns clause.

    "Redundant" here means two columns where one refers to the
    other either based on foreign key, or via a simple equality
    comparison in the WHERE clause of the statement.   The primary purpose
    of this method is to automatically construct a select statement
    with all uniquely-named columns, without the need to use
    table-qualified labels as
    :meth:`_expression.Select.set_label_style`
    does.

    When columns are omitted based on foreign key, the referred-to
    column is the one that's kept.  When columns are omitted based on
    WHERE equivalence, the first column in the columns clause is the
    one that's kept.

    :param only_synonyms: when True, limit the removal of columns
     to those which have the same name as the equivalent.   Otherwise,
     all columns that are equivalent to another are removed.

    """
    woc: Select[Any]
    woc = self.with_only_columns(
        *util.preloaded.sql_util.reduce_columns(
            self._all_selected_columns,
            only_synonyms=only_synonyms,
            *(self._where_criteria + self._from_obj),
        )
    )
    return woc

with_only_columns

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
) -> Select[Tuple[_T0]]

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
) -> Select[Tuple[_T0, _T1]]

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
) -> Select[Tuple[_T0, _T1, _T2]]

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
) -> Select[Tuple[_T0, _T1, _T2, _T3]]

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
    __ent6: _TypedColumnClauseArgument[_T6],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]

with_only_columns(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
    __ent6: _TypedColumnClauseArgument[_T6],
    __ent7: _TypedColumnClauseArgument[_T7],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]

with_only_columns(
    *entities: _ColumnsClauseArgument[Any],
    maintain_column_froms: bool = False,
    **__kw: Any,
) -> Select[Any]

with_only_columns(
    *entities: _ColumnsClauseArgument[Any],
    maintain_column_froms: bool = False,
    **__kw: Any,
) -> Select[Any]

Return a new :func:_expression.select construct with its columns clause replaced with the given entities.

By default, this method is exactly equivalent to as if the original :func:_expression.select had been called with the given entities. E.g. a statement::

s = select(table1.c.a, table1.c.b)
s = s.with_only_columns(table1.c.b)

should be exactly equivalent to::

s = select(table1.c.b)

In this mode of operation, :meth:_sql.Select.with_only_columns will also dynamically alter the FROM clause of the statement if it is not explicitly stated. To maintain the existing set of FROMs including those implied by the current columns clause, add the :paramref:_sql.Select.with_only_columns.maintain_column_froms parameter::

s = select(table1.c.a, table2.c.b)
s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

The above parameter performs a transfer of the effective FROMs in the columns collection to the :meth:_sql.Select.select_from method, as though the following were invoked::

s = select(table1.c.a, table2.c.b)
s = s.select_from(table1, table2).with_only_columns(table1.c.a)

The :paramref:_sql.Select.with_only_columns.maintain_column_froms parameter makes use of the :attr:_sql.Select.columns_clause_froms collection and performs an operation equivalent to the following::

s = select(table1.c.a, table2.c.b)
s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

:param *entities: column expressions to be used.

:param maintain_column_froms: boolean parameter that will ensure the FROM list implied from the current columns clause will be transferred to the :meth:_sql.Select.select_from method first.

.. versionadded:: 1.4.23

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def with_only_columns(
    self,
    *entities: _ColumnsClauseArgument[Any],
    maintain_column_froms: bool = False,
    **__kw: Any,
) -> Select[Any]:
    r"""Return a new :func:`_expression.select` construct with its columns
    clause replaced with the given entities.

    By default, this method is exactly equivalent to as if the original
    :func:`_expression.select` had been called with the given entities.
    E.g. a statement::

        s = select(table1.c.a, table1.c.b)
        s = s.with_only_columns(table1.c.b)

    should be exactly equivalent to::

        s = select(table1.c.b)

    In this mode of operation, :meth:`_sql.Select.with_only_columns`
    will also dynamically alter the FROM clause of the
    statement if it is not explicitly stated.
    To maintain the existing set of FROMs including those implied by the
    current columns clause, add the
    :paramref:`_sql.Select.with_only_columns.maintain_column_froms`
    parameter::

        s = select(table1.c.a, table2.c.b)
        s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

    The above parameter performs a transfer of the effective FROMs
    in the columns collection to the :meth:`_sql.Select.select_from`
    method, as though the following were invoked::

        s = select(table1.c.a, table2.c.b)
        s = s.select_from(table1, table2).with_only_columns(table1.c.a)

    The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`
    parameter makes use of the :attr:`_sql.Select.columns_clause_froms`
    collection and performs an operation equivalent to the following::

        s = select(table1.c.a, table2.c.b)
        s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

    :param \*entities: column expressions to be used.

    :param maintain_column_froms: boolean parameter that will ensure the
     FROM list implied from the current columns clause will be transferred
     to the :meth:`_sql.Select.select_from` method first.

     .. versionadded:: 1.4.23

    """  # noqa: E501

    if __kw:
        raise _no_kw()

    # memoizations should be cleared here as of
    # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this
    # is the case for now.
    self._assert_no_memoizations()

    if maintain_column_froms:
        self.select_from.non_generative(  # type: ignore
            self, *self.columns_clause_froms
        )

    # then memoize the FROMs etc.
    _MemoizedSelectEntities._generate_for_statement(self)

    self._raw_columns = [
        coercions.expect(roles.ColumnsClauseRole, c)
        for c in coercions._expression_collection_was_a_list(
            "entities", "Select.with_only_columns", entities
        )
    ]
    return self

where

where(
    *whereclause: _ColumnExpressionArgument[bool],
) -> Self

Return a new :func:_expression.select construct with the given expression added to its WHERE clause, joined to the existing clause via AND, if any.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:
    """Return a new :func:`_expression.select` construct with
    the given expression added to
    its WHERE clause, joined to the existing clause via AND, if any.

    """

    assert isinstance(self._where_criteria, tuple)

    for criterion in whereclause:
        where_criteria: ColumnElement[Any] = coercions.expect(
            roles.WhereHavingRole, criterion, apply_propagate_attrs=self
        )
        self._where_criteria += (where_criteria,)
    return self

having

having(*having: _ColumnExpressionArgument[bool]) -> Self

Return a new :func:_expression.select construct with the given expression added to its HAVING clause, joined to the existing clause via AND, if any.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:
    """Return a new :func:`_expression.select` construct with
    the given expression added to
    its HAVING clause, joined to the existing clause via AND, if any.

    """

    for criterion in having:
        having_criteria = coercions.expect(
            roles.WhereHavingRole, criterion, apply_propagate_attrs=self
        )
        self._having_criteria += (having_criteria,)
    return self

distinct

distinct(*expr: _ColumnExpressionArgument[Any]) -> Self

Return a new :func:_expression.select construct which will apply DISTINCT to its columns clause.

:param *expr: optional column expressions. When present, the PostgreSQL dialect will render a DISTINCT ON (<expressions>>) construct.

.. deprecated:: 1.4 Using *expr in other dialects is deprecated and will raise :class:_exc.CompileError in a future version.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:
    r"""Return a new :func:`_expression.select` construct which
    will apply DISTINCT to its columns clause.

    :param \*expr: optional column expressions.  When present,
     the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)``
     construct.

     .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
        and will raise :class:`_exc.CompileError` in a future version.

    """
    if expr:
        self._distinct = True
        self._distinct_on = self._distinct_on + tuple(
            coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)
            for e in expr
        )
    else:
        self._distinct = True
    return self

select_from

select_from(*froms: _FromClauseArgument) -> Self

Return a new :func:_expression.select construct with the given FROM expression(s) merged into its list of FROM objects.

E.g.::

table1 = table('t1', column('a'))
table2 = table('t2', column('b'))
s = select(table1.c.a).\
    select_from(
        table1.join(table2, table1.c.a==table2.c.b)
    )

The "from" list is a unique set on the identity of each element, so adding an already present :class:_schema.Table or other selectable will have no effect. Passing a :class:_expression.Join that refers to an already present :class:_schema.Table or other selectable will have the effect of concealing the presence of that selectable as an individual element in the rendered FROM list, instead rendering it into a JOIN clause.

While the typical purpose of :meth:_expression.Select.select_from is to replace the default, derived FROM clause with a join, it can also be called with individual table elements, multiple times if desired, in the case that the FROM clause cannot be fully derived from the columns clause::

select(func.count('*')).select_from(table1)

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def select_from(self, *froms: _FromClauseArgument) -> Self:
    r"""Return a new :func:`_expression.select` construct with the
    given FROM expression(s)
    merged into its list of FROM objects.

    E.g.::

        table1 = table('t1', column('a'))
        table2 = table('t2', column('b'))
        s = select(table1.c.a).\
            select_from(
                table1.join(table2, table1.c.a==table2.c.b)
            )

    The "from" list is a unique set on the identity of each element,
    so adding an already present :class:`_schema.Table`
    or other selectable
    will have no effect.   Passing a :class:`_expression.Join` that refers
    to an already present :class:`_schema.Table`
    or other selectable will have
    the effect of concealing the presence of that selectable as
    an individual element in the rendered FROM list, instead
    rendering it into a JOIN clause.

    While the typical purpose of :meth:`_expression.Select.select_from`
    is to
    replace the default, derived FROM clause with a join, it can
    also be called with individual table elements, multiple times
    if desired, in the case that the FROM clause cannot be fully
    derived from the columns clause::

        select(func.count('*')).select_from(table1)

    """

    self._from_obj += tuple(
        coercions.expect(
            roles.FromClauseRole, fromclause, apply_propagate_attrs=self
        )
        for fromclause in froms
    )
    return self

correlate

correlate(
    *fromclauses: Union[
        Literal[None, False], _FromClauseArgument
    ],
) -> Self

Return a new :class:_expression.Select which will correlate the given FROM clauses to that of an enclosing :class:_expression.Select.

Calling this method turns off the :class:_expression.Select object's default behavior of "auto-correlation". Normally, FROM elements which appear in a :class:_expression.Select that encloses this one via its :term:WHERE clause, ORDER BY, HAVING or :term:columns clause will be omitted from this :class:_expression.Select object's :term:FROM clause. Setting an explicit correlation collection using the :meth:_expression.Select.correlate method provides a fixed list of FROM objects that can potentially take place in this process.

When :meth:_expression.Select.correlate is used to apply specific FROM clauses for correlation, the FROM elements become candidates for correlation regardless of how deeply nested this :class:_expression.Select object is, relative to an enclosing :class:_expression.Select which refers to the same FROM object. This is in contrast to the behavior of "auto-correlation" which only correlates to an immediate enclosing :class:_expression.Select. Multi-level correlation ensures that the link between enclosed and enclosing :class:_expression.Select is always via at least one WHERE/ORDER BY/HAVING/columns clause in order for correlation to take place.

If None is passed, the :class:_expression.Select object will correlate none of its FROM entries, and all will render unconditionally in the local FROM clause.

:param *fromclauses: one or more :class:.FromClause or other FROM-compatible construct such as an ORM mapped entity to become part of the correlate collection; alternatively pass a single value None to remove all existing correlations.

.. seealso::

:meth:`_expression.Select.correlate_except`

:ref:`tutorial_scalar_subquery`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def correlate(
    self,
    *fromclauses: Union[Literal[None, False], _FromClauseArgument],
) -> Self:
    r"""Return a new :class:`_expression.Select`
    which will correlate the given FROM
    clauses to that of an enclosing :class:`_expression.Select`.

    Calling this method turns off the :class:`_expression.Select` object's
    default behavior of "auto-correlation".  Normally, FROM elements
    which appear in a :class:`_expression.Select`
    that encloses this one via
    its :term:`WHERE clause`, ORDER BY, HAVING or
    :term:`columns clause` will be omitted from this
    :class:`_expression.Select`
    object's :term:`FROM clause`.
    Setting an explicit correlation collection using the
    :meth:`_expression.Select.correlate`
    method provides a fixed list of FROM objects
    that can potentially take place in this process.

    When :meth:`_expression.Select.correlate`
    is used to apply specific FROM clauses
    for correlation, the FROM elements become candidates for
    correlation regardless of how deeply nested this
    :class:`_expression.Select`
    object is, relative to an enclosing :class:`_expression.Select`
    which refers to
    the same FROM object.  This is in contrast to the behavior of
    "auto-correlation" which only correlates to an immediate enclosing
    :class:`_expression.Select`.
    Multi-level correlation ensures that the link
    between enclosed and enclosing :class:`_expression.Select`
    is always via
    at least one WHERE/ORDER BY/HAVING/columns clause in order for
    correlation to take place.

    If ``None`` is passed, the :class:`_expression.Select`
    object will correlate
    none of its FROM entries, and all will render unconditionally
    in the local FROM clause.

    :param \*fromclauses: one or more :class:`.FromClause` or other
     FROM-compatible construct such as an ORM mapped entity to become part
     of the correlate collection; alternatively pass a single value
     ``None`` to remove all existing correlations.

    .. seealso::

        :meth:`_expression.Select.correlate_except`

        :ref:`tutorial_scalar_subquery`

    """

    # tests failing when we try to change how these
    # arguments are passed

    self._auto_correlate = False
    if not fromclauses or fromclauses[0] in {None, False}:
        if len(fromclauses) > 1:
            raise exc.ArgumentError(
                "additional FROM objects not accepted when "
                "passing None/False to correlate()"
            )
        self._correlate = ()
    else:
        self._correlate = self._correlate + tuple(
            coercions.expect(roles.FromClauseRole, f) for f in fromclauses
        )
    return self

correlate_except

correlate_except(
    *fromclauses: Union[
        Literal[None, False], _FromClauseArgument
    ],
) -> Self

Return a new :class:_expression.Select which will omit the given FROM clauses from the auto-correlation process.

Calling :meth:_expression.Select.correlate_except turns off the :class:_expression.Select object's default behavior of "auto-correlation" for the given FROM elements. An element specified here will unconditionally appear in the FROM list, while all other FROM elements remain subject to normal auto-correlation behaviors.

If None is passed, or no arguments are passed, the :class:_expression.Select object will correlate all of its FROM entries.

:param *fromclauses: a list of one or more :class:_expression.FromClause constructs, or other compatible constructs (i.e. ORM-mapped classes) to become part of the correlate-exception collection.

.. seealso::

:meth:`_expression.Select.correlate`

:ref:`tutorial_scalar_subquery`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@_generative
def correlate_except(
    self,
    *fromclauses: Union[Literal[None, False], _FromClauseArgument],
) -> Self:
    r"""Return a new :class:`_expression.Select`
    which will omit the given FROM
    clauses from the auto-correlation process.

    Calling :meth:`_expression.Select.correlate_except` turns off the
    :class:`_expression.Select` object's default behavior of
    "auto-correlation" for the given FROM elements.  An element
    specified here will unconditionally appear in the FROM list, while
    all other FROM elements remain subject to normal auto-correlation
    behaviors.

    If ``None`` is passed, or no arguments are passed,
    the :class:`_expression.Select` object will correlate all of its
    FROM entries.

    :param \*fromclauses: a list of one or more
     :class:`_expression.FromClause`
     constructs, or other compatible constructs (i.e. ORM-mapped
     classes) to become part of the correlate-exception collection.

    .. seealso::

        :meth:`_expression.Select.correlate`

        :ref:`tutorial_scalar_subquery`

    """

    self._auto_correlate = False
    if not fromclauses or fromclauses[0] in {None, False}:
        if len(fromclauses) > 1:
            raise exc.ArgumentError(
                "additional FROM objects not accepted when "
                "passing None/False to correlate_except()"
            )
        self._correlate_except = ()
    else:
        self._correlate_except = (self._correlate_except or ()) + tuple(
            coercions.expect(roles.FromClauseRole, f) for f in fromclauses
        )

    return self

selected_columns

selected_columns() -> ColumnCollection[
    str, ColumnElement[Any]
]

A :class:_expression.ColumnCollection representing the columns that this SELECT statement or similar construct returns in its result set, not including :class:_sql.TextClause constructs.

This collection differs from the :attr:_expression.FromClause.columns collection of a :class:_expression.FromClause in that the columns within this collection cannot be directly nested inside another SELECT statement; a subquery must be applied first which provides for the necessary parenthesization required by SQL.

For a :func:_expression.select construct, the collection here is exactly what would be rendered inside the "SELECT" statement, and the :class:_expression.ColumnElement objects are directly present as they were given, e.g.::

col1 = column('q', Integer)
col2 = column('p', Integer)
stmt = select(col1, col2)

Above, stmt.selected_columns would be a collection that contains the col1 and col2 objects directly. For a statement that is against a :class:_schema.Table or other :class:_expression.FromClause, the collection will use the :class:_expression.ColumnElement objects that are in the :attr:_expression.FromClause.c collection of the from element.

A use case for the :attr:_sql.Select.selected_columns collection is to allow the existing columns to be referenced when adding additional criteria, e.g.::

def filter_on_id(my_select, id):
    return my_select.where(my_select.selected_columns['id'] == id)

stmt = select(MyModel)

# adds "WHERE id=:param" to the statement
stmt = filter_on_id(stmt, 42)

.. note::

The :attr:`_sql.Select.selected_columns` collection does not
include expressions established in the columns clause using the
:func:`_sql.text` construct; these are silently omitted from the
collection. To use plain textual column expressions inside of a
:class:`_sql.Select` construct, use the :func:`_sql.literal_column`
construct.

.. versionadded:: 1.4

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@HasMemoized_ro_memoized_attribute
def selected_columns(
    self,
) -> ColumnCollection[str, ColumnElement[Any]]:
    """A :class:`_expression.ColumnCollection`
    representing the columns that
    this SELECT statement or similar construct returns in its result set,
    not including :class:`_sql.TextClause` constructs.

    This collection differs from the :attr:`_expression.FromClause.columns`
    collection of a :class:`_expression.FromClause` in that the columns
    within this collection cannot be directly nested inside another SELECT
    statement; a subquery must be applied first which provides for the
    necessary parenthesization required by SQL.

    For a :func:`_expression.select` construct, the collection here is
    exactly what would be rendered inside the "SELECT" statement, and the
    :class:`_expression.ColumnElement` objects are directly present as they
    were given, e.g.::

        col1 = column('q', Integer)
        col2 = column('p', Integer)
        stmt = select(col1, col2)

    Above, ``stmt.selected_columns`` would be a collection that contains
    the ``col1`` and ``col2`` objects directly. For a statement that is
    against a :class:`_schema.Table` or other
    :class:`_expression.FromClause`, the collection will use the
    :class:`_expression.ColumnElement` objects that are in the
    :attr:`_expression.FromClause.c` collection of the from element.

    A use case for the :attr:`_sql.Select.selected_columns` collection is
    to allow the existing columns to be referenced when adding additional
    criteria, e.g.::

        def filter_on_id(my_select, id):
            return my_select.where(my_select.selected_columns['id'] == id)

        stmt = select(MyModel)

        # adds "WHERE id=:param" to the statement
        stmt = filter_on_id(stmt, 42)

    .. note::

        The :attr:`_sql.Select.selected_columns` collection does not
        include expressions established in the columns clause using the
        :func:`_sql.text` construct; these are silently omitted from the
        collection. To use plain textual column expressions inside of a
        :class:`_sql.Select` construct, use the :func:`_sql.literal_column`
        construct.


    .. versionadded:: 1.4

    """

    # compare to SelectState._generate_columns_plus_names, which
    # generates the actual names used in the SELECT string.  that
    # method is more complex because it also renders columns that are
    # fully ambiguous, e.g. same column more than once.
    conv = cast(
        "Callable[[Any], str]",
        SelectState._column_naming_convention(self._label_style),
    )

    cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(
        [
            (conv(c), c)
            for c in self._all_selected_columns
            if is_column_element(c)
        ]
    )
    return cc.as_readonly()

union

union(
    *other: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a SQL UNION of this select() construct against the given selectables provided as positional arguments.

:param *other: one or more elements with which to create a UNION.

.. versionchanged:: 1.4.28

multiple elements are now accepted.

:param **kwargs: keyword arguments are forwarded to the constructor for the newly created :class:_sql.CompoundSelect object.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def union(
    self, *other: _SelectStatementForCompoundArgument
) -> CompoundSelect:
    r"""Return a SQL ``UNION`` of this select() construct against
    the given selectables provided as positional arguments.

    :param \*other: one or more elements with which to create a
     UNION.

     .. versionchanged:: 1.4.28

        multiple elements are now accepted.

    :param \**kwargs: keyword arguments are forwarded to the constructor
     for the newly created :class:`_sql.CompoundSelect` object.

    """
    return CompoundSelect._create_union(self, *other)

union_all

union_all(
    *other: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a SQL UNION ALL of this select() construct against the given selectables provided as positional arguments.

:param *other: one or more elements with which to create a UNION.

.. versionchanged:: 1.4.28

multiple elements are now accepted.

:param **kwargs: keyword arguments are forwarded to the constructor for the newly created :class:_sql.CompoundSelect object.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def union_all(
    self, *other: _SelectStatementForCompoundArgument
) -> CompoundSelect:
    r"""Return a SQL ``UNION ALL`` of this select() construct against
    the given selectables provided as positional arguments.

    :param \*other: one or more elements with which to create a
     UNION.

     .. versionchanged:: 1.4.28

        multiple elements are now accepted.

    :param \**kwargs: keyword arguments are forwarded to the constructor
     for the newly created :class:`_sql.CompoundSelect` object.

    """
    return CompoundSelect._create_union_all(self, *other)

except_

except_(
    *other: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a SQL EXCEPT of this select() construct against the given selectable provided as positional arguments.

:param *other: one or more elements with which to create a UNION.

.. versionchanged:: 1.4.28

multiple elements are now accepted.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def except_(
    self, *other: _SelectStatementForCompoundArgument
) -> CompoundSelect:
    r"""Return a SQL ``EXCEPT`` of this select() construct against
    the given selectable provided as positional arguments.

    :param \*other: one or more elements with which to create a
     UNION.

     .. versionchanged:: 1.4.28

        multiple elements are now accepted.

    """
    return CompoundSelect._create_except(self, *other)

except_all

except_all(
    *other: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a SQL EXCEPT ALL of this select() construct against the given selectables provided as positional arguments.

:param *other: one or more elements with which to create a UNION.

.. versionchanged:: 1.4.28

multiple elements are now accepted.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def except_all(
    self, *other: _SelectStatementForCompoundArgument
) -> CompoundSelect:
    r"""Return a SQL ``EXCEPT ALL`` of this select() construct against
    the given selectables provided as positional arguments.

    :param \*other: one or more elements with which to create a
     UNION.

     .. versionchanged:: 1.4.28

        multiple elements are now accepted.

    """
    return CompoundSelect._create_except_all(self, *other)

intersect

intersect(
    *other: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a SQL INTERSECT of this select() construct against the given selectables provided as positional arguments.

:param *other: one or more elements with which to create a UNION.

.. versionchanged:: 1.4.28

multiple elements are now accepted.

:param **kwargs: keyword arguments are forwarded to the constructor for the newly created :class:_sql.CompoundSelect object.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def intersect(
    self, *other: _SelectStatementForCompoundArgument
) -> CompoundSelect:
    r"""Return a SQL ``INTERSECT`` of this select() construct against
    the given selectables provided as positional arguments.

    :param \*other: one or more elements with which to create a
     UNION.

     .. versionchanged:: 1.4.28

        multiple elements are now accepted.

    :param \**kwargs: keyword arguments are forwarded to the constructor
     for the newly created :class:`_sql.CompoundSelect` object.

    """
    return CompoundSelect._create_intersect(self, *other)

intersect_all

intersect_all(
    *other: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a SQL INTERSECT ALL of this select() construct against the given selectables provided as positional arguments.

:param *other: one or more elements with which to create a UNION.

.. versionchanged:: 1.4.28

multiple elements are now accepted.

:param **kwargs: keyword arguments are forwarded to the constructor for the newly created :class:_sql.CompoundSelect object.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def intersect_all(
    self, *other: _SelectStatementForCompoundArgument
) -> CompoundSelect:
    r"""Return a SQL ``INTERSECT ALL`` of this select() construct
    against the given selectables provided as positional arguments.

    :param \*other: one or more elements with which to create a
     UNION.

     .. versionchanged:: 1.4.28

        multiple elements are now accepted.

    :param \**kwargs: keyword arguments are forwarded to the constructor
     for the newly created :class:`_sql.CompoundSelect` object.

    """
    return CompoundSelect._create_intersect_all(self, *other)

Selectable

Bases: ReturnsRows

Mark a class as being selectable.

inherit_cache class-attribute instance-attribute

inherit_cache: Optional[bool] = None

Indicate if this :class:.HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

The attribute defaults to None, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value to False, except that a warning is also emitted.

This flag can be set to True on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.

.. seealso::

:ref:`compilerext_caching` - General guideslines for setting the
:attr:`.HasCacheKey.inherit_cache` attribute for third-party or user
defined SQL constructs.

exported_columns property

exported_columns: ReadOnlyColumnCollection[Any, Any]

A :class:_expression.ColumnCollection that represents the "exported" columns of this :class:_expression.ReturnsRows.

The "exported" columns represent the collection of :class:_expression.ColumnElement expressions that are rendered by this SQL construct. There are primary varieties which are the "FROM clause columns" of a FROM clause, such as a table, join, or subquery, the "SELECTed columns", which are the columns in the "columns clause" of a SELECT statement, and the RETURNING columns in a DML statement..

.. versionadded:: 1.4

.. seealso::

:attr:`_expression.FromClause.exported_columns`

:attr:`_expression.SelectBase.exported_columns`

memoized_attribute

memoized_attribute(
    fget: Callable[..., _T], doc: Optional[str] = None
)

Bases: memoized_property[_T]

A read-only @property that is only evaluated once.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

def __init__(self, fget: Callable[..., _T], doc: Optional[str] = None):
    self.fget = fget
    self.__doc__ = doc or fget.__doc__
    self.__name__ = fget.__name__

memoized_instancemethod classmethod

memoized_instancemethod(fn: _F) -> _F

Decorate a method memoize its return value.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

@classmethod
def memoized_instancemethod(cls, fn: _F) -> _F:
    """Decorate a method memoize its return value.

    :meta private:

    """

    def oneshot(self: Any, *args: Any, **kw: Any) -> Any:
        result = fn(self, *args, **kw)

        def memo(*a, **kw):
            return result

        memo.__name__ = fn.__name__
        memo.__doc__ = fn.__doc__
        self.__dict__[fn.__name__] = memo
        self._memoized_keys |= {fn.__name__}
        return result

    return update_wrapper(oneshot, fn)  # type: ignore

unique_params

unique_params(
    __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.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

params

params(
    __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)

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

compare

compare(other: ClauseElement, **kw: Any) -> bool

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).

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

self_group

self_group(
    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.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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

is_derived_from

is_derived_from(fromclause: Optional[FromClause]) -> bool

Return True if this :class:.ReturnsRows is 'derived' from the given :class:.FromClause.

An example would be an Alias of a Table is derived from that Table.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:
    """Return ``True`` if this :class:`.ReturnsRows` is
    'derived' from the given :class:`.FromClause`.

    An example would be an Alias of a Table is derived from that Table.

    """
    raise NotImplementedError()

lateral

lateral(name: Optional[str] = None) -> LateralFromClause

Return a LATERAL alias of this :class:_expression.Selectable.

The return value is the :class:_expression.Lateral construct also provided by the top-level :func:_expression.lateral function.

.. seealso::

:ref:`tutorial_lateral_correlation` -  overview of usage.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def lateral(self, name: Optional[str] = None) -> LateralFromClause:
    """Return a LATERAL alias of this :class:`_expression.Selectable`.

    The return value is the :class:`_expression.Lateral` construct also
    provided by the top-level :func:`_expression.lateral` function.

    .. seealso::

        :ref:`tutorial_lateral_correlation` -  overview of usage.

    """
    return Lateral._construct(self, name=name)

replace_selectable

replace_selectable(old: FromClause, alias: Alias) -> Self

Replace all occurrences of :class:_expression.FromClause 'old' with the given :class:_expression.Alias object, returning a copy of this :class:_expression.FromClause.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

@util.deprecated(
    "1.4",
    message="The :meth:`.Selectable.replace_selectable` method is "
    "deprecated, and will be removed in a future release.  Similar "
    "functionality is available via the sqlalchemy.sql.visitors module.",
)
@util.preload_module("sqlalchemy.sql.util")
def replace_selectable(self, old: FromClause, alias: Alias) -> Self:
    """Replace all occurrences of :class:`_expression.FromClause`
    'old' with the given :class:`_expression.Alias`
    object, returning a copy of this :class:`_expression.FromClause`.

    """
    return util.preloaded.sql_util.ClauseAdapter(alias).traverse(self)

corresponding_column

corresponding_column(
    column: KeyedColumnElement[Any],
    require_embedded: bool = False,
) -> Optional[KeyedColumnElement[Any]]

Given a :class:_expression.ColumnElement, return the exported :class:_expression.ColumnElement object from the :attr:_expression.Selectable.exported_columns collection of this :class:_expression.Selectable which corresponds to that original :class:_expression.ColumnElement via a common ancestor column.

:param column: the target :class:_expression.ColumnElement to be matched.

:param require_embedded: only return corresponding columns for the given :class:_expression.ColumnElement, if the given :class:_expression.ColumnElement is actually present within a sub-element of this :class:_expression.Selectable. Normally the column will match if it merely shares a common ancestor with one of the exported columns of this :class:_expression.Selectable.

.. seealso::

:attr:`_expression.Selectable.exported_columns` - the
:class:`_expression.ColumnCollection`
that is used for the operation.

:meth:`_expression.ColumnCollection.corresponding_column`
- implementation
method.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/selectable.py

def corresponding_column(
    self, column: KeyedColumnElement[Any], require_embedded: bool = False
) -> Optional[KeyedColumnElement[Any]]:
    """Given a :class:`_expression.ColumnElement`, return the exported
    :class:`_expression.ColumnElement` object from the
    :attr:`_expression.Selectable.exported_columns`
    collection of this :class:`_expression.Selectable`
    which corresponds to that
    original :class:`_expression.ColumnElement` via a common ancestor
    column.

    :param column: the target :class:`_expression.ColumnElement`
                  to be matched.

    :param require_embedded: only return corresponding columns for
     the given :class:`_expression.ColumnElement`, if the given
     :class:`_expression.ColumnElement`
     is actually present within a sub-element
     of this :class:`_expression.Selectable`.
     Normally the column will match if
     it merely shares a common ancestor with one of the exported
     columns of this :class:`_expression.Selectable`.

    .. seealso::

        :attr:`_expression.Selectable.exported_columns` - the
        :class:`_expression.ColumnCollection`
        that is used for the operation.

        :meth:`_expression.ColumnCollection.corresponding_column`
        - implementation
        method.

    """

    return self.exported_columns.corresponding_column(
        column, require_embedded
    )

UnaryExpression

UnaryExpression(
    element: ColumnElement[Any],
    operator: Optional[OperatorType] = None,
    modifier: Optional[OperatorType] = None,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    wraps_column_expression: bool = False,
)

Bases: ColumnElement[_T]

Define a 'unary' expression.

A unary expression has a single column expression and an operator. The operator can be placed on the left (where it is called the 'operator') or right (where it is called the 'modifier') of the column expression.

:class:.UnaryExpression is the basis for several unary operators including those used by :func:.desc, :func:.asc, :func:.distinct, :func:.nulls_first and :func:.nulls_last.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def __init__(
    self,
    element: ColumnElement[Any],
    operator: Optional[OperatorType] = None,
    modifier: Optional[OperatorType] = None,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    wraps_column_expression: bool = False,
):
    self.operator = operator
    self.modifier = modifier
    self._propagate_attrs = element._propagate_attrs
    self.element = element.self_group(
        against=self.operator or self.modifier
    )

    # if type is None, we get NULLTYPE, which is our _T.  But I don't
    # know how to get the overloads to express that correctly
    self.type = type_api.to_instance(type_)  # type: ignore

    self.wraps_column_expression = wraps_column_expression

inherit_cache class-attribute instance-attribute

inherit_cache: Optional[bool] = None

Indicate if this :class:.HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.

The attribute defaults to None, which indicates that a construct has not yet taken into account whether or not its appropriate for it to participate in caching; this is functionally equivalent to setting the value to False, except that a warning is also emitted.

This flag can be set to True on a particular class, if the SQL that corresponds to the object does not change based on attributes which are local to this class, and not its superclass.

.. seealso::

:ref:`compilerext_caching` - General guideslines for setting the
:attr:`.HasCacheKey.inherit_cache` attribute for third-party or user
defined SQL constructs.

timetuple class-attribute instance-attribute

timetuple: Literal[None] = None

Hack, allows datetime objects to be compared on the LHS.

key class-attribute instance-attribute

key: Optional[str] = None

The 'key' that in some circumstances refers to this object in a Python namespace.

This typically refers to the "key" of the column as present in the .c collection of a selectable, e.g. sometable.c["somekey"] would return a :class:_schema.Column with a .key of "somekey".

expression property

expression: ColumnElement[Any]

Return a column expression.

Part of the inspection interface; returns self.

memoized_attribute

memoized_attribute(
    fget: Callable[..., _T], doc: Optional[str] = None
)

Bases: memoized_property[_T]

A read-only @property that is only evaluated once.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

def __init__(self, fget: Callable[..., _T], doc: Optional[str] = None):
    self.fget = fget
    self.__doc__ = doc or fget.__doc__
    self.__name__ = fget.__name__

memoized_instancemethod classmethod

memoized_instancemethod(fn: _F) -> _F

Decorate a method memoize its return value.

:meta private:

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py

@classmethod
def memoized_instancemethod(cls, fn: _F) -> _F:
    """Decorate a method memoize its return value.

    :meta private:

    """

    def oneshot(self: Any, *args: Any, **kw: Any) -> Any:
        result = fn(self, *args, **kw)

        def memo(*a, **kw):
            return result

        memo.__name__ = fn.__name__
        memo.__doc__ = fn.__doc__
        self.__dict__[fn.__name__] = memo
        self._memoized_keys |= {fn.__name__}
        return result

    return update_wrapper(oneshot, fn)  # type: ignore

proxy_set

proxy_set() -> FrozenSet[ColumnElement[Any]]

set of all columns we are proxying

as of 2.0 this is explicitly deannotated columns. previously it was effectively deannotated columns but wasn't enforced. annotated columns should basically not go into sets if at all possible because their hashing behavior is very non-performant.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

@util.memoized_property
def proxy_set(self) -> FrozenSet[ColumnElement[Any]]:
    """set of all columns we are proxying

    as of 2.0 this is explicitly deannotated columns.  previously it was
    effectively deannotated columns but wasn't enforced.  annotated
    columns should basically not go into sets if at all possible because
    their hashing behavior is very non-performant.

    """
    return frozenset([self._deannotate()]).union(
        itertools.chain(*[c.proxy_set for c in self._proxies])
    )

unique_params

unique_params(
    __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.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

params

params(
    __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)

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

compare

compare(other: ClauseElement, **kw: Any) -> bool

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).

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

label

label(name: Optional[str]) -> Label[_T]

Produce a column label, i.e. <columnname> AS <name>.

This is a shortcut to the :func:_expression.label function.

If 'name' is None, an anonymous label name will be generated.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def label(self, name: Optional[str]) -> Label[_T]:
    """Produce a column label, i.e. ``<columnname> AS <name>``.

    This is a shortcut to the :func:`_expression.label` function.

    If 'name' is ``None``, an anonymous label name will be generated.

    """
    return Label(name, self, self.type)

shares_lineage

shares_lineage(othercolumn: ColumnElement[Any]) -> bool

Return True if the given :class:_expression.ColumnElement has a common ancestor to this :class:_expression.ColumnElement.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def shares_lineage(self, othercolumn: ColumnElement[Any]) -> bool:
    """Return True if the given :class:`_expression.ColumnElement`
    has a common ancestor to this :class:`_expression.ColumnElement`."""

    return bool(self.proxy_set.intersection(othercolumn.proxy_set))

cast

cast(type_: _TypeEngineArgument[_OPT]) -> Cast[_OPT]

Produce a type cast, i.e. CAST(<expression> AS <type>).

This is a shortcut to the :func:_expression.cast function.

.. seealso::

:ref:`tutorial_casts`

:func:`_expression.cast`

:func:`_expression.type_coerce`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

def cast(self, type_: _TypeEngineArgument[_OPT]) -> Cast[_OPT]:
    """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.

    This is a shortcut to the :func:`_expression.cast` function.

    .. seealso::

        :ref:`tutorial_casts`

        :func:`_expression.cast`

        :func:`_expression.type_coerce`

    """
    return Cast(self, type_)

alias

alias(
    selectable: FromClause,
    name: Optional[str] = None,
    flat: bool = False,
) -> NamedFromClause

Return a named alias of the given :class:.FromClause.

For :class:.Table and :class:.Join objects, the return type is the :class:_expression.Alias object. Other kinds of :class:.NamedFromClause objects may be returned for other kinds of :class:.FromClause objects.

The named alias represents any :class:_expression.FromClause with an alternate name assigned within SQL, typically using the AS clause when generated, e.g. SELECT * FROM table AS aliasname.

Equivalent functionality is available via the :meth:_expression.FromClause.alias method available on all :class:_expression.FromClause objects.

:param selectable: any :class:_expression.FromClause subclass, such as a table, select statement, etc.

:param name: string name to be assigned as the alias. If None, a name will be deterministically generated at compile time. Deterministic means the name is guaranteed to be unique against other constructs used in the same statement, and will also be the same name for each successive compilation of the same statement object.

:param flat: Will be passed through to if the given selectable is an instance of :class:_expression.Join - see :meth:_expression.Join.alias for details.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def alias(
    selectable: FromClause, name: Optional[str] = None, flat: bool = False
) -> NamedFromClause:
    """Return a named alias of the given :class:`.FromClause`.

    For :class:`.Table` and :class:`.Join` objects, the return type is the
    :class:`_expression.Alias` object. Other kinds of :class:`.NamedFromClause`
    objects may be returned for other kinds of :class:`.FromClause` objects.

    The named alias represents any :class:`_expression.FromClause` with an
    alternate name assigned within SQL, typically using the ``AS`` clause when
    generated, e.g. ``SELECT * FROM table AS aliasname``.

    Equivalent functionality is available via the
    :meth:`_expression.FromClause.alias`
    method available on all :class:`_expression.FromClause` objects.

    :param selectable: any :class:`_expression.FromClause` subclass,
        such as a table, select statement, etc.

    :param name: string name to be assigned as the alias.
        If ``None``, a name will be deterministically generated at compile
        time. Deterministic means the name is guaranteed to be unique against
        other constructs used in the same statement, and will also be the same
        name for each successive compilation of the same statement object.

    :param flat: Will be passed through to if the given selectable
     is an instance of :class:`_expression.Join` - see
     :meth:`_expression.Join.alias` for details.

    """
    return Alias._factory(selectable, name=name, flat=flat)

all_

all_(
    expr: _ColumnExpressionArgument[_T],
) -> CollectionAggregate[bool]

Produce an ALL expression.

For dialects such as that of PostgreSQL, this operator applies to usage of the :class:_types.ARRAY datatype, for that of MySQL, it may apply to a subquery. e.g.::

# renders on PostgreSQL:
# '5 = ALL (somearray)'
expr = 5 == all_(mytable.c.somearray)

# renders on MySQL:
# '5 = ALL (SELECT value FROM table)'
expr = 5 == all_(select(table.c.value))

Comparison to NULL may work using None::

None == all_(mytable.c.somearray)

The any_() / all_() operators also feature a special "operand flipping" behavior such that if any_() / all_() are used on the left side of a comparison using a standalone operator such as ==, !=, etc. (not including operator methods such as :meth:_sql.ColumnOperators.is_) the rendered expression is flipped::

# would render '5 = ALL (column)`
all_(mytable.c.column) == 5

Or with None, which note will not perform the usual step of rendering "IS" as is normally the case for NULL::

# would render 'NULL = ALL(somearray)'
all_(mytable.c.somearray) == None

.. versionchanged:: 1.4.26 repaired the use of any_() / all_() comparing to NULL on the right side to be flipped to the left.

The column-level :meth:_sql.ColumnElement.all_ method (not to be confused with :class:_types.ARRAY level :meth:_types.ARRAY.Comparator.all) is shorthand for all_(col)::

5 == mytable.c.somearray.all_()

.. seealso::

:meth:`_sql.ColumnOperators.all_`

:func:`_expression.any_`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
    """Produce an ALL expression.

    For dialects such as that of PostgreSQL, this operator applies
    to usage of the :class:`_types.ARRAY` datatype, for that of
    MySQL, it may apply to a subquery.  e.g.::

        # renders on PostgreSQL:
        # '5 = ALL (somearray)'
        expr = 5 == all_(mytable.c.somearray)

        # renders on MySQL:
        # '5 = ALL (SELECT value FROM table)'
        expr = 5 == all_(select(table.c.value))

    Comparison to NULL may work using ``None``::

        None == all_(mytable.c.somearray)

    The any_() / all_() operators also feature a special "operand flipping"
    behavior such that if any_() / all_() are used on the left side of a
    comparison using a standalone operator such as ``==``, ``!=``, etc.
    (not including operator methods such as
    :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::

        # would render '5 = ALL (column)`
        all_(mytable.c.column) == 5

    Or with ``None``, which note will not perform
    the usual step of rendering "IS" as is normally the case for NULL::

        # would render 'NULL = ALL(somearray)'
        all_(mytable.c.somearray) == None

    .. versionchanged:: 1.4.26  repaired the use of any_() / all_()
       comparing to NULL on the right side to be flipped to the left.

    The column-level :meth:`_sql.ColumnElement.all_` method (not to be
    confused with :class:`_types.ARRAY` level
    :meth:`_types.ARRAY.Comparator.all`) is shorthand for
    ``all_(col)``::

        5 == mytable.c.somearray.all_()

    .. seealso::

        :meth:`_sql.ColumnOperators.all_`

        :func:`_expression.any_`

    """
    return CollectionAggregate._create_all(expr)

and_

and_(*clauses)

Produce a conjunction of expressions joined by AND.

E.g.::

from sqlalchemy import and_

stmt = select(users_table).where(
                and_(
                    users_table.c.name == 'wendy',
                    users_table.c.enrolled == True
                )
            )

The :func:.and_ conjunction is also available using the Python & operator (though note that compound expressions need to be parenthesized in order to function with Python operator precedence behavior)::

stmt = select(users_table).where(
                (users_table.c.name == 'wendy') &
                (users_table.c.enrolled == True)
            )

The :func:.and_ operation is also implicit in some cases; the :meth:_expression.Select.where method for example can be invoked multiple times against a statement, which will have the effect of each clause being combined using :func:.and_::

stmt = select(users_table).\
        where(users_table.c.name == 'wendy').\
        where(users_table.c.enrolled == True)

The :func:.and_ construct must be given at least one positional argument in order to be valid; a :func:.and_ construct with no arguments is ambiguous. To produce an "empty" or dynamically generated :func:.and_ expression, from a given list of expressions, a "default" element of :func:_sql.true (or just True) should be specified::

from sqlalchemy import true
criteria = and_(true(), *expressions)

The above expression will compile to SQL as the expression true or 1 = 1, depending on backend, if no other expressions are present. If expressions are present, then the :func:_sql.true value is ignored as it does not affect the outcome of an AND expression that has other elements.

.. deprecated:: 1.4 The :func:.and_ element now requires that at least one argument is passed; creating the :func:.and_ construct with no arguments is deprecated, and will emit a deprecation warning while continuing to produce a blank SQL string.

.. seealso::

:func:`.or_`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def and_(*clauses):  # noqa: F811
    r"""Produce a conjunction of expressions joined by ``AND``.

    E.g.::

        from sqlalchemy import and_

        stmt = select(users_table).where(
                        and_(
                            users_table.c.name == 'wendy',
                            users_table.c.enrolled == True
                        )
                    )

    The :func:`.and_` conjunction is also available using the
    Python ``&`` operator (though note that compound expressions
    need to be parenthesized in order to function with Python
    operator precedence behavior)::

        stmt = select(users_table).where(
                        (users_table.c.name == 'wendy') &
                        (users_table.c.enrolled == True)
                    )

    The :func:`.and_` operation is also implicit in some cases;
    the :meth:`_expression.Select.where`
    method for example can be invoked multiple
    times against a statement, which will have the effect of each
    clause being combined using :func:`.and_`::

        stmt = select(users_table).\
                where(users_table.c.name == 'wendy').\
                where(users_table.c.enrolled == True)

    The :func:`.and_` construct must be given at least one positional
    argument in order to be valid; a :func:`.and_` construct with no
    arguments is ambiguous.   To produce an "empty" or dynamically
    generated :func:`.and_`  expression, from a given list of expressions,
    a "default" element of :func:`_sql.true` (or just ``True``) should be
    specified::

        from sqlalchemy import true
        criteria = and_(true(), *expressions)

    The above expression will compile to SQL as the expression ``true``
    or ``1 = 1``, depending on backend, if no other expressions are
    present.  If expressions are present, then the :func:`_sql.true` value
    is ignored as it does not affect the outcome of an AND expression that
    has other elements.

    .. deprecated:: 1.4  The :func:`.and_` element now requires that at
      least one argument is passed; creating the :func:`.and_` construct
      with no arguments is deprecated, and will emit a deprecation warning
      while continuing to produce a blank SQL string.

    .. seealso::

        :func:`.or_`

    """
    return BooleanClauseList.and_(*clauses)

any_

any_(
    expr: _ColumnExpressionArgument[_T],
) -> CollectionAggregate[bool]

Produce an ANY expression.

For dialects such as that of PostgreSQL, this operator applies to usage of the :class:_types.ARRAY datatype, for that of MySQL, it may apply to a subquery. e.g.::

# renders on PostgreSQL:
# '5 = ANY (somearray)'
expr = 5 == any_(mytable.c.somearray)

# renders on MySQL:
# '5 = ANY (SELECT value FROM table)'
expr = 5 == any_(select(table.c.value))

Comparison to NULL may work using None or :func:_sql.null::

None == any_(mytable.c.somearray)

The any_() / all_() operators also feature a special "operand flipping" behavior such that if any_() / all_() are used on the left side of a comparison using a standalone operator such as ==, !=, etc. (not including operator methods such as :meth:_sql.ColumnOperators.is_) the rendered expression is flipped::

# would render '5 = ANY (column)`
any_(mytable.c.column) == 5

Or with None, which note will not perform the usual step of rendering "IS" as is normally the case for NULL::

# would render 'NULL = ANY(somearray)'
any_(mytable.c.somearray) == None

.. versionchanged:: 1.4.26 repaired the use of any_() / all_() comparing to NULL on the right side to be flipped to the left.

The column-level :meth:_sql.ColumnElement.any_ method (not to be confused with :class:_types.ARRAY level :meth:_types.ARRAY.Comparator.any) is shorthand for any_(col)::

5 = mytable.c.somearray.any_()

.. seealso::

:meth:`_sql.ColumnOperators.any_`

:func:`_expression.all_`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
    """Produce an ANY expression.

    For dialects such as that of PostgreSQL, this operator applies
    to usage of the :class:`_types.ARRAY` datatype, for that of
    MySQL, it may apply to a subquery.  e.g.::

        # renders on PostgreSQL:
        # '5 = ANY (somearray)'
        expr = 5 == any_(mytable.c.somearray)

        # renders on MySQL:
        # '5 = ANY (SELECT value FROM table)'
        expr = 5 == any_(select(table.c.value))

    Comparison to NULL may work using ``None`` or :func:`_sql.null`::

        None == any_(mytable.c.somearray)

    The any_() / all_() operators also feature a special "operand flipping"
    behavior such that if any_() / all_() are used on the left side of a
    comparison using a standalone operator such as ``==``, ``!=``, etc.
    (not including operator methods such as
    :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::

        # would render '5 = ANY (column)`
        any_(mytable.c.column) == 5

    Or with ``None``, which note will not perform
    the usual step of rendering "IS" as is normally the case for NULL::

        # would render 'NULL = ANY(somearray)'
        any_(mytable.c.somearray) == None

    .. versionchanged:: 1.4.26  repaired the use of any_() / all_()
       comparing to NULL on the right side to be flipped to the left.

    The column-level :meth:`_sql.ColumnElement.any_` method (not to be
    confused with :class:`_types.ARRAY` level
    :meth:`_types.ARRAY.Comparator.any`) is shorthand for
    ``any_(col)``::

        5 = mytable.c.somearray.any_()

    .. seealso::

        :meth:`_sql.ColumnOperators.any_`

        :func:`_expression.all_`

    """
    return CollectionAggregate._create_any(expr)

asc

asc(
    column: _ColumnExpressionOrStrLabelArgument[_T],
) -> UnaryExpression[_T]

Produce an ascending ORDER BY clause element.

e.g.::

from sqlalchemy import asc
stmt = select(users_table).order_by(asc(users_table.c.name))

will produce SQL as::

SELECT id, name FROM user ORDER BY name ASC

The :func:.asc function is a standalone version of the :meth:_expression.ColumnElement.asc method available on all SQL expressions, e.g.::

stmt = select(users_table).order_by(users_table.c.name.asc())

:param column: A :class:_expression.ColumnElement (e.g. scalar SQL expression) with which to apply the :func:.asc operation.

.. seealso::

:func:`.desc`

:func:`.nulls_first`

:func:`.nulls_last`

:meth:`_expression.Select.order_by`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def asc(
    column: _ColumnExpressionOrStrLabelArgument[_T],
) -> UnaryExpression[_T]:
    """Produce an ascending ``ORDER BY`` clause element.

    e.g.::

        from sqlalchemy import asc
        stmt = select(users_table).order_by(asc(users_table.c.name))

    will produce SQL as::

        SELECT id, name FROM user ORDER BY name ASC

    The :func:`.asc` function is a standalone version of the
    :meth:`_expression.ColumnElement.asc`
    method available on all SQL expressions,
    e.g.::


        stmt = select(users_table).order_by(users_table.c.name.asc())

    :param column: A :class:`_expression.ColumnElement` (e.g.
     scalar SQL expression)
     with which to apply the :func:`.asc` operation.

    .. seealso::

        :func:`.desc`

        :func:`.nulls_first`

        :func:`.nulls_last`

        :meth:`_expression.Select.order_by`

    """
    return UnaryExpression._create_asc(column)

between

between(
    expr: _ColumnExpressionOrLiteralArgument[_T],
    lower_bound: Any,
    upper_bound: Any,
    symmetric: bool = False,
) -> BinaryExpression[bool]

Produce a BETWEEN predicate clause.

E.g.::

from sqlalchemy import between
stmt = select(users_table).where(between(users_table.c.id, 5, 7))

Would produce SQL resembling::

SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2

The :func:.between function is a standalone version of the :meth:_expression.ColumnElement.between method available on all SQL expressions, as in::

stmt = select(users_table).where(users_table.c.id.between(5, 7))

All arguments passed to :func:.between, including the left side column expression, are coerced from Python scalar values if a the value is not a :class:_expression.ColumnElement subclass. For example, three fixed values can be compared as in::

print(between(5, 3, 7))

Which would produce::

:param_1 BETWEEN :param_2 AND :param_3

:param expr: a column expression, typically a :class:_expression.ColumnElement instance or alternatively a Python scalar expression to be coerced into a column expression, serving as the left side of the BETWEEN expression.

:param lower_bound: a column or Python scalar expression serving as the lower bound of the right side of the BETWEEN expression.

:param upper_bound: a column or Python scalar expression serving as the upper bound of the right side of the BETWEEN expression.

:param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note that not all databases support this syntax.

.. seealso::

:meth:`_expression.ColumnElement.between`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def between(
    expr: _ColumnExpressionOrLiteralArgument[_T],
    lower_bound: Any,
    upper_bound: Any,
    symmetric: bool = False,
) -> BinaryExpression[bool]:
    """Produce a ``BETWEEN`` predicate clause.

    E.g.::

        from sqlalchemy import between
        stmt = select(users_table).where(between(users_table.c.id, 5, 7))

    Would produce SQL resembling::

        SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2

    The :func:`.between` function is a standalone version of the
    :meth:`_expression.ColumnElement.between` method available on all
    SQL expressions, as in::

        stmt = select(users_table).where(users_table.c.id.between(5, 7))

    All arguments passed to :func:`.between`, including the left side
    column expression, are coerced from Python scalar values if a
    the value is not a :class:`_expression.ColumnElement` subclass.
    For example,
    three fixed values can be compared as in::

        print(between(5, 3, 7))

    Which would produce::

        :param_1 BETWEEN :param_2 AND :param_3

    :param expr: a column expression, typically a
     :class:`_expression.ColumnElement`
     instance or alternatively a Python scalar expression to be coerced
     into a column expression, serving as the left side of the ``BETWEEN``
     expression.

    :param lower_bound: a column or Python scalar expression serving as the
     lower bound of the right side of the ``BETWEEN`` expression.

    :param upper_bound: a column or Python scalar expression serving as the
     upper bound of the right side of the ``BETWEEN`` expression.

    :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
     that not all databases support this syntax.

    .. seealso::

        :meth:`_expression.ColumnElement.between`

    """
    col_expr = coercions.expect(roles.ExpressionElementRole, expr)
    return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)

bindparam

bindparam(
    key: Optional[str],
    value: Any = NO_ARG,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    unique: bool = False,
    required: Union[bool, Literal[NO_ARG]] = NO_ARG,
    quote: Optional[bool] = None,
    callable_: Optional[Callable[[], Any]] = None,
    expanding: bool = False,
    isoutparam: bool = False,
    literal_execute: bool = False,
) -> BindParameter[_T]

Produce a "bound expression".

The return value is an instance of :class:.BindParameter; this is a :class:_expression.ColumnElement subclass which represents a so-called "placeholder" value in a SQL expression, the value of which is supplied at the point at which the statement in executed against a database connection.

In SQLAlchemy, the :func:.bindparam construct has the ability to carry along the actual value that will be ultimately used at expression time. In this way, it serves not just as a "placeholder" for eventual population, but also as a means of representing so-called "unsafe" values which should not be rendered directly in a SQL statement, but rather should be passed along to the :term:DBAPI as values which need to be correctly escaped and potentially handled for type-safety.

When using :func:.bindparam explicitly, the use case is typically one of traditional deferment of parameters; the :func:.bindparam construct accepts a name which can then be referred to at execution time::

from sqlalchemy import bindparam

stmt = select(users_table).\
            where(users_table.c.name == bindparam('username'))

The above statement, when rendered, will produce SQL similar to::

SELECT id, name FROM user WHERE name = :username

In order to populate the value of :username above, the value would typically be applied at execution time to a method like :meth:_engine.Connection.execute::

result = connection.execute(stmt, username='wendy')

Explicit use of :func:.bindparam is also common when producing UPDATE or DELETE statements that are to be invoked multiple times, where the WHERE criterion of the statement is to change on each invocation, such as::

stmt = (users_table.update().
        where(user_table.c.name == bindparam('username')).
        values(fullname=bindparam('fullname'))
        )

connection.execute(
    stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
           {"username": "jack", "fullname": "Jack Jones"},
           ]
)

SQLAlchemy's Core expression system makes wide use of :func:.bindparam in an implicit sense. It is typical that Python literal values passed to virtually all SQL expression functions are coerced into fixed :func:.bindparam constructs. For example, given a comparison operation such as::

expr = users_table.c.name == 'Wendy'

The above expression will produce a :class:.BinaryExpression construct, where the left side is the :class:_schema.Column object representing the name column, and the right side is a :class:.BindParameter representing the literal value::

print(repr(expr.right))
BindParameter('%(4327771088 name)s', 'Wendy', type_=String())

The expression above will render SQL such as::

user.name = :name_1

Where the :name_1 parameter name is an anonymous name. The actual string Wendy is not in the rendered string, but is carried along where it is later used within statement execution. If we invoke a statement like the following::

stmt = select(users_table).where(users_table.c.name == 'Wendy')
result = connection.execute(stmt)

We would see SQL logging output as::

SELECT "user".id, "user".name
FROM "user"
WHERE "user".name = %(name_1)s
{'name_1': 'Wendy'}

Above, we see that Wendy is passed as a parameter to the database, while the placeholder :name_1 is rendered in the appropriate form for the target database, in this case the PostgreSQL database.

Similarly, :func:.bindparam is invoked automatically when working with :term:CRUD statements as far as the "VALUES" portion is concerned. The :func:_expression.insert construct produces an INSERT expression which will, at statement execution time, generate bound placeholders based on the arguments passed, as in::

stmt = users_table.insert()
result = connection.execute(stmt, name='Wendy')

The above will produce SQL output as::

INSERT INTO "user" (name) VALUES (%(name)s)
{'name': 'Wendy'}

The :class:_expression.Insert construct, at compilation/execution time, rendered a single :func:.bindparam mirroring the column name name as a result of the single name parameter we passed to the :meth:_engine.Connection.execute method.

:param key: the key (e.g. the name) for this bind param. Will be used in the generated SQL statement for dialects that use named parameters. This value may be modified when part of a compilation operation, if other :class:BindParameter objects exist with the same key, or if its length is too long and truncation is required.

If omitted, an "anonymous" name is generated for the bound parameter; when given a value to bind, the end result is equivalent to calling upon the :func:.literal function with a value to bind, particularly if the :paramref:.bindparam.unique parameter is also provided.

:param value: Initial value for this bind param. Will be used at statement execution time as the value for this parameter passed to the DBAPI, if no other value is indicated to the statement execution method for this particular parameter name. Defaults to None.

:param callable_: A callable function that takes the place of "value". The function will be called at statement execution time to determine the ultimate value. Used for scenarios where the actual bind value cannot be determined at the point at which the clause construct is created, but embedded bind values are still desirable.

:param type_: A :class:.TypeEngine class or instance representing an optional datatype for this :func:.bindparam. If not passed, a type may be determined automatically for the bind, based on the given value; for example, trivial Python types such as str, int, bool may result in the :class:.String, :class:.Integer or :class:.Boolean types being automatically selected.

The type of a :func:.bindparam is significant especially in that the type will apply pre-processing to the value before it is passed to the database. For example, a :func:.bindparam which refers to a datetime value, and is specified as holding the :class:.DateTime type, may apply conversion needed to the value (such as stringification on SQLite) before passing the value to the database.

:param unique: if True, the key name of this :class:.BindParameter will be modified if another :class:.BindParameter of the same name already has been located within the containing expression. This flag is used generally by the internals when producing so-called "anonymous" bound expressions, it isn't generally applicable to explicitly-named :func:.bindparam constructs.

:param required: If True, a value is required at execution time. If not passed, it defaults to True if neither :paramref:.bindparam.value or :paramref:.bindparam.callable were passed. If either of these parameters are present, then :paramref:.bindparam.required defaults to False.

:param quote: True if this parameter name requires quoting and is not currently known as a SQLAlchemy reserved word; this currently only applies to the Oracle backend, where bound names must sometimes be quoted.

:param isoutparam: if True, the parameter should be treated like a stored procedure "OUT" parameter. This applies to backends such as Oracle which support OUT parameters.

:param expanding: if True, this parameter will be treated as an "expanding" parameter at execution time; the parameter value is expected to be a sequence, rather than a scalar value, and the string SQL statement will be transformed on a per-execution basis to accommodate the sequence with a variable number of parameter slots passed to the DBAPI. This is to allow statement caching to be used in conjunction with an IN clause.

.. seealso::

:meth:`.ColumnOperators.in_`

:ref:`baked_in` - with baked queries

.. note:: The "expanding" feature does not support "executemany"- style parameter sets.

.. versionadded:: 1.2

.. versionchanged:: 1.3 the "expanding" bound parameter feature now supports empty lists.

:param literal_execute: if True, the bound parameter will be rendered in the compile phase with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will render the final value of the parameter into the SQL statement at statement execution time, omitting the value from the parameter dictionary / list passed to DBAPI cursor.execute(). This produces a similar effect as that of using the literal_binds, compilation flag, however takes place as the statement is sent to the DBAPI cursor.execute() method, rather than when the statement is compiled. The primary use of this capability is for rendering LIMIT / OFFSET clauses for database drivers that can't accommodate for bound parameters in these contexts, while allowing SQL constructs to be cacheable at the compilation level.

.. versionadded:: 1.4 Added "post compile" bound parameters

.. seealso::

    :ref:`change_4808`.

.. seealso::

:ref:`tutorial_sending_parameters` - in the
:ref:`unified_tutorial`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def bindparam(
    key: Optional[str],
    value: Any = _NoArg.NO_ARG,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    unique: bool = False,
    required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
    quote: Optional[bool] = None,
    callable_: Optional[Callable[[], Any]] = None,
    expanding: bool = False,
    isoutparam: bool = False,
    literal_execute: bool = False,
) -> BindParameter[_T]:
    r"""Produce a "bound expression".

    The return value is an instance of :class:`.BindParameter`; this
    is a :class:`_expression.ColumnElement`
    subclass which represents a so-called
    "placeholder" value in a SQL expression, the value of which is
    supplied at the point at which the statement in executed against a
    database connection.

    In SQLAlchemy, the :func:`.bindparam` construct has
    the ability to carry along the actual value that will be ultimately
    used at expression time.  In this way, it serves not just as
    a "placeholder" for eventual population, but also as a means of
    representing so-called "unsafe" values which should not be rendered
    directly in a SQL statement, but rather should be passed along
    to the :term:`DBAPI` as values which need to be correctly escaped
    and potentially handled for type-safety.

    When using :func:`.bindparam` explicitly, the use case is typically
    one of traditional deferment of parameters; the :func:`.bindparam`
    construct accepts a name which can then be referred to at execution
    time::

        from sqlalchemy import bindparam

        stmt = select(users_table).\
                    where(users_table.c.name == bindparam('username'))

    The above statement, when rendered, will produce SQL similar to::

        SELECT id, name FROM user WHERE name = :username

    In order to populate the value of ``:username`` above, the value
    would typically be applied at execution time to a method
    like :meth:`_engine.Connection.execute`::

        result = connection.execute(stmt, username='wendy')

    Explicit use of :func:`.bindparam` is also common when producing
    UPDATE or DELETE statements that are to be invoked multiple times,
    where the WHERE criterion of the statement is to change on each
    invocation, such as::

        stmt = (users_table.update().
                where(user_table.c.name == bindparam('username')).
                values(fullname=bindparam('fullname'))
                )

        connection.execute(
            stmt, [{"username": "wendy", "fullname": "Wendy Smith"},
                   {"username": "jack", "fullname": "Jack Jones"},
                   ]
        )

    SQLAlchemy's Core expression system makes wide use of
    :func:`.bindparam` in an implicit sense.   It is typical that Python
    literal values passed to virtually all SQL expression functions are
    coerced into fixed :func:`.bindparam` constructs.  For example, given
    a comparison operation such as::

        expr = users_table.c.name == 'Wendy'

    The above expression will produce a :class:`.BinaryExpression`
    construct, where the left side is the :class:`_schema.Column` object
    representing the ``name`` column, and the right side is a
    :class:`.BindParameter` representing the literal value::

        print(repr(expr.right))
        BindParameter('%(4327771088 name)s', 'Wendy', type_=String())

    The expression above will render SQL such as::

        user.name = :name_1

    Where the ``:name_1`` parameter name is an anonymous name.  The
    actual string ``Wendy`` is not in the rendered string, but is carried
    along where it is later used within statement execution.  If we
    invoke a statement like the following::

        stmt = select(users_table).where(users_table.c.name == 'Wendy')
        result = connection.execute(stmt)

    We would see SQL logging output as::

        SELECT "user".id, "user".name
        FROM "user"
        WHERE "user".name = %(name_1)s
        {'name_1': 'Wendy'}

    Above, we see that ``Wendy`` is passed as a parameter to the database,
    while the placeholder ``:name_1`` is rendered in the appropriate form
    for the target database, in this case the PostgreSQL database.

    Similarly, :func:`.bindparam` is invoked automatically when working
    with :term:`CRUD` statements as far as the "VALUES" portion is
    concerned.   The :func:`_expression.insert` construct produces an
    ``INSERT`` expression which will, at statement execution time, generate
    bound placeholders based on the arguments passed, as in::

        stmt = users_table.insert()
        result = connection.execute(stmt, name='Wendy')

    The above will produce SQL output as::

        INSERT INTO "user" (name) VALUES (%(name)s)
        {'name': 'Wendy'}

    The :class:`_expression.Insert` construct, at
    compilation/execution time, rendered a single :func:`.bindparam`
    mirroring the column name ``name`` as a result of the single ``name``
    parameter we passed to the :meth:`_engine.Connection.execute` method.

    :param key:
      the key (e.g. the name) for this bind param.
      Will be used in the generated
      SQL statement for dialects that use named parameters.  This
      value may be modified when part of a compilation operation,
      if other :class:`BindParameter` objects exist with the same
      key, or if its length is too long and truncation is
      required.

      If omitted, an "anonymous" name is generated for the bound parameter;
      when given a value to bind, the end result is equivalent to calling upon
      the :func:`.literal` function with a value to bind, particularly
      if the :paramref:`.bindparam.unique` parameter is also provided.

    :param value:
      Initial value for this bind param.  Will be used at statement
      execution time as the value for this parameter passed to the
      DBAPI, if no other value is indicated to the statement execution
      method for this particular parameter name.  Defaults to ``None``.

    :param callable\_:
      A callable function that takes the place of "value".  The function
      will be called at statement execution time to determine the
      ultimate value.   Used for scenarios where the actual bind
      value cannot be determined at the point at which the clause
      construct is created, but embedded bind values are still desirable.

    :param type\_:
      A :class:`.TypeEngine` class or instance representing an optional
      datatype for this :func:`.bindparam`.  If not passed, a type
      may be determined automatically for the bind, based on the given
      value; for example, trivial Python types such as ``str``,
      ``int``, ``bool``
      may result in the :class:`.String`, :class:`.Integer` or
      :class:`.Boolean` types being automatically selected.

      The type of a :func:`.bindparam` is significant especially in that
      the type will apply pre-processing to the value before it is
      passed to the database.  For example, a :func:`.bindparam` which
      refers to a datetime value, and is specified as holding the
      :class:`.DateTime` type, may apply conversion needed to the
      value (such as stringification on SQLite) before passing the value
      to the database.

    :param unique:
      if True, the key name of this :class:`.BindParameter` will be
      modified if another :class:`.BindParameter` of the same name
      already has been located within the containing
      expression.  This flag is used generally by the internals
      when producing so-called "anonymous" bound expressions, it
      isn't generally applicable to explicitly-named :func:`.bindparam`
      constructs.

    :param required:
      If ``True``, a value is required at execution time.  If not passed,
      it defaults to ``True`` if neither :paramref:`.bindparam.value`
      or :paramref:`.bindparam.callable` were passed.  If either of these
      parameters are present, then :paramref:`.bindparam.required`
      defaults to ``False``.

    :param quote:
      True if this parameter name requires quoting and is not
      currently known as a SQLAlchemy reserved word; this currently
      only applies to the Oracle backend, where bound names must
      sometimes be quoted.

    :param isoutparam:
      if True, the parameter should be treated like a stored procedure
      "OUT" parameter.  This applies to backends such as Oracle which
      support OUT parameters.

    :param expanding:
      if True, this parameter will be treated as an "expanding" parameter
      at execution time; the parameter value is expected to be a sequence,
      rather than a scalar value, and the string SQL statement will
      be transformed on a per-execution basis to accommodate the sequence
      with a variable number of parameter slots passed to the DBAPI.
      This is to allow statement caching to be used in conjunction with
      an IN clause.

      .. seealso::

        :meth:`.ColumnOperators.in_`

        :ref:`baked_in` - with baked queries

      .. note:: The "expanding" feature does not support "executemany"-
         style parameter sets.

      .. versionadded:: 1.2

      .. versionchanged:: 1.3 the "expanding" bound parameter feature now
         supports empty lists.

    :param literal_execute:
      if True, the bound parameter will be rendered in the compile phase
      with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
      render the final value of the parameter into the SQL statement at
      statement execution time, omitting the value from the parameter
      dictionary / list passed to DBAPI ``cursor.execute()``.  This
      produces a similar effect as that of using the ``literal_binds``,
      compilation flag,  however takes place as the statement is sent to
      the DBAPI ``cursor.execute()`` method, rather than when the statement
      is compiled.   The primary use of this
      capability is for rendering LIMIT / OFFSET clauses for database
      drivers that can't accommodate for bound parameters in these
      contexts, while allowing SQL constructs to be cacheable at the
      compilation level.

      .. versionadded:: 1.4 Added "post compile" bound parameters

        .. seealso::

            :ref:`change_4808`.

    .. seealso::

        :ref:`tutorial_sending_parameters` - in the
        :ref:`unified_tutorial`


    """
    return BindParameter(
        key,
        value,
        type_,
        unique,
        required,
        quote,
        callable_,
        expanding,
        isoutparam,
        literal_execute,
    )

case

case(
    *whens: Union[
        Tuple[_ColumnExpressionArgument[bool], Any],
        Mapping[Any, Any],
    ],
    value: Optional[Any] = None,
    else_: Optional[Any] = None,
) -> Case[Any]

Produce a CASE expression.

The CASE construct in SQL is a conditional object that acts somewhat analogously to an "if/then" construct in other languages. It returns an instance of :class:.Case.

:func:.case in its usual form is passed a series of "when" constructs, that is, a list of conditions and results as tuples::

from sqlalchemy import case

stmt = select(users_table).\
            where(
                case(
                    (users_table.c.name == 'wendy', 'W'),
                    (users_table.c.name == 'jack', 'J'),
                    else_='E'
                )
            )

The above statement will produce SQL resembling::

SELECT id, name FROM user
WHERE CASE
    WHEN (name = :name_1) THEN :param_1
    WHEN (name = :name_2) THEN :param_2
    ELSE :param_3
END

When simple equality expressions of several values against a single parent column are needed, :func:.case also has a "shorthand" format used via the :paramref:.case.value parameter, which is passed a column expression to be compared. In this form, the :paramref:.case.whens parameter is passed as a dictionary containing expressions to be compared against keyed to result expressions. The statement below is equivalent to the preceding statement::

stmt = select(users_table).\
            where(
                case(
                    {"wendy": "W", "jack": "J"},
                    value=users_table.c.name,
                    else_='E'
                )
            )

The values which are accepted as result values in :paramref:.case.whens as well as with :paramref:.case.else_ are coerced from Python literals into :func:.bindparam constructs. SQL expressions, e.g. :class:_expression.ColumnElement constructs, are accepted as well. To coerce a literal string expression into a constant expression rendered inline, use the :func:_expression.literal_column construct, as in::

from sqlalchemy import case, literal_column

case(
    (
        orderline.c.qty > 100,
        literal_column("'greaterthan100'")
    ),
    (
        orderline.c.qty > 10,
        literal_column("'greaterthan10'")
    ),
    else_=literal_column("'lessthan10'")
)

The above will render the given constants without using bound parameters for the result values (but still for the comparison values), as in::

CASE
    WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
    WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
    ELSE 'lessthan10'
END

:param *whens: The criteria to be compared against, :paramref:.case.whens accepts two different forms, based on whether or not :paramref:.case.value is used.

.. versionchanged:: 1.4 the :func:_sql.case function now accepts the series of WHEN conditions positionally

In the first form, it accepts multiple 2-tuples passed as positional arguments; each 2-tuple consists of (<sql expression>, <value>), where the SQL expression is a boolean expression and "value" is a resulting value, e.g.::

case(
    (users_table.c.name == 'wendy', 'W'),
    (users_table.c.name == 'jack', 'J')
)

In the second form, it accepts a Python dictionary of comparison values mapped to a resulting value; this form requires :paramref:.case.value to be present, and values will be compared using the == operator, e.g.::

case(
    {"wendy": "W", "jack": "J"},
    value=users_table.c.name
)

:param value: An optional SQL expression which will be used as a fixed "comparison point" for candidate values within a dictionary passed to :paramref:.case.whens.

:param else_: An optional SQL expression which will be the evaluated result of the CASE construct if all expressions within :paramref:.case.whens evaluate to false. When omitted, most databases will produce a result of NULL if none of the "when" expressions evaluate to true.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def case(
    *whens: Union[
        typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
    ],
    value: Optional[Any] = None,
    else_: Optional[Any] = None,
) -> Case[Any]:
    r"""Produce a ``CASE`` expression.

    The ``CASE`` construct in SQL is a conditional object that
    acts somewhat analogously to an "if/then" construct in other
    languages.  It returns an instance of :class:`.Case`.

    :func:`.case` in its usual form is passed a series of "when"
    constructs, that is, a list of conditions and results as tuples::

        from sqlalchemy import case

        stmt = select(users_table).\
                    where(
                        case(
                            (users_table.c.name == 'wendy', 'W'),
                            (users_table.c.name == 'jack', 'J'),
                            else_='E'
                        )
                    )

    The above statement will produce SQL resembling::

        SELECT id, name FROM user
        WHERE CASE
            WHEN (name = :name_1) THEN :param_1
            WHEN (name = :name_2) THEN :param_2
            ELSE :param_3
        END

    When simple equality expressions of several values against a single
    parent column are needed, :func:`.case` also has a "shorthand" format
    used via the
    :paramref:`.case.value` parameter, which is passed a column
    expression to be compared.  In this form, the :paramref:`.case.whens`
    parameter is passed as a dictionary containing expressions to be
    compared against keyed to result expressions.  The statement below is
    equivalent to the preceding statement::

        stmt = select(users_table).\
                    where(
                        case(
                            {"wendy": "W", "jack": "J"},
                            value=users_table.c.name,
                            else_='E'
                        )
                    )

    The values which are accepted as result values in
    :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
    coerced from Python literals into :func:`.bindparam` constructs.
    SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
    are accepted
    as well.  To coerce a literal string expression into a constant
    expression rendered inline, use the :func:`_expression.literal_column`
    construct,
    as in::

        from sqlalchemy import case, literal_column

        case(
            (
                orderline.c.qty > 100,
                literal_column("'greaterthan100'")
            ),
            (
                orderline.c.qty > 10,
                literal_column("'greaterthan10'")
            ),
            else_=literal_column("'lessthan10'")
        )

    The above will render the given constants without using bound
    parameters for the result values (but still for the comparison
    values), as in::

        CASE
            WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
            WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
            ELSE 'lessthan10'
        END

    :param \*whens: The criteria to be compared against,
     :paramref:`.case.whens` accepts two different forms, based on
     whether or not :paramref:`.case.value` is used.

     .. versionchanged:: 1.4 the :func:`_sql.case`
        function now accepts the series of WHEN conditions positionally

     In the first form, it accepts multiple 2-tuples passed as positional
     arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
     where the SQL expression is a boolean expression and "value" is a
     resulting value, e.g.::

        case(
            (users_table.c.name == 'wendy', 'W'),
            (users_table.c.name == 'jack', 'J')
        )

     In the second form, it accepts a Python dictionary of comparison
     values mapped to a resulting value; this form requires
     :paramref:`.case.value` to be present, and values will be compared
     using the ``==`` operator, e.g.::

        case(
            {"wendy": "W", "jack": "J"},
            value=users_table.c.name
        )

    :param value: An optional SQL expression which will be used as a
      fixed "comparison point" for candidate values within a dictionary
      passed to :paramref:`.case.whens`.

    :param else\_: An optional SQL expression which will be the evaluated
      result of the ``CASE`` construct if all expressions within
      :paramref:`.case.whens` evaluate to false.  When omitted, most
      databases will produce a result of NULL if none of the "when"
      expressions evaluate to true.


    """
    return Case(*whens, value=value, else_=else_)

cast

cast(
    expression: _ColumnExpressionOrLiteralArgument[Any],
    type_: _TypeEngineArgument[_T],
) -> Cast[_T]

Produce a CAST expression.

:func:.cast returns an instance of :class:.Cast.

E.g.::

from sqlalchemy import cast, Numeric

stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))

The above statement will produce SQL resembling::

SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product

The :func:.cast function performs two distinct functions when used. The first is that it renders the CAST expression within the resulting SQL string. The second is that it associates the given type (e.g. :class:.TypeEngine class or instance) with the column expression on the Python side, which means the expression will take on the expression operator behavior associated with that type, as well as the bound-value handling and result-row-handling behavior of the type.

An alternative to :func:.cast is the :func:.type_coerce function. This function performs the second task of associating an expression with a specific type, but does not render the CAST expression in SQL.

:param expression: A SQL expression, such as a :class:_expression.ColumnElement expression or a Python string which will be coerced into a bound literal value.

:param type_: A :class:.TypeEngine class or instance indicating the type to which the CAST should apply.

.. seealso::

:ref:`tutorial_casts`

:func:`.try_cast` - an alternative to CAST that results in
NULLs when the cast fails, instead of raising an error.
Only supported by some dialects.

:func:`.type_coerce` - an alternative to CAST that coerces the type
on the Python side only, which is often sufficient to generate the
correct SQL and data coercion.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def cast(
    expression: _ColumnExpressionOrLiteralArgument[Any],
    type_: _TypeEngineArgument[_T],
) -> Cast[_T]:
    r"""Produce a ``CAST`` expression.

    :func:`.cast` returns an instance of :class:`.Cast`.

    E.g.::

        from sqlalchemy import cast, Numeric

        stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))

    The above statement will produce SQL resembling::

        SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product

    The :func:`.cast` function performs two distinct functions when
    used.  The first is that it renders the ``CAST`` expression within
    the resulting SQL string.  The second is that it associates the given
    type (e.g. :class:`.TypeEngine` class or instance) with the column
    expression on the Python side, which means the expression will take
    on the expression operator behavior associated with that type,
    as well as the bound-value handling and result-row-handling behavior
    of the type.

    An alternative to :func:`.cast` is the :func:`.type_coerce` function.
    This function performs the second task of associating an expression
    with a specific type, but does not render the ``CAST`` expression
    in SQL.

    :param expression: A SQL expression, such as a
     :class:`_expression.ColumnElement`
     expression or a Python string which will be coerced into a bound
     literal value.

    :param type\_: A :class:`.TypeEngine` class or instance indicating
     the type to which the ``CAST`` should apply.

    .. seealso::

        :ref:`tutorial_casts`

        :func:`.try_cast` - an alternative to CAST that results in
        NULLs when the cast fails, instead of raising an error.
        Only supported by some dialects.

        :func:`.type_coerce` - an alternative to CAST that coerces the type
        on the Python side only, which is often sufficient to generate the
        correct SQL and data coercion.


    """
    return Cast(expression, type_)

collate

collate(
    expression: _ColumnExpressionArgument[str],
    collation: str,
) -> BinaryExpression[str]

Return the clause expression COLLATE collation.

e.g.::

collate(mycolumn, 'utf8_bin')

produces::

mycolumn COLLATE utf8_bin

The collation expression is also quoted if it is a case sensitive identifier, e.g. contains uppercase characters.

.. versionchanged:: 1.2 quoting is automatically applied to COLLATE expressions if they are case sensitive.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def collate(
    expression: _ColumnExpressionArgument[str], collation: str
) -> BinaryExpression[str]:
    """Return the clause ``expression COLLATE collation``.

    e.g.::

        collate(mycolumn, 'utf8_bin')

    produces::

        mycolumn COLLATE utf8_bin

    The collation expression is also quoted if it is a case sensitive
    identifier, e.g. contains uppercase characters.

    .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
       expressions if they are case sensitive.

    """
    return CollationClause._create_collation_expression(expression, collation)

column

column(
    text: str,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    is_literal: bool = False,
    _selectable: Optional[FromClause] = None,
) -> ColumnClause[_T]

Produce a :class:.ColumnClause object.

The :class:.ColumnClause is a lightweight analogue to the :class:_schema.Column class. The :func:_expression.column function can be invoked with just a name alone, as in::

from sqlalchemy import column

id, name = column("id"), column("name")
stmt = select(id, name).select_from("user")

The above statement would produce SQL like::

SELECT id, name FROM user

Once constructed, :func:_expression.column may be used like any other SQL expression element such as within :func:_expression.select constructs::

from sqlalchemy.sql import column

id, name = column("id"), column("name")
stmt = select(id, name).select_from("user")

The text handled by :func:_expression.column is assumed to be handled like the name of a database column; if the string contains mixed case, special characters, or matches a known reserved word on the target backend, the column expression will render using the quoting behavior determined by the backend. To produce a textual SQL expression that is rendered exactly without any quoting, use :func:_expression.literal_column instead, or pass True as the value of :paramref:_expression.column.is_literal. Additionally, full SQL statements are best handled using the :func:_expression.text construct.

:func:_expression.column can be used in a table-like fashion by combining it with the :func:.table function (which is the lightweight analogue to :class:_schema.Table ) to produce a working table construct with minimal boilerplate::

from sqlalchemy import table, column, select

user = table("user",
        column("id"),
        column("name"),
        column("description"),
)

stmt = select(user.c.description).where(user.c.name == 'wendy')

A :func:_expression.column / :func:.table construct like that illustrated above can be created in an ad-hoc fashion and is not associated with any :class:_schema.MetaData, DDL, or events, unlike its :class:_schema.Table counterpart.

:param text: the text of the element.

:param type: :class:_types.TypeEngine object which can associate this :class:.ColumnClause with a type.

:param is_literal: if True, the :class:.ColumnClause is assumed to be an exact expression that will be delivered to the output with no quoting rules applied regardless of case sensitive settings. the :func:_expression.literal_column() function essentially invokes :func:_expression.column while passing is_literal=True.

.. seealso::

:class:`_schema.Column`

:func:`_expression.literal_column`

:func:`.table`

:func:`_expression.text`

:ref:`tutorial_select_arbitrary_text`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def column(
    text: str,
    type_: Optional[_TypeEngineArgument[_T]] = None,
    is_literal: bool = False,
    _selectable: Optional[FromClause] = None,
) -> ColumnClause[_T]:
    """Produce a :class:`.ColumnClause` object.

    The :class:`.ColumnClause` is a lightweight analogue to the
    :class:`_schema.Column` class.  The :func:`_expression.column`
    function can
    be invoked with just a name alone, as in::

        from sqlalchemy import column

        id, name = column("id"), column("name")
        stmt = select(id, name).select_from("user")

    The above statement would produce SQL like::

        SELECT id, name FROM user

    Once constructed, :func:`_expression.column`
    may be used like any other SQL
    expression element such as within :func:`_expression.select`
    constructs::

        from sqlalchemy.sql import column

        id, name = column("id"), column("name")
        stmt = select(id, name).select_from("user")

    The text handled by :func:`_expression.column`
    is assumed to be handled
    like the name of a database column; if the string contains mixed case,
    special characters, or matches a known reserved word on the target
    backend, the column expression will render using the quoting
    behavior determined by the backend.  To produce a textual SQL
    expression that is rendered exactly without any quoting,
    use :func:`_expression.literal_column` instead,
    or pass ``True`` as the
    value of :paramref:`_expression.column.is_literal`.   Additionally,
    full SQL
    statements are best handled using the :func:`_expression.text`
    construct.

    :func:`_expression.column` can be used in a table-like
    fashion by combining it with the :func:`.table` function
    (which is the lightweight analogue to :class:`_schema.Table`
    ) to produce
    a working table construct with minimal boilerplate::

        from sqlalchemy import table, column, select

        user = table("user",
                column("id"),
                column("name"),
                column("description"),
        )

        stmt = select(user.c.description).where(user.c.name == 'wendy')

    A :func:`_expression.column` / :func:`.table`
    construct like that illustrated
    above can be created in an
    ad-hoc fashion and is not associated with any
    :class:`_schema.MetaData`, DDL, or events, unlike its
    :class:`_schema.Table` counterpart.

    :param text: the text of the element.

    :param type: :class:`_types.TypeEngine` object which can associate
      this :class:`.ColumnClause` with a type.

    :param is_literal: if True, the :class:`.ColumnClause` is assumed to
      be an exact expression that will be delivered to the output with no
      quoting rules applied regardless of case sensitive settings. the
      :func:`_expression.literal_column()` function essentially invokes
      :func:`_expression.column` while passing ``is_literal=True``.

    .. seealso::

        :class:`_schema.Column`

        :func:`_expression.literal_column`

        :func:`.table`

        :func:`_expression.text`

        :ref:`tutorial_select_arbitrary_text`

    """
    return ColumnClause(text, type_, is_literal, _selectable)

cte

cte(
    selectable: HasCTE,
    name: Optional[str] = None,
    recursive: bool = False,
) -> CTE

Return a new :class:_expression.CTE, or Common Table Expression instance.

Please see :meth:_expression.HasCTE.cte for detail on CTE usage.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def cte(
    selectable: HasCTE, name: Optional[str] = None, recursive: bool = False
) -> CTE:
    r"""Return a new :class:`_expression.CTE`,
    or Common Table Expression instance.

    Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.

    """
    return coercions.expect(roles.HasCTERole, selectable).cte(
        name=name, recursive=recursive
    )

delete

delete(table: _DMLTableArgument) -> Delete

Construct :class:_expression.Delete object.

E.g.::

from sqlalchemy import delete

stmt = (
    delete(user_table).
    where(user_table.c.id == 5)
)

Similar functionality is available via the :meth:_expression.TableClause.delete method on :class:_schema.Table.

:param table: The table to delete rows from.

.. seealso::

:ref:`tutorial_core_update_delete` - in the :ref:`unified_tutorial`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_dml_constructors.py

def delete(table: _DMLTableArgument) -> Delete:
    r"""Construct :class:`_expression.Delete` object.

    E.g.::

        from sqlalchemy import delete

        stmt = (
            delete(user_table).
            where(user_table.c.id == 5)
        )

    Similar functionality is available via the
    :meth:`_expression.TableClause.delete` method on
    :class:`_schema.Table`.

    :param table: The table to delete rows from.

    .. seealso::

        :ref:`tutorial_core_update_delete` - in the :ref:`unified_tutorial`


    """
    return Delete(table)

desc

desc(
    column: _ColumnExpressionOrStrLabelArgument[_T],
) -> UnaryExpression[_T]

Produce a descending ORDER BY clause element.

e.g.::

from sqlalchemy import desc

stmt = select(users_table).order_by(desc(users_table.c.name))

will produce SQL as::

SELECT id, name FROM user ORDER BY name DESC

The :func:.desc function is a standalone version of the :meth:_expression.ColumnElement.desc method available on all SQL expressions, e.g.::

stmt = select(users_table).order_by(users_table.c.name.desc())

:param column: A :class:_expression.ColumnElement (e.g. scalar SQL expression) with which to apply the :func:.desc operation.

.. seealso::

:func:`.asc`

:func:`.nulls_first`

:func:`.nulls_last`

:meth:`_expression.Select.order_by`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def desc(
    column: _ColumnExpressionOrStrLabelArgument[_T],
) -> UnaryExpression[_T]:
    """Produce a descending ``ORDER BY`` clause element.

    e.g.::

        from sqlalchemy import desc

        stmt = select(users_table).order_by(desc(users_table.c.name))

    will produce SQL as::

        SELECT id, name FROM user ORDER BY name DESC

    The :func:`.desc` function is a standalone version of the
    :meth:`_expression.ColumnElement.desc`
    method available on all SQL expressions,
    e.g.::


        stmt = select(users_table).order_by(users_table.c.name.desc())

    :param column: A :class:`_expression.ColumnElement` (e.g.
     scalar SQL expression)
     with which to apply the :func:`.desc` operation.

    .. seealso::

        :func:`.asc`

        :func:`.nulls_first`

        :func:`.nulls_last`

        :meth:`_expression.Select.order_by`

    """
    return UnaryExpression._create_desc(column)

distinct

distinct(
    expr: _ColumnExpressionArgument[_T],
) -> UnaryExpression[_T]

Produce an column-expression-level unary DISTINCT clause.

This applies the DISTINCT keyword to an individual column expression, and is typically contained within an aggregate function, as in::

from sqlalchemy import distinct, func
stmt = select(func.count(distinct(users_table.c.name)))

The above would produce an expression resembling::

SELECT COUNT(DISTINCT name) FROM user

The :func:.distinct function is also available as a column-level method, e.g. :meth:_expression.ColumnElement.distinct, as in::

stmt = select(func.count(users_table.c.name.distinct()))

The :func:.distinct operator is different from the :meth:_expression.Select.distinct method of :class:_expression.Select, which produces a SELECT statement with DISTINCT applied to the result set as a whole, e.g. a SELECT DISTINCT expression. See that method for further information.

.. seealso::

:meth:`_expression.ColumnElement.distinct`

:meth:`_expression.Select.distinct`

:data:`.func`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
    """Produce an column-expression-level unary ``DISTINCT`` clause.

    This applies the ``DISTINCT`` keyword to an individual column
    expression, and is typically contained within an aggregate function,
    as in::

        from sqlalchemy import distinct, func
        stmt = select(func.count(distinct(users_table.c.name)))

    The above would produce an expression resembling::

        SELECT COUNT(DISTINCT name) FROM user

    The :func:`.distinct` function is also available as a column-level
    method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::

        stmt = select(func.count(users_table.c.name.distinct()))

    The :func:`.distinct` operator is different from the
    :meth:`_expression.Select.distinct` method of
    :class:`_expression.Select`,
    which produces a ``SELECT`` statement
    with ``DISTINCT`` applied to the result set as a whole,
    e.g. a ``SELECT DISTINCT`` expression.  See that method for further
    information.

    .. seealso::

        :meth:`_expression.ColumnElement.distinct`

        :meth:`_expression.Select.distinct`

        :data:`.func`

    """
    return UnaryExpression._create_distinct(expr)

except_

except_(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return an EXCEPT of multiple selectables.

The returned object is an instance of :class:_expression.CompoundSelect.

:param *selects: a list of :class:_expression.Select instances.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def except_(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect:
    r"""Return an ``EXCEPT`` of multiple selectables.

    The returned object is an instance of
    :class:`_expression.CompoundSelect`.

    :param \*selects:
      a list of :class:`_expression.Select` instances.

    """
    return CompoundSelect._create_except(*selects)

except_all

except_all(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return an EXCEPT ALL of multiple selectables.

The returned object is an instance of :class:_expression.CompoundSelect.

:param *selects: a list of :class:_expression.Select instances.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def except_all(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect:
    r"""Return an ``EXCEPT ALL`` of multiple selectables.

    The returned object is an instance of
    :class:`_expression.CompoundSelect`.

    :param \*selects:
      a list of :class:`_expression.Select` instances.

    """
    return CompoundSelect._create_except_all(*selects)

exists

exists(
    __argument: Optional[
        Union[
            _ColumnsClauseArgument[Any],
            SelectBase,
            ScalarSelect[Any],
        ]
    ] = None,
) -> Exists

Construct a new :class:_expression.Exists construct.

The :func:_sql.exists can be invoked by itself to produce an :class:_sql.Exists construct, which will accept simple WHERE criteria::

exists_criteria = exists().where(table1.c.col1 == table2.c.col2)

However, for greater flexibility in constructing the SELECT, an existing :class:_sql.Select construct may be converted to an :class:_sql.Exists, most conveniently by making use of the :meth:_sql.SelectBase.exists method::

exists_criteria = (
    select(table2.c.col2).
    where(table1.c.col1 == table2.c.col2).
    exists()
)

The EXISTS criteria is then used inside of an enclosing SELECT::

stmt = select(table1.c.col1).where(exists_criteria)

The above statement will then be of the form::

SELECT col1 FROM table1 WHERE EXISTS
(SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)

.. seealso::

:ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.

:meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
``EXISTS`` clause.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def exists(
    __argument: Optional[
        Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]
    ] = None,
) -> Exists:
    """Construct a new :class:`_expression.Exists` construct.

    The :func:`_sql.exists` can be invoked by itself to produce an
    :class:`_sql.Exists` construct, which will accept simple WHERE
    criteria::

        exists_criteria = exists().where(table1.c.col1 == table2.c.col2)

    However, for greater flexibility in constructing the SELECT, an
    existing :class:`_sql.Select` construct may be converted to an
    :class:`_sql.Exists`, most conveniently by making use of the
    :meth:`_sql.SelectBase.exists` method::

        exists_criteria = (
            select(table2.c.col2).
            where(table1.c.col1 == table2.c.col2).
            exists()
        )

    The EXISTS criteria is then used inside of an enclosing SELECT::

        stmt = select(table1.c.col1).where(exists_criteria)

    The above statement will then be of the form::

        SELECT col1 FROM table1 WHERE EXISTS
        (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)

    .. seealso::

        :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.

        :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
        ``EXISTS`` clause.

    """  # noqa: E501

    return Exists(__argument)

extract

extract(
    field: str, expr: _ColumnExpressionArgument[Any]
) -> Extract

Return a :class:.Extract construct.

This is typically available as :func:.extract as well as func.extract from the :data:.func namespace.

:param field: The field to extract.

:param expr: A column or Python scalar expression serving as the right side of the EXTRACT expression.

E.g.::

from sqlalchemy import extract
from sqlalchemy import table, column

logged_table = table("user",
        column("id"),
        column("date_created"),
)

stmt = select(logged_table.c.id).where(
    extract("YEAR", logged_table.c.date_created) == 2021
)

In the above example, the statement is used to select ids from the database where the YEAR component matches a specific value.

Similarly, one can also select an extracted component::

stmt = select(
    extract("YEAR", logged_table.c.date_created)
).where(logged_table.c.id == 1)

The implementation of EXTRACT may vary across database backends. Users are reminded to consult their database documentation.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
    """Return a :class:`.Extract` construct.

    This is typically available as :func:`.extract`
    as well as ``func.extract`` from the
    :data:`.func` namespace.

    :param field: The field to extract.

    :param expr: A column or Python scalar expression serving as the
      right side of the ``EXTRACT`` expression.

    E.g.::

        from sqlalchemy import extract
        from sqlalchemy import table, column

        logged_table = table("user",
                column("id"),
                column("date_created"),
        )

        stmt = select(logged_table.c.id).where(
            extract("YEAR", logged_table.c.date_created) == 2021
        )

    In the above example, the statement is used to select ids from the
    database where the ``YEAR`` component matches a specific value.

    Similarly, one can also select an extracted component::

        stmt = select(
            extract("YEAR", logged_table.c.date_created)
        ).where(logged_table.c.id == 1)

    The implementation of ``EXTRACT`` may vary across database backends.
    Users are reminded to consult their database documentation.
    """
    return Extract(field, expr)

false

false() -> False_

Return a :class:.False_ construct.

E.g.:

.. sourcecode:: pycon+sql

>>> from sqlalchemy import false
>>> print(select(t.c.x).where(false()))
{printsql}SELECT x FROM t WHERE false

A backend which does not support true/false constants will render as an expression against 1 or 0:

.. sourcecode:: pycon+sql

>>> print(select(t.c.x).where(false()))
{printsql}SELECT x FROM t WHERE 0 = 1

The :func:.true and :func:.false constants also feature "short circuit" operation within an :func:.and_ or :func:.or_ conjunction:

.. sourcecode:: pycon+sql

>>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
{printsql}SELECT x FROM t WHERE true{stop}

>>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
{printsql}SELECT x FROM t WHERE false{stop}

.. seealso::

:func:`.true`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def false() -> False_:
    """Return a :class:`.False_` construct.

    E.g.:

    .. sourcecode:: pycon+sql

        >>> from sqlalchemy import false
        >>> print(select(t.c.x).where(false()))
        {printsql}SELECT x FROM t WHERE false

    A backend which does not support true/false constants will render as
    an expression against 1 or 0:

    .. sourcecode:: pycon+sql

        >>> print(select(t.c.x).where(false()))
        {printsql}SELECT x FROM t WHERE 0 = 1

    The :func:`.true` and :func:`.false` constants also feature
    "short circuit" operation within an :func:`.and_` or :func:`.or_`
    conjunction:

    .. sourcecode:: pycon+sql

        >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
        {printsql}SELECT x FROM t WHERE true{stop}

        >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
        {printsql}SELECT x FROM t WHERE false{stop}

    .. seealso::

        :func:`.true`

    """

    return False_._instance()

funcfilter

funcfilter(
    func: FunctionElement[_T],
    *criterion: _ColumnExpressionArgument[bool],
) -> FunctionFilter[_T]

Produce a :class:.FunctionFilter object against a function.

Used against aggregate and window functions, for database backends that support the "FILTER" clause.

E.g.::

from sqlalchemy import funcfilter
funcfilter(func.count(1), MyClass.name == 'some name')

Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".

This function is also available from the :data:~.expression.func construct itself via the :meth:.FunctionElement.filter method.

.. seealso::

:ref:`tutorial_functions_within_group` - in the
:ref:`unified_tutorial`

:meth:`.FunctionElement.filter`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def funcfilter(
    func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
) -> FunctionFilter[_T]:
    """Produce a :class:`.FunctionFilter` object against a function.

    Used against aggregate and window functions,
    for database backends that support the "FILTER" clause.

    E.g.::

        from sqlalchemy import funcfilter
        funcfilter(func.count(1), MyClass.name == 'some name')

    Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".

    This function is also available from the :data:`~.expression.func`
    construct itself via the :meth:`.FunctionElement.filter` method.

    .. seealso::

        :ref:`tutorial_functions_within_group` - in the
        :ref:`unified_tutorial`

        :meth:`.FunctionElement.filter`

    """
    return FunctionFilter(func, *criterion)

insert

insert(table: _DMLTableArgument) -> Insert

Construct an :class:_expression.Insert object.

E.g.::

from sqlalchemy import insert

stmt = (
    insert(user_table).
    values(name='username', fullname='Full Username')
)

Similar functionality is available via the :meth:_expression.TableClause.insert method on :class:_schema.Table.

.. seealso::

:ref:`tutorial_core_insert` - in the :ref:`unified_tutorial`

:param table: :class:_expression.TableClause which is the subject of the insert.

:param values: collection of values to be inserted; see :meth:_expression.Insert.values for a description of allowed formats here. Can be omitted entirely; a :class:_expression.Insert construct will also dynamically render the VALUES clause at execution time based on the parameters passed to :meth:_engine.Connection.execute.

:param inline: if True, no attempt will be made to retrieve the SQL-generated default values to be provided within the statement; in particular, this allows SQL expressions to be rendered 'inline' within the statement without the need to pre-execute them beforehand; for backends that support "returning", this turns off the "implicit returning" feature for the statement.

If both :paramref:_expression.insert.values and compile-time bind parameters are present, the compile-time bind parameters override the information specified within :paramref:_expression.insert.values on a per-key basis.

The keys within :paramref:_expression.Insert.values can be either :class:~sqlalchemy.schema.Column objects or their string identifiers. Each key may reference one of:

• a literal data value (i.e. string, number, etc.);
• a Column object;
• a SELECT statement.

If a SELECT statement is specified which references this INSERT statement's table, the statement will be correlated against the INSERT statement.

.. seealso::

:ref:`tutorial_core_insert` - in the :ref:`unified_tutorial`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_dml_constructors.py

def insert(table: _DMLTableArgument) -> Insert:
    """Construct an :class:`_expression.Insert` object.

    E.g.::

        from sqlalchemy import insert

        stmt = (
            insert(user_table).
            values(name='username', fullname='Full Username')
        )

    Similar functionality is available via the
    :meth:`_expression.TableClause.insert` method on
    :class:`_schema.Table`.

    .. seealso::

        :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial`


    :param table: :class:`_expression.TableClause`
     which is the subject of the
     insert.

    :param values: collection of values to be inserted; see
     :meth:`_expression.Insert.values`
     for a description of allowed formats here.
     Can be omitted entirely; a :class:`_expression.Insert` construct
     will also dynamically render the VALUES clause at execution time
     based on the parameters passed to :meth:`_engine.Connection.execute`.

    :param inline: if True, no attempt will be made to retrieve the
     SQL-generated default values to be provided within the statement;
     in particular,
     this allows SQL expressions to be rendered 'inline' within the
     statement without the need to pre-execute them beforehand; for
     backends that support "returning", this turns off the "implicit
     returning" feature for the statement.

    If both :paramref:`_expression.insert.values` and compile-time bind
    parameters are present, the compile-time bind parameters override the
    information specified within :paramref:`_expression.insert.values` on a
    per-key basis.

    The keys within :paramref:`_expression.Insert.values` can be either
    :class:`~sqlalchemy.schema.Column` objects or their string
    identifiers. Each key may reference one of:

    * a literal data value (i.e. string, number, etc.);
    * a Column object;
    * a SELECT statement.

    If a ``SELECT`` statement is specified which references this
    ``INSERT`` statement's table, the statement will be correlated
    against the ``INSERT`` statement.

    .. seealso::

        :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial`

    """
    return Insert(table)

intersect

intersect(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return an INTERSECT of multiple selectables.

The returned object is an instance of :class:_expression.CompoundSelect.

:param *selects: a list of :class:_expression.Select instances.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def intersect(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect:
    r"""Return an ``INTERSECT`` of multiple selectables.

    The returned object is an instance of
    :class:`_expression.CompoundSelect`.

    :param \*selects:
      a list of :class:`_expression.Select` instances.

    """
    return CompoundSelect._create_intersect(*selects)

intersect_all

intersect_all(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return an INTERSECT ALL of multiple selectables.

The returned object is an instance of :class:_expression.CompoundSelect.

:param *selects: a list of :class:_expression.Select instances.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def intersect_all(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect:
    r"""Return an ``INTERSECT ALL`` of multiple selectables.

    The returned object is an instance of
    :class:`_expression.CompoundSelect`.

    :param \*selects:
      a list of :class:`_expression.Select` instances.


    """
    return CompoundSelect._create_intersect_all(*selects)

join

join(
    left: _FromClauseArgument,
    right: _FromClauseArgument,
    onclause: Optional[_OnClauseArgument] = None,
    isouter: bool = False,
    full: bool = False,
) -> Join

Produce a :class:_expression.Join object, given two :class:_expression.FromClause expressions.

E.g.::

j = join(user_table, address_table,
         user_table.c.id == address_table.c.user_id)
stmt = select(user_table).select_from(j)

would emit SQL along the lines of::

SELECT user.id, user.name FROM user
JOIN address ON user.id = address.user_id

Similar functionality is available given any :class:_expression.FromClause object (e.g. such as a :class:_schema.Table) using the :meth:_expression.FromClause.join method.

:param left: The left side of the join.

:param right: the right side of the join; this is any :class:_expression.FromClause object such as a :class:_schema.Table object, and may also be a selectable-compatible object such as an ORM-mapped class.

:param onclause: a SQL expression representing the ON clause of the join. If left at None, :meth:_expression.FromClause.join will attempt to join the two tables based on a foreign key relationship.

:param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.

:param full: if True, render a FULL OUTER JOIN, instead of JOIN.

.. seealso::

:meth:`_expression.FromClause.join` - method form,
based on a given left side.

:class:`_expression.Join` - the type of object produced.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def join(
    left: _FromClauseArgument,
    right: _FromClauseArgument,
    onclause: Optional[_OnClauseArgument] = None,
    isouter: bool = False,
    full: bool = False,
) -> Join:
    """Produce a :class:`_expression.Join` object, given two
    :class:`_expression.FromClause`
    expressions.

    E.g.::

        j = join(user_table, address_table,
                 user_table.c.id == address_table.c.user_id)
        stmt = select(user_table).select_from(j)

    would emit SQL along the lines of::

        SELECT user.id, user.name FROM user
        JOIN address ON user.id = address.user_id

    Similar functionality is available given any
    :class:`_expression.FromClause` object (e.g. such as a
    :class:`_schema.Table`) using
    the :meth:`_expression.FromClause.join` method.

    :param left: The left side of the join.

    :param right: the right side of the join; this is any
     :class:`_expression.FromClause` object such as a
     :class:`_schema.Table` object, and
     may also be a selectable-compatible object such as an ORM-mapped
     class.

    :param onclause: a SQL expression representing the ON clause of the
     join.  If left at ``None``, :meth:`_expression.FromClause.join`
     will attempt to
     join the two tables based on a foreign key relationship.

    :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.

    :param full: if True, render a FULL OUTER JOIN, instead of JOIN.

    .. seealso::

        :meth:`_expression.FromClause.join` - method form,
        based on a given left side.

        :class:`_expression.Join` - the type of object produced.

    """

    return Join(left, right, onclause, isouter, full)

label

label(
    name: str,
    element: _ColumnExpressionArgument[_T],
    type_: Optional[_TypeEngineArgument[_T]] = None,
) -> Label[_T]

Return a :class:Label object for the given :class:_expression.ColumnElement.

A label changes the name of an element in the columns clause of a SELECT statement, typically via the AS SQL keyword.

This functionality is more conveniently available via the :meth:_expression.ColumnElement.label method on :class:_expression.ColumnElement.

:param name: label name

:param obj: a :class:_expression.ColumnElement.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def label(
    name: str,
    element: _ColumnExpressionArgument[_T],
    type_: Optional[_TypeEngineArgument[_T]] = None,
) -> Label[_T]:
    """Return a :class:`Label` object for the
    given :class:`_expression.ColumnElement`.

    A label changes the name of an element in the columns clause of a
    ``SELECT`` statement, typically via the ``AS`` SQL keyword.

    This functionality is more conveniently available via the
    :meth:`_expression.ColumnElement.label` method on
    :class:`_expression.ColumnElement`.

    :param name: label name

    :param obj: a :class:`_expression.ColumnElement`.

    """
    return Label(name, element, type_)

lambda_stmt

lambda_stmt(
    lmb: _StmtLambdaType,
    enable_tracking: bool = True,
    track_closure_variables: bool = True,
    track_on: Optional[object] = None,
    global_track_bound_values: bool = True,
    track_bound_values: bool = True,
    lambda_cache: Optional[_LambdaCacheType] = None,
) -> StatementLambdaElement

Produce a SQL statement that is cached as a lambda.

The Python code object within the lambda is scanned for both Python literals that will become bound parameters as well as closure variables that refer to Core or ORM constructs that may vary. The lambda itself will be invoked only once per particular set of constructs detected.

E.g.::

from sqlalchemy import lambda_stmt

stmt = lambda_stmt(lambda: table.select())
stmt += lambda s: s.where(table.c.id == 5)

result = connection.execute(stmt)

The object returned is an instance of :class:_sql.StatementLambdaElement.

.. versionadded:: 1.4

:param lmb: a Python function, typically a lambda, which takes no arguments and returns a SQL expression construct :param enable_tracking: when False, all scanning of the given lambda for changes in closure variables or bound parameters is disabled. Use for a lambda that produces the identical results in all cases with no parameterization. :param track_closure_variables: when False, changes in closure variables within the lambda will not be scanned. Use for a lambda where the state of its closure variables will never change the SQL structure returned by the lambda. :param track_bound_values: when False, bound parameter tracking will be disabled for the given lambda. Use for a lambda that either does not produce any bound values, or where the initial bound values never change. :param global_track_bound_values: when False, bound parameter tracking will be disabled for the entire statement including additional links added via the :meth:_sql.StatementLambdaElement.add_criteria method. :param lambda_cache: a dictionary or other mapping-like object where information about the lambda's Python code as well as the tracked closure variables in the lambda itself will be stored. Defaults to a global LRU cache. This cache is independent of the "compiled_cache" used by the :class:_engine.Connection object.

.. seealso::

:ref:`engine_lambda_caching`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/lambdas.py

def lambda_stmt(
    lmb: _StmtLambdaType,
    enable_tracking: bool = True,
    track_closure_variables: bool = True,
    track_on: Optional[object] = None,
    global_track_bound_values: bool = True,
    track_bound_values: bool = True,
    lambda_cache: Optional[_LambdaCacheType] = None,
) -> StatementLambdaElement:
    """Produce a SQL statement that is cached as a lambda.

    The Python code object within the lambda is scanned for both Python
    literals that will become bound parameters as well as closure variables
    that refer to Core or ORM constructs that may vary.   The lambda itself
    will be invoked only once per particular set of constructs detected.

    E.g.::

        from sqlalchemy import lambda_stmt

        stmt = lambda_stmt(lambda: table.select())
        stmt += lambda s: s.where(table.c.id == 5)

        result = connection.execute(stmt)

    The object returned is an instance of :class:`_sql.StatementLambdaElement`.

    .. versionadded:: 1.4

    :param lmb: a Python function, typically a lambda, which takes no arguments
     and returns a SQL expression construct
    :param enable_tracking: when False, all scanning of the given lambda for
     changes in closure variables or bound parameters is disabled.  Use for
     a lambda that produces the identical results in all cases with no
     parameterization.
    :param track_closure_variables: when False, changes in closure variables
     within the lambda will not be scanned.   Use for a lambda where the
     state of its closure variables will never change the SQL structure
     returned by the lambda.
    :param track_bound_values: when False, bound parameter tracking will
     be disabled for the given lambda.  Use for a lambda that either does
     not produce any bound values, or where the initial bound values never
     change.
    :param global_track_bound_values: when False, bound parameter tracking
     will be disabled for the entire statement including additional links
     added via the :meth:`_sql.StatementLambdaElement.add_criteria` method.
    :param lambda_cache: a dictionary or other mapping-like object where
     information about the lambda's Python code as well as the tracked closure
     variables in the lambda itself will be stored.   Defaults
     to a global LRU cache.  This cache is independent of the "compiled_cache"
     used by the :class:`_engine.Connection` object.

    .. seealso::

        :ref:`engine_lambda_caching`


    """

    return StatementLambdaElement(
        lmb,
        roles.StatementRole,
        LambdaOptions(
            enable_tracking=enable_tracking,
            track_on=track_on,
            track_closure_variables=track_closure_variables,
            global_track_bound_values=global_track_bound_values,
            track_bound_values=track_bound_values,
            lambda_cache=lambda_cache,
        ),
    )

lateral

lateral(
    selectable: Union[SelectBase, _FromClauseArgument],
    name: Optional[str] = None,
) -> LateralFromClause

Return a :class:_expression.Lateral object.

:class:_expression.Lateral is an :class:_expression.Alias subclass that represents a subquery with the LATERAL keyword applied to it.

The special behavior of a LATERAL subquery is that it appears in the FROM clause of an enclosing SELECT, but may correlate to other FROM clauses of that SELECT. It is a special case of subquery only supported by a small number of backends, currently more recent PostgreSQL versions.

.. seealso::

:ref:`tutorial_lateral_correlation` -  overview of usage.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def lateral(
    selectable: Union[SelectBase, _FromClauseArgument],
    name: Optional[str] = None,
) -> LateralFromClause:
    """Return a :class:`_expression.Lateral` object.

    :class:`_expression.Lateral` is an :class:`_expression.Alias`
    subclass that represents
    a subquery with the LATERAL keyword applied to it.

    The special behavior of a LATERAL subquery is that it appears in the
    FROM clause of an enclosing SELECT, but may correlate to other
    FROM clauses of that SELECT.   It is a special case of subquery
    only supported by a small number of backends, currently more recent
    PostgreSQL versions.

    .. seealso::

        :ref:`tutorial_lateral_correlation` -  overview of usage.

    """
    return Lateral._factory(selectable, name=name)

literal

literal(
    value: Any,
    type_: _TypeEngineArgument[_T],
    literal_execute: bool = False,
) -> BindParameter[_T]

literal(
    value: _T,
    type_: None = None,
    literal_execute: bool = False,
) -> BindParameter[_T]

literal(
    value: Any,
    type_: Optional[_TypeEngineArgument[Any]] = None,
    literal_execute: bool = False,
) -> BindParameter[Any]

literal(
    value: Any,
    type_: Optional[_TypeEngineArgument[Any]] = None,
    literal_execute: bool = False,
) -> BindParameter[Any]

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

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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,
    )

literal_column

literal_column(
    text: str,
    type_: Optional[_TypeEngineArgument[_T]] = None,
) -> ColumnClause[_T]

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`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py

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)

not_

not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]

not_(
    clause: _ColumnExpressionArgument[_T],
) -> ColumnElement[_T]

not_(
    clause: _ColumnExpressionArgument[_T],
) -> ColumnElement[_T]

Return a negation of the given clause, i.e. NOT(clause).

The ~ operator is also overloaded on all :class:_expression.ColumnElement subclasses to produce the same result.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
    """Return a negation of the given clause, i.e. ``NOT(clause)``.

    The ``~`` operator is also overloaded on all
    :class:`_expression.ColumnElement` subclasses to produce the
    same result.

    """

    return coercions.expect(roles.ExpressionElementRole, clause).__invert__()

null

null() -> Null

Return a constant :class:.Null construct.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def null() -> Null:
    """Return a constant :class:`.Null` construct."""

    return Null._instance()

nulls_first

nulls_first(
    column: _ColumnExpressionArgument[_T],
) -> UnaryExpression[_T]

Produce the NULLS FIRST modifier for an ORDER BY expression.

:func:.nulls_first is intended to modify the expression produced by :func:.asc or :func:.desc, and indicates how NULL values should be handled when they are encountered during ordering::

from sqlalchemy import desc, nulls_first

stmt = select(users_table).order_by(
    nulls_first(desc(users_table.c.name)))

The SQL expression from the above would resemble::

SELECT id, name FROM user ORDER BY name DESC NULLS FIRST

Like :func:.asc and :func:.desc, :func:.nulls_first is typically invoked from the column expression itself using :meth:_expression.ColumnElement.nulls_first, rather than as its standalone function version, as in::

stmt = select(users_table).order_by(
    users_table.c.name.desc().nulls_first())

.. versionchanged:: 1.4 :func:.nulls_first is renamed from :func:.nullsfirst in previous releases. The previous name remains available for backwards compatibility.

.. seealso::

:func:`.asc`

:func:`.desc`

:func:`.nulls_last`

:meth:`_expression.Select.order_by`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def nulls_first(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
    """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.

    :func:`.nulls_first` is intended to modify the expression produced
    by :func:`.asc` or :func:`.desc`, and indicates how NULL values
    should be handled when they are encountered during ordering::


        from sqlalchemy import desc, nulls_first

        stmt = select(users_table).order_by(
            nulls_first(desc(users_table.c.name)))

    The SQL expression from the above would resemble::

        SELECT id, name FROM user ORDER BY name DESC NULLS FIRST

    Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
    invoked from the column expression itself using
    :meth:`_expression.ColumnElement.nulls_first`,
    rather than as its standalone
    function version, as in::

        stmt = select(users_table).order_by(
            users_table.c.name.desc().nulls_first())

    .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
        :func:`.nullsfirst` in previous releases.
        The previous name remains available for backwards compatibility.

    .. seealso::

        :func:`.asc`

        :func:`.desc`

        :func:`.nulls_last`

        :meth:`_expression.Select.order_by`

    """
    return UnaryExpression._create_nulls_first(column)

nulls_last

nulls_last(
    column: _ColumnExpressionArgument[_T],
) -> UnaryExpression[_T]

Produce the NULLS LAST modifier for an ORDER BY expression.

:func:.nulls_last is intended to modify the expression produced by :func:.asc or :func:.desc, and indicates how NULL values should be handled when they are encountered during ordering::

from sqlalchemy import desc, nulls_last

stmt = select(users_table).order_by(
    nulls_last(desc(users_table.c.name)))

The SQL expression from the above would resemble::

SELECT id, name FROM user ORDER BY name DESC NULLS LAST

Like :func:.asc and :func:.desc, :func:.nulls_last is typically invoked from the column expression itself using :meth:_expression.ColumnElement.nulls_last, rather than as its standalone function version, as in::

stmt = select(users_table).order_by(
    users_table.c.name.desc().nulls_last())

.. versionchanged:: 1.4 :func:.nulls_last is renamed from :func:.nullslast in previous releases. The previous name remains available for backwards compatibility.

.. seealso::

:func:`.asc`

:func:`.desc`

:func:`.nulls_first`

:meth:`_expression.Select.order_by`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def nulls_last(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
    """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.

    :func:`.nulls_last` is intended to modify the expression produced
    by :func:`.asc` or :func:`.desc`, and indicates how NULL values
    should be handled when they are encountered during ordering::


        from sqlalchemy import desc, nulls_last

        stmt = select(users_table).order_by(
            nulls_last(desc(users_table.c.name)))

    The SQL expression from the above would resemble::

        SELECT id, name FROM user ORDER BY name DESC NULLS LAST

    Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
    invoked from the column expression itself using
    :meth:`_expression.ColumnElement.nulls_last`,
    rather than as its standalone
    function version, as in::

        stmt = select(users_table).order_by(
            users_table.c.name.desc().nulls_last())

    .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
        :func:`.nullslast` in previous releases.
        The previous name remains available for backwards compatibility.

    .. seealso::

        :func:`.asc`

        :func:`.desc`

        :func:`.nulls_first`

        :meth:`_expression.Select.order_by`

    """
    return UnaryExpression._create_nulls_last(column)

or_

or_(*clauses)

Produce a conjunction of expressions joined by OR.

E.g.::

from sqlalchemy import or_

stmt = select(users_table).where(
                or_(
                    users_table.c.name == 'wendy',
                    users_table.c.name == 'jack'
                )
            )

The :func:.or_ conjunction is also available using the Python | operator (though note that compound expressions need to be parenthesized in order to function with Python operator precedence behavior)::

stmt = select(users_table).where(
                (users_table.c.name == 'wendy') |
                (users_table.c.name == 'jack')
            )

The :func:.or_ construct must be given at least one positional argument in order to be valid; a :func:.or_ construct with no arguments is ambiguous. To produce an "empty" or dynamically generated :func:.or_ expression, from a given list of expressions, a "default" element of :func:_sql.false (or just False) should be specified::

from sqlalchemy import false
or_criteria = or_(false(), *expressions)

The above expression will compile to SQL as the expression false or 0 = 1, depending on backend, if no other expressions are present. If expressions are present, then the :func:_sql.false value is ignored as it does not affect the outcome of an OR expression which has other elements.

.. deprecated:: 1.4 The :func:.or_ element now requires that at least one argument is passed; creating the :func:.or_ construct with no arguments is deprecated, and will emit a deprecation warning while continuing to produce a blank SQL string.

.. seealso::

:func:`.and_`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def or_(*clauses):  # noqa: F811
    """Produce a conjunction of expressions joined by ``OR``.

    E.g.::

        from sqlalchemy import or_

        stmt = select(users_table).where(
                        or_(
                            users_table.c.name == 'wendy',
                            users_table.c.name == 'jack'
                        )
                    )

    The :func:`.or_` conjunction is also available using the
    Python ``|`` operator (though note that compound expressions
    need to be parenthesized in order to function with Python
    operator precedence behavior)::

        stmt = select(users_table).where(
                        (users_table.c.name == 'wendy') |
                        (users_table.c.name == 'jack')
                    )

    The :func:`.or_` construct must be given at least one positional
    argument in order to be valid; a :func:`.or_` construct with no
    arguments is ambiguous.   To produce an "empty" or dynamically
    generated :func:`.or_`  expression, from a given list of expressions,
    a "default" element of :func:`_sql.false` (or just ``False``) should be
    specified::

        from sqlalchemy import false
        or_criteria = or_(false(), *expressions)

    The above expression will compile to SQL as the expression ``false``
    or ``0 = 1``, depending on backend, if no other expressions are
    present.  If expressions are present, then the :func:`_sql.false` value
    is ignored as it does not affect the outcome of an OR expression which
    has other elements.

    .. deprecated:: 1.4  The :func:`.or_` element now requires that at
       least one argument is passed; creating the :func:`.or_` construct
       with no arguments is deprecated, and will emit a deprecation warning
       while continuing to produce a blank SQL string.

    .. seealso::

        :func:`.and_`

    """
    return BooleanClauseList.or_(*clauses)

outerjoin

outerjoin(
    left: _FromClauseArgument,
    right: _FromClauseArgument,
    onclause: Optional[_OnClauseArgument] = None,
    full: bool = False,
) -> Join

Return an OUTER JOIN clause element.

The returned object is an instance of :class:_expression.Join.

Similar functionality is also available via the :meth:_expression.FromClause.outerjoin method on any :class:_expression.FromClause.

:param left: The left side of the join.

:param right: The right side of the join.

:param onclause: Optional criterion for the ON clause, is derived from foreign key relationships established between left and right otherwise.

To chain joins together, use the :meth:_expression.FromClause.join or :meth:_expression.FromClause.outerjoin methods on the resulting :class:_expression.Join object.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def outerjoin(
    left: _FromClauseArgument,
    right: _FromClauseArgument,
    onclause: Optional[_OnClauseArgument] = None,
    full: bool = False,
) -> Join:
    """Return an ``OUTER JOIN`` clause element.

    The returned object is an instance of :class:`_expression.Join`.

    Similar functionality is also available via the
    :meth:`_expression.FromClause.outerjoin` method on any
    :class:`_expression.FromClause`.

    :param left: The left side of the join.

    :param right: The right side of the join.

    :param onclause:  Optional criterion for the ``ON`` clause, is
      derived from foreign key relationships established between
      left and right otherwise.

    To chain joins together, use the :meth:`_expression.FromClause.join`
    or
    :meth:`_expression.FromClause.outerjoin` methods on the resulting
    :class:`_expression.Join` object.

    """
    return Join(left, right, onclause, isouter=True, full=full)

outparam

outparam(
    key: str, type_: Optional[TypeEngine[_T]] = None
) -> BindParameter[_T]

Create an 'OUT' parameter for usage in functions (stored procedures), for databases which support them.

The outparam can be used like a regular function parameter. The "output" value will be available from the :class:~sqlalchemy.engine.CursorResult object via its out_parameters attribute, which returns a dictionary containing the values.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def outparam(
    key: str, type_: Optional[TypeEngine[_T]] = None
) -> BindParameter[_T]:
    """Create an 'OUT' parameter for usage in functions (stored procedures),
    for databases which support them.

    The ``outparam`` can be used like a regular function parameter.
    The "output" value will be available from the
    :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
    attribute, which returns a dictionary containing the values.

    """
    return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)

over

over(
    element: FunctionElement[_T],
    partition_by: Optional[_ByArgument] = None,
    order_by: Optional[_ByArgument] = None,
    range_: Optional[
        Tuple[Optional[int], Optional[int]]
    ] = None,
    rows: Optional[
        Tuple[Optional[int], Optional[int]]
    ] = None,
) -> Over[_T]

Produce an :class:.Over object against a function.

Used against aggregate or so-called "window" functions, for database backends that support window functions.

:func:_expression.over is usually called using the :meth:.FunctionElement.over method, e.g.::

func.row_number().over(order_by=mytable.c.some_column)

Would produce::

ROW_NUMBER() OVER(ORDER BY some_column)

Ranges are also possible using the :paramref:.expression.over.range_ and :paramref:.expression.over.rows parameters. These mutually-exclusive parameters each accept a 2-tuple, which contains a combination of integers and None::

func.row_number().over(
    order_by=my_table.c.some_column, range_=(None, 0))

The above would produce::

ROW_NUMBER() OVER(ORDER BY some_column
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

A value of None indicates "unbounded", a value of zero indicates "current row", and negative / positive integers indicate "preceding" and "following":

• RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::

  func.row_number().over(order_by='x', range_=(-5, 10))

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::

  func.row_number().over(order_by='x', rows=(None, 0))

• RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::

  func.row_number().over(order_by='x', range_=(-2, None))

• RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::

  func.row_number().over(order_by='x', range_=(1, 3))

:param element: a :class:.FunctionElement, :class:.WithinGroup, or other compatible construct. :param partition_by: a column element or string, or a list of such, that will be used as the PARTITION BY clause of the OVER construct. :param order_by: a column element or string, or a list of such, that will be used as the ORDER BY clause of the OVER construct. :param range_: optional range clause for the window. This is a tuple value which can contain integer values or None, and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.

:param rows: optional rows clause for the window. This is a tuple value which can contain integer values or None, and will render a ROWS BETWEEN PRECEDING / FOLLOWING clause.

This function is also available from the :data:~.expression.func construct itself via the :meth:.FunctionElement.over method.

.. seealso::

:ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`

:data:`.expression.func`

:func:`_expression.within_group`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def over(
    element: FunctionElement[_T],
    partition_by: Optional[_ByArgument] = None,
    order_by: Optional[_ByArgument] = None,
    range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
    rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
) -> Over[_T]:
    r"""Produce an :class:`.Over` object against a function.

    Used against aggregate or so-called "window" functions,
    for database backends that support window functions.

    :func:`_expression.over` is usually called using
    the :meth:`.FunctionElement.over` method, e.g.::

        func.row_number().over(order_by=mytable.c.some_column)

    Would produce::

        ROW_NUMBER() OVER(ORDER BY some_column)

    Ranges are also possible using the :paramref:`.expression.over.range_`
    and :paramref:`.expression.over.rows` parameters.  These
    mutually-exclusive parameters each accept a 2-tuple, which contains
    a combination of integers and None::

        func.row_number().over(
            order_by=my_table.c.some_column, range_=(None, 0))

    The above would produce::

        ROW_NUMBER() OVER(ORDER BY some_column
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    A value of ``None`` indicates "unbounded", a
    value of zero indicates "current row", and negative / positive
    integers indicate "preceding" and "following":

    * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::

        func.row_number().over(order_by='x', range_=(-5, 10))

    * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::

        func.row_number().over(order_by='x', rows=(None, 0))

    * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::

        func.row_number().over(order_by='x', range_=(-2, None))

    * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::

        func.row_number().over(order_by='x', range_=(1, 3))

    :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
     or other compatible construct.
    :param partition_by: a column element or string, or a list
     of such, that will be used as the PARTITION BY clause
     of the OVER construct.
    :param order_by: a column element or string, or a list
     of such, that will be used as the ORDER BY clause
     of the OVER construct.
    :param range\_: optional range clause for the window.  This is a
     tuple value which can contain integer values or ``None``,
     and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.

    :param rows: optional rows clause for the window.  This is a tuple
     value which can contain integer values or None, and will render
     a ROWS BETWEEN PRECEDING / FOLLOWING clause.

    This function is also available from the :data:`~.expression.func`
    construct itself via the :meth:`.FunctionElement.over` method.

    .. seealso::

        :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`

        :data:`.expression.func`

        :func:`_expression.within_group`

    """
    return Over(element, partition_by, order_by, range_, rows)

select

select(
    __ent0: _TypedColumnClauseArgument[_T0],
) -> Select[Tuple[_T0]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
) -> Select[Tuple[_T0, _T1]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
) -> Select[Tuple[_T0, _T1, _T2]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
) -> Select[Tuple[_T0, _T1, _T2, _T3]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
    __ent6: _TypedColumnClauseArgument[_T6],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
    __ent6: _TypedColumnClauseArgument[_T6],
    __ent7: _TypedColumnClauseArgument[_T7],
) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
    __ent6: _TypedColumnClauseArgument[_T6],
    __ent7: _TypedColumnClauseArgument[_T7],
    __ent8: _TypedColumnClauseArgument[_T8],
) -> Select[
    Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8]
]

select(
    __ent0: _TypedColumnClauseArgument[_T0],
    __ent1: _TypedColumnClauseArgument[_T1],
    __ent2: _TypedColumnClauseArgument[_T2],
    __ent3: _TypedColumnClauseArgument[_T3],
    __ent4: _TypedColumnClauseArgument[_T4],
    __ent5: _TypedColumnClauseArgument[_T5],
    __ent6: _TypedColumnClauseArgument[_T6],
    __ent7: _TypedColumnClauseArgument[_T7],
    __ent8: _TypedColumnClauseArgument[_T8],
    __ent9: _TypedColumnClauseArgument[_T9],
) -> Select[
    Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8, _T9]
]

select(
    *entities: _ColumnsClauseArgument[Any], **__kw: Any
) -> Select[Any]

select(
    *entities: _ColumnsClauseArgument[Any], **__kw: Any
) -> Select[Any]

Construct a new :class:_expression.Select.

.. versionadded:: 1.4 - The :func:_sql.select function now accepts column arguments positionally. The top-level :func:_sql.select function will automatically use the 1.x or 2.x style API based on the incoming arguments; using :func:_sql.select from the sqlalchemy.future module will enforce that only the 2.x style constructor is used.

Similar functionality is also available via the :meth:_expression.FromClause.select method on any :class:_expression.FromClause.

.. seealso::

:ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

:param *entities: Entities to SELECT from. For Core usage, this is typically a series of :class:_expression.ColumnElement and / or :class:_expression.FromClause objects which will form the columns clause of the resulting statement. For those objects that are instances of :class:_expression.FromClause (typically :class:_schema.Table or :class:_expression.Alias objects), the :attr:_expression.FromClause.c collection is extracted to form a collection of :class:_expression.ColumnElement objects.

This parameter will also accept :class:_expression.TextClause constructs as given, as well as ORM-mapped classes.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def select(*entities: _ColumnsClauseArgument[Any], **__kw: Any) -> Select[Any]:
    r"""Construct a new :class:`_expression.Select`.


    .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts
       column arguments positionally.   The top-level :func:`_sql.select`
       function will automatically use the 1.x or 2.x style API based on
       the incoming arguments; using :func:`_sql.select` from the
       ``sqlalchemy.future`` module will enforce that only the 2.x style
       constructor is used.

    Similar functionality is also available via the
    :meth:`_expression.FromClause.select` method on any
    :class:`_expression.FromClause`.

    .. seealso::

        :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

    :param \*entities:
      Entities to SELECT from.  For Core usage, this is typically a series
      of :class:`_expression.ColumnElement` and / or
      :class:`_expression.FromClause`
      objects which will form the columns clause of the resulting
      statement.   For those objects that are instances of
      :class:`_expression.FromClause` (typically :class:`_schema.Table`
      or :class:`_expression.Alias`
      objects), the :attr:`_expression.FromClause.c`
      collection is extracted
      to form a collection of :class:`_expression.ColumnElement` objects.

      This parameter will also accept :class:`_expression.TextClause`
      constructs as
      given, as well as ORM-mapped classes.

    """
    # the keyword args are a necessary element in order for the typing
    # to work out w/ the varargs vs. having named "keyword" arguments that
    # aren't always present.
    if __kw:
        raise _no_kw()
    return Select(*entities)

table

table(
    name: str, *columns: ColumnClause[Any], **kw: Any
) -> TableClause

Produce a new :class:_expression.TableClause.

The object returned is an instance of :class:_expression.TableClause, which represents the "syntactical" portion of the schema-level :class:_schema.Table object. It may be used to construct lightweight table constructs.

:param name: Name of the table.

:param columns: A collection of :func:_expression.column constructs.

:param schema: The schema name for this table.

.. versionadded:: 1.3.18 :func:`_expression.table` can now
   accept a ``schema`` argument.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def table(name: str, *columns: ColumnClause[Any], **kw: Any) -> TableClause:
    """Produce a new :class:`_expression.TableClause`.

    The object returned is an instance of
    :class:`_expression.TableClause`, which
    represents the "syntactical" portion of the schema-level
    :class:`_schema.Table` object.
    It may be used to construct lightweight table constructs.

    :param name: Name of the table.

    :param columns: A collection of :func:`_expression.column` constructs.

    :param schema: The schema name for this table.

        .. versionadded:: 1.3.18 :func:`_expression.table` can now
           accept a ``schema`` argument.
    """

    return TableClause(name, *columns, **kw)

tablesample

tablesample(
    selectable: _FromClauseArgument,
    sampling: Union[float, Function[Any]],
    name: Optional[str] = None,
    seed: Optional[ExpressionElementRole[Any]] = None,
) -> TableSample

Return a :class:_expression.TableSample object.

:class:_expression.TableSample is an :class:_expression.Alias subclass that represents a table with the TABLESAMPLE clause applied to it. :func:_expression.tablesample is also available from the :class:_expression.FromClause class via the :meth:_expression.FromClause.tablesample method.

The TABLESAMPLE clause allows selecting a randomly selected approximate percentage of rows from a table. It supports multiple sampling methods, most commonly BERNOULLI and SYSTEM.

e.g.::

from sqlalchemy import func

selectable = people.tablesample(
            func.bernoulli(1),
            name='alias',
            seed=func.random())
stmt = select(selectable.c.people_id)

Assuming people with a column people_id, the above statement would render as::

SELECT alias.people_id FROM
people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
REPEATABLE (random())

:param sampling: a float percentage between 0 and 100 or :class:_functions.Function.

:param name: optional alias name

:param seed: any real-valued SQL expression. When specified, the REPEATABLE sub-clause is also rendered.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def tablesample(
    selectable: _FromClauseArgument,
    sampling: Union[float, Function[Any]],
    name: Optional[str] = None,
    seed: Optional[roles.ExpressionElementRole[Any]] = None,
) -> TableSample:
    """Return a :class:`_expression.TableSample` object.

    :class:`_expression.TableSample` is an :class:`_expression.Alias`
    subclass that represents
    a table with the TABLESAMPLE clause applied to it.
    :func:`_expression.tablesample`
    is also available from the :class:`_expression.FromClause`
    class via the
    :meth:`_expression.FromClause.tablesample` method.

    The TABLESAMPLE clause allows selecting a randomly selected approximate
    percentage of rows from a table. It supports multiple sampling methods,
    most commonly BERNOULLI and SYSTEM.

    e.g.::

        from sqlalchemy import func

        selectable = people.tablesample(
                    func.bernoulli(1),
                    name='alias',
                    seed=func.random())
        stmt = select(selectable.c.people_id)

    Assuming ``people`` with a column ``people_id``, the above
    statement would render as::

        SELECT alias.people_id FROM
        people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
        REPEATABLE (random())

    :param sampling: a ``float`` percentage between 0 and 100 or
        :class:`_functions.Function`.

    :param name: optional alias name

    :param seed: any real-valued SQL expression.  When specified, the
     REPEATABLE sub-clause is also rendered.

    """
    return TableSample._factory(selectable, sampling, name=name, seed=seed)

text

text(text: str) -> TextClause

Construct a new :class:_expression.TextClause clause, representing a textual SQL string directly.

E.g.::

from sqlalchemy import text

t = text("SELECT * FROM users")
result = connection.execute(t)

The advantages :func:_expression.text provides over a plain string are backend-neutral support for bind parameters, per-statement execution options, as well as bind parameter and result-column typing behavior, allowing SQLAlchemy type constructs to play a role when executing a statement that is specified literally. The construct can also be provided with a .c collection of column elements, allowing it to be embedded in other SQL expression constructs as a subquery.

Bind parameters are specified by name, using the format :name. E.g.::

t = text("SELECT * FROM users WHERE id=:user_id")
result = connection.execute(t, user_id=12)

For SQL statements where a colon is required verbatim, as within an inline string, use a backslash to escape::

t = text(r"SELECT * FROM users WHERE name='\:username'")

The :class:_expression.TextClause construct includes methods which can provide information about the bound parameters as well as the column values which would be returned from the textual statement, assuming it's an executable SELECT type of statement. The :meth:_expression.TextClause.bindparams method is used to provide bound parameter detail, and :meth:_expression.TextClause.columns method allows specification of return columns including names and types::

t = text("SELECT * FROM users WHERE id=:user_id").\
        bindparams(user_id=7).\
        columns(id=Integer, name=String)

for id, name in connection.execute(t):
    print(id, name)

The :func:_expression.text construct is used in cases when a literal string SQL fragment is specified as part of a larger query, such as for the WHERE clause of a SELECT statement::

s = select(users.c.id, users.c.name).where(text("id=:user_id"))
result = connection.execute(s, user_id=12)

:func:_expression.text is also used for the construction of a full, standalone statement using plain text. As such, SQLAlchemy refers to it as an :class:.Executable object and may be used like any other statement passed to an .execute() method.

:param text: the text of the SQL statement to be created. Use :<param> to specify bind parameters; they will be compiled to their engine-specific format.

.. seealso::

:ref:`tutorial_select_arbitrary_text`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
def text(text: str) -> TextClause:
    r"""Construct a new :class:`_expression.TextClause` clause,
    representing
    a textual SQL string directly.

    E.g.::

        from sqlalchemy import text

        t = text("SELECT * FROM users")
        result = connection.execute(t)

    The advantages :func:`_expression.text`
    provides over a plain string are
    backend-neutral support for bind parameters, per-statement
    execution options, as well as
    bind parameter and result-column typing behavior, allowing
    SQLAlchemy type constructs to play a role when executing
    a statement that is specified literally.  The construct can also
    be provided with a ``.c`` collection of column elements, allowing
    it to be embedded in other SQL expression constructs as a subquery.

    Bind parameters are specified by name, using the format ``:name``.
    E.g.::

        t = text("SELECT * FROM users WHERE id=:user_id")
        result = connection.execute(t, user_id=12)

    For SQL statements where a colon is required verbatim, as within
    an inline string, use a backslash to escape::

        t = text(r"SELECT * FROM users WHERE name='\:username'")

    The :class:`_expression.TextClause`
    construct includes methods which can
    provide information about the bound parameters as well as the column
    values which would be returned from the textual statement, assuming
    it's an executable SELECT type of statement.  The
    :meth:`_expression.TextClause.bindparams`
    method is used to provide bound
    parameter detail, and :meth:`_expression.TextClause.columns`
    method allows
    specification of return columns including names and types::

        t = text("SELECT * FROM users WHERE id=:user_id").\
                bindparams(user_id=7).\
                columns(id=Integer, name=String)

        for id, name in connection.execute(t):
            print(id, name)

    The :func:`_expression.text` construct is used in cases when
    a literal string SQL fragment is specified as part of a larger query,
    such as for the WHERE clause of a SELECT statement::

        s = select(users.c.id, users.c.name).where(text("id=:user_id"))
        result = connection.execute(s, user_id=12)

    :func:`_expression.text` is also used for the construction
    of a full, standalone statement using plain text.
    As such, SQLAlchemy refers
    to it as an :class:`.Executable` object and may be used
    like any other statement passed to an ``.execute()`` method.

    :param text:
      the text of the SQL statement to be created.  Use ``:<param>``
      to specify bind parameters; they will be compiled to their
      engine-specific format.

    .. seealso::

        :ref:`tutorial_select_arbitrary_text`

    """
    return TextClause(text)

true

true() -> True_

Return a constant :class:.True_ construct.

E.g.:

.. sourcecode:: pycon+sql

>>> from sqlalchemy import true
>>> print(select(t.c.x).where(true()))
{printsql}SELECT x FROM t WHERE true

A backend which does not support true/false constants will render as an expression against 1 or 0:

.. sourcecode:: pycon+sql

>>> print(select(t.c.x).where(true()))
{printsql}SELECT x FROM t WHERE 1 = 1

The :func:.true and :func:.false constants also feature "short circuit" operation within an :func:.and_ or :func:.or_ conjunction:

.. sourcecode:: pycon+sql

>>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
{printsql}SELECT x FROM t WHERE true{stop}

>>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
{printsql}SELECT x FROM t WHERE false{stop}

.. seealso::

:func:`.false`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def true() -> True_:
    """Return a constant :class:`.True_` construct.

    E.g.:

    .. sourcecode:: pycon+sql

        >>> from sqlalchemy import true
        >>> print(select(t.c.x).where(true()))
        {printsql}SELECT x FROM t WHERE true

    A backend which does not support true/false constants will render as
    an expression against 1 or 0:

    .. sourcecode:: pycon+sql

        >>> print(select(t.c.x).where(true()))
        {printsql}SELECT x FROM t WHERE 1 = 1

    The :func:`.true` and :func:`.false` constants also feature
    "short circuit" operation within an :func:`.and_` or :func:`.or_`
    conjunction:

    .. sourcecode:: pycon+sql

        >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
        {printsql}SELECT x FROM t WHERE true{stop}

        >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
        {printsql}SELECT x FROM t WHERE false{stop}

    .. seealso::

        :func:`.false`

    """

    return True_._instance()

tuple_

tuple_(
    *clauses: _ColumnExpressionArgument[Any],
    types: Optional[
        Sequence[_TypeEngineArgument[Any]]
    ] = None,
) -> Tuple

Return a :class:.Tuple.

Main usage is to produce a composite IN construct using :meth:.ColumnOperators.in_ ::

from sqlalchemy import tuple_

tuple_(table.c.col1, table.c.col2).in_(
    [(1, 2), (5, 12), (10, 19)]
)

.. versionchanged:: 1.3.6 Added support for SQLite IN tuples.

.. warning::

The composite IN construct is not supported by all backends, and is
currently known to work on PostgreSQL, MySQL, and SQLite.
Unsupported backends will raise a subclass of
:class:`~sqlalchemy.exc.DBAPIError` when such an expression is
invoked.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def tuple_(
    *clauses: _ColumnExpressionArgument[Any],
    types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
) -> Tuple:
    """Return a :class:`.Tuple`.

    Main usage is to produce a composite IN construct using
    :meth:`.ColumnOperators.in_` ::

        from sqlalchemy import tuple_

        tuple_(table.c.col1, table.c.col2).in_(
            [(1, 2), (5, 12), (10, 19)]
        )

    .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.

    .. warning::

        The composite IN construct is not supported by all backends, and is
        currently known to work on PostgreSQL, MySQL, and SQLite.
        Unsupported backends will raise a subclass of
        :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
        invoked.

    """
    return Tuple(*clauses, types=types)

type_coerce

type_coerce(
    expression: _ColumnExpressionOrLiteralArgument[Any],
    type_: _TypeEngineArgument[_T],
) -> TypeCoerce[_T]

Associate a SQL expression with a particular type, without rendering CAST.

E.g.::

from sqlalchemy import type_coerce

stmt = select(type_coerce(log_table.date_string, StringDateTime()))

The above construct will produce a :class:.TypeCoerce object, which does not modify the rendering in any way on the SQL side, with the possible exception of a generated label if used in a columns clause context:

.. sourcecode:: sql

SELECT date_string AS date_string FROM log

When result rows are fetched, the StringDateTime type processor will be applied to result rows on behalf of the date_string column.

.. note:: the :func:.type_coerce construct does not render any SQL syntax of its own, including that it does not imply parenthesization. Please use :meth:.TypeCoerce.self_group if explicit parenthesization is required.

In order to provide a named label for the expression, use :meth:_expression.ColumnElement.label::

stmt = select(
    type_coerce(log_table.date_string, StringDateTime()).label('date')
)

A type that features bound-value handling will also have that behavior take effect when literal values or :func:.bindparam constructs are passed to :func:.type_coerce as targets. For example, if a type implements the :meth:.TypeEngine.bind_expression method or :meth:.TypeEngine.bind_processor method or equivalent, these functions will take effect at statement compilation/execution time when a literal value is passed, as in::

# bound-value handling of MyStringType will be applied to the
# literal value "some string"
stmt = select(type_coerce("some string", MyStringType))

When using :func:.type_coerce with composed expressions, note that parenthesis are not applied. If :func:.type_coerce is being used in an operator context where the parenthesis normally present from CAST are necessary, use the :meth:.TypeCoerce.self_group method:

.. sourcecode:: pycon+sql

>>> some_integer = column("someint", Integer)
>>> some_string = column("somestr", String)
>>> expr = type_coerce(some_integer + 5, String) + some_string
>>> print(expr)
{printsql}someint + :someint_1 || somestr{stop}
>>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
>>> print(expr)
{printsql}(someint + :someint_1) || somestr{stop}

:param expression: A SQL expression, such as a :class:_expression.ColumnElement expression or a Python string which will be coerced into a bound literal value.

:param type_: A :class:.TypeEngine class or instance indicating the type to which the expression is coerced.

.. seealso::

:ref:`tutorial_casts`

:func:`.cast`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def type_coerce(
    expression: _ColumnExpressionOrLiteralArgument[Any],
    type_: _TypeEngineArgument[_T],
) -> TypeCoerce[_T]:
    r"""Associate a SQL expression with a particular type, without rendering
    ``CAST``.

    E.g.::

        from sqlalchemy import type_coerce

        stmt = select(type_coerce(log_table.date_string, StringDateTime()))

    The above construct will produce a :class:`.TypeCoerce` object, which
    does not modify the rendering in any way on the SQL side, with the
    possible exception of a generated label if used in a columns clause
    context:

    .. sourcecode:: sql

        SELECT date_string AS date_string FROM log

    When result rows are fetched, the ``StringDateTime`` type processor
    will be applied to result rows on behalf of the ``date_string`` column.

    .. note:: the :func:`.type_coerce` construct does not render any
       SQL syntax of its own, including that it does not imply
       parenthesization.   Please use :meth:`.TypeCoerce.self_group`
       if explicit parenthesization is required.

    In order to provide a named label for the expression, use
    :meth:`_expression.ColumnElement.label`::

        stmt = select(
            type_coerce(log_table.date_string, StringDateTime()).label('date')
        )


    A type that features bound-value handling will also have that behavior
    take effect when literal values or :func:`.bindparam` constructs are
    passed to :func:`.type_coerce` as targets.
    For example, if a type implements the
    :meth:`.TypeEngine.bind_expression`
    method or :meth:`.TypeEngine.bind_processor` method or equivalent,
    these functions will take effect at statement compilation/execution
    time when a literal value is passed, as in::

        # bound-value handling of MyStringType will be applied to the
        # literal value "some string"
        stmt = select(type_coerce("some string", MyStringType))

    When using :func:`.type_coerce` with composed expressions, note that
    **parenthesis are not applied**.   If :func:`.type_coerce` is being
    used in an operator context where the parenthesis normally present from
    CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:

    .. sourcecode:: pycon+sql

        >>> some_integer = column("someint", Integer)
        >>> some_string = column("somestr", String)
        >>> expr = type_coerce(some_integer + 5, String) + some_string
        >>> print(expr)
        {printsql}someint + :someint_1 || somestr{stop}
        >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
        >>> print(expr)
        {printsql}(someint + :someint_1) || somestr{stop}

    :param expression: A SQL expression, such as a
     :class:`_expression.ColumnElement`
     expression or a Python string which will be coerced into a bound
     literal value.

    :param type\_: A :class:`.TypeEngine` class or instance indicating
     the type to which the expression is coerced.

    .. seealso::

        :ref:`tutorial_casts`

        :func:`.cast`

    """  # noqa
    return TypeCoerce(expression, type_)

union

union(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a UNION of multiple selectables.

The returned object is an instance of :class:_expression.CompoundSelect.

A similar :func:union() method is available on all :class:_expression.FromClause subclasses.

:param *selects: a list of :class:_expression.Select instances.

:param **kwargs: available keyword arguments are the same as those of :func:select.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def union(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect:
    r"""Return a ``UNION`` of multiple selectables.

    The returned object is an instance of
    :class:`_expression.CompoundSelect`.

    A similar :func:`union()` method is available on all
    :class:`_expression.FromClause` subclasses.

    :param \*selects:
      a list of :class:`_expression.Select` instances.

    :param \**kwargs:
      available keyword arguments are the same as those of
      :func:`select`.

    """
    return CompoundSelect._create_union(*selects)

union_all

union_all(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect

Return a UNION ALL of multiple selectables.

The returned object is an instance of :class:_expression.CompoundSelect.

A similar :func:union_all() method is available on all :class:_expression.FromClause subclasses.

:param *selects: a list of :class:_expression.Select instances.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def union_all(
    *selects: _SelectStatementForCompoundArgument,
) -> CompoundSelect:
    r"""Return a ``UNION ALL`` of multiple selectables.

    The returned object is an instance of
    :class:`_expression.CompoundSelect`.

    A similar :func:`union_all()` method is available on all
    :class:`_expression.FromClause` subclasses.

    :param \*selects:
      a list of :class:`_expression.Select` instances.

    """
    return CompoundSelect._create_union_all(*selects)

update

update(table: _DMLTableArgument) -> Update

Construct an :class:_expression.Update object.

E.g.::

from sqlalchemy import update

stmt = (
    update(user_table).
    where(user_table.c.id == 5).
    values(name='user #5')
)

Similar functionality is available via the :meth:_expression.TableClause.update method on :class:_schema.Table.

:param table: A :class:_schema.Table object representing the database table to be updated.

.. seealso::

:ref:`tutorial_core_update_delete` - in the :ref:`unified_tutorial`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_dml_constructors.py

def update(table: _DMLTableArgument) -> Update:
    r"""Construct an :class:`_expression.Update` object.

    E.g.::

        from sqlalchemy import update

        stmt = (
            update(user_table).
            where(user_table.c.id == 5).
            values(name='user #5')
        )

    Similar functionality is available via the
    :meth:`_expression.TableClause.update` method on
    :class:`_schema.Table`.

    :param table: A :class:`_schema.Table`
     object representing the database
     table to be updated.


    .. seealso::

        :ref:`tutorial_core_update_delete` - in the :ref:`unified_tutorial`


    """
    return Update(table)

values

values(
    *columns: ColumnClause[Any],
    name: Optional[str] = None,
    literal_binds: bool = False,
) -> Values

Construct a :class:_expression.Values construct.

The column expressions and the actual data for :class:_expression.Values are given in two separate steps. The constructor receives the column expressions typically as :func:_expression.column constructs, and the data is then passed via the :meth:_expression.Values.data method as a list, which can be called multiple times to add more data, e.g.::

from sqlalchemy import column
from sqlalchemy import values

value_expr = values(
    column('id', Integer),
    column('name', String),
    name="my_values"
).data(
    [(1, 'name1'), (2, 'name2'), (3, 'name3')]
)

:param *columns: column expressions, typically composed using :func:_expression.column objects.

:param name: the name for this VALUES construct. If omitted, the VALUES construct will be unnamed in a SQL expression. Different backends may have different requirements here.

:param literal_binds: Defaults to False. Whether or not to render the data values inline in the SQL output, rather than using bound parameters.

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_selectable_constructors.py

def values(
    *columns: ColumnClause[Any],
    name: Optional[str] = None,
    literal_binds: bool = False,
) -> Values:
    r"""Construct a :class:`_expression.Values` construct.

    The column expressions and the actual data for
    :class:`_expression.Values` are given in two separate steps.  The
    constructor receives the column expressions typically as
    :func:`_expression.column` constructs,
    and the data is then passed via the
    :meth:`_expression.Values.data` method as a list,
    which can be called multiple
    times to add more data, e.g.::

        from sqlalchemy import column
        from sqlalchemy import values

        value_expr = values(
            column('id', Integer),
            column('name', String),
            name="my_values"
        ).data(
            [(1, 'name1'), (2, 'name2'), (3, 'name3')]
        )

    :param \*columns: column expressions, typically composed using
     :func:`_expression.column` objects.

    :param name: the name for this VALUES construct.  If omitted, the
     VALUES construct will be unnamed in a SQL expression.   Different
     backends may have different requirements here.

    :param literal_binds: Defaults to False.  Whether or not to render
     the data values inline in the SQL output, rather than using bound
     parameters.

    """
    return Values(*columns, literal_binds=literal_binds, name=name)

within_group

within_group(
    element: FunctionElement[_T],
    *order_by: _ColumnExpressionArgument[Any],
) -> WithinGroup[_T]

Produce a :class:.WithinGroup object against a function.

Used against so-called "ordered set aggregate" and "hypothetical set aggregate" functions, including :class:.percentile_cont, :class:.rank, :class:.dense_rank, etc.

:func:_expression.within_group is usually called using the :meth:.FunctionElement.within_group method, e.g.::

from sqlalchemy import within_group
stmt = select(
    department.c.id,
    func.percentile_cont(0.5).within_group(
        department.c.salary.desc()
    )
)

The above statement would produce SQL similar to SELECT department.id, percentile_cont(0.5) WITHIN GROUP (ORDER BY department.salary DESC).

:param element: a :class:.FunctionElement construct, typically generated by :data:~.expression.func. :param *order_by: one or more column elements that will be used as the ORDER BY clause of the WITHIN GROUP construct.

.. seealso::

:ref:`tutorial_functions_within_group` - in the
:ref:`unified_tutorial`

:data:`.expression.func`

:func:`_expression.over`

Source code in .venv/lib/python3.12/site-packages/sqlalchemy/sql/_elements_constructors.py

def within_group(
    element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
) -> WithinGroup[_T]:
    r"""Produce a :class:`.WithinGroup` object against a function.

    Used against so-called "ordered set aggregate" and "hypothetical
    set aggregate" functions, including :class:`.percentile_cont`,
    :class:`.rank`, :class:`.dense_rank`, etc.

    :func:`_expression.within_group` is usually called using
    the :meth:`.FunctionElement.within_group` method, e.g.::

        from sqlalchemy import within_group
        stmt = select(
            department.c.id,
            func.percentile_cont(0.5).within_group(
                department.c.salary.desc()
            )
        )

    The above statement would produce SQL similar to
    ``SELECT department.id, percentile_cont(0.5)
    WITHIN GROUP (ORDER BY department.salary DESC)``.

    :param element: a :class:`.FunctionElement` construct, typically
     generated by :data:`~.expression.func`.
    :param \*order_by: one or more column elements that will be used
     as the ORDER BY clause of the WITHIN GROUP construct.

    .. seealso::

        :ref:`tutorial_functions_within_group` - in the
        :ref:`unified_tutorial`

        :data:`.expression.func`

        :func:`_expression.over`

    """
    return WithinGroup(element, *order_by)