Skip to content

16. Verwendung des MySQL-DBMS

Image

16.1. Installation des MySQL-Datenbankmanagementsystems

Um das MySQL-DBMS zu nutzen, installieren wir die Laragon-Software.

16.1.1. Installation von Laragon

Laragon ist ein Paket, das mehrere Softwarekomponenten vereint:

  • einen Apache-Webserver. Wir werden ihn zum Schreiben von Webskripten in Python verwenden;
  • das MySQL-DBMS;
  • die Skriptsprache PHP, die wir nicht verwenden werden;
  • einen Redis-Server, der einen Cache für Webanwendungen bereitstellt. Wir werden ihn nicht verwenden;

Laragon kann (Stand: Februar 2020) unter folgender Adresse heruntergeladen werden:


https://laragon.org/download/

Image

Image

  • Die Installation [1-5] führt zu folgender Verzeichnisstruktur:

Image

  • in [6] der PHP-Installationsordner (wird in diesem Dokument nicht verwendet);

Beim Starten von [Laragon] wird das folgende Fenster angezeigt:

Image

  • [1]: das Laragon-Hauptmenü;
  • [2]: Die Schaltfläche [Start All] startet den Apache-Webserver und die MySQL-Datenbank;
  • [3]: Die Schaltfläche [WEB] zeigt die Webseite [http://localhost] an;
  • [4]: Über die Schaltfläche [Database] können Sie das MySQL-Datenbankmanagementsystem mit dem Tool [phpMyAdmin] verwalten. Dieses Tool müssen Sie zuvor installieren;
  • [5]: Die Schaltfläche [Terminal] öffnet ein Befehlsterminal;
  • [6]: Die Schaltfläche [Root] öffnet ein Windows-Explorer-Fenster im Ordner [<laragon>/www], dem Stammverzeichnis der Website [http://localhost]. Hier sollten Sie die statischen Webanwendungen ablegen, die vom Apache-Server von Laragon verwaltet werden;

16.1.2. Erstellen einer Datenbank

Wir zeigen Ihnen nun, wie Sie mit dem Laragon-Tool eine Datenbank und einen MySQL-Benutzer erstellen.

Image

  • Nach dem Start kann Laragon [1] über ein Menü [2] verwaltet werden;
  • Installieren Sie in [3-5] das MySQL-Verwaltungstool [phpMyAdmin], falls es noch nicht installiert ist; Image
  • Starten Sie in [6] den Apache-Webserver und das MySQL-Datenbankverwaltungssystem;
  • In [7] wird der Apache-Server gestartet;
  • In [8] wird das MySQL-Datenbankmanagementsystem gestartet;

Image

  • Erstellen Sie in [8-10] eine Datenbank mit dem Namen [dbpersonnes] [11]. Wir werden eine Personendatenbank aufbauen;

Image

  • In [11] werden wir die soeben erstellte Datenbank verwalten;

Image

  • Der Vorgang [Datenbanken] sendet eine Webanfrage an die URL [http://localhost/phpmyadmin] [12]. Der Laragon-Apache-Webserver antwortet darauf. Die URL [http://localhost/phpmyadmin] ist die Adresse des Dienstprogramms [phpMyAdmin], das wir zuvor installiert haben [5]. Mit diesem Dienstprogramm können Sie MySQL-Datenbanken verwalten;
  • standardmäßig lauten die Anmeldedaten des Datenbankadministrators: root [13] ohne Passwort [14];

Image

  • in [16] die Datenbank, die wir zuvor erstellt haben;

Image

  • Derzeit haben wir eine Datenbank [dbpersonnes] [17], die leer ist [18];

Wir erstellen einen Benutzer [admpersonnes] mit dem Passwort [nobody], der über alle Rechte für die Datenbank [dbpersonnes] verfügt:

Image

  • In [19] befinden wir uns in der Datenbank [dbpersonnes];
  • In [20] wählen wir die Registerkarte [Privileges] aus;
  • in [21-22] sehen wir, dass der Benutzer [root] volle Berechtigungen für die Datenbank [dbpersonnes] hat;
  • in [23] erstellen wir einen neuen Benutzer;

Image

  • in [25-26] erhält der Benutzer den Benutzernamen [admdbpersonnes];
  • in [27-29] lautet sein Passwort [nobody];
  • in [30] warnt phpMyAdmin, dass das Passwort sehr schwach (leicht zu knacken) ist. In der Produktion ist es vorzuziehen, mit [31] ein starkes Passwort zu generieren;
  • in [32] legen wir fest, dass der Benutzer [admdbpersonnes] volle Berechtigungen für die Datenbank [dbpersonnes] haben muss;
  • in [33] bestätigen wir die eingegebenen Informationen;

Image

  • in [35] zeigt phpMyAdmin an, dass der Benutzer erstellt wurde;
  • In [36] die SQL-Abfrage, die auf der Datenbank ausgeführt wurde;
  • In [37] hat der Benutzer [admpersonnes] volle Berechtigungen für die Datenbank [dbpersonnes];

Nun haben wir:

  • eine MySQL-Datenbank [dbpersonnes];
  • einen Benutzer [admpersonnes/nobody], der über alle Berechtigungen für diese 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:

Image

Ein Konnektor dient dazu, den Python-Code vom verwendeten DBMS zu trennen. Es gibt Konnektoren für verschiedene DBMS, die alle derselben Schnittstelle folgen. Wenn wir also im obigen Beispiel das MySQL-DBMS durch das PostgreSQL-DBMS ersetzen, sieht die Architektur wie folgt aus:

Image

Da alle DBMS-Konnektoren dieselbe Schnittstelle verwenden, muss das Python-Skript in der Regel nicht geändert werden. In der Praxis verwenden die meisten DBMS proprietäres SQL:

  • sie entsprechen dem SQL-Standard (Structured Query Language);
  • erweitern ihn jedoch – da er allein nicht ausreicht – um proprietäre Spracherweiterungen;

Daher ist es üblich, dass bei einem Wechsel des DBMS SQL-Änderungen an den Skripten vorgenommen werden müssen.

Standardmäßig bietet Python keine Möglichkeit, eine MySQL-Datenbank zu verwalten. Dazu müssen Sie ein Paket herunterladen. Es stehen mehrere zur Verfügung. Hier verwenden wir das Paket [mysql-connector-python], den offiziellen Connector von Oracle, dem Unternehmen, dem MySQL gehört.

Das Paket wird in einem Pycharm-[Terminal]-Fenster installiert:

Image

  • Das Verzeichnis in [2] ist für den weiteren Verlauf irrelevant;

Geben Sie im Terminal den Befehl [pip search MySQL] ein:

  • [pip] (Package Installer for Python) ist das Tool zur Installation von Python-Paketen. Das [pip]-Tool stellt eine Verbindung zum Repository her, das Python-Pakete enthält;
  • [search MySQL]: ruft eine Liste von Paketen ab, deren Namen den Begriff [MySQL] enthalten (Groß-/Kleinschreibung wird nicht berücksichtigt);

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>

Alle Module, deren Name oder Beschreibung das Stichwort MySQL enthält, wurden aufgelistet. Dasjenige, das wir verwenden werden (Februar 2020), ist [mysql-connector-python], Zeile 17. Um es zu installieren, geben Sie den Befehl [pip install -U mysql-connector-python] im Terminal 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 neueste Version der verschiedenen Pakete an, die mit dem Paket [mysql-connector-python] verbunden sind;

Um zu sehen, welche Pakete in der Python-Umgebung unseres Rechners installiert sind, geben Sie 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: Wir haben das Paket [mysql-connector-python];

Informationen zur Verwendung des Pakets [mysql-connector-python] zur Verwaltung einer MySQL-Datenbank finden Sie auf der Website des Pakets |https://dev.mysql.com/doc/connector-python/en/|. Im folgenden Abschnitt werden eine Reihe von Beispielen vorgestellt.

16.3. Skript [mysql_01]: Verbindung zu einer MySQL-Datenbank herstellen – 1

Das Skript [mysql_01] veranschaulicht den ersten Schritt bei der Verwendung einer Datenbank. Damit können wir überprüfen, ob wir eine Verbindung zur zuvor erstellten Datenbank [dbpersonnes] herstellen können.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
#  import module mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

#  connection to a MySql [dbpersonnes] database
#  user identity is (admpersonnes,nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

#  here we go
connexion = None
try:
    print("Connexion au SGBD MySQL en cours...")
    #  connection
    connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
    #  follow-up
    print(
        f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    #  close the connection if it has been opened
    if connexion:
        connexion.close()

Anmerkungen

  • Zeile 2: Importieren bestimmter 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 MySQL-Connector ermöglicht es Ihnen, mit einer Remote-Datenbank zu arbeiten;
  • Zeile 9: Der Name der Datenbank, mit der wir uns verbinden möchten;
  • 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 dies in einem try/except/finally-Block behandelt;
  • Zeile 16: Die connect-Methode des Moduls [mysq.connector] akzeptiert verschiedene benannte Parameter:
    • user: der Benutzer, dem die Verbindung gehört [admpersonnes];
    • password: Benutzerkennwort [nobody];
    • host: MySQL-DBMS-Rechner [localhost];
    • database: die Datenbank, zu der eine Verbindung hergestellt werden soll. Optional.
  • Zeile 20: Wenn eine Ausnahme ausgelöst wird, ist sie vom Typ [DatabaseError] oder [InterfaceError];
  • Zeilen 23–26: In der [finally]-Klausel wird die Verbindung geschlossen;

Ergebnisse

1
2
3
4
5
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_01.py
Connexion au SGBD MySQL en cours...
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody

Process finished with exit code 0

16.4. Skript [mysql_02]: Verbindung zu einer MySQL-Datenbank – 2

In diesem neuen Skript ist die Datenbankverbindung in einer Funktion gekapselt:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
#  import module mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    #  connects then disconnects (login,pwd) from the [database] server [host]
    #  throws DatabaseError exception if problem occurs
    connexion = None
    try:
        #  connection
        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:
        #  close the connection if it has been opened
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


#  ---------------------------------------------- main
#  login credentials
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

#  existing user login
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(erreur)

#  non-existent user login
try:
    connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(erreur)

Anmerkungen:

  • Zeilen 6–19: Eine [connection]-Funktion, die versucht, eine Verbindung zur Datenbank [dbpersonnes] herzustellen und diese anschließend wieder zu trennen. Zeigt das Ergebnis an;
  • Zeilen 29–41: Hauptprogramm – ruft die Verbindungsmethode zweimal auf und zeigt etwaige Ausnahmen an;

Ergebnisse

1
2
3
4
5
6
7
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_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

1045 (28000): Access denied for user 'xx'@'localhost' (using password: YES)

Process finished with exit code 0

16.5. Skript [mysql_03]: Erstellen einer MySQL-Tabelle

Da wir nun wissen, wie man eine Verbindung zu einem MySQL-DBMS herstellt, können wir damit beginnen, über diese Verbindung SQL-Befehle auszuführen. Dazu verbinden wir uns mit der erstellten Datenbank [dbpersonnes] und nutzen die Verbindung, um eine Tabelle in der Datenbank zu erstellen.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
#  imports
import sys

from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection


# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
    #  executes an update request on the
    curseur = None
    try:
        #  a cursor is requested
        curseur = connexion.cursor()
        #  executes the update request on the
        curseur.execute(update)
    finally:
        #  close cursor if obtained
        if curseur:
            curseur.close()


#  ---------------------------------------------- main
#  login credentials
#  user identity
ID = "admpersonnes"
PWD = "nobody"
#  the sgbd host machine
HOST = "localhost"
#  base identity
DATABASE = "dbpersonnes"

#  step by step
try:
    #  connection
    connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
    #  mode AUTOCOMMIT
    connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"L'erreur suivante s'est produite : {erreur}")
    #  we leave
    sys.exit()

#  delete the people table if it exists
#  if it doesn't exist, an error will occur - we ignore it
requête = "drop table personnes"
try:
    execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
    pass

#  create people table
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:
    #  request execution
    execute_sql(connexion, requête)
    #  display
    print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    #  we disconnect
    connexion.close()

Anmerkungen:

  • Zeile 9: Die Funktion `execute_sql` führt eine SQL-Abfrage auf einer offenen Verbindung aus;
  • Zeile 14: SQL-Operationen auf der Verbindung werden über ein spezielles Objekt namens Cursor ausgeführt;
  • Zeile 14: Abrufen eines Cursors;
  • Zeile 16: Ausführung der SQL-Abfrage;
  • Zeilen 17–20: Unabhängig davon, ob ein Fehler vorliegt oder nicht, wird der Cursor geschlossen. Dadurch werden die damit verbundenen Ressourcen freigegeben. Tritt eine Ausnahme auf, wird diese hier nicht behandelt. Sie wird an den aufrufenden Code weitergeleitet;
  • Zeilen 33–43: Herstellen einer Verbindung zur Datenbank;
  • Zeile 38: Die Einstellung AUTOCOMMIT=True für eine Verbindung bedeutet, dass jede Abfrageausführung innerhalb einer automatischen Transaktion läuft. 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 „zurückgesetzt“, sodass die Datenbank in den Zustand zurückkehrt, in dem sie sich vor der Ausführung von Abfrage 1 befand;
  • Hier gibt es zwei SQL-Abfragen (Zeilen 49, 58). Jede wird innerhalb einer Transaktion ausgeführt. Die Tatsache, dass die zweite fehlschlägt, hat keine Auswirkungen auf die erste;
  • Zeilen 45–51: Die SQL-Anweisung [drop table people] wird ausgeführt. Sie löscht die Tabelle mit dem Namen [people]. Wenn die Tabelle nicht existiert, wird möglicherweise ein Fehler gemeldet. Dieser Fehler wird ignoriert (Zeile 51);
  • Zeilen 53–55: Der Befehl zum Anlegen der Tabelle [people]. Eine Tabelle kann als eine Reihe von Zeilen und Spalten betrachtet werden. Der Anlegebefehl gibt die Spaltennamen 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 innerhalb der Tabelle niemals zweimal denselben Wert aufweist und zur Identifizierung einer Person verwendet werden kann;
    • [last_name]: eine Zeichenkette mit bis zu 30 Zeichen;
    • [last_name]: eine Zeichenkette mit bis zu 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(last_name, first_name)] wird als Einschränkung bezeichnet. Hier besteht die Einschränkung für die Zeilen darin, dass das Tupel (last_name, first_name) in der Zeile innerhalb der Tabelle eindeutig sein muss. Das bedeutet, dass wir eine Person in der Tabelle, deren Nachname und Vorname bekannt sind, eindeutig identifizieren können;
  • Zeilen 56–60: Ausführung der SQL-Anweisung;
  • Zeilen 61–63: Behandlung etwaiger Ausnahmen;
  • Zeilen 64–66: Trennen der Verbindung zur Datenbank;

Ergebnisse

1
2
3
4
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_03.py
create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, unique(nom,prenom))  : requête réussie

Process finished with exit code 0

Überprüfung mit [phpMyAdmin]:

Image

  • Die Datenbank [dbpersonnes] [1] enthält eine Tabelle [personnes] [2] mit der Struktur [3-4], dem Primärschlüssel [5] und der Eindeutigkeitsbeschränkung [6];

16.6. Skript [mysql_04]: Ausführen einer SQL-Befehlsdatei

Nachdem wir zuvor die Tabelle [personnes] erstellt haben, füllen wir sie nun und fragen sie anschließend mit SQL-Anweisungen ab.

Wir möchten die SQL-Anweisungen aus einer Textdatei ausführen:

Image

Der Inhalt der Datei [commands.sql] lautet wie folgt:


# suppression de la table [personnes]
drop table personnes
# création de la table personnes
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# insertion de deux personnes
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# affichage de la table
select prenom, nom, age from personnes
# erreur volontaire
xx
# insertion de trois personnes
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)
# affichage de la table
select prenom, nom, age from personnes
# liste des personnes par ordre alphabétique des noms et à nom égal par ordre alphabétique des prénoms
select nom,prenom from personnes order by nom asc, prenom desc
# liste des personnes ayant un âge dans l'intervalle [20,40] par ordre décroissant de l'âge
# puis à âge égal par ordre alphabétique des noms et à nom égal par ordre alphabétique des prénoms
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# insertion de mme Bruneau
insert into personnes(prenom, nom, age) values('Josette','Bruneau',46)
# mise à jour de son âge
update personnes set age=47 where nom='Bruneau'
# liste des personnes ayant Bruneau pour nom
select nom,prenom,age from personnes where nom='Bruneau'
# suppression de Mme Bruneau
delete from personnes where nom='Bruneau'
# liste des personnes ayant Bruneau pour nom
select nom,prenom,age from personnes where nom='Bruneau'

