sqlalchemy.sql.expression.ColumnCollection

Collection of _expression.ColumnElement instances, typically for _sql.FromClause objects.

The _sql.ColumnCollection object is most commonly available as the _schema.Table.c or _schema.Table.columns collection on the _schema.Table object, introduced at :ref:`metadata_tables_and_columns`.

The _expression.ColumnCollection has both mapping- and sequence- like behaviors. A _expression.ColumnCollection usually stores _schema.Column objects, which are then accessible both via mapping style access as well as attribute access style.

To access _schema.Column objects using ordinary attribute-style access, specify the name like any other object attribute, such as below a column named employee_name is accessed:

>>> employee_table.c.employee_name

To access columns that have names with special characters or spaces, index-style access is used, such as below which illustrates a column named employee ' payment is accessed:

>>> employee_table.c["employee ' payment"]

As the _sql.ColumnCollection object provides a Python dictionary interface, common dictionary method names like _sql.ColumnCollection.keys, _sql.ColumnCollection.values, and _sql.ColumnCollection.items are available, which means that database columns that are keyed under these names also need to use indexed access:

>>> employee_table.c["values"]

The name for which a _schema.Column would be present is normally that of the :paramref:`_schema.Column.key` parameter. In some contexts, such as a _sql.Select object that uses a label style set using the _sql.Select.set_label_style method, a column of a certain key may instead be represented under a particular label name such as tablename_columnname:

>>> from sqlalchemy import select, column, table
>>> from sqlalchemy import LABEL_STYLE_TABLENAME_PLUS_COL
>>> t = table("t", column("c"))
>>> stmt = select(t).set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
>>> subq = stmt.subquery()
>>> subq.c.t_c
<sqlalchemy.sql.elements.ColumnClause at 0x7f59dcf04fa0; t_c>

.ColumnCollection also indexes the columns in order and allows them to be accessible by their integer position:

>>> cc[0]
Column('x', Integer(), table=None)
>>> cc[1]
Column('y', Integer(), table=None)

Iterating the collection yields the column expressions in order:

>>> list(cc)
[Column('x', Integer(), table=None),
 Column('y', Integer(), table=None)]

The base _expression.ColumnCollection object can store duplicates, which can mean either two columns with the same key, in which case the column returned by key access is arbitrary:

>>> x1, x2 = Column('x', Integer), Column('x', Integer)
>>> cc = ColumnCollection(columns=[(x1.name, x1), (x2.name, x2)])
>>> list(cc)
[Column('x', Integer(), table=None),
 Column('x', Integer(), table=None)]
>>> cc['x'] is x1
False
>>> cc['x'] is x2
True

Or it can also mean the same column multiple times. These cases are supported as _expression.ColumnCollection is used to represent the columns in a SELECT statement which may include duplicates.

A special subclass .DedupeColumnCollection exists which instead maintains SQLAlchemy's older behavior of not allowing duplicates; this collection is used for schema level objects like _schema.Table and .PrimaryKeyConstraint where this deduping is helpful. The .DedupeColumnCollection class also has additional mutation methods as the schema constructs have more use cases that require removal and replacement of columns.