Python и базы данных MySQL: практическое введение

Содержание

MySQL — одна из самых популярных сегодня на рынке систем управления базами данных (СУБД). В этом году в рейтинге DB‑Engines она заняла второе место после СУБД Oracle^એ. Поскольку большинству программных приложений необходимо взаимодействовать с данными в той или иной форме, языки программирования, такие как Python, предоставляют инструменты для хранения этих источников данных и доступа к ним. Используя методы, описанные в этом уроке, вы сможете эффективно интегрировать базу данных MySQL^એ с приложением Python. Вы разработаете небольшую базу данных MySQL для системы рейтинга фильмов и узнаете, как запрашивать ее прямо из кода Python.

К концу этого урока вы сможете:

• Определите уникальные особенности MySQL.
• Подключите ваше приложение к базе данных MySQL.
• Запросить базу данных для получения необходимых данных.
• Обработка исключений, возникающих при доступе к базе данных.
• Используйте лучшие практики при создании приложений баз данных.

Чтобы получить максимальную отдачу от этого руководства, вы должны иметь практические знания о таких концепциях Python, как циклы, функции, обработка исключений и т.д, установка пакетов Python с помощью pip. Вы также должны иметь базовое представление о системах управления реляционными базами данных и SQL-запросах, таких как SELECT, DROP, CREATE и JOIN.

Сравнение MySQL с другими базами данных SQL

SQL^એ расшифровывается как язык структурированных запросов и является широко используемым языком программирования для управления реляционными базами данных. Возможно, вы слышали о различных вариантах СУБД на основе SQL. К наиболее популярным из них относятся MySQL, PostgreSQL^એ, SQLite^એ и SQL Server^એ. Все эти базы данных соответствуют стандартам SQL, но с разной степенью соответствия.

Будучи открытым исходным кодом с момента своего создания в 1995 году, MySQL быстро стал лидером рынка среди решений SQL. MySQL также является частью экосистемы Oracle. Хотя его основная функциональность полностью бесплатна, есть и платные дополнения. В настоящее время,MySQL используется всеми крупными техническими фирмами, включая Google, LinkedIn, Uber, Netflix, Twitter и другие.

Помимо поддержки большого сообщества разработчиков ПО с открытым исходным кодом, есть много других причин успеха MySQL:

1. Легкость установки: MySQL был разработан, чтобы быть удобным для пользователя. Установить базу данных MySQL довольно просто, а несколько широко доступных сторонних инструментов, таких как phpMyAdmin, еще больше упрощают процесс установки. MySQL доступен для всех основных операционных систем, включая Windows, macOS, Linux и Solaris.
2. Скорость: MySQL имеет репутацию чрезвычайно быстрого решения для баз данных. Он имеет относительно меньшую площадь и чрезвычайно масштабируем в долгосрочной перспективе.
3. Права пользователя и безопасность: MySQL поставляется со сценарием, который позволяет вам устанавливать уровень безопасности паролей, назначать пароли администратора, а также добавлять и удалять привилегии учетной записи пользователя. Подобный подход упрощает процесс администрирования портала управления пользователями веб-хостинга. Другие СУБД, такие как PostgreSQL, используют файлы конфигурации, с которыми гораздо сложнее.

Хотя MySQL славится своей скоростью и простотой использования, вы можете получить более продвинутые функции с PostgreSQL^એ. Кроме того, MySQL не полностью совместим с SQL и имеет определенные функциональные ограничения, например, отсутствие поддержки предложений FULL JOIN.

Вы также можете столкнуться с некоторыми проблемами при одновременном чтении и записи в MySQL. Если в вашем программном обеспечении много пользователей одновременно записывают в него данные, то PostgreSQL может быть более подходящим выбором.

Примечание. Для более подробного сравнения MySQL и PostgreSQL в реальном контексте ознакомьтесь с Why Uber Engineering Switched from Postgres to MySQL.

SQL Server также является очень популярной СУБД, известной своей надежностью, эффективностью и безопасностью. Его предпочитают компании, особенно в банковской сфере, которые регулярно имеют дело с большими объемами трафика. Это коммерческое решение и одна из систем, наиболее совместимых со службами Windows.

В 2010, когда Oracle приобрела Sun Microsystems^એ и MySQL, многих беспокоило будущее MySQL. В то время Oracle была крупнейшим конкурентом MySQL. Разработчики опасались, что это было враждебное поглощение Oracle с целью уничтожения MySQL.