Zunächst definieren wir Funktionen, die wir in einem Modul ablegen, damit wir sie wiederverwenden können:

Image

Das Skript [mysql_module] sieht wie folgt aus:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#  imports
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
    #  displays the result of an sql command
    …


# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #  uses the open connection [connection]
    #  executes the SQL commands contained in the [sql_commands] list on this connection
    #  this is a file of SQL commands to be executed one per line
    #  if followed=True then each execution of a SQL order is displayed, indicating success or failure
    #  if stop=True, the function stops on the 1st error encountered, otherwise it executes all sql commands
    #  if with_transaction=True then any error cancels all previously executed SQL orders
    #  if with_transaction=False then an error has no impact on previously executed SQL orders
    #  the function returns a list [error1, error2, ...]

    ….


# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #  uses the open connection [connection]
    #  executes the SQL commands contained in the sql_filename text file on this connection
    #  this is a file of SQL commands to be executed one per line
    #  if followed=True then each execution of a SQL order is displayed, indicating success or failure
    #  if stop=True, the function stops on the 1st error encountered, otherwise it executes all sql commands
    #  if with_transaction=True then any error cancels all previously executed SQL orders
    #  if with_transaction=False then an error has no impact on previously executed SQL orders
    #  the function returns a list [error1, error2, ...]

    #  use of the SQL file
    try:
        #  open file for reading
        file = open(sql_filename, "r")
        #  operation
        return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
    except BaseException as erreur:
        #  an error table is returned
        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 SQL-Befehle aus, die in der Textdatei mit dem Namen [sql_filename] enthalten sind:
  • siehe die Kommentare in den Zeilen 31–38 zur Bedeutung der Parameter;
  • 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 ihr in einer Liste übergebenen SQL-Befehle ausführt. Diese Liste besteht hier aus allen Zeilen der Textdatei [file.readlines()] (Zeile 45);

