SQLite e Python: Guida Pratica dal JOIN ai Trigger (con Esercizi)

1. Studenti e corsi iscritti:
- Laura Bianchi: Fisica (voto: 27)
- Laura Bianchi: Matematica (voto: 25)
- Marco Rossi: Fisica (voto: 26)
- Marco Rossi: Informatica (voto: 30)
- Marco Rossi: Matematica (voto: 28)
- Giuseppe Verdi: Fisica (voto: 22)

2. Media voti per corso:
- Matematica: 26.50
- Fisica: 25.00
- Informatica: 30.00

3. Studenti non iscritti a nessun corso:
- Sofia Russo

💡 Osservazioni:

• INNER JOIN seleziona solo record con corrispondenza in entrambe le tabelle

• LEFT JOIN seleziona tutti i record della prima tabella, anche senza corrispondenza

• Le chiavi esterne (FOREIGN KEY) mantengono l’integrità referenziale

• IS NULL identifica valori mancanti dopo un LEFT JOIN

Il potere delle relazioni

Questo esercizio non riguarda solo il “unire tabelle”, ma la normalizzazione.

È interessante perché mostra come l’integrità referenziale permetta di interrogare il database in modo multidimensionale.

La query sugli studenti “non iscritti” è il classico esempio di come il SQL possa rispondere a domande di business (es. “quali utenti abbiamo acquisito ma non stanno usando il servizio?”).

Domanda di riflessione: Qual è la differenza tra INNER JOIN e LEFT JOIN? Quando useresti ciascuno?

ESERCIZIO 2: Viste e Indici per Ottimizzazione

Livello: Avanzato
Contesto realistico: Ottimizzi un database di e-commerce con migliaia di prodotti. Le query sono lente.

Testo:
Crea un database con prodotti e ordini. Poi:

1. Crea una vista che mostri prodotti con quantità inferiore a 10

2. Crea un indice sulla colonna categoria della tabella prodotti

3. Mostra come l’indice migliora le prestazioni (concettualmente)

4. Crea una vista complessa che unisce prodotti e statistiche ordini

Soluzione

import sqlite3
import time

conn = sqlite3.connect('ecommerce.db')
cursor = conn.cursor()

# Tabelle di base
cursor.execute('''
CREATE TABLE IF NOT EXISTS prodotti (
    id INTEGER PRIMARY KEY,
    nome TEXT,
    categoria TEXT,
    prezzo REAL,
    quantita INTEGER,
    data_aggiunta DATE
)
''')

cursor.execute('''
CREATE TABLE IF NOT EXISTS ordini (
    id INTEGER PRIMARY KEY,
    prodotto_id INTEGER,
    quantita INTEGER,
    data_ordine DATE,
    FOREIGN KEY (prodotto_id) REFERENCES prodotti(id)
)
''')

# Popolamento con molti dati (simulato)
cursor.execute('DELETE FROM prodotti')
prodotti = []
for i in range(1, 1001):
    categoria = 'Elettronica' if i % 3 == 0 else 'Abbigliamento' if i % 3 == 1 else 'Libri'
    prodotti.append((i, f'Prodotto {i}', categoria, i * 10, i % 20, '2024-01-15'))

cursor.executemany('INSERT INTO prodotti VALUES (?, ?, ?, ?, ?, ?)', prodotti)

# 1. Vista prodotti in esaurimento
cursor.execute('''
CREATE VIEW IF NOT EXISTS prodotti_in_esaurimento AS
SELECT id, nome, categoria, quantita
FROM prodotti
WHERE quantita < 10
ORDER BY quantita ASC
''')

print("1. Vista 'prodotti_in_esaurimento' creata.")
cursor.execute('SELECT COUNT(*) FROM prodotti_in_esaurimento')
count = cursor.fetchone()[0]
print(f"   Prodotti con quantità < 10: {count}")
 

# 2. Indice su categoria
print("\n2. Creazione indice su colonna 'categoria'...")
cursor.execute('CREATE INDEX IF NOT EXISTS idx_categoria ON prodotti(categoria)')

 #3. Dimostrazione prestazioni (concettuale)
print("\n3. Confronto prestazioni (concettuale):")
print("   SENZA indice: SQLite deve scansionare tutta la tabella (scan sequenziale)")
print("   CON indice: SQLite usa l'indice come 'indice di un libro' per accesso diretto")

