Как подключиться к базе данных MySQL на Python: PyMySQL, установка и выполнение запросов

Краткая памятка по подключению к MySQL в Python

1. Установите MySQL сервер и необходимую библиотеку (PyMySQL или mysql-connector-python).
2. Импортируйте библиотеку в Python-скрипт.
3. Создайте соединение с базой данных, указав хост, порт, пользователя, пароль и имя БД.
4. Создайте курсор для выполнения SQL-запросов.
5. Выполняйте запросы (SELECT, INSERT, UPDATE, DELETE) через курсор.
6. Для изменяющих запросов (INSERT, UPDATE, DELETE) используйте connection.commit().
7. Обрабатывайте результаты запросов с помощью fetchall(), fetchone() или fetchmany().
8. Закрывайте курсор и соединение после завершения работы.
9. Используйте параметризованные запросы для защиты от SQL-инъекций.
10. Оборачивайте код в блоки try-except для обработки ошибок.
11. Оптимизируйте подключение, используя пулы соединений при частых запросах.
12. Регулярно делайте резервное копирование базы данных.

Что такое PyMySQL?

Чтобы подключиться из Python в некоторые базы данных вам нужен Driver (Драйвер), это библиотека используемая для контакта с базой данных. С базой данных MySQL у вас есть три выбора приведенных ниже:

• MySQL/connector for Python
• MySQLdb
• PyMySQL

Таблица №1

Driver	Описание
MySQL/Connector for Python	Библиотека, предоставленная самим сообществом MySQL.
MySQLdb	MySQLdb это библиотека позволяющая подключиться в MySQL с Python, она написана на языке С, оно бесплатна в использовании и является открытым исходным кодом.
PyMySQL	Это библиотека позволяющая подключиться к MySQL с Python, и яляется чистой библиотекой Python. Цель PyMySQL это замена MySQLdb и работает на CPython, PyPy и IronPython.

PyMySQL это проект открытых исходных кодов, и его исходный код вы можете посмотреть ниже:

Установка необходимых библиотек

Установим пакет PyMySQL в наше виртуальное окружение. Данная библиотека является связующим звеном между Python и нашей СУБД:

У библиотеки есть весьма простая, понятная и большое комьюнити. комьюнити
документация

Создадим файл, в котором будет происходить вся магия и импортируем в него ранее установленный модуль:

Установка MySQL Server

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

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

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

Драйвер базы данных — программное обеспечение, позволяющее приложению подключаться и взаимодействовать с СУБД. Такие драйверы обычно поставляются в виде отдельных модулей. Сандартный интерфейс, которому должны соответствовать все драйверы баз данных Python, описан в PEP 249. Драйверы баз данных Python, такие как sqlite3 для SQLite, psycopg для PostgreSQL и MySQL Connector/Python для MySQL, следуют этим правилам.

Для установки драйвера (коннектора) воспользуемся менеджером пакетов pip:

pip установит коннектор в текущую активную среду. Чтобы работать с проектом изолированным образом, мы рекомендуем настроить виртуальную среду.

Проверим результат установки, запустив в терминале Python следующую команду:

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

Каким бы ни было приложение, первый шаг ― связать между собой приложение и базу данных.

Чтобы установить соединение, используем connect() из модуля. Эта функция принимает параметры host, user и password, а возвращает объект MySQLConnection. Учетные данные можно получить в результате ввода от пользователя:

Объект MySQLConnection хранится в переменной connection, которую мы будем использовать для доступа к серверу MySQL. Несколько важных моментов:

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

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

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

MySQL обязывает ставить точку с запятой (;) в конце оператора. Однако MySQL Connector/Python автоматически добавляет точку с запятой в конце каждого запроса.

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

Запрос CREATE DATABASE сохраняется в виде строки в переменной create_db_query, а затем передается на выполнение в ().

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

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

Итак, мы создали базу данных под названием online_movie_rating. Чтобы к ней подключиться, просто дополняем вызов connect() параметром database:

В этом разделе мы рассмотрим, как с помощью Python выполнять некоторые базовые запросы: CREATE TABLE, DROP и ALTER.

Подключение к БД

Первым делом нам нужно подключиться к базе. Создадим объект класса pymysql, вызовем у него метод connect и передадим в него параметры для подключения к нашей базе данных (БД): подключения

• host: если ваша БД находится на локальной машине, то его значение будет localhost, либо 127.0.0.1, либо IP адрес хостинга, на котором вы развернули СУБД
• port: стандартный 3306
• user: это логин пользователя
• password: пароль
• db_name: имя нашей базы данных

