База данных MySQL в Python: SQL-запросы через класс и pymysql

Сегодня разберёмся с классами Python на примере MySQL. Эта база данных достаточно производительная, чтобы обрабатывать большое количество запросов. На ней работают как небольшие сайты, так и корпорации типа Amazon (подробнее с историей этой СУБД можно ознакомиться тут).

Что потребуется:

1. Компьютер или ноутбук
2. Редактор кода (У меня PyCharm)
3. Python версии 3.9 и выше
4. Соединение с интернетом

Установка для Windows:

pip install pymysql

Для macOS:

pip3 install pymysql

Python для работы с базой данных MySQL

Сначала разберёмся с терминами. MySQL - это популярная реляционная СУБД (система управления базами данных), которая хранит данные в таблицах со строками и столбцами. На MySQL работают и небольшие сайты, и крупные сервисы - она быстрая, бесплатная и проверенная временем.

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

Связка Python и база данных MySQL - это рабочая лошадка веб-разработки. Бот сохраняет пользователей, сайт хранит товары, парсер складывает собранные данные - почти везде под капотом СУБД и запросы к ней из кода.

Общаются Python и MySQL на языке SQL. Это язык запросов, которым мы говорим базе, что сделать: добавить строку (INSERT), прочитать (SELECT), изменить (UPDATE) или удалить (DELETE). Python лишь отправляет эти SQL-запросы через специальную библиотеку-драйвер. Мы будем использовать pymysql - простую и популярную библиотеку для работы с MySQL в Python.

Установка MySQL

Самый простой способ - задействовать программу OpenServer. Эта программа помогает локально поднимать сервера для сайтов и отображать их в браузере. Мы будем использовать её для быстрого доступа к БД.

1. Скачиваем установочный файл отсюда

2. Запускаем его и следуем всем инструкциям

3. Запускаем OpenServer и ищем внизу флажок:

4. Кликаем на него и выбираем пункт настройки:

5. Ищем в настройках вкладку модули:

6. Если в выпадающем меню «MySQL/MariaDB» выставлено "не использовать", кликаем и выбираем самую последнюю версию СУБД MySQL:

Настройка базы данных MySQL в PhpMyAdmin

Запускаем сервер в OpenServer, наводим на вкладку "дополнительно" и нажимаем на "PhpMyAdmin":

Дальше вас перекидывает на сайт, где мы вводим логин "root". 
Поле "пароль" остается пустым.

В главном меню нам нужен пункт "создать БД". В этом окне придумываем имя базы данных и нажимаем "создать" (тип кодировки не трогаем, оставляем стандартный). После всех манипуляций у нас есть база данных. Остается создать таблицу внутри:

Придумываем имя таблицы и добавляем несколько столбцов. 
Нам потребуется 4 столбца, добавляем их и нажимаем "вперед":

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

Класс для базы данных MySQL на Python

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

[TGBLOCK]

Сформируем класс для базы данных MySQL и добавим ему несколько полезных методов. Подключение к базе создаём один раз в конструкторе __init__, а дальше переиспользуем во всех методах:

import pymysql

class MySQL:
    def __init__(self, host, port, user, password, db_name):
        self.connection = pymysql.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=db_name,
            cursorclass=pymysql.cursors.DictCursor
        )

Конструкция __init__ срабатывает при создании объекта класса. Внутрь неё мы передаём параметры подключения к базе данных MySQL. Объект соединения сохраняем в поле self.connection - дальше через него обращаемся к базе.

Метод добавления пользователя. Обратите внимание: значения подставляются не через f-строку, а через параметры %s - это важно, ниже объясню почему:

    def add_user(self, name, age, email):
        query = "INSERT INTO `user` (name, age, email) VALUES (%s, %s, %s)"
        with self.connection.cursor() as cursor:
            cursor.execute(query, (name, age, email))
            self.connection.commit()

Внутри query хранится SQL-запрос на добавление строки. Сами значения мы передаём вторым аргументом в cursor.execute - кортежем. Для выполнения запроса используем cursor.execute, а чтобы изменения реально записались в базу - connection.commit.

Метод удаления по ID:

    def del_user(self, user_id):
        query = "DELETE FROM `user` WHERE id = %s"
        with self.connection.cursor() as cursor:
            cursor.execute(query, (user_id,))
            self.connection.commit()

