如何为嵌套JSON中的数据库表元数据生成Pandas DataFrame

以下是根据提供的JSON Schema生成Person、HomeAddress、Employment三个表对应Pandas DataFrame的实现方案：

实现代码 #

import pandas as pd
import json

# 定义JSON Schema内容
schema_json = {
    "$id": "12121212",
    "type": "object",
    "properties": {
        "PersonId": {"type": "integer"},
        "Person": {
            "type": ["object", "null"],
            "properties": {
                "PersonId": {"type": "integer"},
                "DateOfBirth": {"type": "string", "format": "date-time"},
                "DateOfBirthVerified": {"type": "boolean"},
                "Sex": {"type": ["string", "null"]},
                "Surname": {"type": ["string", "null"]},
                "Initials": {"type": ["string", "null"]},
                "Forenames": {"type": ["string", "null"]},
                "Title": {"type": ["string", "null"]},
                "NationalIdNumber": {"type": ["string", "null"]},
                "HomeAddress": {
                    "type": ["object", "null"],
                    "properties": {
                        "EffectiveDate": {"type": "string", "format": "date-time"},
                        "EndDate": {"type": "string", "format": "date-time"},
                        "Category": {"type": ["string", "null"]},
                        "Line1": {"type": ["string", "null"]},
                        "Line2": {"type": ["string", "null"]},
                        "Line3": {"type": ["string", "null"]},
                        "Line4": {"type": ["string", "null"]},
                        "City": {"type": ["string", "null"]},
                        "County": {"type": ["string", "null"]},
                        "Country": {"type": ["string", "null"]},
                        "CareOfAddressee": {"type": ["string", "null"]},
                        "PostCode": {"type": ["string", "null"]},
                        "SuspectAddress": {"type": "boolean"},
                        "Overseas": {"type": "boolean"}
                    },
                    "required": ["EffectiveDate", "EndDate", "Category", "Line1", "Line2", "Line3", "Line4", "City", "County", "Country", "CareOfAddressee", "PostCode", "SuspectAddress", "Overseas"]
                }
            },
            "required": ["PersonId", "DateOfBirth", "DateOfBirthVerified", "Sex", "Surname", "Initials", "Forenames", "Title", "NationalIdNumber", "HomeAddress"]
        },
        "Employment": {
            "type": ["object", "null"],
            "properties": {
                "EmployeeReference": {"type": ["string", "null"]},
                "DateFirstEmployed": {"type": "string", "format": "date-time"},
                "PayrollNumber": {"type": ["string", "null"]}
            },
            "required": ["EmployeeReference", "DateFirstEmployed", "PayrollNumber"]
        }
    },
    "required": ["PersonId", "Person", "Employment"]
}

def generate_dataframe(properties, required_fields):
    """从Schema属性中提取字段信息生成DataFrame"""
    data_rows = []
    for col_name, col_props in properties.items():
        # 处理字段类型：优先取非null的类型
        col_type = col_props.get("type")
        if isinstance(col_type, list):
            col_type = next(t for t in col_type if t != "null")
        
        # 提取格式信息，无则为空字符串
        col_format = col_props.get("format", "")
        
        # 判断是否为必填字段
        is_required = "Yes" if col_name in required_fields else "No"
        
        data_rows.append({
            "Column_Name": col_name,
            "Type": col_type,
            "Format": col_format,
            "Required": is_required
        })
    return pd.DataFrame(data_rows)

# 生成各表DataFrame
person_df = generate_dataframe(
    schema_json["properties"]["Person"]["properties"],
    schema_json["properties"]["Person"]["required"]
)

home_address_df = generate_dataframe(
    schema_json["properties"]["Person"]["properties"]["HomeAddress"]["properties"],
    schema_json["properties"]["Person"]["properties"]["HomeAddress"]["required"]
)

employment_df = generate_dataframe(
    schema_json["properties"]["Employment"]["properties"],
    schema_json["properties"]["Employment"]["required"]
)

各表DataFrame结构 #

Person表 #

HomeAddress表 #

Employment表 #

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