SQLAlchemy 中的计数关系 [英] Counting relationships in SQLAlchemy

问题描述

我的 SQLAlchemy 结构如下

My SQLAlchemy structure looks like this

papers2authors_table = Table('papers2authors', Base.metadata,
    Column('paper_id', Integer, ForeignKey('papers.id')),
    Column('author_id', Integer, ForeignKey('authors.id'))
)

class Paper(Base):
    __tablename__ = "papers"

    id = Column(Integer, primary_key=True)
    title = Column(String)
    handle = Column(String)

    authors = relationship("Author",
                    secondary="papers2authors",
                    backref="papers")

class Author(Base):
    __tablename__ = "authors"

    id = Column(Integer, primary_key=True)
    name = Column(String, unique=True)
    code = Column(String, unique=True)

我想查询两件事:

1. 每篇论文的作者人数
2. 每位作者的论文数量(部分回答了这里)

我用 func.count() 和 count() 尝试了很多选项，但它们返回无意义的结果.如何以 SQLAlchemy 的方式完成这两件事?

I tried many options with func.count() and count(), but they return nonsensical results. How to do these two things in an SQLAlchemy way?

我的尝试

• db.s.query(func.count(core.Paper.id)).group_by(core.Author.id).first() = sqlalchemy.exc.OperationalError:(OperationalError) 没有这样的列:authors.id
• db.s.query(func.count(core.Author.papers)).group_by(core.Author.id).first() = (128100)，这不是我所期望的
• db.s.query(core.Author.papers).group_by(core.Author.id).count().first() = AttributeError: 'int' 对象有没有属性 'first'
• ...

• db.s.query(func.count(core.Paper.id)).group_by(core.Author.id).first() = sqlalchemy.exc.OperationalError: (OperationalError) no such column: authors.id
• db.s.query(func.count(core.Author.papers)).group_by(core.Author.id).first() = (128100), which doesn't what I expected
• db.s.query(core.Author.papers).group_by(core.Author.id).count().first() = AttributeError: 'int' object has no attribute 'first'
• ...

推荐答案

找到解决方案:

1. 每篇论文的作者数量:db.s.query(core.Paper.title, func.count(core.Author.id)).join(core.Paper.authors).group_by(core.Paper.id).all()
2. 每位作者拥有的论文数量:db.s.query(core.Author.name, func.count(core.Author.id)).join(core.Author.papers).group_by(core.Author.id).all()

相关:http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.have

这篇关于SQLAlchemy 中的计数关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！