How to count the COUNT of one-to-many relationships in SQLAlchemy?

problem description

I used SQLAlchemy to build a SQLite database to store literature data. Now I want to check the number of authors of each article. The author and the document are stored in two separate tables, using the identification number of the document to establish a connection

I know how to query with SQL, but now I want to use SQLAlchemy instead of pure SQL

the definition code for literature and author objects is as follows:

    class WosDocument(Base):
        __tablename__ = "wos_document"

        document_id = Column(Integer, primary_key=True)
        unique_id = Column(String, unique=True)
        ......
        authors = relationship("WosAuthor", back_populates="document")

    class WosAuthor(Base):
        __tablename__ = "wos_author"

        author_id = Column(Integer, primary_key=True, autoincrement=True)

        document_unique_id = Column(String, ForeignKey("wos_document.unique_id"))
        document = relationship("WosDocument", back_populates="authors")

        last_name = Column(String)
        first_name = Column(String)

what result do you expect? What is the error message actually seen?

I want the result of the query to be the same as this SQL, returning the identification number of each article and the number of authors

     SELECT a.unique_id, COUNT(*) 
     FROM wos_document AS a 
     LEFT JOIN wos_author AS b 
     ON a.unique_id = b.document_unique_id 
     GROUP BY a.unique_id

but after using ORM, I can get all the author information of an article through WosDocument.authors , so I wonder if I can achieve the same effect without using join ? So I tried the following code:

    session.query(WosDocument.unique_id, len(WosDocument.authors)).all()

    session.query(WosDocument.unique_id, func.count(WosDocument.authors)).all()

the first way directly reports an error and cannot be executed, the second way only returns a row of results, and I don"t understand what the result means

[("000275510800023", 40685268)]

I would like to ask what is the correct way to write with SQLAlchemy? Can I query without using a join? Thank you!

May.25,2022

from sqlalchemy import func

session.query(WoDocument.unique_id, func.count(WoAuthor.id))
  .group_by(WoDocument.unique_id)

Note: the code has been beautified


this is a project I have come to. I hope it will be helpful to you.
https://github.com/eastossifr...

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b224ff-2b617.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b224ff-2b617.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?