python-3.x - Flask-SQLAlchemy Alembic 迁移

Question

我正在尝试使用 Flask-SQLAlchemy 和 alembic 在 postgresDB 上进行数据库迁移。

模型.py

import datetime

from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()


class TestOne(db.Model):
    __tablename__ = 'test_one'
    __bind_key__ = 'tester'
    __table_args__ = {"schema": "schema1"}

    id = db.Column(db.Integer, primary_key=True)
    column1 = db.Column(db.String(50), unique=True, nullable=False)
    timestamp = db.Column(db.DateTime, default=datetime.datetime.utcnow)

    def __str__(self):
        return f"<TestOne {self.column1}>"


class TestTwo(db.Model):
    __tablename__ = 'test_two'
    __bind_key__ = 'tester'

    id = db.Column(db.Integer, primary_key=True)
    code = db.Column("code", db.String(50), index=True, nullable=False)
    f_date = db.Column(db.DateTime, nullable=False)
    t_date = db.Column(db.Date, nullable=False)
    modified = db.Column("modified", db.Boolean, index=True, nullable=False)

    __table_args__ = (
        db.Index(
            "ix_test_two_code_modified",
            code, modified,
            unique=True,
            postgresql_where=(modified.is_(True))
        ),
        {"schema": "public"},
    )

    def __str__(self):
        return f"<TestTwo {self.code}>"

数据库设置

def setup_database(app):
    from .models import db

    db.init_app(app)
    migrate.init_app(app, db, include_schema=True)
    manager = Manager(app)
    manager.add_command('db', MigrateCommand)

    with app.app_context():
        db.create_all(app=app, bind='tester')
        return app

并按照文档中指定的步骤进行操作

flask db init
flask db migrate -m "initial"
flask db upgrade

flask db migrate -m "initial migration"正在 migrartion>versions 目录中创建 initial.py 文件，并且在 postgresDB 中创建表

初始.py

"""initial

Revision ID: 65bc44e61e98
Revises: 
Create Date: 2020-08-11 23:17:08.664515

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = '65bc44e61e98'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('test_two',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('code', sa.String(length=50), nullable=False),
    sa.Column('f_date', sa.DateTime(), nullable=False),
    sa.Column('t_date', sa.Date(), nullable=False),
    sa.Column('modified', sa.Boolean(), nullable=False),
    sa.PrimaryKeyConstraint('id'),
    schema='public'
    )
    op.create_index(op.f('ix_public_test_two_code'), 'test_two', ['code'], unique=False, schema='public')
    op.create_index(op.f('ix_public_test_two_modified'), 'test_two', ['modified'], unique=False, schema='public')
    op.create_index('ix_test_two_code_modified', 'test_two', ['code', 'modified'], unique=True, schema='public', postgresql_where=sa.text('modified IS 1'))
    op.create_table('test_one',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('column1', sa.String(length=50), nullable=False),
    sa.Column('timestamp', sa.DateTime(), nullable=True),
    sa.PrimaryKeyConstraint('id'),
    sa.UniqueConstraint('column1'),
    schema='schema1'
    )
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('test_one', schema='schema1')
    op.drop_index('ix_test_two_code_modified', table_name='test_two', schema='public')
    op.drop_index(op.f('ix_public_test_two_modified'), table_name='test_two', schema='public')
    op.drop_index(op.f('ix_public_test_two_code'), table_name='test_two', schema='public')
    op.drop_table('test_two', schema='public')
    # ### end Alembic commands ###

但是烧瓶数据库升级失败并出现错误

    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) unknown database public
[SQL:
CREATE TABLE public.test_two (
        id INTEGER NOT NULL,
        code VARCHAR(50) NOT NULL,
        f_date DATETIME NOT NULL,
        t_date DATE NOT NULL,
        modified BOOLEAN NOT NULL,
        PRIMARY KEY (id),
        CHECK (modified IN (0, 1))
)

]
(Background on this error at: http://sqlalche.me/e/13/e3q8)

不确定为什么将架构视为数据库公开，甚至将 include_schema 参数添加到 True 以使用多个架构不确定是什么问题？

标签： python-3.xflaskflask-sqlalchemyalembicflask-migrate