# Query con spiegazione
print("\n   Spiegazione query con indice (EXPLAIN QUERY PLAN):")
cursor.execute('EXPLAIN QUERY PLAN SELECT * FROM prodotti WHERE categoria = "Elettronica"')
for row in cursor.fetchall():
    print(f"   {row[3]}")

# 4. Vista complessa con statistiche
cursor.execute('''
CREATE VIEW IF NOT EXISTS statistiche_prodotti AS
SELECT 
    p.id,
    p.nome,
    p.categoria,
    p.quantita as giacenza,
    COUNT(o.id) as numero_ordini,
    COALESCE(SUM(o.quantita), 0) as totale_venduto,
    CASE 
        WHEN COUNT(o.id) > 0 THEN COALESCE(SUM(o.quantita), 0) * p.prezzo
        ELSE 0
    END as fatturato_totale
FROM prodotti p
LEFT JOIN ordini o ON p.id = o.prodotto_id
GROUP BY p.id
ORDER BY fatturato_totale DESC
''')

print("\n4. Vista 'statistiche_prodotti' creata.")
print("   Mostra per ogni prodotto: giacenza, ordini, quantità venduta, fatturato")

# Esempio uso vista
print("\n   Top 5 prodotti per fatturato (dalla vista):")
cursor.execute('SELECT nome, fatturato_totale FROM statistiche_prodotti LIMIT 5')
for row in cursor.fetchall():
    print(f"   - {row[0]}: {row[1]:.2f}€")

conn.commit()
conn.close()

💡 Osservazioni:

• Le viste sono query salvate che si comportano come tabelle virtuali

• Gli indici migliorano le prestazioni di ricerca ma rallentano inserimenti/aggiornamenti

• EXPLAIN QUERY PLAN mostra come SQLite esegue una query

• COALESCE() restituisce il primo valore non NULL tra i suoi argomenti

L’arte dell’invisibile (Ottimizzazione)

Qui la peculiarità è il passaggio da dati “passivi” a dati “intelligenti”. La creazione dell’indice trasforma una ricerca lineare O(n) in una ricerca logaritmica O(log n). È interessante perché introduce il concetto di Virtual Table (Viste): permettono di semplificare query mostruose in semplici SELECT * FROM vista, migliorando la leggibilità del codice Python senza appesantire il database.

Domanda di riflessione: In quali casi NON conviene creare un indice?

ESERZIZIO 3: Trigger e Automazione

Livello: Esperto
Contesto realistico: Implementi un sistema di inventory con controllo automatico del stock e log delle modifiche.

Testo:
Crea un sistema dove:

1. Un trigger impedisce di impostare una quantità negativa

2. Un trigger tiene un log delle modifiche ai prezzi

3. Un trigger aggiorna automaticamente la data di modifica

Soluzione

import sqlite3
from datetime import datetime

conn = sqlite3.connect('inventory.db')
cursor = conn.cursor()

# Tabelle principali
cursor.execute('''
CREATE TABLE IF NOT EXISTS prodotti (
    id INTEGER PRIMARY KEY,
    nome TEXT NOT NULL,
    prezzo REAL CHECK(prezzo >= 0),
    quantita INTEGER CHECK(quantita >= 0),
    data_modifica TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
''')

# Tabella di log per audit
cursor.execute('''
CREATE TABLE IF NOT EXISTS log_prezzi (
    id INTEGER PRIMARY KEY,
    prodotto_id INTEGER,
    vecchio_prezzo REAL,
    nuovo_prezzo REAL,
    data_cambiamento TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (prodotto_id) REFERENCES prodotti(id)
)
''')

# 1. Trigger: impedisci quantità negative
cursor.execute('''
CREATE TRIGGER IF NOT EXISTS check_quantita
BEFORE UPDATE OF quantita ON prodotti
FOR EACH ROW
WHEN NEW.quantita < 0
BEGIN
    SELECT RAISE(ABORT, 'Quantità non può essere negativa');
END
''')

