Sasha Chedygov Sasha Chedygov - 1 year ago 363
Python Question

Lazy loading on column_property in SQLAlchemy

Say I have the following models:

class Department(Base):
__tablename__ = 'departments'
id = Column(Integer, primary_key=True)

class Employee(Base):
__tablename__ = 'employees'
id = Column(Integer, primary_key=True)
department_id = Column(None, ForeignKey(, nullable=False)
department = relationship(Department, backref=backref('employees'))

Sometimes, when I query departments, I would also like to fetch the number of employees they have. I can achieve this with a
, like so:

Department.employee_count = column_property(
.where(Employee.department_id ==

Department.query.get(1).employee_count # Works

But then the count is always fetched via a subquery, even when I don't need it. Apparently I can't ask SQLAlchemy not to load this at query time, either:

# Exception: can't locate strategy for <class ''> (('lazy', 'noload'),)

I've also tried implementing this with a hybrid property instead of a column property:

class Department(Base):

def employee_count(self):
return len(self.employees)

def employee_count(cls):
return (
.where(Employee.department_id ==

With no luck:

# AttributeError: 'Select' object has no attribute 'property'

I know I can just query the count as a separate entity in the query, but I'd really prefer to have it as an attribute on the model. Is this even possible in SQLAlchemy?

Edit: To clarify, I want to avoid the N+1 problem and have the employee count get loaded in the same query as the departments, not in a separate query for each department.

Answer Source

The loading strategies that you tried are for relationships. The loading of a column_property is altered in the same way as normal columns, see Deferred Column Loading.

You can defer the loading of employee_count by default by passing deferred=True to column_property. When a column is deferred, a select statement is emitted when the property is accessed.

defer and undefer from sqlalchemy.orm allow this to be changed when constructing a query:

from sqlalchemy.orm import undefer
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download