sqlalchemy: Delete criteria - where clause in which one column is to be compared against another column of same table

Question

I have a table called "Stats". It has three columns:

1. created
2. data (JSON blob)
3. max_age_ms ("ms" means milliseconds. Say max_age_ms=60,000, if created is older than 60 sec, then it's considered ready-for-purging)

How do you express the where clause?

utc_now = datetime.utcnow()
purge_ = Stats.__table__.delete().where(Stats.created>=(utc_now - timedelta(seconds=Stats.max_age_ms/1000)))
session.execute(purge_)
session.flush()

Above attempt lead to: Exception has occurred: TypeError unsupported type for timedelta seconds component: BinaryExpression

The problem is with "Stats.max_age_ms", I am unsure how to rewrite this. The Right-Hand-Side of the comparison operator cannot be a column name?

I tried hybrid_property with both "filter_by" and "filter", by same error.

session.query(Stats).filter_by(age_seconds>100)
    NameError: name 'age_seconds' is not defined


session.query(Stats).filter(Stats.age_seconds>100)
    ...AttributeError: 'Comparator' object has no attribute 'seconds'

    The above exception was the direct cause of the following exception:

    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "C:\ProgramData\Anaconda3\envs\py39\lib\site-packages\sqlalchemy\ext\hybrid.py", line 925, in __get__
        return self._expr_comparator(owner)
      File "C:\ProgramData\Anaconda3\envs\py39\lib\site-packages\sqlalchemy\ext\hybrid.py", line 1143, in expr_comparator
        ...
    AttributeError: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'seconds'

With purge_dt, similar:

utc_now = datetime.utcnow()
old_entries = session.query(Stats).filter(Stats.purge_dt>Stats.created).all()
Exception has occurred: TypeError ... unsupported type for timedelta days component: InstrumentedAttribute

A little stuck, I atm, get by doing this line by line operation (it works when table small, but when this assumption invalidated, Performance concern here):

stats = session.query(Stats).all()
ready_for_purge = [x for x in stats if x.created<=x.purge_dt]
for x in ready_for_purge:
    session.delete(x)
session.flush()

Thanks in advance.

References

import sqlalchemy as sa
import sqlalchemy.dialects.postgresql as psql
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy.ext.hybrid import hybrid_property

class Stats(DbModel):
    __tablename__ = "stats"

    id = sa.Column(sa.Integer, primary_key=True)
    data = sa.Column(MutableDict.as_mutable(psql.JSONB))
    max_age_ms = sa.Column(sa.Integer(), index=False, nullable=False)
    created = sa.Column(sa.DateTime, nullable=False, default=datetime_gizmo.utc_now)

    @hybrid_property
    def age_seconds(self):
        utc_now = datetime.utcnow()
        return (utc_now - self.created).seconds

    @hybrid_property
    def purge_dt(self):
        utc_now = datetime.utcnow()
        return utc_now - timedelta(seconds=self.max_age_ms/1000)

    @purge_dt.expression
    def purge_dt(cls):
        utc_now = datetime.utcnow()
        return sa.func.dateadd(sa.func.now(), sa.bindparam('timedelta', timedelta(seconds=cls.max_age_ms/1000), sa.Interval()))

Using DATEADD in sqlalchemy

https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_filter_operators.htm

https://docs.sqlalchemy.org/en/14/core/tutorial.html#functions

https://docs.sqlalchemy.org/en/14/core/functions.html

https://docs.sqlalchemy.org/en/14/orm/extensions/hybrid.html

SQLAlchemy: How do you delete multiple rows without querying

Answer 1

To make hybrid_property work with filter and filter_by you have to define expression. But it should be defined at database level and implementation for various databases would be different. For PostgreSQL it would look something like that:

class Stats(Base):
    __tablename__ = "stats"

    id = sa.Column(sa.Integer, primary_key=True)
    data = sa.Column(MutableDict.as_mutable(psql.JSONB))
    max_age_ms = sa.Column(sa.Integer(), index=False, nullable=False, server_default='1000')
    created = sa.Column(sa.DateTime, nullable=False, default=datetime.utcnow)

    @hybrid_property
    def age_seconds(self):
        utc_now = datetime.utcnow()
        return (utc_now - self.created).seconds

    @age_seconds.expression
    def age_seconds(cls):
        return func.date_part('second', func.timezone('UTC', func.now()) - cls.created)

    @hybrid_property
    def purge_dt(self):
        utc_now = datetime.utcnow()
        return utc_now - timedelta(seconds=self.max_age_ms/1000)

    @purge_dt.expression
    def purge_dt(cls):
        seconds_delta = literal('1 second').cast(Interval) * (cls.max_age_ms / 1000)
        return func.timezone('UTC', func.now()) - seconds_delta

Now, this statements should not raise an exception:

session.query(Stats).filter(Stats.age_seconds > 100)
session.query(Stats).filter(Stats.purge_dt > "2022-07-05")

If you are using other database, you have to find functions at database level that provide you utcnow functionality and take seconds from it.