from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey,
from sqlalchemy.orm import (mapper, relationship, sessionmaker)
meta = MetaData()
org_table = Table('organizations', meta,
    Column('org_id', Integer, primary_key=True),
    Column('org_name', String(50), nullable=False, key='name'),
member_table = Table('members', meta,
    Column('member_id', Integer, primary_key=True),
    Column('member_name', String(50), nullable=False, key='name'),
    Column('org_id', Integer,
                    ForeignKey('organizations.org_id', ondelete="CASCADE")),
class Organization(object):
    def __init__(self, name): = name
class Member(object):
    def __init__(self, name): = name
mapper(Organization, org_table, properties = {
    'members' : relationship(Member,
        # Organization.members will be a Query object - no loading
        # of the entire collection occurs unless requested
        # Member objects "belong" to their parent, are deleted when
        # removed from the collection
        cascade="all, delete-orphan",
        # "delete, delete-orphan" cascade does not load in objects on delete,
        # allows ON DELETE CASCADE to handle it.
        # this only works with a database that supports ON DELETE CASCADE -
        # *not* sqlite or MySQL with MyISAM
mapper(Member, member_table)
if __name__ == '__main__':
    engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
    # expire_on_commit=False means the session contents
    # will not get invalidated after commit.
    sess = sessionmaker(engine, expire_on_commit=False)()
    # create org with some members
    org = Organization('org one')
    org.members.append(Member('member one'))
    org.members.append(Member('member two'))
    org.members.append(Member('member three'))
    print "-------------------------\nflush one - save org + 3 members\n"
    # the 'members' collection is a Query.  it issues
    # SQL as needed to load subsets of the collection.
    print "-------------------------\nload subset of members\n"
    members = org.members.filter('%member t%')).all()
    print members
    # new Members can be appended without any
    # SQL being emitted to load the full collection
    org.members.append(Member('member four'))
    org.members.append(Member('member five'))
    org.members.append(Member('member six'))
    print "-------------------------\nflush two - save 3 more members\n"
    # delete the object.   Using ON DELETE CASCADE
    # SQL is only emitted for the head row - the Member rows
    # disappear automatically without the need for additional SQL.
    print "-------------------------\nflush three - delete org, delete members in one statement\n"
    print "-------------------------\nno Member rows should remain:\n"
    print sess.query(Member).count()
    print "------------------------\ndone.  dropping tables."