Spaces:
Sleeping
Sleeping
Download app/executor.py from skillissue04/sql-repair-env: direct link, hf CLI and curl.
- Browser
- Download file 11.2 kB
-
https://huggingface.co/spaces/skillissue04/sql-repair-env/resolve/refs%2Fpr%2F2/app/executor.py
- Command line
-
hf download hf://spaces/skillissue04/sql-repair-env@refs/pr/2/app/executor.py
-
curl -L -o executor.py https://huggingface.co/spaces/skillissue04/sql-repair-env/resolve/refs%2Fpr%2F2/app/executor.py
11.2 kB
| """ | |
| app/executor.py | |
| =============== | |
| Pure SQLite execution layer for the SQL Query Repair environment. | |
| Responsibilities: | |
| - Seed a fresh in-memory SQLite database from fixtures.sql | |
| - Execute arbitrary SQL safely and return results or structured errors | |
| - Compute Jaccard similarity between result rows and expected rows | |
| - Classify error messages into human-readable feedback strings | |
| This module has NO side effects on the environment state β it is | |
| stateless and can be called freely from env.py. | |
| """ | |
| from __future__ import annotations | |
| import os | |
| import re | |
| import sqlite3 | |
| from pathlib import Path | |
| from typing import Any, List, Optional, Tuple | |
| # Path to the seed SQL file (relative to project root) | |
| _FIXTURES_PATH = Path(__file__).parent.parent / "db" / "fixtures.sql" | |
| # Hard cap on rows returned to prevent memory abuse | |
| _MAX_ROWS = 500 | |
| # --------------------------------------------------------------------------- | |
| # Public types | |
| # --------------------------------------------------------------------------- | |
| class ExecutionResult: | |
| """ | |
| Returned by every execute_* call. | |
| Attributes | |
| ---------- | |
| ok : True if the query ran without a SQLite error. | |
| rows : List of tuples representing result rows (empty on error). | |
| row_count : len(rows), convenience shortcut. | |
| error : SQLite error string if ok=False, else None. | |
| feedback : Human-readable one-liner (safe to send to agent). | |
| """ | |
| __slots__ = ("ok", "rows", "row_count", "error", "feedback") | |
| def __init__( | |
| self, | |
| ok: bool, | |
| rows: List[Tuple[Any, ...]], | |
| error: Optional[str], | |
| feedback: str, | |
| ) -> None: | |
| self.ok = ok | |
| self.rows = rows | |
| self.row_count = len(rows) | |
| self.error = error | |
| self.feedback = feedback | |
| def __repr__(self) -> str: # pragma: no cover | |
| if self.ok: | |
| return f"ExecutionResult(ok=True, rows={self.row_count})" | |
| return f"ExecutionResult(ok=False, error={self.error!r})" | |
| # --------------------------------------------------------------------------- | |
| # Database factory | |
| # --------------------------------------------------------------------------- | |
| def build_fresh_db() -> sqlite3.Connection: | |
| """ | |
| Create and return a new in-memory SQLite connection seeded with | |
| fixtures.sql. Each episode calls this once inside reset(). | |
| Returns | |
| ------- | |
| sqlite3.Connection | |
| A fully seeded, ready-to-use connection. Caller owns it and | |
| must call .close() when the episode ends. | |
| Raises | |
| ------ | |
| FileNotFoundError | |
| If fixtures.sql cannot be found. | |
| sqlite3.Error | |
| If the fixture script contains invalid SQL (should never happen | |
| in production; would indicate a bug in fixtures.sql). | |
| """ | |
| if not _FIXTURES_PATH.exists(): | |
| raise FileNotFoundError(f"fixtures.sql not found at {_FIXTURES_PATH}") | |
| conn = sqlite3.connect(":memory:") | |
| conn.row_factory = sqlite3.Row # rows accessible by column name | |
| conn.execute("PRAGMA foreign_keys = ON") | |
| conn.execute("PRAGMA journal_mode = MEMORY") | |
| sql = _FIXTURES_PATH.read_text(encoding="utf-8") | |
| conn.executescript(sql) | |
| conn.commit() | |
| return conn | |
| # --------------------------------------------------------------------------- | |
| # Query execution helpers | |
| # --------------------------------------------------------------------------- | |
| def execute_query(conn: sqlite3.Connection, sql: str) -> ExecutionResult: | |
| """ | |
| Execute *sql* against *conn* and return an ExecutionResult. | |
| - SELECT queries: rows are returned as plain tuples (up to _MAX_ROWS). | |
| - Non-SELECT DML: rows=[], ok=True if no error. | |
| - Any SQLite error: ok=False, error contains the raw message. | |
| The agent never receives raw row data β only row counts and error | |
| messages β which is enforced at the env.py layer, not here. | |
| """ | |
| sql = sql.strip() | |
| if not sql: | |
| return ExecutionResult( | |
| ok=False, rows=[], error="empty query", | |
| feedback="Query is empty. Please provide a SQL statement.", | |
| ) | |
| try: | |
| cursor = conn.execute(sql) | |
| if cursor.description is not None: | |
| # SELECT-like query | |
| raw_rows = cursor.fetchmany(_MAX_ROWS) | |
| rows = [tuple(r) for r in raw_rows] | |
| feedback = f"Query ran successfully β {len(rows)} row(s) returned." | |
| return ExecutionResult(ok=True, rows=rows, error=None, feedback=feedback) | |
| else: | |
| # DML (INSERT/UPDATE/DELETE) β valid but unusual in this env | |
| conn.rollback() # do not persist side effects | |
| return ExecutionResult( | |
| ok=True, rows=[], error=None, | |
| feedback="Statement executed (no rows returned).", | |
| ) | |
| except sqlite3.OperationalError as exc: | |
| msg = str(exc) | |
| return ExecutionResult( | |
| ok=False, rows=[], error=msg, | |
| feedback=_humanize_error(msg, sql), | |
| ) | |
| except sqlite3.Error as exc: | |
| msg = str(exc) | |
| return ExecutionResult( | |
| ok=False, rows=[], error=msg, | |
| feedback=f"SQL error: {msg}", | |
| ) | |
| def execute_expected(conn: sqlite3.Connection, correct_sql: str) -> List[Tuple[Any, ...]]: | |
| """ | |
| Execute the reference correct_query and return its rows as plain tuples. | |
| Called once at reset() time to cache expected_rows for the episode. | |
| Raises | |
| ------ | |
| RuntimeError | |
| If the reference query itself errors β indicates a bug in the dataset. | |
| """ | |
| result = execute_query(conn, correct_sql) | |
| if not result.ok: | |
| raise RuntimeError( | |
| f"Reference query failed β dataset bug!\n" | |
| f" query: {correct_sql!r}\n" | |
| f" error: {result.error}" | |
| ) | |
| return result.rows | |
| # --------------------------------------------------------------------------- | |
| # Scoring | |
| # --------------------------------------------------------------------------- | |
| def jaccard_score( | |
| result_rows: List[Tuple[Any, ...]], | |
| expected_rows: List[Tuple[Any, ...]], | |
| ) -> float: | |
| """ | |
| Compute Jaccard similarity between two sets of rows. | |
| Jaccard = |intersection| / |union| | |
| Rows are treated as *sets* (duplicates collapsed) for a fair | |
| comparison that isn't sensitive to ordering or duplicate counts. | |
| Returns a float in [0.0, 1.0]. | |
| Special case: if both sets are empty, returns 1.0 (both correct). | |
| """ | |
| set_r = set(result_rows) | |
| set_e = set(expected_rows) | |
| if not set_r and not set_e: | |
| return 1.0 | |
| intersection = len(set_r & set_e) | |
| union = len(set_r | set_e) | |
| if union == 0: | |
| return 1.0 | |
| return round(intersection / union, 4) | |
| def score_to_reward(jaccard: float, turns_used: int, max_turns: int) -> float: | |
| """ | |
| Convert a Jaccard correctness score to a step reward for submit_query. | |
| Reward tiers | |
| ------------ | |
| jaccard == 1.0 β base +1.0 (perfect) | |
| jaccard >= 0.8 β base +0.5 (close) | |
| jaccard >= 0.5 β base +0.2 (partial) | |
| jaccard < 0.5 β base -0.5 (wrong) | |
| Turn penalty | |
| ------------ | |
| penalty = 0.05 * (turns_used - 1) | |
| Subtracted from base; encourages solving in fewer turns. | |
| Minimum reward is clamped to -1.0. | |
| Returns | |
| ------- | |
| float in [-1.0, 1.0] | |
| """ | |
| if jaccard == 1.0: | |
| base = 1.0 | |
| elif jaccard >= 0.8: | |
| base = 0.5 | |
| elif jaccard >= 0.5: | |
| base = 0.2 | |
| else: | |
| base = -0.5 | |
| penalty = 0.05 * max(0, turns_used - 1) | |
| reward = base - penalty | |
| return round(max(-1.0, min(1.0, reward)), 4) | |
| # --------------------------------------------------------------------------- | |
| # Clarification matching | |
| # --------------------------------------------------------------------------- | |
| def match_clarification(question: str, answers: dict[str, str]) -> Optional[str]: | |
| """ | |
| Fuzzy-match *question* against the task's clarification_answers dict. | |
| Matching strategy (case-insensitive, in priority order): | |
| 1. Exact key match after lowercasing and stripping punctuation. | |
| 2. Key appears as a substring of the question. | |
| 3. Any word in the key appears in the question. | |
| Returns the answer string on match, or None if no match found. | |
| """ | |
| if not answers: | |
| return None | |
| q_clean = _clean(question) | |
| # Priority 1 β exact match | |
| for key, answer in answers.items(): | |
| if _clean(key) == q_clean: | |
| return answer | |
| # Priority 2 β key is substring of question | |
| for key, answer in answers.items(): | |
| if _clean(key) in q_clean: | |
| return answer | |
| # Priority 3 β any word from the key appears in question | |
| q_words = set(q_clean.split()) | |
| for key, answer in answers.items(): | |
| key_words = set(_clean(key).split()) | |
| if key_words & q_words: | |
| return answer | |
| return None | |
| # --------------------------------------------------------------------------- | |
| # Internal helpers | |
| # --------------------------------------------------------------------------- | |
| def _clean(text: str) -> str: | |
| """Lowercase, strip punctuation, collapse whitespace.""" | |
| text = text.lower() | |
| text = re.sub(r"[^\w\s]", " ", text) | |
| text = re.sub(r"\s+", " ", text).strip() | |
| return text | |
| def _humanize_error(sqlite_msg: str, sql: str) -> str: | |
| """ | |
| Convert raw SQLite error messages into concise, agent-friendly hints. | |
| """ | |
| msg = sqlite_msg.lower() | |
| if "syntax error" in msg: | |
| # Try to extract the offending token | |
| near = re.search(r'near "([^"]+)"', sqlite_msg) | |
| if near: | |
| return f"Syntax error near '{near.group(1)}'. Check your SQL syntax around that token." | |
| return "Syntax error in query. Check for typos, missing keywords, or invalid operators." | |
| if "no such table" in msg: | |
| tbl = re.search(r"no such table: (\S+)", sqlite_msg) | |
| name = tbl.group(1) if tbl else "unknown" | |
| return f"Table '{name}' does not exist. Check the schema for correct table names." | |
| if "no such column" in msg: | |
| col = re.search(r"no such column: (\S+)", sqlite_msg) | |
| name = col.group(1) if col else "unknown" | |
| return f"Column '{name}' does not exist. Check the schema for correct column names." | |
| if "ambiguous column" in msg: | |
| col = re.search(r"ambiguous column name: (\S+)", sqlite_msg) | |
| name = col.group(1) if col else "unknown" | |
| return f"Column '{name}' is ambiguous. Qualify it with a table alias (e.g. t.{name})." | |
| if "no such function" in msg: | |
| fn = re.search(r"no such function: (\S+)", sqlite_msg) | |
| name = fn.group(1) if fn else "unknown" | |
| return f"Function '{name}' is not recognised. Check the function name." | |
| if "misuse of aggregate" in msg: | |
| return "Aggregate function (COUNT/SUM/AVG) used in wrong clause. Use HAVING for aggregates, not WHERE." | |
| if "foreign key constraint" in msg: | |
| return "Foreign key constraint failed. Ensure referenced IDs exist in the parent table." | |
| # Fallback | |
| return f"SQL error: {sqlite_msg}" | |