How to get (form) a hierarchical directory from the database?

Question

Python

In the database there is a table of addresses City-Street-House.
You need to get a hierarchy from this and get json like this:

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

I'm interested in the concept as a whole, starting with the database structure and ending with the approach to query formation.
If there is an example for SQLAlchemy, it's great, but I'll also read SQL.
I'm already starting to think about writing a recursion, but somehow I'm not sure about this solution, coupled with SQLAlchemy.

Answer 1

I think the structure of the database should not be any special for solving this problem. We just create a table, fill it with data (examples in MS SQL, but I think the essence will be clear):

Using Python, connecting to the database is not very difficult, here is a link to the article, it explains quite clearly - https://habr.com/ en/post/321510/
After getting the data from the database, it will look something like this:

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

After that, we add data from the database to the dictionary, from which we then form json using a module with the same name (json).
Here is the script that I used (the data is slightly different than from the screenshot, but the essence does not change):

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

The script is far from perfect, I think if you read more about defaultdict, it will be easier to optimize it.
The output is this json (slightly different from the required one, but you can play around with the module to get the desired result):

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