Обернем код в блок try/except, в блоке try будем подключаться к БД, а в блоке except будем выводить в терминал возможные ошибки:

Теперь давайте создадим простую таблицу, на которой сегодня потренируемся. Создаем переменную create_table_query и пишем запрос.

• id типа int со значением, auto increment
• name типа varchar
• password типа varchar
• email также типа varchar
• primary key у нас будет поле id

Для того чтобы выполнить запрос на создание таблицы, вызываем у метод execute, и передаем в него наш запрос. Выведем в print сообщение об успешном исполнении: Выведем в print сообщение об успешном исполнении:
cursor

Создание соединения с базой данных MySQL через Python

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

import # Создание соединения connection = (host=»localhost», # Хост подключения (адрес сервера MySQL) user=»username», # Имя пользователя password=»password», # Пароль database=»database» # Имя базы данных) # Проверка успешного соединения if connection.is_connected(): print(«Успешное подключение к MySQL») # Закрытие соединения ()

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

Для более продвинутого управления соединениями рекомендуется использовать конструкцию with, которая автоматически закроет соединение даже в случае возникновения ошибок:

import try: with (host=»localhost», user=»username», password=»password», database=»database») as connection: print(f»Подключено к серверу MySQL версии {connection.get_server_info()}») except as e: print(f»Ошибка подключения к MySQL: {e}»)

• port — порт, на котором работает MySQL сервер (по умолчанию 3306)
• charset — кодировка для передачи данных
• use_pure — использовать чистый Python-код (True) или C-расширение (False)
• connection_timeout — время ожидания подключения в секундах
• pool_name и pool_size — для настройки пула соединений

Выполнение SQL-запросов с помощью Python

После успешного подключения к базе данных, основная работа сводится к выполнению SQL-запросов. Для этого в mysql-connector-python используется объект курсора, который управляет контекстом выполнения запросов.

• SELECT — для получения данных
• INSERT — для добавления новых записей
• UPDATE — для изменения существующих данных
• DELETE — для удаления записей
• CREATE/DROP/ALTER — для управления структурой базы данных

import try: connection = (host=»localhost», user=»username», password=»password», database=»database») cursor = () # Выполнение простого запроса («SELECT * FROM users LIMIT 5″) # Получение результатов results = () # Вывод результатов for row in results: print(row) () () except as e: print(f»Ошибка при работе с MySQL: {e}»)

Важной особенностью работы с запросами является использование параметризованных запросов для защиты от SQL-инъекций:

# Небезопасный способ (НЕ ИСПОЛЬЗУЙТЕ!) user_id = «1 OR 1=1″ # Потенциально опасный ввод (f»SELECT * FROM users WHERE id = {user_id}») # Безопасный способ с параметризацией user_id = «1 OR 1=1» # Теперь безопасно («SELECT * FROM users WHERE id = %s», (user_id,))

Для работы с изменением данных (INSERT, UPDATE, DELETE) необходимо фиксировать транзакции с помощью метода commit():

try: cursor = () # Вставка новой записи sql = «INSERT INTO users (username, email) VALUES (%s, %s)» values = («new_user», «user@») (sql, values) # Фиксация изменений () print(f»Добавлена запись с ID: {}») except as e: # Откат при ошибке () print(f»Ошибка: {e}»)

MySQL Connector для Python также поддерживает управляемые транзакции, которые позволяют группировать несколько операций:

try: connection.start_transaction() («UPDATE accounts SET balance = balance – 100 WHERE user_id = 1») («UPDATE accounts SET balance = balance + 100 WHERE user_id = 2») () print(«Транзакция успешно выполнена») except: () print(«Транзакция отменена»)

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

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

Главный урок: никогда не пренебрегайте транзакциями, когда работаете с финансовыми или другими критически важными данными. Строчка с start_transaction() и commit() может сэкономить вам недели расследования таинственных исчезновений данных и, что важнее, доверие пользователей.

Обработка данных и результатов запросов в Python

После выполнения запросов необходимо эффективно обрабатывать полученные результаты. MySQL Connector предоставляет несколько методов для извлечения данных из результата запроса:

Таблица №2

Метод	Описание	Возвращаемое значение
fetchall()	Получает все строки результата	Список кортежей с данными
fetchone()	Получает следующую строку результата	Кортеж с данными или None
fetchmany(size)	Получает указанное количество строк	Список кортежей с данными
rowcount	Количество затронутых или полученных строк	Целое число

