private_ordered_relation.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, create_engine
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.orderinglist import ordering_list

Base = declarative_base()


class Parent(Base):
    __tablename__ = 'parents'
    id = Column(Integer, primary_key=True)
    children = relationship(
                'Child', cascade='all,delete-orphan',
                collection_class=ordering_list('order_position'),
                order_by='Child.order_position')
    def __repr__(self):
        return '<{} id={} children={}>'.format(
                type(self).__name__, repr(self.id), repr(self.children))

class Child(Base):
    __tablename__ = 'children'
    parent_id = Column(ForeignKey(Parent.id), nullable=False, primary_key=True)
    order_position = Column(Integer, nullable=False, primary_key=True,
                            autoincrement=False)
    data = Column(String)
    def __repr__(self):
        return '<{} parent_id={} order_position={} data={}>'.format(
                type(self).__name__, repr(self.parent_id),
                repr(self.order_position), repr(self.data))


engine = create_engine('sqlite://')#, echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(bind=engine)()
p = Parent(children=[Child(data='a'), Child(data='b'), Child(data='c')])
session.add(p)
session.commit()
print p
# <Parent id=1 children=[<Child parent_id=1 order_position=0 data=u'a'>, <Child parent_id=1 order_position=1 data=u'b'>, <Child parent_id=1 order_position=2 data=u'c'>]>
# OK

p.children = [Child(data='d')]
session.commit()
print p
# Possible results:
# <Parent id=1 children=[<Child parent_id=1 order_position=0 data=u'd'>, <Child parent_id=1 order_position=2 data=u'c'>]>
# Not OK: must be one child
# <Parent id=1 children=[<Child parent_id=1 order_position=1 data=u'b'>]>
# Not OK: must be order_position=0 and data='d'
# Parent id=1 children=[<Child parent_id=1 order_position=2 data=u'c'>]>
# Not OK: must be order_position=0 and data='d'

p.children = [Child(data='e')]
session.commit()
print p
# Possible results:
# <Parent id=1 children=[<Child parent_id=1 order_position=2 data=u'c'>]>
# Not OK: must be order_position=0 and data='e'