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}← ко всем файлам