Метод изменения возраста по ID. На вход - новый возраст и ID пользователя, которого ищем:

    def update_age_by_id(self, new_age, user_id):
        query = "UPDATE `user` SET age = %s WHERE id = %s"
        with self.connection.cursor() as cursor:
            cursor.execute(query, (new_age, user_id))
            self.connection.commit()

Метод выборки всех строк. SQL-запрос SELECT * FROM user получает все записи таблицы, а fetchall превращает результат в список:

    def select_all_data(self):
        query = "SELECT * FROM `user`"
        with self.connection.cursor() as cursor:
            cursor.execute(query)
            return cursor.fetchall()

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

    def __del__(self):
        self.connection.close()

Метод __del__ срабатывает, когда объект уничтожается, и аккуратно закрывает соединение через connection.close.

Работа с базой данных MySQL в Python: SELECT, INSERT и DELETE

Чтобы начать работу с БД через Python, нам надо передать в объект класса MySQL ряд параметров - IP, порт, логин, пароль и название самой базы данных: 

host = '127.0.0.1'
port = 3306
user = 'root'
password = ''
db_name = 'user'

db = MySQL(host=host, port=port, user=user, password=password, db_name=db_name)

После этого мы сможем обращаться к методам объекта db. 
Давайте добавим несколько пользователей:

db.add_user('Анна', 26, 'anna.holkon@gmail.com')
db.add_user('Егор', 19, 'egor22jokelton@mail.ru')

Тем самым мы в таблицу добавили Анну и Егора.

print(db.select_all_data())

Выводим в консоль весь массив данных с помощью метода select_all_data:

[{'id': 1, 'name': 'Анна', 'age': 26, 'email': 'anna.holkon@gmail.com'}, 
{'id': 2, 'name': 'Егор', 'age': 19, 'email': 'egor22jokelton@mail.ru'}]

Как это выглядит в PhpMyAdmin:

Удалим пользователя 1 из таблицы:

db.del_user(1)

Проверяем базу данных:

Попробуем поменять возраст второго пользователя:

db.update_age_by_id(12, 2)

Егору было 19 лет, стало 12:

Параметризованные SQL-запросы и защита от инъекций

Выше во всех методах значения передавались через %s, а не подставлялись прямо в строку запроса. Это не мелочь, а главное правило безопасной работы с базой данных MySQL в Python.

Сравните. Опасный вариант через f-строку:

# ТАК ДЕЛАТЬ НЕЛЬЗЯ
query = f"INSERT INTO `user` (name, age, email) VALUES ('{name}', {age}, '{email}')"
cursor.execute(query)

Если в name придёт строка вроде '; DROP TABLE user; --, она встроится прямо в SQL-запрос, и база выполнит чужую команду. Так работают SQL-инъекции - одна из самых частых уязвимостей.

Безопасный вариант - параметризованный запрос:

# ПРАВИЛЬНО
query = "INSERT INTO `user` (name, age, email) VALUES (%s, %s, %s)"
cursor.execute(query, (name, age, email))

Здесь pymysql сам подставит значения, экранирует кавычки и спецсимволы. Никакие данные пользователя не превратятся в исполняемый SQL-код. Плейсхолдер в pymysql всегда %s - независимо от типа данных, кавычки писать не нужно.

Правило простое: любые внешние данные в SQL-запрос передавайте только через параметры %s. f-строки и конкатенацию в запросах не используйте никогда.

Основные SQL-запросы: SELECT, INSERT, UPDATE, DELETE

Через Python мы отправляем в базу данных MySQL обычные SQL-запросы. Разберём четыре базовые операции - их называют CRUD (создать, прочитать, обновить, удалить).

INSERT - добавить строку:

INSERT INTO `user` (name, age, email) VALUES (%s, %s, %s)

SELECT - прочитать данные. Можно достать все строки или отфильтровать через WHERE:

SELECT * FROM `user`                    -- все строки
SELECT name, age FROM `user`            -- только нужные столбцы
SELECT * FROM `user` WHERE age > %s     -- с условием

UPDATE - изменить существующие строки:

UPDATE `user` SET age = %s WHERE id = %s

DELETE - удалить строки:

DELETE FROM `user` WHERE id = %s

