Skip to content

17. Verwendung des PostgreSQL-DBMS

Das PostgreSQL-DBMS ist frei verfügbar. Es ist eine Alternative zur „Community“-Version von MySQL.

Wir verwenden es hier, um zu zeigen, dass es relativ einfach ist, Python/MySQL-Skripte in Python/PostgreSQL-Skripte zu migrieren.

Mit dem MySQL-DBMS sah die Architektur unserer Skripte wie folgt aus:

Image

Mit dem PostgreSQL-DBMS sieht sie wie folgt aus:

Image

17.1. Installation des PostgreSQL-DBMS

PostgreSQL-DBMS-Distributionen sind unter der URL [https://www.postgresql.org/download/] (Stand: Mai 2019) verfügbar. Wir zeigen die Installation der 64-Bit-Windows-Version:

Image

Image

  • Laden Sie unter [1-4] das DBMS-Installationsprogramm herunter;

Führen Sie das heruntergeladene Installationsprogramm aus:

Image

  • Geben Sie in [6] ein Installationsverzeichnis an;

Image

  • In [8] wird die Option [Stack Builder] für unsere Zwecke hier nicht benötigt;
  • Behalten Sie in [10] den Standardwert bei;

Image

  • In [12-13] haben wir hier das Passwort [root] eingegeben. Dies ist das Passwort für den DBMS-Administrator, der den Namen [postgres] trägt. PostgreSQL bezeichnet diesen auch als Superuser;
  • In [15] belassen Sie den Standardwert: Dies ist der Listening-Port des DBMS;

Image

  • In [17] belassen Sie den Standardwert;
  • In [19] die Zusammenfassung der Installationskonfiguration;

Image

Image

Unter Windows wird das PostgreSQL-DBMS als Windows-Dienst installiert, der automatisch startet. Meistens ist dies nicht wünschenswert. Wir werden diese Konfiguration ändern. Geben Sie [services] in die Windows-Suchleiste ein [24-26]:

Image

  • In [29] sehen Sie, dass der PostgreSQL-DBMS-Dienst auf den automatischen Modus eingestellt ist. Ändern Sie dies, indem Sie die Diensteigenschaften aufrufen [30]:

Image

  • Stellen Sie in [31-32] den Starttyp auf „Manuell“ ein;
  • Stoppen Sie in [33] den Dienst;

Wenn Sie das DBMS manuell starten möchten, kehren Sie zur Anwendung [Dienste] zurück, klicken Sie mit der rechten Maustaste auf den Dienst [postgresql] (34) und starten Sie ihn (35).

17.2. Verwaltung von PostgreSQL mit dem Tool [pgAdmin]

Starten Sie den Windows-Dienst für das PostgreSQL-DBMS (siehe vorheriger Absatz). Starten Sie anschließend auf die gleiche Weise, wie Sie das [Services]-Tool gestartet haben, das [pgAdmin]-Tool, mit dem Sie das PostgreSQL-DBMS verwalten können [1-3]:

Image

Möglicherweise werden Sie an einer Stelle nach dem Superuser-Passwort gefragt. Der Superuser heißt [postgres]. Dieses Passwort haben Sie bei der Installation des DBMS festgelegt. In diesem Dokument haben wir dem Superuser bei der Installation das Passwort [root] zugewiesen.

  • In [4] ist [pgAdmin] eine Webanwendung;
  • in [5] die Liste der von [pgAdmin] erkannten PostgreSQL-Server, hier 1;
  • in [6] der von uns gestartete PostgreSQL-Server;
  • in [7] die DBMS-Datenbanken, hier 1;
  • in [8] wird die Datenbank [postgresql] vom Superuser [postgres] verwaltet;

Zunächst erstellen wir einen Benutzer [admpersonnes] mit dem Passwort [nobody]:

Image

Image

  • in [17] haben wir [nobody] eingegeben;

Image

  • in [21] den SQL-Code, den das Tool [pgAdmin] an das PostgreSQL-DBMS sendet. Auf diese Weise kann man die proprietäre SQL-Sprache von PostgreSQL erlernen;
  • In [22] wurde nach Bestätigung mit dem [Speichern]-Assistenten der Benutzer [admpersonnes] erstellt;

Nun erstellen wir die Datenbank [dbpersonnes]:

Image

Klicken Sie mit der rechten Maustaste auf [23], dann auf [24-25], um eine neue Datenbank zu erstellen. Legen Sie auf der Registerkarte [26] den Namen der Datenbank [27] und deren Eigentümer [admpersonnes] [28] fest.

Image

  • In [30] der SQL-Code zum Erstellen der Datenbank;
  • In [31] wird nach Bestätigung mit dem [Speichern]-Assistenten die Datenbank [dbpersonnes] erstellt;

Wir werden die Datenbank [dbpersonnes] mit Python-Skripten verwenden.

17.3. Installation des Python-Konnektors für das PostgreSQL-DBMS

Image

Das obige Diagramm zeigt einen Connector, der Python-Skripte mit dem PostgreSQL-DBMS verbindet. Es stehen mehrere zur Verfügung. Wir werden den [psycopg2]-Connector installieren. Dies erfolgt in einem Python-Terminal (unabhängig davon, in welchem Verzeichnis das Terminal geöffnet ist). Der Connector wird mit dem Befehl [pip install psycopg2] installiert:


(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. Portierung von MySQL-Skripten auf PostgreSQL-Skripte

Image

  • Der Ordner [1] mit den MySQL-Skripten wird dupliziert (Strg-C / Strg-V), anschließend werden die Dateinamen entsprechend ihrem Inhalt geändert;

17.4.1. [pgres_module]

Dieses Modul ist eine Kopie des Moduls [mysql_module] (siehe Abschnitt |Skript [mysql-04]: Ausführen einer SQL-Befehlsdatei|). Ändern Sie die Importe:

Anstelle von:

1
2
3
4
#  imports
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor

schreiben wir:

1
2
3
#  imports
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

Die Signatur der Funktion [display_info] lautete:


def afficher_infos(curseur: MySQLCursor):

Sie lautet nun:


def afficher_infos(curseur: cursor)

Die Signatur der Funktion [execute_list_of_commands] lautete:


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

Sie lautet nun:


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

Ansonsten ändert sich nichts weiter.

17.4.2. Skript [pgres_01]

Das Skript [pgres_01] ist eine Kopie des Skripts [mysql_01] (siehe Abschnitt |Skript [mysql-01]: Verbindung zu einer MySQL-Datenbank herstellen - 1|). Es wurden folgende Änderungen vorgenommen:

Anstelle von:

1
2
#  import module mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

Wir schreiben:

1
2
#  psycopg2 module import
from psycopg2 import connect, DatabaseError, InterfaceError

Der Rest bleibt unverändert. Die Ergebnisse sind dieselben wie bei MySQL.

17.4.3. Skript [pgres_02]

Das Skript [pgres_02] ist eine Kopie des Skripts [mysql_02] (siehe Abschnitt |Skript [mysql-02]: Verbindung zu einer MySQL-Datenbank herstellen – 2|). Nehmen Sie folgende Änderungen vor:

Anstelle von:

1
2
#  import module mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect

wir schreiben:

1
2
#  psycopg2 module import
from psycopg2 import DatabaseError, InterfaceError, connect

Die Ergebnisse stimmen nicht mit denen des Skripts [mysql_02] überein:

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

Das Skript [pgres_02] 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
#  import module mysql.connector
from psycopg2 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="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(erreur)

Obwohl in den Zeilen 36–41 eine Fehlermeldung hätte angezeigt werden müssen, die darauf hinweist, dass die Verbindung zum DBMS fehlgeschlagen ist, wird nichts angezeigt. Bei näherer Untersuchung stellen wir fest, dass der Code zwar tatsächlich in den [except]-Block in den Zeilen 35–37 eintritt, die Variable [error] jedoch auf [None] gesetzt ist. Dies tritt bei Version 2.8.4 des [psycopg2]-Connectors auf.

Wir können dieses Problem umgehen, indem wir eine generische, aber weniger präzise Meldung schreiben:

1
2
3
4
5
6
#  non-existent user login
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    #  error is displayed
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

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/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. Skript [pgres_03]

Das Skript [pgres_03] ist eine Kopie des Skripts [mysql_03] (siehe Abschnitt |Skript [mysql-03]: Erstellen einer MySQL-Tabelle|). Es wurden folgende Änderungen daran vorgenommen:

Anstelle von:


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

wir schreiben:


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

Außerdem die Signatur der Funktion [execute_sql], die wie folgt lautete:


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

wird zu:


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

Der Rest bleibt unverändert. Das Ergebnis lautet 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/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

Sie können die Existenz der Tabelle [people] mit dem Verwaltungstool [pgAdmin] überprüfen:

Image

17.4.5. Skript [pgres_04]

Das Skript [pgres_04] ist eine Kopie des Skripts [mysql_04] (siehe Abschnitt |script [mysql-04]: Ausführen einer SQL-Befehlsdatei|). Es verwendet das Modul [pgres_module]:

1
2
3
4
5
6
7
8
9
#  retrieve application configuration
import config_04

config = config_04.configure()

#  syspath is configured - imports can be made
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

Der Rest bleibt unverändert.

Wir erstellen eine Konfiguration [pgres pgres-04 without_transaction], wie im Abschnitt |Skript [mysql-04]: Ausführen einer SQL-Befehlsdatei| beschrieben. Außerdem erstellen wir eine Konfiguration [pgres pgres-04 with_transaction].

Die Ausführung der Konfiguration [pgres pgres-04 without_transaction] liefert folgende Ergebnisse:


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
  • Zeile 5: Wir mussten den Befehl ändern, um die Tabelle [people] zu löschen. Im Gegensatz zum MySQL-Konnektor löst der PostgreSQL-Konnektor eine Ausnahme aus, wenn die zu löschende Tabelle nicht existiert. Der Befehl [drop table] hat eine Variante, [drop table if exists], die keine Ausnahme auslöst, wenn die Tabelle nicht existiert. Diese haben wir hier verwendet. Dies ist ein Beispiel dafür, dass sich zwei DBMS in ähnlichen Situationen nicht gleich verhalten;

Die Tabelle [people] im Tool [pgAdmin] sieht wie folgt aus:

Image

Die Ausführung der Konfiguration [pgres pgres_04 with_transaction] liefert folgende Ergebnisse:


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

Die Tabelle [people] im Tool [pgAdmin] sieht wie folgt aus:

Image

Hier weicht das Ergebnis von dem bei MySQL erzielten ab. Wenn wir die Skripte unter denselben Bedingungen ausführen – d. h. nachdem wir das Skript ohne Transaktion ausgeführt haben –, erhalten wir folgende Ergebnisse:

  • Bei MySQL ist die Tabelle [people] leer;
  • bei PostgreSQL ist die Tabelle [people] nicht leer;

Der Unterschied liegt in der unterschiedlichen Art und Weise, wie diese beiden DBMS die Transaktion zurücksetzen:

  • MySQL rollt die Befehle [drop table] und [create table] nicht zurück. Am Ende haben wir eine leere [people]-Tabelle;
  • PostgreSQL macht die Befehle [drop table] und [create table] rückgängig. Die Tabelle wird in den Zustand zurückversetzt, in dem sie sich vor der Ausführung des Skripts im Rahmen einer Transaktion befand;

17.4.6. Skript [pgres_05]

Das Skript [pgres_05] ist eine Kopie des Skripts [mysql_05] (siehe Abschnitt |Skript [mysql-05]: Verwendung parametrisierter Abfragen|). Das Skript wurde wie folgt geändert:

Anstelle von:

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

wir schreiben:

1
2
#  imports
from psycopg2 import connect, DatabaseError, InterfaceError

Der Rest bleibt unverändert.

Die in [pgAdmin] erzielten Ergebnisse lauten wie folgt:

Image

17.5. Fazit

Die Portierung der MySQL-Skripte auf PostgreSQL-Skripte war relativ einfach. Dies ist eine Ausnahme. Die beiden DBMS unterstützen nicht dieselben Namenskonventionen für SQL-Objekte (Datenbanken, Tabellen, Spalten, Einschränkungen, Datentypen usw.) und verfügen über inkompatible SQL-Erweiterungen… Um eine einfache Portierung zu gewährleisten, muss man sich in beiden Fällen an den SQL-Standard halten, ohne zu versuchen, die proprietären Erweiterungen des DBMS zu verwenden. Dies geht zu Lasten der Leistung.

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.