Skip to content

17. Gebruik van SGBD en PostgreSQL

De SGBD PostgreSQL is vrij beschikbaar. Het is een alternatief voor de ‘community’-versie van MySQL.

We gebruiken deze hier om te laten zien dat het vrij eenvoudig is om Python-scripts / MySQL te migreren naar Python-scripts / PostgreSQL.

Met SGBD en MySQL was de architectuur van onze scripts als volgt:

Met de SGBD en PostgreSQL zal deze als volgt zijn:

Image

17.1. Installatie van de SGBD PostgreSQL

De distributies van de SGBD en PostgreSQL zijn beschikbaar in de URL en [https://www.postgresql.org/download/] (mei 2019). We laten de installatie van de versie voor Windows 64-bits zien:

Image

Image

  • in [1-4] downloadt u het installatieprogramma van SGBD;

Start het gedownloade installatieprogramma:

Image

  • Geef in [6] een installatiemap op;

Image

  • in [8] is de optie [Stack Builder] niet nodig voor wat we hier willen doen;
  • in [10]: laat de waarde staan die wordt weergegeven;

Image

  • in [12-13] hebben we hier het wachtwoord [root] ingevoerd. Dit wordt het wachtwoord van de beheerder van SGBD, die [postgres] heet. PostgreSQL noemt hem ook wel de supergebruiker;
  • laat bij [15] de standaardwaarde staan: dit is de luisterpoort van SGBD;

Image

  • in [17] laat u de standaardwaarde staan;
  • in [19], het overzicht van de installatieconfiguratie;

Image

Image

In Windows wordt SGBD PostgreSQL geïnstalleerd als een Windows-service die automatisch wordt gestart. Meestal is dit niet wenselijk. We gaan deze configuratie aanpassen. Typ [services] in de zoekbalk van Windows [24-26]:

Image

  • in [29]. We zien dat de service SGBD PostgreSQL in de automatische modus staat. We wijzigen dit door de eigenschappen van de service [30] te openen:

Image

  • in [31-32], stel het opstarten in op handmatige modus;
  • bij [33]: stop de service;

Wanneer u de service SGBD handmatig wilt starten, ga dan terug naar de toepassing [services], klik met de rechtermuisknop op de service [postgresql] (34) en start deze (35).

17.2. Beheer PostgreSQL met de tool [pgAdmin]

Start de Windows-service SGBD PostgreSQL (vorige paragraaf). Start vervolgens, op dezelfde manier als u het hulpprogramma [services] hebt gestart, start u het hulpprogramma [pgadmin] waarmee u de SGBD, PostgreSQL en [1-3] kunt beheren:

Image

Het is mogelijk dat u op een bepaald moment wordt gevraagd om het wachtwoord van de supergebruiker. Dit is [postgres]. U hebt dit wachtwoord ingesteld tijdens de installatie van SGBD. In dit document hebben we tijdens de installatie het wachtwoord [root] aan de supergebruiker toegewezen.

  • in [4] is [pgAdmin] een webapplicatie;
  • in [5] is de lijst met PostgreSQL-servers die door [pgAdmin] zijn gedetecteerd, hier 1;
  • in [6], de server PostgreSQL die we hebben gestart;
  • in [7], de databases van SGBD, hier 1;
  • in [8] wordt de database [postgresql] beheerd door de supergebruiker [postgres];

Laten we eerst een gebruiker [admpersonnes] aanmaken met het wachtwoord [nobody]:

Image

Image

  • in [17] hebben we [nobody] ingevoerd;

Image

  • in [21], de code SQL die door de tool [pgAdmin] zal worden verzonden naar de SGBD PostgreSQL. Dit is een manier om de eigen taal van SQL te leren, die eigendom is van PostgreSQL;
  • in [22] is, na validatie door de wizard [Save], de gebruiker [admpersonnes] aangemaakt;

Nu maken we de database [dbpersonnes] aan:

Image

Klik met de rechtermuisknop op [23] en vervolgens op [24-25] om een nieuwe database aan te maken. In het tabblad [26] stellen we de naam van de database in op [27] en de eigenaar op [admpersonnes] [28].

Image

  • in [30], de code SQL voor het aanmaken van de database;
  • in [31], na validatie door de wizard [Save], wordt de database [dbpersonnes] aangemaakt;

We gaan de database [dbpersonnes] gebruiken met Python-scripts.

17.3. Installatie van de Python-connector van SGBD PostgreSQL

Image

In het bovenstaande schema wordt een connector weergegeven die de koppeling vormt tussen de Python-scripts en de SGBD PostgreSQL. Er zijn er meerdere. We installeren de connector [psycopg2]. Dit gebeurt in een Python-terminal (het maakt niet uit in welke map deze terminal is geopend). De connector wordt geïnstalleerd met het commando [pip install psycopg2]:


(venv) C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\troiscouches\v01\tests>pip install psycopg2
Collecting psycopg2
  Downloading psycopg2-2.8.5-cp38-cp38-win_amd64.whl (1.1 MB)
     || 1.1 MB 3.2 MB/s
Installing collected packages: psycopg2
Successfully installed psycopg2-2.8.5

17.4. Overzetten van de MySQL-scripts naar PostgreSQL-scripts

Image

  • de map [1] van de scripts MySQL wordt gekopieerd (Ctrl-C / Ctrl-V), waarna de bestandsnamen worden gewijzigd, maar de inhoud blijft hetzelfde;

17.4.1. module [pgres_module]

Deze module is een kopie van de module [mysql_module] (zie paragraaf |script [mysql-04]: uitvoering van een opdrachtbestand SQL|). De imports worden gewijzigd:

In plaats van:


# import
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor

schrijven we:


# import
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

De functiehandtekening van [afficher_infos] was:


def afficher_infos(curseur: MySQLCursor):

Dit wordt:


def afficher_infos(curseur: cursor)

De handtekening van de functie [execute_list_of_commands] was:


def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True)

