118 lines
6.3 KiB
Python
118 lines
6.3 KiB
Python
# -*- 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()
|