db.py

покрыто: 74 из 7598.7%
1"""Демонстрационная база: клиенты банка, счета, обороты, портфель, DQ-прогоны.23Данные детерминированы (seed), поэтому одинаковы на любой машине — это важно4для тестов сервера и для скриншотов.5"""67from __future__ import annotations89import random10import sqlite311from pathlib import Path1213SCHEMA = """14CREATE TABLE IF NOT EXISTS client (15    client_id TEXT PRIMARY KEY,16    name      TEXT NOT NULL,17    inn       TEXT NOT NULL,18    segment   TEXT NOT NULL,19    manager   TEXT20);21CREATE TABLE IF NOT EXISTS account (22    account_id TEXT PRIMARY KEY,23    client_id  TEXT NOT NULL,24    currency   TEXT NOT NULL,25    opened_dt  TEXT NOT NULL26);27CREATE TABLE IF NOT EXISTS turnover (28    account_id TEXT NOT NULL,29    dt         TEXT NOT NULL,30    debit      REAL NOT NULL,31    credit     REAL NOT NULL,32    balance    REAL NOT NULL,33    PRIMARY KEY (account_id, dt)34);35CREATE TABLE IF NOT EXISTS loan (36    client_id    TEXT NOT NULL,37    product      TEXT NOT NULL,38    limit_amt    REAL NOT NULL,39    debt_amt     REAL NOT NULL,40    overdue_amt  REAL NOT NULL,41    PRIMARY KEY (client_id, product)42);43CREATE TABLE IF NOT EXISTS dq_run (44    run_id     TEXT PRIMARY KEY,45    check_id   TEXT NOT NULL,46    status     TEXT NOT NULL,47    metric     REAL,48    run_ts     TEXT NOT NULL49);50CREATE INDEX IF NOT EXISTS idx_turnover_dt ON turnover (dt);51CREATE INDEX IF NOT EXISTS idx_account_client ON account (client_id);52"""5354SEGMENTS = ["МСБ", "Средний бизнес", "Крупный бизнес", "Госсектор"]55PRODUCTS = ["Овердрафт", "Кредитная линия", "Торговое финансирование", "Депозит"]56CURRENCIES = ["RUB", "RUB", "RUB", "USD", "EUR"]575859def connect(path: str | Path) -> sqlite3.Connection:60    if str(path) != ":memory:":61        Path(path).parent.mkdir(parents=True, exist_ok=True)62    connection = sqlite3.connect(str(path), check_same_thread=False)63    connection.row_factory = sqlite3.Row64    connection.executescript(SCHEMA)65    connection.commit()66    return connection676869def _seed_clients(connection: sqlite3.Connection, rng: random.Random, clients: int) -> None:70    rows = [71        (72            f"CL{index:04d}",73            f"ООО «Клиент-{index:03d}»",74            f"77{rng.randint(10_000_000, 99_999_999)}{index % 10}",75            SEGMENTS[index % len(SEGMENTS)],76            f"MGR{rng.randint(1, 12):02d}",77        )78        for index in range(clients)79    ]80    connection.executemany("INSERT INTO client (client_id, name, inn, segment, manager) VALUES (?, ?, ?, ?, ?)", rows)818283def _seed_accounts(connection: sqlite3.Connection, rng: random.Random, clients: int) -> list[tuple[str, float]]:84    accounts: list[tuple[str, float]] = []85    for index in range(clients):86        for account_index in range(rng.randint(1, 3)):87            account_id = f"AC{index:04d}{account_index}"88            connection.execute(89                "INSERT INTO account (account_id, client_id, currency, opened_dt) VALUES (?, ?, ?, ?)",90                (account_id, f"CL{index:04d}", CURRENCIES[rng.randrange(len(CURRENCIES))],91                 f"2025-{rng.randint(1, 12):02d}-{rng.randint(1, 28):02d}"),92            )93            accounts.append((account_id, rng.uniform(50_000, 3_000_000)))94    return accounts959697def _seed_turnover(connection: sqlite3.Connection, rng: random.Random, accounts: list[tuple[str, float]], days: int) -> None:98    rows: list[tuple] = []99    for account_id, start in accounts:100        balance = start101        for day in range(days):102            balance = max(0.0, balance + rng.gauss(0, 25_000))103            rows.append((account_id, f"2026-10-{day + 1:02d}", round(rng.uniform(0, 200_000), 2),104                         round(rng.uniform(0, 200_000), 2), round(balance, 2)))105    connection.executemany("INSERT INTO turnover (account_id, dt, debit, credit, balance) VALUES (?, ?, ?, ?, ?)", rows)106107108def _seed_loans(connection: sqlite3.Connection, rng: random.Random, clients: int) -> None:109    rows: list[tuple] = []110    for index in range(clients):111        for product in rng.sample(PRODUCTS, k=rng.randint(1, 2)):112            limit_amt = round(rng.uniform(500_000, 40_000_000), 2)113            rows.append((f"CL{index:04d}", product, limit_amt, round(limit_amt * rng.uniform(0.05, 0.9), 2),114                         round(rng.choice([0.0, 0.0, 0.0, 1200.0]), 2)))115    connection.executemany("INSERT INTO loan (client_id, product, limit_amt, debt_amt, overdue_amt) VALUES (?, ?, ?, ?, ?)", rows)116117118def _seed_checks(connection: sqlite3.Connection) -> None:119    checks = [120        ("turnover.freshness", "PASS", 2.1),121        ("turnover.duplicate_grain", "PASS", 0.0),122        ("turnover.orphan_client", "FAIL", 6.0),123        ("loans.debt_within_limit", "WARN", 3.0),124    ]125    connection.executemany(126        "INSERT INTO dq_run (run_id, check_id, status, metric, run_ts) VALUES (?, ?, ?, ?, ?)",127        [(f"RUN-{check_id}", check_id, status, metric, "2026-11-06T06:00:00") for check_id, status, metric in checks],128    )129130131def seed(connection: sqlite3.Connection, clients: int = 120, days: int = 40, seed_value: int = 17) -> dict[str, int]:132    """Детерминированное наполнение: один и тот же seed даёт одни и те же строки."""133    rng = random.Random(seed_value)134    for table in ("turnover", "account", "client", "loan", "dq_run"):135        connection.execute(f"DELETE FROM {table}")136137    _seed_clients(connection, rng, clients)138    accounts = _seed_accounts(connection, rng, clients)139    _seed_turnover(connection, rng, accounts, days)140    _seed_loans(connection, rng, clients)141    _seed_checks(connection)142    connection.commit()143    return table_counts(connection)144145146def table_counts(connection: sqlite3.Connection) -> dict[str, int]:147    names = [row["name"] for row in connection.execute(148        "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' ORDER BY name"149    )]150    return {name: int(connection.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0]) for name in names}151152153def tables(connection: sqlite3.Connection) -> list[dict]:154    result: list[dict] = []155    for name in table_counts(connection):156        columns = [157            {"name": row["name"], "type": row["type"], "pk": bool(row["pk"])}158            for row in connection.execute(f"PRAGMA table_info({name})")159        ]160        rows = int(connection.execute(f"SELECT COUNT(*) FROM {name}").fetchone()[0])161        result.append({"table": name, "columns": columns, "rows": rows})162    return result163164165def preview(connection: sqlite3.Connection, table: str, limit: int = 20) -> dict:166    if table not in table_counts(connection):167        raise KeyError(f"нет таблицы {table}")168    cursor = connection.execute(f"SELECT * FROM {table} LIMIT ?", (limit,))169    columns = [description[0] for description in cursor.description or ()]170    rows = [dict(row) for row in cursor.fetchall()]171    return {"table": table, "columns": columns, "rows": rows, "limit": limit}

← ко всем файлам