6. Unterrichtsblock

Kursinhalte

Datensätze einfügen
Datensätze auswählen
Filtern mit WHERE
Sortieren und begrenzen
Ändern und löschen
Übungsaufgaben

Datensätze einfügen

In Block 5 hast du Tabellen angelegt. Jetzt kommen Daten hinein, und du holst sie gezielt wieder heraus. Die wichtigsten SQL-Befehle dieses Blocks im Überblick:

SQL-Befehl	macht
`INSERT INTO tabelle (spalten) VALUES (?, ?)`	neue Zeile einfügen
`SELECT spalten FROM tabelle`	Zeilen auswählen und anzeigen
`… WHERE bedingung`	nur passende Zeilen
`… ORDER BY spalte DESC LIMIT 3`	sortieren und begrenzen
`UPDATE tabelle SET spalte = ? WHERE …`	Werte ändern
`DELETE FROM tabelle WHERE …`	Zeilen löschen
`DROP TABLE IF EXISTS tabelle`	ganze Tabelle löschen

Eine Zeile einfügen

Für alle Beispiele verwenden wir eine Kundenkartei in kunden.db. Neue Zeilen fügst du mit INSERT INTO ein. Die Werte stehen nicht direkt im SQL-Befehl, sondern als Platzhalter ?. Python setzt sie sicher ein, die Werte übergibst du als Tupel:

import sqlite3
from pathlib import Path

ORDNER = Path(__file__).resolve().parent
verbindung = sqlite3.connect(ORDNER / "kunden.db")
cursor = verbindung.cursor()
cursor.execute("""
    CREATE TABLE IF NOT EXISTS kunden (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        name    TEXT NOT NULL,
        adresse TEXT,
        ort     TEXT,
        punkte  INTEGER DEFAULT 0
    )
""")

sql = "INSERT INTO kunden (name, adresse, ort, punkte) VALUES (?, ?, ?, ?)"
cursor.execute(sql, ("Peter", "Waldweg 15", "Rosenheim", 120))
verbindung.commit()
print("Neue Nummer:", cursor.lastrowid)

Ausgabe im Terminal

Neue Nummer: 1

Mehrere Zeilen auf einmal

executemany() fügt eine ganze Liste von Tupeln mit einem Befehl ein:

neue_kunden = [
    ("Manni", "Hauptstraße 100b", "Kolbermoor", 40),
    ("Hannah", "Hof 1", "Bad Aibling", 0),
    ("Michael", "Schillerstraße 29", "Rosenheim", 310),
    ("Martin", "Prinzregentenstraße 2", "Prien", 85),
    ("Gustl", "Haus 1", "Kolbermoor", 0),
    ("Richard", "Feldweg 12", "Rosenheim", 150),
]
cursor.executemany(sql, neue_kunden)
verbindung.commit()
print(cursor.rowcount, "Kunden eingefügt")

Ausgabe im Terminal

6 Kunden eingefügt

Merke: Ohne verbindung.commit() sind alle Änderungen beim Schließen wieder weg. Faustregel: nach jedem INSERT, UPDATE oder DELETE einmal commit().

Datensätze auswählen

Mit SELECT fragst du Daten ab. * heißt „alle Spalten“. Das Ergebnis holst du über den Cursor ab:

Methode / Eigenschaft	liefert
`cursor.fetchall()`	alle (restlichen) Ergebniszeilen als Liste von Tupeln
`cursor.fetchone()`	die nächste Ergebniszeile als Tupel, `None` wenn keine mehr da ist
`cursor.lastrowid`	die Nummer (`id`) der zuletzt eingefügten Zeile
`cursor.rowcount`	wie viele Zeilen der letzte `INSERT`, `UPDATE` oder `DELETE` betroffen hat

cursor.execute("SELECT * FROM kunden")
for zeile in cursor.fetchall():
    print(zeile)

Ausgabe im Terminal

(1, 'Peter', 'Waldweg 15', 'Rosenheim', 120)
(2, 'Manni', 'Hauptstraße 100b', 'Kolbermoor', 40)
(3, 'Hannah', 'Hof 1', 'Bad Aibling', 0)
(4, 'Michael', 'Schillerstraße 29', 'Rosenheim', 310)
(5, 'Martin', 'Prinzregentenstraße 2', 'Prien', 85)
(6, 'Gustl', 'Haus 1', 'Kolbermoor', 0)
(7, 'Richard', 'Feldweg 12', 'Rosenheim', 150)

