Да, вы можете использовать рекурсивный CTE (Common Table Expression) в SQL, чтобы создать иерархическую структуру в виде вложенных словарей. Однако SQL не поддерживает прямое создание вложенных словарей, поэтому нам нужно будет собрать данные в виде строки, а затем преобразовать их в Python. Давайте проиллюстрируем, как это можно сделать.
### SQL Запрос
Мы можем использовать рекурсивный CTE, чтобы получить все уровни дерева, а затем собрать их в формате, подходящем для преобразования в словарь Python.
Вот запрос, который собирает иерархические данные:
```sql
WITH RECURSIVE tree_cte AS (
SELECT
source_id AS parent,
target_id AS child,
1 AS level
FROM tree
UNION ALL
SELECT
c.parent,
t.target_id AS child,
c.level + 1
FROM tree_cte c
JOIN tree t ON c.child = t.source_id
)
SELECT parent, ARRAY_AGG(child) AS children
FROM tree_cte
GROUP BY parent;
```
### Обработка результата в Python
Теперь, когда у нас есть иерархическая структура данных, мы можем обработать ее в Python и преобразовать в желаемый словарь. Вот как это может быть реализовано:
```python
import psycopg2
import json
# Подключение к базе данных
conn = psycopg2.connect("dbname=test user=postgres password=secret")
cur = conn.cursor()
# Выполнение SQL запроса
cur.execute("""
WITH RECURSIVE tree_cte AS (
SELECT
source_id AS parent,
target_id AS child,
1 AS level
FROM tree
UNION ALL
SELECT
c.parent,
t.target_id AS child,
c.level + 1
FROM tree_cte c
JOIN tree t ON c.child = t.source_id
)
SELECT parent, ARRAY_AGG(child) AS children
FROM tree_cte
GROUP BY parent;
""")
# Получение результатов
rows = cur.fetchall()
# Создание словаря из результатов
tree_dict = {}
# Преобразование в виде вложенных словарей
def build_tree(parent, children):
if children is None:
return None
return {child: build_tree(child, child_dict.get(child)) for child in children}
child_dict = {parent: children for parent, children in rows}
for parent in child_dict.keys():
if parent not in tree_dict:
tree_dict[parent] = build_tree(parent, child_dict.get(parent))
# Закрытие соединения с БД
cur.close()
conn.close()
# Печать результата
print(json.dumps(tree_dict, indent=2))
```
Этот код:
1. Выполняет запрос к базе данных для извлечения иерархических данных.
2. Строит вложенные словари, используя рекурсивную функцию.
3. Печатает полученное дерево в формате JSON для удобства.
Выходной результат будет в формате, который вы указали:
```python
{
"10": {
"15": {
"30": None,
"35": {
"40": {
"45": None
}
}
},
"20": None
}
}
```
Этот подход эффективно извлекает и формирует иерархические данные из SQL в вложенные словари Python.