SQL Expression Language Foundational Constructs¶

Base classes and mixins that are used to compose SQL Expression Language elements.

Object Name	Description
CacheKey
ClauseElement
DialectKWArgs	Establish the ability for a class to have dialect-specific arguments with defaults and constructor validation.
HasCacheKey	Mixin for objects which can produce a cache key.
LambdaElement
StatementLambdaElement

class sqlalchemy.sql.expression.CacheKey¶

inherits from builtins.tuple

The key used to identify a SQL statement construct in the SQL compilation cache.

See also

SQL Compilation Caching

Member Name	Description
bindparams	Alias for field number 1
key	Alias for field number 0
to_offline_string()	Generate an “offline string” form of this `CacheKey`

attribute sqlalchemy.sql.expression.CacheKey.bindparams: Sequence[BindParameter[Any]]¶: Alias for field number 1

attribute sqlalchemy.sql.expression.CacheKey.key: Tuple[Any, ...]¶: Alias for field number 0

method sqlalchemy.sql.expression.CacheKey.to_offline_string(statement_cache: MutableMapping[Any, str], statement: ClauseElement, parameters: _CoreSingleExecuteParams) → str¶

Generate an “offline string” form of this CacheKey

The “offline string” is basically the string SQL for the statement plus a repr of the bound parameter values in series. Whereas the CacheKey object is dependent on in-memory identities in order to work as a cache key, the “offline” version is suitable for a cache that will work for other processes as well.

The given statement_cache is a dictionary-like object where the string form of the statement itself will be cached. This dictionary should be in a longer lived scope in order to reduce the time spent stringifying statements.

class sqlalchemy.sql.expression.ClauseElement¶

inherits from sqlalchemy.sql.annotation.SupportsWrappingAnnotations, sqlalchemy.sql.cache_key.MemoizedHasCacheKey, sqlalchemy.sql.traversals.HasCopyInternals, sqlalchemy.sql.visitors.ExternallyTraversible, sqlalchemy.sql.expression.CompilerElement

Base class for elements of a programmatically constructed SQL expression.

Member Name	Description
compare()	Compare this `ClauseElement` to the given `ClauseElement`.
compile()	Compile this SQL expression.
get_children()	Return immediate child `HasTraverseInternals` elements of this `HasTraverseInternals`.
inherit_cache	Indicate if this `HasCacheKey` instance should make use of the cache key generation scheme used by its immediate superclass.
params()	Return a copy with `bindparam()` elements replaced.
self_group()	Apply a ‘grouping’ to this `ClauseElement`.
unique_params()	Return a copy with `bindparam()` elements replaced.

method sqlalchemy.sql.expression.ClauseElement.compare(other: ClauseElement, **kw: Any) → bool¶

Compare this ClauseElement to the given 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 ColumnElement).

method sqlalchemy.sql.expression.ClauseElement.compile(bind: _HasDialect | None = None, dialect: Dialect | None = None, **kw: Any) → Compiled¶

inherited from the CompilerElement.compile() method of CompilerElement

Compile this SQL expression.

The return value is a Compiled object. Calling str() or unicode() on the returned value will yield a string representation of the result. The Compiled object also can return a dictionary of bind parameter names and values using the params accessor.

Parameters:

bind¶ – An Connection or Engine which can provide a Dialect in order to generate a Compiled object. If the bind and dialect parameters are both omitted, a default SQL compiler is used.
column_keys¶ – Used for INSERT and UPDATE statements, a list of column names which should be present in the VALUES clause of the compiled statement. If None, all columns from the target table object are rendered.
dialect¶ – A Dialect instance which can generate a Compiled object. This argument takes precedence over the bind argument.
compile_kwargs¶ –
optional dictionary of additional parameters that will be passed through to the compiler within all “visit” methods. This allows any custom flag to be passed through to a custom compilation construct, for example. It is also used for the case of passing the literal_binds flag through:
```
from sqlalchemy.sql import table, column, select t = table("t", column("x")) s = select(t).where(t.c.x == 5) print(s.compile(compile_kwargs={"literal_binds": True}))
```

