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

Answer 1

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.