皆さん、こんにちは！
前回、先にalembicコマンドのまとめ記事を書いたのですが、今回は続いてSQLAlchemyのモデルについて書きます。
fastapiというpythonのframworkを勉強していたのですが、そのテンプレート内のalembicディレクトリを使用してます。
GitHub - fastapi/full-stack-fastapi-template: Full stack, modern web application template. Using FastAPI, React, SQLModel, PostgreSQL, Docker, GitHub Actions, automatic HTTPS and more.
Full stack, modern web application template. Using FastAPI, React, SQLModel, PostgreSQL, Docker, GitHub Actions, automatic HTTPS and more. - fastapi/full-stack-...
github.com
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でモデルを書いてみる
今回テスト用に用意したのは、以下３つのモデルです。
- 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/appalembicには、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 tablesDBを確認するとできているようです。
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 | |
+-------------+---------------+------+-----+-------------------+----------------+
ではでは！
