Классы Python для базы данных MySQL: SELECT, INSERT и DELETE с pymysql

Классы Python для базы данных MySQL: SELECT, INSERT и DELETE с pymysql

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

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

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

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

pip install pymysql

Для macOS:

pip3 install pymysql

Установка MySQL

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

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

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

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

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

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

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

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

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

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

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

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

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

Создаём класс MySQL на Python

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

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

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
        )

С импортами тут все ясно - подключаем нашу библиотеку для работы с MySQL. 

Что такое __init__? Эта конструкция будет срабатывать при создании объекта класса. Внутрь нее мы передаём аргументы для создания подключения к базе данных. Также, мы передаём поле connection на хранение внутрь класса, чтобы оно не пропало. Далее мы будем активно использовать connection для обращения к базе.

    def add_user(self, name, age, email):
        insert_query = f"INSERT INTO `user` (name, age, email) VALUES ({name}, {age}, {email})"
        with self.connection.cursor() as cursor:
            cursor.execute(insert_query)
            self.connection.commit()

Метод add_user – тут мы будем создавать новую запись с пользователем, передаем его имя, возраст и почту. Внутри insert_query хранится запрос на языке SQL (обычная строка), в него мы и подставляем добавляемые значения. 
Для выполнения запроса используем curcor.execute, а для сохранения - connection.commit.

    def del_user(self, id):
        delete_query = f"DELETE FROM `user` WHERE id = {id}"
        with self.connection.cursor() as cursor:
            cursor.execute(delete_query)
            self.connection.commit()

Метод del_user – тут мы ищем по ID в базе данных человека и удаляем эту строчку.

    def update_age_by_id(self, new_age, id):
        update_query = f"UPDATE `user` SET age = {new_age} WHERE id = {id}"
        with self.connection.cursor() as cursor:
            cursor.execute(update_query)
            self.connection.commit()

В метод update_age_by_id поступают новый возраст человека и его ID, чтобы было можно его найти. 

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

Метод select_all_data делает запрос к базе на получение всех строчек, которые есть в таблице. Результат возвращается в виде объекта, который мы превращаем в список с помощью fetchall

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

Метод __del__ срабатывает, когда объект уничтожается. Это нужно, поскольку по документации pymysql нужно закрывать сессию подключения к БД после окончания работы. Поэтому внутри этого метода мы обращаемся к connection и вызываем метод close.

Весь класс с методами будет выглядеть так:

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
        )

    def add_user(self, name, age, email):
        insert_query = f"INSERT INTO `user` (name, age, email) VALUES ('{name}', {age}, '{email}')"
        with self.connection.cursor() as cursor:
            cursor.execute(insert_query)
            self.connection.commit()

    def del_user(self, id):
        delete_query = f"DELETE FROM `user` WHERE id = {id}"
        with self.connection.cursor() as cursor:
            cursor.execute(delete_query)
            self.connection.commit()

    def update_age_by_id(self, new_age, id):
        update_query = f"UPDATE `user` SET age = {new_age} WHERE id = {id}"
        with self.connection.cursor() as cursor:
            cursor.execute(update_query)
            self.connection.commit()

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

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

Использование класса MySQL

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

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

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

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

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:

Заключение

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

Думаю, вы заметили насколько лёгким становится управление базой если задействовать классы и методы 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

Комментарии