method sqlalchemy.sql.expression.ClauseElement.get_children(*, omit_attrs: Tuple[str, ...] = (), **kw: Any) → Iterable[HasTraverseInternals]¶

inherited from the HasTraverseInternals.get_children() method of HasTraverseInternals

Return immediate child HasTraverseInternals elements of this HasTraverseInternals.

This is used for visit traversal.

**kw may contain flags that change the collection that is returned, for example to return a subset of items in order to cut down on larger traversals, or to return child items from a different context (such as schema-level collections instead of clause-level).

attribute sqlalchemy.sql.expression.ClauseElement.inherit_cache: bool | None = None¶

inherited from the HasCacheKey.inherit_cache attribute of HasCacheKey

Indicate if this 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.

See also

Enabling Caching Support for Custom Constructs - General guideslines for setting the HasCacheKey.inherit_cache attribute for third-party or user defined SQL constructs.

method sqlalchemy.sql.expression.ClauseElement.params(_ClauseElement__optionaldict: Mapping[str, Any] | None = None, **kwargs: Any) → Self¶

Return a copy with bindparam() elements replaced.

Returns a copy of this ClauseElement with 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}

method sqlalchemy.sql.expression.ClauseElement.self_group(against: OperatorType | None = None) → ClauseElement¶

Apply a ‘grouping’ to this 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 select() constructs when placed into the FROM clause of another select(). (Note that subqueries should be normally created using the Select.alias() method, as many platforms require nested SELECT statements to be named).

As expressions are composed together, the application of 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 self_group() method of ClauseElement just returns self.

method sqlalchemy.sql.expression.ClauseElement.unique_params(_ClauseElement__optionaldict: Dict[str, Any] | None = None, **kwargs: Any) → Self¶

Return a copy with bindparam() elements replaced.

Same functionality as ClauseElement.params(), except adds unique=True to affected bind parameters so that multiple statements can be used.

class sqlalchemy.sql.base.DialectKWArgs¶

Establish the ability for a class to have dialect-specific arguments with defaults and constructor validation.

The DialectKWArgs interacts with the DefaultDialect.construct_arguments present on a dialect.

See also

DefaultDialect.construct_arguments

Member Name	Description
argument_for()	Add a new kind of dialect-specific keyword argument for this class.
dialect_kwargs	A collection of keyword arguments specified as dialect-specific options to this construct.
dialect_options	A collection of keyword arguments specified as dialect-specific options to this construct.
kwargs	A synonym for `DialectKWArgs.dialect_kwargs`.

classmethod sqlalchemy.sql.base.DialectKWArgs.argument_for(dialect_name: str, argument_name: str, default: Any) → None¶

Add a new kind of dialect-specific keyword argument for this class.

E.g.:

Index.argument_for("mydialect", "length", None) some_index = Index("a", "b", mydialect_length=5)

The DialectKWArgs.argument_for() method is a per-argument way adding extra arguments to the DefaultDialect.construct_arguments dictionary. This dictionary provides a list of argument names accepted by various schema-level constructs on behalf of a dialect.

New dialects should typically specify this dictionary all at once as a data member of the dialect class. The use case for ad-hoc addition of argument names is typically for end-user code that is also using a custom compilation scheme which consumes the additional arguments.

Parameters:

dialect_name¶ – name of a dialect. The dialect must be locatable, else a NoSuchModuleError is raised. The dialect must also include an existing DefaultDialect.construct_arguments collection, indicating that it participates in the keyword-argument validation and default system, else ArgumentError is raised. If the dialect does not include this collection, then any keyword argument can be specified on behalf of this dialect already. All dialects packaged within SQLAlchemy include this collection, however for third party dialects, support may vary.
argument_name¶ – name of the parameter.
default¶ – default value of the parameter.

attribute sqlalchemy.sql.base.DialectKWArgs.dialect_kwargs¶

A collection of keyword arguments specified as dialect-specific options to this construct.

The arguments are present here in their original <dialect>_<kwarg> format. Only arguments that were actually passed are included; unlike the DialectKWArgs.dialect_options collection, which contains all options known by this dialect including defaults.

The collection is also writable; keys are accepted of the form <dialect>_<kwarg> where the value will be assembled into the list of options.

See also

DialectKWArgs.dialect_options - nested dictionary form

attribute sqlalchemy.sql.base.DialectKWArgs.dialect_options¶

A collection of keyword arguments specified as dialect-specific options to this construct.

This is a two-level nested registry, keyed to <dialect_name> and <argument_name>. For example, the postgresql_where argument would be locatable as:

arg = my_object.dialect_options["postgresql"]["where"]

Added in version 0.9.2.

See also

DialectKWArgs.dialect_kwargs - flat dictionary form

attribute sqlalchemy.sql.base.DialectKWArgs.kwargs¶: A synonym for DialectKWArgs.dialect_kwargs.

class sqlalchemy.sql.traversals.HasCacheKey¶

Mixin for objects which can produce a cache key.

This class is usually in a hierarchy that starts with the HasTraverseInternals base, but this is optional. Currently, the class should be able to work on its own without including HasTraverseInternals.

See also

CacheKey

SQL Compilation Caching

Member Name	Description
inherit_cache	Indicate if this `HasCacheKey` instance should make use of the cache key generation scheme used by its immediate superclass.

attribute sqlalchemy.sql.traversals.HasCacheKey.inherit_cache: bool | None = None¶

Indicate if this 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.

See also

Enabling Caching Support for Custom Constructs - General guideslines for setting the HasCacheKey.inherit_cache attribute for third-party or user defined SQL constructs.

class sqlalchemy.sql.expression.LambdaElement¶

inherits from sqlalchemy.sql.expression.ClauseElement

A SQL construct where the state is stored as an un-invoked lambda.

The LambdaElement is produced transparently whenever passing lambda expressions into SQL constructs, such as:

stmt = select(table).where(lambda: table.c.col == parameter)

The LambdaElement is the base of the StatementLambdaElement which represents a full statement within a lambda.

Added in version 1.4.

class sqlalchemy.sql.expression.StatementLambdaElement¶

inherits from sqlalchemy.sql.roles.AllowsLambdaRole, sqlalchemy.sql.lambdas.LambdaElement, sqlalchemy.sql.expression.Executable

Represent a composable SQL statement as a LambdaElement.

The StatementLambdaElement is constructed using the lambda_stmt() function:

from sqlalchemy import lambda_stmt stmt = lambda_stmt(lambda: select(table))

Once constructed, additional criteria can be built onto the statement by adding subsequent lambdas, which accept the existing statement object as a single parameter:

stmt += lambda s: s.where(table.c.col == parameter)

Added in version 1.4.

Member Name	Description
add_criteria()	Add new criteria to this `StatementLambdaElement`.
is_delete
is_dml
is_insert
is_select
is_text
is_update
spoil()	Return a new `StatementLambdaElement` that will run all lambdas unconditionally each time.

method sqlalchemy.sql.expression.StatementLambdaElement.add_criteria(other: Callable[[Any], Any], enable_tracking: bool = True, track_on: Any | None = None, track_closure_variables: bool = True, track_bound_values: bool = True) → StatementLambdaElement¶

Add new criteria to this StatementLambdaElement.

E.g.:

>>> def my_stmt(parameter): ...  stmt = lambda_stmt( ...  lambda: select(table.c.x, table.c.y), ...  ) ...  stmt = stmt.add_criteria(lambda: table.c.x > parameter) ...  return stmt

The StatementLambdaElement.add_criteria() method is equivalent to using the Python addition operator to add a new lambda, except that additional arguments may be added including track_closure_values and track_on:

>>> def my_stmt(self, foo): ...  stmt = lambda_stmt( ...  lambda: select(func.max(foo.x, foo.y)), ...  track_closure_variables=False, ...  ) ...  stmt = stmt.add_criteria(lambda: self.where_criteria, track_on=[self]) ...  return stmt