Spaces:
Sleeping
Sleeping
File size: 9,685 Bytes
d34ac77 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 | """
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()
|