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]]}

van · Accepted Answer · 2013-03-04 10:58:33Z

2

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.

edited Mar 4, 2013 at 10:58

answered Mar 4, 2013 at 9:45

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ofir Over a year ago

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 Over a year ago

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 Over a year ago

I guess this could work. Does this mean there is no inherent ability to do so in sqlalchemy?

Ofir · Accepted Answer · 2013-03-07 12:16:57Z

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!

Collectives™ on Stack Overflow

python sqlalchemy - map a table to a certain data structure

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related