如何从SQL查询的结果创建一组值？

Question

我正在尝试用python/flask创建一个旅程预订系统，并尝试创建一个起始目的地和它们的结束目的地的dict，以允许我运行一些ajax代码，这些代码限制了基于起始位置选择目的地的选项，如下所示（这只是我试图创建的示例，而不是我想要的值）：

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

我有一个SQL语句：

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"

返回下面的

[('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)]

我创建了一个dict，其中包含唯一的出发城市名称，如下所示：

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

其中打印：

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

我有没有办法用SQL语句中包含的相应到达位置填充上述dict

谢谢

Answer 1

您可以使用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()}

输出：

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

编辑：在最终结果中包含出发和到达时间：

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()}

输出：

{'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)}]}

您还可以将到达地点本身形成字典，而不是列表：

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

输出：

{'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)}}}