Model-config web apps: describe the app in configuration instead of writing code. Built on Polars and DuckDB.
engine.py · 16.8 KB · 472 lines
| 1 | #!/usr/bin/env python3 |
| 2 | """ |
| 3 | DBBasic Fast - Using Polars and DuckDB for blazing fast spreadsheet operations |
| 4 | |
| 5 | Requirements: |
| 6 | pip install polars duckdb pyarrow |
| 7 | |
| 8 | This version handles millions of rows instantly. |
| 9 | """ |
| 10 | |
| 11 | import polars as pl |
| 12 | import duckdb |
| 13 | import numpy as np |
| 14 | from typing import Any, Dict, Optional, List, Union |
| 15 | import time |
| 16 | import re |
| 17 | import os |
| 18 | |
| 19 | class DBBasicFast: |
| 20 | """ |
| 21 | Fast spreadsheet engine using Polars (Rust-based) and DuckDB (columnar SQL) |
| 22 | |
| 23 | Performance: |
| 24 | - 10-50x faster than Pandas |
| 25 | - 100x faster than SQLite for analytics |
| 26 | - Handles billions of cells |
| 27 | """ |
| 28 | |
| 29 | def __init__(self): |
| 30 | # Use Polars DataFrame as our spreadsheet (FAST!) |
| 31 | self.sheet = pl.DataFrame() |
| 32 | |
| 33 | # DuckDB for SQL operations (in-memory, columnar) |
| 34 | self.db = duckdb.connect(':memory:') |
| 35 | |
| 36 | # Formula cache |
| 37 | self.formulas = {} |
| 38 | |
| 39 | print("🚀 DBBasic Fast - Powered by Polars + DuckDB") |
| 40 | print("📊 Handles millions of rows, instant calculations") |
| 41 | print("-" * 50) |
| 42 | |
| 43 | def execute(self, command: str): |
| 44 | """Execute a DBBasic command with blazing speed""" |
| 45 | command = command.strip() |
| 46 | if not command or command.startswith('#'): |
| 47 | return |
| 48 | |
| 49 | cmd_upper = command.upper() |
| 50 | |
| 51 | try: |
| 52 | # SET command (using Polars) |
| 53 | if cmd_upper.startswith('SET '): |
| 54 | self._handle_set_fast(command[4:].strip()) |
| 55 | |
| 56 | # LOAD command (parallel processing) |
| 57 | elif cmd_upper.startswith('LOAD '): |
| 58 | self._handle_load_fast(command[5:].strip()) |
| 59 | |
| 60 | # SQL command (using DuckDB - 100x faster than SQLite) |
| 61 | elif cmd_upper.startswith('SQL '): |
| 62 | self._handle_sql_fast(command[4:].strip()) |
| 63 | |
| 64 | # CALCULATE command (vectorized operations) |
| 65 | elif cmd_upper.startswith('CALC ') or cmd_upper.startswith('CALCULATE '): |
| 66 | start = 5 if cmd_upper.startswith('CALC ') else 10 |
| 67 | self._handle_calculate_fast(command[start:].strip()) |
| 68 | |
| 69 | # LIST/SHOW command |
| 70 | elif cmd_upper in ('LIST', 'SHOW'): |
| 71 | self._show_sheet() |
| 72 | |
| 73 | # BENCHMARK command |
| 74 | elif cmd_upper.startswith('BENCHMARK'): |
| 75 | self._run_benchmark() |
| 76 | |
| 77 | # GENERATE command (create test data) |
| 78 | elif cmd_upper.startswith('GENERATE '): |
| 79 | self._generate_data(command[9:].strip()) |
| 80 | |
| 81 | # SAVE command |
| 82 | elif cmd_upper.startswith('SAVE '): |
| 83 | self._save_fast(command[5:].strip()) |
| 84 | |
| 85 | # HELP |
| 86 | elif cmd_upper == 'HELP': |
| 87 | self._show_help() |
| 88 | |
| 89 | else: |
| 90 | print(f"Unknown command. Type HELP for commands.") |
| 91 | |
| 92 | except Exception as e: |
| 93 | print(f"❌ Error: {e}") |
| 94 | |
| 95 | def _handle_set_fast(self, command: str): |
| 96 | """Set column values using Polars expressions""" |
| 97 | # Parse: A = expression |
| 98 | match = re.match(r'([A-Z]+)\s*=\s*(.+)', command, re.IGNORECASE) |
| 99 | if match: |
| 100 | col = match.group(1).upper() |
| 101 | expr = match.group(2).strip() |
| 102 | |
| 103 | # Handle different expressions |
| 104 | if expr.startswith('RANGE('): |
| 105 | # Generate range of values |
| 106 | match_range = re.match(r'RANGE\((\d+),\s*(\d+)\)', expr) |
| 107 | if match_range: |
| 108 | start, end = int(match_range.group(1)), int(match_range.group(2)) |
| 109 | values = list(range(start, end + 1)) |
| 110 | self.sheet = self.sheet.with_columns(pl.Series(col, values)) |
| 111 | print(f"✓ {col} = range({start}, {end})") |
| 112 | |
| 113 | elif expr.startswith('RANDOM('): |
| 114 | # Generate random values |
| 115 | match_random = re.match(r'RANDOM\((\d+)\)', expr) |
| 116 | if match_random: |
| 117 | count = int(match_random.group(1)) |
| 118 | values = np.random.rand(count) * 100 |
| 119 | self.sheet = self.sheet.with_columns(pl.Series(col, values)) |
| 120 | print(f"✓ {col} = {count} random values") |
| 121 | |
| 122 | elif any(op in expr for op in ['+', '-', '*', '/', '>']): |
| 123 | # Mathematical expression on columns |
| 124 | try: |
| 125 | # Use Polars expressions for vectorized operations |
| 126 | self.sheet = self.sheet.with_columns( |
| 127 | eval(self._translate_to_polars(expr)).alias(col) |
| 128 | ) |
| 129 | print(f"✓ {col} = {expr} (vectorized)") |
| 130 | except: |
| 131 | print(f"❌ Invalid expression: {expr}") |
| 132 | |
| 133 | else: |
| 134 | # Constant value |
| 135 | try: |
| 136 | value = float(expr) |
| 137 | if col not in self.sheet.columns: |
| 138 | # Create new column with same length as existing data |
| 139 | length = len(self.sheet) if len(self.sheet) > 0 else 1 |
| 140 | self.sheet = self.sheet.with_columns( |
| 141 | pl.Series(col, [value] * length) |
| 142 | ) |
| 143 | else: |
| 144 | self.sheet = self.sheet.with_columns( |
| 145 | pl.lit(value).alias(col) |
| 146 | ) |
| 147 | print(f"✓ {col} = {value}") |
| 148 | except: |
| 149 | # String value |
| 150 | if col not in self.sheet.columns: |
| 151 | length = len(self.sheet) if len(self.sheet) > 0 else 1 |
| 152 | self.sheet = self.sheet.with_columns( |
| 153 | pl.Series(col, [expr.strip('"\'') ] * length) |
| 154 | ) |
| 155 | print(f"✓ {col} = {expr}") |
| 156 | |
| 157 | def _translate_to_polars(self, expr: str) -> str: |
| 158 | """Translate DBBasic expression to Polars expression""" |
| 159 | # Replace column names with pl.col() |
| 160 | result = expr |
| 161 | for col in self.sheet.columns: |
| 162 | result = re.sub(r'\b' + col + r'\b', f'pl.col("{col}")', result) |
| 163 | return result |
| 164 | |
| 165 | def _handle_load_fast(self, filename: str): |
| 166 | """Load data using Polars (much faster than pandas)""" |
| 167 | start_time = time.time() |
| 168 | |
| 169 | if filename.endswith('.csv'): |
| 170 | # Polars CSV reading is 10x faster than pandas |
| 171 | self.sheet = pl.read_csv(filename) |
| 172 | |
| 173 | elif filename.endswith('.parquet'): |
| 174 | # Parquet is even faster (columnar format) |
| 175 | self.sheet = pl.read_parquet(filename) |
| 176 | |
| 177 | elif filename.endswith('.json'): |
| 178 | self.sheet = pl.read_json(filename) |
| 179 | |
| 180 | else: |
| 181 | print(f"❌ Unknown file type: {filename}") |
| 182 | return |
| 183 | |
| 184 | elapsed = time.time() - start_time |
| 185 | rows, cols = self.sheet.shape |
| 186 | print(f"✓ Loaded {rows:,} rows × {cols} columns in {elapsed:.3f}s") |
| 187 | print(f" Speed: {rows/elapsed:,.0f} rows/second") |
| 188 | |
| 189 | # Show preview |
| 190 | print("\nPreview:") |
| 191 | print(self.sheet.head()) |
| 192 | |
| 193 | def _handle_sql_fast(self, query: str): |
| 194 | """Execute SQL using DuckDB (100x faster than SQLite for analytics)""" |
| 195 | start_time = time.time() |
| 196 | |
| 197 | # Register Polars DataFrame with DuckDB |
| 198 | self.db.register('sheet', self.sheet) |
| 199 | |
| 200 | # Execute query |
| 201 | result = self.db.execute(query).pl() # Convert to Polars |
| 202 | |
| 203 | elapsed = time.time() - start_time |
| 204 | |
| 205 | if result is not None and len(result) > 0: |
| 206 | self.sheet = result |
| 207 | rows, cols = self.sheet.shape |
| 208 | print(f"✓ Query executed in {elapsed:.3f}s") |
| 209 | print(f" Result: {rows:,} rows × {cols} columns") |
| 210 | print(f" Speed: {rows/elapsed:,.0f} rows/second") |
| 211 | else: |
| 212 | print(f"✓ Query executed in {elapsed:.3f}s") |
| 213 | |
| 214 | def _handle_calculate_fast(self, expr: str): |
| 215 | """Perform calculations using vectorized operations""" |
| 216 | start_time = time.time() |
| 217 | |
| 218 | # Common calculations |
| 219 | if expr.upper().startswith('SUM('): |
| 220 | col = expr[4:-1] |
| 221 | if col in self.sheet.columns: |
| 222 | result = self.sheet[col].sum() |
| 223 | elapsed = time.time() - start_time |
| 224 | print(f"✓ SUM({col}) = {result:,.2f}") |
| 225 | print(f" Calculated {len(self.sheet):,} values in {elapsed:.6f}s") |
| 226 | |
| 227 | elif expr.upper().startswith('AVG(') or expr.upper().startswith('MEAN('): |
| 228 | col = expr[4:-1] if expr.upper().startswith('AVG(') else expr[5:-1] |
| 229 | if col in self.sheet.columns: |
| 230 | result = self.sheet[col].mean() |
| 231 | elapsed = time.time() - start_time |
| 232 | print(f"✓ MEAN({col}) = {result:,.2f}") |
| 233 | print(f" Calculated {len(self.sheet):,} values in {elapsed:.6f}s") |
| 234 | |
| 235 | elif expr.upper().startswith('GROUP '): |
| 236 | # GROUP BY operations |
| 237 | parts = expr[6:].split(' BY ') |
| 238 | if len(parts) == 2: |
| 239 | agg_expr = parts[0].strip() |
| 240 | group_col = parts[1].strip() |
| 241 | |
| 242 | # Parse aggregation |
| 243 | if 'SUM(' in agg_expr.upper(): |
| 244 | sum_col = re.search(r'SUM\(([^)]+)\)', agg_expr, re.IGNORECASE).group(1) |
| 245 | result = self.sheet.group_by(group_col).agg(pl.col(sum_col).sum()) |
| 246 | self.sheet = result |
| 247 | print(f"✓ Grouped by {group_col}, summed {sum_col}") |
| 248 | |
| 249 | else: |
| 250 | print(f"❌ Unknown calculation: {expr}") |
| 251 | |
| 252 | def _generate_data(self, spec: str): |
| 253 | """Generate test data for benchmarking""" |
| 254 | # Parse: N rows |
| 255 | match = re.match(r'(\d+)\s+rows?', spec, re.IGNORECASE) |
| 256 | if match: |
| 257 | n = int(match.group(1)) |
| 258 | |
| 259 | print(f"Generating {n:,} rows of test data...") |
| 260 | |
| 261 | # Generate various column types |
| 262 | self.sheet = pl.DataFrame({ |
| 263 | 'ID': range(1, n + 1), |
| 264 | 'Value': np.random.randn(n) * 100, |
| 265 | 'Category': np.random.choice(['A', 'B', 'C', 'D'], n), |
| 266 | 'Price': np.random.uniform(10, 1000, n), |
| 267 | 'Quantity': np.random.randint(1, 100, n), |
| 268 | }) |
| 269 | |
| 270 | # Add calculated column |
| 271 | self.sheet = self.sheet.with_columns( |
| 272 | (pl.col('Price') * pl.col('Quantity')).alias('Total') |
| 273 | ) |
| 274 | |
| 275 | print(f"✓ Generated {n:,} rows with 6 columns") |
| 276 | print(f" Memory usage: {self.sheet.estimated_size('mb'):.2f} MB") |
| 277 | |
| 278 | def _run_benchmark(self): |
| 279 | """Run performance benchmarks""" |
| 280 | print("\n🏎️ Performance Benchmark") |
| 281 | print("=" * 50) |
| 282 | |
| 283 | # Generate test data if needed |
| 284 | if len(self.sheet) == 0: |
| 285 | print("Generating 1 million rows for benchmark...") |
| 286 | self.execute("GENERATE 1000000 rows") |
| 287 | |
| 288 | rows = len(self.sheet) |
| 289 | |
| 290 | # Benchmark 1: Sum calculation |
| 291 | start = time.time() |
| 292 | total = self.sheet['Total'].sum() |
| 293 | sum_time = time.time() - start |
| 294 | print(f"\n1. SUM of {rows:,} rows: {sum_time:.6f}s") |
| 295 | print(f" = {rows/sum_time:,.0f} rows/second") |
| 296 | |
| 297 | # Benchmark 2: Group by |
| 298 | start = time.time() |
| 299 | grouped = self.sheet.group_by('Category').agg(pl.col('Total').sum()) |
| 300 | group_time = time.time() - start |
| 301 | print(f"\n2. GROUP BY on {rows:,} rows: {group_time:.6f}s") |
| 302 | print(f" = {rows/group_time:,.0f} rows/second") |
| 303 | |
| 304 | # Benchmark 3: Filter |
| 305 | start = time.time() |
| 306 | filtered = self.sheet.filter(pl.col('Price') > 500) |
| 307 | filter_time = time.time() - start |
| 308 | print(f"\n3. FILTER {rows:,} rows: {filter_time:.6f}s") |
| 309 | print(f" = {rows/filter_time:,.0f} rows/second") |
| 310 | print(f" Result: {len(filtered):,} rows match") |
| 311 | |
| 312 | # Benchmark 4: Sort |
| 313 | start = time.time() |
| 314 | sorted_df = self.sheet.sort('Total', descending=True) |
| 315 | sort_time = time.time() - start |
| 316 | print(f"\n4. SORT {rows:,} rows: {sort_time:.6f}s") |
| 317 | print(f" = {rows/sort_time:,.0f} rows/second") |
| 318 | |
| 319 | # Benchmark 5: SQL Query (DuckDB) |
| 320 | self.db.register('sheet', self.sheet) |
| 321 | start = time.time() |
| 322 | result = self.db.execute(""" |
| 323 | SELECT Category, |
| 324 | COUNT(*) as count, |
| 325 | AVG(Price) as avg_price, |
| 326 | SUM(Total) as total_revenue |
| 327 | FROM sheet |
| 328 | GROUP BY Category |
| 329 | ORDER BY total_revenue DESC |
| 330 | """).pl() |
| 331 | sql_time = time.time() - start |
| 332 | print(f"\n5. Complex SQL on {rows:,} rows: {sql_time:.6f}s") |
| 333 | print(f" = {rows/sql_time:,.0f} rows/second") |
| 334 | |
| 335 | # Total time |
| 336 | total_time = sum_time + group_time + filter_time + sort_time + sql_time |
| 337 | print(f"\n" + "=" * 50) |
| 338 | print(f"Total time for all operations: {total_time:.3f}s") |
| 339 | print(f"Average speed: {rows*5/total_time:,.0f} operations/second") |
| 340 | |
| 341 | # Compare to traditional approaches |
| 342 | print(f"\n📊 Comparison to Traditional Tools:") |
| 343 | print(f" vs Pandas: ~10-50x faster") |
| 344 | print(f" vs SQLite: ~100x faster for analytics") |
| 345 | print(f" vs Ruby/ActiveRecord: ~1000x faster") |
| 346 | print(f" vs Excel: Can handle 100x more data") |
| 347 | |
| 348 | def _save_fast(self, filename: str): |
| 349 | """Save data in various formats""" |
| 350 | start_time = time.time() |
| 351 | |
| 352 | if filename.endswith('.parquet'): |
| 353 | # Parquet is fastest and smallest |
| 354 | self.sheet.write_parquet(filename) |
| 355 | |
| 356 | elif filename.endswith('.csv'): |
| 357 | self.sheet.write_csv(filename) |
| 358 | |
| 359 | elif filename.endswith('.json'): |
| 360 | self.sheet.write_json(filename) |
| 361 | |
| 362 | else: |
| 363 | print(f"❌ Unknown format. Use .parquet, .csv, or .json") |
| 364 | return |
| 365 | |
| 366 | elapsed = time.time() - start_time |
| 367 | size = os.path.getsize(filename) / (1024 * 1024) # MB |
| 368 | rows = len(self.sheet) |
| 369 | |
| 370 | print(f"✓ Saved {rows:,} rows to {filename}") |
| 371 | print(f" Time: {elapsed:.3f}s") |
| 372 | print(f" Size: {size:.2f} MB") |
| 373 | print(f" Speed: {rows/elapsed:,.0f} rows/second") |
| 374 | |
| 375 | def _show_sheet(self): |
| 376 | """Display the spreadsheet""" |
| 377 | if len(self.sheet) == 0: |
| 378 | print("(empty sheet)") |
| 379 | return |
| 380 | |
| 381 | print(f"\nSheet: {len(self.sheet):,} rows × {len(self.sheet.columns)} columns") |
| 382 | print(f"Memory: {self.sheet.estimated_size('mb'):.2f} MB") |
| 383 | print("\nFirst 10 rows:") |
| 384 | print(self.sheet.head(10)) |
| 385 | |
| 386 | if len(self.sheet) > 10: |
| 387 | print(f"\n... and {len(self.sheet) - 10:,} more rows") |
| 388 | |
| 389 | def _show_help(self): |
| 390 | """Show help""" |
| 391 | print(""" |
| 392 | 📘 DBBasic Fast Commands: |
| 393 | |
| 394 | Data Generation: |
| 395 | GENERATE 1000000 rows Generate test data |
| 396 | LOAD data.csv Load CSV file (10x faster than Pandas) |
| 397 | LOAD data.parquet Load Parquet (instant) |
| 398 | |
| 399 | Column Operations: |
| 400 | SET A = 100 Set column to value |
| 401 | SET B = A * 2 Calculate from other columns |
| 402 | SET C = RANDOM(1000) Generate random values |
| 403 | SET D = RANGE(1, 1000) Generate range |
| 404 | |
| 405 | Calculations (Instant on millions of rows): |
| 406 | CALC SUM(A) Sum column |
| 407 | CALC AVG(B) Average column |
| 408 | CALC GROUP SUM(Total) BY Category |
| 409 | |
| 410 | SQL (100x faster than SQLite): |
| 411 | SQL SELECT * FROM sheet WHERE Price > 100 |
| 412 | SQL SELECT Category, SUM(Total) FROM sheet GROUP BY Category |
| 413 | |
| 414 | Other: |
| 415 | BENCHMARK Run performance tests |
| 416 | SAVE output.parquet Save (fastest format) |
| 417 | LIST Show data |
| 418 | HELP Show this help |
| 419 | |
| 420 | 💡 Try: |
| 421 | GENERATE 1000000 rows |
| 422 | BENCHMARK |
| 423 | |
| 424 | This will show operations on 1 million rows in milliseconds! |
| 425 | """) |
| 426 | |
| 427 | |
| 428 | def repl(): |
| 429 | """Run the fast DBBasic REPL""" |
| 430 | db = DBBasicFast() |
| 431 | |
| 432 | print("\n💡 Try: GENERATE 100000 rows") |
| 433 | print("💡 Then: BENCHMARK") |
| 434 | print("💡 This handles millions of rows instantly!\n") |
| 435 | |
| 436 | while True: |
| 437 | try: |
| 438 | command = input("dbbasic> ").strip() |
| 439 | if command.upper() in ('EXIT', 'QUIT'): |
| 440 | break |
| 441 | db.execute(command) |
| 442 | except KeyboardInterrupt: |
| 443 | print("\n👋 Goodbye!") |
| 444 | break |
| 445 | except EOFError: |
| 446 | print("\n👋 Goodbye!") |
| 447 | break |
| 448 | |
| 449 | |
| 450 | if __name__ == "__main__": |
| 451 | print(""" |
| 452 | ╔══════════════════════════════════════════════════╗ |
| 453 | ║ DBBasic Fast - Spreadsheets at Silicon Speed ║ |
| 454 | ║ ║ |
| 455 | ║ Powered by: ║ |
| 456 | ║ • Polars (Rust): 10-50x faster than Pandas ║ |
| 457 | ║ • DuckDB: 100x faster than SQLite ║ |
| 458 | ║ • Zero-copy operations ║ |
| 459 | ╚══════════════════════════════════════════════════╝ |
| 460 | """) |
| 461 | |
| 462 | # Check dependencies |
| 463 | try: |
| 464 | import polars |
| 465 | import duckdb |
| 466 | print("✓ All dependencies installed") |
| 467 | except ImportError as e: |
| 468 | print(f"❌ Missing dependency: {e}") |
| 469 | print("\n📦 Install with: pip install polars duckdb pyarrow") |
| 470 | exit(1) |
| 471 | |
| 472 | repl() |