Spaces:
Paused
Paused
File size: 3,186 Bytes
83bdb4a | 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 | """
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())
|