Python - Make pymysql return a dictionary key with the value being a list of the results

Question

When using pymysql.cursors.DictCursor, pymysql will return the results as separate dictionaries in a list. For example, if the users table looks like this

userName  firstName
sharko     dan
weirdal    dan

then on SELECT userName FROM users WHERE firstName = 'dan'; DictCursor would return

[{'userName': 'sharko'}, {'userName': 'weirdal'}]

Is there a way to make it return this? {userName:['sharko','weirdal']}

Answer 1

Convert the results into the desired form using a defaultdict of lists:

from collections import defaultdict

results = defaultdict(list)

query = 'SELECT userName FROM users WHERE firstName = %s'
for item in cursor.execute(query, ('dan',)):
    for k in item:
        results[k].append(item[k])

For your example this would result in:

>>> results
defaultdict(<class 'list'>, {'userName': ['sharko', 'weirdal']})

If you want to, you can convert back to standard dict:

>>> results = dict(results)
>>> results
{'userName': ['sharko', 'weirdal']}