Die Funktion [execute_list_of_commands] sieht wie folgt aus:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    #  uses the open connection [connection]
    #  executes the SQL commands contained in the [sql_commands] list on this connection
    #  this is a file of SQL commands to be executed one per line
    #  if followed=True then each execution of a SQL order is displayed, indicating success or failure
    #  if stop=True, the function stops on the 1st error encountered, otherwise it executes all sql commands
    #  if with_transaction=True then any error cancels all previously executed SQL orders
    #  if with_transaction=False then an error has no impact on previously executed SQL orders
    #  the function returns a list [error1, error2, ...]

    #  initializations
    curseur = None
    connexion.autocommit = not with_transaction
    erreurs = []
    try:
        #  a cursor is requested
        curseur = connexion.cursor()
        #  execution of sql_commands SQL contained in sql_commands
        #  they are executed one by one
        for command in sql_commands:
            #  eliminates blanks at the beginning and end of the current command
            command = command.strip()
            #  is there an empty command or a comment? If so, move on to the next command
            if command == '' or command[0] == "#":
                continue
            #  execute current command
            error = None
            try:
                curseur.execute(command)
            except (InterfaceError, DatabaseError) as erreur:
                error = erreur
            #  was there a mistake?
            if error:
                #  one more mistake
                msg = f"{command} : Erreur ({error})"
                erreurs.append(msg)
                #  screen tracking or not?
                if suivi:
                    print(msg)
                #  shall we stop?
                if with_transaction or arrêt:
                    #  return the error list
                    return erreurs
            else:
                #  no error
                if suivi:
                    print(f"[{command}] : Exécution réussie")
                #  displays the result of the command
                afficher_infos(curseur)
        #  return the error table
        return erreurs
    finally:
        #  closing the cursor
        if curseur:
            curseur.close()
        #  validate / cancel the transaction if it exists
        if with_transaction:
            if erreurs:
                #  cancellation
                connexion.rollback()
            else:
                #  validation
                connexion.commit()

Hinweise

  • Zeile 2: Die Funktion [execute_list_of_commands] führt die in der Liste [sql_commands] enthaltenen SQL-Befehle aus:
  • Die Bedeutung der Parameter entnehmen Sie bitte den Kommentaren in den Zeilen 4–11;
  • Zeile 2: Die empfangene Verbindung ist eine offene Verbindung zu einer Datenbank;
  • Zeile 15: Wenn Sie möchten, dass alle Befehle in der Liste [sql_commands] innerhalb einer Transaktion ausgeführt werden, müssen Sie im Modus AUTOCOMMIT=False arbeiten. Andernfalls arbeiten Sie im Modus AUTOCOMMIT=True, und jeder Befehl in der Liste [sql_commands] wird innerhalb einer automatischen Transaktion ausgeführt, wobei keine globale Transaktion besteht;
  • Zeile 19: Es wird ein Cursor angefordert, um die verschiedenen SQL-Befehle auszuführen;
  • Zeilen 22–51: Die Befehle werden nacheinander ausgeführt;
  • Zeilen 26–27: Wir akzeptieren Leerzeilen und Kommentare in der Liste der SQL-Befehle. In diesem Fall ignorieren wir den Befehl einfach;
  • Zeilen 30–33: Führen Sie die aktuelle Abfrage aus;
  • Zeilen 35–45: Behandlung eines möglichen Laufzeitfehlers in der aktuellen Abfrage;
  • Zeilen 37–38: Der Fehler wird der Fehlertabelle hinzugefügt;
  • Zeilen 40–41: Wenn die Protokollierung aktiviert ist, wird die Fehlermeldung angezeigt;
  • Zeilen 43–45: Wenn der aufrufende Code nach dem ersten Fehler einen Stopp oder die Verwendung einer Transaktion angefordert hat, muss das Programm beendet werden. Das Fehlerarray wird zurückgegeben;
  • Zeilen 46–51: Fall, in dem bei der aktuellen Abfrage kein Ausführungsfehler aufgetreten ist;
  • Zeilen 48–49: Wenn eine Nachverfolgung angefordert wurde, wird die ausgeführte Abfrage mit der Bezeichnung „successful“ angezeigt;
  • Zeilen 50–51: Anzeige des Ergebnisses der ausgeführten Abfrage. Wir werden etwas später auf die Funktion [display_info] zurückkommen;
  • Zeilen 54–65: Die [finally]-Klausel wird in allen Fällen 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: Wir behandeln den Fall, in dem der aufrufende Code angefordert hat, dass die SQL-Befehle innerhalb einer Transaktion ausgeführt werden;
  • Zeile 60: Wir prüfen, ob die Liste [errors] leer ist, was bedeutet, dass keine Ausnahme aufgetreten ist. In diesem Fall wird die Transaktion bestätigt (Zeile 65); andernfalls wird sie zurückgesetzt (Zeile 62);

