Loading Columns

This section presents additional options regarding the loading of columns.

Deferred Column Loading

Deferred column loading allows particular columns of a table be loaded onlyupon direct access, instead of when the entity is queried usingQuery. This feature is useful when one wants to avoidloading a large text or binary field into memory when it’s not needed.Individual columns can be lazy loaded by themselves or placed into groups thatlazy-load together, using the orm.deferred() function tomark them as “deferred”. In the example below, we define a mapping that will load each of.excerpt and .photo in separate, individual-row SELECT statements when eachattribute is first referenced on the individual object instance:

from sqlalchemy.orm import deferred
from sqlalchemy import Integer, String, Text, Binary, Column
 
class Book(Base):
    __tablename__ = 'book'
 
    book_id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    summary = Column(String(2000))
    excerpt = deferred(Column(Text))
    photo = deferred(Column(Binary))

Classical mappings as always place the usage of orm.deferred() in theproperties dictionary against the table-bound Column:

mapper(Book, book_table, properties={
    'photo':deferred(book_table.c.photo)
})

Deferred columns can be associated with a “group” name, so that they loadtogether when any of them are first accessed. The example below defines amapping with a photos deferred group. When one .photo is accessed, all threephotos will be loaded in one SELECT statement. The .excerpt will be loadedseparately when it is accessed:

class Book(Base):
    __tablename__ = 'book'
 
    book_id = Column(Integer, primary_key=True)
    title = Column(String(200), nullable=False)
    summary = Column(String(2000))
    excerpt = deferred(Column(Text))
    photo1 = deferred(Column(Binary), group='photos')
    photo2 = deferred(Column(Binary), group='photos')
    photo3 = deferred(Column(Binary), group='photos')

Deferred Column Loader Query Options

Columns can be marked as “deferred” or reset to “undeferred” at query timeusing options which are passed to the Query.options() method; the mostbasic query options are orm.defer() andorm.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.

orm.deferred() attributes which are marked with a “group” can be undeferredusing orm.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 ofentities at once, the deferral options may be specified more explicitly usingclass-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 variousrelationship paths, which are themselves often eagerly loaded with loader options. All relationship-bound loader optionssupport chaining onto additional loader options, which include loading forfurther levels of relationships, as well as onto column-oriented attributes atthat path. Such as, to load Author instances, then joined-eager-load theAuthor.books collection for each author, then apply deferral options tocolumn-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 exceptthose 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, suchas hierarchically using the Load.options()method, which allows multiple sub-options to be chained to a common parentoption at once. Any mixture of string names and class-bound attribute objectsmay 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("summary", "excerpt"),
                joinedload(Book.citations).options(
                    joinedload(Citation.author),
                    defer(Citation.fulltext)
                )
            )
        )

New in version 1.3.6: Added Load.options() to allow easierconstruction of hierarchies of loader options.

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

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

See also

Relationship Loading with Loader Options - targeted towards relationship loading

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 thata particular option should apply to all applicable attributes of a mappedclass. Such as, if we wanted to load the Book class but onlythe “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 columnattributes on the Book class. Then, the undefer() option is usedagainst the “summary” and “excerpt” fields so that they are the only columnsloaded 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 whichare always used by the ORM.

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

from sqlalchemy.orm import load_only
 
session.query(Book).options(load_only("summary", "excerpt"))

Wildcard and Exclusionary Options with Multiple-Entity Queries

Wildcard options and exclusionary options such as load_only() mayonly be applied to a single entity at a time within a Query. Tosuit the less common case where a Query is returning multipleprimary entities at once, a special calling style may be required in orderto apply a wildcard or exclusionary option, which is to use theLoad object to indicate the starting entity for a deferral option.Such as, if we were loading Book and Author at once, the Querywill raise an informative error if we try to apply load_only() toboth at once. Using Load looks like:

from sqlalchemy.orm import Load
 
query = session.query(Book, Author).join(Book.author)
query = query.options(
            Load(Book).load_only("summary", "excerpt")
        )

Above, Load is used in conjunction with the exclusionary optionload_only() so that the deferral of all other columns only takesplace for the Book class and not the Author class. Again,the Query object should raise an informative error message whenthe above calling style is actually required that describes those caseswhere explicit use of Load is needed.

Column Deferral API

• sqlalchemy.orm.defer(key, *addl_attrs)
• Indicate that the given column-oriented attribute should be deferred,e.g. not loaded until accessed.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

e.g.:

from sqlalchemy.orm import defer
 
session.query(MyClass).options(
                    defer("attribute_one"),
                    defer("attribute_two"))
 
session.query(MyClass).options(
                    defer(MyClass.attribute_one),
                    defer(MyClass.attribute_two))

To specify a deferred load of an attribute on a related class,the path can be specified one token at a time, specifying the loadingstyle for each link along the chain. To leave the loading stylefor a link unchanged, use orm.defaultload():

session.query(MyClass).options(defaultload("someattr").defer("some_column"))

A Load object that is present on a certain path can haveLoad.defer() called multiple times, each will operate on the sameparent entity:

session.query(MyClass).options(
                defaultload("someattr").
                    defer("some_column").
                    defer("some_other_column").
                    defer("another_column")
    )

• Parameters

• • key – Attribute to be deferred.

  • *addl_attrs –

This option supports the old 0.8 styleof specifying a path as a series of attributes, which is now supersededby the method-chained style.

