SQlAlchemy hybrid property datetime to holidays

Question

I have an SQLAlchemy model like the following.

class ElUsage(Base):
    recid = Column(Integer(),primary_key=True)
    date_of_usage = Column(DATE())
    total_units = Column(Float(precision=5))

I was trying to create a hybrid property is_holiday by comparing a date against pandas calender

@hybrid_property
def is_holiday(self):
    is_hday = 0
    cal = calendar()
    holidays = cal.holidays(start=dt.date(2015,1,1),
                            end=dt.date(2020,12,31))
    if np.datetime64(self.usage_date) in holidays:
        is_hday = 1
    
    return is_hday

@is_holiday.expression
def is_holiday(cls):
    is_hday = 0
    cal = calendar()
    holidays = cal.holidays(start=dt.date(2015,1,1),
                            end=dt.date(2020,12,31))
    
    if np.datetime64(cls.usage_date) in holidays:
        is_hday = 1
    
    return is_hday

The explicit conversion to numpy datetime64 is a challenge. Any easy way in creating a hybrid property to mark a date as holiday or nor (0 or 1) value?

TIA

score 1 · Accepted Answer · answered Oct 09 '20 at 08:06

The first property - the python part - you're doing right, considering this answer.

@hybrid_property
def is_holiday(self):
    is_hday = 0
    cal = calendar()
    holidays = cal.holidays(start=dt.date(2015,1,1),
                            end=dt.date(2020,12,31))
    if np.datetime64(self.usage_date) in holidays:
        is_hday = 1
    
    return is_hday

The second part - the SQL part - is more complex, since you need to write a SQLAlchemy query that returns an equivalent property

@is_holiday.expression
def is_holiday(cls):
    cal = calendar()
    holidays = cal.holidays(start=dt.date(2015,1,1),
                            end=dt.date(2020,12,31))
    return cls.usage_date.in_(holidays)

I wasn't able to test this, so let me know if it doesn't work.

SQlAlchemy hybrid property datetime to holidays

1 Answers1