Tutorial para generar e insertar grandes volúmenes de datos de prueba en tu tabla de PostgreSQL utilizando `Faker`

En este tutorial, aprenderás cómo generar datos simulados y cargarlos en una base de datos PostgreSQL utilizando Python, psycopg2 y Faker. Nos basaremos en el último esquema de base de datos que incluye las tablas estacion, tren, pasajero, trayecto, viaje y reserva. Este proceso es ideal para poblar tu base de datos con datos realistas para pruebas y desarrollo.

Tabla de Contenidos

1. Introducción
2. Requisitos Previos
3. Paso 1: Generar Datos Simulados y Escribir en CSV
4. Paso 2: Cargar Datos en PostgreSQL Usando el Comando COPY
5. Consideraciones Importantes
6. Verificación de Datos
7. Conclusión

Introducción

Trabajar con grandes volúmenes de datos es esencial para probar la escalabilidad y el rendimiento de las aplicaciones. Generar manualmente estos datos puede ser tedioso y poco práctico. Usando Python y librerías como Faker, podemos automatizar la generación de datos realistas. Luego, podemos utilizar el comando COPY de PostgreSQL para cargar los datos de manera eficiente.

Requisitos Previos

• Python 3.x instalado en tu sistema.

• Conocimientos básicos de Python y SQL.

• PostgreSQL instalado y funcionando.

• Acceso a una base de datos PostgreSQL con los siguientes parámetros:

  • Nombre de la base de datos
  • Usuario
  • Contraseña
  • Host y puerto (por defecto, localhost y 5432)

• Instalación de las librerías necesarias:

  pip install psycopg2-binary faker
  

Paso 1: Generar Datos Simulados y Escribir en CSV

Primero, generaremos los datos simulados para cada una de las tablas y los escribiremos en archivos CSV. Esto nos permitirá cargar los datos de manera eficiente en PostgreSQL.

Script para Generar Datos y Escribir en CSV

import csv
from faker import Faker
import random
from datetime import datetime, timedelta

# Inicializar Faker
fake = Faker()

# Cantidad de registros a generar
NUM_ESTACIONES = 1000
NUM_TRENES = 500
NUM_PASAJEROS = 2000
NUM_TRAYECTOS = 1500
NUM_VIAJES = 5000
NUM_RESERVAS = 10000

# Inicializar listas para almacenar datos
estacion_data = []
tren_data = []
pasajero_data = []
trayecto_data = []
viaje_data = []
reserva_data = []

# Generar datos para la tabla 'estacion'
print("Generando datos para la tabla 'estacion'...")
for id in range(1, NUM_ESTACIONES + 1):
    nombre = fake.city()
    direccion = fake.address()
    estacion_data.append([id, nombre, direccion])

# Generar datos para la tabla 'tren'
print("Generando datos para la tabla 'tren'...")
for id in range(1, NUM_TRENES + 1):
    modelo = fake.word()
    capacidad = random.randint(50, 200)
    tren_data.append([id, modelo, capacidad])

# Generar datos para la tabla 'pasajero'
print("Generando datos para la tabla 'pasajero'...")
for id in range(1, NUM_PASAJEROS + 1):
    nombre = fake.name()
    direccion = fake.address()
    fecha_registro = fake.date_time_between(start_date='-5y', end_date='now')
    pasajero_data.append([id, nombre, direccion, fecha_registro])

# Generar datos para la tabla 'trayecto'
print("Generando datos para la tabla 'trayecto'...")
for id in range(1, NUM_TRAYECTOS + 1):
    id_estacion_origen = random.randint(1, NUM_ESTACIONES)
    id_estacion_destino = random.randint(1, NUM_ESTACIONES)
    while id_estacion_destino == id_estacion_origen:
        id_estacion_destino = random.randint(1, NUM_ESTACIONES)
    nombre = f"Ruta {id_estacion_origen}-{id_estacion_destino}"
    trayecto_data.append([id, nombre, id_estacion_origen, id_estacion_destino])

# Generar datos para la tabla 'viaje'
print("Generando datos para la tabla 'viaje'...")
for id in range(1, NUM_VIAJES + 1):
    id_trayecto = random.randint(1, NUM_TRAYECTOS)
    id_tren = random.randint(1, NUM_TRENES)
    fecha_salida = fake.date_time_between(start_date='-1y', end_date='now')
    duracion = timedelta(hours=random.randint(1, 12))
    fecha_llegada = fecha_salida + duracion
    viaje_data.append([id, id_trayecto, id_tren, fecha_salida, fecha_llegada])

# Generar datos para la tabla 'reserva'
print("Generando datos para la tabla 'reserva'...")
reserva_set = set()
for id in range(1, NUM_RESERVAS + 1):
    id_pasajero = random.randint(1, NUM_PASAJEROS)
    id_viaje = random.randint(1, NUM_VIAJES)
    # Asegurar que no haya reservas duplicadas
    while (id_pasajero, id_viaje) in reserva_set:
        id_pasajero = random.randint(1, NUM_PASAJEROS)
        id_viaje = random.randint(1, NUM_VIAJES)
    reserva_set.add((id_pasajero, id_viaje))
    fecha_reserva = fake.date_time_between(start_date='-1y', end_date='now')
    reserva_data.append([id, id_pasajero, id_viaje, fecha_reserva])

