Files
yanxuan/脚本/fix_normalize2.py

113 lines
4.8 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 -*-
"""全量修复 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()