Gino: Partial index

[Pentusha]

• GINO version: 0.8.3
• Python version: Python 3.7.0
• asyncpg version: 0.18.3
• aiocontextvars version: 0.2.2
• PostgreSQL version: 10.6

Description

I want to create partial index for gino model with alembic.

What I Did

Model:

class TestModel(db.Model):
    my_column = db.Column(db.Unicode)
    deleted_at = db.Column(db.DateTime)

    _test_index = db.Index(
        'test_index',
        'my_column',
        postgresql_where=deleted_at.is_(None),
    )

Generate migration

alembic revision -m "test_index" --autogenerate

Traceback

Traceback (most recent call last):
  File "/home/pentusha/.venvs/gino-test/bin/alembic", line 11, in <module>
    load_entry_point('alembic==1.0.10', 'console_scripts', 'alembic')()
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/config.py", line 540, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/config.py", line 534, in main
    self.run_cmd(cfg, options)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/config.py", line 514, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/command.py", line 199, in revision
    scripts = [script for script in revision_context.generate_scripts()]
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/command.py", line 199, in <listcomp>
    scripts = [script for script in revision_context.generate_scripts()]
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 504, in generate_scripts
    yield self._to_script(generated_revision)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/api.py", line 417, in _to_script
    autogen_context, migration_script, template_args
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 49, in _render_python_into_templatevars
    _render_cmd_body(upgrade_ops, autogen_context)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 73, in _render_cmd_body
    lines = render_op(autogen_context, op)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 85, in render_op
    lines = util.to_list(renderer(autogen_context, op))
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 112, in _render_modify_table
    t_lines = render_op(autogen_context, t_op)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 85, in render_op
    lines = util.to_list(renderer(autogen_context, op))
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 257, in _add_index
    if len(index.kwargs)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 253, in <listcomp>
    for key, val in index.kwargs.items()
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/alembic/autogenerate/render.py", line 509, in _render_potential_expr
    value.compile(dialect=autogen_context.dialect, **compile_kw)
  File "<string>", line 1, in <lambda>
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 462, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 468, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 562, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 319, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 350, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 972, in visit_clauselist
    c._compiler_dispatch(self, **kw) for c in clauselist.clauses
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 970, in <genexpr>
    s
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 972, in <genexpr>
    c._compiler_dispatch(self, **kw) for c in clauselist.clauses
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 1285, in visit_binary
    return self._generate_generic_binary(binary, opstring, **kw)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 1332, in _generate_generic_binary
    + binary.right._compiler_dispatch(
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/visitors.py", line 91, in _compiler_dispatch
    return meth(self, **kw)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 841, in visit_column
    name = self._fallback_column_name(column)
  File "/home/pentusha/.venvs/gino-test/lib/python3.7/site-packages/sqlalchemy/sql/compiler.py", line 832, in _fallback_column_name
    "Cannot compile Column object until " 
sqlalchemy.exc.CompileError: Cannot compile Column object until its 'name' is assigned.

I found two workarounds, but I want to know the less hacky way to do this. Is it possible for gino to specify columns name immediately using python's descriptors?

deleted_at = db.Column(db.DateTime, name='deleted_at')

and

_test_index = db.Index(
    'test_index',
    'my_column',
    postgresql_where=sa.text('deleted_at is null'),
)

[wwwjfy]

This is a more Alembic "issue" (I think it's a usage question actually).
IMO, your first solution (specifying the column name) is a good one.
I prefer keeping SQLAlchemy's design to avoid potential bugs.

[fantix]

Also, you can patch GINO for now:

import sqlalchemy
from gino.declarative import ModelType


class Dict(collections.OrderedDict):
    def __setitem__(self, key, value):
        if isinstance(value, sqlalchemy.Column):
            if not value.name:
                value.name = key
        return super().__setitem__(key, value)


ModelType.__prepare__ = lambda *_, **__: Dict()

(But this doesn't work for columns defined in @declared_attr().)

@wwwjfy do you think we could/should add this to the source?

[Pentusha]

fantix, works for me

[wwwjfy]

@fantix only concern is SQLAlchemy seems to have some special handling on name, namely quoted_name in current version. As it's a slight difference, I have no strong opinion we should or should not include that.
I myself am fine with deleted_at = db.Column(db.DateTime, name='deleted_at') method in @Pentusha's example

[fantix]

@wwwjfy I see, thanks for the update! We are doing this anyway, so I'm checking if the quoted_name has any impact.

[wwwjfy]

In SQLAlchemy's doc, it also says The name field may be omitted at construction time and applied later, at any time before the Column is associated with a Table. And I don't find quoted_name is applied in this case. I guess it leaves it to the users. 🤔

[fantix]

Maybe Column(quote)?

[wwwjfy]

I see it's only used in Column.__init__ and not saved. https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/sql/schema.py#L1314

        if name is not None:
            name = quoted_name(name, kwargs.pop("quote", None))

So if the name is assigned to the column afterwards, it won't be applied with quoted_name in SQLAlchemy. The user may need to take care of it. If this is true, I guess we can do that too.

[fantix]

Yeah agreed! Tested with column names, quote=None seems to be auto quoting depending on the dialect, while True/False means force-(no-)quoting.

I'll add the patch and release a new version for recent bug fixes. Users would get:

1. Columns defined in Models (not under @declared_attr) get an auto-quoted name immediately if not specifying name= explicitly.
2. If force-quoting or force-no-quoting is needed, just do this:

class User(db.Model):
    force_quote = db.Column(db.Unicode(), name=db.quoted_name("force_quote", True))
    force_no_quote = db.Column(db.Unicode(), name=db.quoted_name("select", False))
    auto_quote = db.Column(db.Unicode())