Repository navigation
Expand file tree
/
Copy pathdb.py
More file actions
96 lines (81 loc) · 2.91 KB
/
Copy pathdb.py
File metadata and controls
96 lines (81 loc) · 2.91 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
"""db.py — centralized MySQL access (backend only).
Called by the ingestion scripts (writes) and the logic layer (reads).
The browser and Streamlit never connect to MySQL directly.
Note: schema.sql ships empty (per spec). execute_schema() runs its
statements once the user populates it; while empty it is a no-op and
queries that touch missing tables raise, which callers handle gracefully.
"""
import pymysql
from config import CFG
def get_conn():
"""Return a short-lived pymysql connection (DictCursor, autocommit off)."""
return pymysql.connect(
host=CFG["DB_HOST"],
port=CFG["DB_PORT"],
user=CFG["DB_USER"],
password=CFG["DB_PASSWORD"],
database=CFG["DB_NAME"],
cursorclass=pymysql.cursors.DictCursor,
autocommit=False,
charset="utf8mb4",
)
def query(sql, params=None):
"""Run a parameterized SELECT and return a list of row dicts."""
conn = get_conn()
try:
with conn.cursor() as cur:
cur.execute(sql, params or ())
return cur.fetchall()
finally:
conn.close()
def insert_rows(table, columns, rows):
"""Bulk upsert rows into `table`.
rows is an iterable of tuples aligned to `columns`.
Uses INSERT ... ON DUPLICATE KEY UPDATE for idempotent re-runs.
"""
if not rows:
return 0
placeholders = ", ".join(["%s"] * len(columns))
update_cols = [c for c in columns if c != "symbol"] or columns
updates = ", ".join(f"{c} = VALUES({c})" for c in update_cols)
sql = f"INSERT INTO {table} ({', '.join(columns)}) VALUES ({placeholders}) " \
f"ON DUPLICATE KEY UPDATE {updates}"
conn = get_conn()
try:
with conn.cursor() as cur:
cur.executemany(sql, rows)
conn.commit()
return len(rows)
except Exception:
conn.rollback()
raise
finally:
conn.close()
def execute_schema():
"""Run every ';'-terminated statement found in schema.sql."""
import os
path = os.path.join(os.path.dirname(__file__), "schema.sql")
with open(path, encoding="utf-8") as f:
script = f.read()
statements = [s.strip() for s in script.split(";") if s.strip() and not s.strip().startswith("--")]
if not statements:
return 0
conn = get_conn()
try:
with conn.cursor() as cur:
for stmt in statements:
cur.execute(stmt)
conn.commit()
return len(statements)
finally:
conn.close()
def log_ingest(source, symbol, detail, rows_written, status):
"""Write one ingest_log row. Best-effort: logging must never crash a pipeline."""
try:
insert_rows(
"ingest_log",
["source", "symbol", "detail", "rows_written", "status"],
[(source, symbol, detail, rows_written, status)],
)
except Exception as exc: # noqa: BLE001
print(f"[ingest_log skipped] {type(exc).__name__}: {exc}")