6. Unterrichtsblock

Werte in Tabelle einfügen

Um eine Tabelle in MySQL zu füllen, verwenden wir die „INSERT INTO“-Anweisung.

Einfügen eines Eintrags in die Tabelle „Kunden“:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "INSERT INTO kunden (name, adresse) VALUES (%s, %s)"
wert = ("Markus", "Hof 21")
mycursor.execute(sql, wert)

mydb.commit()

print(mycursor.rowcount, "Eintrag gesetzt.")

Wichtig: Zu beachten ist die Anweisung: mydb.commit(). Diese ist notwendig, um Änderungen an der Tabelle vorzunehmen. Ohne diese Anweisung können keine Änderungen vorgenommen werden.

Mehrere Zeilen einfügen

Um mehrere Zeilen in eine Tabelle einzufügen, verwenden wir die executemany()Methode. Der zweite Parameter der executemany()Methode ist eine Liste von Tupeln, die die Daten enthält, die wir einfügen möchten:

In folgendem beispiel wird die Tabelle „Kunden“ mit Daten befüllt:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "INSERT INTO kunden (name, adresse) VALUES (%s, %s)"
wert = [
  ('Peter', 'Waldweg 15'),
  ('Manni', 'Hauptstrasse 100b'),
  ('Hannah', 'Hof 1'),
  ('Michael', 'Schillerstrasse 29'),
  ('Martin', 'Prinzregentenstrasse 2'),
  ('Gustl', 'Haus 1'),
  ('Richard', 'Feldweg 12'),
]

mycursor.executemany(sql, wert)

mydb.commit()

print(mycursor.rowcount, "Einträge gesetzt.")

ID abfragen

Die ID können wir nur abfragen, indem wir das cursor() Objekt verwenden.

Hinweis: Wenn wir mehr als eine Zeile in einer Tabelle haben, wir die letzte ID ausgegeben.

In folgendem Beispiel wird eine Zeile eingefügt und die ID abgefragt:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "INSERT INTO kunden (name, adresse) VALUES (%s, %s)"
wert = ("Michaela", "Rosenheim")
mycursor.execute(sql, wert)

mydb.commit()

print("1 Eintrag gesetzt, ID:", mycursor.lastrowid)

Werte aus einer Tabelle wählen

Um aus einer Tabelle in MySQL auszuwählen, verwenden wir die „SELECT“-Anweisung:

Folgendes Beispiel wählt alle Datensätze aus der „Kunden“-Tabelle und gibt diese aus:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM kunden")

ausgabe = mycursor.fetchall()

for x in ausgabe:
  print(x)

Hinweis: Wir verwenden die fetchall()Methode, um alle Zeilen aus der letzten Anweisung abzurufen.

Auswahl der Spalten

Um nur einige der Spalten in einer Tabelle auszuwählen, verwenden wir die „SELECT“-Anweisung gefolgt von der Spalte:

Folgendes Beispiel wählt nur die Namens- und Adressspalten aus:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT name, adresse FROM kunden")

meinergebnis = mycursor.fetchall()

for x in meinergebnis:
  print(x)

Verwendung der fetchone() Methode

Wenn wir nur an einer einzelnen Reihe interessiert sind, können wir die fetchone()Methode verwenden.

Die fetchone()Methode gibt die erste Reihe des Ergebnisses aus:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM kunden")

meinergebnis = mycursor.fetchone()

print(meinergebnis)

Ergebnisse Filtern

Wenn wir Datensätze aus einer Tabelle auswählen, können wir die Auswahl filtern, indem wir die „WHERE“-Ansage verwenden:

Wählt Datensätze, in denen die Adresse „Prinzregentenstraße 38“ ist:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM kunden WHERE adresse ='Prinzregentenstraße 38'"

mycursor.execute(sql)

meinergebnis = mycursor.fetchall()

for x in meinergebnis:
  print(x)

Wildcard Zeichen

Wir können auch die Datensätze auswählen, die mit einem angegebenen Buchstaben oder Satz beginnen, diesen enthalten oder damit enden.

Wir verwenden das Prozentzeichen %, um eine Wildcard darzustellen.

Im folgenden Beispiel werden Datensätze ausgewählt, in denen die Adresse das Wort „straße“ enthält:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM kunden WHERE adresse LIKE '%straße%'"

mycursor.execute(sql)

meinergebnis = mycursor.fetchall()

for x in meinergebnis:
  print(x)

SQL-Injektion verhindern

