113 lines
3.9 KiB
Python
113 lines
3.9 KiB
Python
# -*- 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()
|