通过选择"SqlAlchemy by column value"创建字典组

Question

我需要从表中选择所有行，并按列my_group分组组织。

例如：

我的型号是

class MyTable:
id = Column(Integer, primary_key=True)
label = Column(Unicode(255))
my_group = Column(Unicode(50), nullable=False)

我的桌子是

id | label | my_group |
1 | lbl1  | groupA   |
2 | lbl2  | groupB   |  
3 | lbl3  | groupA   |
4 | lbl4  | groupC   |
5 | lbl5  | groupC   |

我想要这样的结果

[
{
"groupA": [
{
"label": "lbl1", 
"id": 1
},
{
"label": "lbl3", 
"id": 3
}
]
},
{
"groupB": [
{
"label": "lbl2", 
"id": 2
}
]
},
{
"groupC": [
{
"label": "lbl4", 
"id": 4
},
{
"label": "lbl5", 
"id": 5
}
]
}
]

只有使用SqlAlchemy才能实现这一点吗？

谢谢！

Answer 1

如果您使用的是足够新的MySQL 5.7.22及更高版本，则可以使用JSON函数来生成所需的结果：

from sqlalchemy.dialects.mysql import JSON
query = session.query(
func.json_object(
MyTable.my_group, func.json_arrayagg(
func.json_object(
'label', MyTable.label,
'id', MyTable.id)),
type_=JSON)).
group_by(MyTable.my_group)
result = [r for r, in query]

如果你真的需要一个分组字典，你可以包装分组查询：

sq = session.query(
MyTable.my_group,
func.json_arrayagg(
func.json_object(
'label', MyTable.label,
'id', MyTable.id)).label('arr')).
group_by(MyTable.my_group).
subquery()
result = session.query(
func.json_objectagg(
sq.c.my_group, sq.c.arr,
type_=JSON)).
scalar()