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

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

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

Вставлення записів у таблицю

Якщо ви дотримувалися всіх інструкцій із попередньої частини, то зараз у вас має бути база даних з трьома таблицями: movies, reviewers і ratings. Заповнимо їх даними.

Вставити записи за допомогою MySQL Connector for Python можна двома способами:

• метод .execute() стане в пригоді, коли записів небагато і їх можна закодувати жорстко;
• другий, .executemany(), є кращим для реальних сценаріїв.

Використання методу .execute()

Застосуємо метод cursor.execute(), який ви використовували раніше. Йому потрібно передати запит INSERT INTO, щоб вставити дані в таблицю movies.

Нагадаємо, що таблиця movies має п'ять атрибутів:

• id;
• title;
• release_year;
• genre;
• collection_in_mil.

Додавати дані для атрибута id не потрібно, тому що AUTO_INCREMENT автоматично обчислює його значення. Сценарій, який подано нижче, вставляє записи в таблицю movies:

insert_movies_query = """
INSERT INTO movies (title, release_year, genre, collection_in_mil)
VALUES
    ("Forrest Gump", 1994, "Drama", 330.2),
    ("3 Idiots", 2009, "Drama", 2.4),
    ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5),
    ("Good Will Hunting", 1997, "Drama", 138.1),
    ("Skyfall", 2012, "Action", 304.6),
    ("Gladiator", 2000, "Action", 188.7),
    ("Black", 2005, "Drama", 3.0),
    ("Titanic", 1997, "Romance", 659.2),
    ("The Shawshank Redemption", 1994, "Drama",28.4),
    ("Udaan", 2010, "Drama", 1.5),
    ("Home Alone", 1990, "Comedy", 286.9),
    ("Casablanca", 1942, "Romance", 1.0),
    ("Avengers: Endgame", 2019, "Action", 858.8),
    ("Night of the Living Dead", 1968, "Horror", 2.5),
    ("The Godfather", 1972, "Crime", 135.6),
    ("Haider", 2014, "Action", 4.2),
    ("Inception", 2010, "Adventure", 293.7),
    ("Evil", 2003, "Horror", 1.3),
    ("Toy Story 4", 2019, "Animation", 434.9),
    ("Air Force One", 1997, "Drama", 138.1),
    ("The Dark Knight", 2008, "Action",535.4),
    ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1),
    ("The Lion King", 1994, "Animation", 423.6),
    ("Pulp Fiction", 1994, "Crime", 108.8),
    ("Kai Po Che", 2013, "Sport", 6.0),
    ("Beasts of No Nation", 2015, "War", 1.4),
    ("Andadhun", 2018, "Thriller", 2.9),
    ("The Silence of the Lambs", 1991, "Crime", 68.2),
    ("Deadpool", 2016, "Action", 363.6),
    ("Drishyam", 2015, "Mystery", 3.0)
"""
with connection.cursor() as cursor:
    cursor.execute(insert_movies_query)
    connection.commit()

Тепер таблиця movies містить тридцять записів. У кінці коду здійснюється виклик методу connection.commit(). Потрібно обов'язково викликати метод .commit() після внесення будь-яких змін до таблиці.

Використання методу .executemany()

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

Тому в реальних умовах зазвичай використовують метод .executemany(). Він приймає два параметри:

• запит зі специфікаторами для записів, які потрібно вставити;
• список, що містить усі записи, які треба вставити.

Фрагмент коду, який подано нижче, вставляє записи в таблицю reviewers:

insert_reviewers_query = """
INSERT INTO reviewers
(first_name, last_name)
VALUES ( %s, %s )
"""
reviewers_records = [
    ("Chaitanya", "Baweja"),
    ("Mary", "Cooper"),
    ("John", "Wayne"),
    ("Thomas", "Stoneman"),
    ("Penny", "Hofstadter"),
    ("Mitchell", "Marsh"),
    ("Wyatt", "Skaggs"),
    ("Andre", "Veiga"),
    ("Sheldon", "Cooper"),
    ("Kimbra", "Masters"),
    ("Kat", "Dennings"),
    ("Bruce", "Wayne"),
    ("Domingo", "Cortes"),
    ("Rajesh", "Koothrappali"),
    ("Ben", "Glocker"),
    ("Mahinder", "Dhoni"),
    ("Akbar", "Khan"),
    ("Howard", "Wolowitz"),
    ("Pinkie", "Petit"),
    ("Gurkaran", "Singh"),
    ("Amy", "Farah Fowler"),
    ("Marlon", "Crafford"),
]
with connection.cursor() as cursor:
    cursor.executemany(insert_reviewers_query, reviewers_records)
    connection.commit()

