Много уже говорилось о том, что SQLAlchemy - одна из самых популярных библиотек для создания схем баз данных. Сегодня рассмотрим несложный пример по созданию небольшой схемы данных для приложения по поиску цитат. В качестве СУБД будем использовать PostgreSQL.

Подход к определению моделей будем использовать декларативный, так как, на мой взгляд, он проще и понятнее классического подхода, основанного на mapper. Предварительно набросаем er-диаграмму.

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

Построим схему в SQLAlchemy в соответствии с диаграммой. Для удобства запросов к базе и манипуляций с моделями включим в таблицу Quote relationship.

from sqlalchemy import Column, ForeignKey, Integer, String, Text, Date, DateTime
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Topic(Base):

    __tablename__ = 'topic'
    __tableargs__ = {
        'comment': 'Темы цитат'
    }

    topic_id = Column(
        Integer,
        nullable=False,
        unique=True,
        primary_key=True,
        autoincrement=True
    )
    name = Column(String(128), comment='Наименование темы')
    description = Column(Text, comment='Описание темы')

    def __repr__(self):
        return f'{self.topic_id} {self.name} {self.description}'


class Author(Base):

    __tablename__ = 'author'
    __tableargs__ = {
        'comment': 'Авторы цитат'
    }

    author_id = Column(
        Integer,
        nullable=False,
        unique=True,
        primary_key=True,
        autoincrement=True
    )
    name = Column(String(128), comment='Имя автора')
    birth_date = Column(Date, comment='Дата рождения автора')
    country = Column(String(128), comment='Страна рождения автора')

    def __repr__(self):
        return f'{self.author_id} {self.name} {self.birth_date} {self.country}'


class Quote(Base):

    __tablename__ = 'quote'
    __tableargs__ = {
        'comment': 'Цитаты'
    }

    quote_id = Column(
        Integer,
        nullable=False,
        unique=True,
        primary_key=True,
        autoincrement=True
    )
    text = Column(Text, comment='Текст цитаты')
    created_at = Column(DateTime, comment='Дата и время создания цитаты')
    author_id = Column(Integer,  ForeignKey('author.author_id'), comment='Автор цитаты')
    topic_id = Column(Integer, ForeignKey('topic.topic_id'), comment='Тема цитаты')
    author = relationship('Author', backref='quote_author', lazy='subquery')
    topic = relationship('Topic', backref='quote_topic', lazy='subquery')

    def __repr__(self):
        return f'{self.text} {self.created_at} {self.author_id} {self.topic_id}'

Пройдемся по коду, некоторые вещи могут показаться элементарными, можете их пропустить. Стоит отметить, что при декларативном подходе все объекты таблиц наследуются от Base . Для каждой таблицы есть возможность добавить ее название в базе данных, а так же комментарий к ней с помощью встроенных __tablename__ и __tableargs__ .

Для каждого из столбцов таблицы есть возможность задать различные параметры, как и в различных СУБД. Внешние ключи задаются через название таблицы и поле, которое будет являться внешним ключом. Для удобства операций с данными используется relationship. Он позволяет связать объекты таблиц, а не только отдельные поля, как происходит при объявлении только внешних ключей. Параметр lazy определяет, как связанные объекты загружаются при запросе через отношения. Значения joined и subquery фактически делают одно и то же: объединяют таблицы и возвращают результат, но под капотом устроены по-разному, поэтому могут быть различия в производительности, поскольку они по-разному объединяются в таблицы.

Магический метод __repr__ фактически определяет, что будет выведено на экран при распечатке таблицы.

После создания схемы данных, развернуть таблицы можно разными способами. Для проверки отсутствия противоречий можно использовать следующие строки, предварительно создав базу данных (пример приведен для postgresql).

engine = create_engine('postgresql://user:password@host:port/db_name')
Base.metadata.create_all(engine)

Но намного удобнее использовать инструменты для управления миграциями, например, alembic. Фактически он позволяет переводить базу данных из одного согласованного состояния в другое.