SQLAlchemyでのモデルの作成

Web開発

皆さん、こんにちは!

前回、先にalembicコマンドのまとめ記事を書いたのですが、今回は続いてSQLAlchemyのモデルについて書きます。

fastapiというpythonのframworkを勉強していたのですが、そのテンプレート内のalembicディレクトリを使用してます。

tiangolo/full-stack-fastapi-postgresql
Full stack, modern web application generator. Using FastAPI, PostgreSQL as database, Docker, automatic HTTPS and more. - tiangolo/full-stack-fastapi-postgresql

SQLAlchemyとは?

まずそもそもSQLAlchemyとは、というところからですが。

python用のORM(Object-relational mapping)です。

作業前の準備

alembicでマイグレーションを実行するので、pipで必要なpackageをインストールする。

今回はMySQLをDBとして使用するので、mysqlclientも入れてます。

# pip install alembic
...
Installing collected packages: Mako, SQLAlchemy, python-editor, alembic
Successfully installed Mako-1.1.3 SQLAlchemy-1.3.19 alembic-1.4.2 python-editor-1.0.4
# pip install mysqlclient
...
Installing collected packages: mysqlclient
    Running setup.py install for mysqlclient ... done
Successfully installed mysqlclient-2.0.1

実際にSQLAlchemyでモデルを書いてみる

今回テスト用に用意したのは、以下3つのモデルです。

  • company.py
  • user.py
  • item.py

Companyモデル

from typing import TYPE_CHECKING

from sqlalchemy import (
    Boolean, Column, Integer, String,
    DateTime, func
    )
from sqlalchemy.orm import relationship

from app.db.base_class import Base

if TYPE_CHECKING:
    from .user import User  # noqa: F401


class Company(Base):
    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(100), index=True)
    is_active = Column(Boolean(), default=True)
    created_at = Column(
        DateTime,
        server_default=func.now()
        )
    updated_at = Column(
        DateTime,
        server_default=func.now(),
        onupdate=func.now()
        )

    users = relationship("User", back_populates="company")

Userモデル

from typing import TYPE_CHECKING

from sqlalchemy import (
    Boolean, Column, Integer, String,
    DateTime, func, ForeignKey
    )
from sqlalchemy.orm import relationship

from app.db.base_class import Base

if TYPE_CHECKING:
    from .item import Item  # noqa: F401
    from .company import Company  # noqa: F401


class User(Base):
    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(
        Integer,
        ForeignKey("company.id"),
        nullable=False
        )
    full_name = Column(String(100), index=True)
    email = Column(String(100), unique=True, index=True, nullable=False)
    hashed_password = Column(String(100), nullable=False)
    is_active = Column(Boolean(), default=True)
    is_superuser = Column(Boolean(), default=False)
    created_at = Column(
        DateTime,
        server_default=func.now()
        )
    updated_at = Column(
        DateTime,
        server_default=func.now(),
        onupdate=func.now()
        )

    company = relationship("Company", back_populates="users")
    items = relationship("Item", back_populates="owner")

Itemモデル

from typing import TYPE_CHECKING

from sqlalchemy import (
    Column, ForeignKey, Integer, String,
    DateTime, func
    )
from sqlalchemy.orm import relationship

from app.db.base_class import Base

if TYPE_CHECKING:
    from .user import User  # noqa: F401


class Item(Base):
    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(100), index=True)
    description = Column(String(1000))
    owner_id = Column(Integer, ForeignKey("user.id"))
    created_at = Column(
        DateTime,
        server_default=func.now()
        )
    updated_at = Column(
        DateTime,
        server_default=func.now(),
        onupdate=func.now()
        )

    owner = relationship("User", back_populates="items")

alembicでマイグレーションスクリプトを作成する

※以下に入る前に、私の環境では開発ディレクトリまでのパスを追加しないとalembicコマンドをたたいた際に、appディレクトリが見つからず、importできないとうエラーがでました。

export PYTHONPATH=${PYTHONPATH}:<YOUR_PATH>/backend/app

alembicには、autogenerateオプションというものがあり、これを使用するとモデルと既存のDB内容の差分を読み取りマイグレーションスクリプトを自動生成してくれます。

※このautogenerateも、最初に試した際にモデルの内容が反映されてないなど、少々クセがあるので。それもまた別記事で書きます。

# alembic revision --autogenerate -m "Add init tables"
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.autogenerate.compare] Detected added table 'company'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_company_id' on '['id']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_company_name' on '['name']'
INFO  [alembic.autogenerate.compare] Detected added table 'user'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_email' on '['email']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_full_name' on '['full_name']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_user_id' on '['id']'
INFO  [alembic.autogenerate.compare] Detected added table 'item'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_item_id' on '['id']'
INFO  [alembic.autogenerate.compare] Detected added index 'ix_item_title' on '['title']'
  Generating <FILE_PATH>/alembic/versions/fb1001010ac8_add_init_tables.py ...  done

