Attribute as dict of lists using middleman table with SQLAlchemy

Question

My question is about SQLAlchemy but I'm having troubles explaining it in words so I figured I explain it with a simple example of what I'm trying to achieve:

parent = Table('parent', metadata,
    Column('parent_id', Integer, primary_key=True),
    Column('name', Unicode),
)
parent_child = Table('parent_child', metadata,
    Column('parent_id', Integer, primary_key=True),
    Column('child_id', Integer, primary_key=True),
    Column('number', Integer),
    ForeignKeyConstraint(['parent_id'], ['parent.parent_id']),
    ForeignKeyConstraint(['child_id'], ['child.child_id']),
)
child = Table('child', metadata,
    Column('child_id', Integer, primary_key=True),
    Column('name', Unicode),
)
class Parent(object):
    pass
class ParentChild(object):
    pass
class Child(object):
    pass

>>> p = Parent(name=u'A')
>>> print p.children 
{}
>>> p.children[0] = Child(name=u'Child A')
>>> p.children[10] = Child(name=u'Child B')
>>> p.children[10] = Child(name=u'Child C')

This code would create 3 rows in parent_child table, column number would be 0 for the first row, and 10 for the second and third row.

>>> print p.children 
{0: [<Child A>], 10: [<Child B>, <Child C>]}
>>> print p.children[10][0]
<Child B>

(I left out all SQLAlchemy session/engine code in the example to make it as clean as possible)

I did a try using´

collection_class=attribute_mapped_collection('number')

on a relation between Parent and ParentChild, but it only gave me one child for each number. Not a dict with lists in it. Any help appreciated!

UPDATED!

Thanks to Denis Otkidach I now has this code, but it still doesn't work.

def _create_children(number, child):
    return ParentChild(parent=None, child=child, number=number)

class Parent(object):
    children = association_proxy('_children', 'child', creator=_create_children)

class MyMappedCollection(MappedCollection):
    def __init__(self):
        keyfunc = lambda attr_name: operator.attrgetter('number')
        MappedCollection.__init__(self, keyfunc=keyfunc)

    @collection.appender
    @collection.internally_instrumented
    def set(self, value, _sa_initiator=None):
        key = self.keyfunc(value)
        try:
            self.__getitem__(key).append(value)
        except KeyError:
            self.__setitem__(key, [value])

mapper(Parent, parent, properties={
     '_children': relation(ParentChild, collection_class=MyMappedCollection),
})

To insert an Child seems to work

p.children[100] = Child(...)

But when I try to print children like this:

print p.children

I get this error:

sqlalchemy.orm.exc.UnmappedInstanceError: Class '__builtin__.list' is not mapped

Answer 1

You have to define your own collection class. There are only 3 methods to implement: appender, remover, and converter. See sqlalchemy.orm.collections.MappedCollection as an example.

Update: Here is quick-n-dirty implementation according to your requirements:

from sqlalchemy import *
from sqlalchemy.orm import mapper, relation, sessionmaker
from sqlalchemy.orm.collections import collection

metadata = MetaData()

parent = Table('parent', metadata,
    Column('parent_id', Integer, primary_key=True),
    Column('name', Unicode),
)

child = Table('child', metadata,
    Column('child_id', Integer, primary_key=True),
    Column('name', Unicode),
)

parent_child = Table('parent_child', metadata,
    Column('parent_id', Integer, ForeignKey(parent.c.parent_id)),
    Column('child_id', Integer, ForeignKey(child.c.child_id)),
    Column('number', Integer),
    PrimaryKeyConstraint('parent_id', 'child_id'),
)

class ParentChild(object):
    def __init__(self, child, number):
        self.child = child
        self.number = number

class Parent(object): pass

class Child(object): pass


class MyMappedCollection(object):

    def __init__(self, data=None):
        self._data = data or {}

    @collection.appender
    def _append(self, parent_child):
        l = self._data.setdefault(parent_child.number, [])
        l.append(parent_child)

    def __setitem__(self, number, child):
        self._append(ParentChild(number=number, child=child))

    def __getitem__(self, number):
        return tuple(pc.child for pc in self._data[number])

    @collection.remover
    def _remove(self, parent_child):
        self._data[parent_child.number].remove(parent_child)

    @collection.iterator
    def _iterator(self):
        for pcs in self._data.itervalues():
            for pc in pcs:
                yield pc

    def __repr__(self):
        return '%s(%r)' % (type(self).__name__, self._data)


mapper(Parent, parent, properties={
     'children': relation(ParentChild, collection_class=MyMappedCollection),
})
mapper(Child, child)
mapper(ParentChild, parent_child, properties={
    'parent': relation(Parent),
    'child': relation(Child),
})

engine = create_engine('sqlite://')
db = sessionmaker(bind=engine)()
metadata.create_all(bind=engine)

p = Parent()
c1 = Child()
c2 = Child()
c3 = Child()
p.children[1] = c1
p.children[1] = c2
p.children[2] = c3

db.add(p)
db.commit()
p_id = p.parent_id
db.expunge_all()

p = db.query(Parent).get(p_id)
print p.children[1]
print p.children[2]