diff options
Diffstat (limited to 'examples/materialized_paths/materialized_paths.py')
| -rw-r--r-- | examples/materialized_paths/materialized_paths.py | 78 |
1 files changed, 49 insertions, 29 deletions
diff --git a/examples/materialized_paths/materialized_paths.py b/examples/materialized_paths/materialized_paths.py index 4ded90f7e..45ae0c193 100644 --- a/examples/materialized_paths/materialized_paths.py +++ b/examples/materialized_paths/materialized_paths.py @@ -44,21 +44,35 @@ class Node(Base): # To find the descendants of this node, we look for nodes whose path # starts with this node's path. descendants = relationship( - "Node", viewonly=True, order_by=path, - primaryjoin=remote(foreign(path)).like(path.concat(".%"))) + "Node", + viewonly=True, + order_by=path, + primaryjoin=remote(foreign(path)).like(path.concat(".%")), + ) # Finding the ancestors is a little bit trickier. We need to create a fake # secondary table since this behaves like a many-to-many join. - secondary = select([ - id.label("id"), - func.unnest(cast(func.string_to_array( - func.regexp_replace(path, r"\.?\d+$", ""), "."), - ARRAY(Integer))).label("ancestor_id") - ]).alias() - ancestors = relationship("Node", viewonly=True, secondary=secondary, - primaryjoin=id == secondary.c.id, - secondaryjoin=secondary.c.ancestor_id == id, - order_by=path) + secondary = select( + [ + id.label("id"), + func.unnest( + cast( + func.string_to_array( + func.regexp_replace(path, r"\.?\d+$", ""), "." + ), + ARRAY(Integer), + ) + ).label("ancestor_id"), + ] + ).alias() + ancestors = relationship( + "Node", + viewonly=True, + secondary=secondary, + primaryjoin=id == secondary.c.id, + secondaryjoin=secondary.c.ancestor_id == id, + order_by=path, + ) @property def depth(self): @@ -70,38 +84,44 @@ class Node(Base): def __str__(self): root_depth = self.depth s = [str(self.id)] - s.extend(((n.depth - root_depth) * " " + str(n.id)) - for n in self.descendants) + s.extend( + ((n.depth - root_depth) * " " + str(n.id)) + for n in self.descendants + ) return "\n".join(s) def move_to(self, new_parent): new_path = new_parent.path + "." + str(self.id) for n in self.descendants: - n.path = new_path + n.path[len(self.path):] + n.path = new_path + n.path[len(self.path) :] self.path = new_path if __name__ == "__main__": - engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True) + engine = create_engine( + "postgresql://scott:tiger@localhost/test", echo=True + ) Base.metadata.create_all(engine) session = Session(engine) print("-" * 80) print("create a tree") - session.add_all([ - Node(id=1, path="1"), - Node(id=2, path="1.2"), - Node(id=3, path="1.3"), - Node(id=4, path="1.3.4"), - Node(id=5, path="1.3.5"), - Node(id=6, path="1.3.6"), - Node(id=7, path="1.7"), - Node(id=8, path="1.7.8"), - Node(id=9, path="1.7.9"), - Node(id=10, path="1.7.9.10"), - Node(id=11, path="1.7.11"), - ]) + session.add_all( + [ + Node(id=1, path="1"), + Node(id=2, path="1.2"), + Node(id=3, path="1.3"), + Node(id=4, path="1.3.4"), + Node(id=5, path="1.3.5"), + Node(id=6, path="1.3.6"), + Node(id=7, path="1.7"), + Node(id=8, path="1.7.8"), + Node(id=9, path="1.7.9"), + Node(id=10, path="1.7.9.10"), + Node(id=11, path="1.7.11"), + ] + ) session.flush() print(str(session.query(Node).get(1))) |