# 2. Trigger: log cambiamenti prezzo
cursor.execute('''
CREATE TRIGGER IF NOT EXISTS log_cambiamento_prezzo
AFTER UPDATE OF prezzo ON prodotti
FOR EACH ROW
WHEN OLD.prezzo != NEW.prezzo
BEGIN
    INSERT INTO log_prezzi (prodotto_id, vecchio_prezzo, nuovo_prezzo)
    VALUES (OLD.id, OLD.prezzo, NEW.prezzo);
END
''')

# 3. Trigger: aggiorna automaticamente data_modifica
cursor.execute('''
CREATE TRIGGER IF NOT EXISTS update_timestamp
AFTER UPDATE ON prodotti
FOR EACH ROW
BEGIN
    UPDATE prodotti 
    SET data_modifica = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END
''')

# Reset tabelle
cursor.execute('DELETE FROM prodotti')
cursor.execute('DELETE FROM log_prezzi')

# Inserimento dati test
cursor.execute('''
INSERT INTO prodotti (id, nome, prezzo, quantita) 
VALUES (1, 'Laptop Gaming', 999.99, 15)
''')

cursor.execute('''
INSERT INTO prodotti (id, nome, prezzo, quantita) 
VALUES (2, 'Mouse Wireless', 29.99, 50)
''')

conn.commit()

print("=== SISTEMA INVENTORY CON TRIGGER ===\n")

print("1. Stato iniziale prodotti:")
cursor.execute('SELECT id, nome, prezzo, quantita FROM prodotti')
for row in cursor.fetchall():
    print(f"   Prodotto {row[0]}: {row[1]}, Prezzo: {row[2]}€, Quantità: {row[3]}")

print("\n2. Test: Tentativo di impostare quantità negativa...")
try:
    cursor.execute('UPDATE prodotti SET quantita = -5 WHERE id = 1')
    conn.commit()
    print("   ERRORE: Questo messaggio non dovrebbe apparire!")
except sqlite3.Error as e:
    print(f"   ✅ Trigger bloccato: {e}")

print("\n3. Test: Modifica prezzo (dovrebbe creare log)...")
cursor.execute('UPDATE prodotti SET prezzo = 899.99 WHERE id = 1')
conn.commit()
print("   ✅ Prezzo aggiornato")

print("\n4. Test: Log cambiamenti prezzo:")
cursor.execute('SELECT * FROM log_prezzi')
log_entries = cursor.fetchall()
if log_entries:
    for entry in log_entries:
        print(f"   Prodotto {entry[1]}: {entry[2]}€ → {entry[3]}€ il {entry[4]}")
else:
    print("   Nessuna voce di log (inespetato!)")

print("\n5. Test: Data modifica aggiornata automaticamente?")
cursor.execute('SELECT nome, data_modifica FROM prodotti WHERE id = 1')
result = cursor.fetchone()
print(f"   {result[0]}: ultima modifica il {result[1]}")

print("\n6. Stato finale prodotti:")
cursor.execute('SELECT id, nome, prezzo, quantita, data_modifica FROM prodotti')
for row in cursor.fetchall():
    print(f"   Prodotto {row[0]}: {row[1]}, Prezzo: {row[2]}€, Quantità: {row[3]}, Modifica: {row[4]}")

conn.close()

=== SISTEMA INVENTORY CON TRIGGER ===

1. Stato iniziale prodotti:
   Prodotto 1: Laptop Gaming, Prezzo: 999.99€, Quantità: 15
   Prodotto 2: Mouse Wireless, Prezzo: 29.99€, Quantità: 50

2. Test: Tentativo di impostare quantità negativa...
   ✅ Trigger bloccato: Quantità non può essere negativa

3. Test: Modifica prezzo (dovrebbe creare log)...
   ✅ Prezzo aggiornato

4. Test: Log cambiamenti prezzo:
   Prodotto 1: 999.99€ → 899.99€ il 2026-01-27 13:53:17

5. Test: Data modifica aggiornata automaticamente?
   Laptop Gaming: ultima modifica il 2026-01-27 13:53:17

6. Stato finale prodotti:
   Prodotto 1: Laptop Gaming, Prezzo: 899.99€, Quantità: 15, Modifica: 2026-01-27 13:53:17
   Prodotto 2: Mouse Wireless, Prezzo: 29.99€, Quantità: 50, Modifica: 2026-01-27 13:53:17