Mapping Table Columns

Introductory background on mapping to columns falls under the subject of Table configuration; the general form falls under one of three forms:

In all of the above cases, the mapper constructor is ultimately invoked with a completed Table object passed as the selectable unit to be mapped. The behavior of mapper then is to assemble all the columns in the mapped Table into mapped object attributes, each of which are named according to the name of the column itself (specifically, the key attribute of Column). This behavior can be modified in several ways.

Naming Columns Distinctly from Attribute Names

A mapping by default shares the same name for a Column as that of the mapped attribute - specifically it matches the Column.key attribute on Column, which by default is the same as the Column.name.

The name assigned to the Python attribute which maps to Column can be different from either Column.name or Column.key just by assigning it that way, as we illustrate here in a Declarative mapping:

class User(Base):
    __tablename__ = "user"
    id = Column("user_id", Integer, primary_key=True)
    name = Column("user_name", String(50))

Where above User.id resolves to a column named user_id and User.name resolves to a column named user_name.

When mapping to an existing table, the Column object can be referenced directly:

class User(Base):
    __table__ = user_table
    id = user_table.c.user_id
    name = user_table.c.user_name

The corresponding technique for an imperative mapping is to place the desired key in the mapper.properties dictionary with the desired key:

mapper_registry.map_imperatively(
    User,
    user_table,
    properties={
        "id": user_table.c.user_id,
        "name": user_table.c.user_name,
    },
)

Automating Column Naming Schemes from Reflected Tables

In the previous section Naming Columns Distinctly from Attribute Names, we showed how a Column explicitly mapped to a class can have a different attribute name than the column. But what if we aren’t listing out Column objects explicitly, and instead are automating the production of Table objects using reflection (i.e. as described in Reflecting Database Objects)? In this case we can make use of the DDLEvents.column_reflect() event to intercept the production of Column objects and provide them with the Column.key of our choice. The event is most easily associated with the MetaData object that’s in use, such as below we use the one linked to the declarative_base instance:

@event.listens_for(Base.metadata, "column_reflect")
def column_reflect(inspector, table, column_info):
    # set column.key = "attr_<lower_case_name>"
    column_info["key"] = "attr_%s" % column_info["name"].lower()

With the above event, the reflection of Column objects will be intercepted with our event that adds a new “.key” element, such as in a mapping as below:

class MyClass(Base):
    __table__ = Table("some_table", Base.metadata, autoload_with=some_engine)

The approach also works with both the DeferredReflection base class as well as with the Automap extension. For automap specifically, see the section Intercepting Column Definitions for background.

See also

Mapping Declaratively with Reflected Tables

DDLEvents.column_reflect()

Intercepting Column Definitions - in the Automap documentation

Using column_property for column level options

Options can be specified when mapping a Column using the column_property() function. This function explicitly creates the ColumnProperty used by the mapper() to keep track of the Column; normally, the mapper() creates this automatically. Using column_property(), we can pass additional arguments about how we’d like the Column to be mapped. Below, we pass an option active_history, which specifies that a change to this column’s value should result in the former value being loaded first:

from sqlalchemy.orm import column_property


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    name = column_property(Column(String(50)), active_history=True)

column_property() is also used to map a single attribute to multiple columns. This use case arises when mapping to a join() which has attributes which are equated to each other:

class User(Base):
    __table__ = user.join(address)

    # assign "user.id", "address.user_id" to the
    # "id" attribute
    id = column_property(user_table.c.id, address_table.c.user_id)

For more examples featuring this usage, see Mapping a Class against Multiple Tables.

Another place where column_property() is needed is to specify SQL expressions as mapped attributes, such as below where we create an attribute fullname that is the string concatenation of the firstname and lastname columns:

class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = Column(String(50))
    fullname = column_property(firstname + " " + lastname)

See examples of this usage at SQL Expressions as Mapped Attributes.

Object Name Description

column_property(*columns, **kwargs)

Provide a column-level property for use with a mapping.
function sqlalchemy.orm.column_property(*columns, **kwargs)

Provide a column-level property for use with a mapping.

Column-based properties can normally be applied to the mapper’s properties dictionary using the Column element directly. Use this function when the given column is not directly present within the mapper’s selectable; examples include SQL expressions, functions, and scalar SELECT queries.

The column_property() function returns an instance of ColumnProperty.

Columns that aren’t present in the mapper’s selectable won’t be persisted by the mapper and are effectively “read-only” attributes.

