🏠 Домашнее задание 4: SQLAlchemy запросы

Текст задания из LMS

Задача 1: Наполнение данными. Добавьте в базу данных следующие категории и продукты:

Добавление категорий: Добавьте в таблицу categories следующие категории:

• Название: «Электроника», Описание: «Гаджеты и устройства.»
• Название: «Книги», Описание: «Печатные книги и электронные книги.»
• Название: «Одежда», Описание: «Одежда для мужчин и женщин.»

Добавление продуктов: Добавьте в таблицу products следующие продукты, убедившись, что каждый продукт связан с соответствующей категорией:

• Название: «Смартфон», Цена: 299.99, Наличие на складе: True, Категория: Электроника
• Название: «Ноутбук», Цена: 499.99, Наличие на складе: True, Категория: Электроника
• Название: «Научно-фантастический роман», Цена: 15.99, Наличие на складе: True, Категория: Книги
• Название: «Джинсы», Цена: 40.50, Наличие на складе: True, Категория: Одежда
• Название: «Футболка», Цена: 20.00, Наличие на складе: True, Категория: Одежда

Задача 2: Чтение данных. Извлеките все записи из таблицы categories. Для каждой категории извлеките и выведите все связанные с ней продукты, включая их названия и цены.

Задача 3: Обновление данных. Найдите в таблице products первый продукт с названием «Смартфон». Замените цену этого продукта на 349.99.

Задача 4: Агрегация и группировка. Используя агрегирующие функции и группировку, подсчитайте общее количество продуктов в каждой категории.

Задача 5: Группировка с фильтрацией. Отфильтруйте и выведите только те категории, в которых более одного продукта.

Подготовка окружения

Шаг 0: Создать папку проекта

# В терминале (PowerShell):
mkdir python-advanced-hw4
cd python-advanced-hw4

Шаг 1: Инициализировать git-репозиторий

git init
git checkout -b lesson/07-sqlalchemy-queries

Шаг 2: Создать виртуальное окружение

# Создать venv в папке .venv
python -m venv .venv

# Активировать (Windows PowerShell):
.venv\Scripts\activate

# Активировать (Mac/Linux):
source .venv/bin/activate

Шаг 3: Установить зависимости

pip install sqlalchemy

# Зафиксировать зависимости
pip freeze > requirements.txt

Шаг 4: Создать .gitignore

# Файл .gitignore:
.venv/
__pycache__/
*.pyc
*.db

Пошаговое решение

Создайте файл queries.py. Ниже — полное решение с объяснением логики каждого шага.

Шаг 1: Импорты, модели, движок

Логика: используем модели из ДЗ 3 (Category + Product с ForeignKey). В этом задании упор на запросы, поэтому модели остаются прежними, а код расширяется новыми операциями.

from sqlalchemy import create_engine, select, String, Integer, Boolean, ForeignKey, func
from sqlalchemy.types import Numeric
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column, relationship, Session
)

# Движок — SQLite в памяти (сбрасывается при перезапуске)
engine = create_engine("sqlite:///:memory:", echo=False)

class Base(DeclarativeBase):
    pass

class Category(Base):
    __tablename__ = "categories"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    description: Mapped[str | None] = mapped_column(String(255))

    # Обратная связь: category.products — список продуктов
    products: Mapped[list["Product"]] = relationship(back_populates="category")

    def __repr__(self) -> str:
        return f"Category(id={self.id}, name={self.name!r})"

class Product(Base):
    __tablename__ = "products"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    price: Mapped[float] = mapped_column(Numeric(10, 2), default=0.00)
    in_stock: Mapped[bool] = mapped_column(Boolean, default=True)
    category_id: Mapped[int | None] = mapped_column(ForeignKey("categories.id"))

    # Прямая связь: product.category — объект Category
    category: Mapped["Category | None"] = relationship(back_populates="products")

    def __repr__(self) -> str:
        return f"Product(id={self.id}, name={self.name!r}, price={self.price})"

# Создать таблицы
Base.metadata.create_all(engine)
print("Таблицы созданы.")

Шаг 2: Задача 1 — Наполнение данными (Create)

Логика: сначала создаём категории и делаем flush(), чтобы SQLAlchemy присвоил им id. Затем создаём продукты, связывая их с объектами категорий через навигационную связь category=.

with Session(engine) as session:
    # Создаём три категории
    electronics = Category(name="Электроника", description="Гаджеты и устройства.")
    books       = Category(name="Книги",       description="Печатные книги и электронные книги.")
    clothing    = Category(name="Одежда",      description="Одежда для мужчин и женщин.")

    session.add_all([electronics, books, clothing])
    # flush() — отправляет INSERT в транзакции, но не делает COMMIT
    # нужно, чтобы объекты получили id и их можно было использовать в продуктах
    session.flush()

    # Создаём продукты со ссылкой на категорию
    products = [
        Product(name="Смартфон",                  price=299.99, in_stock=True, category=electronics),
        Product(name="Ноутбук",                   price=499.99, in_stock=True, category=electronics),
        Product(name="Научно-фантастический роман",price=15.99,  in_stock=True, category=books),
        Product(name="Джинсы",                    price=40.50,  in_stock=True, category=clothing),
        Product(name="Футболка",                  price=20.00,  in_stock=True, category=clothing),
    ]
    session.add_all(products)
    session.commit()
    print("Данные добавлены: 3 категории, 5 продуктов.")

Шаг 3: Задача 2 — Чтение данных (Read)

Логика: выбираем все категории, затем для каждой обращаемся к навигационному атрибуту .products. SQLAlchemy автоматически делает SELECT для продуктов каждой категории (lazy loading).

