Array of Enum in Postgres with SQLAlchemy

Question

I've been using an array of enums with postgres and SQLAlchemy successfully over the past year like so:

class MyModel(BaseModel):
    enum_field = Column(postgresql.ARRAY(EnumField(MyEnum, native_enum=False)))

The EnumField is from the sqlalchemy_enum34 library, a small wrapper around the builtin enum that uses Python enums as Python representation instead of strings.

Although the docs say, array of enum is not supported, I guess it worked, because I chose 'native_enum=False'. Recently I noticed that it doesn't work anymore, I think it's due to the upgrade from SQLA 1.0 to 1.1, but I'm not sure.

The problem is, that it generates invalid DQL:

CREATE TABLE my_model (
    enum_field VARCHAR(5)[3] NOT NULL CHECK (contexts IN ('ONE', 'TWO', 'THREE'))
)

The error I get is:

ERROR:  malformed array literal: "ONE"
DETAIL:  Array value must start with "{" or dimension information.

Any idea how I can get back my enum array?
By the way: when it worked, no CHECK constraint was actually created, just an array of varying. I'm ok with that as long as I can use enums in my Python code (e.g. query.filter(enum_field==MyEnum.ONE))

WilliamMayor · Answer 1 · 2016-12-22 11:48:27Z

When I needed an array of enums I used the recipe from Mike Bayer here: https://bitbucket.org/zzzeek/sqlalchemy/issues/3467/array-of-enums-does-not-allow-assigning#comment-19370832

This is, create a custom type like this:

class ArrayOfEnum(ARRAY):

    def bind_expression(self, bindvalue):
        return sa.cast(bindvalue, self)

    def result_processor(self, dialect, coltype):
        super_rp = super(ArrayOfEnum, self).result_processor(dialect, coltype)

        def handle_raw_string(value):
            inner = re.match(r"^{(.*)}$", value).group(1)
            return inner.split(",")

        def process(value):
            return super_rp(handle_raw_string(value))
        return process

I haven't used this for a while so I'm not certain that it continues to work.

It's not the same code as your enum34 library so maybe it won't have the same problems?

I tried that before, however, it doesn't change how an array is created in Postgres, the generated DDL is the same. As you can see it only really processes results different than the default ARRAY. — Tim-Erwin, Dec 27 '16 at 14:43

Tim-Erwin · Accepted Answer · 2017-01-05 14:05:56Z

Mike Bayer answered on the sqlalchemy mailing list:

you probably want to add create_constraint=False, see if that works

http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=enum#sqlalchemy.types.Enum.params.create_constraint

I can now create the table (without any CHECK).

asked	2 months ago
viewed	96 times
active	2 months ago

Array of Enum in Postgres with SQLAlchemy

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged python arrays postgresql enums sqlalchemy or ask your own question.

Hot Network Questions

Array of Enum in Postgres with SQLAlchemy

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged python arrays postgresql enums sqlalchemy or ask your own question.

Related

Hot Network Questions