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.
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.
4.1. Conceptes Fonamentals: Cursors i Transaccions
Sección titulada «4.1. Conceptos Fundamentales: Cursores y Transacciones»Què és un Cursor?
Sección titulada «¿Qué es un Cursor?»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:
- Execució Segura: Rebre i processa la consulta:
cursor.execute(sql). - Extracció Controlada: Almacena temporalment les dades resultants de les lectures, permetent recuperar-les de forma granular utilitzant
cursor.fetchone()(extreu una sola fila) ocursor.fetchall()(recupera la totalitat dels registres resultants).
- Execució Segura: Rebre i processa la consulta:
# Conexión al motor de base de datos localconn = obtener_conexion(db_name)
# Instanciación del puntero o cursorcursor = conn.cursor()
# Ejecución de la sentencia relacionalcursor.execute("SELECT nombre, email FROM portel_usuarios;")
# Recuperación de resultados en memoria del procesoresultados = cursor.fetchall()Què és una Transacció?
Sección titulada «¿Qué es una Transacción?»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 emisorUPDATE portel_cuentas SET saldo = saldo - 100 WHERE id = 'A';-- 2. Sumar importe al receptorUPDATE 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 }Pas 1: Declaració d'Esquemes Lògics (DDL)
Sección titulada «Paso 1: Declaración de Esquemas Lógicos (DDL)»Cream la taula “pare” (portel_proyectos) i posteriorment la taula “fill” (portel_tareas) vinculant-les mitjançant clau forà:
-- Estructura PrincipalCREATE TABLE portel_proyectos ( id INTEGER PRIMARY KEY AUTOINCREMENT, nombre_proyecto TEXT NOT NULL, fecha_inicio TEXT DEFAULT (date('now')));
-- Estructura RelacionadaCREATE 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 RESTRICTindica al motor de base de dades que bloquegi l'esborrat de qualsevol projecte aportel_proyectossi encara existeixen tasques òrfanes vinculades aportel_tareas.
Pas 2: Inserció de Registres de Prova (DML)
Sección titulada «Paso 2: Inserción de Registros de Prueba (DML)»Provisionem dos projectes i assignem tasques vinculant els seus respectius IDs relacionals:
-- Inserción de proyectosINSERT 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.completadaFROM portel_proyectosJOIN portel_tareas ON portel_proyectos.id = portel_tareas.id_proyecto;Sortida formatada esperada:
Sección titulada «Salida Formateada Esperada:»Hacer portal de documentación | Diseñar interfaz premium | 0Hacer portal de documentación | Sanear datos de prueba | 0Migrar base de datos local | Exportar esquemas SQL | 0Pas 4: Actualització controlada (DML)
Sección titulada «Paso 4: Actualización Controlada (DML)»Per marcar la fita de disseny com a completada de manera atòmica:
UPDATE portel_tareasSET completada = 1WHERE 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.dbes confirmen explícitament mitjançantconn.commit(). - ✓ Gestió en cascada: Planificar correctament les directives
ON DELETE(RESTRICToCASCADE) en crear esquemes per evitar registres òrfans o borrados accidentals de dades en cascada.

