与SqlAlchemy的内部联接 [英] An inner join with 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.
这篇关于与SqlAlchemy的内部联接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文