Jede Zeile ist ein Tupel. Wählst du nur einzelne Spalten aus, kannst du sie in der Schleife direkt auf Variablen verteilen. COUNT(*) zählt die Zeilen, das Ergebnis ist eine Zeile mit einem einzigen Wert:

cursor.execute("SELECT name, ort FROM kunden")
print(cursor.fetchone())
print(cursor.fetchone())

cursor.execute("SELECT name, ort FROM kunden")
for name, ort in cursor.fetchall():
    print(f"{name:<10}{ort}")

cursor.execute("SELECT COUNT(*) FROM kunden")
print("Kunden insgesamt:", cursor.fetchone()[0])

Ausgabe im Terminal

('Peter', 'Rosenheim')
('Manni', 'Kolbermoor')
Peter     Rosenheim
Manni     Kolbermoor
Hannah    Bad Aibling
Michael   Rosenheim
Martin    Prien
Gustl     Kolbermoor
Richard   Rosenheim
Kunden insgesamt: 7

Merke: fetchone() und fetchall() holen ab, was der letzte execute() geliefert hat, und zwar nur einmal. Brauchst du das Ergebnis noch einmal, speichere es in einer Variablen oder frage erneut ab.

Aufgabe 1 – Doppelt gemoppelt

Zeit: ca. 8 Minuten

Was passiert, wenn ein Programm mit INSERT mehrmals läuft? Lege eine neue Datei block_06_sql_datensaetze.py an und schreibe unter den Kopf (siehe Übungsaufgaben) dieses Programm:

verbindung = sqlite3.connect(ORDNER / "freunde.db")
cursor = verbindung.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS freund (id INTEGER PRIMARY KEY, name TEXT)")
cursor.executemany("INSERT INTO freund (name) VALUES (?)", [("Lina",), ("Tom",), ("Ayşe",)])
verbindung.commit()

cursor.execute("SELECT COUNT(*) FROM freund")
print(cursor.fetchone()[0])
verbindung.close()

Sage voraus, welche Zahl beim ersten und welche beim zweiten Start ausgegeben wird. Prüfe es.
Warum verhindert der Primärschlüssel id die doppelten Namen nicht? Antworte als Kommentar.
Ändere das Programm so, dass die drei Freunde nur eingefügt werden, wenn die Tabelle noch leer ist. Kontrolle: Auch nach fünf Starts steht dort 3.

Filtern mit WHERE

Mit WHERE wählst du nur die Zeilen aus, die eine Bedingung erfüllen:

Bedingung	Bedeutung
`=` `!=` `<` `>` `<=` `>=`	vergleichen (Texte in einfachen Anführungszeichen, Groß-/Kleinschreibung zählt)
`AND` `OR` `NOT`	Bedingungen verknüpfen
`LIKE 'M%'`	Muster: `%` steht für beliebig viele Zeichen, `_` für genau ein Zeichen
`IS NULL`	Feld ist leer

cursor.execute("SELECT name, punkte FROM kunden WHERE ort = ?", ("Rosenheim",))
print(cursor.fetchall())

cursor.execute("SELECT name FROM kunden WHERE punkte >= ? AND ort != ?", (80, "Rosenheim"))
print(cursor.fetchall())

cursor.execute("SELECT name, adresse FROM kunden WHERE adresse LIKE ?", ("%straße%",))
print(cursor.fetchall())

Ausgabe im Terminal

[('Peter', 120), ('Michael', 310), ('Richard', 150)]
[('Martin',)]
[('Manni', 'Hauptstraße 100b'), ('Michael', 'Schillerstraße 29'), ('Martin', 'Prinzregentenstraße 2')]

Achtung beim Tupel mit nur einem Wert: ("Rosenheim",) braucht das Komma, sonst ist es kein Tupel.

Warum immer ? statt f-String?

Stammt ein Wert vom Benutzer, kann er SQL-Befehle einschleusen (SQL-Injection). Hier gibt jemand statt eines Orts einen präparierten Text ein:

eingabe = "x' OR '1'='1"

cursor.execute(f"SELECT name FROM kunden WHERE ort = '{eingabe}'")
print("mit f-String:", len(cursor.fetchall()), "Treffer")

