Ir al contenido

Modellazione Relazionale e Concetti Chiave

Analisi concettuale dell'uso di cursori e transazioni ACID nel motore sqlite3 di Python, con un esempio pratico di modellazione relazionale uno-a-molti.

0letture

Per interagire in modo efficiente e sicuro con SQLite da linguaggi di programmazione come Python, è fondamentale assimilare due principi architettonici fondamentali: i Cursori del database e le Transazioni transazionali.


Nella libreria sqlite3 di Python, l'esecuzione di istruzioni non viene eseguita direttamente sulla connessione al database (conn). Al suo posto, è necessario istanziare una struttura di controllo intermedia denominata Cursor (cursor).

  • Definizione Tecnica: Un cursore agisce come un puntatore di memoria o agente di controllo che esegue le istruzioni SQL sul file fisico su disco e gestisce l'insieme di record restituiti.
  • Funzionamento Operativo:
    1. Esecuzione Sicura: Riceve e elabora la query: cursor.execute(sql).
    2. Estrazione Controllata: Memorizza temporaneamente i dati risultanti dalle letture, consentendo di recuperarli in modo granulare utilizzando cursor.fetchone() (estrare una sola riga) o cursor.fetchall() (recuperare la totalità dei record risultanti).
# 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()

Una transazione è una sequenza logica di una o più istruzioni SQL che vengono eseguite come una singola unità di lavoro indivisibile. Questo comportamento risponde al principio di Atomicità dello standard ACID: “o tutte le operazioni vengono completate con successo, o l'intero lotto viene ripristinato allo stato iniziale”.

Analizziamo la classica transazione finanziaria in cui trasferiamo il saldo tra due record:

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;

Se il server subisse un'improvvisa perdita di alimentazione dopo aver completato l'operazione 1 ma prima dell'operazione 2, il saldo svanirebbe. Le transazioni mitigano questo rischio:

  • BEGIN TRANSACTION: Apre una zona di lavoro isolata su disco.
  • COMMIT: Rende permanenti tutte le inserzioni o le modifiche effettuate dall'inizio della transazione.
  • ROLLBACK: Annulla le modifiche effettuate e ripristina il database al suo esatto stato originale precedente all'inizio del blocco.

In Python, il modulo sqlite3 inizializza le transazioni in modo implicito eseguendo istruzioni di scrittura (INSERT, UPDATE, DELETE). Per confermare fisicamente le modifiche nel file .db, devi chiamare esplicitamente conn.commit().


4.2. Caso Pratico: Gestione di Progetti e Attività

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

Successivamente, implementeremo un modello di database relazionale di tipo Uno-a-Molti (1:N) utilizzando vincoli di chiave esterna (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
}

Creiamo la tabella “padre” (portel_proyectos) e successivamente la tabella “figlia” (portel_tareas), collegandole tramite chiave esterna:

-- 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] Integrità Referenziale: La definizione ON DELETE RESTRICT indica al motore del database di bloccare l'eliminazione di qualsiasi progetto in portel_proyectos se esistono ancora attività orfane collegate in portel_tareas.


Forniamo due progetti e assegniamo attività collegando i rispettivi ID relazionali:

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

Per recuperare i task consolidati insieme al nome del progetto a cui appartengono, abbiamo implementato un accoppiamento tramite 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

Per contrassegnare la milestone di progettazione come completata in modo atomico:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Checklist di Modellazione Relazionale Sicura

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

Garantisci la coerenza logica dei tuoi database nei laboratori di Portelcore seguendo questa checklist di controllo:

  • ✓ Attivazione delle Chiavi Esterne: Assicurarsi che lo script Python invochi la direttiva di verifica quando si apre la connessione: conn.execute("PRAGMA foreign_keys = ON;") (disattivata di default in SQLite).
  • ✓ Mappatura delle Chiavi Esterne: Verificare che tutte le relazioni logiche tra tabelle di dati contengano restrizioni dichiarative di tipo FOREIGN KEY.
  • ✓ Conferma Transazionale (Commit): Validare che tutte le operazioni di scrittura che alterano lo stato fisico del file .db siano confermate esplicitamente tramite conn.commit().
  • ✓ Gestione a Cascata: Pianificare correttamente le direttive ON DELETE (RESTRICT o CASCADE) quando si creano schemi per evitare record orfani o cancellazioni accidentali di dati a cascata.