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)
|