Ir al contenido

Modelowanie relacyjne i kluczowe pojęcia

Analiza koncepcyjna użycia kursorów i transakcji ACID w silniku sqlite3 w Pythonie, z praktycznym przykładem modelowania relacyjnego jeden-do-wielu.

0odwiedzin

Aby efektywnie i bezpiecznie wchodzić w interakcję z SQLite za pomocą języków programowania takich jak Python, niezbędne jest przyswojenie dwóch fundamentalnych zasad architektonicznych: kursorów bazy danych oraz transakcji.


W bibliotece sqlite3 języka Python wykonanie instrukcji nie odbywa się bezpośrednio na połączeniu z bazą danych (conn). Zamiast tego wymagane jest utworzenie pośredniej struktury sterującej zwanej kursorem (cursor dreading).

  • Definicja techniczna: Kursor działa jako wskaźnik pamięci lub agent sterujący, który wykonuje instrukcje SQL na fizycznym pliku na dysku i zarządza zwróconym zestawem rekordów.
  • Funkcjonowanie operacyjne:
    1. Bezpieczne wykonanie: Odbiera i przetwarza zapytanie: cursor.execute(sql).
    2. Kontrolowana ekstrakcja: Tymczasowo przechowuje dane wynikające z odczytów, umożliwiając ich granularne odzyskiwanie za pomocą cursor.fetchone() (wyodrębnia pojedynczy wiersz) lub cursor.fetchall() (odzyskuje wszystkie rekordy wynikowe).
# 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()

Transakcja to logiczna sekwencja jednej lub więcej instrukcji SQL, które są wykonywane jako jedna niepodzielna jednostka pracy. To zachowanie wynika z zasady atomowości standardu ACID: „albo wszystkie operacje zakończyą się powodzeniem, albo całość serii powróci do stanu początkowego”.

Przeanalizujmy klasyczną transakcję finansową, w której przenosimy saldo między dwoma rekordami:

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;

Gdyby serwer doświadczył nagłej utraty zasilania po zakończeniu operacji 1, ale przed operacją 2, saldo by zniknęło. Transakcje łagodzą to ryzyko:

  • BEGIN TRANSACTION: Otwiera odizolowaną strefę pracy na dysku.
  • COMMIT: Utrwala wszystkie wstawienia lub modyfikacje wprowadzone od początku transakcji.
  • ROLLBACK: Anuluje wprowadzone modyfikacje i przywraca bazę danych do dokładnego stanu oryginalnego sprzed rozpoczęcia bloku.

W Pythonie moduł sqlite3 inicjuje transakcje w sposób niejawny podczas wykonywania instrukcji zapisu (INSERT, UPDATE, DELETE). Aby fizycznie zatwierdzić zmiany w pliku .db, musisz jawnie wywołać conn.commit().


4.2. Studium przypadku: Zarządzanie projektami i zadaniami

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

Poniżej zaimplementujemy relacyjny model bazy danych typu jeden-do-wielu (1:N), korzystając z ograniczeń klucza obcego (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
}

Krok 1: Deklaracja schematów logicznych (DDL)

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

Tworzymy tabelę „nadrzędną” (portel_proyectos), a następnie tabelę „podrzędną” (portel_tareas), łącząc je za pomocą klucza obcego:

-- 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] Integralność referencyjna: Definicja ON DELETE RESTRICT informuje silnik bazy danych o zablokowaniu usunięcia dowolnego projektu w portel_proyectos, jeśli nadal istnieją powiązane zadania osierocone w portel_tareas.


Udostępniamy dwa projekty i przypisujemy zadania, łącząc ich odpowiednie identyfikatory relacyjne:

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

Krok 3: Zaawansowane zapytania relacyjne (DQL - JOIN)

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

Aby pobrać skonsolidowane zadania wraz z nazwą projektu, do którego należą, wdrażamy sprzężenie za 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;

Oczekiwane sformatowane wyjście:

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

Aby w sposób atomowy oznaczyć kamień milowy projektu jako ukończony:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Lista kontrolna bezpiecznego modelowania relacyjnego

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

Zapewnij logiczną spójność swoich baz danych w laboratoriach Portelcore, postępując zgodnie z tą listą kontrolną:

  • ✓ Aktywacja kluczy obcych: Upewnij się, że Twój skrypt Python wywołuje dyrektywę weryfikacji podczas otwierania połączenia: conn.execute("PRAGMA foreign_keys = ON;") (domyślnie wyłączona w SQLite).
  • ✓ Mapowanie kluczy obcych: Sprawdzenie, czy wszystkie logiczne relacje między tabelami danych zawierają deklaratywne ograniczenia typu FOREIGN KEY.
  • ✓ Zatwierdzanie transakcji (Commit): Walidacja, czy wszystkie operacje zapisu zmieniające fizyczny stan pliku .db są jawnie potwierdzane za pomocą conn.commit().
  • ✓ Zarządzanie kaskadowe: Prawidłowe planowanie dyrektyw ON DELETE (RESTRICT lub CASCADE) podczas tworzenia schematów, aby uniknąć osieroconych rekordów lub przypadkowego usuwania danych w kaskadzie.