我原本以为把几千个单词塞进数据库最折磨人的环节是写 INSERT 语句直到 danci 3 这个项目做到一半我才发现自己幼稚得可爱。danci 是一个背单词工具第三版的目标很简单把散落在多个 JSON 文件里的几千个单词干净利落地倒进数据库让查询、筛选、打标签都变成一条 SQL 的事。可现实是JSON 数据清洗这一步比写代码花的时间多得多。这篇就是记录我从 JSON 数据清洗一路走到 AI Coding 的完整过程包括我踩过的坑、验证过的方法以及最后为什么说“先采集再清洗再入库”这三个顺序一个都不能省。1. 一个背单词工具为什么拖到第三版才开始认真导数据1.1 前两版的教训内存 JSON 和文件索引都扛不住danci 第一版的时候我很天真地以为“几千个单词而已全读进内存不就行了”。于是所有词库都是 JSON 文件App 一启动就把整个词库加载成一个字典查词就是dict.get(word)。四千多条词启动慢一点也还能忍真正的问题是后续需求根本做不动想要按前缀搜索、按标签筛选、统计学习进度全都要遍历整个字典写一遍逻辑。更离谱的是生词本我当时用一个单独的 JSON 文件保存用户标记的生词每加一个生词就整体覆盖写一次文件。词少的时候没事等用户标记了上千个生词写文件就是一场灾难。第二版我换了思路把词库存成 JSON Lines一行一条单词启动时只加载索引用到哪个词再去文件里定位。听起来挺聪明实际上同步逻辑麻烦到爆炸词库本身在更新生词本在另一个文件两个文件之间还要互相引用去重和合并全靠手写代码。做了几个月我意识到一个很简单的道理这种结构化数据本来就该交给数据库处理索引、事务、去重、查询成熟系统都已经替你解决了自己造轮子纯属浪费时间。所以 danci 3 的目标就非常明确所有单词数据全部落进数据库查询交给 SQL后续的学习记录、词书、标签都围绕数据库来设计。1.2 先清洗再入库这个顺序为什么不能省那段时间我在好几个数据相关的帖子里都看到一句话叫“数据治理要先采集再清洗”。当时没太当回事觉得不就是读 JSON 然后写进数据库吗。直到我真正拿到那份原始词库 JSON才明白如果不先清洗就导入会遇到多恶心的事。第一重复词会破坏唯一约束。原始数据里同一个单词可能出现两次一次是大写开头一次是小写如果建表时给word加了 UNIQUE第一条插进去之后第二条直接报错整个导入脚本中断。第二字段缺失会让查询炸得很隐蔽。比如某个词条没有音标导入的时候你给它塞了个NULL后面代码里用len(word.phonetic)判断就报 TypeError。第三释义和例句里的转义符、全角空格、BOM 字符会直接污染数据。我当时查一个词怎么都查不到后来才发现这个词前面藏了一个\ufeff字符。先清洗再入库和先入库再清洗最大的差别是心理上的清洗是在源头上挡住垃圾失败了可以重跑脚本代价很低入库之后再清洗脏数据已经流进业务层你还要到处排查是哪条数据、哪个环节被污染的成本高十倍都不止。所以我把清洗写成了独立的一步原始文件直接备份不动清洗脚本一遍一遍跑直到报告满意了才进入导入阶段。2. 原始 JSON 词库体检在写清洗脚本之前先看数据有多脏2.1 一份“看起来正常”的单词 JSON 长什么样很多教程里展示的 JSON 样例都干净得像教科书但实际项目中拿到的数据完全不是这样。我手上这份词库比较常规的一条是这个样子{ word: aberration, phonetic: /ˌæbəˈreɪʃn/, meanings: [ {pos: n., def: a temporary departure from the normal}, {pos: n., def: a deviation from the expected} ], examples: [ { text: The delay was caused by an aberration in the computer system., translation: 延迟是由电脑系统异常引起的。 } ], tags: [ielts, gre], meta: {source: lexicon, level: 7} }看着人模人样但一旦开始批量检查各种变体就冒出来了。有的文件顶层直接是一个 JSON 数组有的文件顶层是一个对象里面套着{words: [...]}有的单词字段不叫word而叫name音标有时候是字符串有时候是对象{AmE: ..., BrE: ...}这种结构也出现过meanings字段有时候是列表有时候直接被写成字符串n. 单词vi. 意味着例句缺失更是家常便饭。这些不规则不是一次能摸清的所以我决定先写一个体检脚本把每个字段的缺失情况统计出来而不是闷头写清洗代码。2.2 字段缺失率统计先体检再动手体检脚本很简单就是用json遍历所有文件统计字段出现次数和缺失次数。这里有个小坑文件是用 UTF-8 with BOM 编码保存的直接用encodingutf-8读第一个字段会带一个看不见的\ufeff导致word: \ufeffabandon。所以读取时要用utf-8-sigimport json import glob from collections import Counter total 0 field_count Counter() missing_count Counter() fields [word, phonetic, meanings, examples, tags] for path in glob.glob(raw/*.json): with open(path, encodingutf-8-sig) as f: data json.load(f) items data if isinstance(data, list) else data.get(words, []) for item in items: total 1 for field in fields: if field in item and item[field]: field_count[field] 1 else: missing_count[field] 1 print(total:, total) print(field_count:, dict(field_count)) print(missing_count:, dict(missing_count))我这边跑出来的结果大概是这样字段存在数缺失数缺失率word4607230.5%phonetic396366714.4%meanings4618120.3%examples3159147131.8%tags2901172937.3%看这个报告我就有数了word字段缺失虽然只有 23 条但这是硬伤必须删音标缺失比例超过百分之十四能补则补补不了就置空例句缺失三成这是可选字段导入时留空即可不影响主表。没有这份体检报告我脑子里对“脏数据”的理解就永远是模糊的。3. 清洗是重头戏从嵌套结构到表结构的一步步改造3.1 把嵌套 JSON 拍平成行记录清洗的第一步是把每个单词对象里面的嵌套结构拆开。这里要做一个重要决定meanings和examples不能简单塞进一个字段里因为后面要建子表一个单词对应多条释义、多个例句。我的做法是写一个flatten_word函数每个单词对象输出一个主表行同时把释义、例句、标签分别收集到三个列表里def flatten_word(item): word (item.get(word) or ).strip() if not word: return None, [], [], [] phonetic item.get(phonetic, ) if isinstance(phonetic, dict): phonetic phonetic.get(BrE) or phonetic.get(AmE) or meanings item.get(meanings, []) if isinstance(meanings, str): # 部分数据把 meanings 直接写成字符串简单切分 parts [p.strip() for p in meanings.split() if p.strip()] meanings [{pos: , def: p} for p in parts] examples item.get(examples, []) if isinstance(examples, str): examples [{text: examples, translation: }] tags item.get(tags, []) if isinstance(tags, str): tags [tags] meta item.get(meta, {}) level meta.get(level, 0) if isinstance(meta, dict) else 0 row { word: word, phonetic: phonetic, source: item.get(source, lexicon), level: level, } return row, meanings, examples, tags这个函数看着不复杂但它把我在体检阶段发现的所有“变体”都统一掉了。音标是对象就取英式发音meanings是字符串就暴力切分标签是字符串就包装成列表。这样后面构造 DataFrame 的时候字段类型基本是可控的。3.2 pandas 去重、空值、类型统一的一条龙手术拍平之后我用 pandas 接住所有主表行接下来就是清洗的三大动作去重、空值处理、类型统一。import pandas as pd df pd.DataFrame(row_list) df df.dropna(subset[word]) df[word] df[word].astype(str).str.strip() df df.drop_duplicates(subset[word], keepfirst) df[phonetic] df[phonetic].fillna().astype(str) df[source] df[source].fillna(lexicon) df[level] pd.to_numeric(df[level], errorscoerce).fillna(0).astype(int)这里要特别说一下去重策略。很多人一上来就df[word] df[word].str.lower()然后按小写去重觉得这样可以把 “GRE” 和 “gre” 这种重复项干掉。但我检查之后发现词库里 “I” 和 “i”、“US” 和 “us”、“A” 和 “a” 并不是同一类词单词的语义有时候就是依赖大小写的。所以我最终采用的是精确匹配去重只把那些完全相同的小写词去掉同时把标签统一转小写归一化。这个决策看起来只是在去重函数里加不加一个lower()的区别实际上是一个业务语义判断。所以处理顺序是先用dropna把缺失word字段的 23 条残渣删掉再精确去重掉 218 条重复记录最后剩下 4389 条候选词。这个过程中我以为自己是“在清洗数据”实际上是在替数据做业务规则决策这也是为什么我不能把这一步完全交给一个自动工具的原因。3.3 清洗产物干净 JSON 与质量报告清洗的结果我输出成了一个中间文件clean_words.json同时保留了一份打印出来的质量报告原始词条4630 删除缺失 word 的记录23 删除重复记录218 最终候选词条4389 音标缺失3828.7% 有标签的词条276663.0%为什么要中间文件而不是直接入库因为我可能在导入阶段发现表结构要调整、或者某个字段还要补如果清洗和导入耦合在同一个脚本里就要重新跑一遍全部逻辑。拆成两步之后清洗脚本只需要做一件事把原始 JSON 变成干净的中间 JSON。导入脚本也只需要做一件事把干净 JSON 写进数据库。出了问题定位起来非常快。清洗之后的clean_words.json长这样[ { word: aberration, phonetic: /ˌæbəˈreɪʃn/, source: lexicon, level: 7, meanings: [ {pos: n., def: a temporary departure from the normal} ], examples: [], tags: [ielts, gre] } ]我习惯在生成这个文件之后用可视化工具打开确认一眼不把清洗过程当黑盒。如果你不想写一堆样板代码Windows 上可以用 dbx 数据库工具直接打开后续生成的 SQLite 文件查看数据也可以在这一步先导入随机几十条到临时库里扫一眼。亲眼看到干净数据长什么样比看任何统计数据都安心。4. 表结构设计让单词、释义、例句、标签各归其位4.1 主表 三张子表拆分依据和建表语句这次我选了 SQLite 作为 danci 3 的存储原因很简单单机工具、不需要服务端、不需要并发写入SQLite 一个文件搞定备份和分发都方便。如果你要做成多人同时在线的服务那直接换 MySQL / PostgreSQL核心表结构是一样的只是语法上微调。表结构我拆成了四张主表words存单词本身meanings存释义examples存例句word_tags存标签关联。建表语句如下CREATE TABLE words ( id INTEGER PRIMARY KEY AUTOINCREMENT, word TEXT NOT NULL UNIQUE, phonetic TEXT NOT NULL DEFAULT , source TEXT NOT NULL DEFAULT , level INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL DEFAULT (datetime(now)) ); CREATE TABLE meanings ( id INTEGER PRIMARY KEY AUTOINCREMENT, word_id INTEGER NOT NULL, pos TEXT NOT NULL DEFAULT , definition TEXT NOT NULL, FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE ); CREATE TABLE examples ( id INTEGER PRIMARY KEY AUTOINCREMENT, word_id INTEGER NOT NULL, sentence TEXT NOT NULL, translation TEXT NOT NULL DEFAULT , FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE ); CREATE TABLE word_tags ( word_id INTEGER NOT NULL, tag TEXT NOT NULL, PRIMARY KEY (word_id, tag), FOREIGN KEY (word_id) REFERENCES words(id) ON DELETE CASCADE ); CREATE INDEX idx_meanings_word_id ON meanings(word_id); CREATE INDEX idx_examples_word_id ON examples(word_id); CREATE INDEX idx_word_tags_tag ON word_tags(tag);拆表这件事纠结过。如果只看“显示一个单词详情”把释义和例句全塞进 words 表的一个 JSON 字符串字段确实是最快的查询都不用 JOIN。但往后你会发现想统计“哪些标签下没有音标的单词”、“某个释义出现的次数”、“例句最多的前五十个词”整存 JSON 的方案全都得靠 LIKE 匹配又慢又不准。对比一下两种方案在几个常见需求上的表现需求整存 JSON 字段主表 子表查标签含 gre 的单词LIKE %gre% 全表扫JOIN word_tags走索引按释义内容模糊搜索对整个 JSON 做 LIKE只对 definition 做 LIKE统计单词释义数量读出 JSON 解析GROUP BY word_id给单词追加例句改 JSON 数组INSERT 一条子表记录我选后者本质上是在为后续所有查询场景买单。4.2 容易忽略的字段细节大小写、唯一约束、来源标记建表时我一度想给word加上COLLATE NOCASE让 SQLite 在比较时忽略大小写“GRE” 和 “gre” 就自动算同一个单词。后来仔细想了下放弃了这个念头原因跟清洗时不用lower()去重是一样的英语单词里大小写可能造成语义分歧“us” 是宾格“US” 是国家缩写强制忽略大小写会把它们合并这是我不想要的结果。所以word字段的 UNIQUE 是精确匹配的大小写不同的词可以共存。另一个容易忽略的点是source字段。我当时觉得多个词库来源以后可能要混着导入所以在主表里加了source和level分别记录词库来源和难度等级。后来第二套词库导进来这个字段真的帮了大忙一条 SQL 就能把不同来源的词分开不至于混成一锅粥。如果你的项目也要做学习记录我建议后续再加一张learning_records表记录每个用户对每个单词的复习次数、下次复习时间不要现在就急着把一堆字段塞进 words 表。danci 3 的阶段先把干净的基础数据落库后面的业务表基于word_id再扩展完全来得及。5. 批量导入的正确姿势别再一条一条 INSERT 了5.1 逐条 INSERT 为什么慢一个简单的基准测试写导入脚本的时候我顺手做了一个很小的基准测试。同样是插入 4389 条 words 记录用最朴素的for循环 conn.execute()逐条 INSERT再逐条 commit本地 SQLite 跑了大概 17 秒。如果每次提交一个事务还要等磁盘把日志写下去这个时间还会往上走。换成executemany 最后一次性 commit4389 条只花了大概 0.6 秒。差距接近三十倍。原因不复杂逐条 INSERT 的时候每一条都要重新解析一遍 SQL 语句每一条都开一个隐式事务日志写入和磁盘同步的次数也翻了几十倍。数据库的设计目标本来就是处理大量数据批量提交才能发挥它的正常水平。所以结论很直接几千条数据你已经属于“批量导入”的范畴不要用循环去一条条怼。5.2 executemany 批量导入核心代码与执行思路我最终的导入脚本分成两步。第一步插入 words 主表拿到word到id的映射第二步根据映射插入 meanings、examples、word_tags 子表。import sqlite3 import json data json.load(open(clean_words.json, encodingutf-8)) conn sqlite3.connect(danci.db) cur conn.cursor() cur.executemany( INSERT OR IGNORE INTO words (word, phonetic, source, level) VALUES (?, ?, ?, ?), [ (item[word], item[phonetic], item[source], item[level]) for item in data ] ) conn.commit() # 建立 word - id 映射用于子表关联 cur.execute(SELECT id, word FROM words) word_id_map {row[1]: row[0] for row in cur.fetchall()} meaning_rows [] example_rows [] tag_rows [] for item in data: wid word_id_map.get(item[word]) if wid is None: continue for m in item.get(meanings, []): meaning_rows.append((wid, m.get(pos, ), m.get(def, ))) for e in item.get(examples, []): example_rows.append((wid, e.get(text, ), e.get(translation, ))) for tag in item.get(tags, []): tag_rows.append((wid, tag.lower())) cur.executemany( INSERT INTO meanings (word_id, pos, definition) VALUES (?, ?, ?), meaning_rows ) cur.executemany( INSERT INTO examples (word_id, sentence, translation) VALUES (?, ?, ?), example_rows ) cur.executemany( INSERT OR IGNORE INTO word_tags (word_id, tag) VALUES (?, ?), tag_rows ) conn.commit() conn.close()这里有一个 SQLite 和 MySQL 的差异要注意INSERT OR IGNORE是 SQLite 的处理重复方式换到 MySQL 就是INSERT IGNORE或者ON DUPLICATE KEY UPDATE。MySQL 8.0 之后旧的ON DUPLICATE KEY UPDATE wordVALUES(word)写法已经不建议用了官方推荐用别名写法。如果你在 AI Coding 工具的帮助下生成导入代码一定要先确认目标数据库类型否则语法直接报错。5.3 导入之后的验证数量、缺失、抽样三查导入完不能看到一句“导入成功”就算了我会从三个维度验证。第一是数量对得上。把清洗报告的数字和数据库里的聚合结果对照SELECT COUNT(*) FROM words; -- 期望值4389 SELECT COUNT(*) FROM meanings; SELECT COUNT(*) FROM examples; SELECT COUNT(*) FROM word_tags;第二是检查关键字段的缺失情况这一步能发现导入映射有没有写错SELECT COUNT(*) FROM words WHERE phonetic OR phonetic IS NULL; -- 期望值382 左右和清洗报告对得上 SELECT COUNT(*) FROM words w LEFT JOIN meanings m ON m.word_id w.id WHERE m.id IS NULL; -- 如果一个词没有任何释义说明清洗阶段处理有问题第三是抽样人工核对。我通常会随机抽十个单词在数据库里查出完整详情和原始 JSON 对照一遍确认释义、例句的对应关系没有错位。清洗和导入都是自动化脚本但最终数据准确性还是需要一双人眼来背书。6. AI Coding 帮我写了脚本也帮我挖了三个坑6.1 哪些环节我放心交给 AI Codingdanci 3 整个流程里AI Coding 工具确实帮我省了不少时间。最典型的是字段缺失率统计脚本这种“遍历文件、统计字段、输出报告”的确定性任务它生成的第一版就基本能跑。还有 pandas 管道代码dropna、drop_duplicates、fillna、astype这些方法名和参数我可能要翻文档确认AI 生成模板再改效率高很多。以及数据库方言的快速转换SQLite、MySQL 之间的语法细节让它补全也很省事。但这里有个很重要的前提AI 适合生成“代码模板”不适合替我做“业务决策”。比如“一个单词有两个完全不同的释义时怎么拆分成子表”这种问题AI 会给你一个看起来合理的答案但合不合理要我自己判断。6.2 三个翻车案例AI 不是保险箱第一个翻车是 BOM 编码。我让 AI 写读取 JSON 的脚本它第一版给的是encodingutf-8结果第一个单词的word字段变成了\ufeffabandon在数据库里怎么查都查不到。后来我把输出的字符串带\ufeff这个现象贴给 AI它才建议改用utf-8-sig。这个坑虽然不深但很隐蔽AI 不会主动知道你手里的文件带着 BOM。第二个翻车是去重策略。我让它“清洗单词列表并去重”它在代码里自作主张加了一句df[word] df[word].str.lower()把所有单词转小写之后再drop_duplicates。结果词库里的 “US” 和 “us” 被合并成了一个。从纯技术角度看这代码写得很漂亮但业务语义完全错了。我要的是精确去重它默认我应该忽略大小写这种“贴心”反而制造了脏数据。第三个翻车是 SQL 方言混淆。我当时在 SQLite 里跑它生成的 MySQL 导入代码执行到ON DUPLICATE KEY UPDATE直接报 syntax error。AI 生成了语法正确但不适用于当前环境的语句。解决办法是把它当成结对程序员明确告诉它“我用的数据库是 SQLite不要生成 MySQL 语法”它就能改成INSERT OR IGNORE。这个例子说明AI 生成的代码永远需要人来确认运行环境。6.3 和 AI 协作的正确姿势给上下文、贴报错、做 review踩过几次坑之后我摸索出一套对 AI Coding 工具比较有效的使用习惯。首先是给足上下文。一个合格的请求应该包含样例数据、期望输出、已知约束。比如我有一段 JSON 数据结构是贴一段真实数据样例。 我想统计每个字段的缺失率并且把 meanings 数组拍平成一行一个释义的 CSV。 注意有些条目的 meanings 可能是字符串。 请用 Python 写一个脚本。其次是报错信息原样贴回。AI 没有“看到程序运行状态”的能力它只能根据你提供的报错文本来猜。贴完整报错比说一千句“代码不行”都管用。而且贴回去之后要顺着它的思路追问源代码里哪一行出了问题不要只让它重写。最后是人工 review。AI 生成的代码尤其是数据处理部分的逻辑我会逐行读一遍重点看它有没有替我做业务判断。所有跟“哪些词算重复”“哪些字段可以丢弃”相关的逻辑必须由我确认之后才放行。说到底AI Coding 很擅长把我脑子里已经想清楚的逻辑快速变成代码但它不能替我把没想清楚的业务规则想清楚。7. 导入之后查询终于变成了一件轻松的事7.1 一条 SQL 解决过去要写 30 行代码的统计数据入库之后很多过去要写一堆 Python 逻辑的事情现在一条 SQL 就结束了。比如我最常用到的“统计每个标签下的单词数量”SELECT t.tag, COUNT(*) AS cnt FROM word_tags t GROUP BY t.tag ORDER BY cnt DESC LIMIT 10;再比如“找出 GRE 标签下缺失音标的单词”这种需求在 danci 1 阶段要遍历整个词库现在只要 JOIN 两张表SELECT w.word, w.phonetic FROM words w JOIN word_tags t ON t.word_id w.id WHERE t.tag gre AND (w.phonetic OR w.phonetic IS NULL) ORDER BY w.word LIMIT 20;查询速度基本是毫秒级SQLite 的索引对这种小体量数据完全够用。而且因为表结构拆分得干净后面想加词书、学习记录、复习计划都只需要往子表里加数据不需要动原来的结构。7.2 如果我再导一套词库会怎么做这次经验沉淀下来之后我导第二套词库的时候已经轻车熟路。原始 JSON 备份不动清洗脚本跑一遍输出中间文件再跑导入脚本入库最后对照清洗报告验证一遍。整个过程除了调一两个字段映射基本不用改代码二十分钟就能搞定。最花时间的永远不是导入脚本本身而是最初那 10% 的数据体检。花点时间把 JSON 里到底有哪些变体、哪些字段缺失、哪些重复规则搞清楚后面每一步都会顺很多。如果你也在做类似的词库导入我的建议是从字段统计开始不要从抄导入代码开始。数据脏成什么样你都没看过写出来的清洗脚本只会在一堆预料之外的脏数据面前崩溃。