Python MySQL Datenbankzugriff: Standard DB-API nutzen

Einführung

Der Python-Standard für Datenbankschnittstellen ist die Python DB-API. Die meisten Python-Datenbankschnittstellen halten sich an diesen Standard.

Sie können die passende Datenbank für Ihre Anwendung wählen. Die Python Database API unterstützt eine breite Palette von Datenbankservern:

GadFly
mSQL
MySQL
PostgreSQL
Microsoft SQL Server 2000
Informix
Interbase
Oracle
Sybase

Eine Liste verfügbarer Python-Datenbankschnittstellen finden Sie unter „Python Database Interfaces and APIs“. Für jede Datenbank müssen Sie ein separates DB-API-Modul herunterladen. Für Oracle und MySQL benötigen Sie sowohl das Oracle- als auch das MySQL-Modul.

Die DB-API bietet einen minimalen Standard für die Arbeit mit Datenbanken unter Verwendung von Python-Strukturen und -Syntax. Diese API umfasst:

Importieren des API-Moduls.
Verbindung zur Datenbank herstellen.
SQL-Statements und Stored Procedures ausführen.
Verbindung schließen.

Wir lernen alle Konzepte anhand von MySQL, daher betrachten wir das MySQLdb-Modul.

Was ist MySQLdb?

MySQLdb ist eine Schnittstelle zur Verbindung mit einem MySQL-Datenbankserver aus Python. Es implementiert die Python Database API v2.0 und basiert auf der MySQL C API.

Installation von MySQLdb

Prüfen Sie, ob MySQLdb installiert ist:

#!/usr/bin/python
import MySQLdb

Falls folgende Fehlermeldung erscheint, ist das Modul nicht installiert:

Traceback (most recent call last):
  File "test.py", line 3, in 
    import MySQLdb
ImportError: No module named MySQLdb

Installation

Ubuntu: sudo apt-get install python-pip python-dev libmysqlclient-dev
Fedora: sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
Python Kommandozeile: pip install MySQL-python

Hinweis: Root-Rechte sind für die Installation erforderlich.

Datenbankverbindung

Voraussetzungen für die Verbindung zu einer MySQL-Datenbank:

Datenbank TESTDB wurde erstellt.
Tabelle EMPLOYEE in TESTDB wurde erstellt.
Felder: FIRST_NAME, LAST_NAME, AGE, SEX, INCOME.
Benutzer-ID testuser und Passwort test123 für TESTDB eingerichtet.
MySQLdb ordnungsgemäß installiert.
MySQL-Grundlagen verstanden (MySQL-Tutorial).

Beispiel: Verbindung herstellen

#!/usr/bin/python
import MySQLdb
# Datenbankverbindung öffnen
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
# Cursor-Objekt erstellen
cursor = db.cursor()
# SQL-Abfrage ausführen
cursor.execute("SELECT VERSION()")
# Einzelne Zeile abrufen
data = cursor.fetchone()
print("Database version : %s" % data)
# Verbindung trennen
db.close()

Ausgabe (Beispiel):

Database version : 5.0.45

Bei erfolgreicher Verbindung wird ein Connection Object zurückgegeben und in db gespeichert, andernfalls ist db None. Das db-Objekt erstellt ein cursor-Objekt für SQL-Queries. Am Ende wird die Verbindung geschlossen und Ressourcen freigegeben.

Datenbanktabelle erstellen

Nachher können wir Tabellen oder Datensätze mit der execute-Methode des Cursors erstellen.

Beispiel: Tabelle EMPLOYEE erstellen

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()
# Tabelle löschen, falls vorhanden
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Tabelle erstellen
sql = """CREATE TABLE EMPLOYEE (
    FIRST_NAME CHAR(20) NOT NULL,
    LAST_NAME CHAR(20),
    AGE INT,
    SEX CHAR(1),
    INCOME FLOAT
)"""
cursor.execute(sql)
db.close()

INSERT-Operation

Erforderlich, um Datensätze in eine Datenbanktabelle einzufügen.

