SQLAlchemy convert SELECT query result to a list of dicts

Question

When I was using session.query, I was able to convert the result to a list of dicts :

my_query = session.query(table1,table2).filter(all_filters)
result_dict = [u.__dict__ for u in my_query.all()]

But now that I have to work with the SELECT() operation, how can I convert the results to a dict that looks like, for every row result :

[{'Row1column1Name' : 'Row1olumn1Value', 'Row1column2Name' :'Row1Column2Value'},{'Row2column1Name' : 'Row2olumn1Value', 'Row2column2Name' : 'Row2Column2Value'},etc....].

This is my SELECT() code :

select = select([table1,table2]).where(all_filters)
res = conn.execute(select)
row = res.fetchone() #I have to use fetchone() because the query returns lots of rows
resultset=[]
while row is not None:
    row = res.fetchone()
    resultset.append(row)

print resultset

The result is :

[('value1', 'value2', 'value3', 'value4'),(.....),etc for each row]

I'm new to Python, any help would be appreciated.

possible duplicate of [SQLAlchemy returns tuple not dictionary](http://stackoverflow.com/questions/2828248/sqlalchemy-returns-tuple-not-dictionary) — fanti, Oct 16 '13 at 23:09

fanti · Answer 1 · 2013-10-16T23:06:01.677

31

This seems to be a RowProxy object. Try:

row = dict(zip(row.keys(), row))

edited Oct 16 '13 at 23:06

answered Oct 16 '13 at 22:42

fanti

1,859
3
16
31

2

thanks! But how can I use this with the `fetchone()`? I actually need to load one row at a time, when I do this : `while row is not None: row = res.fetchone() result = dict(zip(row.keys(), row)) ` It gives me the error : `AttributeError: 'NoneType' object has no attribute 'keys'` – salamey Oct 18 '13 at 09:11
Just use res.keys() since difference between fetchall() and fetchone() is that fetchone() returns first element of list returned by fetchall(). – omikron Jul 22 '15 at 14:45
This does not give the result expected by OP – scientific_explorer Jul 03 '19 at 07:40

score 18 · Answer 2 · answered Nov 19 '13 at 18:16

You can typecast each row from a select result as either a dict or a tuple. What you've been seeing is the default behaviour, which is to represent the each row as a tuple. To typecast to a dict, modify your code like this:

select = select([table1, table2]).where(all_filters)
res = conn.execute(select)
resultset = []
for row in res:
    resultset.append(dict(row))
print resultset

This works nicely if you need to process the result one row at a time.

If you're happy to put all rows into a list in one go, list comprehension is a bit neater:

select = select([table1, table2]).where(all_filters)
res = conn.execute(select)
resultset = [dict(row) for row in res]
print resultset

score 4 · Answer 3 · answered Jul 22 '19 at 16:18

4

Building off of Fanti's answer, if you use a List Comprehension, you can produce the list of dicts all in one row. Here results is the result of my query.

records = [dict(zip(row.keys(), row)) for row in results]

answered Jul 22 '19 at 16:18

Nick Throckmorton

41
2

jackotonye · Answer 4 · 2017-08-24T21:15:28.740

For the first query its better to use this approach for sqlalchemy KeyedTuple:

# Convert an instance of `sqlalchemy.util._collections.KeyedTuple`
# to a dictionary
my_query = session.query(table1,table2).filter(all_filters)
result_dict = map(lambda q: q._asdict(), my_query)

OR

result_dict = map(lambda obj: dict(zip(obj.keys(), obj)), my_query)

For ResultProxy as earlier mentioned:

result_dict = dict(zip(row.keys(), row))

score 1 · Answer 5 · answered Jul 29 '20 at 12:55

Make a function -

def resultToDict(result):
    ds = []
    for rows in result:
        d = {}
        for row in rows:
            for col in row.__table__.columns:
                d[col.name] = str(getattr(row, col.name))
        ds.append(d)
    return ds

Execute like this -

resultToDict(my_query.all())

Result -

[
{'Row1column1Name' : 'Row1column1Value', 'Row1column2Name' : 'Row1Column2Value'},
{'Row2column1Name' : 'Row2column1Value', 'Row2column2Name' : 'Row2Column2Value'},
{'Row3column1Name' : 'Row3column1Value', 'Row3column2Name' : 'Row3Column2Value'},
etc....
]

SQLAlchemy convert SELECT query result to a list of dicts

5 Answers5