SQLAlchemy’s “Lazy” Parameter

SQLAlchemy is one of the object-relational mapping softwares — it lets us map python classes to SQL databases and allows us to talk to the databases using python. You can imagine that each python class you’ve created acts like a table in the database and each instance of the class represents a row on the table.

Relationship( )

Python class often has relationships with the other classes (ex. one-to-many, one-to-one, and many-to-many) and this makes the database richer. Below is an example of one-to-many relationship.

class Country(Base):
    __tablename__ = 'countries'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    cities = relationship('City', back_populates = 'country', 
lazy = True)
class City(Base):
    __tablename__ = 'cities'
    id = Column(Integer, primary_key=True)
    name = Column(Text)
    country_id = Column(Integer, ForeignKey('countries.id'))
    country = relationship('Country', back_populates = 'cities')

Here, country has many cities and cities belong to a country (One country To Many cities). Both classes have relationship function which shows their relationships, and the ‘child’ (= City class) has the foreign-key of its ‘parent’. There is a parameter in the relationship function, but what is it doing there?

What is the ‘Lazy Parameter’?

Lazy Homer Simpson

Lazy parameter determines how the related objects get loaded when querying through relationships. Below listed are the four main lazy parameters. Typically when you query the database, the data get loaded at once; however, lazy parameter allows you to alternate the way they get loaded.

• lazy = ‘select’ (or True)
• lazy = ‘dynamic’
• lazy = ‘joined’ (or False)
• lazy = ‘subquery’

lazy = ‘select’:

lazy = ‘select’ is the default setting. It emits a SELECT statement when loading. For instance, if you want to get all the city objects for the first country in the above example, you would query as:

session.query(Country).first().cities

This would return a list of city objects.

lazy = ‘dynamic’:

When querying with lazy = ‘dynamic’, however, a separate query gets generated for the related object. If you use the same query as ‘select’, it will return:

You can see that it returns a sqlalchemy object instead of the city objects. This is basically a SELECT statement for the city class, hence, it’s still waiting for us to call a method on it. In order to return the list of cities, therefore, you need to call .all( ) after cities:

session.query(Country).first().cities.all()

The benefit of this is that you can query further to return what you want by using methods like filter/filter_by, order_by, etc...

lazy = ‘joined’ (or ‘False’)

lazy = ‘joined’ literally joins the two tables and returns the results. By querying like below:

session.query(Country).first().cities

lazy = ‘subquery’

lazy = ‘subquery’ and lazy = ‘joined’ basically do the same thing, except that subquery uses subquery. The query and return would be the same for joined and subquery; however there could be performance differences between the two as they joins the tables differently.