SQL Expressions as Mapped Attributes

The easiest and most flexible way to link relatively simple SQL expressions to a class is to use a so-called “hybrid attribute”, described in the section . The hybrid provides for an expression that works at both the Python level as well as at the SQL expression level. For example, below we map a class User, containing attributes firstname and lastname, and include a hybrid that will provide for us the fullname, which is the string concatenation of the two:

Above, the fullname attribute is interpreted at both the instance and class level, so that it is available from an instance:

some_user = session.scalars(select(User).limit(1)).first()
print(some_user.fullname)

as well as usable within queries:

some_user = session.scalars(
    select(User).where(User.fullname == "John Smith").limit(1)
).first()

The string concatenation example is a simple one, where the Python expression can be dual purposed at the instance and class level. Often, the SQL expression must be distinguished from the Python expression, which can be achieved using hybrid_property.expression(). Below we illustrate the case where a conditional needs to be present inside the hybrid, using the if statement in Python and the construct for SQL expressions:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import case
class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    firstname = mapped_column(String(50))
    lastname = mapped_column(String(50))
    @hybrid_property
    def fullname(self):
        if self.firstname is not None:
            return self.firstname + " " + self.lastname
        else:
            return self.lastname
    @fullname.expression
    def fullname(cls):
        return case(
            [
                (cls.firstname != None, cls.firstname + " " + cls.lastname),
            ],
            else_=cls.lastname,
        )

The function can be used to map a SQL expression in a manner similar to a regularly mapped Column. With this technique, the attribute is loaded along with all other column-mapped attributes at load time. This is in some cases an advantage over the usage of hybrids, as the value can be loaded up front at the same time as the parent row of the object, particularly if the expression is one which links to other tables (typically as a correlated subquery) to access data that wouldn’t normally be available on an already loaded object.

Disadvantages to using for SQL expressions include that the expression must be compatible with the SELECT statement emitted for the class as a whole, and there are also some configurational quirks which can occur when using column_property() from declarative mixins.

Our “fullname” example can be expressed using as follows:

from sqlalchemy.orm import column_property
from sqlalchemy import select, func
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
    pass
class Address(Base):
    __tablename__ = "address"
    user_id = mapped_column(Integer, ForeignKey("user.id"))
class User(Base):
    __tablename__ = "user"
    id = mapped_column(Integer, primary_key=True)
    address_count = column_property(
        select(func.count(Address.id))
        .where(Address.user_id == id)
        .correlate_except(Address)
        .scalar_subquery()
    )

In the above example, we define a ScalarSelect() construct like the following:

stmt = (
    select(func.count(Address.id))
    .where(Address.user_id == id)
    .correlate_except(Address)
    .scalar_subquery()
)

Above, we first use to create a Select construct, which we then convert into a using the Select.scalar_subquery() method, indicating our intent to use this statement in a column expression context.

Within the Select itself, we select the count of Address.id rows where the Address.user_id column is equated to id, which in the context of the User class is the named id (note that id is also the name of a Python built in function, which is not what we want to use here - if we were outside of the User class definition, we’d use User.id).

The Select.correlate_except() method indicates that each element in the FROM clause of this may be omitted from the FROM list (that is, correlated to the enclosing SELECT statement against User) except for the one corresponding to Address. This isn’t strictly necessary, but prevents Address from being inadvertently omitted from the FROM list in the case of a long string of joins between User and Address tables where SELECT statements against Address are nested.

For a column_property() that refers to columns linked from a many-to-many relationship, use to join the fields of the association table to both tables in a relationship:

from sqlalchemy import and_
class Author(Base):
    # ...
    book_count = column_property(
        select(func.count(books.c.id))
        .where(
            and_(
                book_authors.c.author_id == authors.c.id,
                book_authors.c.book_id == books.c.id,
            )
        )
        .scalar_subquery()
    )

If import issues prevent the from being defined inline with the class, it can be assigned to the class after both are configured. When using mappings that make use of a Declarative base class (i.e. produced by the DeclarativeBase superclass or legacy functions such as ), this attribute assignment has the effect of calling Mapper.add_property() to add an additional property after the fact:

When using mapping styles that don’t use Declarative base classes such as the decorator, the Mapper.add_property() method may be invoked explicitly on the underlying object, which can be obtained using inspect():

from sqlalchemy.orm import registry
@reg.mapped
class User:
    __tablename__ = "user"
    # ... additional mapping directives
# later ...
# works for any kind of mapping
from sqlalchemy import inspect
inspect(User).add_property(
    column_property(
        select(func.count(Address.id))
        .where(Address.user_id == User.id)
        .scalar_subquery()
    )
)

It is possible to create mappings that combine multiple objects together. The ColumnProperty will be interpreted as a SQL expression when used in a Core expression context, provided that it is targeted by an existing expression object; this works by the Core detecting that the object has a __clause_element__() method which returns a SQL expression. However, if the is used as a lead object in an expression where there is no other Core SQL expression object to target it, the ColumnProperty.expression attribute will return the underlying SQL expression so that it can be used to build SQL expressions consistently. Below, the File class contains an attribute File.path that concatenates a string token to the File.filename attribute, which is itself a :

class File(Base):
    __tablename__ = "file"
    id = mapped_column(Integer, primary_key=True)
    name = mapped_column(String(64))
    extension = mapped_column(String(8))
    filename = column_property(name + "." + extension)
    path = column_property("C:/" + filename.expression)

When the File class is used in expressions normally, the attributes assigned to filename and path are usable directly. The use of the ColumnProperty.expression attribute is only necessary when using the directly within the mapping definition:

stmt = select(File.path).where(File.filename == "foo.txt")

The column deferral feature introduced in the at Limiting which Columns Load with Column Deferral may be applied at mapping time to a SQL expression mapped by by using the deferred() function in place of :