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!