При работе с большими объемами данных рекомендуется использовать итерационный подход вместо загрузки всех данных в память:

(«SELECT * FROM large_table») # Вместо fetchall() используем построчную обработку for row in cursor: process_row(row) # Функция обработки каждой строки

Если нам необходимо работать с данными как со словарями, а не кортежами, можно использовать специальный тип курсора:

Для более сложных сценариев обработки данных можно комбинировать возможности Python с запросами MySQL:

import import pandas as pd from datetime import datetime connection = (host=»localhost», user=»username», password=»password», database=»sales_db») # Получение данных о продажах за последний месяц query = «»» SELECT product_id, product_name, SUM(quantity) as total_sold, SUM(price * quantity) as revenue FROM sales WHERE sale_date >= %s GROUP BY product_id, product_name ORDER BY revenue DESC «»» current_date = () first_day = datetime(current_date.year, current_date.month, 1) cursor = (dictionary=True) (query, (first_day,)) results = () # Преобразование в DataFrame для дальнейшего анализа sales_df = (results) # Расчет дополнительных метрик sales_df[‘average_price’] = sales_df[‘revenue’] / sales_df[‘total_sold’] # Вывод топ-5 продуктов print(sales_df.head(5)) () ()

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

• DECIMAL/NUMERIC -> Decimal (требуется import decimal)
• BLOB -> bytes
• JSON -> словарь или список Python (требуется ручная десериализация)

Важно также эффективно освобождать ресурсы. После завершения работы с курсором и соединением их следует закрыть:

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

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

• id
• title
• release year
• genre
• collection_in_mi

1. id
2. first_name
3. last_name

1. movie_id (foreign key)
2. reviewer_id (foreign key)
3. rating

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

Чтобы создать новую таблицу в MySQL, нам нужно использовать оператор CREATE TABLE. Следующий запрос MySQL создаст таблицу movies нашей базы данных online_movie_rating:

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

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

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

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

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

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

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

Отображение схемы таблиц с использованием оператора DESCRIBE

Мы создали три таблицы и можем просмотреть схему, используя оператор DESCRIBE.

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

DECIMAL(4,1) указывает на десятичное число, которое может иметь максимум 4 цифры, из которых 1 соответствует разряду десятых, например, 120.1, 3.4, 38.0 и т. д.

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

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

Для удаления таблиц служит оператор DROP TABLE. Удаление таблицы ― необратимый процесс. Если вы выполните приведенный ниже код, вам нужно будет снова вызвать запрос CREATE TABLE для таблицы ratings:

Заполним таблицы данными. В этом разделе мы рассмотрим два способа вставки записей с помощью MySQL Connector в коде Python.

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

Добавление данных в таблицу

За добавление данных в таблицу в SQL отвечает метод INSERT. Пишем запрос. Дословно говорим:
Таблицу мы создали, теперь давайте заполним её данными.

Вставить в таблицу users, перечисляем поля, которые хотим заполнить, а затем данные, которыми мы хотим наполнить запись в таблице.

Например, у нас будет пользователь Анна, с паролем qwerty и почтой от gmail:

Вызываем метод execute у cursor и передаем в него наш запрос. Для того чтобы наши данные занеслись в таблицу и сохранились там, нам нужно закоммитить или зафиксировать наш запрос. Вызываем метод commit у объекта connection:

Вставка записей с помощью.execute()

Первый подход использует тот же метод (), который мы применяли до сих пор. Пишем запрос INSERT INTO и передаем в ():

аблица movies теперь заполнена тридцатью записями. В конце код вызывает (). Не забывайте вызывать.commit() после выполнения любых изменений в таблице.

Вставка записей с помощью.executemany()

Предыдущий подход годится, когда количество записей мало, и их можно вставить из кода. Но обычно данные хранятся в файле или генерируются другим сценарием. Вот где пригодится.executemany(). Метод принимает два параметра:

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

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

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

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

Извлечение данных из таблицы

Напишем запрос на извлечение всех данных из таблицы. Звездочка в данном запросе забирает все, что есть в таблице:

У cursor есть замечательный метод fetchall, который извлекает из таблицы все строки. Нам лишь остается пробежаться по ним циклом и распечатать.

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

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

В MySQL оператору LIMIT можно передать два неотрицательных числовых аргумента:

При использовании двух числовых аргументов первый указывает смещение, равное в данном примере 2, а второй ограничивает количество возвращаемых строк до 5. То есть запрос из примера вернет строки с 3 по 7.

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

