Release: 1.1.0b1 | Release Date: not released

SQLAlchemy 1.1 Documentation

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 orm.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 orm.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 orm.defer() and orm.undefer():

from sqlalchemy.orm import defer, undefer

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

orm.deferred() attributes which are marked with a “group” can be undeferred using orm.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 orm.load_only():

from sqlalchemy.orm import load_only

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

버전 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 orm.defaultload():

from sqlalchemy.orm import defaultload

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

버전 0.9.0에 추가: support for Load and other options which allow for better targeting of deferral options.

Column Deferral API

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

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

매개 변수:
  • *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.

더 보기

Deferred Column Loading

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 orm.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")
    )
매개 변수:
  • 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.

더 보기

Deferred Column Loading

orm.undefer()

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("addreses").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")
        )

버전 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 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("*"))
매개 변수:
  • 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.

더 보기

Deferred Column Loading

orm.defer()

orm.undefer_group()

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 orm.defaultload():

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

버전 0.9.0으로 변경: orm.undefer_group() is now specific to a particiular entity load path.

더 보기

Deferred Column Loading

orm.defer()

orm.undefer()

Column Bundles

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

버전 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

버전 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.