Corso Python / Collegare MariaDB con PyMySQL e gestire le transazioni

Per usare MariaDB da Python, apri una connessione tramite un driver e passa i valori separatamente dal testo SQL. Questo esempio usa PyMySQL e richiede un server attivo, un database dedicato e un account applicativo: a differenza di SQLite, non basta scegliere un file locale.

Preparare server, tabella e account dedicato

Usa un server MariaDB locale per esercitazioni. Da una connessione amministrativa esegui il seguente SQL soltanto dopo aver controllato che database e account non appartengano ad altre applicazioni. Se i nomi esistono già, scegli nomi nuovi e adattali coerentemente anche nel programma. Non eliminare oggetti estranei per far riuscire il setup.

CREATE DATABASE python_lab CHARACTER SET utf8mb4;
CREATE USER 'python_app'@'127.0.0.1' IDENTIFIED BY 'replace-with-a-local-secret';
CREATE TABLE python_lab.expenses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  external_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  amount BIGINT NOT NULL CHECK(amount >= 0)
) ENGINE=InnoDB;
GRANT SELECT, INSERT, UPDATE ON python_lab.expenses TO 'python_app'@'127.0.0.1';

replace-with-a-local-secret è un segnaposto da sostituire durante la preparazione con un segreto locale; non è una password da riutilizzare. Il programma si connette come python_app, mentre l’amministratore serve soltanto a predisporre schema e permessi. L’account ha SELECT, INSERT e UPDATE sulla sola tabella; non ha bisogno di autorizzazioni sull’intero server.

external_id è una chiave univoca per riconoscere la stessa spesa e amount è un intero non negativo. Il motore InnoDB supporta le transazioni usate qui. La documentazione degli esempi PyMySQL mostra connessione, valori parametrizzati e conferma delle scritture. Per utilizzare l’esempio devi già disporre di un’istanza MariaDB locale raggiungibile e di accesso amministrativo alla sola istanza di prova.

Installare il driver e fornire i segreti

Nell’ambiente virtuale del progetto installa il driver Python:

python -m pip install PyMySQL

Il client usa TCP su 127.0.0.1, coerente con l’host dell’account SQL. La porta predefinita è 3306; se la tua istanza di prova usa un’altra porta, imposta MARIADB_PORT nella stessa sessione prima di eseguire il programma. La variabile deve contenere il numero effettivo della porta.

Fornisci MARIADB_USER e MARIADB_PASSWORD al processo tramite l’ambiente o il gestore di segreti del progetto. Non inserirne i valori reali nel codice, nel controllo di versione o nei log. Anche un comando con una password letterale può rimanere nella cronologia della shell.

Prepara prima mariadb_demo.py con il codice della sezione seguente. In PowerShell questo frammento chiede la password senza mostrarla durante la digitazione e poi rimuove le variabili della sessione:

$env:MARIADB_USER = "python_app"
$secret = Read-Host "MariaDB password" -AsSecureString
$credential = New-Object System.Net.NetworkCredential("", $secret)
$env:MARIADB_PASSWORD = $credential.Password
python mariadb_demo.py
Remove-Item Env:MARIADB_PASSWORD
Remove-Item Env:MARIADB_USER
Remove-Variable secret, credential

In Bash su macOS o Ubuntu puoi usare il frammento seguente. Se l’interprete si chiama python3, sostituisci il nome del comando:

export MARIADB_USER=python_app
read -r -s -p "MariaDB password: " MARIADB_PASSWORD
printf "\n"
export MARIADB_PASSWORD
python mariadb_demo.py
unset MARIADB_PASSWORD MARIADB_USER

Questi comandi eseguono già il programma; non ripeterlo inutilmente soltanto perché viene mostrato anche il comando autonomo sotto. Le variabili d’ambiente passano ai processi figli e non offrono la stessa protezione di un archivio dedicato di segreti. La cancellazione finale evita di mantenerle nella sessione dopo la prova.

