Python字典生成JSON时为空或覆盖问题（MySQL数据转换场景）

问题场景 #

从MySQL数据库提取学生测试记录并转换为指定JSON结构时，遇到两个典型问题：

• 使用case_dataset.clear()时，additional_information字段为空
• 移除clear()后，所有测试记录被最后一条数据覆盖

原函数代码：

def detail():
    student = 'John Doe'
    conn = get_db_connection()
    cur = conn.cursor()
    sql = ("""
             select
                a.student_name,
                a.student_id,
                a.student_homeroom_name,
                a.test_id,
                a.datetaken, 
                a.datecertified,
                b.request_number
                FROM student_information a 
                INNER JOIN homeroom b ON a.homeroom_id = b.homeroom_id
                WHERE a.student_name = '""" + student + """'
                ORDER BY datecertified DESC 
             """)
    cur.execute(sql)
    details=cur.fetchall()
    
    dataset = defaultdict(dict)
    case_dataset = defaultdict(dict)
    case_dataset = dict(case_dataset)
    
    for student_name, student_id, student_homeroom_name, test_id, datetaken, datecertified, request_number in details:
        dataset[student_name]['student_id'] = student_id
        dataset[student_name]['student_homeroom_name'] = student_homeroom_name
        
        case_dataset['test_id'] = test_id
        case_dataset['datetaken'] = datetaken
        case_dataset['datecertified'] = datecertified
        case_dataset['request_number'] = request_number

        dataset[student_name]['additional_information'] = case_dataset

        case_dataset.clear()
    
    dataset= dict(dataset)
    print(dataset)

    cur.close()
    conn.close()

错误输出示例 #

1. 使用case_dataset.clear()时的输出：

{
    "John Doe": {
        "student_id": "1234",
        "student_homeroom_name": "HR1",
        "additional_information": []
    }
}

2. 移除case_dataset.clear()时的输出：

{
    "John Doe": {
        "student_id": "1234",
        "student_homeroom_name": "HR1",
        "additional_information": [
            {
                "test_id": "0987",
                "datetaken": "1-1-1970",
                "datecertified": "1-2-1970",
                "request_number": "5643"
            },
            {
                "test_id": "0987",
                "datetaken": "1-1-1970",
                "datecertified": "1-2-1970",
                "request_number": "5643"
            }
        ]
    }
}

期望输出 #

{
    "John Doe": {
        "student_id": "1234",
        "student_homeroom_name": "HR1",
        "additional_information": [
                {
                    "test_id": "0987",
                    "datetaken": "1-1-1970",
                    "datecertified": "1-2-1970",
                    "request_number": "5643"
                },
                {
                    "test_id": "12343",
                    "datetaken": "1-1-1980",
                    "datecertified": "1-2-1980",
                    "request_number": "39807"
                }
        ]
    }
}

问题根源分析 #

1. 字典引用复用：始终使用同一个case_dataset字典，列表中存储的是该字典的引用，最后会被最后一次循环的内容覆盖。
2. clear()副作用：调用clear()会清空字典内容，而dataset中引用的是同一个对象，导致additional_information为空。
3. 数据结构错误：additional_information应为列表类型存储多条记录，但原代码直接赋值字典而非追加到列表。
4. SQL注入风险：字符串拼接构造SQL语句存在安全漏洞。

修正后的代码 #

from collections import defaultdict

def detail():
    student = 'John Doe'
    conn = get_db_connection()
    cur = conn.cursor()
    # 参数化查询避免SQL注入
    sql = """
        select
            a.student_name,
            a.student_id,
            a.student_homeroom_name,
            a.test_id,
            a.datetaken, 
            a.datecertified,
            b.request_number
        FROM student_information a 
        INNER JOIN homeroom b ON a.homeroom_id = b.homeroom_id
        WHERE a.student_name = %s
        ORDER BY datecertified DESC 
    """
    cur.execute(sql, (student,))
    details = cur.fetchall()
    
    # 初始化每个学生的默认结构，确保additional_information是列表
    dataset = defaultdict(lambda: {
        'student_id': '',
        'student_homeroom_name': '',
        'additional_information': []
    })
    
    for student_name, student_id, student_homeroom_name, test_id, datetaken, datecertified, request_number in details:
        # 同学生基础信息仅第一次赋值
        if not dataset[student_name]['student_id']:
            dataset[student_name]['student_id'] = student_id
            dataset[student_name]['student_homeroom_name'] = student_homeroom_name
        
        # 每次循环创建新字典，避免引用复用
        case_record = {
            'test_id': test_id,
            'datetaken': datetaken,
            'datecertified': datecertified,
            'request_number': request_number
        }
        # 将新记录追加到列表
        dataset[student_name]['additional_information'].append(case_record)
    
    dataset = dict(dataset)
    print(dataset)

    cur.close()
    conn.close()

修正点说明 #

• 参数化SQL：用%s占位符替代字符串拼接，杜绝SQL注入风险。
• 默认结构初始化：通过defaultdict确保每个学生的additional_information是列表类型。
• 独立字典创建：每次循环生成新的case_record字典，避免引用复用导致的覆盖问题。
• 基础信息优化：同学生的基础信息仅在第一次循环时赋值，减少冗余操作。

验证结果 #

运行修正后的代码，将生成符合期望的JSON结构：每个学生对应的additional_information列表包含所有独立的测试记录，既不会为空，也不会出现重复覆盖的情况。

内容的提问来源于stack exchange，提问作者Robert Thompson