Die Funktion [display_info] zeigt das Ergebnis einer Abfrage an:


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
    print(type(curseur))
    # affiche le résultat d'une command sql
    # s'agissait-il d'un select ?
    if curseur.description:
        # le curseur a une description - donc il a exécuté un select
        # description[i] est la description de la colonne n° i du select
        # description[i][0] est le nom de la colonne n° i du select
        # on affiche les noms des champs
        titre = ""
        for i in range(len(curseur.description)):
            titre += curseur.description[i][0] + ", "
        # on affiche la liste des champs sans la virgule de fin
        print(titre[0:len(titre) - 1])
        # ligne séparatrice
        print("*" * (len(titre) - 1))
        # ligne courante du select
        ligne = curseur.fetchone()
        while ligne:
            # on l'affiche
            print(ligne)
            # ligne suivante du select
            ligne = curseur.fetchone()
        # ligne séparatrice
        print("*" * (len(titre) - 1))
    else:
        # le curseur n'a pas de champ [description] - il a donc exécuté un ordre SQL
        # de mise à jour (insert, delete, update)
        print(f"nombre de lignes modifiées : {curseur.rowcount}")

Anmerkungen

  • Zeile 1: Der Parameter der Funktion ist der Cursor, der gerade eine SQL-Anweisung ausgeführt hat. Je nachdem, ob es sich bei dieser Anweisung um eine SELECT- oder eine Aktualisierungsanweisung (INSERT, UPDATE, DELETE) handelt, ist der Inhalt des Cursors unterschiedlich;
  • Zeile 6: Wenn der Cursor das Feld [description] enthält, hat er eine SELECT-Anweisung ausgeführt, und [description] beschreibt die in der SELECT-Anweisung angeforderten Felder:
    • description[i] beschreibt das Feld Nummer i, das von der SELECT-Anweisung angefordert wurde. Es handelt sich um eine Liste;
    • description[i][0] ist der Name des Feldes Nummer i;
  • Zeilen 11–17: Die Namen der von der SELECT-Anweisung angeforderten Felder werden angezeigt;
  • Zeilen 18–24: Wir verarbeiten das Ergebnis der SELECT-Anweisung;
  • Zeilen 20, 24: Das Ergebnis einer SELECT-Anweisung wird sequenziell verarbeitet. Dieses Ergebnis ist eine Reihe von Zeilen. Die aktuelle Zeile wird über [cursor.fetchone()] (Zeile 19) abgerufen. Anschließend wird ein Tupel erhalten;
  • Zeilen 27–30: Wenn der Cursor das Feld [description] nicht enthält, hat er eine INSERT-, UPDATE- oder DELETE-Anweisung ausgeführt. Wir können dann feststellen, wie viele Zeilen in der Tabelle durch die Ausführung dieser Anweisung geändert wurden;
  • Zeile 30: [cursor.rowcount] ist diese Zahl;

