python - `alembic revision --autogenerate` 产生冗余外键迁移

Question

软件版本:alembic 1.0.5、SQLAlchemy 1.2.14、MySQL 5.7、Python 3.6.7

我正在尝试使用 alembic 来保持 MySQL 数据库模式和 Python ORM 表示同步。

我看到的问题是迁移总是有多余的删除和创建外键命令。似乎 autogenerate 看到的是不同的东西，但实际上它们是相同的。

关于命令的重复调用:

alembic revision --autogenerate 
alembic upgrade head

...将生成相同的放置和创建命令。

到 stdout 的日志记录显示类似(例如)的内容:

INFO  [alembic.autogenerate.compare] Detected removed foreign key (t1_id)(id) on table table_two
INFO  [alembic.autogenerate.compare] Detected added foreign key (t1_id)(id) on table test_fktdb.table_two

迁移脚本有:

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_table1', 'table_two', type_='foreignkey')
    op.create_foreign_key('fk_table1', 'table_two', 'table_one', ['t1_id'], ['id'], source_schema='test_fktdb', referent_schema='test_fktdb')
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_constraint('fk_table1', 'table_two', schema='test_fktdb', type_='foreignkey')
    op.create_foreign_key('fk_table1', 'table_two', 'table_one', ['t1_id'], ['id'])
    # ### end Alembic commands ###

这个问题可以重现，我做了一个最小的例子(https://github.com/sqlalchemy/alembic/files/2625781/FK_test.tar.gz 上的 tar.gz)。示例中的 ORM 是这样的:

[...import and bobs...]

class TableOne(Base):
    """Class representing a table with an id."""
    __tablename__ = "table_one"

    id = Column(UNSIGNED_INTEGER, nullable=False, autoincrement=True, primary_key=True)

    __table_args__ = (
        dict(mysql_engine='InnoDB'),
    )


class TableTwo(Base):
    """A table representing records with a foreign key link to table one."""
    __tablename__ = "table_two"

    id = Column(UNSIGNED_INTEGER, nullable=False, autoincrement=True, primary_key=True)
    t1_id = Column(UNSIGNED_INTEGER, nullable=False)

    __table_args__ = (
        ForeignKeyConstraint(["t1_id"], ["test_fktdb.table_one.id"], name="fk_table1"),
        dict(mysql_engine='InnoDB'),
    )

是否可以采取任何措施使 alembic“看到”数据库中的 FK 与 ORM 中的相同？例如，通过 env.py 应用一些配置？

我查看了这个问题并在 alembic GitHub 中发现了一些旧问题(参见 [1]、[2]、[3])。有解决方案的问题似乎与 postgres 数据库和公开模式有关。我不确定这是否适用于这种情况，因为我使用的是 MySQL；公共(public) postgres 模式的相关文档在这里:https://docs.sqlalchemy.org/en/latest/dialects/postgresql.html#remote-schema-table-introspection-and-postgresql-search-path

我现在已将我自己的问题添加到 alembic GitHub 存储库:https://github.com/sqlalchemy/alembic/issues/519

---

alembic 问题跟踪器中的已关闭问题，它们显示出类似的症状，但其解决方案不适用(据我所知):

[1] https://github.com/sqlalchemy/alembic/issues/444

[2] https://github.com/sqlalchemy/alembic/issues/398

[3] https://github.com/sqlalchemy/alembic/issues/293

Answer 1

所以，虽然这个 SO 问题很老，并且让我获得了 Tumbleweed 徽章，但我认为最好回答它并关闭它。我从 GitHub 上的软件包维护者 Mike Bayer 那里得到了很好的回答:

OK, so here is the thing. you are connecting with "test_fktdb" in your database URL as the default schema. which means, alembic is going to find your tables in that schema, and when it finds the foreign key, it will see the "schema_name" field in that FK as empty, because this is the default schema. So it doesn't match what you have in your metadata. Also you aren't adding "include_schemas=True" to the environment, so you will definitely not get reasonable results when your ORM models have "schema='test_fktdb'" in them.

there's two general worlds you can go into to fix this.

• easy one. take out "schema" from your tables/metadata/foreign keys entirely. then everything works in test_fktdb as the default and everything matches.

• hard one. you need to connect to a different database on your URL, then set up include_schemas=True in your envrionment, you probably also need a reasonable include_object() scheme so that it doesnt read in all the other databases, set up version_table_schema='test_fktdb', then that works too:

env.py:

SCHEMA_NAME = "NOT_test_fktdb"

    def include_object(object, name, type_, reflected, compare_to):
        if (type_ == "table"):
            return object.schema == "test_fktdb"

        else:
            return True

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            compare_type=True,
            compare_server_default=True,
            include_schemas=True,
            version_table_schema="test_schema",
            include_object=include_object
        )

       # ...

the "schema" logic necessarily has to rely heavily on this concept of "default" schema being a blank string, so when you mix up the default schema also being present it confuses things.

GitHub 上还有更多内容 https://github.com/sqlalchemy/alembic/issues/519 .

我发现简单的选项很管用，我做了以下更改:

# instead of [...]:
# declarative_base(metadata=sqlalchemy.MetaData(schema=test_fktdb.SCHEMA_NAME))
Base = sqlalchemy.ext.declarative.declarative_base()

# instead of [...]:
# ForeignKeyConstraint(["t1_id"], ["test_fktdb.table_one.id"], name="fk_table1"),
ForeignKeyConstraint(["t1_id"], ["table_one.id"], name="fk_table1"),