Пример создания сложных реляционных схем с SQLAlchemy

Шаг 1: постановка задачи и анализ данных

Давайте создадим реляционную схему для системы управления онлайн-курсами. Система будет включать следующие сущности:

1. Пользователи (Users):
   • Студенты и преподаватели.
2. Курсы (Courses):
   • Каждый курс может иметь одного или нескольких преподавателей.
   • У курса есть список студентов, которые на него записаны.
3. Задания (Assignments):
   • Каждый курс включает задания.
   • У заданий есть статус выполнения для каждого студента.
4. Отзывы (Reviews):
   • Студенты могут оставлять отзывы о курсах.
5. Сертификаты (Certificates):
   • Выдаются студентам за успешное завершение курсов.

Всё это нужно связать реляционно. Ну что, готовы? Погнали!

---

Шаг 2: реализация сложной реляционной схемы

Для начала определяем базу данных и базовую модель

from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, relationship, sessionmaker
from sqlalchemy import Column, Integer, String, ForeignKey, Table

# Создаём базовый класс для наших моделей
Base = declarative_base()

# Настраиваем подключение к базе данных
engine = create_engine("sqlite:///school.db", echo=True)
SessionLocal = sessionmaker(bind=engine)

Мы учтём, что пользователь может быть как студентом, так и преподавателем. Сделаем это при помощи поля role.

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String, nullable=False)
    email = Column(String, unique=True, nullable=False)
    role = Column(String, nullable=False)  # student или teacher

    # Связь с таблицей «Courses» как преподаватель
    teaching_courses = relationship("Course", back_populates="instructor")

    # Связь с таблицей «Enrollments» как студент
    enrolled_courses = relationship("Enrollment", back_populates="student")

Курсы (Courses)

Каждый курс может быть привязан к преподавателю и студентам через связи ForeignKey и ManyToMany.

class Course(Base):
    __tablename__ = 'courses'

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)

    # Внешний ключ для связи с преподавателем
    instructor_id = Column(Integer, ForeignKey('users.id'))

    # Связь с преподавателем (один преподаватель -> много курсов)
    instructor = relationship("User", back_populates="teaching_courses")

    # Связь с таблицей «Assignments» для заданий
    assignments = relationship("Assignment", back_populates="course")

    # Связь с таблицей «Enrollments» для студентов
    enrollments = relationship("Enrollment", back_populates="course")

Переходная таблица для связи студентов и курсов

Поскольку студент может быть записан на несколько курсов, нам нужна переходная таблица, чтобы описать эту связь.

class Enrollment(Base):
    __tablename__ = 'enrollments'

    id = Column(Integer, primary_key=True, index=True)
    course_id = Column(Integer, ForeignKey('courses.id'))
    student_id = Column(Integer, ForeignKey('users.id'))

    # Связь с курсом
    course = relationship("Course", back_populates="enrollments")

    # Связь с пользователем
    student = relationship("User", back_populates="enrolled_courses")

Задания (Assignments)

Теперь добавим задания, которые принадлежат конкретным курсам.

class Assignment(Base):
    __tablename__ = 'assignments'

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String, nullable=False)
    course_id = Column(Integer, ForeignKey('courses.id'))

    # Связь с курсом
    course = relationship("Course", back_populates="assignments")

Отзывы (Reviews)

Добавим возможность оставлять отзывы о курсе.

class Review(Base):
    __tablename__ = 'reviews'

    id = Column(Integer, primary_key=True, index=True)
    content = Column(String, nullable=False)
    course_id = Column(Integer, ForeignKey('courses.id'))
    student_id = Column(Integer, ForeignKey('users.id'))

    # Связь с курсом (многие отзывы -> один курс)
    course = relationship("Course")

    # Связь с пользователем (многие отзывы -> один студент)
    student = relationship("User")

Сертификаты (Certificates)

И наконец, сертификаты для студентов, которые завершили курсы.

class Certificate(Base):
    __tablename__ = 'certificates'

    id = Column(Integer, primary_key=True, index=True)
    student_id = Column(Integer, ForeignKey('users.id'))
    course_id = Column(Integer, ForeignKey('courses.id'))
    issue_date = Column(String)

    # Связь с пользователем
    student = relationship("User")

    # Связь с курсом
    course = relationship("Course")

---

Шаг 3: создание базы данных

# Создаём все таблицы в базе данных
Base.metadata.create_all(bind=engine)

Запустив это, мы добавим нашу схему в базу данных. Она готова для использования!

---

Шаг 4: наполнение базы данными (пример)

Теперь добавим тестовые записи:

# Создаём сессию
session = SessionLocal()

# Преподаватель
teacher = User(name="Джон Сноу", email="john.snow@example.com", role="teacher")

# Курс
course = Course(title="Основы программирования", instructor=teacher)

# Студент
student = User(name="Арья Старк", email="arya.stark@example.com", role="student")
enrollment = Enrollment(student=student, course=course)

# Добавляем в сессию
session.add_all([teacher, course, student, enrollment])
session.commit()

print("Данные успешно добавлены в базу!")

---

Шаг 5: пример сложного запроса

Теперь вытащим всех студентов, записанных на конкретный курс:

course_title = "Основы программирования"
students = session.query(User).join(Enrollment).join(Course).filter(Course.title == course_title).all()

for student in students:
    print(f"Студент: {student.name}, Email: {student.email}")

---

Практическое применение

Эта схема может быть полезна для реальных систем управления обучением. Она позволяет гибко работать со студентами, преподавателями, курсами и заданиями. Более того, добавление новых сущностей или полей в будущем не составит труда благодаря модульному подходу.

Не забывайте регулярно профилировать запросы и оптимизировать схему, чтобы избежать проблем с производительностью на больших объёмах данных.