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))

score 5 · Answer 1 · answered Jun 06 '19 at 10:39

I found nice workaround in SqlAlchemy source code:

import re

from sqlalchemy import TypeDecorator, cast
from sqlalchemy.dialects.postgresql import ARRAY


class ArrayOfEnum(TypeDecorator):

    impl = ARRAY

    def bind_expression(self, bindvalue):
        return 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(",") if inner else []

        def process(value):
            if value is None:
                return None

            return super_rp(handle_raw_string(value))

        return process

And now:

achievements = Column(ArrayOfEnum(Enum(AchievementsType)))

And then:

career.achievements = [AchievementsType.world, AchievementsType.local]

score 4 · Answer 2 · answered Aug 11 '20 at 21:30

4

In modern SqlAlchemy, you don't have to define a custom type for this:

import sqlalchemy.dialects.postgresql as pg

class MyModel(Base):
    ...
    flags = Column(pg.ARRAY(sa.Enum(MyEnum, 
                   create_constraint=False, native_enum=False)))

answered Aug 11 '20 at 21:30

kolypto

31,774
17
105
99

What is `MyEnum` in this case? Can it be a python class such as `Genres` as following: `class Genres(enum.Enum):` _linebreak_ `alternative ='Alternative'` _linebreak_ `blues ='Blues'` ? – Ndrslmpk May 15 '22 at 15:09
2

@Ndrslmpk yes, `MyEnum` is any `enum.Enum` subclass defined in your code :) – kolypto May 16 '22 at 10:16

WilliamMayor · Answer 3 · 2019-01-25T17:11:01.703

1

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

EDIT: Issue moved to https://github.com/sqlalchemy/sqlalchemy/issues/3467

This is, create a custom type like this:

import sqlalchemy as sa

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?

edited Jan 25 '19 at 17:11

answered Dec 22 '16 at 11:48

WilliamMayor

745
6
15

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 Dec 27 '16 at 14:43
What is "sa" on fourth line? – Andru Jan 24 '19 at 15:25
Hey @Andru, I can't find this code in my codebase anymore so I can't tell you for certain (without diving into git histories). I suspect it's just a aliased import of SQLAlchemy though. I've updated the answer to fill this in. – WilliamMayor Jan 25 '19 at 17:11

score 0 · Accepted Answer · edited Jun 20 '20 at 09:12

0

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).

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 05 '17 at 14:05

Tim

1,315
1
14
35

score 0 · Answer 5 · answered Aug 19 '20 at 14:27

0

If you find your way here, updating SQLAlchemy to >=1.3.17 should sort you out.

See the release notes at: https://docs.sqlalchemy.org/en/13/changelog/changelog_13.html#change-e57f5913ab592a9c044cad747636edd8

answered Aug 19 '20 at 14:27

Keith Ma

314
3
4

Array of Enum in Postgres with SQLAlchemy

5 Answers5

Linked