# Función para escribir datos en archivos CSV
def write_csv(filename, data, headers):
    with open(filename, 'w', newline='', encoding='utf-8') as csvfile:
        csvwriter = csv.writer(csvfile)
        csvwriter.writerow(headers)
        csvwriter.writerows(data)

# Escribir datos en archivos CSV
print("Escribiendo datos en archivos CSV...")
write_csv('estacion.csv', estacion_data, ['id', 'nombre', 'direccion'])
write_csv('tren.csv', tren_data, ['id', 'modelo', 'capacidad'])
write_csv('pasajero.csv', pasajero_data, ['id', 'nombre', 'direccion', 'fecha_registro'])
write_csv('trayecto.csv', trayecto_data, ['id', 'nombre', 'id_estacion_origen', 'id_estacion_destino'])
write_csv('viaje.csv', viaje_data, ['id', 'id_trayecto', 'id_tren', 'fecha_salida', 'fecha_llegada'])
write_csv('reserva.csv', reserva_data, ['id', 'id_pasajero', 'id_viaje', 'fecha_reserva'])

print("Generación de datos y escritura en CSV completada.")

Explicación del Código

• Generación de Datos:

  • estacion: Genera estaciones con IDs únicos, nombres y direcciones.
  • tren: Genera trenes con IDs únicos, modelos y capacidades.
  • pasajero: Genera pasajeros con IDs únicos, nombres, direcciones y fechas de registro.
  • trayecto: Genera trayectos entre dos estaciones diferentes, asegurando que origen y destino no sean iguales.
  • viaje: Genera viajes con IDs únicos, asociados a trayectos y trenes existentes, con fechas de salida y llegada.
  • reserva: Genera reservas, asegurando que no haya duplicados mediante un conjunto que rastrea combinaciones existentes de id_pasajero e id_viaje.

• Escritura en Archivos CSV:

  • Los datos de cada tabla se escriben en archivos CSV correspondientes con los encabezados adecuados.

Paso 2: Cargar Datos en PostgreSQL Usando el Comando COPY

Ahora cargaremos los datos desde los archivos CSV a la base de datos PostgreSQL utilizando el comando COPY, que es eficiente para cargas masivas.

Script para Cargar Datos en PostgreSQL

import psycopg2

# Parámetros de conexión a la base de datos
DB_NAME = 'tu_nombre_de_base_de_datos'
DB_USER = 'tu_usuario'
DB_PASSWORD = 'tu_contraseña'
DB_HOST = 'localhost'
DB_PORT = '5432'

# Conectar a la base de datos PostgreSQL
conn = psycopg2.connect(
    dbname=DB_NAME, user=DB_USER, password=DB_PASSWORD, host=DB_HOST, port=DB_PORT
)
cur = conn.cursor()

# Deshabilitar restricciones y triggers para carga rápida
print("Deshabilitando restricciones y triggers...")
tables = ['reserva', 'viaje', 'trayecto']
for table in tables:
    cur.execute(f"ALTER TABLE public.{table} DISABLE TRIGGER ALL;")

# Función para cargar datos usando COPY
def load_csv(table_name, file_name, columns):
    print(f"Cargando datos en la tabla '{table_name}'...")
    with open(file_name, 'r', encoding='utf-8') as f:
        next(f)  # Saltar la fila de encabezado
        cur.copy_expert(
            f"COPY public.{table_name} ({columns}) FROM STDIN WITH CSV", f
        )

# Cargar datos en las tablas
load_csv('estacion', 'estacion.csv', 'id, nombre, direccion')
load_csv('tren', 'tren.csv', 'id, modelo, capacidad')
load_csv('pasajero', 'pasajero.csv', 'id, nombre, direccion, fecha_registro')
load_csv('trayecto', 'trayecto.csv', 'id, nombre, id_estacion_origen, id_estacion_destino')
load_csv('viaje', 'viaje.csv', 'id, id_trayecto, id_tren, fecha_salida, fecha_llegada')
load_csv('reserva', 'reserva.csv', 'id, id_pasajero, id_viaje, fecha_reserva')

# Rehabilitar restricciones y triggers
print("Rehabilitando restricciones y triggers...")
for table in reversed(tables):
    cur.execute(f"ALTER TABLE public.{table} ENABLE TRIGGER ALL;")

# Actualizar secuencias para que coincidan con los IDs máximos
print("Actualizando secuencias...")
sequence_tables = ['estacion', 'tren', 'pasajero', 'trayecto', 'viaje', 'reserva']
for table in sequence_tables:
    cur.execute(f"SELECT setval(pg_get_serial_sequence('public.{table}', 'id'), MAX(id)) FROM public.{table};")