Parameters:

  • *cols – list of Column objects to be mapped.

  • active_history=False – When True, indicates that the “previous” value for a scalar attribute should be loaded when replaced, if not already loaded. Normally, history tracking logic for simple non-primary-key scalar values only needs to be aware of the “new” value in order to perform a flush. This flag is available for applications that make use of get_history() or Session.is_modified() which also need to know the “previous” value of the attribute.

  • comparator_factory – a class which extends Comparator which provides custom SQL clause generation for comparison operations.

  • group – a group name for this property when marked as deferred.

  • deferred – when True, the column property is “deferred”, meaning that it does not load immediately, and is instead loaded when the attribute is first accessed on an instance. See also deferred().

  • doc – optional string that will be applied as the doc on the class-bound descriptor.

  • expire_on_flush=True – Disable expiry on flush. A column_property() which refers to a SQL expression (and not a single table-bound column) is considered to be a “read only” property; populating it has no effect on the state of data, and it can only return database state. For this reason a column_property()’s value is expired whenever the parent object is involved in a flush, that is, has any kind of “dirty” state within a flush. Setting this parameter to False will have the effect of leaving any existing value present after the flush proceeds. Note however that the Session with default expiration settings still expires all attributes after a Session.commit() call, however.

  • info – Optional data dictionary which will be populated into the MapperProperty.info attribute of this object.

  • raiseload –

    if True, indicates the column should raise an error when undeferred, rather than loading the value. This can be altered at query time by using the deferred() option with raiseload=False.

    New in version 1.4.

    See also

    Raiseload for Deferred Columns

See also

Using column_property for column level options - to map columns while including mapping options

Using column_property - to map SQL expressions

Mapping to an Explicit Set of Primary Key Columns

The Mapper construct in order to successfully map a table always requires that at least one column be identified as the “primary key” for that selectable. This is so that when an ORM object is loaded or persisted, it can be placed in the identity map with an appropriate identity key.

To support this use case, all FromClause objects (where FromClause is the common base for objects such as Table, Join, Subquery, etc.) have an attribute FromClause.primary_key which returns a collection of those Column objects that indicate they are part of a “primary key”, which is derived from each Column object being a member of a PrimaryKeyConstraint collection that’s associated with the Table from which they ultimately derive.

In those cases where the selectable being mapped does not include columns that are explicitly part of the primary key constraint on their parent table, a user-defined set of primary key columns must be defined. The mapper.primary_key parameter is used for this purpose.

Given the following example of a Imperative Table mapping against an existing Table object, as would occur in a scenario such as when the Table were reflected from an existing database, where the table does not have any declared primary key, we may map such a table as in the following example:

from sqlalchemy import Column
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import UniqueConstraint
from sqlalchemy.orm import declarative_base


metadata = MetaData()
group_users = Table(
    "group_users",
    metadata,
    Column("user_id", String(40), nullable=False),
    Column("group_id", String(40), nullable=False),
    UniqueConstraint("user_id", "group_id"),
)


Base = declarative_base()


class GroupUsers(Base):
    __table__ = group_users
    __mapper_args__ = {"primary_key": [group_users.c.user_id, group_users.c.group_id]}

Above, the group_users table is an association table of some kind with string columns user_id and group_id, but no primary key is set up; instead, there is only a UniqueConstraint establishing that the two columns represent a unique key. The Mapper does not automatically inspect unique constraints for primary keys; instead, we make use of the mapper.primary_key parameter, passing a collection of [group_users.c.user_id, group_users.c.group_id], indicating that these two columns should be used in order to construct the identity key for instances of the GroupUsers class.

Mapping a Subset of Table Columns

Sometimes, a Table object was made available using the reflection process described at Reflecting Database Objects to load the table’s structure from the database. For such a table that has lots of columns that don’t need to be referenced in the application, the include_properties or exclude_properties arguments can specify that only a subset of columns should be mapped. For example:

class User(Base):
    __table__ = user_table
    __mapper_args__ = {"include_properties": ["user_id", "user_name"]}

…will map the User class to the user_table table, only including the user_id and user_name columns - the rest are not referenced. Similarly:

class Address(Base):
    __table__ = address_table
    __mapper_args__ = {"exclude_properties": ["street", "city", "state", "zip"]}

…will map the Address class to the address_table table, including all columns present except street, city, state, and zip.

When this mapping is used, the columns that are not included will not be referenced in any SELECT statements emitted by Query, nor will there be any mapped attribute on the mapped class which represents the column; assigning an attribute of that name will have no effect beyond that of a normal Python attribute assignment.

In some cases, multiple columns may have the same name, such as when mapping to a join of two or more tables that share some column name. include_properties and exclude_properties can also accommodate Column objects to more accurately describe which columns should be included or excluded:

class UserAddress(Base):
    __table__ = user_table.join(addresses_table)
    __mapper_args__ = {
        "exclude_properties": [address_table.c.id],
        "primary_key": [user_table.c.id],
    }

Note

insert and update defaults configured on individual Column objects, i.e. those described at Column INSERT/UPDATE Defaults including those configured by the Column.default, Column.onupdate, Column.server_default and Column.server_onupdate parameters, will continue to function normally even if those Column objects are not mapped. This is because in the case of Column.default and Column.onupdate, the Column object is still present on the underlying Table, thus allowing the default functions to take place when the ORM emits an INSERT or UPDATE, and in the case of Column.server_default and Column.server_onupdate, the relational database itself emits these defaults as a server side behavior.