python nested dictionaries from sql queries

Hello i am trying to create a dictionary that would like this

{104: {'tid': 1234, 'date': '08/26/2022', 'total': '95.96'}, {'tid': 1235, 'date': '09/25/2022', 'total': '95.96'}, {'tid': 1236, 'date': '07/27/2022', 'total': '95.96'}}
{105: {'tid': 1237, 'date': '08/26/2022', 'total': '85.96'}, {'tid': 1238, 'date': '09/25/2022', 'total': '85.96'}, {'tid': 1238, 'date': '07/27/2022', 'total': '85.96'}}

I have the following in a db which i am querying

CID || TID || DATE || TOTAL
104    1234   08/26/2022  95.96
104    1235   09/25/2022  95.96
104    1236   07/27/2022  95.96
105    1237   08/26/2022  85.96
105    1238   09/25/2022  85.96
105    1239   07/27/2022  85.96

i have the following code in order to loop through the query results and create a dictionary but its not creating it like the i am wanting it o

outer_dict = {}
records = cursor.fetchall()
for i in records:
    inner_dict = {"tid":i[1], "date": i[2], "total": i[3]}
    outer_dict[i[0]] = inner_dict

Any help would be very much appreciated.

Answer

I can only imagine that what you really wanted is something like this:

{   
    104: [
        {'tid': 1234, 'date': '08/26/2022', 'total': '95.96'}, 
        {'tid': 1235, 'date': '09/25/2022', 'total': '95.96'}, 
        {'tid': 1236, 'date': '07/27/2022', 'total': '95.96'}
    ],
    105: [
        {'tid': 1237, 'date': '08/26/2022', 'total': '85.96'}, 
        {'tid': 1238, 'date': '09/25/2022', 'total': '85.96'}, 
        {'tid': 1238, 'date': '07/27/2022', 'total': '85.96'}
    ]
}

And this is how to get that:

outer_dict = {}
records = cursor.fetchall()
for i in records:
    inner_dict = {"tid": i[1], "date": i[2], "total": i[3]}
    outer_dict[i[0]] = outer_dict.get(i[0], []) + [inner_dict]

The final line is the only difference. .get() gets the value for the given dictionary key, which in this case is either the list that was already there, or the default value when the key doesn’t exist, which is a new and empty list in this case. It then appends the new inner_dict to the list and sets the dictionary value for that key to the resulting list.

By the way: looping over records directly with for i in records: is the right way to go, but you probably shouldn’t name the loop variable i here, since i is typically used for an indexing loop variable. Something like row or rec seems more appropriate here, and less likely to cause confusion with readers (including future you).

Edit: you correctly pointed out that using .append() causes an error, since it doesn’t return the list itself. The updated answer works as advertised.