I really appreciate the help.. I've been stuck a while on this.
I am including the SQL string which may help, but to elaborate
I would like to receive ORM objects that I can further operate on.
>From above, the line
print session.query(parents).all()
prints
[(9, 7, u'subnode4'), (7, 1, u'node4'), (1, None, u'rootnode')]
which are tuples ...
but I want convert to convert the tuple into TreeNodes. I tried
session.query (TreeNode).from_statement (parents.select()).all()
but get
sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for
column 'tree.id'"
I actually want to build a more complex express using multiple CTE to walk
up and down a tree creating union of following the parent pointer and
finding all the children
of a particular node. I would then like to filter and order the TreeNodes
based on some other criteria.
It appears I lose track of the type "TreeNode" after the "union" and cannot
get it back.
For completeness here is the fragment I am generating.
WITH RECURSIVE parents AS
(SELECT tree.id AS id, tree.parent_id AS parent_id, tree.name AS name
FROM tree
WHERE tree.name = 'subnode4'
UNION SELECT tree.id AS tree_id, tree.parent_id AS tree_parent_id,
tree.name AS tree_name
FROM tree, parents
WHERE tree.id = parents.parent_id),
kids AS
(SELECT tree.id AS id, tree.parent_id AS parent_id, tree.name AS name
FROM tree
WHERE tree.name = 'subnode4'
UNION SELECT tree.id AS tree_id, tree.
AS tree_parent_id, tree.name AS tree_name
FROM tree, kids
WHERE tree.parent_id = kids.id)
SELECT parents.id, parents.parent_id, parents.name
FROM parents
UNION
SELECT kids.id, kids.parent_id, kids.name
FROM kids
BUT I want to end up with TreeNode.. Not Tuples.
Thanks again.
On Tuesday, November 10, 2020 at 11:05:54 AM UTC-8 Mike Bayer wrote:
> 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 sqlalchemy+...@googlegroups.com.
>
> 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 sqlalchemy+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/617fc823-ecbf-42a9-9581-28623c747fccn%40googlegroups.com.