Несколько разработчиков во главе с Майклом Видениусом, первоначальным автором MySQL, создал форк^એ базы кода MySQL и заложил основу MariaDB^એ. Целью было обезопасить доступ к MySQL и сделать его бесплатным навсегда.

На сегодняшний день MariaDB^એ остается полностью под GNU General Public License^એ, сохраняя ее полностью в открытом доступе. С другой стороны, некоторые функции MySQL доступны только с платными лицензиями. Также, MariaDB предоставляет несколько чрезвычайно полезных функций, которые не поддерживаются сервером MySQL, например распределенный SQL и столбчатое хранилище. Вы можете найти больше различий между MySQL и MariaDB, перечисленных на веб-сайте MariaDB.

MySQL использует синтаксис, очень похожий на стандартный SQL. Однако есть некоторые заметные отличия, упомянутые в официальной документации.

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

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

Установка сервера MySQL

В официальной документации подробно описан рекомендуемый способ загрузки и установки сервера MySQL. Вы найдете инструкции для всех популярных операционных систем, включая Windows, macOS, Solaris, Linux и многие другие.

Для Windows лучше всего загрузить установщик MySQL и позволить ему позаботиться обо всем процессе. Диспетчер установки также помогает настроить параметры безопасности сервера MySQL. На странице Учетные записи и роли вам необходимо ввести пароль для учетной записи root (admin), а также при желании добавить других пользователей с различными привилегиями:

Хотя вы должны указать учетные данные для учетной записи root во время установки, вы можете изменить эти настройки позже.

Примечание: запомните имя хоста, имя пользователя и пароль, так как они потребуются для установления соединения с сервером MySQL позже.

Хотя в этом уроке понадобится только сервер MySQL, c помощью этих установщика вы можете настроить другие полезные инструменты, такие как MySQL Workbench. Если вы не хотите устанавливать MySQL непосредственно в свою операционную систему, то удобная альтернатива развертывание MySQL в Linux с помощью Docker.

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

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

Эти драйверы обычно поставляются как сторонние модули. API базы данных Python(DB‑API) определяет стандартный интерфейс, которому должны соответствовать все драйверы баз данных Python. Эти сведения задокументированы в PEP 249. Все драйверы баз данных Python, такие как sqlite3 для SQLite, psycopg для PostgreSQL и MySQL Connector/Python для MySQL, следуют этим правилам реализации.

Примечание. В официальной документации MySQL вместо «драйвер» используется термин «коннектор». Технически соединители связаны только с подключением к базе данных, а не с ней. Однако этот термин часто используется для всего модуля доступа к базе данных, включающего коннектор и драйвер.

Чтобы обеспечить соответствие документации, вы будете видеть термин коннектор всякий раз, когда упоминается MySQL.

Многие популярные языки программирования имеют собственный API баз данных. Например, в Java есть API подключения к базе данных Java (JDBC). Если вам необходимо подключить Java‑приложение к базе данных MySQL, вам необходимо использовать MySQL JDBC‑коннектор, который следует за JDBC API.

Так же, в Python вам необходимо установить коннектор Python MySQL для взаимодействия с базой данных MySQL. Многие пакеты соответствуют стандартам DB‑API, но наиболее популярным среди них является MySQL Connector/Python. Вы можете получить его с помощью pip:

$ pip install mysql-connector-python

pip устанавливает коннектор как сторонний модуль в текущую активную виртуальную среду. Рекомендуется настроить изолированную виртуальную среду для проекта вместе со всеми зависимостями.

Чтобы проверить, была ли установка успешной, введите в терминале Python следующую команду:

>>> import mysql.connector

Если приведенный выше код выполняется без ошибок, значит mysql.connector установлен и готов к использованию. Если вы столкнулись с какими-либо ошибками, убедитесь, что вы находитесь в правильной виртуальной среде и используете правильный интерпретатор Python.

Убедитесь, что вы устанавливаете правильный пакет mysql‑connector‑python, который является чистой реализацией Python. Остерегайтесь одноименных, но теперь устаревших коннекторов, таких как mysql‑connector.

Установление соединения с сервером MySQL

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

1. Подключитесь к серверу MySQL.
2. Создайте новую базу данных.
3. Подключитесь к вновь созданной или существующей базе данных.
4. Выполните SQL-запрос и получите результаты.
5. Сообщите базе данных, если в таблицу были внесены какие-либо изменения.
6. Закройте соединение с сервером MySQL.

