| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294 |
- """单文件流式入库:CSV/XLSX → 本地 SQLite 工作库(无需任何数据库服务)。
- 入库阶段完成:
- * 字段别名归一(支持中文表头);
- * 范围校验:10 位 HS、进出口方向、截止日期、ISO 年周合法性;
- * 周四归月:整周记入“周四年份-月份”,不拆分跨月周;
- * 明细审计:原始行、解析结果、数值异常全部落表,便于复核。
- 聚合表 ``monthly`` 是全流程唯一的取数入口,所有报表都从它派生,
- 因此“同一批记录、不同视图”的口径可以被显式表达。
- """
- from __future__ import annotations
- import csv
- import hashlib
- import json
- import sqlite3
- import re
- import unicodedata
- from collections import Counter
- from ...source import iter_source
- from ...query_indexes import create_monthly_indexes
- from collections.abc import Callable, Iterator
- from datetime import date
- from pathlib import Path
- from .numbers import StrictSum, number
- from .rules import MEMORY_HS, Rules
- #: fact 表维度列(顺序即主键顺序)
- DIMENSIONS = [
- "direction",
- "year",
- "month",
- "c1",
- "c2",
- "c3",
- "c4",
- "brand",
- "model",
- "brand_country",
- "app",
- "origin",
- "trade_country",
- "dispatch",
- "port",
- "port_country",
- "mapped",
- ]
- #: 必需字段
- REQUIRED = ["I_E_FLAG", "HS_CODE", "YEAR", "WEEK", "MONORSUM", "COUNT", "G_MODEL"]
- #: 中文/别名表头 → 规范字段名
- ALIASES = {'MONORSUM': 'MONORSUM', '金额': 'MONORSUM', '金额(美元)': 'MONORSUM', '金额(美元)': 'MONORSUM', '金额:美元': 'MONORSUM', '美元金额': 'MONORSUM', '成交金额': 'MONORSUM', '总金额': 'MONORSUM', '进出口': 'I_E_FLAG', '进/出口': 'I_E_FLAG', '进出口标志': 'I_E_FLAG', '进口/出口': 'I_E_FLAG', '进出口类型': 'I_E_FLAG', 'HS编码': 'HS_CODE', 'HS 编码': 'HS_CODE', '商品编码': 'HS_CODE', '商品编号': 'HS_CODE', '海关编码': 'HS_CODE', '税则号列': 'HS_CODE', '商品编码(10位)': 'HS_CODE', '年份': 'YEAR', '年度': 'YEAR', '周': 'WEEK', '周数': 'WEEK', 'ISO周': 'WEEK', '统计周': 'WEEK', '数量': 'COUNT', '成交数量': 'COUNT', '数量(成交)': 'COUNT', '数量(成交)': 'COUNT', '品牌/型号': 'G_MODEL', '品牌型号': 'G_MODEL', '申报要素': 'G_MODEL', '规格型号': 'G_MODEL', '原产国': 'ORIGIN_COUNTRY', '原产国(地区)': 'ORIGIN_COUNTRY', '贸易国': 'TRADE_AREA_CODE', '贸易国(地区)': 'TRADE_AREA_CODE', '启运国': 'TRADE_COUNTRY', '启运国(地区)': 'TRADE_COUNTRY', '经停港/指运港': 'DISTINATE_PORT', '经停港': 'DISTINATE_PORT', '指运港': 'DISTINATE_PORT', '目的国': 'DESTINATION_COUNTRY', '境内目的地/境内货源地': 'DISTRICT_CODE', '境内目的地': 'DISTRICT_CODE', '贸易方式': 'TRADE_MODE', '申报地海关': 'DECLARING_CUSTOMS', '运输方式': 'TRAF_MODE', '包装种类': 'PACKING_TYPE', 'MONORSUM_USD': 'MONORSUM'}
- Progress = Callable[[str, float], None]
- def connect(path: str | Path) -> sqlite3.Connection:
- database = sqlite3.connect(str(path))
- database.row_factory = sqlite3.Row
- database.create_aggregate("dsum", 1, StrictSum)
- database.execute("PRAGMA temp_store=FILE")
- database.execute("PRAGMA cache_size=-65536")
- return database
- def sha256_file(path: str | Path, chunk: int = 1 << 20) -> str:
- digest = hashlib.sha256()
- with Path(path).open("rb") as handle:
- for block in iter(lambda: handle.read(chunk), b""):
- digest.update(block)
- return digest.hexdigest()
- def _iter_source_rows(source: Path, encoding: str, delimiter: str):
- # Retained for callers using the engine's previous reader helper.
- for row_id, end_line, headers, values in iter_source(source, encoding, delimiter):
- yield end_line, headers, values
- def normalize_header(value):
- return unicodedata.normalize("NFKC", str(value or "")).replace("\ufeff", "").replace("\u200b", "").strip()
- def normalize_code(value):
- text = normalize_header(value)
- # Excel may export integer code cells with a .0 suffix. No rounding or padding.
- if re.fullmatch(r"[0-9]+\.0+", text):
- text = text.split('.')[0]
- return text
- def ingest(config: dict, rules: Rules, out: Path, progress: Progress | None = None) -> tuple[sqlite3.Connection, dict]:
- """把源文件流式写入 ``working.sqlite``,返回连接与运行统计。"""
- def report(message: str, fraction: float) -> None:
- if progress:
- progress(message, fraction)
- source = Path(config["input_csv"])
- database = connect(out / "working.sqlite")
- database.execute("CREATE TABLE raw(row_id INTEGER PRIMARY KEY, end_line INTEGER, original_json TEXT, status TEXT)")
- columns = ",".join(f"{name} TEXT" for name in DIMENSIONS)
- database.execute(f"CREATE TABLE fact(row_id INTEGER,{columns},amount TEXT,quantity TEXT)")
- database.execute("CREATE TABLE audit(row_id INTEGER, hs TEXT, raw_model TEXT, parsed_json TEXT)")
- database.execute("CREATE TABLE issues(row_id INTEGER, field TEXT, raw_value TEXT, reason TEXT)")
- cutoff = date.fromisoformat(config["cutoff_date"]) if config.get("cutoff_date") else date.today()
- stats: dict = {
- "total": 0,
- "included": 0,
- "excluded": 0,
- "alerts": 0,
- "min_month": None,
- "max_month": None,
- "recognition": {},
- "exclusion_reasons": {},
- }
- def issue(row_id: int, field: str, raw: object, reason: str) -> None:
- database.execute("INSERT INTO issues VALUES(?,?,?,?)", (row_id, field, str(raw), reason))
- stats["alerts"] += 1
- headers_seen: list[str] = []
- recognition = Counter()
- exclusions = Counter()
- report("读取源文件…", 0.02)
- for row_id, end_line, headers, values in iter_source(source, config.get("encoding", "auto"), config.get("delimiter", "auto")):
- if values is None:
- cleaned = [normalize_header(name) for name in headers]
- lookup = {normalize_header(key).upper(): value for key, value in ALIASES.items()}
- headers_seen = [lookup.get(name.upper(), name.upper()) for name in cleaned]
- if "MONORSUM" not in headers_seen and any("人民币" in name or "RMB" in name.upper() or "CNY" in name.upper() for name in cleaned):
- raise ValueError("发现人民币金额列;本工具统计口径为美元,请先换汇并明确提供 MONORSUM_USD")
- stats["input_fields"] = headers_seen
- if len(set(headers_seen)) != len(headers_seen):
- raise ValueError("源文件存在重复字段名")
- missing = set(REQUIRED) - set(headers_seen)
- if missing:
- raise ValueError(f"缺少必需字段: {sorted(missing)}")
- continue
- stats["total"] += 1
- if len(values) != len(headers_seen):
- row = dict(zip(headers_seen, values))
- status = "CSV列数不一致"
- else:
- row = dict(zip(headers_seen, values))
- status = ""
- hs = normalize_code(row.get("HS_CODE"))
- direction_raw = normalize_code(row.get("I_E_FLAG")).upper()
- direction = {"进口": "I", "出口": "E"}.get(direction_raw, direction_raw)
- if not normalize_header(row.get("G_MODEL")):
- status = status or "G_MODEL为空"
- thursday: date | None
- try:
- thursday = date.fromisocalendar(int(normalize_code(row["YEAR"])), int(normalize_code(row["WEEK"])), 4)
- except (ValueError, TypeError, KeyError):
- thursday = None
- status = status or "无效ISO年份或周号"
- if len(hs) != 10 or not hs.isdigit():
- status = status or "HS必须为10位文本"
- elif hs not in rules.cap and hs not in MEMORY_HS:
- status = status or "不在项目HS范围"
- if direction not in config["directions"]:
- status = status or "不在配置进出口范围"
- if thursday and not int(config.get("start_year", 1900)) <= thursday.year <= int(config.get("end_year", 9998)):
- status = status or "不在配置年份范围"
- if thursday and thursday > cutoff:
- status = status or "晚于截止日期的周四"
- database.execute(
- "INSERT INTO raw VALUES(?,?,?,?)",
- (row_id, end_line, json.dumps(row, ensure_ascii=False), status or "纳入"),
- )
- for key in ("MONORSUM", "COUNT"):
- value = number(row.get(key))
- if value is None:
- issue(row_id, key, row.get(key), "无法识别;受影响指标留空")
- elif value <= 0:
- issue(row_id, key, row.get(key), "零值或负数;保留原值")
- if status:
- stats["excluded"] += 1
- exclusions[status] += 1
- issue(row_id, "记录范围", hs, status)
- else:
- parsed = rules.parse(hs, row["G_MODEL"])
- recognition["brand_resolved" if parsed["brand"] else "brand_unresolved"] += 1
- recognition["model_present" if parsed["model"] else "model_absent"] += 1
- for label, marker in [("explicit_no_brand", "无品牌标记"), ("explicit_no_model", "无型号标记"), ("unknown_brand", "疑似缺品牌:"), ("brand_conflict", "品牌映射冲突")]:
- if marker in parsed.get("flags", ""):
- recognition[label] += 1
- port, port_country = rules.port(row.get("DISTINATE_PORT"))
- record = dict(
- parsed,
- direction=direction,
- year=str(thursday.year),
- month=f"{thursday:%Y-%m}",
- origin=rules.country(row.get("ORIGIN_COUNTRY")),
- trade_country=rules.country(row.get("TRADE_AREA_CODE")),
- dispatch=rules.country(row.get("TRADE_COUNTRY")),
- port=port,
- port_country=port_country,
- )
- placeholders = ",".join("?" for _ in range(len(DIMENSIONS) + 3))
- database.execute(
- f"INSERT INTO fact VALUES({placeholders})",
- [row_id] + [str(record[key]) for key in DIMENSIONS] + [row["MONORSUM"], row["COUNT"]],
- )
- database.execute(
- "INSERT INTO audit VALUES(?,?,?,?)",
- (row_id, hs, row["G_MODEL"], json.dumps(parsed, ensure_ascii=False)),
- )
- stats["included"] += 1
- month = record["month"]
- stats["min_month"] = min(stats["min_month"] or month, month)
- stats["max_month"] = max(stats["max_month"] or month, month)
- if row_id % 10000 == 0:
- database.commit()
- report(f"已读取 {row_id:,} 条记录", min(0.55, 0.02 + row_id / max(stats["total"], 1) * 0.1))
- database.commit()
- report("汇总月度中间数据…", 0.6)
- dimensions = ",".join(DIMENSIONS)
- database.execute(
- f"CREATE TABLE monthly AS SELECT {dimensions},dsum(amount) amount,dsum(quantity) quantity,count(*) n "
- f"FROM fact GROUP BY {dimensions}"
- )
- create_monthly_indexes(database)
- database.commit()
- stats.update(
- recognition=dict(recognition),
- exclusion_reasons=dict(exclusions),
- sha256=sha256_file(source),
- cutoff=str(cutoff),
- partial_year_rule="仅 confirmed_complete_years 明确确认为全年,其余均为已提供期间暂算",
- )
- return database, stats
- class Store:
- """聚合查询封装:只暴露“维度 + 过滤 → 金额/数量/记录数”。"""
- def __init__(self, database: sqlite3.Connection) -> None:
- self.db = database
- def aggregate(self, dimensions: tuple = (), filters: dict | None = None) -> Iterator[dict]:
- dimensions = tuple(dimensions)
- if any(key not in DIMENSIONS for key in dimensions):
- raise ValueError(f"非法维度: {dimensions}")
- filters = filters or {}
- if any(key not in DIMENSIONS for key in filters):
- raise ValueError(f"非法过滤字段: {filters}")
- clause = " AND ".join(f"{key}=?" for key in filters) or "1=1"
- select = (",".join(dimensions) + ",") if dimensions else ""
- group = " GROUP BY " + ",".join(dimensions) if dimensions else ""
- query = f"SELECT {select}dsum(amount) amount,dsum(quantity) quantity,sum(n) n FROM monthly WHERE {clause}{group}" + (" ORDER BY " + ",".join(dimensions) if dimensions else "")
- for row in self.db.execute(query, list(filters.values())):
- result = dict(row)
- result["amount"] = number(row["amount"])
- result["quantity"] = number(row["quantity"])
- result["n"] = row["n"] or 0
- yield result
- def total(self, filters: dict) -> dict:
- return next(self.aggregate(filters=filters))
- def week_rows(start: int, end: int) -> Iterator[list]:
- """生成“ISO 年周 → 归属年/月”的核对表。"""
- for year in range(start - 1, end + 2):
- for week in range(1, 54):
- try:
- thursday = date.fromisocalendar(year, week, 4)
- except ValueError:
- continue
- yield [
- year,
- week,
- str(date.fromisocalendar(year, week, 1)),
- str(date.fromisocalendar(year, week, 7)),
- str(thursday),
- thursday.year,
- thursday.month,
- "ISO;整周按周四归月",
- ]
|