У наведеному вище сценарії ви передаєте методу .executemany() запит і список записів. Ці записи могли взяти з файлу або їх міг ввести користувач і зберегти у списку reviewers_records.

Тепер скористайтеся методом .executemany(), щоб вставити записи в таблицю ratings:

insert_ratings_query = """
INSERT INTO ratings
(rating, movie_id, reviewer_id)
VALUES ( %s, %s, %s)
"""
ratings_records = [
    (6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17),
    (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4),
    (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10),
    (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19),
    (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9),
    (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19),
    (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15),
    (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20),
    (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13),
    (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17),
    (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4),
    (9.8, 13, 1)
]
with connection.cursor() as cursor:
    cursor.executemany(insert_ratings_query, ratings_records)
    connection.commit()

Тепер усі три таблиці заповнено даними й ви маєте цілком робочу базу даних із рейтингами фільмів.

Далі потрібно навчитися взаємодіяти з цією базою даних.

Читання записів бази даних за допомогою оператора SELECT

Для отримання записів потрібно передати методу cursor.execute() запит SELECT. Потім треба скористатися методом cursor.fetchall() і отримати таблицю у вигляді списку рядків або записів.

Напишемо запит MySQL для відбору записів із таблиці movies і передамо його методу .execute():

select_movies_query = "SELECT * FROM movies LIMIT 5"
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    result = cursor.fetchall()
    for row in result:
        print(row)

Результат виконання запиту:

Змінна result містить записи, які повернув метод .fetchall(). Це список кортежів, якими представлено окремі записи в таблиці.

У наведеному вище запиті використано речення LIMIT для обмеження кількості записів, які отримано від оператора SELECT. Розробники часто застосовують LIMIT для пагінації під час обробки великих обсягів даних.

Оскільки у вашому запиті зазначено LIMIT 5, то буде отримано лише 5 перших записів. Якщо використано два аргументи, то в першому з них зазначають зсув відносно першого запису:

SELECT * FROM movies LIMIT 2,5;

У першому аргументі зазначено зсув 2, а другий аргумент вказує, що потрібно повернути 5 записів. Наведений вище запит поверне записи з 3 до 7.

Також ви можете надіслати запит на отримання даних з окремих стовпчиків:

select_movies_query = "SELECT title, release_year FROM movies LIMIT 5"
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for row in cursor.fetchall():
        print(row)

Результат виконання запиту:

Тепер код виводить значення лише з двох зазначених стовпчиків: title і release_year.

Фільтрація результатів за допомогою речення WHERE

Записи з таблиці можна відфільтрувати за певними критеріями за допомогою речення WHERE. Наприклад, для отримання всіх фільмів зі збором понад $300 млн можна виконати такий запит:

SELECT title, collection_in_mil
FROM movies
WHERE collection_in_mil > 300;

Також у цьому запиті можна використати речення ORDER BY для сортування результатів від найбільших зборів до найменших:

select_movies_query = """
SELECT title, collection_in_mil
FROM movies
WHERE collection_in_mil > 300
ORDER BY collection_in_mil DESC
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)

Результат виконання запиту:

MySQL пропонує багато операцій форматування рядків, наприклад, CONCAT для конкатенації рядків. Часто, щоб уникнути плутанини, на вебсайтах поряд із назвою фільму зазначають рік його випуску. Щоб отримати назви п'яти фільмів з найбільшими зборами та вказати їх із роками випуску, скористайтеся таким запитом:

select_movies_query = """
SELECT CONCAT(title, " (", release_year, ")"),
    collection_in_mil
FROM movies
ORDER BY collection_in_mil DESC
LIMIT 5
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)

Результат виконання запиту:

Якщо ви не хочете використовувати речення LIMIT і вам не потрібно повертати всі записи, можна скористатися методами об'єкта cursor .fetchone() і .fetchmany():

• .fetchone() отримує наступний рядок результату у вигляді кортежу або None, якщо рядки скінчилися;
• .fetchmany() отримує наступний набір рядків результату у вигляді списку кортежів. Він приймає аргумент size, який за замовчуванням має значення 1. У ньому вказують, скільки рядків потрібно отримати. Коли рядки закінчуються, метод повертає порожній список.

Перепишемо код для отримання п'яти фільмів з найбільшими зборами з роками випуску з використанням методу .fetchmany():

select_movies_query = """
SELECT CONCAT(title, " (", release_year, ")"),
    collection_in_mil
FROM movies
ORDER BY collection_in_mil DESC
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchmany(size=5):
        print(movie)
    cursor.fetchall()

Результат виконання запиту:

Вивід у разі використання методу .fetchmany() схожий на вивід, який отримано із застосуванням речення LIMIT. Ви могли помітити додатковий виклик cursor.fetchall() у кінці. Це роблять для очищення від решти результатів, які не прочитав метод .fetchmany().

Якщо після виконання одного оператора з певним з'єднанням потрібно використати те саме з'єднання для виконання наступного оператора, то перед цим треба провести очищення від усіх результатів, які не прочитано. Якщо цього не зробити, буде викинуто виключну ситуацію InternalError: Unread result found.

Робота з декількома таблицями з використанням речення JOIN

Якщо ви хочете знайти в базі даних назви п'яти найкасовіших фільмів, то можна виконати такий запит:

select_movies_query = """
SELECT title, AVG(rating) as average_rating
FROM ratings
INNER JOIN movies
    ON movies.id = ratings.movie_id
GROUP BY movie_id
ORDER BY average_rating DESC
LIMIT 5
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)

Результат виконання запиту:

Як показано вище, «Хрещений батько» (The Godfather) і «Ніч живих мерців» (Night of the Living Dead) мають найвищий рейтинг у вашій базі даних.

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

select_movies_query = """
SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num
FROM reviewers
INNER JOIN ratings
    ON reviewers.id = ratings.reviewer_id
GROUP BY reviewer_id
ORDER BY num DESC
LIMIT 1
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
        print(movie)

Результат виконання запиту:

Mary Cooper поставила найбільше рейтингів у цій базі даних.

Як бачимо, ступінь складності запиту неважливий, тому що його зрештою обробляє сервер MySQL. А для вас у будь-якому разі цей процес становитимуть такі дії:

• передати запит методу cursor.execute();
• отримати результат за допомогою .fetchall().

Оновлення записів та їх видалення з бази даних

Оператор UPDATE

Уявімо, що є таке завдання: персонаж Amy Farah Fowler одружується з персонажем Sheldon Cooper і змінює прізвище, тому потрібно внести зміни до бази даних. Для оновлення записів MySQL використовує оператор UPDATE:

update_query = """
UPDATE
    reviewers
SET
    last_name = "Cooper"
WHERE
    first_name = "Amy"
"""
with connection.cursor() as cursor:
    cursor.execute(update_query)
    connection.commit()

Цей код передає запит на оновлення методу cursor.execute(), а метод .commit() вносить потрібні зміни до таблиці reviewers.

Припустимо, що нам потрібна опція, за допомогою якої користувачі можуть змінювати рейтинги. Користувач вказує три значення:

• movie_id;
• reviewer_id;
• нове значення rating.

Код виведе запис після введення потрібних змін.

Приймемо, що movie_id = 18, reviewer_id = 15, а нове значення rating = 5.0. Для внесення зміни використовуйте такі запити MySQL:

UPDATE
    ratings
SET
    rating = 5.0
WHERE
    movie_id = 18 AND reviewer_id = 15;

SELECT *
FROM ratings
WHERE
    movie_id = 18 AND reviewer_id = 15;

Наведені вище запити спочатку змінюють рейтинг, а потім повертають його значення. Можна створити сценарій Python, який з'єднується з базою даних і дає користувачеві змогу змінювати рейтинг:

from getpass import getpass
from mysql.connector import connect, Error

movie_id = input("Введіть id фільму: ")
reviewer_id = input("Введіть id користувача: ")
new_rating = input("Введіть новий рейтинг: ")
update_query = """
UPDATE
    ratings
