如何基于数据库查询数据创建Python多值键字典用于UI展示

一、生成目标字典的实现代码 #

要得到以问题文本为键、选项数组为值的字典，推荐先通过question_id做中间映射（避免因问题文本重复导致数据覆盖），再生成目标结构，代码如下：

# 数据库查询返回的问题数据
questions = [
    {'question_id': 1, 'question_text': 'How many servers run at max capacity?', 'category_id': 1, 'is_long_survey': 0},
    {'question_id': 2, 'question_text': 'Is the database getting replicated?', 'category_id': 1, 'is_long_survey': 0},
    {'question_id': 3, 'question_text': 'Where is the infrastructure located?', 'category_id': 1, 'is_long_survey': 0}
]

# 数据库查询返回的选项数据
choices = [
    {'choice_id': 1, 'choice_text': '2', 'question_id': 1},
    {'choice_id': 2, 'choice_text': '4', 'question_id': 1},
    {'choice_id': 3, 'choice_text': '6', 'question_id': 1},
    {'choice_id': 4, 'choice_text': 'Yes', 'question_id': 2},
    {'choice_id': 5, 'choice_text': 'No', 'question_id': 2},
    {'choice_id': 6, 'choice_text': 'global', 'question_id': 3},
    {'choice_id': 7, 'choice_text': 'local', 'question_id': 3}
]

# 1. 按question_id分组选项，避免重复遍历
choices_by_qid = {}
for choice in choices:
    qid = choice['question_id']
    if qid not in choices_by_qid:
        choices_by_qid[qid] = []
    choices_by_qid[qid].append(choice['choice_text'])

# 2. 生成以问题文本为键的字典
question_choices_dict = {}
for q in questions:
    question_choices_dict[q['question_text']] = choices_by_qid.get(q['question_id'], [])

# 输出结果
print(question_choices_dict)

运行后会得到如下结构：

{
    'How many servers run at max capacity?': ['2', '4', '6'],
    'Is the database getting replicated?': ['Yes', 'No'],
    'Where is the infrastructure located?': ['global', 'local']
}

二、更适合UI展示的其他数据结构 #

如果需要在HTML中展示更多问题属性（比如分类ID、是否为长问卷），推荐使用嵌套字典的列表，每个元素包含完整的问题信息和对应选项，更便于模板遍历：

questions_with_choices = []
for q in questions:
    # 复制原问题的所有属性
    q_item = q.copy()
    # 添加对应选项列表
    q_item['choices'] = choices_by_qid.get(q['question_id'], [])
    questions_with_choices.append(q_item)

生成的结构示例：

[
    {
        'question_id': 1,
        'question_text': 'How many servers run at max capacity?',
        'category_id': 1,
        'is_long_survey': 0,
        'choices': ['2', '4', '6']
    },
    # ... 其他问题对象
]

另外，也可以用collections.defaultdict简化选项分组的代码，减少手动判断键是否存在的步骤：

from collections import defaultdict

choices_by_qid = defaultdict(list)
for choice in choices:
    choices_by_qid[choice['question_id']].append(choice['choice_text'])

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