Beispiel: Statisches INSERT

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()
sql = """INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Beispiel: Dynamisches INSERT

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d')" % \
       ('Mac', 'Mohan', 20, 'M', 2000)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Beispiel: Parameter direkt übergeben

user_id = "test123"
password = "password"
con.execute('insert into Login values("%s", "%s")' % (user_id, password))

READ-Operation

READ bedeutet, nützliche Informationen aus der Datenbank abzurufen.

Nach Verbindungsherstellung können Sie Abfragen durchführen. Verwenden Sie fetchone() für einzelne Datensätze oder fetchall() für mehrere.

fetchone() – Holt die nächste Zeile eines Abfrageergebnisses.
fetchall() – Holt alle Zeilen eines Ergebnis-Sets.
rowcount – Read-only-Attribut, Anzahl der von execute() betroffenen Zeilen.

Beispiel: Alle Datensätze mit INCOME > 1000

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()
sql = "SELECT * FROM EMPLOYEE WHERE INCOME > '%d'" % (1000)
try:
    cursor.execute(sql)
    results = cursor.fetchall()
    for row in results:
        fname, lname, age, sex, income = row[0], row[1], row[2], row[3], row[4]
        print("fname=%s,lname=%s,age=%d,sex=%s,income=%d" % (fname, lname, age, sex, income))
except:
    print("Error: unable to fetch data")
db.close()

Ausgabe:

fname=Mac,lname=Mohan,age=20,sex=M,income=2000

UPDATE-Operation

UPDATE ändert einen oder mehrere bestehende Datensätze.

Beispiel: Alle Datensätze mit SEX = 'M' aktualisieren – Alter aller Männer um ein Jahr erhöhen.

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

DELETE-Operation

DELETE entfernt Datensätze aus der Datenbank. Beispiel: Alle Datensätze aus EMPLOYEE löschen, wo AGE > 20.

#!/usr/bin/python
import MySQLdb
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB")
cursor = db.cursor()
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()
db.close()

Transaktionen durchführen

Transaktionen sichern Datenkonsistenz. Sie haben vier Eigenschaften (ACID):

Atomicity – Entweder vollständige Transaktion oder gar nichts.
Consistency – Transaktion startet und endet in konsistentem Zustand.
Isolation – Zwischenergebnisse sind außerhalb der Transaktion nicht sichtbar.
Durability – Nach Commit sind Effekte persistent, auch nach Systemausfall.

Python DB API 2.0 bietet commit() und rollback().

Beispiel

sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
    cursor.execute(sql)
    db.commit()
except:
    db.rollback()

COMMIT-Operation

commit() finalisiert Änderungen; danach können sie nicht mehr rückgängig gemacht werden.

db.commit()

ROLLBACK-Operation

rollback() macht Änderungen rückgängig, wenn Sie nicht zufrieden sind.

db.rollback()

Datenbankverbindung trennen

Verwenden Sie close().

db.close()

Wird die Verbindung per close() geschlossen, werden offene Transaktionen vom DB zurückgerollt. Besser ist es, commit oder rollback explizit aufzurufen.

Fehlerbehandlung

Fehlerquellen: Syntaxfehler in SQL, Verbindungsfehler, Aufruf von fetch für abgebrochenen Statement Handle.

Die DB-API definiert eine Reihe von Exceptions, die in jedem Datenbankmodul existieren müssen:

Nr.	Exception & Beschreibung
1	Warning – Nicht-fatale Probleme. Muss `StandardError` unterklassen.
2	Error – Basisklasse für Fehler. Muss `StandardError` unterklassen.
3	InterfaceError – Fehler im Datenbankmodul, nicht in der DB selbst. Muss `Error` unterklassen.
4	DatabaseError – Fehler in der Datenbank. Muss `Error` unterklassen.
5	DataError – Unterklasse von `DatabaseError` für Datenfehler.
6	OperationalError – Unterklasse von `DatabaseError` für Verbindungsverlust etc. Allgemein außerhalb der Kontrolle des Python-Skripts.
7	IntegrityError – Unterklasse von `DatabaseError` für relationale Integritätsverletzungen (Eindeutigkeit, Fremdschlüssel).
8	InternalError – Unterklasse von `DatabaseError` für interne Modulfehler (z. B. inaktiver Cursor).
9	ProgrammingError – Unterklasse von `DatabaseError` für Programmierfehler (schlechter Tabellenname etc.).
10	NotSupportedError – Unterklasse von `DatabaseError` für nicht unterstützte Funktionalität.

Ihre Python-Skripte sollten diese Fehler behandeln. Prüfen Sie vor Nutzung, ob MySQLdb die Exception unterstützt. Details in der DB API 2.0 Spezifikation.

Python Reguläre Ausdrücke: Praktischer Leitfaden Python Netzwerkprogrammierung: Sockets & erweiterte Protokolle meistern

Python