DBAPI如何用SQL将多表关联查询出树状结构数据（嵌套JSON格式）

在Python中，使用DBAPI进行多表联合查询并以嵌套JSON格式输出树状数据，可以通过编写SQL查询语句来实现，然后使用Python代码处理查询结果并构建嵌套JSON。以下是一个简化的例子：

首先，假设有两个表：categories 和 subcategories，它们通过 parent_id 字段相互关联。

-- 示例SQL查询，用于获取树状结构数据
SELECT 
    c1.id,
    c1.name,
    c2.id AS sub_id,
    c2.name AS sub_name,
    c2.parent_id
FROM 
    categories c1
LEFT JOIN 
    subcategories c2 ON c1.id = c2.parent_id;

接下来，使用Python代码处理查询结果：

import json
import pymysql  # 假设使用pymysql作为DBAPI
 
# 连接数据库
connection = pymysql.connect(host='localhost', user='user', password='pass', db='dbname')
 
try:
    with connection.cursor() as cursor:
        # 执行SQL查询
        cursor.execute("SELECT c1.id, c1.name, c2.id AS sub_id, c2.name AS sub_name, c2.parent_id FROM categories c1 LEFT JOIN subcategories c2 ON c1.id = c2.parent_id")
        
        # 获取所有结果
        rows = cursor.fetchall()
        
        # 使用字典存储树状结构
        tree = {}
        for row in rows:
            parent_id, parent_name, sub_id, sub_name, sub_parent_id = row
            
            # 如果父节点不在树中，则添加
            if parent_id not in tree:
                tree[parent_id] = {
                    'id': parent_id,
                    'name': parent_name,
                    'children': []
                }
            
            # 如果子节点不是根节点（parent_id为None），则添加到父节点的children列表中
            if sub_parent_id is None:
                tree[parent_id]['children'].append({
                    'id': sub_id,
                    'name': sub_name,
                    'children': []
                })
        
        # 将树状结构转化为嵌套JSON字符串
        nested_json = json.dumps(tree.values(), indent=2)
        print(nested_json)
 
finally:
    connection.close()

这段代码首先执行SQL查询，然后遍历查询结果，根据父子关系构建树状结构。最后，使用json.dumps()将树状结构转换为嵌套JSON格式的字符串。注意，这个例子假设每个父节点只有一个子节点，如果一个父节点有多个子节点，需要进一步处理以保持正确的嵌套结构。