Spaces:
Running
Running
File size: 1,906 Bytes
f1cd642 | 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 | """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)
|