Ir al contenido

Modelado Relacional y Conceptos Clave

Análisis conceptual del uso de cursores y transacciones ACID en el motor sqlite3 de Python, con ejemplo práctico de modelado relacional uno-a-muchos.

0lecturas

Para interactuar de forma eficiente y segura con SQLite desde lenguajes de programación como Python, es imprescindible asimilar dos principios arquitectónicos fundamentales: los Cursores de base de datos y las Transacciones transaccionales.


4.1. Conceptos Fundamentales: Cursores y Transacciones

Sección titulada «4.1. Conceptos Fundamentales: Cursores y Transacciones»

En la biblioteca sqlite3 de Python, la ejecución de sentencias no se realiza de forma directa sobre la conexión de la base de datos (conn). En su lugar, se requiere instanciar una estructura de control intermedia denominada Cursor (cursor).

  • Definición Técnica: Un cursor actúa como un puntero de memoria o agente de control que ejecuta las sentencias SQL sobre el archivo físico en disco y gestiona el conjunto de registros devueltos.
  • Funcionamiento Operacional:
    1. Ejecución Segura: Recibe y procesa la consulta: cursor.execute(sql).
    2. Extracción Controlada: Almacena temporalmente los datos resultantes de las lecturas, permitiendo recuperarlos de forma granular utilizando cursor.fetchone() (extrae una sola fila) o cursor.fetchall() (recupera la totalidad de los registros resultantes).
# Conexión al motor de base de datos local
conn = obtener_conexion(db_name)
# Instanciación del puntero o cursor
cursor = conn.cursor()
# Ejecución de la sentencia relacional
cursor.execute("SELECT nombre, email FROM portel_usuarios;")
# Recuperación de resultados en memoria del proceso
resultados = cursor.fetchall()

Una transacción es una secuencia lógica de una o más instrucciones SQL que se ejecutan como una única unidad de trabajo indivisible. Este comportamiento responde al principio de Atomicidad del estándar ACID: “o se completan todas las operaciones con éxito, o se revierte la totalidad del lote al estado inicial”.

Analicemos la clásica transacción financiera donde transferimos saldo entre dos registros:

BEGIN TRANSACTION;
-- 1. Restar importe del emisor
UPDATE portel_cuentas SET saldo = saldo - 100 WHERE id = 'A';
-- 2. Sumar importe al receptor
UPDATE portel_cuentas SET saldo = saldo + 100 WHERE id = 'B';
COMMIT;

Si el servidor experimentara una pérdida repentina de energía tras finalizar la operación 1 pero antes de la operación 2, el saldo se desvanecería. Las transacciones mitigan este riesgo:

  • BEGIN TRANSACTION: Abre una zona de trabajo aislada en disco.
  • COMMIT: Hace permanentes todas las inserciones o modificaciones realizadas desde el inicio de la transacción.
  • ROLLBACK: Cancela las modificaciones realizadas y devuelve la base de datos a su estado original exacto previo al inicio del bloque.

En Python, el módulo sqlite3 inicializa transacciones de forma implícita al ejecutar sentencias de escritura (INSERT, UPDATE, DELETE). Para confirmar los cambios físicamente en el archivo .db, debes llamar explícitamente a conn.commit().


4.2. Caso Práctico: Gestión de Proyectos y Tareas

Sección titulada «4.2. Caso Práctico: Gestión de Proyectos y Tareas»

A continuación, implementaremos un modelo de base de datos relacional de tipo Uno-a-Muchos (1:N) utilizando restricciones de clave foránea (FOREIGN KEY).

erDiagram
PORTEL_PROYECTOS ||--o{ PORTEL_TAREAS : contiene
PORTEL_PROYECTOS {
int id PK
string nombre_proyecto
string fecha_inicio
}
PORTEL_TAREAS {
int id PK
string descripcion
int completada
int id_proyecto FK
}

Paso 1: Declaración de Esquemas Lógicos (DDL)

Sección titulada «Paso 1: Declaración de Esquemas Lógicos (DDL)»

Creamos la tabla “padre” (portel_proyectos) y posteriormente la tabla “hija” (portel_tareas) vinculándolas mediante clave foránea:

-- Estructura Principal
CREATE TABLE portel_proyectos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nombre_proyecto TEXT NOT NULL,
fecha_inicio TEXT DEFAULT (date('now'))
);
-- Estructura Relacionada
CREATE TABLE portel_tareas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
descripcion TEXT NOT NULL,
completada INTEGER DEFAULT 0, -- 0 representa falso, 1 verdadero
id_proyecto INTEGER,
FOREIGN KEY (id_proyecto) REFERENCES portel_proyectos (id) ON DELETE RESTRICT
);

[!TIP] Integridad Referencial: La definición ON DELETE RESTRICT le indica al motor de base de datos que bloquee el borrado de cualquier proyecto en portel_proyectos si aún existen tareas huérfanas vinculadas en portel_tareas.


Paso 2: Inserción de Registros de Prueba (DML)

Sección titulada «Paso 2: Inserción de Registros de Prueba (DML)»

Aprovisionamos dos proyectos y asignamos tareas vinculando sus respectivos IDs relacionales:

-- Inserción de proyectos
INSERT INTO portel_proyectos (nombre_proyecto) VALUES ('Hacer portal de documentación');
INSERT INTO portel_proyectos (nombre_proyecto) VALUES ('Migrar base de datos local');
-- Inserción de tareas vinculadas (Proyecto ID 1 y Proyecto ID 2)
INSERT INTO portel_tareas (descripcion, id_proyecto) VALUES ('Diseñar interfaz premium', 1);
INSERT INTO portel_tareas (descripcion, id_proyecto) VALUES ('Sanear datos de prueba', 1);
INSERT INTO portel_tareas (descripcion, id_proyecto) VALUES ('Exportar esquemas SQL', 2);

Paso 3: Consultas Relacionales Avanzadas (DQL - JOIN)

Sección titulada «Paso 3: Consultas Relacionales Avanzadas (DQL - JOIN)»

Para recuperar las tareas consolidadas junto al nombre del proyecto al que pertenecen, implementamos un acoplamiento mediante JOIN:

SELECT
portel_proyectos.nombre_proyecto,
portel_tareas.descripcion,
portel_tareas.completada
FROM portel_proyectos
JOIN portel_tareas ON portel_proyectos.id = portel_tareas.id_proyecto;
Hacer portal de documentación | Diseñar interfaz premium | 0
Hacer portal de documentación | Sanear datos de prueba | 0
Migrar base de datos local | Exportar esquemas SQL | 0

Para marcar el hito de diseño como completado de forma atómica:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Checklist de Modelado Relacional Seguro

Sección titulada «🏁 Checklist de Modelado Relacional Seguro»

Garantice la consistencia lógica de sus bases de datos en los laboratorios de Portelcore siguiendo este checklist de control:

  • ✓ Activación de Claves Foráneas: Asegurar que tu script de Python invoca la directiva de verificación al abrir la conexión: conn.execute("PRAGMA foreign_keys = ON;") (desactivada por defecto en SQLite).
  • ✓ Mapeo de Claves Foráneas: Verificar que todas las relaciones lógicas entre tablas de datos contienen restricciones declarativas de tipo FOREIGN KEY.
  • ✓ Confirmación Transaccional (Commit): Validar que todas las operaciones de escritura que alteran el estado físico del archivo .db se confirman explícitamente mediante conn.commit().
  • ✓ Gestión en Cascada: Planificar correctamente las directivas ON DELETE (RESTRICT o CASCADE) al crear esquemas para evitar registros huérfanos o borrados accidentales de datos en cascada.