如何从SQLite获取含JSON的结果并在Jinja模板中正确使用？

方案一：Python层解析JSON字符串+支持列名访问 #

步骤1：配置游标返回可按列名访问的结果 #

默认fetchall()返回元组，无法用列名访问。通过设置row_factory为sqlite3.Row，让结果支持列名（如product.colorarray或product['colorarray']）：

import sqlite3
import json

conn = sqlite3.connect('你的数据库路径.db')
conn.row_factory = sqlite3.Row  # 启用列名访问
cursor = conn.cursor()

# 执行原查询
products = cursor.execute("""
    SELECT Item, EntryDate, json_group_array(json_object('color', P.option)) as colorarray
    FROM 你的表名
    -- 你的分组/过滤条件
    GROUP BY Item, EntryDate
""").fetchall()

# 将JSON字符串解析为Python列表
for product in products:
    product['colorarray'] = json.loads(product['colorarray'])

步骤2：Jinja模板中正常遍历 #

此时product.colorarray已经是Python列表，可以直接遍历：

{% for product in products %}
  <div>
    <h3>{{ product.Item }}</h3>
    <p>日期：{{ product.EntryDate }}</p>
    <p>颜色：</p>
    {% for color_item in product.colorarray %}
      <span>{{ color_item.color }}</span>
    {% endfor %}
  </div>
{% endfor %}

方案二：修改SQL查询，避免JSON聚合，在Python中分组 #

如果不想依赖SQLite的JSON函数，可以直接查询所有明细行，再在Python中分组构造结构：

步骤1：修改SQL查询 #

去掉json_group_array和聚合逻辑，返回每一行的颜色数据：

SELECT Item, EntryDate, P.option as color
FROM 你的表名
-- 保留原过滤条件，去掉GROUP BY
ORDER BY Item, EntryDate  -- 分组前需排序

步骤2：Python中分组处理 #

用itertools.groupby按商品和日期分组，构造包含颜色列表的结构：

import sqlite3
from itertools import groupby

conn = sqlite3.connect('你的数据库路径.db')
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

rows = cursor.execute("""
    SELECT Item, EntryDate, P.option as color
    FROM 你的表名
    ORDER BY Item, EntryDate
""").fetchall()

# 分组构造products结构
products = []
for (item, entry_date), group in groupby(rows, key=lambda r: (r['Item'], r['EntryDate'])):
    colorarray = [{'color': row['color']} for row in group]
    products.append({
        'Item': item,
        'EntryDate': entry_date,
        'colorarray': colorarray
    })

步骤3：Jinja模板遍历 #

和方案一的模板代码完全一致，直接遍历product.colorarray即可。

问题原因说明 #

原查询中json_group_array返回的是JSON格式的字符串，而非Python的列表对象。Jinja遍历字符串时会将其视为字符序列，导致逐个输出字符。必须将字符串解析为Python原生的列表/字典结构，才能正常遍历。

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