Важный момент: в UPDATE и DELETE почти всегда нужен WHERE. Без него SQL-запрос изменит или удалит сразу всю таблицу. DELETE FROM user без условия очистит всех пользователей - частая и болезненная ошибка новичков.

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

Получение данных из MySQL в Python: fetchall, fetchone, fetchmany

Когда мы выполняем SELECT, данные не возвращаются сразу - их нужно забрать у курсора. Для этого есть три метода, и выбор зависит от того, сколько строк вам нужно.

with self.connection.cursor() as cursor:
    cursor.execute("SELECT * FROM `user`")

    one = cursor.fetchone()      # одна следующая строка (или None)
    many = cursor.fetchmany(5)   # список из 5 строк
    rows = cursor.fetchall()     # список всех оставшихся строк

• fetchone - берёт одну строку. Удобно, когда ищете конкретного пользователя по ID и ждёте один результат.
• fetchmany(n) - берёт пачку из n строк. Пригодится для постраничной выдачи или обработки большой таблицы кусками.
• fetchall - берёт всё разом. Просто, но на больших таблицах съедает много памяти.

Поскольку мы создавали курсор с cursorclass=pymysql.cursors.DictCursor, каждая строка приходит словарём вида {'id': 1, 'name': 'Анна', 'age': 26}. К полям обращаемся по имени: row['name']. Без DictCursor строки были бы кортежами, и пришлось бы обращаться по индексу - менее наглядно.

Обработка ошибок MySQL в Python и транзакции

Запрос к базе данных может упасть: пропало соединение, нарушено уникальное поле, ошибка в SQL. Если не обработать исключение, программа аварийно завершится. Оборачиваем работу с базой в try / except:

def add_user(self, name, age, email):
    query = "INSERT INTO `user` (name, age, email) VALUES (%s, %s, %s)"
    try:
        with self.connection.cursor() as cursor:
            cursor.execute(query, (name, age, email))
            self.connection.commit()
    except pymysql.MySQLError as e:
        self.connection.rollback()
        print(f"Ошибка при добавлении пользователя: {e}")

Главное здесь - rollback. Если внутри одной операции было несколько запросов и один упал, rollback откатывает базу данных к состоянию до начала, чтобы не остались наполовину записанные данные. Это и есть транзакция: либо выполняются все запросы, либо ни одного.

commit подтверждает изменения, rollback отменяет. Пока вы не вызвали commit, изменения в MySQL не сохранены и видны только внутри текущего соединения.

pymysql, mysql-connector или SQLAlchemy

Для работы с MySQL в Python есть несколько библиотек. Коротко, чем они отличаются и что когда брать.

pymysql и mysql-connector работают похоже - отправляют SQL-запросы и возвращают строки. Разница в деталях установки и производительности. SQLAlchemy стоит особняком: это ORM, она позволяет описывать таблицы классами Python и не писать SQL руками вообще.

Для знакомства с базой данных MySQL на Python pymysql - оптимальный вариант: минимум магии, виден весь SQL, легко понять что происходит. Когда проект вырастет и ручных запросов станет много, есть смысл посмотреть в сторону SQLAlchemy.

Частые ошибки при работе с MySQL в Python

Грабли, на которые чаще всего наступают новички с базой данных MySQL в Python.

• Забыли commit. Сделали INSERT или UPDATE, а в базе ничего не изменилось. Без connection.commit изменения не сохраняются. Для SELECT commit не нужен.
• SQL-инъекции через f-строки. Подставляете данные прямо в запрос - открываете дыру в безопасности. Всегда параметры %s.
• UPDATE и DELETE без WHERE. Один такой запрос меняет или удаляет всю таблицу. Проверяйте условие перед запуском.
• Не закрыли соединение. Открытые подключения копятся и упираются в лимит MySQL. Закрывайте через close или контекстный менеджер.
• Перепутали порядок параметров. Значения в кортеже подставляются по очереди. Если поменять местами age и id, обновится не та строка.
• Ошибка Access denied. Чаще всего неверный логин, пароль или имя базы данных в параметрах подключения. Проверьте их в PhpMyAdmin.

Полный пример работы с базой данных MySQL на Python

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

Шаг первый - подключаемся к базе данных MySQL. В Python создаём объект класса и передаём параметры доступа:

db = MySQL(host='127.0.0.1', port=3306, user='root', password='', db_name='user')

