File size: 4,009 Bytes
be6ee20 | 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 | # IDENTITY: backend/migrate_data.py
# ⚙️ GEAR: Data Migration (SQLite -> PostgreSQL)
import sqlite3
import os
from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv
# Load environment variables
load_dotenv()
# Configuration
SQLITE_DB_PATH = "edu_ai_vault.db"
POSTGRES_URL = os.getenv("DATABASE_URL")
if not POSTGRES_URL:
print("❌ Error: DATABASE_URL not found in .env file.")
exit(1)
# Fix for Neon/Heroku: SQLAlchemy requires 'postgresql://' instead of 'postgres://'
if POSTGRES_URL.startswith("postgres://"):
POSTGRES_URL = POSTGRES_URL.replace("postgres://", "postgresql://", 1)
def migrate():
print(f"🚀 Starting migration from {SQLITE_DB_PATH} to Neon PostgreSQL...")
# Connect to SQLite
if not os.path.exists(SQLITE_DB_PATH):
print(f"❌ Error: SQLite file {SQLITE_DB_PATH} not found.")
return
sqlite_conn = sqlite3.connect(SQLITE_DB_PATH)
sqlite_conn.row_factory = sqlite3.Row
sqlite_cursor = sqlite_conn.cursor()
# Connect to PostgreSQL
pg_engine = create_engine(POSTGRES_URL)
PgSession = sessionmaker(bind=pg_engine)
pg_session = PgSession()
try:
# 1. Migrate Users
print("👤 Migrating Users...")
sqlite_cursor.execute("SELECT * FROM users")
users = sqlite_cursor.fetchall()
for row in users:
# We use raw SQL to ensure IDs are preserved
pg_session.execute(
text("INSERT INTO users (id, username, role, sensory_mode, difficulty, ai_persona, semester_status, interests) "
"VALUES (:id, :username, :role, :sensory_mode, :difficulty, :ai_persona, :semester_status, :interests) "
"ON CONFLICT (id) DO NOTHING"),
dict(row)
)
# 2. Migrate Units
print("📚 Migrating Units...")
sqlite_cursor.execute("SELECT * FROM units")
units = sqlite_cursor.fetchall()
for row in units:
pg_session.execute(
text("INSERT INTO units (id, name, is_active, category, owner_id) "
"VALUES (:id, :name, :is_active, :category, :owner_id) "
"ON CONFLICT (id) DO NOTHING"),
dict(row)
)
# 3. Migrate Quiz History
print("📊 Migrating Quiz History...")
sqlite_cursor.execute("SELECT * FROM quiz_history")
quizzes = sqlite_cursor.fetchall()
for row in quizzes:
pg_session.execute(
text("INSERT INTO quiz_history (id, unit_name, score, total, pnl, timestamp, owner_id) "
"VALUES (:id, :unit_name, :score, :total, :pnl, :timestamp, :owner_id) "
"ON CONFLICT (id) DO NOTHING"),
dict(row)
)
# 4. Migrate Chat Messages
print("💬 Migrating Chat Messages...")
sqlite_cursor.execute("SELECT * FROM chat_messages")
chats = sqlite_cursor.fetchall()
for row in chats:
pg_session.execute(
text("INSERT INTO chat_messages (id, role, content, timestamp, owner_id) "
"VALUES (:id, :role, :content, :timestamp, :owner_id) "
"ON CONFLICT (id) DO NOTHING"),
dict(row)
)
pg_session.commit()
print("✅ Migration successful! All records synced to Neon.")
# Update sequences in Postgres (Important for ID generation)
print("🔄 Updating ID sequences...")
tables = ["users", "units", "quiz_history", "chat_messages"]
for table in tables:
pg_session.execute(text(f"SELECT setval('{table}_id_seq', (SELECT MAX(id) FROM {table}))"))
pg_session.commit()
except Exception as e:
print(f"❌ Migration failed: {e}")
pg_session.rollback()
finally:
sqlite_conn.close()
pg_session.close()
if __name__ == "__main__":
migrate()
|