CODEWITHSUNDEEP
pythonsqlalchemy
Arti
Arti

Reputation: 7762

Return SQLAlchemy results as dicts instead of lists

When I examine the results of a query, it looks like a list of lists. I want to return a list of dicts mapping column names to result values. How can I convert the result rows to dicts?

results = db.session.query(
    PendingPost.campaign_id.label('campaign_id'),
    Campaign.title.label('title'),
    sqlalchemy.func.count(PendingPost.status).label('status_count'),
).join(
    Campaign, Campaign.id == PendingPost.campaign_id,
).join(
    Areas, Areas.id == PendingPost.area_id
).filter(
    sqlalchemy.func.month(PendingPost.creation_date) == datetime.datetime.utcnow().month
).group_by(
    PendingPost.status,
    PendingPost.campaign_id,
).all()

print(results)
[(3, 'campaign title', 1),
 (4, 'campaign title', 1)]

Upvotes: 28

Views: 43713

Answers (3)

johnny b good
johnny b good

Reputation: 61

As of SQLAlchemy 1.4 and above, the “KeyedTuple” object returned by Query is replaced by Row. See here.

This means that this will no longer work:

[dict(r) for r in row_list]

I was able to get it working in SQLAlchemy 2.0 with this subtle change:

[dict(r._mapping) for r in row_list]

Upvotes: 4

davidism
davidism

Reputation: 127240

The results look like tuples/lists, but they are actually a special Row object (KeyedTuple for SQLAlchemy < 1.4). Use the _asdict() method to convert each row to a dict.

return [r._asdict() for r in results]
[{'campaign_id': 3, 'title': 'campaign title', 'status_count': 1},
 {'campaign_id': 4, 'title': 'campaign title', 'status_count': 1}]

Upvotes: 41

Leon Starr
Leon Starr

Reputation: 522

in Python 3.7 / SQLAlchemy 1.3.18 this works for me:

return [dict(r) for r in results]

Upvotes: 11

Related Questions