Das Hauptskript [mysql-04] verwendet das soeben beschriebene Modul [mysql_module]:

Image

Die Datei [config_04] konfiguriert den Ausführungskontext des Skripts [mysql_04]:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
def configure():
    import os

    #  absolute path of the configuration file folder
    script_dir = os.path.dirname(os.path.abspath(__file__))
    #  syspath folder configuration
    absolute_dependencies = [
        #  local files
        f"{script_dir}/shared",
    ]

    #  syspath mounting
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    #  return the config
    return {
        #  order file SQL
        "commands_filename": f"{script_dir}/data/commandes.sql",
        #  database connection identifiers
        "host": "localhost",
        "database": "dbpersonnes",
        "user": "admpersonnes",
        "password": "nobody"
    }

Das Skript [mysql_04] lautet wie folgt:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#  retrieve application configuration
import config_04

config = config_04.configure()

#  syspath is configured - imports can be made
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError

#  ---------------------------------------------- main
#  check call syntax
#  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"
#  mistake?
if erreur:
    print(f"syntaxe : {args[0]} true / false")
    sys.exit()

#  text calculation
with_transaction = with_transaction == "true"
if with_transaction:
    texte = "avec transaction"
else:
    texte = "sans transaction"

#  screen logs
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")

#  execution of SQL orders in the file
connexion = None
try:
    #  connection to comics
    connexion = connect(host=config['host'], user=config['user'], password=config['password'],
                        database=config['database'])
    #  execution of SQL command file
    erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
                                       with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
    #  error display
    print(f"L'erreur fatale suivante s'est produite : {erreur}")
    #  we stop
    sys.exit()
