Python从嵌套字典提取非空实体键及SQLite3存储问题

错误原因 #

• 原代码仅遍历entities字典的键，未对键对应的值做非空校验，所以会取出所有键包括值为空的symbols、user_mentions等
• 报字符串对象相关错误，大概率是部分数据的entities字段为JSON字符串格式，而非Python字典，需要先对该字段做反序列化处理

修复后代码 #

第一步：修正样例JSON语法错误 #

你提供的样例中urls下的对象缺少键名，调整后符合JSON规范的结构如下：

[ 
  {"id" : "123", "created_at" : "monday", "tweet_text" : "#hi my name is john", "entities": {"hashtags": [{"text": "hi", "indices": [0, 2]}], "symbols": [], "user_mentions": [], "urls": [{"url": "link_1", "indices": [211, 234]}] }},
  {"id" : "456", "created_at" : "tuesday", "tweet_text" : "#food i am eating now", "entities": {"hashtags": [{"text": "food", "indices": [0, 5]}], "symbols": [],  "user_mentions": [], "urls": [], "media": "img_1"} }
]

注：我把原来的id_1、id_2统一改成了id字段，更符合常规数据结构，你可以根据实际字段名调整

第二步：提取非空键的实现代码 #

import json
import sqlite3

# 若从本地文件加载JSON数据，可使用下行代码
# data = json.load(open('你的推文JSON文件存储路径'))
conn = sqlite3.connect('entities_data.sqlite')
cur = conn.cursor()

for record in data:
    # 兼容字符串格式的entities字段
    entities = record['entities']
    if isinstance(entities, str):
        entities = json.loads(entities)
    # 筛选值非空的键
    non_empty_keys = [k for k, v in entities.items() if v]
    # 输出验证结果
    print(f"推文id {record['id']} 非空实体键：{non_empty_keys}")
    
    # 若需要写入SQLite库，可参考下方注释逻辑，根据实际实体结构调整取值规则
    # for entity_type in non_empty_keys:
    #     entity_content = entities[entity_type]
    #     if isinstance(entity_content, list):
    #         for item in entity_content:
    #             start_idx = item.get('indices', [0,0])[0]
    #             end_idx = item.get('indices', [0,0])[1]
    #             value = item.get('text') or item.get('url') or item.get('media_url')
    #             cur.execute('INSERT INTO entities (tweet_id, type, value, start_index, end_index) VALUES (?,?,?,?,?)',
    #                       (record['id'], entity_type, value, start_idx, end_idx))
    #     else:
    #         # 处理非列表类型的实体比如media
    #         cur.execute('INSERT INTO entities (tweet_id, type, value) VALUES (?,?,?)',
    #                   (record['id'], entity_type, entity_content))
# conn.commit()
conn.close()

运行效果 #

对调整后的样例数据运行上述代码，输出结果完全符合需求：

推文id 123 非空实体键：['hashtags', 'urls']
推文id 456 非空实体键：['hashtags', 'media']

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