SQLite+Python单查询实现订单关联商品结构化结果可行吗？

现有数据表 #

products表 #

orders表 #

orderedProducts表 #

期望结果格式 #

orders[
   {id, address, items[{productid , price, amount}, {productid , price, amount}]},
   {id, address, items[{productid , price, amount}]}
]

能否通过单个查询实现？ #

可以实现，只要你的SQLite版本不低于3.33.0（2020年8月发布），就能利用SQLite的JSON聚合函数生成嵌套结构，再结合Python解析即可，全程单个SQL查询搞定。

具体实现步骤 #

1. SQL查询语句
   用JSON_GROUP_ARRAY将每个订单对应的商品聚合为JSON数组，JSON_OBJECT构造单个商品的键值对，通过分组订单ID和地址来聚合数据：

   SELECT 
     o.orderId AS id,
     o.address,
     JSON_GROUP_ARRAY(
       JSON_OBJECT(
         'productid', p.productid,
         'price', p.price,
         'amount', op.amount
       )
     ) AS items
   FROM orders o
   LEFT JOIN orderedProducts op ON o.orderId = op.orderId
   LEFT JOIN products p ON op.productid = p.productid
   GROUP BY o.orderId, o.address;
   

2. Python处理代码
   查询结果中的items是JSON字符串，用Python的json模块解析成列表，最终组装成你要的结构：

   import sqlite3
   import json
   
   # 连接数据库
   conn = sqlite3.connect('your_db.db')
   cursor = conn.cursor()
   
   # 执行查询
   cursor.execute("""
   SELECT 
     o.orderId AS id,
     o.address,
     JSON_GROUP_ARRAY(
       JSON_OBJECT(
         'productid', p.productid,
         'price', p.price,
         'amount', op.amount
       )
     ) AS items
   FROM orders o
   LEFT JOIN orderedProducts op ON o.orderId = op.orderId
   LEFT JOIN products p ON op.productid = p.productid
   GROUP BY o.orderId, o.address;
   """)
   
   # 组装结果
   orders_list = []
   for row in cursor.fetchall():
       order = {
           'id': row[0],
           'address': row[1],
           'items': json.loads(row[2]) if row[2] else []
       }
       orders_list.append(order)
   
   # 输出最终结构
   print(json.dumps({'orders': orders_list}, indent=2))
   
   # 关闭连接
   conn.close()
   

说明 #

这个方案通过一次SQL查询直接生成包含嵌套商品数组的订单数据，Python只需要做简单的JSON解析和结构组装，完全满足你要的格式要求。如果你的SQLite版本过低，建议升级到支持JSON聚合函数的版本。

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