Custom MappedCollection example for SQLAlchemy returning a dict of lists

example_models.py

from sqlalchemy import Model, Column, Integer, ForeignKey, String, Text
from orm_helpers import KeyedListCollection


class ItemAttribute(Model):
    __tablename__ = 'item_attributes'
    id = Column(Integer, primary_key=True, autoincrement=True)
    item_id = Column(Integer, ForeignKey('items.id'))
    attribute = Column(String(255))
    value = Column(Text)


class Item(Model):
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True, autoincrement=True)
    attributes = db.relationship(ItemAttribute,
                                 collection_class=lambda: KeyedListCollection('attribute'),
                                 cascade='all, delete-orphan')

"""This relationship allows you to access your attributes as a list of ItemAttribute objects,
keyed off their `attribute` value. So `item.attributes` might return something like this:

    {
        'title': [ <ItemAttribute attribute="title" value="Example"> ]
        'features': [
            <ItemAttribute attribute="features" value="Hello">,
            <ItemAttribute attribute="features" value="World">
        ]
    }
"""

orm_helpers.py

import operator

from sqlalchemy.orm.collections import MappedCollection, collection


class KeyedListCollection(MappedCollection):
    """This transforms a collection of attribute values into a dict of lists.
    
    This is useful when representing an EAV table, for instance, because
    any given attribute can have multiple values, but it is typically
    most useful to access them by attribute name.
    """
    def __init__(self, key):
        super(KeyedListCollection, self).__init__(operator.attrgetter(key))
    
    @collection.internally_instrumented
    def __setitem__(self, key, value, _sa_initiator=None):
        if not super(KeyedListCollection, self).get(key):
            super(KeyedListCollection, self).__setitem__(key, [], _sa_initiator)
        super(KeyedListCollection, self).__getitem__(key).append(value)