Vaishnavi-279/sql-debugger-env

1"""2SQL execution and reward grading — fully self-contained using SQLite.3No external dependencies beyond the Python standard library.4"""5 6import sqlite37from typing import Any, Dict, List, Optional, Tuple8 9 10def execute_sql(11    schema_ddl: str, query: str12) -> Tuple[Optional[List[Dict[str, Any]]], Optional[str]]:13    """14    Run `query` against a fresh in-memory SQLite database seeded with `schema_ddl`.15 16    Returns:17        (rows, None)       on success18        (None, error_msg)  on failure19    """20    conn = sqlite3.connect(":memory:")21    conn.row_factory = sqlite3.Row22    try:23        conn.executescript(schema_ddl)24        cursor = conn.execute(query)25        return [dict(row) for row in cursor.fetchall()], None26    except Exception as exc:27        return None, str(exc)28    finally:29        conn.close()30 31 32def rows_match(33    actual: List[Dict[str, Any]], expected: List[Dict[str, Any]]34) -> bool:35    """Exact order-sensitive match, float-tolerant (±0.01)."""36    if len(actual) != len(expected):37        return False38    for a, e in zip(actual, expected):39        if set(a.keys()) != set(e.keys()):40            return False41        for k in e:42            av, ev = a.get(k), e[k]43            if isinstance(ev, float) or isinstance(av, float):44                try:45                    if abs(float(av) - float(ev)) > 0.01:46                        return False47                except (TypeError, ValueError):48                    return False49            else:50                if av != ev:51                    return False52    return True53 54 55def grade(56    actual: Optional[List[Dict[str, Any]]],57    expected: List[Dict[str, Any]],58) -> float:59    """60    5-tier partial reward in [0.0, 1.0]:61 62      0.0  query errored or returned nothing63      0.3  ran but wrong row count64      0.6  right row count, wrong values65      0.8  right values, wrong order66      1.0  perfect match (values + order)67    """68    if actual is None:69        return 0.070    if len(actual) == 0 and len(expected) == 0:71        return 1.072    if len(actual) != len(expected):73        return 0.374 75    # Values match ignoring order?76    def key(row: Dict[str, Any]) -> str:77        return str(sorted(row.items()))78 79    if not rows_match(sorted(actual, key=key), sorted(expected, key=key)):80        return 0.6  # right count, wrong values81 82    # Values match — check order83    return 1.0 if rows_match(actual, expected) else 0.884