Sqlalchemy filter by calculated datetime hybrid_property

Question

I have a model Prescription.

from datetime import timedelta
from sqlalchemy.ext.hybrid import hybrid_property


class Prescription(db.Model):
    """ docstring """
    ID = db.column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    duration = db.Column(db.SmallInteger)

    @hybrid_property
    def expiration_date(self):
        # return self.date + timedelta(days=self.duration)
        return self.date + timedelta(days=7)

    @classmethod
    def actual(cls ):
        """ docstring """
        today = datetime.today()

        return cls.query.filter(Prescription.expiration_date>=today)

I want to get only actual prescriptions in my actual method, and when I specify

@hybrid_property
def expiration_date(self): 
    return self.date + timedelta(days=7)

everything works like a charm.

But every prescription has a different duration, and when I specify

@hybrid_property
def expiration_date(self): 
     return self.date + timedelta(days=self.duration)

I've got an error

TypeError: unsupported type for timedelta days component: InstrumentedAttribute

I tried to make a little hack like this

@property
def days(self):
    return int(self.duration)

but no luck. Can anyone tell some workaround, or creating some lazy object for duration field or maybe another way get actual prescriptions, filtering by calculated expiration_date?

what database are you using ? – Tech at The Sparks Foundation May 28 '19 at 16:03 — Tech at The Sparks Foundation, May 28 '19 at 16:03
PostgreSQL and package psycopg2-binary==2.7.7 – Sergey Miletskiy May 28 '19 at 16:04 — Sergey Miletskiy, May 28 '19 at 16:04

score 3 · Accepted Answer · answered May 29 '19 at 10:55

You might be trying to calculate a DATEDIFF: Calculate DATEDIFF in POSTGRES using SQLAlchemy

Here, is_expired will generate a SQL query part which calculates difference in days between the start date and utcnow(), and compares the result with self.duration

This works in PostgreSQL, but I have not tested on other RDBMS.

from datetime import datetime
import sqlalchemy as sa


class Prescription:
    ID = db.column(db.Integer, primary_key=True)
    date = db.Column(db.DateTime)
    duration = db.Column(db.SmallInteger)

    @hybrid_property
    def is_expired(self):
        days_since_published = sa.func.trunc((
            sa.extract('epoch', datetime.utcnow()) -
            sa.extract('epoch', self.date)
        ) / 3600 / 24)

        return days_since_published >= self.duration

    @classmethod
    def active(cls):
        return cls.query.filter(is_expired=False)

Tech at The Sparks Foundation · Answer 2 · 2019-05-28T18:36:09.790

0

In your @classmethod annotated actual() method, you are trying to access a non-static property(expiration_date) which is raising the error. As per my understanding of what your code is doing, I have changed the code as follow:

from datetime import datetime,timedelta
from sqlalchemy.ext.hybrid import hybrid_property
from flask_sqlalchemy import SQLAlchemy
from flask import Flask

app = Flask(__name__)
db = SQLAlchemy(app)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
app.config['SQLALCHEMY_DATABASE_URI'] ='sqlite:///'

class Prescription(db.Model):
    """ docstring """
    def mydefault(context):
        return context.get_current_parameters()['date'] + timedelta(days=context.get_current_parameters()['duration'])

    ID = db.Column(db.Integer,primary_key= True)
    date = db.Column(db.Date)
    duration = db.Column(db.SmallInteger)
    expiration_date = db.Column(db.Date,default = mydefault)

    @classmethod
    def actual(cls ):
        """ docstring """
        today = datetime.today().date()
        return cls.query.filter(Prescription.expiration_date>=today).all()
    def __repr__(self):
      return "Id:{} , date:{}, duration:{}, expiration_date:{}".format(self.ID,self.date,self.duration,self.expiration_date)

db.create_all()
q=Prescription(ID=1,date=datetime(2019,5,26).date(),duration = 1) #Expired
r=Prescription(ID=2,date=datetime(2019,5,20).date(),duration = 3) #Expired
s=Prescription(ID=3,date=datetime(2019,5,27).date(),duration = 5) #Not Expired
t = Prescription(ID=4,date=datetime.now().date(),duration = 1) #Not Expired

db.session.add(q)
db.session.add(r)
db.session.add(s)
db.session.add(t)
db.session.commit()

list_obj = Prescription.query.all()
print("All Objects in DB:-")
for l in list_obj:
  print(l)
print()
print("Valid Prescription:")
print(Prescription.actual())

Output:

One thing I can suggest to you is since the model does not need the time of expiration. So you could change

date = db.Column(db.DateTime)

to

date = db.Column(db.Date)

and it will ease your task :)

edited May 28 '19 at 18:36

answered May 28 '19 at 16:32

Tech at The Sparks Foundation

1,123
2
7
15

please, try to make a query ```Prescription.actual()```, cause I get an error after querying. – Sergey Miletskiy May 28 '19 at 16:35
in that case, `actual` is a class method. if you want to compare the attributes of something in it. you need to pass the object too. isn't it? – Tech at The Sparks Foundation May 28 '19 at 16:59
yes, it is a class method, but I need to calculate expiration_date for each object, so the comparing of the attributes run when querying invokes. – Sergey Miletskiy May 28 '19 at 17:03
can you add some code where you are using so i understand it clearly – Tech at The Sparks Foundation May 28 '19 at 17:07
just create a few prescriptions objects with different datetime and try to get actual. E.g. ```Prescription(ID=1,date=datetime(2019,5,26),duration = 1) Prescription(ID=2,date=datetime(2019,5,20),duration = 3) Prescription(ID=3,date=datetime(2019,5,27),duration = 5) Prescription.actual()``` – Sergey Miletskiy May 28 '19 at 17:18
When `Prescription.actual()` is invoked. In its code it uses `Prescription.expiration_date` which depends on an object thats why it is taking `self` as an argument. How will you satisfy its dependency. – Tech at The Sparks Foundation May 28 '19 at 17:23
oops, you are right. I think a solution in my case can be adding an additional column ```expiration_date``` and then querying over it. Thanks a lot. – Sergey Miletskiy May 28 '19 at 17:28
Sure no problem. All the best :) – Tech at The Sparks Foundation May 28 '19 at 17:37
Definitely, yes. – Sergey Miletskiy May 28 '19 at 17:40
Can you upvote and accept the answer if this was helpful. So it helps anyone else who visits your question. Have a good day. – Tech at The Sparks Foundation Jun 01 '19 at 14:29

Sqlalchemy filter by calculated datetime hybrid_property

2 Answers2