SQLalchemy with multiple object hierarchy

Question

I'm trying to build a hierarchy of objects using SQLAlchemy in the Pyramid framework. I have a working hierarchy set up - currently a C object has B as its parent, which has A as its parent.

But I need to change it so that model B can have A, B, or C as it's parent, etc. I tried using an Association table but the foreign keys used in it also links to only one type of object. I'd also like to keep the current Children and Parent relationship attributes.

This is my current models.py file:

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()

class Root(Base):
    __tablename__ = 'Root'
    ID = Column(Integer, primary_key=True)

    Children = relationship("A",
                        backref='Parent',
                        cascade="all, delete, delete-orphan")


class A(Base):
    def getID():
        return uuid.uuid1().hex

    __tablename__ = 'A'
    ID = Column(Text, primary_key=True, default=getID)
    ParentID = Column(Integer, ForeignKey('Root.ID'))

    Children = relationship("B",
                            backref='Parent',
                            cascade="all, delete, delete-orphan")

class B(Base):
    def getID():
        return uuid.uuid1().hex

    __tablename__ = 'B'
    ID = Column(Text, primary_key=True, default=getID)
    ParentID = Column(Integer, ForeignKey('A.ID'))
                      cascade="all, delete, delete-orphan")

    Children = relationship("C",
                            backref='Parent',
                            cascade="all, delete, delete-orphan")


class C(Base):
    def getID():
        return uuid.uuid1().hex

    __tablename__ = 'C'
    ID = Column(Text, primary_key=True, default=getID)
    Name = Column(Text)
    ParentID = Column(Integer, ForeignKey('B.ID'))

    Children = []

Therefore my end goal is to have a hierarchy where any node can have any number of children of any type A, B, C.

Note: I used uuid for the primary key ID so that each ID in the entire hierarchy is unique.

Hi van, I haven't tried that out, but in the article it says: A tricky case comes up when two subclasses want to specify the same column ... the start_date column declared on both Engineer and Manager will result in an error ... Declarative can’t be sure of the intent link — Niel
– Niel, Commented Feb 25, 2015 at 22:08
Yes, but it only applies to Single Table Inheritance, which is not your case (either Joined or Concrete) — van
– van, Commented Feb 26, 2015 at 5:01

Niel · Accepted Answer · 2015-03-02 10:01:57Z

Thanks to van and using Inheritance, here is my solution if it could help anyone else:

from sqlalchemy import (
    Column,
    Integer,
    Text,
    ForeignKey,
)

from sqlalchemy.orm import (
    scoped_session,
    sessionmaker,
    relationship,
    backref,
)

import uuid
from sqlalchemy.ext.declarative import declarative_base
from zope.sqlalchemy import ZopeTransactionExtension

DBSession = scoped_session(sessionmaker(extension=ZopeTransactionExtension()))
Base = declarative_base()

class Node(Base):
    """
    An object representing a node in the hierarchy.
    All the other objects inherit from Node.
    """

    def getID():
        return uuid.uuid1().hex

    __tablename__ = 'Node'
    ID = Column(Text, primary_key=True, default=getID)
    ParentID = Column(Text, ForeignKey('Node.ID'))
    type = Column(Text(50))

    Children = relationship("Node",
                backref=backref('Parent', remote_side=[ID], uselist=False)
            )

    __mapper_args__ = {
        'polymorphic_identity':'Node',
        'polymorphic_on':type
    }


 class A(Node):
    __tablename__ = 'A'
    ID = Column(Text, ForeignKey('Node.ID'), primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity':'A',
    }

class B(Node):
    __tablename__ = 'B'
    ID = Column(Text, ForeignKey('Node.ID'), primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity':'B',
    }

class C(Node):
    __tablename__ = 'C'
    ID = Column(Text, ForeignKey('Node.ID'), primary_key=True)

    __mapper_args__ = {
        'polymorphic_identity':'C',
    }

Collectives™ on Stack Overflow

SQLalchemy with multiple object hierarchy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related