Referencing a related object in Pyramids/Python/SQLAlchemy

Question

I'm not sure how to title this question. I've also simplified my code so it's easier to ask. Say I have the following code in myproject.models in Pyramid:

class Links(Base):
    __tablename__ = 'links'
    id = Column(Integer, primary_key=True)
    link = Column(Text)

    def __init__(self, link):
        self.link = link

class Submissions(Base):
    __tablename__ = 'submissions'
    id = Column(Integer, primary_key=True)
    title = Column(Text)
    link_id = Column(Integer, ForeignKey('links.id'))
    link = relationship(Links)

    def __init__(self, title, link):  
    self.title = title
        self.link = link

The view will be very simple:

def my_view(request):
    dbsession = DBSession()
    submissions = dbsession.query(Submissions)
    return {'submissions':submissions}

I want to return this on my page using Chameleon:

<p tal:repeat="thing submissions">
    ${thing.title} ${thing.link}
</p>

However, ${thing.link} doesn't show the link of the site.

Questions:

How do I reference thing.link's link? Intuitively, I would type ${thing.link.link}, but that doesn't work.
How do I reference an arbitrary subclass? I want to be able to extract any attribute from an object's subclass, for example, thing.link.link, thing.link.domain, thing.link.created, etc.

BTW, someone please tell me a better title to give this question.

I would think ${thing.link.link} would work, and ${thing.link} would at least show *something*. Are you sure that your submission has a Links object attached, and that the Links object has a link value? — Dave, Sep 21 '11 at 23:08

score 2 · Accepted Answer · answered Sep 22 '11 at 14:13

In your example, you are missing the .all() after your .query(). You can check in your view if your submissions are really loaded by doing something like

for submission in submissions:
    print submission.id, submission.title

and then watch your console when loading the page.

Then, when you confirmed you really have them loaded, you can access the link object with submission.link. In the link object, you can access the link attribute with .link.

for submission in submissions:
    print submission.link.link

So in your template, you could write ${thing.link.link}.

I guess it was just the .all(). Thanks! – Jonathan Ong Sep 23 '11 at 19:53 — Jonathan Ong, Sep 23 '11 at 19:53

score 0 · Answer 2 · answered Sep 22 '11 at 07:22

Assuming you do have the link object attached (given the fact that the link_id column is not nullable), most probably you need to (eager)load the relationship to Links because the session is alread closed when you populate your view.
See Relationship Loading Techniques for more information. The code below should do it:

submissions = dbsession.query(Submissions).options(joinedload('link'))

Referencing a related object in Pyramids/Python/SQLAlchemy

2 Answers2