Source code for examples.adjacency_list.adjacency_list
fromsqlalchemyimportColumnfromsqlalchemyimportcreate_enginefromsqlalchemyimportForeignKeyfromsqlalchemyimportIntegerfromsqlalchemyimportStringfromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemy.ormimportbackreffromsqlalchemy.ormimportjoinedload_allfromsqlalchemy.ormimportrelationshipfromsqlalchemy.ormimportSessionfromsqlalchemy.orm.collectionsimportattribute_mapped_collectionBase=declarative_base()classTreeNode(Base):__tablename__="tree"id=Column(Integer,primary_key=True)parent_id=Column(Integer,ForeignKey(id))name=Column(String(50),nullable=False)children=relationship("TreeNode",# cascade deletionscascade="all, delete-orphan",# many to one + adjacency list - remote_side# is required to reference the 'remote'# column in the join condition.backref=backref("parent",remote_side=id),# children will be represented as a dictionary# on the "name" attribute.collection_class=attribute_mapped_collection("name"),)def__init__(self,name,parent=None):self.name=nameself.parent=parentdef__repr__(self):return"TreeNode(name=%r, id=%r, parent_id=%r)"%(self.name,self.id,self.parent_id,)defdump(self,_indent=0):return(" "*_indent+repr(self)+"\n"+"".join([c.dump(_indent+1)forcinself.children.values()]))if__name__=="__main__":engine=create_engine("sqlite://",echo=True)defmsg(msg,*args):msg=msg%argsprint("\n\n\n"+"-"*len(msg.split("\n")[0]))print(msg)print("-"*len(msg.split("\n")[0]))msg("Creating Tree Table:")Base.metadata.create_all(engine)session=Session(engine)node=TreeNode("rootnode")TreeNode("node1",parent=node)TreeNode("node3",parent=node)node2=TreeNode("node2")TreeNode("subnode1",parent=node2)node.children["node2"]=node2TreeNode("subnode2",parent=node.children["node2"])msg("Created new tree structure:\n%s",node.dump())msg("flush + commit:")session.add(node)session.commit()msg("Tree After Save:\n%s",node.dump())TreeNode("node4",parent=node)TreeNode("subnode3",parent=node.children["node4"])TreeNode("subnode4",parent=node.children["node4"])TreeNode("subsubnode1",parent=node.children["node4"].children["subnode3"])# remove node1 from the parent, which will trigger a delete# via the delete-orphan cascade.delnode.children["node1"]msg("Removed node1. flush + commit:")session.commit()msg("Tree after save:\n%s",node.dump())msg("Emptying out the session entirely, selecting tree on root, using ""eager loading to join four levels deep.")session.expunge_all()node=(session.query(TreeNode).options(joinedload_all("children","children","children","children")).filter(TreeNode.name=="rootnode").first())msg("Full Tree:\n%s",node.dump())msg("Marking root node as deleted, flush + commit:")session.delete(node)session.commit()