from sqlalchemy import (MetaData, Table, Column, Integer, String, ForeignKey, create_engine) 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'), mysql_engine='InnoDB') 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")), mysql_engine='InnoDB') class Organization(object): def __init__(self, name): self.name = name class Member(object): def __init__(self, name): 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 lazy="dynamic", # 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 passive_deletes=True, ) }) mapper(Member, member_table) if __name__ == '__main__': engine = create_engine("mysql://scott:tiger@localhost/test", echo=True) meta.create_all(engine) # 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')) sess.add(org) print "-------------------------\nflush one - save org + 3 members\n" sess.commit() # 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_table.c.name.like('%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" sess.commit() # 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. sess.delete(org) print "-------------------------\nflush three - delete org, delete members in one statement\n" sess.commit() print "-------------------------\nno Member rows should remain:\n" print sess.query(Member).count() print "------------------------\ndone. dropping tables." meta.drop_all(engine)