SQLAlchemy select distinct

Question

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.

Answer 1

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)