attendx-api / scripts /sync_to_supabase.py
iamudit02's picture
feat(db): add Supabase data migration and sync script
8722fe5
Raw History Blame Contribute Delete
3.82 kB
"""
Sync all data from local SQLite (attendx.db) to Supabase PostgreSQL
"""
import sqlite3
import psycopg2
import json
import os
import sys
# Add backend directory to sys.path
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from app.database.session import Base, engine
from app.models import models
# Ensure all tables exist in Supabase
print("1. Initializing schema tables in Supabase...")
Base.metadata.create_all(bind=engine)
print("Schema verified.")
# Connect to local SQLite (backend/attendx.db)
sqlite_path = os.path.join(os.path.dirname(os.path.dirname(os.path.abspath(__file__))), "attendx.db")
if not os.path.exists(sqlite_path) or os.path.getsize(sqlite_path) < 1000:
sqlite_path = os.path.join(os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__)))), "backend", "attendx.db")
print(f"2. Connecting to SQLite at: {sqlite_path}")
sqlite_conn = sqlite3.connect(sqlite_path)
sqlite_conn.row_factory = sqlite3.Row
s_cur = sqlite_conn.cursor()
# Connect to Supabase
pg_url = "postgresql://postgres:doctorvondoom1812@db.vffgjjlucktyrfjhbqbp.supabase.co:5432/postgres"
print("3. Connecting to Supabase PostgreSQL...")
pg_conn = psycopg2.connect(pg_url)
pg_cur = pg_conn.cursor()
tables = [
"users",
"classes",
"subjects",
"students",
"enrollments",
"face_embeddings",
"attendance_sessions",
"attendance_records"
]
boolean_columns = {
"users": ["is_active"],
"classes": ["active"],
"students": ["active", "face_registration_complete"]
}
total_synced = 0
for table in tables:
try:
s_cur.execute(f"SELECT * FROM {table}")
rows = s_cur.fetchall()
except Exception as e:
print(f"Table {table} not found in SQLite: {e}")
continue
if not rows:
print(f"Table {table}: 0 rows to sync")
continue
col_names = [description[0] for description in s_cur.description]
cols_str = ', '.join(f'"{c}"' for c in col_names)
placeholders = ', '.join(['%s'] * len(col_names))
update_set = ', '.join([f'"{c}" = EXCLUDED."{c}"' for c in col_names if c != 'id'])
count = 0
for r in rows:
vals = list(r)
# Cast boolean columns
if table in boolean_columns:
for b_col in boolean_columns[table]:
if b_col in col_names:
b_idx = col_names.index(b_col)
if vals[b_idx] is not None:
vals[b_idx] = bool(vals[b_idx])
# Handle JSON embedding column
if table == 'face_embeddings':
idx = col_names.index('embedding')
if isinstance(vals[idx], str):
vals[idx] = json.dumps(json.loads(vals[idx]))
elif isinstance(vals[idx], list):
vals[idx] = json.dumps(vals[idx])
query = f"""
INSERT INTO {table} ({cols_str})
VALUES ({placeholders})
ON CONFLICT (id) DO UPDATE SET {update_set};
"""
try:
pg_cur.execute(query, vals)
count += 1
except Exception as err:
print(f"Error syncing row {vals[0]} in {table}: {err}")
pg_conn.rollback()
break
else:
pg_conn.commit()
# Reset serial sequence
try:
pg_cur.execute(f"SELECT setval(pg_get_serial_sequence('{table}', 'id'), COALESCE((SELECT MAX(id) + 1 FROM {table}), 1), false);")
pg_conn.commit()
except Exception:
pg_conn.rollback()
print(f"[OK] Synced {count} rows in table '{table}' to Supabase!")
total_synced += count
sqlite_conn.close()
pg_conn.close()
print(f"\n[SUCCESS] COMPLETE: All {total_synced} database records, student profiles, and 3D face scan vectors are permanently saved in Supabase!")