Python та MySQL: повний практичний посібник, ч. 1

MySQL наразі є однією з найпопулярніших систем управління базами даних (СУБД) на ринку. В рейтингу DB-Engines Ranking станом на листопад 2023 року вона поступається лише СУБД Oracle.

У цьому посібнику розглядаємо, як працювати з СУБД MySQL, розробляючи програми на Python. Зокрема створимо невелику базу даних із рейтингом фільмів і навчимося взаємодіяти з нею безпосередньо з коду.

Чому використовуємо саме MySQL: переваги цієї СУБД

SQL розшифровується як Structured Query Language (мова структурованих запитів). Її широко використовують для керування реляційними базами даних, зокрема MySQL.

MySQL є СУБД з відкритим вихідним кодом. Її було видано 23 травня 1995 року, і вона швидко увійшла до числа лідерів ринку. Основні функції MySQL надають безоплатно, але є платні доповнення.

Зараз MySQL використовують усі великі технологічні компанії, зокрема Google, LinkedIn, Netflix, X (Twitter) та Uber.

Успіх MySQL зумовлено зокрема такими перевагами:

• Простота встановлення. MySQL доступна для всіх поширених операційних систем, зокрема Windows, MacOS, Linux і Solaris. Її легко налаштовувати, а інші інструменти, як-от phpMyAdmin, прискорюють процес встановлення.
• Висока швидкість роботи. MySQL вважають дуже швидкою СУБД. Вона вимагає порівняно мало ресурсів та ефективно масштабується у довготривалій перспективі.
• Права доступу та безпека користувачів. Сценарій MySQL дає змогу встановити рівень захисту паролем, призначити паролі адміністраторів, а також додавати та відкликати права доступу. Цей сценарій полегшує керування користувачами. Для інших СУБД, наприклад, PostgreSQL, використовують конфігураційні файли, з якими складніше працювати.
• Підтримка спільноти. MySQL підтримує широка спільнота розробників програмного забезпечення з відкритим вихідним кодом.

Установка MySQL Server та MySQL Connector/Python

Для створення нашої програми вам знадобиться встановити три речі:

• MySQL Server
• Python
• MySQL Connector/Python

Сервер MySQL виконує всю роботу з базою даних. Після встановлення та налаштування сервера можна підключити до нього програму, написану на Python, за допомогою MySQL Connector/Python.

Установка MySQL Server

Для Windows найкраще завантажити MySQL Installer . Можна скористатися вебустановником або завантажити програму інсталяції повністю. Інсталятор проведе вас крізь весь процес.

На першій сторінці буде запропоновано обрати тип встановлення: лише сервер, лише клієнт, повне встановлення та вибіркове встановлення. Для цього посібника знадобиться лише сервер:

Якщо у вас не встановлено пакет Microsoft Visual C++ 2019 Redistributable Package, встановлення якого є попередньою вимогою для MySQL Server 8.0, то відкриється сторінка Check requirements, на якій буде запропоновано встановити нову сумісну версію пакета. Натисніть кнопку Execute, щоб установити його:

Відкриється вікно програми інсталяції Microsoft Visual C++ 2015-2019 Redistributable Package. Прийміть умови ліцензії та натисніть кнопку Install:

Після успішного встановлення пакета натисніть кнопку Close. Потім натисніть кнопку Next на сторінці Check requirements у вікні MySQL Installer.

На сторінці Installation натисніть кнопку Execute. Після успішного встановлення натисніть кнопку Next, щоб перейти до конфігурації продукту.

Налаштування MySQL Server

На сторінках Type and Networking та Authentication Method залиште параметри без змін і натисніть кнопку Next.

На сторінці Accounts and Roles необхідно ввести пароль для облікового запису root (адміністратор), а також можна додати користувачів з різними правами доступу:

Хоч під час інсталяції й потрібно вказати облікові дані root, їх можна змінити пізніше.

Встановлення MySQL Connector/Python

