🏠 Самостоятельная практика

Задание: расширение схемы и новые запросы

Контекст

Вы работаете с приложением для учёта товаров. Необходимо добавить модель Product к уже знакомым User и Address, и написать аналитические запросы.

Часть 1: Расширение схемы

Добавьте модель Product к существующей схеме:

• id — первичный ключ
• name — название товара (строка, до 100 символов)
• price — цена (число с плавающей точкой)
• user_id — внешний ключ на таблицу users

Добавьте двустороннее отношение: User.products / Product.user.

Часть 2: Запросы (5 штук)

1. Дорогие товары: вывести все товары с ценой выше 1000. Показать название и цену.
2. Сумма по пользователю: для каждого пользователя подсчитать суммарную стоимость его товаров. Использовать func.sum() и group_by.
3. Пользователи без товаров: вывести пользователей, у которых нет ни одного товара (LEFT JOIN + IS NULL).
4. Средняя цена: найти среднюю цену всех товаров.
5. Обновить цену: найти товар по названию и увеличить его цену на 10%.

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

1. Создать виртуальное окружение

# PowerShell
cd your-project-folder
python -m venv .venv
.venv\Scripts\Activate.ps1

# Установить зависимости
pip install sqlalchemy

2. Создать файл проекта

# Структура
practice_10/
├── models.py        # модели User, Address, Product
└── queries.py       # 5 запросов

3. models.py — базовая структура

from sqlalchemy import create_engine, ForeignKey, String, Integer, Float
from sqlalchemy.orm import (
    DeclarativeBase, Mapped, mapped_column,
    relationship, Session
)
from typing import List, Optional

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = 'users'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(50))
    age: Mapped[int] = mapped_column(Integer)
    addresses: Mapped[List["Address"]] = relationship("Address", back_populates="user")
    # TODO: добавить products relationship

class Address(Base):
    __tablename__ = 'addresses'
    id: Mapped[int] = mapped_column(primary_key=True)
    description: Mapped[Optional[str]] = mapped_column(String(255))
    user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
    user: Mapped["User"] = relationship("User", back_populates="addresses")

# TODO: добавить класс Product

engine = create_engine('sqlite:///practice10.db', echo=True)
Base.metadata.create_all(engine)

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

Шаг 1: Модель Product

class Product(Base):
    __tablename__ = 'products'
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    price: Mapped[float] = mapped_column(Float)
    user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
    user: Mapped["User"] = relationship("User", back_populates="products")

# В классе User добавить:
products: Mapped[List["Product"]] = relationship("Product", back_populates="user")

Шаг 2: Тестовые данные

with Session(engine) as session:
    alice = User(name="Alice", age=25)
    bob = User(name="Bob", age=30)
    charlie = User(name="Charlie", age=22)
    session.add_all([alice, bob, charlie])
    session.flush()

    products = [
        Product(name="Laptop", price=1500.0, user=alice),
        Product(name="Mouse", price=25.0, user=alice),
        Product(name="Monitor", price=800.0, user=bob),
        Product(name="Keyboard", price=150.0, user=bob),
        Product(name="Headphones", price=200.0, user=bob),
        # Charlie без товаров
    ]
    session.add_all(products)
    session.commit()

Шаг 3: Запросы

from sqlalchemy import func

with Session(engine) as session:
    # 1. Дорогие товары (цена > 1000)
    expensive = session.query(Product.name, Product.price).filter(Product.price > 1000).all()
    for name, price in expensive:
        print(f"{name}: {price}")

    # 2. Сумма по пользователю
    totals = (
        session.query(User.name, func.sum(Product.price).label('total'))
        .join(User.products)
        .group_by(User.id)
        .all()
    )
    for name, total in totals:
        print(f"{name}: {total:.2f}")

    # 3. Пользователи без товаров
    no_products = (
        session.query(User.name)
        .outerjoin(User.products)
        .filter(Product.id.is_(None))
        .all()
    )
    for u in no_products:
        print(f"No products: {u.name}")

    # 4. Средняя цена
    avg_price = session.query(func.avg(Product.price)).scalar()
    print(f"Average price: {avg_price:.2f}")

    # 5. Обновить цену Laptop на +10%
    product = session.query(Product).filter(Product.name == "Laptop").first()
    if product:
        product.price *= 1.10
        session.commit()
        print(f"New price: {product.price:.2f}")

Проверка в VS Code

Запуск через терминал

# В терминале VS Code (PowerShell)
.venv\Scripts\Activate.ps1
python models.py    # создать таблицы
python queries.py   # выполнить запросы

Запуск через F5 (launch.json)

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

Отладка с точками останова

Поставьте breakpoint на строке for name, price in expensive:. В режиме отладки (F5) вы увидите значения переменных в панели "Переменные". Это удобно для проверки результатов каждого запроса.

Связь с материалом урока

• Запрос 1 — Задачи 2, 4 из практикума (задания / решения)
• Запрос 2 — Задача 18 (group_by с JOIN) + Задача 11 (func.sum аналогично func.avg)
• Запрос 3 — Задача 17 (пользователи без адресов → без товаров)
• Запрос 4 — Задача 11 (среднее)
• Запрос 5 — Задача 3 (обновление через атрибут)