Skip to content

17. Verwendung von SGBD und PostgreSQL

Das SGBD PostgreSQL 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-Skripte von MySQL auf Python-Skripte von PostgreSQL zu migrieren.

Mit SGBD und MySQL sah die Architektur unserer Skripte wie folgt aus:

Mit SGBD und PostgreSQL wird sie wie folgt aussehen:

Image

17.1. Installation von SGBD und PostgreSQL

Die Distributionen von SGBD und PostgreSQL sind im URL und [https://www.postgresql.org/download/] (Mai 2019) verfügbar. Wir zeigen die Installation der Version für Windows 64-Bit:

Image

Image

  • Bei [1-4] laden Sie das Installationsprogramm von SGBD herunter;

Starten Sie das heruntergeladene Installationsprogramm:

Image

  • Geben Sie in [6] einen Installationsordner an;

Image

  • in [8] ist die Option [Stack Builder] für das, was wir hier tun wollen, nicht erforderlich;
  • Bei [10] behalten Sie den angezeigten Wert bei;

Image

  • Bei [12-13] haben wir hier das Passwort [root] eingegeben. Dies ist das Passwort des Administrators von SGBD, der [postgres] heißt. PostgreSQL bezeichnet ihn ebenfalls als Superuser;
  • bei [15] belassen Sie den Standardwert: Dies ist der Listening-Port von SGBD;

Image

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

Image

Image

Unter Windows wird der Dienst „SGBD PostgreSQL“ als automatisch gestarteter Windows-Dienst installiert. Meistens ist dies nicht wünschenswert. Wir werden diese Konfiguration ändern. Geben Sie „[services]“ in die Windows-Suchleiste ein:

Image

  • in [29]. Wir sehen, dass der Dienst SGBD PostgreSQL im automatischen Modus läuft. Wir ändern dies, indem wir die Eigenschaften des Dienstes [30] aufrufen:

Image

  • bei [31-32] den Startmodus auf „Manuell“ setzen;
  • bei [33] den Dienst beenden;

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

17.2. Verwalten Sie PostgreSQL mit dem Tool [pgAdmin]

Starten Sie den Windows-Dienst SGBD PostgreSQL (vorheriger Absatz). Starten Sie anschließend auf dieselbe Weise, wie Sie das Tool [services] gestartet haben, starten Sie das Tool [pgadmin], mit dem Sie die Dienste SGBD, PostgreSQL und [1-3] verwalten können:

Image

Möglicherweise werden Sie zu einem bestimmten Zeitpunkt nach dem Superuser-Passwort gefragt. Dieses lautet [postgres]. Sie haben dieses Passwort bei der Installation von SGBD festgelegt. In diesem Dokument haben wir dem Superuser bei der Installation das Passwort [root] zugewiesen.

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

Erstellen wir zunächst einen Benutzer [admpersonnes] mit dem Passwort [nobody]:

Image

Image

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

Image

  • in [21], den Code SQL, den das Tool [pgAdmin] an SGBD und PostgreSQL übermitteln wird. Dies ist eine Möglichkeit, die proprietäre Sprache SQL von PostgreSQL zu erlernen;
  • In [22] wurde nach der Bestätigung durch den Assistenten [Save] der Benutzer [admpersonnes] angelegt;

Nun erstellen wir die Datenbank [dbpersonnes]:

Image

Wir klicken mit der rechten Maustaste auf [23] und dann auf [24-25], um eine neue Datenbank anzulegen. Auf der Registerkarte „[26]“ legen wir den Namen der Datenbank „[27]“ und deren Eigentümer „[admpersonnes] [28]“ fest.

Image

  • in [30], den Code SQL für die Erstellung der Datenbank;
  • in [31]; nach der Validierung durch den Assistenten [Save] wird die Datenbank [dbpersonnes] erstellt;

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

17.3. Installation des Python-Konnektors von SGBD PostgreSQL

Image

In der obigen Abbildung ist ein Konnektor dargestellt, der die Verbindung zwischen den Python-Skripten und den Konnektoren SGBD sowie PostgreSQL herstellt. Es gibt mehrere davon. Wir installieren den Konnektor [psycopg2]. Dies erfolgt in einem Python-Terminal (unabhängig davon, in welchem Ordner dieses Terminal geöffnet ist). Der Konnektor 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. Migration der Skripte MySQL zu den Skripten PostgreSQL

Image

  • Der Ordner [1] der Skripte MySQL wird dupliziert (Strg-C / Strg-V), anschließend werden die Dateinamen geändert, nicht jedoch deren Inhalt;

17.4.1. Modul [pgres_module]

Dieses Modul ist eine Kopie des Moduls [mysql_module] (siehe Abschnitt |Skript [mysql-04]: Ausführung einer Befehlsdatei SQL|). Die Importe werden geändert:

Anstelle von:


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

schreibt man:


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

Die Signatur der Funktion [afficher_infos] 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.

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 Datenbank MySQL – 1|). Dort werden folgende Änderungen vorgenommen:

Anstelle von:


# Import des Moduls mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

schreibt man:


