Filling an Array of Enumerations

Question

I am having trouble listing to a table from my alembic migration.

MVCE is here: https://pastebin.com/ng3XcKLf

(SQLAlchemy 1.2, psycopg2 2.7.3.2 and postgres 10.1 - line 15 should be changed with your postgres URI)

I read about problems with SQLAlchemy / Postgres and Enums arrays, but according to what I could find in the tracker problem, this was resolved with 1.1.

Can someone point me in the right direction?

Variation 1: Attempting to use the postgres enumeration type attribute

op.bulk_insert(permission, [{ 'name': 'ViewContent', 'contexts': [pgpermissioncontexts.resourceType] }]) 

This fails: AttributeError: 'Enum' object has no attribute 'resourceType'

Option 2: trying to use the base python Enum attribute

 op.bulk_insert(permission, [{ 'name': 'ViewContent', 'contexts': [PermissionContexts.resourceType] }]) 

This does not work with sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) cols of type permissioncontexts[] but expression is of type text[]

Variation 3: Listing an array of strings in an array of enumerations

 op.bulk_insert(permission, [{ 'name': 'ViewContent', 'contexts': sa.cast([PermissionContexts.resourceType], sa.ARRAY(pgenum)) }]) 

This may or may not work - the python process launches balloons before using 4 GB of memory and sits there until it finishes.

Option 4: Insert an Empty Array

 op.bulk_insert(permission, [{ 'name': 'ViewContent', 'contexts': [] }]) 

It works, but obviously of no value.

Answer 1

Unfortunately, enumeration arrays do not work out of the box, but there is a documented workaround that is also described in this answer . Using ArrayOfEnum instead of ARRAY , your option 2 works:

 op.bulk_insert(permission, [{ 'name': 'ViewContent', 'contexts': [PermissionContexts.resourceType], }]) 

ARRAY(permissioncontexts) should also work and works if bulk_insert() not used

 op.execute(permission.insert().values({ 'name': 'ViewContent', 'contexts': sa.cast([PermissionContexts.resourceType], ARRAY(pgpermissioncontexts)), })) 

or when using bulk_insert(multiinsert=False) :

 op.bulk_insert(permission, [{ 'name': 'ViewContent', 'contexts': sa.cast([PermissionContexts.resourceType], ARRAY(pgpermissioncontexts)), }], multiinsert=False) 

There seems to be an error with multicast processing using alembic or sqlalchemy.