Spaces:
Running
Running
Download backend/db_helper.py from sysvelop/sentrythetaai: direct link, hf CLI and curl.
- Browser
- Download file 7.53 kB
-
https://huggingface.co/spaces/sysvelop/sentrythetaai/resolve/main/backend/db_helper.py
- Command line
-
hf download hf://spaces/sysvelop/sentrythetaai/backend/db_helper.py
-
curl -L -o db_helper.py https://huggingface.co/spaces/sysvelop/sentrythetaai/resolve/main/backend/db_helper.py
7.53 kB
| import os | |
| import sqlite3 | |
| import datetime | |
| import logging | |
| from typing import Dict, Any, List, Optional, Tuple | |
| logger = logging.getLogger("SentryTheta.DBHelper") | |
| DB_PATH = os.path.abspath(os.path.join(os.path.dirname(__file__), "..", "sentrytheta.db")) | |
| class DBHelper: | |
| def __init__(self): | |
| self.db_path = DB_PATH | |
| self.init_db() | |
| def get_connection(self): | |
| """Returns a connection to the SQLite database.""" | |
| conn = sqlite3.connect(self.db_path) | |
| conn.row_factory = sqlite3.Row # Access columns by name | |
| return conn | |
| def init_db(self): | |
| """Initialize SQLite database tables if they do not exist.""" | |
| logger.info(f"Initializing database at: {self.db_path}") | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| # 1. Settings Table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS settings ( | |
| key TEXT PRIMARY KEY, | |
| value TEXT | |
| ) | |
| """) | |
| # 2. Terminal Logs Table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS terminal_logs ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| timestamp TEXT, | |
| message TEXT | |
| ) | |
| """) | |
| # 3. Mock Portfolio Table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS mock_portfolio ( | |
| id INTEGER PRIMARY KEY CHECK (id = 1), | |
| cash REAL, | |
| initial_equity REAL | |
| ) | |
| """) | |
| # Initialize default mock portfolio if empty | |
| cursor.execute("SELECT COUNT(*) FROM mock_portfolio") | |
| if cursor.fetchone()[0] == 0: | |
| cursor.execute( | |
| "INSERT INTO mock_portfolio (id, cash, initial_equity) VALUES (1, 95000.0, 100000.0)" | |
| ) | |
| # 4. Mock Positions Table | |
| cursor.execute(""" | |
| CREATE TABLE IF NOT EXISTS mock_positions ( | |
| symbol TEXT PRIMARY KEY, | |
| underlying_symbol TEXT, | |
| qty INTEGER, | |
| entry_price REAL, | |
| current_price REAL, | |
| side TEXT, | |
| type TEXT, | |
| strike REAL, | |
| expiration TEXT, | |
| market_value REAL, | |
| cost_basis REAL, | |
| unrealized_pl REAL, | |
| unrealized_plpc REAL | |
| ) | |
| """) | |
| conn.commit() | |
| conn.close() | |
| logger.info("Database tables initialized successfully.") | |
| # --- Settings Handlers --- | |
| def get_setting(self, key: str, default: Any = None) -> Any: | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute("SELECT value FROM settings WHERE key = ?", (key,)) | |
| row = cursor.fetchone() | |
| conn.close() | |
| return row[0] if row else default | |
| def set_setting(self, key: str, value: Any): | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute( | |
| "INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)", | |
| (key, str(value)) | |
| ) | |
| conn.commit() | |
| conn.close() | |
| def get_all_settings(self) -> Dict[str, str]: | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute("SELECT key, value FROM settings") | |
| rows = cursor.fetchall() | |
| conn.close() | |
| return {row["key"]: row["value"] for row in rows} | |
| # --- Logs Handlers --- | |
| def add_log(self, message: str): | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| timestamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") | |
| cursor.execute( | |
| "INSERT INTO terminal_logs (timestamp, message) VALUES (?, ?)", | |
| (timestamp, message) | |
| ) | |
| conn.commit() | |
| conn.close() | |
| def get_logs(self, limit: int = 100) -> List[str]: | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute( | |
| "SELECT message FROM terminal_logs ORDER BY id DESC LIMIT ?", | |
| (limit,) | |
| ) | |
| rows = cursor.fetchall() | |
| conn.close() | |
| # Return in chronological order | |
| return [row["message"] for row in reversed(rows)] | |
| def clear_all_logs(self): | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute("DELETE FROM terminal_logs") | |
| conn.commit() | |
| conn.close() | |
| # --- Mock Portfolio Handlers --- | |
| def get_mock_portfolio(self) -> Tuple[float, float]: | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute("SELECT cash, initial_equity FROM mock_portfolio WHERE id = 1") | |
| row = cursor.fetchone() | |
| conn.close() | |
| if row: | |
| return float(row["cash"]), float(row["initial_equity"]) | |
| return 95000.0, 100000.0 | |
| def save_mock_portfolio(self, cash: float, initial_equity: float): | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute( | |
| "UPDATE mock_portfolio SET cash = ?, initial_equity = ? WHERE id = 1", | |
| (cash, initial_equity) | |
| ) | |
| conn.commit() | |
| conn.close() | |
| # --- Mock Positions Handlers --- | |
| def get_mock_positions(self) -> List[Dict[str, Any]]: | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute("SELECT * FROM mock_positions") | |
| rows = cursor.fetchall() | |
| conn.close() | |
| positions = [] | |
| for row in rows: | |
| positions.append({ | |
| "symbol": row["symbol"], | |
| "underlying_symbol": row["underlying_symbol"], | |
| "qty": int(row["qty"]), | |
| "entry_price": float(row["entry_price"]), | |
| "current_price": float(row["current_price"]), | |
| "side": row["side"], | |
| "type": row["type"], | |
| "strike": float(row["strike"]), | |
| "expiration": row["expiration"], | |
| "market_value": float(row["market_value"]), | |
| "cost_basis": float(row["cost_basis"]), | |
| "unrealized_pl": float(row["unrealized_pl"]), | |
| "unrealized_plpc": float(row["unrealized_plpc"]) | |
| }) | |
| return positions | |
| def save_mock_position(self, symbol: str, pos: Dict[str, Any]): | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute(""" | |
| INSERT OR REPLACE INTO mock_positions ( | |
| symbol, underlying_symbol, qty, entry_price, current_price, | |
| side, type, strike, expiration, market_value, cost_basis, | |
| unrealized_pl, unrealized_plpc | |
| ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) | |
| """, ( | |
| symbol, | |
| pos["underlying_symbol"], | |
| int(pos["qty"]), | |
| float(pos["entry_price"]), | |
| float(pos["current_price"]), | |
| pos["side"], | |
| pos["type"], | |
| float(pos["strike"]), | |
| pos["expiration"], | |
| float(pos["market_value"]), | |
| float(pos["cost_basis"]), | |
| float(pos["unrealized_pl"]), | |
| float(pos["unrealized_plpc"]) | |
| )) | |
| conn.commit() | |
| conn.close() | |
| def delete_mock_position(self, symbol: str): | |
| conn = self.get_connection() | |
| cursor = conn.cursor() | |
| cursor.execute("DELETE FROM mock_positions WHERE symbol = ?", (symbol,)) | |
| conn.commit() | |
| conn.close() | |