Python - Convert sql join data into list of dictionaries on certain same key-CodePudding

From a sql stored proc that performs a join on two tables I get the data below.

[{"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 0, "part_number": "string", "part_description": "string"},
{"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 1, "part_number": "string", "part_description": "string"},
{"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 2, "part_number": "string", "part_description": "string"},
{"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 3, "part_number": "string", "part_description": "string"}]

What would be the best way to convert this data into the below format? Is it possible on the python side? Or is there something other than a join I can perform to get data back in this format?

[{
  "service_order_number": "ABC",
  "vendor_id": 0,
  "recipient_id": 0,
  items: [
    {
      "item_id": 0,
      "part_number": "string",
      "part_description": "string",
      
    },
{
      "item_id": 1,
      "part_number": "string",
      "part_description": "string",
     
    }
  ]
},

{"service_order_number": "DEF"
  "vendor_id": 0,
  "recipient_id": 0,
  items: [
    {
      "item_id": 2,
      "part_number": "string",
      "part_description": "string",
      
    },
{
      "item_id": 3,
      "part_number": "string",
      "part_description": "string",
     
    }
  ]
}]

CodePudding user response：

Yes, it's possible to convert the data on the python side. You can use a dictionary to group the items based on the service_order_number and then convert the dictionary to the desired format. Here is an example:

 data = [{"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 0, "part_number": "string", "part_description": "string"},{"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 1, "part_number": "string", "part_description": "string"},{"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 2, "part_number": "string", "part_description": "string"},{"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 3, "part_number": "string", "part_description": "string"}]

grouped_data = {}
for item in data:
    service_order_number = item['service_order_number']
    if service_order_number not in grouped_data:
        grouped_data[service_order_number] = {
            "service_order_number": service_order_number,
            "vendor_id": item["vendor_id"],
            "recipient_id": item["recipient_id"],
            "items": []
        }
    grouped_data[service_order_number]["items"].append({
        "item_id": item["item_id"],
        "part_number": item["part_number"],
        "part_description": item["part_description"]
    })

final_data = [value for key, value in grouped_data.items()]

print(final_data)

CodePudding user response：

Here a possibile solution using defaultdict()

from collections import defaultdict

grouped_data = defaultdict(list)
for item in data:
    grouped_data[item['service_order_number']].append(item)

res = [{'service_order_number': k, 'vendor_id': v[0]['vendor_id'],
    'recipient_id': v[0]['recipient_id'], 'items': [{'item_id': item['item_id'], 
    'part_number': item['part_number'], 'part_description': item['part_description']}
    for item in v]} for k, v in grouped_data.items()]
print(res)