from sqlalchemy import MetaData, Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import Session, mapper, relationship
from sqlalchemy.orm.collections import collection


# container code from
# http://groups.google.com/group/sqlalchemy/browse_thread/thread/500b76c80ab5336
class ContainerOfA(dict):
    __emulates__ = set

    def __init__(self):
        self._field = "I'm a great... awesom! container"

    #I also defined the appender, remover and iterator
    @collection.iterator
    def __iter__(self):
        return self.itervalues()

    @collection.appender
    def append(self, item):
        self[id(item)] = item

    @collection.remover
    def remove(self, item):
        if id(item) in self.keys():
            del self[id(item)]


class ContainerOfB(ContainerOfA):
    def __init__(self):
        super(ContainerOfB, self).__init__()


# map it up
metadata = MetaData('sqlite:///')

parents_table = Table('parents', metadata,
                     Column('id', Integer, primary_key=True),
                     Column('name', String(60)))
children_table = Table('children', metadata,
                    Column('id', Integer, primary_key=True),
                    Column('parent_id', Integer, ForeignKey('parents.id')),
                    Column('name', String(60)))
metadata.create_all()


class Parent(object):
    def __init__(self, name=None):
        self.name = name


class Child(object):
    def __init__(self, name=None):
        self.name = name

mapper(Parent, parents_table, properties={
    'children': relationship(Child, backref='parent',
                         collection_class=ContainerOfB),
    })
mapper(Child, children_table)


# use that appender
s = Session()
p = Parent('p1')
assert type(p.children) is ContainerOfB
p.children.append(Child('c1'))
p.children.append(Child('c2'))
p.children.append(Child('c3'))
s.add(p)
s.commit()
s.expunge_all()

p = s.query(Parent).filter_by(name='p1').first()
print p.children