SET
    rating = "%s"
WHERE
    movie_id = "%s" AND reviewer_id = "%s";

SELECT *
FROM ratings
WHERE
    movie_id = "%s" AND reviewer_id = "%s"
""" % (
    new_rating,
    movie_id,
    reviewer_id,
    movie_id,
    reviewer_id,
)

try:
    with connect(
        host="localhost",
        user=input("Введіть ім'я користувача: "),
        password=getpass("Введіть пароль: "),
        database="movie_rating",
    ) as connection:
        with connection.cursor() as cursor:
            for result in cursor.execute(update_query, multi=True):
                if result.with_rows:
                    print(result.fetchall())
            connection.commit()
except Error as e:
    print(e)

Збережіть цей код у файлі з іменем modify_ratings.py.

У наведеному вище коді використано специфікатори %s для підстановки отриманого вводу в рядок update_query. Уперше в цьому посібнику в одному рядку передається декілька запитів. Щоб передати методу cursor.execute() декілька запитів, потрібно вказати для параметра multi значення True.

Коли multi дорівнює True, cursor.execute() повертає ітератор. Кожен елемент в ітераторі відповідає об'єкту cursor, який виконує оператор, переданий в запит. У наведеному вище коді до цього ітератора застосовано цикл for, а потім для кожного об'єкта cursor викликано метод .fetchall().

Якщо операція не повертає набір результатів, то .fetchall() викидає виключну ситуацію. Щоб уникнути цієї помилки, у наведеному вище коді використано властивість cursor.with_rows, яка вказує, чи повернула попередня операція рядки.

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

Наприклад, якщо користувач надсилає movie_id=18, reviewer_id=15 і rating=5.0, то вивід буде таким:

Значення rating для movie_id=18 та reviewer_id=15 змінено на 5.0. Але якби ви були хакером, то могли б ввести приховану команду:

Вивід знову показує, що вказаний rating змінено на 5.0. Що змінилися?

Хакер змінив запит на оновлення під час введення даних для reviewer_id. Запит на оновлення update reviewers set lastname = "A" змінює значення last_name усіх записів у таблиці reviewers на "A". Ви можете побачити цю зміну, коли роздрукуєте таблицю reviewers:

 select_query = """
... SELECT first_name, last_name
... FROM reviewers
... """
 with connection.cursor() as cursor:
...     cursor.execute(select_query)
...     for reviewer in cursor.fetchall():
...         print(reviewer)

Результат виконання запиту:

Наведений вище код виводить firstname і lastname для всіх записів у таблиці reviewers. Атака з використанням SQL пошкодила таблицю, коли змінила last_name у всіх записах на A.

Існує швидке виправлення, що дає змогу запобігти таким атакам. Не додавайте значення, які вказано користувачем, безпосередньо в рядок запиту. Натомість змініть сценарій modify_ratings.py, щоб передати ці значення у вигляді аргументів для .execute():

from getpass import getpass
from mysql.connector import connect, Error

movie_id = input("Enter movie id: ")
reviewer_id = input("Enter reviewer id: ")
new_rating = input("Enter new rating: ")
update_query = """
UPDATE
    ratings
SET
    rating = %s
WHERE
    movie_id = %s AND reviewer_id = %s;

SELECT *
FROM ratings
WHERE
    movie_id = %s AND reviewer_id = %s
