Datenbankprogrammierung mit Python

Datenbankprogrammierung mit Python
©Ralf Adams
Version vom: 02.03.2017
Inhaltsverzeichnis
1 setup-Programm
1.1 Erstellen des ER-Modells . . . . .
1.2 Verbindung auf- und abbauen . . .
1.3 Datenbanken erstellen und löschen
1.4 Tabellen anlegen . . . . . . . . . .
1.5 Zwischenbericht . . . . . . . . . .
1.6 Tabellen löschen . . . . . . . . . .
1.7 Tabellen ändern . . . . . . . . . .
Datenbankprogrammierung mit Python
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2
2
4
7
12
18
19
20
1
1 setup-Programm
1 setup-Programm
1 setup-Programm
Der Deutsche Fußballbund (DFB)1 möchte Spielergebnisse, Trainerverpflichtungen und Titelgewinne Online darstellen. Dazu soll eine relationale Datenbank verwendet werden. Sie sind Teil
des Projektteams und für die Planung, Programmierung und den Test der Datenhaltungsschicht
verantwortlich.
Ihr erster Teilauftrag besteht darin, ein setup-Programm in Python zu erstellen, welches die Datenbank, die Tabellen und die Inhalte anlegt. Ebenso sollen die CSV-Dateien importiert werden. Zum
Abschluss sollen zwei SQL-User angelegt werden: bundesliga_root und bundesliga_user.
1.1 Erstellen des ER-Modells
Zunächst müssen wir über Reverse Engineering die vorhanden Daten analysieren und die Struktur in
Form eines ER-Modells ermitteln. Dieses ER-Modell ist dann später die Basis für die entsprechenden
SQL-Befehle zum Anlegen der Tabellen.
Mit Hilfe des Arbeitsblattes DAB.2.1 soll im Unterricht die entsprechende Analyse betrieben werden. Die Hausaufgabe dient der Eigenkontrolle, ob Sie das Vorgehen auch an einem anderen Beispiel
wiederholen können.
Das ER-Modell sollte ungefähr dem in Abbildung 1 entsprechen. Hier Auszüge aus den CSV-Dateien:
• vereine.csv
1
2
3
4
5
1;Meidericher SV 02 Duisburg;MSV Duisburg;17.09.1902;Blau
,→
Weiß;http://www.msv-duisburg.de
2;FC Gelsenkirchen-Schalke 04;FC Schalke 04;04.05.1904;Blau
,→
Weiß;http://www.schalke04.de
3;FC Hansa Rostock;Hansa Rostock;01.11.1954;Weiß
,→
Blau;http://www.fc-hansa-rostock.de
4;SV Werder Bremen;Werder Bremen;04.02.1899;Grün Weiß;http://www.werder-online.de
5;TSV München von 1860;1860 München;17.05.1860;Weiß Blau;http://www.tsv1860.de
• trainer.csv
1
2
3
4
5
1;Ilia;Gruev;Bulgarien;1969-10-30
2;Gino;Lettieri;Italien;1966-12-23
3;Karsten;Baumann;Deutschland;1969-10-14
4;Kosta;Runjaic;Deutschland;1971-06-04
5;Ivica;Grlić;Bosnien-Herzegowina;1975-08-06
• trainer_verein.csv
1
2
3
4
5
1
1;1;2015-11-03;2017-06-30
1;2;2014-07-01;2015-11-01
1;3;2013-07-01;2014-06-14
1;4;2012-09-03;2013-06-30
1;5;2012-08-27;2012-08-31
Nicht wirklich ;-)
Datenbankprogrammierung mit Python
2
reverse
engineering
DAB.2.1
1 setup-Programm
1.1 Erstellen des ER-Modells
• titel.csv
1
2
3
4
5
27;Deutscher Meister;1902/03
27;Deutscher Meister;1905/06
29;Berliner Meister;1905/06
26;Süddeutscher Meister;1907/08
41;Norddeutscher Meister;1907/08
• paarungen.csv
1
2
3
4
5
5;41;1963-08-24 17:00:00.000;1963/64;1;1;1;1;0
104;12;1963-08-24 17:00:00.000;1963/64;1;1;1;0;0
106;16;1963-08-24 17:00:00.000;1963/64;1;0;2;0;2
6;1;1963-08-24 17:00:00.000;1963/64;1;1;4;0;3
32;37;1963-08-24 17:00:00.000;1963/64;1;1;1;1;1
Abbildung 1: ER-Modell der Bundesliga-Datenbank
Datenbankprogrammierung mit Python
3
1 setup-Programm
1.2 Verbindung auf- und abbauen
1.2 Verbindung auf- und abbauen
Beginnen wir damit, eine Verbindung zum Server herzustellen. Zunächst tun wir dies auf eine sehr
einfache Art und Weise.
1
import mysql.connector
# MySql-API einbinden
verbindung = mysql.connector.connect(user="root",
password="",
host="127.0.0.1",
database="test")
verbindung.close()
# Verbindungsparameter festlegen
DAB.2.2
2
3
4
5
6
7
# Schließen nicht vergessen
In Zeile 1 wird die MySql-API, genauer die connector-Klasse importiert. In Zeile 3 wird eine
Verbindung hergestellt. Die Verbindung wird in der Objektvariablen verbindung abgelegt. Beim
Aufruf der Methoden connect() werden die Verbindungsparameter festgelegt. Die Angaben können
natürlich auf Ihrem Server variieren. Wichtig ist das Schließen der Verbindung in Zeile 7. Ohne
dieses Schließen würde die Verbindung offen bleiben und damit alle Ressourcen weiter binden, bis
der Server einen timeout erzeugt.
connect()
timeout
Aufgabe 1.1 Finden Sie heraus, was ein timeout ist und wie hoch er auf Ihrem Server eingestellt
ist.
Was dieser Lösung fehlt, ist eine Fehlerbehandlung. Wenn beispielsweise die Verbindungsparameter
falsch sind oder der Server nicht gestartet wurde, soll das Programm nicht einfach abbrechen,
sondern eine vernünftige Fehlermeldung ausgeben.
Aufgabe 1.2 Recherchieren Sie:
• Welche Bedeutung hat die try-/except-/finally-Umgebung?
• Welche Fehlerkodes stellt mysql.connector.Error zur Verfügung?
1
2
import mysql.connector
from mysql.connector import errorcode
# Errorcodes importieren
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
try:
# Versuche Verbindungsaufbau
verbindung = mysql.connector.connect(user="root",
password="",
host="128.0.0.1",
database="bundesliga")
except mysql.connector.Error as err:
# Abfangen eines Fehlers
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
# Zugriff verweigert
print("Benutzername oder Passwort falsch.")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
# DB unbekannt
print("Datenbank nicht vorhanden.")
else:
# Alle anderen Fehler
print(err)
else:
verbindung.close()
Datenbankprogrammierung mit Python
4
exception
1 setup-Programm
1.2 Verbindung auf- und abbauen
In Zeile 2 werden die Codes für die Fehlermeldungen importiert. Mit diesen können gezielt bestimmte Fehlerursachen abgefragt werden. Nun wird der Verbindungsaufbau nicht mehr einfach
so ausgeführt, sondern in einem try-Block (siehe Zeile 4). Wirft der Server eine Exception, wird
diese in Zeile 9 aufgefangen und in den Zeilen 10 und 12 werden mögliche Fehlerursachen abgefragt. Falls keine dieser Ursachen zutrifft, wird nach Zeile 14 die Fehlermeldung des Servers
ausgegeben.
Aufgabe 1.3 Provozieren Sie die Fehlmeldungen wie folgt:
• Ändern Sie das Passwort so, dass es falsch ist.
• Ändern Sie den Datenbanknamen so, dass er eine nicht existierende angibt.
• Ändern Sie den Host so, dass er auf eine IP-Adresse ohne SQL-Server verweist.
Natürlich haben Sie daran gedacht, die vorherige Fehlerquelle wieder zu beheben, bevor Sie die
neue ausprobieren ;-)
Etwas unschön ist, dass die Übergabeparameter beim Aufruf der connect()-Methode festgelegt werden. Spätestens, wenn man mehrmals eine Verbindung aufbauen möchte, will man die
Parameter nicht jedesmal festlegen. Es bietet sich an, die Daten in einem dictionary abzulegen.
1
2
import mysql.connector
from mysql.connector import errorcode
3
4
5
6
7
8
9
verbindung_parameter = dict(
user="root",
password="",
host="127.0.0.1",
database="bundesliga")
# Verbindungsparameter
10
11
12
13
14
15
16
17
18
19
20
21
try:
verbindung = mysql.connector.connect(**verbindung_parameter) # dictionary übergeben
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Benutzername oder Passwort falsch.")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Datenbank nicht vorhanden.")
else:
print(err)
else:
verbindung.close()
In Zeile 5 wird das dictionary verbindung_parameter mit den Verbindungsparametern erstellt.
Das Wort vor dem Gleichheitszeichen ist der Schlüssel und das Wort dahinter der Wert. So kann
der Wert bequem über einen sprechenden Namen abgerufen werden. Alternativ zur Initialisierung
mit dem Konstruktor wäre auch folgende Schreibweise denkbar:
Datenbankprogrammierung mit Python
5
dictionary
1 setup-Programm
5
6
7
8
9
1.2 Verbindung auf- und abbauen
conn_parameter = {
"user": "root",
"password": "myroot",
"host": "127.0.0.1",
"database": "bundesliga"}
In Zeile 12 wird die Verbindung nun unter Verwendung des dictionaries (keyword argument unpacking) aufgerufen.
keyword
argument
unpacking
Aufgabe 1.4 Recherchieren Sie: Was bedeutet keyword argument unpacking?
Noch besser wäre eine externe Konfigurationsdatei, in welcher man die Verbindungsparameter ablegen kann. So muss bei geänderten Verbindungsparameter nicht der Quelltext neu übersetzt werden.
Legen Sie eine Datei mit dem Namen settings.cfg im gleichen Verzeichnis an und fügen Sie
folgenden Inhalt ein:
1
2
3
4
5
[VERBINDUNGSPARAMETER]
user = root
password =
host = 127.0.0.1
database = bundesliga
Unterhalb einer in eckigen Klammern benannten Sektion können die Verbindungsdaten abgespeichert werden. Der Name der Sektion wird üblicherweise in Großbuchstaben geschrieben.
1
2
3
import mysql.connector
from mysql.connector import errorcode
import configparser
# Importieren des Moduls
4
5
6
7
config = configparser.ConfigParser()
config.read("settings01.cfg")
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
# Anlegen eines Objekts
# Auslesen der Sektion
8
9
10
11
12
13
14
15
16
17
18
19
try:
verbindung = mysql.connector.connect(**verbindung_parameter)
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Benutzername oder Passwort falsch.")
elif err.errno == errorcode.ER_BAD_DB_ERROR:
print("Datenbank nicht vorhanden.")
else:
print(err)
else:
verbindung.close()
Zeile 3 importiert das benötigte Modul und in Zeile 5 wird ein ConfigParser-Objekt angelegt.
In der nachfolgenden Zeile wird die angegebene Datei eingelesen. In Zeile 7 wird der Inhalt der
Sektion als dictionary zur Verfügung gestellt und in verbindung_parameter abgespeichert. Ab
da ist alles wie gehabt.
Datenbankprogrammierung mit Python
6
configparser
1 setup-Programm
1.3 Datenbanken erstellen und löschen
1.3 Datenbanken erstellen und löschen
Sie sollen eine Funktionsbibliothek aufbauen, welche Funktionen zum Anlegen und Löschen von
Datenbanken enthält. Diese Funktionen sollen in einer Klasse gekapselt werden. Die Klasse soll
alle wichtigen Informationen als Eigenschaften verwalten. In Abbildung 2 ist der erste Entwurf des
entsprechenden Klassendiagramms zu sehen.
DAB.2.3
DatenbankTools
+ konfigurationsdatei : string = "settings.cfg"
+ dbname : string = "datenbank_test"
+ zeichensatz : string = "utf8"
+ sortierung : string = "utf8_general_ci"
+ anlegen(): bool
Abbildung 2: Klassendiagramm DatenbankTools, Version 1
• Die Eigenschaft konfigurationsdatei enthält den Namen (und ggf. auch den Pfad) der
Konfigurationsdatei. Die Vorbelegung ist settings.cfg.
• Die Eigenschaft dbname enthält den Namen der anzulegenden Datenbank. Die Vorbelegung
ist datenbank_test.
• Die Eigenschaft zeichensatz enthält den Namen des Zeichensatzes, der als Vorbelegung
bei allen zeichenbasierenden Datentypen verwendet wird. Die Vorbelegung ist utf8.
Zeichensatz
character
set
• Die Eigenschaft sortierung enthält den Namen der Sortierung des Zeichensatzes. Die Vorbelegung ist utf8_general_ci.
Sortierung
collation
Aufgabe 1.5 Recherchieren Sie:
1. Was sind Zeichensätze und welche sind für deutschsprachige Anwendungen interessant?
2. Was bedeuten die Sortierungen und welche sind für deutschsprachige Anwendungen interessant?
3. Welche zwei Sortiernormen sind in Deutschland gültig?
Die Umsetzung beginnt damit, dass wir zuerst eine neue Konfigurationsdatei settings02.cfg,
welche keinen Datenbanknamen enthält, anlegen.
1
2
3
4
[VERBINDUNGSPARAMETER]
user = root
password =
host = 127.0.0.1
Die Datei sqltools.py braucht nur zwei Module:
Datenbankprogrammierung mit Python
7
1 setup-Programm
1
2
1.3 Datenbanken erstellen und löschen
import mysql.connector
import configparser
Im Konstruktor werden die Eigenschaften (Objektvariablen, Attribute) der Klasse mit den Startwerten versehen. Meine Konfigurationsdatei heißt nur etwas anders, weil ich nicht mit meinen anderen
durcheinander kommen möchte.
5
6
7
8
9
10
11
12
###################################################
class DatenbankTools:
def __init__(self):
# Instanzvariablen vorgeben
self.konfigurationsdatei = "settings02.cfg"
self.dbname = "datenbank_test"
self.zeichensatz = "utf8"
self.sortierung = "utf8_general_ci"
Nun wird die Methode anlegen() implementiert. Zuerst werden ab Zeile 16 die Datentypen der
Eigenschaften überprüft. Damit erfolgt keine inhaltliche Prüfung – beispielsweise darüber, ob es
diesen Zeichensatz überhaupt gibt –, sondern nur, ob die Eigenschaft überhaupt ein String ist. Eine
solche Überprüfung ist notwendig, da Python eine sogenannte schwach typisierte Sprache ist. Es ist
dem Programmierer überlassen, ob und wie er die Datentypen überprüft.
14
15
16
17
18
19
20
####################################
def anlegen(self):
# Sind die Eigenschaften vom richtigen Datentyp?
assert isinstance(self.konfigurationsdatei, str)
assert isinstance(self.dbname, str)
assert isinstance(self.zeichensatz, str)
assert isinstance(self.sortierung, str)
Nun werden mit Hilfe der Konfigurationsdatei ab Zeile 24 die Verbindungsdaten eingelesen und die
Verbindung aufgebaut.
22
23
24
25
26
try:
config = configparser.ConfigParser()
config.read(self.konfigurationsdatei)
# Konfigdatei ohne Datenbankname
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
verbindung = mysql.connector.connect(**verbindung_parameter)
Alle Befehle werden über ein cursor-Objekt zum MySQL-Server gesendet. Dieses Objekt wird über
das MySQLConnection-Objekt verbindung mit Hilfe der Methode cursor() in Zeile 28 ermittelt.
Danach wird der SQL-Befehl zusammengebaut; der besseren Lesbarkeit wegen auf mehrere Zeilen
verteilt. Das \n im Befehl ist eigentlich völlig überflüssig. Es hilft nur, bei einer Bildschirmausgabe
des Befehls diesen besser lesen zu können, da die einzelnen Befehlsabschnitte auf Zeilen verteilt
werden und durch ein Leerzeichen eingerückt werden.
In Zeile 32 wird der SQL-Befehl ausgeführt. Dabei kann es auch wieder zu Fehlern kommen (Syntax falsch, keine Berechtigung, Verbindung abgebrochen usw.). Daher erfolgt das ganze in einem
try-Block. Zum Schluss wird die Verbindung geschlossen.
Datenbankprogrammierung mit Python
8
cursor
1 setup-Programm
28
29
30
31
32
33
1.3 Datenbanken erstellen und löschen
cursor = verbindung.cursor()
# Erzeugen eines Cursors
befehl = "CREATE DATABASE IF NOT EXISTS {0} " \
"\n CHARACTER SET {1} " \
"\n COLLATE {2}".format(self.dbname, self.zeichensatz,
,→
self.sortierung)
cursor.execute(befehl)
# Befehl ausführen
verbindung.close()
War alles erfolgreich, wird der Wert True zurückgeliefert. Ist man in der exception gelandet, wird
der Wert False zurückgeliefert.
34
35
36
37
except mysql.connector.Error as err:
print("Konnte Datenbank '{0}' nicht anlegen: {1}".format(self.dbname, err))
return False
return True
Verbleibt nur noch der Einbau in das Programm setup.py:
1
from sqltools01 import DatenbankTools
2
3
4
5
6
7
8
9
10
11
12
13
14
try:
db = DatenbankTools()
db.dbname = "milchgesicht"
db.zeichensatz = "latin1"
db.sortierung = "latin1_general_ci"
if db.anlegen():
print("Angelegt!")
else:
print("Nicht angelegt!")
except Exception as err:
print("Fehler: {0}".format(err))
Aufgabe 1.6 Erweitern Sie die Klasse um die Methode löschen() und überschreiben(). Die
Methode überschreiben() soll eine ggf. vorhanden Datenbank vorher löschen und dann neu
anlegen. Testen Sie, ob alle drei Methoden genau das tun, was Sie erwarten.
Aufgabe 1.7 Es ist sicherlich nicht besonders schlau, bei der Methode überschreiben() jeweils
eine Verbindung zum Server auf- und wieder abzubauen. Bauen Sie die Klasse und Methoden so
um, dass in den Methoden löschen() und anlegen() nur dann eine Verbindung zum Server
hergestellt wird, wenn nicht schon eine vorhanden ist.
Zunächst brauchen wir eine neue Eigenschaft: verbindung (siehe Abbildung 3). In dieser Eigenschaft wird eine Verbindung abgelegt, die bei mehreren Methoden verwendet wird. So wird
vermieden, dass jede Methode immer eine neue Verbindung aufbaut. Sie müssen nur noch prüfen,
ob die Verbindung bereits vorhanden ist oder nicht (==None).
Musterlösung: Im Konstruktor wird in Zeile 13 die neue Eigenschaft hinzugefügt und auf None
initialisiert.
Datenbankprogrammierung mit Python
9
1 setup-Programm
1.3 Datenbanken erstellen und löschen
DatenbankTools
+ konfigurationsdatei : string = "settings.cfg"
+ dbname : string = "datenbank_test"
+ zeichensatz : string = "utf8"
+ sortierung : string = "utf8_general_ci"
+ verbindung : MySqlConnection = None
+ anlegen(): bool
+ löschen(): bool
+ überschreiben(): bool
Abbildung 3: Klassendiagramm DatenbankTools, Version 2
13
self.verbindung = None
# Die neue Eigenschaft!
In der Methode anlegen() wird nun vorab gefragt, ob diese Eigenschaft den Wert None hat (siehe
Zeile 23). Nur wenn keine offene Verbindung vorhanden ist, wird die Konfigurationsdatei eingelesen
und eine lokale Verbindung hergestellt; ansonsten wird in Zeile 30 die bereits bestehende Verbindung
verwendet. Ebenso wird auch nur die lokale Verbindung geschlossen. Die restlichen Zeilen der
Methode bleiben gleich.
23
24
25
26
27
28
29
30
if self.verbindung == None:
# verbinden, wenn keine vorhanden!
config = configparser.ConfigParser()
config.read(self.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
verbindung_lokal = mysql.connector.connect(**verbindung_parameter)
cursor = verbindung_lokal.cursor()
else:
cursor = self.verbindung.cursor()
# sonst verwende bestehende
31
32
33
34
35
36
37
befehl = "CREATE DATABASE IF NOT EXISTS {0} " \
"\n CHARACTER SET {1} " \
"\n COLLATE {2}".format(self.dbname, self.zeichensatz,
,→
self.sortierung)
cursor.execute(befehl)
if self.verbindung == None:
verbindung_lokal.close()
Die neue Methoden löschen() sieht fast genauso aus:
43
44
45
46
####################################
def löschen(self):
assert isinstance(self.konfigurationsdatei, str)
assert isinstance(self.dbname, str)
47
48
49
50
51
52
try:
if self.verbindung == None:
config = configparser.ConfigParser()
config.read(self.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
Datenbankprogrammierung mit Python
10
1 setup-Programm
53
54
55
56
1.3 Datenbanken erstellen und löschen
verbindung_lokal = mysql.connector.connect(**verbindung_parameter)
cursor = verbindung_lokal.cursor()
else:
cursor = self.verbindung.cursor()
57
58
59
60
61
62
63
64
65
befehl = "DROP DATABASE IF EXISTS {0} ".format(self.dbname)
cursor.execute(befehl)
if self.verbindung == None:
verbindung_lokal.close()
except mysql.connector.Error as err:
print("Konnte Datenbank '{0}' nicht löschen: {1}".format(self.dbname, err))
return False
return True
Die neue Methoden überschreiben() ist eigentlich nur ein Aufruf der beiden anderen Methoden.
68
69
70
71
72
73
74
####################################
def überschreiben(self):
if not self.löschen():
return False
if not self.anlegen():
return False
return True
75
76
Aber wie sieht nun das Hauptprogramm aus? Ziel war ja, nicht jedes mal die Verbindung neu
aufzubauen.
1
2
3
import mysql.connector
import configparser
from sqltools02 import DatenbankTools
4
5
6
7
8
9
10
try:
db = DatenbankTools()
config = configparser.ConfigParser()
config.read(db.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
db.verbindung = mysql.connector.connect(**verbindung_parameter)
11
12
13
14
15
16
if db.überschreiben():
print("Überschrieben!")
else:
print("Nicht Überschrieben!")
db.verbindung.close()
17
18
19
except Exception as err:
print("Fehler: {0}".format(err))
Datenbankprogrammierung mit Python
11
1 setup-Programm
1.4 Tabellen anlegen
1.4 Tabellen anlegen
TabellenTools
+ konfigurationsdatei : string = "settings.cfg"
+ dbname : string = "datenbank_test"
+ tabellenename : string = "test"
+ create : string = ""
+ verbindung : MySqlConnection = None
+ anlegen() : bool
Abbildung 4: Klassendiagramm Tabelle, Version 1
Die Klasse TabellenTools im Modul sqltools.py ist sehr analog der Klasse DatenbankTools
aufgebaut (siehe Abbildung 4). Es werden folgende die Eigenschaften benötigt:
• konfigurationsdatei: Name der Konfigurationsdatei.
• dbname: Name der Datenbank, in welcher die Tabelle angelegt werden soll.
• tabellenname: Name der Tabelle
• create: Die CREATE TABLE-Anweisung
• verbindung: Eine ggf. schon vorhandene Verbindung
Die Methode anlegen() holt sich alle Infos, die sie benötigt, aus den Eigenschaften.
Die ersten Zeilen der Klasse sollten nun kein Problem mehr sein:
78
79
80
81
82
83
84
85
86
###################################################
class TabellenTools:
def __init__(self):
# Instanzvariablen vorgeben
self.konfigurationsdatei = "settings02.cfg"
self.dbname = "datenbank_test"
self.tabellenname = "test"
self.create_befehl = ""
self.verbindung = None
Die Eigenschaften werden grob auf ihre Datentyprichtigkeit hin überprüft.
89
90
91
92
93
def anlegen(self):
assert isinstance(self.konfigurationsdatei, str)
assert isinstance(self.dbname, str)
assert isinstance(self.tabellenname, str)
assert isinstance(self.create_befehl, str)
Die Methode beginnt genauso so, wie die Methode anlegen() der Klasse DatenbankTools. Es
wird lediglich neu festgelegt, auf welcher Datenbank die Tabellen angelegt werden sollen.
Datenbankprogrammierung mit Python
12
DAB.2.4
1 setup-Programm
95
96
97
98
99
100
101
102
103
104
105
1.4 Tabellen anlegen
try:
if self.verbindung == None:
config = configparser.ConfigParser()
config.read(self.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
verbindung_lokal = mysql.connector.connect(**verbindung_parameter)
verbindung_lokal.database = self.dbname
cursor = verbindung_lokal.cursor()
else:
self.verbindung.database = self.dbname
cursor = self.verbindung.cursor()
In Zeile 107 wird lediglich der Befehl zum Server gesendet und danach wird das Ergebnis verwertet.
107
108
109
110
111
112
113
cursor.execute(self.create_befehl) # Anlegen der Tabelle
if self.verbindung == None:
verbindung_lokal.close()
except mysql.connector.Error as err:
print("Konnte Tabelle '{0}.{1}' nicht anlegen: {2}".format(self.dbname,
,→
self.tabellenname, err))
return False
return True
Aufgabe 1.8 Ermitteln Sie anhand der CSV-Dateien, welche Datentypen dort verwendet werden.
Erstellen Sie anschließend für jede CSV-Datei entsprechend dem ER-Modell in Abbildung 1 das
passende CREATE TABLE-Statement.
Erweitern Sie die CREATE TABLE-Statements um die Primär- und Fremdschlüsselspezifikationen.
Verbleibt der Einbau der CREATE TABLE-Anweisungen in das setup-Programm. In einer Variablen
speicheren wir zunächst den Befehl zum Erzeugen einer Tabellen (siehe Zeile 6ff.). Bis Zeile 26 ist
alles gleich geblieben. Nun wird ein TabellenTools-Objekt angelegt und die offene Verbindung
weitergereicht. Anschließend wird die Tabelle angelegt.
1
2
3
4
import mysql.connector
import configparser
from sqltools03 import DatenbankTools
from sqltools03 import TabellenTools
5
6
7
8
9
10
11
12
13
14
15
16
# Aufbau des Befehls
create_befehl = '''CREATE TABLE verein
(
verein_id
INT AUTO_INCREMENT,
name
VARCHAR(255),
kurzname
VARCHAR(255),
gegründet_am
DATE,
farben
VARCHAR(255),
homepage
VARCHAR(255),
PRIMARY KEY(verein_id)
)'''
Datenbankprogrammierung mit Python
13
Datentypen
Spezifikation von PK
und FK
1 setup-Programm
1.4 Tabellen anlegen
17
18
19
20
21
22
23
24
try:
db = DatenbankTools()
config = configparser.ConfigParser()
config.read(db.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
db.verbindung = mysql.connector.connect(**verbindung_parameter)
db.dbname = "bundesliga"
25
26
27
28
29
30
31
32
if db.überschreiben():
# Nur wenn DB-anlegen geklappt hat
tab = TabellenTools()
tab.verbindung = db.verbindung
tab.dbname = db.dbname
tab.tabellenname = "verein"
tab.create_befehl = create_befehl;
tab.anlegen()
33
34
db.verbindung.close()
35
36
37
except Exception as err:
print("Fehler: {0}".format(err))
Aufgabe 1.9 Erweitern Sie das Setup-Programm so, dass es alle Tabellen anlegt. Testen Sie, ob
die Tabellen auch wirklich angelegt wurden.
Zusatz für Pfiffige: Versuchen Sie es mit Hilfe einer geeigneten Datenstruktur und einer Schleife.
Musterlösung: Das Setup-Programm erweitert. Zunächst wird ab Zeile 6 eine Liste von TabellenTools-Objekt
erstellt.
6
7
# Erstellen einer Tabellenliste
liste_tabellen = []
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
tab = TabellenTools()
tab.tabellenname = "verein"
tab.create_befehl = '''
CREATE TABLE verein
(
verein_id
INT AUTO_INCREMENT,
name
VARCHAR(255),
kurzname
VARCHAR(255),
gegründet_am
DATE,
farben
VARCHAR(255),
homepage
VARCHAR(255),
PRIMARY KEY(verein_id)
)'''
liste_tabellen.append(tab)
23
24
25
26
27
28
29
tab = TabellenTools()
tab.tabellenname = "paarung"
tab.create_befehl = '''
CREATE TABLE paarung
(
paarung_id
INT AUTO_INCREMENT,
Datenbankprogrammierung mit Python
14
1 setup-Programm
30
31
32
33
34
35
36
37
38
39
40
41
42
43
1.4 Tabellen anlegen
heim_verein_id
INT,
gast_verein_id
INT,
anstoß
DATETIME,
saison
CHAR(7),
spieltag
INT,
tore_heim_ende
INT,
tore_gast_ende
INT,
tore_heim_halb
INT,
tore_gast_halb
INT,
PRIMARY KEY(paarung_id),
FOREIGN KEY(heim_verein_id) REFERENCES verein(verein_id),
FOREIGN KEY(gast_verein_id) REFERENCES verein(verein_id)
)'''
liste_tabellen.append(tab)
44
45
46
47
48
49
50
51
52
53
54
55
56
57
tab = TabellenTools()
tab.tabellenname = "titelgewinn"
tab.create_befehl = '''
CREATE TABLE titelgewinn
(
titelgewinn_id
INT AUTO_INCREMENT,
titelname
VARCHAR(255),
saison
CHAR(7),
verein_id
INT,
PRIMARY KEY(titelgewinn_id),
FOREIGN KEY(verein_id) REFERENCES verein(verein_id)
)'''
liste_tabellen.append(tab)
58
59
60
61
62
63
64
65
66
67
68
69
70
71
tab = TabellenTools()
tab.tabellenname = "trainer"
tab.create_befehl = '''
CREATE TABLE trainer
(
trainer_id
INT AUTO_INCREMENT,
vorname
VARCHAR(255),
nachname
VARCHAR(255),
geboren_in
VARCHAR(255),
geboren_am
DATE,
PRIMARY KEY(trainer_id)
)'''
liste_tabellen.append(tab)
72
73
74
75
76
77
78
79
80
81
82
83
84
tab = TabellenTools()
tab.tabellenname = "trainer_verein"
tab.create_befehl = '''
CREATE TABLE trainer_verein
(
trainer_id
INT,
verein_id
INT,
von
DATE,
bis
DATE,
FOREIGN KEY(trainer_id) REFERENCES trainer(trainer_id),
FOREIGN KEY(verein_id) REFERENCES verein(verein_id)
)'''
Datenbankprogrammierung mit Python
15
1 setup-Programm
85
1.4 Tabellen anlegen
liste_tabellen.append(tab)
Nun können alle Tabellen bequem durch eine Schleife den gleichen Datenbanknamen erhalten (siehe
Zeile 87):
87
88
for t in liste_tabellen: # Durchlaufen der Liste
t.dbname = "bundesliga"
Mit Hilfe der gleichen Schleifenkonstruktion (siehe Zeile 99) werden nun die Tabellen angelegt.
90
91
92
93
94
95
96
try:
db = DatenbankTools()
config = configparser.ConfigParser()
config.read(db.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
db.verbindung = mysql.connector.connect(**verbindung_parameter)
db.dbname = "bundesliga"
97
98
99
100
101
if db.überschreiben():
for t in liste_tabellen: # Durchlaufen der Liste
t.verbindung = db.verbindung
t.anlegen()
102
103
db.verbindung.close()
104
105
106
except Exception as err:
print("Fehler: {0}".format(err))
Bitte beachten Sie die Reihenfolge. Es könnte sonst passierten, dass die Tabelle paarung vor der
Tabelle verein angelegt werden würde. Dies würde zu einer Fehlermeldung führen, weil dann die
Fremdschlüsselreferenzen ins leere verweisen.
Noch schöner wäre es, wenn wir die Anwendung so umbauen würden, dass die Tabellendefinitionen
nicht im Quelltext, sondern in externen Textdateien stehen würden. Sie erinnern sich? Trennung
von Daten und Logik!
Aufgabe 1.10 Legen Sie das Unterverzeichnis def_tab an und erstellen Sie dort 5 SQL-Dateien.
Die Namen der Dateien soll beispielhaft folgenden Aufbau haben:
01_verein.sql,
02_paarung.sql
...
In jeder Datei steht nur der CREATE TABLE-Befehl der entsprechenden Tabelle:
1
2
3
4
5
6
7
8
CREATE TABLE verein
(
verein_id
name
kurzname
gegründet_am
farben
homepage
INT AUTO_INCREMENT,
VARCHAR(255),
VARCHAR(255),
DATE,
VARCHAR(255),
VARCHAR(255),
Datenbankprogrammierung mit Python
16
Modul os
1 setup-Programm
9
10
)
1.4 Tabellen anlegen
PRIMARY KEY(verein_id)
Die Nummerierung der Dateinamen soll sicherstellen, dass die Tabellen in der richtigen Reihenfolge
angelegt werden.
Nun kopieren Sie die Datei settings02.cfg nach settings03.cfg und erweitern diese wie
folgt:
1
2
3
4
[VERBINDUNGSPARAMETER]
user = root
password =
host = 127.0.0.1
5
6
7
8
[VERZEICHNISSE]
projekt = .
tabellen = def_tab
Nun sind das Projektverzeichnis und das Unterverzeichnis mit den Tabellendefinitionen frei wählbar.
Verbleibt nur noch die Implementierung.
1
2
3
4
5
import mysql.connector
import configparser
from sqltools04 import DatenbankTools
from sqltools04 import TabellenTools
import os
# Importieren der os-Funktionen
6
7
8
9
10
11
12
13
14
15
try:
db = DatenbankTools()
db.konfigurationsdatei = "settings03.cfg"
config = configparser.ConfigParser()
config.read(db.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
db.verbindung = mysql.connector.connect(**verbindung_parameter)
db.dbname = "bundesliga"
In Zeile 5 werden Betriebssystemfunktionen importiert. Diese brauchen wir zum Auslesen von
Verzeichnis- und Dateiinhalten. Vergessen Sie nicht den Namen der Konfigurationsdatei nach
settings03.cfg zu ändern!
17
18
19
20
# Einlesen der Dateinamen des Tabellenverzeichnisses
tabellenverzeichnis = config["VERZEICHNISSE"]["projekt"] + os.sep +
,→
config["VERZEICHNISSE"]["tabellen"]
dateiliste = os.listdir(tabellenverzeichnis)
dateiliste.sort() # Nummerierung bestimmt Reihenfolge
Ab der Zeile 17 werden die Verzeichnisinhalte eingelesen. Im ersten Schritt wird der Verzeichnisname aus dem Projektverzeichnis und dem Tabellendefinitionsverzeichnis zusammengebastelt.
Anschließend werden mit os.listdir() die Dateinamen ermittelt. Sie müssen selbst sicherstellen,
Datenbankprogrammierung mit Python
17
1 setup-Programm
1.5 Zwischenbericht
dass nur die Definitionsdateien im Verzeichnis liegen. Zum Schluss werden in Zeile 20 die Dateinamen noch sortiert, da wir nicht davon ausgehen können, dass die Dateinamen immer in richtigen
Reihenfolge eingelesen werden.
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
# Erstellen der Tabellenliste nun aus den Dateien
liste_tabellen = []
for dateiname in dateiliste:
länge = len(dateiname)
tabname = dateiname[3:länge - 4]
dateiname_komplett = tabellenverzeichnis + os.sep + dateiname
try:
befehl = open(dateiname_komplett, encoding="utf-8").read()
t = TabellenTools();
t.dbname = db.dbname
t.tabellenname = tabname
t.create_befehl = befehl
liste_tabellen.append(t)
except OSError as err:
print("Fehler beim Lesen der Datei {0}: {1}".format(dateiname, err))
Ab Zeile 22 wird nun die Tabellenliste aufgebaut. Im ersten Teil muss aus dem Dateinamen der
Tabellenname herausgeschnitten werden. Dazu wird die Gesamtlänge des Dateinamens ermittelt
und der vordere und hintere Teil weggeschnitten.
Jetzt wird die Datei geöffnet und dabei ist das Encoding zu beachten. Da unsere Datenbank in
Unicode utf-8 kodiert ist, sollten die Strings der Datei auch so ausgelesen werden. Mit read()
wird der Inhalt komplett ausgelesen und in die String-Variable befehl abgespeichert. Verbleibt der
Aufruf des Konstruktors von Tabelle und das Anhängen an die Liste.
Der Rest des Quelltextes bleibt unverändert.
38
39
40
41
if db.überschreiben():
for t in liste_tabellen:
t.verbindung = db.verbindung
t.anlegen()
42
43
db.verbindung.close()
44
45
46
except Exception as err:
print("Fehler: {0}".format(err))
1.5 Zwischenbericht
Unser Setup-Programm kann schon folgendes:
• Die Zugangsparameter zum Datenbankserver werden aus einer Konfigurationsdatei ausgelesen.
• Es kann eine neue Datenbank mit einem vorgegeben Namen angelegt werden. Dieser Datenbank kann eine Zeichenkodierung und eine Sortierung zugewiesen werden.
• Eine ggf. vorhanden Datenbank gleichen namens kann überschrieben werden.
Datenbankprogrammierung mit Python
18
1 setup-Programm
1.6 Tabellen löschen
• In einem Verzeichnis können SQL-Skripte mit entsprechenden CREATE TABLE-Anweisungen
abgelegt werden. Der Verzeichnisname ist über eine Konfigurationsdatei einstellbar.
• Die SQL-Dateien werden sortiert und entsprechend ihrer Reihenfolge ausgeführt.
Somit stehen uns nach Ausführung des Setup-Programmes die Bundesliga-Datenbank und die
entsprechenden Tabellen zur Verfügung.
Wir können also schon eine ganze Menge, oder?
1.6 Tabellen löschen
Aufgabe 1.11 Erweitern Sie die Klasse TabellenTools um die Methode löschen() (siehe Abbildung 5). Legen Sie zum Test dazu eine neue sinnlose Tabellen an.
TabellenTools
+ konfigurationsdatei : string = "settings.cfg"
+ dbname : string = "datenbank_test"
+ tabellenename : string = "test"
+ create : string = ""
+ verbindung : MySqlConnection = None
+ anlegen() : bool
+ löschen() : bool
Abbildung 5: Klassendiagramm Tabelle, Version 2
115
116
117
118
119
####################################
def löschen(self):
assert isinstance(self.konfigurationsdatei, str)
assert isinstance(self.dbname, str)
assert isinstance(self.tabellenname, str)
120
121
122
123
124
125
126
127
128
129
130
131
try:
if self.verbindung == None:
config = configparser.ConfigParser()
config.read(self.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
verbindung_lokal = mysql.connector.connect(**verbindung_parameter)
verbindung_lokal.database = self.dbname
cursor = verbindung_lokal.cursor()
else:
cursor = self.verbindung.cursor()
self.verbindung.database = self.dbname
132
133
134
135
136
137
befehl = "DROP TABLE IF EXISTS {0} ".format(self.tabellenname)
cursor.execute(befehl)
if self.verbindung == None:
verbindung_lokal.close()
except mysql.connector.Error as err:
Datenbankprogrammierung mit Python
19
1 setup-Programm
138
139
140
1.7 Tabellen ändern
print("Konnte Tabelle '{0}.{1}' nicht löschen: {2}".format(self.tabellenname,
,→
self.dbname, err))
return False
return True
Der Test könnte wie folgt aussehen:
143
144
145
146
147
148
149
150
###################################################
# Hauptprogramm
if __name__ == "__main__":
db = DatenbankTools()
if db.anlegen():
print("DB Angelegt!")
else:
print("DB nicht angelegt!")
151
152
153
154
155
if db.löschen():
print("DB Gelöscht!")
else:
print("DB nicht gelöscht!")
156
157
158
159
160
if db.überschreiben():
print("DB überschrieben!")
else:
print("DB nicht überschrieben!")
161
162
163
164
165
166
167
168
169
170
171
172
173
174
tab = TabellenTools()
tab.konfigurationsdatei = "settings03.cfg"
tab.dbname = "bundesliga";
tab.tabellenname = "wurst"
tab.create_befehl = "CREATE TABLE IF NOT EXISTS wurst(i int, x varchar(100))"
if tab.anlegen():
print("TAB angelegt")
if tab.löschen():
print("TAB gelöscht")
else:
print("TAB nicht angelegt")
else:
print("TAB nicht angelegt")
1.7 Tabellen ändern
Ich möchte die Tabellen so geändert haben, dass diese ein Löschkennzeichen (deleted vom Typ
BOOL) besitzen. Dieses soll nicht NULL werden können und die Vorbelegung FALSE haben. Dazu muss die Klasse TabellenTools um die Methode ändern() erweitert werden (siehe Abbildung 6).
Aufgabe 1.12 Schreiben Sie die Methode ändern(). Hinweis: Die Aufgabe ist einfach.
Musterlösung:
Datenbankprogrammierung mit Python
20
1 setup-Programm
1.7 Tabellen ändern
TabellenTools
+ konfigurationsdatei : string = "settings.cfg"
+ dbname : string = "datenbank_test"
+ tabellenename : string = "test"
+ create : string = ""
+ verbindung : MySqlConnection = None
+ anlegen() : bool
+ löschen() : bool
+ ändern(alteroption : string) : bool
Abbildung 6: Klassendiagramm Tabelle, Version 3
142
143
144
145
146
147
####################################
def ändern(self, alteroption):
assert isinstance(self.konfigurationsdatei, str)
assert isinstance(self.dbname, str)
assert isinstance(self.tabellenname, str)
assert isinstance(alteroption, str)
148
149
150
151
152
153
154
155
156
157
158
159
try:
if self.verbindung == None:
config = configparser.ConfigParser()
config.read(self.konfigurationsdatei)
verbindung_parameter = config["VERBINDUNGSPARAMETER"]
verbindung_lokal = mysql.connector.connect(**verbindung_parameter)
verbindung_lokal.database = self.dbname
cursor = verbindung_lokal.cursor()
else:
cursor = self.verbindung.cursor()
self.verbindung.database = self.dbname
160
161
162
163
164
165
166
167
168
befehl = "ALTER TABLE {0} {1}".format(self.tabellenname, alteroption)
cursor.execute(befehl)
if self.verbindung == None:
verbindung_lokal.close()
except mysql.connector.Error as err:
print("Konnte Tabellenstruktur von '{0}.{1}' nicht verändern:
,→
{2}".format(self.tabellenname, self.dbname, err))
return False
return True
Jetzt muss nur noch das Setup-Programm erweitert werden:
44
45
46
47
# Anhängen des Löschkennzeichens
alteroption = "ADD deleted BOOL NOT NULL DEFAULT FALSE"
for t in liste_tabellen:
t.ändern(alteroption)
Ab Zeile 44 wird allen Tabellen – wieder kommt unsere supertolle Schleifenkonstruktion zum tragen
– das entsprechende alteroption zugewiesen und die Änderungen werden ausgeführt.
Datenbankprogrammierung mit Python
21