CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
dennismonsewicz
dennismonsewicz

Reputation: 25542

SQLAlchemy select distinct

I have the following code:

    session = Session()
    query = session.query('count').from_statement(
        """
            SELECT COUNT(DISTINCT autoresponder.campaign_id) as count
            FROM autoresponder
            WHERE autoresponder.account_id=:account_id AND autoresponder.is_active='t'
        """
    ).params(account_id=account_id).all()

    print query[0].count

But I am trying to understand SQLAlchemy better and want to transform the above SQL statement into a SQLAlchemy ORM object that just returns the a count of distinct rows.

Upvotes: 3

Views: 4960

Answers (1)

van
van

Reputation: 76962

Assuming that Autoresponder is a mapped class:

class Autoresponder(Base):
    __tablename__ = 'autoresponder'
    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, ForeignKey("account.id"))
    # account_id = Column(Integer)  # @note: probably a ForeignKey("account.id"))
    campaign_id = Column(Integer)  # @note: probably as well a FK
    is_active = Column(Boolean)

below query should do it:

from sqlalchemy import func
cnt = (session.query(func.count(Autoresponder.campaign_id.distinct()).label("count"))
    .filter(Autoresponder.account_id == account_id)
    .filter(Autoresponder.is_active == True)
).scalar()
print(cnt)

Upvotes: 4

Related Questions