Dit wordt:


def execute_list_of_commands(connexion: connection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):

Voor de rest verandert er niets.

17.4.2. script [pgres_01]

Het script [pgres_01] is een kopie van het script [mysql_01] (zie paragraaf |script [mysql-01]: verbinding met een database MySQL - 1|). Hierin worden de volgende wijzigingen aangebracht:

In plaats van:


# module importeren mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

schrijft men:


# import van de module psycopg2
from psycopg2 import connect, DatabaseError, InterfaceError

De rest blijft ongewijzigd. De resultaten zijn dezelfde als bij MySQL.

17.4.3. script [pgres_02]

Het script [pgres_02] is een kopie van het script [mysql_02] (zie paragraaf |script [mysql-02]: verbinding met een database MySQL - 2|). Hierin worden de volgende wijzigingen aangebracht:

In plaats van:


# import van de module mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect

schrijft men:


# de psycopg2-module importeren
from psycopg2 import DatabaseError, InterfaceError, connect

De resultaten zijn niet dezelfde als die van het script [mysql_02]:

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/postgresql/pgres_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

Process finished with exit code 0

Het script [pgres_02] is als volgt:


# de module mysql.connector importeren
from psycopg2 import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # maakt verbinding met 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"

# verbinding van een bestaande gebruiker
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # de foutmelding wordt weergegeven
    print(erreur)

# aanmelding van een niet-bestaande gebruiker
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # de foutmelding wordt weergegeven
    print(erreur)

Terwijl de regels 36-41 een foutmelding hadden moeten weergeven dat de verbinding met SGBD was mislukt, wordt er niets weergegeven. Als we de kwestie nader onderzoeken, zien we dat de regels 35-37 wel degelijk door [except] worden verwerkt, maar dat de variabele [erreur] de waarde [None] heeft. Dit gebeurt met versie 2.8.4 van de connector [psycopg2].

Dit probleem kan worden omzeild door een generiek, maar minder specifiek bericht te schrijven:


# aanmelding van een niet-bestaande gebruiker
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # de fout wordt weergegeven
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

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/postgresql/pgres_02.py
Connexion réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion réussie

Erreur de connexion à la base [dbpersonnes] par l'utilisateur [xx/yy]

Process finished with exit code 0

17.4.4. script [pgres_03]

Het script [pgres_03] is een kopie van het script [mysql_03] (zie paragraaf |script [mysql-03]: aanmaken van een tabel MySQL|). Hierin worden de volgende wijzigingen aangebracht:

In plaats van:


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

schrijven we:


from psycopg2 import DatabaseError, InterfaceError, connect
from psycopg2.extensions import connection

Verder was de handtekening van de functie [execute_sql]:


def execute_sql(connexion: MySQLConnection, update: str):

wordt:


def execute_sql(connexion: connection, update: str):

