python - 使用带有 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 session /引擎代码，以使其尽可能干净)

我尝试使用´

collection_class=attribute_mapped_collection('number') 

关于 Parent 和 ParentChild 之间的关系，但每个数字只给了我一个 child 。不是里面有列表的字典。任何帮助表示赞赏!

已更新!

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

插入一个 Child 似乎可行

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

但是当我尝试像这样打印 child 时:

print p.children

我收到这个错误:

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

Answer 1

您必须定义自己的集合类。只有 3 个方法可以实现:appender、remover 和 converter。以 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]