SQLAlchemy/PostgreSQL mutable, deduplicated array

Question

After having read this article on implementing tags with PostgreSQL, I've decide to work with PostgreSQL's array datatype.

But from what I understand, there is not any built-in support for mutable arrays in SQLAlchemy. I would also like to prevent duplicates within these arrays (there isn't a any built-in 'set-like' datatype in PostgreSQL).

I'm having a hard time finding any concrete examples of how to use and manipulate SQLAlchemy arrays (declaratively) that are backed by PostgreSQL, even for simple append/update operations. How would I set the ARRAY and append to (if the to-be-appended string isn't a duplicate) to a SQLAlchemy ARRAY datatype?

Answer 1

I ran into exactly this problem. The solution I used is to create a type that inherits from sqlalchemy.ext.mutable.Mutable and set, then wrap that in a type backed by sqlalchemy.dialects.postgresql.ARRAY.

from sqlalchemy.ext.mutable import Mutable
from sqlalchemy.dialects.postgresql import ARRAY

modmethods = ['add', 'clear', 'difference_update', 'discard', 
              'intersection_update', 'pop', 'remove',
              'symmetric_difference_update', 'update',
              '__ior__', '__iand__', '__isub__', '__ixor__']

class MutableSet(Mutable, set):
    @classmethod
    def coerce(cls, key, value):
        if not isinstance(value, cls):
            return cls(value)
        else:
            return value

def _make_mm(mmname):
    def mm(self, *args, **kwargs):
        try:
            retval = getattr(set, mmname)(self, *args, **kwargs)
        finally:
            self.changed()
        return retval
    return mm

for m in modmethods:
    setattr(MutableSet, m, _make_mm(m))

del modmethods, _make_mm

def ArraySet(_type, dimensions=1):
    return MutableSet.as_mutable(ARRAY(_type, dimensions=dimensions))

This overrides the methods of set that can change the set's value by adding a call to Mutable's changed method, causing SQLAlchemy to flush the possibly changed value to the database.

Then, you declare this type in SQLAlchemy with the type that will be inside it; for instance, to declare it as a set of Integers:

Column(ArraySet(Integer))

Then, when you are using it, you can treat the value just like a standard Python set, with all the operators and methods unchanged.