Шаг второй - наполняем таблицу. Python отправляет в MySQL запросы INSERT через метод класса:

db.add_user('Анна', 26, 'anna@example.com')
db.add_user('Егор', 19, 'egor@example.com')

Шаг третий - читаем данные обратно в Python. Метод возвращает список словарей, с которым дальше работает обычный код Python:

users = db.select_all_data()
for user in users:
    print(user['name'], user['age'])

Шаг четвёртый - меняем и удаляем. MySQL выполняет UPDATE и DELETE, а Python лишь вызывает методы:

db.update_age_by_id(20, 2)   # Егору теперь 20
db.del_user(1)               # удалили Анну

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

MySQL или SQLite: какую базу данных выбрать для Python

Кроме MySQL, в Python часто используют SQLite и PostgreSQL. Коротко, когда какая база данных уместнее.

• SQLite - встроена в Python, не требует сервера, вся база данных лежит в одном файле. Идеальна для небольших приложений, прототипов и локальных скриптов на Python. Минус - слабо тянет много одновременных записей.
• MySQL - полноценный сервер базы данных. Берут, когда с данными работает много пользователей сразу: сайты, боты, сервисы. Наш случай. Python подключается к MySQL по сети через драйвер вроде pymysql.
• PostgreSQL - тоже серверная СУБД, более продвинутая по возможностям. В Python подключается аналогично MySQL, через свой драйвер.

Хорошая новость: код на Python для всех трёх баз данных похож. Принцип один - подключение, курсор, SQL-запрос, commit. Поэтому, освоив работу с базой данных MySQL на Python, вы почти без переучивания перейдёте на SQLite или PostgreSQL, если проект этого потребует.

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

FAQ: база данных MySQL и Python

Как подключить базу данных MySQL к Python?

Установите драйвер pip install pymysql, затем вызовите pymysql.connect() с параметрами host, port, user, password и database. Объект соединения используйте для отправки SQL-запросов через курсор.

Чем pymysql отличается от mysql-connector в Python?

pymysql - это сторонний драйвер, написанный целиком на Python. mysql-connector - официальный драйвер от Oracle. Оба отправляют SQL-запросы в базу данных MySQL и работают похоже, разница в установке и мелких деталях.

Зачем в Python нужны параметризованные SQL-запросы?

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

Почему изменения не сохраняются в базе данных?

Скорее всего, забыли connection.commit() после INSERT, UPDATE или DELETE. Без commit изменения остаются в рамках текущей транзакции и не записываются в MySQL.

Что лучше для python и базы данных - pymysql или SQLAlchemy?

Для учёбы и небольших проектов - pymysql: виден весь SQL, проще понять. Для крупных проектов удобнее SQLAlchemy: она работает с базой данных через объекты Python без ручных запросов.

Нужно ли знать SQL, чтобы работать с MySQL на Python?

Базовый SQL знать стоит: даже простые запросы SELECT и INSERT вы пишете на нём. Но глубоко погружаться необязательно - на Python достаточно понимать четыре операции CRUD. Остальное Python берёт на себя: подключение к базе, передачу запроса и разбор ответа. А с библиотекой SQLAlchemy на Python можно почти не писать SQL руками.

Заключение

Мы разобрали работу с базой данных MySQL в Python от начала до конца: подняли MySQL через OpenServer, создали базу и таблицу в PhpMyAdmin, написали класс на pymysql и наполнили его методами для SELECT, INSERT, UPDATE и DELETE.

Главное, что стоит запомнить: общение с базой идёт через SQL-запросы, значения всегда передаём параметрами %s против инъекций, а изменения подтверждаем через commit. Класс удобен тем, что прячет SQL внутрь методов - из остального кода работа с базой данных MySQL выглядит как обычные вызовы Python.

Дальше можно усложнять: добавить связи между таблицами, индексы для ускорения запросов или перейти на SQLAlchemy. Но базовый каркас работы Python с базой данных у вас уже есть - переносите его в свои проекты и автоматизируйте всё, что нужно.

Полезные ссылки

Документация pymysql: https://pymysql.readthedocs.io/en/latest/

Туториалы по MySQL: https://www.mysqltutorial.org/

Документация MySQL: https://dev.mysql.com/doc/refman/8.0/en/tutorial.html