Приклад створення складних реляційних схем за допомогою 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}")

---

Практичне застосування

Ця схема може бути корисна для реальних систем управління навчанням. Вона дозволяє гнучко працювати зі студентами, викладачами, курсами й завданнями. Більш того, додавання нових сутностей або полів у майбутньому не буде складним завдяки модульному підходу.

Не забувайте регулярно профілювати запити і оптимізувати схему, щоб уникнути проблем з продуктивністю на великих обсягах даних.