Loading Columns¶

This section presents additional options regarding the loading of columns.

Deferred Column Loading¶

This feature allows particular columns of a table be loaded only upon direct access, instead of when the entity is queried using Query. This feature is useful when one wants to avoid loading 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 that lazy-load together, using the deferred() function to mark 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 each attribute 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 deferred() in the properties 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 load together when any of them are first accessed. The example below defines a mapping with a photos deferred group. When one .photo is accessed, all three photos will be loaded in one SELECT statement. The .excerpt will be loaded separately 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')

You can defer or undefer columns at the Query level using options, including defer() and undefer():

from sqlalchemy.orm import defer, undefer

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

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()

Load Only Cols¶

An arbitrary set of columns can be selected as “load only” columns, which will be loaded while deferring all other columns on a given entity, using load_only():

from sqlalchemy.orm import load_only

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

New in version 0.9.0.

Deferred Loading with Multiple Entities¶

To specify column deferral options within a Query that loads multiple types of entity, the Load object can specify which parent entity to start with:

from sqlalchemy.orm import Load

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

To specify column deferral options along the path of various relationships, the options support chaining, where the loading style of each relationship is specified first, then is chained to the deferral options. Such as, to load Book instances, then joined-eager-load the Author, then apply deferral options to the Author entity:

from sqlalchemy.orm import joinedload

query = session.query(Book)
query = query.options(
            joinedload(Book.author).load_only("summary", "excerpt"),
        )

In the case where the loading style of parent relationships should be left unchanged, use defaultload():

from sqlalchemy.orm import defaultload

query = session.query(Book)
query = query.options(
            defaultload(Book.author).load_only("summary", "excerpt"),
        )

New in version 0.9.0: support for Load and other options which allow for better targeting of deferral options.

Column Deferral API¶

Object Name	Description
defer(key, *addl_attrs)	Indicate that the given column-oriented attribute should be deferred, e.g. not loaded until accessed.
deferred(columns, *kw)	Indicate a column-based mapped attribute that by default will not load unless accessed.
load_only(*attrs)	Indicate that for a particular entity, only the given list of column-based attribute names should be loaded; all others will be deferred.
query_expression()	Indicate an attribute that populates from a query-time SQL expression.
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.
undefer_group(name)	Indicate that columns within the given deferred group name should be undeferred.
with_expression(key, expression)	Apply an ad-hoc SQL expression to a “deferred expression” attribute.

function 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 supports both 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 loading style for each link along the chain. To leave the loading style for a link unchanged, use defaultload():

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

A Load object that is present on a certain path can have Load.defer() called multiple times, each will operate on the same parent 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 – Deprecated; this option supports the old 0.8 style of specifying a path as a series of attributes, which is now superseded by the method-chained style.

See also

Deferred Column Loading

undefer()

function sqlalchemy.orm.deferred(*columns, **kw)¶

Indicate a column-based mapped attribute that by default will not load unless accessed.

Parameters:

*columns – columns to be mapped. This is typically a single Column object, however a collection is supported in order to support multiple columns mapped under the same attribute.
**kw – additional keyword arguments passed to ColumnProperty.

See also

Deferred Column Loading

function 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

function sqlalchemy.orm.load_only(*attrs)¶

Indicate that for a particular entity, only the given list of column-based attribute names should be loaded; all others will be deferred.

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

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

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

Example - given a relationship User.addresses -> Address, specify subquery loading for the User.addresses collection, but on each Address 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 be specifically 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.

function 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 a deferred() attribute.

This function is part of the Load interface and supports both 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("*"))

Parameters:

key – Attribute to be undeferred.
*addl_attrs – Deprecated; this option supports the old 0.8 style of specifying a path as a series of attributes, which is now superseded by the method-chained style.

See also

Deferred Column Loading

defer()

undefer_group()

function sqlalchemy.orm.undefer_group(name)¶

Indicate that columns within the given deferred group name should be undeferred.

The columns being undeferred are set up on the mapping as deferred() 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 be spelled out using relationship loader options, such as defaultload():

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

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

See also

Deferred Column Loading

defer()

undefer()

function sqlalchemy.orm.with_expression(key, expression)¶

Apply an ad-hoc SQL expression to a “deferred expression” attribute.

This option is used in conjunction with the query_expression() mapper-level construct that indicates an attribute which should be the target 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 one namespace.

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 results are fetched. The method Bundle.create_row_processor() is given the Query and a set of “row processor” functions at query execution time; these processor functions when given a result row will return the individual attribute value, which can then be adapted into any kind of return data structure. Below illustrates replacing the usual KeyedTuple return 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 behavior of composite(), where it is used to return composite attributes as objects when queried as individual attributes.

SQLAlchemy 1.2 Documentation

SQLAlchemy 1.2 Documentation

SQLAlchemy ORM

Loading Columns¶

Deferred Column Loading¶

Load Only Cols¶

Deferred Loading with Multiple Entities¶

Column Deferral API¶

Column Bundles¶