How do I implement a null coalescing operator in SQLAlchemy?

How do I implement a null coalescing operator in SQLAlchemy?

sqlalchemy coalesce
sqlalchemy not
sqlalchemy not in
sqlalchemy func
sqlalchemy case
sqlalchemy or_
sqlalchemy alias
sqlalchemy group by

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 ;)


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, func


class 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.start


class 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

python, Or how do I make this thing work? I have an Interval object: class Interval(Base): __tablename__ = 'intervals' id = Column(Integer,  12 How do I implement a null coalescing operator in SQLAlchemy? Jun 6 '14 8 How can I avoid Flask-Admin 2.1 warning “UserWarning: Fields missing from ruleset”?


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

🤾 How to implement a null coalescing statement in SQLAlchemy , SQLAlchemy is not smart enough to build a tree of SQL expressions from these operands, you need to use the explicit constructor  When you work with nullable value types and need to provide a value of an underlying value type, use the null-coalescing operator to specify the value to provide in case a nullable type value is null:


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 compiles
from sqlalchemy.sql.expression import FunctionElement

class 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 coalesce
select([coalesce(A.value, '---')])  # etc

Hope that helps.

Handling null values in a SQLAlchemy query - equivalent , SQLAlchemy allows you to use the equivalent of SQL's COALESCE function to handle NULL values. I didn't find the documentation for this  A null coalescing operator, in C#, is an operator that is used to check whether the value of a variable is null. It is represented by the symbol "??". The null coalescing operator allows for the selection of the first non-null value from a pair of values. It is used to set the default value for variables of nullable value type or reference type.


Column Elements and Expressions, The and_() conjunction is also available using the Python & operator The CASE construct in SQL is a conditional object that acts When omitted, most databases will produce a result of NULL if none of the “when” expressions evaluate to true. Column to override columns during table reflection. Null conditional. Similar to the coalescing operator, the null conditional operator tests for null before accessing a member of an instance. First example. The null coalescing operator is useful inside properties. Often, a property that returns an object (such as a string) may be null. This null value complicates things. Property Null.


Base Type API, Note that this method, when implemented, should always return the exact same structure, without any conditional logic, as it may be used in an executemany() call Given an operator and value, gives the type a chance to return a type Forcing NULL on a column with a default - in the ORM documentation. The null coalescing operator (??) is a binary operator in C# that evaluates and checks if the first operand is null. If it isn’t, then the value of the first operand is the value of the operation; if it is, then the second operand is evaluated and returned.


Null coalescing operator, The null coalescing operator is a binary operator that is part of the syntax for a basic conditional For example, if one wishes to implement some C# code to give a page a default title if none is present, one may use the following "Is there a Python equivalent of the C sharp null-coalescing operator". stackoverflow.com. Column Elements and Expressions¶ The expression API consists of a series of classes each of which represents a specific lexical element within a SQL string. Composed together into a larger structure, they form a statement construct that may be compiled into a string representation that can be passed to a database.