【備忘録】FLASK-migrate ... 外部キーとビュー表

# はじめに #
FLSK, FLASK-SQLAlchemy, FLASK-migrate 等々を使用したPythonアプリケーションで、外部キーを持つテーブルとビュー表を利用する場合のポイントを忘れぬうちにメモ。

# テーブル構成のイメージ #
    TABLE:employee
    +-----------------+ 
    | emp_id    [PK]  |
    | emp_name        |                    VIEW:employee_view
    | dept_id   [FK]--|---+                +-----------------------+
    +-----------------+   |                | employee.emp_id       |
                          |                | employee.emp_name     |
    TABLE:department      |                | department.dept_name  |
    +-----------------+   |                +-----------------------+
    | dept_id   [PK]<-|---+  
    | dept_name       |                  CREATE VIEW employee_view AS
    +-----------------+                    SELECT e.emp_id, e.emp_name, d.dept_name
                                           FROM employee e LEFT JOIN department d ON e.dept_id =d.dept_id;

# モデル定義 #
    class EmployeeModel(db.Model):
       __tablename__ = 'employee'

       emp_id   = db.Column(db.String(16), nullable=False, primary_key=True)
       emp_name = db.Column(db.String(80), nullable=False)
       dept_id  = db.Column(db.String(16), db.ForeignKey("departments.dept_id"), nullable=False)

    class EmployeeSchema(ma.SQLAlchemyAutoSchema):
        class Meta:
            model = EmployeeModel

    class DepartmentModel(db.Model):
        __tablename__ = 'department'

        dept_id   = db.Column(db.String(16), nullable=False, primary_key=True)
        dept_name = db.Column(db.String(80), nullable=False)

    class DepartmentSchema(ma.SQLAlchemyAutoSchema):
        class Meta:
            model = DepartmentModel

    class EmployeesViewModel(db.Model):
        __tablename__ = 'employee_view'

      emp_id    = db.Column(db.String(16), primary_key=True)
      emp_name  = db.Column(db.String(80))
      dept_name = db.Column(db.String(80))

    class EmployeesViewSchema(ma.SQLAlchemyAutoSchema):
        class Meta:
            model = EmployeesViewModel
【ポイント】
・ビュー表のモデル定義は実表と同様。

# マイグレーション #
FLASK-migrate はビュー表には対応していない。そのため、`flask db migrate`を実行して生成されるスクリプト（`～/migrations/versions/xxxxxxxxxxxx_.py`）に、`CREATE VIEW ～`を追加した後、`flask db upgrade`を実行しなければならない。

    :
    :
    from sqlalchemy import text
    :
    :

    # 従業員ビュー作成DML … upgradeから実行される
    create_view_dml = text("""
    CREATE VIEW employee_view AS
     SELECT e.emp_id, e.emp_name, d.dept_name 
     FROM employee e LEFT JOIN department d ON e.dept_id =d.dept_id;
    """)
    def upgrade():
        # ### commands auto generated by Alembic - please adjust! ###
        op.create_table('department',
        sa.Column('dept_id', sa.String(length=16), nullable=False),
        sa.Column('dept_name', sa.String(length=80), nullable=False),
        sa.PrimaryKeyConstraint('dept_id')
        )
        op.create_table('employee',
        sa.Column('emp_id', sa.String(length=16), nullable=False),
        sa.Column('emp_name', sa.String(length=80), nullable=False),
        sa.ForeignKeyConstraint(['dept_id'], ['department.dept_id'], ),
        sa.PrimaryKeyConstraint('emp_id')
        )
        op.execute(create_view_dml)
        # ### end Alembic commands ###
    :
    :
【ポイント】
・flask db migrate で CREATE VIEW は生成されない。
・upgradeスクリプトを修正した後、flask db upgrade を行う。