I am trying to create a table attribute via a relationship. It needs to be
ready-only and return the nodes (peer) where which have at least one common
left node (predecessor). I am able to construct a query (shown below at the
bottom) but I am having difficultly working out where I am going wrong when
translating it into a relationship (if it is at all possible). Below is
some code to demonstrate what I am trying to achieve. Many thanks.
from sqlalchemy import (select, create_engine, Integer, ForeignKey, String,
Column, Table)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, sessionmaker
import random
Base = declarative_base()
node_to_node = Table("node_to_node", Base.metadata,
Column("left_node_id", Integer,
ForeignKey("node.id"), primary_key=True),
Column("right_node_id", Integer, ForeignKey("node.id"),
primary_key=True)
)
class Node(Base):
__tablename__ = 'node'
id = Column(Integer, primary_key=True)
label = Column(String)
right_nodes = relationship("Node",
secondary=node_to_node,
primaryjoin=id==node_to_node.c.left_node_id,
secondaryjoin=id==node_to_node.c.right_node_id,
backref= "left_nodes"
)
# This is my attempt to create a sibling relationship based on a query below
sibling_nodes = relationship("Node",
secondary=node_to_node,
primaryjoin=id==node_to_node.c.right_node_id,
secondaryjoin=node_to_node.c.left_node_id.in_(
select([node_to_node.c.left_node_id]).where(
node_to_node.c.right_node_id ==
id)),
viewonly=True,
)
def __repr__(self):
return "({0}) {1}".format(self.id, self.label)
engine = create_engine('sqlite:///:memory:', echo=False)
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()
predecessors = ["one", "two", "three", "four", "five"]
peers = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"]
successors = ["1", "2", "3", "4", "5"]
session.add_all([Node(label=l) for l in predecessors + peers + successors])
for p in peers:
r = session.query(Node).filter(Node.label == p).first()
r.left_nodes = session.query(Node).filter(Node.label.in_(
random.sample(predecessors, 1))).all()
session.commit()
for s in successors:
r = session.query(Node).filter(Node.label == s).first()
r.left_nodes = session.query(Node).filter(Node.label.in_(
random.sample(peers, 1))).all()
session.commit()
# The query reporting to p is the result I want and I have tried
unsuccessfully to translate it into a relationship
# see above sibling_nodes in Node class
for l in peers:
r = session.query(Node.id).filter(Node.label == l).scalar()
p = session.query(Node).filter(node_to_node.c.right_node_id ==
Node.id).filter(node_to_node.c.left_node_id.in_(
session.query(node_to_node.c.left_node_id).filter(
node_to_node.c.right_node_id ==
r))).all()
print(p)
#
=============================================================================
# SELECT node.id AS node_id, node.label AS node_label
# FROM node, node_to_node
# WHERE node_to_node.right_node_id = node.id AND node_to_node.left_node_id
IN
# (SELECT node_to_node.left_node_id AS node_to_node_left_node_id
# FROM node_to_node
# WHERE node_to_node.right_node_id = ?)
#
=============================================================================
--
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 post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
