Convert sqlalchemy output into dictionary of list in python

Question

I have output of sqlalchemy in tuple format. Please see below output

with SQLAlchemyDBConnection() as db_conn:
    response = db_conn.session.query(GroceryTbl.category,GroceryTbl.name).all()
for response in responses:
    print(response)

('Fruit', 'Mango')
('Fruit', 'Banana')
('Fruit', 'Apple')
('Fruit', 'Grapes')
('Vegetable', 'Potato')
('Vegetable', 'Tomato')
('Vegetable', 'Carrot')

But I want the output in below format using pandas.

output = {'Fruit':['Mango','Banana','Apple','Grapes'],'Vegetable':['Potato','Tomato','Carrot']}

Answer 1

Please try using the below code.

import pandas as pd
output = pd.DataFrame(responses).groupby('category')['name'].apply(list).to_dict()

Answer 2

Another approach:

from collections import defaultdict

output = defaultdict(list)
for response in responses:
    output[response[0]].append(response[1])
print(output)

Basically you create a dictionary with keys as the first element of your tuple and list value and add elements to it.