Use python date function inside SQLAlchemy column property

Question

I have these models in SQLAlchemy. it is defining a simple action of my system which is liking a post by an account, so each account can like several posts and also a post can be liked by many accounts(Many-to-Many), Every looks fine execpt likes_per_day column property that I want it to show number of likes each day of an account. when I run this code and keep this running, it looks like date.today() will execute at run time and always shows me the number of likes of rundate, not today.

So I'm planning to get number of likes for each day using column property. what is the correct way to do this?

class Account(Base):
 __tablename__ = 'account'
 id = Column(
 Integer,
 primary_key=True
 )
 likes_per_day = column_property(
 select([func.count(Like.id)])
 .where(Like.account_id == id)
 .where(func.date(Like.created_at) == date.today()) # Problem is here
 .correlate_except(Like)
 )
class Like(Base):
 __tablename__ = 'like'
 id = Column(
 Integer,
 primary_key=True
 )
 post_id = Column(
 Integer,
 ForeignKey('post.id')
 )
 account_id = Column(
 Integer,
 ForeignKey('account.id')
 )
 created_at = Column(
 DateTime,
 nullable=False,
 default=datetime.utcnow,
 )

Answer 1

How about to use:

 likes_per_day = column_property(
 select([func.count(Like.id)])
 .where(Like.account_id == id)
 .where(func.date(Like.created_at) == bindparam(
 'today',
 callable_=lambda:date.today().isoformat()
 )
 )
 .correlate_except(Like),
 deferred=True
 )

the date function returns the iso format of a DateTime, on the other hand the date.today() is an object of date that sqlalchemy doesn't know how should parse because of various date formats. So by declaring the date format explicitly you should get the result you want.

• Mehrdad Pedramfar: actully my code works just fine. but just for a one day because date.today() will execute once. there is no problem about filtering. the problem is every day the date.today() remains as before and points to yesterday.
• Mehrdad Pedramfar: It didnt fix the problem.
• shayan rok rok: @MehrdadPedramfar Check out the answer.