Files

118 lines
6.3 KiB
Python
Raw Permalink Blame History

This file contains invisible Unicode characters
This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
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 -*-
"""定向清洗派生层的编码缺陷,每条改动写进 changelog;源 CSV 镜像表一律不碰。
为什么不碰 src_*:那是上游 CSV 的逐字镜像,改了就和源文件不一致,
verify_db 的行列一致性会红,也就失去了「每个数字能回溯到源文件」的前提。
镜像里的脏字符改在读取侧归一(align_chsi.nm / check_fee 已这样做),并在 README 记为已知边界。
修的三件事:
1 notices_all.body 存进了 PDF/ZIP/OLE 二进制(4 条)—— 附件已在盘,正文置空并标原因
2 notices_all / chsi_* / errata / fee_check 里的零宽与控制字符 —— 清掉,不动可见文字
3 键列全半角括号混用 —— 不改值,只在 sources.note 记一笔,关联靠归一化解决
"""
import os
import re
import sqlite3
import sys
import time
sys.stdout.reconfigure(encoding="utf-8", errors="replace")
HERE = os.path.dirname(os.path.abspath(__file__))
DB = os.path.join(HERE, "zexiao_2027.db")
INVIS = re.compile(r"[​-‏

­᠎]")
CTRL = re.compile(r"[\x00-\x08\x0b\x0c\x0e-\x1f\x7f]")
# 二进制正文的硬特征:PDF 对象头 / zip 条目 / OLE 复合文档头
BINARY = re.compile(r"<<\s*/[A-Za-z]|PK\x03\x04|\xd0\xcf\x11\xe0|/Type\s*/Catalog|obj\\?r\\?\\?")
TARGETS = [("notices_all", ("title", "body"), "notice_key"),
("chsi_dir", ("dwmc", "yxsmc", "zymc", "yjfxmc", "quota_raw"), "zyyjfxdm"),
("chsi_align", ("dwmc", "yxsmc", "zymc", "yjfxmc", "note"), "zyyjfxdm"),
("errata", ("school", "college", "sides", "reason", "evidence_src", "evidence_quote"), None),
("fee_check", ("school", "major", "plat_raw", "notice_fee", "blocked_reason"), None),
("sources", ("school", "note", "entry_url", "domain", "candidates",
"channel_url", "fetch_state"), "school"),
("att_fields", ("name", "value", "snippet", "src_file", "src_url"), None),
("files", ("path", "kind", "status"), None),
("chsi_school", ("dwmc", "szss"), "dwdm"),
("chsi_major", ("dwmc", "yxsmc", "zymc", "quota_raw"), None)]
def main():
con = sqlite3.connect(DB, timeout=180)
con.row_factory = sqlite3.Row
now = time.strftime("%Y-%m-%d %H:%M:%S")
con.execute("CREATE TABLE IF NOT EXISTS changelog(ts TEXT, script TEXT, object TEXT,"
" action TEXT, n_rows INTEGER, detail TEXT)")
stats = {"binary_cleared": 0, "invis_cells": 0, "ctrl_cells": 0, "paren_keys": 0}
# 1) 二进制正文
rows = list(con.execute("SELECT notice_key, body, title, school, url FROM notices_all"
" WHERE body IS NOT NULL"))
for r in rows:
# 判据就用替换符本身:正文出现 U+FFFD 说明这段是解码失败的字节流(PDF/ZIP/OLE 被当文字读),
# 比按魔数猜可靠——附件已单独存档,正文列没有保留它的理由。
if "\ufffd" not in (r["body"] or ""):
continue
con.execute("UPDATE notices_all SET body=NULL WHERE notice_key=?", (r["notice_key"],))
con.execute("INSERT INTO changelog VALUES(?,?,?,?,?,?)", (
now, "fix_encoding.py", "notices_all.body", "clear_binary_body", 1,
"notice_key=%s|%s|正文含替换符,是解码失败的字节流(附件已单独存档,正文列不保留)" % (
r["notice_key"], (r["school"] or "")[:20])))
stats["binary_cleared"] += 1
# 2) 不可见与控制字符
names = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}
for tb, cols, keycol in TARGETS:
if tb not in names:
continue
have = {r[1] for r in con.execute("PRAGMA table_info(%s)" % tb)}
for col in cols:
if col not in have:
continue
kc = keycol if keycol in have else "rowid"
for r in con.execute('SELECT "%s" v, "%s" k FROM %s WHERE "%s" IS NOT NULL' % (col, kc, tb, col)):
s = r["v"]
if not isinstance(s, str):
continue
nv = CTRL.sub("", INVIS.sub("", s))
if nv == s:
continue
n_i = len(INVIS.findall(s))
n_c = len(CTRL.findall(s))
con.execute('UPDATE %s SET "%s"=? WHERE "%s"=?' % (tb, col, kc), (nv, r["k"]))
stats["invis_cells"] += bool(n_i)
stats["ctrl_cells"] += bool(n_c)
con.execute("INSERT INTO changelog VALUES(?,?,?,?,?,?)", (
now, "fix_encoding.py", "%s.%s" % (tb, col), "strip_invisible", 1,
"行键=%s;去零宽 %d 个、控制字符 %d 个(可见文字未改)" % (str(r["k"])[:26], n_i, n_c)))
# 3) 键列括号混用:只登记,不改值
for tb, cols, keycol in (("src_master", ("专业名称", "学院"), None), ("chsi_dir", ("zymc",), "zyyjfxdm")):
names = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}
if tb not in names:
continue
for col in cols:
q = 'SELECT DISTINCT "%s" v FROM %s WHERE "%s" LIKE \'%%(%%\' AND "%s" LIKE \'%%(%%\'' % (
col, tb, col, col)
try:
for (v,) in con.execute(q):
stats["paren_keys"] += 1
con.execute("INSERT INTO changelog VALUES(?,?,?,?,?,?)", (
now, "fix_encoding.py", "%s.%s" % (tb, col), "note_mixed_paren", 1,
"值=%s(全半角括号混用;不改源值,关联侧统一 NFKC 归一)" % (v or "")[:60]))
except sqlite3.OperationalError:
pass
con.commit()
n_ch = con.execute("SELECT COUNT(*) FROM changelog WHERE script='fix_encoding.py'").fetchone()[0]
left = con.execute("SELECT COUNT(*) FROM notices_all WHERE body LIKE '%' || char(0xFFFD) || '%'").fetchone()[0]
print("清洗完成:二进制正文置空 %d 条;去不可见字符 %d 格;去控制字符 %d 格;"
"键列括号混用登记 %d 处(不改值)" % (
stats["binary_cleared"], stats["invis_cells"], stats["ctrl_cells"], stats["paren_keys"]))
print("changelog 新增 %d 条;库里仍含替换符的正文 %d 条(应为 0)" % (n_ch, left))
con.close()
if __name__ == "__main__":
main()