CODEWITHSUNDEEP
many-to-manysqlalchemy
EnToutCas
EnToutCas

Reputation: 1357

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

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

Table: Task

Table: Project_Task

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!

Upvotes: 3

Views: 1019

Answers (2)

dvincelli
dvincelli

Reputation: 148

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)

Upvotes: 0

dvincelli
dvincelli

Reputation: 148

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)

Upvotes: 2

Related Questions