Datenbanken

Kurz gesagt: Eine Datenbank speichert Daten in Tabellen und findet sie mit einer SQL-Abfrage schnell; in Python sprichst du über sqlite3 mit ihr, und ?-Parameter schützen vor SQL-Injection.

Problem

Ein Katalog in einer Datei

Der Katalog des Shops ist eine CSV-Datei, wie im Modul über Dateien. Solange es hundert Produkte sind, geht das. Dann werden es hunderttausend, und es gibt Probleme:

• um ein Produkt zu finden, liest du die ganze Datei;
• „alles unter 5 €, nach Preis sortiert“ ist eine Schleife, ein if und eine Sortierung, alles von Hand geschrieben;
• zwei Kassen schreiben gleichzeitig in die Datei, und ein Eintrag geht verloren oder die Datei wird beschädigt;
• mitten beim Schreiben fällt der Strom aus, und die halbe Datei ist weg.

Deshalb liegen die Daten fast jedes echten Programms in einer Datenbank: einem Programm, dessen Aufgabe es ist, Daten aufzubewahren, sie schnell zu finden und nie zu verlieren.

Die Idee

Tabellen und SQL

Eine relationale Datenbank speichert Daten in Tabellen. Eine Tabelle hat Spalten (Name, Typ) und Zeilen (eine für jedes Produkt). Die Spalte id ist der Primärschlüssel: Ihr Wert ist in jeder Zeile anders, deshalb findet man mit ihm genau eine Zeile.

Mit der Datenbank sprichst du in SQL. Die fünf häufigsten Befehle:

SQL

CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    price DECIMAL(8, 2)
);

INSERT INTO products VALUES (1, 'Kaffee', 7.50);
SELECT name, price FROM products WHERE price < 5 ORDER BY price;
UPDATE products SET price = 6.90 WHERE id = 1;
DELETE FROM products WHERE id = 3;

• CREATE TABLE legt eine Tabelle an, INSERT fügt eine Zeile ein, UPDATE ändert Zeilen und DELETE löscht sie.
• SELECT liest: welche Spalten, aus welcher Tabelle, unter welcher Bedingung (WHERE) und in welcher Reihenfolge (ORDER BY).

Du sagst, was du willst, und die Datenbank entscheidet, wie sie es schnell findet.

Genauer, für Neugierige

• Bekannte relationale Datenbanken sind PostgreSQL, MySQL, Oracle und SQL Server. Sie laufen als eigene Server. Hier benutzen wir SQLite: eine kleine Datenbank, die in einer Datei oder ganz im Speicher des Programms lebt. Das Modul sqlite3 ist in der Standardbibliothek von Python. Die Adresse ":memory:" ist eine Datenbank im Speicher: Jeder Lauf beginnt mit einer leeren.
• Text steht in SQL in einfachen Anführungszeichen: 'Kaffee'. In PostgreSQL und MySQL ist DECIMAL(8, 2) eine exakte Zahl mit 2 Nachkommastellen, das passt zu Geld. SQLite speichert keine exakten Dezimalzahlen: Dort ist 7.50 eine gewöhnliche Gleitkommazahl.

Sieh es dir an

sqlite3: Verbindung, Abfrage, Ergebnis

Das Modul sqlite3 folgt dem Standard von Python für Datenbanken (DB-API, PEP 249): PostgreSQL und MySQL haben dieselben connect, execute, commit und close. Die Verbindung ist eine Connection, und das Ergebnis einer Abfrage ist ein Cursor. Die Funktion setup legt die Tabelle an und fügt drei Produkte ein:

def setup(conn):
    conn.execute("CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, price DECIMAL(8, 2))")
    conn.execute("INSERT INTO products VALUES (1, 'Kaffee', 7.50), (2, 'Tee', 3.20), (3, 'Schokolade', 2.40)")
    conn.commit()

Wir lesen die Zeilen und machen aus jeder ein Objekt der Dataclass Product mit den Feldern id, name und price:

@dataclass
class Product:
    id: int
    name: str
    price: float

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Ausgabe

Product(id=3, name='Schokolade', price=2.4)
Product(id=2, name='Tee', price=3.2)
Product(id=1, name='Kaffee', price=7.5)

