Python字典在添加列表时显示空值

Question

我正试图为给定实体生成JSON格式，我遇到了一个问题，让字典不覆盖自身或变为空。这是从MySQL数据库中的表中提取行，并尝试从查询中生成JSON结果。

下面是我的函数:

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()

我试了几种不同的方法，但似乎都不起作用。我得到的是additonal_information键中没有任何内容。我得到的是:

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

我期待的是类似于下面的JSON。然而，我甚至不知道这是否正确。每个学生将有一个或多个test_id，我将需要在我的应用程序中遍历它们。

{
"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"
}
]
}
}

从函数中删除clear()产生如下JSON:

{
"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"
}
]
}
}

Answer 1

列表是可变对象。这意味着list's是通过引用传递的。

设置

dataset[student]['additional_information'] = case_dataset
case_dataset.clear()

你在设置列表，然后清除它。因此，additional_information中的列表也被清除。

设置时复制列表:

dataset[student]['additional_information'] = case_dataset[:]
case_dataset.clear()

Answer 2

感谢大家的指导，给我指明了正确的方向。

我现在有我要找的东西了。根据一些注释和故障排除，我更新了我的代码。以下是我所做的:

1. 我添加回additional_dataset作为列表
2. 删除case_dataset = defaultdict(dict)和case_dataset = dict(case_dataset)，用case_dataset = {}代替
3. 用dataset[student_name]['additional_information'] = additional_dataset更新dataset[student_name]['additional_information'] = case_dataset
4. case_dataset.clear()替换为case_dataset = {}

现在是我的新代码

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 = {} #2 - Updated to just dict
additional_dataset = [] #1 - added back additional_dataset as a list

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'] = additional_dataset #3 - updated to additional_dataset
case_dataset = {} #4 - updated to clear with new dict

dataset= dict(dataset)
print(dataset)
cur.close()
conn.close()

这是它现在产生的。这是一个比我之前期望的要好得多的结构。

{
"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"
}
]
}
}