Geek asks and answers

Sqlalchemy: store column values ​​from association table in many-to-many relationships

Suppose I have a project table and a task table. A project can have many tasks, and a task can be assigned to several projects. I have a project_task association table that has a mapping between projects and tasks, but also an extra column ratethat is used to record the user-defined task speed.

Table: Project

  • Projectid
  • name
  • Description

Table: Task

  • Taskid
  • name
  • Description
  • rate <- default rate

Table: Project_Task

  • Projectid
  • Taskid
  • rate <- unit cost of the project

Sqlalchemy? , project.tasks , , task.rate, , project_task.

, !

+3
2

:

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)
+2

? ProjectTask . get, set delete ( ).

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)
0

More articles:


All Articles