How do I create a dict of values from the results of an SQL query?

I’m trying to create a journey booking system in python/flask and am trying to create a dict of starting destinations and their ending destinations to allow me to run some ajax code which restricts the options of selecting destination based on the starting location like this (this is just an example of what I’m trying to create, not exactly the values I want):

routes = {
            'Newcastle' : ['Bristol'],
            'Bristol' : ['Newcastle', 'New York', 'Manchester', 'Glasgow', 'Mainhead'],
            'Cardiff' : ['Edinburgh'],
            'Manchester' : ['Bristol', 'Birmingham', 'Glasgow', 'Southampton'],
            'London' : ['Manchester'],
            'Birmingham' : ['Newcastle'],
            'Edinburgh' : ['Cardiff']
        }

I’ve got an SQL statement:

SELECT_routes_statement = "SELECT c.city_name, r.departure_time, city.city_name, r.arrival_time, r.price, r.available_seats 
                               FROM routes r 
                               JOIN cities c ON r.departing_city_id=c.city_id 
                               JOIN cities city on r.arrival_city_id=city.city_id"

Which returns the following

[('Newcastle', datetime.timedelta(seconds=60300), 'Bristol', datetime.timedelta(seconds=82800), 140.0, 200), ('Bristol', datetime.timedelta(seconds=28800), 'Newcastle', datetime.timedelta(seconds=51300), 140.0, 200), ('Bristol', datetime.timedelta(seconds=41400), 'Manchester', datetime.timedelta(seconds=59400), 100.0, 200), ('Manchester', datetime.timedelta(seconds=44400), 'Bristol', datetime.timedelta(seconds=62400), 100.0, 200), ('Bristol', datetime.timedelta(seconds=27600), 'London', datetime.timedelta(seconds=39600), 100.0, 200), ('London', datetime.timedelta(seconds=39600), 'Manchester', datetime.timedelta(seconds=63600), 130.0, 200), ('Manchester', datetime.timedelta(seconds=44400), 'Glasgow', datetime.timedelta(seconds=65400), 130.0, 200), ('Bristol', datetime.timedelta(seconds=27600), 'Glasgow', datetime.timedelta(seconds=47100), 160.0, 200), ('Glasgow', datetime.timedelta(seconds=52200), 'Newcastle', datetime.timedelta(seconds=74700), 130.0, 200), ('Newcastle', datetime.timedelta(seconds=58500), 'Manchester', datetime.timedelta(seconds=75000), 130.0, 200), ('Manchester', datetime.timedelta(seconds=66300), 'Bristol', datetime.timedelta(seconds=84300), 100.0, 200), ('Bristol', datetime.timedelta(seconds=22800), 'Manchester', datetime.timedelta(seconds=40800), 100.0, 200), ('Southampton', datetime.timedelta(seconds=43200), 'Manchester', datetime.timedelta(seconds=70200), 100.0, 200), ('Manchester', datetime.timedelta(seconds=68400), 'Southampton', datetime.timedelta(seconds=9000), 100.0, 200), ('Birmingham', datetime.timedelta(seconds=57600), 'Newcastle', datetime.timedelta(seconds=84600), 130.0, 200), ('Newcastle', datetime.timedelta(seconds=21600), 'Birmingham', datetime.timedelta(seconds=48600), 130.0, 200), ('Aberdeen', datetime.timedelta(seconds=25200), 'Portsmouth', datetime.timedelta(seconds=61200), 130.0, 200)]

I’ve created a dict that contains unique departing city names as follows:

for row in results:
    tempDict.append(row[0])
    departure_cities = dict.fromkeys(tempDict,)
print(departure_cities)

Which prints:

{'Newcastle': None, 'Bristol': None, 'Manchester': None, 'London': None, 'Glasgow': None, 'Southampton': None, 'Birmingham': None, 'Aberdeen': None}

Is there a way for me to populate the above dict with the corresponding arrival locations contained within the SQL statement?

Thanks.

Answer

You can use collections.defaultdict:

from collections import defaultdict
d = defaultdict(set)
for a, _, b, *_ in results:
   d[a].add(b)

new_result = {a:list(b) for a, b in d.items()}

Output:

{'Newcastle': ['Birmingham', 'Bristol', 'Manchester'], 'Bristol': ['London', 'Manchester', 'Newcastle', 'Glasgow'], 'Manchester': ['Southampton', 'Bristol', 'Glasgow'], 'London': ['Manchester'], 'Glasgow': ['Newcastle'], 'Southampton': ['Manchester'], 'Birmingham': ['Newcastle'], 'Aberdeen': ['Portsmouth']}

Edit: inclusion of departure and arrival times in final result:

d = defaultdict(set)
for a, _d, b, arr, *_ in results:
   d[a].add((b, _d, arr))

result = {a:[dict(zip(['destination', 'departure', 'arrival'], i)) for i in b] for a, b in d.items()}

Output:

{'Newcastle': [{'destination': 'Manchester', 'departure': datetime.timedelta(seconds=58500), 'arrival': datetime.timedelta(seconds=75000)}, {'destination': 'Birmingham', 'departure': datetime.timedelta(seconds=21600), 'arrival': datetime.timedelta(seconds=48600)}, {'destination': 'Bristol', 'departure': datetime.timedelta(seconds=60300), 'arrival': datetime.timedelta(seconds=82800)}], 'Bristol': [{'destination': 'Glasgow', 'departure': datetime.timedelta(seconds=27600), 'arrival': datetime.timedelta(seconds=47100)}, {'destination': 'London', 'departure': datetime.timedelta(seconds=27600), 'arrival': datetime.timedelta(seconds=39600)}, {'destination': 'Newcastle', 'departure': datetime.timedelta(seconds=28800), 'arrival': datetime.timedelta(seconds=51300)}, {'destination': 'Manchester', 'departure': datetime.timedelta(seconds=22800), 'arrival': datetime.timedelta(seconds=40800)}, {'destination': 'Manchester', 'departure': datetime.timedelta(seconds=41400), 'arrival': datetime.timedelta(seconds=59400)}], 'Manchester': [{'destination': 'Bristol', 'departure': datetime.timedelta(seconds=44400), 'arrival': datetime.timedelta(seconds=62400)}, {'destination': 'Glasgow', 'departure': datetime.timedelta(seconds=44400), 'arrival': datetime.timedelta(seconds=65400)}, {'destination': 'Southampton', 'departure': datetime.timedelta(seconds=68400), 'arrival': datetime.timedelta(seconds=9000)}, {'destination': 'Bristol', 'departure': datetime.timedelta(seconds=66300), 'arrival': datetime.timedelta(seconds=84300)}], 'London': [{'destination': 'Manchester', 'departure': datetime.timedelta(seconds=39600), 'arrival': datetime.timedelta(seconds=63600)}], 'Glasgow': [{'destination': 'Newcastle', 'departure': datetime.timedelta(seconds=52200), 'arrival': datetime.timedelta(seconds=74700)}], 'Southampton': [{'destination': 'Manchester', 'departure': datetime.timedelta(seconds=43200), 'arrival': datetime.timedelta(seconds=70200)}], 'Birmingham': [{'destination': 'Newcastle', 'departure': datetime.timedelta(seconds=57600), 'arrival': datetime.timedelta(seconds=84600)}], 'Aberdeen': [{'destination': 'Portsmouth', 'departure': datetime.timedelta(seconds=25200), 'arrival': datetime.timedelta(seconds=61200)}]}

You can also form the arrival locations themselves as dictionaries, instead of a list:

result = {a:{j:{'departure':k, 'arrival':l} for j, k, l in b} for a, b in d.items()}

Output:

{'Newcastle': {'Manchester': {'departure': datetime.timedelta(seconds=58500), 'arrival': datetime.timedelta(seconds=75000)}, 'Birmingham': {'departure': datetime.timedelta(seconds=21600), 'arrival': datetime.timedelta(seconds=48600)}, 'Bristol': {'departure': datetime.timedelta(seconds=60300), 'arrival': datetime.timedelta(seconds=82800)}}, 'Bristol': {'Glasgow': {'departure': datetime.timedelta(seconds=27600), 'arrival': datetime.timedelta(seconds=47100)}, 'London': {'departure': datetime.timedelta(seconds=27600), 'arrival': datetime.timedelta(seconds=39600)}, 'Newcastle': {'departure': datetime.timedelta(seconds=28800), 'arrival': datetime.timedelta(seconds=51300)}, 'Manchester': {'departure': datetime.timedelta(seconds=41400), 'arrival': datetime.timedelta(seconds=59400)}}, 'Manchester': {'Bristol': {'departure': datetime.timedelta(seconds=66300), 'arrival': datetime.timedelta(seconds=84300)}, 'Glasgow': {'departure': datetime.timedelta(seconds=44400), 'arrival': datetime.timedelta(seconds=65400)}, 'Southampton': {'departure': datetime.timedelta(seconds=68400), 'arrival': datetime.timedelta(seconds=9000)}}, 'London': {'Manchester': {'departure': datetime.timedelta(seconds=39600), 'arrival': datetime.timedelta(seconds=63600)}}, 'Glasgow': {'Newcastle': {'departure': datetime.timedelta(seconds=52200), 'arrival': datetime.timedelta(seconds=74700)}}, 'Southampton': {'Manchester': {'departure': datetime.timedelta(seconds=43200), 'arrival': datetime.timedelta(seconds=70200)}}, 'Birmingham': {'Newcastle': {'departure': datetime.timedelta(seconds=57600), 'arrival': datetime.timedelta(seconds=84600)}}, 'Aberdeen': {'Portsmouth': {'departure': datetime.timedelta(seconds=25200), 'arrival': datetime.timedelta(seconds=61200)}}}