Spaces:
Sleeping
Sleeping
Download auth/seed_from_flat.py from Coeinterns/bank-statement-api: direct link, hf CLI and curl.
- Browser
- Download file 6.51 kB
-
https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/auth/seed_from_flat.py
- Command line
-
hf download hf://spaces/Coeinterns/bank-statement-api/auth/seed_from_flat.py
-
curl -L -o seed_from_flat.py https://huggingface.co/spaces/Coeinterns/bank-statement-api/resolve/main/auth/seed_from_flat.py
6.51 kB
| # auth/seed_from_flat.py | |
| import sys | |
| import os | |
| sys.path.insert(0, os.path.dirname(os.path.dirname(__file__))) | |
| from dotenv import load_dotenv | |
| load_dotenv() | |
| from sqlalchemy.orm import Session | |
| from auth.database import SessionLocal | |
| from auth.rule_models import ( | |
| ClassificationFlat, | |
| ClassificationGroup, | |
| ClassificationCategory, | |
| ClassificationRule, | |
| ) | |
| import uuid | |
| from collections import defaultdict | |
| def _unique_preserve_order(items: list[str]) -> list[str]: | |
| seen: set[str] = set() | |
| unique: list[str] = [] | |
| for item in items: | |
| normalized = item.strip().lower() | |
| if normalized not in seen: | |
| seen.add(normalized) | |
| unique.append(item) | |
| return unique | |
| def seed_from_flat(): | |
| db: Session = SessionLocal() | |
| try: | |
| # ββ Step A: Deactivate all existing records (non-destructive rollback path) | |
| db.query(ClassificationRule).update({"is_active": False}) | |
| db.query(ClassificationCategory).update({"is_active": False}) | |
| db.query(ClassificationGroup).update({"is_active": False}) | |
| db.commit() | |
| print("[OK] Deactivated all old groups, categories, rules") | |
| # ββ Step B: Load all active rows from classification_flat | |
| flat_rows = db.query(ClassificationFlat).filter_by(is_active=True).all() | |
| print(f"[OK] Loaded {len(flat_rows)} rows from classification_flat") | |
| # ββ Step C: Build group β category β keywords index | |
| groups = {} | |
| category_map = defaultdict(list) | |
| category_metadata = {} | |
| for row in flat_rows: | |
| cat = row.category.strip() | |
| sub = row.sub_category.strip() if row.sub_category else None | |
| kw = row.keyword.strip() | |
| cat_name = sub if sub else cat | |
| if cat not in groups: | |
| groups[cat] = None # placeholder | |
| category_map[cat_name].append(kw) | |
| if cat_name not in category_metadata: | |
| category_metadata[cat_name] = { | |
| "group_name": cat, | |
| "sheet_name": cat, | |
| "sub_column": sub if sub else cat, | |
| } | |
| # Deduplicate keywords inside each category group while preserving order. | |
| keyword_groups: dict[str, set[str]] = defaultdict(set) | |
| for cat_name, keywords in category_map.items(): | |
| category_map[cat_name] = _unique_preserve_order(keywords) | |
| for kw in category_map[cat_name]: | |
| keyword_groups[kw.strip().lower()].add(cat_name) | |
| for kw, groups_set in keyword_groups.items(): | |
| if len(groups_set) > 1: | |
| print(f"[WARN] Keyword '{kw}' appears in multiple categories: {sorted(groups_set)}") | |
| # ββ Step D: Insert or reactivate groups | |
| for cat_name in groups: | |
| existing = db.query(ClassificationGroup).filter_by(name=cat_name).first() | |
| if existing: | |
| existing.is_active = True | |
| groups[cat_name] = existing | |
| else: | |
| g = ClassificationGroup( | |
| id=str(uuid.uuid4()), | |
| name=cat_name, | |
| is_active=True, | |
| ) | |
| db.add(g) | |
| db.flush() | |
| groups[cat_name] = g | |
| db.commit() | |
| print(f"[OK] Upserted {len(groups)} groups") | |
| # ββ Step E: Insert or reactivate categories + rules | |
| category_count = 0 | |
| rule_count = 0 | |
| # Pre-fetch all categories and rules to avoid N+1 queries in loop | |
| all_cats = db.query(ClassificationCategory).all() | |
| cats_by_name = {c.name: c for c in all_cats} | |
| all_rules = db.query(ClassificationRule).all() | |
| rules_by_cat_id = {r.category_id: r for r in all_rules} | |
| for cat_name, keywords in category_map.items(): | |
| meta = category_metadata[cat_name] | |
| sheet_name = meta["sheet_name"] | |
| sub_column = meta["sub_column"] | |
| group = groups[meta["group_name"]] | |
| # Find or create category | |
| existing_cat = cats_by_name.get(cat_name) | |
| if existing_cat: | |
| existing_cat.is_active = True | |
| existing_cat.sheet_name = sheet_name | |
| existing_cat.sub_column = sub_column | |
| existing_cat.group_id = group.id | |
| cat_obj = existing_cat | |
| else: | |
| cat_obj = ClassificationCategory( | |
| id=str(uuid.uuid4()), | |
| name=cat_name, | |
| sheet_name=sheet_name, | |
| sub_column=sub_column, | |
| group_id=group.id, | |
| is_active=True, | |
| ) | |
| db.add(cat_obj) | |
| db.flush() | |
| cats_by_name[cat_name] = cat_obj | |
| category_count += 1 | |
| # Find or create rule for this category | |
| existing_rule = rules_by_cat_id.get(cat_obj.id) | |
| if existing_rule: | |
| existing_rule.is_active = True | |
| existing_rule.keywords = _unique_preserve_order(keywords) | |
| existing_rule.match_on = "description" | |
| else: | |
| rule = ClassificationRule( | |
| id=str(uuid.uuid4()), | |
| name=f"rule_{cat_name.lower().replace(' ', '_').replace('&', 'and').replace('/', '_')}", | |
| category_id=cat_obj.id, | |
| match_on="description", | |
| is_active=True, | |
| ) | |
| rule.keywords = _unique_preserve_order(keywords) | |
| db.add(rule) | |
| rules_by_cat_id[cat_obj.id] = rule | |
| rule_count += 1 | |
| db.commit() | |
| print(f"[OK] Upserted {category_count} categories, {rule_count} rules") | |
| # ββ Step F: Verification query | |
| from sqlalchemy import text | |
| result = db.execute(text( | |
| "SELECT category, COUNT(*) as cnt FROM classification_flat " | |
| "WHERE is_active = true GROUP BY category ORDER BY cnt DESC" | |
| )).fetchall() | |
| print("\n-- Verification: flat table group counts --") | |
| for row in result: | |
| print(f" {row[0]:45s} {row[1]} keywords") | |
| finally: | |
| db.close() | |
| if __name__ == "__main__": | |
| seed_from_flat() | |