CODEWITHSUNDEEP
pythonpython-3.xdatesqlalchemy
Mehrdad Pedramfar
Mehrdad Pedramfar

Reputation: 11073

Use python date function inside SQLAlchemy column property

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,
    )

Upvotes: 3

Views: 369

Answers (1)

shayan rok rok
shayan rok rok

Reputation: 540

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.

Upvotes: 3

Related Questions