当前位置: 动力学知识库 > 问答 > 编程问答 >

python - sqlalchemy relation through another (declarative)

问题描述:

Is anyone familiar with ActiveRecord's "has_many :through" relations for models? I'm not really a Rails guy, but that's basically what I'm trying to do.

As a contrived example consider Projects, Programmers, and Assignments:

from sqlalchemy import create_engine

from sqlalchemy.orm import sessionmaker

from sqlalchemy import Column, ForeignKey

from sqlalchemy.types import Integer, String, Text

from sqlalchemy.orm import relation

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Assignment(Base):

__tablename__ = 'assignment'

id = Column(Integer, primary_key=True)

description = Column(Text)

programmer_id = Column(Integer, ForeignKey('programmer.id'))

project_id = Column(Integer, ForeignKey('project.id'))

def __init__(self, description=description):

self.description = description

def __repr__(self):

return '<Assignment("%s")>' % self.description

class Programmer(Base):

__tablename__ = 'programmer'

id = Column(Integer, primary_key=True)

name = Column(String(64))

assignments = relation("Assignment", backref='programmer')

def __init__(self, name=name):

self.name = name

def __repr__(self):

return '<Programmer("%s")>' % self.name

class Project(Base):

__tablename__ = 'project'

id = Column(Integer, primary_key=True)

name = Column(String(64))

description = Column(Text)

assignments = relation("Assignment", backref='project')

def __init__(self, name=name, description=description):

self.name = name

self.description = description

def __repr__(self):

return '<Project("%s", "%s...")>' % (self.name, self.description[:10])

engine = create_engine('sqlite://')

Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)

session = Session()

Projects have many Assignments.

Programmers have many Assignments. (understatement?)

But in my office at least, Programmers also have many Projects - I'd like this relationship to be inferred through the Assignments assigned to the Programmer.

I'd like the Programmer model to have a attribute "projects" which will return a list of Projects associated to the Programmer through the Assignment model.

me = session.query(Programmer).filter_by(name='clay').one()

projects = session.query(Project).\

join(Project.assignments).\

join(Assignment.programmer).\

filter(Programmer.id==me.id).all()

How can I describe this relationship clearly and simply using the sqlalchemy declarative syntax?

Thanks!

网友答案:

There are two ways I see:

  1. Define a relation Programmer.projects with secondary='assignment'.

  2. I define Assignment.project as relation and Programmer.projects as association_proxy('assignments', 'project') (probably you'd also like to define a creator). See Simplifying Association Object Relationships chapter for more information.

分享给朋友:
您可能感兴趣的文章:
随机阅读: