How to Convert SQL Query Results into a Python Dictionary

Python: Convert SELECT query list to dict

Use GROUP_CONCAT in the query to get all the codes for a city together as a comma-delimited string, then split it up in Python to create a list.

 cur.execute("SELECT name, ARRAY_AGG(codcity) FROM cities GROUP BY name")
 rows = cur.fetchall()
 cities = {}
 for row in rows:
    cities[row[0]] = [int(code) for code in row[1].split(',')]

This will put the codes in a list, not a dictionary, which is probably better than making up dictionary keys like id-1, id-2, etc., e.g.

'New York': [132, 24]

Populate a dictionary with the result of a query

Using list comprehension:

things = [{'thing_id': row[0], 'thing_name': row[1]} for row in cursor.fetchall()]

or using list comprehension with zip:

things = [dict(zip(['thing_id', 'thing_name'], row)) for row in cursor.fetchall()]

If you use Cursor.description attribute, you can get column names:

names = [d.name for d in c.description]
things = [dict(zip(names, row)) for row in cursor.fetchall()]

How to store the sql query result into a dictionary object

d = {}
for x in result:
    d.setdefault(x[0], [] ).append(x[2])
for x in d:
    while len(d[x]) < 7:
        d[x].append(0)

convert sqlalchemy query result to a list of dicts

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.

---

Related Topics