• sqlite3.connect(address) öffnet die Verbindung. Die Adresse sagt, welche Datenbank: hier ":memory:", oder ein Dateiname wie "shop.db".
• conn.execute(sql) schickt einen beliebigen Befehl und gibt einen Cursor zurück. Eine for-Schleife über den Cursor gibt dir eine Zeile nach der anderen, jede als Tupel der Spalten in der Reihenfolge des SELECT. Hier wird das Tupel in drei Variablen entpackt.
• Vor INSERT, UPDATE und DELETE beginnt sqlite3 von selbst eine Transaktion, und conn.commit() speichert die Änderungen. Ohne das gehen Änderungen an einer Datenbank in einer Datei beim Schließen verloren.
• conn.close() schließt die Verbindung. (with conn: schließt sie nicht: Es macht eine Transaktion, wie du weiter unten siehst.)
• Fehler sind Exceptions von sqlite3.Error, zum Beispiel OperationalError für falsches SQL und IntegrityError für eine verletzte Regel der Tabelle. Python hat keine geprüften Exceptions.

conn = sqlite3.connect(":memory:")
setup(conn)
products = []
for product_id, name, price in conn.execute("SELECT id, name, price FROM products ORDER BY price"):
    products.append(Product(product_id, name, price))
for p in products:
    print(p)
conn.close()

Schritt für Schritt, in Worten

1. Eine Verbindung zur Datenbank im Speicher. setup legt die Tabelle products an und fügt 3 Zeilen ein.
2. Eine leere Liste, in die die Objekte kommen.
3. SELECT mit ORDER BY price: conn.execute gibt einen Cursor über die Zeilen zurück, die günstigste zuerst.
4. Die Schleife nimmt Zeile 1 als Tupel und entpackt sie in drei Variablen, und Product(...) macht daraus ein Objekt: Schokolade.
5. Die Schleife nimmt Zeile 2 als Tupel und entpackt sie in drei Variablen, und Product(...) macht daraus ein Objekt: Tee.
6. Die Schleife nimmt Zeile 3 als Tupel und entpackt sie in drei Variablen, und Product(...) macht daraus ein Objekt: Kaffee.
7. Der Cursor hat keine Zeilen mehr, und die Schleife hört auf.
8. Die Liste enthält gewöhnliche Objekte. Die Ausgabe zeigt die Dataclass mit ihren Feldern.

Genauer, für Neugierige

• Bei einer echten Datenbank auf einem Server ist die Adresse anders, und es gibt einen Benutzer und ein Passwort. Außerdem brauchst du den Treiber der Datenbank als Abhängigkeit im Projekt (für MySQL: das Paket mysql-connector-python):

import mysql.connector

conn = mysql.connector.connect(host="localhost", port=3306, database="shop", user="shop_app", password="geheim")
print("Mit der Datenbank verbunden!")
conn.close()

• Das Passwort steht nicht im Code. Es wird aus den Einstellungen oder aus einer Umgebungsvariable gelesen (os.environ).

• Die Preise sind hier float, damit es kurz bleibt. In einem echten Shop wird Geld exakt gespeichert: als DECIMAL in PostgreSQL oder MySQL (in Python kommt es als decimal.Decimal an) und in SQLite in ganzen Cent.
• Mit conn.row_factory = sqlite3.Row kannst du jede Zeile auch nach dem Namen der Spalte lesen: row["name"].

Probier es aus

Was wird ausgegeben?

Die Produkte sind Kaffee 7,50 €, Tee 3,20 € und Schokolade 2,40 €. Was gibt das Programm aus?

conn = sqlite3.connect(":memory:")
setup(conn)
for row in conn.execute("SELECT name FROM products WHERE price < 5 ORDER BY price DESC"):
    print(row[0])
conn.close()

Antwort

Tee
Schokolade

Kaffee
Tee
Schokolade

Schokolade
Tee

Prüfen

Problem

Die Suche, die alles zeigt

Die Website sucht ein Produkt nach dem Namen, den der Nutzer eintippt. Die Abfrage wird mit einem f-String zusammengesetzt:

def search(conn, name):
    sql = f"SELECT name, price FROM products WHERE name = '{name}'"
    for found, price in conn.execute(sql):
        print(f"  {found}: {price} €")

conn = sqlite3.connect(":memory:")
setup(conn)
print("Suche: Tee")
search(conn, "Tee")
print("Suche: x' OR '1'='1")
search(conn, "x' OR '1'='1")
conn.close()

