import_resumes.py 2.63 KB
#!/usr/bin/env python3
"""从JSONL导入简历到 resumes.db

用法:
  python3 import_resumes.py
  python3 import_resumes.py --jsonl /sdcard/bosszhipin/data/xiwen_resumes.jsonl --db /sdcard/bosszhipin/data/resumes.db
"""
import sqlite3, json, datetime, argparse

def main():
    parser = argparse.ArgumentParser(description='导入简历到SQLite')
    parser.add_argument('--jsonl', default='/sdcard/bosszhipin/data/xiwen_resumes.jsonl')
    parser.add_argument('--db', default='/sdcard/bosszhipin/data/resumes.db')
    args = parser.parse_args()

    db = sqlite3.connect(args.db)
    db.row_factory = sqlite3.Row

    db.executescript('''
    CREATE TABLE IF NOT EXISTS resumes (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        friendId TEXT NOT NULL,
        name TEXT,
        jobPosition TEXT,
        jobId TEXT,
        age TEXT,
        education TEXT,
        gender TEXT,
        applyStatus TEXT,
        city TEXT,
        expSalary TEXT,
        workAge TEXT,
        firstText TEXT,
        secondText TEXT,
        positionName TEXT,
        jobSalary TEXT,
        status TEXT DEFAULT NULL,
        experiences TEXT,
        raw_json TEXT,
        imported_at TEXT,
        UNIQUE(friendId, jobId)
    );
    ''')

    with open(args.jsonl, 'r', encoding='utf-8') as f:
        lines = f.readlines()

    now = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    count = 0
    for line in lines:
        r = json.loads(line)
        exps = json.dumps(r.get('experiences', []), ensure_ascii=False)
        raw = json.dumps(r, ensure_ascii=False)
        vals = [r['friendId'], r['name'], r['jobPosition'], r['jobId'],
                r['age'], r['education'], r.get('gender'), r['applyStatus'], r['city'],
                r['expSalary'], r['workAge'], r['firstText'], r['secondText'],
                r['positionName'], r['jobSalary'], exps, raw, now]
        # ⚠️ 占位符 ? 个数必须 = 列数 = 18。用单行 SQL 避免肉眼数错。
        sql = "INSERT INTO resumes (friendId,name,jobPosition,jobId,age,education,gender,applyStatus,city,expSalary,workAge,firstText,secondText,positionName,jobSalary,experiences,raw_json,imported_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        assert sql.count('?') == len(vals) == 18, f"placeholder mismatch: {sql.count('?')} vs {len(vals)}"
        try:
            db.execute(sql, vals)
            count += 1
        except sqlite3.IntegrityError:
            pass

    db.commit()
    total = db.execute('SELECT count(*) FROM resumes').fetchone()[0]
    print(f'导入完成: {count} 份')
    print(f'表里总数: {total}')
    db.close()

if __name__ == '__main__':
    main()