与SqlAlchemy的内部联接

本文介绍了与SqlAlchemy的内部联接的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个带计数的原始内部联接查询，直接在Postgres SQL上编写：

I have a raw inner join query with counting, written directly on Postgres SQL:

SELECT "films"."id" AS "megaId", COUNT("filmComments"."id") AS "numOfComments" FROM "films" INNER JOIN "filmComments" ON ("films"."id" = "filmComments"."filmId") GROUP BY "films"."id";

如何使用普通的SqlAlchemy在没有 connection.execute的情况下进行相同的操作（sqlCode）？

How can I make the same, using normal SqlAlchemy, without connection.execute(sqlCode)?

PS我的SqlAlchemy表类：

P.S. My SqlAlchemy table classes:

from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() from sqlalchemy import Column, Integer, String, Date, Float class Film(Base): __tablename__ = "films" id = Column(Integer, primary_key = True) name = Column(String) rating = Column(Float) marksCount = Column(Integer) commentsCount = Column(Integer, index=True) class FilmComment(Base): __tablename__ = "filmComments" id = Column(Integer, primary_key = True) filmId = Column(Integer, index=True) rating = Column(Integer, index=True) text = Column(String) votesUp = Column(Integer) votesDown = Column(Integer) userId = Column(Integer) date = Column(Date)

推荐答案

将其映射到SQLAlchemy应该非常简单。出于明显的原因，我没有考虑别名。

Mapping that to SQLAlchemy should be quite straightforward. I'm not considering the aliases, for obvious reasons.

from sqlalchemy import func megaId, numOfComments = (session.query(Film.id, func.count(FilmComment.id)) .join(FilmComment, Film.id == FilmComment.filmId) .group_by(Film.id).first())

这应该有效。如果将 FilmComment.filmId 声明为外键，则不需要显式的 on 子句。

This should work. The explicit on clause wouldn't be needed if FilmComment.filmId were declared as a foreign key.