I'm looking for a way to create hierarchy in form of child parent relationship between two or more instances of same class.

How would one go about creating such objects from nested dictionary like in example ? Is this even possible ? Is there some other way which would be recommended to do such task?

# -*- coding: utf-8 -*-from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, exists
from sqlalchemy.orm import relationship, sessionmaker
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, StringBase = declarative_base()class Person(Base):__tablename__ = 'person';id = Column(Integer, primary_key=True)name = Column(String, nullable=False)parent_id = Column(Integer, ForeignKey('person.id'))def __init__(self, **kwargs):self.parent_id = kwargs.get('parent_id', None)self.name = kwargs.get('name')self.team = kwargs.get('team', [])# Is it possible to create more object of this type# and establish that their parent_id is ID of this object?def __repr__(self):return """ID: {}Name: {}ParentID: {}""".format(self.id, self.name, self.parent_id)engine = create_engine('sqlite:///db.sqlite3')
Base.metadata.create_all(engine)
connection = engine.connect()
Session = sessionmaker(bind=engine)
session = Session()alice = {'name' : 'Alice'}
bob = {'name' : 'Bob', 'team' : [alice, ]}p1 = Person(bob)
session.add(p1)
session.commit()

I understand the iterative approach where I would first create parent object, then iterate over possible children and create them. I'm curious if there is a way to do this inside constructor rather than from 'outside' with loops.

Try this.

#your import statements including "relationship"Base = declarative_base()class Person(Base):__tablename__ = 'person'id = Column(Integer, primary_key=True)name = Column(String, nullable=False)parent_id = Column(Integer, ForeignKey('person.id'))team = relationship("Person")  def __init__(self, **kwargs):self.parent_id = kwargs.get('parent_id', None)self.name = kwargs.get('name')team_kwargs = kwargs.get('team', [])for member_kwargs in team_kwargs:new_person = Person(**member_kwargs)new_person.parent_id = self.idself.team.append(new_person)# Is it possible to create more object of this type# and establish that their parent_id is ID of this object?def __repr__(self):return """ID: {}Name: {}ParentID: {}""".format(self.id, self.name, self.parent_id)engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
connection = engine.connect()
Session = sessionmaker(bind=engine)
session = Session()alice = {'name' : 'Alice'}
joe = {'name' : 'Joe'}
anne = {'name' : 'Anne', 'team': [alice]}
bob = {'name' : 'Bob', 'team' : [anne, joe]}p1 = Person(**bob) session.add(p1)   
session.commit()for person in session.query(Person).all():print(person)

output:

    ID: 1Name: BobParentID: NoneID: 2Name: AnneParentID: 1ID: 3Name: JoeParentID: 1ID: 4Name: AliceParentID: 2

when i ran this on a saved database, (engine = create_engine('sqlite:///delme.db'), and ran it multiple times, it created all the entries on a single add and commit.

Different Approach

You could also make a separate "teams" table that stores team leader and team members

# your imports and "from sqlalchemy import create_engine, Table"Base = declarative_base()teams = Table("teams", Base.metadata,Column("leader", Integer, ForeignKey("person.id"), primary_key=True),Column("member", Integer, ForeignKey("person.id"), primary_key=True),)class Person(Base):__tablename__ = 'person'id = Column(Integer, primary_key=True)name = Column(String, nullable=False)team = relationship("Person",secondary=teams,primaryjoin=id==teams.c.leader,secondaryjoin=id==teams.c.member,)def __init__(self, **kwargs):self.name = kwargs.get('name')team_input = kwargs.get('team', [])for member in team_input:new_person = Person(**member)self.team.append(new_person)def __repr__(self):return "ID: {}  Name: {}".format(self.id, self.name)engine = create_engine('sqlite://')
Base.metadata.create_all(engine)
connection = engine.connect()
Session = sessionmaker(bind=engine)
session = Session()alice = {'name' : 'Alice'}
joe = {'name' : 'Joe'}
anne = {'name' : 'Anne', 'team': [alice]}
bob = {'name' : 'Bob', 'team' : [anne, joe]}p1 = Person(**bob)
session.add(p1)
session.commit()for person in session.query(Person).all():print(person)for team in session.query(teams).all():print(team)

output:

ID: 1  Name: Bob
ID: 2  Name: Anne
ID: 3  Name: Alice
ID: 4  Name: Joe
(1, 2)  # anne and joe are on bob's team
(1, 4)
(2, 3)  # alice is on anne's team