如何用Python动态拆分JSON响应的Details字段生成多列DataFrame

Python 生成 DataFrame #

直接通过拆分Details字段为字典，再与原字段合并即可实现动态列生成：

• 先获取API响应数据（这里用模拟数据示例，实际替换为你的API请求代码）：

import requests
import pandas as pd

# 模拟API响应（实际用requests.get(url).json()获取）
api_response = {
    "posts": [
        {"ID": 1, "Details": "Author:Alice;Category:Tech;Views:100"},
        {"ID": 2, "Details": "Author:Bob;Status:Published;Likes:50"},
        {"ID": 3, "Details": "Category:Life;Tags:Travel;Comments:10"}
    ]
}

• 处理每个post的Details字段，拆分为键值对字典，再与原ID合并：

processed_posts = []
for post in api_response["posts"]:
    # 拆分Details为键值对，兼容值中含冒号的情况
    details_dict = {}
    for item in post["Details"].split(";"):
        if ":" in item:
            key, value = item.split(":", 1)
            details_dict[key.strip()] = value.strip()
    # 合并原ID和拆分后的字典
    processed_post = {"ID": post["ID"], **details_dict}
    processed_posts.append(processed_post)

# 生成DataFrame，缺失字段自动填充为NaN
df = pd.DataFrame(processed_posts)
print(df)

SQL 创建数据表 #

由于Details的键不固定，需先提取所有可能的键，再动态创建表结构并插入数据。以下以MySQL为例：

步骤1：提取所有唯一键 #

• 先创建临时表存储原始API数据：

-- 创建临时表
CREATE TEMPORARY TABLE temp_posts (id INT, details TEXT);
-- 插入模拟数据（实际替换为API数据的插入逻辑）
INSERT INTO temp_posts VALUES
(1, 'Author:Alice;Category:Tech;Views:100'),
(2, 'Author:Bob;Status:Published;Likes:50'),
(3, 'Category:Life;Tags:Travel;Comments:10');

-- 提取所有唯一的键
SELECT DISTINCT TRIM(SUBSTRING_INDEX(item, ':', 1)) AS key_name
FROM temp_posts,
     JSON_TABLE(
         CONCAT('["', REPLACE(details, ';', '","'), '"]'),
         "$[*]" COLUMNS(item TEXT PATH "$")
     ) AS jt
WHERE item LIKE '%:%';

步骤2：动态创建目标表 #

• 通过存储过程自动拼接表结构：

DELIMITER //
CREATE PROCEDURE create_posts_table()
BEGIN
    -- 拼接所有动态列的定义
    SET @columns = '';
    SELECT GROUP_CONCAT(DISTINCT CONCAT('`', TRIM(SUBSTRING_INDEX(item, ':', 1)), '` VARCHAR(255)'))
    INTO @columns
    FROM temp_posts,
         JSON_TABLE(
             CONCAT('["', REPLACE(details, ';', '","'), '"]'),
             "$[*]" COLUMNS(item TEXT PATH "$")
         ) AS jt
    WHERE item LIKE '%:%';

    -- 执行建表语句
    SET @create_sql = CONCAT('CREATE TABLE posts (id INT PRIMARY KEY, ', @columns, ')');
    PREPARE stmt FROM @create_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程创建表
CALL create_posts_table();

步骤3：插入拆分后的数据 #

• 用存储过程动态拆分并插入数据：

DELIMITER //
CREATE PROCEDURE insert_posts_data()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE post_id INT;
    DECLARE post_details TEXT;
    DECLARE cur CURSOR FOR SELECT id, details FROM temp_posts;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO post_id, post_details;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 拆分details为键值对，拼接插入语句
        SET @items = CONCAT('["', REPLACE(post_details, ';', '","'), '"]');
        SELECT GROUP_CONCAT('`', TRIM(SUBSTRING_INDEX(item, ':', 1)), '`'),
               GROUP_CONCAT('''', TRIM(SUBSTRING_INDEX(item, ':', -1)), '''')
        INTO @key_list, @value_list
        FROM JSON_TABLE(@items, "$[*]" COLUMNS(item TEXT PATH "$")) AS jt
        WHERE item LIKE '%:%';

        SET @insert_sql = CONCAT('INSERT INTO posts (id, ', @key_list, ') VALUES (', post_id, ', ', @value_list, ')');
        PREPARE stmt FROM @insert_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END //
DELIMITER ;

-- 调用存储过程插入数据
CALL insert_posts_data();

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