SQLAlchemy, how to set hybrid expression to 'understand' datetime?-CodePudding

I'm trying to set up an SQLAlchemy hybrid property for a declarative mapped class of a Person that has a DateTime field called birth_date which represents the person's birth date.

I want to set up a @hybrid_property that would represent the person's age, like so:

class Person(Base):
    __tablename__ = 'person'
    name: str = Column(String)
    date_of_birth: DateTime = Column(DateTime)

    #works fine after the objects are loaded
    @hybrid_property
    def age(self):
        today = date.today()
        if self.date_of_birth:
            return today.year - self.date_of_birth.year - (
                    (today.month, today.day) < (self.date_of_birth.month, self.date_of_birth.day))
    
    @age.expression
    def age(cls):   #Don't know how to set this up
        pass

I'm having trouble setting up the expression for the hybrid property. As far as I understand, the expression should return an SQL statement that would help filter/ query the database for the preson's Age.

To that end, the following SQL works

SELECT (strftime('%Y', 'now') - strftime('%Y', person.date_of_birth)) - (strftime('%m-%d', 'now') < strftime('%m-%d', person.date_of_birth)) as age from person

But I don't know how to 'tell' the expression to use this SQL (Or even if it's the correct way to go about this.) I tried to use text like so:

@age.expression
def age(cls):
    current_time = datetime.utcnow()   
    return text(f"{current_time.year} - strftime('%Y', '{cls.date_of_birth}')")

But it didn't work. I don't know how to tell the expression to use the SQL statement as a select for a virtual column. (That would be the age column)

The goal is to be able to filter and query on the age property like so:

session.query(Person).filter(Person.age > 25)

Please render assistance.

CodePudding user response：

That part of a hybrid property needs to return an executable SQLAlchemy clause. And since Postgres already has a suitable function for this, you can just use it:

import sqlalchemy as sa

@age.expression
def age(cls):
    return sa.func.age(cls.date_of_birth)

The function: docs, look for age(timestamp).

Or in MySQL:

@age.expression
def age(cls):
    return sa.func.timestampdiff('year', cls.date_of_birth, sa.func.curdate())

Or in SQLite:

@age.expression
def age(cls):
  strftime = sa.func.strftime

  year_diff = strftime('%Y', 'now') - strftime('%Y', cls.date_of_birth)
  # If the month and date of the current year are before the
  # month and date of birth, then we need to subtract one year
  # because the "birthday" hasn't happened yet.
  is_partial_year = strftime('%m-%d', 'now') < strftime('%m-%d', cls.date_of_birth)

  return year_diff - is_partial_year