📖 Теория: конспект блока SQLAlchemy (Уроки 05–07)

🎯 Итоговое повторение

⚡ Краткий конспект

SQLAlchemy — ORM + Core. ORM — объектный уровень, Core — SQL-выражения.
create_engine(url) — создаёт движок подключения к БД.
Модель — класс, наследующий Base. Содержит __tablename__ и Column.
ForeignKey + relationship() — связи между таблицами на уровне БД и ORM.
Session — рабочий интерфейс. add() → commit() → close().
CRUD: add/add_all → commit (Create); query().all()/filter() (Read); изменение атрибута → commit (Update); delete() → commit (Delete).
Запросы: filter(), order_by(), group_by(), having(), join(), subquery().

SQLAlchemy: ORM vs Core

SQLAlchemy — библиотека Python для работы с реляционными базами данных. Предоставляет два уровня:

Уровень	Описание	Когда использовать
ORM	Объектно-реляционное отображение. Работа через Python-классы.	Большинство приложений, скорость разработки
Core	SQL-выражения через Python API. Близко к «сырому» SQL.	Сложные запросы, высокая производительность

Преимущества ORM: абстракция, легкость, интеграция. Недостатки: производительность при сложных запросах, неявное поведение.

Преимущества Core: скорость, полный контроль над SQL. Недостатки: больше кода, ниже читаемость.

Движок (Engine) и строка подключения

Строка подключения имеет формат:

'<DBMS>+<library>://<user>:<password>@<host>:<port>/<database>'

Примеры:

from sqlalchemy import create_engine

# PostgreSQL
engine = create_engine('postgresql://user:password@localhost:5432/mydb')

# MySQL
engine = create_engine('mysql+pymysql://user:password@localhost:3306/mydb')

# SQLite (файл)
engine = create_engine('sqlite:///path/to/database.db')

# SQLite (в памяти — для тестов)
engine = create_engine('sqlite:///:memory:')

Модели: декларативный стиль

Декларативный стиль — основной способ определения моделей в SQLAlchemy ORM. Класс модели наследует Base и описывает таблицу через атрибуты.

from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, String, Boolean, Numeric

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), nullable=False)
    age = Column(Integer)
    is_active = Column(Boolean, default=True)

Основные типы колонок

Тип	SQL	Описание
`Integer`	INTEGER	Целое число
`String(n)`	VARCHAR(n)	Строка, максимум n символов
`Text`	TEXT	Длинный текст
`Boolean`	BOOLEAN	True/False
`Float`	FLOAT	Число с плавающей точкой
`Numeric(p,s)`	DECIMAL(p,s)	Фиксированная точность (финансы)
`DateTime`	DATETIME	Дата и время
`Date`	DATE	Только дата
`JSON`	JSON	JSON-данные

Параметры Column

Параметр	Описание
`primary_key=True`	Первичный ключ
`nullable=False`	Запрет NULL
`default=...`	Значение по умолчанию
`unique=True`	Уникальные значения
`index=True`	Создать индекс
`autoincrement=True`	Автоинкремент

Связи: ForeignKey и relationship()

ForeignKey — ограничение на уровне базы данных. Указывает, что значение колонки ссылается на первичный ключ другой таблицы.

relationship() — конструкция ORM. Позволяет обращаться к связанным объектам через Python-атрибуты.

Один-ко-многим (1:N)

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    addresses = relationship("Address", back_populates="user")

class Address(Base):
    __tablename__ = 'addresses'
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.id'))
    user = relationship("User", back_populates="addresses")

Один-к-одному (1:1)

addresses = relationship("Address", back_populates="user", uselist=False)

Многие-ко-многим (M:N)

from sqlalchemy import Table
tags_assoc = Table('tags_assoc', Base.metadata,
    Column('user_id', Integer, ForeignKey('users.id')),
    Column('tag_id', Integer, ForeignKey('tags.id'))
)
class User(Base):
    tags = relationship("Tag", secondary=tags_assoc, back_populates="users")

Параметры relationship()

Параметр	Описание
`back_populates`	Двунаправленная связь (явная)
`backref`	Двунаправленная связь (автоматическая)
`lazy`	Стратегия загрузки: select/joined/subquery/noload
`cascade`	Каскадные операции: "all, delete-orphan"
`uselist=False`	Связь 1:1 (скалярное значение вместо списка)
`secondary`	Ассоциативная таблица для M:N

Сессия: жизненный цикл

Сессия — рабочий интерфейс между объектами Python и базой данных. Управляет транзакциями и отслеживает изменения объектов.

Создание сессии

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///my_database.db')
Session = sessionmaker(bind=engine)
session = Session()

Состояния объекта в сессии

Состояние	Описание
Transient	Объект создан, не связан с сессией
Pending	Добавлен в сессию (add()), но не сохранён в БД
Persistent	Сохранён в БД, находится под управлением сессии
Detached	Был в сессии, но сессия закрыта или объект отсоединён
Deleted	Помечен на удаление, но ещё не закоммичен

CRUD операции

Create — создание записей

# Один объект
new_user = User(name="Alice", age=30)
session.add(new_user)
session.commit()

# Несколько объектов
session.add_all([
    User(name='Bob', age=22),
    User(name='David', age=27),
])
session.commit()

Read — чтение данных

# Все записи
users = session.query(User).all()

# По первичному ключу
user = session.query(User).get(1)

# С фильтром
users = session.query(User).filter(User.name == "Alice").all()

Update — обновление

user = session.query(User).get(1)
if user:
    user.age = 35
    session.commit()

Delete — удаление

user = session.query(User).get(1)
if user:
    session.delete(user)
    session.commit()

Запросы

Методы извлечения данных

Метод	Описание
`.all()`	Список всех объектов
`.first()`	Первый объект или None
`.one()`	Ровно один объект; исключение если 0 или 2+
`.one_or_none()`	Один или None; исключение если 2+
`.scalar()`	Первое поле первой строки

Фильтрация

from sqlalchemy import and_, or_, not_

# Простое условие
session.query(User).filter(User.age > 25).all()

# Функции: like, between, in_
session.query(User).filter(User.name.like('A%')).all()
session.query(User).filter(User.id.between(2, 4)).all()
session.query(User).filter(User.name.in_(["Alice", "Bob"])).all()

# Логические условия
session.query(User).filter(and_(User.age > 20, User.age < 30)).all()
session.query(User).filter(or_(User.age > 30, User.name == 'David')).all()

Сортировка, агрегации, группировка

from sqlalchemy import func, desc

# Сортировка
session.query(User).order_by(User.age).all()
session.query(User).order_by(desc(User.age)).all()

# Агрегации
total = session.query(func.count(User.id)).scalar()
avg_age = session.query(func.avg(User.age)).scalar()

# Группировка
session.query(User.name, func.count(User.id)).group_by(User.name).all()

# having() — фильтр после group_by
session.query(
    User.age, func.count(User.id).label('cnt')
).group_by(User.age).having(func.count(User.id) > 1).all()

JOIN

# Inner Join
session.query(User).join(Address).all()

# Left Outer Join
session.query(User).outerjoin(Address).all()

Подзапросы

avg_age_sub = session.query(func.avg(User.age).label('avg_age')).subquery()
users = session.query(User).filter(User.age > avg_age_sub).all()