Corso MariaDB / Collegare Python a MariaDB e usare query con parametri

Quando Python si collega a MariaDB, passare separatamente testo SQL e valori dell'utente tramite l'interfaccia del driver. Le query con parametri evitano di costruire sintassi SQL dai valori; un account limitato e commit o rollback espliciti controllano le modifiche del database.

Preparare ambiente e account applicativo

L'esempio usa Python 3, PyMySQL 1.1.2 e TCP locale verso MariaDB. Il comportamento centrale è verificato con Python 3.12 e MariaDB 12.3.3. PyMySQL è il driver che gestisce la comunicazione dal programma; il segnaposto dei valori è %s. Non sostituirlo con il punto interrogativo di un altro driver.

Eseguire la preparazione seguente da una connessione amministrativa. Sostituire il segnaposto pubblico Replace_With_Your_Own_Lab_Password con una propria password di prova. CREATE USER è previsto per la prima preparazione: controllare account e tabella esistenti prima di ripetere. Se appops_lab esiste, accertare che appartenga a questi esercizi. La guida ad account e privilegi MariaDB descrive le responsabilità.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE app_notes (
  note_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  note_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE USER 'appops_app'@'127.0.0.1'
  IDENTIFIED BY 'Replace_With_Your_Own_Lab_Password';
GRANT SELECT, INSERT ON appops_lab.app_notes TO 'appops_app'@'127.0.0.1';

L'host dell'account 127.0.0.1 presume TCP verso un server direttamente sul computer locale. Un container o server remoto può vedere un'origine diversa. Controllare corrispondenza dell'host e percorso di connessione senza concedere accesso amministrativo da qualsiasi origine.

Creare un progetto separato e salvare il programma completo

Creare una nuova cartella di progetto e aprirvi PowerShell. Python deve essere installato e disponibile come python. Creare un ambiente virtuale, cioè pacchetti Python separati per il progetto, e installare la versione del driver usata:

python -m venv .venv
.venv\Scripts\python.exe -m pip install PyMySQL==1.1.2

Richiamare direttamente il Python dell'ambiente evita di cambiare le regole di esecuzione degli script PowerShell per l'attivazione. Su Linux o macOS usare python3 -m venv .venv e poi .venv/bin/python -m pip install PyMySQL==1.1.2. Salvare il programma completo seguente come file UTF-8 notes_app.py nella cartella:

import argparse
import getpass
import os
import pymysql

def save_and_find(conn, name, sort_key="id"):
    if not name or len(name) > 80:
        raise ValueError("Name must contain 1 to 80 characters")
    columns = {"id": "note_id", "name": "note_name, note_id"}
    if sort_key not in columns:
        raise ValueError("Unknown sort key")
    with conn.cursor() as cur:
        cur.execute("INSERT INTO app_notes (note_name) VALUES (%s)", (name,))
        cur.execute(
            "SELECT note_id, note_name FROM app_notes "
            "WHERE note_name = %s ORDER BY " + columns[sort_key],
            (name,),
        )
        return cur.fetchall()

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--name", default="O'Reilly")
    parser.add_argument("--sort", choices=("id", "name"), default="id")
    args = parser.parse_args()
    password = getpass.getpass("Database password: ")
    conn = pymysql.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        port=int(os.getenv("DB_PORT", "3306")),
        user=os.getenv("DB_USER", "appops_app"),
        password=password,
        database="appops_lab",
        charset="utf8mb4", autocommit=False,
        connect_timeout=5, read_timeout=10, write_timeout=10,
    )
    try:
        rows = save_and_find(conn, args.name, args.sort)
        conn.commit()
        for note_id, note_name in rows:
            print(note_id, note_name)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    main()

Il programma richiede la password tramite getpass all'esecuzione, senza conservarla nel sorgente o nel testo SQL. La connessione usa utf8mb4 per Unicode e timeout per connessione, lettura e scrittura. Il messaggio Database password: richiede la password del database.

La configurazione è destinata alla prova locale. Su altre reti serve anche Transport Layer Security, o TLS, la protezione cifrata della connessione, con verifica del certificato server. La sola password non protegge il percorso di trasmissione.

Provare un nome ordinario e uno con apostrofo

Nella cartella di notes_app.py eseguire i due comandi come avvii separati del programma:

.venv\Scripts\python.exe notes_app.py --name "Notebook"
.venv\Scripts\python.exe notes_app.py --name "O'Reilly" --sort name

Inserire la password ogni volta. Il primo comando salva Notebook, il secondo O'Reilly con l'apostrofo. Entrambi cercano poi le righe con lo stesso nome. Su una tabella nuova, eseguiti una volta ciascuno, restituiscono una riga corrispondente. Non richiedere uno specifico ID AUTO_INCREMENT: dati precedenti e inserimenti annullati influenzano la numerazione.

Ripetere lo stesso nome aggiunge un'altra riga e restituisce tutte le corrispondenze. L'esempio non impone l'unicità del nome. Se il dominio richiede prevenzione dei duplicati, definire esplicitamente identità della registrazione e vincoli appropriati.

Separare valori e sintassi SQL

La chiamata centrale è cur.execute(sql, (name,)). SQL e tupla dei valori sono argomenti distinti; la virgola crea una tupla di un elemento. Non aggiungere manualmente virgolette SQL intorno a %s. Non inserire valori dell'utente tramite operatore % di Python, f-string o concatenazioni. Gli esempi ufficiali PyMySQL mostrano il passaggio separato dei valori.

Un nome come x' OR 1=1 -- deve rimanere un singolo valore memorizzato e cercato letteralmente, senza diventare una condizione che seleziona tutto. Qui «query con parametri» descrive l'interfaccia del driver e non implica che ogni driver usi sempre il protocollo di prepared statement del server.

Identificatori e parole chiave SQL, compresi colonne e ASC o DESC, non si sostituiscono con normali parametri di valore. ORDER BY proviene soltanto dalla mappa columns definita dallo sviluppatore. argparse e la funzione controllano entrambi le scelte, impedendo anche a chiamanti diversi dalla riga di comando di superare l'elenco ammesso. Il riferimento di Cursor.execute descrive gli argomenti.

Verificare insieme transazioni e privilegi

Con autocommit=False, il ritorno senza errori di save_and_find non rende permanente INSERT. main conferma dopo il successo di INSERT e SELECT, annulla in caso di eccezione e chiude in finally. Distinguere il confine del commit dai dati visibili ad altre connessioni. La guida a InnoDB, COMMIT e ROLLBACK ne descrive i confini.

La funzione rifiuta nome vuoto, oltre 80 caratteri e chiavi di ordinamento sconosciute prima di inserire. Name must contain 1 to 80 characters indica il limite di 1–80 caratteri; Unknown sort key indica una scelta sconosciuta. Se si verifica un'eccezione dopo INSERT e prima di COMMIT, ROLLBACK deve impedire la persistenza della riga.

L'account ha soltanto SELECT e INSERT su app_notes. Non crea né elimina tabelle, quindi non richiede CREATE o DROP. Privilegi limitati completano i parametri corretti e riducono le operazioni possibili in presenza di altri difetti. Il riferimento delle connessioni PyMySQL descrive autocommit e timeout.

Diagnosticare gli errori e pulire la prova

Sintomo Controllo
ModuleNotFoundError Usare lo stesso Python .venv impiegato per installare PyMySQL
Access denied Utente, password, host dell'account e origine vista dal server
Connessione rifiutata o scaduta Server, DB_HOST, DB_PORT, firewall e percorso effettivo
Riga assente dopo l'elaborazione Eccezione con rollback, database e stato di commit esaminati
Errore soltanto con apostrofi Valore interpolato manualmente invece di passarlo separatamente a execute

Impostazioni non segrete possono essere sovrascritte in PowerShell, per esempio $env:DB_PORT = "3307". Conservare il prompt interattivo della password. Rimuovere l'override non più necessario con Remove-Item Env:DB_PORT, oppure ripristinare il valore precedente se era già presente.

Al termine fermare il programma e far confermare all'amministratore che i nomi seguenti corrispondano all'account e alla tabella eliminabili dell'esercizio:

DROP USER 'appops_app'@'127.0.0.1';
DROP TABLE appops_lab.app_notes;

Non sostituire account o tabelle applicative nella pulizia. Queste eliminazioni non vengono annullate da un normale ROLLBACK. Salvare prima i dati da conservare.