I'm trying to create a self-referential many-to-many relationship table with an extra column called sort_order:
from sqlalchemy import Integer, ForeignKey, String, Column, Table
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy.ext.associationproxy import association_proxy
from sqlalchemy.ext.orderinglist import ordering_list
Base = declarative_base()
class Node2Node(Base):
__tablename__ = "NodeToNode"
left_node_id = Column(Integer, ForeignKey("Node.id"), primary_key=True)
right_node_id = Column(Integer, ForeignKey("Node.id"), primary_key=True)
sort_order = Column(Integer, nullable=False)
class Node(Base):
__tablename__ = "Node"
id = Column(Integer, primary_key=True)
label = Column(String)
# Many To Many with Node
right_nodes = relationship(
"Node",
secondary="NodeToNode",
primaryjoin="Node.id==NodeToNode.c.left_node_id",
secondaryjoin="Node.id==NodeToNode.c.right_node_id",
backref="left_nodes",
order_by="NodeToNode.c.sort_order",
)
def __repr__(self):
return f"<Node: {self.label}>"
When I run the following script I get an error saying NOT NULL constraint failed: NodeToNode.sort_order:
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
engine = create_engine("sqlite:///practice.sqlite3")
session = Session(bind=engine)
# Build tables
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
# Load in data
nodes = [
Node(label="A"),
Node(label="B"),
Node(label="C"),
Node(label="D"),
Node(label="E"),
]
session.add_all(nodes)
session.commit()
nodes[0].right_nodes = [nodes[3], nodes[1], nodes[2]]
nodes[0].left_nodes = [nodes[4]]
session.add_all(nodes)
session.commit()
# Display data
print("Nodes: {}".format(nodes))
print("Node A right nodes: {}".format(nodes[0].right_nodes))
print("Node A left nodes: {}".format(nodes[0].left_nodes))
session.close()
How can I set this sort_order key based on the item's index in the list?
