Array of enums does not allow assigning

[Wichert Akkerman]

There is an overlap with #2940 here.

I have an array of enums:

topping = sa.Enum('spinach', 'feta', 'pinenuts', name='topping')

Base = declarative_base()

class Pizza(Base):
    __tablename__ = 'pizza'
    id = sa.Column(sa.Integer(), primary_key=True)
    toppings = sa.Column(ARRAY(topping))

With that minimal model I try to creating a new pizza instance with some toppings:

pizza = Pizza(toppings=['feta', 'spinach'])
session.add(pizza)
session.flush()

which results in this error:

Traceback (most recent call last):
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
psycopg2.ProgrammingError: column "toppings" is of type topping[] but expression is of type text[]
LINE 1: INSERT INTO pizza (toppings) VALUES (ARRAY['feta', 'spinach'...
                                             ^
HINT:  You will need to rewrite or cast the expression.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "x.py", line 25, in <module>
    session.flush()
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2004, in flush
    self._flush(objects)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2122, in _flush
    transaction.rollback(_capture_exception=True)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 60, in __exit__
    compat.reraise(exc_type, exc_value, exc_tb)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 182, in reraise
    raise value
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/session.py", line 2086, in _flush
    flush_context.execute()
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 373, in execute
    rec.execute(self)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/unitofwork.py", line 532, in execute
    uow
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 174, in save_obj
    mapper, table, insert)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/orm/persistence.py", line 761, in _emit_insert_statements
    execute(statement, params)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1010, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1339, in _handle_dbapi_exception
    exc_info
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 188, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/util/compat.py", line 181, in reraise
    raise value.with_traceback(tb)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/Users/wichert/Jzoo/backend/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "toppings" is of type topping[] but expression is of type text[]
LINE 1: INSERT INTO pizza (toppings) VALUES (ARRAY['feta', 'spinach'...
                                             ^
HINT:  You will need to rewrite or cast the expression.
 [SQL: 'INSERT INTO pizza (toppings) VALUES (%(toppings)s) RETURNING pizza.id'] [parameters: {'toppings': ['feta', 'spinach']}]

This is using SQLAlchemy 1.0.4 and psycopg2 2.5.4.

[Wichert Akkerman]

I tried to use an array literal as workaround:

pizza = Pizza(toppings=array(['feta', 'spinach'], type_=topping))

this gave in the same error.

[Wichert Akkerman]

I've attached a minimal script to reproduce this.

[Mike Bayer]

the overlap with #2940 suggests there's a psycopg2 hook I should be calling that allows psycopg2 to combine ARRAY and ENUM correctly - is there one? ( I will check). Also, the error message suggests using a cast, so I'd think the workaround here needs to use cast().

[Mike Bayer]

no feature in PG, I already did all the work to identify this here: http://stackoverflow.com/a/9547795/34549, three years ago.

Please email the psycopg2 list with our use case, as I think they should support it. Below, we illustrate ENUM works, ARRAY of VARCHAR works, ARRAY of ENUM does not. PG's support for UUID handles the ARRAY of UUID type, why can't they do ARRAY of ENUM?

import psycopg2
conn = psycopg2.connect(
    user='scott', password='tiger', host='localhost', dbname='test')
cursor = conn.cursor()

cursor.execute("CREATE TYPE topping AS ENUM ('spinach', 'feta', 'pinenuts')")

cursor.execute("""
CREATE TABLE pizza_one_enum_topping (
    id SERIAL NOT NULL,
    toppings topping,
    PRIMARY KEY (id)
)
""")

cursor.execute("""
CREATE TABLE pizza_many_varchar_toppings (
    id SERIAL NOT NULL,
    toppings VARCHAR(20)[],
    PRIMARY KEY (id)
)
""")

cursor.execute("""
CREATE TABLE pizza_many_enum_toppings (
    id SERIAL NOT NULL,
    toppings topping[],
    PRIMARY KEY (id)
)
""")

cursor.execute(
    "INSERT INTO pizza_one_enum_topping (toppings) VALUES (%(toppings)s)",
    {'toppings': 'spinach'}
)
print("OK for pizza_one_enum_topping")

cursor.execute(
    "INSERT INTO pizza_many_varchar_toppings (toppings) VALUES (%(toppings)s)",
    {'toppings': ['feta', 'spinach']}
)
print("OK for pizza_many_varchar_toppings")

cursor.execute(
    "INSERT INTO pizza_many_enum_toppings (toppings) VALUES (%(toppings)s)",
    {'toppings': ['feta', 'spinach']}
)
print("OK for pizza_many_enum_toppings")

[Mike Bayer]

note that I'd rather not get into creating custom psycopg2 type casters within SQLAlchemy, because one day psycopg2 will implement the feature and then probably break against what we're doing (you never know when psycopg2 will add a new type handler either as always-on, e.g. JSON/JSONB, or optional, e.g. HSTORE, UUID).

We also could do this with a CAST expression on the typing side.

[Mike Bayer]

though, we're sending in strings so psycopg2 wouldn't have any way of knowing we're even dealing with an ENUM. OK. Hmmmm..., we'd need a psycopg2 enum("mystring") type object from them, at the very least.

[Mike Bayer]

OK so what it wants is:

INSERT INTO pizza_many_enum_toppings (toppings) VALUES (CAST(%(toppings)s AS topping[]))

that's not too hard. Your test can run at the moment as:

pizza = Pizza(toppings=sa.cast(['feta', 'spinach'], ARRAY(topping)))

[Mike Bayer]

OK you can use this type:

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

and I can patch a feature:

diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py
index 22c66db..ba0c44a 100644
--- a/lib/sqlalchemy/dialects/postgresql/base.py
+++ b/lib/sqlalchemy/dialects/postgresql/base.py
@@ -1015,7 +1015,7 @@ class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine):
     comparator_factory = Comparator

     def __init__(self, item_type, as_tuple=False, dimensions=None,
-                 zero_indexes=False):
+                 zero_indexes=False, requires_bind_cast=False):
         """Construct an ARRAY.

         E.g.::
@@ -1049,6 +1049,13 @@ class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine):

          .. versionadded:: 0.9.5

+        :param requires_bind_cast: when True, expressions that refer to this
+         type surrounding a bound parameter will be wrapped in the
+         expression ``CAST(<bindparam> AS <type>[])``; this is used when
+         the database adapter does not automatically handle this case,
+         and is needed in particular for an array of ENUM types.
+
+
         """
         if isinstance(item_type, ARRAY):
             raise ValueError("Do not nest ARRAY types; ARRAY(basetype) "
@@ -1059,6 +1066,7 @@ class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine):
         self.as_tuple = as_tuple
         self.dimensions = dimensions
         self.zero_indexes = zero_indexes
+        self.requires_bind_cast = requires_bind_cast

     @property
     def python_type(self):
@@ -1088,6 +1096,12 @@ class ARRAY(sqltypes.Concatenable, sqltypes.TypeEngine):
                 for x in arr
             )

