Skip to content

16. Використання SGBD MySQL

Image

16.1. Встановлення SGBD та MySQL

Щоб отримати доступ до SGBD MySQL, ми встановимо програмне забезпечення Laragon.

16.1.1. Встановлення Laragon

Laragon — це пакет, що об’єднує кілька програм:

  • веб-сервер Apache. Ми будемо використовувати його для написання веб-скриптів на Python;
  • SGBD MySQL;
  • скриптова мова PHP, яку ми не використовуватимемо;
  • сервер Redis, що реалізує кеш для веб-додатків. Ми не будемо використовувати;

Laragon можна завантажити (лютий 2020 року) за наступною адресою:

https://laragon.org/download/

Image

Image

  • Встановлення [1-5] створює таку структуру каталогів:

Image

  • у [6] — папка інсталяції PHP (у цьому документі не використовується);

При запуску [Laragon] з’являється таке вікно:

Image

  • [1]: головне меню Laragon;
  • [2]: кнопка [Start All] запускає веб-сервер Apache, а SGBD — MySQL;
  • [3]: кнопка [WEB] відкриває веб-сторінку [http://localhost];
  • [4]: кнопка [Database] дозволяє керувати SGBD та MySQL за допомогою інструменту [phpMyAdmin]. Перед цим його потрібно встановити;
  • [5]: кнопка [Terminal] відкриває командний рядок;
  • [6]: кнопка [Root] відкриває провідник Windows, налаштований на папку [<laragon>/www], яка є кореневою папкою веб-сайту [http://localhost]. Саме туди потрібно розмістити статичні веб-додатки, що обслуговуються сервером Apache від Laragon;

16.1.2. Створення бази даних

Тепер ми покажемо, як створити базу даних, а також користувача MySQL за допомогою інструменту Laragon.

Image

  • Після запуску Laragon [1] можна адмініструвати через меню [2];
  • у [3-5] встановлюємо інструмент [phpMyAdmin] для адміністрування MySQL, якщо він ще не встановлений;

Image

  • у [6] запускається веб-сервер Apache, а також SGBD та MySQL;
  • у [7] запускається сервер Apache;
  • у [8] запускаються SGBD та MySQL;

Image

  • у [8-10] створюється база даних, яку називають [dbpersonnes] [11]. Ми створимо базу даних про осіб;

Image

  • у [11] ми будемо керувати базою даних, яку щойно створили;

Image

  • Операція [Bases de données] надсилає веб-запит до URL, [http://localhost/phpmyadmin] та [12]. Відповідає веб-сервер Apache від Laragon. URL [http://localhost/phpmyadmin] — це URL утиліти [phpMyAdmin], яку ми встановили раніше [5]. Ця утиліта дозволяє керувати базами даних MySQL;
  • за замовчуванням облікові дані адміністратора бази такі: root [13] без пароля [14];

Image

  • у [16] — це база даних, яку ми створили раніше;

Image

  • наразі маємо базу [dbpersonnes] [17], яка є порожньою [18];

Створюємо користувача [admpersonnes] з паролем [nobody], який матиме всі права на базу даних [dbpersonnes]:

Image

  • у [19] ми перебуваємо в базі [dbpersonnes];
  • у [20] вибираємо вкладку [Privileges];
  • у [21-22] бачимо, що користувач [root] має всі права на базу даних [dbpersonnes];
  • у [23] створюється новий користувач;

Image

  • у [25-26] користувач матиме ідентифікатор [admdbpersonnes];
  • у [27-29] його пароль буде [nobody];
  • у [30] значення phpMyAdmin вказує, що пароль є дуже слабким (його легко зламати). У виробничому середовищі краще генерувати надійний пароль із значенням [31];
  • у [32] вказується, що користувач [admdbpersonnes] повинен мати всі права на базу даних [dbpersonnes];
  • у [33] підтверджується надана інформація;

Image

  • у [35], phpMyAdmin вказує, що користувач було створено;
  • у [36] — наказ SQL, який було видано на основі;
  • у [37] користувач [admpersonnes] має всі права на базу даних [dbpersonnes];

Тепер ми маємо:

  • базу даних MySQL [dbpersonnes];
  • користувач [admpersonnes/nobody], який має всі права на цю базу даних;

16.2. Встановлення пакета [mysql-connector-python]

Ми напишемо скрипти на Python для роботи з раніше створеною базою даних із такою архітектурою:

Image

Коннектор слугує для ізоляції коду Python від використовуваного SGBD. Існують коннектори для різних моделей SGBD, і всі вони мають однаковий інтерфейс. Отже, коли вищезазначені SGBD та MySQL замінити на SGBD та PostgreSQL, архітектура набуває такого вигляду:

Image

Оскільки всі з'єднувачі SGBD відповідають одному й тому ж інтерфейсу, зазвичай скрипт Python не потрібно змінювати. Насправді більшість SGBD мають власний SQL:

  • вони відповідають стандарту SQL (Structured Query Language);
  • але розширюють його, оскільки він є недостатнім, за допомогою власних розширень мови;

Тому часто трапляється, що під час зміни SGBD у скриптах доводиться вносити зміни до SQL.

Сама по собі Python не надає можливості роботи з базою даних MySQL. Для цього потрібно завантажити пакет. Існує кілька таких пакетів. Тут ми будемо використовувати пакет [mysql-connector-python], який є офіційним коннектором компанії Oracle, що володіє MySQL.

Встановлення пакета відбуватиметься у вікні [Terminal] у Pycharm:

Image

  • папка [2] не має значення для подальших дій;

У терміналі введіть команду [pip search MySQL]:

  • [pip] (Package Installer for Python) — це інструмент для встановлення пакетів Python. Інструмент [pip] підключається до репозиторію, що містить пакети Python;
  • [search MySQL]: запитує список пакетів, у назві яких міститься термін [MySQL] (регістр не має значення);

Результати виконання команди такі:


mysql (0.0.2)                                                   - Virtual package for MySQL-python
jx-mysql (3.49.20042)                                           - jx-mysql - JSON Expressions for MySQL
weibo-mysql (0.1)                                               - insert mysql
bits-mysql (1.0.3)                                              - BITS MySQL
MySQL-python (1.2.5)                                            - Python interface to MySQL
deployfish-mysql (0.2.13)                                       - Deployfish MySQL plugin
mtstat-mysql (0.7.3.3)                                          - MySQL Plugins for mtstat
bottle-mysql (0.3.1)                                            - MySQL integration for Bottle.
WintxDriver-MySQL (2.0.0-1)                                     - MySQL support for Wintx
py-mysql (1.0)                                                  - Operating Mysql for Python.
mysql-utilities (1.4.3)                                         - MySQL Utilities 1.4.3 (part of MySQL Workbench Distribution 6.0.0)
….                                        - Tool to move slices of data from one MySQL store to another
mysql-tracer (2.0.2)                                            - A MySQL client to run queries, write execution reports and export results
mysql-utils (0.0.2)                                             - A simple MySQL library including a set of utility APIs for Python database programming
mysql-connector-repackaged (0.3.1)                              - MySQL driver written in Python
dffml-source-mysql (0.0.5)                                      - DFFML Source for MySQL Protocol
mysql-connector-python (8.0.19)                                 - MySQL driver written in Python
  INSTALLED: 8.0.19 (latest)
prometheus-mysql-exporter (0.2.0)                               - MySQL query Prometheus exporter
backwork-backup-mysql (0.3.0)                                   - Backwork plug-in for MySQL backups.
django-mysql-manager (0.1.4)                                    - django-mysql-manager is a Django based management interface for MySQL users and databases.
….                                              - mysql operate

C:\Data\st-2020\dev\python\cours-2020\v-01>

Було перелічено всі модулі, у назві або описі яких міститься ключове слово MySQL. Той, який ми будемо використовувати (лютий 2020 р.), — це [mysql-connector-python], рядок 17. Щоб його встановити, у терміналі вводимо команду [pip install -U mysql-connector-python]:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip install -U mysql-connector-python
Collecting mysql-connector-python
  Using cached mysql_connector_python-8.0.19-py2.py3-none-any.whl (355 kB)
Requirement already satisfied, skipping upgrade: protobuf==3.6.1 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (3.6.1)
Requirement already satisfied, skipping upgrade: dnspython==1.16.0 in c:\myprograms\python38\lib\site-packages (from mysql-connector-python) (1.16.0)
Requirement already satisfied, skipping upgrade: six>=1.9 in c:\users\serge\appdata\roaming\python\python38\site-packages (from protobuf==3.6.1->mysql-connector-python) (1.14.0)
Requirement already satisfied, skipping upgrade: setuptools in c:\myprograms\python38\lib\site-packages (from protobuf==3.6.1->mysql-connector-python) (41.2.0)
Installing collected packages: mysql-connector-python
Successfully installed mysql-connector-python-8.0.19
  • рядок 1: опція [install -U] (U=upgrade) вимагає найновішої версії різних пакетів, пов’язаних із пакетом [mysql-connector-python];

Щоб дізнатися, які пакети встановлені в середовищі Python на нашій машині, вводимо команду [pip list]:


C:\Data\st-2020\dev\python\cours-2020\v-01>pip list
Package                Version
---------------------- ----------
asgiref                3.2.3
astroid                2.3.3
atomicwrites           1.3.0
attrs                  19.3.0
certifi                2019.11.28
…
MarkupSafe             1.1.1
mccabe                 0.6.1
more-itertools         8.1.0
mysql-connector-python 8.0.19
mysqlclient            1.4.6
packaging              20.0
pip                    20.0.1
pipenv                 2018.11.26
…
  • рядок 13: пакет [mysql-connector-python] дійсно присутній;

Щоб дізнатися, як використовувати пакет [mysql-connector-python] для управління базою даних MySQL, перейдемо на сайт пакета |https://dev.mysql.com/doc/connector-python/en/|. Далі наведено низку прикладів.

16.3. Скрипт [mysql_01]: підключення до бази даних MySQL — 1

Скрипт [mysql_01] демонструє перший етап роботи з базою даних. Він дозволить нам перевірити, чи можемо ми підключитися до раніше створеної бази даних [dbpersonnes].


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

# підключення до бази даних MySql [dbpersonnes]
# ідентифікатор користувача — (admpersonnes,nobody)
USER = "admpersonnes"
PWD = "nobody"
HOST = "localhost"
DATABASE = "dbpersonnes"

# почалося
connexion = None
try:
    print("Connexion au SGBD MySQL en cours...")
    # вхід
    connexion = connect(host=HOST, user=USER, password=PWD, database=DATABASE)
    # відстеження
    print(
        f"Connexion MySQL réussie à la base database={DATABASE}, host={HOST} sous l'identité user={USER}, passwd={PWD}")
except (InterfaceError, DatabaseError) as erreur:
    # виводиться повідомлення про помилку
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    # закриття з'єднання, якщо воно було відкрите
    if connexion:
        connexion.close()

Примітки

  • рядок 2: імпортуємо деякі функції та класи з модуля [mysql.connector];
  • рядки 6–7: ідентифікатори користувача, який буде підключатися;
  • рядок 8: комп’ютер, на якому розміщена база даних. Адже коннектор MySQL дозволяє працювати з віддаленою базою даних;
  • рядок 9: ім’я бази даних, до якої потрібно підключитися;
  • рядки 11–26: скрипт підключить (рядок 16) користувача [admpersonnes / nobody] до бази даних [dbpersonnes];
  • рядки 20–26: підключення може завершитися невдачею. Тому його виконують у блоці try / except / finally;
  • рядок 16: метод connect модуля [mysq.connector] приймає різні іменовані параметри:
    • user: користувач, якому належить з’єднання [admpersonnes];
    • password: пароль користувача [nobody];
    • host: машина з SGBD, MySQL, [localhost];
    • database: база даних, до якої здійснюється підключення. Необов’язкове.
  • рядок 20: якщо генерується виняток, він має тип [DatabaseError] або [InterfaceError];
  • рядки 23–26: у розділі [finally] з’єднання закривається;

Результати

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/mysql/mysql_01.py
Connexion au SGBD MySQL en cours...
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody

Process finished with exit code 0

16.4. скрипт [mysql_02]: підключення до бази даних MySQL - 2

У цьому новому скрипті підключення до бази даних винесено в окрему функцію:


# імпорт модуля mysql.connector
from mysql.connector 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="xx", database=DATABASE)
except (InterfaceError, DatabaseError) as erreur:
    # виводиться повідомлення про помилку
    print(erreur)

Примітки:

  • рядки 6–19: функція [connexion], яка намагається підключити, а потім відключити користувача від бази даних [dbpersonnes]. Виводить результат;
  • рядки 29–41: головна програма — двічі викликає метод connexion і виводить можливі винятки;

Результати

1
2
3
4
5
6
7
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/mysql/mysql_02.py
Connexion MySQL réussie à la base database=dbpersonnes, host=localhost sous l'identité user=admpersonnes, passwd=nobody
Déconnexion MySQL réussie

1045 (28000): Access denied for user 'xx'@'localhost' (using password: YES)

Process finished with exit code 0

16.5. скрипт [mysql_03]: створення таблиці MySQL

Тепер, коли ми знаємо, як встановити з’єднання з SGBD MySQL, почнемо надсилати команди SQL через це з’єднання. Для цього ми під’єднаємося до створеної бази даних [dbpersonnes] і використаємо це з’єднання для створення таблиці в базі.


# імпорт
import sys

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


# ---------------------------------------------------------------------------------
def execute_sql(connexion: MySQLConnection, update: str):
    # виконується запит на оновлення під час підключення
    curseur = None
    try:
        # запит на курсор
        curseur = connexion.cursor()
        # виконує запит на оновлення для з'єднання
        curseur.execute(update)
    finally:
        # закриття курсора, якщо він був отриманий
        if curseur:
            curseur.close()


# ---------------------------------------------- main
# ідентифікатори з'єднання
# ідентифікатор користувача
ID = "admpersonnes"
PWD = "nobody"
# хост-машина СГБД
HOST = "localhost"
# ідентифікатор бази даних
DATABASE = "dbpersonnes"

# почнемо крок за кроком
try:
    # підключення
    connexion = connect(host=HOST, user=ID, password=PWD, database=DATABASE)
    # режим AUTOCOMMIT
    connexion.autocommit = True
except (InterfaceError, DatabaseError) as erreur:
    # виводимо повідомлення про помилку
    print(f"L'erreur suivante s'est produite : {erreur}")
    # вихід
    sys.exit()

# видалення таблиці «особи», якщо вона існує
# якщо вона не існує, виникне помилка — її ігнорують
requête = "drop table personnes"
try:
    execute_sql(connexion, requête)
except (InterfaceError, DatabaseError):
    pass

# створення таблиці «особи»
requête = "create table personnes (id int PRIMARY KEY, prenom varchar(30) NOT NULL, nom varchar(30) NOT NULL, age integer NOT NULL, " \
          "unique(nom,prenom)) "
try:
    # виконання запиту
    execute_sql(connexion, requête)
    # виведення на екран
    print(f"{requête} : requête réussie")
except (InterfaceError, DatabaseError) as erreur:
    # виводиться повідомлення про помилку
    print(f"L'erreur suivante s'est produite : {erreur}")
finally:
    # відключення
    connexion.close()

Примітки:

  • рядок 9: функція execute_sql виконує запит SQL через відкрите з’єднання;
  • рядок 14: операції SQL над з’єднанням виконуються через спеціальний об’єкт, який називається курсором;
  • рядок 14: отримання курсора;
  • рядок 16: виконання запиту SQL;
  • рядки 17–20: незалежно від того, чи сталася помилка, курсор закривається. Це звільняє пов’язані з ним ресурси. Якщо виникла виняткова ситуація, то вона тут не обробляється. Вона передається до коду, що викликає;
  • рядки 33–43: встановлення з’єднання з базою даних;
  • рядок 38: режим AUTOCOMMIT=True для з’єднання означає, що кожне виконання запиту відбувається в рамках автоматичної транзакції. Режим за замовчуванням — AUTOCOMMIT=False, де відповідальність за управління транзакціями покладається на розробника. Транзакція — це механізм, що охоплює виконання декількох запитів від 1 до n. Або всі вони виконуються успішно, або жоден. Отже, якщо запити від 1 до i виконуються успішно, але запит i+1 завершується з помилкою, то запити від 1 до i будуть «скасовані», щоб база даних повернулася до стану, який вона мала до виконання запиту 1;
  • тут є два запити SQL (рядки 49, 58). Кожен із них буде виконано в рамках транзакції. Те, що другий запит завершиться невдало, жодним чином не вплине на перший;
  • рядки 45–51: виконується наказ SQL [drop table personnes]. Він видаляє таблицю з назвою [personnes]. Якщо вона не існує, може бути повідомлено про помилку. Ця помилка ігнорується (рядок 51);
  • рядки 53–55: команда створення таблиці [personnes]. Таблицю можна розглядати як сукупність рядків і стовпців. Команда створення визначає імена стовпців:
    • [id]: цілочисельний ідентифікатор. Він буде унікальним для кожної особи. Це буде первинний ключ (PRIMARY KEY). Це означає, що в таблиці цей стовпець не містить двох однакових значень і може використовуватися для ідентифікації особи;
    • [nom]: рядок довжиною не більше 30 символів;
    • [prenom]: рядок довжиною не більше 30 символів;
    • [age]: ціле число;
    • атрибут [NOT NULL] для кожного з цих стовпців означає, що в одному рядку таблиці жоден із трьох стовпців не може бути порожнім;
    • параметр [unique(nom,prenom)] називається обмеженням. У даному випадку обмеженням для рядків є те, що кортеж (прізвище, ім’я) у рядку має бути унікальним у таблиці. Це означає, що в таблиці можна однозначно ідентифікувати особу, прізвище та ім’я якої відомі;
  • рядки 56–60: виконання команди SQL;
  • рядки 61–63: обробка можливого винятку;
  • рядки 64–66: відключення від бази даних;

Результати

1
2
3
4
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/mysql/mysql_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

Перевірка за допомогою [phpMyAdmin]:

Image

  • база даних [dbpersonnes] [1] містить таблицю [personnes] [2], яка має структуру [3-4], первинний ключ [5] та обмеження унікальності [6];

16.6. скрипт [mysql_04]: виконання файлу команд SQL

Після попереднього створення таблиці [personnes] ми тепер заповнюємо її, а потім обробляємо за допомогою команд SQL.

Ми хочемо виконати команди SQL з текстового файлу:

Image

Вміст файлу [commandes.sql] такий:


# видалення таблиці [personnes]
drop table personnes
# створення таблиці «особи»
create table personnes (prenom varchar(30) not null, nom varchar(30) not null, age integer not null, primary key (nom,prenom))
# додано двох осіб
insert into personnes(prenom, nom, age) values('Paul','Langevin',48)
insert into personnes(prenom, nom, age) values ('Sylvie','Lefur',70)
# перегляд таблиці
select prenom, nom, age from personnes
# навмисна помилка
xx
# додавання трьох осіб
insert into personnes(prenom, nom, age) values ('Pierre','Nicazou',35)
insert into personnes(prenom, nom, age) values ('Geraldine','Colou',26)
insert into personnes(prenom, nom, age) values ('Paulette','Girond',56)
# перегляд таблиці
select prenom, nom, age from personnes
# список осіб за алфавітним порядком прізвищ, а при однакових прізвищах — за алфавітним порядком імен
select nom,prenom from personnes order by nom asc, prenom desc
# список осіб, вік яких знаходиться в діапазоні [20,40], у порядку спадання віку
# а потім, за однакового віку, за алфавітом прізвищ, а за однакових прізвищ — за алфавітом імен
select nom,prenom,age from personnes where age between 20 and 40 order by age desc, nom asc, prenom asc
# додавання пані Брюно
insert into personnes(prenom, nom, age) values('Josette','Bruneau',46)
# оновлення її віку
update personnes set age=47 where nom='Bruneau'
# список осіб із прізвищем Брюно
select nom,prenom,age from personnes where nom='Bruneau'
# вилучення пані Брюно
delete from personnes where nom='Bruneau'
# список осіб із прізвищем Брюно
select nom,prenom,age from personnes where nom='Bruneau'

Спочатку ми визначаємо функції, які розміщуємо в модулі, щоб мати змогу використовувати їх повторно:

Image

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


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


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
     # виводить результат виконання команди SQL
    …


# ---------------------------------------------------------------------------------
def execute_list_of_commands(connexion: MySQLConnection, sql_commands: list,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    # використовує відкрите з'єднання [connexion]
    # виконує через це з’єднання команди SQL, що містяться у списку [sql_commands]
    # цей файл є файлом команд SQL, які виконуються по одній на рядок
    # якщо «супровід»=True, то після кожного виконання команди SQL виводиться повідомлення про успіх або невдачу
    # якщо «зупинка»=True, функція зупиняється при першій виявленій помилці, інакше вона виконує всі команди SQL
    # якщо with_transaction=True, то будь-яка помилка скасовує всі команди SQL, виконані раніше
    # якщо with_transaction=False, то помилка ніяк не впливає на команди SQL, виконані раніше
    # функція повертає список [erreur1, erreur2, ...]

    ….


# ---------------------------------------------------------------------------------
def execute_file_of_commands(connexion: MySQLConnection, sql_filename: str,
                             suivi: bool = False, arrêt: bool = True, with_transaction: bool = True):
    # використовує відкрите з’єднання [connexion]
    # виконує через це з'єднання команди SQL, що містяться у текстовому файлі sql_filename
    # цей файл є файлом команд SQL, які виконуються по одній на рядок
    # якщо «супровід»=True, то після кожного виконання команди SQL виводиться повідомлення про успіх або невдачу
    # якщо «зупинка»=True, функція зупиняється при першій виявленій помилці, інакше вона виконує всі команди SQL
    # якщо with_transaction=True, то будь-яка помилка скасовує всі команди SQL, виконані раніше
    # якщо with_transaction=False, то помилка ніяк не впливає на команди SQL, виконані раніше
    # функція повертає список [erreur1, erreur2, ...]

    # обробка файлу SQL
    try:
        # відкриття файлу для читання
        file = open(sql_filename, "r")
        # обробка
        return execute_list_of_commands(connexion, file.readlines(), suivi, arrêt, with_transaction)
    except BaseException as erreur:
        # повертається масив помилок
        return [f"Le fichier {sql_filename} n'a pu être être exploité : {erreur}"]

Примітки:

  • рядок 29: функція [execute_file_of_commands] виконує команди SQL, що містяться у текстовому файлі з назвою [sql_filename]:
  • значення параметрів можна дізнатися з коментарів у рядках 31–38;
  • рядки 40–48: обробляється текстовий файл [sql_filename];
  • рядок 43: відкриття файлу;
  • рядок 34: виконання функції [execute_list_of_commands], яка виконує команди SQL, що передаються їй у вигляді списку. Цей список тут складається зі списку всіх рядків текстового файлу [file.readlines()] (рядок 45);

Функція [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):
    # використовує відкрите з'єднання [connexion]
    # виконує через це з'єднання команди SQL, що містяться у списку [sql_commands]
    # цей файл є файлом команд SQL, які виконуються по одній на рядок
    # якщо «супровід»=True, то після кожного виконання команди SQL виводиться повідомлення про успіх або невдачу
    # якщо «зупинка»=True, функція зупиняється при першій виявленій помилці, інакше вона виконує всі команди SQL
    # якщо with_transaction=True, то будь-яка помилка скасовує всі команди SQL, виконані раніше
    # якщо with_transaction=False, то помилка ніяк не впливає на команди SQL, виконані раніше
    # функція повертає список [erreur1, erreur2, ...]

    # ініціалізації
    curseur = None
    connexion.autocommit = not with_transaction
    erreurs = []
    try:
        # потрібен повзунок
        curseur = connexion.cursor()
        # виконання sql_commands SQL, що містяться в sql_commands
        # виконуються по черзі
        for command in sql_commands:
            # видаляються пробіли на початку та в кінці поточної команди
            command = command.strip()
            # чи маємо ми порожню команду або коментар? Якщо так, переходимо до наступної команди
            if command == '' or command[0] == "#":
                continue
            # виконання поточної команди
            error = None
            try:
                curseur.execute(command)
            except (InterfaceError, DatabaseError) as erreur:
                error = erreur
            # Чи сталася помилка?
            if error:
                # ще одна помилка
                msg = f"{command} : Erreur ({error})"
                erreurs.append(msg)
                # відстежувати екран чи ні?
                if suivi:
                    print(msg)
                # зупинитися?
                if with_transaction or arrêt:
                    # вивести список помилок
                    return erreurs
            else:
                # помилок немає
                if suivi:
                    print(f"[{command}] : Exécution réussie")
                # вивести результат виконання команди
                afficher_infos(curseur)
        # повертаємо масив помилок
        return erreurs
    finally:
        # закриття курсора
        if curseur:
            curseur.close()
        # підтвердження / скасування транзакції, якщо вона існує
        if with_transaction:
            if erreurs:
                # скасування
                connexion.rollback()
            else:
                # підтвердження
                connexion.commit()

Примітки

  • рядок 2: функція [execute_list_of_commands] виконує команди SQL, що містяться у списку [sql_commands]:
  • значення параметрів можна дізнатися з коментарів до рядків 4–11;
  • рядок 2: отримане з’єднання є відкритим з’єднанням із базою даних;
  • рядок 15: якщо потрібно, щоб усі команди зі списку [sql_commands] виконувалися в рамках транзакції, то слід працювати в режимі AUTOCOMMIT=False. В іншому випадку робота відбуватиметься в режимі AUTOCOMMIT=True, і тоді кожна з команд зі списку [sqlCommands] виконуватиметься в рамках автоматичної транзакції, а загальної транзакції не буде;
  • рядок 19: запитується курсор для виконання різних команд SQL;
  • рядки 22–51: команди виконуються по черзі;
  • рядки 26–27: у списку команд SQL допускаються порожні рядки та коментарі. У цьому випадку команда просто ігнорується;
  • рядки 30–33: виконання поточного запиту;
  • рядки 35–45: обробляється випадок можливої помилки під час виконання поточного запиту;
  • рядки 37–38: помилка додається до масиву помилок;
  • рядки 40–41: якщо було запрошено відстеження, то виводиться повідомлення про помилку;
  • рядки 43–45: якщо викликаючий код вимагав зупинки після першої помилки або використання транзакції, то потрібно зупинитися. Повертається масив помилок;
  • рядки 46–51: випадок, коли під час виконання поточного запиту помилок не сталося;
  • рядки 48–49: якщо було запрошено відстеження, виводиться виконаний запит із позначкою «успішно»;
  • рядки 50–51: виводиться результат виконаного запиту. До функції [afficher_infos] ми повернемося трохи пізніше;
  • рядки 54–65: оператор [finally] виконується в будь-якому випадку, незалежно від того, чи стався виняток, чи ні;
  • рядки 56–57: закриття курсора. Це звільняє ресурси, виділені для нього;
  • рядки 59–65: обробляється випадок, коли викликаючий код вимагав, щоб команди SQL виконувалися в транзакції;
  • рядок 60: перевіряється, чи список [erreurs] порожній, що означає відсутність винятків. У цьому випадку транзакція підтверджується (рядок 65), інакше вона скасовується (рядок 62);

Функція [afficher_infos] відображає результат запиту:


# ---------------------------------------------------------------------------------
def afficher_infos(curseur: MySQLCursor):
    print(type(curseur))
    # виводить результат виконання команди SQL
    # чи це був запит SELECT?
    if curseur.description:
        # курсор має опис — отже, він виконав запит SELECT
        # опис[i] — це опис стовпця № i у запиті SELECT
        # описQZXW2HTMLBW2ldZQXQZXW2HTMLBWzBdZQX — це назва стовпця № i у запиті SELECT
        # виводяться назви полів
        titre = ""
        for i in range(len(curseur.description)):
            titre += curseur.description[i][0] + ", "
        # виводиться список полів без кінцевої коми
        print(titre[0:len(titre) - 1])
        # розділювальний рядок
        print("*" * (len(titre) - 1))
        #— поточний рядок списку
        ligne = curseur.fetchone()
        while ligne:
            # виводиться
            print(ligne)
            # наступний рядок списку
            ligne = curseur.fetchone()
        # розділювальний рядок
        print("*" * (len(titre) - 1))
    else:
        # курсор не має поля [description] — отже, він виконав команду SQL
        # команду оновлення (insert, delete, update)
        print(f"nombre de lignes modifiées : {curseur.rowcount}")

Примітки

  • рядок 1: параметром функції є курсор, який щойно виконав команду SQL. Залежно від того, чи є ця команда SELECT, чи командою оновлення INSERT, UPDATE, DELETE, вміст курсора буде різним;
  • рядок 6: якщо курсор має поле [description], то він виконав SELECT, а [description] описує поля, запитувані в SELECT:
    • description[i] описує поле № i, запитуване в SELECT. Це список;
    • description[i][0] — це назва поля № i;
  • рядки 11–17: відображаються назви полів, запитуваних SELECT;
  • рядки 18–24: обробляється результат SELECT;
  • рядки 20, 24: результат SELECT обробляється послідовно. Цей результат є набором рядків. Поточний рядок отримується за допомогою [curseur.fetchone()] (рядок 19). У результаті отримуємо кортеж;
  • рядки 27–30: якщо курсор не має поля [description], то він виконав команди оновлення INSERT, UPDATE, DELETE. Тоді можна дізнатися, скільки рядків таблиці було змінено в результаті виконання цієї команди;
  • рядок 30: [curseur.rowcount] — це саме це число;

Головний скрипт [mysql-04] використовує модуль [mysql_module], який ми щойно описали:

Image

Файл [config_04] налаштовує контекст виконання скрипта [mysql_04]:


def configure():
    import os

    # абсолютний шлях до папки з файлом конфігурації
    script_dir = os.path.dirname(os.path.abspath(__file__))
    # конфігурація папок syspath
    absolute_dependencies = [
        # локальні папки
        f"{script_dir}/shared",
    ]

    # зафіксування syspath
    from myutils import set_syspath
    set_syspath(absolute_dependencies)

    # застосовуємо конфігурацію
    return {
        # файл команд SQL
        "commands_filename": f"{script_dir}/data/commandes.sql",
        # ідентифікатори для підключення до бази даних
        "host": "localhost",
        "database": "dbpersonnes",
        "user": "admpersonnes",
        "password": "nobody"
    }

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


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

config = config_04.configure()

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

# ---------------------------------------------- main
# перевірка синтаксису виклику
# argv[0] true / false
args = sys.argv
erreur = len(args) != 2
if not erreur:
    with_transaction = args[1].lower()
    erreur = with_transaction != "true" and with_transaction != "false"
# помилка?
if erreur:
    print(f"syntaxe : {args[0]} true / false")
    sys.exit()

# обчислення тексту
with_transaction = with_transaction == "true"
if with_transaction:
    texte = "avec transaction"
else:
    texte = "sans transaction"

# екранні журнали
print("--------------------------------------------------------------------")
print(f"Exécution du fichier SQL {config['commands_filename']} {texte}")
print("--------------------------------------------------------------------")

# виконання команд SQL із файлу
connexion = None
try:
    # підключення до БД
    connexion = connect(host=config['host'], user=config['user'], password=config['password'],
                        database=config['database'])
    # виконання файлу команд SQL
    erreurs = execute_file_of_commands(connexion, config["commands_filename"], suivi=True, arrêt=False,
                                       with_transaction=with_transaction)
except (InterfaceError, DatabaseError) as erreur:
     # виведення повідомлення про помилку
    print(f"L'erreur fatale suivante s'est produite : {erreur}")
    # програма завершується
    sys.exit()
finally:
    # закриття з'єднання, якщо воно було відкрите
    if connexion:
        connexion.close()

# виведення кількості помилок
print("--------------------------------------------------------------------")
print(f"Exécution terminée")
print("--------------------------------------------------------------------")
print(f"Il y a eu {len(erreurs)} erreur(s)")
# відображення помилок
for erreur in erreurs:
    print(erreur)

Примітки

  • рядки 1–4: налаштування скрипта;
  • рядок 8: імпорт модуля [mysql_module], описаного вище:
  • рядки 12–22: скрипт [mysql-04] очікує на параметр, який повинен мати одне з значень [true / false]. Цей параметр вказує, чи слід виконувати файл команд SQL у рамках транзакції (true) чи ні (false);
  • рядок 14: параметри, передані користувачем скрипту, містяться у списку [sys.argv];
  • рядок 15: потрібно два параметри, наприклад [mysql-04 true]. Ім’я скрипта вважається одним із параметрів;
  • рядки 17–18: якщо параметрів дійсно два, другий повинен бути рядком значень «true» або «false»;
  • рядки 24–29: обчислення тексту, що відображається в рядку 33;
  • рядки 39–44: виконуються команди з файлу [./data/commandes.sql];
  • рядки 45–49: якщо під час підключення (рядок 40) сталася помилка або помилка, яку скрипт [execute_file_of_commands] не обробляє, виводиться повідомлення про помилку та зупиняється виконання;
  • рядки 55–62: у разі успішного виконання виводиться кількість помилок, що виникли під час виконання команд SQL;

Виконання № 1

Спочатку виконуємо операцію без транзакції. Для цього створимо конфігурацію виконання, як це було зроблено в розділі |конфігурація контексту виконання|:

Image

  • у [1-4] створюємо конфігурацію виконання на Python;

Image

  • [5]: назва конфігурації виконання;
  • [6]: шлях до скрипта, що виконується;
  • [7]: параметри скрипта;
  • [8]: папка виконання;

Отже, ця конфігурація відповідає виконанню файлу SQL із транзакцією. Натисніть кнопку [Apply], щоб підтвердити конфігурацію.

Таким самим чином створюємо конфігурацію виконання [mysql mysql-04 without_transaction]:

Image

Отже, ця конфігурація відповідає запуску файлу SQL без транзакції. Натисніть кнопку [Apply], щоб підтвердити конфігурацію.

Спочатку виконаємо версію без транзакції:

Image

Отже, результати такі:


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/mysql/mysql_04.py false
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql sans transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[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 : 0
[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 (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
[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 (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)

Process finished with exit code 0

Примітки:

  • рядок 19: бачимо, що після помилки виконання команд SQL продовжилося, оскільки виконання відбувалося без транзакції та з параметром [arrêt=False]. Отже, усі команди SQL були виконані. Тому ми повинні мати таблицю [personnes], яка відображає це виконання;

Перевірка за допомогою phpMyAdmin:

Image

Виконання № 2

Тепер ми виконуємо конфігурацію [mysql mysql-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/mysql/mysql_04.py true
--------------------------------------------------------------------
Exécution du fichier SQL C:\Data\st-2020\dev\python\cours-2020\python3-flask-2020\databases\mysql/data/commandes.sql avec transaction
--------------------------------------------------------------------
[drop table personnes] : Exécution réussie
nombre de lignes modifiées : 0
[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 : 0
[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 (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)
--------------------------------------------------------------------
Exécution terminée
--------------------------------------------------------------------
Il y a eu 1 erreur(s)
xx : Erreur (1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx' at line 1)

Process finished with exit code 0

Примітки:

  • рядок 19: бачимо, що після помилки більше не виконується наказ SQL, оскільки виконання відбувалося в транзакції, і при першій виявленій помилці ми скасували транзакцію та зупинили виконання наказу SQL. Це означає, що результати команд у рядках 9, 11, 13 були скасовані. Отже, таблиця [personnes] має бути порожньою;

Перевірка за допомогою phpMyAdmin:

Image

  • у [5] видно, що таблиці [personnes] та [2] порожні;

16.7. скрипт [mysql_05]: використання параметризованих запитів

Скрипт [mysql_05] вводить поняття параметризованих запитів:


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

# ідентифікатор користувача
ID = "admpersonnes"
PWD = "nobody"
# хост-машина СГБД
HOST = "localhost"
# ідентифікатор бази даних
BASE = "dbpersonnes"

# список осіб (прізвище, ім’я, вік)
personnes = []
for i in range(5):
    personnes.append((i, f"n0{i}", f"p0{i}", i + 10))
personnes.append((40, "d'Aboot", "Y'éna", 18))
# інший список осіб
autresPersonnes = []
for i in range(5):
    autresPersonnes.append((i + 100, f"n1{i}", f"p1{i}", i + 20))
autresPersonnes.append((200, "d'Aboot", "F'ilhem", 34))

# доступ до SGBD
connexion = None
try:
    # вхід
    connexion = connect(host=HOST, user=ID, password=PWD, database=BASE)
    # курсор
    curseur = connexion.cursor()
    # видалення існуючих записів
    curseur.execute("delete from personnes")
    # додавання осіб поодинці за допомогою підготовленого запиту
    for personne in personnes:
        curseur.execute("insert into personnes(id,nom,prenom,age) values(%s,%s,%s,%s)", personne)
    # масове додавання списку осіб
    curseur.executemany("insert into personnes(id,nom,prenom,age) values(%s, %s,%s,%s)", autresPersonnes)
    # підтвердження транзакції
    connexion.commit()
except (DatabaseError, InterfaceError) as erreur:
    # відображення помилки
    print(f"L'erreur suivante s'est produite : {erreur}")
    # скасування транзакції
    if connexion:
        connexion.rollback()
finally:
    # завершення сеансу
    if connexion:
        connexion.close()

Примітки

  • рядки 12–21: створюються два списки осіб, які слід включити до бази даних [dbpersonnes];
  • рядок 27: підключення до бази даних;
  • рядок 31: видалення вмісту таблиці [personnes];
  • рядки 33–34: вставлення осіб за допомогою налаштованого запиту. У рядку 34 першим параметром є команда SQL, яку потрібно виконати. Вона є неповною. Вона містить параметри [%s], які будуть замінені один за одним і в порядку на значення зі списку другого параметра;
  • рядок 36: введення осіб, цього разу з єдиною інструкцією [curseur.executemany]. Другий параметр [executemany] є списком списків;

Переваги параметризованих запитів полягають у двох аспектах:

  • вони виконуються швидше, ніж «жорсткі» запити, які потрібно аналізувати під час кожного виконання. Параметризований запит [executemany] аналізується лише один раз. Потім він виконується n разів без повторного аналізу;
  • параметри, що вводяться в параметризований запит, перевіряються. Якщо вони містять зарезервовані символи, наприклад апостроф, то ці символи «захищаються», щоб вони не заважали виконанню команди SQL. Саме для перевірки цього моменту до списку було включено імена та прізвища з апострофами (рядки 16 та 21);

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

Image

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