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

💻 Примеры кода

Пример 1. Подключение без указания базы

Подключаемся к серверу и выводим список баз данных:

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)

Пример 2. Выбор базы через USE

После подключения выбираем базу hr и выводим список таблиц:

import pymysql

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

connection = pymysql.connect(**config)

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

Пример 3. Чтение через 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"])  # доступ по имени столбца

Пример 4. Создание базы и таблицы

Подключаемся к серверу с правами на изменения и создаём структуру:

import pymysql

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

Пример 5. INSERT, UPDATE, DELETE с 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()

# Обновление данных
cursor.execute(
    "UPDATE sales SET quantity = %s WHERE item_name = %s",
    (3, "Keyboard")
)
connection.commit()

# Удаление данных
cursor.execute(
    "DELETE FROM sales WHERE item_name = %s",
    ("Keyboard",)
)
connection.commit()

Пример 6. Массовая вставка через executemany()

Вставляем несколько строк одним вызовом:

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

Пример 7. Транзакция покупки с rollback()

Создаём таблицы, заполняем данными и пробуем выполнить покупку. Если денег недостаточно — откатываем изменения:

import pymysql

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

with pymysql.connect(**config) as connection:
    with connection.cursor() as cursor:
        cursor.execute("CREATE DATABASE IF NOT EXISTS shop")
        cursor.execute("USE shop")

        cursor.execute("""
            CREATE TABLE IF NOT EXISTS customers (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100),
                balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)
            )
        """)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100),
                price DECIMAL(10, 2),
                stock INT NOT NULL CHECK (stock >= 0)
            )
        """)
        cursor.execute("""
            CREATE TABLE IF NOT EXISTS purchases (
                id INT AUTO_INCREMENT PRIMARY KEY,
                customer_id INT,
                product_id INT,
                purchase_date DATE,
                FOREIGN KEY (customer_id) REFERENCES customers(id),
                FOREIGN KEY (product_id) REFERENCES products(id)
            )
        """)

        cursor.execute("DELETE FROM purchases")
        cursor.execute("DELETE FROM customers")
        cursor.execute("DELETE FROM products")
        cursor.executemany(
            "INSERT INTO customers (name, balance) VALUES (%s, %s)",
            [("Alice", 20.00), ("Bob", 200.00)]
        )
        cursor.executemany(
            "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)",
            [("Headphones", 99.99, 3), ("Mouse", 25.00, 5)]
        )
        connection.commit()

        # Попытка покупки с недостаточным балансом
        try:
            cursor.execute("SELECT id, price, stock FROM products WHERE name = %s", ("Headphones",))
            product_id, price, stock = cursor.fetchone()
            if stock < 1:
                raise ValueError("Out of stock")
            cursor.execute("UPDATE products SET stock = stock - 1 WHERE id = %s", (product_id,))

            cursor.execute("SELECT id, balance FROM customers WHERE name = %s", ("Alice",))
            customer_id, balance = cursor.fetchone()
            if balance < price:
                raise ValueError("Insufficient funds")
            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)