De rest blijft ongewijzigd. Het resultaat is 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/postgresql/pgres_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

Je kunt controleren of de tabel [personnes] aanwezig is met het beheertool [pgAdmin]:

Image

17.4.5. script [pgres_04]

Het script [pgres_04] is een kopie van het script [mysql_04] (zie paragraaf |script [mysql-04]: uitvoering van een opdrachtbestand SQL|). Het maakt gebruik van de module [pgres_module]:


# de configuratie van de applicatie wordt opgehaald
import config_04

config = config_04.configure()

# het syspath is geconfigureerd – de imports kunnen worden uitgevoerd
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

De rest blijft ongewijzigd.

We maken een configuratie [pgres pgres-04 without_transaction] aan, zoals gedaan in de paragraaf |script [mysql-04]: uitvoering van een opdrachtbestand SQL|. We maken eveneens een configuratie [pgres pgres-04 with_transaction] aan.

Het uitvoeren van de configuratie [pgres pgres-04 without_transaction] levert de volgende resultaten op:


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/postgresql/pgres_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[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 : -1
[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 (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)
[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 (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)

Process finished with exit code 0
  • regel 5: het verwijderingscommando voor de tabel [personnes] moest worden aangepast. In tegenstelling tot de connector van MySQL genereert de connector van PostgreSQL een uitzondering als de te verwijderen tabel niet bestaat. De opdracht [drop table] heeft een variant, [drop table if exists], die geen uitzondering genereert als de tabel niet bestaat. Deze hebben we hier gebruikt. Dit is een voorbeeld waarbij twee SGBD-opdrachten zich in vergelijkbare situaties niet op dezelfde manier gedragen;

De tabel [personnes] in de tool [pgAdmin] ziet er als volgt uit:

Image

Het uitvoeren van de configuratie [pgres pgres_04 with_transaction] levert de volgende resultaten op:


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/postgresql/pgres_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\postgresql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table if exists personnes] : Exécution réussie
nombre de lignes modifiées : -1
[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 : -1
[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 (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (ERREUR:  erreur de syntaxe sur ou près de « xx »
LINE 1: xx
        ^
)

Process finished with exit code 0

De tabel [personnes] in de tool [pgAdmin] is als volgt:

Image

Hier is het resultaat anders dan dat verkregen met MySQL. Als we de scripts onder dezelfde omstandigheden uitvoeren, d.w.z. na uitvoering van het script zonder transactie, krijgen we de volgende resultaten:

  • met MySQL is de tabel [personnes] leeg;
  • met PostgreSQL is de tabel [personnes] niet leeg;

Het verschil zit hem in de verschillende manieren waarop deze twee SGBD-scripts de transactie ongedaan maken:

  • MySQL maakt de opdrachten [drop table] en [create table] niet ongedaan. We houden een lege tabel [personnes] over;
  • PostgreSQL maakt de opdrachten [drop table] en [create table] ongedaan. De tabel keert terug naar de toestand waarin deze zich bevond vóór de uitvoering van het script met transactie;

17.4.6. script [pgres_05]

Het script [pgres_05] is een kopie van het script [mysql_05] (zie paragraaf |script [mysql-05]: gebruik van geparametriseerde query's|). Het script is als volgt gewijzigd:

In plaats van:


# imports
from mysql.connector import connect, DatabaseError, InterfaceError

schrijven we:


# imports
from psycopg2 import connect, DatabaseError, InterfaceError

De rest blijft ongewijzigd.

De resultaten in [pgAdmin] zijn als volgt:

Image

17.5. Conclusie

De conversie van de scripts MySQL naar de scripts PostgreSQL verliep vrij vlot. Dit is een uitzondering. De twee SGBD-scripts ondersteunen niet dezelfde naamgevingsregels voor objecten als SQL (databases, tabellen, kolommen, beperkingen, gegevenstypen…), en hebben incompatibele extensies SQL… Om een eenvoudige migratie te garanderen, moet men zich in beide gevallen houden aan de SQL-standaard, zonder te proberen de eigen uitbreidingen van de SGBD te gebruiken. Dit gaat dan ten koste van de prestaties.

https://stahe.github.io
Deze cursus-tutorial, geschreven door Serge Tahé, wordt aan het publiek ter beschikking gesteld volgens de voorwaarden van de Creative Commons Naamsvermelding – Geen commercieel gebruik – Gelijk delen 3.0 ongewijzigd.