Ir al contenido

Modelatge Relacional i Conceptes Clau

Anàlisi conceptual de l'ús de cursors i transaccions ACID en el motor sqlite3 de Python, amb un exemple pràctic de modelatge relacional de un-a-molts.

0lectures

Per interactuar de forma eficient i segura amb SQLite des d'llenguatges de programació com Python, és imprescindible assimilar dos principis arquitectònics fonamentals: els Cursors de base de dades i les Transaccions transaccionals.


A la biblioteca sqlite3 de Python, l'execució de sentències no es realitza de forma directa sobre la connexió de la base de dades (conn). En lloc d'això, cal instanciar una estructura de control intermèdia anomenada Cursor (cursor).

  • Definició Tècnica: Un cursor actua com un punter de memòria o agent de control que executa les sentències SQL sobre l'arxiu físic al disc i gestiona el conjunt de registres retornats.
  • Funcionamiento Operacional:
    1. Execució Segura: Rebre i processa la consulta: cursor.execute(sql).
    2. Extracció Controlada: Almacena temporalment les dades resultants de les lectures, permetent recuperar-les de forma granular utilitzant cursor.fetchone() (extreu una sola fila) o cursor.fetchall() (recupera la totalitat dels registres resultants).
# 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”.

Analitzem la clàssica transacció financera on transferim el saldo entre dos registres:

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 experimentés una pèrdua sobtada d'energia després de finalitzar l'operació 1 però abans de l'operació 2, el saldo es desvaniria. Les transaccions mitiguen aquest risc:

  • BEGIN TRANSACTION: Obre una zona de treball aïllada al disc.
  • COMMIT: Fa permanents totes les insercions o modificacions realitzades des de l'inici de la transacció.
  • ROLLBACK: Cancella les modificacions realitzades i torna la base de dades al seu estat original exacte previ a l'inici del bloc.

En Python, el mòdul sqlite3 inicialitza transaccions de forma implícita en executar sentències d'escriptura (INSERT, UPDATE, DELETE). Per confirmar els canvis físicament al fitxer .db, has de cridar explícitament conn.commit().


4.2. Cas Pràctic: Gestió de Projectes i Tasques

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

A continuació, implementarem un model de base de dades relacional de tipus Un-a-Molts (1:N) utilitzant restriccions de clau forà (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
}

Cream la taula “pare” (portel_proyectos) i posteriorment la taula “fill” (portel_tareas) vinculant-les mitjançant clau forà:

-- 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] Integritat Referencial: La definició ON DELETE RESTRICT indica al motor de base de dades que bloquegi l'esborrat de qualsevol projecte a portel_proyectos si encara existeixen tasques òrfanes vinculades a portel_tareas.


Provisionem dos projectes i assignem tasques vinculant els seus respectius IDs relacionals:

-- 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);

Pas 3: Consultes Relacionals Avançades (DQL - JOIN)

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

Per recuperar les tasques consolidades juntament amb el nom del projecte al qual pertanyen, implementem un acoblament mitjançant 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

Per marcar la fita de disseny com a completada de manera atòmica:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Llista de control de modelat relacional segur

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

Garantit la consistència lògica de les vostres bases de dades als laboratoris de Portelcore seguint aquesta llista de control:

  • ✓ Activació de claus foranes: Assegurar que el teu script de Python invoqui la directiva de verificació en obrir la connexió: conn.execute("PRAGMA foreign_keys = ON;") (desactivada per defecte en SQLite).
  • ✓ Mapeig de claus foranes: Verificar que totes les relacions lògiques entre taules de dades contenen restriccions declaratives de tipus FOREIGN KEY.
  • ✓ Confirmació transaccional (Commit): Validar que totes les operacions d'escriptura que alteren l'estat físic de l'arxiu .db es confirmen explícitament mitjançant conn.commit().
  • ✓ Gestió en cascada: Planificar correctament les directives ON DELETE (RESTRICT o CASCADE) en crear esquemes per evitar registres òrfans o borrados accidentals de dades en cascada.