class documentation

class ColumnElement(roles.ColumnArgumentOrKeyRole, roles.StatementOptionRole, roles.WhereHavingRole, roles.BinaryElementRole, roles.OrderByRole, roles.ColumnsClauseRole, roles.LimitOffsetRole, roles.DMLColumnRole, roles.DDLConstraintColumnRole, roles.DDLExpressionRole, operators.ColumnOperators, ClauseElement):

Known subclasses: sqlalchemy.dialects.postgresql.aggregate_order_by, sqlalchemy.dialects.postgresql.array, sqlalchemy.sql.elements.BinaryExpression, sqlalchemy.sql.elements.BindParameter, sqlalchemy.sql.elements.Case, sqlalchemy.sql.elements.Cast, sqlalchemy.sql.elements.Extract, sqlalchemy.sql.elements.False_, sqlalchemy.sql.elements.FunctionFilter, sqlalchemy.sql.elements.Label, sqlalchemy.sql.elements.Null, sqlalchemy.sql.elements.Over, sqlalchemy.sql.elements.True_, sqlalchemy.sql.elements.Tuple, sqlalchemy.sql.elements.TypeCoerce, sqlalchemy.sql.elements.UnaryExpression, sqlalchemy.sql.elements.WithinGroup, sqlalchemy.dialects.mssql.information_schema._cast_on_2005, sqlalchemy.sql.compiler._CompileLabel, sqlalchemy.sql.crud._multiparam_column, sqlalchemy.sql.elements._label_reference, sqlalchemy.sql.elements._textual_label_reference, sqlalchemy.sql.elements.BooleanClauseList, sqlalchemy.sql.elements.CollationClause, sqlalchemy.sql.elements.Grouping, sqlalchemy.sql.elements.NamedColumn, sqlalchemy.sql.elements.Slice

View In Hierarchy

Represent a column-oriented SQL expression suitable for usage in the "columns" clause, WHERE clause etc. of a statement.

While the most familiar kind of _expression.ColumnElement is the _schema.Column object, _expression.ColumnElement serves as the basis for any unit that may be present in a SQL expression, including the expressions themselves, SQL functions, bound parameters, literal expressions, keywords such as NULL, etc. _expression.ColumnElement is the ultimate base class for all such elements.

A wide variety of SQLAlchemy Core functions work at the SQL expression level, and are intended to accept instances of _expression.ColumnElement as arguments. These functions will typically document that they accept a "SQL expression" as an argument. What this means in terms of SQLAlchemy usually refers to an input which is either already in the form of a _expression.ColumnElement object, or a value which can be coerced into one. The coercion rules followed by most, but not all, SQLAlchemy Core functions with regards to SQL expressions are as follows:

  • a literal Python value, such as a string, integer or floating point value, boolean, datetime, Decimal object, or virtually any other Python object, will be coerced into a "literal bound value". This generally means that a .bindparam will be produced featuring the given value embedded into the construct; the resulting .BindParameter object is an instance of _expression.ColumnElement. The Python value will ultimately be sent to the DBAPI at execution time as a parameterized argument to the execute() or executemany() methods, after SQLAlchemy type-specific converters (e.g. those provided by any associated .TypeEngine objects) are applied to the value.
  • any special object value, typically ORM-level constructs, which feature an accessor called __clause_element__(). The Core expression system looks for this method when an object of otherwise unknown type is passed to a function that is looking to coerce the argument into a _expression.ColumnElement and sometimes a _expression.SelectBase expression. It is used within the ORM to convert from ORM-specific objects like mapped classes and mapped attributes into Core expression objects.
  • The Python None value is typically interpreted as NULL, which in SQLAlchemy Core produces an instance of .null.

A _expression.ColumnElement provides the ability to generate new _expression.ColumnElement objects using Python expressions. This means that Python operators such as ==, != and < are overloaded to mimic SQL operations, and allow the instantiation of further _expression.ColumnElement instances which are composed from other, more fundamental _expression.ColumnElement objects. For example, two .ColumnClause objects can be added together with the addition operator + to produce a .BinaryExpression. Both .ColumnClause and .BinaryExpression are subclasses of _expression.ColumnElement:

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

See Also

_schema.Column

_expression.column

