Amin Etesamian Amin Etesamian - 2 months ago 9
Python Question

weird behaviour possibly from my knowledge or sqlalchemy or turbogears

Suppose I got two models. Account and Question.

class Account(DeclarativeBase):
__tablename__ = 'accounts'

id = Column(Integer, primary_key=True)
user_name = Column(Unicode(255), unique=True, nullable=False)


and my Question model be like:

class Question(DeclarativeBase):
__tablename__ = 'questions'

id = Column(Integer, primary_key=True)
content = Column(Unicode(2500), nullable=False)
account_id = Column(Integer, ForeignKey(
'accounts.id', onupdate='CASCADE', ondelete='CASCADE'), nullable=False)
account = relationship('Account', backref=backref('questions'))


I got a method that returns a question in json format from the provided question ID.
when the method is like this below, it only returns the
id
the
content
and the
account_id
of the question.

@expose('json')
def question(self, question_id):
return dict(questions=DBSession.query(Question).filter(Question.id == question_id).one())


but I need the user_name of Account to be included in the json response too. something weird (at least to me) is that I have to explicitly tell the method that the query result contains a relation to an Account and this way the account info will be included in the json response: I mean doing something like this

@expose('json')
def question(self, question_id):
result = DBSession.query(Question).filter(Question.id == question_id).one()
weird_variable = result.account.user_name
return dict(question=result)


why do I have to do such thing? what is the reason behind this?

Answer

From Relationship Loading Techniques:

By default, all inter-object relationships are lazy loading.

In other words in its default configuration the relationship account does not actually load the account data when you fetch a Question, but when you access the account attribute of a Question instance. This behaviour can be controlled:

from sqlalchemy.orm import joinedload

DBSession.query(Question).\
    filter(Question.id == question_id).\
    options(joinedload('account')).\
    one()

Adding the joinedload option instructs the query to load the relationship at the same time with the parent, using a join. Other eager loading techniques are also available, their possible use cases and tradeoffs discussed under "What Kind of Loading to Use?"

Comments