How do I implement a null coalescing operator in SQLAlchemy?

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 coalescemy_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()

SQLAlchemy is not smart enough to build SQL expression tree from these operands, you have to use explicit propname.expression decorator to provide it. But then comes another problem: there is no portable way to convert interval to hours in-database. You'd use TIMEDIFF in MySQL, EXTRACT(EPOCH FROM ... ) / 3600 in PostgreSQL etc. I suggest changing properties to return timedelta instead, and comparing apples to apples.

from sqlalchemy import select, funcclass Interval(Base):    ...    @hybrid_property    def time_spent(self):        return (self.end or datetime.now()) - self.start    @time_spent.expression    def time_spent(cls):        return func.coalesce(cls.end, func.current_timestamp()) - cls.startclass Task(Base):    ...    @hybrid_property    def time_spent(self):        return sum((i.time_spent for i in self.intervals), timedelta(0))    @time_spent.expression    def hours_spent(cls):        return (select([func.sum(Interval.time_spent)])            .where(cls.id==Interval.task_id)            .label('time_spent'))

The final query is:

session.query(Task).filter(Task.time_spent > timedelta(hours=3)).all()

which translates to (on PostgreSQL backend):

SELECT task.id AS task_id, task.title AS task_title FROM task WHERE (SELECT sum(coalesce(interval."end", CURRENT_TIMESTAMP) - interval.start) AS sum_1 FROM interval WHERE task.id = interval.task_id) > %(param_1)s

There is a complete example of making a func action similar to coalesc or nvl.

Note how it takes in arguements, and renders an expression... in this case NVL(a, b) when used with Oracle.

http://docs.sqlalchemy.org/en/latest/core/compiler.html#subclassing-guidelines

from sqlalchemy.ext.compiler import compilesfrom sqlalchemy.sql.expression import FunctionElementclass coalesce(FunctionElement):     name = 'coalesce'@compiles(coalesce)def compile(element, compiler, **kw):     return "coalesce(%s)" % compiler.process(element.clauses)@compiles(coalesce, 'oracle')def compile(element, compiler, **kw):     if len(element.clauses) > 2:          raise TypeError("coalesce only supports two arguments on Oracle")     return "nvl(%s)" % compiler.process(element.clauses)

Then when you want to use it...

from my_oracle_functions_sqla import coalesceselect([coalesce(A.value, '---')])  # etc

Hope that helps.