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.

• Ofir: Thank you van! The lazy attribute worked like a charm ... Regarding the second part, I want this mapping to be effective also for save operations (meaning that i'll pass {roles:[[1,1]]} to merge and it will know how to handle it). Is this possible? Regarding the EmployeeJob, you are right, the foreign keys are unique but not primary.
• van: Just create methods (par of the Employee class) to do what you describe in words. You would need to look up (query) the referenced objects by their ids and add/del/update relationships using your dictionary.
• Ofir: I guess this could work. Does this mean there is no inherent ability to do so in sqlalchemy?

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!