Ausgabe

Suche: Tee
  Tee: 3.2 €
Suche: x' OR '1'='1
  Kaffee: 7.5 €
  Tee: 3.2 €
  Schokolade: 2.4 €

Mit „Tee“ funktioniert es. Mit x' OR '1'='1 wird die Abfrage zu ... WHERE name = 'x' OR '1'='1'. Die Bedingung ist immer wahr, und die ganze Tabelle kommt heraus. Die Eingabe des Nutzers ist Teil des SQL geworden. Das ist SQL-Injection, einer der häufigsten Angriffe auf Websites. Mit demselben Trick kann sich jemand ohne Passwort anmelden oder eine Tabelle löschen.

Die Idee

Parameter mit ?

Die Lösung ist, die Abfrage und die Werte getrennt zu schicken. Die Abfrage hat ? an den Stellen der Werte, und du übergibst sie als Tupel, das zweite Argument von execute. (name,) ist ein Tupel mit einem Wert: Das Komma ist Pflicht.

def search(conn, name):
    sql = "SELECT name, price FROM products WHERE name = ?"
    for found, price in conn.execute(sql, (name,)):
        print(f"  {found}: {price} €")

Ausgabe

Suche: Tee
  Tee: 3.2 €
Suche: x' OR '1'='1

Jetzt ist x' OR '1'='1 nur Text, der als Name gesucht wird. Ein solches Produkt gibt es nicht, und nichts kommt heraus. Was der Nutzer auch eintippt, es kann nicht zu SQL werden.

Die Regel ist einfach: Ein Wert vom Nutzer, aus einer Datei oder aus dem Netzwerk wird nie in SQL hineingeklebt. Immer ?.

Probier es aus

Kaffee mit Apostroph

Die Funktion benennt ein Produkt um. Mit dem Namen Kaffee 'Arabica' bricht das Programm mit sqlite3.OperationalError: near "Arabica": syntax error ab. In welcher Zeile ist der Fehler?

Wähle die Zeile mit dem Fehler.

1def rename(conn, product_id, new_name):2 conn.execute(f"UPDATE products SET name = '{new_name}' WHERE id = {product_id}")3 conn.commit()

Prüfen

Die Idee

Transaktion: alles oder nichts

Maria überweist 150 € an Jonas. Das sind zwei Abfragen: bei Jonas hinzufügen, bei Maria abziehen. Die Tabelle hat die Bedingung CHECK (balance >= 0), deshalb schlägt die zweite Abfrage fehl: Maria hat nur 100 €.

def open_accounts(conn):
    conn.execute("CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(50), balance INT CONSTRAINT no_debt CHECK (balance >= 0))")
    conn.execute("INSERT INTO accounts VALUES (1, 'Maria', 100), (2, 'Jonas', 50)")
    conn.commit()


def move(conn, account_id, amount):
    conn.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?", (amount, account_id))


def print_balances(conn):
    for owner, balance in conn.execute("SELECT owner, balance FROM accounts ORDER BY id"):
        print(f"  {owner}: {balance} €")

def transfer(conn, source, target, amount):
    move(conn, target, amount)
    conn.commit()
    move(conn, source, -amount)
    conn.commit()

conn = sqlite3.connect(":memory:")
open_accounts(conn)
try:
    transfer(conn, 1, 2, 150)
except sqlite3.Error:
    print("Die Überweisung von 150 € wurde abgelehnt.")
print_balances(conn)
conn.close()

Ausgabe

Die Überweisung von 150 € wurde abgelehnt.
  Maria: 100 €
  Jonas: 200 €

Die Überweisung wurde abgelehnt, aber Jonas hat schon 150 € aus dem Nichts bekommen: Jede Buchung wurde sofort mit commit() gespeichert. Die zwei Abfragen müssen eine Transaktion sein: Entweder gehen beide durch, oder keine. In Python macht das with conn:.

def transfer(conn, source, target, amount):
    with conn:
        move(conn, target, amount)
        move(conn, source, -amount)

Ausgabe

Die Überweisung von 150 € wurde abgelehnt.
  Maria: 100 €
  Jonas: 50 €

