™️ 👩🏻‍🤝‍👨🏿 💪🏽 Create a database schema using SQLAlchemy 💻 ⛸️ 🍟

It has been said a lot that SQLAlchemy is one of the most popular libraries for creating database schemas. Today we will look at a simple example of creating a small data schema for a quote search application. We will use PostgreSQL as a DBMS.

We will use a declarative approach to defining models, since, in my opinion, it is simpler and more understandable than the classical approach based on mapper. Let's first sketch the er-diagram.

, , , .

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__

, .

After creating the data schema, you can expand the tables in different ways. To check that there are no inconsistencies, you can use the following lines, having previously created the database (example is 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.

Create a database schema using SQLAlchemy

More articles: