Spaces:
Sleeping
Sleeping
Download scripts/migrate_to_postgres.py from fmbishu/image-search-api: direct link, hf CLI and curl.
- Browser
- Download file 9.69 kB
-
https://huggingface.co/spaces/fmbishu/image-search-api/resolve/main/scripts/migrate_to_postgres.py
- Command line
-
hf download hf://spaces/fmbishu/image-search-api/scripts/migrate_to_postgres.py
-
curl -L -o migrate_to_postgres.py https://huggingface.co/spaces/fmbishu/image-search-api/resolve/main/scripts/migrate_to_postgres.py
9.69 kB
| """ | |
| Migration script to migrate data from SQLite to PostgreSQL. | |
| This script: | |
| 1. Reads all products from SQLite database | |
| 2. Creates PostgreSQL tables with pgvector extension | |
| 3. Migrates all product data to PostgreSQL | |
| 4. Optionally migrates vector embeddings to pgvector | |
| Usage: | |
| # Using command line argument: | |
| python scripts/migrate_to_postgres.py \ | |
| --sqlite-path app/data/products.db \ | |
| --postgres-url postgresql://user:password@host:port/database | |
| # Using environment variable (recommended for Neon): | |
| export DATABASE_CONNECTION_STRING="postgresql://user:password@host:port/database" | |
| python scripts/migrate_to_postgres.py --sqlite-path app/data/products.db | |
| For Neon PostgreSQL: | |
| 1. Create a project at https://neon.tech | |
| 2. Copy the connection string from the dashboard | |
| 3. Use it as --postgres-url or set DATABASE_CONNECTION_STRING env var | |
| 4. Neon automatically includes pgvector extension | |
| """ | |
| import argparse | |
| import sqlite3 | |
| import json | |
| import sys | |
| import os | |
| from pathlib import Path | |
| from typing import List, Dict, Any, Optional | |
| from datetime import datetime | |
| try: | |
| import psycopg2 | |
| from psycopg2.extras import execute_values | |
| from psycopg2 import sql | |
| except ImportError: | |
| print("ERROR: psycopg2 is required. Install it with: pip install psycopg2") | |
| print("Note: You may also need: apt-get install libpq-dev python3-dev (Linux) or brew install postgresql (macOS)") | |
| sys.exit(1) | |
| def create_postgres_tables(conn, vector_dimension: int = 768) -> None: | |
| """Create PostgreSQL tables with pgvector extension.""" | |
| with conn.cursor() as cur: | |
| # Enable pgvector extension | |
| cur.execute("CREATE EXTENSION IF NOT EXISTS vector;") | |
| # Create products table | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS products ( | |
| id VARCHAR(255) PRIMARY KEY, | |
| title TEXT NOT NULL, | |
| description TEXT, | |
| price REAL NOT NULL, | |
| currency VARCHAR(10) DEFAULT 'XAF', | |
| category TEXT NOT NULL, | |
| brand TEXT, | |
| image_url TEXT NOT NULL, | |
| availability VARCHAR(50) DEFAULT 'in_stock', | |
| rating REAL, | |
| review_count INTEGER, | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| metadata JSONB | |
| ) | |
| """) | |
| # Create indexes | |
| cur.execute("CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)") | |
| cur.execute("CREATE INDEX IF NOT EXISTS idx_products_brand ON products(brand)") | |
| cur.execute("CREATE INDEX IF NOT EXISTS idx_products_price ON products(price)") | |
| cur.execute("CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating)") | |
| cur.execute("CREATE INDEX IF NOT EXISTS idx_products_availability ON products(availability)") | |
| # Create vector embeddings table with pgvector | |
| cur.execute(f""" | |
| CREATE TABLE IF NOT EXISTS product_embeddings ( | |
| product_id VARCHAR(255) PRIMARY KEY, | |
| vector vector({vector_dimension}), | |
| metadata JSONB, | |
| created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, | |
| FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE | |
| ) | |
| """) | |
| # Create vector index for similarity search | |
| cur.execute(""" | |
| CREATE INDEX IF NOT EXISTS product_embeddings_vector_idx | |
| ON product_embeddings | |
| USING ivfflat (vector vector_cosine_ops) | |
| WITH (lists = 100) | |
| """) | |
| conn.commit() | |
| print("✓ PostgreSQL tables created successfully") | |
| def migrate_products(sqlite_path: str, postgres_url: str) -> int: | |
| """Migrate products from SQLite to PostgreSQL.""" | |
| # Connect to SQLite | |
| sqlite_conn = sqlite3.connect(sqlite_path) | |
| sqlite_conn.row_factory = sqlite3.Row | |
| # Connect to PostgreSQL | |
| pg_conn = psycopg2.connect(postgres_url) | |
| try: | |
| # Create tables | |
| create_postgres_tables(pg_conn) | |
| # Read all products from SQLite | |
| cursor = sqlite_conn.execute("SELECT * FROM products") | |
| products = cursor.fetchall() | |
| if not products: | |
| print("No products found in SQLite database") | |
| return 0 | |
| print(f"Found {len(products)} products to migrate") | |
| # Prepare data for bulk insert | |
| products_data = [] | |
| for row in products: | |
| metadata_json = None | |
| if row['metadata']: | |
| try: | |
| metadata_json = json.loads(row['metadata']) | |
| except: | |
| metadata_json = {} | |
| products_data.append(( | |
| row['id'], | |
| row['title'], | |
| row['description'], | |
| row['price'], | |
| row.get('currency', 'XAF'), | |
| row['category'], | |
| row.get('brand'), | |
| row['image_url'], | |
| row.get('availability', 'in_stock'), | |
| row.get('rating'), | |
| row.get('review_count'), | |
| row.get('created_at'), | |
| row.get('updated_at'), | |
| json.dumps(metadata_json) if metadata_json else None | |
| )) | |
| # Bulk insert into PostgreSQL | |
| with pg_conn.cursor() as cur: | |
| insert_query = """ | |
| INSERT INTO products | |
| (id, title, description, price, currency, category, brand, image_url, | |
| availability, rating, review_count, created_at, updated_at, metadata) | |
| VALUES %s | |
| ON CONFLICT (id) DO UPDATE SET | |
| title = EXCLUDED.title, | |
| description = EXCLUDED.description, | |
| price = EXCLUDED.price, | |
| currency = EXCLUDED.currency, | |
| category = EXCLUDED.category, | |
| brand = EXCLUDED.brand, | |
| image_url = EXCLUDED.image_url, | |
| availability = EXCLUDED.availability, | |
| rating = EXCLUDED.rating, | |
| review_count = EXCLUDED.review_count, | |
| updated_at = EXCLUDED.updated_at, | |
| metadata = EXCLUDED.metadata | |
| """ | |
| execute_values( | |
| cur, | |
| insert_query, | |
| products_data, | |
| template=None, | |
| page_size=100 | |
| ) | |
| pg_conn.commit() | |
| print(f"✓ Migrated {len(products)} products to PostgreSQL") | |
| return len(products) | |
| finally: | |
| sqlite_conn.close() | |
| pg_conn.close() | |
| def main(): | |
| parser = argparse.ArgumentParser(description="Migrate SQLite database to PostgreSQL") | |
| parser.add_argument( | |
| "--sqlite-path", | |
| type=str, | |
| default="app/data/products.db", | |
| help="Path to SQLite database file" | |
| ) | |
| parser.add_argument( | |
| "--postgres-url", | |
| type=str, | |
| default=None, | |
| help="PostgreSQL connection URL (postgresql://user:password@host:port/database). " | |
| "If not provided, will use DATABASE_CONNECTION_STRING environment variable." | |
| ) | |
| parser.add_argument( | |
| "--vector-dimension", | |
| type=int, | |
| default=768, | |
| help="Vector dimension for pgvector (default: 768 for clip-ViT-L-14)" | |
| ) | |
| args = parser.parse_args() | |
| # Get PostgreSQL URL from argument or environment variable | |
| postgres_url = args.postgres_url or os.getenv("DATABASE_CONNECTION_STRING") | |
| if not postgres_url: | |
| print("ERROR: PostgreSQL connection URL is required!") | |
| print("Either provide --postgres-url argument or set DATABASE_CONNECTION_STRING environment variable") | |
| print("\nExample:") | |
| print(" export DATABASE_CONNECTION_STRING='postgresql://user:password@host:port/database'") | |
| print(" python scripts/migrate_to_postgres.py") | |
| sys.exit(1) | |
| # Ensure it's a PostgreSQL URL | |
| if not postgres_url.lower().startswith("postgresql://") and not postgres_url.lower().startswith("postgres://"): | |
| print("ERROR: Connection string must start with 'postgresql://' or 'postgres://'") | |
| print(f"Got: {postgres_url[:20]}...") | |
| sys.exit(1) | |
| # Check if SQLite file exists | |
| if not Path(args.sqlite_path).exists(): | |
| print(f"ERROR: SQLite database not found at {args.sqlite_path}") | |
| sys.exit(1) | |
| print(f"Starting migration from {args.sqlite_path} to PostgreSQL...") | |
| # Hide credentials in output - show only host and database | |
| if "@" in postgres_url: | |
| url_parts = postgres_url.split("@") | |
| if len(url_parts) > 1: | |
| print(f"PostgreSQL: {url_parts[-1]}") | |
| else: | |
| print("PostgreSQL: [connection string provided]") | |
| try: | |
| count = migrate_products(args.sqlite_path, postgres_url) | |
| print(f"\n✓ Migration completed successfully! Migrated {count} products.") | |
| print("\nNext steps:") | |
| print("1. Update DATABASE_CONNECTION_STRING in your environment variables") | |
| print("2. Set VECTOR_STORE_TYPE=pgvector") | |
| print("3. Set PGVECTOR_CONNECTION_STRING to the same PostgreSQL URL") | |
| print("4. Restart your application to use PostgreSQL") | |
| except Exception as e: | |
| print(f"ERROR: Migration failed: {e}") | |
| import traceback | |
| traceback.print_exc() | |
| sys.exit(1) | |
| if __name__ == "__main__": | |
| main() | |