python sqlacalchemy许多表中的任何条目[重复]

qry = session.query(parenttable).all() for parent in qry: if parent.children != None: childrenindication = [{'Name': '...'}] else: childrenindication = [] hierarchylist.append({ 'Name': parent.name, 'Children': childrenindication })

假设一个样本模型:

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)

    children = relationship("Child", cascade="all", backref="parent")


class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False)
    parent_id = Column(ForeignKey(Parent.id))

下面列出了几个选项,其中第一个选项是您问题的最直接答案:

选项1:使用relationship.any(…)-可能是最快的

has_children = Parent.children.any()
q = session.query(Parent, has_children)
for parent, has_children in q.all():
    print(parent, has_children)

选项2:使用子查询获取子查询数

# @note: returns None instead of 0 for parent with no children
from sqlalchemy import func
subq = (
    session.query(Child.parent_id, func.count(Child.id).label("num_children"))
    .group_by(Child.parent_id)
    .subquery()
)
q = (session
     .query(Parent, subq.c.num_children)
     .outerjoin(subq, Parent.id == subq.c.parent_id)
     )
for parent, has_children in q.all():
    print(parent, has_children)

选项3:获取不带子查询的子级数目(如果父表有,则很好

# not have many columns
from sqlalchemy import func
q = (session
     .query(Parent, func.count(Child.id).label("num_children"))
     .outerjoin(Child, Parent.children)
     .group_by(Parent)
     )
for parent, has_children in q.all():
    print(parent, has_children)