Spaces:
Sleeping
Sleeping
| import asyncio | |
| import os | |
| import textwrap | |
| from typing import List, Optional | |
| from openai import OpenAI | |
| from client import SQLArenaEnv, SQLArenaAction | |
| IMAGE_NAME = os.getenv("LOCAL_IMAGE_NAME") or os.getenv("IMAGE_NAME") | |
| API_KEY = os.getenv("HF_TOKEN") or os.getenv("API_KEY") | |
| API_BASE_URL = os.getenv("API_BASE_URL", "https://router.huggingface.co/v1") | |
| MODEL_NAME = os.getenv("MODEL_NAME", "Qwen/Qwen2.5-72B-Instruct") | |
| TASK_NAME = os.getenv("SQLARENA_TASK", "medium_001") | |
| BENCHMARK = "sql_arena_env" | |
| MAX_STEPS = 8 | |
| TEMPERATURE = 0.0 | |
| MAX_TOKENS = 512 | |
| SUCCESS_SCORE_THRESHOLD = 0.5 | |
| SYSTEM_PROMPT = textwrap.dedent(""" | |
| You are an expert SQL agent working with a SQLite database. | |
| You will be given a natural language question and a schema description. | |
| Your job is to write correct SQLite SQL to answer the question. | |
| Rules: | |
| - Use only standard SQLite syntax (no MySQL/PostgreSQL specific features) | |
| - IMPORTANT: Never use SELECT * — always specify exact column names that match what the question asks for | |
| - Read the question carefully to know exactly which columns to return | |
| - You can run EXPLORE queries first to understand the data structure | |
| - When ready, submit your final answer with query_type="submit" | |
| - SQLite date functions: SUBSTR(date,1,7) for YYYY-MM, julianday() for date math | |
| - Window functions available: ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER(), PERCENT_RANK() | |
| Response format — respond with ONLY a JSON object like this: | |
| {"sql": "SELECT ...", "query_type": "explore"} | |
| or | |
| {"sql": "SELECT ...", "query_type": "submit"} | |
| No explanation, no markdown, just the JSON. | |
| """).strip() | |
| def log_start(task: str, env: str, model: str) -> None: | |
| print(f"[START] task={task} env={env} model={model}", flush=True) | |
| def log_step(step: int, action: str, reward: float, done: bool, error: Optional[str]) -> None: | |
| error_val = error if error else "null" | |
| done_val = str(done).lower() | |
| action_safe = action.replace("\n", " ").replace("\r", "")[:200] | |
| print( | |
| f"[STEP] step={step} action={action_safe} reward={reward:.2f} done={done_val} error={error_val}", | |
| flush=True, | |
| ) | |
| def log_end(success: bool, steps: int, score: float, rewards: List[float]) -> None: | |
| rewards_str = ",".join(f"{r:.2f}" for r in rewards) | |
| print( | |
| f"[END] success={str(success).lower()} steps={steps} score={score:.2f} rewards={rewards_str}", | |
| flush=True, | |
| ) | |
| def build_user_prompt(obs, step: int, history: List[str]) -> str: | |
| history_block = "\n".join(history[-3:]) if history else "None" | |
| result_str = str(obs.query_result[:5]) if obs.query_result else "No results yet" | |
| return textwrap.dedent(f""" | |
| QUESTION: {obs.question} | |
| SCHEMA: {obs.schema_info} | |
| Step: {step} | |
| Explore steps remaining: {obs.explore_steps_remaining} | |
| Last query result (first 5 rows): {result_str} | |
| Last error: {obs.query_error or 'None'} | |
| Previous actions: | |
| {history_block} | |
| {"No more explore steps — you MUST submit now (query_type='submit')" if obs.explore_steps_remaining == 0 else "You can explore more or submit your final answer."} | |
| Respond with ONLY a JSON object: {{"sql": "...", "query_type": "explore" or "submit"}} | |
| """).strip() | |
| def get_model_action(client: OpenAI, obs, step: int, history: List[str]): | |
| import json | |
| user_prompt = build_user_prompt(obs, step, history) | |
| try: | |
| completion = client.chat.completions.create( | |
| model=MODEL_NAME, | |
| messages=[ | |
| {"role": "system", "content": SYSTEM_PROMPT}, | |
| {"role": "user", "content": user_prompt}, | |
| ], | |
| temperature=TEMPERATURE, | |
| max_tokens=MAX_TOKENS, | |
| ) | |
| text = (completion.choices[0].message.content or "").strip() | |
| # remove markdown block if model wrapped it | |
| if text.startswith("```"): | |
| text = text.split("```")[1] | |
| if text.startswith("json"): | |
| text = text[4:] | |
| text = text.strip() | |
| parsed = json.loads(text) | |
| sql = parsed.get("sql", "SELECT 1") | |
| query_type = parsed.get("query_type", "explore") | |
| # force submit if no explore budget left | |
| if obs.explore_steps_remaining == 0: | |
| query_type = "submit" | |
| return SQLArenaAction(sql=sql, query_type=query_type) | |
| except Exception as exc: | |
| print(f"[DEBUG] Model parse error: {exc} | raw: {text[:200] if 'text' in dir() else 'N/A'}", flush=True) | |
| # fallback query on parse error | |
| return SQLArenaAction( | |
| sql=f"SELECT * FROM sqlite_master WHERE type='table'", | |
| query_type="explore" if obs.explore_steps_remaining > 0 else "submit" | |
| ) | |
| async def main() -> None: | |
| client = OpenAI(base_url=API_BASE_URL, api_key=API_KEY) | |
| # connect to env (docker or hf space) | |
| if IMAGE_NAME: | |
| env = await SQLArenaEnv.from_docker_image(IMAGE_NAME) | |
| else: | |
| hf_space_url = os.getenv("HF_SPACE_URL", "http://localhost:8000") | |
| env = SQLArenaEnv(base_url=hf_space_url) | |
| history: List[str] = [] | |
| rewards: List[float] = [] | |
| steps_taken = 0 | |
| score = 0.0 | |
| success = False | |
| log_start(task=TASK_NAME, env=BENCHMARK, model=MODEL_NAME) | |
| try: | |
| result = await env.reset(task_id=TASK_NAME) | |
| obs = result.observation | |
| for step in range(1, MAX_STEPS + 1): | |
| if result.done: | |
| break | |
| action = get_model_action(client, obs, step, history) | |
| result = await env.step(action) | |
| obs = result.observation | |
| reward = result.reward or 0.0 | |
| done = result.done | |
| error = obs.query_error | |
| rewards.append(reward) | |
| steps_taken = step | |
| log_step( | |
| step=step, | |
| action=f"{action.query_type}:{action.sql[:100]}", | |
| reward=reward, | |
| done=done, | |
| error=error, | |
| ) | |
| history.append( | |
| f"Step {step} [{action.query_type}]: {action.sql[:80]} → " | |
| f"rows={obs.rows_returned} reward={reward:.2f}" | |
| ) | |
| if done: | |
| break | |
| final_reward = rewards[-1] if rewards else 0.0 | |
| score = final_reward | |
| success = score >= SUCCESS_SCORE_THRESHOLD | |
| except Exception as exc: | |
| print(f"[DEBUG] Episode error: {exc}", flush=True) | |
| finally: | |
| try: | |
| await env.close() | |
| except Exception as e: | |
| print(f"[DEBUG] env.close() error: {e}", flush=True) | |
| log_end(success=success, steps=steps_taken, score=score, rewards=rewards) | |
| if __name__ == "__main__": | |
| asyncio.run(main()) |