shelf-talk / engine /executor.py
Ashish-K's picture
Deploy Shelf Talk (replay mode)
f1cd642 verified
Raw History Blame Contribute Delete
1.91 kB
"""Layer 6b: execute SQL against DuckDB - read-only, SELECT-only, row-capped."""
from __future__ import annotations
import re
import threading
import time
from dataclasses import dataclass
import duckdb
import pandas as pd
from data.generate import ensure_db
MAX_ROWS = 500
_WRITE = re.compile(r"\b(insert|update|delete|drop|create|alter|attach|copy|pragma|install|load|export)\b", re.I)
class UnsafeSQL(ValueError):
pass
@dataclass
class Execution:
sql: str
df: pd.DataFrame
row_count: int
truncated: bool
ms: float
class Executor:
def __init__(self):
self.con = duckdb.connect(str(ensure_db()), read_only=True)
self._lock = threading.Lock()
def query(self, sql: str) -> pd.DataFrame:
with self._lock:
return self.con.execute(sql).df()
def columns(self, relation: str) -> list[str]:
return list(self.query(f"SELECT * FROM {relation} LIMIT 0").columns)
def run(self, sql: str) -> Execution:
stripped = sql.strip().rstrip(";")
if not re.match(r"^\s*(select|with)\b", stripped, re.I) or _WRITE.search(stripped) or ";" in stripped:
raise UnsafeSQL("Only a single read-only SELECT statement is allowed.")
t0 = time.perf_counter()
df = self.query(f"SELECT * FROM ({stripped}) AS q LIMIT {MAX_ROWS + 1}")
ms = (time.perf_counter() - t0) * 1000
truncated = len(df) > MAX_ROWS
return Execution(sql=stripped, df=df.head(MAX_ROWS), row_count=min(len(df), MAX_ROWS), truncated=truncated, ms=ms)
def schema_ddl(self, tables: list[str]) -> str:
out = []
for t in tables:
cols = self.query(f"DESCRIBE {t}")
out.append(f"TABLE {t} (\n" + ",\n".join(f" {r.column_name} {r.column_type}" for r in cols.itertuples()) + "\n)")
return "\n".join(out)