Gino: How to use Enum type in GINO for Alembic

[kexirong]

#### Python version:3.6.8

aiocontextvars==0.2.2
aiofiles==0.4.0
alembic==1.3.1
asn1crypto==0.24.0
asyncpg==0.19.0
bcrypt==3.1.7
certifi==2019.9.11
cffi==1.11.5
chardet==3.0.4
configobj==5.0.6
contextvars==2.4
cryptography==2.8
decorator==4.2.1
gino==0.8.4
gpg==1.10.0
h11==0.8.1
h2==3.1.1
hpack==3.0.0
httpcore==0.3.0
httptools==0.0.13
hyperframe==5.2.0
idna==2.5
immutables==0.11
iniparse==0.4
Mako==1.1.0
MarkupSafe==1.1.1
multidict==4.5.2
netifaces==0.10.6
paramiko==2.6.0
pciutils==2.3.6
perf==0.1
ply==3.9
psycopg2==2.8.4
pyasn1==0.4.7
pyasn1-modules==0.2.7
pycparser==2.14
pygobject==3.28.3
PyJWT==1.7.1
PyNaCl==1.3.0
pyOpenSSL==18.0.0
python-dateutil==2.6.1
python-dmidecode==3.12.2
python-editor==1.0.4
python-ldap==3.2.0
python-linux-procfs==0.6
pyudev==0.21.0
requests==2.22.0
requests-async==0.5.0
rfc3986==1.3.2
rhnlib==2.8.6
rpm==4.14.2
sanic==19.9.0
sanic-jwt==1.3.2
schedutils==0.6
six==1.11.0
slip==0.6.4
slip.dbus==0.6.4
SQLAlchemy==1.3.11
syspurpose==1.23.8
ujson==1.35
urllib3==1.25.7
uvloop==0.14.0
websockets==8.1

Description

The Enum type can't woruk in Alembic.

What I Did

INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 1954d3e07c17 -> f33a93033354, in
Traceback (most recent call last):
  File "/usr/local/bin/alembic", line 11, in <module>
    load_entry_point('alembic==1.3.1', 'console_scripts', 'alembic')()
  File "/usr/local/lib/python3.6/site-packages/alembic/config.py", line 575, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/usr/local/lib/python3.6/site-packages/alembic/config.py", line 569, in main
    self.run_cmd(cfg, options)
  File "/usr/local/lib/python3.6/site-packages/alembic/config.py", line 549, in run_cmd
    **dict((k, getattr(options, k, None)) for k in kwarg)
  File "/usr/local/lib/python3.6/site-packages/alembic/command.py", line 298, in upgrade
    script.run_env()
  File "/usr/local/lib/python3.6/site-packages/alembic/script/base.py", line 489, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/usr/local/lib/python3.6/site-packages/alembic/util/pyfiles.py", line 98, in load_python_file
    module = load_module_py(module_id, path)
  File "/usr/local/lib/python3.6/site-packages/alembic/util/compat.py", line 173, 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 83, in <module>
    run_migrations_online()
  File "alembic/env.py", line 77, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.6/site-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.6/site-packages/alembic/runtime/migration.py", line 518, in run_migrations
    step.migration_fn(**kw)
  File "/home/kexirong/qkacmdb-sanic/alembic/versions/f33a93033354_in.py", line 29, in upgrade
    sa.Column('roles', gino.schema.Enum('staff', 'admin', 'superuser'), nullable=True, comment='用户类别'),
NameError: name 'gino' is not defined

My code

 roles = db.Column(db.Enum('staff', 'admin', 'superuser', name='role'), default='staff', comment='用户类别')

Alembic generate code

sa.Column('roles', gino.schema.Enum('staff', 'admin', 'superuser', name='role'), nullable=True, comment='用户类别'),

need modified gino.schema.Enum to sa.Enum
I need help ,
thanks

[Pentusha]

What version of PostgreSQL do you use?

PostgreSQL 12 documentation:
https://www.postgresql.org/docs/12/sql-altertype.html

If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

PostgreSQL 11 documentation:
https://www.postgresql.org/docs/11/sql-altertype.html

ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.

So, in older versions of the PostgreSQL it is impossible to use migrations (which wrapped into transactions) of Enums.

[kexirong]

What version of PostgreSQL do you use?

PostgreSQL 12 documentation:
https://www.postgresql.org/docs/12/sql-altertype.html

If ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) is executed inside a transaction block, the new value cannot be used until after the transaction has been committed.

PostgreSQL 11 documentation:
https://www.postgresql.org/docs/11/sql-altertype.html

ALTER TYPE ... ADD VALUE (the form that adds a new value to an enum type) cannot be executed inside a transaction block.

So, in older versions of the PostgreSQL it is impossible to use migrations (which wrapped into transactions) of Enums.

PostgreSQL: 10.6
but the error is

NameError: name 'gino' is not defined

and i modified gino.schema.Enum to sa.Enum can work

[wwwjfy]

Right, that is still at Gino, and hasn't reached PostgreSQL yet.

@kexirong I'm afraid currently you have to manually fix it like what you did, or import gino in the migration files. Alembic assumes SQLAlchemy, and it doesn't support Gino, and it also uses psycopg2 to communicate.

[kexirong]

Every time manually fix it is too much trouble

I tried to add import in script.py.mako

from gino import Gino

gino = Gino()

but still has errors

 gino.schema.Enum('staff', 'admin', 'superuser', name='role')
AttributeError: 'NoneType' object has no attribute 'Enum'

@wwwjfy

[kexirong]

@wwwjfy hi, 请问这个问题，有永久方案吗？ 每次修改migrations 有所不便

[wwwjfy]

Gino is not optimised for Alembic, i.e. Enum is not declared as a class so it couldn't be imported in the migration file.
Before we support that (if ever), I saw a temp hack for this: (being a novice to Alembic, I guess there are better ways though)

In env.py:

def render_item(type_, col, autogen_context):
    if type_ == 'type':
        if (col.__class__.__module__ == 'gino.schema' and
                col.__class__.__name__ == 'Enum'):
            return 'sa.' + repr(col)
    return False

And in context.configure call:

context.configure(render_item=render_item, ...)

[kexirong]

@wwwjfy thanks a lot.
Your solution perfectly solved my troubles

[gazorby]

While this solution works to create the enum automatically with alembic, it does not take into account subsequent changes made to the enum.
As a workaround, i ported SqlAlchemy-Enum-Tables to make it works with gino. Basically, the idea is to store enum values in a table instead of a SQL type. The package also bring alembic support to track changes made to the Python enum and reflects them in the enum table in the database