Model-config web apps: describe the app in configuration instead of writing code. Built on Polars and DuckDB.
server.py · 20.0 KB · 718 lines
| 1 | #!/usr/bin/env python3 |
| 2 | """ |
| 3 | DBBasic Server - FastAPI wrapper around dbbasic_fast.py |
| 4 | |
| 5 | Provides REST API for spreadsheet operations using Polars + DuckDB. |
| 6 | Each user gets their own isolated data directory. |
| 7 | |
| 8 | Requirements: |
| 9 | pip install fastapi uvicorn polars duckdb pyarrow python-multipart aiofiles |
| 10 | |
| 11 | Run: |
| 12 | uvicorn dbbasic_server:app --reload --port 8000 |
| 13 | """ |
| 14 | |
| 15 | from fastapi import FastAPI, HTTPException, File, UploadFile, Form |
| 16 | from fastapi.middleware.cors import CORSMiddleware |
| 17 | from fastapi.responses import FileResponse, StreamingResponse |
| 18 | from fastapi.staticfiles import StaticFiles |
| 19 | from pydantic import BaseModel |
| 20 | from typing import Optional, List, Dict, Any, Union |
| 21 | import polars as pl |
| 22 | import duckdb |
| 23 | import numpy as np |
| 24 | import time |
| 25 | import os |
| 26 | import json |
| 27 | import uuid |
| 28 | from pathlib import Path |
| 29 | import io |
| 30 | import aiofiles |
| 31 | from datetime import datetime |
| 32 | |
| 33 | # Initialize FastAPI |
| 34 | app = FastAPI( |
| 35 | title="DBBasic API", |
| 36 | description="Spreadsheet operations at 285 million rows/second", |
| 37 | version="1.0.0" |
| 38 | ) |
| 39 | |
| 40 | # Enable CORS for browser access |
| 41 | app.add_middleware( |
| 42 | CORSMiddleware, |
| 43 | allow_origins=["*"], # In production, specify your domain |
| 44 | allow_credentials=True, |
| 45 | allow_methods=["*"], |
| 46 | allow_headers=["*"], |
| 47 | ) |
| 48 | |
| 49 | # Mount static files |
| 50 | app.mount("/static", StaticFiles(directory="static"), name="static") |
| 51 | |
| 52 | # Serve the main HTML file |
| 53 | @app.get("/") |
| 54 | async def serve_main(): |
| 55 | """Serve the main DBBasic HTML interface""" |
| 56 | return FileResponse("dbbasic_v1.html") |
| 57 | |
| 58 | @app.get("/dbbasic_v1.html") |
| 59 | async def serve_dbbasic_v1(): |
| 60 | """Serve the DBBasic V1 HTML file""" |
| 61 | return FileResponse("dbbasic_v1.html") |
| 62 | |
| 63 | # User data directory |
| 64 | DATA_DIR = Path("user_data") |
| 65 | DATA_DIR.mkdir(exist_ok=True) |
| 66 | |
| 67 | # Session storage (in production, use Redis) |
| 68 | sessions = {} |
| 69 | |
| 70 | # Request/Response Models |
| 71 | class GenerateRequest(BaseModel): |
| 72 | rows: int |
| 73 | columns: Optional[List[str]] = None |
| 74 | session_id: Optional[str] = None |
| 75 | |
| 76 | class CalculateRequest(BaseModel): |
| 77 | operation: str # SUM, AVG, COUNT, GROUP BY, etc. |
| 78 | column: Optional[str] = None |
| 79 | group_by: Optional[str] = None |
| 80 | session_id: str |
| 81 | |
| 82 | class SQLRequest(BaseModel): |
| 83 | query: str |
| 84 | session_id: str |
| 85 | |
| 86 | class CommandRequest(BaseModel): |
| 87 | command: str |
| 88 | session_id: str |
| 89 | |
| 90 | class DataResponse(BaseModel): |
| 91 | data: List[List[Any]] |
| 92 | columns: List[Dict[str, Any]] |
| 93 | rows: int |
| 94 | execution_time: float |
| 95 | rows_per_second: Optional[float] = None |
| 96 | |
| 97 | class CalculationResponse(BaseModel): |
| 98 | result: Any |
| 99 | execution_time: float |
| 100 | rows_processed: int |
| 101 | rows_per_second: float |
| 102 | |
| 103 | class SessionInfo(BaseModel): |
| 104 | session_id: str |
| 105 | created_at: str |
| 106 | rows: int |
| 107 | columns: int |
| 108 | memory_mb: float |
| 109 | |
| 110 | # Helper Functions |
| 111 | def get_user_dir(session_id: str) -> Path: |
| 112 | """Get or create user-specific data directory""" |
| 113 | user_dir = DATA_DIR / session_id |
| 114 | user_dir.mkdir(exist_ok=True) |
| 115 | return user_dir |
| 116 | |
| 117 | def get_session_data(session_id: str) -> pl.DataFrame: |
| 118 | """Load session data from disk or memory""" |
| 119 | if session_id in sessions: |
| 120 | return sessions[session_id] |
| 121 | |
| 122 | user_dir = get_user_dir(session_id) |
| 123 | parquet_file = user_dir / "data.parquet" |
| 124 | |
| 125 | if parquet_file.exists(): |
| 126 | df = pl.read_parquet(parquet_file) |
| 127 | sessions[session_id] = df |
| 128 | return df |
| 129 | |
| 130 | return pl.DataFrame() |
| 131 | |
| 132 | def save_session_data(session_id: str, df: pl.DataFrame): |
| 133 | """Save session data to disk and memory""" |
| 134 | sessions[session_id] = df |
| 135 | user_dir = get_user_dir(session_id) |
| 136 | parquet_file = user_dir / "data.parquet" |
| 137 | df.write_parquet(parquet_file) |
| 138 | |
| 139 | # API Endpoints |
| 140 | @app.get("/") |
| 141 | async def root(): |
| 142 | return { |
| 143 | "name": "DBBasic API", |
| 144 | "version": "1.0.0", |
| 145 | "speed": "285 million rows/second", |
| 146 | "engine": "Polars + DuckDB" |
| 147 | } |
| 148 | |
| 149 | @app.post("/update/cell") |
| 150 | async def update_cell( |
| 151 | session_id: str = Form(...), |
| 152 | row: int = Form(...), |
| 153 | column: str = Form(...), |
| 154 | value: str = Form(...) |
| 155 | ): |
| 156 | """Update a single cell value""" |
| 157 | df = get_session_data(session_id) |
| 158 | if df.is_empty(): |
| 159 | raise HTTPException(status_code=404, detail="No data in session") |
| 160 | |
| 161 | try: |
| 162 | # Update the value in the DataFrame |
| 163 | df = df.with_row_count() |
| 164 | |
| 165 | # Convert value to appropriate type based on column |
| 166 | col_type = df[column].dtype |
| 167 | if col_type in [pl.Float64, pl.Int64]: |
| 168 | value = float(value) if '.' in str(value) else int(value) |
| 169 | |
| 170 | # Update the specific cell |
| 171 | df = df.with_columns( |
| 172 | pl.when(pl.col("row_nr") == row) |
| 173 | .then(pl.lit(value)) |
| 174 | .otherwise(pl.col(column)) |
| 175 | .alias(column) |
| 176 | ) |
| 177 | |
| 178 | # Remove the row_nr column |
| 179 | df = df.drop("row_nr") |
| 180 | |
| 181 | # Save updated data |
| 182 | save_session_data(session_id, df) |
| 183 | |
| 184 | return { |
| 185 | "success": True, |
| 186 | "row": row, |
| 187 | "column": column, |
| 188 | "value": value |
| 189 | } |
| 190 | except Exception as e: |
| 191 | raise HTTPException(status_code=400, detail=str(e)) |
| 192 | |
| 193 | @app.post("/update/batch") |
| 194 | async def update_batch( |
| 195 | session_id: str = Form(...), |
| 196 | updates: str = Form(...) # JSON string of updates |
| 197 | ): |
| 198 | """Update multiple cells at once""" |
| 199 | import json |
| 200 | |
| 201 | df = get_session_data(session_id) |
| 202 | if df.is_empty(): |
| 203 | raise HTTPException(status_code=404, detail="No data in session") |
| 204 | |
| 205 | try: |
| 206 | updates_list = json.loads(updates) |
| 207 | |
| 208 | for update in updates_list: |
| 209 | row = update['row'] |
| 210 | column = update['column'] |
| 211 | value = update['value'] |
| 212 | |
| 213 | # Update each cell |
| 214 | df = df.with_row_count() |
| 215 | |
| 216 | # Convert value to appropriate type |
| 217 | col_type = df[column].dtype |
| 218 | if col_type in [pl.Float64, pl.Int64]: |
| 219 | value = float(value) if '.' in str(value) else int(value) |
| 220 | |
| 221 | df = df.with_columns( |
| 222 | pl.when(pl.col("row_nr") == row) |
| 223 | .then(pl.lit(value)) |
| 224 | .otherwise(pl.col(column)) |
| 225 | .alias(column) |
| 226 | ) |
| 227 | |
| 228 | df = df.drop("row_nr") |
| 229 | |
| 230 | # Save updated data |
| 231 | save_session_data(session_id, df) |
| 232 | |
| 233 | return { |
| 234 | "success": True, |
| 235 | "updates": len(updates_list) |
| 236 | } |
| 237 | except Exception as e: |
| 238 | raise HTTPException(status_code=400, detail=str(e)) |
| 239 | |
| 240 | @app.post("/add/row") |
| 241 | async def add_row( |
| 242 | session_id: str = Form(...), |
| 243 | data: Optional[str] = Form(None) # JSON string of row data |
| 244 | ): |
| 245 | """Add a new row to the data""" |
| 246 | import json |
| 247 | |
| 248 | df = get_session_data(session_id) |
| 249 | |
| 250 | try: |
| 251 | if data: |
| 252 | row_data = json.loads(data) |
| 253 | else: |
| 254 | # Create empty row with default values |
| 255 | row_data = {col: None for col in df.columns} |
| 256 | |
| 257 | # Create new row as DataFrame |
| 258 | new_row = pl.DataFrame([row_data]) |
| 259 | |
| 260 | # Append to existing data |
| 261 | df = pl.concat([df, new_row]) |
| 262 | |
| 263 | # Save updated data |
| 264 | save_session_data(session_id, df) |
| 265 | |
| 266 | return { |
| 267 | "success": True, |
| 268 | "rows": len(df) |
| 269 | } |
| 270 | except Exception as e: |
| 271 | raise HTTPException(status_code=400, detail=str(e)) |
| 272 | |
| 273 | @app.delete("/delete/row") |
| 274 | async def delete_row( |
| 275 | session_id: str = Form(...), |
| 276 | row: int = Form(...) |
| 277 | ): |
| 278 | """Delete a row from the data""" |
| 279 | df = get_session_data(session_id) |
| 280 | if df.is_empty(): |
| 281 | raise HTTPException(status_code=404, detail="No data in session") |
| 282 | |
| 283 | try: |
| 284 | # Add row numbers and filter out the specified row |
| 285 | df = df.with_row_count() |
| 286 | df = df.filter(pl.col("row_nr") != row) |
| 287 | df = df.drop("row_nr") |
| 288 | |
| 289 | # Save updated data |
| 290 | save_session_data(session_id, df) |
| 291 | |
| 292 | return { |
| 293 | "success": True, |
| 294 | "rows": len(df) |
| 295 | } |
| 296 | except Exception as e: |
| 297 | raise HTTPException(status_code=400, detail=str(e)) |
| 298 | |
| 299 | @app.post("/session/create") |
| 300 | async def create_session(): |
| 301 | """Create a new user session""" |
| 302 | session_id = str(uuid.uuid4()) |
| 303 | sessions[session_id] = pl.DataFrame() |
| 304 | |
| 305 | return SessionInfo( |
| 306 | session_id=session_id, |
| 307 | created_at=datetime.now().isoformat(), |
| 308 | rows=0, |
| 309 | columns=0, |
| 310 | memory_mb=0 |
| 311 | ) |
| 312 | |
| 313 | @app.get("/session/{session_id}/info") |
| 314 | async def get_session_info(session_id: str): |
| 315 | """Get session information""" |
| 316 | df = get_session_data(session_id) |
| 317 | |
| 318 | return SessionInfo( |
| 319 | session_id=session_id, |
| 320 | created_at=datetime.now().isoformat(), |
| 321 | rows=len(df), |
| 322 | columns=len(df.columns), |
| 323 | memory_mb=df.estimated_size('mb') if len(df) > 0 else 0 |
| 324 | ) |
| 325 | |
| 326 | @app.get("/session/{session_id}/data") |
| 327 | async def get_session_data_endpoint(session_id: str): |
| 328 | """Get the actual data from a session""" |
| 329 | df = get_session_data(session_id) |
| 330 | |
| 331 | if df.is_empty(): |
| 332 | return { |
| 333 | "data": [], |
| 334 | "columns": [], |
| 335 | "rows": 0 |
| 336 | } |
| 337 | |
| 338 | # Convert to list format for spreadsheet |
| 339 | data_list = df.to_numpy().tolist() |
| 340 | columns = [ |
| 341 | {"title": col, "width": 120, "type": "numeric" if df[col].dtype in [pl.Float64, pl.Int64] else "text"} |
| 342 | for col in df.columns |
| 343 | ] |
| 344 | |
| 345 | return { |
| 346 | "data": data_list, |
| 347 | "columns": columns, |
| 348 | "rows": len(df) |
| 349 | } |
| 350 | |
| 351 | @app.post("/generate", response_model=DataResponse) |
| 352 | async def generate_data(request: GenerateRequest): |
| 353 | """Generate test data with specified rows""" |
| 354 | start_time = time.time() |
| 355 | |
| 356 | # Get or create session |
| 357 | session_id = request.session_id or str(uuid.uuid4()) |
| 358 | n = request.rows |
| 359 | |
| 360 | # Generate data using Polars (FAST!) |
| 361 | data = { |
| 362 | 'ID': range(1, n + 1), |
| 363 | 'Value': np.random.randn(n) * 100, |
| 364 | 'Category': np.random.choice(['A', 'B', 'C', 'D'], n), |
| 365 | 'Price': np.random.uniform(10, 1000, n), |
| 366 | 'Quantity': np.random.randint(1, 100, n), |
| 367 | } |
| 368 | |
| 369 | df = pl.DataFrame(data) |
| 370 | |
| 371 | # Add calculated column |
| 372 | df = df.with_columns( |
| 373 | (pl.col('Price') * pl.col('Quantity')).alias('Total') |
| 374 | ) |
| 375 | |
| 376 | # Save to session |
| 377 | save_session_data(session_id, df) |
| 378 | |
| 379 | # Convert to response format |
| 380 | elapsed = time.time() - start_time |
| 381 | |
| 382 | # Convert to list format for JSON response |
| 383 | data_list = df.to_numpy().tolist() |
| 384 | columns = [ |
| 385 | {"title": col, "width": 120, "type": "numeric" if df[col].dtype in [pl.Float64, pl.Int64] else "text"} |
| 386 | for col in df.columns |
| 387 | ] |
| 388 | |
| 389 | return DataResponse( |
| 390 | data=data_list[:100], # Return first 100 rows for display |
| 391 | columns=columns, |
| 392 | rows=len(df), |
| 393 | execution_time=elapsed, |
| 394 | rows_per_second=n / elapsed |
| 395 | ) |
| 396 | |
| 397 | @app.post("/calculate", response_model=CalculationResponse) |
| 398 | async def calculate(request: CalculateRequest): |
| 399 | """Perform calculations on data""" |
| 400 | start_time = time.time() |
| 401 | |
| 402 | df = get_session_data(request.session_id) |
| 403 | if df.is_empty(): |
| 404 | raise HTTPException(status_code=404, detail="No data in session") |
| 405 | |
| 406 | result = None |
| 407 | operation = request.operation.upper() |
| 408 | |
| 409 | if operation == "SUM": |
| 410 | if request.column and request.column in df.columns: |
| 411 | result = df[request.column].sum() |
| 412 | else: |
| 413 | result = df.select(pl.sum(pl.all())).to_numpy()[0].tolist() |
| 414 | |
| 415 | elif operation == "AVG" or operation == "MEAN": |
| 416 | if request.column and request.column in df.columns: |
| 417 | result = df[request.column].mean() |
| 418 | else: |
| 419 | result = df.select(pl.mean(pl.all())).to_numpy()[0].tolist() |
| 420 | |
| 421 | elif operation == "COUNT": |
| 422 | result = len(df) |
| 423 | |
| 424 | elif operation == "MAX": |
| 425 | if request.column and request.column in df.columns: |
| 426 | result = df[request.column].max() |
| 427 | |
| 428 | elif operation == "MIN": |
| 429 | if request.column and request.column in df.columns: |
| 430 | result = df[request.column].min() |
| 431 | |
| 432 | elif operation == "GROUP" and request.group_by: |
| 433 | if request.column: |
| 434 | grouped = df.group_by(request.group_by).agg(pl.col(request.column).sum()) |
| 435 | result = grouped.to_dict(as_series=False) |
| 436 | else: |
| 437 | result = {"error": "Column required for GROUP BY"} |
| 438 | |
| 439 | elapsed = time.time() - start_time |
| 440 | rows = len(df) |
| 441 | |
| 442 | return CalculationResponse( |
| 443 | result=result, |
| 444 | execution_time=elapsed, |
| 445 | rows_processed=rows, |
| 446 | rows_per_second=rows / elapsed if elapsed > 0 else 0 |
| 447 | ) |
| 448 | |
| 449 | @app.post("/sql") |
| 450 | async def execute_sql(request: SQLRequest): |
| 451 | """Execute SQL query using DuckDB""" |
| 452 | start_time = time.time() |
| 453 | |
| 454 | df = get_session_data(request.session_id) |
| 455 | if df.is_empty(): |
| 456 | raise HTTPException(status_code=404, detail="No data in session") |
| 457 | |
| 458 | # Create DuckDB connection |
| 459 | conn = duckdb.connect(':memory:') |
| 460 | |
| 461 | # Register DataFrame as table |
| 462 | conn.register('sheet', df) |
| 463 | |
| 464 | try: |
| 465 | # Execute query |
| 466 | result = conn.execute(request.query).pl() |
| 467 | |
| 468 | elapsed = time.time() - start_time |
| 469 | |
| 470 | # Convert result to response format |
| 471 | data_list = result.to_numpy().tolist() if not result.is_empty() else [] |
| 472 | columns = [ |
| 473 | {"title": col, "width": 120} |
| 474 | for col in result.columns |
| 475 | ] |
| 476 | |
| 477 | return DataResponse( |
| 478 | data=data_list[:1000], # Limit response size |
| 479 | columns=columns, |
| 480 | rows=len(result), |
| 481 | execution_time=elapsed, |
| 482 | rows_per_second=len(df) / elapsed if elapsed > 0 else 0 |
| 483 | ) |
| 484 | |
| 485 | except Exception as e: |
| 486 | raise HTTPException(status_code=400, detail=f"SQL Error: {str(e)}") |
| 487 | finally: |
| 488 | conn.close() |
| 489 | |
| 490 | @app.post("/command") |
| 491 | async def execute_command(request: CommandRequest): |
| 492 | """Execute DBBasic command""" |
| 493 | cmd = request.command.upper().strip() |
| 494 | session_id = request.session_id |
| 495 | |
| 496 | if cmd.startswith("GENERATE"): |
| 497 | # Parse: GENERATE 1000 rows |
| 498 | import re |
| 499 | match = re.match(r'GENERATE\s+(\d+)\s+ROWS?', cmd) |
| 500 | if match: |
| 501 | rows = int(match.group(1)) |
| 502 | gen_request = GenerateRequest(rows=rows, session_id=session_id) |
| 503 | return await generate_data(gen_request) |
| 504 | |
| 505 | elif cmd.startswith("CALC"): |
| 506 | # Parse: CALC SUM(column) |
| 507 | import re |
| 508 | match = re.match(r'CALC\s+(\w+)\(([\w\s]+)\)', cmd) |
| 509 | if match: |
| 510 | operation = match.group(1) |
| 511 | column = match.group(2).strip() |
| 512 | calc_request = CalculateRequest( |
| 513 | operation=operation, |
| 514 | column=column, |
| 515 | session_id=session_id |
| 516 | ) |
| 517 | return await calculate(calc_request) |
| 518 | |
| 519 | elif cmd.startswith("SQL"): |
| 520 | # Parse: SQL SELECT * FROM sheet |
| 521 | query = cmd[3:].strip() |
| 522 | sql_request = SQLRequest(query=query, session_id=session_id) |
| 523 | return await execute_sql(sql_request) |
| 524 | |
| 525 | else: |
| 526 | raise HTTPException(status_code=400, detail=f"Unknown command: {cmd}") |
| 527 | |
| 528 | @app.post("/upload/csv") |
| 529 | async def upload_csv( |
| 530 | file: UploadFile = File(...), |
| 531 | session_id: str = Form(...) |
| 532 | ): |
| 533 | """Upload CSV file""" |
| 534 | start_time = time.time() |
| 535 | |
| 536 | # Read CSV into Polars |
| 537 | contents = await file.read() |
| 538 | df = pl.read_csv(io.BytesIO(contents)) |
| 539 | |
| 540 | # Save to session |
| 541 | save_session_data(session_id, df) |
| 542 | |
| 543 | elapsed = time.time() - start_time |
| 544 | |
| 545 | return { |
| 546 | "filename": file.filename, |
| 547 | "rows": len(df), |
| 548 | "columns": len(df.columns), |
| 549 | "execution_time": elapsed, |
| 550 | "rows_per_second": len(df) / elapsed |
| 551 | } |
| 552 | |
| 553 | @app.get("/download/csv") |
| 554 | async def download_csv(session_id: str): |
| 555 | """Download data as CSV""" |
| 556 | df = get_session_data(session_id) |
| 557 | if df.is_empty(): |
| 558 | raise HTTPException(status_code=404, detail="No data in session") |
| 559 | |
| 560 | # Convert to CSV |
| 561 | csv_buffer = io.StringIO() |
| 562 | df.write_csv(csv_buffer) |
| 563 | csv_buffer.seek(0) |
| 564 | |
| 565 | return StreamingResponse( |
| 566 | io.BytesIO(csv_buffer.getvalue().encode()), |
| 567 | media_type="text/csv", |
| 568 | headers={"Content-Disposition": f"attachment; filename=dbbasic_{session_id}.csv"} |
| 569 | ) |
| 570 | |
| 571 | @app.get("/download/parquet") |
| 572 | async def download_parquet(session_id: str): |
| 573 | """Download data as Parquet (much faster and smaller)""" |
| 574 | df = get_session_data(session_id) |
| 575 | if df.is_empty(): |
| 576 | raise HTTPException(status_code=404, detail="No data in session") |
| 577 | |
| 578 | user_dir = get_user_dir(session_id) |
| 579 | parquet_file = user_dir / "data.parquet" |
| 580 | |
| 581 | if parquet_file.exists(): |
| 582 | return FileResponse( |
| 583 | parquet_file, |
| 584 | media_type="application/octet-stream", |
| 585 | filename=f"dbbasic_{session_id}.parquet" |
| 586 | ) |
| 587 | else: |
| 588 | raise HTTPException(status_code=404, detail="Parquet file not found") |
| 589 | |
| 590 | @app.post("/benchmark") |
| 591 | async def run_benchmark(session_id: str = Form(...)): |
| 592 | """Run performance benchmark""" |
| 593 | results = [] |
| 594 | |
| 595 | # Generate test data |
| 596 | df = pl.DataFrame({ |
| 597 | 'ID': range(1, 1000001), |
| 598 | 'Value': np.random.randn(1000000) * 100, |
| 599 | 'Category': np.random.choice(['A', 'B', 'C', 'D'], 1000000), |
| 600 | 'Price': np.random.uniform(10, 1000, 1000000), |
| 601 | 'Quantity': np.random.randint(1, 100, 1000000), |
| 602 | }) |
| 603 | |
| 604 | df = df.with_columns( |
| 605 | (pl.col('Price') * pl.col('Quantity')).alias('Total') |
| 606 | ) |
| 607 | |
| 608 | save_session_data(session_id, df) |
| 609 | rows = len(df) |
| 610 | |
| 611 | # Benchmark 1: SUM |
| 612 | start = time.time() |
| 613 | total = df['Total'].sum() |
| 614 | sum_time = time.time() - start |
| 615 | results.append({ |
| 616 | "operation": "SUM", |
| 617 | "time": sum_time, |
| 618 | "rows_per_second": rows / sum_time, |
| 619 | "result": float(total) |
| 620 | }) |
| 621 | |
| 622 | # Benchmark 2: GROUP BY |
| 623 | start = time.time() |
| 624 | grouped = df.group_by('Category').agg(pl.col('Total').sum()) |
| 625 | group_time = time.time() - start |
| 626 | results.append({ |
| 627 | "operation": "GROUP BY", |
| 628 | "time": group_time, |
| 629 | "rows_per_second": rows / group_time |
| 630 | }) |
| 631 | |
| 632 | # Benchmark 3: FILTER |
| 633 | start = time.time() |
| 634 | filtered = df.filter(pl.col('Price') > 500) |
| 635 | filter_time = time.time() - start |
| 636 | results.append({ |
| 637 | "operation": "FILTER", |
| 638 | "time": filter_time, |
| 639 | "rows_per_second": rows / filter_time, |
| 640 | "matches": len(filtered) |
| 641 | }) |
| 642 | |
| 643 | # Benchmark 4: SORT |
| 644 | start = time.time() |
| 645 | sorted_df = df.sort('Total', descending=True) |
| 646 | sort_time = time.time() - start |
| 647 | results.append({ |
| 648 | "operation": "SORT", |
| 649 | "time": sort_time, |
| 650 | "rows_per_second": rows / sort_time |
| 651 | }) |
| 652 | |
| 653 | # Benchmark 5: Complex SQL |
| 654 | conn = duckdb.connect(':memory:') |
| 655 | conn.register('sheet', df) |
| 656 | start = time.time() |
| 657 | result = conn.execute(""" |
| 658 | SELECT Category, |
| 659 | COUNT(*) as count, |
| 660 | AVG(Price) as avg_price, |
| 661 | SUM(Total) as total_revenue |
| 662 | FROM sheet |
| 663 | GROUP BY Category |
| 664 | ORDER BY total_revenue DESC |
| 665 | """).pl() |
| 666 | sql_time = time.time() - start |
| 667 | conn.close() |
| 668 | |
| 669 | results.append({ |
| 670 | "operation": "Complex SQL", |
| 671 | "time": sql_time, |
| 672 | "rows_per_second": rows / sql_time |
| 673 | }) |
| 674 | |
| 675 | # Calculate average |
| 676 | total_ops = sum(r['rows_per_second'] for r in results) |
| 677 | avg_speed = total_ops / len(results) |
| 678 | |
| 679 | return { |
| 680 | "results": results, |
| 681 | "average_speed": avg_speed, |
| 682 | "rows_tested": rows, |
| 683 | "summary": f"Average: {avg_speed:,.0f} operations/second" |
| 684 | } |
| 685 | |
| 686 | @app.delete("/session/{session_id}") |
| 687 | async def delete_session(session_id: str): |
| 688 | """Delete session and its data""" |
| 689 | # Remove from memory |
| 690 | if session_id in sessions: |
| 691 | del sessions[session_id] |
| 692 | |
| 693 | # Remove from disk |
| 694 | user_dir = get_user_dir(session_id) |
| 695 | if user_dir.exists(): |
| 696 | import shutil |
| 697 | shutil.rmtree(user_dir) |
| 698 | |
| 699 | return {"message": "Session deleted", "session_id": session_id} |
| 700 | |
| 701 | if __name__ == "__main__": |
| 702 | import uvicorn |
| 703 | print(""" |
| 704 | ╔══════════════════════════════════════════════════╗ |
| 705 | ║ DBBasic Server - FastAPI + Polars + DuckDB ║ |
| 706 | ║ ║ |
| 707 | ║ Endpoints: ║ |
| 708 | ║ POST /session/create - Create new session ║ |
| 709 | ║ POST /generate - Generate test data ║ |
| 710 | ║ POST /calculate - Run calculations ║ |
| 711 | ║ POST /sql - Execute SQL queries ║ |
| 712 | ║ POST /benchmark - Run performance test ║ |
| 713 | ║ ║ |
| 714 | ║ Starting server on http://localhost:8000 ║ |
| 715 | ╚══════════════════════════════════════════════════╝ |
| 716 | """) |
| 717 | |
| 718 | uvicorn.run(app, host="0.0.0.0", port=8000, reload=True) |