Spaces:
Paused
Paused
| """ | |
| Migración SQLite → PostgreSQL — CrowData. | |
| Uso: | |
| 1. Configurar DATABASE_URL en .env con la conexión PostgreSQL: | |
| DATABASE_URL=postgresql+asyncpg://user:password@localhost:5432/crowdata | |
| 2. Instalar dependencias: | |
| pip install asyncpg | |
| 3. Ejecutar: | |
| python -m app.migrations.sqlite_to_pg | |
| 4. Verificar que la tabla login_history existe: | |
| python -c "from app.database import init_db; import asyncio; asyncio.run(init_db())" | |
| """ | |
| import asyncio | |
| import logging | |
| import sqlite3 | |
| from datetime import datetime | |
| from pathlib import Path | |
| logging.basicConfig(level=logging.INFO) | |
| logger = logging.getLogger(__name__) | |
| DB_PATH = Path(__file__).parent.parent / "crowdata.db" | |
| def get_sqlite_data(): | |
| """Lee todos los datos de SQLite.""" | |
| if not DB_PATH.exists(): | |
| logger.error(f"SQLite DB not found: {DB_PATH}") | |
| return {} | |
| conn = sqlite3.connect(str(DB_PATH)) | |
| conn.row_factory = sqlite3.Row | |
| cursor = conn.cursor() | |
| data = {} | |
| # Listar todas las tablas | |
| cursor.execute("SELECT name FROM sqlite_master WHERE type='table'") | |
| tables = [row[0] for row in cursor.fetchall()] | |
| logger.info(f"Tablas encontradas en SQLite: {tables}") | |
| for table in tables: | |
| cursor.execute(f"SELECT * FROM {table}") | |
| rows = [dict(row) for row in cursor.fetchall()] | |
| data[table] = rows | |
| logger.info(f" {table}: {len(rows)} registros") | |
| conn.close() | |
| return data | |
| async def migrate_to_postgres(data: dict): | |
| """Inserta los datos en PostgreSQL.""" | |
| from sqlalchemy import text | |
| from app.database import AsyncSessionLocal | |
| async with AsyncSessionLocal() as db: | |
| for table_name, rows in data.items(): | |
| if not rows: | |
| continue | |
| logger.info(f"Migrando tabla {table_name} ({len(rows)} registros)...") | |
| for row in rows: | |
| # Limpiar columnas que no existen en el modelo | |
| columns = list(row.keys()) | |
| values = list(row.values()) | |
| # Construir INSERT dinámico | |
| cols_str = ", ".join(columns) | |
| placeholders = ", ".join([f":{col}" for col in columns]) | |
| query = text(f"INSERT INTO {table_name} ({cols_str}) VALUES ({placeholders})") | |
| try: | |
| await db.execute(query, row) | |
| except Exception as e: | |
| logger.warning(f" Error insertando en {table_name}: {e}") | |
| # Continuar con el siguiente registro | |
| await db.commit() | |
| logger.info(f" {table_name} migrado correctamente") | |
| async def main(): | |
| logger.info("=== Migración SQLite → PostgreSQL ===") | |
| logger.info(f"SQLite DB: {DB_PATH}") | |
| # Leer datos de SQLite | |
| data = get_sqlite_data() | |
| if not data: | |
| logger.error("No se encontraron datos en SQLite") | |
| return | |
| # Migrar a PostgreSQL | |
| await migrate_to_postgres(data) | |
| logger.info("=== Migración completada ===") | |
| logger.info("Verificar con: python -c \"from app.database import init_db; import asyncio; asyncio.run(init_db())\"") | |
| if __name__ == "__main__": | |
| asyncio.run(main()) | |