See: Flask-SQLAlchemy for an explanation of lazy

The relationship of SQLAlchemy (… , lazy=’?? ‘) method’s lazy parameter is always the most confusing one for beginners.

Lazy Load Methods are a set of Load Methods defined by SQLAlchemy for multiple table associations. What value you choose for the lazy parameter determines when SQLAlchemy loads data from the database. Each method corresponds to a way of writing multi-table association in SQL statement, so it has different advantages and disadvantages, efficiency and high and low.

Alternative methods for the lazy parameter are:

  • select– (default) the background will load all data at once with a SELECT statement, that is, when a property is accessed, it will load all data for that property.
  • joined– Data will be loaded by JOIN statement, that is, JOIN two associated tables to get all related objects.
  • subquery– Data is loaded using the SubQuery subquery SQL statement
  • dynamic– When accessing a property, it does not load data in memory. Instead, it returns a Query object that needs to execute the corresponding method to retrieve the object. Suitable for large amount of data.
  • immediate – items should be loaded as the parents are loaded, using a separate SELECT statement, or identity map fetch for simple many-to-one references.
  • noload-No loading should occur at any time. This is to support “write-only” attributes, or attributes which are populated in some manner specific to the application.
  • True– That’s the ‘select’ method
  • False– That is, the ‘joined’ method
  • None– The ‘noload’ method

Let’s use the examples of School and Students to see the difference between the various methods.

Suppose we define two ORM classes:

class School(..) : id = Column(..) students = relationship( 'Student', backref='school' ) class Student(..) : id = Column(..) school_id = Column(.. , ForeignKey('school.id') )

In the example above we set up a normal two-table association: students = relationship(‘Student’, backref=’school’). By default, the lazy parameter is select, which we can do without). That is, if we define lazy=’select’, then when we want to search for references (assuming data already exists in the table) :

>>> > school_01. Students [<Student: u'test'>, <Student: school.query. First () # School_01 = school.query. u'test2'>, <Student: u'test3'> ]

As you can see, lazy=’select’ simply returns all associated data. However, if the amount of data is very large: say, millions, it is not rational to return it all, because it will eat up a lot of memory. So we can select lazy=’dynamic’, which returns only one query object for you to manually add conditional queries, such as query.all() or query.filter().

Suppose we change the previous definition to: students = db.relationship(‘Student’, backref=’_class’, lazy=”dynamic”). So:

>>> school_01.students <sqlalchemy.orm.dynamic.AppenderBaseQuery object at 0x7f007d2e8ed0> >>> print( school_01.students  ) SELECT students.id AS students_id, students.name AS students_name FROM students, registrations WHERE :param_1 = registrations.class_id AND students.id = registrations.student_id >>> school_01.students.all() [ <Student: u'test'>, <Student: u'test2'>, <Student: u'test3'> ]

You can see that executing school_01.students returns only a Query object, or even a SQL statement with no data. So you can imagine that this is going to take 0 time. If lazy=select or join, return the result directly.

Note that lazy=”dynamic” can only be used in one-to-many and many-to-many relationships, not one-to-one or many-to-one.

This is also reasonable: there is no need to lazily load data if few results are returned.

backref(… , lazy=…) Lazy load of backreferences

Directly to the relationship (.. , lazy=’?? ‘), just set the load method for the forward reference. You can actually set the lazy load method to backreferences as well. To do this, use backref(..) Function:

students = relationship(... , lazy='.. ', backref=backref('Student, lazy='dynamic') )

As you can see, backref(..) The backref function returns a value specific to the backref parameter, where you can specify the backreference loading method.