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)
I would like to query two things:
- The numbers of authors in each paper
- The numbers of papers each author has (partly answered here)
I tried many options with func.count()
and count()
, but they return nonsensical results. How to do these two things in an SQLAlchemy way?
What I tried
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'
- ...
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…