SQLAlchemy 1.3 Documentation
SQLAlchemy ORM
- Object Relational Tutorial
- Mapper Configuration
- Relationship Configuration
- Loading Objects
- Using the Session
- Events and Internals
- ORM Extensions
- ORM Examples
Project Versions
- Previous: Loading Objects
- Next: Relationship Loading Techniques
- Up: Home
- On this page:
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 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')
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("summary", "excerpt"),
joinedload(Book.citations).options(
joinedload(Citation.author),
defer(Citation.fulltext)
)
)
)
New in version 1.3.6: Added Load.options()
to allow easier
construction of hierarchies of loader options.
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("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 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("summary", "excerpt"))
Wildcard and Exclusionary Options with Multiple-Entity Queries¶
Wildcard options and exclusionary options such as load_only()
may
only be applied to a single entity at a time within a Query
. To
suit the less common case where a Query
is returning multiple
primary entities at once, a special calling style may be required in order
to apply a wildcard or exclusionary option, which is to use the
Load
object to indicate the starting entity for a deferral option.
Such as, if we were loading Book
and Author
at once, the Query
will raise an informative error if we try to apply load_only()
to
both 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 option
load_only()
so that the deferral of all other columns only takes
place for the Book
class and not the Author
class. Again,
the Query
object should raise an informative error message when
the above calling style is actually required that describes those cases
where explicit use of Load
is needed.
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([default_expr]) |
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 haveLoad.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¶ –
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
defer()
is deprecated and will be removed in a future release. Please use method chaining in conjunction with defaultload() to indicate a path.
- 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
- function sqlalchemy.orm.query_expression(default_expr=<sqlalchemy.sql.elements.Null object>)¶
Indicate an attribute that populates from a query-time SQL expression.
- Parameters:
default_expr¶ –
Optional SQL expression object that will be used in all cases if not assigned later with
with_expression()
. E.g.:from sqlalchemy.sql import literal class C(Base): #... my_expr = query_expression(literal(1))
New in version 1.3.18.
New in version 1.2.
- 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 thename
andfullname
attributes:session.query(User).options(load_only("name", "fullname"))
Example - given a relationship
User.addresses -> Address
, specify subquery loading for theUser.addresses
collection, but on eachAddress
object load only theemail_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 theLoad
constructor:session.query(User, Address).join(User.addresses).options( Load(User).load_only("name", "fullname"), Load(Address).load_only("email_address") ) .. note:: This method will still load a :class:`_schema.Column` even if the column property is defined with ``deferred=True`` for the :func:`.column_property` function.
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("*")) # 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 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
undefer()
is deprecated and will be removed in a future release. Please use method chaining in conjunction with defaultload() to indicate a path.
- 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.
- 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:
Note
the target attribute is populated only if the target object is not currently loaded in the current
Session
unless theQuery.populate_existing()
method is used. Please refer to Query-time SQL expressions as mapped attributes for complete usage details.
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.
flambé! the dragon and The Alchemist image designs created and generously donated by Rotem Yaari.
Created using Sphinx 7.2.6. Documentation last generated: Sat 06 Jan 2024 12:16:19 PM