mirror of
https://git.simgreek.com/zzz/yanxuan.git
synced 2026-11-23 00:40:46 +08:00
113 lines
4.8 KiB
Python
113 lines
4.8 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""全量修复 v2:处理肉眼审查发现的字段级问题。
|
||
|
||
1. dir_hist:324 行"名称=代码"且同键已存在正确名称 → 删除重复;其余回填名称
|
||
2. pd_dir_detail.duration:118 行混入备注长文本 → 提取主学制,原文移入 remark
|
||
3. 输出修复前后对照,可回溯
|
||
"""
|
||
import re
|
||
import sqlite3
|
||
|
||
DB = r'D:\择校\数据库\zexiao_2027.db'
|
||
con = sqlite3.connect(DB, timeout=600)
|
||
con.execute('PRAGMA synchronous=OFF')
|
||
|
||
# ---------- 1. dir_hist ----------
|
||
print('=== 1. dir_hist 名称=代码 %d 行 ==='
|
||
% con.execute('SELECT COUNT(*) FROM dir_hist WHERE 专业名称=专业代码').fetchone()[0])
|
||
|
||
dup = con.execute('''SELECT COUNT(*) FROM dir_hist a WHERE a.专业名称=a.专业代码 AND EXISTS(
|
||
SELECT 1 FROM dir_hist b WHERE b.年份=a.年份 AND b.院校=a.院校
|
||
AND b.专业代码=a.专业代码 AND b.专业名称<>a.专业名称)''').fetchone()[0]
|
||
con.execute('''DELETE FROM dir_hist WHERE 专业名称=专业代码 AND EXISTS(
|
||
SELECT 1 FROM dir_hist b WHERE b.年份=dir_hist.年份 AND b.院校=dir_hist.院校
|
||
AND b.专业代码=dir_hist.专业代码 AND b.专业名称<>dir_hist.专业名称)''')
|
||
con.commit()
|
||
print(' 删除重复行 %d' % dup)
|
||
|
||
NAME = {}
|
||
for r in con.execute("SELECT c_code, major_name FROM ggyx_dim_major WHERE IFNULL(c_code,'')<>''"):
|
||
NAME.setdefault(r[0], (r[1] or '').split('/')[-1] if r[1] else '')
|
||
for r in con.execute('SELECT zydm, zymc FROM chsi_2027_dir GROUP BY zydm'):
|
||
NAME.setdefault(r[0], r[1] or '')
|
||
|
||
rest = con.execute('SELECT rowid, 专业代码 FROM dir_hist WHERE 专业名称=专业代码').fetchall()
|
||
ok = clash = miss = 0
|
||
for rid, code in rest:
|
||
nm = NAME.get(code)
|
||
if not nm:
|
||
miss += 1
|
||
continue
|
||
try:
|
||
con.execute('UPDATE dir_hist SET 专业名称=? WHERE rowid=?', (nm, rid))
|
||
ok += 1
|
||
except sqlite3.IntegrityError:
|
||
clash += 1
|
||
con.commit()
|
||
print(' 回填成功 %d,撞唯一键 %d,无名称可查 %d' % (ok, clash, miss))
|
||
|
||
# 撞键的属于同键已存在,删除
|
||
if clash:
|
||
con.execute('''DELETE FROM dir_hist a WHERE a.专业名称=a.专业代码 AND EXISTS(
|
||
SELECT 1 FROM dir_hist b WHERE b.年份=a.年份 AND b.院校=a.院校
|
||
AND b.专业代码=a.专业代码 AND b.专业名称<>a.专业名称)''')
|
||
con.commit()
|
||
print(' 剩余名称=代码:%d'
|
||
% con.execute('SELECT COUNT(*) FROM dir_hist WHERE 专业名称=专业代码').fetchone()[0])
|
||
|
||
# 科目错位 23 行
|
||
bad2 = con.execute("SELECT rowid, 专业代码 FROM dir_hist WHERE 专业名称 LIKE '%①%' OR 专业名称 LIKE '%政治理论%'").fetchall()
|
||
f2 = 0
|
||
for rid, code in bad2:
|
||
nm = NAME.get(code)
|
||
if nm:
|
||
try:
|
||
con.execute('UPDATE dir_hist SET 专业名称=? WHERE rowid=?', (nm, rid))
|
||
f2 += 1
|
||
except sqlite3.IntegrityError:
|
||
con.execute('DELETE FROM dir_hist WHERE rowid=?', (rid,))
|
||
con.commit()
|
||
print(' 科目错位 %d 行:回填 %d' % (len(bad2), f2))
|
||
|
||
# ---------- 2. duration 备注混入 ----------
|
||
print()
|
||
print('=== 2. pd_dir_detail.duration 长文本 ===')
|
||
rows = con.execute('''SELECT id, duration, remark FROM pd_dir_detail
|
||
WHERE LENGTH(IFNULL(duration,''))>10''').fetchall()
|
||
print(' 待处理 %d 行' % len(rows))
|
||
fixed = 0
|
||
samples = []
|
||
for i, dur, rem in rows:
|
||
m = re.match(r'^\s*(\d+(?:\.\d+)?)', dur or '')
|
||
if not m:
|
||
# 完全不是学制(纯备注)→ 清空 duration,原文进 remark
|
||
newd, lo, hi = '', None, None
|
||
else:
|
||
newd = m.group(1)
|
||
lo = hi = float(newd)
|
||
newrem = rem if (rem or '').strip() else dur
|
||
con.execute('''UPDATE pd_dir_detail SET duration=?, remark=?, duration_min=?, duration_max=?,
|
||
duration_kind=? WHERE id=?''',
|
||
(newd, newrem, lo, hi, 'single' if lo else 'note_only', i))
|
||
fixed += 1
|
||
if len(samples) < 6:
|
||
samples.append((dur[:50], newd, (newrem or '')[:40]))
|
||
con.commit()
|
||
print(' 已修正 %d 行,样本:' % fixed)
|
||
for a, b, c in samples:
|
||
print(' %-52r → duration=%r remark=%r' % (a, b, c))
|
||
|
||
# ---------- 3. 验证 ----------
|
||
print()
|
||
print('=== 3. 修复后验证 ===')
|
||
for sql, label in [
|
||
('SELECT COUNT(*) FROM dir_hist WHERE 专业名称=专业代码', 'dir_hist 名称=代码(应0)'),
|
||
("SELECT COUNT(*) FROM dir_hist WHERE 专业名称 LIKE '%①%'", 'dir_hist 科目错位(应0)'),
|
||
('SELECT COUNT(*) FROM pd_dir_detail WHERE LENGTH(IFNULL(duration,""))>10', 'duration 长文本(应0)'),
|
||
("SELECT COUNT(*) FROM pd_dir_detail WHERE duration_kind='note_only'", '纯备注型(duration已清空)'),
|
||
('SELECT COUNT(*) FROM dir_hist', 'dir_hist 当前总行数'),
|
||
('SELECT COUNT(*) FROM pd_dir_detail', 'pd_dir_detail 当前总行数'),
|
||
]:
|
||
print(' %-32s %s' % (label, con.execute(sql).fetchone()[0]))
|
||
con.close()
|