Array of enums does not allow assigning

[sqlalchemy-bot]

Migrated issue, originally created by Wichert Akkerman (@wichert)

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.

---

Attachments: x.py

[sqlalchemy-bot]

Wichert Akkerman (@wichert) wrote:

I tried to use an array literal as workaround:

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

this gave in the same error.

[sqlalchemy-bot]

Wichert Akkerman (@wichert) wrote:

I've attached a minimal script to reproduce this.

[sqlalchemy-bot]

Changes by Wichert Akkerman (@wichert):

• attached file x.py

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• added labels: postgres

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• set milestone to "1.x.xx"

[sqlalchemy-bot]

Jacob Heller wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• document workaround type for ARRAY of ENUM, fixes Array of enums does not allow assigning #3467

→ ba1e959

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• document workaround type for ARRAY of ENUM, fixes Array of enums does not allow assigning #3467

(cherry picked from commit ba1e959)

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

→ 7a3a5e9

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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.

[sqlalchemy-bot]

jrhite (@jrhite) wrote:

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!

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

jrhite (@jrhite) wrote:

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.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

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

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• add a section for ARRAY of JSON to complement ARRAY of ENUM. references Array of enums does not allow assigning #3467

Change-Id: I9836b842be01ef24138071fa022d80f5f77be14f

→ 2b4d028

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

• add a section for ARRAY of JSON to complement ARRAY of ENUM. references Array of enums does not allow assigning #3467

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

→ c113b0e

[vyh]

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

If the array is empty this raises a LookupError for me (due to returning ['']) - can modify to return inner.split(',') if inner else [] for an empty list instead.

[zzzeek]

hi @vyh -

this issue is three years old so I don't really know what your code excerpt refers to or how you are using it. can you please create a new issue with a complete example and full context, thanks

[flying-sheep]

Hello traveller from the future. Know that the docs now say

Changed in version 1.3.17: The combination of ENUM and ARRAY is now directly handled by SQLAlchemy’s implementation without any workarounds needed.