+    def bind_expression(self, bindvalue):
+        if self.requires_bind_cast:
+            return sql.cast(bindvalue, self)
+        else:
+            return bindvalue
+
     def bind_processor(self, dialect):
         item_proc = self.item_type.\
             dialect_impl(dialect).\

[Mike Bayer]

heh, well that's not enough. because here is what you get back:

 ['{', 'f', 'e', 't', 'a', ',', 's', 'p', 'i', 'n', 'a', 'c', 'h', '}']

[Mike Bayer]

OK this is really the kind of thing where if I build this into the dialect, psycopg2 is going to suddenly work one day, so I'd rather just add this workaround to the docs for now:

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

let me know that this type object does everything you need, at least.

[Jacob Heller]

Your code is great (I even answered an SO question with it), except it raises an error if the retrieved value in the database is null. To fix that, I just added

if value==None:
                return []

to the top of the

hand_raw_string

method.

[Mike Bayer]

@jacobgh222 OK, well if the column is NULL we typically expect NULL to be returned.

[Mike Bayer]

• document workaround type for ARRAY of ENUM, fixes #3467

→ <<cset ba1e959e5316>>

[Mike Bayer]

• document workaround type for ARRAY of ENUM, fixes #3467

(cherry picked from commit ba1e959e5316a8c17ca80dca950574038bd650c4)

Conflicts: lib/sqlalchemy/dialects/postgresql/array.py

→ <<cset 7a3a5e921132>>

[Mike Bayer]

sorry, that's all i want to get into on this one for now, if I build it in it's suddenly going to just break one day as each DBAPI supports it. If people check with psycopg2 and can show that they have no plans to ever support this, we can reopen as a feature here.

[JacobH]

Thanks. This works great assuming somewhere you map the enum type directly as a column. I only have my enum type mapped as an ArrayOfEnum. create_all() and drop_all() do not recognize the enum so it neither creates nor drops the type.

day_of_week_type = Enum('Su', 'Mo', 'Tu', 'We', 'Th', 'Fr', 'Sa', name='days_of_week')

class Party(Model):
    <snip>
    days_of_week = Column(ArrayOfEnum(day_of_week_type))
    <snip>

will fail if I actually try to create and insert a Party object. However simply doing:

class Party(Model):
    <snip>
    day_of_week = Column(day_of_week_type)
    days_of_week = Column(ArrayOfEnum(day_of_week_type))
    <snip>

works. Somewhere in at least one mapping, the actual enum type needs to be mapped directly and not via an ArrayOfEnum

I've been digging around and can think of a couple of ugly solutions, but was wondering if there is a quick and elegant solution to this little problem.

Ideas?

Thanks much!

[Mike Bayer]

fixed in 1.1 http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#change-2729

[JacobH]

Thanks Mike!

I pip installed SQLAlchemy==1.1.0b1.dev0 and looking good. I guess it's still handy to keep the ArrayOfEnum helper class around to get around the expression is of type text[] need for casting.

[Mike Bayer]

this just came up for JSONB also. However psycopg2 seems to handle the "result" side without modification for that one (leaning towards the requires_bind_cast feature again).

[Mike Bayer]

• add a section for ARRAY of JSON to complement ARRAY of ENUM. references #3467

Change-Id: I9836b842be01ef24138071fa022d80f5f77be14f

→ <<cset 2b4d028a6927>>

[Mike Bayer]

• add a section for ARRAY of JSON to complement ARRAY of ENUM. references #3467

Change-Id: I9836b842be01ef24138071fa022d80f5f77be14f (cherry picked from commit 2b4d028a69270c1c7918281a60280dd0b65963a2)

→ <<cset c113b0e98b37>>