python sqlalchemy - map a table to a certain data structure -

i have table defined relationships , noticed though don't use joins in query, information 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 roles , jobs querying db each row.

i have 2 questions situation:
1. in terms of performance guess should join myself. correct?
2. how map table data structure? example, want list of employees roles each role should represented array of location id , role id e.g. {id:1, jobs:[1,2,3], roles:[[1,1],[1,2],[2,3]]}

1) please read eager loading sa documentation. default, relationships loaded lazy on first access it. in case, use, example, joined load, related rows loaded in same query:

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

if want relationships loaded when employee loaded, can configure relationship automatically loaded:

class employee(base):     # ...     jobs = relationship("employeejob", lazy="joined")     roles = relationship("employeerole", lazy="subquery") 

2) create method extract data structure query. below should (using qry first part of answer):

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

---

also note: employeejob table has funny primary_key, includes both id column 2 foreignkey columns. think should choose either 1 or other.