16. Verwendung von SGBD und MySQL
16.1. Installation von SGBD und MySQL
Um SGBD und MySQL nutzen zu können, installieren wir die Software Laragon.
16.1.1. Installation von Laragon
Laragon ist ein Paket, das mehrere Programme umfasst:
- einen Apache-Webserver. Wir werden ihn zum Schreiben von Webskripten in Python verwenden;
- SGBD MySQL;
- die Skriptsprache PHP, die wir nicht verwenden werden;
- einen Redis-Server, der einen Cache für Webanwendungen implementiert. Wir werden ihn nicht verwenden;
Laragon kann (Stand: Februar 2020) unter folgender Adresse heruntergeladen werden:
- Die Installation [1-5] führt zu folgendem Verzeichnisbaum:
- in [6] den Installationsordner von PHP (in diesem Dokument nicht verwendet);
Beim Starten von [Laragon] wird das folgende Fenster angezeigt:
- [1]: das Hauptmenü von Laragon;
- [2]: Die Schaltfläche [Start All] startet den Apache-Webserver und SGBD MySQL;
- [3]: Die Schaltfläche [WEB] zeigt die Webseite [http://localhost] an;
- [4]: Über die Schaltfläche [Database] kann man SGBD und MySQL mit dem Tool [phpMyAdmin] verwalten. Dieses muss zuvor installiert werden;
- [5]: Die Schaltfläche „[Terminal]“ öffnet ein Befehlsterminal;
- [6]: Die Schaltfläche [Root] öffnet einen Windows-Explorer, der auf den Ordner [<laragon>/www] ausgerichtet ist, der das Stammverzeichnis der Website [http://localhost] darstellt. Dort müssen die statischen Webanwendungen abgelegt werden, die vom Apache-Server von Laragon verwaltet werden;
16.1.2. Erstellen einer Datenbank
Wir zeigen nun, wie man mit dem Laragon-Tool eine Datenbank sowie einen Benutzer namens MySQL anlegt.
- Nach dem Start kann Laragon [1] über ein Menü [2] verwaltet werden;
- In [3-5] wird das Verwaltungstool [phpMyAdmin] für MySQL installiert, sofern es nicht bereits installiert ist;
- In [6] werden der Apache-Webserver sowie SGBD und MySQL gestartet;
- in [7] wird der Apache-Server gestartet;
- in [8] werden SGBD und MySQL gestartet;
- In [8-10] wird eine Datenbank erstellt, die den Namen [dbpersonnes] [11] erhält. Wir werden eine Personendatenbank aufbauen;
- in [11] verwalten wir die soeben erstellte Datenbank;
- Der Vorgang [Bases de données] sendet eine Webanfrage an URL, [http://localhost/phpmyadmin] und [12]. Der Apache-Webserver von Laragon antwortet darauf. URL und [http://localhost/phpmyadmin] sind Teil des Dienstprogramms URL, das wir zuvor installiert haben ([phpMyAdmin] und [5]). Mit diesem Dienstprogramm lassen sich die Datenbanken MySQL verwalten;
- standardmäßig lauten die Anmeldedaten des Datenbankadministrators: root [13] ohne Passwort [14];
- in [16], der Datenbank, die wir zuvor angelegt haben;
- Derzeit haben wir eine Datenbank mit den Namen [dbpersonnes], [17], die leer ist, und [18];
Wir legen einen Benutzer [admpersonnes] mit dem Passwort [nobody] an, der alle Rechte an der Datenbank [dbpersonnes] erhält:
- In [19] befinden wir uns in der Datenbank [dbpersonnes];
- in [20] wählen wir die Registerkarte [Privileges] aus;
- In [21-22] sieht man, dass der Benutzer [root] alle Rechte an der Datenbank [dbpersonnes] hat;
- In [23] wird ein neuer Benutzer angelegt;
- In [25-26] erhält der Benutzer die ID [admdbpersonnes];
- in [27-29] lautet sein Passwort [nobody];
- Bei [30] weist phpMyAdmin darauf hin, dass das Passwort sehr schwach ist (leicht zu knacken). In der Produktion ist es ratsam, mit [31] ein starkes Passwort zu generieren;
- Bei [32] wird festgelegt, dass der Benutzer [admdbpersonnes] alle Rechte an der Datenbank [dbpersonnes] haben soll;
- in [33] werden die angegebenen Informationen bestätigt;
- In [35] gibt phpMyAdmin an, dass der Benutzer angelegt wurde;
- in [36] wird der Befehl SQL ausgeführt, der auf der Datenbankbasis erteilt wurde;
- in [37] hat der Benutzer [admpersonnes] alle Rechte an der Datenbank [dbpersonnes];
Nun haben wir:
- eine Datenbank MySQL und [dbpersonnes];
- einen Benutzer [admpersonnes/nobody], der über alle Rechte an dieser Datenbank verfügt;
16.2. Installation des Pakets [mysql-connector-python]
Wir werden Python-Skripte schreiben, um die zuvor erstellte Datenbank mit der folgenden Architektur zu nutzen:
Ein Connector dient dazu, den Python-Code vom verwendeten SGBD zu trennen. Es gibt Connectoren für verschiedene SGBD, die alle dieselbe Schnittstelle verwenden. Wenn man also oben das SGBD und das MySQL durch das SGBD und das PostgreSQL ersetzt, ergibt sich folgende Architektur:
Da alle Konnektoren von SGBD dieselbe Schnittstelle verwenden, muss das Python-Skript normalerweise nicht geändert werden. In der Praxis verfügen die meisten SGBD über einen proprietären SQL:
- sie entsprechen dem Standard SQL (Structured Query Language);
- erweitern diesen jedoch, da er nicht ausreicht, um proprietäre Spracherweiterungen zu integrieren;
Daher kommt es häufig vor, dass bei einem Wechsel der SGBD-Datenbank auch Änderungen an den Skripten vorgenommen werden müssen.
Python bietet von Haus aus keine Möglichkeit, eine MySQL-Datenbank zu verwalten. Dazu muss ein Paket heruntergeladen werden. Es gibt mehrere davon. Wir werden hier das Paket [mysql-connector-python] verwenden, das der offizielle Konnektor von Oracle ist, dem Unternehmen, dem MySQL gehört.
Die Installation des Pakets erfolgt in einem [Terminal]-Fenster von PyCharm:
- Der Ordner „[2]“ spielt für die folgenden Schritte keine Rolle;
Im Terminal geben Sie den Befehl [pip search MySQL] ein:
- [pip] (Package Installer for Python) ist das Tool zur Installation von Python-Paketen. Das Tool [pip] stellt eine Verbindung zum Repository her, das die Python-Pakete enthält;
- [search MySQL]: Fordert die Liste der Pakete an, deren Name den Begriff [MySQL] enthält (Groß-/Kleinschreibung spielt keine Rolle);
Die Ergebnisse des Befehls lauten wie folgt:
mysql (0.0.2) - Virtual package for MySQL-python
jx-mysql (3.49.20042) - jx-mysql - JSON Expressions for MySQL
weibo-mysql (0.1) - insert mysql
bits-mysql (1.0.3) - BITS MySQL
MySQL-python (1.2.5) - Python interface to MySQL
deployfish-mysql (0.2.13) - Deployfish MySQL plugin
mtstat-mysql (0.7.3.3) - MySQL Plugins for mtstat
bottle-mysql (0.3.1) - MySQL integration for Bottle.
WintxDriver-MySQL (2.0.0-1) - MySQL support for Wintx
py-mysql (1.0) - Operating Mysql for Python.
mysql-utilities (1.4.3) - MySQL Utilities 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
…. - Tool to move slices of data from one MySQL store to another
mysql-tracer (2.0.2) - A MySQL client to run queries, write execution reports and export results
mysql-utils (0.0.2) - A simple MySQL library including a set of utility APIs for Python database programming
mysql-connector-repackaged (0.3.1) - MySQL driver written in Python
dffml-source-mysql (0.0.5) - DFFML Source for MySQL Protocol
mysql-connector-python (8.0.19) - MySQL driver written in Python
INSTALLED: 8.0.19 (latest)
prometheus-mysql-exporter (0.2.0) - MySQL query Prometheus exporter
backwork-backup-mysql (0.3.0) - Backwork plug-in for MySQL backups.
django-mysql-manager (0.1.4) - django-mysql-manager is a Django based management interface for MySQL users and databases.
…. - mysql operate
C:\Data\st-2020\dev\python\cours-2020\v-01>
Es wurden alle Module aufgelistet, deren Name oder Beschreibung das Schlüsselwort MySQL enthält. Das Modul, das wir verwenden werden (Februar 2020), ist [mysql-connector-python], Zeile 17. Um es zu installieren, geben wir im Terminal den Befehl [pip install -U mysql-connector-python] ein:
C:\Data\st-2020\dev\python\cours-2020\v-01>pip install -U mysql-connector-python
Collecting mysql-connector-python
Using cached mysql_connector_python-8.0.19-py2.py3-none-any.whl (355 kB)
Requirement already satisfied, skipping upgrade: protobuf==3.6.1 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (3.6.1)
Requirement already satisfied, skipping upgrade: dnspython==1.16.0 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (1.16.0)
Requirement already satisfied, skipping upgrade: six>=1.9 in c:\users\serge\appdata\roaming\python\python38\site-packages (from protobuf==3.6.1->mysql-connector-python) (1.14.0)
Requirement already satisfied, skipping upgrade: setuptools in c:\myprograms\python38\lib\site-packages (from protobuf==3.6.1->mysql-connector-python) (41.2.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.19
- Zeile 1: Die Option [install -U] (U=upgrade) fordert die aktuellste Version der verschiedenen Pakete an, die mit dem Paket [mysql-connector-python] verbunden sind;
Um die in der Python-Umgebung unseres Rechners installierten Pakete anzuzeigen, geben wir den Befehl [pip list] ein:
C:\Data\st-2020\dev\python\cours-2020\v-01>pip list
Package Version
---------------------- ----------
asgiref 3.2.3
astroid 2.3.3
atomicwrites 1.3.0
attrs 19.3.0
certifi 2019.11.28
…
MarkupSafe 1.1.1
mccabe 0.6.1
more-itertools 8.1.0
mysql-connector-python 8.0.19
mysqlclient 1.4.6
packaging 20.0
pip 20.0.1
pipenv 2018.11.26
…
- Zeile 13: Das Paket [mysql-connector-python] ist tatsächlich vorhanden;
Um zu erfahren, wie man das Paket [mysql-connector-python] zur Verwaltung einer Datenbank MySQL verwendet, besuchen wir die Website des Pakets |https://dev.mysql.com/doc/connector-python/en/|. Im Folgenden finden Sie eine Reihe von Beispielen.
16.3. Skript [mysql_01]: Verbindung zu einer Datenbank MySQL – 1
Das Skript [mysql_01] stellt den ersten Schritt bei der Verwendung einer Datenbank dar. Damit können wir überprüfen, ob wir eine Verbindung zu der zuvor angelegten Datenbank [dbpersonnes] herstellen können.
# Import des Moduls mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError
# Verbindung zu einer Datenbank MySql [dbpersonnes]
# Die Benutzeridentität lautet (admpersonnes,nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# Los geht's
connexion = None
try:
print("Connexion au SGBD MySQL en cours...")
# Anmeldung
connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
# Weiter
print(
f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
# Der Fehler wird angezeigt
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# Die Verbindung wird geschlossen, falls sie geöffnet wurde
if connexion:
connexion.close()
Hinweise
- Zeile 2: Wir importieren bestimmte Funktionen und Klassen aus dem Modul [mysql.connector];
- Zeilen 6–7: die Anmeldedaten des Benutzers, der die Verbindung herstellen wird;
- Zeile 8: Der Rechner, auf dem die Datenbank gehostet wird. Der Konnektor MySQL ermöglicht nämlich die Arbeit mit einer entfernten Datenbank;
- Zeile 9: Der Name der Datenbank, mit der eine Verbindung hergestellt werden soll;
- Zeilen 11–26: Das Skript verbindet (Zeile 16) den Benutzer [admpersonnes / nobody] mit der Datenbank [dbpersonnes];
- Zeilen 20–26: Die Verbindung kann fehlschlagen. Daher wird sie in einem „try/except/finally“-Block ausgeführt;
- Zeile 16: Die Methode connect des Moduls [mysq.connector] akzeptiert verschiedene benannte Parameter:
- user: Benutzer, der Eigentümer der Verbindung [admpersonnes] ist;
- password: Passwort des Benutzers [nobody];
- host: Maschine von SGBD, MySQL, [localhost];
- database: Die Datenbank, mit der eine Verbindung hergestellt wird. Optional.
- Zeile 20: Wenn eine Ausnahme ausgelöst wird, handelt es sich um den Typ [DatabaseError] oder [InterfaceError];
- Zeilen 23–26: In der Klausel [finally] wird die Verbindung geschlossen;
Ergebnisse
16.4. Skript [mysql_02]: Verbindung zu einer Datenbank MySQL – 2
In diesem neuen Skript wird die Verbindung zur Datenbank in einer Funktion isoliert:
# Import des Moduls mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect
# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
# stellt eine Verbindung zur Datenbank [database] auf dem Server [host] her und trennt sie anschließend wieder (Login, Passwort)
# löst bei einem Problem die Ausnahme DatabaseError aus
connexion = None
try:
# Verbindung
connexion = connect(host=host, user=login, password=pwd, database=database)
print(
f"Connexion réussie à la base database={database}, host={host} sous l'identité user={login}, passwd={pwd}")
finally:
# Die Verbindung wird geschlossen, wenn sie geöffnet wurde
if connexion:
connexion.close()
print("Déconnexion réussie\n")
# ---------------------------------------------- main
# Anmeldedaten
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# Anmeldung eines bestehenden Benutzers
try:
connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# Es wird eine Fehlermeldung angezeigt
print(erreur)
# Anmeldung eines nicht vorhandenen Benutzers
try:
connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# Fehler wird angezeigt
print(erreur)
Anmerkungen:
- Zeilen 6–19: Eine Funktion [connexion], die versucht, einen Benutzer mit der Datenbank [dbpersonnes] zu verbinden und anschließend die Verbindung wieder zu trennen. Zeigt das Ergebnis an;
- Zeilen 29–41: Hauptprogramm – ruft zweimal die Methode connexion auf und zeigt eventuelle Ausnahmen an;
Ergebnisse
16.5. Skript [mysql_03]: Erstellung einer Tabelle MySQL
Nachdem wir nun wissen, wie man eine Verbindung mit SGBD und MySQL herstellt, beginnen wir damit, Befehle SQL über diese Verbindung auszuführen. Dazu stellen wir eine Verbindung zur erstellten Datenbank [dbpersonnes] her und nutzen die Verbindung, um eine Tabelle in der Datenbank anzulegen.
# Importe
import sys
from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection
# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
# führt eine Aktualisierungsanfrage für die Verbindung aus
curseur = None
try:
# ein Cursor wird angefordert
curseur = connexion.cursor()
# führt die Update-Abfrage über die Verbindung aus
curseur.execute(update)
finally:
# Schließt den Cursor, falls er abgerufen wurde
if curseur:
curseur.close()
# ---------------------------------------------- main
# Anmeldedaten der Verbindung
# Benutzer-ID
ID = "admpersonnes"
PWD = "nobody"
# Host-Rechner des DBMS
HOST = "localhost"
# Datenbank-ID
DATABASE = "dbpersonnes"
# Schritt für Schritt
try:
# Verbindung
connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
# Modus AUTOCOMMIT
connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
# Fehler anzeigen
print(f"L'erreur suivante s'est produite : {erreur}")
# Beenden
sys.exit()
# Löschen der Tabelle „Personen“, falls vorhanden
# Wenn sie nicht existiert, tritt ein Fehler auf – dieser wird ignoriert
requête = "drop table personnes"
try:
execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
pass
# Erstellung der Tabelle „personen“
requête = "create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, " \
"unique(nom,prenom)) "
try:
# Abfrage ausführen
execute_sql(connexion, requête)
# Anzeige
print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
# Der Fehler wird angezeigt
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# Abmeldung
connexion.close()
Hinweise:
- Zeile 9: Die Funktion execute_sql führt eine Abfrage SQL über eine geöffnete Verbindung aus;
- Zeile 14: Die Operationen SQL auf der Verbindung erfolgen über ein spezielles Objekt, das als Cursor bezeichnet wird;
- Zeile 14: Abrufen eines Cursors;
- Zeile 16: Ausführung der Abfrage SQL;
- Zeilen 17–20: Unabhängig davon, ob ein Fehler auftritt oder nicht, wird der Cursor geschlossen. Dadurch werden die ihm zugeordneten Ressourcen freigegeben. Sollte eine Ausnahme auftreten, wird diese hier nicht behandelt. Sie wird an den aufrufenden Code weitergeleitet;
- Zeilen 33–43: Aufbau einer Verbindung zur Datenbank;
- Zeile 38: Der Modus AUTOCOMMIT=True für eine Verbindung bedeutet, dass jede Ausführung einer Abfrage in einer automatischen Transaktion erfolgt. Der Standardmodus ist AUTOCOMMIT=False, wobei der Entwickler für die Verwaltung der Transaktionen verantwortlich ist. Eine Transaktion ist ein Mechanismus, der die Ausführung mehrerer Abfragen von 1 bis n umfasst. Entweder sind alle erfolgreich oder keine. Wenn also die Abfragen 1 bis i erfolgreich sind, die Abfrage i+1 jedoch fehlschlägt, werden die Abfragen 1 bis i „rückgängig gemacht“, damit die Datenbank wieder den Zustand erreicht, den sie vor der Ausführung der Abfrage 1 hatte;
- Hier gibt es zwei Abfragen SQL (Zeilen 49, 58). Sie werden jeweils in einer Transaktion ausgeführt. Dass die zweite fehlschlägt, hat keinerlei Auswirkungen auf die erste;
- Zeilen 45–51: Der Befehl SQL [drop table personnes] wird ausgeführt. Er löscht die Tabelle mit dem Namen [personnes]. Falls diese nicht existiert, kann ein Fehler gemeldet werden. Dieser wird ignoriert (Zeile 51);
- Zeilen 53–55: Der Befehl zum Anlegen der Tabelle [personnes]. Eine Tabelle kann als eine Ansammlung von Zeilen und Spalten betrachtet werden. Der Anlegebefehl gibt die Namen der Spalten an:
- [id]: eine ganzzahlige Kennung. Sie ist für jede Person eindeutig. Dies ist der Primärschlüssel (PRIMARY KEY). Das bedeutet, dass diese Spalte in der Tabelle keinen doppelten Wert aufweist und zur Identifizierung einer Person verwendet werden kann;
- [nom]: eine Zeichenfolge mit maximal 30 Zeichen;
- [prenom]: eine Zeichenfolge mit maximal 30 Zeichen;
- [age]: eine ganze Zahl;
- Das Attribut [NOT NULL] für jede dieser Spalten bedeutet, dass in einer Zeile der Tabelle keine der drei Spalten leer sein darf;
- Der Parameter [unique(nom,prenom)] wird als Einschränkung bezeichnet. Hier besteht die Einschränkung für die Zeilen darin, dass das Tupel (Nachname, Vorname) der Zeile in der Tabelle eindeutig sein muss. Das bedeutet, dass eine Person, deren Nachname und Vorname bekannt sind, in der Tabelle eindeutig identifiziert werden kann;
- Zeilen 56–60: Ausführung des Befehls SQL;
- Zeilen 61–63: Behandlung einer möglichen Ausnahme;
- Zeilen 64–66: Die Verbindung zur Datenbank wird getrennt;
Ergebnisse
Überprüfung mit [phpMyAdmin]:
- Die Datenbank [dbpersonnes] [1] enthält eine Tabelle [personnes] [2] mit der Struktur [3-4], den Primärschlüssel [5] und die Eindeutigkeitsbeschränkung [6];
16.6. Skript [mysql_04]: Ausführung einer Befehlsdatei SQL
Nachdem wir zuvor die Tabelle [personnes] angelegt haben, füllen wir sie nun und werten sie mithilfe der Befehle SQL aus.
Wir möchten die Befehle SQL aus einer Textdatei ausführen:
Der Inhalt der Datei [commandes.sql] lautet wie folgt:
# Löschen der Tabelle [personnes]
drop table personnes
# Erstellung der Tabelle „Personen“
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# Einfügen von zwei Personen
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# Anzeige der Tabelle
select prenom, nom, age from personnes
# absichtlicher Fehler
xx
# Einfügen von drei Personen
insert into personnes(prenom, nom, age) values ('Pierre','Nicazou',35)
insert into personnes(prenom, nom, age) values ('Geraldine','Colou',26)
insert into personnes(prenom, nom, age) values ('Paulette','Girond',56)
# Anzeige der Tabelle
select prenom, nom, age from personnes
# Liste der Personen in alphabetischer Reihenfolge der Nachnamen und bei gleichen Nachnamen in alphabetischer Reihenfolge der Vornamen
select nom,prenom from personnes order by nom asc, prenom desc
# Liste der Personen, deren Alter im Intervall [20,40] liegt, in absteigender Reihenfolge nach dem Alter
# anschließend bei gleichem Alter in alphabetischer Reihenfolge der Nachnamen und bei gleichen Nachnamen in alphabetischer Reihenfolge der Vornamen
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# Einfügung von Frau Bruneau
insert into personnes(prenom, nom, age) values('Josette','Bruneau',46)
# Aktualisierung ihres Alters
update personnes set age=47 where nom='Bruneau'
# Liste der Personen mit dem Nachnamen Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
# Löschung von Frau Bruneau
delete from personnes where nom='Bruneau'
# Liste der Personen mit dem Nachnamen Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
Zunächst definieren wir Funktionen, die wir in einem Modul ablegen, um sie wiederverwenden zu können:
Das Skript [mysql_module] lautet wie folgt:
# Importe
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
# zeigt das Ergebnis eines SQL-Befehls an
…
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# verwendet die geöffnete Verbindung [connexion]
# führt über diese Verbindung die Befehle SQL aus, die in der Liste [sql_commands] enthalten sind
# Diese Datei ist eine Befehlsdatei mit den Befehlen SQL, die zeilenweise ausgeführt werden sollen
# Wenn „suivi“=True ist, wird bei jeder Ausführung eines Befehls SQL eine Meldung angezeigt, die angibt, ob der Befehl erfolgreich war oder fehlgeschlagen ist
# Wenn „Stopp“=True ist, bricht die Funktion beim ersten aufgetretenen Fehler ab, andernfalls führt sie alle SQL-Befehle aus
# Wenn with_transaction=True ist, führt jeder Fehler zur Rücknahme aller zuvor ausgeführten Befehle SQL
# Wenn with_transaction=False ist, hat ein Fehler keine Auswirkungen auf die zuvor ausgeführten Befehle SQL
# Die Funktion gibt eine Liste [erreur1, erreur2, ...] zurück
….
# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# nutzt die geöffnete Verbindung [connexion]
# führt über diese Verbindung die Befehle SQL aus, die in der Textdatei sql_filename enthalten sind
# Diese Datei ist eine Befehlsdatei mit Befehlen vom Typ SQL, die zeilenweise ausgeführt werden sollen
# Wenn „suivi“=True ist, wird bei jeder Ausführung eines Befehls SQL eine Meldung angezeigt, die angibt, ob der Befehl erfolgreich war oder fehlgeschlagen ist
# Wenn „Stopp“=True ist, bricht die Funktion beim ersten aufgetretenen Fehler ab, andernfalls führt sie alle SQL-Befehle aus
# Wenn with_transaction=True ist, führt jeder Fehler zur Rücknahme aller zuvor ausgeführten Befehle SQL
# Wenn with_transaction=False ist, hat ein Fehler keine Auswirkungen auf die zuvor ausgeführten Befehle SQL
# Die Funktion gibt eine Liste [erreur1, erreur2, ...] zurück
# Auswertung der Datei SQL
try:
# Datei zum Lesen öffnen
file = open(sql_filename, "r")
# Auswertung
return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
except BaseException as erreur:
# Es wird ein Fehlerarray zurückgegeben
return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]
Hinweise:
- Zeile 29: Die Funktion [execute_file_of_commands] führt die Befehle SQL aus, die in der Textdatei mit dem Namen [sql_filename] enthalten sind:
- Die Bedeutung der Parameter geht aus den Kommentaren in den Zeilen 31–38 hervor;
- Zeilen 40–48: Die Textdatei [sql_filename] wird verarbeitet;
- Zeile 43: Öffnen der Datei;
- Zeile 34: Ausführung der Funktion [execute_list_of_commands], die die Befehle SQL ausführt, die ihr in einer Liste übergeben werden. Diese Liste besteht hier aus der Liste aller Zeilen der Textdatei [file.readlines()] (Zeile 45);
Die Funktion [execute_list_of_commands] lautet wie folgt:
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# verwendet die geöffnete Verbindung [connexion]
# Führt über diese Verbindung die Befehle SQL aus, die in der Liste [sql_commands] enthalten sind
# Diese Datei ist eine Befehlsdatei mit den Befehlen SQL, die zeilenweise ausgeführt werden sollen
# Wenn „suivi“=True ist, wird bei jeder Ausführung eines Befehls SQL eine Meldung angezeigt, die angibt, ob der Befehl erfolgreich war oder fehlgeschlagen ist
# Wenn „Stopp“=True ist, bricht die Funktion beim ersten aufgetretenen Fehler ab, andernfalls führt sie alle SQL-Befehle aus
# Wenn with_transaction=True ist, führt jeder Fehler zur Rücknahme aller zuvor ausgeführten Befehle SQL
# Wenn with_transaction=False ist, hat ein Fehler keine Auswirkungen auf die zuvor ausgeführten Befehle SQL
# Die Funktion gibt eine Liste [erreur1, erreur2, ...] zurück
# Initialisierungen
curseur = None
connexion.autocommit = not with_transaction
erreurs = []
try:
# Es wird ein Schieberegler benötigt
curseur = connexion.cursor()
# Ausführung der in sql_commands enthaltenen SQL
# sie werden nacheinander ausgeführt
for command in sql_commands:
# Die Leerzeichen am Anfang und am Ende des aktuellen Befehls werden entfernt
command = command.strip()
# Handelt es sich um einen leeren Befehl oder einen Kommentar? Wenn ja, geht man zum nächsten Befehl über
if command == '' or command[0] == "#":
continue
# Ausführung des aktuellen Befehls
error = None
try:
curseur.execute(command)
except (InterfaceError, DatabaseError) as erreur:
error = erreur
# Ist ein Fehler aufgetreten?
if error:
# Ein weiterer Fehler
msg = f"{command} : Erreur ({error})"
erreurs.append(msg)
# Bildschirmüberwachung ja oder nein?
if suivi:
print(msg)
# Abbruch?
if with_transaction or arrêt:
# Die Fehlerliste ausgeben
return erreurs
else:
# kein Fehler
if suivi:
print(f"[{command}] : Exécution réussie")
# Das Ergebnis des Befehls anzeigen
afficher_infos(curseur)
# Das Fehlerarray wird zurückgegeben
return erreurs
finally:
# Cursor schließen
if curseur:
curseur.close()
# Die Transaktion wird bestätigt bzw. storniert, falls vorhanden
if with_transaction:
if erreurs:
# Stornierung
connexion.rollback()
else:
# Bestätigung
connexion.commit()
Anmerkungen
- Zeile 2: Die Funktion [execute_list_of_commands] führt die Befehle SQL aus, die in der Liste [sql_commands] enthalten sind:
- Die Bedeutung der Parameter geht aus den Kommentaren in den Zeilen 4–11 hervor;
- Zeile 2: Bei der empfangenen Verbindung handelt es sich um eine offene Verbindung zu einer Datenbank;
- Zeile 15: Wenn alle Befehle der Liste [sql_commands] innerhalb einer Transaktion ausgeführt werden sollen, muss im Modus AUTOCOMMIT=False gearbeitet werden. Andernfalls wird im Modus AUTOCOMMIT=True gearbeitet, und dann wird jeder Befehl aus der Liste [sqlCommands] innerhalb einer automatischen Transaktion ausgeführt, und es gibt keine übergreifende Transaktion;
- Zeile 19: Es wird ein Cursor angefordert, um die verschiedenen Befehle SQL auszuführen;
- Zeilen 22–51: Die Befehle werden nacheinander ausgeführt;
- Zeilen 26–27: Leerzeilen sowie Kommentare in der Befehlsliste SQL werden akzeptiert. In diesem Fall wird der Befehl einfach ignoriert;
- Zeilen 30–33: Ausführung der aktuellen Abfrage;
- Zeilen 35–45: Behandlung eines möglichen Ausführungsfehlers bei der aktuellen Abfrage;
- Zeilen 37–38: Der Fehler wird dem Fehlerarray hinzugefügt;
- Zeilen 40–41: Wenn eine Fehlerausgabe angefordert wurde, wird die Fehlermeldung angezeigt;
- Zeilen 43–45: Wenn der aufrufende Code nach dem ersten Fehler einen Abbruch angefordert hat oder die Verwendung einer Transaktion angefordert hat, muss der Vorgang abgebrochen werden. Das Fehlerarray wird zurückgegeben;
- Zeilen 46–51: Fall, in dem bei der Ausführung der aktuellen Abfrage kein Fehler aufgetreten ist;
- Zeilen 48–49: Wenn eine Nachverfolgung angefordert wurde, wird die ausgeführte Abfrage mit dem Vermerk „erfolgreich“ angezeigt;
- Zeilen 50–51: Das Ergebnis der ausgeführten Abfrage wird angezeigt. Wir werden etwas später noch einmal auf die Funktion [afficher_infos] zurückkommen;
- Zeilen 54–65: Die Klausel [finally] wird in jedem Fall ausgeführt, unabhängig davon, ob eine Ausnahme aufgetreten ist oder nicht;
- Zeilen 56–57: Schließen des Cursors. Dadurch werden die ihm zugewiesenen Ressourcen freigegeben;
- Zeilen 59–65: Es wird der Fall behandelt, in dem der aufrufende Code angefordert hat, dass die Befehle SQL in einer Transaktion ausgeführt werden;
- Zeile 60: Es wird geprüft, ob die Liste [erreurs] leer ist, was bedeutet, dass keine Ausnahme aufgetreten ist. In diesem Fall wird die Transaktion bestätigt (Zeile 65), andernfalls wird sie abgebrochen (Zeile 62);
Die Funktion [afficher_infos] zeigt das Ergebnis einer Abfrage an:
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
print(type(curseur))
# Zeigt das Ergebnis eines SQL-Befehls an
# Handelte es sich um eine SELECT-Anweisung?
if curseur.description:
# Der Cursor hat eine Beschreibung – er hat also eine SELECT-Anweisung ausgeführt
# Beschreibung[i] ist die Beschreibung der Spalte Nr. i der SELECT-Anweisung
# BeschreibungQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX ist der Name der Spalte Nr. i der SELECT-Anweisung
# Es werden die Feldnamen angezeigt
titre = ""
for i in range(len(curseur.description)):
titre += curseur.description[i][0] + ", "
# Es wird die Liste der Felder ohne abschließendes Komma angezeigt
print(titre[0:len(titre) - 1])
# Trennzeile
print("*" * (len(titre) - 1))
# aktuelle Zeile der Auswahl
ligne = curseur.fetchone()
while ligne:
# wird angezeigt
print(ligne)
# nächste Zeile des Auswahlfelds
ligne = curseur.fetchone()
# Trennzeile
print("*" * (len(titre) - 1))
else:
# Der Cursor hat kein Feld [description] – er hat also einen Befehl SQL ausgeführt
# Aktualisierungsbefehl (Einfügen, Löschen, Aktualisieren)
print(f"nombre de lignes modifiées : {curseur.rowcount}")
Hinweise
- Zeile 1: Der Parameter der Funktion ist der Cursor, der gerade einen Befehl SQL ausgeführt hat. Je nachdem, ob es sich bei diesem Befehl um einen Befehl vom Typ SELECT oder um einen Aktualisierungsbefehl vom Typ INSERT, UPDATE oder DELETE handelt, ist der Inhalt des Cursors unterschiedlich;
- Zeile 6: Wenn der Cursor das Feld [description] enthält, dann hat er einen Befehl SELECT ausgeführt, und [description] beschreibt die im Befehl SELECT angeforderten Felder:
- description[i] beschreibt das von SELECT angeforderte Feld Nr. i. Es handelt sich um eine Liste;
- description[i][0] ist der Name des Feldes Nr. i;
- Zeilen 11–17: Es werden die Namen der von SELECT angeforderten Felder angezeigt;
- Zeilen 18–24: Das Ergebnis von SELECT wird ausgewertet;
- Zeilen 20, 24: Das Ergebnis eines SELECT wird sequenziell ausgewertet. Dieses Ergebnis besteht aus einer Reihe von Zeilen. Die aktuelle Zeile wird durch [curseur.fetchone()] (Zeile 19) ermittelt. Man erhält dann ein Tupel;
- Zeilen 27–30: Wenn der Cursor das Feld [description] nicht enthält, dann hat er einen Aktualisierungsbefehl INSERT, UPDATE, DELETE ausgeführt. Man kann dann feststellen, wie viele Zeilen der Tabelle durch die Ausführung dieses Befehls geändert wurden;
- Zeile 30: [curseur.rowcount] ist diese Zahl;
Das Hauptskript [mysql-04] nutzt das soeben beschriebene Modul [mysql_module]:
Die Datei [config_04] konfiguriert den Ausführungskontext des Skripts [mysql_04]:
def configure():
import os
# Absoluter Pfad zum Ordner der Konfigurationsdatei
script_dir = os.path.dirname(os.path.abspath(__file__))
# Konfiguration der Syspath-Ordner
absolute_dependencies = [
# lokale Ordner
f"{script_dir}/shared",
]
# Festlegen des Syspath
from myutils import set_syspath
set_syspath(absolute_dependencies)
# Konfiguration wird übernommen
return {
# Befehlsdatei SQL
"commands_filename": f"{script_dir}/data/commandes.sql",
# Anmeldedaten für die Datenbankverbindung
"host": "localhost",
"database": "dbpersonnes",
"user": "admpersonnes",
"password": "nobody"
}
Das Skript [mysql_04] lautet wie folgt:
# die Anwendungskonfiguration wird abgerufen
import config_04
config = config_04.configure()
# Der Syspath ist konfiguriert – die Importe können durchgeführt werden
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError
# ---------------------------------------------- main
# Überprüfung der Syntax des Aufrufs
# argv[0] true / false
args = sys.argv
erreur = len(args) != 2
if not erreur:
with_transaction = args[1].lower()
erreur = with_transaction != "true" and with_transaction != "false"
# Fehler?
if erreur:
print(f"syntaxe : {args[0]} true / false")
sys.exit()
# Berechnung eines Textes
with_transaction = with_transaction == "true"
if with_transaction:
texte = "avec transaction"
else:
texte = "sans transaction"
# Bildschirmprotokolle
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")
# Ausführung der Befehle SQL aus der Datei
connexion = None
try:
# Verbindung zur Datenbank
connexion = connect(host=config['host'], user=config['user'], password=config['password'],
database=config['database'])
# Ausführung der Befehlsdatei SQL
erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
# Anzeige des Fehlers
print(f"L'erreur fatale suivante s'est produite : {erreur}")
# Beendigung
sys.exit()
finally:
# Schließen der Verbindung, falls diese geöffnet wurde
if connexion:
connexion.close()
# Anzahl der Fehler anzeigen
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# Anzeige der Fehler
for erreur in erreurs:
print(erreur)
Hinweise
- Zeilen 1–4: Konfiguration des Skripts;
- Zeile 8: Import des zuvor beschriebenen Moduls [mysql_module]:
- Zeilen 12–22: Das Skript [mysql-04] erwartet einen Parameter, der einen der Werte [true / false] annehmen muss. Dieser Parameter gibt an, ob die Befehlsdatei SQL innerhalb einer Transaktion (true) oder nicht (false) ausgeführt werden soll;
- Zeile 14: Die vom Benutzer an das Skript übergebenen Parameter sind in der Liste [sys.argv] zu finden;
- Zeile 15: Es sind zwei Parameter erforderlich, zum Beispiel [mysql-04 true]. Der Name des Skripts zählt als ein Parameter;
- Zeilen 17–18: Wenn tatsächlich zwei Parameter vorhanden sind, muss der zweite ein Zeichenfolgenwert mit dem Wert „true“ oder „false“ sein;
- Zeilen 24–29: Berechnung eines Textes, der in Zeile 33 angezeigt wird;
- Zeilen 39–44: Die Befehle der Datei [./data/commandes.sql] werden ausgeführt;
- Zeilen 45–49: Tritt bei der Verbindung (Zeile 40) ein Fehler auf oder wird dieser vom Skript [execute_file_of_commands] nicht behandelt, wird der Fehler angezeigt und der gesamte Vorgang abgebrochen;
- Zeilen 55–62: Bei erfolgreicher Ausführung wird die Anzahl der Fehler angezeigt, die bei der Ausführung der Befehle aus der Datei „SQL“ aufgetreten sind;
Ausführung Nr. 1
Zunächst wird ein Lauf ohne Transaktion durchgeführt. Dazu erstellen wir eine Ausführungskonfiguration, wie im Abschnitt |Konfiguration eines Ausführungskontexts| beschrieben:
- In [1-4] erstellen wir eine Python-Ausführungskonfiguration;
- [5]: Name der Ausführungskonfiguration;
- [6]: Pfad zum auszuführenden Skript;
- [7]: Parameter des Skripts;
- [8]: Ausführungsordner;
Diese Konfiguration entspricht somit einer Ausführung der Datei SQL mit einer Transaktion. Verwenden Sie die Schaltfläche [Apply], um die Konfiguration zu bestätigen.
Auf die gleiche Weise erstellen wir die Ausführungskonfiguration [mysql mysql-04 without_transaction]:
Diese Konfiguration entspricht somit einer Ausführung der Datei SQL ohne Transaktion. Verwenden Sie die Schaltfläche [Apply], um die Konfiguration zu bestätigen.
Wir führen zunächst die Version ohne Transaktion aus:
Die Ergebnisse lauten dann wie folgt:
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : 0
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
[insert into personnes(id, prenom, nom, age) values (3, 'Pierre','Nicazou',35)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (4, 'Geraldine','Colou',26)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (5, 'Paulette','Girond',56)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
('Pierre', 'Nicazou', 35)
('Geraldine', 'Colou', 26)
('Paulette', 'Girond', 56)
*****************
[select nom,prenom from personnes order by nom asc, prenom desc] : Exécution réussie
nom, prenom,
************
('Colou', 'Geraldine')
('Girond', 'Paulette')
('Langevin', 'Paul')
('Lefur', 'Sylvie')
('Nicazou', 'Pierre')
************
[select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc] : Exécution réussie
nom, prenom, age,
*****************
('Nicazou', 'Pierre', 35)
('Colou', 'Geraldine', 26)
*****************
[insert into personnes(id, prenom, nom, age) values(6, 'Josette','Bruneau',46)] : Exécution réussie
nombre de lignes modifiées : 1
[update personnes set age=47 where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
('Bruneau', 'Josette', 47)
*****************
[delete from personnes where nom='Bruneau'] : Exécution réussie
nombre de lignes modifiées : 1
[select nom,prenom,age from personnes where nom='Bruneau'] : Exécution réussie
nom, prenom, age,
*****************
*****************
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
Process finished with exit code 0
Anmerkungen:
- Zeile 19: Man sieht, dass nach dem Fehler die Ausführung der Befehle SQL fortgesetzt wurde, da die Ausführung ohne Transaktion und mit dem Parameter [arrêt=False] erfolgte. Alle Befehle SQL wurden somit ausgeführt. Es sollte daher eine Tabelle [personnes] vorhanden sein, die diese Ausführung widerspiegelt;
Überprüfung mit phpMyAdmin:
Ausführung Nr. 2
Wir führen nun die Konfiguration [mysql mysql-04 with_transaction] aus. Die Ergebnisse lauten wie folgt:
C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/mysql/mysql_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[create table personnes (id int primary key, prenom varchar(30) not null, nom varchar(30) not null, age integer not null, unique (nom,prenom))] : Exécution réussie
nombre de lignes modifiées : 0
[insert into personnes(id, prenom, nom, age) values(1, 'Paul','Langevin',48)] : Exécution réussie
nombre de lignes modifiées : 1
[insert into personnes(id, prenom, nom, age) values (2, 'Sylvie','Lefur',70)] : Exécution réussie
nombre de lignes modifiées : 1
[select prenom, nom, age from personnes] : Exécution réussie
prenom, nom, age,
*****************
('Paul', 'Langevin', 48)
('Sylvie', 'Lefur', 70)
*****************
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
Process finished with exit code 0
Anmerkungen:
- Zeile 19: Es ist zu erkennen, dass nach dem Fehler keine Befehle der Konfiguration SQL mehr ausgeführt wurden. Dies liegt daran, dass die Ausführung in einer Transaktion erfolgte und wir beim ersten aufgetretenen Fehler die Transaktion rückgängig gemacht und die Ausführung der Befehle der Konfiguration SQL abgebrochen haben. Das bedeutet, dass das Ergebnis der Befehle in den Zeilen 9, 11 und 13 rückgängig gemacht wurde. Die Tabelle [personnes] sollte daher leer sein;
Überprüfung mit phpMyAdmin:
- In [5] ist zu sehen, dass die Tabellen [personnes] und [2] leer sind;
16.7. Skript [mysql_05]: Verwendung von parametrisierten Abfragen
Das Skript [mysql_05] führt das Konzept der parametrisierten Abfragen ein:
# Importe
from mysql.connector import connect, DatabaseError, InterfaceError
# Benutzeridentität
ID = "admpersonnes"
PWD = "nobody"
# Host-Rechner des DBMS
HOST = "localhost"
# Datenbank-ID
BASE = "dbpersonnes"
# Personenliste (Nachname, Vorname, Alter)
personnes = []
for i in range(5):
personnes.append((i, f"n0{i}", f"p0{i}", i + 10))
personnes.append((40, "d'Aboot", "Y'éna", 18))
# weitere Personenliste
autresPersonnes = []
for i in range(5):
autresPersonnes.append((i + 100, f"n1{i}", f"p1{i}", i + 20))
autresPersonnes.append((200, "d'Aboot", "F'ilhem", 34))
# Zugriff auf SGBD
connexion = None
try:
# Anmeldung
connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
# Cursor
curseur = connexion.cursor()
# Löschen bestehender Datensätze
curseur.execute("delete from personnes")
# Einfügen einzelner Personen mit einer vorbereiteten Abfrage
for personne in personnes:
curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
# Massen-Einfügung einer Personenliste
curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
# Transaktionsfreigabe
connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
# Fehleranzeige
print(f"L'erreur suivante s'est produite : {erreur}")
# Transaktion stornieren
if connexion:
connexion.rollback()
finally:
# Verbindung beenden
if connexion:
connexion.close()
Anmerkungen
- Zeilen 12–21: Es werden zwei Listen mit Personen erstellt, die in die Datenbank [dbpersonnes] aufgenommen werden sollen;
- Zeile 27: Verbindung zur Datenbank;
- Zeile 31: Löschen des Inhalts der Tabelle [personnes];
- Zeilen 33–34: Einfügen von Personen mit einer parametrisierten Abfrage. Zeile 34: Der erste Parameter ist der auszuführende Befehl SQL. Dieser ist unvollständig. Er enthält Parameter von [%s], die nacheinander und in der angegebenen Reihenfolge durch die Werte aus der Liste des zweiten Parameters ersetzt werden;
- Zeile 36: Einfügen von Personen, diesmal mit einer einzigen Anweisung [curseur.executemany]. Der zweite Parameter von [executemany] ist dann eine Liste von Listen;
Der Vorteil parametrisierter Abfragen liegt in zwei Punkten:
- Sie werden schneller ausgeführt als „feste“ Abfragen, die bei jeder Ausführung analysiert werden müssen. Die parametrisierte Abfrage [executemany] wird nur einmal analysiert. Anschließend wird sie n-mal ausgeführt, ohne erneut analysiert zu werden;
- Die in die parametrisierte Abfrage eingefügten Parameter werden überprüft. Wenn sie reservierte Zeichen enthalten, wie beispielsweise das Apostroph, werden diese „geschützt“, damit sie die Ausführung des Befehls SQL nicht beeinträchtigen. Um diesen Punkt zu überprüfen, wurden Vor- und Nachnamen mit Apostrophen in die Liste aufgenommen (Zeilen 16 und 21);
Die in phpMyAdmin erzielten Ergebnisse lauten wie folgt:
- Es ist zu beachten, dass die Zeichenfolgen mit einem Apostroph – einem in SQL reservierten Zeichen – korrekt eingefügt wurden. Die parametrisierte Abfrage hat sie „geschützt“. Ohne parametrisierte Abfrage hätten wir diese Arbeit selbst erledigen müssen;