How I can parse response with SQLAlchemy from multiple rows & columns to one field with list of dictionaries?

Question

My current output looks like:

item	attribute_id	attribute_value	attribute_name
A	zone	A	zone_position
A	type	simple	type_item
A	status	active	state

Desired output is:

item	attributes
A	[{"attribute_id": "zone", "attribute_value": "A", "attribute_name": "zone_position"}, {"attribute_id: "type", "attribute_value": "simple", "attribute_name": "type_item"}, {"attribute_id": "status", "attribute_value": "active", "attribute_name": "state}]

If that would be hard to be done from sql alchemy, How can be done from a json format?

Thanks in advance!

Does this answer your question? [How to convert SQLAlchemy row object to a Python dict?](https://stackoverflow.com/questions/1958219/how-to-convert-sqlalchemy-row-object-to-a-python-dict) — snakecharmerb, Dec 13 '22 at 08:30
[This answer](https://stackoverflow.com/a/1958228/5320906) is the current best way to convert rows to dicts. — snakecharmerb, Dec 13 '22 at 08:31
I feel like I am close, but still can't figure out how i can get the desired structure of my data — Cristi Chiticariu, Dec 13 '22 at 09:10
I think i would do it after the sqlalchemy result. Like if i would have the dictionary mapping ("item":"A", "attribute_id":"zone", "attribute_value":"A", "attribute_name":"zone_pozition"}, I would want to turn it into {"item" : "A", attributes: [{"attribute_id": "zone", "attribute_value": "A", "attribute_name": "zone_position"}]. — Cristi Chiticariu, Dec 13 '22 at 09:44
@snakecharmerb used that ``` for row in result: row_as_dict = dict(row) row_as_dict["attributes"] = [{key: value for key, value in row_as_dict.items() if key in ["attribute_id", "attribute_value", "attribute_name"]}] print(row_as_dict) ``` and helped me a bit — Cristi Chiticariu, Dec 13 '22 at 09:50

score 1 · Accepted Answer · answered Dec 13 '22 at 10:49

Something like this should work. We can use itertools.groupby to group results by the value of item, and tools from the operator module to abstract finding and extracting values.

import itertools
import operator
import pprint

import sqlalchemy as sa
from sqlalchemy import orm

Base = orm.declarative_base()

# Assuming this model strcuture
class MyModel(Base):
    __tablename__ = 't74781694'

    id = sa.Column(sa.Integer, primary_key=True)
    item = sa.Column(sa.String)
    attribute_id = sa.Column(sa.String)
    attribute_value = sa.Column(sa.String)
    attribute_name = sa.Column(sa.String)


engine = sa.create_engine('sqlite://', echo=True, future=True)
Base.metadata.create_all(engine)
Session = orm.sessionmaker(engine, future=True)

# The attributes that we want to group.
keys = ['attribute_id', 'attribute_value', 'attribute_name']

# ORM solution (using model entities).
attrgetter = operator.attrgetter(*keys)

with Session() as s:
    instances = s.scalars(sa.select(MyModel))
    data = [
        (k, [dict.fromkeys(keys, attrgetter(g)) for g in grouped])
        for k, grouped in itertools.groupby(instances, key=lambda m: m.item)
    ]
    pprint.pprint(data)

# Hybrid core/ORM solution (Using session and table).
# Pure core would entail using engine instead of session 
keygetter = operator.itemgetter('item')
itemgetter = operator.itemgetter(*keys)
with Session() as s:
    tbl = MyModel.__table__
    rows = s.execute(sa.select(tbl)).mappings()
    data = [
        (k, [dict.fromkeys(keys, itemgetter(g)) for g in grouped])
        for k, grouped in itertools.groupby(rows, key=keygetter)
    ]
    pprint.pprint(data)

How I can parse response with SQLAlchemy from multiple rows & columns to one field with list of dictionaries?

1 Answers1