do you know what SQL string you want? I can do this quickly if you can
send me the exact string.
On Tue, Nov 10, 2020, at 1:40 PM, kris wrote:
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/f5bdafe1-7c8e-46e9-9771-c8f56fdccbcan%40googlegroups.com?utm_medium=email&utm_source=footer>.
--
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/f3416778-4d5f-4d1e-9376-f046ec91c034%40www.fastmail.com.
