Files
yanxuan/数据库/ingest_qoder_plan.py

113 lines
3.9 KiB
Python
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
# -*- coding: utf-8 -*-
"""qoder 镜像「计划招生」字段(132,124 行)→ 主库低可信层 qoder_plan_enroll
决策(2026-10-20 拍板):qoder 只入「计划招生」字段到独立低可信层,
标注 third_party_rendered + year_unconfirmed,不入核心事实层。
- 源:rows_catalog.jsonl(1,241,475 行,10 种 field_name)
- 仅取 field_name='计划招生'(132,124 行)
- field_value 格式统一为「专业:N(不含推免)」/「研究方向:N(不含推免)」,解析出
plan_scope(专业/研究方向) + plan_count(N) + 备注(不含推免)
"""
import json
import os
import re
import sqlite3
import sys
import time
SRC = r'D:\择校\staging\运行批次\parallel_sources\qoder_bulk_source_hunt_2022_2024_structured_20260927\parsed\hf_mirror\rows_catalog.jsonl'
ZX = r'D:\择校\数据库\zexiao_2027.db'
TAB = 'qoder_plan_enroll'
BATCH = 10000
COLS = [
'row_pk', 'school_name', 'school_code', 'major_code', 'major_name',
'plan_scope', 'plan_count', 'field_value', 'year', 'authenticity_status',
'source_id', 'raw_rel', 'row_no', '_ingested_at',
]
PAT = re.compile(r'^(专业|研究方向):(\d+)')
def main():
if not os.path.exists(SRC):
sys.exit('源不存在: ' + SRC)
t0 = time.time()
z = sqlite3.connect(ZX, timeout=300)
z.execute('PRAGMA synchronous=NORMAL')
z.execute('PRAGMA cache_size=-131072')
z.execute('PRAGMA temp_store=MEMORY')
z.execute(f'DROP TABLE IF EXISTS {TAB}')
ddl = 'CREATE TABLE %s (\n %s\n)' % (
TAB,
',\n '.join('"%s" TEXT' % c for c in COLS[:-1]) + ',\n "_ingested_at" TEXT')
z.execute(ddl)
z.execute(f'CREATE UNIQUE INDEX IF NOT EXISTS ux_{TAB}_pk ON {TAB}("row_pk")')
for c in ['major_code', 'school_name']:
z.execute(f'CREATE INDEX IF NOT EXISTS ix_{TAB}_{c} ON {TAB}("{c}")')
ins = ('INSERT OR REPLACE INTO %s ("%s") VALUES (%s)'
% (TAB, '","'.join(COLS), ','.join(['?'] * len(COLS))))
buf = []
n = 0
n_skip = 0
with open(SRC, encoding='utf-8') as f:
for line in f:
d = json.loads(line)
if d.get('field_name') != '计划招生':
n_skip += 1
continue
fv = d.get('field_value') or ''
m = PAT.match(fv)
scope = m.group(1) if m else ''
cnt = m.group(2) if m else ''
raw_rel = (d.get('raw_rel') or '').strip()
row_no = str(d.get('row_no') or '').strip()
row_pk = raw_rel + '#' + row_no
vals = [
row_pk,
(d.get('school_name') or '').strip(),
(d.get('school_code') or '').strip(),
(d.get('major_code') or '').strip(),
(d.get('major_name') or '').strip(),
scope,
cnt,
fv.strip(),
(d.get('year') or '').strip(),
(d.get('authenticity_status') or '').strip(),
(d.get('source_id') or '').strip(),
raw_rel,
row_no,
None,
]
buf.append(tuple(vals))
n += 1
if len(buf) >= BATCH:
z.executemany(ins, buf)
z.commit()
buf = []
if buf:
z.executemany(ins, buf)
z.commit()
tgt = z.execute(f'SELECT COUNT(*) FROM {TAB}').fetchone()[0]
print('入库完成:计划招生 %d 行(跳过 %d)→ 表 %d 行 耗时 %.0fs'
% (n, n_skip, tgt, time.time() - t0))
for row in z.execute(
f'SELECT plan_scope, COUNT(*) FROM {TAB} GROUP BY 1').fetchall():
print(' plan_scope:', row)
c = z.execute(
f'SELECT COUNT(*) FROM {TAB} WHERE "plan_count" != \'\'').fetchone()[0]
print(' 解析出数字的计划招生:', c)
z.execute('PRAGMA wal_checkpoint(TRUNCATE)')
z.close()
if __name__ == '__main__':
main()