Драйвер бази даних — це програмний продукт, що дозволяє застосунку підключатися до системи керування базою даних і взаємодіяти з нею. Мовам програмування, як-от Python, потрібен спеціальний драйвер для взаємодії з базою даних певного постачальника.

Зазвичай такі драйвери постачають у вигляді сторонніх модулів. Python Database API (DB-API) визначає стандартний інтерфейс, якому повинні відповідати всі драйвери баз даних для Python. Детально його описано в PEP 249. Всі драйвери баз даних Python, наприклад, sqlite3 для SQLite, psycopg для PostgreSQL і MySQL Connector/Python для MySQL, дотримуються цих правил реалізації.

Найпопулярнішим конектором MySQL для Python є MySQL Connector/Python. Його можна встановити за допомогою pip (рекомендовано встановити його у віртуальному середовищі проєкту):

pip install mysql-connector-python

Щоб переконатися в успішності встановлення, введіть у терміналі Python:

import mysql.connector

Якщо цей код виконано без помилок, значить, mysql.connector встановлено і він готовий до використання. Якщо виникли помилки, переконайтеся, що ви запустили відповідне віртуальне середовище та використовуєте потрібний інтерпретатор Python.

Підключення до MySQL Server

MySQL — система керування базами даних на базі сервера. На одному сервері може бути розміщено кілька баз даних.Перед тим, як працювати з базою даних, потрібно встановити з'єднання із сервером.

Процес роботи програми на Python, яка взаємодіє з базою даних MySQL, складається з таких кроків:

• Встановити з'єднання з сервером MySQL.
• Створити базу даних.
• Підключитися до новоствореної чи наявної бази даних.
• Виконати запит SQL та отримати результати.
• Повідомити базі даних, якщо до таблиці внесено будь-які зміни.
• Закрити з'єднання із сервером MySQL.

Це узагальнений опис робочого процесу. У різних застосунках його можуть реалізувати по-різному, але в будь-якому разі спочатку потрібно підключити базу даних до застосунку.

Встановлення з'єднання

Першим кроком у взаємодії із сервером MySQL є встановлення з'єднання. Для цього потрібно підключитися (connect()) до бази даних з модуля mysql.connector. Ця функція приймає параметри host, user і password і повертає об'єкт MySQLConnection. Ці облікові дані можна отримати від користувача, а потім передати їх методу connect():

from getpass import getpass
from mysql.connector import connect, Error
try:
    with connect(
        host="localhost",
        user=input("Уведіть ім'я користувача: "),
        password=getpass("Уведіть пароль: "),
    ) as connection:
        print(f"""
Підключення встановлено:
хост: {connection.server_host}
порт: {connection.server_port}
користувач: {connection.user}""")
except Error as e:
    print(f"Помилка підключення до сервера:\n{e}")

Наведений вище код запитує облікові дані для підключення до сервера MySQL. У результаті ви отримуєте об'єкт MySQLConnection, що зберігається в змінній connection. Тепер цю змінну можна використовувати для взаємодії із сервером.

Внаслідок виконання наведеного вище коду отримаємо приблизно такий вивід:

У цьому коді є кілька важливих моментів:

• Завжди потрібно обробляти виняткові ситуації, які можуть виникнути під час встановлення з'єднання із сервером MySQL. Для цього використовують блок try... except. У ньому перехоплюється виняток і виводиться його повідомлення.
• Після завершення роботи з базою даних завжди потрібно закривати з'єднання. Якщо залишити непотрібне з'єднання відкритим, це може призвести до непередбачених помилок і зниження продуктивності. У наведеному вище коді використано блок with, який очищає пам'ять від даних, що були потрібні для з'єднання.
• Ніколи не варто жорстко кодувати облікові дані (ім'я користувача та пароль) у сценарії Python. Жорстке кодування цих даних є загрозою безпеці. У наведеному вище коді облікові дані отримують від користувача, а для приховування пароля застосовано модуль getpass. Хоча це і краще, ніж жорстке кодування, існують безпечніші способи зберігання конфіденційної інформації, наприклад, у змінних оточення.

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

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

Щоб створити базу даних, виконайте такий оператор SQL:

CREATE DATABASE movies_db;

Він створить базу даних з ім'ям movies_db.

Для виконання запиту SQL у Python потрібно використовувати об'єкт курсор, у якому абстраговано доступ до записів бази даних. MySQL Connector/Python надає клас MySQLCursor, екземплярами якого є об'єкти, здатні виконувати запити MySQL у Python. Екземпляр класу MySQLCursor також називається cursor.

Об'єкти cursor використовують об'єкт MySQLConnection для взаємодії із сервером MySQL. Щоб створити cursor, використовуйте метод .cursor() змінної connection:

cursor = connection.cursor()

Після виконання цього коду ви отримаєте екземпляр класу MySQLCursor.

Запит, який потрібно виконати, передається методом cursor.execute() у рядковому форматі. У нашому конкретному випадку ви надішлете цьому методу запит CREATE DATABASE. Змінимо вміст блоку після оголошення змінної connection:

from getpass import getpass
from mysql.connector import connect, Error
try:
    with connect(
        host="localhost",
        user=input("Уведіть ім'я користувача: "),
        password=getpass("Уведіть пароль: "),
    ) as connection:
        query = "CREATE DATABASE movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(query)
except Error as e:
    print(e)

Після виконання наведеного коду на сервері MySQL з'явиться база даних з ім'ям movie_rating.

Запит CREATE DATABASE зберігається в рядковому вигляді у змінній query, а потім передається методу cursor.execute() для виконання. Для реалізації процесу очищення в наведеному вище коді використано менеджер контексту з об'єктом cursor.

Якщо база даних з тим самим ім'ям уже існує на сервері, може бути виведене повідомлення про помилку. Для перевірки можна вивести список усіх баз даних на сервері. Використовуючи отриманий нами об'єкт MySQLConnection, виконаємо запит SHOW DATABASES.

Для цього знову змінимо вміст блоку із запитом:

query = "SHOW DATABASES"
with connection.cursor() as cursor:
cursor.execute(query)
for db in cursor:
print(db)

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

Команда SHOW DATABASES виводить не лише ті бази даних, які ви створили, а й деякі інші, зокрема information_schema, performance_schema тощо. Ці бази даних створює сервер MySQL автоматично, вони надають доступ до різних метаданих баз даних та налаштувань сервера MySQL.

Підключення до наявної бази даних

Підключитися до вже готової бази даних, як це часто буває в реальних умовах, можна за допомогою того самого методу connect(), тільки тепер потрібно передати йому додатковий параметр — database:

from getpass import getpass
from mysql.connector import connect, Error
try:
    with connect(
        host="localhost",
        user=input("Уведіть ім'я користувача: "),
        password=getpass("Уведіть пароль: "),
        database="movie_rating",
    ) as connection:
        print(f"""
Підключення встановлено:
хост: {connection.server_host}
порт: {connection.server_port}
користувач: {connection.user}
база даних: {connection.database}""")
except Error as e:
    print(f"Помилка підключення до сервера:\n{e}")

Коли з'єднання буде встановлено, ім'я бази даних збережеться у властивості connection.database:

Наведений вище код засновано на коді з першого прикладу підключення. У попередній код додано рядок, що передає ім'я бази даних у параметрі database, і рядок для виведення її імені. Тому в результаті виконання цього коду буде встановлено з'єднання з базою даних movie_rating.

Створення, змінення та видалення таблиці

У цьому розділі ви скористаєтеся кількома базовими запитами DDL (Data Definition Language), зокрема CREATE, DROP і ALTER з коду на Python. Ви розглянете структуру бази даних MySQL, яку використовуватимете під час роботи з цим посібником. Також ви створите всі необхідні таблиці й навчитеся змінювати їх.

Опис схеми бази даних

Почнімо створювати схему бази даних для системи рейтингів фільмів. База даних складатиметься з трьох таблиць:

• movies містить загальні відомості про фільми та має такі атрибути:
  1. - id
  2. - title
  3. - release_year
  4. - genre
  5. - collection_in_mil
• reviewers містить інформацію про рецензентів і має такі атрибути:
  1. - id
  2. - first_name
  3. - last_name
• ratings містить інформацію про призначені рейтинги та має такі атрибути:
  1. - movie_id (зовнішній ключ)
  2. - reviewer_id (зовнішній ключ)
  3. - rating

Реальна система (наприклад, IMDb) містила б багато іншої інформації: адреси електронної пошти, списки акторів тощо. За бажання ви можете додати в цю базу даних нові таблиці та атрибути, але для цього посібника буде достатньо описаних трьох таблиць.

Нижче наведено схему нашої бази даних:

Таблиці movies і reviewers пов'язано відносинами «багато до багатьох», оскільки огляд одного й того самого фільму можуть зробити кілька рецензентів, а один рецензент може зробити огляд кількох фільмів.

Таблиця ratings пов'язує таблиці movies і reviewers.

Створення таблиць за допомогою оператора CREATE TABLE

Тепер, щоб створити в MySQL нову таблицю, використовуємо оператор CREATE TABLE. Наведений нижче запит MySQL створює таблицю movies у базі даних movie_rating:

CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
)

