SQLAlchemy foreign key lazy loading

Question

I have a basic one to many relationship:

class Term(Base):
  __tablename__ = 'term'
  id = Column(Integer, primary_key=True)

class Node(Base):
  __tablename__ = 'node'
  id = Column(Integer, primary_key=True)
  term = Column(Integer, ForeignKey('term.id'))

But when I load the Node object, access the "term" property, I just get the numeric term id, not the Term object.

node = session.query(Node).filter(Node.id == 1).one()
print node.term # 123

How do I get Foreign Key fields to lazy load the object?

Thanks very much. Ben

SingleNegationElimination · Accepted Answer · 2013-11-15 15:14:40Z

5

because your term attribute is a Column, sqlalchemy maps it as that column's value. You can get sqlalchemy to actually load the referent row by using relationship:

from sqlalchemy.orm import relationship

class Term(Base):
  __tablename__ = 'term'
  id = Column(Integer, primary_key=True)

class Node(Base):
  __tablename__ = 'node'
  id = Column(Integer, primary_key=True)
  term = Column(Integer, ForeignKey('term.id'))
  related_term = relationship(Term, backref="nodes")

Because my_node.related_term looks a bit odd, I tend to prefer a naming convention of having the column called table_column instead of just table, so that I can also name the relationship attribute after the table, instead of inventing some other, odd name.

answered Nov 15, 2013 at 15:14

SingleNegationElimination

157k35 gold badges270 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ben Scott Over a year ago

Brilliant. That sorts it, and I'll use that naming tip too. Thanks very much.

aIKid · Accepted Answer · 2013-11-15 14:40:13Z

0

Use the returned value of node.term for a new query, to get the related objects:

node = session.query(Node).filter(Node.id == 1).one()
related_term = session.query(Term).filter(Term.id == node.term).one()

edited Nov 15, 2013 at 14:40

answered Nov 15, 2013 at 14:34

aIKid

28.5k5 gold badges41 silver badges65 bronze badges

2 Comments

SingleNegationElimination Over a year ago

This is very much the opposite of what Ben is asking for, explicitly loading this object largely negates the value of using an ORM, where you can ignore the on disk persistence of the data and treat them as regular python objects.

aIKid Over a year ago

Thanks a lot, i'm pretty much a newbie in SQLAlchemy haha. Will be fun to learn from you, sir!

Collectives™ on Stack Overflow

SQLAlchemy foreign key lazy loading

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related