cursor.execute("SELECT name FROM kunden WHERE ort = ?", (eingabe,))
print("mit ?:", len(cursor.fetchall()), "Treffer")

Ausgabe im Terminal

mit f-String: 7 Treffer
mit ?: 0 Treffer

Mit dem f-String wird aus der Eingabe ein Teil des Befehls: WHERE ort = 'x' OR '1'='1' ist immer wahr, also kommen alle Kunden heraus. Mit ? bleibt die Eingabe ein ganz normaler Text, den es als Ort nicht gibt.

Merke: Werte gehören nie per f-String oder + in einen SQL-Befehl, sondern immer über Platzhalter ?. Bei MySQL heißt der Platzhalter %s, das Prinzip ist dasselbe.

Aufgabe 2 – Die Kundensuche

Zeit: ca. 8 Minuten

Erst denken, dann prüfen. Lege die Kundentabelle mit kunden_neu_anlegen() aus dem Kopf an (siehe Übungsaufgaben) und sieh dir die sieben Kunden an. Wie viele Kunden findet jede dieser Bedingungen?

abfragen = [
    ("name LIKE ?", "M%"),
    ("name LIKE ?", "%A%"),
    ("adresse LIKE ?", "%straße%"),
    ("ort = ?", "rosenheim"),
]

Schreibe für jede der vier Bedingungen deine Vorhersage als Kommentar dazu.
Prüfe sie mit einer Schleife: cursor.execute(f"SELECT name FROM kunden WHERE {bedingung}", (wert,)). Die Bedingung stammt von dir, der Wert kommt über den Platzhalter ?. Gib die Namen und die Anzahl aus.
Zwei Ergebnisse sind überraschend. Erkläre sie als Kommentar.

Kontrolle: 3, 5, 3 und 0 Treffer.

Sortieren und begrenzen

ORDER BY sortiert das Ergebnis, standardmäßig aufsteigend (ASC), mit DESC absteigend. Mehrere Spalten trennst du mit Komma: Erst wird nach der ersten sortiert, bei Gleichstand nach der zweiten. LIMIT begrenzt die Anzahl der Zeilen:

cursor.execute("SELECT name, punkte FROM kunden ORDER BY punkte DESC LIMIT 3")
print(cursor.fetchall())

cursor.execute("SELECT ort, name FROM kunden ORDER BY ort, name")
for ort, name in cursor.fetchall():
    print(f"{ort:<13}{name}")

Ausgabe im Terminal

[('Michael', 310), ('Richard', 150), ('Peter', 120)]
Bad Aibling  Hannah
Kolbermoor   Gustl
Kolbermoor   Manni
Prien        Martin
Rosenheim    Michael
Rosenheim    Peter
Rosenheim    Richard

Ändern und löschen

UPDATE ändert Werte in vorhandenen Zeilen, DELETE löscht Zeilen. cursor.rowcount verrät, wie viele Zeilen betroffen waren:

cursor.execute("UPDATE kunden SET punkte = punkte + ? WHERE name = ?", (50, "Hannah"))
print(cursor.rowcount, "Zeile geändert")

cursor.execute("DELETE FROM kunden WHERE punkte = ?", (0,))
print(cursor.rowcount, "Zeile gelöscht")
verbindung.commit()

cursor.execute("SELECT COUNT(*) FROM kunden")
print("Kunden übrig:", cursor.fetchone()[0])

Ausgabe im Terminal

1 Zeile geändert
1 Zeile gelöscht
Kunden übrig: 6

Hannah hatte 0 Punkte und bekam vorher 50 dazu, deshalb wurde nur Gustl gelöscht. Eine ganze Tabelle samt Inhalt löschst du mit DROP TABLE. IF EXISTS verhindert eine Fehlermeldung, falls es sie nicht gibt:

cursor.execute("DROP TABLE IF EXISTS alte_kunden")
verbindung.commit()
verbindung.close()

Merke: Vergisst du bei UPDATE oder DELETE das WHERE, betrifft der Befehl alle Zeilen der Tabelle, ohne Rückfrage. Prüfe die Bedingung vorher mit einem SELECT.

Übungsaufgaben