Якщо ви вже знайомі з операторами SQL, то більшість наведеного вище коду ви розумієте. Але синтаксис MySQL дещо відмінний.

Для створення таблиці потрібно передати цей запит методу cursor.execute(), який приймає запит MySQL і виконує його в підключеній базі даних MySQL:

create_movies_table_query = """
CREATE TABLE movies(
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100),
    release_year YEAR(4),
    genre VARCHAR(100),
    collection_in_mil INT
)
"""
with connection.cursor() as cursor:
cursor.execute(create_movies_table_query)
connection.commit()

Також зверніть увагу на оператор connection.commit() наприкінці коду. За замовчуванням конектор MySQL не фіксує транзакції автоматично. Зміни, які зазначено в транзакції, застосовують лише тоді, коли в кінці вказують команду COMMIT. Викликайте цей метод після кожної транзакції, щоб застосовувати фактичні зміни в таблиці.

Так само, як ви створили таблицю movies, виконайте наведений нижче сценарій, щоб створити таблицю reviewers:

create_reviewers_table_query = """
CREATE TABLE reviewers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100),
    last_name VARCHAR(100)
)
"""
with connection.cursor() as cursor:
cursor.execute(create_reviewers_table_query)
connection.commit()

За потреби можна додати більше інформації про людину, яка залишила відгук, наприклад, адресу електронної пошти або дату народження. Для наших цілей достатньо полів first_name (ім'я) та last_name (прізвище).

Тепер для створення таблиці ratings виконайте наведений далі сценарій:

create_ratings_table_query = """
CREATE TABLE ratings (
    movie_id INT,
    reviewer_id INT,
    rating DECIMAL(2,1),
    FOREIGN KEY(movie_id) REFERENCES movies(id),
    FOREIGN KEY(reviewer_id) REFERENCES reviewers(id),
    PRIMARY KEY(movie_id, reviewer_id)
)
"""
with connection.cursor() as cursor:
cursor.execute(create_ratings_table_query)
connection.commit()

Реалізація зовнішніх ключів (foreign key) у MySQL дещо відрізняється від стандартного SQL. У MySQL і батьківський, і дочірній зовнішній ключ мають використовувати одну й ту саму підсистему зберігання (storage engine).

Підсистема зберігання є програмним компонентом, який використовують в СКБД для виконання операцій SQL.

MySQL використовує два типи підсистем зберігання:

• Транзакційні підсистеми зберігання забезпечують безпечні транзакції та дозволяють здійснювати відкат транзакцій за допомогою простих команд, наприклад rollback. До цього типу належать багато популярних підсистем зберігання MySQL, зокрема InnoDB та NDB.
• Нетранзакційні підсистеми зберігання використовують створений вручну код скасування операторів, дії яких зафіксовано в базі даних. MyISAM, MEMORY та багато інших підсистем зберігання MySQL є нетранзакційними.

InnoDB використовують за замовчуванням і є найпопулярнішою. Вона забезпечує цілісність даних завдяки підтримці обмеження зовнішнього ключа. Це означає, що будь-яка операція CRUD на зовнішньому ключі перевіряється, щоб уникнути невідповідностей між таблицями.

Ви можете використовувати один і той самий cursor для виконання кількох операцій. Тоді всі виконання становитимуть одну атомарну транзакцію, а не кілька окремих транзакцій. Наприклад, ви можете виконати всі оператори CREATE TABLE з використанням одного об’єкта cursor, а потім зафіксувати транзакцію один раз:

with connection.cursor() as cursor:
    cursor.execute(create_movies_table_query)
    cursor.execute(create_reviewers_table_query)
    cursor.execute(create_ratings_table_query)
    connection.commit()

Наведений вище код спочатку виконає всі три оператори CREATE. Потім він відправить команду COMMIT серверу MySQL, який зафіксує транзакцію. Також ви можете використати метод .rollback(), щоб відправити команду ROLLBACK на сервер MySQL і скасувати всі зміни в даних, які внесено транзакцією.

Перегляд схеми таблиці за допомогою оператора DESCRIBE

Тепер, коли всі три таблиці створено, можна переглянути їхні схеми за допомогою такого оператора SQL:

DESCRIBE <table_name>;

Щоб отримати результати від об'єкта cursor, потрібно використовувати метод cursor.fetchall(). Цей метод повертає всі рядки, які отримано після останнього оператора. Припустимо, що об'єкт MySQLConnection вже є у змінній connection. Ви можете вивести всі результати, які повернено методом cursor.fetchall():

show_table_query = "DESCRIBE movies"
with connection.cursor() as cursor:
    cursor.execute(show_table_query)
    result = cursor.fetchall()
    for row in result:
        print(row)

Отримаємо опис таблиці, де кожен стовпчик представлено у вигляді кортежу, в якому вказано тип даних стовпчика, зазначено, чи він є первинним ключем тощо:

Змінення схеми таблиці за допомогою оператора ALTER

Таблиця movies має стовпчик collection_in_mil, що містить дані про касові збори в мільйонах доларів. За допомогою наведеного нижче оператора MySQL можна змінити тип даних атрибута collection_in_mil з INT на DECIMAL:

ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);