Per un server remoto configura TLS e verifica certificato, CA e identità dell’host secondo le opzioni del driver e del server. Cambiare soltanto l’indirizzo non preserva le ipotesi della connessione locale. Le opzioni di connessione PyMySQL descrivono anche timeout e verifica TLS.

Eseguire il programma completo di connessione e salvataggio

Nella cartella di esercitazione salva questo contenuto UTF-8 come mariadb_demo.py:

import os
import pymysql

con = pymysql.connect(
    host="127.0.0.1", port=int(os.environ.get("MARIADB_PORT", "3306")),
    user=os.environ["MARIADB_USER"], password=os.environ["MARIADB_PASSWORD"],
    database="python_lab", charset="utf8mb4", autocommit=False,
    connect_timeout=5, read_timeout=5, write_timeout=5,
)
try:
    try:
        with con.cursor() as cur:
            cur.execute("INSERT INTO expenses(external_id,name,amount) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATE name=VALUES(name), amount=VALUES(amount)", ("demo-1", "lunch", 8000))
        con.commit()
    except Exception:
        con.rollback()
        raise
    with con.cursor() as cur:
        cur.execute("SELECT name,amount FROM expenses WHERE external_id=%s", ("demo-1",))
        print(cur.fetchone())
finally:
    con.close()

Con ambiente, porta e credenziali correttamente impostati, il comando è:

python mariadb_demo.py

L’uscita prevista è ('lunch', 8000) e il codice di uscita deve essere 0. Il valore è espresso in unità intere di KRW, senza conversione valutaria. Una seconda esecuzione mantiene una sola spesa con external_id="demo-1", aggiornandone nome e importo invece di creare nuove transazioni.

Il blocco interno esegue la scrittura e chiama commit() soltanto se riesce. In caso di eccezione chiama rollback() e rilancia l’errore, senza far sembrare riuscita un’operazione fallita. Il finally esterno chiude la connessione anche se la query successiva non riesce. I cursori hanno una durata distinta e vengono chiusi dai loro blocchi with.

Se la scrittura è stata confermata e la SELECT successiva fallisce, il dato già confermato resta salvato: non c’è un rollback globale dell’intero programma. Se invece fallisce direttamente connect, la variabile con non è stata creata e il flusso non è ancora entrato nel blocco di gestione della connessione. La lezione sulle eccezioni e sulla pulizia delle risorse aiuta a distinguere queste fasi.

Verificare parametri, rollback e permessi

I segnaposto dei valori in PyMySQL sono %s sia per stringhe sia per numeri. Passa la tupla come argomento di execute; non applicare l’operatore Python % e non costruire la query con f-string. Nomi di tabelle e colonne sono struttura SQL e non possono essere sostituiti con gli stessi parametri: se devono variare, scegli da un insieme controllato.

1. Esegui il programma due volte con i valori originali e verifica che il conteggio resti uno.
2. In una copia di prova cambia solo 8000 in 9000 nella tupla inserita: la lettura deve mostrare ('lunch', 9000).
3. Cambia temporaneamente l’importo in -1. Il vincolo del database deve rifiutare la scrittura e il valore precedente deve restare 9000. Ripristina poi 8000 e verifica nuovamente l’esito corretto.
4. Prova una password errata e ripristina quella valida. Per un errore di connessione controlla nell’ordine server e porta, host e credenziali dell’account, poi privilegi sulla tabella.
5. Con un account di sola lettura verifica che SELECT sia consentita e la scrittura sia rifiutata. Con l’account applicativo dell’esempio la cancellazione non è autorizzata: non ampliare i permessi globali per aggirare un errore.

Esegui queste prove esclusivamente nell’istanza dedicata. Il rollback riguarda le scritture della transazione, non richieste di rete o file modificati altrove. La lezione sulle transazioni SQLite presenta la stessa distinzione per un archivio incorporato, mentre qui occorre gestire anche il server e l’account di rete.