finally:
    #  close the connection if it has been opened
    if connexion:
        connexion.close()

#  display number of errors
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
#  error display
for erreur in erreurs:
    print(erreur)

Anmerkungen

  • Zeilen 1–4: Skriptkonfiguration;
  • Zeile 8: Import des oben 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 SQL-Befehlsdatei innerhalb einer Transaktion (true) oder nicht (false) ausgeführt werden soll;
  • Zeile 14: Die vom Benutzer an das Skript übergebenen Parameter befinden sich in der Liste [sys.argv];
  • Zeile 15: Es sind zwei Parameter erforderlich, zum Beispiel [mysql-04 true]. Der Skriptname zählt als Parameter;
  • Zeilen 17–18: Wenn tatsächlich zwei Parameter vorhanden sind, muss der zweite ein String mit dem Wert „true“ oder „false“ sein;
  • Zeilen 24–29: Berechnung des in Zeile 33 angezeigten Textes;
  • Zeilen 39–44: Ausführung der Befehle in der Datei [./data/commands.sql];
  • Zeilen 45–49: Wenn während der Verbindung (Zeile 40) ein Fehler auftritt oder dieser vom Skript [execute_file_of_commands] nicht behandelt wird, wird der Fehler angezeigt und der Vorgang abgebrochen;
  • Zeilen 55–62: Bei erfolgreicher Ausführung wird die Anzahl der Fehler angezeigt, die bei der Ausführung der SQL-Befehle aufgetreten sind;

Ausführung Nr. 1

Zunächst führen wir eine Ausführung ohne Transaktion durch. Dazu erstellen wir eine Ausführungskonfiguration, wie im Abschnitt |Konfigurieren eines Ausführungskontexts| beschrieben:

Image

  • In [1-4] erstellen wir eine Python-Ausführungskonfiguration;

Image

  • [5]: Name der Ausführungskonfiguration;
  • [6]: Pfad zum auszuführenden Skript;
  • [7]: Skriptparameter;
  • [8]: Ausführungsverzeichnis;

Diese Konfiguration entspricht somit der Ausführung der SQL-Datei mit einer Transaktion. Bestätigen Sie die Konfiguration über die Schaltfläche [Übernehmen].

Auf die gleiche Weise erstellen wir die Ausführungskonfiguration [mysql mysql-04 without_transaction]:

Image

Diese Konfiguration entspricht somit der Ausführung der SQL-Datei ohne Transaktion. Bestätigen Sie die Konfiguration mit der Schaltfläche [Übernehmen].

Wir führen zunächst die Version ohne Transaktion aus:

