Eager loading

There are situations when we want the data of the object we want to be loaded along with the relationships our object maps to. This is a valid use case, such as when the developer is sure they will be accessing the data of the relationship, no matter the situation.

In these kinds of use cases, there is no point wasting time while the ORM layers load the relationships on demand. This technique of loading the object data along with the data of the associated objects to which our main object is related is known as eager loading.

SQLAlchemy provides an easy way to achieve this behavior. Remember the lazy_load attribute we specified in the previous section? Yes, that's all you need to switch from lazy load behavior to eager load behavior.

For example, let's consider our user model only. If we wanted to make our roles relation use the eager loading technique, all we would need to do is modify the lazy_load attribute and set it to joined, as can be seen in the following snippet:

role = relationship("Role", lazy_load='joined')

Setting lazy_load to joined,  will cause SQLAlchemy to emit a JOIN statement to load the data of our user object and the related data from the associated role object, as can be seen from the following example:

>>> Session = sessionmaker(bind=engine)
>>> db_session = Session()
>>> user_record = db_session.query(User).first()
sqlalchemy.engine.base.Engine BEGIN (implicit)
sqlalchemy.engine.base.Engine SELECT users.username AS users_username, users.id AS users_id, users.role_id AS users_role_id, roles_1.id AS roles_1_id, roles_1.role_name AS roles_1_role_name, roles_1.role_permissions AS roles_1_role_permissions 
FROM users LEFT OUTER JOIN roles AS roles_1 ON roles_1.id = users.role_id
INFO sqlalchemy.engine.base.Engine {}

As you can see from this example, SQLAlchemy used a LEFT OUTER JOIN to eager load the user object as well as the associated role object.

There is another way through which SQLAlchemy can eager load the results. This technique involves SQLAlchemy emitting successive SELECT statements to load the data and then joining the results to form one complex result object.

This behavior is also easy to achieve and requires the setting of lazy_load='subquery'.

So, with all this knowledge , how can we optimize our data loading techniques so that our application will work easily when dealing with large-scale data? Let's take a look in the next section.