DECIMAL(4;1) означає десяткове число, що складається максимум із 4 цифр, з яких одна позначає десяті частки, наприклад, 120.1, 3.4, 38.0 тощо. Після виконання оператора ALTER TABLE ви можете переглянути оновлену схему таблиці за допомогою розглянутого вище оператора DESCRIBE.

Визначимо два запити:

alter_table_query = """
ALTER TABLE movies
MODIFY COLUMN collection_in_mil DECIMAL(4,1)
"""
show_table_query = "DESCRIBE movies"

Виконаємо їх після підключення:

with connection.cursor() as cursor:
    cursor.execute(alter_table_query)
    cursor.execute(show_table_query)
    result = cursor.fetchall()
    print("Схема таблиці movie после змінення:")
    for row in result:
        print(row)

Результат:

Видалення таблиць за допомогою оператора DROP

Щоб видалити таблицю, потрібно застосувати оператор DROP. Видалення таблиці є незворотним процесом.

Для видалення таблиці ratings передайте запит на видалення методу cursor.execute():

drop_table_query = "DROP TABLE ratings"
with connection.cursor() as cursor:
    cursor.execute(drop_table_query)

На цьому закінчується перша частина посібника з Python і MySQL. У наступній частині читайте, як заповнити таблицю даними, читати записи з однієї та кількох таблиць, а також змінювати дані та видаляти записи з таблиці.