Method cast Produce a type cast, i.e. CAST(<expression> AS <type>).
Method label Produce a column label, i.e. <columnname> AS <name>.
Method operate Operate on an argument.
Method reverse​_operate Reverse operate on an argument.
Method self​_group Apply a 'grouping' to this _expression.ClauseElement.
Method shares​_lineage Return True if the given _expression.ColumnElement has a common ancestor to this _expression.ColumnElement.
Class Variable foreign​_keys Undocumented
Class Variable key The 'key' that in some circumstances refers to this object in a Python namespace.
Class Variable primary​_key Undocumented
Method __getattr__ Undocumented
Method ​_anon​_label Undocumented
Method ​_bind​_param Undocumented
Method ​_compare​_name​_for​_result Return True if the given column element compares to this one when targeting within a result row.
Method ​_dedupe​_anon​_label​_idx label to apply to a column that is anon labeled, but repeated in the SELECT, so that we have to make an "extra anon" label that disambiguates it from the previous appearance.
Method ​_dedupe​_anon​_tq​_label​_idx Undocumented
Method ​_expression​_label a suggested label to use in the case that the column has no name, which should be used if possible as the explicit 'AS <label>' where this expression would normally have an anon label.
Method ​_make​_proxy Create a new _expression.ColumnElement representing this _expression.ColumnElement as it appears in the select list of a descending selectable.
Method ​_negate Undocumented
Method ​_proxy​_key Undocumented
Method ​_tq​_key​_label A label-based version of 'key' that in some circumstances refers to this object in a Python namespace.
Method ​_uncached​_proxy​_set An 'uncached' version of proxy set.
Method comparator Undocumented
Class Variable __visit​_name__ Undocumented
Class Variable ​_allow​_label​_resolve A flag that can be flipped to prevent a column from being resolvable by string label name.
Class Variable ​_alt​_names Undocumented
Class Variable ​_is​_implicitly​_boolean Undocumented
Class Variable ​_proxies Undocumented
Class Variable ​_render​_label​_in​_columns​_clause No summary
Class Variable ​_tq​_label The named label that can be used to target this column in a result set in a "table qualified" context.
Property ​_anon​_key​_label Provides a constant 'anonymous key label' for this ColumnElement.
Property ​_anon​_name​_label Provides a constant 'anonymous label' for this ColumnElement.
Property ​_anon​_tq​_key​_label Undocumented
Property ​_anon​_tq​_label Undocumented
Property ​_key​_label legacy; renamed to _tq_key_label
Property ​_label legacy; renamed to _tq_label
Property ​_non​_anon​_label the 'name' that naturally applies this element when rendered in SQL.
Property ​_select​_iterable Undocumented
Property anon​_key​_label Undocumented
Property anon​_label Undocumented
Property base​_columns Undocumented
Property expression Return a column expression.
Property proxy​_set Undocumented
Property type Undocumented

Inherited from ColumnArgumentOrKeyRole:

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnArgumentOrKeyRole, ColumnArgumentRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from StatementOptionRole:

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via StatementOptionRole, StructuralRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from WhereHavingRole:

Class Variable ​_role​_name Undocumented

Inherited from AllowsLambdaRole (via WhereHavingRole, OnClauseRole):

Class Variable allows​_lambda Undocumented

