Files
yanxuan/脚本/fix_all_tables.py

123 lines
6.1 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 -*-
"""按全表审计报告逐项修复。
三类处理,严格区分:
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()