This section presents additional options regarding the loading of columns.
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()
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"))
New in version 0.9.0.
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"),
)
New in version 0.9.0: support for Load
and other options which
allow for better targeting of deferral options.
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¶ –
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.
Deprecated since version 0.9: The *addl_attrs on
orm.defer()
is deprecated and will be removed in a future release. Please use method chaining in conjunction with defaultload() to indicate a path.
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
.
See also
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.
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.
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("*"))
# undefer a column on a related object
session.query(MyClass).options(
defaultload(MyClass.items).undefer('text'))
key¶ – Attribute to be undeferred.
*addl_attrs¶ –
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.
Deprecated since version 0.9: The *addl_attrs on
orm.undefer()
is deprecated and will be removed in a future release. Please use method chaining in conjunction with defaultload() to indicate a path.
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"))
Changed in version 0.9.0: orm.undefer_group()
is now specific to a
particular entity load path.
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 orm.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.
See also
mapper_query_expression
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.