Inherited from SQLRole (via WhereHavingRole, OnClauseRole, StructuralRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from BinaryElementRole:

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via BinaryElementRole, ExpressionElementRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from OrderByRole:

Class Variable ​_role​_name Undocumented

Inherited from AllowsLambdaRole (via OrderByRole):

Class Variable allows​_lambda Undocumented

Inherited from SQLRole (via OrderByRole, ByOfRole, ColumnListRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from ColumnsClauseRole:

Class Variable ​_role​_name Undocumented

Inherited from AllowsLambdaRole (via ColumnsClauseRole):

Class Variable allows​_lambda Undocumented

Inherited from UsesInspection (via ColumnsClauseRole):

Class Variable ​_post​_inspect Undocumented
Class Variable uses​_inspection Undocumented

Inherited from SQLRole (via ColumnsClauseRole, ColumnListRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from LimitOffsetRole:

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via LimitOffsetRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from DMLColumnRole:

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via DMLColumnRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from DDLConstraintColumnRole:

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via DDLConstraintColumnRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from DDLExpressionRole:

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via DDLExpressionRole, StructuralRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from ColumnOperators:

Method __add__ Implement the + operator.
Method __contains__ Undocumented
Method __div__ Implement the / operator.
Method __eq__ Implement the == operator.
Method __ge__ Implement the >= operator.
Method __getitem__ Implement the [] operator.
Method __gt__ Implement the > operator.
Method __le__ Implement the <= operator.
Method __lshift__ implement the << operator.
Method __lt__ Implement the < operator.
Method __mod__ Implement the % operator.
Method __mul__ Implement the * operator.
Method __ne__ Implement the != operator.
Method __neg__ Implement the - operator.
Method __radd__ Implement the + operator in reverse.
Method __rdiv__ Implement the / operator in reverse.
Method __rmod__ Implement the % operator in reverse.
Method __rmul__ Implement the * operator in reverse.
Method __rshift__ implement the >> operator.
Method __rsub__ Implement the - operator in reverse.
Method __rtruediv__ Implement the // operator in reverse.
Method __sub__ Implement the - operator.
Method __truediv__ Implement the // operator.
Method all​_ Produce an _expression.all_ clause against the parent object.
Method any​_ Produce an _expression.any_ clause against the parent object.
Method asc Produce a _expression.asc clause against the parent object.
Method between Produce a _expression.between clause against the parent object, given the lower and upper range.
Method collate Produce a _expression.collate clause against the parent object, given the collation string.
Method concat Implement the 'concat' operator.
Method contains Implement the 'contains' operator.
Method desc Produce a _expression.desc clause against the parent object.
Method distinct Produce a _expression.distinct clause against the parent object.
Method endswith Implement the 'endswith' operator.
Method ilike Implement the ilike operator, e.g. case insensitive LIKE.
Method in​_ Implement the in operator.
Method is​_ Implement the IS operator.
Method is​_distinct​_from Implement the IS DISTINCT FROM operator.
Method is​_not Implement the IS NOT operator.
Method is​_not​_distinct​_from Implement the IS NOT DISTINCT FROM operator.
Method like Implement the like operator.
Method match Implements a database-specific 'match' operator.
Method not​_ilike implement the NOT ILIKE operator.
Method not​_in implement the NOT IN operator.
Method not​_like implement the NOT LIKE operator.
Method nulls​_first Produce a _expression.nulls_first clause against the parent object.
Method nulls​_last Produce a _expression.nulls_last clause against the parent object.
Method regexp​_match Implements a database-specific 'regexp match' operator.
Method regexp​_replace Implements a database-specific 'regexp replace' operator.
Method startswith Implement the startswith operator.
Class Variable __slots__ Undocumented
Class Variable timetuple Hack, allows datetime objects to be compared on the LHS.

Inherited from Operators (via ColumnOperators):

Method __and__ Implement the & operator.
Method __invert__ Implement the ~ operator.
Method __or__ Implement the | operator.
Method bool​_op Return a custom boolean operator.
Method op Produce a generic operator function.

Inherited from ClauseElement:

Method compare Compare this _expression.ClauseElement to the given _expression.ClauseElement.
Method compile Compile this SQL expression.
Method params Return a copy with _expression.bindparam elements replaced.
Method unique​_params Return a copy with _expression.bindparam elements replaced.
Method __bool__ Undocumented
Method __getstate__ Undocumented
Method __invert__ Undocumented
Method __repr__ Undocumented
Method __str__ Undocumented
Method ​_clone Create a shallow copy of this ClauseElement.
Method ​_cloned​_set Return the set consisting all cloned ancestors of this ClauseElement.
Method ​_compile​_w​_cache Undocumented
Method ​_compiler Return a compiler appropriate for this ClauseElement, given a Dialect.
Method ​_execute​_on​_connection Undocumented
Method ​_negate​_in​_binary a hook to allow the right side of a binary expression to respond to a negation of the binary expression.
Method ​_replace​_params Undocumented
Method ​_set​_propagate​_attrs Undocumented
Method ​_ungroup Return this _expression.ClauseElement without any groupings.
Method ​_with​_binary​_element​_type in the context of binary expression, convert the type of this object to the one given.
Class Variable ​_cache​_key​_traversal Undocumented
Class Variable ​_from​_objects Undocumented
Class Variable ​_is​_bind​_parameter Undocumented
Class Variable ​_is​_clause​_list Undocumented
Class Variable ​_is​_clone​_of Undocumented
Class Variable ​_is​_from​_clause Undocumented
Class Variable ​_is​_from​_container Undocumented
Class Variable ​_is​_immutable Undocumented
Class Variable ​_is​_lambda​_element Undocumented
Class Variable ​_is​_returns​_rows Undocumented
Class Variable ​_is​_select​_container Undocumented
Class Variable ​_is​_select​_statement Undocumented
Class Variable ​_is​_singleton​_constant Undocumented
Class Variable ​_is​_text​_clause Undocumented
Class Variable ​_is​_textual Undocumented
Class Variable ​_order​_by​_label​_element Undocumented
Class Variable bind Undocumented
Class Variable description Undocumented
Class Variable is​_clause​_element Undocumented
Class Variable is​_selectable Undocumented
Class Variable stringify​_dialect Undocumented
Class Variable supports​_execution Undocumented
Instance Variable ​_propagate​_attrs like annotations, however these propagate outwards liberally as SQL constructs are built, and are set up at construction time.
Property ​_constructor return the 'constructor' for this ClauseElement.
Property entity​_namespace Undocumented

Inherited from SQLRole (via ClauseElement):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from SupportsWrappingAnnotations (via ClauseElement):

Method ​_annotate return a copy of this ClauseElement with annotations updated by the given dictionary.
Method ​_deannotate return a copy of this _expression.ClauseElement with annotations removed.
Method ​_with​_annotations return a copy of this ClauseElement with annotations replaced by the given dictionary.

Inherited from SupportsAnnotations (via ClauseElement, SupportsWrappingAnnotations):

Property ​_annotations​_cache​_key Undocumented

Inherited from MemoizedHasCacheKey (via ClauseElement):

Method ​_generate​_cache​_key return a cache key.

Inherited from HasCacheKey (via ClauseElement, MemoizedHasCacheKey):

Class Variable inherit​_cache Indicate if this .HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.
Class Method ​_generate​_cache​_attrs generate cache key dispatcher for a new class.
Class Method ​_generate​_cache​_key​_for​_object Undocumented
Method ​_gen​_cache​_key return an optional cache key.
Class Variable __slots__ Undocumented
Class Variable ​_hierarchy​_supports​_caching private attribute which may be set to False to prevent the inherit_cache warning from being emitted for a hierarchy of subclasses.
Class Variable ​_is​_has​_cache​_key Undocumented

Inherited from HasCopyInternals (via ClauseElement):

Method ​_copy​_internals Reassign internal elements to be clones of themselves.

Inherited from Traversible (via ClauseElement):

Method get​_children Return immediate child .visitors.Traversible elements of this .visitors.Traversible.
Method __class​_getitem__ Undocumented
def cast(self, type_):

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

This is a shortcut to the _expression.cast function.

See Also

:ref:`coretutorial_casts`

_expression.cast

_expression.type_coerce

New in version 1.0.7.
def label(self, name):

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

This is a shortcut to the _expression.label function.

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

def operate(self, op, *other, **kwargs):

Operate on an argument.

This is the lowest level of operation, raises NotImplementedError by default.

Overriding this on a subclass can allow common behavior to be applied to all operations. For example, overriding .ColumnOperators to apply func.lower() to the left and right side:

class MyComparator(ColumnOperators):
    def operate(self, op, other):
        return op(func.lower(self), func.lower(other))
Parameters
opOperator callable.
*otherthe 'other' side of the operation. Will be a single scalar for most operations.
**kwargsmodifiers. These may be passed by special operators such as ColumnOperators.contains.
def reverse_operate(self, op, other, **kwargs):

Reverse operate on an argument.

Usage is the same as operate.

def self_group(self, against=None):

Apply a 'grouping' to this _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 _expression.select constructs when placed into the FROM clause of another _expression.select. (Note that subqueries should be normally created using the _expression.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 _expression.ClauseElement just returns self.

def shares_lineage(self, othercolumn):
Return True if the given _expression.ColumnElement has a common ancestor to this _expression.ColumnElement.
foreign_keys: list =
key =

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 _schema.Column with a .key of "somekey".

primary_key: bool =
def __getattr__(self, key):

Undocumented

def _anon_label(self, seed, add_hash=None):

Undocumented

def _bind_param(self, operator, obj, type_=None, expanding=False):
def _compare_name_for_result(self, other):
Return True if the given column element compares to this one when targeting within a result row.
def _dedupe_anon_label_idx(self, idx):

label to apply to a column that is anon labeled, but repeated in the SELECT, so that we have to make an "extra anon" label that disambiguates it from the previous appearance.

these labels come out like "foo_bar_id__1" and have double underscores in them.

def _dedupe_anon_tq_label_idx(self, idx):

Undocumented

@HasMemoized.memoized_attribute
def _expression_label(self):

a suggested label to use in the case that the column has no name, which should be used if possible as the explicit 'AS <label>' where this expression would normally have an anon label.

this is essentially mostly what _proxy_key does except it returns None if the column has a normal name that can be used.

def _make_proxy(self, selectable, name=None, key=None, name_is_truncatable=False, **kw):
Create a new _expression.ColumnElement representing this _expression.ColumnElement as it appears in the select list of a descending selectable.
@HasMemoized.memoized_attribute
def _proxy_key(self):

Undocumented

@HasMemoized.memoized_attribute
def _tq_key_label(self):

A label-based version of 'key' that in some circumstances refers to this object in a Python namespace.

_tq_key_label comes into play when a select() statement is constructed with apply_labels(); in this case, all Column objects in the .c collection are rendered as <tablename>_<columnname> in SQL; this is essentially the value of ._label. But to locate those columns in the .c collection, the name is along the lines of <tablename>_<key>; that's the typical value of .key_label.

Changed in version 1.4.21: renamed from ._key_label
def _uncached_proxy_set(self):

An 'uncached' version of proxy set.

This is so that we can read annotations from the list of columns without breaking the caching of the above proxy_set.

@HasMemoized.memoized_attribute
def comparator(self):

Undocumented

_allow_label_resolve: bool =

A flag that can be flipped to prevent a column from being resolvable by string label name.

The joined eager loader strategy in the ORM uses this, for example.

_alt_names: tuple =

Undocumented

_render_label_in_columns_clause: bool =
A flag used by select._columns_plus_names that helps to determine we are actually going to render in terms of "SELECT <col> AS <label>". This flag can be returned as False for some Column objects that want to be rendered as simple "SELECT <col>"; typically columns that don't have any parent table and are named the same as what the label would be in any case.
_tq_label =

The named label that can be used to target this column in a result set in a "table qualified" context.

This label is almost always the label used when rendering <expr> AS <label> in a SELECT statement when using the LABEL_STYLE_TABLENAME_PLUS_COL label style, which is what the legacy ORM Query object uses as well.

For a regular Column bound to a Table, this is typically the label <tablename>_<columnname>. For other constructs, different rules may apply, such as anonymized labels and others.

Changed in version 1.4.21: renamed from ._label
@util.memoized_property
_anon_key_label =

Provides a constant 'anonymous key label' for this ColumnElement.

Compare to anon_label, except that the "key" of the column, if available, is used to generate the label.

This is used when a deduplicating key is placed into the columns collection of a selectable.

Changed in version 1.4.9: - this attribute was not intended to be public and is renamed to _anon_key_label. anon_key_label exists for backwards compat
@util.memoized_property
_anon_name_label =

Provides a constant 'anonymous label' for this ColumnElement.

This is a label() expression which will be named at compile time. The same label() is returned each time anon_label is called so that expressions can reference anon_label multiple times, producing the same label name at compile time.

The compiler uses this function automatically at compile time for expressions that are known to be 'unnamed' like binary expressions and function calls.

Changed in version 1.4.9: - this attribute was not intended to be public and is renamed to _anon_name_label. anon_name exists for backwards compat
@util.memoized_property
_anon_tq_key_label =

Undocumented

@util.memoized_property
_anon_tq_label =

Undocumented

@property
_key_label =
legacy; renamed to _tq_key_label
@property
_label =
legacy; renamed to _tq_label
@property
_non_anon_label =

the 'name' that naturally applies this element when rendered in SQL.

Concretely, this is the "name" of a column or a label in a SELECT statement; <columnname> and <labelname> below:

SELECT <columnmame> FROM table

SELECT column AS <labelname> FROM table

Above, the two names noted will be what's present in the DBAPI cursor.description as the names.

If this attribute returns None, it means that the SQL element as written does not have a 100% fully predictable "name" that would appear in the cursor.description. Examples include SQL functions, CAST functions, etc. While such things do return names in cursor.description, they are only predictable on a database-specific basis; e.g. an expression like MAX(table.col) may appear as the string max on one database (like PostgreSQL) or may appear as the whole expression max(table.col) on SQLite.

The default implementation looks for a .name attribute on the object, as has been the precedent established in SQLAlchemy for many years. An exception is made on the FunctionElement subclass so that the return value is always None.

New in version 1.4.21.
@property
@util.deprecated('1.4', 'The :attr:`_expression.ColumnElement.anon_key_label` attribute is now private, and the public accessor is deprecated.')
anon_key_label =

Undocumented

@property
@util.deprecated('1.4', 'The :attr:`_expression.ColumnElement.anon_label` attribute is now private, and the public accessor is deprecated.')
anon_label =

Undocumented

@util.memoized_property
base_columns =

Undocumented

@property
expression =

Return a column expression.

Part of the inspection interface; returns self.

@util.memoized_property
proxy_set =

Undocumented