从mysql数据库获取树数据

import mysql.connector

# 建立与MySQL数据库的连接
cnx = mysql.connector.connect(user='username', password='password', host='localhost', database='database_name')

cursor = cnx.cursor()

# 递归查询获取树数据
query = "WITH RECURSIVE tree AS ( " \
        "SELECT id, name, parent_id FROM tree_table WHERE parent_id IS NULL " \
        "UNION ALL " \
        "SELECT t.id, t.name, t.parent_id FROM tree_table AS t " \
        "INNER JOIN tree ON t.parent_id = tree.id " \
        ") SELECT * FROM tree"
        
cursor.execute(query)
tree_data = cursor.fetchall()

tree = {}

# 处理查询结果
for row in tree_data:
    node_id, node_name, parent_id = row
    if parent_id not in tree:
        tree[parent_id] = {'children': []}
    tree[parent_id]['children'].append({'id': node_id, 'name': node_name})

# 打印树数据
print(tree)