print("\n--- Задача 2: Чтение категорий и продуктов ---")
with Session(engine) as session:
    categories = session.scalars(select(Category)).all()
    for cat in categories:
        print(f"\nКатегория: {cat.name}")
        for prod in cat.products:
            print(f"  - {prod.name}: {prod.price}")

Шаг 4: Задача 3 — Обновление цены Смартфона

Логика: используем .first() — нам нужен первый продукт с именем «Смартфон». После изменения атрибута вызываем commit() — SQLAlchemy автоматически генерирует UPDATE.

print("\n--- Задача 3: Обновление цены Смартфона ---")
with Session(engine) as session:
    stmt = select(Product).where(Product.name == "Смартфон")
    smartphone = session.scalars(stmt).first()

    if smartphone:
        print(f"До: {smartphone.name} = {smartphone.price}")
        smartphone.price = 349.99
        session.commit()
        print(f"После обновления — новая цена: 349.99")
    else:
        print("Смартфон не найден")

Шаг 5: Задача 4 — Агрегация и GROUP BY

Логика: используем func.count() для подсчёта продуктов + group_by() для группировки по категории. session.execute() (а не session.scalars()) нужен здесь, потому что запрос возвращает несколько столбцов (Category + count), а не один ORM-объект.

from sqlalchemy import func

print("\n--- Задача 4: Количество продуктов по категориям ---")
with Session(engine) as session:
    # Запрос: SELECT category.name, COUNT(product.id) GROUP BY category.id
    stmt = (
        select(Category.name, func.count(Product.id).label("product_count"))
        .join(Product, isouter=True)  # LEFT JOIN — чтобы учесть пустые категории
        .group_by(Category.id)
    )
    results = session.execute(stmt).all()
    for row in results:
        print(f"  {row.name}: {row.product_count} продуктов")

Шаг 6: Задача 5 — Фильтрация по агрегату (HAVING)

Логика: добавляем .having() к запросу из задачи 4, чтобы оставить только категории с количеством продуктов более одного. HAVING — это WHERE для агрегатных функций.

print("\n--- Задача 5: Категории с более чем одним продуктом ---")
with Session(engine) as session:
    stmt = (
        select(Category.name, func.count(Product.id).label("product_count"))
        .join(Product, isouter=True)
        .group_by(Category.id)
        .having(func.count(Product.id) > 1)  # аналог SQL HAVING COUNT > 1
    )
    results = session.execute(stmt).all()
    for row in results:
        print(f"  {row.name}: {row.product_count} продуктов")

print("\nГотово!")

Проверка в VS Code

Открыть проект

1. Откройте VS Code.
2. Меню File → Open Folder → выберите папку python-advanced-hw4.

Выбрать интерпретатор

1. Нажмите Ctrl+Shift+P → введите Python: Select Interpreter.
2. Выберите интерпретатор из .venv (помечен как «Recommended»).

Запустить файл

Вариант 1 — терминал:

# В терминале VS Code (Ctrl+`):
python queries.py

Вариант 2 — нажать F5 (нужен launch.json).

Настройка launch.json для отладки

{
  "version": "0.2.0",
  "configurations": [
    {
      "name": "Python: queries.py",
      "type": "debugpy",
      "request": "launch",
      "program": "${workspaceFolder}/queries.py",
      "console": "integratedTerminal",
      "env": {
        "PYTHONPATH": "${workspaceFolder}"
      }
    }
  ]
}

Точки останова (Breakpoints)

1. Кликните на номер строки слева от кода — появится красная точка.
2. Поставьте breakpoint на строке session.commit() в задаче 3.
3. Нажмите F5 — программа остановится до коммита.
4. В панели Variables проверьте значение smartphone.price — должно быть 349.99.
5. F5 — продолжить; F10 — следующая строка.

Debug Console

# При остановке на breakpoint в Debug Console:
smartphone.price     # → 349.99
smartphone.name      # → 'Смартфон'
len(products)        # → 5

Типичные ошибки при выполнении ДЗ

Ошибка: OperationalError — no such table

Причина: Base.metadata.create_all(engine) вызван до определения моделей или не вызван вовсе.

# Проверьте: create_all() должен быть ПОСЛЕ определения всех классов
Base.metadata.create_all(engine)  # только ПОСЛЕ Category и Product

Ошибка: AttributeError при обращении к .products

Причина: сессия закрыта до обращения к lazy-loaded атрибуту. Обращайтесь к cat.products внутри блока with Session(engine) as session:.

Ошибка: IntegrityError при добавлении продуктов

Причина: попытка добавить продукт с category_id, которого нет в таблице categories. Проверьте, что категории добавлены и flush() вызван до создания продуктов.

session.add_all([electronics, books, clothing])
session.flush()   # ← обязательно перед созданием продуктов!

Ошибка: count() возвращает 0 для всех категорий

Причина: в JOIN не указан правильный ключ связи, или join написан как INNER JOIN без продуктов. Используйте isouter=True (LEFT JOIN) чтобы не потерять категории без продуктов.

Как сдать задание

1. Убедитесь, что queries.py запускается без ошибок и выводит ожидаемый результат.
2. Добавьте файлы в git и создайте коммит:

   git add queries.py requirements.txt .gitignore
   git commit -m "lesson 07: SQLAlchemy queries — CRUD, filter, sort, aggregation"

3. Загрузите на GitHub:

   git push origin lesson/07-sqlalchemy-queries

4. Скопируйте ссылку на репозиторий и вставьте в LMS как ответ на задание.