Deprecated since version 0.9: The *addl_attrs on orm.defer() isdeprecated and will be removed in a future release. Pleaseuse method chaining in conjunction with defaultload() toindicate a path.

See also

Deferred Column Loading

orm.undefer()

• sqlalchemy.orm.deferred(*columns, **kw)

• Indicate a column-based mapped attribute that by default willnot load unless accessed.

  • Parameters

  • • *columns – columns to be mapped. This is typically a singleColumn object, however a collection is supported in orderto support multiple columns mapped under the same attribute.

    • **kw – additional keyword arguments passed toColumnProperty.

See also

Deferred Column Loading

• sqlalchemy.orm.query_expression()
• Indicate an attribute that populates from a query-time SQL expression.

New in version 1.2.

See also

mapper_query_expression

• sqlalchemy.orm.loadonly(*attrs_)
• Indicate that for a particular entity, only the given listof column-based attribute names should be loaded; all others will bedeferred.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

Example - given a class User, load only the name and fullnameattributes:

session.query(User).options(load_only("name", "fullname"))

Example - given a relationship User.addresses -> Address, specifysubquery loading for the User.addresses collection, but on eachAddress object load only the email_address attribute:

session.query(User).options(
        subqueryload("addresses").load_only("email_address")
)

For a Query that has multiple entities, the lead entity can bespecifically referred to using the Load constructor:

session.query(User, Address).join(User.addresses).options(
            Load(User).load_only("name", "fullname"),
            Load(Address).load_only("email_addres")
        )

New in version 0.9.0.

• sqlalchemy.orm.undefer(key, *addl_attrs)
• Indicate that the given column-oriented attribute should be undeferred,e.g. specified within the SELECT statement of the entity as a whole.

The column being undeferred is typically set up on the mapping as adeferred() attribute.

This function is part of the Load interface and supportsboth method-chained and standalone operation.

Examples:

# undefer two columns
session.query(MyClass).options(undefer("col1"), undefer("col2"))
 
# undefer all columns specific to a single class using Load + *
session.query(MyClass, MyOtherClass).options(
    Load(MyClass).undefer("*"))
 
# undefer a column on a related object
session.query(MyClass).options(
    defaultload(MyClass.items).undefer('text'))

• Parameters

• • key – Attribute to be undeferred.

  • *addl_attrs –

This option supports the old 0.8 styleof specifying a path as a series of attributes, which is now supersededby the method-chained style.

Deprecated since version 0.9: The *addl_attrs on orm.undefer() isdeprecated and will be removed in a future release. Pleaseuse method chaining in conjunction with defaultload() toindicate a path.

See also

Deferred Column Loading

orm.defer()

orm.undefer_group()

• sqlalchemy.orm.undefergroup(_name)
• Indicate that columns within the given deferred group name should beundeferred.

The columns being undeferred are set up on the mapping asdeferred() attributes and include a “group” name.

E.g:

session.query(MyClass).options(undefer_group("large_attrs"))

To undefer a group of attributes on a related entity, the path can bespelled out using relationship loader options, such asorm.defaultload():

session.query(MyClass).options(
    defaultload("someattr").undefer_group("large_attrs"))

Changed in version 0.9.0: orm.undefer_group() is now specific to aparticular entity load path.

See also

Deferred Column Loading

orm.defer()

orm.undefer()

• sqlalchemy.orm.withexpression(_key, expression)
• Apply an ad-hoc SQL expression to a “deferred expression” attribute.

This option is used in conjunction with the orm.query_expression()mapper-level construct that indicates an attribute which should be thetarget of an ad-hoc SQL expression.

E.g.:

sess.query(SomeClass).options(
    with_expression(SomeClass.x_y_expr, SomeClass.x + SomeClass.y)
)

New in version 1.2.

• Parameters

• • key – Attribute to be undeferred.

  • expr – SQL expression to be applied to the attribute.

See also

mapper_query_expression

Column Bundles

The Bundle may be used to query for groups of columns under onenamespace.

New in version 0.9.0.

The bundle allows columns to be grouped together:

from sqlalchemy.orm import Bundle
 
bn = Bundle('mybundle', MyClass.data1, MyClass.data2)
for row in session.query(bn).filter(bn.c.data1 == 'd1'):
    print(row.mybundle.data1, row.mybundle.data2)

The bundle can be subclassed to provide custom behaviors when resultsare fetched. The method Bundle.create_row_processor() is giventhe Query and a set of “row processor” functions at query executiontime; these processor functions when given a result row will return theindividual attribute value, which can then be adapted into any kind ofreturn data structure. Below illustrates replacing the usual KeyedTuplereturn structure with a straight Python dictionary:

from sqlalchemy.orm import Bundle
 
class DictBundle(Bundle):
    def create_row_processor(self, query, procs, labels):
        """Override create_row_processor to return values as dictionaries"""
        def proc(row):
            return dict(
                        zip(labels, (proc(row) for proc in procs))
                    )
        return proc

Changed in version 1.0: The proc() callable passed to the create_row_processor()method of custom Bundle classes now accepts only a single“row” argument.

A result from the above bundle will return dictionary values:

bn = DictBundle('mybundle', MyClass.data1, MyClass.data2)
for row in session.query(bn).filter(bn.c.data1 == 'd1'):
    print(row.mybundle['data1'], row.mybundle['data2'])

The Bundle construct is also integrated into the behaviorof composite(), where it is used to return composite attributes as objectswhen queried as individual attributes.