打印mysql数据库中同一表中的父层次结构

WITH RECURSIVE cte AS (
  SELECT id, parent_id, name
  FROM category
  WHERE id = <指定节点的id>
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM category c
  JOIN cte ON cte.parent_id = c.id
)
SELECT id, parent_id, name
FROM cte;

class Node:
    def __init__(self, id, parent_id, name):
        self.id = id
        self.parent_id = parent_id
        self.name = name
        self.children = []

def build_tree(nodes):
    node_map = {}
    root = None
    for node in nodes:
        node_map[node.id] = Node(node.id, node.parent_id, node.name)
    for node in nodes:
        if node.parent_id in node_map:
            parent = node_map[node.parent_id]
            child = node_map[node.id]
            parent.children.append(child)
        else:
            root = node_map[node.id]
    return root

def print_hierarchy(node, indent=0):
    print(' ' * indent + f'{node.id} - {node.name}')
    for child in node.children:
        print_hierarchy(child, indent + 2)

# 假设从数据库中获取到了节点数据，存储在nodes列表中
nodes = [
    Node(1, None, 'A'),
    Node(2, 1, 'B'),
    Node(3, 2, 'C'),
    Node(4, 1, 'D'),
    Node(5, 4, 'E')
]

root = build_tree(nodes)
print_hierarchy(root)