Это общий рабочий процесс, который может различаться в зависимости от конкретного приложения. Но каким бы ни было приложение, первый шаг — подключить вашу базу данных к вашему приложению.

Установление соединения

Первым шагом во взаимодействии с сервером MySQL является установление соединения. Для этого вам потребуется connect() из модуля mysql.connector. Эта функция принимает такие параметры, как хост, пользователь и пароль, и возвращает объект MySQLConnection. Вы можете получить эти учетные данные в качестве ввода от пользователя и передать их в connect():

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

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        print(connection)
except Error as e:
    print(e)

В приведенном выше коде используются введенные учетные данные для подключения к вашему серверу MySQL. Взамен вы получаете объект MySQLConnection, который хранится в переменной соединения. С этого момента вы будете использовать эту переменную для доступа к серверу MySQL.

В приведенном выше коде есть несколько важных моментов:

• Вы всегда должны иметь дело с исключениями, которые могут возникнуть при установлении соединения с сервером MySQL. Вот почему вы используете блок try… except для перехвата и печати любых исключений, с которыми вы можете столкнуться.
• Вы всегда должны закрывать соединение после завершения доступа к базе данных. Если оставить неиспользуемые открытые соединения, это может привести к нескольким неожиданным ошибкам и проблемам с производительностью. Приведенный выше код использует диспетчер контекста с помощью with, который абстрагирует процесс очистки соединения.
• Никогда не следует жестко кодировать свои учетные данные для входа, то есть имя пользователя и пароль, непосредственно в скрипт Python. Это плохая практика для развертывания и представляет серьезную угрозу безопасности. Приведенный выше код запрашивает у пользователя учетные данные для входа. Он использует встроенный модуль getpass, чтобы скрыть пароль. Хотя это лучше, чем жесткое кодирование, есть и другие, более безопасные способы хранения конфиденциальной информации, например, использование переменных среды.

Теперь вы установили соединение между вашей программой и сервером MySQL, но вам все равно нужно либо создать новую базу данных, либо подключиться к существующей базе данных внутри сервера.

Создание новой базы данных

Вы установили соединение с вашим сервером MySQL. Чтобы создать новую базу данных, вам необходимо выполнить инструкцию SQL:

CREATE DATABASE books_db;

Приведенный выше оператор создаст новую базу данных с именем books_db.

Примечание. В MySQL обязательно ставить точку с запятой (;) в конце оператора, что означает завершение запроса. Однако MySQL Connector/Python автоматически добавляет точку с запятой в конце ваших запросов, поэтому нет необходимости использовать ее в вашем коде Python.

Чтобы выполнить SQL-запрос в Python, вам потребуется использовать курсор, который абстрагирует доступ к записям базы данных. MySQL Connector/Python предоставляет вам класс MySQLCursor, который создает экземпляры объектов для выполнения запросов MySQL в Python. Экземпляр класса MySQLCursor также называется cursor. Объект cursor используется для взаимодействия с вашим сервером MySQL. Чтобы создать курсор, используйте метод .cursor() вашей переменной соединения:

cursor = connection.cursor()

Приведенный выше код создает экземпляр класса MySQLCursor.

Запрос, который необходимо выполнить, отправляется в cursor.execute() в строковом формате. В этом конкретном случае вы отправите запрос CREATE DATABASE к cursor.execute():

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

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
    ) as connection:
        create_db_query = "CREATE DATABASE online_movie_rating"
        with connection.cursor() as cursor:
            cursor.execute(create_db_query)
except Error as e:
    print(e)

После выполнения приведенного выше кода на вашем сервере MySQL появится новая база данных с именем online_movie_rating.

Запрос CREATE DATABASE сохраняется в виде строки в переменной create_db_query, а затем передается для выполнения в cursor.execute(). Код использует диспетчер контекста с объектом курсора для обработки процесса очистки.

Здесь вы можете получить сообщение об ошибке, если база данных с таким именем уже существует на вашем сервере. Чтобы подтвердить это, вы можете отобразить имена всех баз данных на вашем сервере. Используя тот же объект MySQLConnection, что и ранее, выполните инструкцию SHOW DATABASES:

