SQL Expressions as Mapped Attributes¶

Attributes on a mapped class can be linked to SQL expressions, which can be used in queries.

Using a Hybrid¶

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 Hybrid Attributes. 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:

from sqlalchemy.ext.hybrid import hybrid_property


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

    @hybrid_property
    def fullname(self):
        return self.firstname + " " + self.lastname

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

some_user = session.query(User).first()
print(some_user.fullname)

as well as usable within queries:

some_user = session.query(User).filter(User.fullname == "John Smith").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 case() construct for SQL expressions:

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql import case


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    firstname = Column(String(50))
    lastname = 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,
        )

Using column_property¶

The column_property() 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 column_property() 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 column_property() as follows:

from sqlalchemy.orm import column_property


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

Correlated subqueries may be used as well. Below we use the select() construct to create a ScalarSelect, representing a column-oriented SELECT statement, that links together the count of Address objects available for a particular User:

from sqlalchemy.orm import column_property
from sqlalchemy import select, func
from sqlalchemy import Column, Integer, String, ForeignKey

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Address(Base):
    __tablename__ = "address"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey("user.id"))


class User(Base):
    __tablename__ = "user"
    id = 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 select() to create a Select construct, which we then convert into a scalar subquery using the Select.scalar_subquery() method, indicating our intent to use this Select 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 Column 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 select() 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.

If import issues prevent the column_property() 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() base class, this attribute assignment has the effect of calling Mapper.add_property() to add an additional property after the fact:

# only works if a declarative base class is in use
User.address_count = column_property(
    select(func.count(Address.id)).where(Address.user_id == User.id).scalar_subquery()
)

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

from sqlalchemy.orm import registry

reg = 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()
    )
)

For a column_property() that refers to columns linked from a many-to-many relationship, use and_() 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()
    )

Composing from Column Properties at Mapping Time¶

It is possible to create mappings that combine multiple ColumnProperty 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 ColumnProperty 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 ColumnProperty:

class File(Base):
    __tablename__ = "file"

    id = Column(Integer, primary_key=True)
    name = Column(String(64))
    extension = 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 ColumnProperty directly within the mapping definition:

q = session.query(File.path).filter(File.filename == "foo.txt")

Using a plain descriptor¶

In cases where a SQL query more elaborate than what column_property() or hybrid_property can provide must be emitted, a regular Python function accessed as an attribute can be used, assuming the expression only needs to be available on an already-loaded instance. The function is decorated with Python’s own @property decorator to mark it as a read-only attribute. Within the function, object_session() is used to locate the Session corresponding to the current object, which is then used to emit a query:

from sqlalchemy.orm import object_session
from sqlalchemy import select, func


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

    @property
    def address_count(self):
        return object_session(self).scalar(
            select(func.count(Address.id)).where(Address.user_id == self.id)
        )

The plain descriptor approach is useful as a last resort, but is less performant in the usual case than both the hybrid and column property approaches, in that it needs to emit a SQL query upon each access.

Query-time SQL expressions as mapped attributes¶

When using Session.query(), we have the option to specify not just mapped entities but ad-hoc SQL expressions as well. Suppose if a class A had integer attributes .x and .y, we could query for A objects, and additionally the sum of .x and .y, as follows:

q = session.query(A, A.x + A.y)

The above query returns tuples of the form (A object, integer).

An option exists which can apply the ad-hoc A.x + A.y expression to the returned A objects instead of as a separate tuple entry; this is the with_expression() query option in conjunction with the query_expression() attribute mapping. The class is mapped to include a placeholder attribute where any particular SQL expression may be applied:

from sqlalchemy.orm import query_expression


class A(Base):
    __tablename__ = "a"
    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    y = Column(Integer)

    expr = query_expression()

We can then query for objects of type A, applying an arbitrary SQL expression to be populated into A.expr:

from sqlalchemy.orm import with_expression

q = session.query(A).options(with_expression(A.expr, A.x + A.y))

The query_expression() mapping has these caveats:

On an object where query_expression() were not used to populate the attribute, the attribute on an object instance will have the value None, unless the query_expression.default_expr parameter is set to an alternate SQL expression.

The query_expression value does not populate on an object that is already loaded. That is, this will not work:

obj = session.query(A).first()

obj = session.query(A).options(with_expression(A.expr, some_expr)).first()

To ensure the attribute is re-loaded, use Query.populate_existing():

obj = (
    session.query(A)
    .populate_existing()
    .options(with_expression(A.expr, some_expr))
    .first()
)

