如何实现将JSON写入SQLite3时移除不在JSON中的人员数据

核心逻辑 #

先同步今日JSON中的新数据（保留原有INSERT OR IGNORE逻辑避免重复），再删除数据库中不在今日JSON列表内的人员记录。

代码示例 #

假设你的人员表以name作为唯一标识（建表时需设为PRIMARY KEY），实现代码如下：

import json
import sqlite3

# 读取今日生成的JSON数据
with open('daily_people.json', 'r', encoding='utf-8') as f:
    today_people = json.load(f)

# 提取今日所有人员的唯一标识（此处用name，按需替换为id等字段）
current_ids = [p['name'] for p in today_people]

# 连接SQLite数据库
conn = sqlite3.connect('people_db.sqlite')
cursor = conn.cursor()

# 1. 插入/保留现有数据（沿用原有INSERT OR IGNORE逻辑）
for person in today_people:
    # 替换为你的表实际字段，比如name, age, department等
    cursor.execute('''
        INSERT OR IGNORE INTO people (name, age, department)
        VALUES (?, ?, ?)
    ''', (person['name'], person['age'], person['department']))

# 2. 删除不在今日JSON中的人员记录
# 用参数化查询避免SQL注入，动态生成占位符
placeholders = ','.join(['?' for _ in current_ids])
cursor.execute(f'''
    DELETE FROM people
    WHERE name NOT IN ({placeholders})
''', current_ids)

# 提交更改并关闭连接
conn.commit()
conn.close()

关键注意事项 #

• 必须确保用于判断唯一性的字段（如name）在表中是唯一约束（例如建表语句CREATE TABLE people (name TEXT PRIMARY KEY, age INTEGER, department TEXT);），否则INSERT OR IGNORE和删除逻辑都会失效。
• 如果需要同步更新人员的信息（比如原有人员的部门变化），可以把INSERT OR IGNORE改为INSERT OR REPLACE，这样会覆盖已有记录的非主键字段。
• 若唯一标识不是name，将代码中所有name替换为实际字段（如user_id）即可。

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