Wenn Abfragewerte vom Benutzer bereitgestellt werden, sollten wir diese Werte escapen. Dies soll SQL-Injektionen verhindern, was eine übliche Web-Hacking-Technik ist, um Datenbanken zu zerstören oder zu missbrauchen.

Das mysql.connector Modul hat Methoden, um Abfragewerte zu escapen.

Escapen von Abfragewerten mit der Platzhalter %s Methode:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM kunden WHERE adresse = %s"
adr = ("Prinzregentenstr. 63", )

mycursor.execute(sql, adr)

meinergebnis = mycursor.fetchall()

for x in meinergebnis:
  print(x)

Ergebnisse sortieren

Wir verwenden die ORDER BY-Anweisung, um das Ergebnis in auf- oder absteigenden Ordnung zu sortieren.

Das ORDER BY Schlüsselwort sortiert das Ergebnis standardmäßig aufsteigen. Um die Ergebnisse in absteigende Reihenfolge zu sortieren, verwenden wir das DESC-Schlüsselwort.

In folgendem Beispiel wird das Ergebnis alphabetisch nach Name sortiert:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM kunden ORDER BY name"

mycursor.execute(sql)

meinergebnis = mycursor.fetchall()

for x in meinergebnis:
  print(x)

Absteigende Sortierung

Um das Ergebnis in absteigende Reihenfolge zu sortieren, verwenden wir das DESC-Keyword.

Beispielhafte Sortierung des Ergebnisse in alphabetischer, absteigender Reihenfolge nach Namen:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM kunde ORDER BY name DESC"

mycursor.execute(sql)

meinergebnis = mycursor.fetchall()

for x in meinergebnis:
  print(x)

Löschen von Einträgen

Wir können Datensätze aus einer vorhandenen Tabelle löschen, indem wir die „DELETE FROM„-Anweisung verwenden:

Löschen von Datensätzen, in der die Adresse „Berg 21“ lautet:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "DELETE FROM kunden WHERE adresse = 'Berg 21'"

mycursor.execute(sql)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

Wichtig!: Zu beachten ist die Aussage: mydb.commit(). Es ist erforderlich, um Änderungen durchzuführen. Anderenfalls werden keine Änderungen an der Tabelle vorgenommen.

Zu beachten ist auch die WHERE-Klausel innerhalb der DELETE-Syntax: Die WHERE-Klausel gibt an, welche Datensätze gelöscht werden sollen. Wenn Sie die WHERE-Klausel ausgelassen wird, werden alle Datensätze gelöscht!

SQL-Injektion verhindern

Es gilt als gute Praxis, die Werte jeder Abfrage zu escapen, auch beim Löschen von Aussagen. Dies soll ebenfalls SQL-Injektionen verhindern. Das mysql.connector-Modul nutzt den Platzhalter %s um Werten im Löschen der Aussage zu escapen.

Beispielhaftes eycapen von Werten mit der Platzhalter %s Methode:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "DELETE FROM kunden WHERE adresse = %s"
adr = ("Prinzregentenstr. 63", )

mycursor.execute(sql, adr)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

Löschen von Tabellen

Wir können eine vorhandene Tabelle löschen, indem wir die Anweisung „DROP TABLE“ verwenden.

Folgendes Beispiel löscht die Tabelle „kunden“:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "DROP TABLE kunden"

mycursor.execute(sql)

Nur löschen, wenn vorhanden

Wenn die Tabelle, die wir löschen möchten, bereits gelöscht ist oder aus einem anderen Grund nicht vorhanden ist, können wir das Schlüsselwort IF EXISTS verwenden, um eine Fehlermeldung zu vermeiden.

Folgendes Beispiel löscht die Tabelle „kunden“, falls vorhanden:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="nutzername",
  password="meinpasswort",
  database="meinedatenbank"
)

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS kunden"

mycursor.execute(sql)

Aufgaben

  • Erstelle eine Datei, die die nötigen Funktionen über Module einbindet, die Du baust
  • Erstelle ein Modul das:
    • Eine Datenbank mit mehreren Tabellen erstellt
  • Befülle die Tabellen mit Inhalt
    • Versuche dabei Schleifen anzuwenden
  • Frage den Inhalt ab und erstelle hierfür ein Modul, welches de Code dafür enthält
    • Erstelle eine Funktion, die prüft, ob ein bestimmter Wert in der Datenbank enthalten ist
    • Die Eingabe der Abfrage soll über eine Input-Funktion erfolgen