Словосочетание ORDER BY в запросе позволяет отсортировать сборы от самого высокого до самого низкого.

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

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

• .fetchone() извлекает следующую строку результата в виде кортежа, либо None, если доступных строк больше нет.
• .fetchmany() извлекает следующий набор строк из результата в виде списка кортежей. Для этого ему передается аргумент, по умолчанию равный 1. Если доступных строк больше нет, метод возвращает пустой список.

Снова извлечем названия пяти самых кассовых фильмов с указанием года выпуска, но на этот раз используя.fetchmany():

Вы могли заметить дополнительный вызов (). Мы делаем это, чтобы очистить все оставшиеся результаты, которые не были прочитаны.fetchmany().

Перед выполнением любых других операторов в том же соединении необходимо очистить все непрочитанные результаты. В противном случае вызывается исключение InternalError.

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

Не имеет значения, насколько сложен запрос ― в конечном счете он обрабатывается сервером MySQL. Процесс выполнения запроса всегда остается прежним: передаем запрос в (), получаем результаты с помощью.fetchall().

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

Удаление данных из таблицы

Напишем запрос на удаление. Говорим: Удалить запись из таблицы users, где id равен 1. У нас ведь пока только одна запись:

Команда UPDATE

Представим, что рецензент Amy Farah Fowler вышла замуж за Sheldon Cooper. Она сменила фамилию на Cooper, и нам необходимо обновить базу данных. Для обновления записей в MySQL используется оператор UPDATE:

Код передает запрос на обновление в (), а.commit() вносит необходимые изменения в таблицу reviewers.

Представим, что мы хотим дать возможность рецензентам изменять оценки. Программа должна знать movie_id, reviewer_id и новый rating. Пример на SQL:

Указанные запросы сначала обновляют рейтинг, а затем выведут обновленный. Напишем скрипт на Python, который позволит корректировать оценки:

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

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

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

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

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

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

Хакер перехватил запрос на обновление данных. Запрос на обновление, изменит last_name всех записей в таблице рецензентов «A»:

Приведенный код отображает first_name и last_name для всех записей в таблице проверяющих. Атака с использованием SQL-инъекции повредила эту таблицу, изменив last_name всех записей на «A».

Есть быстрое решение для предотвращения таких атак. Не добавляйте значения запроса, предоставленные пользователем, напрямую в строку запроса. Лучше обнолять сценарий с отправкой значений запроса в качестве аргументов в.execute():

Обратите внимание, что плейсхолдеры %s больше не заключены в строковые кавычки. () проверяет, что значения в кортеже, полученном в качестве аргумента, имеют требуемый тип данных. Если пользователь попытается ввести какие-то проблемные символы, код вызовет исключение:

Удаление записей: команда DELETE¶

Процедура удаления записей очень похожа на их обновление. Поскольку DELETE является необратимой операцией, мы рекомендуем сначала запускать запрос SELECT с тем же фильтром, чтобы убедиться, что вы удаляете нужные записи. Например, чтобы удалить все оценки фильмов, данные reviewer_id = 2, мы можем сначала запустить соответствующий запрос SELECT:

Приведенный фрагмент кода выводит пары reviewer_id и movie_id для записей в таблице оценок, для которых reviewer_id = 2. Убедившись, что это те записи, которые нужно удалить, выполним запрос DELETE с тем же фильтром:

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

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

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

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

• SQLAlchemy ― это ORM, которая упрощает взаимодействие между Python и другими базами данных SQL. Вы можете создавать разные движки для разных баз данных, таких как MySQL, PostgreSQL, SQLite и т. д. Читайте наш туториал по SQLAlchemy.
• peewee ― легкая и быстрая ORM-библиотека с простой настройкой, что очень полезно, когда ваше взаимодействие с базой данных ограничивается извлечением нескольких записей. Если нужно скопировать отдельные записи из базы данных MySQL в csv-файл, то лучший выбор ― peewee.
• Django ORM ― одна из самых мощных составляющих веб-фреймворка Django, позволяющая простым образом взаимодействовать с различными базами данных SQLite, PostgreSQL и MySQL. Многие приложения на основе Django используют Django ORM для моделирования данных и базовых запросов, однако для более сложных задач разработчики обычно используют SQLAlchemy.

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

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

Удаление таблицы

Последний запрос, который мы выполним. Давайте дропним нашу таблицу. Под словом дропнут подразумевается удаление всей таблицы целиком. Будьте аккуратны: данному запросу, как и запросу на создание таблицы, коммит не требуется:

Безопасность и оптимизация подключения к MySQL

