Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/orm/query.py : 53%

Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# orm/query.py
2# Copyright (C) 2005-2020 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
8"""The Query class and support.
10Defines the :class:`_query.Query` class, the central
11construct used by the ORM to construct database queries.
13The :class:`_query.Query` class should not be confused with the
14:class:`_expression.Select` class, which defines database
15SELECT operations at the SQL (non-ORM) level. ``Query`` differs from
16``Select`` in that it returns ORM-mapped objects and interacts with an
17ORM session, whereas the ``Select`` construct interacts directly with the
18database to return iterable result sets.
20"""
22from itertools import chain
24from . import attributes
25from . import exc as orm_exc
26from . import interfaces
27from . import loading
28from . import persistence
29from . import properties
30from .base import _entity_descriptor
31from .base import _generative
32from .base import _is_aliased_class
33from .base import _is_mapped_class
34from .base import _orm_columns
35from .base import InspectionAttr
36from .path_registry import PathRegistry
37from .util import _entity_corresponds_to
38from .util import aliased
39from .util import AliasedClass
40from .util import join as orm_join
41from .util import object_mapper
42from .util import ORMAdapter
43from .util import with_parent
44from .. import exc as sa_exc
45from .. import inspect
46from .. import inspection
47from .. import log
48from .. import sql
49from .. import util
50from ..sql import expression
51from ..sql import util as sql_util
52from ..sql import visitors
53from ..sql.base import ColumnCollection
54from ..sql.expression import _interpret_as_from
55from ..sql.selectable import ForUpdateArg
58__all__ = ["Query", "QueryContext", "aliased"]
61_path_registry = PathRegistry.root
64@inspection._self_inspects
65@log.class_logger
66class Query(object):
67 """ORM-level SQL construction object.
69 :class:`_query.Query`
70 is the source of all SELECT statements generated by the
71 ORM, both those formulated by end-user query operations as well as by
72 high level internal operations such as related collection loading. It
73 features a generative interface whereby successive calls return a new
74 :class:`_query.Query` object, a copy of the former with additional
75 criteria and options associated with it.
77 :class:`_query.Query` objects are normally initially generated using the
78 :meth:`~.Session.query` method of :class:`.Session`, and in
79 less common cases by instantiating the :class:`_query.Query` directly and
80 associating with a :class:`.Session` using the
81 :meth:`_query.Query.with_session`
82 method.
84 For a full walkthrough of :class:`_query.Query` usage, see the
85 :ref:`ormtutorial_toplevel`.
87 """
89 _only_return_tuples = False
90 _enable_eagerloads = True
91 _enable_assertions = True
92 _with_labels = False
93 _criterion = None
94 _yield_per = None
95 _order_by = False
96 _group_by = False
97 _having = None
98 _distinct = False
99 _prefixes = None
100 _suffixes = None
101 _offset = None
102 _limit = None
103 _for_update_arg = None
104 _statement = None
105 _correlate = frozenset()
106 _populate_existing = False
107 _invoke_all_eagers = True
108 _version_check = False
109 _autoflush = True
110 _only_load_props = None
111 _refresh_state = None
112 _refresh_identity_token = None
113 _from_obj = ()
114 _join_entities = ()
115 _select_from_entity = None
116 _mapper_adapter_map = {}
117 _filter_aliases = ()
118 _from_obj_alias = None
119 _joinpath = _joinpoint = util.immutabledict()
120 _execution_options = util.immutabledict()
121 _params = util.immutabledict()
122 _attributes = util.immutabledict()
123 _with_options = ()
124 _with_hints = ()
125 _enable_single_crit = True
126 _orm_only_adapt = True
127 _orm_only_from_obj_alias = True
128 _current_path = _path_registry
129 _has_mapper_entities = False
130 _bake_ok = True
132 lazy_loaded_from = None
133 """An :class:`.InstanceState` that is using this :class:`_query.Query`
134 for a
135 lazy load operation.
137 The primary rationale for this attribute is to support the horizontal
138 sharding extension, where it is available within specific query
139 execution time hooks created by this extension. To that end, the
140 attribute is only intended to be meaningful at **query execution time**,
141 and importantly not any time prior to that, including query compilation
142 time.
144 .. note::
146 Within the realm of regular :class:`_query.Query` usage,
147 this attribute is
148 set by the lazy loader strategy before the query is invoked. However
149 there is no established hook that is available to reliably intercept
150 this value programmatically. It is set by the lazy loading strategy
151 after any mapper option objects would have been applied, and now that
152 the lazy loading strategy in the ORM makes use of "baked" queries to
153 cache SQL compilation, the :meth:`.QueryEvents.before_compile` hook is
154 also not reliable.
156 Currently, setting the :paramref:`_orm.relationship.bake_queries` to
157 ``False`` on the target :func:`_orm.relationship`,
158 and then making use of
159 the :meth:`.QueryEvents.before_compile` event hook, is the only
160 available programmatic path to intercepting this attribute. In future
161 releases, there will be new hooks available that allow interception of
162 the :class:`_query.Query` before it is executed,
163 rather than before it is
164 compiled.
166 .. versionadded:: 1.2.9
168 """
170 def __init__(self, entities, session=None):
171 """Construct a :class:`_query.Query` directly.
173 E.g.::
175 q = Query([User, Address], session=some_session)
177 The above is equivalent to::
179 q = some_session.query(User, Address)
181 :param entities: a sequence of entities and/or SQL expressions.
183 :param session: a :class:`.Session` with which the
184 :class:`_query.Query`
185 will be associated. Optional; a :class:`_query.Query`
186 can be associated
187 with a :class:`.Session` generatively via the
188 :meth:`_query.Query.with_session` method as well.
190 .. seealso::
192 :meth:`.Session.query`
194 :meth:`_query.Query.with_session`
196 """
197 self.session = session
198 self._polymorphic_adapters = {}
199 self._set_entities(entities)
201 def _set_entities(self, entities, entity_wrapper=None):
202 if entity_wrapper is None:
203 entity_wrapper = _QueryEntity
204 self._entities = []
205 self._primary_entity = None
206 self._has_mapper_entities = False
208 # 1. don't run util.to_list() or _set_entity_selectables
209 # if no entities were passed - major performance bottleneck
210 # from lazy loader implementation when it seeks to use Query
211 # class for an identity lookup, causes test_orm.py to fail
212 # with thousands of extra function calls, see issue #4228
213 # for why this use had to be added
214 # 2. can't use classmethod on Query because session.query_cls
215 # is an arbitrary callable in some user recipes, not
216 # necessarily a class, so we don't have the class available.
217 # see issue #4256
218 # 3. can't do "if entities is not None" because we usually get here
219 # from session.query() which takes in *entities.
220 # 4. can't do "if entities" because users make use of undocumented
221 # to_list() behavior here and they pass clause expressions that
222 # can't be evaluated as boolean. See issue #4269.
223 if entities != ():
224 for ent in util.to_list(entities):
225 entity_wrapper(self, ent)
227 self._set_entity_selectables(self._entities)
229 def _set_entity_selectables(self, entities):
230 self._mapper_adapter_map = d = self._mapper_adapter_map.copy()
232 for ent in entities:
233 for entity in ent.entities:
234 if entity not in d:
235 ext_info = inspect(entity)
236 if (
237 not ext_info.is_aliased_class
238 and ext_info.mapper.with_polymorphic
239 ):
240 if (
241 ext_info.mapper.persist_selectable
242 not in self._polymorphic_adapters
243 ):
244 self._mapper_loads_polymorphically_with(
245 ext_info.mapper,
246 sql_util.ColumnAdapter(
247 ext_info.selectable,
248 ext_info.mapper._equivalent_columns,
249 ),
250 )
251 aliased_adapter = None
252 elif ext_info.is_aliased_class:
253 aliased_adapter = ext_info._adapter
254 else:
255 aliased_adapter = None
257 d[entity] = (ext_info, aliased_adapter)
258 ent.setup_entity(*d[entity])
260 def _mapper_loads_polymorphically_with(self, mapper, adapter):
261 for m2 in mapper._with_polymorphic_mappers or [mapper]:
262 self._polymorphic_adapters[m2] = adapter
263 for m in m2.iterate_to_root():
264 self._polymorphic_adapters[m.local_table] = adapter
266 def _set_select_from(self, obj, set_base_alias):
267 fa = []
268 select_from_alias = None
270 for from_obj in obj:
271 info = inspect(from_obj)
272 if hasattr(info, "mapper") and (
273 info.is_mapper or info.is_aliased_class
274 ):
275 self._select_from_entity = info
276 if set_base_alias and not info.is_aliased_class:
277 raise sa_exc.ArgumentError(
278 "A selectable (FromClause) instance is "
279 "expected when the base alias is being set."
280 )
281 fa.append(info.selectable)
282 elif not info.is_selectable:
283 raise sa_exc.ArgumentError(
284 "argument is not a mapped class, mapper, "
285 "aliased(), or FromClause instance."
286 )
287 else:
288 if isinstance(from_obj, expression.SelectBase):
289 from_obj = from_obj.alias()
290 if set_base_alias:
291 select_from_alias = from_obj
292 fa.append(from_obj)
294 self._from_obj = tuple(fa)
296 if (
297 set_base_alias
298 and len(self._from_obj) == 1
299 and isinstance(select_from_alias, expression.Alias)
300 ):
301 equivs = self.__all_equivs()
302 self._from_obj_alias = sql_util.ColumnAdapter(
303 self._from_obj[0], equivs
304 )
305 elif (
306 set_base_alias
307 and len(self._from_obj) == 1
308 and hasattr(info, "mapper")
309 and info.is_aliased_class
310 ):
311 self._from_obj_alias = info._adapter
313 def _reset_polymorphic_adapter(self, mapper):
314 for m2 in mapper._with_polymorphic_mappers:
315 self._polymorphic_adapters.pop(m2, None)
316 for m in m2.iterate_to_root():
317 self._polymorphic_adapters.pop(m.local_table, None)
319 def _adapt_polymorphic_element(self, element):
320 if "parententity" in element._annotations:
321 search = element._annotations["parententity"]
322 alias = self._polymorphic_adapters.get(search, None)
323 if alias:
324 return alias.adapt_clause(element)
326 if isinstance(element, expression.FromClause):
327 search = element
328 elif hasattr(element, "table"):
329 search = element.table
330 else:
331 return None
333 alias = self._polymorphic_adapters.get(search, None)
334 if alias:
335 return alias.adapt_clause(element)
337 def _adapt_col_list(self, cols):
338 return [
339 self._adapt_clause(
340 expression._literal_as_label_reference(o), True, True
341 )
342 for o in cols
343 ]
345 @_generative()
346 def _set_lazyload_from(self, state):
347 self.lazy_loaded_from = state
349 @_generative()
350 def _adapt_all_clauses(self):
351 self._orm_only_adapt = False
353 def _adapt_clause(self, clause, as_filter, orm_only):
354 """Adapt incoming clauses to transformations which
355 have been applied within this query."""
357 adapters = []
358 # do we adapt all expression elements or only those
359 # tagged as 'ORM' constructs ?
360 if not self._orm_only_adapt:
361 orm_only = False
363 if as_filter and self._filter_aliases:
364 for fa in self._filter_aliases:
365 adapters.append((orm_only, fa.replace))
367 if self._from_obj_alias:
368 # for the "from obj" alias, apply extra rule to the
369 # 'ORM only' check, if this query were generated from a
370 # subquery of itself, i.e. _from_selectable(), apply adaption
371 # to all SQL constructs.
372 adapters.append(
373 (
374 orm_only if self._orm_only_from_obj_alias else False,
375 self._from_obj_alias.replace,
376 )
377 )
379 if self._polymorphic_adapters:
380 adapters.append((orm_only, self._adapt_polymorphic_element))
382 if not adapters:
383 return clause
385 def replace(elem):
386 is_orm_adapt = (
387 "_orm_adapt" in elem._annotations
388 or "parententity" in elem._annotations
389 )
390 for _orm_only, adapter in adapters:
391 if not _orm_only or is_orm_adapt:
392 e = adapter(elem)
393 if e is not None:
394 return e
396 return visitors.replacement_traverse(clause, {}, replace)
398 def _query_entity_zero(self):
399 """Return the first QueryEntity."""
400 return self._entities[0]
402 def _mapper_zero(self):
403 """return the Mapper associated with the first QueryEntity."""
404 return self._entities[0].mapper
406 def _entity_zero(self):
407 """Return the 'entity' (mapper or AliasedClass) associated
408 with the first QueryEntity, or alternatively the 'select from'
409 entity if specified."""
411 return (
412 self._select_from_entity
413 if self._select_from_entity is not None
414 else self._query_entity_zero().entity_zero
415 )
417 @property
418 def _mapper_entities(self):
419 for ent in self._entities:
420 if isinstance(ent, _MapperEntity):
421 yield ent
423 def _joinpoint_zero(self):
424 return self._joinpoint.get("_joinpoint_entity", self._entity_zero())
426 def _bind_mapper(self):
427 ezero = self._entity_zero()
428 if ezero is not None:
429 insp = inspect(ezero)
430 if not insp.is_clause_element:
431 return insp.mapper
433 return None
435 def _only_full_mapper_zero(self, methname):
436 if self._entities != [self._primary_entity]:
437 raise sa_exc.InvalidRequestError(
438 "%s() can only be used against "
439 "a single mapped class." % methname
440 )
441 return self._primary_entity.entity_zero
443 def _only_entity_zero(self, rationale=None):
444 if len(self._entities) > 1:
445 raise sa_exc.InvalidRequestError(
446 rationale
447 or "This operation requires a Query "
448 "against a single mapper."
449 )
450 return self._entity_zero()
452 def __all_equivs(self):
453 equivs = {}
454 for ent in self._mapper_entities:
455 equivs.update(ent.mapper._equivalent_columns)
456 return equivs
458 def _get_condition(self):
459 return self._no_criterion_condition(
460 "get", order_by=False, distinct=False
461 )
463 def _get_existing_condition(self):
464 self._no_criterion_assertion("get", order_by=False, distinct=False)
466 def _no_criterion_assertion(self, meth, order_by=True, distinct=True):
467 if not self._enable_assertions:
468 return
469 if (
470 self._criterion is not None
471 or self._statement is not None
472 or self._from_obj
473 or self._limit is not None
474 or self._offset is not None
475 or self._group_by
476 or (order_by and self._order_by)
477 or (distinct and self._distinct)
478 ):
479 raise sa_exc.InvalidRequestError(
480 "Query.%s() being called on a "
481 "Query with existing criterion. " % meth
482 )
484 def _no_criterion_condition(self, meth, order_by=True, distinct=True):
485 self._no_criterion_assertion(meth, order_by, distinct)
487 self._from_obj = ()
488 self._statement = self._criterion = None
489 self._order_by = self._group_by = self._distinct = False
491 def _no_clauseelement_condition(self, meth):
492 if not self._enable_assertions:
493 return
494 if self._order_by:
495 raise sa_exc.InvalidRequestError(
496 "Query.%s() being called on a "
497 "Query with existing criterion. " % meth
498 )
499 self._no_criterion_condition(meth)
501 def _no_statement_condition(self, meth):
502 if not self._enable_assertions:
503 return
504 if self._statement is not None:
505 raise sa_exc.InvalidRequestError(
506 (
507 "Query.%s() being called on a Query with an existing full "
508 "statement - can't apply criterion."
509 )
510 % meth
511 )
513 def _no_limit_offset(self, meth):
514 if not self._enable_assertions:
515 return
516 if self._limit is not None or self._offset is not None:
517 raise sa_exc.InvalidRequestError(
518 "Query.%s() being called on a Query which already has LIMIT "
519 "or OFFSET applied. To modify the row-limited results of a "
520 " Query, call from_self() first. "
521 "Otherwise, call %s() before limit() or offset() "
522 "are applied." % (meth, meth)
523 )
525 def _get_options(
526 self,
527 populate_existing=None,
528 version_check=None,
529 only_load_props=None,
530 refresh_state=None,
531 identity_token=None,
532 ):
533 if populate_existing:
534 self._populate_existing = populate_existing
535 if version_check:
536 self._version_check = version_check
537 if refresh_state:
538 self._refresh_state = refresh_state
539 if only_load_props:
540 self._only_load_props = set(only_load_props)
541 if identity_token:
542 self._refresh_identity_token = identity_token
543 return self
545 def _clone(self):
546 cls = self.__class__
547 q = cls.__new__(cls)
548 q.__dict__ = self.__dict__.copy()
549 return q
551 @property
552 def statement(self):
553 """The full SELECT statement represented by this Query.
555 The statement by default will not have disambiguating labels
556 applied to the construct unless with_labels(True) is called
557 first.
559 """
561 stmt = self._compile_context(labels=self._with_labels).statement
562 if self._params:
563 stmt = stmt.params(self._params)
565 return stmt
567 def subquery(self, name=None, with_labels=False, reduce_columns=False):
568 """return the full SELECT statement represented by
569 this :class:`_query.Query`, embedded within an
570 :class:`_expression.Alias`.
572 Eager JOIN generation within the query is disabled.
574 :param name: string name to be assigned as the alias;
575 this is passed through to :meth:`_expression.FromClause.alias`.
576 If ``None``, a name will be deterministically generated
577 at compile time.
579 :param with_labels: if True, :meth:`.with_labels` will be called
580 on the :class:`_query.Query` first to apply table-qualified labels
581 to all columns.
583 :param reduce_columns: if True,
584 :meth:`_expression.Select.reduce_columns` will
585 be called on the resulting :func:`_expression.select` construct,
586 to remove same-named columns where one also refers to the other
587 via foreign key or WHERE clause equivalence.
589 """
590 q = self.enable_eagerloads(False)
591 if with_labels:
592 q = q.with_labels()
593 q = q.statement
595 if reduce_columns:
596 q = q.reduce_columns()
597 return q.alias(name=name)
599 def cte(self, name=None, recursive=False):
600 r"""Return the full SELECT statement represented by this
601 :class:`_query.Query` represented as a common table expression (CTE).
603 Parameters and usage are the same as those of the
604 :meth:`_expression.SelectBase.cte` method; see that method for
605 further details.
607 Here is the `PostgreSQL WITH
608 RECURSIVE example
609 <http://www.postgresql.org/docs/8.4/static/queries-with.html>`_.
610 Note that, in this example, the ``included_parts`` cte and the
611 ``incl_alias`` alias of it are Core selectables, which
612 means the columns are accessed via the ``.c.`` attribute. The
613 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
614 ``Part`` entity, so column-mapped attributes are available
615 directly::
617 from sqlalchemy.orm import aliased
619 class Part(Base):
620 __tablename__ = 'part'
621 part = Column(String, primary_key=True)
622 sub_part = Column(String, primary_key=True)
623 quantity = Column(Integer)
625 included_parts = session.query(
626 Part.sub_part,
627 Part.part,
628 Part.quantity).\
629 filter(Part.part=="our part").\
630 cte(name="included_parts", recursive=True)
632 incl_alias = aliased(included_parts, name="pr")
633 parts_alias = aliased(Part, name="p")
634 included_parts = included_parts.union_all(
635 session.query(
636 parts_alias.sub_part,
637 parts_alias.part,
638 parts_alias.quantity).\
639 filter(parts_alias.part==incl_alias.c.sub_part)
640 )
642 q = session.query(
643 included_parts.c.sub_part,
644 func.sum(included_parts.c.quantity).
645 label('total_quantity')
646 ).\
647 group_by(included_parts.c.sub_part)
649 .. seealso::
651 :meth:`_expression.HasCTE.cte`
653 """
654 return self.enable_eagerloads(False).statement.cte(
655 name=name, recursive=recursive
656 )
658 def label(self, name):
659 """Return the full SELECT statement represented by this
660 :class:`_query.Query`, converted
661 to a scalar subquery with a label of the given name.
663 Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.label`.
665 """
667 return self.enable_eagerloads(False).statement.label(name)
669 def as_scalar(self):
670 """Return the full SELECT statement represented by this
671 :class:`_query.Query`, converted to a scalar subquery.
673 Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.as_scalar`.
675 """
677 return self.enable_eagerloads(False).statement.as_scalar()
679 @property
680 def selectable(self):
681 """Return the :class:`_expression.Select` object emitted by this
682 :class:`_query.Query`.
684 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
686 query.enable_eagerloads(False).with_labels().statement
688 """
689 return self.__clause_element__()
691 def __clause_element__(self):
692 return self.enable_eagerloads(False).with_labels().statement
694 @_generative()
695 def only_return_tuples(self, value):
696 """When set to True, the query results will always be a tuple.
698 This is specifically for single element queries. The default is False.
700 .. versionadded:: 1.2.5
702 .. seealso::
704 :meth:`_query.Query.is_single_entity`
706 """
707 self._only_return_tuples = value
709 @property
710 def is_single_entity(self):
711 """Indicates if this :class:`_query.Query`
712 returns tuples or single entities.
714 Returns True if this query returns a single entity for each instance
715 in its result list, and False if this query returns a tuple of entities
716 for each result.
718 .. versionadded:: 1.3.11
720 .. seealso::
722 :meth:`_query.Query.only_return_tuples`
724 """
725 return (
726 not self._only_return_tuples
727 and len(self._entities) == 1
728 and self._entities[0].supports_single_entity
729 )
731 @_generative()
732 def enable_eagerloads(self, value):
733 """Control whether or not eager joins and subqueries are
734 rendered.
736 When set to False, the returned Query will not render
737 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
738 :func:`~sqlalchemy.orm.subqueryload` options
739 or mapper-level ``lazy='joined'``/``lazy='subquery'``
740 configurations.
742 This is used primarily when nesting the Query's
743 statement into a subquery or other
744 selectable, or when using :meth:`_query.Query.yield_per`.
746 """
747 self._enable_eagerloads = value
749 def _no_yield_per(self, message):
750 raise sa_exc.InvalidRequestError(
751 "The yield_per Query option is currently not "
752 "compatible with %s eager loading. Please "
753 "specify lazyload('*') or query.enable_eagerloads(False) in "
754 "order to "
755 "proceed with query.yield_per()." % message
756 )
758 @_generative()
759 def with_labels(self):
760 """Apply column labels to the return value of Query.statement.
762 Indicates that this Query's `statement` accessor should return
763 a SELECT statement that applies labels to all columns in the
764 form <tablename>_<columnname>; this is commonly used to
765 disambiguate columns from multiple tables which have the same
766 name.
768 When the `Query` actually issues SQL to load rows, it always
769 uses column labeling.
771 .. note:: The :meth:`_query.Query.with_labels` method *only* applies
772 the output of :attr:`_query.Query.statement`, and *not* to any of
773 the result-row invoking systems of :class:`_query.Query` itself, e.
774 g.
775 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
776 To execute
777 a query using :meth:`_query.Query.with_labels`, invoke the
778 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
780 result = session.execute(query.with_labels().statement)
783 """
784 self._with_labels = True
786 @_generative()
787 def enable_assertions(self, value):
788 """Control whether assertions are generated.
790 When set to False, the returned Query will
791 not assert its state before certain operations,
792 including that LIMIT/OFFSET has not been applied
793 when filter() is called, no criterion exists
794 when get() is called, and no "from_statement()"
795 exists when filter()/order_by()/group_by() etc.
796 is called. This more permissive mode is used by
797 custom Query subclasses to specify criterion or
798 other modifiers outside of the usual usage patterns.
800 Care should be taken to ensure that the usage
801 pattern is even possible. A statement applied
802 by from_statement() will override any criterion
803 set by filter() or order_by(), for example.
805 """
806 self._enable_assertions = value
808 @property
809 def whereclause(self):
810 """A readonly attribute which returns the current WHERE criterion for
811 this Query.
813 This returned value is a SQL expression construct, or ``None`` if no
814 criterion has been established.
816 """
817 return self._criterion
819 @_generative()
820 def _with_current_path(self, path):
821 """indicate that this query applies to objects loaded
822 within a certain path.
824 Used by deferred loaders (see strategies.py) which transfer
825 query options from an originating query to a newly generated
826 query intended for the deferred load.
828 """
829 self._current_path = path
831 @_generative(_no_clauseelement_condition)
832 def with_polymorphic(
833 self, cls_or_mappers, selectable=None, polymorphic_on=None
834 ):
835 """Load columns for inheriting classes.
837 :meth:`_query.Query.with_polymorphic` applies transformations
838 to the "main" mapped class represented by this :class:`_query.Query`.
839 The "main" mapped class here means the :class:`_query.Query`
840 object's first argument is a full class, i.e.
841 ``session.query(SomeClass)``. These transformations allow additional
842 tables to be present in the FROM clause so that columns for a
843 joined-inheritance subclass are available in the query, both for the
844 purposes of load-time efficiency as well as the ability to use
845 these columns at query time.
847 See the documentation section :ref:`with_polymorphic` for
848 details on how this method is used.
850 """
852 if not self._primary_entity:
853 raise sa_exc.InvalidRequestError(
854 "No primary mapper set up for this Query."
855 )
856 entity = self._entities[0]._clone()
857 self._entities = [entity] + self._entities[1:]
858 entity.set_with_polymorphic(
859 self,
860 cls_or_mappers,
861 selectable=selectable,
862 polymorphic_on=polymorphic_on,
863 )
865 @_generative()
866 def yield_per(self, count):
867 r"""Yield only ``count`` rows at a time.
869 The purpose of this method is when fetching very large result sets
870 (> 10K rows), to batch results in sub-collections and yield them
871 out partially, so that the Python interpreter doesn't need to declare
872 very large areas of memory which is both time consuming and leads
873 to excessive memory use. The performance from fetching hundreds of
874 thousands of rows can often double when a suitable yield-per setting
875 (e.g. approximately 1000) is used, even with DBAPIs that buffer
876 rows (which are most).
878 The :meth:`_query.Query.yield_per` method **is not compatible
879 subqueryload eager loading or joinedload eager loading when
880 using collections**. It is potentially compatible with "select in"
881 eager loading, **provided the database driver supports multiple,
882 independent cursors** (pysqlite and psycopg2 are known to work,
883 MySQL and SQL Server ODBC drivers do not).
885 Therefore in some cases, it may be helpful to disable
886 eager loads, either unconditionally with
887 :meth:`_query.Query.enable_eagerloads`::
889 q = sess.query(Object).yield_per(100).enable_eagerloads(False)
891 Or more selectively using :func:`.lazyload`; such as with
892 an asterisk to specify the default loader scheme::
894 q = sess.query(Object).yield_per(100).\
895 options(lazyload('*'), joinedload(Object.some_related))
897 .. warning::
899 Use this method with caution; if the same instance is
900 present in more than one batch of rows, end-user changes
901 to attributes will be overwritten.
903 In particular, it's usually impossible to use this setting
904 with eagerly loaded collections (i.e. any lazy='joined' or
905 'subquery') since those collections will be cleared for a
906 new load when encountered in a subsequent result batch.
907 In the case of 'subquery' loading, the full result for all
908 rows is fetched which generally defeats the purpose of
909 :meth:`~sqlalchemy.orm.query.Query.yield_per`.
911 Also note that while
912 :meth:`~sqlalchemy.orm.query.Query.yield_per` will set the
913 ``stream_results`` execution option to True, currently
914 this is only understood by
915 :mod:`~sqlalchemy.dialects.postgresql.psycopg2`,
916 :mod:`~sqlalchemy.dialects.mysql.mysqldb` and
917 :mod:`~sqlalchemy.dialects.mysql.pymysql` dialects
918 which will stream results using server side cursors
919 instead of pre-buffer all rows for this query. Other
920 DBAPIs **pre-buffer all rows** before making them
921 available. The memory use of raw database rows is much less
922 than that of an ORM-mapped object, but should still be taken into
923 consideration when benchmarking.
925 .. seealso::
927 :meth:`_query.Query.enable_eagerloads`
929 """
930 self._yield_per = count
931 self._execution_options = self._execution_options.union(
932 {"stream_results": True, "max_row_buffer": count}
933 )
935 def get(self, ident):
936 """Return an instance based on the given primary key identifier,
937 or ``None`` if not found.
939 E.g.::
941 my_user = session.query(User).get(5)
943 some_object = session.query(VersionedFoo).get((5, 10))
945 some_object = session.query(VersionedFoo).get(
946 {"id": 5, "version_id": 10})
948 :meth:`_query.Query.get` is special in that it provides direct
949 access to the identity map of the owning :class:`.Session`.
950 If the given primary key identifier is present
951 in the local identity map, the object is returned
952 directly from this collection and no SQL is emitted,
953 unless the object has been marked fully expired.
954 If not present,
955 a SELECT is performed in order to locate the object.
957 :meth:`_query.Query.get` also will perform a check if
958 the object is present in the identity map and
959 marked as expired - a SELECT
960 is emitted to refresh the object as well as to
961 ensure that the row is still present.
962 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
964 :meth:`_query.Query.get` is only used to return a single
965 mapped instance, not multiple instances or
966 individual column constructs, and strictly
967 on a single primary key value. The originating
968 :class:`_query.Query` must be constructed in this way,
969 i.e. against a single mapped entity,
970 with no additional filtering criterion. Loading
971 options via :meth:`_query.Query.options` may be applied
972 however, and will be used if the object is not
973 yet locally present.
975 A lazy-loading, many-to-one attribute configured
976 by :func:`_orm.relationship`, using a simple
977 foreign-key-to-primary-key criterion, will also use an
978 operation equivalent to :meth:`_query.Query.get` in order to retrieve
979 the target value from the local identity map
980 before querying the database. See :doc:`/orm/loading_relationships`
981 for further details on relationship loading.
983 :param ident: A scalar, tuple, or dictionary representing the
984 primary key. For a composite (e.g. multiple column) primary key,
985 a tuple or dictionary should be passed.
987 For a single-column primary key, the scalar calling form is typically
988 the most expedient. If the primary key of a row is the value "5",
989 the call looks like::
991 my_object = query.get(5)
993 The tuple form contains primary key values typically in
994 the order in which they correspond to the mapped
995 :class:`_schema.Table`
996 object's primary key columns, or if the
997 :paramref:`_orm.Mapper.primary_key` configuration parameter were used
998 , in
999 the order used for that parameter. For example, if the primary key
1000 of a row is represented by the integer
1001 digits "5, 10" the call would look like::
1003 my_object = query.get((5, 10))
1005 The dictionary form should include as keys the mapped attribute names
1006 corresponding to each element of the primary key. If the mapped class
1007 has the attributes ``id``, ``version_id`` as the attributes which
1008 store the object's primary key value, the call would look like::
1010 my_object = query.get({"id": 5, "version_id": 10})
1012 .. versionadded:: 1.3 the :meth:`_query.Query.get`
1013 method now optionally
1014 accepts a dictionary of attribute names to values in order to
1015 indicate a primary key identifier.
1018 :return: The object instance, or ``None``.
1020 """
1021 return self._get_impl(ident, loading.load_on_pk_identity)
1023 def _identity_lookup(
1024 self,
1025 mapper,
1026 primary_key_identity,
1027 identity_token=None,
1028 passive=attributes.PASSIVE_OFF,
1029 lazy_loaded_from=None,
1030 ):
1031 """Locate an object in the identity map.
1033 Given a primary key identity, constructs an identity key and then
1034 looks in the session's identity map. If present, the object may
1035 be run through unexpiration rules (e.g. load unloaded attributes,
1036 check if was deleted).
1038 For performance reasons, while the :class:`_query.Query` must be
1039 instantiated, it may be instantiated with no entities, and the
1040 mapper is passed::
1042 obj = session.query()._identity_lookup(inspect(SomeClass), (1, ))
1044 :param mapper: mapper in use
1045 :param primary_key_identity: the primary key we are searching for, as
1046 a tuple.
1047 :param identity_token: identity token that should be used to create
1048 the identity key. Used as is, however overriding subclasses can
1049 repurpose this in order to interpret the value in a special way,
1050 such as if None then look among multiple target tokens.
1051 :param passive: passive load flag passed to
1052 :func:`.loading.get_from_identity`, which impacts the behavior if
1053 the object is found; the object may be validated and/or unexpired
1054 if the flag allows for SQL to be emitted.
1055 :param lazy_loaded_from: an :class:`.InstanceState` that is
1056 specifically asking for this identity as a related identity. Used
1057 for sharding schemes where there is a correspondence between an object
1058 and a related object being lazy-loaded (or otherwise
1059 relationship-loaded).
1061 .. versionadded:: 1.2.9
1063 :return: None if the object is not found in the identity map, *or*
1064 if the object was unexpired and found to have been deleted.
1065 if passive flags disallow SQL and the object is expired, returns
1066 PASSIVE_NO_RESULT. In all other cases the instance is returned.
1068 .. versionadded:: 1.2.7
1070 """
1072 key = mapper.identity_key_from_primary_key(
1073 primary_key_identity, identity_token=identity_token
1074 )
1075 return loading.get_from_identity(self.session, mapper, key, passive)
1077 def _get_impl(self, primary_key_identity, db_load_fn, identity_token=None):
1078 # convert composite types to individual args
1079 if hasattr(primary_key_identity, "__composite_values__"):
1080 primary_key_identity = primary_key_identity.__composite_values__()
1082 mapper = self._only_full_mapper_zero("get")
1084 is_dict = isinstance(primary_key_identity, dict)
1085 if not is_dict:
1086 primary_key_identity = util.to_list(
1087 primary_key_identity, default=(None,)
1088 )
1090 if len(primary_key_identity) != len(mapper.primary_key):
1091 raise sa_exc.InvalidRequestError(
1092 "Incorrect number of values in identifier to formulate "
1093 "primary key for query.get(); primary key columns are %s"
1094 % ",".join("'%s'" % c for c in mapper.primary_key)
1095 )
1097 if is_dict:
1098 try:
1099 primary_key_identity = list(
1100 primary_key_identity[prop.key]
1101 for prop in mapper._identity_key_props
1102 )
1104 except KeyError as err:
1105 util.raise_(
1106 sa_exc.InvalidRequestError(
1107 "Incorrect names of values in identifier to formulate "
1108 "primary key for query.get(); primary key attribute "
1109 "names are %s"
1110 % ",".join(
1111 "'%s'" % prop.key
1112 for prop in mapper._identity_key_props
1113 )
1114 ),
1115 replace_context=err,
1116 )
1118 if (
1119 not self._populate_existing
1120 and not mapper.always_refresh
1121 and self._for_update_arg is None
1122 ):
1124 instance = self._identity_lookup(
1125 mapper, primary_key_identity, identity_token=identity_token
1126 )
1128 if instance is not None:
1129 self._get_existing_condition()
1130 # reject calls for id in identity map but class
1131 # mismatch.
1132 if not issubclass(instance.__class__, mapper.class_):
1133 return None
1134 return instance
1135 elif instance is attributes.PASSIVE_CLASS_MISMATCH:
1136 return None
1138 return db_load_fn(self, primary_key_identity)
1140 @_generative()
1141 def correlate(self, *args):
1142 """Return a :class:`_query.Query`
1143 construct which will correlate the given
1144 FROM clauses to that of an enclosing :class:`_query.Query` or
1145 :func:`_expression.select`.
1147 The method here accepts mapped classes, :func:`.aliased` constructs,
1148 and :func:`.mapper` constructs as arguments, which are resolved into
1149 expression constructs, in addition to appropriate expression
1150 constructs.
1152 The correlation arguments are ultimately passed to
1153 :meth:`_expression.Select.correlate`
1154 after coercion to expression constructs.
1156 The correlation arguments take effect in such cases
1157 as when :meth:`_query.Query.from_self` is used, or when
1158 a subquery as returned by :meth:`_query.Query.subquery` is
1159 embedded in another :func:`_expression.select` construct.
1161 """
1163 for s in args:
1164 if s is None:
1165 self._correlate = self._correlate.union([None])
1166 else:
1167 self._correlate = self._correlate.union(
1168 sql_util.surface_selectables(_interpret_as_from(s))
1169 )
1171 @_generative()
1172 def autoflush(self, setting):
1173 """Return a Query with a specific 'autoflush' setting.
1175 Note that a Session with autoflush=False will
1176 not autoflush, even if this flag is set to True at the
1177 Query level. Therefore this flag is usually used only
1178 to disable autoflush for a specific Query.
1180 """
1181 self._autoflush = setting
1183 @_generative()
1184 def populate_existing(self):
1185 """Return a :class:`_query.Query`
1186 that will expire and refresh all instances
1187 as they are loaded, or reused from the current :class:`.Session`.
1189 :meth:`.populate_existing` does not improve behavior when
1190 the ORM is used normally - the :class:`.Session` object's usual
1191 behavior of maintaining a transaction and expiring all attributes
1192 after rollback or commit handles object state automatically.
1193 This method is not intended for general use.
1195 """
1196 self._populate_existing = True
1198 @_generative()
1199 def _with_invoke_all_eagers(self, value):
1200 """Set the 'invoke all eagers' flag which causes joined- and
1201 subquery loaders to traverse into already-loaded related objects
1202 and collections.
1204 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1206 """
1207 self._invoke_all_eagers = value
1209 def with_parent(self, instance, property=None, from_entity=None): # noqa
1210 """Add filtering criterion that relates the given instance
1211 to a child object or collection, using its attribute state
1212 as well as an established :func:`_orm.relationship()`
1213 configuration.
1215 The method uses the :func:`.with_parent` function to generate
1216 the clause, the result of which is passed to
1217 :meth:`_query.Query.filter`.
1219 Parameters are the same as :func:`.with_parent`, with the exception
1220 that the given property can be None, in which case a search is
1221 performed against this :class:`_query.Query` object's target mapper.
1223 :param instance:
1224 An instance which has some :func:`_orm.relationship`.
1226 :param property:
1227 String property name, or class-bound attribute, which indicates
1228 what relationship from the instance should be used to reconcile the
1229 parent/child relationship.
1231 :param from_entity:
1232 Entity in which to consider as the left side. This defaults to the
1233 "zero" entity of the :class:`_query.Query` itself.
1235 """
1237 if from_entity:
1238 entity_zero = inspect(from_entity)
1239 else:
1240 entity_zero = self._entity_zero()
1241 if property is None:
1243 mapper = object_mapper(instance)
1245 for prop in mapper.iterate_properties:
1246 if (
1247 isinstance(prop, properties.RelationshipProperty)
1248 and prop.mapper is entity_zero.mapper
1249 ):
1250 property = prop # noqa
1251 break
1252 else:
1253 raise sa_exc.InvalidRequestError(
1254 "Could not locate a property which relates instances "
1255 "of class '%s' to instances of class '%s'"
1256 % (
1257 entity_zero.mapper.class_.__name__,
1258 instance.__class__.__name__,
1259 )
1260 )
1262 return self.filter(with_parent(instance, property, entity_zero.entity))
1264 @_generative()
1265 def add_entity(self, entity, alias=None):
1266 """add a mapped entity to the list of result columns
1267 to be returned."""
1269 if alias is not None:
1270 entity = aliased(entity, alias)
1272 self._entities = list(self._entities)
1273 m = _MapperEntity(self, entity)
1274 self._set_entity_selectables([m])
1276 @_generative()
1277 def with_session(self, session):
1278 """Return a :class:`_query.Query` that will use the given
1279 :class:`.Session`.
1281 While the :class:`_query.Query`
1282 object is normally instantiated using the
1283 :meth:`.Session.query` method, it is legal to build the
1284 :class:`_query.Query`
1285 directly without necessarily using a :class:`.Session`. Such a
1286 :class:`_query.Query` object, or any :class:`_query.Query`
1287 already associated
1288 with a different :class:`.Session`, can produce a new
1289 :class:`_query.Query`
1290 object associated with a target session using this method::
1292 from sqlalchemy.orm import Query
1294 query = Query([MyClass]).filter(MyClass.id == 5)
1296 result = query.with_session(my_session).one()
1298 """
1300 self.session = session
1302 def from_self(self, *entities):
1303 r"""return a Query that selects from this Query's
1304 SELECT statement.
1306 :meth:`_query.Query.from_self` essentially turns the SELECT statement
1307 into a SELECT of itself. Given a query such as::
1309 q = session.query(User).filter(User.name.like('e%'))
1311 Given the :meth:`_query.Query.from_self` version::
1313 q = session.query(User).filter(User.name.like('e%')).from_self()
1315 This query renders as:
1317 .. sourcecode:: sql
1319 SELECT anon_1.user_id AS anon_1_user_id,
1320 anon_1.user_name AS anon_1_user_name
1321 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1322 FROM "user"
1323 WHERE "user".name LIKE :name_1) AS anon_1
1325 There are lots of cases where :meth:`_query.Query.from_self`
1326 may be useful.
1327 A simple one is where above, we may want to apply a row LIMIT to
1328 the set of user objects we query against, and then apply additional
1329 joins against that row-limited set::
1331 q = session.query(User).filter(User.name.like('e%')).\
1332 limit(5).from_self().\
1333 join(User.addresses).filter(Address.email.like('q%'))
1335 The above query joins to the ``Address`` entity but only against the
1336 first five results of the ``User`` query:
1338 .. sourcecode:: sql
1340 SELECT anon_1.user_id AS anon_1_user_id,
1341 anon_1.user_name AS anon_1_user_name
1342 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1343 FROM "user"
1344 WHERE "user".name LIKE :name_1
1345 LIMIT :param_1) AS anon_1
1346 JOIN address ON anon_1.user_id = address.user_id
1347 WHERE address.email LIKE :email_1
1349 **Automatic Aliasing**
1351 Another key behavior of :meth:`_query.Query.from_self`
1352 is that it applies
1353 **automatic aliasing** to the entities inside the subquery, when
1354 they are referenced on the outside. Above, if we continue to
1355 refer to the ``User`` entity without any additional aliasing applied
1356 to it, those references wil be in terms of the subquery::
1358 q = session.query(User).filter(User.name.like('e%')).\
1359 limit(5).from_self().\
1360 join(User.addresses).filter(Address.email.like('q%')).\
1361 order_by(User.name)
1363 The ORDER BY against ``User.name`` is aliased to be in terms of the
1364 inner subquery:
1366 .. sourcecode:: sql
1368 SELECT anon_1.user_id AS anon_1_user_id,
1369 anon_1.user_name AS anon_1_user_name
1370 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1371 FROM "user"
1372 WHERE "user".name LIKE :name_1
1373 LIMIT :param_1) AS anon_1
1374 JOIN address ON anon_1.user_id = address.user_id
1375 WHERE address.email LIKE :email_1 ORDER BY anon_1.user_name
1377 The automatic aliasing feature only works in a **limited** way,
1378 for simple filters and orderings. More ambitious constructions
1379 such as referring to the entity in joins should prefer to use
1380 explicit subquery objects, typically making use of the
1381 :meth:`_query.Query.subquery`
1382 method to produce an explicit subquery object.
1383 Always test the structure of queries by viewing the SQL to ensure
1384 a particular structure does what's expected!
1386 **Changing the Entities**
1388 :meth:`_query.Query.from_self`
1389 also includes the ability to modify what
1390 columns are being queried. In our example, we want ``User.id``
1391 to be queried by the inner query, so that we can join to the
1392 ``Address`` entity on the outside, but we only wanted the outer
1393 query to return the ``Address.email`` column::
1395 q = session.query(User).filter(User.name.like('e%')).\
1396 limit(5).from_self(Address.email).\
1397 join(User.addresses).filter(Address.email.like('q%'))
1399 yielding:
1401 .. sourcecode:: sql
1403 SELECT address.email AS address_email
1404 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1405 FROM "user"
1406 WHERE "user".name LIKE :name_1
1407 LIMIT :param_1) AS anon_1
1408 JOIN address ON anon_1.user_id = address.user_id
1409 WHERE address.email LIKE :email_1
1411 **Looking out for Inner / Outer Columns**
1413 Keep in mind that when referring to columns that originate from
1414 inside the subquery, we need to ensure they are present in the
1415 columns clause of the subquery itself; this is an ordinary aspect of
1416 SQL. For example, if we wanted to load from a joined entity inside
1417 the subquery using :func:`.contains_eager`, we need to add those
1418 columns. Below illustrates a join of ``Address`` to ``User``,
1419 then a subquery, and then we'd like :func:`.contains_eager` to access
1420 the ``User`` columns::
1422 q = session.query(Address).join(Address.user).\
1423 filter(User.name.like('e%'))
1425 q = q.add_entity(User).from_self().\
1426 options(contains_eager(Address.user))
1428 We use :meth:`_query.Query.add_entity` above **before** we call
1429 :meth:`_query.Query.from_self`
1430 so that the ``User`` columns are present
1431 in the inner subquery, so that they are available to the
1432 :func:`.contains_eager` modifier we are using on the outside,
1433 producing:
1435 .. sourcecode:: sql
1437 SELECT anon_1.address_id AS anon_1_address_id,
1438 anon_1.address_email AS anon_1_address_email,
1439 anon_1.address_user_id AS anon_1_address_user_id,
1440 anon_1.user_id AS anon_1_user_id,
1441 anon_1.user_name AS anon_1_user_name
1442 FROM (
1443 SELECT address.id AS address_id,
1444 address.email AS address_email,
1445 address.user_id AS address_user_id,
1446 "user".id AS user_id,
1447 "user".name AS user_name
1448 FROM address JOIN "user" ON "user".id = address.user_id
1449 WHERE "user".name LIKE :name_1) AS anon_1
1451 If we didn't call ``add_entity(User)``, but still asked
1452 :func:`.contains_eager` to load the ``User`` entity, it would be
1453 forced to add the table on the outside without the correct
1454 join criteria - note the ``anon1, "user"`` phrase at
1455 the end:
1457 .. sourcecode:: sql
1459 -- incorrect query
1460 SELECT anon_1.address_id AS anon_1_address_id,
1461 anon_1.address_email AS anon_1_address_email,
1462 anon_1.address_user_id AS anon_1_address_user_id,
1463 "user".id AS user_id,
1464 "user".name AS user_name
1465 FROM (
1466 SELECT address.id AS address_id,
1467 address.email AS address_email,
1468 address.user_id AS address_user_id
1469 FROM address JOIN "user" ON "user".id = address.user_id
1470 WHERE "user".name LIKE :name_1) AS anon_1, "user"
1472 :param \*entities: optional list of entities which will replace
1473 those being selected.
1475 """
1476 fromclause = (
1477 self.with_labels()
1478 .enable_eagerloads(False)
1479 .statement.correlate(None)
1480 )
1481 q = self._from_selectable(fromclause)
1482 q._enable_single_crit = False
1483 q._select_from_entity = self._entity_zero()
1484 if entities:
1485 q._set_entities(entities)
1486 return q
1488 @_generative()
1489 def _set_enable_single_crit(self, val):
1490 self._enable_single_crit = val
1492 @_generative()
1493 def _from_selectable(self, fromclause):
1494 for attr in (
1495 "_statement",
1496 "_criterion",
1497 "_order_by",
1498 "_group_by",
1499 "_limit",
1500 "_offset",
1501 "_joinpath",
1502 "_joinpoint",
1503 "_distinct",
1504 "_having",
1505 "_prefixes",
1506 "_suffixes",
1507 ):
1508 self.__dict__.pop(attr, None)
1509 self._set_select_from([fromclause], True)
1511 # this enables clause adaptation for non-ORM
1512 # expressions.
1513 self._orm_only_from_obj_alias = False
1515 old_entities = self._entities
1516 self._entities = []
1517 for e in old_entities:
1518 e.adapt_to_selectable(self, self._from_obj[0])
1520 def values(self, *columns):
1521 """Return an iterator yielding result tuples corresponding
1522 to the given list of columns"""
1524 if not columns:
1525 return iter(())
1526 q = self._clone()
1527 q._set_entities(columns, entity_wrapper=_ColumnEntity)
1528 if not q._yield_per:
1529 q._yield_per = 10
1530 return iter(q)
1532 _values = values
1534 def value(self, column):
1535 """Return a scalar result corresponding to the given
1536 column expression."""
1537 try:
1538 return next(self.values(column))[0]
1539 except StopIteration:
1540 return None
1542 @_generative()
1543 def with_entities(self, *entities):
1544 r"""Return a new :class:`_query.Query`
1545 replacing the SELECT list with the
1546 given entities.
1548 e.g.::
1550 # Users, filtered on some arbitrary criterion
1551 # and then ordered by related email address
1552 q = session.query(User).\
1553 join(User.address).\
1554 filter(User.name.like('%ed%')).\
1555 order_by(Address.email)
1557 # given *only* User.id==5, Address.email, and 'q', what
1558 # would the *next* User in the result be ?
1559 subq = q.with_entities(Address.email).\
1560 order_by(None).\
1561 filter(User.id==5).\
1562 subquery()
1563 q = q.join((subq, subq.c.email < Address.email)).\
1564 limit(1)
1566 """
1567 self._set_entities(entities)
1569 @_generative()
1570 def add_columns(self, *column):
1571 """Add one or more column expressions to the list
1572 of result columns to be returned."""
1574 self._entities = list(self._entities)
1575 l = len(self._entities)
1576 for c in column:
1577 _ColumnEntity(self, c)
1578 # _ColumnEntity may add many entities if the
1579 # given arg is a FROM clause
1580 self._set_entity_selectables(self._entities[l:])
1582 @util.pending_deprecation(
1583 "0.7",
1584 ":meth:`.add_column` is superseded " "by :meth:`.add_columns`",
1585 False,
1586 )
1587 def add_column(self, column):
1588 """Add a column expression to the list of result columns to be
1589 returned.
1591 Pending deprecation: :meth:`.add_column` will be superseded by
1592 :meth:`.add_columns`.
1594 """
1595 return self.add_columns(column)
1597 def options(self, *args):
1598 """Return a new :class:`_query.Query` object,
1599 applying the given list of
1600 mapper options.
1602 Most supplied options regard changing how column- and
1603 relationship-mapped attributes are loaded.
1605 .. seealso::
1607 :ref:`deferred_options`
1609 :ref:`relationship_loader_options`
1611 """
1612 return self._options(False, *args)
1614 def _conditional_options(self, *args):
1615 return self._options(True, *args)
1617 @_generative()
1618 def _options(self, conditional, *args):
1619 # most MapperOptions write to the '_attributes' dictionary,
1620 # so copy that as well
1621 self._attributes = self._attributes.copy()
1622 if "_unbound_load_dedupes" not in self._attributes:
1623 self._attributes["_unbound_load_dedupes"] = set()
1624 opts = tuple(util.flatten_iterator(args))
1625 self._with_options = self._with_options + opts
1626 if conditional:
1627 for opt in opts:
1628 opt.process_query_conditionally(self)
1629 else:
1630 for opt in opts:
1631 opt.process_query(self)
1633 def with_transformation(self, fn):
1634 """Return a new :class:`_query.Query` object transformed by
1635 the given function.
1637 E.g.::
1639 def filter_something(criterion):
1640 def transform(q):
1641 return q.filter(criterion)
1642 return transform
1644 q = q.with_transformation(filter_something(x==5))
1646 This allows ad-hoc recipes to be created for :class:`_query.Query`
1647 objects. See the example at :ref:`hybrid_transformers`.
1649 """
1650 return fn(self)
1652 @_generative()
1653 def with_hint(self, selectable, text, dialect_name="*"):
1654 """Add an indexing or other executional context
1655 hint for the given entity or selectable to
1656 this :class:`_query.Query`.
1658 Functionality is passed straight through to
1659 :meth:`~sqlalchemy.sql.expression.Select.with_hint`,
1660 with the addition that ``selectable`` can be a
1661 :class:`_schema.Table`, :class:`_expression.Alias`,
1662 or ORM entity / mapped class
1663 /etc.
1665 .. seealso::
1667 :meth:`_query.Query.with_statement_hint`
1669 :meth:`.Query.prefix_with` - generic SELECT prefixing which also
1670 can suit some database-specific HINT syntaxes such as MySQL
1671 optimizer hints
1673 """
1674 if selectable is not None:
1675 selectable = inspect(selectable).selectable
1677 self._with_hints += ((selectable, text, dialect_name),)
1679 def with_statement_hint(self, text, dialect_name="*"):
1680 """add a statement hint to this :class:`_expression.Select`.
1682 This method is similar to :meth:`_expression.Select.with_hint`
1683 except that
1684 it does not require an individual table, and instead applies to the
1685 statement as a whole.
1687 This feature calls down into
1688 :meth:`_expression.Select.with_statement_hint`.
1690 .. versionadded:: 1.0.0
1692 .. seealso::
1694 :meth:`_query.Query.with_hint`
1696 """
1697 return self.with_hint(None, text, dialect_name)
1699 def get_execution_options(self):
1700 """ Get the non-SQL options which will take effect during execution.
1702 .. versionadded:: 1.3
1704 .. seealso::
1706 :meth:`_query.Query.execution_options`
1707 """
1708 return self._execution_options
1710 @_generative()
1711 def execution_options(self, **kwargs):
1712 """ Set non-SQL options which take effect during execution.
1714 The options are the same as those accepted by
1715 :meth:`_engine.Connection.execution_options`.
1717 Note that the ``stream_results`` execution option is enabled
1718 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1719 method is used.
1721 .. seealso::
1723 :meth:`_query.Query.get_execution_options`
1725 """
1726 self._execution_options = self._execution_options.union(kwargs)
1728 @_generative()
1729 @util.deprecated(
1730 "0.9",
1731 "The :meth:`_query.Query.with_lockmode` "
1732 "method is deprecated and will "
1733 "be removed in a future release. Please refer to "
1734 ":meth:`_query.Query.with_for_update`. ",
1735 )
1736 def with_lockmode(self, mode):
1737 """Return a new :class:`_query.Query`
1738 object with the specified "locking mode",
1739 which essentially refers to the ``FOR UPDATE`` clause.
1741 :param mode: a string representing the desired locking mode.
1742 Valid values are:
1744 * ``None`` - translates to no lockmode
1746 * ``'update'`` - translates to ``FOR UPDATE``
1747 (standard SQL, supported by most dialects)
1749 * ``'update_nowait'`` - translates to ``FOR UPDATE NOWAIT``
1750 (supported by Oracle, PostgreSQL 8.1 upwards)
1752 * ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL),
1753 and ``FOR SHARE`` (for PostgreSQL)
1755 .. seealso::
1757 :meth:`_query.Query.with_for_update` - improved API for
1758 specifying the ``FOR UPDATE`` clause.
1760 """
1761 self._for_update_arg = LockmodeArg.parse_legacy_query(mode)
1763 @_generative()
1764 def with_for_update(
1765 self,
1766 read=False,
1767 nowait=False,
1768 of=None,
1769 skip_locked=False,
1770 key_share=False,
1771 ):
1772 """return a new :class:`_query.Query`
1773 with the specified options for the
1774 ``FOR UPDATE`` clause.
1776 The behavior of this method is identical to that of
1777 :meth:`_expression.SelectBase.with_for_update`.
1778 When called with no arguments,
1779 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1780 appended. When additional arguments are specified, backend-specific
1781 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1782 can take effect.
1784 E.g.::
1786 q = sess.query(User).with_for_update(nowait=True, of=User)
1788 The above query on a PostgreSQL backend will render like::
1790 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1792 .. versionadded:: 0.9.0 :meth:`_query.Query.with_for_update`
1793 supersedes
1794 the :meth:`_query.Query.with_lockmode` method.
1796 .. seealso::
1798 :meth:`_expression.GenerativeSelect.with_for_update`
1799 - Core level method with
1800 full argument and behavioral description.
1802 """
1803 self._for_update_arg = LockmodeArg(
1804 read=read,
1805 nowait=nowait,
1806 of=of,
1807 skip_locked=skip_locked,
1808 key_share=key_share,
1809 )
1811 @_generative()
1812 def params(self, *args, **kwargs):
1813 r"""add values for bind parameters which may have been
1814 specified in filter().
1816 parameters may be specified using \**kwargs, or optionally a single
1817 dictionary as the first positional argument. The reason for both is
1818 that \**kwargs is convenient, however some parameter dictionaries
1819 contain unicode keys in which case \**kwargs cannot be used.
1821 """
1822 if len(args) == 1:
1823 kwargs.update(args[0])
1824 elif len(args) > 0:
1825 raise sa_exc.ArgumentError(
1826 "params() takes zero or one positional argument, "
1827 "which is a dictionary."
1828 )
1829 self._params = self._params.copy()
1830 self._params.update(kwargs)
1832 @_generative(_no_statement_condition, _no_limit_offset)
1833 def filter(self, *criterion):
1834 r"""apply the given filtering criterion to a copy
1835 of this :class:`_query.Query`, using SQL expressions.
1837 e.g.::
1839 session.query(MyClass).filter(MyClass.name == 'some name')
1841 Multiple criteria may be specified as comma separated; the effect
1842 is that they will be joined together using the :func:`.and_`
1843 function::
1845 session.query(MyClass).\
1846 filter(MyClass.name == 'some name', MyClass.id > 5)
1848 The criterion is any SQL expression object applicable to the
1849 WHERE clause of a select. String expressions are coerced
1850 into SQL expression constructs via the :func:`_expression.text`
1851 construct.
1853 .. seealso::
1855 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1857 """
1858 for criterion in list(criterion):
1859 criterion = expression._expression_literal_as_text(criterion)
1861 criterion = self._adapt_clause(criterion, True, True)
1863 if self._criterion is not None:
1864 self._criterion = self._criterion & criterion
1865 else:
1866 self._criterion = criterion
1868 def filter_by(self, **kwargs):
1869 r"""apply the given filtering criterion to a copy
1870 of this :class:`_query.Query`, using keyword expressions.
1872 e.g.::
1874 session.query(MyClass).filter_by(name = 'some name')
1876 Multiple criteria may be specified as comma separated; the effect
1877 is that they will be joined together using the :func:`.and_`
1878 function::
1880 session.query(MyClass).\
1881 filter_by(name = 'some name', id = 5)
1883 The keyword expressions are extracted from the primary
1884 entity of the query, or the last entity that was the
1885 target of a call to :meth:`_query.Query.join`.
1887 .. seealso::
1889 :meth:`_query.Query.filter` - filter on SQL expressions.
1891 """
1893 zero = self._joinpoint_zero()
1894 if zero is None:
1895 raise sa_exc.InvalidRequestError(
1896 "Can't use filter_by when the first entity '%s' of a query "
1897 "is not a mapped class. Please use the filter method instead, "
1898 "or change the order of the entities in the query"
1899 % self._query_entity_zero()
1900 )
1902 clauses = [
1903 _entity_descriptor(zero, key) == value
1904 for key, value in kwargs.items()
1905 ]
1906 return self.filter(*clauses)
1908 @_generative(_no_statement_condition, _no_limit_offset)
1909 def order_by(self, *criterion):
1910 """apply one or more ORDER BY criterion to the query and return
1911 the newly resulting ``Query``
1913 All existing ORDER BY settings can be suppressed by
1914 passing ``None`` - this will suppress any ordering configured
1915 on the :func:`.mapper` object using the deprecated
1916 :paramref:`.mapper.order_by` parameter.
1918 """
1920 if len(criterion) == 1:
1921 if criterion[0] is False:
1922 if "_order_by" in self.__dict__:
1923 self._order_by = False
1924 return
1925 if criterion[0] is None:
1926 self._order_by = None
1927 return
1929 criterion = self._adapt_col_list(criterion)
1931 if self._order_by is False or self._order_by is None:
1932 self._order_by = criterion
1933 else:
1934 self._order_by = self._order_by + criterion
1936 @_generative(_no_statement_condition, _no_limit_offset)
1937 def group_by(self, *criterion):
1938 """apply one or more GROUP BY criterion to the query and return
1939 the newly resulting :class:`_query.Query`
1941 All existing GROUP BY settings can be suppressed by
1942 passing ``None`` - this will suppress any GROUP BY configured
1943 on mappers as well.
1945 .. versionadded:: 1.1 GROUP BY can be cancelled by passing None,
1946 in the same way as ORDER BY.
1948 """
1950 if len(criterion) == 1:
1951 if criterion[0] is None:
1952 self._group_by = False
1953 return
1955 criterion = list(chain(*[_orm_columns(c) for c in criterion]))
1956 criterion = self._adapt_col_list(criterion)
1958 if self._group_by is False:
1959 self._group_by = criterion
1960 else:
1961 self._group_by = self._group_by + criterion
1963 @_generative(_no_statement_condition, _no_limit_offset)
1964 def having(self, criterion):
1965 r"""apply a HAVING criterion to the query and return the
1966 newly resulting :class:`_query.Query`.
1968 :meth:`_query.Query.having` is used in conjunction with
1969 :meth:`_query.Query.group_by`.
1971 HAVING criterion makes it possible to use filters on aggregate
1972 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
1974 q = session.query(User.id).\
1975 join(User.addresses).\
1976 group_by(User.id).\
1977 having(func.count(Address.id) > 2)
1979 """
1981 criterion = expression._expression_literal_as_text(criterion)
1983 if criterion is not None and not isinstance(
1984 criterion, sql.ClauseElement
1985 ):
1986 raise sa_exc.ArgumentError(
1987 "having() argument must be of type "
1988 "sqlalchemy.sql.ClauseElement or string"
1989 )
1991 criterion = self._adapt_clause(criterion, True, True)
1993 if self._having is not None:
1994 self._having = self._having & criterion
1995 else:
1996 self._having = criterion
1998 def _set_op(self, expr_fn, *q):
1999 return self._from_selectable(
2000 expr_fn(*([self] + list(q)))
2001 )._set_enable_single_crit(False)
2003 def union(self, *q):
2004 """Produce a UNION of this Query against one or more queries.
2006 e.g.::
2008 q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
2009 q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
2011 q3 = q1.union(q2)
2013 The method accepts multiple Query objects so as to control
2014 the level of nesting. A series of ``union()`` calls such as::
2016 x.union(y).union(z).all()
2018 will nest on each ``union()``, and produces::
2020 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
2021 SELECT * FROM y) UNION SELECT * FROM Z)
2023 Whereas::
2025 x.union(y, z).all()
2027 produces::
2029 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
2030 SELECT * FROM Z)
2032 Note that many database backends do not allow ORDER BY to
2033 be rendered on a query called within UNION, EXCEPT, etc.
2034 To disable all ORDER BY clauses including those configured
2035 on mappers, issue ``query.order_by(None)`` - the resulting
2036 :class:`_query.Query` object will not render ORDER BY within
2037 its SELECT statement.
2039 """
2040 return self._set_op(expression.union, *q)
2042 def union_all(self, *q):
2043 """Produce a UNION ALL of this Query against one or more queries.
2045 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2046 that method for usage examples.
2048 """
2049 return self._set_op(expression.union_all, *q)
2051 def intersect(self, *q):
2052 """Produce an INTERSECT of this Query against one or more queries.
2054 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2055 that method for usage examples.
2057 """
2058 return self._set_op(expression.intersect, *q)
2060 def intersect_all(self, *q):
2061 """Produce an INTERSECT ALL of this Query against one or more queries.
2063 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2064 that method for usage examples.
2066 """
2067 return self._set_op(expression.intersect_all, *q)
2069 def except_(self, *q):
2070 """Produce an EXCEPT of this Query against one or more queries.
2072 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2073 that method for usage examples.
2075 """
2076 return self._set_op(expression.except_, *q)
2078 def except_all(self, *q):
2079 """Produce an EXCEPT ALL of this Query against one or more queries.
2081 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2082 that method for usage examples.
2084 """
2085 return self._set_op(expression.except_all, *q)
2087 def join(self, *props, **kwargs):
2088 r"""Create a SQL JOIN against this :class:`_query.Query`
2089 object's criterion
2090 and apply generatively, returning the newly resulting
2091 :class:`_query.Query`.
2093 **Simple Relationship Joins**
2095 Consider a mapping between two classes ``User`` and ``Address``,
2096 with a relationship ``User.addresses`` representing a collection
2097 of ``Address`` objects associated with each ``User``. The most
2098 common usage of :meth:`_query.Query.join`
2099 is to create a JOIN along this
2100 relationship, using the ``User.addresses`` attribute as an indicator
2101 for how this should occur::
2103 q = session.query(User).join(User.addresses)
2105 Where above, the call to :meth:`_query.Query.join` along
2106 ``User.addresses`` will result in SQL approximately equivalent to::
2108 SELECT user.id, User.name
2109 FROM user JOIN address ON user.id = address.user_id
2111 In the above example we refer to ``User.addresses`` as passed to
2112 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2113 how the "ON" portion of the JOIN should be constructed.
2115 To construct a chain of joins, multiple :meth:`_query.Query.join`
2116 calls may be used. The relationship-bound attribute implies both
2117 the left and right side of the join at once::
2119 q = session.query(User).\
2120 join(User.orders).\
2121 join(Order.items).\
2122 join(Item.keywords)
2124 .. note:: as seen in the above example, **the order in which each
2125 call to the join() method occurs is important**. Query would not,
2126 for example, know how to join correctly if we were to specify
2127 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2128 such a case, depending on the arguments passed, it may raise an
2129 error that it doesn't know how to join, or it may produce invalid
2130 SQL in which case the database will raise an error. In correct
2131 practice, the
2132 :meth:`_query.Query.join` method is invoked in such a way that lines
2133 up with how we would want the JOIN clauses in SQL to be
2134 rendered, and each call should represent a clear link from what
2135 precedes it.
2137 **Joins to a Target Entity or Selectable**
2139 A second form of :meth:`_query.Query.join` allows any mapped entity or
2140 core selectable construct as a target. In this usage,
2141 :meth:`_query.Query.join` will attempt to create a JOIN along the
2142 natural foreign key relationship between two entities::
2144 q = session.query(User).join(Address)
2146 In the above calling form, :meth:`_query.Query.join` is called upon to
2147 create the "on clause" automatically for us. This calling form will
2148 ultimately raise an error if either there are no foreign keys between
2149 the two entities, or if there are multiple foreign key linkages between
2150 the target entity and the entity or entities already present on the
2151 left side such that creating a join requires more information. Note
2152 that when indicating a join to a target without any ON clause, ORM
2153 configured relationships are not taken into account.
2155 **Joins to a Target with an ON Clause**
2157 The third calling form allows both the target entity as well
2158 as the ON clause to be passed explicitly. A example that includes
2159 a SQL expression as the ON clause is as follows::
2161 q = session.query(User).join(Address, User.id==Address.user_id)
2163 The above form may also use a relationship-bound attribute as the
2164 ON clause as well::
2166 q = session.query(User).join(Address, User.addresses)
2168 The above syntax can be useful for the case where we wish
2169 to join to an alias of a particular target entity. If we wanted
2170 to join to ``Address`` twice, it could be achieved using two
2171 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2173 a1 = aliased(Address)
2174 a2 = aliased(Address)
2176 q = session.query(User).\
2177 join(a1, User.addresses).\
2178 join(a2, User.addresses).\
2179 filter(a1.email_address=='ed@foo.com').\
2180 filter(a2.email_address=='ed@bar.com')
2182 The relationship-bound calling form can also specify a target entity
2183 using the :meth:`_orm.PropComparator.of_type` method; a query
2184 equivalent to the one above would be::
2186 a1 = aliased(Address)
2187 a2 = aliased(Address)
2189 q = session.query(User).\
2190 join(User.addresses.of_type(a1)).\
2191 join(User.addresses.of_type(a2)).\
2192 filter(a1.email_address == 'ed@foo.com').\
2193 filter(a2.email_address == 'ed@bar.com')
2195 **Joining to Tables and Subqueries**
2198 The target of a join may also be any table or SELECT statement,
2199 which may be related to a target entity or not. Use the
2200 appropriate ``.subquery()`` method in order to make a subquery
2201 out of a query::
2203 subq = session.query(Address).\
2204 filter(Address.email_address == 'ed@foo.com').\
2205 subquery()
2208 q = session.query(User).join(
2209 subq, User.id == subq.c.user_id
2210 )
2212 Joining to a subquery in terms of a specific relationship and/or
2213 target entity may be achieved by linking the subquery to the
2214 entity using :func:`_orm.aliased`::
2216 subq = session.query(Address).\
2217 filter(Address.email_address == 'ed@foo.com').\
2218 subquery()
2220 address_subq = aliased(Address, subq)
2222 q = session.query(User).join(
2223 User.addresses.of_type(address_subq)
2224 )
2227 **Controlling what to Join From**
2229 In cases where the left side of the current state of
2230 :class:`_query.Query` is not in line with what we want to join from,
2231 the :meth:`_query.Query.select_from` method may be used::
2233 q = session.query(Address).select_from(User).\
2234 join(User.addresses).\
2235 filter(User.name == 'ed')
2237 Which will produce SQL similar to::
2239 SELECT address.* FROM user
2240 JOIN address ON user.id=address.user_id
2241 WHERE user.name = :name_1
2243 **Legacy Features of Query.join()**
2245 The :meth:`_query.Query.join` method currently supports several
2246 usage patterns and arguments that are considered to be legacy
2247 as of SQLAlchemy 1.3. A deprecation path will follow
2248 in the 1.4 series for the following features:
2251 * Joining on relationship names rather than attributes::
2253 session.query(User).join("addresses")
2255 **Why it's legacy**: the string name does not provide enough context
2256 for :meth:`_query.Query.join` to always know what is desired,
2257 notably in that there is no indication of what the left side
2258 of the join should be. This gives rise to flags like
2259 ``from_joinpoint`` as well as the ability to place several
2260 join clauses in a single :meth:`_query.Query.join` call
2261 which don't solve the problem fully while also
2262 adding new calling styles that are unnecessary and expensive to
2263 accommodate internally.
2265 **Modern calling pattern**: Use the actual relationship,
2266 e.g. ``User.addresses`` in the above case::
2268 session.query(User).join(User.addresses)
2270 * Automatic aliasing with the ``aliased=True`` flag::
2272 session.query(Node).join(Node.children, aliased=True).\
2273 filter(Node.name == 'some name')
2275 **Why it's legacy**: the automatic aliasing feature of
2276 :class:`_query.Query` is intensely complicated, both in its internal
2277 implementation as well as in its observed behavior, and is almost
2278 never used. It is difficult to know upon inspection where and when
2279 its aliasing of a target entity, ``Node`` in the above case, will be
2280 applied and when it won't, and additionally the feature has to use
2281 very elaborate heuristics to achieve this implicit behavior.
2283 **Modern calling pattern**: Use the :func:`_orm.aliased` construct
2284 explicitly::
2286 from sqlalchemy.orm import aliased
2288 n1 = aliased(Node)
2290 session.query(Node).join(Node.children.of_type(n1)).\
2291 filter(n1.name == 'some name')
2293 * Multiple joins in one call::
2295 session.query(User).join("orders", "items")
2297 session.query(User).join(User.orders, Order.items)
2299 session.query(User).join(
2300 (Order, User.orders),
2301 (Item, Item.order_id == Order.id)
2302 )
2304 # ... and several more forms actually
2306 **Why it's legacy**: being able to chain multiple ON clauses in one
2307 call to :meth:`_query.Query.join` is yet another attempt to solve
2308 the problem of being able to specify what entity to join from,
2309 and is the source of a large variety of potential calling patterns
2310 that are internally expensive and complicated to parse and
2311 accommodate.
2313 **Modern calling pattern**: Use relationship-bound attributes
2314 or SQL-oriented ON clauses within separate calls, so that
2315 each call to :meth:`_query.Query.join` knows what the left
2316 side should be::
2318 session.query(User).join(User.orders).join(
2319 Item, Item.order_id == Order.id)
2322 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2323 the props collection in modern use should be considered to be a one
2324 or two argument form, either as a single "target" entity or ORM
2325 attribute-bound relationship, or as a target entity plus an "on
2326 clause" which may be a SQL expression or ORM attribute-bound
2327 relationship.
2329 :param isouter=False: If True, the join used will be a left outer join,
2330 just as if the :meth:`_query.Query.outerjoin` method were called.
2332 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2334 .. versionadded:: 1.1
2336 :param from_joinpoint=False: When using ``aliased=True``, a setting
2337 of True here will cause the join to be from the most recent
2338 joined target, rather than starting back from the original
2339 FROM clauses of the query.
2341 .. note:: This flag is considered legacy.
2343 :param aliased=False: If True, indicate that the JOIN target should be
2344 anonymously aliased. Subsequent calls to :meth:`_query.Query.filter`
2345 and similar will adapt the incoming criterion to the target
2346 alias, until :meth:`_query.Query.reset_joinpoint` is called.
2348 .. note:: This flag is considered legacy.
2350 .. seealso::
2352 :ref:`ormtutorial_joins` in the ORM tutorial.
2354 :ref:`inheritance_toplevel` for details on how
2355 :meth:`_query.Query.join` is used for inheritance relationships.
2357 :func:`_orm.join` - a standalone ORM-level join function,
2358 used internally by :meth:`_query.Query.join`, which in previous
2359 SQLAlchemy versions was the primary ORM-level joining interface.
2361 """
2362 aliased, from_joinpoint, isouter, full = (
2363 kwargs.pop("aliased", False),
2364 kwargs.pop("from_joinpoint", False),
2365 kwargs.pop("isouter", False),
2366 kwargs.pop("full", False),
2367 )
2368 if kwargs:
2369 raise TypeError(
2370 "unknown arguments: %s" % ", ".join(sorted(kwargs))
2371 )
2372 return self._join(
2373 props,
2374 outerjoin=isouter,
2375 full=full,
2376 create_aliases=aliased,
2377 from_joinpoint=from_joinpoint,
2378 )
2380 def outerjoin(self, *props, **kwargs):
2381 """Create a left outer join against this ``Query`` object's criterion
2382 and apply generatively, returning the newly resulting ``Query``.
2384 Usage is the same as the ``join()`` method.
2386 """
2387 aliased, from_joinpoint, full = (
2388 kwargs.pop("aliased", False),
2389 kwargs.pop("from_joinpoint", False),
2390 kwargs.pop("full", False),
2391 )
2392 if kwargs:
2393 raise TypeError(
2394 "unknown arguments: %s" % ", ".join(sorted(kwargs))
2395 )
2396 return self._join(
2397 props,
2398 outerjoin=True,
2399 full=full,
2400 create_aliases=aliased,
2401 from_joinpoint=from_joinpoint,
2402 )
2404 def _update_joinpoint(self, jp):
2405 self._joinpoint = jp
2406 # copy backwards to the root of the _joinpath
2407 # dict, so that no existing dict in the path is mutated
2408 while "prev" in jp:
2409 f, prev = jp["prev"]
2410 prev = prev.copy()
2411 prev[f] = jp.copy()
2412 jp["prev"] = (f, prev)
2413 jp = prev
2414 self._joinpath = jp
2416 @_generative(_no_statement_condition, _no_limit_offset)
2417 def _join(self, keys, outerjoin, full, create_aliases, from_joinpoint):
2418 """consumes arguments from join() or outerjoin(), places them into a
2419 consistent format with which to form the actual JOIN constructs.
2421 """
2423 if not from_joinpoint:
2424 self._reset_joinpoint()
2426 if (
2427 len(keys) == 2
2428 and isinstance(
2429 keys[0], (expression.FromClause, type, AliasedClass)
2430 )
2431 and isinstance(
2432 keys[1],
2433 (str, expression.ClauseElement, interfaces.PropComparator),
2434 )
2435 ):
2436 # detect 2-arg form of join and
2437 # convert to a tuple.
2438 keys = (keys,)
2440 # Query.join() accepts a list of join paths all at once.
2441 # step one is to iterate through these paths and determine the
2442 # intent of each path individually. as we encounter a path token,
2443 # we add a new ORMJoin construct to the self._from_obj tuple,
2444 # either by adding a new element to it, or by replacing an existing
2445 # element with a new ORMJoin.
2446 keylist = util.to_list(keys)
2447 for idx, arg1 in enumerate(keylist):
2448 if isinstance(arg1, tuple):
2449 # "tuple" form of join, multiple
2450 # tuples are accepted as well. The simpler
2451 # "2-arg" form is preferred.
2452 arg1, arg2 = arg1
2453 else:
2454 arg2 = None
2456 # determine onclause/right_entity. there
2457 # is a little bit of legacy behavior still at work here
2458 # which means they might be in either order.
2459 if isinstance(
2460 arg1, (interfaces.PropComparator, util.string_types)
2461 ):
2462 right, onclause = arg2, arg1
2463 else:
2464 right, onclause = arg1, arg2
2466 if onclause is None:
2467 r_info = inspect(right)
2468 if not r_info.is_selectable and not hasattr(r_info, "mapper"):
2469 raise sa_exc.ArgumentError(
2470 "Expected mapped entity or "
2471 "selectable/table as join target"
2472 )
2474 if isinstance(onclause, interfaces.PropComparator):
2475 of_type = getattr(onclause, "_of_type", None)
2476 else:
2477 of_type = None
2479 if isinstance(onclause, util.string_types):
2480 # string given, e.g. query(Foo).join("bar").
2481 # we look to the left entity or what we last joined
2482 # towards
2483 onclause = _entity_descriptor(self._joinpoint_zero(), onclause)
2485 # check for q.join(Class.propname, from_joinpoint=True)
2486 # and Class corresponds at the mapper level to the current
2487 # joinpoint. this match intentionally looks for a non-aliased
2488 # class-bound descriptor as the onclause and if it matches the
2489 # current joinpoint at the mapper level, it's used. This
2490 # is a very old use case that is intended to make it easier
2491 # to work with the aliased=True flag, which is also something
2492 # that probably shouldn't exist on join() due to its high
2493 # complexity/usefulness ratio
2494 elif from_joinpoint and isinstance(
2495 onclause, interfaces.PropComparator
2496 ):
2497 jp0 = self._joinpoint_zero()
2498 info = inspect(jp0)
2500 if getattr(info, "mapper", None) is onclause._parententity:
2501 onclause = _entity_descriptor(jp0, onclause.key)
2503 if isinstance(onclause, interfaces.PropComparator):
2504 # descriptor/property given (or determined); this tells
2505 # us explicitly what the expected "left" side of the join is.
2506 if right is None:
2507 if of_type:
2508 right = of_type
2509 else:
2510 right = onclause.property.entity
2512 left = onclause._parententity
2514 alias = self._polymorphic_adapters.get(left, None)
2516 # could be None or could be ColumnAdapter also
2517 if isinstance(alias, ORMAdapter) and alias.mapper.isa(left):
2518 left = alias.aliased_class
2519 onclause = getattr(left, onclause.key)
2521 prop = onclause.property
2522 if not isinstance(onclause, attributes.QueryableAttribute):
2523 onclause = prop
2525 if not create_aliases:
2526 # check for this path already present.
2527 # don't render in that case.
2528 edge = (left, right, prop.key)
2529 if edge in self._joinpoint:
2530 # The child's prev reference might be stale --
2531 # it could point to a parent older than the
2532 # current joinpoint. If this is the case,
2533 # then we need to update it and then fix the
2534 # tree's spine with _update_joinpoint. Copy
2535 # and then mutate the child, which might be
2536 # shared by a different query object.
2537 jp = self._joinpoint[edge].copy()
2538 jp["prev"] = (edge, self._joinpoint)
2539 self._update_joinpoint(jp)
2541 # warn only on the last element of the list
2542 if idx == len(keylist) - 1:
2543 util.warn(
2544 "Pathed join target %s has already "
2545 "been joined to; skipping" % prop
2546 )
2547 continue
2548 else:
2549 # no descriptor/property given; we will need to figure out
2550 # what the effective "left" side is
2551 prop = left = None
2553 # figure out the final "left" and "right" sides and create an
2554 # ORMJoin to add to our _from_obj tuple
2555 self._join_left_to_right(
2556 left, right, onclause, prop, create_aliases, outerjoin, full
2557 )
2559 def _join_left_to_right(
2560 self, left, right, onclause, prop, create_aliases, outerjoin, full
2561 ):
2562 """given raw "left", "right", "onclause" parameters consumed from
2563 a particular key within _join(), add a real ORMJoin object to
2564 our _from_obj list (or augment an existing one)
2566 """
2568 self._polymorphic_adapters = self._polymorphic_adapters.copy()
2570 if left is None:
2571 # left not given (e.g. no relationship object/name specified)
2572 # figure out the best "left" side based on our existing froms /
2573 # entities
2574 assert prop is None
2575 (
2576 left,
2577 replace_from_obj_index,
2578 use_entity_index,
2579 ) = self._join_determine_implicit_left_side(left, right, onclause)
2580 else:
2581 # left is given via a relationship/name. Determine where in our
2582 # "froms" list it should be spliced/appended as well as what
2583 # existing entity it corresponds to.
2584 assert prop is not None
2585 (
2586 replace_from_obj_index,
2587 use_entity_index,
2588 ) = self._join_place_explicit_left_side(left)
2590 if left is right and not create_aliases:
2591 raise sa_exc.InvalidRequestError(
2592 "Can't construct a join from %s to %s, they "
2593 "are the same entity" % (left, right)
2594 )
2596 # the right side as given often needs to be adapted. additionally
2597 # a lot of things can be wrong with it. handle all that and
2598 # get back the new effective "right" side
2599 r_info, right, onclause = self._join_check_and_adapt_right_side(
2600 left, right, onclause, prop, create_aliases
2601 )
2603 if replace_from_obj_index is not None:
2604 # splice into an existing element in the
2605 # self._from_obj list
2606 left_clause = self._from_obj[replace_from_obj_index]
2608 self._from_obj = (
2609 self._from_obj[:replace_from_obj_index]
2610 + (
2611 orm_join(
2612 left_clause,
2613 right,
2614 onclause,
2615 isouter=outerjoin,
2616 full=full,
2617 ),
2618 )
2619 + self._from_obj[replace_from_obj_index + 1 :]
2620 )
2621 else:
2622 # add a new element to the self._from_obj list
2624 if use_entity_index is not None:
2625 # why doesn't this work as .entity_zero_or_selectable?
2626 left_clause = self._entities[use_entity_index].selectable
2627 else:
2628 left_clause = left
2630 self._from_obj = self._from_obj + (
2631 orm_join(
2632 left_clause, right, onclause, isouter=outerjoin, full=full
2633 ),
2634 )
2636 def _join_determine_implicit_left_side(self, left, right, onclause):
2637 """When join conditions don't express the left side explicitly,
2638 determine if an existing FROM or entity in this query
2639 can serve as the left hand side.
2641 """
2643 # when we are here, it means join() was called without an ORM-
2644 # specific way of telling us what the "left" side is, e.g.:
2645 #
2646 # join(RightEntity)
2647 #
2648 # or
2649 #
2650 # join(RightEntity, RightEntity.foo == LeftEntity.bar)
2651 #
2653 r_info = inspect(right)
2655 replace_from_obj_index = use_entity_index = None
2657 if self._from_obj:
2658 # we have a list of FROMs already. So by definition this
2659 # join has to connect to one of those FROMs.
2661 indexes = sql_util.find_left_clause_to_join_from(
2662 self._from_obj, r_info.selectable, onclause
2663 )
2665 if len(indexes) == 1:
2666 replace_from_obj_index = indexes[0]
2667 left = self._from_obj[replace_from_obj_index]
2668 elif len(indexes) > 1:
2669 raise sa_exc.InvalidRequestError(
2670 "Can't determine which FROM clause to join "
2671 "from, there are multiple FROMS which can "
2672 "join to this entity. Please use the .select_from() "
2673 "method to establish an explicit left side, as well as "
2674 "providing an explcit ON clause if not present already to "
2675 "help resolve the ambiguity."
2676 )
2677 else:
2678 raise sa_exc.InvalidRequestError(
2679 "Don't know how to join to %r. "
2680 "Please use the .select_from() "
2681 "method to establish an explicit left side, as well as "
2682 "providing an explcit ON clause if not present already to "
2683 "help resolve the ambiguity." % (right,)
2684 )
2686 elif self._entities:
2687 # we have no explicit FROMs, so the implicit left has to
2688 # come from our list of entities.
2690 potential = {}
2691 for entity_index, ent in enumerate(self._entities):
2692 entity = ent.entity_zero_or_selectable
2693 if entity is None:
2694 continue
2695 ent_info = inspect(entity)
2696 if ent_info is r_info: # left and right are the same, skip
2697 continue
2699 # by using a dictionary with the selectables as keys this
2700 # de-duplicates those selectables as occurs when the query is
2701 # against a series of columns from the same selectable
2702 if isinstance(ent, _MapperEntity):
2703 potential[ent.selectable] = (entity_index, entity)
2704 else:
2705 potential[ent_info.selectable] = (None, entity)
2707 all_clauses = list(potential.keys())
2708 indexes = sql_util.find_left_clause_to_join_from(
2709 all_clauses, r_info.selectable, onclause
2710 )
2712 if len(indexes) == 1:
2713 use_entity_index, left = potential[all_clauses[indexes[0]]]
2714 elif len(indexes) > 1:
2715 raise sa_exc.InvalidRequestError(
2716 "Can't determine which FROM clause to join "
2717 "from, there are multiple FROMS which can "
2718 "join to this entity. Please use the .select_from() "
2719 "method to establish an explicit left side, as well as "
2720 "providing an explcit ON clause if not present already to "
2721 "help resolve the ambiguity."
2722 )
2723 else:
2724 raise sa_exc.InvalidRequestError(
2725 "Don't know how to join to %r. "
2726 "Please use the .select_from() "
2727 "method to establish an explicit left side, as well as "
2728 "providing an explcit ON clause if not present already to "
2729 "help resolve the ambiguity." % (right,)
2730 )
2731 else:
2732 raise sa_exc.InvalidRequestError(
2733 "No entities to join from; please use "
2734 "select_from() to establish the left "
2735 "entity/selectable of this join"
2736 )
2738 return left, replace_from_obj_index, use_entity_index
2740 def _join_place_explicit_left_side(self, left):
2741 """When join conditions express a left side explicitly, determine
2742 where in our existing list of FROM clauses we should join towards,
2743 or if we need to make a new join, and if so is it from one of our
2744 existing entities.
2746 """
2748 # when we are here, it means join() was called with an indicator
2749 # as to an exact left side, which means a path to a
2750 # RelationshipProperty was given, e.g.:
2751 #
2752 # join(RightEntity, LeftEntity.right)
2753 #
2754 # or
2755 #
2756 # join(LeftEntity.right)
2757 #
2758 # as well as string forms:
2759 #
2760 # join(RightEntity, "right")
2761 #
2762 # etc.
2763 #
2765 replace_from_obj_index = use_entity_index = None
2767 l_info = inspect(left)
2768 if self._from_obj:
2769 indexes = sql_util.find_left_clause_that_matches_given(
2770 self._from_obj, l_info.selectable
2771 )
2773 if len(indexes) > 1:
2774 raise sa_exc.InvalidRequestError(
2775 "Can't identify which entity in which to assign the "
2776 "left side of this join. Please use a more specific "
2777 "ON clause."
2778 )
2780 # have an index, means the left side is already present in
2781 # an existing FROM in the self._from_obj tuple
2782 if indexes:
2783 replace_from_obj_index = indexes[0]
2785 # no index, means we need to add a new element to the
2786 # self._from_obj tuple
2788 # no from element present, so we will have to add to the
2789 # self._from_obj tuple. Determine if this left side matches up
2790 # with existing mapper entities, in which case we want to apply the
2791 # aliasing / adaptation rules present on that entity if any
2792 if (
2793 replace_from_obj_index is None
2794 and self._entities
2795 and hasattr(l_info, "mapper")
2796 ):
2797 for idx, ent in enumerate(self._entities):
2798 # TODO: should we be checking for multiple mapper entities
2799 # matching?
2800 if isinstance(ent, _MapperEntity) and ent.corresponds_to(left):
2801 use_entity_index = idx
2802 break
2804 return replace_from_obj_index, use_entity_index
2806 def _join_check_and_adapt_right_side(
2807 self, left, right, onclause, prop, create_aliases
2808 ):
2809 """transform the "right" side of the join as well as the onclause
2810 according to polymorphic mapping translations, aliasing on the query
2811 or on the join, special cases where the right and left side have
2812 overlapping tables.
2814 """
2816 l_info = inspect(left)
2817 r_info = inspect(right)
2819 overlap = False
2820 if not create_aliases:
2821 right_mapper = getattr(r_info, "mapper", None)
2822 # if the target is a joined inheritance mapping,
2823 # be more liberal about auto-aliasing.
2824 if right_mapper and (
2825 right_mapper.with_polymorphic
2826 or isinstance(right_mapper.persist_selectable, expression.Join)
2827 ):
2828 for from_obj in self._from_obj or [l_info.selectable]:
2829 if sql_util.selectables_overlap(
2830 l_info.selectable, from_obj
2831 ) and sql_util.selectables_overlap(
2832 from_obj, r_info.selectable
2833 ):
2834 overlap = True
2835 break
2837 if (
2838 overlap or not create_aliases
2839 ) and l_info.selectable is r_info.selectable:
2840 raise sa_exc.InvalidRequestError(
2841 "Can't join table/selectable '%s' to itself"
2842 % l_info.selectable
2843 )
2845 right_mapper, right_selectable, right_is_aliased = (
2846 getattr(r_info, "mapper", None),
2847 r_info.selectable,
2848 getattr(r_info, "is_aliased_class", False),
2849 )
2851 if (
2852 right_mapper
2853 and prop
2854 and not right_mapper.common_parent(prop.mapper)
2855 ):
2856 raise sa_exc.InvalidRequestError(
2857 "Join target %s does not correspond to "
2858 "the right side of join condition %s" % (right, onclause)
2859 )
2861 # _join_entities is used as a hint for single-table inheritance
2862 # purposes at the moment
2863 if hasattr(r_info, "mapper"):
2864 self._join_entities += (r_info,)
2866 need_adapter = False
2868 # test for joining to an unmapped selectable as the target
2869 if r_info.is_clause_element:
2871 if prop:
2872 right_mapper = prop.mapper
2874 if right_selectable._is_lateral:
2875 # orm_only is disabled to suit the case where we have to
2876 # adapt an explicit correlate(Entity) - the select() loses
2877 # the ORM-ness in this case right now, ideally it would not
2878 right = self._adapt_clause(right, True, False)
2880 elif prop:
2881 # joining to selectable with a mapper property given
2882 # as the ON clause
2883 if not right_selectable.is_derived_from(
2884 right_mapper.persist_selectable
2885 ):
2886 raise sa_exc.InvalidRequestError(
2887 "Selectable '%s' is not derived from '%s'"
2888 % (
2889 right_selectable.description,
2890 right_mapper.persist_selectable.description,
2891 )
2892 )
2894 # if the destination selectable is a plain select(),
2895 # turn it into an alias().
2896 if isinstance(right_selectable, expression.SelectBase):
2897 right_selectable = right_selectable.alias()
2898 need_adapter = True
2900 # make the right hand side target into an ORM entity
2901 right = aliased(right_mapper, right_selectable)
2902 elif create_aliases:
2903 # it *could* work, but it doesn't right now and I'd rather
2904 # get rid of aliased=True completely
2905 raise sa_exc.InvalidRequestError(
2906 "The aliased=True parameter on query.join() only works "
2907 "with an ORM entity, not a plain selectable, as the "
2908 "target."
2909 )
2911 aliased_entity = (
2912 right_mapper
2913 and not right_is_aliased
2914 and (
2915 right_mapper.with_polymorphic
2916 and isinstance(
2917 right_mapper._with_polymorphic_selectable, expression.Alias
2918 )
2919 or overlap
2920 # test for overlap:
2921 # orm/inheritance/relationships.py
2922 # SelfReferentialM2MTest
2923 )
2924 )
2926 if not need_adapter and (create_aliases or aliased_entity):
2927 right = aliased(right, flat=True)
2928 need_adapter = True
2930 if need_adapter:
2931 assert right_mapper
2933 # if an alias() of the right side was generated,
2934 # apply an adapter to all subsequent filter() calls
2935 # until reset_joinpoint() is called.
2936 adapter = ORMAdapter(
2937 right, equivalents=right_mapper._equivalent_columns
2938 )
2939 # current adapter takes highest precedence
2940 self._filter_aliases = (adapter,) + self._filter_aliases
2942 # if an alias() on the right side was generated,
2943 # which is intended to wrap a the right side in a subquery,
2944 # ensure that columns retrieved from this target in the result
2945 # set are also adapted.
2946 if not create_aliases:
2947 self._mapper_loads_polymorphically_with(right_mapper, adapter)
2949 # if the onclause is a ClauseElement, adapt it with any
2950 # adapters that are in place right now
2951 if isinstance(onclause, expression.ClauseElement):
2952 onclause = self._adapt_clause(onclause, True, True)
2954 # if joining on a MapperProperty path,
2955 # track the path to prevent redundant joins
2956 if not create_aliases and prop:
2957 self._update_joinpoint(
2958 {
2959 "_joinpoint_entity": right,
2960 "prev": ((left, right, prop.key), self._joinpoint),
2961 }
2962 )
2963 else:
2964 self._joinpoint = {"_joinpoint_entity": right}
2966 return right, inspect(right), onclause
2968 def _reset_joinpoint(self):
2969 self._joinpoint = self._joinpath
2970 self._filter_aliases = ()
2972 @_generative(_no_statement_condition)
2973 def reset_joinpoint(self):
2974 """Return a new :class:`_query.Query`, where the "join point" has
2975 been reset back to the base FROM entities of the query.
2977 This method is usually used in conjunction with the
2978 ``aliased=True`` feature of the :meth:`_query.Query.join`
2979 method. See the example in :meth:`_query.Query.join` for how
2980 this is used.
2982 """
2983 self._reset_joinpoint()
2985 @_generative(_no_clauseelement_condition)
2986 def select_from(self, *from_obj):
2987 r"""Set the FROM clause of this :class:`_query.Query` explicitly.
2989 :meth:`_query.Query.select_from` is often used in conjunction with
2990 :meth:`_query.Query.join` in order to control which entity is selected
2991 from on the "left" side of the join.
2993 The entity or selectable object here effectively replaces the
2994 "left edge" of any calls to :meth:`_query.Query.join`, when no
2995 joinpoint is otherwise established - usually, the default "join
2996 point" is the leftmost entity in the :class:`_query.Query` object's
2997 list of entities to be selected.
2999 A typical example::
3001 q = session.query(Address).select_from(User).\
3002 join(User.addresses).\
3003 filter(User.name == 'ed')
3005 Which produces SQL equivalent to::
3007 SELECT address.* FROM user
3008 JOIN address ON user.id=address.user_id
3009 WHERE user.name = :name_1
3011 :param \*from_obj: collection of one or more entities to apply
3012 to the FROM clause. Entities can be mapped classes,
3013 :class:`.AliasedClass` objects, :class:`_orm.Mapper` objects
3014 as well as core :class:`_expression.FromClause`
3015 elements like subqueries.
3017 .. versionchanged:: 0.9
3018 This method no longer applies the given FROM object
3019 to be the selectable from which matching entities
3020 select from; the :meth:`.select_entity_from` method
3021 now accomplishes this. See that method for a description
3022 of this behavior.
3024 .. seealso::
3026 :meth:`_query.Query.join`
3028 :meth:`_query.Query.select_entity_from`
3030 """
3032 self._set_select_from(from_obj, False)
3034 @_generative(_no_clauseelement_condition)
3035 def select_entity_from(self, from_obj):
3036 r"""Set the FROM clause of this :class:`_query.Query` to a
3037 core selectable, applying it as a replacement FROM clause
3038 for corresponding mapped entities.
3040 The :meth:`_query.Query.select_entity_from`
3041 method supplies an alternative
3042 approach to the use case of applying an :func:`.aliased` construct
3043 explicitly throughout a query. Instead of referring to the
3044 :func:`.aliased` construct explicitly,
3045 :meth:`_query.Query.select_entity_from` automatically *adapts* all
3046 occurrences of the entity to the target selectable.
3048 Given a case for :func:`.aliased` such as selecting ``User``
3049 objects from a SELECT statement::
3051 select_stmt = select([User]).where(User.id == 7)
3052 user_alias = aliased(User, select_stmt)
3054 q = session.query(user_alias).\
3055 filter(user_alias.name == 'ed')
3057 Above, we apply the ``user_alias`` object explicitly throughout the
3058 query. When it's not feasible for ``user_alias`` to be referenced
3059 explicitly in many places, :meth:`_query.Query.select_entity_from`
3060 may be
3061 used at the start of the query to adapt the existing ``User`` entity::
3063 q = session.query(User).\
3064 select_entity_from(select_stmt).\
3065 filter(User.name == 'ed')
3067 Above, the generated SQL will show that the ``User`` entity is
3068 adapted to our statement, even in the case of the WHERE clause:
3070 .. sourcecode:: sql
3072 SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
3073 FROM (SELECT "user".id AS id, "user".name AS name
3074 FROM "user"
3075 WHERE "user".id = :id_1) AS anon_1
3076 WHERE anon_1.name = :name_1
3078 The :meth:`_query.Query.select_entity_from` method is similar to the
3079 :meth:`_query.Query.select_from` method,
3080 in that it sets the FROM clause
3081 of the query. The difference is that it additionally applies
3082 adaptation to the other parts of the query that refer to the
3083 primary entity. If above we had used :meth:`_query.Query.select_from`
3084 instead, the SQL generated would have been:
3086 .. sourcecode:: sql
3088 -- uses plain select_from(), not select_entity_from()
3089 SELECT "user".id AS user_id, "user".name AS user_name
3090 FROM "user", (SELECT "user".id AS id, "user".name AS name
3091 FROM "user"
3092 WHERE "user".id = :id_1) AS anon_1
3093 WHERE "user".name = :name_1
3095 To supply textual SQL to the :meth:`_query.Query.select_entity_from`
3096 method,
3097 we can make use of the :func:`_expression.text` construct. However,
3098 the
3099 :func:`_expression.text`
3100 construct needs to be aligned with the columns of our
3101 entity, which is achieved by making use of the
3102 :meth:`_expression.TextClause.columns` method::
3104 text_stmt = text("select id, name from user").columns(
3105 User.id, User.name)
3106 q = session.query(User).select_entity_from(text_stmt)
3108 :meth:`_query.Query.select_entity_from` itself accepts an
3109 :func:`.aliased`
3110 object, so that the special options of :func:`.aliased` such as
3111 :paramref:`.aliased.adapt_on_names` may be used within the
3112 scope of the :meth:`_query.Query.select_entity_from`
3113 method's adaptation
3114 services. Suppose
3115 a view ``user_view`` also returns rows from ``user``. If
3116 we reflect this view into a :class:`_schema.Table`, this view has no
3117 relationship to the :class:`_schema.Table` to which we are mapped,
3118 however
3119 we can use name matching to select from it::
3121 user_view = Table('user_view', metadata,
3122 autoload_with=engine)
3123 user_view_alias = aliased(
3124 User, user_view, adapt_on_names=True)
3125 q = session.query(User).\
3126 select_entity_from(user_view_alias).\
3127 order_by(User.name)
3129 .. versionchanged:: 1.1.7 The :meth:`_query.Query.select_entity_from`
3130 method now accepts an :func:`.aliased` object as an alternative
3131 to a :class:`_expression.FromClause` object.
3133 :param from_obj: a :class:`_expression.FromClause`
3134 object that will replace
3135 the FROM clause of this :class:`_query.Query`.
3136 It also may be an instance
3137 of :func:`.aliased`.
3141 .. seealso::
3143 :meth:`_query.Query.select_from`
3145 """
3147 self._set_select_from([from_obj], True)
3149 def __getitem__(self, item):
3150 if isinstance(item, slice):
3151 start, stop, step = util.decode_slice(item)
3153 if (
3154 isinstance(stop, int)
3155 and isinstance(start, int)
3156 and stop - start <= 0
3157 ):
3158 return []
3160 # perhaps we should execute a count() here so that we
3161 # can still use LIMIT/OFFSET ?
3162 elif (isinstance(start, int) and start < 0) or (
3163 isinstance(stop, int) and stop < 0
3164 ):
3165 return list(self)[item]
3167 res = self.slice(start, stop)
3168 if step is not None:
3169 return list(res)[None : None : item.step]
3170 else:
3171 return list(res)
3172 else:
3173 if item == -1:
3174 return list(self)[-1]
3175 else:
3176 return list(self[item : item + 1])[0]
3178 @_generative(_no_statement_condition)
3179 def slice(self, start, stop):
3180 """Computes the "slice" of the :class:`_query.Query` represented by
3181 the given indices and returns the resulting :class:`_query.Query`.
3183 The start and stop indices behave like the argument to Python's
3184 built-in :func:`range` function. This method provides an
3185 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
3186 query.
3188 For example, ::
3190 session.query(User).order_by(User.id).slice(1, 3)
3192 renders as
3194 .. sourcecode:: sql
3196 SELECT users.id AS users_id,
3197 users.name AS users_name
3198 FROM users ORDER BY users.id
3199 LIMIT ? OFFSET ?
3200 (2, 1)
3202 .. seealso::
3204 :meth:`_query.Query.limit`
3206 :meth:`_query.Query.offset`
3208 """
3209 if start is not None and stop is not None:
3210 self._offset = self._offset if self._offset is not None else 0
3211 if start != 0:
3212 self._offset += start
3213 self._limit = stop - start
3214 elif start is None and stop is not None:
3215 self._limit = stop
3216 elif start is not None and stop is None:
3217 self._offset = self._offset if self._offset is not None else 0
3218 if start != 0:
3219 self._offset += start
3221 if isinstance(self._offset, int) and self._offset == 0:
3222 self._offset = None
3224 @_generative(_no_statement_condition)
3225 def limit(self, limit):
3226 """Apply a ``LIMIT`` to the query and return the newly resulting
3227 ``Query``.
3229 """
3230 self._limit = limit
3232 @_generative(_no_statement_condition)
3233 def offset(self, offset):
3234 """Apply an ``OFFSET`` to the query and return the newly resulting
3235 ``Query``.
3237 """
3238 self._offset = offset
3240 @_generative(_no_statement_condition)
3241 def distinct(self, *expr):
3242 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
3243 ``Query``.
3246 .. note::
3248 The :meth:`.distinct` call includes logic that will automatically
3249 add columns from the ORDER BY of the query to the columns
3250 clause of the SELECT statement, to satisfy the common need
3251 of the database backend that ORDER BY columns be part of the
3252 SELECT list when DISTINCT is used. These columns *are not*
3253 added to the list of columns actually fetched by the
3254 :class:`_query.Query`, however, so would not affect results.
3255 The columns are passed through when using the
3256 :attr:`_query.Query.statement` accessor, however.
3258 :param \*expr: optional column expressions. When present,
3259 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
3260 construct.
3262 """
3263 if not expr:
3264 self._distinct = True
3265 else:
3266 expr = self._adapt_col_list(expr)
3267 if isinstance(self._distinct, list):
3268 self._distinct += expr
3269 else:
3270 self._distinct = expr
3272 @_generative()
3273 def prefix_with(self, *prefixes):
3274 r"""Apply the prefixes to the query and return the newly resulting
3275 ``Query``.
3277 :param \*prefixes: optional prefixes, typically strings,
3278 not using any commas. In particular is useful for MySQL keywords
3279 and optimizer hints:
3281 e.g.::
3283 query = sess.query(User.name).\
3284 prefix_with('HIGH_PRIORITY').\
3285 prefix_with('SQL_SMALL_RESULT', 'ALL').\
3286 prefix_with('/*+ BKA(user) */')
3288 Would render::
3290 SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL /*+ BKA(user) */
3291 users.name AS users_name FROM users
3293 .. seealso::
3295 :meth:`_expression.HasPrefixes.prefix_with`
3297 """
3298 if self._prefixes:
3299 self._prefixes += prefixes
3300 else:
3301 self._prefixes = prefixes
3303 @_generative()
3304 def suffix_with(self, *suffixes):
3305 r"""Apply the suffix to the query and return the newly resulting
3306 ``Query``.
3308 :param \*suffixes: optional suffixes, typically strings,
3309 not using any commas.
3311 .. versionadded:: 1.0.0
3313 .. seealso::
3315 :meth:`_query.Query.prefix_with`
3317 :meth:`_expression.HasSuffixes.suffix_with`
3319 """
3320 if self._suffixes:
3321 self._suffixes += suffixes
3322 else:
3323 self._suffixes = suffixes
3325 def all(self):
3326 """Return the results represented by this :class:`_query.Query`
3327 as a list.
3329 This results in an execution of the underlying SQL statement.
3331 .. warning:: The :class:`_query.Query` object,
3332 when asked to return either
3333 a sequence or iterator that consists of full ORM-mapped entities,
3334 will **deduplicate entries based on primary key**. See the FAQ for
3335 more details.
3337 .. seealso::
3339 :ref:`faq_query_deduplicating`
3340 """
3341 return list(self)
3343 @_generative(_no_clauseelement_condition)
3344 def from_statement(self, statement):
3345 """Execute the given SELECT statement and return results.
3347 This method bypasses all internal statement compilation, and the
3348 statement is executed without modification.
3350 The statement is typically either a :func:`_expression.text`
3351 or :func:`_expression.select` construct, and should return the set
3352 of columns
3353 appropriate to the entity class represented by this
3354 :class:`_query.Query`.
3356 .. seealso::
3358 :ref:`orm_tutorial_literal_sql` - usage examples in the
3359 ORM tutorial
3361 """
3362 statement = expression._expression_literal_as_text(statement)
3364 if not isinstance(
3365 statement, (expression.TextClause, expression.SelectBase)
3366 ):
3367 raise sa_exc.ArgumentError(
3368 "from_statement accepts text(), select(), "
3369 "and union() objects only."
3370 )
3372 self._statement = statement
3374 def first(self):
3375 """Return the first result of this ``Query`` or
3376 None if the result doesn't contain any row.
3378 first() applies a limit of one within the generated SQL, so that
3379 only one primary entity row is generated on the server side
3380 (note this may consist of multiple result rows if join-loaded
3381 collections are present).
3383 Calling :meth:`_query.Query.first`
3384 results in an execution of the underlying
3385 query.
3387 .. seealso::
3389 :meth:`_query.Query.one`
3391 :meth:`_query.Query.one_or_none`
3393 """
3394 if self._statement is not None:
3395 ret = list(self)[0:1]
3396 else:
3397 ret = list(self[0:1])
3398 if len(ret) > 0:
3399 return ret[0]
3400 else:
3401 return None
3403 def one_or_none(self):
3404 """Return at most one result or raise an exception.
3406 Returns ``None`` if the query selects
3407 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
3408 if multiple object identities are returned, or if multiple
3409 rows are returned for a query that returns only scalar values
3410 as opposed to full identity-mapped entities.
3412 Calling :meth:`_query.Query.one_or_none`
3413 results in an execution of the
3414 underlying query.
3416 .. versionadded:: 1.0.9
3418 Added :meth:`_query.Query.one_or_none`
3420 .. seealso::
3422 :meth:`_query.Query.first`
3424 :meth:`_query.Query.one`
3426 """
3427 ret = list(self)
3429 l = len(ret)
3430 if l == 1:
3431 return ret[0]
3432 elif l == 0:
3433 return None
3434 else:
3435 raise orm_exc.MultipleResultsFound(
3436 "Multiple rows were found for one_or_none()"
3437 )
3439 def one(self):
3440 """Return exactly one result or raise an exception.
3442 Raises ``sqlalchemy.orm.exc.NoResultFound`` if the query selects
3443 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
3444 if multiple object identities are returned, or if multiple
3445 rows are returned for a query that returns only scalar values
3446 as opposed to full identity-mapped entities.
3448 Calling :meth:`.one` results in an execution of the underlying query.
3450 .. seealso::
3452 :meth:`_query.Query.first`
3454 :meth:`_query.Query.one_or_none`
3456 """
3457 try:
3458 ret = self.one_or_none()
3459 except orm_exc.MultipleResultsFound as err:
3460 util.raise_(
3461 orm_exc.MultipleResultsFound(
3462 "Multiple rows were found for one()"
3463 ),
3464 replace_context=err,
3465 )
3466 else:
3467 if ret is None:
3468 raise orm_exc.NoResultFound("No row was found for one()")
3469 return ret
3471 def scalar(self):
3472 """Return the first element of the first result or None
3473 if no rows present. If multiple rows are returned,
3474 raises MultipleResultsFound.
3476 >>> session.query(Item).scalar()
3477 <Item>
3478 >>> session.query(Item.id).scalar()
3479 1
3480 >>> session.query(Item.id).filter(Item.id < 0).scalar()
3481 None
3482 >>> session.query(Item.id, Item.name).scalar()
3483 1
3484 >>> session.query(func.count(Parent.id)).scalar()
3485 20
3487 This results in an execution of the underlying query.
3489 """
3490 try:
3491 ret = self.one()
3492 if not isinstance(ret, tuple):
3493 return ret
3494 return ret[0]
3495 except orm_exc.NoResultFound:
3496 return None
3498 def __iter__(self):
3499 context = self._compile_context()
3500 context.statement.use_labels = True
3501 if self._autoflush and not self._populate_existing:
3502 self.session._autoflush()
3503 return self._execute_and_instances(context)
3505 def __str__(self):
3506 context = self._compile_context()
3507 try:
3508 bind = (
3509 self._get_bind_args(context, self.session.get_bind)
3510 if self.session
3511 else None
3512 )
3513 except sa_exc.UnboundExecutionError:
3514 bind = None
3515 return str(context.statement.compile(bind))
3517 def _connection_from_session(self, **kw):
3518 conn = self.session.connection(**kw)
3519 if self._execution_options:
3520 conn = conn.execution_options(**self._execution_options)
3521 return conn
3523 def _execute_and_instances(self, querycontext):
3524 conn = self._get_bind_args(
3525 querycontext, self._connection_from_session, close_with_result=True
3526 )
3528 result = conn.execute(querycontext.statement, self._params)
3529 return loading.instances(querycontext.query, result, querycontext)
3531 def _execute_crud(self, stmt, mapper):
3532 conn = self._connection_from_session(
3533 mapper=mapper, clause=stmt, close_with_result=True
3534 )
3536 return conn.execute(stmt, self._params)
3538 def _get_bind_args(self, querycontext, fn, **kw):
3539 return fn(
3540 mapper=self._bind_mapper(), clause=querycontext.statement, **kw
3541 )
3543 @property
3544 def column_descriptions(self):
3545 """Return metadata about the columns which would be
3546 returned by this :class:`_query.Query`.
3548 Format is a list of dictionaries::
3550 user_alias = aliased(User, name='user2')
3551 q = sess.query(User, User.id, user_alias)
3553 # this expression:
3554 q.column_descriptions
3556 # would return:
3557 [
3558 {
3559 'name':'User',
3560 'type':User,
3561 'aliased':False,
3562 'expr':User,
3563 'entity': User
3564 },
3565 {
3566 'name':'id',
3567 'type':Integer(),
3568 'aliased':False,
3569 'expr':User.id,
3570 'entity': User
3571 },
3572 {
3573 'name':'user2',
3574 'type':User,
3575 'aliased':True,
3576 'expr':user_alias,
3577 'entity': user_alias
3578 }
3579 ]
3581 """
3583 return [
3584 {
3585 "name": ent._label_name,
3586 "type": ent.type,
3587 "aliased": getattr(insp_ent, "is_aliased_class", False),
3588 "expr": ent.expr,
3589 "entity": getattr(insp_ent, "entity", None)
3590 if ent.entity_zero is not None
3591 and not insp_ent.is_clause_element
3592 else None,
3593 }
3594 for ent, insp_ent in [
3595 (
3596 _ent,
3597 (
3598 inspect(_ent.entity_zero)
3599 if _ent.entity_zero is not None
3600 else None
3601 ),
3602 )
3603 for _ent in self._entities
3604 ]
3605 ]
3607 def instances(self, cursor, __context=None):
3608 """Given a ResultProxy cursor as returned by connection.execute(),
3609 return an ORM result as an iterator.
3611 e.g.::
3613 result = engine.execute("select * from users")
3614 for u in session.query(User).instances(result):
3615 print u
3616 """
3617 context = __context
3618 if context is None:
3619 context = QueryContext(self)
3621 return loading.instances(self, cursor, context)
3623 def merge_result(self, iterator, load=True):
3624 """Merge a result into this :class:`_query.Query` object's Session.
3626 Given an iterator returned by a :class:`_query.Query`
3627 of the same structure
3628 as this one, return an identical iterator of results, with all mapped
3629 instances merged into the session using :meth:`.Session.merge`. This
3630 is an optimized method which will merge all mapped instances,
3631 preserving the structure of the result rows and unmapped columns with
3632 less method overhead than that of calling :meth:`.Session.merge`
3633 explicitly for each value.
3635 The structure of the results is determined based on the column list of
3636 this :class:`_query.Query` - if these do not correspond,
3637 unchecked errors
3638 will occur.
3640 The 'load' argument is the same as that of :meth:`.Session.merge`.
3642 For an example of how :meth:`_query.Query.merge_result` is used, see
3643 the source code for the example :ref:`examples_caching`, where
3644 :meth:`_query.Query.merge_result` is used to efficiently restore state
3645 from a cache back into a target :class:`.Session`.
3647 """
3649 return loading.merge_result(self, iterator, load)
3651 @property
3652 def _select_args(self):
3653 return {
3654 "limit": self._limit,
3655 "offset": self._offset,
3656 "distinct": self._distinct,
3657 "prefixes": self._prefixes,
3658 "suffixes": self._suffixes,
3659 "group_by": self._group_by or None,
3660 "having": self._having,
3661 }
3663 @property
3664 def _should_nest_selectable(self):
3665 kwargs = self._select_args
3666 return (
3667 kwargs.get("limit") is not None
3668 or kwargs.get("offset") is not None
3669 or kwargs.get("distinct", False)
3670 or kwargs.get("group_by", False)
3671 )
3673 def exists(self):
3674 """A convenience method that turns a query into an EXISTS subquery
3675 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3677 e.g.::
3679 q = session.query(User).filter(User.name == 'fred')
3680 session.query(q.exists())
3682 Producing SQL similar to::
3684 SELECT EXISTS (
3685 SELECT 1 FROM users WHERE users.name = :name_1
3686 ) AS anon_1
3688 The EXISTS construct is usually used in the WHERE clause::
3690 session.query(User.id).filter(q.exists()).scalar()
3692 Note that some databases such as SQL Server don't allow an
3693 EXISTS expression to be present in the columns clause of a
3694 SELECT. To select a simple boolean value based on the exists
3695 as a WHERE, use :func:`.literal`::
3697 from sqlalchemy import literal
3699 session.query(literal(True)).filter(q.exists()).scalar()
3701 """
3703 # .add_columns() for the case that we are a query().select_from(X),
3704 # so that ".statement" can be produced (#2995) but also without
3705 # omitting the FROM clause from a query(X) (#2818);
3706 # .with_only_columns() after we have a core select() so that
3707 # we get just "SELECT 1" without any entities.
3708 return sql.exists(
3709 self.enable_eagerloads(False)
3710 .add_columns(sql.literal_column("1"))
3711 .with_labels()
3712 .statement.with_only_columns([1])
3713 )
3715 def count(self):
3716 r"""Return a count of rows this the SQL formed by this :class:`Query`
3717 would return.
3719 This generates the SQL for this Query as follows::
3721 SELECT count(1) AS count_1 FROM (
3722 SELECT <rest of query follows...>
3723 ) AS anon_1
3725 The above SQL returns a single row, which is the aggregate value
3726 of the count function; the :meth:`_query.Query.count`
3727 method then returns
3728 that single integer value.
3730 .. warning::
3732 It is important to note that the value returned by
3733 count() is **not the same as the number of ORM objects that this
3734 Query would return from a method such as the .all() method**.
3735 The :class:`_query.Query` object,
3736 when asked to return full entities,
3737 will **deduplicate entries based on primary key**, meaning if the
3738 same primary key value would appear in the results more than once,
3739 only one object of that primary key would be present. This does
3740 not apply to a query that is against individual columns.
3742 .. seealso::
3744 :ref:`faq_query_deduplicating`
3746 :ref:`orm_tutorial_query_returning`
3748 For fine grained control over specific columns to count, to skip the
3749 usage of a subquery or otherwise control of the FROM clause, or to use
3750 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3751 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3753 from sqlalchemy import func
3755 # count User records, without
3756 # using a subquery.
3757 session.query(func.count(User.id))
3759 # return count of user "id" grouped
3760 # by "name"
3761 session.query(func.count(User.id)).\
3762 group_by(User.name)
3764 from sqlalchemy import distinct
3766 # count distinct "name" values
3767 session.query(func.count(distinct(User.name)))
3769 """
3770 col = sql.func.count(sql.literal_column("*"))
3771 return self.from_self(col).scalar()
3773 def delete(self, synchronize_session="evaluate"):
3774 r"""Perform a bulk delete query.
3776 Deletes rows matched by this query from the database.
3778 E.g.::
3780 sess.query(User).filter(User.age == 25).\
3781 delete(synchronize_session=False)
3783 sess.query(User).filter(User.age == 25).\
3784 delete(synchronize_session='evaluate')
3786 .. warning:: The :meth:`_query.Query.delete`
3787 method is a "bulk" operation,
3788 which bypasses ORM unit-of-work automation in favor of greater
3789 performance. **Please read all caveats and warnings below.**
3791 :param synchronize_session: chooses the strategy for the removal of
3792 matched objects from the session. Valid values are:
3794 ``False`` - don't synchronize the session. This option is the most
3795 efficient and is reliable once the session is expired, which
3796 typically occurs after a commit(), or explicitly using
3797 expire_all(). Before the expiration, objects may still remain in
3798 the session which were in fact deleted which can lead to confusing
3799 results if they are accessed via get() or already loaded
3800 collections.
3802 ``'fetch'`` - performs a select query before the delete to find
3803 objects that are matched by the delete query and need to be
3804 removed from the session. Matched objects are removed from the
3805 session.
3807 ``'evaluate'`` - Evaluate the query's criteria in Python straight
3808 on the objects in the session. If evaluation of the criteria isn't
3809 implemented, an error is raised.
3811 The expression evaluator currently doesn't account for differing
3812 string collations between the database and Python.
3814 :return: the count of rows matched as returned by the database's
3815 "row count" feature.
3817 .. warning:: **Additional Caveats for bulk query deletes**
3819 * This method does **not work for joined
3820 inheritance mappings**, since the **multiple table
3821 deletes are not supported by SQL** as well as that the
3822 **join condition of an inheritance mapper is not
3823 automatically rendered**. Care must be taken in any
3824 multiple-table delete to first accommodate via some other means
3825 how the related table will be deleted, as well as to
3826 explicitly include the joining
3827 condition between those tables, even in mappings where
3828 this is normally automatic. E.g. if a class ``Engineer``
3829 subclasses ``Employee``, a DELETE against the ``Employee``
3830 table would look like::
3832 session.query(Engineer).\
3833 filter(Engineer.id == Employee.id).\
3834 filter(Employee.name == 'dilbert').\
3835 delete()
3837 However the above SQL will not delete from the Engineer table,
3838 unless an ON DELETE CASCADE rule is established in the database
3839 to handle it.
3841 Short story, **do not use this method for joined inheritance
3842 mappings unless you have taken the additional steps to make
3843 this feasible**.
3845 * The polymorphic identity WHERE criteria is **not** included
3846 for single- or
3847 joined- table updates - this must be added **manually** even
3848 for single table inheritance.
3850 * The method does **not** offer in-Python cascading of
3851 relationships - it is assumed that ON DELETE CASCADE/SET
3852 NULL/etc. is configured for any foreign key references
3853 which require it, otherwise the database may emit an
3854 integrity violation if foreign key references are being
3855 enforced.
3857 After the DELETE, dependent objects in the
3858 :class:`.Session` which were impacted by an ON DELETE
3859 may not contain the current state, or may have been
3860 deleted. This issue is resolved once the
3861 :class:`.Session` is expired, which normally occurs upon
3862 :meth:`.Session.commit` or can be forced by using
3863 :meth:`.Session.expire_all`. Accessing an expired
3864 object whose row has been deleted will invoke a SELECT
3865 to locate the row; when the row is not found, an
3866 :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is
3867 raised.
3869 * The ``'fetch'`` strategy results in an additional
3870 SELECT statement emitted and will significantly reduce
3871 performance.
3873 * The ``'evaluate'`` strategy performs a scan of
3874 all matching objects within the :class:`.Session`; if the
3875 contents of the :class:`.Session` are expired, such as
3876 via a proceeding :meth:`.Session.commit` call, **this will
3877 result in SELECT queries emitted for every matching object**.
3879 * The :meth:`.MapperEvents.before_delete` and
3880 :meth:`.MapperEvents.after_delete`
3881 events **are not invoked** from this method. Instead, the
3882 :meth:`.SessionEvents.after_bulk_delete` method is provided to
3883 act upon a mass DELETE of entity rows.
3885 .. seealso::
3887 :meth:`_query.Query.update`
3889 :ref:`inserts_and_updates` - Core SQL tutorial
3891 """
3893 delete_op = persistence.BulkDelete.factory(self, synchronize_session)
3894 delete_op.exec_()
3895 return delete_op.rowcount
3897 def update(self, values, synchronize_session="evaluate", update_args=None):
3898 r"""Perform a bulk update query.
3900 Updates rows matched by this query in the database.
3902 E.g.::
3904 sess.query(User).filter(User.age == 25).\
3905 update({User.age: User.age - 10}, synchronize_session=False)
3907 sess.query(User).filter(User.age == 25).\
3908 update({"age": User.age - 10}, synchronize_session='evaluate')
3911 .. warning:: The :meth:`_query.Query.update`
3912 method is a "bulk" operation,
3913 which bypasses ORM unit-of-work automation in favor of greater
3914 performance. **Please read all caveats and warnings below.**
3917 :param values: a dictionary with attributes names, or alternatively
3918 mapped attributes or SQL expressions, as keys, and literal
3919 values or sql expressions as values. If :ref:`parameter-ordered
3920 mode <updates_order_parameters>` is desired, the values can be
3921 passed as a list of 2-tuples;
3922 this requires that the
3923 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3924 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3925 as well.
3927 .. versionchanged:: 1.0.0 - string names in the values dictionary
3928 are now resolved against the mapped entity; previously, these
3929 strings were passed as literal column names with no mapper-level
3930 translation.
3932 :param synchronize_session: chooses the strategy to update the
3933 attributes on objects in the session. Valid values are:
3935 ``False`` - don't synchronize the session. This option is the most
3936 efficient and is reliable once the session is expired, which
3937 typically occurs after a commit(), or explicitly using
3938 expire_all(). Before the expiration, updated objects may still
3939 remain in the session with stale values on their attributes, which
3940 can lead to confusing results.
3942 ``'fetch'`` - performs a select query before the update to find
3943 objects that are matched by the update query. The updated
3944 attributes are expired on matched objects.
3946 ``'evaluate'`` - Evaluate the Query's criteria in Python straight
3947 on the objects in the session. If evaluation of the criteria isn't
3948 implemented, an exception is raised.
3950 The expression evaluator currently doesn't account for differing
3951 string collations between the database and Python.
3953 :param update_args: Optional dictionary, if present will be passed
3954 to the underlying :func:`_expression.update`
3955 construct as the ``**kw`` for
3956 the object. May be used to pass dialect-specific arguments such
3957 as ``mysql_limit``, as well as other special arguments such as
3958 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3960 .. versionadded:: 1.0.0
3962 :return: the count of rows matched as returned by the database's
3963 "row count" feature.
3965 .. warning:: **Additional Caveats for bulk query updates**
3967 * The method does **not** offer in-Python cascading of
3968 relationships - it is assumed that ON UPDATE CASCADE is
3969 configured for any foreign key references which require
3970 it, otherwise the database may emit an integrity
3971 violation if foreign key references are being enforced.
3973 After the UPDATE, dependent objects in the
3974 :class:`.Session` which were impacted by an ON UPDATE
3975 CASCADE may not contain the current state; this issue is
3976 resolved once the :class:`.Session` is expired, which
3977 normally occurs upon :meth:`.Session.commit` or can be
3978 forced by using :meth:`.Session.expire_all`.
3980 * The ``'fetch'`` strategy results in an additional
3981 SELECT statement emitted and will significantly reduce
3982 performance.
3984 * The ``'evaluate'`` strategy performs a scan of
3985 all matching objects within the :class:`.Session`; if the
3986 contents of the :class:`.Session` are expired, such as
3987 via a proceeding :meth:`.Session.commit` call, **this will
3988 result in SELECT queries emitted for every matching object**.
3990 * The method supports multiple table updates, as detailed
3991 in :ref:`multi_table_updates`, and this behavior does
3992 extend to support updates of joined-inheritance and
3993 other multiple table mappings. However, the **join
3994 condition of an inheritance mapper is not
3995 automatically rendered**. Care must be taken in any
3996 multiple-table update to explicitly include the joining
3997 condition between those tables, even in mappings where
3998 this is normally automatic. E.g. if a class ``Engineer``
3999 subclasses ``Employee``, an UPDATE of the ``Engineer``
4000 local table using criteria against the ``Employee``
4001 local table might look like::
4003 session.query(Engineer).\
4004 filter(Engineer.id == Employee.id).\
4005 filter(Employee.name == 'dilbert').\
4006 update({"engineer_type": "programmer"})
4008 * The polymorphic identity WHERE criteria is **not** included
4009 for single- or
4010 joined- table updates - this must be added **manually**, even
4011 for single table inheritance.
4013 * The :meth:`.MapperEvents.before_update` and
4014 :meth:`.MapperEvents.after_update`
4015 events **are not invoked from this method**. Instead, the
4016 :meth:`.SessionEvents.after_bulk_update` method is provided to
4017 act upon a mass UPDATE of entity rows.
4019 .. seealso::
4021 :meth:`_query.Query.delete`
4023 :ref:`inserts_and_updates` - Core SQL tutorial
4025 """
4027 update_args = update_args or {}
4028 update_op = persistence.BulkUpdate.factory(
4029 self, synchronize_session, values, update_args
4030 )
4031 update_op.exec_()
4032 return update_op.rowcount
4034 def _compile_context(self, labels=True):
4035 if self.dispatch.before_compile:
4036 for fn in self.dispatch.before_compile:
4037 new_query = fn(self)
4038 if new_query is not None and new_query is not self:
4039 self = new_query
4040 if not fn._bake_ok:
4041 self._bake_ok = False
4043 context = QueryContext(self)
4045 if context.statement is not None:
4046 return context
4048 context.labels = labels
4050 context._for_update_arg = self._for_update_arg
4052 for entity in self._entities:
4053 entity.setup_context(self, context)
4055 for rec in context.create_eager_joins:
4056 strategy = rec[0]
4057 strategy(context, *rec[1:])
4059 if context.from_clause:
4060 # "load from explicit FROMs" mode,
4061 # i.e. when select_from() or join() is used
4062 context.froms = list(context.from_clause)
4063 # else "load from discrete FROMs" mode,
4064 # i.e. when each _MappedEntity has its own FROM
4066 if self._enable_single_crit:
4067 self._adjust_for_single_inheritance(context)
4069 if not context.primary_columns:
4070 if self._only_load_props:
4071 raise sa_exc.InvalidRequestError(
4072 "No column-based properties specified for "
4073 "refresh operation. Use session.expire() "
4074 "to reload collections and related items."
4075 )
4076 else:
4077 raise sa_exc.InvalidRequestError(
4078 "Query contains no columns with which to " "SELECT from."
4079 )
4081 if context.multi_row_eager_loaders and self._should_nest_selectable:
4082 context.statement = self._compound_eager_statement(context)
4083 else:
4084 context.statement = self._simple_statement(context)
4086 return context
4088 def _compound_eager_statement(self, context):
4089 # for eager joins present and LIMIT/OFFSET/DISTINCT,
4090 # wrap the query inside a select,
4091 # then append eager joins onto that
4093 if context.order_by:
4094 order_by_col_expr = sql_util.expand_column_list_from_order_by(
4095 context.primary_columns, context.order_by
4096 )
4097 else:
4098 context.order_by = None
4099 order_by_col_expr = []
4101 inner = sql.select(
4102 context.primary_columns + order_by_col_expr,
4103 context.whereclause,
4104 from_obj=context.froms,
4105 use_labels=context.labels,
4106 # TODO: this order_by is only needed if
4107 # LIMIT/OFFSET is present in self._select_args,
4108 # else the application on the outside is enough
4109 order_by=context.order_by,
4110 **self._select_args
4111 )
4112 # put FOR UPDATE on the inner query, where MySQL will honor it,
4113 # as well as if it has an OF so PostgreSQL can use it.
4114 inner._for_update_arg = context._for_update_arg
4116 for hint in self._with_hints:
4117 inner = inner.with_hint(*hint)
4119 if self._correlate:
4120 inner = inner.correlate(*self._correlate)
4122 inner = inner.alias()
4124 equivs = self.__all_equivs()
4126 context.adapter = sql_util.ColumnAdapter(inner, equivs)
4128 statement = sql.select(
4129 [inner] + context.secondary_columns, use_labels=context.labels
4130 )
4132 # Oracle however does not allow FOR UPDATE on the subquery,
4133 # and the Oracle dialect ignores it, plus for PostgreSQL, MySQL
4134 # we expect that all elements of the row are locked, so also put it
4135 # on the outside (except in the case of PG when OF is used)
4136 if (
4137 context._for_update_arg is not None
4138 and context._for_update_arg.of is None
4139 ):
4140 statement._for_update_arg = context._for_update_arg
4142 from_clause = inner
4143 for eager_join in context.eager_joins.values():
4144 # EagerLoader places a 'stop_on' attribute on the join,
4145 # giving us a marker as to where the "splice point" of
4146 # the join should be
4147 from_clause = sql_util.splice_joins(
4148 from_clause, eager_join, eager_join.stop_on
4149 )
4151 statement.append_from(from_clause)
4153 if context.order_by:
4154 statement.append_order_by(
4155 *context.adapter.copy_and_process(context.order_by)
4156 )
4158 statement.append_order_by(*context.eager_order_by)
4159 return statement
4161 def _simple_statement(self, context):
4162 if not context.order_by:
4163 context.order_by = None
4165 if self._distinct is True and context.order_by:
4166 context.primary_columns += (
4167 sql_util.expand_column_list_from_order_by
4168 )(context.primary_columns, context.order_by)
4169 context.froms += tuple(context.eager_joins.values())
4171 statement = sql.select(
4172 context.primary_columns + context.secondary_columns,
4173 context.whereclause,
4174 from_obj=context.froms,
4175 use_labels=context.labels,
4176 order_by=context.order_by,
4177 **self._select_args
4178 )
4179 statement._for_update_arg = context._for_update_arg
4181 for hint in self._with_hints:
4182 statement = statement.with_hint(*hint)
4184 if self._correlate:
4185 statement = statement.correlate(*self._correlate)
4187 if context.eager_order_by:
4188 statement.append_order_by(*context.eager_order_by)
4189 return statement
4191 def _adjust_for_single_inheritance(self, context):
4192 """Apply single-table-inheritance filtering.
4194 For all distinct single-table-inheritance mappers represented in
4195 the columns clause of this query, as well as the "select from entity",
4196 add criterion to the WHERE
4197 clause of the given QueryContext such that only the appropriate
4198 subtypes are selected from the total results.
4200 """
4202 search = set(self._mapper_adapter_map.values())
4203 if (
4204 self._select_from_entity
4205 and self._select_from_entity not in self._mapper_adapter_map
4206 ):
4207 insp = inspect(self._select_from_entity)
4208 if insp.is_aliased_class:
4209 adapter = insp._adapter
4210 else:
4211 adapter = None
4212 search = search.union([(self._select_from_entity, adapter)])
4214 for (ext_info, adapter) in search:
4215 if ext_info in self._join_entities:
4216 continue
4217 single_crit = ext_info.mapper._single_table_criterion
4218 if single_crit is not None:
4219 if adapter:
4220 single_crit = adapter.traverse(single_crit)
4222 single_crit = self._adapt_clause(single_crit, False, False)
4223 context.whereclause = sql.and_(
4224 sql.True_._ifnone(context.whereclause), single_crit
4225 )
4228class LockmodeArg(ForUpdateArg):
4229 @classmethod
4230 def parse_legacy_query(self, mode):
4231 if mode in (None, False):
4232 return None
4234 if mode == "read":
4235 read = True
4236 nowait = False
4237 elif mode == "update":
4238 read = nowait = False
4239 elif mode == "update_nowait":
4240 nowait = True
4241 read = False
4242 else:
4243 raise sa_exc.ArgumentError(
4244 "Unknown with_lockmode argument: %r" % mode
4245 )
4247 return LockmodeArg(read=read, nowait=nowait)
4250class _QueryEntity(object):
4251 """represent an entity column returned within a Query result."""
4253 def __new__(cls, *args, **kwargs):
4254 if cls is _QueryEntity:
4255 entity = args[1]
4256 if not isinstance(entity, util.string_types) and _is_mapped_class(
4257 entity
4258 ):
4259 cls = _MapperEntity
4260 elif isinstance(entity, Bundle):
4261 cls = _BundleEntity
4262 else:
4263 cls = _ColumnEntity
4264 return object.__new__(cls)
4266 def _clone(self):
4267 q = self.__class__.__new__(self.__class__)
4268 q.__dict__ = self.__dict__.copy()
4269 return q
4272class _MapperEntity(_QueryEntity):
4273 """mapper/class/AliasedClass entity"""
4275 def __init__(self, query, entity):
4276 if not query._primary_entity:
4277 query._primary_entity = self
4278 query._entities.append(self)
4279 query._has_mapper_entities = True
4280 self.entities = [entity]
4281 self.expr = entity
4283 supports_single_entity = True
4285 use_id_for_hash = True
4287 def setup_entity(self, ext_info, aliased_adapter):
4288 self.mapper = ext_info.mapper
4289 self.aliased_adapter = aliased_adapter
4290 self.selectable = ext_info.selectable
4291 self.is_aliased_class = ext_info.is_aliased_class
4292 self._with_polymorphic = ext_info.with_polymorphic_mappers
4293 self._polymorphic_discriminator = ext_info.polymorphic_on
4294 self.entity_zero = ext_info
4295 if ext_info.is_aliased_class:
4296 self._label_name = self.entity_zero.name
4297 else:
4298 self._label_name = self.mapper.class_.__name__
4299 self.path = self.entity_zero._path_registry
4301 def set_with_polymorphic(
4302 self, query, cls_or_mappers, selectable, polymorphic_on
4303 ):
4304 """Receive an update from a call to query.with_polymorphic().
4306 Note the newer style of using a free standing with_polymporphic()
4307 construct doesn't make use of this method.
4310 """
4311 if self.is_aliased_class:
4312 # TODO: invalidrequest ?
4313 raise NotImplementedError(
4314 "Can't use with_polymorphic() against " "an Aliased object"
4315 )
4317 if cls_or_mappers is None:
4318 query._reset_polymorphic_adapter(self.mapper)
4319 return
4321 mappers, from_obj = self.mapper._with_polymorphic_args(
4322 cls_or_mappers, selectable
4323 )
4324 self._with_polymorphic = mappers
4325 self._polymorphic_discriminator = polymorphic_on
4327 self.selectable = from_obj
4328 query._mapper_loads_polymorphically_with(
4329 self.mapper,
4330 sql_util.ColumnAdapter(from_obj, self.mapper._equivalent_columns),
4331 )
4333 @property
4334 def type(self):
4335 return self.mapper.class_
4337 @property
4338 def entity_zero_or_selectable(self):
4339 return self.entity_zero
4341 def corresponds_to(self, entity):
4342 return _entity_corresponds_to(self.entity_zero, entity)
4344 def adapt_to_selectable(self, query, sel):
4345 query._entities.append(self)
4347 def _get_entity_clauses(self, query, context):
4349 adapter = None
4351 if not self.is_aliased_class:
4352 if query._polymorphic_adapters:
4353 adapter = query._polymorphic_adapters.get(self.mapper, None)
4354 else:
4355 adapter = self.aliased_adapter
4357 if adapter:
4358 if query._from_obj_alias:
4359 ret = adapter.wrap(query._from_obj_alias)
4360 else:
4361 ret = adapter
4362 else:
4363 ret = query._from_obj_alias
4365 return ret
4367 def row_processor(self, query, context, result):
4368 adapter = self._get_entity_clauses(query, context)
4370 if context.adapter and adapter:
4371 adapter = adapter.wrap(context.adapter)
4372 elif not adapter:
4373 adapter = context.adapter
4375 # polymorphic mappers which have concrete tables in
4376 # their hierarchy usually
4377 # require row aliasing unconditionally.
4378 if not adapter and self.mapper._requires_row_aliasing:
4379 adapter = sql_util.ColumnAdapter(
4380 self.selectable, self.mapper._equivalent_columns
4381 )
4383 if query._primary_entity is self:
4384 only_load_props = query._only_load_props
4385 refresh_state = context.refresh_state
4386 else:
4387 only_load_props = refresh_state = None
4389 _instance = loading._instance_processor(
4390 self.mapper,
4391 context,
4392 result,
4393 self.path,
4394 adapter,
4395 only_load_props=only_load_props,
4396 refresh_state=refresh_state,
4397 polymorphic_discriminator=self._polymorphic_discriminator,
4398 )
4400 return _instance, self._label_name
4402 def setup_context(self, query, context):
4403 adapter = self._get_entity_clauses(query, context)
4405 # if self._adapted_selectable is None:
4406 context.froms += (self.selectable,)
4408 if context.order_by is False and self.mapper.order_by:
4409 context.order_by = self.mapper.order_by
4411 # apply adaptation to the mapper's order_by if needed.
4412 if adapter:
4413 context.order_by = adapter.adapt_list(
4414 util.to_list(context.order_by)
4415 )
4417 loading._setup_entity_query(
4418 context,
4419 self.mapper,
4420 self,
4421 self.path,
4422 adapter,
4423 context.primary_columns,
4424 with_polymorphic=self._with_polymorphic,
4425 only_load_props=query._only_load_props,
4426 polymorphic_discriminator=self._polymorphic_discriminator,
4427 )
4429 def __str__(self):
4430 return str(self.mapper)
4433@inspection._self_inspects
4434class Bundle(InspectionAttr):
4435 """A grouping of SQL expressions that are returned by a
4436 :class:`_query.Query`
4437 under one namespace.
4439 The :class:`.Bundle` essentially allows nesting of the tuple-based
4440 results returned by a column-oriented :class:`_query.Query` object.
4441 It also
4442 is extensible via simple subclassing, where the primary capability
4443 to override is that of how the set of expressions should be returned,
4444 allowing post-processing as well as custom return types, without
4445 involving ORM identity-mapped classes.
4447 .. versionadded:: 0.9.0
4449 .. seealso::
4451 :ref:`bundles`
4453 """
4455 single_entity = False
4456 """If True, queries for a single Bundle will be returned as a single
4457 entity, rather than an element within a keyed tuple."""
4459 is_clause_element = False
4461 is_mapper = False
4463 is_aliased_class = False
4465 def __init__(self, name, *exprs, **kw):
4466 r"""Construct a new :class:`.Bundle`.
4468 e.g.::
4470 bn = Bundle("mybundle", MyClass.x, MyClass.y)
4472 for row in session.query(bn).filter(
4473 bn.c.x == 5).filter(bn.c.y == 4):
4474 print(row.mybundle.x, row.mybundle.y)
4476 :param name: name of the bundle.
4477 :param \*exprs: columns or SQL expressions comprising the bundle.
4478 :param single_entity=False: if True, rows for this :class:`.Bundle`
4479 can be returned as a "single entity" outside of any enclosing tuple
4480 in the same manner as a mapped entity.
4482 """
4483 self.name = self._label = name
4484 self.exprs = exprs
4485 self.c = self.columns = ColumnCollection()
4486 self.columns.update(
4487 (getattr(col, "key", col._label), col) for col in exprs
4488 )
4489 self.single_entity = kw.pop("single_entity", self.single_entity)
4491 columns = None
4492 """A namespace of SQL expressions referred to by this :class:`.Bundle`.
4494 e.g.::
4496 bn = Bundle("mybundle", MyClass.x, MyClass.y)
4498 q = sess.query(bn).filter(bn.c.x == 5)
4500 Nesting of bundles is also supported::
4502 b1 = Bundle("b1",
4503 Bundle('b2', MyClass.a, MyClass.b),
4504 Bundle('b3', MyClass.x, MyClass.y)
4505 )
4507 q = sess.query(b1).filter(
4508 b1.c.b2.c.a == 5).filter(b1.c.b3.c.y == 9)
4510 .. seealso::
4512 :attr:`.Bundle.c`
4514 """
4516 c = None
4517 """An alias for :attr:`.Bundle.columns`."""
4519 def _clone(self):
4520 cloned = self.__class__.__new__(self.__class__)
4521 cloned.__dict__.update(self.__dict__)
4522 return cloned
4524 def __clause_element__(self):
4525 return expression.ClauseList(group=False, *self.exprs)
4527 @property
4528 def clauses(self):
4529 return self.__clause_element__().clauses
4531 def label(self, name):
4532 """Provide a copy of this :class:`.Bundle` passing a new label."""
4534 cloned = self._clone()
4535 cloned.name = name
4536 return cloned
4538 def create_row_processor(self, query, procs, labels):
4539 """Produce the "row processing" function for this :class:`.Bundle`.
4541 May be overridden by subclasses.
4543 .. seealso::
4545 :ref:`bundles` - includes an example of subclassing.
4547 """
4548 keyed_tuple = util.lightweight_named_tuple("result", labels)
4550 def proc(row):
4551 return keyed_tuple([proc(row) for proc in procs])
4553 return proc
4556class _BundleEntity(_QueryEntity):
4557 use_id_for_hash = False
4559 def __init__(self, query, bundle, setup_entities=True):
4560 query._entities.append(self)
4561 self.bundle = self.expr = bundle
4562 self.type = type(bundle)
4563 self._label_name = bundle.name
4564 self._entities = []
4566 if setup_entities:
4567 for expr in bundle.exprs:
4568 if isinstance(expr, Bundle):
4569 _BundleEntity(self, expr)
4570 else:
4571 _ColumnEntity(self, expr)
4573 self.supports_single_entity = self.bundle.single_entity
4575 @property
4576 def mapper(self):
4577 ezero = self.entity_zero
4578 if ezero is not None:
4579 return ezero.mapper
4580 else:
4581 return None
4583 @property
4584 def entities(self):
4585 entities = []
4586 for ent in self._entities:
4587 entities.extend(ent.entities)
4588 return entities
4590 @property
4591 def entity_zero(self):
4592 for ent in self._entities:
4593 ezero = ent.entity_zero
4594 if ezero is not None:
4595 return ezero
4596 else:
4597 return None
4599 def corresponds_to(self, entity):
4600 # TODO: we might be able to implement this but for now
4601 # we are working around it
4602 return False
4604 @property
4605 def entity_zero_or_selectable(self):
4606 for ent in self._entities:
4607 ezero = ent.entity_zero_or_selectable
4608 if ezero is not None:
4609 return ezero
4610 else:
4611 return None
4613 def adapt_to_selectable(self, query, sel):
4614 c = _BundleEntity(query, self.bundle, setup_entities=False)
4615 # c._label_name = self._label_name
4616 # c.entity_zero = self.entity_zero
4617 # c.entities = self.entities
4619 for ent in self._entities:
4620 ent.adapt_to_selectable(c, sel)
4622 def setup_entity(self, ext_info, aliased_adapter):
4623 for ent in self._entities:
4624 ent.setup_entity(ext_info, aliased_adapter)
4626 def setup_context(self, query, context):
4627 for ent in self._entities:
4628 ent.setup_context(query, context)
4630 def row_processor(self, query, context, result):
4631 procs, labels = zip(
4632 *[
4633 ent.row_processor(query, context, result)
4634 for ent in self._entities
4635 ]
4636 )
4638 proc = self.bundle.create_row_processor(query, procs, labels)
4640 return proc, self._label_name
4643class _ColumnEntity(_QueryEntity):
4644 """Column/expression based entity."""
4646 def __init__(self, query, column, namespace=None):
4647 self.expr = column
4648 self.namespace = namespace
4649 search_entities = True
4650 check_column = False
4652 if isinstance(column, util.string_types):
4653 util.warn_deprecated(
4654 "Plain string expression passed to Query() should be "
4655 "explicitly declared using literal_column(); "
4656 "automatic coercion of this value will be removed in "
4657 "SQLAlchemy 1.4"
4658 )
4659 column = sql.literal_column(column)
4660 self._label_name = column.name
4661 search_entities = False
4662 check_column = True
4663 _entity = None
4664 elif isinstance(
4665 column, (attributes.QueryableAttribute, interfaces.PropComparator)
4666 ):
4667 _entity = getattr(column, "_parententity", None)
4668 if _entity is not None:
4669 search_entities = False
4670 self._label_name = column.key
4671 column = column._query_clause_element()
4672 check_column = True
4673 if isinstance(column, Bundle):
4674 _BundleEntity(query, column)
4675 return
4677 if not isinstance(column, sql.ColumnElement):
4678 if hasattr(column, "_select_iterable"):
4679 # break out an object like Table into
4680 # individual columns
4681 for c in column._select_iterable:
4682 if c is column:
4683 break
4684 _ColumnEntity(query, c, namespace=column)
4685 else:
4686 return
4688 raise sa_exc.InvalidRequestError(
4689 "SQL expression, column, or mapped entity "
4690 "expected - got '%r'" % (column,)
4691 )
4692 elif not check_column:
4693 self._label_name = getattr(column, "key", None)
4694 search_entities = True
4696 self.type = type_ = column.type
4697 self.use_id_for_hash = not type_.hashable
4699 # If the Column is unnamed, give it a
4700 # label() so that mutable column expressions
4701 # can be located in the result even
4702 # if the expression's identity has been changed
4703 # due to adaption.
4705 if not column._label and not getattr(column, "is_literal", False):
4706 column = column.label(self._label_name)
4708 query._entities.append(self)
4710 self.column = column
4711 self.froms = set()
4713 # look for ORM entities represented within the
4714 # given expression. Try to count only entities
4715 # for columns whose FROM object is in the actual list
4716 # of FROMs for the overall expression - this helps
4717 # subqueries which were built from ORM constructs from
4718 # leaking out their entities into the main select construct
4719 self.actual_froms = list(column._from_objects)
4720 actual_froms = set(self.actual_froms)
4722 if not search_entities:
4723 self.entity_zero = _entity
4724 if _entity:
4725 self.entities = [_entity]
4726 self.mapper = _entity.mapper
4727 else:
4728 self.entities = []
4729 self.mapper = None
4730 self._from_entities = set(self.entities)
4731 else:
4732 all_elements = [
4733 elem
4734 for elem in sql_util.surface_column_elements(
4735 column, include_scalar_selects=False
4736 )
4737 if "parententity" in elem._annotations
4738 ]
4740 self.entities = util.unique_list(
4741 [
4742 elem._annotations["parententity"]
4743 for elem in all_elements
4744 if "parententity" in elem._annotations
4745 ]
4746 )
4748 self._from_entities = set(
4749 [
4750 elem._annotations["parententity"]
4751 for elem in all_elements
4752 if "parententity" in elem._annotations
4753 and actual_froms.intersection(elem._from_objects)
4754 ]
4755 )
4756 if self.entities:
4757 self.entity_zero = self.entities[0]
4758 self.mapper = self.entity_zero.mapper
4759 elif self.namespace is not None:
4760 self.entity_zero = self.namespace
4761 self.mapper = None
4762 else:
4763 self.entity_zero = None
4764 self.mapper = None
4766 supports_single_entity = False
4768 @property
4769 def entity_zero_or_selectable(self):
4770 if self.entity_zero is not None:
4771 return self.entity_zero
4772 elif self.actual_froms:
4773 return self.actual_froms[0]
4774 else:
4775 return None
4777 def adapt_to_selectable(self, query, sel):
4778 c = _ColumnEntity(query, sel.corresponding_column(self.column))
4779 c._label_name = self._label_name
4780 c.entity_zero = self.entity_zero
4781 c.entities = self.entities
4783 def setup_entity(self, ext_info, aliased_adapter):
4784 if "selectable" not in self.__dict__:
4785 self.selectable = ext_info.selectable
4787 if set(self.actual_froms).intersection(
4788 ext_info.selectable._from_objects
4789 ):
4790 self.froms.add(ext_info.selectable)
4792 def corresponds_to(self, entity):
4793 if self.entity_zero is None:
4794 return False
4795 elif _is_aliased_class(entity):
4796 # TODO: polymorphic subclasses ?
4797 return entity is self.entity_zero
4798 else:
4799 return not _is_aliased_class(
4800 self.entity_zero
4801 ) and entity.common_parent(self.entity_zero)
4803 def row_processor(self, query, context, result):
4804 if ("fetch_column", self) in context.attributes:
4805 column = context.attributes[("fetch_column", self)]
4806 else:
4807 column = query._adapt_clause(self.column, False, True)
4809 if column._annotations:
4810 # annotated columns perform more slowly in compiler and
4811 # result due to the __eq__() method, so use deannotated
4812 column = column._deannotate()
4814 if context.adapter:
4815 column = context.adapter.columns[column]
4817 getter = result._getter(column)
4818 return getter, self._label_name
4820 def setup_context(self, query, context):
4821 column = query._adapt_clause(self.column, False, True)
4823 if column._annotations:
4824 # annotated columns perform more slowly in compiler and
4825 # result due to the __eq__() method, so use deannotated
4826 column = column._deannotate()
4828 context.froms += tuple(self.froms)
4829 context.primary_columns.append(column)
4831 context.attributes[("fetch_column", self)] = column
4833 def __str__(self):
4834 return str(self.column)
4837class QueryContext(object):
4838 __slots__ = (
4839 "multi_row_eager_loaders",
4840 "adapter",
4841 "froms",
4842 "for_update",
4843 "query",
4844 "session",
4845 "autoflush",
4846 "populate_existing",
4847 "invoke_all_eagers",
4848 "version_check",
4849 "refresh_state",
4850 "primary_columns",
4851 "secondary_columns",
4852 "eager_order_by",
4853 "eager_joins",
4854 "create_eager_joins",
4855 "propagate_options",
4856 "attributes",
4857 "statement",
4858 "from_clause",
4859 "whereclause",
4860 "order_by",
4861 "labels",
4862 "_for_update_arg",
4863 "runid",
4864 "partials",
4865 "post_load_paths",
4866 "identity_token",
4867 )
4869 def __init__(self, query):
4871 if query._statement is not None:
4872 if (
4873 isinstance(query._statement, expression.SelectBase)
4874 and not query._statement._textual
4875 and not query._statement.use_labels
4876 ):
4877 self.statement = query._statement.apply_labels()
4878 else:
4879 self.statement = query._statement
4880 else:
4881 self.statement = None
4882 self.from_clause = query._from_obj
4883 self.whereclause = query._criterion
4884 self.order_by = query._order_by
4886 self.multi_row_eager_loaders = False
4887 self.adapter = None
4888 self.froms = ()
4889 self.for_update = None
4890 self.query = query
4891 self.session = query.session
4892 self.autoflush = query._autoflush
4893 self.populate_existing = query._populate_existing
4894 self.invoke_all_eagers = query._invoke_all_eagers
4895 self.version_check = query._version_check
4896 self.refresh_state = query._refresh_state
4897 self.primary_columns = []
4898 self.secondary_columns = []
4899 self.eager_order_by = []
4900 self.eager_joins = {}
4901 self.create_eager_joins = []
4902 self.propagate_options = set(
4903 o for o in query._with_options if o.propagate_to_loaders
4904 )
4905 self.attributes = query._attributes.copy()
4906 if self.refresh_state is not None:
4907 self.identity_token = query._refresh_identity_token
4908 else:
4909 self.identity_token = None
4912class AliasOption(interfaces.MapperOption):
4913 def __init__(self, alias):
4914 r"""Return a :class:`.MapperOption` that will indicate to the
4915 :class:`_query.Query`
4916 that the main table has been aliased.
4918 This is a seldom-used option to suit the
4919 very rare case that :func:`.contains_eager`
4920 is being used in conjunction with a user-defined SELECT
4921 statement that aliases the parent table. E.g.::
4923 # define an aliased UNION called 'ulist'
4924 ulist = users.select(users.c.user_id==7).\
4925 union(users.select(users.c.user_id>7)).\
4926 alias('ulist')
4928 # add on an eager load of "addresses"
4929 statement = ulist.outerjoin(addresses).\
4930 select().apply_labels()
4932 # create query, indicating "ulist" will be an
4933 # alias for the main table, "addresses"
4934 # property should be eager loaded
4935 query = session.query(User).options(
4936 contains_alias(ulist),
4937 contains_eager(User.addresses))
4939 # then get results via the statement
4940 results = query.from_statement(statement).all()
4942 :param alias: is the string name of an alias, or a
4943 :class:`_expression.Alias` object representing
4944 the alias.
4946 """
4947 self.alias = alias
4949 def process_query(self, query):
4950 if isinstance(self.alias, util.string_types):
4951 alias = query._mapper_zero().persist_selectable.alias(self.alias)
4952 else:
4953 alias = self.alias
4954 query._from_obj_alias = sql_util.ColumnAdapter(alias)