>>> show_db_query = "SHOW DATABASES"
>>> with connection.cursor() as cursor:
...     cursor.execute(show_db_query)
...     for db in cursor:
...         print(db)
...
('information_schema',)
('mysql',)
('online_movie_rating',)
('performance_schema',)
('sys',)

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

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

Подключение к существующей базе данных

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

Вы можете сделать это, используя ту же функцию connect(), которую вы использовали ранее, отправив дополнительный параметр с именем database:

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

try:
    with connect(
        host="localhost",
        user=input("Enter username: "),
        password=getpass("Enter password: "),
        database="online_movie_rating",
    ) as connection:
        print(connection)
except Error as e:
    print(e)

Приведенный выше код очень похож на сценарий подключения, который вы использовали ранее. Единственное изменение здесь — это дополнительный параметр базы данных, в котором имя вашей базы данных передается в connect(). После выполнения этого скрипта вы будете подключены к базе данных online_movie_rating.

Создание, изменение и удаление таблицы

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

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

Вы можете начать с создания схемы базы данных для онлайн-рейтинговой системы фильмов. База данных будет состоять из трех таблиц:

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

Реальная система оценки фильмов, например, IMDb потребуется хранить кучу других атрибутов, таких как электронные письма, списки актеров фильмов и так далее. Если хотите, вы можете добавить в эту базу данных больше таблиц и атрибутов. Но этих трех таблиц будет достаточно для этого урока.

На изображении ниже показана схема базы данных:




Таблицы в этой базе данных связаны друг с другом. фильмы и рецензенты будут иметь отношение «многие‑ко‑многим», поскольку один фильм может быть просмотрен несколькими рецензентами, а один рецензент может рецензировать несколько фильмов. Таблица рейтингов соединяет таблицу фильмов с таблицей обозревателей.

Создание таблиц с помощью оператора CREATE TABLE

Теперь, чтобы создать новую таблицу в MySQL, вам нужно использовать оператор CREATE TABLE. Следующий запрос MySQL создаст таблицу фильмов для вашей базы данных online_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, о которых вам следует знать.

Например, MySQL предлагает широкий выбор типов данных, включая YEAR, INT, BIGINT и т.д. Также,MySQL использует ключевое слово AUTO_INCREMENT, когда значение столбца должно автоматически увеличиваться при вставке новых записей.

Чтобы создать новую таблицу, вам необходимо передать этот запрос в 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()

Теперь у вас есть таблица фильмов в вашей базе данных. Вы передаете create_movies_table_query функции cursor.execute(), которая выполняет требуемое выполнение.

Примечание. Переменная connection относится к объекту MySQLConnection, который был возвращен при подключении к базе данных.

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

Как и в случае с таблицей фильмов, выполните следующий сценарий для создания таблицы обозревателей:

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 будут служить вашей цели.

Наконец, вы можете создать таблицу рейтингов с помощью следующего скрипта:

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

Реализация отношений внешнего ключа в MySQL немного отличается и ограничена по сравнению со стандартным SQL. В MySQL и родитель, и потомок в ограничении внешнего ключа должны использовать один и тот же механизм хранения.

Механизм хранения — это базовый программный компонент, который система управления базами данных использует для выполнения операций SQL. В MySQL механизмы хранения бывают двух разных видов:

1. Механизмы хранения транзакций безопасны для транзакций и позволяют откатывать транзакции с помощью простых команд, таких как откат. Многие популярные движки MySQL, включая InnoDB^એ и NDB, принадлежат к этой категории.
2. Нетранзакционные механизмы хранения зависят от сложного ручного кода для отмены операторов, зафиксированных в базе данных. MyISAM^એ, MEMORY и многие другие механизмы MySQL нетранзакционные.

InnoDB^એ — это самый популярный механизм хранения по умолчанию. Он помогает поддерживать целостность данных, поддерживая ограничения внешнего ключа. Это означает, что любая операция CRUD с внешним ключом проверяется, чтобы убедиться, что она не приводит к несогласованности между разными таблицами.

Также обратите внимание, что таблица рейтингов использует столбцы movie_id и reviewer_id, оба внешних ключа, вместе в качестве первичного ключа. Этот шаг гарантирует, что рецензент не сможет дважды оценить один и тот же фильм.

Вы можете повторно использовать один и тот же курсор для нескольких запусков. В этом случае все исполнения станут одной атомарной транзакцией, а не несколькими отдельными транзакциями. Например, вы можете выполнить все операторы CREATE TABLE с одним курсором, а затем зафиксировать транзакцию только один раз:

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 ;

