Ir al contenido

Relacionálne modelovanie a kľúčové koncepty

Konceptuálna analýza použitia kurzorov a transakcií ACID v motore sqlite3 Pythonu, s praktickým príkladom relačného modelovania jeden-k- mnohým.

0lecturas

Pre efektívnu a bezpečnú interakciu s SQLite z programovacích jazykov ako Python je nevyhnutné asimilovať dva základné architektonické princípy: kurzory databáze a transakcie.


V knižnici sqlite3 Pythonu sa vykonávanie príkazov nevykonáva priamo cez pripojenie k databáze (conn). Namiesto toho je potrebné instantiovať strednú kontrolnú štruktúru nazývanú kurzor (cursor).

  • Technická definícia: Kurzor funguje ako ukazovateľ pamäte alebo riadiaci agent, ktorý vykonáva príkazy SQL na fyzickom súbore na disku a spravuje množinu vrátených záznamov.
  • Operačný chod:
    1. Bezpečné vykonávanie: Prijíma a spracúva dotaz: cursor.execute(sql).
    2. Kontrolovaná extrakcia: Dočasne ukladá výsledné dáta z čítaní, umožňuje ich získať granularitou pomocou cursor.fetchone() (extrahuje jeden riadok) alebo cursor.fetchall() (získava všetky výsledné záznamy).
# 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()

Transakcia je logická sekvencia jednej alebo viacerých príkazov SQL, ktoré sa vykonávajú ako jediná nedeliteľná pracovná jednotka. Toto správanie zodpovedá princípu atómovosti ACID štandardu: „buď všetky operácie sú úspešne dokončené, alebo sa celý dávkový súbor vráti do pôvodného stavu“.

Analyzujme klasickú finančnú transakciu, kde prevádzame zostatok medzi dvoma záznamami:

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;

Ak by server náhle stratil energiu po dokončení operácie 1, ale pred operáciou 2, zostatok by zmizol. Transakcie zmierňujú toto riziko:

  • BEGIN TRANSACTION: Otvorí izolovanú pracovnú oblasť na disku.
  • COMMIT: Urobí všetky vloženia alebo úpravy trvalými od začiatku transakcie.
  • ROLLBACK: Zruší vykonané úpravy a vráti databázu do pôvodného stavu pred začiatkom bloku.

V Pythonu modul sqlite3 implicitne inicializuje transakcie pri vykonávaní zápisov príkazov (INSERT, UPDATE, DELETE). Ak chcete zmeny fyzicky potvrdiť v súbore .db, musíte explicitne zavolať conn.commit().


4.2. Praktický prípad: Správa projektov a úloh

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

Ďalej implementujeme relačný model databáze typu jeden-k-mnohým (1:N) pomocou obmedzení cudzích kľúčov (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
}

Vytvárame tabuľku 'otec' (portel_proyectos) a následne dcérsku tabuľku (portel_tareas) prepojujeme pomocou cudzieho kľúča:

-- 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] Referenčná integrita: Definícia ON DELETE RESTRICT informuje motor databáze, aby zablokoval odstránenie akéhokoľvek projektu v portel_proyectos, ak stále existujú viazané osiáľované úlohy v portel_tareas.


Krok 2: Vloženie testovacích záznamov (DML)

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

Poskytujeme dva projekty a priradzujeme úlohy prepojením ich relačných ID:

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

Na získanie konsolidovaných úloh spolu s názvom projektu, ku ktorému patria, implementujeme prepojenie pomocou 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;

Očakávaný formátovaný výstup:

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

Krok 4: Kontrolovaná aktualizácia (DML)

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

Na označenie dizajnového míľnika ako dokončeného v atómovom režime:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

✅ Kontrolný zoznam bezpečného relačného modelovania

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

Zabezpečte logickú konzistenciu vašich databáz v laboratóriách Portelcore podľa tohto kontrolného zoznamu:

  • ✓ Aktivácia cudzích kľúčov: Zabezpečte, že váš skript Pythonu volá overovaciu direktívu pri otvorení spojenia: conn.execute("PRAGMA foreign_keys = ON;") (štandardne vypnuté v SQLite).
  • ✓ Mapovanie cudzích kľúčov: Overte, že všetky logické vzťahy medzi tabuľkami údajov obsahujú deklaratívne obmedzenia typu FOREIGN KEY.
  • ✓ Transakčné potvrdenie (Commit): Overte, že všetky operácie zápisu, ktoré menia fyzický stav súboru .db, sú explicitne potvrdené pomocou conn.commit().
  • ✓ Kaskádová správa: Správne plánujte direktívy ON DELETE (RESTRICT alebo CASCADE) pri vytváraní schém, aby ste predišli osi rodeným záznamom alebo náhodnému odstráneniu údajov v kaskáde.