123 lines
6.1 KiB
Python
123 lines
6.1 KiB
Python
# -*- coding: utf-8 -*-
|
||
"""按全表审计报告逐项修复。
|
||
|
||
三类处理,严格区分:
|
||
A 真修(安全,可回滚) 空格 trim、全角数字转半角
|
||
B 标记(不改原值) 数值越界行加 flag,供下游过滤
|
||
C 登记(不改数据) 字段语义误判写入 dim_table_meta,避免下次再误报
|
||
"""
|
||
import re
|
||
import sqlite3
|
||
|
||
DB = r'D:\择校\数据库\zexiao_2027.db'
|
||
con = sqlite3.connect(DB, timeout=600)
|
||
con.execute('PRAGMA synchronous=OFF')
|
||
|
||
FULLWIDTH = str.maketrans('0123456789', '0123456789')
|
||
report = []
|
||
|
||
# ================= A1. 代码字段去空格 =================
|
||
print('=== A1 去空格 ===')
|
||
for t, col in [('dir_hist', '院校代码'), ('ggyx_ssd_master', 'school_code')]:
|
||
before = con.execute('SELECT COUNT(*) FROM "%s" WHERE "%s"<>TRIM("%s") AND IFNULL("%s",\'\')<>\'\''
|
||
% (t, col, col, col)).fetchone()[0]
|
||
con.execute('UPDATE "%s" SET "%s"=TRIM("%s") WHERE "%s"<>TRIM("%s")' % (t, col, col, col, col))
|
||
con.commit()
|
||
after = con.execute('SELECT COUNT(*) FROM "%s" WHERE "%s"<>TRIM("%s") AND IFNULL("%s",\'\')<>\'\''
|
||
% (t, col, col, col)).fetchone()[0]
|
||
print(' %-24s %-14s %d → %d' % (t, col, before, after))
|
||
report.append(['A1', '去空格', '%s.%s' % (t, col), before, after])
|
||
|
||
# 验证修完是否满足 5 位
|
||
for t, col in [('dir_hist', '院校代码'), ('ggyx_ssd_master', 'school_code')]:
|
||
bad = con.execute('''SELECT COUNT(*) FROM "%s" WHERE IFNULL("%s",'')<>'' AND LENGTH("%s")<>5'''
|
||
% (t, col, col)).fetchone()[0]
|
||
print(' 仍非 5 位:%d' % bad)
|
||
|
||
# ================= A2. 全角数字转半角 =================
|
||
print()
|
||
print('=== A2 全角数字 ==="')
|
||
n = 0
|
||
for t, col in [('core_outlines', 'code'), ('core_school_lines', 'code')]:
|
||
rows = con.execute('''SELECT rowid,"%s" FROM "%s" WHERE "%s" GLOB '*[0-9]*' ''' % (col, t, col)).fetchall()
|
||
for rid, v in rows:
|
||
con.execute('UPDATE "%s" SET "%s"=? WHERE rowid=?' % (t, col), (v.translate(FULLWIDTH), rid))
|
||
n += 1
|
||
con.commit()
|
||
print(' 修正 %d 行' % n)
|
||
report.append(['A2', '全角转半角', 'core_outlines/code + core_school_lines/code', n, 0])
|
||
|
||
# ================= B. 数值异常标记 =================
|
||
print()
|
||
print('=== B 数值异常标记(不改原值)===')
|
||
|
||
|
||
def flag(table, cond, flag_name, note):
|
||
cols = [r[1] for r in con.execute('PRAGMA table_info("%s")' % table)]
|
||
if 'data_flag' not in cols:
|
||
con.execute('ALTER TABLE "%s" ADD COLUMN data_flag TEXT' % table)
|
||
n = con.execute('UPDATE "%s" SET data_flag=IFNULL(data_flag||"|","")||? WHERE %s' % (table, cond),
|
||
(flag_name,)).rowcount
|
||
con.commit()
|
||
print(' %-26s %-28s %d 行 %s' % (table, flag_name, n, note))
|
||
report.append(['B', flag_name, table, n, ''])
|
||
return n
|
||
|
||
|
||
flag('fact_admission', 'score > 500', 'score_gt_500', '推免/加分或录入错误,超总分上限')
|
||
flag('ggyx_a_exam_score', 'CAST(score AS REAL) > 500', 'score_gt_500', '初试总分 522(同专业英语录成132)')
|
||
flag('core_hist_lines', 'plan < 0', 'plan_negative', '计划数为负,疑为扩招/缩招差值误存')
|
||
flag('ggyx_a_st_source', 'num < 0', 'num_negative', '占比为负,百分比计算错误')
|
||
|
||
# ================= C. 字段语义登记(防再误报) =================
|
||
print()
|
||
print('=== C 字段语义登记 ===')
|
||
MISJUDGE = [
|
||
('core_outlines', 'code', '科目代码(801/807/100 等 3 位),非专业代码'),
|
||
('core_school_lines', 'code', '门类代码(08/12 等 2 位),非专业代码'),
|
||
('ggyx_fact_raw2', 'code', 'HTTP 状态码(200),非专业代码'),
|
||
('ggyx_endpoint_manifest', 'code', 'HTTP 状态码(200/-1/500),非专业代码'),
|
||
('ggyx_dim_major_class', 'code', '门类代码(2 位)'),
|
||
('ggyx_dim_xkpg', 'major_id', '存的是学科评估学科代码,非平台 major_id'),
|
||
('qoder_plan_enroll', 'year', "全部为 '未确认',该表不可用于按年分析"),
|
||
('qoder_plan_enroll', 'school_code', "全部为 '未确认',无院校码"),
|
||
('core_majors', '*', '早期宽表,20+ 字段空值率 99.94%,已被 ggyx_* 事实表取代,建议 deprecated'),
|
||
('core_official_0856', '*', '同上(附件类字段 100% 空)'),
|
||
('fee_check', '*', '同上'),
|
||
('src_target_major_checked', '*', '同上'),
|
||
]
|
||
con.execute('''CREATE TABLE IF NOT EXISTS dim_field_semantics (
|
||
table_name TEXT, field_name TEXT, real_meaning TEXT, note TEXT,
|
||
created_at TEXT DEFAULT (datetime('now','localtime')),
|
||
PRIMARY KEY (table_name, field_name))''')
|
||
for t, f, meaning in MISJUDGE:
|
||
con.execute('INSERT OR REPLACE INTO dim_field_semantics (table_name, field_name, real_meaning, note) VALUES (?,?,?,?)',
|
||
(t, f, meaning, '全表审计复核'))
|
||
con.commit()
|
||
print(' 登记 %d 条字段语义' % len(MISJUDGE))
|
||
|
||
# 早期宽表标 deprecated
|
||
for t in ('core_majors', 'core_official_0856', 'fee_check', 'src_target_major_checked'):
|
||
con.execute('''UPDATE dim_table_meta SET purpose = IFNULL(purpose,'')||' [DEPRECATED 宽表遗留]'
|
||
WHERE table_name=? AND IFNULL(purpose,'') NOT LIKE '%DEPRECATED%' ''', (t,))
|
||
con.commit()
|
||
|
||
# ================= 验证 =================
|
||
print()
|
||
print('=== 修复后验证 ===')
|
||
checks = [
|
||
('dir_hist 院校码非5位', 'SELECT COUNT(*) FROM dir_hist WHERE IFNULL(院校代码,\'\')<>\'\' AND LENGTH(院校代码)<>5'),
|
||
('ssd_master 院校码非5位', 'SELECT COUNT(*) FROM ggyx_ssd_master WHERE IFNULL(school_code,\'\')<>\'\' AND LENGTH(school_code)<>5'),
|
||
('core_outlines 全角', "SELECT COUNT(*) FROM core_outlines WHERE code GLOB '*[0-9]*'"),
|
||
('未标记的数值异常',
|
||
'''SELECT (SELECT COUNT(*) FROM fact_admission WHERE score>500 AND IFNULL(data_flag,'')='')
|
||
+ (SELECT COUNT(*) FROM core_hist_lines WHERE plan<0 AND IFNULL(data_flag,'')='')
|
||
+ (SELECT COUNT(*) FROM ggyx_a_st_source WHERE num<0 AND IFNULL(data_flag,'')='')'''),
|
||
]
|
||
for label, sql in checks:
|
||
print(' %-24s %s' % (label, con.execute(sql).fetchone()[0]))
|
||
|
||
print()
|
||
print('字段语义登记表 %d 条' % con.execute('SELECT COUNT(*) FROM dim_field_semantics').fetchone()[0])
|
||
con.close()
|