JiuTianDataAgent/NL2SQL
0
1from fastapi import FastAPI, Request, HTTPException, UploadFile, File2from fastapi.responses import HTMLResponse, JSONResponse3from fastapi.staticfiles import StaticFiles4from fastapi.templating import Jinja2Templates5import json6import os7from pathlib import Path8from typing import Dict, List, Optional9import logging10from datetime import datetime11 12# 设置日志13logging.basicConfig(level=logging.INFO)14logger = logging.getLogger(__name__)15 16app = FastAPI(title="NL2SQL Leaderboard", version="1.0.0")17 18# 创建必要的目录19Path("static").mkdir(exist_ok=True)20 21# 挂载静态文件22app.mount("/static", StaticFiles(directory="static"), name="static")23 24# 模板25templates = Jinja2Templates(directory=".")26 27# 数据文件路径28DATA_FILE = "data.json"29 30def load_data() -> Dict:31 """加载排行榜数据"""32 try:33 with open(DATA_FILE, "r", encoding="utf-8") as f:34 return json.load(f)35 except FileNotFoundError:36 # 返回默认数据结构37 return {38 "datasets": {39 "birddev": {40 "name": "BIRD Dev",41 "metrics": [42 "execution_ability",43 "correct_rate", 44 "execution_efficiency_on_executable_sql",45 "table_precision_on_executable_sql",46 "table_recall_on_executable_sql",47 "table_f1_on_executable_sql",48 "column_precision_on_executable_sql",49 "column_recall_on_executable_sql", 50 "column_f1_on_executable_sql"51 ],52 "algorithms": [53 {54 "name": "Algorithm A",55 "execution_ability": 0.85,56 "correct_rate": 0.78,57 "execution_efficiency_on_executable_sql": 0.92,58 "table_precision_on_executable_sql": 0.87,59 "table_recall_on_executable_sql": 0.85,60 "table_f1_on_executable_sql": 0.86,61 "column_precision_on_executable_sql": 0.83,62 "column_recall_on_executable_sql": 0.80,63 "column_f1_on_executable_sql": 0.81564 },65 {66 "name": "Algorithm B", 67 "execution_ability": 0.82,68 "correct_rate": 0.75,69 "execution_efficiency_on_executable_sql": 0.90,70 "table_precision_on_executable_sql": 0.85,71 "table_recall_on_executable_sql": 0.82,72 "table_f1_on_executable_sql": 0.835,73 "column_precision_on_executable_sql": 0.80,74 "column_recall_on_executable_sql": 0.78,75 "column_f1_on_executable_sql": 0.7976 },77 {78 "name": "Algorithm C",79 "execution_ability": 0.88,80 "correct_rate": 0.81,81 "execution_efficiency_on_executable_sql": 0.94,82 "table_precision_on_executable_sql": 0.89,83 "table_recall_on_executable_sql": 0.87,84 "table_f1_on_executable_sql": 0.88,85 "column_precision_on_executable_sql": 0.85,86 "column_recall_on_executable_sql": 0.83,87 "column_f1_on_executable_sql": 0.8488 }89 ]90 },91 "birdtrain": {92 "name": "BIRD Train",93 "metrics": [94 "execution_ability",95 "correct_rate", 96 "execution_efficiency_on_executable_sql",97 "table_precision_on_executable_sql",98 "table_recall_on_executable_sql",99 "table_f1_on_executable_sql",100 "column_precision_on_executable_sql",101 "column_recall_on_executable_sql", 102 "column_f1_on_executable_sql"103 ],104 "algorithms": [] # 空数据，只有架子105 },106 "spider": {107 "name": "Spider",108 "metrics": [109 "execution_ability",110 "correct_rate"111 ],112 "algorithms": [113 {114 "name": "Model X",115 "execution_ability": 0.91,116 "correct_rate": 0.85117 },118 {119 "name": "Model Y",120 "execution_ability": 0.89,121 "correct_rate": 0.83122 }123 ]124 }125 },126 "last_updated": datetime.now().isoformat()127 }128 129def save_data(data: Dict):130 """保存数据到文件"""131 data["last_updated"] = datetime.now().isoformat()132 with open(DATA_FILE, "w", encoding="utf-8") as f:133 json.dump(data, f, indent=2, ensure_ascii=False)134 135@app.get("/", response_class=HTMLResponse)136async def read_root(request: Request):137 """渲染主页面"""138 data = load_data()139 return templates.TemplateResponse(140 "index.html", 141 {"request": request, "datasets": data["datasets"]}142 )143 144@app.get("/api/data")145async def get_data():146 """获取排行榜数据"""147 return load_data()148 149@app.get("/api/health")150async def health_check():151 """健康检查端点"""152 return {"status": "healthy", "timestamp": datetime.now().isoformat()}153 154@app.post("/api/submit")155async def submit_results(156 dataset: str,157 algorithm_name: str,158 results: str # JSON字符串159):160 """161 提交新结果的端点（架子功能）162 实际使用时应该验证和解析results163 """164 try:165 # 这里只是架子，实际应该验证和存储数据166 results_data = json.loads(results)167 logger.info(f"Received submission for {dataset} - {algorithm_name}")168 169 # 在实际实现中，这里应该：170 # 1. 验证数据格式171 # 2. 存储到数据库或文件172 # 3. 更新排行榜173 174 return {175 "status": "success",176 "message": "Submission received (demo mode - not actually saved)",177 "dataset": dataset,178 "algorithm": algorithm_name,179 "received_results": results_data180 }181 except json.JSONDecodeError:182 raise HTTPException(status_code=400, detail="Invalid JSON format")183 except Exception as e:184 raise HTTPException(status_code=500, detail=str(e))185 186@app.post("/api/submit/file")187async def submit_results_file(188 dataset: str,189 algorithm_name: str,190 file: UploadFile = File(...)191):192 """193 通过文件提交结果的端点194 """195 try:196 # 读取文件内容197 content = await file.read()198 results = json.loads(content)199 200 logger.info(f"Received file submission for {dataset} - {algorithm_name}")201 202 return {203 "status": "success",204 "message": "File submission received (demo mode)",205 "dataset": dataset,206 "algorithm": algorithm_name,207 "filename": file.filename,208 "file_size": len(content)209 }210 except Exception as e:211 raise HTTPException(status_code=500, detail=str(e))212 213@app.get("/api/datasets")214async def get_datasets():215 """获取所有数据集信息"""216 data = load_data()217 return {218 "datasets": list(data["datasets"].keys()),219 "details": {k: {"name": v["name"], "count": len(v["algorithms"])} 220 for k, v in data["datasets"].items()}221 }222 223if __name__ == "__main__":224 import uvicorn225 uvicorn.run("server:app", host="0.0.0.0", port=7860, reload=True)