Il database con SQLite

Finora gli annunci stavano in una lista Python: riavviando il server spariscono. Serve un database: conserva i dati in modo permanente, li condivide tra tutti gli utenti e permette ricerche veloci. Useremo l'SQL che conoscete da Informatica, con le stesse query che scrivete in PHP con mysqli.

Il kit della lezione

• flask5-04-database.zipBacheca con database SQLite: db.py, schema.sql, ricerca, pubblicazione ed eliminazione salvate su file

Al primo avvio nel terminale compare Database creato: …bacheca.db. Pubblicate un annuncio, fermate il server (Ctrl + C), riavviatelo: l'annuncio è ancora lì. Provate anche la ricerca e il pulsante "Elimina".

SQLite: un database in un file

	MySQL (PHP)	SQLite (oggi)
Dove stanno i dati	in un server (XAMPP, Aruba…)	in un file: bacheca.db
Cosa installare	il server MySQL	niente: sqlite3 è già dentro Python
Il linguaggio	SQL	SQL (quasi identico)
Adatto a	siti con tanti utenti contemporanei	sviluppo, app piccole e medie, telefoni

SQLite è il database più usato al mondo: c'è in ogni telefono, in ogni browser, in tantissime app. Per guardare dentro bacheca.db usate l'estensione SQLite Viewer di VS Code (basta un clic sul file) oppure il programma gratuito DB Browser for SQLite.

I file nuovi del kit

flask5-04-database/
├── app.py          ← le route ora usano le funzioni di db.py
├── db.py           ← NUOVO: tutte le funzioni per parlare con il database
├── schema.sql      ← NUOVO: la struttura delle tabelle e i dati di esempio
├── bacheca.db      ← viene creato al primo avvio (non si consegna)
├── templates/
└── static/

schema.sql

CREATE TABLE annunci (
    id        INTEGER PRIMARY KEY AUTOINCREMENT,
    titolo    TEXT    NOT NULL,
    testo     TEXT    NOT NULL,
    categoria TEXT    NOT NULL DEFAULT 'avviso',
    data      TEXT    NOT NULL DEFAULT (date('now', 'localtime'))
);

È SQL normale. Due dettagli di SQLite: i tipi principali sono pochi (INTEGER, REAL, TEXT), e le date si salvano come testo nel formato "AAAA-MM-GG", che si ordina e si confronta correttamente. DEFAULT (date('now', 'localtime')) mette da solo la data di oggi se non la indichiamo.

db.py, le quattro funzioni

def connetti():
    conn = sqlite3.connect(DATABASE)
    conn.row_factory = sqlite3.Row      # le righe si leggono come dizionari
    return conn

def leggi_tutti(sql, parametri=()):     # SELECT con tante righe → lista
    conn = connetti()
    righe = conn.execute(sql, parametri).fetchall()
    conn.close()
    return righe

def leggi_uno(sql, parametri=()):       # SELECT con una riga → riga oppure None
    ...fetchone()...

def esegui(sql, parametri=()):          # INSERT, UPDATE, DELETE
    conn = connetti()
    cursore = conn.execute(sql, parametri)
    conn.commit()                       # senza commit NON si salva niente!
    conn.close()
    return cursore.lastrowid            # l'id della riga appena inserita

Se avete fatto PHP, il confronto è diretto:

PHP con mysqli	Python con sqlite3 (db.py)
mysqli_connect(...)	connetti()
mysqli_prepare + bind_param + execute	conn.execute(sql, parametri)
mysqli_fetch_all(..., MYSQLI_ASSOC)	leggi_tutti(...)
mysqli_fetch_assoc(...)	leggi_uno(...)
mysqli_insert_id($conn)	il valore restituito da esegui(...)

Con row_factory = sqlite3.Row ogni riga si legge per nome della colonna: riga["titolo"] in Python, riga.titolo nei template. Per questo i template non sono cambiati rispetto alla lezione 3: prima ricevevano dizionari, ora righe che si comportano come dizionari.

crea_database() viene chiamata all'avvio di app.py: se bacheca.db non esiste, lo crea eseguendo schema.sql. Volete ripartire da zero? Fermate il server e cancellate bacheca.db.

Le route con il database

Leggere: SELECT

def trova_annuncio(id):
    return leggi_uno("SELECT * FROM annunci WHERE id = ?", (id,))

@app.route("/")
def home():
    annunci = leggi_tutti("SELECT * FROM annunci ORDER BY data DESC, id DESC")
    return render_template("index.html", annunci=annunci)

ORDER BY data DESC, id DESC: prima i più recenti; a parità di data, prima l'ultimo inserito.

Il punto interrogativo: i segnaposto

leggi_uno("SELECT * FROM annunci WHERE id = ?", (id,))

Il ? è un segnaposto: il valore lo inserisce sqlite3, trattandolo sempre come un dato e mai come codice SQL. I parametri vanno passati come tupla: con un solo valore serve la virgola, (id,).

Mai costruire una query unendo stringhe, come f"SELECT * FROM annunci WHERE titolo = '{titolo}'". Se l'utente scrive come titolo ' OR '1'='1, la query cambia significato: è la SQL injection, uno degli attacchi più diffusi al mondo. Lo proverete sul serio nel laboratorio di sicurezza. Con i ? è impossibile.

Cercare: LIKE

q = request.args.get("q", "").strip()
if q:
    annunci = leggi_tutti(
        "SELECT * FROM annunci WHERE titolo LIKE ? OR testo LIKE ? ORDER BY data DESC, id DESC",
        (f"%{q}%", f"%{q}%"))

% vuol dire "qualunque testo": %museo% trova "museo" in qualsiasi punto. I % si mettono nel valore, non nella query, e il ? resta. Il form di ricerca è un form GET: il testo finisce nell'indirizzo (/?q=museo), così una ricerca si può copiare e condividere.

Inserire: INSERT

nuovo_id = esegui(
    "INSERT INTO annunci (titolo, testo, categoria) VALUES (?, ?, ?)",
    (titolo, testo, categoria))
flash("Annuncio pubblicato!", "successo")
return redirect(url_for("annuncio", id=nuovo_id))

id e data non li scriviamo: li mette il database (AUTOINCREMENT e DEFAULT). esegui restituisce l'id appena creato, che usiamo per il redirect.

Eliminare: DELETE

@app.route("/annuncio/<int:id>/elimina", methods=["POST"])
def elimina(id):
    if trova_annuncio(id) is None:
        abort(404)
    esegui("DELETE FROM annunci WHERE id = ?", (id,))
    flash("Annuncio eliminato", "successo")
    return redirect(url_for("home"))

Solo POST: nel template il pulsante sta dentro un <form method="post">.

CRUD

Le quattro operazioni che fa ogni applicazione gestionale:

Lettera	Operazione	SQL	Nella bacheca
C	Create	INSERT	/nuovo ✔
R	Read	SELECT	/, /annuncio/<id>, ricerca ✔
U	Update	UPDATE	tocca a voi (missione 4)
D	Delete	DELETE	/annuncio/<id>/elimina ✔

Gli errori più comuni

Errore	Causa
sqlite3.OperationalError: no such table	il file .db è vecchio o vuoto: cancellatelo e riavviate
sqlite3.OperationalError: near "…": syntax error	errore di scrittura nella query SQL
Incorrect number of bindings supplied	il numero di ? non coincide con i valori nella tupla (o manca la virgola in (id,))
Le modifiche non restano	manca conn.commit() (in esegui c'è già)
database is locked	il file è aperto in scrittura da DB Browser: chiudetelo

Missioni