The example is from adjecency_list.py example.
The last three lines construct a recursive CTE to walk to the parent from
a found node.
I cannot figure out how to return TreeNodes vs tuples.
Thanks
================================================
from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import ForeignKey
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref
from sqlalchemy.orm import joinedload_all
from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session
from sqlalchemy.orm.collections import attribute_mapped_collection
Base = declarative_base()
class TreeNode(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="all, delete-orphan",
backref=backref("parent", remote_side=id),
collection_class=attribute_mapped_collection("name"),
)
def __init__(self, name, parent=None):
self.name = name
self.parent = parent
def __repr__(self):
return "TreeNode(name=%r, id=%r, parent_id=%r)" % (
self.name,
self.id,
self.parent_id,
)
def dump(self, _indent=0):
return (
" " * _indent
+ repr(self)
+ "\n"
+ "".join([c.dump(_indent + 1) for c in self.children.values()])
)
if __name__ == "__main__":
engine = create_engine("sqlite://", echo=True)
def msg(msg, *args):
msg = msg % args
print("\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"] = node2
TreeNode("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"])
# Want to return contents of CTE as TreeNode
node = session.query(TreeNode).filter(TreeNode.name ==
"subnode4").cte('parents')
parents = node.union(session.query(TreeNode).filter(TreeNode.id ==
node.c.parent_id))
print session.query(parents).all()
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/f5bdafe1-7c8e-46e9-9771-c8f56fdccbcan%40googlegroups.com.
