Download sqlcf/compare.py from sqlcf-lexsi/counterfactual-sql-demo: direct link, hf CLI and curl.
- Browser
- Download file 3.55 kB
-
https://huggingface.co/spaces/sqlcf-lexsi/counterfactual-sql-demo/resolve/main/sqlcf/compare.py
- Command line
-
hf download hf://spaces/sqlcf-lexsi/counterfactual-sql-demo/sqlcf/compare.py
-
curl -L -o compare.py https://huggingface.co/spaces/sqlcf-lexsi/counterfactual-sql-demo/resolve/main/sqlcf/compare.py
3.55 kB
| """Sandboxed SQL execution and result comparison. | |
| run(): read-only, single statement, no ATTACH, 5 s timeout. | |
| match(): multiset by default; ordered when the gold is ordered; column permutation | |
| allowed; floats rounded to 4 dp (and 3.0 == 3). set_match(): BIRD-style set EX. | |
| """ | |
| import itertools | |
| import sqlite3 | |
| import time | |
| from collections import Counter | |
| TIMEOUT_S = 5.0 | |
| MAX_ROWS = 100_000 | |
| class SQLError(Exception): | |
| pass | |
| def _deny_attach(action, *_): | |
| return sqlite3.SQLITE_DENY if action in (sqlite3.SQLITE_ATTACH, sqlite3.SQLITE_DETACH) else sqlite3.SQLITE_OK | |
| def run(db_path: str, sql: str, timeout: float = TIMEOUT_S) -> list[tuple]: | |
| """Execute one read-only query; raise SQLError on any failure.""" | |
| con = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True, check_same_thread=False) | |
| try: | |
| con.execute("PRAGMA query_only = ON") | |
| con.set_authorizer(_deny_attach) | |
| deadline = time.monotonic() + timeout | |
| con.set_progress_handler(lambda: time.monotonic() > deadline, 10_000) | |
| cur = con.execute(sql) # sqlite3 refuses multiple statements here | |
| rows = cur.fetchmany(MAX_ROWS + 1) | |
| if len(rows) > MAX_ROWS: | |
| raise SQLError("too many rows") | |
| return rows | |
| except sqlite3.Error as e: | |
| raise SQLError(str(e)) from e | |
| finally: | |
| con.close() | |
| def _norm(v): | |
| if isinstance(v, float): | |
| v = round(v, 4) | |
| return int(v) if v.is_integer() else v | |
| if isinstance(v, bytes): | |
| return v.hex() | |
| return v | |
| def normalize(rows) -> list[tuple]: | |
| return [tuple(_norm(v) for v in r) for r in rows] | |
| def _key(v): | |
| return (v is None, type(v).__name__, v if v is not None else 0) | |
| def match(pred, gold, ordered: bool = False) -> bool: | |
| """True if pred equals gold up to column permutation (multiset, or list if ordered).""" | |
| pred, gold = normalize(pred), normalize(gold) | |
| if len(pred) != len(gold): | |
| return False | |
| if not gold: | |
| return True | |
| ncol = len(gold[0]) | |
| if any(len(r) != ncol for r in pred): | |
| return False | |
| gcols = [sorted((r[j] for r in gold), key=_key) for j in range(ncol)] | |
| pcols = [sorted((r[j] for r in pred), key=_key) for j in range(ncol)] | |
| # candidate pred columns for each gold column = same sorted values | |
| cands = [[i for i in range(ncol) if pcols[i] == gcols[j]] for j in range(ncol)] | |
| if any(not c for c in cands): | |
| return False | |
| target = gold if ordered else Counter(gold) | |
| for perm in itertools.product(*cands): | |
| if len(set(perm)) != ncol: | |
| continue | |
| mapped = [tuple(r[i] for i in perm) for r in pred] | |
| if (mapped if ordered else Counter(mapped)) == target: | |
| return True | |
| return False | |
| def set_match(pred, gold) -> bool: | |
| """BIRD/Spider-style execution accuracy: set of rows, column order as written.""" | |
| return set(normalize(pred)) == set(normalize(gold)) | |
| if __name__ == "__main__": | |
| assert match([(1, "a"), (1, "a")], [(1, "a"), (1, "a")]) | |
| assert not match([(1, "a")], [(1, "a"), (1, "a")]) # duplicates matter | |
| assert set_match([(1, "a")], [(1, "a"), (1, "a")]) # ...but not for set EX | |
| assert match([("a", 1)], [(1, "a")]) # column permutation | |
| assert match([(2.00000001,)], [(2,)]) # float rounding | |
| assert not match([(1,), (2,)], [(2,), (1,)], ordered=True) | |
| assert match([(1,), (2,)], [(2,), (1,)]) | |
| assert not match([(1, 2)], [(1, 2, 3)]) | |
| print("compare.py self-check ok") | |