mysql - 使用 Python 从 MySQL 重建 JSON 格式的文件夹层次结构

Question

我有一个结构如下的表:

| itemID |   parentFolderID | folderName

     1              0           folderA
     2              1           folderB
     3              1           folderC
     4              3           folderD
     5              4           folderE
     6              5           file.txt

前端开发人员需要我构建一个 JSON 文件，该文件具有表示文件夹结构的层次结构，如下所示:

{"name": "folderA",
 "itemID": "1",
 "children":[
       { "name": "folderB",
          "itemID": "2",
          "children": []
       },
       { "name": "folderC",
         "itemID": "3",
         "children": [
               { "name": "folderD",
                 "itemID": "4",
                 "children": [
                       {"name": "folderE",
                        "itemID": "5",
                        "children": [
                               {"name": "file.txt",
                                "itemID": "6"
                               },
                           ]
                        }
                     ]
                  }
               ]
            }
         ]
      }

我是数据库和 python 的 super 新手，这个问题对我的技能水平来说有点难以应付。我查看了 CTE，但它在 MySql 5.6 中不可用。我还被告知，这项工作由 Python 处理比在数据库内部处理更好。

我看过建议的解决方案，但它们要么是 JS、R 或其他。也许这些有用，但我的问题是针对 Python 的；必须有办法完成这项工作。

如何获取一个表并将其转换为 JSON 结构？谢谢。

为MySQL查询添加python数据结构:

 [{'assetID': 1,
  'assetName': 'Study Name',
  'assetTypeID': 2,
  'assetStoreName': 'TEST TEMPLATE',
  'parentAssetID': None},
 {'assetID': 2, 'assetName': '1.json', 'assetTypeID': 1, 'parentAssetID': 1},
 {'assetID': 3,
  'assetName': 'Binder-IRB',
  'assetTypeID': 2,
  'assetStoreName': 'TEST TEMPLATE',
  'parentAssetID': 1},
 {'assetID': 4,
  'assetName': 'Serverless Security Evaluation Criteria 2019.pdf',
  'assetTypeID': 1,
  'assetStoreName': 'TEST TEMPLATE',
  'parentAssetID': 1},
 {'assetID': 5,
  'assetName': '1- IRB Approvals',
  'assetTypeID': 2,
  'assetStoreName': 'TEST TEMPLATE',
  'parentAssetID': 3},
 {'assetID': 6, 'assetName': '2-ICF', 'assetTypeID': 2, 'parentAssetID': 3},
 {'assetID': 7,
  'assetName': "3-Reports",
  'assetTypeID': 2,
  'assetStoreName': 'TEST TEMPLATE',
  'parentAssetID': 3},
 {'assetID': 8,
  'assetName': 'sample resume.docx',
  'assetTypeID': 1,
  'assetStoreName': 'TEST TEMPLATE',
  'parentAssetID': 5},
 {'assetID': 9,
  'assetName': 'Inactive ICFs',
  'assetTypeID': 2,
  'assetStoreName': 'TEST TEMPLATE',
  'parentAssetID': 6}]

Answer 1

这是一个在标记为query 的变量中使用您的数据的解决方案:

def find_and_add_child(dic, element):
    """
    Searches through the passed in dict and its children
    to find where to insert `element`. Returns True if
    inserted and False otherwise. (Recursive)
    """
    if dic['itemID'] == element['parentAssetID']:
        # This is the right parent so add the child
        dic['children'].append({
            "name": element['assetName'],
            "itemID": element['assetID'],
            "children": []
        })
        return True
    else: # Search the children to find a valid parent
        for child in dic['children']:
            if find_and_add_child(child, element):
                return True
        return False

def query_to_json(query):
    """
    Converts the passed in json as a list of dicts
    to a json object.
    """
    results = []
    for entry in query:
        if entry['parentAssetID'] == None:
            # If not parent ID
            results.append({
                "name": entry['assetName'],
                "itemID": entry['assetID'],
                "children":[]
            })
        else: # We need to find the parent
            for result in results:
                if find_and_add_child(result, entry):
                    break # Entry added so break out of this loop
    return results

import json
print(json.dumps(query_to_json(query), indent=2))

输出:

[
  {
    "name": "Study Name",
    "itemID": 1,
    "children": [
      {
        "name": "1.json",
        "itemID": 2,
        "children": []
      },
      {
        "name": "Binder-IRB",
        "itemID": 3,
        "children": [
          {
            "name": "1- IRB Approvals",
            "itemID": 5,
            "children": [
              {
                "name": "sample resume.docx",
                "itemID": 8,
                "children": []
              }
            ]
          },
          {
            "name": "2-ICF",
            "itemID": 6,
            "children": [
              {
                "name": "Inactive ICFs",
                "itemID": 9,
                "children": []
              }
            ]
          },
          {
            "name": "3-Reports",
            "itemID": 7,
            "children": []
          }
        ]
      },
      {
        "name": "Serverless Security Evaluation Criteria 2019.pdf",
        "itemID": 4,
        "children": []
      }
    ]
  }
]