0

Against better judgment, I decided to rename the primary column in my databases from id to key, as I didn't want to clash with Python's id function.

I managed to use alembic to rename the column by using batch migrations, as I'm using an SQLite backend.

Now when adding a new table to my model, alembic manages to detect the new table and then crashes with the following:

sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'tests.id' on table 'runs': table 'tests' has no column named 'id'

I have a table named tests and a table named runs which are linked via a foreign key:

class Base():
    key = Column(Integer, primary_key=True)

BASE = declarative_base(cls=Base)

class Test(BASE):
     runs = relationship("Run", back_populates="test",
                         cascade="all, delete, delete-orphan")

class Run(BASE):
     test_id = Column(Integer, ForeignKey("tests.key"))
     test = relationship("Test", back_populates="runs")

It looks like alembic is trying to recreate all of the migrations when trying to autogenerate. Or at least the table creation.

Am I doing something wrong? Or is this a bug in alembic?

Following is the full command output with my paths redacted.

$ alembic -n testing revision --autogenerate -m "Add sessions table"
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.autogenerate.compare] Detected added table 'sessions'
Traceback (most recent call last):
  File "bin/alembic", line 10, in <module>
    sys.exit(main())
  File "lib64/python3.6/site-packages/alembic/config.py", line 540, in main
    CommandLine(prog=prog).main(argv=argv)
  File "lib64/python3.6/site-packages/alembic/config.py", line 534, in main
    self.run_cmd(cfg, options)
  File "lib64/python3.6/site-packages/alembic/config.py", line 514, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "lib64/python3.6/site-packages/alembic/command.py", line 197, in revision
    script_directory.run_env()
  File "lib64/python3.6/site-packages/alembic/script/base.py", line 475, in run_env
    util.load_python_file(self.dir, "env.py")
  File "lib64/python3.6/site-packages/alembic/util/pyfiles.py", line 90, in load_python_file
    module = load_module_py(module_id, path)
  File "lib64/python3.6/site-packages/alembic/util/compat.py", line 177, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 71, in <module>
    run_migrations_online()
  File "alembic/env.py", line 65, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "lib64/python3.6/site-packages/alembic/runtime/environment.py", line 839, in run_migrations
    self.get_context().run_migrations(**kw)
  File "lib64/python3.6/site-packages/alembic/runtime/migration.py", line 351, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "lib64/python3.6/site-packages/alembic/command.py", line 173, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "lib64/python3.6/site-packages/alembic/autogenerate/api.py", line 433, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "lib64/python3.6/site-packages/alembic/autogenerate/api.py", line 473, in _run_environment
    autogen_context, migration_script
  File "lib64/python3.6/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "lib64/python3.6/site-packages/alembic/autogenerate/compare.py", line 51, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "lib64/python3.6/site-packages/alembic/util/langhelpers.py", line 303, in go
    fn(*arg, **kw)
  File "lib64/python3.6/site-packages/alembic/autogenerate/compare.py", line 83, in _autogen_for_tables
    autogen_context,
  File "lib64/python3.6/site-packages/alembic/autogenerate/compare.py", line 225, in _compare_tables
    metadata_table,
  File "lib64/python3.6/site-packages/alembic/util/langhelpers.py", line 303, in go
    fn(*arg, **kw)
  File "lib64/python3.6/site-packages/alembic/autogenerate/compare.py", line 994, in _compare_foreign_keys
    for fk in conn_fks
  File "lib64/python3.6/site-packages/alembic/autogenerate/compare.py", line 994, in <genexpr>
    for fk in conn_fks
  File "lib64/python3.6/site-packages/alembic/autogenerate/compare.py", line 400, in __init__
    ) = _fk_spec(const)
  File "lib64/python3.6/site-packages/alembic/util/sqla_compat.py", line 77, in _fk_spec
    target_schema = constraint.elements[0].column.table.schema
  File "lib64/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 855, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "lib64/python3.6/site-packages/sqlalchemy/sql/schema.py", line 2039, in column
    colname,
sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'tests.id' on table 'runs': table 'tests' has no column named 'id'
Tsvi M
  • 334
  • 1
  • 12

1 Answers1

0

The issue was in the database itself. My previous migration script didn't update the ForeignKey constraint by dropping the constraint and recreating on the new key.

And since SQLite by default doesn't respect foreign keys The upgrade passed, but the database still had the old constraint in it.

By calling the drop_constraint and the create_foreign_key in the upgrade script and using the method described here to specify the unnamed constraint, I managed to run the upgrade script correctly on my test database and run autogenerate correctly.

Subsequentially, I added a check to foreign keys as described in the SQLAlchemy docs, so next time such a change would cause the upgrade to fail if the foreign keys don't match.

Tsvi M
  • 334
  • 1
  • 12