python - SQLAlchemy 选择不同的

Question

我有以下代码:

    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

但我试图更好地理解 SQLAlchemy，并希望将上述 SQL 语句转换为一个只返回不同行数的 SQLAlchemy ORM 对象。

Answer 1

假设 Autoresponder 是一个映射类:

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)

下面的查询应该这样做:

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)