作成されたものがこちら。

"""Add init tables
Revision ID: fb1001010ac8
Revises: 
Create Date: 2020-XX-XX XX:XX:XX.XXXXXX
"""
from alembic import op
import sqlalchemy as sa
# revision identifiers, used by Alembic.
revision = 'fb1001010ac8'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table(
        'company',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(length=100), nullable=True),
        sa.Column('is_active', sa.Boolean(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_company_id'), 'company', ['id'], unique=False)
    op.create_index(op.f('ix_company_name'), 'company', ['name'], unique=False)
    op.create_table(
        'user',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('company_id', sa.Integer(), nullable=False),
        sa.Column('full_name', sa.String(length=100), nullable=True),
        sa.Column('email', sa.String(length=100), nullable=False),
        sa.Column('hashed_password', sa.String(length=100), nullable=False),
        sa.Column('is_active', sa.Boolean(), nullable=True),
        sa.Column('is_superuser', sa.Boolean(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['company_id'], ['company.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_user_email'), 'user', ['email'], unique=True)
    op.create_index(op.f('ix_user_full_name'), 'user', ['full_name'], unique=False)
    op.create_index(op.f('ix_user_id'), 'user', ['id'], unique=False)
    op.create_table(
        'item',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('title', sa.String(length=100), nullable=True),
        sa.Column('description', sa.String(length=1000), nullable=True),
        sa.Column('owner_id', sa.Integer(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.text('now()'), nullable=True),
        sa.ForeignKeyConstraint(['owner_id'], ['user.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_item_id'), 'item', ['id'], unique=False)
    op.create_index(op.f('ix_item_title'), 'item', ['title'], unique=False)
    # ### end Alembic commands ###
def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f('ix_item_title'), table_name='item')
    op.drop_index(op.f('ix_item_id'), table_name='item')
    op.drop_table('item')
    op.drop_index(op.f('ix_user_id'), table_name='user')
    op.drop_index(op.f('ix_user_full_name'), table_name='user')
    op.drop_index(op.f('ix_user_email'), table_name='user')
    op.drop_table('user')
    op.drop_index(op.f('ix_company_name'), table_name='company')
    op.drop_index(op.f('ix_company_id'), table_name='company')
    op.drop_table('company')
    # ### end Alembic commands ###

マイグレーションファイルが作成されたので、マイグレーションコマンドを実行します。

# alembic upgrade head
INFO  [alembic.runtime.migration] Context impl MySQLImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade  -> fb1001010ac8, Add init tables

DBを確認するとできているようです。

mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| alembic_version    |
| company            |
| item               |
| user               |
+--------------------+
mysql> desc company;
+------------+--------------+------+-----+-------------------+----------------+
| Field      | Type         | Null | Key | Default           | Extra          |
+------------+--------------+------+-----+-------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment |
| name       | varchar(100) | YES  | MUL | NULL              |                |
| is_active  | tinyint(1)   | YES  |     | NULL              |                |
| created_at | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
| updated_at | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
+------------+--------------+------+-----+-------------------+----------------+
mysql> desc user;
+-----------------+--------------+------+-----+-------------------+----------------+
| Field           | Type         | Null | Key | Default           | Extra          |
+-----------------+--------------+------+-----+-------------------+----------------+
| id              | int(11)      | NO   | PRI | NULL              | auto_increment |
| company_id      | int(11)      | NO   | MUL | NULL              |                |
| full_name       | varchar(100) | YES  | MUL | NULL              |                |
| email           | varchar(100) | NO   | UNI | NULL              |                |
| hashed_password | varchar(100) | NO   |     | NULL              |                |
| is_active       | tinyint(1)   | YES  |     | NULL              |                |
| is_superuser    | tinyint(1)   | YES  |     | NULL              |                |
| created_at      | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
| updated_at      | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
+-----------------+--------------+------+-----+-------------------+----------------+
mysql> desc item
;
+-------------+---------------+------+-----+-------------------+----------------+
| Field       | Type          | Null | Key | Default           | Extra          |
+-------------+---------------+------+-----+-------------------+----------------+
| id          | int(11)       | NO   | PRI | NULL              | auto_increment |
| title       | varchar(100)  | YES  | MUL | NULL              |                |
| description | varchar(1000) | YES  |     | NULL              |                |
| owner_id    | int(11)       | YES  | MUL | NULL              |                |
| created_at  | datetime      | YES  |     | CURRENT_TIMESTAMP |                |
| updated_at  | datetime      | YES  |     | CURRENT_TIMESTAMP |                |
+-------------+---------------+------+-----+-------------------+----------------+

ではでは!

タイトルとURLをコピーしました