Как получить (сформировать) из БД иерархический справочник?

Question

[REDkiy]

В бд есть табличка адресов Город-Улица-Дом.
Нужно добыть из этого иерархию и получить json такого вида:

[
  {
    'city': 'Moscow',
    'streets': [
      {
        'street': 'Lomonosova',
        'buildings': [
          '1',
          '2',
          '3'
        ]
      }
    ]
  }
]

Интересует в целом концепция, начиная со структуры БД и заканчивая подходом к формированию запроса.
Если будет пример для SQLAlchemy отлично, но и SQL почитаю.
Я уже начинаю подумывать написать рекурсию, но как-то не уверен в этом решении вкупе с SQLAlchemy.

Answer 1

[Илья]

Думаю, структура базы не должна быть какой-то особенной для решение данной задачи. Просто создаем таблицу, заливаем в нее данные (примеры на MS SQL, но думаю, суть будет ясна):


С помощью Python подключиться к базе не очень сложно, вот ссылка на статью, там довольно доступно объясняется - https://habr.com/ru/post/321510/

После получения данных из базы, они будут выглядеть примерно так:

result = [('Moscow', 'Ivanova', '10',) , ('Moscow', 'Ivanova', '13a',) , ('Moscow', 'Ivanova', '2') ......]

После чего добавляем данные из базы в словарь, из которого потом формируем json с помощью модуля с таким же названием(json).
Вот скрипт, которым я пользовался (данные немного другие, чем со скриншота, но суть не меняется):

import json
import collections as col


rows_from_DB = [('Moscow','Lomonosova','14',), ('Moscow','Lomonosova','15a',), ('Omsk','Ivanova','4',), ('Moscow','Kotlyara','7',), ('Sochi','Zhukova','11b',)]

cities = col.defaultdict()


for row in rows_from_DB:
    
    if row[0] not in cities:
        cities[row[0]] = col.defaultdict()
        
        
    if row[1] not in cities[row[0]]:
        cities[row[0]][row[1]] = []
       
        
    cities[row[0]][row[1]].append(row[2])
    
    
print(json.dumps(cities))

Скрипт далеко не идеальный, думаю, если почитать больше про defaultdict, будет проще его оптимизировать.

На выходе вот такой json (немного отличается от требуемого, но можно поиграться с модулем, для нужного результата):

{
	"Omsk": {
				"Ivanova": ["4"]
			}, 
	"Moscow": {
				"Kotlyara": ["7"], 
				"Lomonosova": ["14", "15a"]
			  }, 
	"Sochi": {
				"Zhukova": ["11"]
			 }
}