Python-Kurs / MariaDB mit PyMySQL verbinden und Transaktionen prüfen

Verbinden Sie Python über einen Datenbanktreiber mit MariaDB und übergeben Sie SQL-Werte getrennt vom Anweisungstext. Dieses Beispiel verwendet den Python-Treiber PyMySQL und benötigt einen laufenden lokalen MariaDB-Server, eine eigene Übungsdatenbank sowie ein eingeschränktes Anwendungskonto.

Server, Schema und Anwendungskonto vorbereiten

Anders als SQLite arbeitet MariaDB hier als eigenständiger Serverprozess. Installieren und starten Sie zuerst eine geeignete lokale Instanz und prüfen Sie deren TCP-Port. Die Übung verwendet standardmäßig 127.0.0.1:3306. Ein vorhandener fremder Datenbestand ist keine passende Grundlage für die folgenden Schema-Anweisungen.

Führen Sie das folgende SQL über eine administrative Verbindung ausschließlich in Ihrer vorgesehenen Übungsinstanz aus. Wenn die Datenbank oder das Konto mit diesem Namen bereits existiert, klären Sie zuerst deren Verwendung und wählen Sie gegebenenfalls andere Namen. Ersetzen Sie das gezeigte Beispielpasswort durch ein eigenes lokales Geheimnis. Verwenden Sie das Administratorkonto nur für diese Vorbereitung, nicht im Python-Programm.

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';

Das Anwendungskonto erhält nur SELECT, INSERT und UPDATE auf der benötigten Tabelle. Es benötigt weder Tabellenlöschung noch serverweite Verwaltungsrechte. ENGINE=InnoDB wählt die transaktionale Speicherengine. external_id ist eindeutig, damit ein erneuter Lauf denselben Übungsdatensatz aktualisieren kann.

CHECK(amount >= 0) lässt negative Beträge scheitern. Es ist keine vollständige Prüfung beliebiger Python-Eingaben; beim Erweitern der Anwendung brauchen Namen, Identität, Typen und Wertebereiche eine eigene Regel. Das Beispiel übergibt feste Testwerte. Die MariaDB-Referenz zu Constraints beschreibt die Datenbankregeln.

Den Treiber installieren und Zugangsdaten übergeben

Installieren Sie PyMySQL in der Projektumgebung:

python -m pip install PyMySQL

Speichern Sie die Python-Datei aus dem nächsten Abschnitt als mariadb_demo.py im eigenen Übungsordner. Setzen Sie Benutzer und Passwort im aktuellen Prozessumfeld, statt echte Geheimnisse im Quelltext oder Versionsverlauf abzulegen. Die PyMySQL-Beispiele zeigen Parameterabfragen und die getrennte Bestätigung von Änderungen.

Für eine PowerShell-Sitzung kann das Passwort verdeckt eingelesen werden. Führen Sie die Schritte erst aus, wenn die Programmdatei vorbereitet ist, und entfernen Sie danach die Umgebungs- und Hilfsvariablen:

$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

Für Bash unter macOS oder Ubuntu verwendet die aktuelle Shell beispielsweise:

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

Die tatsächliche Passwortzeichenfolge gehört nicht als Literal in einen Shell-Befehl und darf nicht ausgegeben werden. Umgebungsvariablen werden an Kindprozesse weitergegeben; sie bieten nicht automatisch den Schutz eines eigenen Geheimnisspeichers. Die gezeigten Abläufe gelten für die jeweilige aktuelle Sitzung.

Der Serverhost ist ausdrücklich die TCP-Adresse 127.0.0.1, daher passt auch der Benutzerhost im SQL dazu. Bei einem anderen lokalen Port setzen Sie zusätzlich MARIADB_PORT in derselben Sitzung; ohne diese Variable nimmt das Programm 3306 an. Prüfen Sie bei Verbindungsfehlern zuerst Server und Port, danach Konto-Host und Passwort, anschließend die Tabellenrechte. Erteilen Sie keine globalen Rechte nur, um einen einzelnen verweigerten Zugriff zu überdecken.