# Confirmar transacciones y cerrar conexión
conn.commit()
cur.close()
conn.close()

print("Carga de datos completada.")

Explicación del Código

• Deshabilitar Restricciones y Triggers:

  • Deshabilita temporalmente los triggers en las tablas con claves foráneas (reserva, viaje, trayecto) para acelerar la carga de datos.
  • Esto permite inserciones masivas sin comprobaciones de restricciones durante la carga.

• Cargar Datos Usando COPY:

  • Utiliza copy_expert() para ejecutar el comando COPY para cada tabla.
  • Carga datos desde los archivos CSV a las tablas correspondientes.

• Rehabilitar Restricciones y Triggers:

  • Rehabilita los triggers después de completar la carga de datos.
  • El orden inverso garantiza que las dependencias se manejen correctamente.

• Actualizar Secuencias:

  • Actualiza las secuencias de las columnas id para que coincidan con los valores máximos en las tablas.
  • Asegura que futuras inserciones continúen desde el ID correcto.

Consideraciones Importantes

1. Relaciones de Claves Foráneas:

   • Asegúrate de que la generación de datos respeta las relaciones de claves foráneas.
   • Por ejemplo, id_estacion_origen y id_estacion_destino en trayecto deben referenciar IDs de estaciones existentes.

2. Integridad de Datos:

   • Dado que las restricciones están deshabilitadas durante la carga, es crucial que los datos sean consistentes.
   • Después de rehabilitar las restricciones, PostgreSQL verificará la integridad de los datos.

3. Formatos de Fecha y Hora:

   • Faker genera objetos datetime que se formatean correctamente al escribir en CSV.
   • Asegúrate de que los formatos de fecha sean compatibles con las expectativas de PostgreSQL.

4. Restricciones Únicas:

   • En la tabla reserva, se asegura que (id_pasajero, id_viaje) sea único evitando duplicados durante la generación de datos.

5. Ajuste de Cantidad de Registros:

   • Los números proporcionados (por ejemplo, NUM_ESTACIONES = 1000) pueden ajustarse según tus necesidades y capacidad del sistema.

6. Permisos:

   • Asegúrate de que el usuario de PostgreSQL tenga los permisos necesarios para ejecutar el comando COPY y alterar restricciones de tablas.

Verificación de Datos

Después de cargar los datos, es importante verificar que todo se haya importado correctamente.

Consultas SQL para Verificación

-- Contar registros en cada tabla
SELECT COUNT(*) FROM public.estacion;
SELECT COUNT(*) FROM public.tren;
SELECT COUNT(*) FROM public.pasajero;
SELECT COUNT(*) FROM public.trayecto;
SELECT COUNT(*) FROM public.viaje;
SELECT COUNT(*) FROM public.reserva;

-- Ver algunos registros de muestra
SELECT * FROM public.estacion LIMIT 5;
SELECT * FROM public.tren LIMIT 5;
SELECT * FROM public.pasajero LIMIT 5;
SELECT * FROM public.trayecto LIMIT 5;
SELECT * FROM public.viaje LIMIT 5;
SELECT * FROM public.reserva LIMIT 5;

Verificar Integridad Referencial

-- Verificar claves foráneas en 'trayecto'
SELECT t.*
FROM public.trayecto t
LEFT JOIN public.estacion e1 ON t.id_estacion_origen = e1.id
LEFT JOIN public.estacion e2 ON t.id_estacion_destino = e2.id
WHERE e1.id IS NULL OR e2.id IS NULL;

-- Debería devolver cero filas si todas las claves foráneas son válidas

Conclusión

En este tutorial, aprendiste cómo generar datos simulados y cargarlos en una base de datos PostgreSQL utilizando Python, psycopg2 y Faker. Este método es eficiente para poblar tu base de datos con datos realistas, manteniendo la integridad de los datos y respetando las relaciones de claves foráneas.

Pasos Siguientes

• Manejo de Errores:

  • En entornos de producción, considera agregar manejo de errores utilizando bloques try-except.
  • Asegúrate de que las conexiones se cierren adecuadamente incluso si ocurre un error.

• Optimización de Rendimiento:

  • El comando COPY es eficiente para cargas masivas de datos.
  • Ajusta los tamaños de lote y considera estrategias de indexación si trabajas con conjuntos de datos más grandes.

• Seguridad:

  • Protege tus credenciales de base de datos.
  • Utiliza variables de entorno o archivos de configuración para almacenar información sensible de forma segura.

• Limpieza de Datos:

  • Después de cargar los datos, puedes eliminar o archivar los archivos CSV si ya no son necesarios.

¡Felicidades! Has completado el tutorial sobre cómo generar e insertar datos simulados en PostgreSQL utilizando CSVs y Python. Este proceso te permitirá probar y desarrollar tus aplicaciones con una base de datos poblada de manera realista.

Si tienes alguna pregunta o necesitas asistencia adicional, ¡no dudes en preguntar!