CODEWITHSUNDEEP
pythonsqlalchemy
user11064812
user11064812

Reputation:

How can I access subclasses from upper class in sqlalchemy?

I have 3 classes;

What I could not do and understand despite reading the document is that;;

class Company(Base):
    __tablename__ = 'company'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    departments = relationship('Department',backref='company')


class Department(Base):
    __tablename__ = 'department'

    id = Column(Integer, primary_key=True)
    department_name = Column(String)

    company_id = Column(Integer, ForeignKey('company.id'))
    departmentalunits = relationship('DepartmentalUnit', backref='department')


class DepartmentalUnit(Base):

    __tablename__ = 'departmentalunit'

    id = Column(Integer, primary_key=True,nullable=False)
    departmental_unit_name = Column(String)

    departments_id = Column(Integer, ForeignKey('department.id'))

The code from which I access the upper classes from the subclasses:

query = session.query(DepartmentalUnit)
instance = query.all()

for i in instance:
    print(i.department.company.name)
    print(i.department.department_name)
    print(i.departmental_unit_name)

The code I can't access other subclasses from the company class:

query = session.query(Company)
instance = query.all()

for i in instance:
    print(i.department.department_name)

Upvotes: 0

Views: 72

Answers (2)

user11064812
user11064812

Reputation:

I solved the problem. I added a backref to relationships and now I can access all of them from the company. Not sure if it's a correct method? However, I am currently getting the return I want. I have no unanswered request yet.

Example solved:

class Company(Base):
    __tablename__ = 'company'

    id = Column(Integer, primary_key=True)
    name = Column(String)

    departments = relationship('Department',backref='company',uselist=False)


class Department(Base):
    __tablename__ = 'department'

    id = Column(Integer, primary_key=True)
    department_name = Column(String)

    company_id = Column(Integer, ForeignKey('company.id'))
    departmentalunits = relationship('DepartmentalUnit', backref='department',uselist=False)

class DepartmentalUnit(Base):

    __tablename__ = 'departmentalunit'

    id = Column(Integer, primary_key=True,nullable=False)
    departmental_unit_name = Column(String)

    departments_id = Column(Integer, ForeignKey('department.id'))


query = session.query(Company)
instance = query.all()

for i in instance:
    print(f"Company: {i.name}")

    print(f"Department: {i.departments.department_name}")

    print(f"Department Unit: {i.departments.departmentalunits.departmental_unit_name}")

    print( f"Report Category : {i.departments.departmentalunits.reportcategoryoftheunit.report_category_name}")

Upvotes: 0

van
van

Reputation: 76992

Your last query should be used differently:

  • there is a typo in the name of the relationship: should be departments instead of department
  • given that the relationship is 1-N, the result is a list, so you should iterate over children.

This should work:

query = session.query(Company)

for company in query.all():
    print(company.name)
    for dep in company.departments:
        print("  ", dep.department_name)
        for dep_unit in dep.departmentalunits:
            print("    ", dep_unit.departmental_unit_name)

Upvotes: 1

Related Questions