1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
|
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)
|
