Урок 82. MySQL. Транзакции

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

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

• сначала подключиться, а затем выбрать базу вручную;
• выполнять административные задачи — создавать или удалять базы данных;
• переключаться между разными базами в рамках одного соединения.

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)

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

🗄️ Выбор базы данных через USE

Команда USE выбирает базу данных на время текущего соединения. После неё все запросы выполняются внутри выбранной базы:

with connection.cursor() as cursor:
    cursor.execute("USE hr")
    cursor.execute("SHOW TABLES")
    for row in cursor:
        print(row)

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

• USE действует только в рамках текущего соединения.
• Без выбора базы запросы к таблицам вызовут ошибку No database selected.
• Можно в любой момент переключиться на другую базу, выполнив новый USE other_database.

📖 DictCursor: строки как словари

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

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"])  # доступ по имени столбца

DictCursor удобен, когда:

• запрос возвращает много колонок и легко ошибиться с порядком индексов;
• нужно сделать код понятнее и самодокументированнее;
• структура таблицы может измениться, а порядок колонок — нет.

✅ Метод commit()

Запросы INSERT, UPDATE и DELETE сначала попадают во временное хранилище транзакции. Чтобы окончательно сохранить изменения в базе, нужно вызвать connection.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()  # фиксируем изменения

Зачем нужен commit():

• Без него изменения будут потеряны при закрытии соединения.
• Это защита: данные применяются только после явного подтверждения.

Команды CREATE DATABASE и CREATE TABLE не требуют commit() — они сохраняются сразу.

🔑 Подключение с правами на изменения

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

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

📦 Метод 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.
• Вторым аргументом передаётся список кортежей.
• Запрос компилируется один раз, а выполняется много — это экономит ресурсы.

🔄 Транзакции

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

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

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

↩️ Метод 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)

Если транзакция не подтверждена вызовом commit(), то rollback() отменяет все действия, выполненные с момента начала транзакции — то есть с последнего commit() или с открытия соединения.

✨ Современные практики

• Храните пароли в переменных окружения, а не в коде.
• Используйте with для автоматического закрытия соединения и курсора.
• Ловите конкретные исключения PyMySQL вместо общего Exception.
• Для сложных сценариев изучайте пулы соединений и ORM.