convert sqlalchemy query result to a list of dicts

Question

I want to have the result of my query converted to a list of dicts like this :

result_dict = [{'category': 'failure', 'week': '1209', 'stat': 'tdc_ok', 'severityDue': '2_critic'}, {'category': 'failure', 'week': '1210', 'stat': 'tdc_nok', 'severityDue': '2_critic'}]

But instead I get it as a dict, thus with repeated keys:

result_dict = {'category': 'failure', 'week': '1209', 'stat': 'tdc_ok', 'severityDue': '2_critic', 'category': 'failure', 'week': '1210', 'stat': 'tdc_nok', 'severityDue': '2_critic'}

I get this result by doing this :

for u in my_query.all():
     result_dict = u.__dict__

How can I convert sqlAlchemy query result to a list of dicts (each row would be a dict) ?

Help please

twil · Accepted Answer · 2021-03-15T09:47:22.767

23

Try

result_dict = [u.__dict__ for u in my_query.all()]

Besides what is the type of your result_dict before the for loop? Its behavior is rather strange.

edited Mar 15 '21 at 09:47

answered Jul 18 '13 at 08:46

twil

6,032
1
30
28

score 8 · Answer 2 · answered Aug 30 '17 at 10:09

8

This works now

result_dict = [u._asdict() for u in my_query.all()]

The reason is that u is not actually a tuple but a KeyedTuple.

The correct answer on this thread also would be helpful

answered Aug 30 '17 at 10:09

Abhishek J

2,386
2
21
22

[row.__dict__ for row in User.query.all()] – Amin Golmahalleh Apr 12 '20 at 14:11

score 3 · Answer 3 · edited May 11 '15 at 14:18

3

There's no .all()

You can try:

result_dict = [u.__dict__ for u in my_query.fetchall()]

edited May 11 '15 at 14:18

R3tep

12,512
10
48
75

answered May 11 '15 at 13:48

BenjiMan

67
2
4

4

it depends on if you are using the ORM or not (the OP did not specify). `.all()` is a `fetchall()` proxy for ORM queries. – cowbert Aug 19 '17 at 02:01

score 1 · Answer 4 · answered Jun 15 '20 at 18:52

1

Not sure if this is because a few years have passed since the original post, but the following works for me (the others did not)...

result_dict = [dict(u) for u in my_query.fetchall()]

I am working in core, not ORM.

answered Jun 15 '20 at 18:52

Ben

4,798
3
21
35

convert sqlalchemy query result to a list of dicts

4 Answers4

Linked