sentrythetaai / backend /db_helper.py
mabdullahsibghatullah123's picture
Upload 5 files
b2cc431 verified
Raw History Blame Contribute Delete
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()