Чтобы получить некоторые результаты от объекта курсора, вам нужно использовать cursor.fetchall(). Этот метод извлекает все строки из последнего выполненного оператора. Предполагая, что у вас уже есть объект MySQLConnection в переменной соединения, вы можете распечатать все результаты, полученные с помощью cursor.fetchall():

>>> show_table_query = "DESCRIBE movies"
>>> with connection.cursor() as cursor:
...     cursor.execute(show_table_query)
...     # Fetch rows from last executed query
...     result = cursor.fetchall()
...     for row in result:
...         print(row)
...
('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('title', 'varchar(100)', 'YES', '', None, '')
('release_year', 'year(4)', 'YES', '', None, '')
('genre', 'varchar(100)', 'YES', '', None, '')
('collection_in_mil', 'int(11)', 'YES', '', None, '')

После выполнения приведенного выше кода вы должны получить таблицу, содержащую информацию обо всех столбцах в таблице фильмов. Для каждого столбца вы получите подробную информацию, например о типе данных столбца, о том, является ли столбец первичным ключом и т.д.

Изменение схемы таблицы с помощью оператора ALTER

В таблице фильмов у вас есть столбец с именем collection_in_mil, который содержит кассовые сборы фильма в миллионах долларов. Вы можете написать следующий оператор MySQL, чтобы изменить тип данных атрибута collection_in_mil с INT на DECIMAL:

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

DECIMAL (4,1) означает десятичное число, которое может иметь не более 4 цифр, из которых 1 является десятичной, например 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)
...     # Fetch rows from last executed query
...     result = cursor.fetchall()
...     print("Movie Table Schema after alteration:")
...     for row in result:
...         print(row)
...
Movie Table Schema after alteration
('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment')
('title', 'varchar(100)', 'YES', '', None, '')
('release_year', 'year(4)', 'YES', '', None, '')
('genre', 'varchar(100)', 'YES', '', None, '')
('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '')

Как показано в выходных данных, атрибут collection_in_mil теперь имеет тип DECIMAL (4,1). Также обратите внимание, что в приведенном выше коде вы дважды вызываете cursor.execute(). Но cursor.fetchall() выбирает строки только из последнего выполненного запроса, которым является show_table_query.

Удаление таблиц с помощью оператора DROP

Чтобы удалить таблицу, вам необходимо выполнить оператор DROP TABLE в MySQL. Удаление таблицы — необратимый процесс. Если вы выполните приведенный ниже код, вам нужно будет снова вызвать запрос CREATE TABLE для использования таблицы рейтингов в следующих разделах.

Чтобы удалить таблицу рейтингов, отправьте drop_table_query в cursor.execute():

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

Если вы выполните приведенный выше код, вы успешно удалите таблицу рейтингов.

Вставка записей в таблицы

В последнем разделе вы создали в своей базе данных три таблицы: фильмы, рецензенты и рейтинги. Теперь вам нужно заполнить эти таблицы данными. В этом разделе будут рассмотрены два разных способа вставки записей в MySQL Connector для Python.

Первый метод, .execute(), хорошо работает, когда количество записей невелико, а записи могут быть сложно-кодированными. Второй метод, .executemany(), более популярен и лучше подходит для реальных сценариев.

Использование .execute()

Первый подход использует тот же метод cursor.execute(), который вы использовали до сих пор. Вы пишете запрос INSERT INTO в строке и передаете его в cursor.execute(). Вы можете использовать этот метод для вставки данных в таблицу фильмов.

Для справки, таблица фильмов имеет пять атрибутов:

1. id
2. title
3. release_year
4. genre
5. collection_in_mil

Вам не нужно добавлять данные для id, так как AUTO_INCREMENT автоматически вычисляет id для вас. Следующий скрипт вставляет записи в таблицу фильмов:

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

Таблица фильмов теперь загружена тридцатью записями. В конце код вызывает connection.commit(). Крайне важно вызывать .commit() после внесения любых изменений в таблицу.

Использование .executemany()

Предыдущий подход более подходит, когда количество записей довольно мало, и вы можете записывать эти записи прямо в код. Но так редко бывает. Чаще данные хранятся в каком-то другом файле, или данные будут сгенерированы другим сценарием, и их нужно будет добавить в базу данных MySQL.

Вот здесь пригодится .executemany(). Он принимает два параметра:

1. Запрос, содержащий заполнители для записей, которые необходимо вставить.
2. Список, содержащий все записи, которые вы хотите вставить.

В следующем примере вставляются записи в таблицу 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.

Код использует %s в качестве заполнителя для двух строк, которые нужно было вставить в insert_reviewers_query. Заполнители действуют как спецификаторы формата и помогают зарезервировать место для переменной внутри строки. Затем указанная переменная добавляется в это место во время выполнения.

Аналогичным образом вы можете использовать .executemany() для вставки записей в таблицу рейтингов:

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.

Чтение записей с помощью оператора SELECT

Чтобы получить записи, вам необходимо отправить запрос SELECT в cursor.execute(). Затем вы используете cursor.fetchall() для извлечения извлеченной таблицы в виде списка строк или записей.

Попробуйте написать запрос MySQL, чтобы выбрать все записи из таблицы фильмов и отправить его в .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)
...
(1, 'Forrest Gump', 1994, 'Drama', Decimal('330.2'))
(2, '3 Idiots', 2009, 'Drama', Decimal('2.4'))
(3, 'Eternal Sunshine of the Spotless Mind', 2004, 'Drama', Decimal('34.5'))
(4, 'Good Will Hunting', 1997, 'Drama', Decimal('138.1'))
(5, 'Skyfall', 2012, 'Action', Decimal('304.6'))

