SQLAlchemy.Association Proxy.

example1.py

# N to M relationship without association_proxy
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String, Table
from sqlalchemy.orm import backref, Session, relationship
from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('sqlite:///', echo=False)
Base = declarative_base()
session = Session(bind=engine)


association_table = Table('association', Base.metadata,
    Column('parent_id', Integer, ForeignKey('parents.id')),
    Column('child_id', Integer, ForeignKey('children.id'))
)


class Parent(Base):
    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)
    title = Column(String(256))
    children = relationship("Child", secondary=association_table)

    def __repr__(self):
        return 'Parent({0}): {1}'.format(self.title, ','.join([child.title for child in self.children]))


class Child(Base):
    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)
    title = Column(String(256))

    # def __repr__(self):
    #     return self.title


Base.metadata.create_all(engine)

parent = Parent(title='Parent')
parent.children.append(Child(title='Child1'))
parent.children.append(Child(title='Child2'))
parent.children.append(Child(title='Child3'))

session.add(parent)
session.commit()

print(parent)
print(parent.children)
print(parent.children[0])
print(parent.children[0].title)


# $ python example1.py 
# Parent(Parent): Child1,Child2,Child3
# [<__main__.Child object at 0x108f2f250>, <__main__.Child object at 0x108f2f3d0>, <__main__.Child object at 0x108f2f450>]
# <__main__.Child object at 0x108f2f250>
# Child1

example2.py

# N to M relationship with association_proxy
from sqlalchemy import create_engine, ForeignKey, Column, Integer, String, Table
from sqlalchemy.orm import backref, Session, relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

engine = create_engine('sqlite:///', echo=False)
Base = declarative_base()
session = Session(bind=engine)


association_table = Table('association', Base.metadata,
    Column('parent_id', Integer, ForeignKey('parents.id')),
    Column('child_id', Integer, ForeignKey('children.id'))
)


class Parent(Base):
    __tablename__ = 'parents'

    id = Column(Integer, primary_key=True)
    title = Column(String(256))
    _children = relationship("Child", secondary=association_table)

    # proxy the 'title' attribute from the '_children' relationship
    # use Child(title=title) on append() events
    children = association_proxy('_children', 'title', creator=lambda title: Child(title=title))

    def __repr__(self):
        return 'Parent({0}): {1}'.format(self.title, ','.join([child.title for child in self._children]))


class Child(Base):
    __tablename__ = 'children'

    id = Column(Integer, primary_key=True)
    title = Column(String(256))

    # def __repr__(self):
    #     return self.title


Base.metadata.create_all(engine)

parent = Parent(title='Parent')
parent._children.append(Child(title='Child1'))
parent.children.append('Child2')
parent.children.append('Child3')

session.add(parent)
session.commit()

print(parent)
print(parent.children)
print(parent.children[0])

# $ python example2.py 
# Parent(Parent): Child1,Child2,Child3
# [u'Child1', u'Child2', u'Child3']
# Child1