Ir al contenido

Relační modelování a klíčové koncepty

Konceptuální analýza použití kurzorů a ACID transakcí v Pythonovém enginu sqlite3, s praktickým příkladem relačního modelování jeden-mnoho.

0čtení

Pro efektivní a bezpečný interakci se SQLite z programovacích jazyků, jako je Python, je nezbytné osvojit si dva základní architektonické principy: databázové kurzory a transakce.


V knihovně Python sqlite3 se provádění příkazů neprovádí přímo přes připojení k databázi (conn). Místo toho je nutné vytvořit prostřední řídicí strukturu nazvanou Cursor (cursor).

  • Technická definice: Kurzor funguje jako ukazatel paměti nebo řídicí agent, který provádí SQL příkazy nad fyzickým souborem na disku a spravuje sadu vrácených záznamů.
  • Operační fungování:
    1. Bezpečné provádění: Přijímá a zpracovává dotaz: cursor.execute(sql).
    2. Řízená extrakce: Dočasně ukládá výsledná data z čtení, což umožňuje jejich granulární získávání pomocí cursor.fetchone() (extrahuje pouze jeden řádek) nebo cursor.fetchall() (získává všechny 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()

Transakce je logická sekvence jednoho nebo více SQL příkazů, které se provádějí jako jednotná nedělitelná pracovní jednotka. Tento proces odpovídá principu Atomicity standardu ACID: „buď jsou všechny operace úspěšně dokončeny, nebo je celý blok vrácen do původního stavu“.

Pojďme analyzovat klasickou finanční transakci, kde převádíme zůstatek mezi dvěma záznamy:

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;

Pokud by server zaznamenal náhlý výpadek napájení po dokončení operace 1, ale před operací 2, zůstatek by zmizel. Transakce toto riziko zmírňují:

  • BEGIN TRANSACTION: Otevře izolovanou pracovní zónu na disku.
  • COMMIT: Trvale uloží všechny vložení nebo změny provedené od začátku transakce.
  • ROLLBACK: Zruší provedené změny a vrátí databázi do přesného původního stavu před začátkem bloku.

V Pythonu modul sqlite3 implicitně inicializuje transakce při provádění zápisových příkazů (INSERT, UPDATE, DELETE). Pro fyzické potvrzení změn v souboru .db musíte výslovně zavolat conn.commit().


4.2. Praktický příklad: Správa projektů a úkolů

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

Následně implementujeme relační databázový model typu jeden-k-mnohým (1:N) pomocí omezení cizích klíčů (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
}

Vytvoříme „rodičovskou“ tabulku (portel_proyectos) a následně „dítěcí“ tabulku (portel_tareas), které propojíme pomocí cizího klíče:

-- 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]\r Integridad Referencial: La definición ON DELETE RESTRICT le indica al motor de base de datos que bloquee el borrado de cualquier proyecto en portel_proyectos si aún existen tareas huérfanas vinculadas en portel_tareas.


Krok 2: Vložení testovacích záznamů (DML)

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

Zajistíme dva projekty a přiřadíme úkoly propojením jejich příslušných 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);

Pro získání konsolidovaných úkolů spolu s názvem projektu, ke kterému patří, implementujeme propojení pomocí 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čeká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

Pro atomické označení milníku návrhu jako dokončeného:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Kontrolní seznam bezpečného relačního modelování

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

Zajistěte logickou konzistenci svých databází v laboratořích Portelcore pomocí tohoto kontrolního seznamu:

  • ✓ Aktivace cizích klíčů: Ujistěte se, že váš Python skript při otevření spojení vyvolá příkaz pro ověřování: conn.execute("PRAGMA foreign_keys = ON;") (ve SQLite je ve výchozím nastavení deaktivováno).
  • ✓ Mapování cizích klíčů: Ověřte, že všechny logické vztahy mezi datovými tabulkami obsahují deklarativní omezení typu FOREIGN KEY.
  • ✓ Transakční potvrzení (Commit): Ověřte, že všechny zápisové operace, které mění fyzický stav souboru .db, jsou výslovně potvrzeny pomocí conn.commit().
  • ✓ Řízení kaskádou: Správně naplánujte pokyny ON DELETE (RESTRICT nebo CASCADE) při vytváření schémat, abyste předešli osamoceným záznamům nebo nechtěnému mazání dat v kaskádě.