Переменная результата содержит записи, возвращенные с помощью .fetchall(). Это список кортежей, представляющих отдельные записи из таблицы.

В приведенном выше запросе вы используете предложение LIMIT, чтобы ограничить количество строк, получаемых от оператора SELECT. Разработчики часто используют LIMIT для разбивки на страницы при обработке больших объемов данных.

В MySQL предложение 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)
...
('Forrest Gump', 1994)
('3 Idiots', 2009)
('Eternal Sunshine of the Spotless Mind', 2004)
('Good Will Hunting', 1997)
('Skyfall', 2012)

Теперь код выводит значения только из двух указанных столбцов: 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)
...
('Avengers: Endgame', Decimal('858.8'))
('Titanic', Decimal('659.2'))
('The Dark Knight', Decimal('535.4'))
('Toy Story 4', Decimal('434.9'))
('The Lion King', Decimal('423.6'))
('Deadpool', Decimal('363.6'))
('Forrest Gump', Decimal('330.2'))
('Skyfall', Decimal('304.6'))

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)
...
('Avengers: Endgame (2019)', Decimal('858.8'))
('Titanic (1997)', Decimal('659.2'))
('The Dark Knight (2008)', Decimal('535.4'))
('Toy Story 4 (2019)', Decimal('434.9'))
('The Lion King (1994)', Decimal('423.6'))

Если вы не хотите использовать предложение LIMIT и вам не нужно получать все записи, тогда объект курсора также имеет методы .fetchone() и .fetchmany():

• .fetchone() извлекает либо следующую строку результата в виде кортежа, либо None, если доступных строк больше нет.
• .fetchmany() извлекает следующий набор строк из результата в виде списка кортежей.У него есть аргумент размера, который по умолчанию равен 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()
...
('Avengers: Endgame (2019)', Decimal('858.8'))
('Titanic (1997)', Decimal('659.2'))
('The Dark Knight (2008)', Decimal('535.4'))
('Toy Story 4 (2019)', Decimal('434.9'))
('The Lion King (1994)', Decimal('423.6'))

Вывод с .fetchmany() аналогичен тому, который вы получили, когда использовали предложение LIMIT. Вы могли заметить дополнительный вызов cursor.fetchall() в конце. Вы делаете это, чтобы очистить все оставшиеся результаты, которые не были прочитаны .fetchmany(). Перед выполнением любых других операторов в том же соединении необходимо очистить все непрочитанные результаты. В противном случае возникает исключение InternalError: Unread result (обнаружен непрочитанный результат).

Обработка нескольких таблиц с помощью оператора JOIN

Если вы обнаружили, что вопросы в последнем разделе довольно просты, не волнуйтесь. Вы можете сделать ваши запросы SELECT настолько сложными, насколько захотите, используя те же методы, что и в предыдущем разделе.

