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)

Результат выполнения запроса:

Результат выполнения запроса: 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 для подстановки полученного ввода в строку updatequery. Впервые в этом пособии в одну строку помещено несколько запросов. Чтобы передать методу 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. Что изменилось?

Хакер изменил запрос на обновление, когда вводил данные для reviewerid. Запрос на обновление 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)

Результат выполнения запроса:

Приведенный выше фрагмент кода выводит reviewerid и movieid для записей из таблицы 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.