16. Gebruik van SGBD MySQL
16.1. Installatie van SGBD en MySQL
Om de SGBD en MySQL te kunnen gebruiken, gaan we de Laragon-software installeren.
16.1.1. Installatie van Laragon
Laragon is een pakket dat verschillende programma's bevat:
- een Apache-webserver. Deze zullen we gebruiken voor het schrijven van webscripts in Python;
- de SGBD MySQL;
- de scripttaal PHP, die we niet zullen gebruiken;
- een Redis-server die een cache voor webapplicaties implementeert. Deze zullen we niet gebruiken;
Laragon kan (februari 2020) worden gedownload via de volgende link:
- De installatie [1-5] resulteert in de volgende mapstructuur:
- in [6] de installatiemap van PHP (wordt in dit document niet gebruikt);
Bij het starten van [Laragon] verschijnt het volgende venster:
- [1]: het hoofdmenu van Laragon;
- [2]: de knop [Start All] start de Apache-webserver en de SGBD MySQL;
- [3]: de knop [WEB] geeft de webpagina [http://localhost] weer;
- [4]: met de knop [Database] kunt u de SGBD en MySQL beheren met de tool [phpMyAdmin]. Deze moet eerst worden geïnstalleerd;
- [5]: de knop [Terminal] opent een opdrachtprompt;
- [6]: de knop [Root] opent Windows Verkenner, waarbij de map [<laragon>/www] is geselecteerd, de hoofdmap van de website [http://localhost]. Hier moeten de statische webapplicaties worden geplaatst die door de Apache-server van Laragon worden beheerd;
16.1.2. Een database aanmaken
We laten nu zien hoe je met de Laragon-tool een database en een gebruiker MySQL aanmaakt.
- Zodra Laragon [1] is gestart, kan het worden beheerd via een menu [2];
- in [3-5] installeert u de beheertool [phpMyAdmin] voor MySQL, indien deze nog niet is geïnstalleerd;
- in [6] worden de Apache-webserver en SGBD MySQL gestart;
- in [7] wordt de Apache-server gestart;
- in [8] worden SGBD en MySQL gestart;
- in [8-10] wordt een database aangemaakt met de naam [dbpersonnes] [11]. We gaan een database met personen opbouwen;
- in [11] gaan we de zojuist aangemaakte database beheren;
- De bewerking [Bases de données] verstuurt een webverzoek naar URL, [http://localhost/phpmyadmin] en [12]. De Apache-webserver van Laragon geeft hierop een antwoord. URL en [http://localhost/phpmyadmin] zijn onderdeel van het hulpprogramma URL dat we eerder hebben geïnstalleerd ([phpMyAdmin] en [5]). Met dit hulpprogramma kun je de MySQL-databases beheren;
- standaard zijn de inloggegevens van de databasebeheerder: root [13] zonder wachtwoord [14];
- in [16], de database die we eerder hebben aangemaakt;
- we hebben momenteel een database [dbpersonnes] [17] die leeg is [18];
We maken een gebruiker aan, [admpersonnes], met het wachtwoord [nobody], die alle rechten krijgt op de database [dbpersonnes]:
- in [19] bevinden we ons in de database [dbpersonnes];
- in [20] selecteren we het tabblad [Privileges];
- in [21-22] zien we dat de gebruiker [root] alle rechten heeft op de database [dbpersonnes];
- in [23] maakt u een nieuwe gebruiker aan;
- in [25-26] krijgt de gebruiker de gebruikersnaam [admdbpersonnes];
- in [27-29] is zijn wachtwoord [nobody];
- bij [30] geeft phpMyAdmin aan dat het wachtwoord erg zwak is (gemakkelijk te kraken). In de productieomgeving verdient het de voorkeur om een sterk wachtwoord te genereren met [31];
- in [32] wordt aangegeven dat de gebruiker [admdbpersonnes] alle rechten moet hebben op de database [dbpersonnes];
- in [33] worden de opgegeven gegevens gevalideerd;
- in [35] geeft phpMyAdmin aan dat de gebruiker is aangemaakt;
- in [36] is de opdracht SQL die op basis daarvan is uitgegeven;
- in [37] heeft de gebruiker [admpersonnes] alle rechten op de database [dbpersonnes];
Nu hebben we:
- een database MySQL [dbpersonnes];
- een gebruiker [admpersonnes/nobody] die alle rechten heeft op deze database;
16.2. Installatie van het pakket [mysql-connector-python]
We gaan Python-scripts schrijven om de eerder aangemaakte database te gebruiken met de volgende architectuur:
Een connector dient om de Python-code te scheiden van de gebruikte SGBD. Er bestaan connectoren voor verschillende SGBD-modellen en deze volgen allemaal dezelfde interface. Ook wanneer we hierboven de SGBD en MySQL vervangen door de SGBD en PostgreSQL, ziet de architectuur er als volgt uit:
Omdat alle connectoren van SGBD allemaal dezelfde interface volgen, hoeft het Python-script normaal gesproken niet te worden aangepast. In de praktijk hebben de meeste SGBD-connectoren een eigen SQL:
- ze voldoen aan de SQL-standaard (Structured Query Language);
- maar breiden deze uit, omdat deze niet toereikend is, met eigen taaluitbreidingen;
Daarom komt het vaak voor dat bij een wijziging van de SGBD er ook aanpassingen aan de SQL in de scripts moeten worden aangebracht.
Python biedt standaard geen mogelijkheid om een MySQL-database te beheren. Hiervoor moet een pakket worden gedownload. Er zijn er verschillende beschikbaar. We gaan hier het pakket [mysql-connector-python] gebruiken, de officiële connector van Oracle, het bedrijf dat eigenaar is van MySQL.
De installatie van het pakket vindt plaats in een [Terminal]-venster van PyCharm:
- de map [2] is niet van belang voor wat hierna volgt;
In de terminal voer je de opdracht [pip search MySQL] in:
- [pip] (Package Installer for Python) is het installatieprogramma voor Python-pakketten. Het programma [pip] maakt verbinding met de repository die de Python-pakketten bevat;
- [search MySQL]: vraagt de lijst op van pakketten waarvan de naam de term [MySQL] bevat (hoofdletters en kleine letters maken niet uit);
De resultaten van het commando zijn als volgt:
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 modules waarvan de naam of beschrijving het trefwoord MySQL bevat, zijn weergegeven. De module die we zullen gebruiken (februari 2020) is [mysql-connector-python], regel 17. Om deze te installeren, typ je in de terminal het commando [pip install -U mysql-connector-python]:
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
- regel 1: de optie [install -U] (U=upgrade) vraagt om de meest recente versie van de verschillende pakketten die bij het pakket [mysql-connector-python] horen;
Om te zien welke pakketten in de Python-omgeving van onze machine zijn geïnstalleerd, voeren we het commando [pip list] in:
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
…
- regel 13: het pakket [mysql-connector-python] is inderdaad aanwezig;
Om te weten te komen hoe je het pakket [mysql-connector-python] kunt gebruiken om een database MySQL te beheren, ga je naar de website van het pakket |https://dev.mysql.com/doc/connector-python/en/|. Hieronder volgt een reeks voorbeelden.
16.3. script [mysql_01]: verbinding maken met een database MySQL - 1
Het script [mysql_01] toont de eerste stap bij het gebruik van een database. Hiermee kunnen we controleren of we verbinding kunnen maken met de eerder aangemaakte database [dbpersonnes].
# module importeren mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError
# verbinding met een database MySql [dbpersonnes]
# de gebruikersidentiteit is (admpersonnes,nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# daar gaan we
connexion = None
try:
print("Connexion au SGBD MySQL en cours...")
# verbinding
connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
# vervolg
print(
f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
# de foutmelding wordt weergegeven
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# de verbinding wordt verbroken als deze is geopend
if connexion:
connexion.close()
Opmerkingen
- regel 2: we importeren bepaalde functies en klassen uit de module [mysql.connector];
- regels 6-7: de inloggegevens van de gebruiker die verbinding gaat maken;
- regel 8: de machine waarop de database draait. Met de connector MySQL kun je namelijk met een externe database werken;
- regel 9: de naam van de database waarmee verbinding moet worden gemaakt;
- regels 11-26: het script zal (regel 16) de gebruiker [admpersonnes / nobody] verbinden met de database [dbpersonnes];
- regels 20-26: de verbinding kan mislukken. Daarom wordt deze uitgevoerd in een try / except / finally-blok;
- regel 16: de methode connect van de module [mysq.connector] accepteert verschillende benoemde parameters:
- user: gebruiker die eigenaar is van de verbinding [admpersonnes];
- password: het wachtwoord van de gebruiker [nobody];
- host: machine van SGBD, MySQL, [localhost];
- database: de database waarmee verbinding wordt gemaakt. Optioneel.
- regel 20: als er een uitzondering wordt gegenereerd, is deze van het type [DatabaseError] of [InterfaceError];
- regels 23-26: in de clausule [finally] wordt de verbinding verbroken;
Resultaten
16.4. script [mysql_02]: verbinding met een database MySQL - 2
In dit nieuwe script is de verbinding met de database ondergebracht in een functie:
# module importeren mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect
# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
# maakt verbinding en verbreekt vervolgens de verbinding (login, wachtwoord) met de database [database] op de server [host]
# genereert de uitzondering DatabaseError bij een probleem
connexion = None
try:
# verbinding
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:
# de verbinding wordt verbroken als deze is geopend
if connexion:
connexion.close()
print("Déconnexion réussie\n")
# ---------------------------------------------- main
# inloggegevens
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"
# aanmelding van een bestaande gebruiker
try:
connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# er wordt een foutmelding weergegeven
print(erreur)
# aanmelding van een niet-bestaande gebruiker
try:
connexion(host=HOST, login="xx", pwd="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
# de foutmelding wordt weergegeven
print(erreur)
Opmerkingen:
- regels 6-19: een functie [connexion] die probeert een gebruiker aan te melden bij en vervolgens af te melden van de database [dbpersonnes]. Geeft het resultaat weer;
- regels 29-41: hoofdprogramma – roept tweemaal de methode connexion aan en geeft eventuele uitzonderingen weer;
Resultaten
16.5. script [mysql_03]: aanmaken van een tabel MySQL
Nu we weten hoe we een verbinding kunnen maken met een SGBD MySQL, gaan we SQL-opdrachten via deze verbinding uitvoeren. Hiervoor maken we verbinding met de aangemaakte database [dbpersonnes] en gebruiken we de verbinding om een tabel in de database aan te maken.
# import
import sys
from mysql.connector import DatabaseError, InterfaceError, connect
from mysql.connector.connection import MySQLConnection
# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
# voert een updateverzoek uit op de verbinding
curseur = None
try:
# er wordt een cursor aangevraagd
curseur = connexion.cursor()
# voert de update-query uit op de verbinding
curseur.execute(update)
finally:
# de cursor wordt gesloten als deze is verkregen
if curseur:
curseur.close()
# ---------------------------------------------- main
# inloggegevens van de verbinding
# de identiteit van de gebruiker
ID = "admpersonnes"
PWD = "nobody"
# de hostmachine van het DBMS
HOST = "localhost"
# database-identiteit
DATABASE = "dbpersonnes"
# we gaan stap voor stap te werk
try:
# verbinding
connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
# modus AUTOCOMMIT
connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
# de fout wordt weergegeven
print(f"L'erreur suivante s'est produite : {erreur}")
# afsluiten
sys.exit()
# de tabel ‘personen’ verwijderen als deze bestaat
# als deze niet bestaat, treedt er een fout op – deze wordt genegeerd
requête = "drop table personnes"
try:
execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
pass
# de tabel 'personen' wordt aangemaakt
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:
# query uitvoeren
execute_sql(connexion, requête)
# weergave
print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
# de fout wordt weergegeven
print(f"L'erreur suivante s'est produite : {erreur}")
finally:
# verbinding wordt verbroken
connexion.close()
Opmerkingen:
- regel 9: de functie execute_sql voert een query SQL uit op een geopende verbinding;
- regel 14: de bewerkingen SQL op de verbinding worden uitgevoerd via een specifiek object dat ‘cursor’ wordt genoemd;
- regel 14: een cursor ophalen;
- regel 16: uitvoering van de query SQL;
- regels 17-20: of er nu een fout optreedt of niet, de cursor wordt gesloten. Hierdoor worden de bijbehorende bronnen vrijgegeven. Als er een uitzondering optreedt, wordt deze hier niet afgehandeld. Deze wordt doorgegeven aan de aanroepende code;
- regels 33-43: het tot stand brengen van een verbinding met de database;
- regel 38: de modus AUTOCOMMIT=True voor een verbinding betekent dat elke query wordt uitgevoerd binnen een automatische transactie. De standaardmodus is AUTOCOMMIT=False, waarbij de ontwikkelaar verantwoordelijk is voor het beheer van de transacties. Een transactie is een mechanisme dat de uitvoering van meerdere query’s 1 tot en met n omvat. Ofwel slagen ze allemaal, ofwel slaagt er geen enkele. Als dus de query’s 1 tot en met i slagen, maar query i+1 mislukt, dan worden de query’s 1 tot en met i ‘teruggedraaid’, zodat de database weer in de toestand komt waarin deze zich bevond vóór de uitvoering van query 1;
- hier zijn er twee query’s SQL (regels 49, 58). Ze worden elk in een transactie uitgevoerd. Dat de tweede mislukt, heeft geen invloed op de eerste;
- regels 45-51: de opdracht SQL [drop table personnes] wordt uitgevoerd. Deze verwijdert de tabel met de naam [personnes]. Als deze niet bestaat, kan er een foutmelding worden gegeven. Deze wordt genegeerd (regel 51);
- regels 53-55: de opdracht om de tabel [personnes] aan te maken. Een tabel kan worden gezien als een verzameling rijen en kolommen. De aanmaakopdracht specificeert de namen van de kolommen:
- [id]: een gehele identificatiecode. Deze is uniek voor elke persoon. Dit is de primaire sleutel (PRIMARY KEY). Dit betekent dat deze kolom in de tabel nooit twee keer dezelfde waarde heeft en dat deze kan worden gebruikt om een persoon te identificeren;
- [nom]: een tekenreeks van maximaal 30 tekens;
- [prenom]: een tekenreeks van maximaal 30 tekens;
- [age]: een geheel getal;
- het attribuut [NOT NULL] voor elk van deze kolommen betekent dat in een rij van de tabel geen van de drie kolommen leeg mag zijn;
- de parameter [unique(nom,prenom)] wordt een beperking genoemd. Hier is de beperking op de rijen dat het tupel (achternaam, voornaam) van de rij uniek moet zijn in de tabel. Dit betekent dat men in de tabel op unieke wijze een persoon kan identificeren waarvan men de achternaam en voornaam kent;
- regels 56-60: uitvoering van de opdracht SQL;
- regels 61-63: afhandeling van een eventuele uitzondering;
- regels 64-66: we verbreken de verbinding met de database;
Resultaten
Controle met [phpMyAdmin]:
- de database [dbpersonnes] [1] bevat een tabel [personnes] [2] met de structuur [3-4], de primaire sleutel [5] en de uniekheidsbeperking [6];
16.6. script [mysql_04]: uitvoeren van een opdrachtenbestand SQL
Nadat we eerder de tabel [personnes] hebben aangemaakt, vullen we deze nu in en verwerken we deze met behulp van opdrachten SQL.
We willen de opdrachten SQL uit een tekstbestand uitvoeren:
De inhoud van het bestand [commandes.sql] is als volgt:
# verwijdering van de tabel [personnes]
drop table personnes
# de tabel 'personen' wordt aangemaakt
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# twee personen worden ingevoegd
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# de tabel weergeven
select prenom, nom, age from personnes
# opzettelijke fout
xx
# toevoegen van drie 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)
# de tabel weergeven
select prenom, nom, age from personnes
# lijst van personen in alfabetische volgorde van de achternamen en, bij gelijke achternamen, in alfabetische volgorde van de voornamen
select nom,prenom from personnes order by nom asc, prenom desc
# lijst van personen met een leeftijd in het interval [20,40], in aflopende volgorde van leeftijd
# vervolgens, bij gelijke leeftijd, in alfabetische volgorde van de achternamen en bij gelijke achternamen in alfabetische volgorde van de voornamen
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# toevoeging van mevrouw Bruneau
insert into personnes(prenom, nom, age) values('Josette','Bruneau',46)
# bijwerking van haar leeftijd
update personnes set age=47 where nom='Bruneau'
# lijst van personen met de achternaam Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
# verwijdering van mevrouw Bruneau
delete from personnes where nom='Bruneau'
# lijst van personen met de achternaam Bruneau
select nom,prenom,age from personnes where nom='Bruneau'
Eerst definiëren we functies die we in een module plaatsen, zodat we ze opnieuw kunnen gebruiken:
Het script [mysql_module] ziet er als volgt uit:
# importen
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
# geeft het resultaat van een SQL-opdracht weer
…
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# gebruikt de geopende verbinding [connexion]
# voert via deze verbinding de SQL-opdrachten uit die zijn opgenomen in de lijst [sql_commands]
# dit bestand is een opdrachtbestand SQL waarin de opdrachten per regel worden uitgevoerd
# als ‘volgen’=True, wordt bij elke uitvoering van een opdracht SQL een melding weergegeven die aangeeft of deze is geslaagd of mislukt
# als 'stop'=True, stopt de functie bij de eerste fout die wordt aangetroffen; anders voert ze alle SQL-opdrachten uit
# als with_transaction=True, dan annuleert elke fout alle eerder uitgevoerde SQL-opdrachten
# als with_transaction=False, dan heeft een fout geen invloed op de eerder uitgevoerde SQL-opdrachten
# de functie retourneert een lijst [erreur1, erreur2, ...]
….
# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# maakt gebruik van de geopende verbinding [connexion]
# voert via deze verbinding de opdrachten SQL uit die zijn opgenomen in het tekstbestand sql_filename
# dit bestand is een opdrachtbestand met SQL-opdrachten die één per regel moeten worden uitgevoerd
# als ‘volgen’=True is, wordt bij elke uitvoering van een opdracht SQL een melding weergegeven waarin wordt aangegeven of de opdracht is geslaagd of mislukt
# als 'stop'=True, stopt de functie bij de eerste fout die wordt aangetroffen; anders voert ze alle SQL-opdrachten uit
# als with_transaction=True, dan annuleert elke fout alle eerder uitgevoerde SQL-opdrachten
# als with_transaction=False, dan heeft een fout geen invloed op de eerder uitgevoerde SQL-opdrachten
# de functie retourneert een lijst [erreur1, erreur2, ...]
# verwerking van het bestand SQL
try:
# het bestand in leesmodus openen
file = open(sql_filename, "r")
# verwerking
return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
except BaseException as erreur:
# er wordt een fouttabel geretourneerd
return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]
Opmerkingen:
- regel 29: de functie [execute_file_of_commands] voert de opdrachten SQL uit die zijn opgenomen in het tekstbestand met de naam [sql_filename]:
- lees de opmerkingen op de regels 31-38 voor de betekenis van de parameters;
- regels 40-48: het tekstbestand [sql_filename] wordt verwerkt;
- regel 43: het bestand wordt geopend;
- regel 34: uitvoering van de functie [execute_list_of_commands], die de opdrachten SQL uitvoert die in een lijst aan deze functie worden doorgegeven. Deze lijst bestaat hier uit de lijst van alle regels van het tekstbestand [file.readlines()] (regel 45);
De functie [execute_list_of_commands] is als volgt:
# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
# gebruikt de geopende verbinding [connexion]
# voert via deze verbinding de opdrachten SQL uit die zijn opgenomen in de lijst [sql_commands]
# dit bestand is een opdrachtbestand met SQL-opdrachten die één per regel moeten worden uitgevoerd
# als ‘volgen’=True, wordt bij elke uitvoering van een opdracht SQL een melding weergegeven die aangeeft of deze is geslaagd of mislukt
# als 'stop'=True, stopt de functie bij de eerste fout die wordt aangetroffen; anders voert ze alle SQL-opdrachten uit
# als with_transaction=True, dan annuleert elke fout alle eerder uitgevoerde SQL-opdrachten
# als with_transaction=False, dan heeft een fout geen invloed op de eerder uitgevoerde SQL-opdrachten
# de functie retourneert een lijst [erreur1, erreur2, ...]
# initialisaties
curseur = None
connexion.autocommit = not with_transaction
erreurs = []
try:
# er wordt om een schuifbalk gevraagd
curseur = connexion.cursor()
# uitvoering van de sql_commands SQL die in sql_commands zijn opgenomen
# ze worden één voor één uitgevoerd
for command in sql_commands:
# de spaties aan het begin en einde van het huidige commando worden verwijderd
command = command.strip()
# is er sprake van een leeg commando of een opmerking? Zo ja, ga dan naar het volgende commando
if command == '' or command[0] == "#":
continue
# het huidige commando wordt uitgevoerd
error = None
try:
curseur.execute(command)
except (InterfaceError, DatabaseError) as erreur:
error = erreur
# is er een fout opgetreden?
if error:
# nog een fout
msg = f"{command} : Erreur ({error})"
erreurs.append(msg)
# schermuitvoer volgen of niet?
if suivi:
print(msg)
# stoppen we?
if with_transaction or arrêt:
# geven we de foutenlijst weer
return erreurs
else:
# geen fout
if suivi:
print(f"[{command}] : Exécution réussie")
# het resultaat van het commando weergeven
afficher_infos(curseur)
# de foutentabel weergeven
return erreurs
finally:
# de cursor wordt gesloten
if curseur:
curseur.close()
# de transactie wordt bevestigd/geannuleerd indien deze bestaat
if with_transaction:
if erreurs:
# annulering
connexion.rollback()
else:
# bevestiging
connexion.commit()
Opmerkingen
- regel 2: de functie [execute_list_of_commands] voert de opdrachten SQL uit die in de lijst [sql_commands] staan:
- lees de opmerkingen bij de regels 4-11 voor de betekenis van de parameters;
- regel 2: de ontvangen verbinding is een open verbinding met een database;
- regel 15: als men wil dat alle opdrachten uit de lijst [sql_commands] binnen een transactie worden uitgevoerd, dan moet men werken in de modus AUTOCOMMIT=False. Anders werkt men in de modus AUTOCOMMIT=True en dan wordt elk commando uit de lijst [sqlCommands] binnen een automatische transactie uitgevoerd en is er geen globale transactie;
- regel 19: er wordt een cursor aangevraagd om de verschillende opdrachten SQL uit te voeren;
- regels 22-51: de opdrachten worden één voor één uitgevoerd;
- regels 26-27: lege regels en opmerkingen in de lijst met opdrachten SQL worden geaccepteerd. In dat geval wordt de opdracht gewoon genegeerd;
- regels 30-33: uitvoering van de huidige query;
- regels 35-45: er wordt omgegaan met een eventuele fout bij de uitvoering van de huidige query;
- regels 37-38: de fout wordt toegevoegd aan de foutentabel;
- regels 40-41: als er om een melding is gevraagd, wordt het foutbericht weergegeven;
- regels 43-45: als de aanroepende code heeft gevraagd om te stoppen na de eerste fout of als er om het gebruik van een transactie is gevraagd, dan moet het programma worden gestopt. De foutentabel wordt teruggegeven;
- regels 46-51: het geval waarin er geen fout is opgetreden bij de uitvoering van de huidige query;
- regels 48-49: als er om een follow-up is gevraagd, wordt de uitgevoerde query weergegeven met de vermelding 'geslaagd';
- regels 50-51: het resultaat van de uitgevoerde query wordt weergegeven. We komen iets verderop terug op de functie [afficher_infos];
- regels 54-65: de clausule [finally] wordt in alle gevallen uitgevoerd, ongeacht of er een uitzondering is opgetreden of niet;
- regels 56-57: de cursor wordt gesloten. Hierdoor worden de aan de cursor toegewezen bronnen vrijgegeven;
- regels 59-65: we behandelen het geval waarin de aanroepende code heeft gevraagd om de commando’s SQL binnen een transactie uit te voeren;
- regel 60: er wordt gecontroleerd of de lijst [erreurs] leeg is, wat betekent dat er geen uitzondering heeft plaatsgevonden. In dat geval wordt de transactie bevestigd (regel 65), anders wordt deze geannuleerd (regel 62);
De functie [afficher_infos] geeft het resultaat van een query weer:
# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
print(type(curseur))
# geeft het resultaat van een SQL-opdracht weer
# was dit een SELECT-opdracht?
if curseur.description:
# de cursor heeft een beschrijving – dus er is een SELECT-opdracht uitgevoerd
# beschrijving[i] is de beschrijving van kolom nr. i van de SELECT
# beschrijvingQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX is de naam van kolom nr. i van de SELECT-opdracht
# de veldnamen worden weergegeven
titre = ""
for i in range(len(curseur.description)):
titre += curseur.description[i][0] + ", "
# de lijst met velden wordt weergegeven zonder de afsluitende komma
print(titre[0:len(titre) - 1])
# scheidingsregel
print("*" * (len(titre) - 1))
# huidige regel van de selectie
ligne = curseur.fetchone()
while ligne:
# wordt weergegeven
print(ligne)
# volgende regel van de selectielijst
ligne = curseur.fetchone()
# scheidingsregel
print("*" * (len(titre) - 1))
else:
# de cursor heeft geen veld [description] - hij heeft dus een opdracht uitgevoerd SQL
# update-opdracht (insert, delete, update)
print(f"nombre de lignes modifiées : {curseur.rowcount}")
Opmerkingen
- regel 1: de parameter van de functie is de cursor die zojuist een opdracht SQL heeft uitgevoerd. Afhankelijk van of deze opdracht een SELECT is of een bijwerkopdracht INSERT, UPDATE, DELETE, is de inhoud van de cursor niet dezelfde;
- regel 6: als de cursor het veld [description] heeft, dan heeft hij een SELECT uitgevoerd en beschrijft [description] de velden die in de SELECT worden opgevraagd:
- description[i] beschrijft veld nr. i dat wordt opgevraagd door SELECT. Dit is een lijst;
- beschrijving[i][0] is de naam van veld nr. i;
- regels 11-17: de namen van de velden die door SELECT worden opgevraagd, worden weergegeven;
- regels 18-24: het resultaat van de SELECT wordt verwerkt;
- regels 20, 24: het resultaat van een SELECT wordt sequentieel verwerkt. Dit resultaat bestaat uit een reeks regels. De huidige regel wordt verkregen via [curseur.fetchone()] (regel 19). Dit levert een tuple op;
- regels 27-30: als de cursor het veld [description] niet heeft, dan heeft hij een bijwerkopdracht INSERT, UPDATE, DELETE uitgevoerd. Zo kan worden vastgesteld hoeveel rijen in de tabel door de uitvoering van deze opdracht zijn gewijzigd;
- regel 30: [curseur.rowcount] is dit aantal;
Het hoofdscript [mysql-04] maakt gebruik van de module [mysql_module] die we zojuist hebben beschreven:
Het bestand [config_04] configureert de uitvoeringscontext van het script [mysql_04]:
def configure():
import os
# absoluut pad naar de map met het configuratiebestand
script_dir = os.path.dirname(os.path.abspath(__file__))
# configuratie van de mappen in de syspath
absolute_dependencies = [
# lokale mappen
f"{script_dir}/shared",
]
# syspath vastleggen
from myutils import set_syspath
set_syspath(absolute_dependencies)
# de configuratie wordt doorgevoerd
return {
# het bestand met de SQL-opdrachten
"commands_filename": f"{script_dir}/data/commandes.sql",
# inloggegevens voor de databaseverbinding
"host": "localhost",
"database": "dbpersonnes",
"user": "admpersonnes",
"password": "nobody"
}
Het script [mysql_04] is als volgt:
# de configuratie van de applicatie wordt opgehaald
import config_04
config = config_04.configure()
# het syspath is geconfigureerd – we kunnen de imports uitvoeren
import sys
from mysql_module import execute_file_of_commands
from mysql.connector import connect, DatabaseError, InterfaceError
# ---------------------------------------------- main
# controle van de syntaxis van de aanroep
# 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"
# fout?
if erreur:
print(f"syntaxe : {args[0]} true / false")
sys.exit()
# berekening van een tekst
with_transaction = with_transaction == "true"
if with_transaction:
texte = "avec transaction"
else:
texte = "sans transaction"
# schermlogs
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")
# uitvoering van de opdrachten SQL uit het bestand
connexion = None
try:
# verbinding met de database
connexion = connect(host=config['host'], user=config['user'], password=config['password'],
database=config['database'])
# uitvoering van het opdrachtenbestand SQL
erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
# weergave van de foutmelding
print(f"L'erreur fatale suivante s'est produite : {erreur}")
# afsluiten
sys.exit()
finally:
# verbinding afsluiten indien deze was geopend
if connexion:
connexion.close()
# aantal fouten weergeven
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# fouten weergeven
for erreur in erreurs:
print(erreur)
Opmerkingen
- regels 1-4: configuratie van het script;
- regel 8: import van de eerder beschreven module [mysql_module]:
- regels 12-22: het script [mysql-04] verwacht een parameter die een van de waarden [true / false] moet hebben. Deze parameter geeft aan of het opdrachtenbestand SQL binnen een transactie moet worden uitgevoerd (true) of niet (false);
- regel 14: de parameters die de gebruiker aan het script doorgeeft, staan in de lijst [sys.argv];
- regel 15: er zijn twee parameters nodig, bijvoorbeeld [mysql-04 true]. De naam van het script telt als een parameter;
- regels 17-18: als er inderdaad twee parameters zijn, moet de tweede een tekenreeks zijn met de waarde 'true' of 'false';
- regels 24-29: berekening van een tekst die op regel 33 wordt weergegeven;
- regels 39-44: de opdrachten uit het bestand [./data/commandes.sql] worden uitgevoerd;
- regels 45-49: als er een fout optreedt bij het verbinden (regel 40) of een fout die niet wordt afgehandeld door het script [execute_file_of_commands], wordt de fout weergegeven en wordt alles gestopt;
- regels 55-62: bij een succesvolle uitvoering wordt het aantal fouten weergegeven dat is opgetreden tijdens de uitvoering van de opdrachten in het bestand SQL;
Uitvoering nr. 1
We voeren eerst een uitvoering zonder transactie uit. Hiervoor maken we een uitvoeringsconfiguratie aan zoals beschreven in de paragraaf |configuratie van een uitvoeringscontext|:
- in [1-4] maken we een Python-uitvoeringsconfiguratie aan;
- [5]: naam van de uitvoeringsconfiguratie;
- [6]: pad naar het uit te voeren script;
- [7]: parameters van het script;
- [8]: uitvoermap;
Deze configuratie komt dus overeen met het uitvoeren van het bestand SQL met een transactie. Gebruik de knop [Apply] om de configuratie te bevestigen.
Op dezelfde manier maken we de uitvoeringsconfiguratie [mysql mysql-04 without_transaction] aan:
Deze configuratie komt dus overeen met een uitvoering van het bestand SQL zonder transactie. Gebruik de knop [Apply] om de configuratie te bevestigen.
We voeren eerst de versie zonder transactie uit:
De resultaten zijn dan als volgt:
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
Opmerkingen:
- regel 19: we zien dat na de fout de uitvoering van de opdrachten SQL is voortgezet, omdat de uitvoering plaatsvond zonder transactie en met de parameter [arrêt=False]. Alle opdrachten SQL zijn dus uitgevoerd. Er zou dus een tabel [personnes] moeten zijn die deze uitvoering weergeeft;
Controle met phpMyAdmin:
Uitvoering nr. 2
We voeren nu de configuratie [mysql mysql-04 with_transaction] uit. De resultaten zijn als volgt:
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
Opmerkingen:
- regel 19: we zien dat er na de fout geen opdrachten van SQL meer worden uitgevoerd. Dit komt doordat de uitvoering plaatsvond binnen een transactie en we bij de eerste fout die we tegenkwamen de transactie hebben teruggedraaid en de uitvoering van de opdrachten van SQL hebben gestopt. Dit betekent dat het resultaat van de opdrachten in de regels 9, 11 en 13 ongedaan is gemaakt. We zouden dus een lege tabel [personnes] moeten hebben;
Controles met phpMyAdmin:
- in [5] zien we dat de tabellen [personnes] en [2] leeg zijn;
16.7. script [mysql_05]: gebruik van geparametriseerde query's
Het script [mysql_05] introduceert het concept van query's met parameters:
# importen
from mysql.connector import connect, DatabaseError, InterfaceError
# de identiteit van de gebruiker
ID = "admpersonnes"
PWD = "nobody"
# de hostmachine van het DBMS
HOST = "localhost"
# database-identiteit
BASE = "dbpersonnes"
# lijst met personen (achternaam, voornaam, leeftijd)
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))
# andere lijst met personen
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))
# toegang tot SGBD
connexion = None
try:
# aanmelding
connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
# schuifbalk
curseur = connexion.cursor()
# verwijdering van bestaande records
curseur.execute("delete from personnes")
# persoon voor persoon invoegen met een voorbereide query
for personne in personnes:
curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
# in één keer een lijst met personen invoegen
curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
# transactie bevestigen
connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
# foutmelding weergeven
print(f"L'erreur suivante s'est produite : {erreur}")
# transactie annuleren
if connexion:
connexion.rollback()
finally:
# verbinding afsluiten
if connexion:
connexion.close()
Opmerkingen
- regels 12-21: er worden twee lijsten met personen aangemaakt die in de database [dbpersonnes] moeten worden opgenomen;
- regel 27: verbinding met de database;
- regel 31: de inhoud van de tabel [personnes] wordt gewist;
- regels 33-34: personen invoegen met een geparametriseerde query. Regel 34: de eerste parameter is de uit te voeren opdracht SQL. Deze is onvolledig. Hij bevat parameters van [%s] die één voor één en in volgorde zullen worden vervangen door de waarden uit de lijst van de tweede parameter;
- regel 36: het invoeren van personen, ditmaal met slechts één instructie: [curseur.executemany]. De tweede parameter van [executemany] is dan een lijst van lijsten;
Het voordeel van geparametriseerde query’s ligt in twee punten:
- ze worden sneller uitgevoerd dan ‘vaste’ query’s die bij elke uitvoering opnieuw moeten worden geparseerd. De geparametriseerde query [executemany] wordt slechts één keer geparseerd. Daarna wordt deze n keer uitgevoerd zonder opnieuw te worden geparseerd;
- de parameters die in de geparametriseerde query worden ingevoerd, worden gecontroleerd. Als ze gereserveerde tekens bevatten, zoals bijvoorbeeld het apostrof, worden deze ‘afgeschermd’ zodat ze de uitvoering van de opdracht SQL niet verstoren. Om dit te controleren zijn er achternamen en voornamen met apostrofs in de lijst opgenomen (regels 16 en 21);
De resultaten in phpMyAdmin zijn als volgt:
- merk op dat de tekenreeksen met een apostrof – een gereserveerd teken in SQL – correct zijn ingevoegd. De geparametriseerde query heeft ze ‘beschermd’. Zonder geparametriseerde query hadden we dit werk zelf moeten doen;