sqlalchemy: keep column values from the association table in a many-to-many relationship

Question

Suppose I have a project table and a task table. A project may have many tasks and a task may be assigned to multiple projects. I have an association table project_task, which has the mapping between projects and tasks but also an extra column of rate, which is used to record project-specific task rate.

Table: Project

• projectid
• name
• description

Table: Task

• taskid
• name
• description
• rate <- default rate

Table: Project_Task

• projectid
• taskid
• rate <- project-specific rate

How would I map this relationship in Sqlalchemy? My goal is that project.tasks should give me a list of task objects associated with the project with task.rate set to the rate recorded in project_task table.

Thanks a bunch!

Answer 1

With a table join in the mapping:

import sqlalchemy

from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import DECIMAL
from sqlalchemy import Unicode
from sqlalchemy import Text
from sqlalchemy import ForeignKey

from sqlalchemy.sql import join

from sqlalchemy.orm import relation
from sqlalchemy.orm import column_property
from sqlalchemy.orm import create_session

from sqlalchemy.ext.declarative import declarative_base

engine = sqlalchemy.create_engine('sqlite:///stackoverflow_6144557.db', echo = True)

Base = declarative_base(bind=engine)

class ProjectTask(Base):
    __tablename__ = 'project_task'

    projectid = Column(Integer, ForeignKey('project.projectid'), primary_key = True)
    taskid = Column(Integer, ForeignKey('task.taskid'), primary_key = True)
    project_rate = Column('rate', DECIMAL(12, 4))

class Task(Base):
    __tablename__ = 'task'

    taskid = Column(Integer, primary_key = True)
    name = Column(Unicode(255))
    description = Column(Text)
    rate = Column(DECIMAL(12, 4))

class Project(Base):
    __tablename__ = 'project'

    projectid = Column(Integer, primary_key = True)
    name = Column(Unicode(255))
    description = Column(Text)
    tasks = relation("ExtendedProjectTask", backref = "project", lazy = 'joined')

class ExtendedProjectTask(Base):
    __table__ = join(ProjectTask.__table__, Task.__table__)

    projectid = column_property(ProjectTask.projectid)
    taskid = column_property(Task.taskid, ProjectTask.taskid)
    name = column_property(Task.name)
    description = column_property(Task.description)
    task_rate = column_property(Task.rate)
    project_rate = column_property(ProjectTask.project_rate)

    @property
    def rate(self):
        if self.project_rate is None:
            return self.task_rate
        else:
            return self.project_rate

if __name__ == '__main__':
    Base.metadata.create_all(engine)
    session = create_session(engine)
    for project in session.query(Project).all():
        print "\n%r, %r, %r" % (project.projectid, project.name, project.description)
        for task in project.tasks:
            print "\t%r, %r, %r, %r" % (task.taskid, task.name, task.description, task.rate)