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()

В результате выполнения этого кода вы получите экземпляр класса.

Запрос, который нужно выполнить, передается методу 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 TABLE. Удаление таблицы — необратимый процесс.

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

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

На этом заканчивается первая часть пособия по Python и MySQL. В следующей части читайте, как заполнить таблицу данными, читать записи из одной и нескольких таблиц, а также изменять данные и удалять записи из таблицы.