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 derDELETE
-Syntax: DieWHERE
-Klausel gibt an, welche Datensätze gelöscht werden sollen. Wenn Sie dieWHERE
-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