如何使用Python解析并提取嵌套JSON格式的产品数据表信息？

Hey Dan, let's walk through how to pull that nested product data from the Icecat API and get it into a structured table format perfect for your database. I’ve tackled similar nested JSON parsing tasks before, so here’s a practical approach that should work smoothly for you.

First, let's start with fetching and parsing the API response properly—we’ll add error handling to avoid headaches with network issues or bad responses:

import requests

# Your target API endpoint
api_url = "http://live.icecat.biz/api/?shopname=openIcecat-live&lang=en&content=featuregroups&icecat_id=1334921"

try:
    # Fetch the data
    response = requests.get(api_url)
    response.raise_for_status()  # Throw an error if the request fails (4xx/5xx)
    product_json = response.json()
except requests.exceptions.RequestException as e:
    print(f"Oops, something went wrong fetching data: {e}")
    exit(1)

Next, the tricky part is handling the nested FeatureGroups and their child features. A recursive function works best here—it’ll dig through all levels of nesting so you don’t miss any features:

def extract_all_features(feature_groups, parent_group_label=None):
    """Recursively pull features from nested feature groups"""
    feature_list = []
    
    for group in feature_groups:
        # Build a clear label for the feature group (including parent hierarchy)
        group_name = group.get("Name", "Unnamed Group")
        full_group_label = f"{parent_group_label} > {group_name}" if parent_group_label else group_name
        
        # Grab all direct features in this group
        if "Features" in group:
            for feature in group["Features"]:
                feature_list.append({
                    "feature_group": full_group_label,
                    "feature_name": feature.get("Name", "Unnamed Feature"),
                    "feature_value": feature.get("Value", "No Value Provided"),
                    "feature_unit": feature.get("Unit", "")
                })
        
        # Recurse into any child feature groups
        if "FeatureGroups" in group and group["FeatureGroups"]:
            feature_list.extend(extract_all_features(group["FeatureGroups"], full_group_label))
    
    return feature_list

Now let’s use this function to extract all features, then convert them into a structured table (great for database insertion):

# Check if the top-level has the FeatureGroups we need
if "FeatureGroups" in product_json:
    all_product_features = extract_all_features(product_json["FeatureGroups"])
    
    # Convert to a pandas DataFrame for easy database handling
    import pandas as pd
    features_df = pd.DataFrame(all_product_features)
    
    # Quick check to verify the data
    print("Extracted Feature Preview:")
    print(features_df.head())
    
    # Example: Insert into a SQLite database (adjust for your DB system)
    # import sqlite3
    # db_conn = sqlite3.connect("product_database.db")
    # features_df.to_sql("product_features", db_conn, if_exists="replace", index=False)
    # db_conn.close()
    # print("Data saved to database successfully!")
else:
    print("No FeatureGroups found in the API response—double-check the endpoint or icecat_id.")

Key Tips for Your Workflow: #

• Debug First: If you’re unsure about the JSON structure, print the top-level keys with print(list(product_json.keys())) to explore what you’re working with.
• Robust Access: Using .get() for dictionary keys prevents KeyError if some fields are missing from the API response.
• Database Flexibility: The pandas DataFrame can be easily exported to most databases (MySQL, PostgreSQL, etc.) using df.to_sql() with the right connector library.

内容的提问来源于stack exchange，提问作者Dan P.