Die Aufgaben 3 bis 7 sind die Kernaufgaben, wer schneller ist, macht mit den Bonusaufgaben weiter. Schreibe alle Aufgaben in deine Datei block_06_sql_datensaetze.py aus Aufgabe 1. Sie beginnt mit diesem Kopf. Die Funktion kunden_neu_anlegen() legt die Kundenkartei mit den sieben Kunden aus den Beispielen frisch an, so beginnt jede Aufgabe mit denselben Daten:

import sqlite3
from pathlib import Path

ORDNER = Path(__file__).resolve().parent
DATENBANK = ORDNER / "kunden.db"

KUNDEN = [
    ("Peter", "Waldweg 15", "Rosenheim", 120),
    ("Manni", "Hauptstraße 100b", "Kolbermoor", 40),
    ("Hannah", "Hof 1", "Bad Aibling", 0),
    ("Michael", "Schillerstraße 29", "Rosenheim", 310),
    ("Martin", "Prinzregentenstraße 2", "Prien", 85),
    ("Gustl", "Haus 1", "Kolbermoor", 0),
    ("Richard", "Feldweg 12", "Rosenheim", 150),
]


def kunden_neu_anlegen():
    """Legt die Tabelle kunden mit den sieben Startkunden neu an (alte Daten werden gelöscht)."""
    verbindung = sqlite3.connect(DATENBANK)
    cursor = verbindung.cursor()
    cursor.execute("DROP TABLE IF EXISTS kunden")
    cursor.execute("""
        CREATE TABLE kunden (
            id      INTEGER PRIMARY KEY AUTOINCREMENT,
            name    TEXT NOT NULL,
            adresse TEXT,
            ort     TEXT,
            punkte  INTEGER DEFAULT 0
        )
    """)
    cursor.executemany("INSERT INTO kunden (name, adresse, ort, punkte) VALUES (?, ?, ?, ?)", KUNDEN)
    verbindung.commit()
    verbindung.close()

Aufgabe 3 – Die Bestenliste

Zeit: ca. 10 Minuten

Ein Quiz-Spiel speichert seine Ergebnisse in einer eigenen Datenbank quiz.db.

ergebnisse = [("Lina", 870), ("Tom", 640), ("Ayşe", 990), ("Ben", 450), ("Mia", 870), ("Lina", 920)]

Lege in quiz.db eine Tabelle ergebnis an (Nummer, Spieler, Punkte; Spieler und Punkte sind Pflicht). Leere sie zu Beginn mit DELETE FROM ergebnis, damit jeder Start gleich beginnt.
Füge die Ergebnisse mit executemany() ein.
Gib die Top 3 als Rangliste aus, z. B. 1. Ayşe 990. Bei gleicher Punktzahl wird alphabetisch sortiert.
Gib alle Ergebnisse von Lina aus und berechne in Python ihren Durchschnitt. Kontrolle: 895.00

Kontrolle Top 3: Ayşe 990, Lina 920, Lina 870.

Aufgabe 4 – Kundensuche mit Eingabe

Zeit: ca. 8 Minuten

Der Außendienst will wissen, welche Kunden in einem Ort wohnen. Starte mit frischen Daten (kunden_neu_anlegen()).

Frage nach einem Ort und gib alle Kunden dieses Orts mit Adresse aus, alphabetisch nach Namen sortiert. Den Ort setzt du mit dem Platzhalter ? ein.
Wird niemand gefunden, erscheint „Keine Kunden in …“.
Groß- und Kleinschreibung soll egal sein. Tipp: WHERE LOWER(ort) = LOWER(?). Kontrolle: „rosenheim“ findet Michael, Peter und Richard.

Zusatz: Auch der Anfang des Orts soll genügen: „kol“ findet die Kunden aus Kolbermoor. Tipp: LIKE mit dem Wert eingabe + "%".

Aufgabe 5 – Bonuspunkte vergeben

Zeit: ca. 8 Minuten

Kunden sammeln Treuepunkte. Ein kleines Programm soll Punkte gutschreiben. Starte mit frischen Daten (kunden_neu_anlegen()).

Frage nach einem Namen und nach der Anzahl Bonuspunkte.
Lehne negative Punkte mit einer Meldung ab.
Erhöhe die Punkte mit UPDATE kunden SET punkte = punkte + ? WHERE name = ?. Ist cursor.rowcount 0, gib „Kunde … nicht gefunden.“ aus, sonst den neuen Punktestand.

