带有完整子对象的 SQLAlchemy 分组依据 [英] SQLAlchemy Group By With Full Child Objects
问题描述
想象下面的媒体表:
| site | show_id | time |
| ---------------------|-------|
| CNN | 1 | 'a' |
| ABC | 2 | 'b' |
| ABC | 5 | 'c' |
| CNN | 3 | 'd' |
| NBC | 4 | 'e' |
| NBC | 5 | 'f' |
--------------------------------
我想遍历按 show_id 分组的查询结果并尝试过此查询:
I would like to iterate over query results grouped by show_id and have tried this query:
listings = session.query(Media).filter(Media.site == "CNN").group_by(Media.show_id).all()
以下是我想对结果进行迭代的方式:
Here's how I would like to iterate over the results:
for showtimes in listings:
for show in showtimes:
print(show.time)
但是那个查询并没有给我所有分组的子对象.我错过了什么?
But that query doesn't give me all of the grouped child objects. What am I missing?
推荐答案
在 SQL 中,GROUP BY 子句根据分组表达式将分组的行压缩为一行.我的猜测是您使用的是 SQLite 或较旧版本的 MySQL,因为您可以选择非聚合而不是 功能上依赖 分组表达式.在这种情况下,结果包含来自每组未指定行的值,这很少(如果有的话)有用.
In SQL the GROUP BY clause condenses the grouped rows into a single row, based on the grouping expressions. My guess is that you are using SQLite, or an older version of MySQL, since you are allowed to select non-aggregates without them being functionally dependent on the grouping expressions. The results contain values from an unspecified row per group in that case, which is seldom — if ever — useful.
解决方案是在 SQL 中使用 ORDER BY 而不是 GROUP BY,然后 Python中的分组基于订单表达式:
A solution is to ORDER BY instead of GROUP BY in SQL and then group in Python based on the order expressions:
from itertools import groupby
from operator import attrgetter
listings = session.query(Media).\
filter(Media.site == "CNN").\
order_by(Media.show_id).\
all()
# Materialize the subiterators to lists
listings = [list(g) for k, g in groupby(listings, attrgetter('show_id'))]
这篇关于带有完整子对象的 SQLAlchemy 分组依据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!