CODEWITHSUNDEEP
pythonsqlalchemy
Cristhian Boujon
Cristhian Boujon

Reputation: 4190

A SELECT with convenient format in SQLAlchemy

I'm new in SqlAlchemy and I'm looking for something like this:

TABLE

CATEGORY VALUE     ORDER
cat_1    val_11       1
cat_1    val_12       2
cat_2    val_21       1
cat_1    val_13       3
cat_3    val_31       1

convenint retrieve result: {"cat_1": (val11, val12, val13), "cat_2": (val_21), "cat_3": (val_31)}

I'm trying avoid write a query with distinct by categories and then iterate over category list querying values by category

Upvotes: 2

Views: 226

Answers (1)

Haleemur Ali
Haleemur Ali

Reputation: 28243

MySql doesn't have an array type (unlike most other RDBMS systems), so it isn't possible to aggregate a column into an array.

The closest thing one can do in mysql is use the group_concat function, which returns a comma separated list (or you can specify a custom separator)

from sqlalchemy import func
q = session.query(
  Table.category, 
  func.group_concat(Table.value).label('group_values')
).group_by(Table.category).all()

And then, iterate over the resultset & split the string into a list in python.

Upvotes: 1

Related Questions