Creating a database scheme in SQLAlchemy – InformTFB

Creating a database scheme in SQLAlchemy

Creating a database scheme in SQLAlchemy

Much has already been said that SQLAlchemy is one of the most popular libraries for creating database scheme. Today, let’s look at a simple example of creating a small data scheme for a quote search application. We will use PostgreSQL as the DBMS.

We will use a declarative approach to defining models, since, in my opinion, it is simpler and clearer than the classical approach based on mapper. Let’s first outline an er diagram.

The scheme is based on the fact that a quote can only have one topic. if you need multiple topics, you should create an intermediate table to model the many-to-many relationship.

Let’s build a scheme in SQLAlchemy according to the diagram. For convenience of queries to the database and manipulations with models, we will include Quote relationship in the table.

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}'

Let’s go through the code, some things may seem elementary, but you can skip them. It is worth noting that in the declarative approach, all table objects are inherited from Base. For each table, you can add its name in the database, as well as a comment to it using the built__tablename__-in and __tableargs__.

You can set different parameters for each of the table columns, just like in different DBMS systems. Foreign keys are set using the table name and the field that will be the foreign key. For convenience of operations with data, it is used relationship. It allows you to link table objects, not just individual fields, as happens when declaring only foreign keys. This parameter lazydefines how related objects are loaded when querying through relationships. Values joinedand subquery in fact, they do the same thing: they merge tables and return the result, but under the hood they are arranged differently, so there may be performance differences, since they are combined into tables in different ways.

The magic method __repr__actually determines what will be displayed when the table is printed out.

After creating a data scheme, you can expand tables in different ways. To check if there are no contradictions, you can use the following lines by first creating a database (an example is given for postgresql).

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

But it is much more convenient to use tools for managing migrations, for example, alembic. In fact, it allows you to move the database from one consistent state to another.

Anderson
Anderson
Web site editor and tester.

Leave a Reply

Your email address will not be published. Required fields are marked *