Autogenerate fails on functional index with sqlite

Hello there,

I have a following table:

from sqlalchemy import Column, Integer, String, Index, func,
from sqlalchemy.ext.declarative import declarative_base

class Base(object):
    id = Column(Integer, primary_key=True)

    def __repr__(self):
        return f'{self.__class__.__name__}({self.id})'

Base = declarative_base(cls=Base)

class Supplier(Base):
    __tablename__ = "suppliers"
    id = Column(String(length=20), primary_key=True)  # noqa: A003
    name = Column(String(), index=True)

with functional index defined as follows:

Index(
    "supplier_name_idx",
    func.replace(func.replace(func.replace(func.lower(Supplier.name), ".", ""), " ", ""), ",", ""),
)

Now, if try to run alembic revision --autogenerate, it throws the following warning for postgres:

/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/util/messaging.py:70: UserWarning: autogenerate skipping functional index supplier_name_idx; not supported by SQLAlchemy reflection
  warnings.warn(msg)

However, for sqlite it throws an error:

Traceback (most recent call last):
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/bin/alembic", line 11, in <module>
    load_entry_point('alembic==1.0.5', 'console_scripts', 'alembic')()
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/config.py", line 503, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/config.py", line 497, in main
    self.run_cmd(cfg, options)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/config.py", line 480, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/command.py", line 176, in revision
    script_directory.run_env()
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/script/base.py", line 427, in run_env
    util.load_python_file(self.dir, 'env.py')
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/util/pyfiles.py", line 81, in load_python_file
    module = load_module_py(module_id, path)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/util/compat.py", line 145, 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 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 "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/runtime/environment.py", line 836, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/runtime/migration.py", line 321, in run_migrations
    for step in self._migrations_fn(heads, self):
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/command.py", line 156, in retrieve_migrations
    revision_context.run_autogenerate(rev, context)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 415, in run_autogenerate
    self._run_environment(rev, migration_context, True)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 451, in _run_environment
    autogen_context, migration_script)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 22, in _populate_migration_script
    _produce_net_changes(autogen_context, upgrade_ops)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 48, in _produce_net_changes
    autogen_context, upgrade_ops, schemas
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 315, in go
    fn(*arg, **kw)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 75, in _autogen_for_tables
    inspector, upgrade_ops, autogen_context)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 190, in _compare_tables
    s, tname, conn_table, metadata_table
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/util/langhelpers.py", line 315, in go
    fn(*arg, **kw)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 427, in _compare_indexes_and_uniques
    conn_indexes = set(_make_index(ix, conn_table) for ix in conn_indexes)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 427, in <genexpr>
    conn_indexes = set(_make_index(ix, conn_table) for ix in conn_indexes)
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 200, in _make_index
    *[conn_table.c[cname] for cname in params['column_names']],
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/alembic/autogenerate/compare.py", line 200, in <listcomp>
    *[conn_table.c[cname] for cname in params['column_names']],
  File "/Users/krystofpilnacekrossum/workspace/elis-connector-integreon/venv/lib/python3.7/site-packages/sqlalchemy/util/_collections.py", line 194, in __getitem__
    return self._data[key]
KeyError: None

In the end, it's not that big deal as it is possible to generate the migration using postgres and apply it on SQLite.

Reproduce the failure with alembic revision --autogenerate against SQLite, then inspect alembic/autogenerate/compare.py, especially _compare_indexes_and_uniques and _make_index at the traceback locations. Done means functional indexes no longer raise KeyError: None during SQLite autogeneration and are handled consistently with the unsupported-reflection warning shown for PostgreSQL.