python sqlalchemy - map a table to a certain data structure

Question

I have a table defined with relationships and I noticed that even though I don't use joins in my query, the information is still retrieved:

class Employee(Base):
    __tablename__ = "t_employee"

    id = Column(Identifier(20), Sequence('%s_id_seq' % __tablename__), primary_key=True, nullable=False)
    jobs = relationship("EmployeeJob")
    roles = relationship("EmployeeRole")

class EmployeeJob(Base):
    __tablename__ = "t_employee_job"

    id = Column(Integer(20), Sequence('%s_id_seq' % __tablename__), primary_key=True, nullable=False)
    employee_id = Column(Integer(20), ForeignKey('t_employee.id', ondelete="CASCADE"), primary_key=True)
    job_id = Column(Integer(20), ForeignKey('t_job.id', ondelete="CASCADE"), primary_key=True)

class EmployeeRole(Base):
    __tablename__ = "t_employee_role"

    id = Column(Integer(20), Sequence('%s_id_seq' % __tablename__), primary_key=True, nullable=False)
    employee_id = Column(Integer(20), ForeignKey('t_employee.id', ondelete="CASCADE"), nullable=False)
    location_id = Column(Identifier(20), ForeignKey('t_location.id', ondelete="CASCADE"))
    role_id = Column(Integer(20), ForeignKey('t_role.id', ondelete="CASCADE"), nullable=False)

session.query(Employee).all() retrieves also the roles and jobs but does so by querying the db for each row.

I have 2 questions about this situation:
1. In terms of performance I guess I should do the join by myself. Am I correct?
2. How do I map a table to a certain data structure? For example, I want to get the list of employees with their roles where each role should be represented by an Array of location ID and role ID e.g. {id:1, jobs:[1,2,3], roles:[[1,1],[1,2],[2,3]]}

Answer 1

1) Please read Eager Loading from the SA documentation. By default, relationships are loaded lazy on first access to it. In your case, you could use, for example, Joined Load, so that the related rows would be loaded in the same query:

qry = (session.query(Employee).
       options(joinedload(Employee.jobs)).
       options(joinedload(Employee.roles))
      ).all()

If you want those relationships to be always loaded when an Employee is loaded, you can configure the relationship to automatically be loaded:

class Employee(Base):
    # ...
    jobs = relationship("EmployeeJob", lazy="joined")
    roles = relationship("EmployeeRole", lazy="subquery")

2) Just create a method to extract the data structure from your query. Something like below should do it (using qry from first part of the answer):

def get_structure(qry):
    res = [{"id": e.id, 
            "jobs": [j.id for j in e.jobs],
            "roles": [[r.location_id, r.role_id] for r in e.roles],
            }
            for e in qry
            ]
    return res

---

Also note: your EmployeeJob table has funny primary_key, which includes both the id column as well as two ForeignKey columns. I think you should choose either one or the other.

Answer 2

I have finally found a way to accomplish my second issue and decided to answer my own question for the benefit of others:

from sqlalchemy.ext.hybrid import hybrid_property

class Employee(Base):
    __tablename__ = "t_employee"

    id = Column(Identifier(20), Sequence('%s_id_seq' % __tablename__), primary_key=True, nullable=False)
    _jobs = relationship("EmployeeJob", lazy="joined", cascade="all, delete, delete-orphan")
    _roles = relationship("EmployeeRole", lazy="joined", cascade="all, delete, delete-orphan")

    @hybrid_property
    def jobs(self):
        return [item.employee_id for item in self._jobs]

    @jobs.setter
    def jobs(self, value):
        self._jobs = [EmployeeJob(job_id=id) for id in value]

    @hybrid_property
    def roles(self):
        return [[item.location_id, item.role_id] for item in self._roles]

    @roles.setter
    def roles(self, value):
        self._roles = [EmployeeRole(location_id=l_id, role_id=r_id) for l_id, r_id in value]

The cascade in the relationship is to ensure that the orphans are deleted once the list is updated, and the decorators define the getter and setter of each complex property

Thank you van for pointing me to the right direction!