Diese Übung verbindet sich lokal. Für einen entfernten Server konfigurieren Sie TLS mit passender Zertifizierungsstelle und Prüfung des Servernamens. Eine bloße Änderung der Hostadresse erhält die bisherigen Verbindungsannahmen nicht. Die PyMySQL-Verbindungsreferenz beschreibt unter anderem Zeitlimits und TLS-Optionen.

Die vollständige Verbindung und Speicherung ausführen

Die vollständige UTF-8-Datei mariadb_demo.py lautet:

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()

Starten Sie nach der Vorbereitung mit dem Interpreter der Projektumgebung:

python mariadb_demo.py

Erwartete Ausgabe ist ('lunch', 8000). Unter macOS oder Ubuntu kann der Interpreterbefehl außerhalb einer virtuellen Umgebung python3 heißen. Der feste Testbetrag ist ein ganzer KRW-Betrag. Ein unveränderter erneuter Lauf muss dieselbe Ausgabe liefern und weiterhin genau einen Datensatz mit external_id="demo-1" enthalten.

Die Werteplatzhalter lauten in PyMySQL %s, auch für Zahlen. Das zweite Argument von execute liefert die Werte; verwenden Sie dafür weder den Python-Operator % noch einen f-String. Ein SQL-ähnlicher Name bleibt so ein Textwert. Ein Tabellen- oder Spaltenname ist dagegen Teil der SQL-Struktur und muss bei variabler Auswahl aus einer kontrollierten erlaubten Menge stammen.

autocommit=False verlangt eine bewusste Bestätigung durch commit(). Falls im Schreibteil eine Ausnahme auftritt, führt der innere Fehlerpfad rollback() aus und reicht den Fehler weiter. Danach liest das Programm den gespeicherten Datensatz; der äußere finally-Block schließt die Verbindung. Das Schließen eines Cursors, die Transaktionsbestätigung und das Verbindungsende erfüllen unterschiedliche Aufgaben.

Verbindungsfehler, Rollback und Rechte praktisch prüfen

1. Führen Sie das Original zweimal aus und prüfen Sie die Ausgabe sowie die Zeilenanzahl. Ändern Sie dann in einer Kopie nur den Betrag auf 9000; derselbe externe Schlüssel muss aktualisiert werden.
2. Verwenden Sie vorübergehend ein falsches Passwort. Die Verbindung muss bereits vor einer Schreiboperation scheitern. Stellen Sie danach die richtige Sitzungsvariable wieder her, ohne sie auszugeben.
3. Ändern Sie den Testbetrag auf -1. Der Datenbank-Constraint muss den Schreibversuch ablehnen; der zuvor bestätigte Wert muss erhalten bleiben. Stellen Sie anschließend die ursprüngliche Datei wieder her.
4. Prüfen Sie in der eigenen Übungsinstanz den Unterschied zwischen erlaubtem Lesen und einer nicht erteilten Operation. Für einen reinen Lesetest verwenden Sie ein getrenntes Lesekonto; ändern Sie nicht unnötig die Rechte eines anderswo verwendeten Kontos.

Wenn pymysql.connect selbst scheitert, ist noch kein Verbindungsobjekt entstanden. Der spätere finally-Block ist dann nicht der Fehlerort. Nach einer erfolgreichen Verbindung unterscheiden Sie wiederum Schreibfehler und Fehler der anschließenden Leseoperation: Ein Fehler nach dem bereits erfolgreichen commit macht die bestätigte Änderung nicht automatisch rückgängig.

Ein Datenbank-Rollback nimmt außerdem keine früheren Dateiänderungen oder externen Anfragen zurück. Die SQLite-Übung zur Transaktionsgrenze zeigt dieselbe Trennung für eine eingebettete Datenbank. Beenden Sie eine nur für diese Übung gestartete lokale Instanz nach dem Test kontrolliert und bewahren Sie benötigte Ergebnisse vor dem Aufräumen auf.