使用中间表通过SQLAlchemy将属性作为字典列表

Question

我想问的是关于SQLAlchemy的事情，但我发现用语言表达起来有点困难，所以我决定用一个简单的例子来说明我想要实现的目标：

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')

这段代码会在parent_child表中创建3行数据，第一行的列数是0，第二和第三行的列数都是10。

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

（为了让例子尽量简洁，我省略了所有SQLAlchemy的会话和引擎代码）

我尝试使用´

collection_class=attribute_mapped_collection('number') 

在Parent和ParentChild之间建立关系，但结果是每个数字只给了我一个子项，而不是一个包含列表的字典。希望能得到一些帮助！

更新！

多亏了Denis Otkidach，我现在有了这段代码，但它仍然不工作。

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),
})

插入一个子项似乎是可以的

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

但是当我尝试这样打印子项时：

print p.children

我得到了这个错误：

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

Answer 1

你需要自己定义一个集合类。只需要实现三个方法：添加、移除和转换。可以参考一下 sqlalchemy.orm.collections.MappedCollection 这个例子。

更新：这里有一个简单粗暴的实现，符合你的要求：

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]