Spaces:
Sleeping
Sleeping
| import sqlite3 | |
| import pandas as pd | |
| from fastapi import FastAPI | |
| from fastapi.responses import HTMLResponse, JSONResponse | |
| DB_PATH = "CarOwners_Full_Final.db" | |
| PAGE_SIZE = 200 # rows per page | |
| app = FastAPI() | |
| # Connect SQLite | |
| conn = sqlite3.connect(DB_PATH, check_same_thread=False) | |
| cursor = conn.cursor() | |
| # Create indexes for fast filtering/searching | |
| cursor.execute("CREATE INDEX IF NOT EXISTS idx_city ON owners(City);") | |
| cursor.execute("CREATE INDEX IF NOT EXISTS idx_name ON owners(Name);") | |
| cursor.execute("CREATE INDEX IF NOT EXISTS idx_mobile ON owners([Mobile No]);") | |
| conn.commit() | |
| # ========================= | |
| # API endpoint: fetch paginated data with filters/search | |
| # ========================= | |
| def get_data(offset: int = 0, city: str = "", search: str = ""): | |
| query = "SELECT * FROM owners WHERE 1=1" | |
| params = [] | |
| if city: | |
| query += " AND LOWER(City) LIKE ?" | |
| params.append(f"%{city.lower()}%") | |
| if search: | |
| query += " AND (LOWER(Name) LIKE ? OR [Mobile No] LIKE ?)" | |
| params.extend([f"%{search.lower()}%", f"%{search}%"]) | |
| query += " LIMIT ? OFFSET ?" | |
| params.extend([PAGE_SIZE, offset]) | |
| df = pd.read_sql_query(query, conn, params=params) | |
| # 🔹 JSON-safe conversion | |
| # Replace NaN with None | |
| df = df.where(pd.notnull(df), None) | |
| # Replace inf/-inf with None | |
| df = df.replace([float("inf"), float("-inf")], None) | |
| # Convert all numeric columns to strings to prevent JSON float overflow | |
| numeric_cols = df.select_dtypes(include=["float64", "int64"]).columns | |
| for col in numeric_cols: | |
| df[col] = df[col].apply(lambda x: str(x) if x is not None else None) | |
| return JSONResponse(df.to_dict(orient="records")) | |
| # ========================= | |
| # API endpoint: total count for pagination | |
| # ========================= | |
| def get_count(city: str = "", search: str = ""): | |
| query = "SELECT COUNT(*) FROM owners WHERE 1=1" | |
| params = [] | |
| if city: | |
| query += " AND LOWER(City) LIKE ?" | |
| params.append(f"%{city.lower()}%") | |
| if search: | |
| query += " AND (LOWER(Name) LIKE ? OR [Mobile No] LIKE ?)" | |
| params.extend([f"%{search.lower()}%", f"%{search}%"]) | |
| if params: | |
| cursor.execute(query, tuple(params)) | |
| else: | |
| cursor.execute(query) | |
| total = cursor.fetchone()[0] | |
| return {"total": total} | |
| # ========================= | |
| # Frontend page with infinite scroll + filters | |
| # ========================= | |
| def home(): | |
| html = """ | |
| <html> | |
| <head> | |
| <title>Car Owners Data - Infinite Scroll + Search</title> | |
| <style> | |
| body { font-family: Arial; margin: 20px; } | |
| table { border-collapse: collapse; width: 100%; } | |
| th, td { border: 1px solid #ddd; padding: 8px; } | |
| th { background-color: #f2f2f2; } | |
| #loader { text-align: center; padding: 10px; } | |
| .filter { margin-bottom: 20px; } | |
| </style> | |
| </head> | |
| <body> | |
| <h1>📋 Car Owners Data</h1> | |
| <div class="filter"> | |
| <label>City: <input type="text" id="city"></label> | |
| <label>Search (Name or Mobile): <input type="text" id="search"></label> | |
| <button onclick="applyFilter()">Apply</button> | |
| </div> | |
| <table id="data-table"> | |
| <thead> | |
| <tr> | |
| <th>Name</th> | |
| <th>Mobile No</th> | |
| <th>Address</th> | |
| <th>Pin Code</th> | |
| <th>City</th> | |
| <th>Submodel</th> | |
| <th>Model</th> | |
| <th>Assettype</th> | |
| <th>Misstatus</th> | |
| <th>Tenor</th> | |
| <th>Roi</th> | |
| <th>Irr</th> | |
| <th>Noadvemi</th> | |
| <th>Emiamt</th> | |
| <th>Netltv</th> | |
| <th>Mfrsubded</th> | |
| <th>Mfrsubnded</th> | |
| <th>Dlrsubded</th> | |
| <th>Dlrsubnded</th> | |
| <th>Dsasubded</th> | |
| <th>Promotiondesc</th> | |
| <th>Product</th> | |
| </tr> | |
| </thead> | |
| <tbody id="tbody"></tbody> | |
| </table> | |
| <div id="loader">Loading...</div> | |
| <script> | |
| let offset = 0; | |
| const PAGE_SIZE = 200; | |
| let loading = false; | |
| let cityFilter = ""; | |
| let searchFilter = ""; | |
| async function loadData() { | |
| if (loading) return; | |
| loading = true; | |
| document.getElementById('loader').innerText = "Loading..."; | |
| try { | |
| let response = await fetch(`/api/data?offset=${offset}&city=${cityFilter}&search=${searchFilter}`); | |
| if (!response.ok) throw new Error("Server error"); | |
| let data = await response.json(); | |
| let tbody = document.getElementById('tbody'); | |
| for (let row of data) { | |
| let tr = document.createElement('tr'); | |
| for (let key in row) { | |
| let td = document.createElement('td'); | |
| td.innerText = row[key] === null ? "" : row[key]; | |
| tr.appendChild(td); | |
| } | |
| tbody.appendChild(tr); | |
| } | |
| if (data.length < PAGE_SIZE) { | |
| document.getElementById('loader').innerText = "No more data"; | |
| } else { | |
| document.getElementById('loader').innerText = "Scroll down to load more..."; | |
| } | |
| offset += PAGE_SIZE; | |
| loading = false; | |
| } catch (err) { | |
| document.getElementById('loader').innerText = "Error loading data"; | |
| loading = false; | |
| } | |
| } | |
| function applyFilter() { | |
| cityFilter = document.getElementById('city').value; | |
| searchFilter = document.getElementById('search').value; | |
| offset = 0; | |
| document.getElementById('tbody').innerHTML = ""; | |
| loadData(); | |
| } | |
| // Infinite scroll | |
| window.onscroll = function() { | |
| if ((window.innerHeight + window.scrollY) >= document.body.offsetHeight - 50) { | |
| loadData(); | |
| } | |
| } | |
| // Initial load | |
| loadData(); | |
| </script> | |
| </body> | |
| </html> | |
| """ | |
| return HTMLResponse(html) |