# Import des Moduls psycopg2
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 Datenbank MySQL – 2|). Darin werden folgende Änderungen vorgenommen:

Anstelle von:


# Import des Moduls mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect

schreibt man:


# Import des Moduls psycopg2
from psycopg2 import DatabaseError, InterfaceError, connect

Die Ergebnisse unterscheiden sich von denen des Skripts [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

Das Skript [pgres_02] lautet wie folgt:


# Import des Moduls mysql.connector
from psycopg2 import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # stellt eine Verbindung zur Datenbank [database] auf dem Server [host] her und trennt sie anschließend wieder (Login, Passwort)
    # löst bei einem Problem die Ausnahme DatabaseError aus
    connexion = None
    try:
        # Verbindung
        connexion = connect(host=host, user=login, password=pwd, database=database)
        print(
            f"Connexion réussie à la base database={database}, host={host} sous l'identité user={login}, passwd={pwd}")
    finally:
        # Die Verbindung wird geschlossen, wenn sie geöffnet wurde
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- manuell
# Anmeldedaten der Verbindung
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# Anmeldung eines bestehenden Benutzers
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # Der Fehler wird angezeigt
    print(erreur)

# Anmeldung eines nicht vorhandenen Benutzers
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # Fehler wird angezeigt
    print(erreur)

Obwohl in den Zeilen 36–41 eigentlich eine Fehlermeldung hätte angezeigt werden müssen, dass die Verbindung zu SGBD fehlgeschlagen ist, wird nichts angezeigt. Bei genauerer Betrachtung stellt man fest, dass die Zeilen 35–37 zwar in das Skript [except] übergehen, die Variable [erreur] jedoch den Wert [None] annimmt. Dies tritt bei der Version 2.8.4 des Konnektors [psycopg2] auf.

Man kann dieses Problem umgehen, indem man eine generische, aber weniger präzise Meldung schreibt:


# Anmeldung eines nicht vorhandenen Benutzers
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # Der Fehler wird angezeigt
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

Die Ergebnisse lauten dann wie folgt:


C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\venv\Scripts\python.exe C:/Data/st-2020/dev/python/cours-2020/python3-flask-2020/databases/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]: Erstellung einer Tabelle MySQL|). Dort werden folgende Änderungen vorgenommen:

Anstelle von:


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

schreibt man:


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

Außerdem lautete die Signatur der Funktion [execute_sql] bisher:


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

heißt nun:


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

Das Vorhandensein der Tabelle [personnes] lässt sich 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 |Skript [mysql-04]: Ausführung einer Befehlsdatei SQL|). Es verwendet das Modul [pgres_module]:


# Die Anwendungskonfiguration wird abgerufen
import config_04

config = config_04.configure()

# Der Syspath ist konfiguriert – die Importe können durchgeführt werden
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

Der Rest bleibt unverändert.

Es wird eine Konfiguration [pgres pgres-04 without_transaction] angelegt, wie es im Abschnitt |Skript [mysql-04]: Ausführung einer Befehlsdatei SQL| beschrieben wurde. Ebenso wird eine Konfiguration [pgres pgres-04 with_transaction] angelegt.

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: Der Befehl zum Löschen der Tabelle [personnes] musste geändert werden. Im Gegensatz zum Konnektor MySQL löst der Konnektor PostgreSQL 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 SGBD in ähnlichen Situationen nicht gleich verhalten;

Die Tabelle [personnes] 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 [personnes] im Tool [pgAdmin] sieht wie folgt aus:

Image

Hier weicht das Ergebnis von dem ab, das mit MySQL erzielt wurde. Führt man die Skripte unter denselben Bedingungen aus, d. h. nach Ausführung des Skripts ohne Transaktion, erhält man folgende Ergebnisse:

  • Bei MySQL ist die Tabelle [personnes] leer;
  • mit PostgreSQL ist die Tabelle [personnes] nicht leer;

Der Unterschied liegt in der unterschiedlichen Art und Weise, wie diese beiden SGBD-Skripte die Transaktion rückgängig machen:

  • MySQL macht die Befehle [drop table] und [create table] nicht rückgängig. Die Tabelle [personnes] bleibt leer;
  • PostgreSQL macht die Befehle [drop table] und [create table] rückgängig. Die Tabelle befindet sich wieder in dem Zustand, in dem sie sich vor der Ausführung des Skripts mit 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 von parametrisierten Abfragen|). Das Skript wird wie folgt geändert:

Anstelle von:


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

schreibt man:


# Importe
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 Skripte MySQL auf die Skripte PostgreSQL verlief relativ problemlos. Dies ist eine Ausnahme. Die beiden SGBD-Skripte unterstützen nicht dieselben Namenskonventionen für Objekte wie SQL (Datenbanken, Tabellen, Spalten, Einschränkungen, Datentypen …), weisen inkompatible Erweiterungen auf … Um eine einfache Portierung zu gewährleisten, muss man sich in beiden Fällen an den Standard SQL halten, ohne zu versuchen, die proprietären Erweiterungen von SGBD zu verwenden. Dies geht dann 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.