如何使用Python和SQL在父对象中获取子对象？

Question

我正在尝试创建一个基本API，其中一个表名为orders，另一个表名为order_lines，它为每个订购的产品都有一行，其中一个字段名为order_id，对应于订单。在执行GET请求时，如何将订单作为父级，并将其中的每个订单行记录作为子数组？我不确定是否需要以某种方式在SQL中执行此操作，或者是否可以轻松地在Python中执行此操作。我只需要一个如何提取和构造这些数据的一般概念

我的API GET请求如下所示：

@app.route('/api/v1/ordertest', methods=['GET'])
def orders_test2():
    # try:
    
    if request.method == 'GET':
        query_parameters = request.args

        id = query_parameters.get('id')
        customer_id = query_parameters.get('customer_id')

        query = '''
                    SELECT 
                        o.*,
                        ol.product_id, 
                        p.name, 
                        ol.quantity, 
                        p.price as price, 
                        round((p.price * ol.quantity), 2) as total_price
                    FROM orders o
                    join order_lines ol
                    on o.id = ol.order_id
                    join products p
                    on ol.product_id = p.id
                    WHERE o.
                '''
        to_filter = []

        if id:
            query += 'id=? AND'
            to_filter.append(id)
        if customer_id:
            query += 'customer_id=? AND'
            to_filter.append(customer_id)

        if not (id or customer_id):
            return page_not_found(404)

        query = query[:-4] + ';'

        conn = sqlite3.connect(db)
        conn.row_factory = dict_factory
        cur = conn.cursor()

        results = cur.execute(query, to_filter).fetchall()

        return jsonify(results)

Doing/api/v1/ordertest?id=1返回我想要的所有数据，但它包含每个产品的对象中列出的orders表中的信息

[
  {
    "created_at": "2020-11-12 00:08:32", 
    "customer_id": 1, 
    "id": 1, 
    "name": "plum", 
    "price": 0.3, 
    "product_id": 4, 
    "quantity": 2, 
    "status": "delivered", 
    "total_price": 0.6, 
    "updated_at": "2021-02-05 18:41:20"
  }, 
  {
    "created_at": "2020-11-12 00:08:32", 
    "customer_id": 1, 
    "id": 1, 
    "name": "salmon", 
    "price": 6.76, 
    "product_id": 20, 
    "quantity": 4, 
    "status": "delivered", 
    "total_price": 27.04, 
    "updated_at": "2021-02-05 18:41:20"
  }, 
  {
    "created_at": "2020-11-12 00:08:32", 
    "customer_id": 1, 
    "id": 1, 
    "name": "mint chocolate bar", 
    "price": 2.01, 
    "product_id": 30, 
    "quantity": 3, 
    "status": "delivered", 
    "total_price": 6.03, 
    "updated_at": "2021-02-05 18:41:20"
  }
]

但是，我希望数据看起来像这样，其中具有匹配订单id的每个订单行都位于主对象内：

[
    {
      "created_at": "2020-11-12 00:08:32", 
      "customer_id": 1, 
      "id": 1, 
      "status": "delivered", 
      "updated_at": "2021-02-05 18:41:20",
      "products": 
      {
          "name": "plum", 
          "price": 0.3, 
          "product_id": 4, 
          "quantity": 2,
          "total_price": 0.6
          }, 
      {
          "name": "salmon", 
          "price": 6.76, 
          "product_id": 20, 
          "quantity": 4, 
          "total_price": 27.04
      }, 
      {
          "name": "mint chocolate bar", 
          "price": 2.01, 
          "product_id": 30, 
          "quantity": 3, 
          "total_price": 6.03
        }
    }
  ]

Answer 1

我建议您阅读REST（ful）API，因为它将让您了解如何处理分层数据（GraphQL是另一种方式）。如您所述，您可以有两个端点，也可以有一个端点：

GET /orders?order_id=1 or
GET /orders/1

这可能会返回如下内容（csv、json也是一个不错的选择，但会给示例增加更多噪音）：

item,quantity,price
Bicycle,1,$500
Helmet,1,$125

如果您想使用两个端点，它可以返回指向每个订单项目的链接：

/order_line/27
/order_line/42

另一种设计选择是在URL中对数据的层次结构进行编码，如下所示：

 /order/1/order_line/27
 /order/1/order_line/42

然后，您的客户端将这些URL中的每一个都发送到 提取附加信息。它会有很大的开销，所以对于这样的小数据，通常将数据嵌入到单个端点中

编写代码时，您将提取此1中的order_id，并获取此SQL中数据库的值：

select item, quantity, price from line_items where order_id = 1

（确保将变量绑定到您决定使用的任何数据库API）。相关表格可能如下所示：

create table orders (
   order_id int primary key,
);

create table order_lines (
   order_line_id int primary key,
   order_id int not null references orders (order_id),
);

Answer 2

管理一个数据库，应该通过SQL管理系统如MySQL、SQLite。。。您可以通过将它们的python库导入到代码中来使用它们来执行SQL查询。 您的问题可以通过以下方式解决：

    SELECT column_name(s)
    FROM table1
    INNER JOIN table2
    ON table1.column_name = table2.column_name;