Alembic migration doesn't generate UUID correctly

Question

In fact surely it does, but I'm certainly doing something wrong.

I've got a database where I create a FK on a table and in a future migration, it's dropped.

So the create statement of the table is:

CREATE TABLE this_table (
    pk INTEGER NOT NULL,
    some_other_table_pk INTEGER,
    CONSTRAINT "fk_436d3a57-0ae2-5668-937d-8cbc82d15172" FOREIGN KEY(some_other_table_pk) REFERENCES some_other_table (pk)

Then I drop the constraint and alembic auto generates the following batch operation:

def upgrade():
    with op.batch_alter_table('this_table', schema=None) as batch_op:
        # how does it know here which fk to drop? there are more than one???
        batch_op.drop_constraint(None, type_='foreignkey')
        batch_op.drop_column('some_other_table_pk')

def downgrade():
    with op.batch_alter_table('this_table', schema=None) as batch_op:
        batch_op.add_column(sa.Column('some_other_table_pk', sa.INTEGER(), nullable=True))
        batch_op.create_foreign_key(None, 'some_other_table_pk', ['some_other_table'], ['pk'])

Which leads to an error on upgrade head:

KeyError: 'fk_ad156c6d-7a24-5d8e-868e-2fb00c2cb1c9'

so clearly it didn't regenerate the same key?!? The key doesn't match any fk on this table... But how should it know which key to generate? drop_constraint didn't get any info about which fk to drop?

my MetaDataConventions:

def fk_guid(constraint, table):
    str_tokens = [
        table.name,
    ] + [
        element.parent.name for element in constraint.elements
    ] + [
        element.target_fullname for element in constraint.elements
    ]
    guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens))
    return str(guid)

convention = {
  "fk_guid": fk_guid,
  "ix": "ix_%(column_0_label)s",
  "uq": "uq_%(table_name)s_%(column_0_name)s",
  "ck": "ck_%(table_name)s_%(column_0_name)s",
  "fk": "fk_%(fk_guid)s",
  "pk": "pk_%(table_name)s",
}

metadata = MetaData(naming_convention=convention)

Base = declarative_base(metadata=metadata)

and in env.py:

# ...
target_metadata = models.Base.metadata
# ...
def run_migrations_offline():
    url = getUrl()
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        render_as_batch=True,
        dialect_opts={"paramstyle": "named"},
        compare_type=True
    )

    with context.begin_transaction():
        context.run_migrations()

def run_migrations_online():
    alembic_config = config.get_section(config.config_ini_section)

    connectable = create_engine(getUrl())

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            render_as_batch=True,
            dialect_opts={"paramstyle": "named"},
            compare_type=True
        )

        with context.begin_transaction():
            context.run_migrations()

Database is a sqlite3 file.

start of edit

So I tried to manually pass in the name of the constraint to drop:

def upgrade():
    with op.batch_alter_table('this_table', schema=None) as batch_op:
        batch_op.drop_constraint('fk_436d3a57-0ae2-5668-937d-8cbc82d15172', type_='foreignkey')
        batch_op.drop_column('some_other_table_pk')

And now it resulted in a key error but the key should have been present in the DB?!?

KeyError: 'fk_436d3a57-0ae2-5668-937d-8cbc82d15172'

end of edit

start of edit2

OK so I played a bit in the sandbox, and it seems that it's user defined token keys which are not working on upgrade if it's a drop_constraint:

fk_guid in the case below is only called if the constraint is created

convention = {
  "fk_guid": some_function,
  "ix": "ix_%(column_0_label)s",
  "uq": "uq_%(table_name)s_%(column_0_name)s",
  "ck": "ck_%(table_name)s_%(column_0_name)s",
  "fk": "fk_%(fk_guid)s",
#  "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
  "pk": "pk_%(table_name)s",
}

And gives the following migration code for creation and dropping:

sa.ForeignKeyConstraint(['table_b'], ['table_b.id'], name=op.f('fk_9d68bd30-b17a-565a-aee7-7bd21e90672e')),

# But for dropping, it's not generated:
batch_op.drop_constraint(None, type_='foreignkey')

But strangely enough, some_function is getting called on alembic upgrade head but not with the same constraints as on the creation call:

def fk_guid(constraint, table):
    str_tokens = [
        table.name,
    ] + [
        element.parent.name for element in constraint.elements
    ] + [
        element.target_fullname for element in constraint.elements
    ]

    print(f'string_tokens: {str_tokens}')

    guid = uuid.uuid5(uuid.NAMESPACE_OID, "_".join(str_tokens))
    return str(guid)

def some_function(*params):
    print(f'params: {params}')
    return fk_guid(*params)

Output:

string_tokens: ['table_a']

Changing the naming convention of foreign key to standard

convention = {
  "fk_guid": some_function,
  "ix": "ix_%(column_0_label)s",
  "uq": "uq_%(table_name)s_%(column_0_name)s",
  "ck": "ck_%(table_name)s_%(column_0_name)s",
#  "fk": "fk_%(fk_guid)s",
  "fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
  "pk": "pk_%(table_name)s",
}

Gives creation and dropping as wanted:

# creation:
sa.ForeignKeyConstraint(['table_b'], ['table_b.id'], name=op.f('fk_table_a_table_b_table_b')),

# dropping:
batch_op.drop_constraint('fk_table_a_table_b_table_b', type_='foreignkey')

end of edit2

start of edit3

There's a thread on github: https://github.com/sqlalchemy/alembic/issues/713

end of edit3