mzl92
96 lines
2.7 KiB
Markdown
Executable File
96 lines
2.7 KiB
Markdown
Executable File
# 数据迁移：JSON 文件 → SQLite 数据库
|
||
|
||
## 迁移时间
|
||
2025年，从单人 JSON 文件存储迁移到 SQLite 数据库。
|
||
|
||
## 源结构（旧）
|
||
|
||
```
|
||
~/.hermes/data/recruitment/
|
||
├── candidates/
|
||
│ ├── a1b2c3d4e5f6.json # 单人文件，文件名 = fingerprint(MD5前12位)
|
||
│ └── ...
|
||
└── candidates_index.json # 索引文件，key = fingerprint
|
||
```
|
||
|
||
单人 JSON 文件结构：
|
||
```json
|
||
{
|
||
"fingerprint": "a1b2c3d4e5f6",
|
||
"name": "张女士",
|
||
"age": 25,
|
||
"education": "本科",
|
||
"experience": "3年经验",
|
||
"school": "郑州经贸学院",
|
||
"expected_city": "郑州",
|
||
"expected_salary": "5千-6千",
|
||
"score": 22,
|
||
"score_level": "待定",
|
||
"greeted": false,
|
||
"raw_text": "...",
|
||
"education_detail": "...",
|
||
"work_detail": "...",
|
||
"scraped_at": "2025-01-15 14:30:00"
|
||
}
|
||
```
|
||
|
||
## 目标结构（新）
|
||
|
||
SQLite 数据库 `recruitment.db`，三表结构（见 `database-schema.md`）。
|
||
|
||
## 迁移脚本要点
|
||
|
||
```python
|
||
import sqlite3, json, os, glob
|
||
|
||
db = sqlite3.connect('recruitment.db')
|
||
# 创建表（见 database-schema.md）
|
||
|
||
candidates_dir = os.path.expanduser('~/.hermes/data/recruitment/candidates')
|
||
for fp_file in glob.glob(os.path.join(candidates_dir, '*.json')):
|
||
with open(fp_file, 'r', encoding='utf-8') as f:
|
||
data = json.load(f)
|
||
|
||
db.execute("""
|
||
INSERT OR REPLACE INTO candidates
|
||
(id, name, age, education, experience, school, expected_city,
|
||
expected_salary, score, score_level, greeted, raw_text,
|
||
education_detail, work_detail, scraped_at)
|
||
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
|
||
""", (
|
||
data['fingerprint'], data['name'], data.get('age'),
|
||
data.get('education'), data.get('experience'), data.get('school'),
|
||
data.get('expected_city'), data.get('expected_salary'),
|
||
data.get('score'), data.get('score_level'),
|
||
1 if data.get('greeted') else 0,
|
||
data.get('raw_text', ''), data.get('education_detail', ''),
|
||
data.get('work_detail', ''), data.get('scraped_at', '')
|
||
))
|
||
|
||
db.commit()
|
||
```
|
||
|
||
## 迁移结果
|
||
|
||
- 迁移了 84 条候选人记录
|
||
- 分布：6人推荐，52人待定，26人不推荐
|
||
- 旧 JSON 文件保留在 `candidates/` 目录作为备份
|
||
|
||
## 反向导出（SQLite → JSON）
|
||
|
||
如需从数据库导出为单人 JSON 文件：
|
||
|
||
```python
|
||
import sqlite3, json, os
|
||
db = sqlite3.connect('recruitment.db')
|
||
db.row_factory = sqlite3.Row
|
||
out_dir = 'candidates'
|
||
os.makedirs(out_dir, exist_ok=True)
|
||
|
||
for row in db.execute("SELECT * FROM candidates"):
|
||
d = dict(row)
|
||
fp = d.pop('id')
|
||
with open(os.path.join(out_dir, f'{fp}.json'), 'w', encoding='utf-8') as f:
|
||
json.dump(d, f, ensure_ascii=False, indent=2, default=str)
|
||
```
|