Ir al contenido

Relationeel modelleren en kernconcepten

Conceptuele analyse van het gebruik van cursors en ACID-transacties in de Python sqlite3-engine, met een praktisch voorbeeld van een-op-veel relationeel modelleren.

0lecturas

Om efficiënt en veilig met SQLite te interageren vanuit programmeertalen zoals Python, is het essentieel om twee fundamentele architecturale principes te begrijpen: database-cursors en transacties.


4.1. Fundamentele Concepten: Cursors en Transacties

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

In de Python-bibliotheek sqlite3 vindt de uitvoering van statements niet rechtstreeks plaats op de databaseverbinding (conn). In plaats daarvan moet een tussenliggende besturingsstructuur genaamd een Cursor (cursor) worden geïnstancieerd.

  • Technische definitie: Een cursor fungeert als een geheugenwijzer of controle-agent die SQL-statements uitvoert op het fysieke bestand op de schijf en het resultaatset van de geretourneerde records beheert.
  • Operationele Functionaliteit:
    1. Veilige uitvoering: Ontvangt en verwerkt de query: cursor.execute(sql).
    2. Gecontroleerde extractie: Slaat de resulterende leesgegevens tijdelijk op, waardoor ze op een granulaire manier kunnen worden opgehaald met behulp van cursor.fetchone() (haalt één enkele rij op) of cursor.fetchall() (haalt alle resulterende records op).
# 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()

Een transactie is een logische reeks van één of meer SQL-instructies die worden uitgevoerd als één enkele ondeelbare eenheid van werk. Dit gedrag komt overeen met het principe van atomiciteit uit de ACID-standaard: “of alle operaties worden succesvol voltooid, of de gehele batch wordt teruggedraaid naar de beginstatus”.

Laten we de klassieke financiële transactie analyseren waarbij we saldo overboeken tussen twee records:

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;

Als de server een plotseling stroomverlies zou ervaren na het voltooien van bewerking 1 maar vóór bewerking 2, zou het saldo verdwijnen. Transacties beperken dit risico:

  • BEGIN TRANSACTION: Opent een geïsoleerde werkzone op de schijf.
  • COMMIT: Maakt alle invoegingen of wijzigingen die sinds het begin van de transactie zijn uitgevoerd permanent.
  • ROLLBACK: Annuleert de uitgevoerde wijzigingen en herstelt de database naar de exacte oorspronkelijke staat van vóór het begin van het blok.

In Python initialiseert de module sqlite3 transacties impliciet bij het uitvoeren van schrijfbewerkingen (INSERT, UPDATE, DELETE). Om de wijzigingen fysiek in het bestand .db te bevestigen, moet je expliciet conn.commit() aanroepen.


Vervolgens zullen we een relationeel databasemodel van het type één-op-veel (1:N) implementeren met behulp van foreign key-restricties (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
}

Stap 1: Declaratie van Logische Schema's (DDL)

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

We maken de bovenliggende tabel (portel_proyectos) en vervolgens de onderliggende tabel (portel_tareas), die we via een foreign key aan elkaar koppelen:

-- 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] Referentiële integriteit: De definitie ON DELETE RESTRICT vertelt de database-engine om het verwijderen van elk project in portel_proyectos te blokkeren als er nog steeds gekoppelde, wees kinderprocessen bestaan in portel_tareas.


We voorzien twee projecten en wijzen taken toe door hun respectievelijke relationele ID's te koppelen:

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

Stap 3: Geavanceerde Relationele Query's (DQL - JOIN)

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

Om de geconsolideerde taken op te halen, samen met de naam van het project waartoe ze behoren, implementeren we een koppeling 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;

Verwachte Geformatteerde Uitvoer:

Sección titulada «Salida Formateada Esperada:»
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

Om de ontwerpmijlpaal atomair als voltooid te markeren:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Checklist voor Veilig Relationeel Modelleren

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

Garandeer de logische consistentie van uw databases in de Portelcore-laboratoria door deze controlelijst te volgen:

  • ✓ Activering van Foreign Keys: Zorg ervoor dat je Python-script de verificatierichtlijn aanroept bij het openen van de verbinding: conn.execute("PRAGMA foreign_keys = ON;") (standaard uitgeschakeld in SQLite).
  • ✓ Mapping van Foreign Keys: Controleer of alle logische relaties tussen datatabellen declaratieve beperkingen van het type FOREIGN KEY bevatten.
  • ✓ Transactionele Bevestiging (Commit): Valideer dat alle schrijfacties die de fysieke status van het bestand .db wijzigen, expliciet worden bevestigd via conn.commit().
  • ✓ Cascadebeheer: Plan de richtlijnen ON DELETE (RESTRICT of CASCADE) correct bij het maken van schema's om losgekoppelde records of onbedoelde cascade-verwijderingen van gegevens te voorkomen.