Давайте посмотрим на несколько более сложные запросы 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)
...
('Night of the Living Dead', Decimal('9.90000'))
('The Godfather', Decimal('9.90000'))
('Avengers: Endgame', Decimal('9.75000'))
('Eternal Sunshine of the Spotless Mind', Decimal('8.90000'))
('Beasts of No Nation', Decimal('8.70000'))

Как показано выше, «’Night of the Living Dead» и «The Godfather» имеют самый высокий рейтинг в вашей базе данных online_movie_rating.

Чтобы найти имя рецензента, который дал наибольшее количество оценок, введите следующий запрос:

>>> 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', 4)

Mary Cooper — самый частый рецензент в этой базе данных. Как видно выше, не имеет значения, насколько сложен запрос, потому что в конечном итоге он обрабатывается сервером MySQL. Ваш процесс выполнения запроса всегда останется прежним: передайте запрос в cursor.execute() и получите результаты с помощью .fetchall().

Обновление и удаление записей из базы данных

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

Команда UPDATE

Одна из рецензентов в вашей базе данных, Amy Farah Fowler, сейчас замужем за Sheldon Cooper. Ее фамилия изменилась на 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.

Примечание. В запросе UPDATE предложение WHERE помогает указать записи, которые необходимо обновить. Если вы не используете WHERE, все записи будут обновлены!

