SQLAlchemy: Disable lazy loading and load object only on join()

Question

I'm working on disabling lazy loading on SQLAlchemy, so it will not load by default all the objects when fetching the records from the database. I'm trying to load for example the user object only from the event object when you specifically join it on the query or if you access it for example event.user. Is this possible somehow with a parameter or is it a bad practice to disabling lazy loading?

I already tried the noload("*") but it disables any join at the end. For example I have the below model and also the queries which I'm doing my tests.

# Event model
class Event(Base):
    __tablename__ = 'events'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(50), nullable=False)
    amount = Column(Integer)

    _user_id = Column("user_id", Integer, ForeignKey("users.id"), nullable=False)
    user = relationship(User)

# Query - This fetches also the whole user object <-- I don't want such behavior
some_session.query(Event).all()

# Query - I would like to load the user object when I will use the join() if possible
some_session.query(Event).join(Event.user).all()

@IljaEverilä Yes that is true. I'm ok with it since the default is lazy="select" but when I execute the query to fetch all the Events I got the whole user object loaded without doing any event.user for example. I will also amend the title not to confuse people — Panagiotis, Dec 31 '18 at 12:09
@IljaEverilä I posted an image which showing to me the user object in my result set when executing the query. Is that not loaded? — Panagiotis, Dec 31 '18 at 12:14
@IljaEverilä I do not have any __repr__() or inpection on the specific object. I have a function which executes the query as above — Panagiotis, Dec 31 '18 at 12:19
@IljaEverilä Good point. Thank you for your information and since I'm new to sqlalchemy you helped me a lot. I will look at that post you gave me. So in order to load the user object I have to use the .join(Event.user) and this will load the object? — Panagiotis, Dec 31 '18 at 12:23

score 6 · Accepted Answer · answered Dec 31 '18 at 12:35

The default relationship loading strategy is "lazy loading", which works the way you want; the related User is loaded only if the user attribute of an Event object is touched. In your case it is touched by your IDE, when it inspects the object in order to display the attributes as a handy tree, and that triggers the fetch. The same happens easily with custom __repr__() implementations, if not careful.

When you wish to eager load the related users using a join, either use joined loading:

some_session.query(Event).options(joinedload(Event.user)).all()

or if you wish to filter based on User in the same query, explicit join(s) and contains_eager():

some_session.query(Event).join(Event.user).options(contains_eager(Event.user)).all()

Following the same __repr__() as in the link provided I'm getting the following: which indicates the the user is not loaded and when I'm using the contains_eager it shows the object to be loaded. Thank you very much for your help. — Panagiotis, Dec 31 '18 at 12:50

SQLAlchemy: Disable lazy loading and load object only on join()

1 Answers1