File size: 5,247 Bytes
469692c 9391632 84e6d52 | 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 | """
Migration script to move data from SQLite to Supabase PostgreSQL
Run this once to migrate existing data
"""
import os
import sys
from datetime import datetime
# Add src to path
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
def migrate_data():
"""Migrate all data from SQLite to Supabase"""
# Check if Supabase is configured
if not os.environ.get('SUPABASE_DB_URL'):
print("❌ SUPABASE_DB_URL not set. Please set it first.")
print(" export SUPABASE_DB_URL='postgresql://postgres:Adminhamza%402019@db.jkwtddcqbkpfqmuwxazm.supabase.co:5432/postgres'")
return False
try:
from database import Database as SQLiteDB
from database_supabase import SupabaseDatabase
except ImportError as e:
print(f"❌ Import error: {e}")
return False
# Initialize databases
print("📦 Initializing databases...")
sqlite_db = SQLiteDB(db_path=os.path.join('..', 'data', 'app.db'))
supabase_db = SupabaseDatabase()
print("✅ Databases initialized")
# Migrate users
print("\n👥 Migrating users...")
try:
users = sqlite_db.get_all_users()
migrated_users = 0
for user in users:
try:
# Check if user already exists
existing = supabase_db.get_user(user['user_id'])
if existing:
print(f" ⏭️ User {user['user_id']} already exists, skipping")
continue
supabase_db.create_user(user)
migrated_users += 1
except Exception as e:
print(f" ⚠️ Failed to migrate user {user.get('user_id', 'unknown')}: {e}")
print(f"✅ Migrated {migrated_users} users")
except Exception as e:
print(f"⚠️ Error migrating users: {e}")
# Migrate predictions
print("\n📊 Migrating predictions...")
try:
# SQLite doesn't have a get_all_predictions method, so we'll read directly
with sqlite_db.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM predictions')
predictions = [dict(row) for row in cursor.fetchall()]
migrated_predictions = 0
for pred in predictions:
try:
# Check if prediction already exists
# We'll just try to insert and let PostgreSQL handle duplicates
pred_data = {
'user_id': pred.get('user_id'),
'prediction': pred.get('prediction'),
'confidence': pred.get('confidence', 0),
'audio_id': pred.get('audio_id'),
'model_type': pred.get('model_type'),
'timestamp': pred.get('timestamp', datetime.now().isoformat())
}
supabase_db.create_prediction(pred_data)
migrated_predictions += 1
except Exception as e:
# Likely duplicate, skip
pass
print(f"✅ Migrated {migrated_predictions} predictions")
except Exception as e:
print(f"⚠️ Error migrating predictions: {e}")
# Migrate feedback
print("\n💬 Migrating feedback...")
try:
with sqlite_db.get_connection() as conn:
cursor = conn.cursor()
cursor.execute('SELECT * FROM feedback')
feedbacks = [dict(row) for row in cursor.fetchall()]
migrated_feedback = 0
for fb in feedbacks:
try:
fb_data = {
'audio_id': fb.get('audio_id'),
'user_id': fb.get('user_id'),
'predicted_label': fb.get('predicted_label'),
'correct_label': fb.get('correct_label'),
'is_correct': bool(fb.get('is_correct', 0)),
'confidence': fb.get('confidence'),
'timestamp': fb.get('timestamp', datetime.now().isoformat())
}
supabase_db.create_feedback(fb_data)
migrated_feedback += 1
except Exception as e:
# Likely duplicate, skip
pass
print(f"✅ Migrated {migrated_feedback} feedback entries")
except Exception as e:
print(f"⚠️ Error migrating feedback: {e}")
# Verify migration
print("\n🔍 Verifying migration...")
sqlite_stats = sqlite_db.get_user_stats()
supabase_stats = supabase_db.get_user_stats()
print(f"SQLite users: {sqlite_stats['total_users']}")
print(f"Supabase users: {supabase_stats['total_users']}")
if supabase_stats['total_users'] >= sqlite_stats['total_users']:
print("✅ Migration appears successful!")
else:
print("⚠️ Some data may not have been migrated")
return True
if __name__ == '__main__':
print("🚀 Starting SQLite to Supabase migration...")
print("=" * 60)
success = migrate_data()
print("=" * 60)
if success:
print("✅ Migration completed!")
else:
print("❌ Migration failed. Check errors above.")
|