Bottle框架下如何查询SQLite外键数据返回嵌套JSON

完全可以实现，不需要依赖数据库特殊语法，两种常用方案都可以快速达成需求。

方案1：保留联表查询，后置重组结构 #

这个方案改动量最小，不需要调整原有查询逻辑，只需要在拿到扁平查询结果后做字段拆分归集即可：
首先先明确两张表的字段归属：

• patient表字段：id、name、cpr、adress、access
• journal表字段：description、date_、given_medicine、patient_cpr

修改后的接口代码如下：

@get('/provider/pharmacy/cpr/<cpr>/token/<token>/JSON')
def _(token, cpr):
    try:
        conn = sqlite3.connect('Mandatory.db')
        if token not in users:
            raise Exception('Token is invalid')

        response.content_type = 'application/json'
        c = conn.cursor()
        c.row_factory = row_to_dict
        # 修复原代码SQL注入风险，改用参数化查询
        c.execute("SELECT * FROM patient JOIN journal ON patient.cpr = journal.patientcpr WHERE patient.cpr=?", (cpr,))
        raw_result = c.fetchall()
        
        # 重组嵌套结构
        patient_map = {}
        final_result = []
        journal_fields = {"description", "date_", "given_medicine", "patient_cpr"}
        
        for row in raw_result:
            cpr_val = row["cpr"]
            # 患者信息未初始化时先提取患者公共字段
            if cpr_val not in patient_map:
                patient_data = {}
                journals = []
                for k, v in row.items():
                    if k not in journal_fields:
                        patient_data[k] = v
                patient_data["journals"] = journals
                patient_map[cpr_val] = patient_data
                final_result.append(patient_data)
            # 提取当前行的就诊记录
            journal_data = {k:v for k,v in row.items() if k in journal_fields}
            patient_map[cpr_val]["journals"].append(journal_data)
        
        # 如果需要单条journal返回对象而非数组，打开下方注释即可匹配你给出的目标格式
        # for p in final_result:
        #     if len(p["journals"]) == 1:
        #         p["journals"] = p["journals"][0]

        return json.dumps(final_result)

    except Exception as ex:
        response.status = 400
        return str(ex)

该方案天然支持一个患者对应多条就诊记录的场景，只需要去掉单条记录转对象的逻辑，journals就会以数组形式返回所有关联记录，扩展性更好。

方案2：分两次查询，逻辑更直观 #

如果觉得后置重组逻辑繁琐，也可以拆分查询步骤，先查患者信息再查关联就诊记录，代码可读性更高：

@get('/provider/pharmacy/cpr/<cpr>/token/<token>/JSON')
def _(token, cpr):
    try:
        conn = sqlite3.connect('Mandatory.db')
        if token not in users:
            raise Exception('Token is invalid')

        response.content_type = 'application/json'
        c = conn.cursor()
        c.row_factory = row_to_dict
        
        # 查询患者基础信息
        c.execute("SELECT * FROM patient WHERE cpr=?", (cpr,))
        patient = c.fetchone()
        if not patient:
            return json.dumps([])
        
        # 查询该患者所有关联就诊记录
        c.execute("SELECT description, date_, given_medicine, patient_cpr FROM journal WHERE patient_cpr=?", (cpr,))
        journals = c.fetchall()
        
        # 组装嵌套结构
        patient["journals"] = journals[0] if len(journals) == 1 else journals
        return json.dumps([patient])

    except Exception as ex:
        response.status = 400
        return str(ex)

注意事项 #

• 原代码使用f-string直接拼接用户传入参数到SQL语句，存在严重SQL注入风险，上述示例全部改用sqlite3原生参数化查询，避免恶意参数攻击。
• 从业务逻辑看，一个患者通常会对应多条就诊记录，建议journals字段保留数组格式，后续新增就诊记录时不需要调整接口返回结构。

内容的提问来源于stack exchange，提问作者Nadia Hansen