Безопасность и производительность — ключевые аспекты при работе с базами данных. Рассмотрим основные практики, которые помогут защитить данные и оптимизировать работу с MySQL через Python.

Основным вектором атак на базы данных являются SQL-инъекции. Всегда используйте параметризованные запросы вместо конкатенации строк:

# Создание подготовленного запроса prepared_stmt = «INSERT INTO logs (user_id, action, timestamp) VALUES (%s, %s, %s)» # Многократное выполнение с разными параметрами log_entries = [(1, «login», «2026-04-01 10:00:00»), (1, «view_page», «2026-04-01 10:05:23»), (1, «logout», «2026-04-01 10:30:45»),] (prepared_stmt, log_entries) ()

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

import dbconfig = { «host»: «localhost», «user»: «username», «password»: «password», «database»: «database» } # Создание пула из 5 соединений connection_pool = (pool_name=»mypool», pool_size=5, **dbconfig) # Получение соединения из пула connection = connection_pool.get_connection() try: cursor = () («SELECT * FROM users») # Обработка результатов finally: () () # Возвращает соединение в пул, а не закрывает его

1. Минимизируйте количество запросов — объединяйте несколько запросов в один, где это возможно.
2. Используйте индексы в таблицах MySQL для полей, по которым часто выполняется поиск или сортировка.
3. Ограничивайте объем данных с помощью LIMIT, особенно при работе с большими таблицами.
4. Используйте транзакции для группировки множественных операций записи.
5. Настройте буферы для больших наборов данных.

# Неоптимальный подход (много отдельных INSERT) for item in items_list: («INSERT INTO items (name, price) VALUES (%s, %s)», (item[‘name’], item[‘price’])) # Оптимальный подход (один вызов для всех вставок) values = [(item[‘name’], item[‘price’]) for item in items_list] («INSERT INTO items (name, price) VALUES (%s, %s)», values)

Никогда не храните учетные данные базы данных непосредственно в коде. Вместо этого используйте:

• Переменные окружения
• Файлы конфигурации (вне репозитория)
• Менеджеры секретов (Vault, AWS Secrets Manager и др.)

Для обнаружения проблем и оптимизации производительности важно внедрить систему мониторинга и логирования:

import logging import time import # Настройка логгера (filename=», level=, format=’%(asctime)s – %(levelname)s – %(message)s’) def execute_query(connection, query, params=None): cursor = () start_time = () try: if params: (query, params) else: (query) elapsed = () – start_time (f»Query executed in {elapsed:.4f} seconds: {query[:100]}…») return cursor except as err: (f»Database error: {err}») raise finally: ()

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

Часто задаваемые вопросы о подключении к MySQL через Python

Вопрос: Какую библиотеку лучше использовать для подключения к MySQL в Python?
Ответ: Наиболее популярные библиотеки — PyMySQL и mysql-connector-python. Выбор зависит от ваших предпочтений и требований проекта.

Вопрос: Как установить PyMySQL?
Ответ: Используйте команду pip install pymysql в терминале или командной строке.

Вопрос: Что делать, если возникает ошибка подключения к базе данных?
Ответ: Проверьте правильность хоста, порта, имени пользователя и пароля. Убедитесь, что MySQL сервер запущен.

Вопрос: Как выполнить SQL-запрос через Python?
Ответ: Создайте курсор с помощью connection.cursor(), затем выполните запрос методом cursor.execute(‘SQL_QUERY’).

Вопрос: Нужно ли закрывать соединение с базой данных?
Ответ: Да, рекомендуется закрывать соединение и курсор после завершения работы, используя методы.close().

Вопрос: Как обрабатывать исключения при работе с MySQL?
Ответ: Используйте блоки try-except для перехвата ошибок, например, pymysql.err.OperationalError.

Вопрос: Можно ли использовать параметризованные запросы для безопасности?
Ответ: Да, используйте параметры в execute() для предотвращения SQL-инъекций, например: cursor.execute(‘SELECT * FROM users WHERE id = %s’, (user_id,)).

Вопрос: Как получить все строки из результата запроса?
Ответ: Используйте метод cursor.fetchall() для получения всех строк или cursor.fetchone() для одной строки.

Вопрос: Как вставить несколько записей за один раз?
Ответ: Используйте метод cursor.executemany() с списком кортежей данных.

Вопрос: Как обновить данные в таблице?
Ответ: Выполните SQL-запрос UPDATE с помощью cursor.execute(), затем зафиксируйте изменения через connection.commit().