How do I implement a null coalescing operator in SQLAlchemy?

Question

Or how do I make this thing work?

I have an Interval object:

class Interval(Base):     __tablename__ = 'intervals'     id = Column(Integer, primary_key=True)     start = Column(DateTime)     end = Column(DateTime, nullable=True)     task_id = Column(Integer, ForeignKey('tasks.id'))  @hybrid_property #used to just be @property def hours_spent(self):     end = self.end or datetime.datetime.now()     return (end-start).total_seconds()/60/60 

And a Task:

class Task(Base):     __tablename__ = 'tasks'     id = Column(Integer, primary_key=True)     title = Column(String)     intervals = relationship("Interval", backref="task")  @hybrid_property  # Also used to be just @property def hours_spent(self):     return sum(i.hours_spent for i in self.intervals) 

Add all the typical setup code, of course.

Now when I try to do session.query(Task).filter(Task.hours_spent > 3).all()

I get NotImplementedError: <built-in function getitem> from the sum(i.hours_spent... line.

So I was looking at this part of the documentation and theorized that there might be some way that I can write something that will do what I want. This part also looks like it may be of use, and I'll be looking at it while waiting for an answer here ;)

Answer 1

For a simple example of SQLAlchemy's coalesce function, this may help: Handling null values in a SQLAlchemy query - equivalent of isnull, nullif or coalesce.

Here are a couple of key lines of code from that post:

from sqlalchemy.sql.functions import coalesce my_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()