Ir al contenido

Modélisation relationnelle et concepts clés

Analyse conceptuelle de l'utilisation des curseurs et des transactions ACID dans le moteur sqlite3 de Python, avec un exemple pratique de modélisation relationnelle un-à-plusieurs.

0vues

Pour interagir de manière efficace et sécurisée avec SQLite à partir de langages de programmation comme Python, il est indispensable d'assimiler deux principes architecturaux fondamentaux : les Curseurs de base de données et les Transactions transactionnelles.


4.1. Concepts fondamentaux : Curseurs et transactions

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

Dans la bibliothèque sqlite3 de Python, l'exécution des instructions ne se fait pas directement sur la connexion à la base de données (conn). À la place, il est nécessaire d'instancier une structure de contrôle intermédiaire appelée Cursor (cursor).

  • Définition technique : Un curseur agit comme un pointeur de mémoire ou un agent de contrôle qui exécute les instructions SQL sur le fichier physique sur le disque et gère l'ensemble des enregistrements renvoyés.
  • Fonctionnement Opérationnel :
    1. Exécution sécurisée : Reçoit et traite la requête : cursor.execute(sql).
    2. Extraction contrôlée : Stocke temporairement les données résultant des lectures, permettant de les récupérer de manière granulaire en utilisant cursor.fetchone() (extrait une seule ligne) ou cursor.fetchall() (récupère la totalité des enregistrements résultants).
# 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()

Une transaction est une séquence logique d'une ou plusieurs instructions SQL exécutées comme une seule unité de travail indivisible. Ce comportement répond au principe d'atomicité de la norme ACID : « soit toutes les opérations sont menées à bien, soit la totalité du lot est annulée pour revenir à l'état initial ».

Analysons la transaction financière classique où nous transférons un solde entre deux enregistrements :

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 le serveur subissait une perte de puissance soudaine après avoir terminé l'opération 1 mais avant l'opération 2, le solde disparaîtrait. Les transactions atténuent ce risque :

  • BEGIN TRANSACTION : Ouvre une zone de travail isolée sur le disque.
  • COMMIT : Rend permanentes toutes les insertions ou modifications effectuées depuis le début de la transaction.
  • ROLLBACK : Annule les modifications effectuées et rétablit la base de données dans son état d'origine exact avant le début du bloc.

En Python, le module sqlite3 initialise les transactions de manière implicite lors de l'exécution d'instructions d'écriture (INSERT, UPDATE, DELETE). Pour confirmer physiquement les changements dans le fichier .db, vous devez appeler explicitement conn.commit().


4.2. Cas pratique : Gestion de projets et de tâches

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

Ensuite, nous implémenterons un modèle de base de données relationnelle de type Un-à-Plusieurs (1:N) en utilisant des contraintes de clé étrangère (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
}

Étape 1 : Déclaration de schémas logiques (DDL)

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

Nous créons la table « parente » (portel_proyectos) puis la table « enfant » (portel_tareas) en les liant par une clé étrangère :

-- 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] Intégrité Référentielle : La définition ON DELETE RESTRICT indique au moteur de base de données de bloquer la suppression de tout projet dans portel_proyectos si des tâches orphelines liées existent encore dans portel_tareas.


Étape 2 : Insertion d'enregistrements de test (DML)

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

Nous provisionnons deux projets et attribuons des tâches en liant leurs identifiants relationnels respectifs :

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

Étape 3 : Requêtes relationnelles avancées (DQL - JOIN)

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

Pour récupérer les tâches consolidées avec le nom du projet auquel elles appartiennent, nous avons implémenté un couplage via 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

Étape 4 : Mise à jour contrôlée (DML)

Sección titulada «Paso 4: Actualización Controlada (DML)»

Pour marquer le jalon de conception comme terminé de manière atomique :

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Liste de contrôle de modélisation relationnelle sécurisée

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

Garantissez la cohérence logique de vos bases de données dans les laboratoires de Portelcore en suivant cette liste de contrôle :

  • ✓ Activation des clés étrangères : Assurez-vous que votre script Python invoque la directive de vérification lors de l'ouverture de la connexion : conn.execute("PRAGMA foreign_keys = ON;") (désactivée par défaut dans SQLite).
  • ✓ Mappage des clés étrangères : Vérifier que toutes les relations logiques entre les tables de données contiennent des contraintes déclaratives de type FOREIGN KEY.
  • ✓ Confirmation Transactionnelle (Commit) : Valider que toutes les opérations d'écriture qui modifient l'état physique du fichier .db sont explicitement confirmées par conn.commit().
  • ✓ Gestion en cascade : Planifier correctement les directives ON DELETE (RESTRICT ou CASCADE) lors de la création de schémas afin d'éviter les enregistrements orphelins ou les suppressions accidentelles de données en cascade.