Skip to content

17. Korzystanie z SGBD i PostgreSQL

SGBD PostgreSQL jest dostępny bezpłatnie. Stanowi on alternatywę dla wersji „community” MySQL.

Wykorzystujemy go tutaj, aby pokazać, że migracja skryptów Python / MySQL do skryptów Python / PostgreSQL jest dość prosta.

W przypadku SGBD i MySQL architektura naszych skryptów wyglądała następująco:

W przypadku SGBD i PostgreSQL będzie ona wyglądać następująco:

Image

17.1. Instalacja SGBD PostgreSQL

Dystrybucje SGBD i PostgreSQL są dostępne w URL i [https://www.postgresql.org/download/] (maj 2019 r.). Poniżej przedstawiamy instalację wersji dla systemu Windows 64-bitowego:

Image

Image

  • w [1-4] należy pobrać instalator z SGBD;

Uruchamiamy pobrany instalator:

Image

  • w [6] należy wskazać folder instalacyjny;

Image

  • w [8] opcja [Stack Builder] nie jest potrzebna do tego, co chcemy tutaj zrobić;
  • w [10] pozostaw domyślną wartość;

Image

  • w polu [12-13] wprowadzono hasło [root]. Będzie to hasło administratora SGBD, który nazywa się [postgres]. PostgreSQL nazywa go również superużytkownikiem;
  • w polu [15] należy pozostawić wartość domyślną: jest to port nasłuchowy serwera SGBD;

Image

  • w polu [17] pozostaw wartość domyślną;
  • w [19] – podsumowanie konfiguracji instalacji;

Image

Image

W systemie Windows SGBD PostgreSQL jest zainstalowany jako usługa systemu Windows uruchamiana automatycznie. W większości przypadków nie jest to pożądane. Zmienimy tę konfigurację. Wpisz [services] w pasku wyszukiwania systemu Windows [24-26]:

Image

  • W [29] widać, że usługa SGBD PostgreSQL działa w trybie automatycznym. Można to zmienić, otwierając właściwości usługi [30]:

Image

  • w przypadku usługi [31-32] należy ustawić uruchamianie w trybie ręcznym;
  • w przypadku usługi [33] należy zatrzymać usługę;

Gdy zechcesz ręcznie uruchomić usługę SGBD, wróć do aplikacji [services], kliknij prawym przyciskiem myszy usługę [postgresql] (34) i uruchom ją (35).

17.2. Zarządzanie usługą PostgreSQL za pomocą narzędzia [pgAdmin]

Uruchom usługę systemu Windows o nazwie SGBD PostgreSQL (poprzedni akapit). Następnie, w taki sam sposób, w jaki uruchomiłeś narzędzie [services], uruchom narzędzie [pgadmin], które umożliwia zarządzanie SGBD, PostgreSQL i [1-3]:

Image

Możliwe, że w pewnym momencie pojawi się prośba o podanie hasła superużytkownika. Nazywa się ono [postgres]. Hasło to zostało przez Państwa ustawione podczas instalacji programu SGBD. W niniejszym dokumencie podczas instalacji nadaliśmy superużytkownikowi hasło [root].

  • w [4], [pgAdmin] jest aplikacją internetową;
  • w [5] znajduje się lista serwerów PostgreSQL wykrytych przez [pgAdmin], w tym przypadku 1;
  • w [6] – serwer PostgreSQL, który uruchomiliśmy;
  • w [7] – bazy danych serwera SGBD, w tym przypadku 1;
  • w [8] baza [postgresql] jest zarządzana przez superużytkownika [postgres];

Najpierw utwórzmy użytkownika o nazwie [admpersonnes] z hasłem [nobody]:

Image

Image

  • w [17] wpisano [nobody];

Image

  • z [21] na kod SQL, który narzędzie [pgAdmin] wygeneruje w kierunku SGBD PostgreSQL. Jest to sposób na naukę języka SQL, będącego własnością PostgreSQL;
  • w [22], po zatwierdzeniu przez kreatora [Save], utworzono użytkownika [admpersonnes];

Teraz tworzymy bazę danych [dbpersonnes]:

Image

Klikamy prawym przyciskiem myszy na [23], a następnie na [24-25], aby utworzyć nową bazę danych. W zakładce [26] określamy nazwę bazy danych [27] oraz jej właściciela [admpersonnes] [28].

Image

  • w [30] – kod SQL tworzenia bazy;
  • na [31], po zatwierdzeniu przez kreatora [Save], baza [dbpersonnes] zostaje utworzona;

Będziemy korzystać z bazy danych [dbpersonnes] za pomocą skryptów w języku Python.

17.3. Instalacja łącznika Python dla bazy danych SGBD i PostgreSQL

Image

Na powyższym schemacie przedstawiono łącznik łączący skrypty w języku Python z SGBD i PostgreSQL. Istnieje ich kilka. Instalujemy łącznik [psycopg2]. Odbywa się to w terminalu Pythona (nie ma znaczenia, w jakim katalogu jest otwarty ten terminal). Łącznik instaluje się za pomocą polecenia [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. Przeniesienie skryptów MySQL do skryptów PostgreSQL

Image

  • folder [1] ze skryptami MySQL jest duplikowany (Ctrl-C / Ctrl-V), a następnie nazwy plików są zmieniane, ale ich zawartość pozostaje bez zmian;

17.4.1. moduł [pgres_module]

Moduł ten jest kopią modułu [mysql_module] (patrz akapit |skrypt [mysql-04]: wykonanie pliku poleceń SQL|). Zmienia się importy:

Zamiast:


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

piszemy:


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

Sygnatura funkcji [afficher_infos] brzmiała:


def afficher_infos(curseur: MySQLCursor):

Zmienia się na:


def afficher_infos(curseur: cursor)

Sygnatura funkcji [execute_list_of_commands] brzmiała:


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

Zmienia się na:


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

Poza tym nic się nie zmienia.

17.4.2. skrypt [pgres_01]

Skrypt [pgres_01] jest kopią skryptu [mysql_01] (patrz akapit |skrypt [mysql-01]: połączenie z bazą danych MySQL – 1|). Wprowadza się w nim następujące zmiany:

Zamiast:


# import modułu mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

piszemy:


# import modułu psycopg2
from psycopg2 import connect, DatabaseError, InterfaceError

Pozostałe elementy pozostają bez zmian. Wyniki są takie same jak w przypadku MySQL.

17.4.3. skrypt [pgres_02]

Skrypt [pgres_02] jest kopią skryptu [mysql_02] (patrz akapit |skrypt [mysql-02]: połączenie z bazą danych MySQL – 2|). Wprowadza się w nim następujące zmiany:

Zamiast:


# import modułu mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect

piszemy:


# import modułu psycopg2
from psycopg2 import DatabaseError, InterfaceError, connect

Wyniki nie są takie same jak w przypadku skryptu [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

Skrypt [pgres_02] wygląda następująco:


# import modułu mysql.connector
from psycopg2 import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # loguje się, a następnie wylogowuje (login, hasło) z bazy danych [database] na serwerze [host]
    # rzuca wyjątek DatabaseError w przypadku wystąpienia problemu
    connexion = None
    try:
        # połączenie
        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:
        # zamyka się połączenie, jeśli zostało otwarte
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- main
# dane logowania
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# logowanie istniejącego użytkownika
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # wyświetla się komunikat o błędzie
    print(erreur)

# logowanie użytkownika, który nie istnieje
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # wyświetlany jest komunikat o błędzie
    print(erreur)

Chociaż w wierszach 36–41 powinien pojawić się komunikat o błędzie informujący, że połączenie ze skryptem SGBD nie powiodło się, nic nie jest wyświetlane. W rzeczywistości, gdy przyjrzymy się tej kwestii bliżej, okazuje się, że w [except] w wierszach 35–37 wszystko przebiega prawidłowo, ale zmienna [erreur] przyjmuje wartość [None]. Dotyczy to wersji 2.8.4 łącznika [psycopg2].

Problem ten można obejść, wpisując komunikat ogólny, ale mniej precyzyjny:


# logowanie użytkownika, który nie istnieje
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # wyświetlany jest błąd
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

Wyniki są wówczas następujące:


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

Skrypt [pgres_03] jest kopią skryptu [mysql_03] (patrz akapit |skrypt [mysql-03]: utworzenie tabeli MySQL|). Wprowadza się w nim następujące zmiany:

Zamiast:


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

piszemy:


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

Ponadto sygnatura funkcji [execute_sql], która brzmiała:


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

zmienia się na:


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

Pozostałe elementy pozostają bez zmian. Wynik jest następujący:


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

Obecność tabeli [personnes] można sprawdzić za pomocą narzędzia administracyjnego [pgAdmin]:

Image

17.4.5. skrypt [pgres_04]

Skrypt [pgres_04] jest kopią skryptu [mysql_04] (patrz akapit |Skrypt [mysql-04]: wykonanie pliku poleceń SQL|). Wykorzystuje on moduł [pgres_module]:


# pobierana jest konfiguracja aplikacji
import config_04

config = config_04.configure()

# ścieżka systemowa jest skonfigurowana – można przeprowadzić importy
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

Pozostałe elementy pozostają bez zmian.

Tworzymy konfigurację [pgres pgres-04 without_transaction] tak samo, jak to zrobiono w akapicie |skrypt [mysql-04]: wykonanie pliku poleceń SQL|. Podobnie tworzymy konfigurację [pgres pgres-04 with_transaction].

Wykonanie konfiguracji [pgres pgres-04 without_transaction] daje następujące wyniki:


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
  • wiersz 5: konieczne było zmodyfikowanie polecenia usuwania tabeli [personnes]. W przeciwieństwie do łącznika MySQL, łącznik PostgreSQL zgłasza wyjątek, jeśli tabela przeznaczona do usunięcia nie istnieje. Polecenie [drop table] ma wariant [drop table if exists], który nie generuje wyjątku, jeśli tabela nie istnieje. Wykorzystaliśmy go w tym przypadku. Jest to przykład sytuacji, w której dwa polecenia SGBD nie zachowują się tak samo w analogicznych okolicznościach;

Tabela [personnes] w narzędziu [pgAdmin] wygląda następująco:

Image

Wykonanie konfiguracji [pgres pgres_04 with_transaction] daje następujące wyniki:


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

Tabela [personnes] w narzędziu [pgAdmin] wygląda następująco:

Image

W tym przypadku wynik różni się od tego uzyskanego przy użyciu MySQL. Jeśli skrypty zostaną uruchomione w tych samych warunkach, tj. po uruchomieniu skryptu bez transakcji, otrzymujemy następujące wyniki:

  • w przypadku MySQL tabela [personnes] jest pusta;
  • w przypadku skryptu PostgreSQL tabela [personnes] nie jest pusta;

Różnica wynika z odmiennych sposobów, w jakie te dwa skrypty SGBD cofają transakcję:

  • MySQL nie cofa zleceń [drop table] i [create table]. W rezultacie otrzymujemy pustą tabelę [personnes];
  • PostgreSQL cofa zlecenia [drop table] i [create table]. Tabela powraca do stanu, w jakim znajdowała się przed wykonaniem skryptu z transakcją;

17.4.6. skrypt [pgres_05]

Skrypt [pgres_05] jest kopią skryptu [mysql_05] (zob. akapit |Skrypt [mysql-05]: korzystanie z zapytań parametrycznych|). Skrypt został zmodyfikowany w następujący sposób:

Zamiast:


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

piszemy:


# importy
from psycopg2 import connect, DatabaseError, InterfaceError

Pozostałe elementy pozostają bez zmian.

Wyniki uzyskane w [pgAdmin] są następujące:

Image

17.5. Wniosek

Przeniesienie skryptów MySQL do skryptów PostgreSQL przebiegło dość łatwo. Jest to wyjątek. Oba skrypty SGBD nie obsługują tych samych reguł nazewnictwa obiektów co skrypty SQL (bazy danych, tabele, kolumny, ograniczenia, typy danych…), mają też niekompatybilne rozszerzenia SQL… Aby zapewnić łatwą migrację, w obu przypadkach należy trzymać się standardu SQL, nie próbując korzystać z zastrzeżonych rozszerzeń SGBD. Odbywa się to jednak kosztem wydajności.

https://stahe.github.io
Ten kurs-samouczek, napisany przez Serge'a Tahé, jest udostępniany publicznie na warunkach licencji Creative Commons Uznanie autorstwa – Zakaz wykorzystania komercyjnego – Na tych samych warunkach 3.0 bez zmian.