Skip to content

17. Використання SGBD та PostgreSQL

SGBD PostgreSQL є у вільному доступі. Це альтернатива «спільнотній» версії MySQL.

Ми використовуємо її тут, щоб продемонструвати, що перенести скрипти Python / MySQL на скрипти Python / PostgreSQL досить просто.

З SGBD та MySQL архітектура наших скриптів була такою:

З SGBD та PostgreSQL вона буде такою:

Image

17.1. Встановлення SGBD PostgreSQL

Дистрибутиви SGBD та PostgreSQL доступні у URL та [https://www.postgresql.org/download/] (травень 2019 року). Ми покажемо встановлення версії для 64-бітної Windows:

Image

Image

  • у [1-4] завантажується інсталятор з SGBD;

Запускаємо завантажений інсталятор:

Image

  • у полі [6] вкажіть папку для встановлення;

Image

  • у [8] опція [Stack Builder] не потрібна для того, що ми хочемо зробити тут;
  • у полі [10] залиште значення, яке вам буде запропоновано;

Image

  • у [12-13] ми вказали тут пароль [root]. Це буде пароль адміністратора SGBD, який називається [postgres]. PostgreSQL також називає його суперкористувачем;
  • у полі [15] залиште значення за замовчуванням: це порт прослуховування SGBD;

Image

  • у полі [17] залиште значення за замовчуванням;
  • у полі [19] — підсумок конфігурації інсталяції;

Image

Image

У Windows SGBD PostgreSQL встановлюється як служба Windows, що запускається автоматично. У більшості випадків це небажано. Ми змінимо цю конфігурацію. Введіть [services] у рядку пошуку Windows [24-26]:

Image

  • на [29]. Ми бачимо, що служба SGBD PostgreSQL працює в автоматичному режимі. Змінимо це, відкривши властивості служби [30]:

Image

  • у [31-32] встановіть запуск у ручному режимі;
  • у [33] зупиніть службу;

Коли ви захочете запустити SGBD вручну, поверніться до програми [services], клацніть правою кнопкою миші на службі [postgresql] (34) і запустіть її (35).

17.2. Управління PostgreSQL за допомогою інструменту [pgAdmin]

Запустіть службу Windows SGBD PostgreSQL (попередній абзац). Потім, так само, як ви запускали інструмент [services], запустіть утиліту [pgadmin], яка дозволяє адмініструвати SGBD, PostgreSQL та [1-3]:

Image

Можливо, в якийсь момент вас попросять ввести пароль суперкористувача. Він називається [postgres]. Ви встановили цей пароль під час інсталяції SGBD. У цьому документі ми присвоїли суперкористувачеві пароль [root] під час інсталяції.

  • у [4], [pgAdmin] — це веб-додаток;
  • у [5] — список серверів PostgreSQL, виявлених [pgAdmin], тут — 1;
  • у [6] — сервер PostgreSQL, який ми запустили;
  • у [7] — бази даних SGBD, тут 1;
  • у [8] база [postgresql] управляється суперкористувачем [postgres];

Спочатку створимо користувача [admpersonnes] з паролем [nobody]:

Image

Image

  • у [17] ми вказали [nobody];

Image

  • у [21] — це код SQL, який інструмент [pgAdmin] передасть до SGBD PostgreSQL. Це один із способів вивчити власну мову SQL, що належить PostgreSQL;
  • у [22], після підтвердження майстром [Save], було створено користувача [admpersonnes];

Тепер створюємо базу даних [dbpersonnes]:

Image

Клацаємо правою кнопкою миші на [23], а потім на [24-25], щоб створити нову базу даних. На вкладці [26] задаємо ім’я бази даних [27] та її власника [admpersonnes] [28].

Image

  • у [30] — код створення бази SQL;
  • у [31], після перевірки майстром [Save] створюється база даних [dbpersonnes];

Ми будемо використовувати базу даних [dbpersonnes] за допомогою скриптів Python.

17.3. Встановлення Python-коннектора для SGBD PostgreSQL

Image

На схемі вище зображено коннектор, що забезпечує зв’язок між скриптами Python та SGBD PostgreSQL. Існує кілька таких коннекторів. Ми встановлюємо коннектор [psycopg2]. Це робиться в терміналі Python (не має значення, в якій папці відкрито цей термінал). Коннектор встановлюється за допомогою команди [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. Перенесення скриптів MySQL у скрипти PostgreSQL

Image

  • папка [1] зі скриптами MySQL дублюється (Ctrl-C / Ctrl-V), після чого імена файлів змінюються, але їхній вміст залишається незмінним;

17.4.1. модуль [pgres_module]

Цей модуль є копією модуля [mysql_module] (див. параграф |скрипт [mysql-04]: виконання файлу команд SQL|). Змінюємо імпорти:

Замість:


# імпорт
from mysql.connector import DatabaseError, InterfaceError
from mysql.connector.connection import MySQLConnection
from mysql.connector.cursor import MySQLCursor

пишемо:


# імпорт
from psycopg2 import DatabaseError, InterfaceError
from psycopg2.extensions import connection, cursor

Сигнатура функції [afficher_infos] була такою:


def afficher_infos(curseur: MySQLCursor):

Тепер вона виглядає так:


def afficher_infos(curseur: cursor)

Підпис функції [execute_list_of_commands] був таким:


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

Вона стає:


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

В іншому нічого не змінюється.

17.4.2. скрипт [pgres_01]

Скрипт [pgres_01] є копією скрипта [mysql_01] (див. параграф |скрипт [mysql-01]: підключення до бази даних MySQL - 1|). У ньому вносяться такі зміни:

Замість:


# імпорт модуля mysql.connector
from mysql.connector import connect, DatabaseError, InterfaceError

пишемо:


# імпорт модуля psycopg2
from psycopg2 import connect, DatabaseError, InterfaceError

Решта залишається без змін. Результати такі самі, як і з MySQL.

17.4.3. скрипт [pgres_02]

Скрипт [pgres_02] є копією скрипта [mysql_02] (див. параграф |скрипт [mysql-02]: підключення до бази даних MySQL - 2|). У ньому вносяться такі зміни:

Замість:


# імпорт модуля mysql.connector
from mysql.connector import DatabaseError, InterfaceError, connect

пишемо:


# імпорт модуля psycopg2
from psycopg2 import DatabaseError, InterfaceError, connect

Результати відрізняються від результатів скрипта [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

Скрипт [pgres_02] виглядає так:


# імпорт модуля mysql.connector
from psycopg2 import DatabaseError, InterfaceError, connect


# ---------------------------------------------------------------------------------
def connexion(host: str, database: str, login: str, pwd: str):
    # входить, а потім виходить (login, pwd) з бази даних [database] на сервері [host]
    # генерує виняток DatabaseError у разі проблеми
    connexion = None
    try:
        # підключення
        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:
        # з'єднання закривається, якщо воно було відкрите
        if connexion:
            connexion.close()
            print("Déconnexion réussie\n")


# ---------------------------------------------- main
# ідентифікатори з'єднання
USER = "admpersonnes"
PASSWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# вхід існуючого користувача
try:
    connexion(host=HOST, login=USER, pwd=PASSWD, database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # виводиться повідомлення про помилку
    print(erreur)

# вхід користувача, якого не існує
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # видається повідомлення про помилку
    print(erreur)

Хоча в рядках 36–41 мало б з’явитися повідомлення про помилку, що вказує на невдале підключення до SGBD, нічого не відображається. Насправді, якщо детальніше розглянути це питання, виявляється, що рядки 35–37 дійсно передаються до [except], але значення змінної [erreur] дорівнює [None]. Це відбувається у версії 2.8.4 коннектора [psycopg2].

Цю проблему можна обійти, написавши загальне, але менш точне повідомлення:


# вхід користувача, якого не існує
try:
    connexion(host=HOST, login="xx", pwd="yy", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # виводиться помилка
    print(f"Erreur de connexion à la base [{DATABASE}] par l'utilisateur [xx/yy]")

У цьому випадку результати будуть такими:


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. скрипт [pgres_03]

Скрипт [pgres_03] є копією скрипта [mysql_03] (див. параграф |скрипт [mysql-03]: створення таблиці MySQL|). У ньому вносяться такі зміни:

Замість:


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

пишемо:


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

Крім того, сигнатура функції [execute_sql], яка була:


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

стає:


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

Решта залишається без змін. Результат такий:


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

Наявність таблиці [personnes] можна перевірити за допомогою інструменту адміністрування [pgAdmin]:

Image

17.4.5. скрипт [pgres_04]

Скрипт [pgres_04] є копією скрипта [mysql_04] (див. розділ |Скрипт [mysql-04]: виконання файлу команд SQL|). Він використовує модуль [pgres_module]:


# отримано конфігурацію додатка
import config_04

config = config_04.configure()

# syspath налаштовано — можна виконувати імпорт
import sys
from pgres_module import execute_file_of_commands
from psycopg2 import connect, DatabaseError, InterfaceError

Решта залишається без змін.

Створюємо конфігурацію [pgres pgres-04 without_transaction], як це було зроблено в параграфі |скрипт [mysql-04]: виконання файлу команд SQL|. Аналогічно створюємо конфігурацію [pgres pgres-04 with_transaction].

Виконання конфігурації [pgres pgres-04 without_transaction] дає такі результати:


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
  • рядок 5: довелося змінити команду видалення таблиці [personnes]. На відміну від коннектора MySQL, коннектор PostgreSQL генерує виняток, якщо таблиця, яку потрібно видалити, не існує. Команда [drop table] має варіант [drop table if exists], який не генерує виняток, якщо таблиця не існує. Ми використали саме його. Це приклад того, як дві команди SGBD поводяться по-різному в аналогічних ситуаціях;

Таблиця [personnes] у інструменті [pgAdmin] має такий вигляд:

Image

Виконання конфігурації [pgres pgres_04 with_transaction] дає такі результати:


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

Таблиця [personnes] у інструменті [pgAdmin] має такий вигляд:

Image

Тут результат відрізняється від того, що отримано за допомогою MySQL. Якщо виконати скрипти за тих самих умов, тобто після виконання скрипта без транзакції, отримаємо такі результати:

  • зі скриптом MySQL таблиця [personnes] порожня;
  • з PostgreSQL таблиця [personnes] не порожня;

Різниця полягає в тому, що ці два SGBD скасовують транзакцію по-різному:

  • MySQL не скасовує команди [drop table] та [create table]. У результаті таблиця [personnes] залишається порожньою;
  • PostgreSQL скасовує команди [drop table] та [create table]. Таблиця повертається до стану, в якому вона перебувала до виконання скрипта з транзакцією;

17.4.6. скрипт [pgres_05]

Скрипт [pgres_05] є копією скрипта [mysql_05] (див. розділ |Скрипт [mysql-05]: використання параметризованих запитів|). Скрипт змінено наступним чином:

Замість:


# імпорт
from mysql.connector import connect, DatabaseError, InterfaceError

пишемо:


# імпорт
from psycopg2 import connect, DatabaseError, InterfaceError

Решта залишається без змін.

Результати, отримані в [pgAdmin], такі:

Image

17.5. Висновок

Перенесення скриптів MySQL у скрипти PostgreSQL відбулося досить легко. Це виняток. Обидва SGBD не підтримують ті самі правила іменування об’єктів, що й SQL (бази даних, таблиці, стовпці, обмеження, типи даних тощо), мають несумісні розширення SQL… Щоб забезпечити простий перенос, в обох випадках слід дотримуватися стандарту SQL, не намагаючись використовувати власницькі розширення SGBD. Це, однак, відбувається за рахунок продуктивності.

https://stahe.github.io
Цей навчальний курс, написаний Сержем Тахе, надається громадськості на умовах Creative Commons «Вказання авторства — Некомерційне використання — Поділитися на рівних умовах 3.0 без змін.