Kontrolle: Hannah mit 50 Bonuspunkten hat danach 50 Punkte, „Otto“ wird nicht gefunden.

Aufgabe 6 – Aufräumen

Zeit: ca. 8 Minuten

Kunden ohne einen einzigen Treuepunkt sollen aus der Kartei. Starte mit frischen Daten (kunden_neu_anlegen()).

Gib alle Kunden mit 0 Punkten aus. Kontrolle: Hannah und Gustl.
Frage nach: „Diese 2 Kunden löschen? (ja/nein)“. Die Zahl kommt aus deiner Abfrage.
Bei „ja“ löschst du sie mit DELETE … WHERE, speicherst mit commit() und gibst aus, wie viele gelöscht wurden und wie viele Kunden übrig sind. Kontrolle: 2 gelöscht, 5 übrig.
Denkfrage als Kommentar: Was würde DELETE FROM kunden ohne WHERE tun?

Aufgabe 7 – Das Kunden-Menü

Zeit: ca. 15 Minuten

Zum Schluss baust du ein kleines Verwaltungsprogramm für die Kundenkartei.

Zeige in einer Schleife ein Menü: 1 alle anzeigen, 2 Kunde hinzufügen, 3 suchen, 4 löschen, 0 Ende.
Schreibe für jeden Menüpunkt eine eigene Funktion, die den Cursor bekommt.
Beim Hinzufügen ist der Name Pflicht, die Punkte beginnen bei 0 (Standardwert der Tabelle). Gib die neue Nummer aus (cursor.lastrowid).
Beim Löschen fragst du nach der Nummer, zeigst den Namen an und fragst nach, bevor gelöscht wird.

Zusatz: Menüpunkt 5: die Top 3 nach Punkten.

Bonusaufgabe 1 – Import aus einer Textdatei

Neue Kunden kommen als Textdatei aus einem anderen Programm. Verbinde Block 4 und Block 6. Starte mit frischen Daten (kunden_neu_anlegen()).

neue_kunden_text = """Sofia;Bahnhofstraße 3;Rosenheim;60
Jonas;Am Bach 7;Prien;
kaputte Zeile
Lea;Ringstraße 12;Kolbermoor;95
"""

Schreibe den Text in die Datei neue_kunden.txt.
Lies die Datei Zeile für Zeile und trenne die Felder mit split(";"). Fehlen die Punkte, nimmst du 0. Zeilen mit falscher Feldanzahl überspringst du mit einer Meldung.
Füge alle gültigen Kunden mit einem einzigen executemany() ein und gib aus, wie viele es waren. Kontrolle: 3 eingefügt, 1 übersprungen, danach 10 Kunden.

Bonusaufgabe 2 – Der Angriff

Warum man Werte nie mit einem f-String in SQL einbaut, siehst du am besten an einem Angriff auf einen Login.

Lege in login.db eine Tabelle benutzer (name, passwort) mit zwei Benutzern an: lina / geheim123 und admin / sehrgeheim.
Schreibe eine absichtlich unsichere Funktion login_unsicher(name, passwort), die die Abfrage mit einem f-String baut: … WHERE name = '{name}' AND passwort = '{passwort}'. Sie gibt True zurück, wenn ein Benutzer gefunden wurde.
Teste mit dem Namen admin' -- und einem falschen Passwort. Was passiert? Erkläre als Kommentar, was -- in SQL bewirkt.
Schreibe login_sicher(name, passwort) mit Platzhaltern ? und zeige, dass derselbe Angriff scheitert.

Hinweis: Echte Programme speichern Passwörter nie im Klartext, sondern nur als Hashwert. Hier geht es nur um den Angriff.

Kurz zusammengefasst:

Einfügen: INSERT INTO kunden (name, ort) VALUES (?, ?) mit execute() oder executemany(), danach commit().
Abfragen: SELECT spalten FROM tabelle, Ergebnis mit fetchall() (Liste von Tupeln) oder fetchone() abholen. COUNT(*) zählt.
Filtern mit WHERE (=, <, AND, LIKE 'M%'), sortieren mit ORDER BY … DESC, begrenzen mit LIMIT.
Ändern mit UPDATE … SET … WHERE, löschen mit DELETE FROM … WHERE. Ohne WHERE trifft es alle Zeilen.
Werte immer über Platzhalter ? übergeben, nie per f-String: Das schützt vor SQL-Injection.