Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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'
  • ...
like image 701
Anton Tarasenko Avatar asked Aug 26 '14 08:08

Anton Tarasenko


People also ask

How do you create a many to many relationship in SQLAlchemy?

Python Flask and SQLAlchemy ORM Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table's primary key.

What is relationship in SQLAlchemy?

The relationship function is a part of Relationship API of SQLAlchemy ORM package. It provides a relationship between two mapped classes. This corresponds to a parent-child or associative table relationship.

What is Backref in SQLAlchemy?

The sqlalchemy backref is one of the type keywords and it passed as the separate argument parameters which has to be used in the ORM mapping objects. It mainly includes the event listener on the configuration attributes with both directions of the user datas through explicitly handling the database relationships.


1 Answers

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

like image 53
Anton Tarasenko Avatar answered Sep 23 '22 10:09

Anton Tarasenko