Предположим, вам нужно предоставить возможность рецензентам изменять оценки. Рецензент предоставит три значения: 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("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"
""" % (
    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, 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(), вам необходимо установить для множественного аргумента метода значение True.

Если multi равно True, то cursor.execute() возвращает итератор. Каждый элемент в итераторе соответствует объекту курсора, который выполняет инструкцию, переданную в запросе. Приведенный выше код запускает цикл for на этом итераторе, а затем вызывает .fetchall() для каждого объекта курсора.

Примечание. Запуск .fetchall() для всех объектов курсора важен. Чтобы выполнить новый оператор в том же соединении, вы должны убедиться, что нет непрочитанных результатов предыдущих выполнений. Если есть непрочитанные результаты, вы получите исключение.

Если для операции не был получен набор результатов, то .fetchall() вызывает исключение. Чтобы избежать этой ошибки, в приведенном выше коде вы используете свойство cursor.with_rows, которое указывает, создавала ли последняя выполненная операция строки.

Хотя этот код должен решить вашу задачу, предложение WHERE в его текущем состоянии является основной целью для веб-хакеров. Он уязвим для так называемой атаки с использованием SQL-инъекции, которая может позволить злоумышленникам либо повредить вашу базу данных, либо использовать ее не по назначению.

Предупреждение: не пытайтесь ввести указанные ниже данные в свою базу данных! Они испортят вашу таблицу, и вам придется ее воссоздать.

Например, если пользователь отправляет movie_id = 18, reviewer_id = 15 и новый рейтинг = 5.0 в качестве входных данных, то результат будет выглядеть следующим образом:

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15
Enter new rating: 5.0
Enter username: 
Enter password:
[(18, 15, Decimal('5.0'))]

Рейтинг с movie_id = 18 и reviewer_id = 15 изменен на 5.0. Но если вы были хакером, вы могли бы отправить скрытую команду на входе:

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A
Enter new rating: 5.0
Enter username: 
Enter password:
[(18, 15, Decimal('5.0'))]

И снова выходные данные показывают, что указанный рейтинг был изменен на 5.0. Что изменилось?

Хакер при вводе украл reviewer_id и сделал запрос на обновление reviewers, где записал "; UPDATE reviewers SET last_name = "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)
...
('Chaitanya', 'A')
('Mary', 'A')
('John', 'A')
('Thomas', 'A')
('Penny', 'A')
('Mitchell', 'A')
('Wyatt', 'A')
('Andre', 'A')
('Sheldon', 'A')
('Kimbra', 'A')
('Kat', 'A')
('Bruce', 'A')
('Domingo', 'A')
('Rajesh', 'A')
('Ben', 'A')
('Mahinder', 'A')
('Akbar', 'A')
('Howard', 'A')
('Pinkie', 'A')
('Gurkaran', 'A')
('Amy', 'A')
('Marlon', 'A')

Приведенный выше код отображает first_name и last_name для всех записей в таблице проверяющих. Атака с использованием 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() проверяет, что значения в кортеже, полученном в качестве аргумента, имеют требуемый тип данных.

Если пользователь попытается ввести какие-то проблемные символы, код вызовет исключение:

$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15"; UPDATE reviewers SET last_name = "A
Enter new rating: 5.0
Enter username: 
Enter password:
1292 (22007): Truncated incorrect DOUBLE value: '15";
UPDATE reviewers SET last_name = "A'

cursor.execute() вызовет исключение, если обнаружит какие-либо нежелательные символы во вводе пользователем. Вы должны использовать этот подход всякий раз, когда вы включаете пользовательский ввод в запрос. Есть и другие способы предотвращения атак SQL-инъекций.

Команда DELETE

Удаление записей очень похоже на обновление записей. Вы используете оператор DELETE для удаления выбранных записей.

Примечание. Удаление — необратимый процесс. Если вы не используете предложение WHERE, все записи из указанной таблицы будут удалены. Вам нужно будет снова запустить запрос INSERT INTO, чтобы вернуть удаленные записи.

Рекомендуется сначала выполнить запрос 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)
...
(2, 7)
(2, 8)
(2, 12)
(2, 23)

Приведенный выше фрагмент кода выводит reviewer_id и movie_id для записей в таблице рейтингов, где 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. Есть еще два популярных способа:

1. mysqlclient — это библиотека, которая является близким конкурентом официального коннектора и активно обновляется новыми функциями. Поскольку его ядро ​​написано на C, он имеет лучшую производительность, чем официальный коннектор на чистом Python. Большой недостаток в том, что его довольно сложно настроить и установить, особенно в Windows.
2. MySQLdb — это устаревшее программное обеспечение, которое до сих пор используется в коммерческих приложениях. Он написан на C и работает быстрее, чем MySQL Connector / Python, но доступен только для Python 2.

Эти соединители действуют как интерфейсы между вашей программой и базой данных MySQL, и вы отправляете через них свои SQL-запросы. Но многие разработчики предпочитают использовать объектно-ориентированную парадигму, а не SQL-запросы для управления данными.

Object-relational mapping (ORM^એ) — это метод, который позволяет вам запрашивать данные из базы данных и управлять ими напрямую, используя объектно-ориентированный язык. Библиотека ORM инкапсулирует код, необходимый для управления данными, что избавляет от необходимости использовать даже крошечный бит SQL. Вот самые популярные ORM Python для баз данных на основе SQL:

1. SQLAlchemy — это ORM, который упрощает взаимодействие между Python и другими базами данных SQL. Вы можете создавать разные движки для разных баз данных, таких как MySQL, PostgreSQL, SQLite и т. Д. SQLAlchemy обычно используется вместе с библиотекой pandas для обеспечения полной функциональности обработки данных.
2. peewee — это легкий и быстрый ORM, который можно быстро настроить. Это очень полезно, когда ваше взаимодействие с базой данных ограничивается извлечением нескольких записей. Например, если вам нужно скопировать выбранные записи из базы данных MySQL в файл CSV, тогда peewee может быть вашим лучшим выбором.
3. Django ORM — одна из самых мощных функций Django, поставляемая вместе с веб-фреймворком Django. Он может взаимодействовать с различными базами данных, такими как SQLite, PostgreSQL и MySQL. Многие приложения на основе Django используют Django ORM для моделирования данных и базовых запросов, но часто переключаются на SQLAlchemy для более сложных требований.

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

Заключение

В этом уроке вы узнали, как использовать MySQL Connector/Python для интеграции базы данных MySQL с вашим приложением Python. Вы также увидели некоторые уникальные особенности базы данных MySQL, которые отличают ее от других баз данных SQL.

По пути, вы узнали о некоторых передовых методах программирования, которые стоит учесть, когда дело доходит до установления соединения, создания таблиц, а также вставки и обновления записей в приложении базы данных. Вы также разработали образец базы данных MySQL для онлайн-рейтинговой системы фильмов и взаимодействовали с ней непосредственно из приложения Python.

Здесь вы узнали, как:

• Подключить приложение Python к базе данных MySQL.
• Перенести данные из базы данных MySQL в Python для дальнейшего анализа.
• Выполнять SQL-запросы из вашего приложения Python.
• Обрабатывать исключения при доступе к базе данных.
• Предотвращать атаки SQL-инъекций на ваше приложение.

Если вы заинтересованы,Python также имеет коннекторы для других СУБД, таких как MongoDB и PostgreSQL. Для получения дополнительной информации ознакомьтесь с учебными пособиями по базам данных Python.

Источник

Получить в PDF