Corso Python / Salvare dati con sqlite3 e gestire le transazioni

Il modulo sqlite3 consente di salvare dati SQL in un file senza un server di database separato. Passa i valori come parametri, invece di inserirli nel testo SQL, e gestisci esplicitamente conferma, annullamento e chiusura della connessione.

Scegliere come controllare la transazione

SQLite è un motore incorporato nell’applicazione. È pratico per iniziare con un database locale, ma la gestione di scritture concorrenti e le condizioni operative differiscono da quelle di un database a server. Aprire una connessione non rende automaticamente permanenti tutte le modifiche successive.

Questa lezione usa autocommit=False, disponibile in Python 3.12 o successivo, per rendere esplicita la modalità di gestione della transazione. Il blocco with con conferma con commit se termina correttamente e annulla con rollback se esce per un’eccezione; non chiude però la connessione. La chiusura resta nel finally separato.

La documentazione ufficiale di sqlite3 distingue controllo delle transazioni e durata della connessione. Il principio di propagare un fallimento al livello appropriato è trattato nella guida alle eccezioni.

Inserire e leggere un record completo

Crea una nuova cartella di esercizio: ledger-demo.db deve essere un database dimostrativo, non un archivio con dati importanti. Salva il programma completo come sqlite_demo.py, in UTF-8, ed esegui python sqlite_demo.py dal terminale con Python 3.12 o successivo selezionato. Fuori dall’ambiente virtuale su macOS o Ubuntu usa python3 se è il comando scelto.

import sqlite3
MAX_AMOUNT = 1_000_000_000_000
amount = 8000
if type(amount) is not int or not 0 <= amount <= MAX_AMOUNT:
    raise ValueError("amount outside practice range")
con = sqlite3.connect("ledger-demo.db", autocommit=False)
try:
    with con:
        con.execute("CREATE TABLE IF NOT EXISTS expenses (id TEXT PRIMARY KEY, name TEXT NOT NULL, amount INTEGER NOT NULL CHECK(amount BETWEEN 0 AND 1000000000000))")
        con.execute("INSERT INTO expenses VALUES (?, ?, ?) ON CONFLICT(id) DO UPDATE SET name=excluded.name, amount=excluded.amount", ("e1", "lunch", amount))
    print(con.execute("SELECT name, amount FROM expenses ORDER BY id").fetchall())
finally:
    con.close()

In un database nuovo, il risultato previsto è:

[('lunch', 8000)]

La tabella contiene un identificatore, un nome obbligatorio e un importo con un vincolo di intervallo. L’istruzione INSERT usa parametri separati dal testo SQL. Se esiste già la chiave e1, ON CONFLICT aggiorna nome e importo invece di inserire una seconda copia di quel record.

La query legge tutte le righe ordinandole per id. Se aggiungi altri identificatori durante le prove, anche quelli compariranno nel risultato: riavviare l’originale aggiorna e1, ma non svuota la tabella. Per ritrovare esattamente l’output iniziale puoi usare una cartella nuova senza eliminare un database di cui non conosci il contenuto.

Distinguere parametri, vincoli e tipi memorizzati

I punti interrogativi indicano posizioni per i valori, forniti nella tupla come secondo argomento di execute. Per una sola posizione la tupla deve avere la virgola, come (value,). Una stringa contenente apici o parole che assomigliano a SQL rimane un dato quando viene legata correttamente come parametro.

I parametri non sostituiscono la struttura SQL: non usarli per scegliere nomi di tabelle, colonne o parole chiave. Se tali elementi devono variare, selezionali da un insieme di alternative controllate, mantenendo i valori separati.

La regola Python accetta esattamente interi da 0 a 1.000.000.000.000, escludendo True e stringhe. Il limite è una scelta della prova, non il massimo generale dei numeri Python. La classe di memorizzazione INTEGER di SQLite usa un intero con segno fino a 64 bit, mentre Python può rappresentare interi più grandi nei limiti delle risorse.

SQLite usa normalmente tipi flessibili: dichiarare una colonna INTEGER non la rende automaticamente equivalente a type(value) is int. Il CHECK dell’esempio impone l’intervallo, ma un valore frazionario positivo può ancora soddisfarlo se scritto direttamente da un altro percorso. Per imporre anche il tipo nello schema serve una scelta ulteriore, per esempio un vincolo appropriato o una tabella STRICT progettata per il requisito. La documentazione SQLite sui tipi e l’affinità spiega questa distinzione.

PRIMARY KEY, NOT NULL e CHECK offrono controlli nello schema oltre a quelli del programma, ma devi conoscere esattamente quale regola esprimono. Inoltre CREATE TABLE IF NOT EXISTS non modifica una tabella già presente: se provi un vecchio database senza il CHECK, l’esecuzione non aggiunge magicamente quel vincolo. Su dati reali servono backup e una migrazione separata verificata.

Verificare rifiuti, ripetizione e chiusura

1. Esegui due volte il programma originale nella cartella nuova. Deve restare un solo record con id e1, non due copie della stessa spesa.
2. Cambia soltanto amount a -1. Il controllo Python deve generare ValueError prima del collegamento al database; controlla poi che il valore precedentemente salvato sia ancora 8000.
3. Ripristina 8000. In una prova separata cambia l’id del record in e2 e il nome in bus: compariranno due righe ordinate secondo id. L’id, non la somiglianza di nome e importo, decide il conflitto dell’upsert.

Per osservare il vincolo indipendentemente dal controllo Python, lavora solo su una copia del database dimostrativo e prova una scrittura SQL con un importo negativo: CHECK deve produrre IntegrityError. Se più scritture sono nello stesso blocco with e un errore esce dal blocco, rollback annulla anche le scritture precedenti di quella transazione; gestire l’errore troppo presto e continuare all’interno del blocco può cambiare questa politica.

Un’uscita dal blocco with non rende inutilizzabile la connessione: è la chiamata close nel finally a chiuderla. Non affidarti alla semplice fine di un blocco per rilasciare una connessione che rimane aperta. Se compaiono errori di blocco del file, controlla altre connessioni, transazioni lunghe e chiusure mancanti.

Senza ORDER BY non basare il programma sull’ordine osservato casualmente. Per recuperare una prova o gestire uno schema precedente, evita di inserire DROP TABLE per comodità: identifica prima database, stato e dati da conservare. La separazione tra inizializzazione, aggiornamento e lettura rende più chiaro cosa cambia a ogni esecuzione.