The query_expression value does not refresh when the object is expired. Once the object is expired, either via Session.expire() or via the expire_on_commit behavior of Session.commit(), the value is removed from the attribute and will return None on subsequent access. Only by running a new Query that touches the object which includes a new with_expression() directive will the attribute be set to a non-None value.
with_expression(), as an object loading option, only takes effect on the outermost part of a query and only for a query against a full entity, and not for arbitrary column selects, within subqueries, or the elements of a compound statement such as a UNION. See the next section Using with_expression() with UNIONs, other subqueries for an example.

Changed in version 1.4: This is new as of version 1.4. See the change notes at Column loaders such as deferred(), with_expression() only take effect when indicated on the outermost, full entity query for background.
The mapped attribute cannot be applied to other parts of the query, such as the WHERE clause, the ORDER BY clause, and make use of the ad-hoc expression; that is, this won’t work:
```
# wont work
q = (
    session.query(A)
    .options(with_expression(A.expr, A.x + A.y))
    .filter(A.expr > 5)
    .order_by(A.expr)
)
```
The A.expr expression will resolve to NULL in the above WHERE clause and ORDER BY clause. To use the expression throughout the query, assign to a variable and use that:
```
a_expr = A.x + A.y
q = (
    session.query(A)
    .options(with_expression(A.expr, a_expr))
    .filter(a_expr > 5)
    .order_by(a_expr)
)
```

New in version 1.2.

Using `with_expression()` with UNIONs, other subqueries¶

The with_expression() construct is an ORM loader option, and as such may only be applied to the outermost level of a SELECT statement which is to load a particular ORM entity. It does not have any effect if used inside of a select() that will then be used as a subquery or as an element within a compound statement such as a UNION.

Changed in version 1.4: The behavior of column loader options applying only at the outermost layer of an ORM SELECT statement was previously not applied consistently; in 1.4 it applies to all loader options for both columns as well as relationships. Background on this change is at Column loaders such as deferred(), with_expression() only take effect when indicated on the outermost, full entity query.

In order to use arbitrary SQL expressions in subqueries, normal Core-style means of adding expressions should be used. To assemble a subquery-derived expression onto the ORM entity’s query_expression() attributes, with_expression() is used at the top layer of ORM object loading, referencing the SQL expression within the subquery.

Note

The example below uses 2.0 style queries in order to demonstrate a UNION. ORM UNIONs may be assembled without ambiguity using this style of query.

In the example below, two select() constructs are used against the ORM entity A with an additional SQL expression labeled in expr, and combined using union_all(). Then, at the topmost layer, the A entity is SELECTed from this UNION, using the querying technique described at Selecting Entities from UNIONs and other set operations, adding an option with with_expression() to extract this SQL expression onto newly loaded instances of A:

>>> fromsqlalchemyimportunion_all>>> s1=(...     select(User, func.count(Book.id).label("book_count"))...     .join_from(User, Book)...     .where(User.name == "spongebob")... )>>> s2=(...     select(User, func.count(Book.id).label("book_count"))...     .join_from(User, Book)...     .where(User.name == "sandy")... )>>> union_stmt=union_all(s1,s2)>>> orm_stmt=(...     select(User)...     .from_statement(union_stmt)...     .options(with_expression(User.book_count, union_stmt.c.book_count))... )>>> foruserinsession.scalars(orm_stmt):...     print(f"Username: {user.name}  Number of books: {user.book_count}")SELECT user_account.id, user_account.name, user_account.fullname, count(book.id) AS book_count
FROM user_account JOIN book ON user_account.id = book.owner_id
WHERE user_account.name = ?
UNION ALL
SELECT user_account.id, user_account.name, user_account.fullname, count(book.id) AS book_count
FROM user_account JOIN book ON user_account.id = book.owner_id
WHERE user_account.name = ?
[...] ('spongebob', 'sandy')
Username: spongebob  Number of books: 3Username: sandy  Number of books: 3

SQLAlchemy 1.4 Documentation

SQLAlchemy 1.4 Documentation

SQLAlchemy ORM

SQL Expressions as Mapped Attributes¶

Using a Hybrid¶

Using column_property¶

Composing from Column Properties at Mapping Time¶

Using a plain descriptor¶

Query-time SQL expressions as mapped attributes¶

Using `with_expression()` with UNIONs, other subqueries¶

SQLAlchemy 1.4 Documentation

SQLAlchemy 1.4 Documentation

SQLAlchemy ORM

SQL Expressions as Mapped Attributes¶

Using a Hybrid¶

Using column_property¶

Composing from Column Properties at Mapping Time¶

Using a plain descriptor¶

Query-time SQL expressions as mapped attributes¶

Using with_expression() with UNIONs, other subqueries¶

Using `with_expression()` with UNIONs, other subqueries¶