Alembic: NoSuchTableError for autogenerate migration with existing tables outside of the application

[zpsjs]

Hi there,
after I added a new schema including tables outside of my existing application to my database I ran the following command:

alembic revision --autogenerate

As a result I got the error:

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
Traceback (most recent call last):
  File "/home/.../anaconda3/bin/alembic", line 8, in <module>
    sys.exit(main())
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/config.py", line 575, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/config.py", line 569, in main
    self.run_cmd(cfg, options)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/config.py", line 549, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/command.py", line 214, in revision
    script_directory.run_env()
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/util/compat.py", line 173, in load_module_py
    spec.loader.exec_module(module)
  File "<frozen importlib._bootstrap_external>", line 728, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "alembic/env.py", line 97, in <module>
    run_migrations_online()
  File "alembic/env.py", line 91, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/runtime/migration.py", line 507, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/command.py", line 190, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 442, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 482, in _run_environment
    autogen_context, migration_script
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 25, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 51, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 303, in go
    fn(*arg, **kw)
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 83, in _autogen_for_tables
    autogen_context,
  File "/home/.../anaconda3/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 164, in _compare_tables
    inspector.reflecttable(t, None)
  File "/home/.../anaconda3/lib/python3.7/site-packages/sqlalchemy/engine/reflection.py", line 655, in reflecttable
    raise exc.NoSuchTableError(table.name)
sqlalchemy.exc.NoSuchTableError: second_table

I'm already using the include_object method as described in the documentation, see my code below:

from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
from sqlalchemy import Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    test_url = Column(String, unique=True)


# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = Base.metadata

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.


def include_object(object, name, type_, reflected, compare_to):
    if (type_ == "table" and reflected and compare_to is None):
        return False
    else:
        return True

def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.

    """
    connectable = engine_from_config(
        config.get_section(config.config_ini_section),
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(
            connection=connection,
            target_metadata=target_metadata,
            include_schemas=True,
            include_object=include_object,
        )

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


run_migrations_online()

Shouldn't this work in general?

I'm using the following versions of sqlalchemy / alembic:
sqlalchemy: 1.3.12
alembic: 1.3.2

Thanks a lot!

[zzzeek]

this is an issue that probably has to do with schema name confusion, can you specify:

1. what database platform this is?
2. Does the create_engine() URL you have indicate a particular database name that will be the default ?
3. send the whole stack trace for the error

Some combinations that have trouble in this area are: 1. postgresql with a custom search_path setting that confuses things 2. MySQL with a URL that has no database name 3. SQL Server where there are some weird things going on with the whole "dbo." prefix thing 4. Oracle where things with synonyms are going on

need more specifics to know. thanks

[zpsjs]

yes, of course:

1. database: Postgresql v. 10.11
2. database URL: postgresql://user:[email protected]:5432/zdata (zdata is the tested name of the database)
3. changed my first post with the whole stack trace

To describe it a little bit more:
I ran the first migration, got the table 'test' in the public schema. Then I added a second schema with the table 'second_table' directly on the database without alembic. After that I ran the migration again and the error occurred.

[zzzeek]

are you changing the value of the PostgreSQL "search_path" variable and/or what is the setting for this variable in your setup?

[zzzeek]

also can you turn on "echo=True" for your create_engine? basically this is a mismatch between the query that gets the names of all the tables, and the query that tries to get the table itself.

looking at the source another possibility is that "second_table" has no columns? is that possible here?

[zpsjs]

search_path is default:

show search_path;

"$user", public

[zpsjs]

second_table has no columns, that is why the error occurs.
its working now!
thank you very much!

[zzzeek]

ok you're good? great.