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_ta开发者_如何转开发sk, 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!

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)

How about this? I am making ProjectTask delegate to Task for task attributes. I only setup a get property but you can easily add in set and delete (and you can easily think of a way to do that generically).

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.orm import relation
from sqlalchemy.orm import create_session

from sqlalchemy.ext.declarative import declarative_base

engine = sqlalchemy.create_engine('sqlite:///stackoverflow_6144557.db')
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))
    task = relation("Task", backref="project_tasks", lazy="joined")

    @property
    def name(self):
        return self.task.name

    @property
    def description(self):
        return self.task.description

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

class Project(Base):
    __tablename__ = 'project'

    projectid = Column(Integer, primary_key=True)
    name = Column(Unicode(255))
    description = Column(Text)

    tasks = relation(ProjectTask, backref="project", lazy="joined")

class Task(Base):
    __tablename__ = 'task'

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


Base.metadata.create_all(engine)

if __name__ == '__main__':
    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)