📖 Теория: повторение уроков 80–82

1. Зачем нужны базы данных

Когда программы обрабатывают большие объёмы данных, хранить всё в файлах или переменных становится неудобно. Базы данных позволяют:

• Хранить структурированные данные вне программы.
• Быстро искать, обновлять и удалять записи.
• Работать с большими объёмами данных без загрузки их всех в память.
• Обеспечивать многопользовательский доступ и защиту данных.
• Делать программы более надёжными и масштабируемыми.

2. Библиотеки для работы с MySQL в Python

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

pip install pymysql

3. Создание соединения

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

import pymysql

connection = pymysql.connect(
    host="localhost",
    user="root",
    password="yourpassword",
    database="yourdatabase",
    charset="utf8mb4",
)

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

import pymysql

config = {
    "host": "ich-db.edu.itcareerhub.de",
    "user": "ich1",
    "password": "password",
    "database": "hr",
}

connection = pymysql.connect(**config)

Проверить, что соединение установлено, можно через атрибут connection.open:

if connection.open:
    print("Connection successful!")

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

connection.close()

4. Работа с курсором

После подключения создаётся объект курсора — через него отправляются SQL-запросы и получаются результаты.

cursor = connection.cursor()
cursor.execute("SELECT * FROM departments")
cursor.close()

• cursor() — метод соединения, который создаёт новый курсор.
• execute(sql) — отправляет SQL-запрос в базу данных.
• close() — закрывает курсор и освобождает ресурсы.

Особенности работы с курсором:

• SQL-запрос передаётся строкой.
• После выполнения запроса можно получить результат.
• Можно выполнять любые SQL-запросы: SELECT, INSERT, UPDATE, DELETE и другие.
• После каждого нового запроса старые результаты очищаются.
• При ошибке в запросе выбрасывается исключение.

5. Получение данных

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

cursor.execute("SELECT * FROM employees")

row = cursor.fetchone()
print("One row:", row)

rows_5 = cursor.fetchmany(5)
print("Five rows:", rows_5)

rows = cursor.fetchall()
print("All rows:", rows)

Курсор можно использовать как итератор — это удобно и экономит память на больших выборках:

cursor.execute("SELECT * FROM employees")
for row in cursor:
    print(row)

Особенности:

• fetchmany(size) позволяет читать данные порциями, чтобы не перегружать память.
• После того как строки считаны, повторный вызов fetchone() вернёт None, а fetchmany() и fetchall() — пустую коллекцию.
• Курсор — это итератор: каждый вызов чтения сдвигает указатель, поэтому повторный вызов методов читает только оставшиеся строки.

6. Параметризованные запросы

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

Позиционные параметры %s

cursor.execute(
    "SELECT * FROM employees WHERE department_id = %s OR salary > %s",
    (60, 20000)
)

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

cursor.execute(
    "SELECT * FROM employees WHERE department_id = %s",
    (100,)  # обязательно запятая
)

Именованные параметры %(name)s

cursor.execute(
    "SELECT * FROM employees WHERE department_id = %(dep_id)s OR salary > %(min_salary)s",
    {"min_salary": 20000, "dep_id": 60}
)

Преимущества параметризованных запросов:

• Безопасность — защита от SQL-инъекций.
• Универсальность — одно выражение можно выполнять с разными данными.
• Автоматическое экранирование — библиотека сама обрабатывает типы данных.
• Улучшение производительности — сервер может кэшировать план выполнения запроса.

7. Обработка ошибок

Чтобы программа не завершалась аварийно, ошибки при работе с базой обрабатываются через try ... except. Базовый класс всех исключений PyMySQL — pymysql.MySQLError.

import pymysql

try:
    connection = pymysql.connect(
        host="ich-db.edu.itcareerhub.de",
        user="root",
        password="wrong_password",
        database="test"
    )
    print("Connected!")
except pymysql.MySQLError as e:
    print("Connection error:", e)

Можно обрабатывать и более конкретные ошибки, например pymysql.ProgrammingError или pymysql.OperationalError.

8. Контекстный менеджер with

Конструкция with автоматически закрывает соединение и курсор, даже если внутри блока произошла ошибка.

with pymysql.connect(
    host="ich-db.edu.itcareerhub.de",
    user="ich1",
    password="password",
    database="hr"
) as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT * FROM employees")
        for row in cursor:
            print(row)

