Spaces:
Sleeping
Sleeping
Download app/database/models.py from fmbishu/image-search-api: direct link, hf CLI and curl.
- Browser
- Download file 11.6 kB
-
https://huggingface.co/spaces/fmbishu/image-search-api/resolve/main/app/database/models.py
- Command line
-
hf download hf://spaces/fmbishu/image-search-api/app/database/models.py
-
curl -L -o models.py https://huggingface.co/spaces/fmbishu/image-search-api/resolve/main/app/database/models.py
11.6 kB
| """Database models and persistence helpers for product metadata.""" | |
| from __future__ import annotations | |
| import sqlite3 | |
| from dataclasses import dataclass | |
| from typing import Any, Dict, List, Optional | |
| from datetime import datetime | |
| import json | |
| class Product: | |
| """Domain model representing a product stored in the catalog database.""" | |
| id: str | |
| title: str | |
| description: str | |
| price: float | |
| currency: str | |
| category: str | |
| brand: str | |
| image_url: str | |
| availability: str | |
| rating: Optional[float] = None | |
| review_count: Optional[int] = None | |
| created_at: Optional[datetime] = None | |
| updated_at: Optional[datetime] = None | |
| metadata: Optional[Dict[str, Any]] = None | |
| def to_dict(self) -> Dict[str, Any]: | |
| """Serialize the product to a JSON-friendly dictionary.""" | |
| def _serialize_timestamp(value: Optional[datetime]) -> Optional[str]: | |
| if value is None: | |
| return None | |
| if isinstance(value, datetime): | |
| return value.isoformat() | |
| # SQLite returns ISO-formatted strings; pass through unchanged | |
| if isinstance(value, str): | |
| return value | |
| return str(value) | |
| return { | |
| "id": self.id, | |
| "title": self.title, | |
| "description": self.description, | |
| "price": self.price, | |
| "currency": self.currency, | |
| "category": self.category, | |
| "brand": self.brand, | |
| "image_url": self.image_url, | |
| "availability": self.availability, | |
| "rating": self.rating, | |
| "review_count": self.review_count, | |
| "created_at": _serialize_timestamp(self.created_at), | |
| "updated_at": _serialize_timestamp(self.updated_at), | |
| "metadata": self.metadata or {}, | |
| } | |
| class ProductDatabase: | |
| """SQLite-backed data access layer used by the API and vector store manager.""" | |
| def __init__(self, db_path: str): | |
| self.db_path = db_path | |
| self.init_database() | |
| def init_database(self) -> None: | |
| """Ensure required tables and indexes exist.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| conn.execute( | |
| """ | |
| CREATE TABLE IF NOT EXISTS products ( | |
| id TEXT PRIMARY KEY, | |
| title TEXT NOT NULL, | |
| description TEXT, | |
| price REAL NOT NULL, | |
| currency TEXT DEFAULT 'XAF', | |
| category TEXT NOT NULL, | |
| brand TEXT, | |
| image_url TEXT NOT NULL, | |
| availability TEXT DEFAULT 'in_stock', | |
| rating REAL, | |
| review_count INTEGER, | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| metadata TEXT | |
| ) | |
| """ | |
| ) | |
| conn.execute( | |
| """ | |
| CREATE TABLE IF NOT EXISTS categories ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| name TEXT UNIQUE NOT NULL, | |
| parent_id INTEGER, | |
| description TEXT, | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| FOREIGN KEY (parent_id) REFERENCES categories (id) | |
| ) | |
| """ | |
| ) | |
| conn.execute( | |
| """ | |
| CREATE TABLE IF NOT EXISTS brands ( | |
| id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| name TEXT UNIQUE NOT NULL, | |
| description TEXT, | |
| logo_url TEXT, | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP | |
| ) | |
| """ | |
| ) | |
| conn.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)") | |
| conn.execute("CREATE INDEX IF NOT EXISTS idx_products_brand ON products(brand)") | |
| conn.execute("CREATE INDEX IF NOT EXISTS idx_products_price ON products(price)") | |
| conn.execute("CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating)") | |
| conn.execute("CREATE INDEX IF NOT EXISTS idx_products_availability ON products(availability)") | |
| conn.commit() | |
| def add_product(self, product: Product) -> None: | |
| """Insert or replace a product record.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| conn.execute( | |
| """ | |
| INSERT OR REPLACE INTO products | |
| (id, title, description, price, currency, category, brand, image_url, | |
| availability, rating, review_count, metadata, updated_at) | |
| VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP) | |
| """, | |
| ( | |
| product.id, | |
| product.title, | |
| product.description, | |
| product.price, | |
| product.currency, | |
| product.category, | |
| product.brand, | |
| product.image_url, | |
| product.availability, | |
| product.rating, | |
| product.review_count, | |
| json.dumps(product.metadata) if product.metadata else None, | |
| ), | |
| ) | |
| conn.commit() | |
| def get_product(self, product_id: str) -> Optional[Product]: | |
| """Fetch a product by identifier.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| conn.row_factory = sqlite3.Row | |
| cursor = conn.execute("SELECT * FROM products WHERE id = ?", (product_id,)) | |
| row = cursor.fetchone() | |
| if row: | |
| return self._row_to_product(row) | |
| return None | |
| def get_products( | |
| self, | |
| limit: int = 100, | |
| offset: int = 0, | |
| category: Optional[str] = None, | |
| brand: Optional[str] = None, | |
| min_price: Optional[float] = None, | |
| max_price: Optional[float] = None, | |
| availability: Optional[str] = None, | |
| ) -> List[Product]: | |
| """Return paginated products with optional filters.""" | |
| query = "SELECT * FROM products WHERE 1=1" | |
| params = [] | |
| if category: | |
| query += " AND category = ?" | |
| params.append(category) | |
| if brand: | |
| query += " AND brand = ?" | |
| params.append(brand) | |
| if min_price is not None: | |
| query += " AND price >= ?" | |
| params.append(min_price) | |
| if max_price is not None: | |
| query += " AND price <= ?" | |
| params.append(max_price) | |
| if availability: | |
| query += " AND availability = ?" | |
| params.append(availability) | |
| query += " ORDER BY created_at DESC LIMIT ? OFFSET ?" | |
| params.extend([limit, offset]) | |
| with sqlite3.connect(self.db_path) as conn: | |
| conn.row_factory = sqlite3.Row | |
| cursor = conn.execute(query, params) | |
| rows = cursor.fetchall() | |
| return [self._row_to_product(row) for row in rows] | |
| def get_all_products(self) -> List[Product]: | |
| """Return all products (used for initial embedding generation).""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| conn.row_factory = sqlite3.Row | |
| cursor = conn.execute("SELECT * FROM products ORDER BY id") | |
| rows = cursor.fetchall() | |
| return [self._row_to_product(row) for row in rows] | |
| def get_categories(self) -> List[str]: | |
| """Return the unique category values.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| cursor = conn.execute("SELECT DISTINCT category FROM products ORDER BY category") | |
| return [row[0] for row in cursor.fetchall()] | |
| def get_brands(self) -> List[str]: | |
| """Return the unique brand values.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| cursor = conn.execute("SELECT DISTINCT brand FROM products WHERE brand IS NOT NULL ORDER BY brand") | |
| return [row[0] for row in cursor.fetchall()] | |
| def search_products(self, query: str, limit: int = 20) -> List[Product]: | |
| """Simple LIKE-based text search used for admin tooling.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| conn.row_factory = sqlite3.Row | |
| cursor = conn.execute( | |
| """ | |
| SELECT * FROM products | |
| WHERE title LIKE ? OR description LIKE ? | |
| ORDER BY created_at DESC LIMIT ? | |
| """, | |
| (f"%{query}%", f"%{query}%", limit), | |
| ) | |
| rows = cursor.fetchall() | |
| return [self._row_to_product(row) for row in rows] | |
| def delete_product(self, product_id: str) -> bool: | |
| """Delete a product by ID. Returns True if a row was removed.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| cursor = conn.execute("DELETE FROM products WHERE id = ?", (product_id,)) | |
| conn.commit() | |
| return cursor.rowcount > 0 | |
| def update_product(self, product: Product) -> bool: | |
| """Persist updates to an existing product record.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| cursor = conn.execute( | |
| """ | |
| UPDATE products SET | |
| title = ?, | |
| description = ?, | |
| price = ?, | |
| currency = ?, | |
| category = ?, | |
| brand = ?, | |
| image_url = ?, | |
| availability = ?, | |
| rating = ?, | |
| review_count = ?, | |
| metadata = ?, | |
| updated_at = CURRENT_TIMESTAMP | |
| WHERE id = ? | |
| """, | |
| ( | |
| product.title, | |
| product.description, | |
| product.price, | |
| product.currency, | |
| product.category, | |
| product.brand, | |
| product.image_url, | |
| product.availability, | |
| product.rating, | |
| product.review_count, | |
| json.dumps(product.metadata) if product.metadata else None, | |
| product.id, | |
| ), | |
| ) | |
| conn.commit() | |
| return cursor.rowcount > 0 | |
| def get_product_count(self) -> int: | |
| """Return the total number of catalog entries.""" | |
| with sqlite3.connect(self.db_path) as conn: | |
| cursor = conn.execute("SELECT COUNT(*) FROM products") | |
| result = cursor.fetchone() | |
| return int(result[0]) if result else 0 | |
| def _row_to_product(self, row: sqlite3.Row) -> Product: | |
| """Convert a SQLite row into a Product dataclass instance.""" | |
| metadata = json.loads(row["metadata"]) if row["metadata"] else None | |
| return Product( | |
| id=row["id"], | |
| title=row["title"], | |
| description=row["description"] or "", | |
| price=row["price"], | |
| currency=row["currency"], | |
| category=row["category"], | |
| brand=row["brand"] or "", | |
| image_url=row["image_url"], | |
| availability=row["availability"], | |
| rating=row["rating"], | |
| review_count=row["review_count"], | |
| created_at=row["created_at"], | |
| updated_at=row["updated_at"], | |
| metadata=metadata, | |
| ) | |