CODEWITHSUNDEEP
pythonpostgresqlsqlalchemyalembic
Andrew Hows
Andrew Hows

Reputation: 1438

Populating an array of enums

I'm having trouble getting enums into my table from my alembic migration.

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

(SQLAlchemy 1.2, psycopg2 2.7.3.2 and postgres 10.1 - line 15 needs to be modified with your postgres URI)

I read about issues with SQLAlchemy/Postgres and Arrays of Enums, but according to what I could find in issue tracker, that was resolved with 1.1.

Can someone point me in the right direction?

Variation 1: Attempting to use an attribute of the postgres enum type

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

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

Variation 2: attempting to use an attribute of the underlying python Enum

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

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

Variation 3: Casting the string array into an enum array

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

This may or may not work - the python process balloons up to using 4GB of memory, and sits there until terminated.

Variation 4: Inserting empty array

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

This works, but, obviously, no value.

Upvotes: 5

Views: 1611

Answers (1)

emulbreh
emulbreh

Reputation: 3461

Unfortunately arrays of enums don't work out of the box, but there's a documented workaround, which is also described in this answer. Using ArrayOfEnum instead of ARRAY, your variation 2 works:

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

Casting to ARRAY(permissioncontexts) should also work, and does work when not using bulk_insert()

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 a bug with either alembic's or sqlalchemy's handling of multiparams.

Upvotes: 3

Related Questions