• Das erste UPDATE beginnt eine Transaktion: Die Änderungen sind noch nicht endgültig.
• Wenn der Block with conn: ohne Fehler endet, ruft er conn.commit() auf und speichert alles auf einmal.
• Wenn darin eine Exception auftritt, ruft er conn.rollback() auf und macht alles rückgängig. Die Exception geht weiter nach oben, damit auch der Aufrufer davon erfährt.
• open_accounts ruft am Ende commit() auf. Ohne das wäre das Anlegen der Konten in derselben Transaktion, und rollback() würde auch das rückgängig machen.

Genauer, für Neugierige

• Die Exception für die fehlgeschlagene Abfrage ist ein IntegrityError mit der Meldung CHECK constraint failed: no_debt. no_debt ist der Name der Bedingung: In der Tabelle steht balance INT CONSTRAINT no_debt CHECK (balance >= 0).

• Datenbanken versprechen bei Transaktionen vier Dinge, bekannt als ACID: alles oder nichts (Atomarität), die Daten bleiben nach den Regeln der Tabelle korrekt (Konsistenz), gleichzeitig laufende Transaktionen stören sich nicht gegenseitig (Isolation), und was gespeichert ist, geht nicht verloren, auch wenn der Strom ausfällt (Dauerhaftigkeit).

Die Idee

ORM: Objekte statt Zeilen

Mit sqlite3 schreibst du für jede Klasse SQL und machst die Zeilen von Hand zu Objekten. Ein ORM (objektrelationale Abbildung, object-relational mapping) nimmt dir das ab: Du beschreibst die Klasse, und die Bibliothek macht die Tabelle, die Abfragen und die Objekte selbst. Die Standardbibliothek von Python hat kein ORM. Das bekannteste ist SQLAlchemy (das Paket sqlalchemy), und Webprojekte mit Django benutzen das eingebaute ORM von Django.

models.py

from sqlalchemy import String, create_engine, select
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column

class Base(DeclarativeBase):
    pass


class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    price: Mapped[float]

• Eine Unterklasse von Base (aus DeclarativeBase) ist eine Tabelle, und __tablename__ gibt ihren Namen an.
• Jedes Feld mit dem Typ Mapped[...] ist eine Spalte. primary_key=True ist der Primärschlüssel: SQLAlchemy lässt die Datenbank ihm einen Wert geben.
• mapped_column legt Details der Spalte fest: hier String(50). Ein Feld nur mit Mapped[float] wird auch zu einer Spalte.
• Der Konstruktor wird für dich erzeugt: Product(name="Kaffee", price=7.50).

Speichern und Lesen gehen über die Session:

engine = create_engine("sqlite://")
Base.metadata.create_all(engine)

with Session(engine) as session:
    session.add(Product(name="Kaffee", price=7.50))
    session.add(Product(name="Tee", price=3.20))
    session.commit()

    cheap = session.scalars(select(Product).where(Product.price < 5).order_by(Product.price))
    for p in cheap:
        print(f"{p.name}: {p.price} €")

Ausgabe (mit installiertem SQLAlchemy)

Tee: 3.2 €

create_engine("sqlite://") sagt, welche Datenbank: hier SQLite im Speicher. create_all legt die Tabellen an. session.add fügt das Objekt hinzu, und commit() speichert es in einer Transaktion, wie bei sqlite3. Die Abfrage wird mit Code gebaut: select(Product).where(Product.price < 5) spricht von der Klasse und ihrem Feld price, nicht von der Tabelle. session.scalars gibt fertige Objekte zurück.

SQLAlchemy ist ein Paket, und die Prüfung der Website hat es nicht, deshalb läuft dieser Code hier nicht. Die Ausgabe stammt aus einem Lauf mit installiertem SQLAlchemy 2.1 (pip install sqlalchemy).

Genauer, für Neugierige

• Eine Abfrage mit select ist Code, deshalb lassen sich die Bedingungen leicht aus der Auswahl des Nutzers zusammensetzen. Eine Funktion, die nach dem Namen sucht:

def named(session, name):
    query = select(Product).where(Product.name == name)
    return session.scalars(query).all()

• Eine Transaktion, die bei einem Fehler rückgängig gemacht wird: session.begin() in einem with speichert am Ende alles und macht bei einer Exception alles rückgängig:

with Session(engine) as session, session.begin():
    session.add(Product(name="Schokolade", price=2.40))
    # weitere Änderungen: bei einer Exception wird alles rückgängig gemacht, sonst wird alles gespeichert

• Mit create_engine(address, echo=True) gibt SQLAlchemy das SQL aus, das es schickt. So siehst du, dass darunter weiterhin SQL steckt.
• In älterem Code siehst du session.query(Product).filter(...). Das ist der Stil von SQLAlchemy 1.x, und select(...) gibt es ab 2.0.

Übung

Ein Shop mit Datenbank

Löse die Aufgaben in deinem Editor, mit sqlite3 und der Datenbank ":memory:".1. Katalog. Lege eine Tabelle products an und die Funktionen add, set_price, remove und show (list ist der Name eines eingebauten Typs), alle außer show mit ?. Füge Kaffee (7,50 €), Tee (3,20 €) und Schokolade (2,40 €) hinzu, gib sie aus, ändere den Preis des Kaffees auf 6,90 €, lösche die Schokolade und gib sie wieder aus:

Ausgabe

1. Kaffee: 7.5 €
2. Tee: 3.2 €
3. Schokolade: 2.4 €
---
1. Kaffee: 6.9 €
2. Tee: 3.2 €

2. Suche nach einem Teil des Namens. Die Produkte sind Kaffee, Kakao, Tee und Éclair. Eine Funktion find(conn, text) findet alle Produkte, deren Name den Text enthält, ohne auf Groß- und Kleinschreibung zu achten. LOWER ändert in SQLite nur die Buchstaben A bis Z, deshalb bleibt É, wie es ist. Gib der Datenbank eine eigene Funktion: conn.create_function("lower_text", 1, str.lower), dann WHERE lower_text(name) LIKE ? mit dem Wert f"%{text.lower()}%". Probiere „KA“, „éclair“ und einen Injection-Versuch:

Ausgabe

Suche nach „KA“:
  Kaffee: 7.5 €
  Kakao: 4.8 €
Suche nach „éclair“:
  Éclair: 2.4 €
Suche nach „' OR '1'='1“:
  keine Produkte gefunden

3. Eine Überweisung, die mittendrin fehlschlägt. Die Konten sind Maria (100 €) und Jonas (50 €). transfer(conn, source, target, amount) zieht zuerst von source ab und fügt dann bei target hinzu. Wenn cursor.rowcount 0 ist (kein solches Konto), wirf ValueError(f"Kein Konto {account_id}"). Alles ist in with conn:. Überweise 30 € von 1 an 2, dann 40 € von 1 an das Konto 99, das es nicht gibt:

Ausgabe

Überwiesen: 30 € von 1 an 2
Abgelehnt: Kein Konto 99
  Maria: 70 €
  Jonas: 80 €

Die zweite Überweisung hat Maria 40 € abgezogen, bevor sie fehlschlug, aber with conn (mit rollback()) hat die 40 € zurückgebucht.

Test

Teste dich

1. Was gibt conn.execute("SELECT ...") zurück?

Die Anzahl der geänderten ZeilenEine Liste von Product-ObjektenEinen Cursor: die Zeilen des Ergebnisses, durch die du mit for gehst

2. Warum ist f"... WHERE name = '{name}'" gefährlich?

Weil der Text des Nutzers Teil des SQL wird und die Abfrage verändern kannEs ist nicht gefährlich, wenn name ein str istWeil es langsam ist

3. Eine Überweisung besteht aus zwei Abfragen in with conn:. Die erste gelingt, die zweite wirft eine Exception. Was macht with conn?

Es macht alle Änderungen seit dem Beginn der Transaktion rückgängig, auch die erste (rollback)Es speichert die erste und macht die zweite rückgängigEs wiederholt die zweite Abfrage

4. Was macht ein ORM wie SQLAlchemy?

Es macht aus Objekten Zeilen und umgekehrt, ohne dass du für alles SQL schreibstEs ersetzt die DatenbankEs macht nur SQL-Abfragen schneller

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Daten in Dateien · Listen: append, remove und sort · Lambdas · dataclass, match und Muster
• Geht weiter in: Injektionen: wenn Daten zum Befehl werden · Monolith, modularer Monolith, Services · Transactional Outbox · Eine Transaktion über Services hinweg (Saga)
• Aufgaben zum Thema: Programme in der echten Welt