"""
val_tuple = (
    new_rating,
    movie_id,
    reviewer_id,
    movie_id,
    reviewer_id,
)

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        with connection.cursor() as cursor:
            for result in cursor.execute(update_query, val_tuple, multi=True):
                if result.with_rows:
                    print(result.fetchall())
            connection.commit()
except Error as e:
    print(e)

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

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

cursor.execute() викидає виключну ситуацію, якщо знаходить неприйнятні символи в користувацькому вводі. Цей підхід потрібно застосовувати завжди, коли ви вставляєте користувацьке введення у запит. Також існують інші способи запобігання атакам з використанням SQL.

Оператор DELETE

Процес видалення записів схожий на процес їх оновлення. Для видалення вибраних записів використовують оператор DELETE.

Рекомендовано спочатку виконати запит SELECT із тим самим фільтром, щоб переконатися, що ви видаляєте відповідні записи. Наприклад, для видалення всіх рейтингів, які поставив користувач із reviewer_id = 2, спочатку потрібно надіслати відповідний запит SELECT:

select_movies_query = """
SELECT reviewer_id, movie_id FROM ratings
WHERE reviewer_id = 2
"""
with connection.cursor() as cursor:
    cursor.execute(select_movies_query)
    for movie in cursor.fetchall():
         print(movie)

Результат виконання запиту:

Наведений вище фрагмент коду виводить reviewer_id і movie_id для записів із таблиці ratings, де reviewer_id = 2. Коли ви переконаєтеся, що це саме ті записи, які потрібно видалити, можна виконати запит DELETE із тим самим фільтром:

delete_query = "DELETE FROM ratings WHERE reviewer_id = 2"
with connection.cursor() as cursor:
    cursor.execute(delete_query)
    connection.commit()

Цим запитом ви видаляєте всі рейтинги користувача з reviewer_id = 2 з таблиці ratings.

Інші способи підключення Python до MySQL

У цьому посібнику ви користувалися MySQL Connector/Python, який офіційно рекомендовано для взаємодії з базою даних MySQL із програми Python. Є ще два популярних конектори:

• mysqlclient — це бібліотека, що є близьким конкурентом офіційного конектора й активно оновлюється завдяки новим можливостям. Оскільки ядро цієї бібліотеки написане на C, її продуктивність вища за продуктивність офіційного конектора, який написано на чистому Python. Великим недоліком mysqlclient є складність встановлення й налаштування, особливо у Windows.
• MySQLdb — це застаріле ПЗ, яке досі використовують у комерційних програмах. Воно написане на C і працює швидше за MySQL Connector/Python, але доступне лише для Python 2.

Ці конектори відіграють роль інтерфейсів між вашою програмою та базою даних MySQL, через які ви надсилаєте запити SQL. Але багато розробників віддають перевагу об'єктно-орієнтованій парадигмі замість SQL для керування базами даних.

Бібліотека ORM інкапсулює код, необхідний для керування даними, завдяки чому зникає потреба використання SQL. Зазначимо найпопулярніші ORM для Python для баз даних на основі SQL:

• SQLAlchemy — це ORM, яке спрощує взаємодію між Python і базами даних SQL. Ви можете створювати підсистеми для різних СУБД, як-от MySQL, PostgreSQL, SQLite тощо. SQLAlchemy зазвичай використовують з бібліотекою Pandas, щоб забезпечити повноцінну роботу з даними.
• peewee — це невелике та швидке ORM, яке можна швидко налаштувати. Воно є дуже ефективним, якщо потрібно лише отримати з бази даних декілька записів. Наприклад, якщо треба скопіювати вибрані записи з бази даних MySQL у файл CSV, то peewee може бути найкращим варіантом.
• Django ORM — одна з найпотужніших можливостей Django.Її постачають разом із вебфреймворком Django. Це ORM може взаємодіяти з різними базами даних, зокрема SQLite, PostgreSQL і MySQL. Багато програм на базі Django використовують Django ORM для моделювання даних і базових запитів, але для складніших випадків застосовують SQLAlchemy.

Можливо, для вашої програми буде кориснішим один із цих підходів. Але якщо ви не знаєте, який із них застосовувати, то краще скористатися офіційно рекомендованим конектором MySQL Connector/Python.

Висновок

З цього посібника ви дізналися, як використовувати MySQL Connector/Python для інтеграції бази даних MySQL з вашою програмою Python, ознайомилися з деякими унікальними можливостями СУБД MySQL, які виділяють її з-поміж систем керування базами даних на основі SQL, і з рекомендаціями щодо програмування встановлення з'єднання, створення таблиць, вставки та змінення записів у програмі бази даних.

Також ви розробили приклад бази даних MySQL для системи рейтингів фільмів і безпосередньої взаємодії з нею з програми Python.