Using alembic with the Declarative Enum recipe.

[Jan Wąsak]

Hi,

I am currently trying to make alembic perform a migration, where I have a new column using the declarative enum recipe: http://techspot.zzzeek.org/2011/01/14/the-enum-recipe/

My code is more or less:

class AwesomeEnumMixin(DeclEnum):
    @classmethod
    def all_value_sorted(cls):
        """Return enum as list sorted by enum.value."""
        return sorted(list(cls), key=lambda lcs: lcs.value)

class ClientCameFrom(AwesomeEnumMixin):
    undefined = 'A', u'Nie określono'
    internet = 'B', u'Internet'
    ex_customer = 'C', u'Były klient'
    friend = 'E', u'Z polecenia znajomego'

class Client(AbstractModel, ModelCreatorMixin):
    __tablename__ = 'clients'

    id = Column(Integer, primary_key=True)
    ...
    ...
    came_from = Column(ClientCameFrom.db_type(), nullable=False)

The ```came_from`` column is a new one, I'd like to include it in the migration:

"""My very first alembic migration

Revision ID: 41ca36462c98
Revises: None
Create Date: 2013-06-04 23:51:27.104259

"""

# revision identifiers, used by Alembic.
revision = '41ca36462c98'
down_revision = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql
from actavera.models.clients import ClientCameFrom

def upgrade():
    ### commands auto generated by Alembic - please adjust! ###
    ClientCameFrom.db_type().create(bind=op.get_bind().engine)
    op.add_column('clients', sa.Column('came_from', ClientCameFrom.db_type(), nullable=False))
    op.execute(u'UPDATE clients SET came_from=\'A\'')
    ### end Alembic commands ###


def downgrade():
    ### commands auto generated by Alembic - please adjust! ###
    op.drop_column('clients', 'came_from')
    ### end Alembic commands ###


The recipe works really well for me and I love it, but after some debugging I found (or so I think) that in this recipe the create statement for the DeclEnum specific types are issued _on_table_create of the clients table. The create statement doesn't help much because as I traced

        if t.__class__ is not self.__class__ and isinstance(t, SchemaType):
            t.create(bind=bind, checkfirst=checkfirst)

DeclEnum is in fact DeclEnum (phew) and no creating happens.

I really love what this recipe does to my code and I like the idea of doing things with alembic only, so if possible I'd like to avoid hacking and mixing migration techniques.

I am sure I'm missing something important here, can you please tell me what it is? :)

Thanks for the FANTASTIC work on all this.
Cheers,
John

[Jan Wąsak]

Oh, sorry it's late. I should probably also say what the actual problem is.

File "/home/jhnwsk/env/local/lib/python2.7/site-packages/SQLAlchemy-0.8.1-py2.7-linux-i686.egg/sqlalchemy/engine/default.py", line 324, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (ProgrammingError) type "ck_client_came_from" does not exist
LINE 1: ALTER TABLE clients ADD COLUMN came_from ck_client_came_from...
                                                 ^
 'ALTER TABLE clients ADD COLUMN came_from ck_client_came_from NOT NULL' {}

:)

[Michael Bayer]

When you create that Emum you need to make sure you're in the same transaction, so don't convert from Connection to Engine, just use get_bind() as is:

ClientCameFrom.db_type().create(bind=op.get_bind())

    op.add_column('clients', sa.Column('came_from', ClientCameFrom.db_type(), nullable=False))

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Jan Wąsak]

This does not change a lot. Judging by the logs I still believe no create statement is issued:

(env)jhnwsk@master-blaster:~/_development/$ alembic upgrade head
INFO  [sqlalchemy.engine.base.Engine] select version()
INFO  [sqlalchemy.engine.base.Engine] {}
DEBUG [sqlalchemy.engine.base.Engine] Col ('version',)
DEBUG [sqlalchemy.engine.base.Engine] Row (u'PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit',)
INFO  [sqlalchemy.engine.base.Engine] select current_schema()
INFO  [sqlalchemy.engine.base.Engine] {}
DEBUG [sqlalchemy.engine.base.Engine] Col ('current_schema',)
DEBUG [sqlalchemy.engine.base.Engine] Row (u'public',)
INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
DEBUG [txn.140264917944064] new transaction
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
DEBUG [sqlalchemy.engine.base.Engine] Col ('relname',)
INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO  [sqlalchemy.engine.base.Engine] {}
DEBUG [sqlalchemy.engine.base.Engine] Col ('version_num',)
INFO  [alembic.migration] Running upgrade None -> 41ca36462c98, My very first alembic migration
INFO  [sqlalchemy.engine.base.Engine] ALTER TABLE clients ADD COLUMN came_from ck_client_came_from NOT NULL
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] ROLLBACK
Traceback (most recent call last):
...
...
...

sqlalchemy.exc.ProgrammingError: (ProgrammingError) type "ck_client_came_from" does not exist
LINE 1: ALTER TABLE clients ADD COLUMN came_from ck_client_came_from...
                                                 ^
 'ALTER TABLE clients ADD COLUMN came_from ck_client_came_from NOT NULL' {}

My problem is that upon calling DeclEnumType.create() and in consequence self.dialect_impl(bind.dialect), the implied type of the DeclEnumType gets hidden away by the actual DeclEnum type. Thus, the create statement for the Enum type is never emitted.

I managed to "FIX" this by overriding the create method on the DeclEnumType like so:

class DeclEnumType(SchemaType, TypeDecorator):
    def __init__(self, enum):
        self.enum = enum
        self.impl = Enum(
            *enum.values(),
            name="ck%s" % re.sub(
                '([A-Z])',
                lambda m: "_" + m.group(1).lower(),
                enum.__name__)
        )

    ...
    ...

    def create(self, bind=None, checkfirst=False):
        """Issue CREATE ddl for this type, if applicable."""
        super(DeclEnumType, self).create(bind, checkfirst)
        t = self.dialect_impl(bind.dialect)
        if t.impl.__class__ is not self.__class__ and isinstance(t, SchemaType):
            t.impl.create(bind=bind, checkfirst=checkfirst)

This way my ```t``` is the {Enum} ck_client_came_from type (I'm guessing the implied type for the DeclEnum abstraction) and the create statement gets emitted like so:

(env)jhnwsk@master-blaster:~/_development/$ alembic upgrade head
INFO  [sqlalchemy.engine.base.Engine] select version()
INFO  [sqlalchemy.engine.base.Engine] {}
DEBUG [sqlalchemy.engine.base.Engine] Col ('version',)
DEBUG [sqlalchemy.engine.base.Engine] Row (u'PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit',)
INFO  [sqlalchemy.engine.base.Engine] select current_schema()
INFO  [sqlalchemy.engine.base.Engine] {}
DEBUG [sqlalchemy.engine.base.Engine] Col ('current_schema',)
DEBUG [sqlalchemy.engine.base.Engine] Row (u'public',)
INFO  [sqlalchemy.engine.base.Engine] BEGIN (implicit)
DEBUG [txn.140612540696320] new transaction
INFO  [alembic.migration] Context impl PostgresqlImpl.
INFO  [alembic.migration] Will assume transactional DDL.
INFO  [sqlalchemy.engine.base.Engine] select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
INFO  [sqlalchemy.engine.base.Engine] {'name': u'alembic_version'}
DEBUG [sqlalchemy.engine.base.Engine] Col ('relname',)
INFO  [sqlalchemy.engine.base.Engine]
CREATE TABLE alembic_version (
    version_num VARCHAR(32) NOT NULL
)


INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] SELECT alembic_version.version_num
FROM alembic_version
INFO  [sqlalchemy.engine.base.Engine] {}
DEBUG [sqlalchemy.engine.base.Engine] Col ('version_num',)
INFO  [alembic.migration] Running upgrade None -> 41ca36462c98, My very first alembic migration
INFO  [sqlalchemy.engine.base.Engine] CREATE TYPE ck_client_came_from AS ENUM ('A','C','B','E','D')
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] ALTER TABLE clients ADD COLUMN came_from ck_client_came_from DEFAULT 'A' NOT NULL
INFO  [sqlalchemy.engine.base.Engine] {}
INFO  [sqlalchemy.engine.base.Engine] INSERT INTO alembic_version (version_num) VALUES ('41ca36462c98')
INFO  [sqlalchemy.engine.base.Engine] {}

Hooray!
There's two questions I have

1. Does what I did make any sense whatsoever? Is the Declarative Enum from the recipe meant to be created explicitly this way? Your code is very descriptive and I THNIK I get the concept of implied types but honestly I am kind of a dog in the fog with my "fix" ;)
2. I can get the exact same effect if I just tell my migration to use my ScopedSession and Base and say: Base.metadata.create_all() in the run online migrations routine. Is that the preferred way to run migrations online when using the (oh-so-awesome) ORM from SQLAlchemy?

Thanks for you patience, I'm kind of new to this :)

cheers,
John

[Michael Bayer]

On Jun 5, 2013, at 6:13 AM, Jan Wąsak <jhn...@gmail.com> wrote:

>
> I managed to "FIX" this by overriding the create method on the DeclEnumType like so:
>
> class DeclEnumType(SchemaType, TypeDecorator):
> def __init__(self, enum):
> self.enum = enum
> self.impl = Enum(
> *enum.values(),
> name="ck%s" % re.sub(
> '([A-Z])',
> lambda m: "_" + m.group(1).lower(),
> enum.__name__)
> )
>
> ...
> ...
>
> def create(self, bind=None, checkfirst=False):
> """Issue CREATE ddl for this type, if applicable."""
> super(DeclEnumType, self).create(bind, checkfirst)
> t = self.dialect_impl(bind.dialect)
> if t.impl.__class__ is not self.__class__ and isinstance(t, SchemaType):
> t.impl.create(bind=bind, checkfirst=checkfirst)
>
> This way my ```t``` is the {Enum} ck_client_came_from type (I'm guessing the implied type for the DeclEnum abstraction) and the create statement gets emitted like so:

yeah that is probably the correct workaround for this one.

>
>
> 1. Does what I did make any sense whatsoever? Is the Declarative Enum from the recipe meant to be created explicitly this way? Your code is very descriptive and I THNIK I get the concept of implied types but honestly I am kind of a dog in the fog with my "fix" ;)

Postgresql's ENUM type is hugely annoying to me as there are no other objects in any other database that follow this really awkward pattern, so the type API really has a hard time with it and we get a lot of bug reports about it. I'd need to look into the mechanics of SchemaType and TypeDecorator more deeply in order to get them to play better together so that workarounds like this arent necessary.

> 2. I can get the exact same effect if I just tell my migration to use my ScopedSession and Base and say: Base.metadata.create_all() in the run online migrations routine. Is that the preferred way to run migrations online when using the (oh-so-awesome) ORM from SQLAlchemy?

Well if you aren't in alembic then yes, that system will emit events that I think the SchemaType is taking advantage of. Ideally all of these systems could be improved a bit.

[Darin Gordon]

This post is almost a year old.  How relevant are the issues discussed in this thread to SQLAlchemy 9.3?  I was intending to use your declarative enum recipe posted from '11.  

-Darin

[Michael Bayer]

PG’s ENUM type still presents some complexities that we haven’t fully smoothed over in Alembic though these issues can all be worked around.

On my end I use plain CHAR columns for enums even on PG.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy-alem...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Luca Barbato]

On Tuesday, May 6, 2014 10:06:35 PM UTC+2, Michael Bayer wrote:

PG’s ENUM type still presents some complexities that we haven’t fully smoothed over in Alembic though these issues can all be worked around.

On my end I use plain CHAR columns for enums even on PG.

I'm having a similar problem with sqlite, in this case is even more cryptic...

AttributeError: 'module' object has no attribute 'DeclEnumType'