Особенности:

• Код становится компактнее и безопаснее.
• Блок with автоматически вызывает close().
• Даже при ошибке ресурсы корректно закрываются.

9. Подключение без указания базы данных

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

import pymysql

config = {
    "host": "ich-db.edu.itcareerhub.de",
    "user": "ich1",
    "password": "password",
}

connection = pymysql.connect(**config)
with connection.cursor() as cursor:
    cursor.execute("SHOW DATABASES")
    for db in cursor:
        print(db)

Команда USE выбирает активную базу данных на время текущего соединения. Если база не выбрана, запросы к таблицам вызовут ошибку No database selected.

10. DictCursor

По умолчанию курсор возвращает строки в виде кортежей. Чтобы получать результаты в виде словарей с названиями колонок в качестве ключей, используется DictCursor.

import pymysql
from pymysql.cursors import DictCursor

config = {
    "host": "ich-db.edu.itcareerhub.de",
    "user": "ich1",
    "password": "password",
    "database": "hr",
    "cursorclass": DictCursor,
}

connection = pymysql.connect(**config)
with connection.cursor() as cursor:
    cursor.execute("SELECT * FROM employees")
    result = cursor.fetchone()
    print(result)
    print(result["first_name"])  # доступ по имени столбца

11. Изменение данных: INSERT, UPDATE, DELETE

Запросы INSERT, UPDATE, DELETE сначала попадают во временное хранилище транзакции. Чтобы окончательно записать изменения в базу, нужно вызвать connection.commit(). Без commit() изменения будут потеряны при закрытии соединения.

cursor.execute(
    "INSERT INTO sales (item_name, quantity, price, sale_date) VALUES (%s, %s, %s, %s)",
    ("Keyboard", 2, 45.50, "2024-06-15")
)
connection.commit()

Создание баз данных и таблиц (CREATE DATABASE, CREATE TABLE) не требует вызова commit() — эти команды сохраняются сразу.

12. Создание баз данных и таблиц

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

config = {
    "host": "ich-edit.edu.itcareerhub.de",
    "user": "ich1",
    "password": "ich1_password_ilovedbs",
}

connection = pymysql.connect(**config)
with connection.cursor() as cursor:
    cursor.execute("CREATE DATABASE IF NOT EXISTS market")
    cursor.execute("USE market")
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS sales (
            id INT AUTO_INCREMENT PRIMARY KEY,
            item_name VARCHAR(100),
            quantity INT,
            price DECIMAL(10, 2),
            sale_date DATE
        )
    """)

13. Массовые операции через executemany()

Метод executemany() выполняет один и тот же SQL-запрос с разными наборами данных. Это удобно при массовом добавлении, обновлении или удалении строк.

cursor.executemany(
    "INSERT INTO sales (item_name, quantity, price, sale_date) VALUES (%s, %s, %s, %s)",
    [
        ("Notebook", 3, 19.99, "2024-06-15"),
        ("Pen", 10, 1.99, "2024-06-16"),
        ("Bag", 1, 49.90, "2024-06-17"),
    ]
)
connection.commit()

Особенности:

• Работает только с изменяющими запросами (INSERT, UPDATE, DELETE).
• Вторым аргументом передаётся список кортежей.
• Экономит ресурсы, потому что запрос компилируется один раз, а выполняется много раз.

14. Транзакции и rollback()

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

Преимущества транзакций:

• Защищают от частичных обновлений при сбоях.
• Гарантируют атомарность: всё или ничего.
• Полезны при нескольких связанных изменениях, например при перемещении денег между счетами.

Если в процессе транзакции возникает ошибка, используется connection.rollback(). Он отменяет все изменения, сделанные с начала текущей транзакции. В связке с try-except и commit() это позволяет сохранить целостность данных.

try:
    cursor.execute("UPDATE products SET stock = stock - 1 WHERE id = %s", (product_id,))
    cursor.execute("UPDATE customers SET balance = balance - %s WHERE id = %s", (price, customer_id))
    cursor.execute("INSERT INTO purchases (customer_id, product_id, purchase_date) VALUES (%s, %s, CURDATE())",
                   (customer_id, product_id))
    connection.commit()
    print("Purchase successful.")
except Exception as e:
    connection.rollback()
    print("Transaction failed:", e)