SQL Expressions as Mapped Attributes

Attributes on a mapped class can be linked to SQL expressions, which canbe 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 providesfor an expression that works at both the Python level as well as at theSQL expression level. For example, below we map a class User,containing attributes firstname and lastname, and include a hybrid thatwill provide for us the fullname, which is the string concatenation of the two:

  1. from sqlalchemy.ext.hybrid import hybrid_property
  2.  
  3. class User(Base):
  4.     __tablename__ = 'user'
  5.     id = Column(Integer, primary_key=True)
  6.     firstname = Column(String(50))
  7.     lastname = Column(String(50))
  8.  
  9.     @hybrid_property
  10.     def fullname(self):
  11.         return self.firstname + " " + self.lastname

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

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

as well as usable within queries:

  1. some_user = session.query(User).filter(User.fullname == "John Smith").first()

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

  1. from sqlalchemy.ext.hybrid import hybrid_property
  2. from sqlalchemy.sql import case
  3.  
  4. class User(Base):
  5.     __tablename__ = 'user'
  6.     id = Column(Integer, primary_key=True)
  7.     firstname = Column(String(50))
  8.     lastname = Column(String(50))
  9.  
  10.     @hybrid_property
  11.     def fullname(self):
  12.         if self.firstname is not None:
  13.             return self.firstname + " " + self.lastname
  14.         else:
  15.             return self.lastname
  16.  
  17.     @fullname.expression
  18.     def fullname(cls):
  19.         return case([
  20.             (cls.firstname != None, cls.firstname + " " + cls.lastname),
  21.         ], else_ = cls.lastname)

Using column_property

The orm.column_property() function can be used to map a SQLexpression in a manner similar to a regularly mapped Column.With this technique, the attribute is loadedalong with all other column-mapped attributes at load time. This is in somecases an advantage over the usage of hybrids, as the value can be loadedup front at the same time as the parent row of the object, particularly ifthe expression is one which links to other tables (typically as a correlatedsubquery) to access data that wouldn’t normally beavailable on an already loaded object.

Disadvantages to using orm.column_property() for SQL expressions include thatthe expression must be compatible with the SELECT statement emitted for the classas a whole, and there are also some configurational quirks which can occurwhen using orm.column_property() from declarative mixins.

Our “fullname” example can be expressed using orm.column_property() asfollows:

  1. from sqlalchemy.orm import column_property
  2.  
  3. class User(Base):
  4.     __tablename__ = 'user'
  5.     id = Column(Integer, primary_key=True)
  6.     firstname = Column(String(50))
  7.     lastname = Column(String(50))
  8.     fullname = column_property(firstname + " " + lastname)

Correlated subqueries may be used as well. Below we use the select()construct to create a SELECT that links together the count of Addressobjects available for a particular User:

  1. from sqlalchemy.orm import column_property
  2. from sqlalchemy import select, func
  3. from sqlalchemy import Column, Integer, String, ForeignKey
  4.  
  5. from sqlalchemy.ext.declarative import declarative_base
  6.  
  7. Base = declarative_base()
  8.  
  9. class Address(Base):
  10.     __tablename__ = 'address'
  11.     id = Column(Integer, primary_key=True)
  12.     user_id = Column(Integer, ForeignKey('user.id'))
  13.  
  14. class User(Base):
  15.     __tablename__ = 'user'
  16.     id = Column(Integer, primary_key=True)
  17.     address_count = column_property(
  18.         select([func.count(Address.id)]).\
  19.             where(Address.user_id==id).\
  20.             correlate_except(Address)
  21.     )

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

  1. select([func.count(Address.id)]).\
  2.     where(Address.user_id==id).\
  3.     correlate_except(Address)

The meaning of the above statement is, select the count of Address.id rowswhere the Address.user_id column is equated to id, which in the contextof the User class is the Column named id (note that id isalso the name of a Python built in function, which is not what we want to usehere - if we were outside of the User class definition, we’d use User.id).

The select.correlate_except() directive indicates that each element in theFROM clause of this select() may be omitted from the FROM list (that is, correlatedto the enclosing SELECT statement against User) except for the one correspondingto Address. This isn’t strictly necessary, but prevents Address frombeing inadvertently omitted from the FROM list in the case of a long stringof joins between User and Address tables where SELECT statements againstAddress are nested.

If import issues prevent the column_property() from being definedinline with the class, it can be assigned to the class after bothare configured. In Declarative this has the effect of calling Mapper.add_property()to add an additional property after the fact:

  1. User.address_count = column_property(
  2.         select([func.count(Address.id)]).\
  3.             where(Address.user_id==User.id)
  4.     )

For many-to-many relationships, use and_() to join the fields of theassociation table to both tables in a relation, illustratedhere with a classical mapping:

  1. from sqlalchemy import and_
  2.  
  3. mapper(Author, authors, properties={
  4.     'book_count': column_property(
  5.                         select([func.count(books.c.id)],
  6.                             and_(
  7.                                 book_authors.c.author_id==authors.c.id,
  8.                                 book_authors.c.book_id==books.c.id
  9.                             )))
  10.     })

Using a plain descriptor

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

  1. from sqlalchemy.orm import object_session
  2. from sqlalchemy import select, func
  3.  
  4. class User(Base):
  5.     __tablename__ = 'user'
  6.     id = Column(Integer, primary_key=True)
  7.     firstname = Column(String(50))
  8.     lastname = Column(String(50))
  9.  
  10.     @property
  11.     def address_count(self):
  12.         return object_session(self).\
  13.             scalar(
  14.                 select([func.count(Address.id)]).\
  15.                     where(Address.user_id==self.id)
  16.             )

The plain descriptor approach is useful as a last resort, but is less performantin the usual case than both the hybrid and column property approaches, in thatit 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 justmapped entities but ad-hoc SQL expressions as well. Suppose if a classA had integer attributes .x and .y, we could query for Aobjects, and additionally the sum of .x and .y, as follows:

  1. 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 thereturned A objects instead of as a separate tuple entry; this is thewith_expression() query option in conjunction with thequery_expression() attribute mapping. The class is mappedto include a placeholder attribute where any particular SQL expressionmay be applied:

  1. from sqlalchemy.orm import query_expression
  2.  
  3. class A(Base):
  4.     __tablename__ = 'a'
  5.     id = Column(Integer, primary_key=True)
  6.     x = Column(Integer)
  7.     y = Column(Integer)
  8.  
  9.     expr = query_expression()

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

  1. from sqlalchemy.orm import with_expression
  2. q = session.query(A).options(
  3.     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 populatethe attribute, the attribute on an object instance will have the valueNone.

  • The query_expression value does not refresh when the object isexpired. Once the object is expired, either via Session.expire()or via the expire_on_commit behavior of Session.commit(), the value isremoved from the attribute and will return None on subsequent access.Only by running a new Query that touches the object which includesa new with_expression() directive will the attribute be set to anon-None value.

  • The mapped attribute currently cannot be applied to other parts of thequery, such as the WHERE clause, the ORDER BY clause, and make use of thead-hoc expression; that is, this won’t work:

  1. # wont work
  2. q = session.query(A).options(
  3.     with_expression(A.expr, A.x + A.y)
  4. ).filter(A.expr > 5).order_by(A.expr)

The A.expr expression will resolve to NULL in the above WHERE clauseand ORDER BY clause. To use the expression throughout the query, assign to avariable and use that:

  1. a_expr = A.x + A.y
  2. q = session.query(A).options(
  3.     with_expression(A.expr, a_expr)
  4. ).filter(a_expr > 5).order_by(a_expr)

New in version 1.2.