Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
636 views
in Technique[技术] by (71.8m points)

python - Counting relationships in 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)

I would like to query two things:

  1. The numbers of authors in each paper
  2. 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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Found the solutions:

  1. The number of authors each paper has: db.s.query(core.Paper.title, func.count(core.Author.id)).join(core.Paper.authors).group_by(core.Paper.id).all()
  2. The number of papers each author has: db.s.query(core.Author.name, func.count(core.Author.id)).join(core.Author.papers).group_by(core.Author.id).all()

Relevant: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html#sqlalchemy.orm.query.Query.having


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...