Loading Columns¶

Deferred Column Loader Query Options¶

Columns can be marked as “deferred” or reset to “undeferred” at query time using options which are passed to the Query.options() method; the most basic query options are defer() and undefer():

from sqlalchemy.orm import defer
from sqlalchemy.orm import undefer

query = session.query(Book)
query = query.options(defer("summary"), undefer("excerpt"))
query.all()

Above, the “summary” column will not load until accessed, and the “excerpt” column will load immediately even if it was mapped as a “deferred” column.

deferred() attributes which are marked with a “group” can be undeferred using undefer_group(), sending in the group name:

from sqlalchemy.orm import undefer_group

query = session.query(Book)
query.options(undefer_group("photos")).all()

Deferred Loading across Multiple Entities¶

To specify column deferral for a Query that loads multiple types of entities at once, the deferral options may be specified more explicitly using class-bound attributes, rather than string names:

from sqlalchemy.orm import defer

query = session.query(Book, Author).join(Book.author)
query = query.options(defer(Author.bio))

Column deferral options may also indicate that they take place along various relationship paths, which are themselves often eagerly loaded with loader options. All relationship-bound loader options support chaining onto additional loader options, which include loading for further levels of relationships, as well as onto column-oriented attributes at that path. Such as, to load Author instances, then joined-eager-load the Author.books collection for each author, then apply deferral options to column-oriented attributes onto each Book entity from that relationship, the joinedload() loader option can be combined with the load_only() option (described later in this section) to defer all Book columns except those explicitly specified:

from sqlalchemy.orm import joinedload

query = session.query(Author)
query = query.options(
    joinedload(Author.books).load_only(Book.summary, Book.excerpt),
)

Option structures as above can also be organized in more complex ways, such as hierarchically using the Load.options() method, which allows multiple sub-options to be chained to a common parent option at once. Any mixture of string names and class-bound attribute objects may be used:

from sqlalchemy.orm import defer
from sqlalchemy.orm import joinedload
from sqlalchemy.orm import load_only

query = session.query(Author)
query = query.options(
    joinedload(Author.book).options(
        load_only(Book.summary, Book.excerpt),
        joinedload(Book.citations).options(
            joinedload(Citation.author), defer(Citation.fulltext)
        ),
    )
)

Another way to apply options to a path is to use the defaultload() function. This function is used to indicate a particular path within a loader option structure without actually setting any options at that level, so that further sub-options may be applied. The defaultload() function can be used to create the same structure as we did above using Load.options() as:

query = session.query(Author)
query = query.options(
    joinedload(Author.book).load_only(Book.summary, Book.excerpt),
    defaultload(Author.book).joinedload(Book.citations).joinedload(Citation.author),
    defaultload(Author.book).defaultload(Book.citations).defer(Citation.fulltext),
)

Load Only and Wildcard Options¶

The ORM loader option system supports the concept of “wildcard” loader options, in which a loader option can be passed an asterisk "*" to indicate that a particular option should apply to all applicable attributes of a mapped class. Such as, if we wanted to load the Book class but only the “summary” and “excerpt” columns, we could say:

from sqlalchemy.orm import defer
from sqlalchemy.orm import undefer

session.query(Book).options(defer("*"), undefer("summary"), undefer("excerpt"))

Above, the defer() option is applied using a wildcard to all column attributes on the Book class. Then, the undefer() option is used against the “summary” and “excerpt” fields so that they are the only columns loaded up front. A query for the above entity will include only the “summary” and “excerpt” fields in the SELECT, along with the primary key columns which are always used by the ORM.

A similar function is available with less verbosity by using the load_only() option. This is a so-called exclusionary option which will apply deferred behavior to all column attributes except those that are named:

from sqlalchemy.orm import load_only

session.query(Book).options(load_only(Book.summary, Book.excerpt))

from sqlalchemy.orm import Load

query = session.query(Book, Author).join(Book.author)
query = query.options(Load(Book).load_only(Book.summary, Book.excerpt))

Raiseload for Deferred Columns¶

The deferred() loader option and the corresponding loader strategy also support the concept of “raiseload”, which is a loader strategy that will raise InvalidRequestError if the attribute is accessed such that it would need to emit a SQL query in order to be loaded. This behavior is the column-based equivalent of the raiseload() feature for relationship loading, discussed at Preventing unwanted lazy loads using raiseload. Using the defer.raiseload parameter on the defer() option, an exception is raised if the attribute is accessed:

book = session.query(Book).options(defer(Book.summary, raiseload=True)).first()

# would raise an exception
book.summary

Deferred “raiseload” can be configured at the mapper level via deferred.raiseload on deferred(), so that an explicit undefer() is required in order for the attribute to be usable:

class Book(Base):
    __tablename__ = "book"

    book_id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    summary = deferred(Column(String(2000)), raiseload=True)
    excerpt = deferred(Column(Text), raiseload=True)


book_w_excerpt = session.query(Book).options(undefer(Book.excerpt)).first()

Column Deferral API¶