Image

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 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: Wir sehen, dass die Ausführung der SQL-Anweisungen nach dem Fehler fortgesetzt wurde. Dies liegt daran, dass die Ausführung ohne Transaktion und mit dem Parameter [stop=False] erfolgte. Alle SQL-Anweisungen wurden daher ausgeführt. Wir sollten daher eine Tabelle [people] haben, die diese Ausführung widerspiegelt;

Überprüfung mit phpMyAdmin:

Image

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: Wir sehen, dass nach dem Fehler keine weiteren SQL-Anweisungen ausgeführt werden. Das liegt daran, dass die Ausführung innerhalb einer Transaktion stattfand und wir beim Auftreten des ersten Fehlers die Transaktion zurückgesetzt und die Ausführung der SQL-Anweisungen gestoppt haben. Das bedeutet, dass die Ergebnisse der Anweisungen in den Zeilen 9, 11 und 13 zurückgesetzt wurden. Wir sollten daher eine leere Tabelle [people] haben;

Überprüfung mit phpMyAdmin:

Image

  • In [5] sehen wir, dass die Tabelle [people] [2] leer ist;

16.7. Skript [mysql_05]: Verwendung von parametrisierten Abfragen

Das Skript [mysql_05] führt das Konzept der parametrisierten Abfragen ein:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#  imports
from mysql.connector import connect, DatabaseError, InterfaceError

#  user identity
ID = "admpersonnes"
PWD = "nobody"
#  the sgbd host machine
HOST = "localhost"
#  base identity
BASE = "dbpersonnes"

#  list of people (last name, first name, age)
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))
#  other list of persons
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))

#  access to SGBD
connexion = None
try:
    #  connection
    connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
    #  cursor
    curseur = connexion.cursor()
    #  delete existing registrations
    curseur.execute("delete from personnes")
    #  person-by-person insertions with a prepared query
    for personne in personnes:
        curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
    #  bulk insertion of a list of people
    curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
    #  transaction validation
    connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
    #  error display
    print(f"L'erreur suivante s'est produite : {erreur}")
    #  cancel transaction
    if connexion:
        connexion.rollback()
finally:
    #  locking connection
    if connexion:
        connexion.close()

Anmerkungen

  • Zeilen 12–21: Wir erstellen zwei Listen mit Personen, die in die Datenbank [dbpeople] aufgenommen werden sollen;
  • Zeile 27: Verbindung zur Datenbank herstellen;
  • Zeile 31: Löschen des Inhalts der Tabelle [people];
  • Zeilen 33–34: Einfügen von Personen mithilfe einer parametrisierten Abfrage. In Zeile 34 ist der erste Parameter die auszuführende SQL-Anweisung. Diese Anweisung ist unvollständig. Sie enthält Platzhalter [%s], die nacheinander und der Reihe nach durch die Werte aus der Liste im zweiten Parameter ersetzt werden;
  • Zeile 36: Einfügen von Personen, diesmal mithilfe einer einzigen Anweisung [cursor.executemany]. Der zweite Parameter von [executemany] ist daher eine Liste von Listen;

Die Vorteile parametrisierter Abfragen liegen in zwei Punkten:

  • Sie werden schneller ausgeführt als „fest codierte“ Abfragen, die bei jeder Ausführung geparst werden müssen. Die parametrisierte Abfrage [executemany] wird nur einmal geparst. Anschließend wird sie n-mal ausgeführt, ohne erneut geparst zu werden;
  • die in die parametrisierte Abfrage eingefügten Parameter werden validiert. Wenn sie reservierte Zeichen wie das Apostroph enthalten, werden diese „escaped“, damit sie die Ausführung der SQL-Anweisung nicht beeinträchtigen. Um dies zu überprüfen, haben wir Vor- und Nachnamen mit Apostrophen in die Liste aufgenommen (Zeilen 16 und 21);

Die in phpMyAdmin erzielten Ergebnisse lauten wie folgt:

Image

  • Beachten Sie, dass die Zeichenfolgen, die ein Apostroph enthalten – ein reserviertes Zeichen in SQL – korrekt eingefügt wurden. Die parametrisierte Abfrage hat sie „escaped“. Ohne eine parametrisierte Abfrage hätten wir dies selbst tun müssen;
https://stahe.github.io
Dieses Tutorial, verfasst von Serge Tahé, wird der Öffentlichkeit unter den Bedingungen der Creative Commons Namensnennung – Nicht kommerziell – Weitergabe unter gleichen Bedingungen 3.0 Unported.