Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/query.py: 42%
Shortcuts on this page
r m x 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-2026 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: https://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"""
21from __future__ import annotations
23import collections.abc as collections_abc
24import operator
25from typing import Any
26from typing import Callable
27from typing import cast
28from typing import Dict
29from typing import Generic
30from typing import Iterable
31from typing import Iterator
32from typing import List
33from typing import Literal
34from typing import Mapping
35from typing import Optional
36from typing import overload
37from typing import Sequence
38from typing import SupportsIndex
39from typing import Tuple
40from typing import Type
41from typing import TYPE_CHECKING
42from typing import TypeVar
43from typing import Union
45from . import attributes
46from . import interfaces
47from . import loading
48from . import util as orm_util
49from ._typing import _O
50from .base import _assertions
51from .context import _column_descriptions
52from .context import _determine_last_joined_entity
53from .context import _legacy_filter_by_entity_zero
54from .context import _ORMCompileState
55from .context import FromStatement
56from .context import QueryContext
57from .interfaces import ORMColumnDescription
58from .interfaces import ORMColumnsClauseRole
59from .util import AliasedClass
60from .util import object_mapper
61from .util import with_parent
62from .. import exc as sa_exc
63from .. import inspect
64from .. import inspection
65from .. import log
66from .. import sql
67from .. import util
68from ..engine import Result
69from ..engine import Row
70from ..event import dispatcher
71from ..event import EventTarget
72from ..sql import coercions
73from ..sql import expression
74from ..sql import roles
75from ..sql import Select
76from ..sql import util as sql_util
77from ..sql import visitors
78from ..sql._typing import _FromClauseArgument
79from ..sql.annotation import SupportsCloneAnnotations
80from ..sql.base import _entity_namespace_key
81from ..sql.base import _generative
82from ..sql.base import _NoArg
83from ..sql.base import Executable
84from ..sql.base import Generative
85from ..sql.elements import BooleanClauseList
86from ..sql.expression import Exists
87from ..sql.selectable import _MemoizedSelectEntities
88from ..sql.selectable import _SelectFromElements
89from ..sql.selectable import ForUpdateArg
90from ..sql.selectable import HasHints
91from ..sql.selectable import HasPrefixes
92from ..sql.selectable import HasSuffixes
93from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL
94from ..sql.selectable import SelectLabelStyle
95from ..util import deprecated
96from ..util import warn_deprecated
97from ..util.typing import Self
98from ..util.typing import TupleAny
99from ..util.typing import TypeVarTuple
100from ..util.typing import Unpack
103if TYPE_CHECKING:
104 from ._typing import _EntityType
105 from ._typing import _ExternalEntityType
106 from ._typing import _InternalEntityType
107 from ._typing import SynchronizeSessionArgument
108 from .mapper import Mapper
109 from .path_registry import PathRegistry
110 from .session import _PKIdentityArgument
111 from .session import Session
112 from .state import InstanceState
113 from ..engine.cursor import CursorResult
114 from ..engine.interfaces import _ImmutableExecuteOptions
115 from ..engine.interfaces import CompiledCacheType
116 from ..engine.interfaces import IsolationLevel
117 from ..engine.interfaces import SchemaTranslateMapType
118 from ..engine.result import FrozenResult
119 from ..engine.result import ScalarResult
120 from ..sql._typing import _ColumnExpressionArgument
121 from ..sql._typing import _ColumnExpressionOrStrLabelArgument
122 from ..sql._typing import _ColumnsClauseArgument
123 from ..sql._typing import _DMLColumnArgument
124 from ..sql._typing import _JoinTargetArgument
125 from ..sql._typing import _LimitOffsetType
126 from ..sql._typing import _MAYBE_ENTITY
127 from ..sql._typing import _no_kw
128 from ..sql._typing import _NOT_ENTITY
129 from ..sql._typing import _OnClauseArgument
130 from ..sql._typing import _PropagateAttrsType
131 from ..sql._typing import _T0
132 from ..sql._typing import _T1
133 from ..sql._typing import _T2
134 from ..sql._typing import _T3
135 from ..sql._typing import _T4
136 from ..sql._typing import _T5
137 from ..sql._typing import _T6
138 from ..sql._typing import _T7
139 from ..sql._typing import _TypedColumnClauseArgument as _TCCA
140 from ..sql.base import CacheableOptions
141 from ..sql.base import ExecutableOption
142 from ..sql.base import SyntaxExtension
143 from ..sql.dml import UpdateBase
144 from ..sql.elements import ColumnElement
145 from ..sql.elements import Label
146 from ..sql.selectable import _ForUpdateOfArgument
147 from ..sql.selectable import _JoinTargetElement
148 from ..sql.selectable import _SetupJoinsElement
149 from ..sql.selectable import Alias
150 from ..sql.selectable import CTE
151 from ..sql.selectable import ExecutableReturnsRows
152 from ..sql.selectable import FromClause
153 from ..sql.selectable import ScalarSelect
154 from ..sql.selectable import Subquery
157__all__ = ["Query", "QueryContext"]
159_T = TypeVar("_T", bound=Any)
160_Ts = TypeVarTuple("_Ts")
163@inspection._self_inspects
164@log.class_logger
165class Query(
166 _SelectFromElements,
167 SupportsCloneAnnotations,
168 HasPrefixes,
169 HasSuffixes,
170 HasHints,
171 EventTarget,
172 log.Identified,
173 Generative,
174 Executable,
175 Generic[_T],
176):
177 """ORM-level SQL construction object.
179 .. legacy:: The ORM :class:`.Query` object is a legacy construct
180 as of SQLAlchemy 2.0. See the notes at the top of
181 :ref:`query_api_toplevel` for an overview, including links to migration
182 documentation.
184 :class:`_query.Query` objects are normally initially generated using the
185 :meth:`~.Session.query` method of :class:`.Session`, and in
186 less common cases by instantiating the :class:`_query.Query` directly and
187 associating with a :class:`.Session` using the
188 :meth:`_query.Query.with_session`
189 method.
191 """
193 # elements that are in Core and can be cached in the same way
194 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
195 _having_criteria: Tuple[ColumnElement[Any], ...] = ()
197 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()
198 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()
199 _limit_clause: Optional[ColumnElement[Any]] = None
200 _offset_clause: Optional[ColumnElement[Any]] = None
202 _distinct: bool = False
203 _distinct_on: Tuple[ColumnElement[Any], ...] = ()
205 _for_update_arg: Optional[ForUpdateArg] = None
206 _correlate: Tuple[FromClause, ...] = ()
207 _auto_correlate: bool = True
208 _from_obj: Tuple[FromClause, ...] = ()
209 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()
211 _label_style: SelectLabelStyle = SelectLabelStyle.LABEL_STYLE_LEGACY_ORM
213 _memoized_select_entities = ()
215 _syntax_extensions: Tuple[SyntaxExtension, ...] = ()
217 _compile_options: Union[Type[CacheableOptions], CacheableOptions] = (
218 _ORMCompileState.default_compile_options
219 )
221 _with_options: Tuple[ExecutableOption, ...]
222 load_options = QueryContext.default_load_options + {
223 "_legacy_uniquing": True
224 }
226 _params: util.immutabledict[str, Any] = util.EMPTY_DICT
228 # local Query builder state, not needed for
229 # compilation or execution
230 _enable_assertions = True
232 _statement: Optional[ExecutableReturnsRows] = None
234 session: Session
236 dispatch: dispatcher[Query[_T]]
238 # mirrors that of ClauseElement, used to propagate the "orm"
239 # plugin as well as the "subject" of the plugin, e.g. the mapper
240 # we are querying against.
241 @util.memoized_property
242 def _propagate_attrs(self) -> _PropagateAttrsType:
243 return util.EMPTY_DICT
245 def __init__(
246 self,
247 entities: Union[
248 _ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]
249 ],
250 session: Optional[Session] = None,
251 ):
252 """Construct a :class:`_query.Query` directly.
254 E.g.::
256 q = Query([User, Address], session=some_session)
258 The above is equivalent to::
260 q = some_session.query(User, Address)
262 :param entities: a sequence of entities and/or SQL expressions.
264 :param session: a :class:`.Session` with which the
265 :class:`_query.Query`
266 will be associated. Optional; a :class:`_query.Query`
267 can be associated
268 with a :class:`.Session` generatively via the
269 :meth:`_query.Query.with_session` method as well.
271 .. seealso::
273 :meth:`.Session.query`
275 :meth:`_query.Query.with_session`
277 """
279 # session is usually present. There's one case in subqueryloader
280 # where it stores a Query without a Session and also there are tests
281 # for the query(Entity).with_session(session) API which is likely in
282 # some old recipes, however these are legacy as select() can now be
283 # used.
284 self.session = session # type: ignore
285 self._set_entities(entities)
287 def _set_propagate_attrs(self, values: Mapping[str, Any]) -> Self:
288 self._propagate_attrs = util.immutabledict(values)
289 return self
291 def _set_entities(
292 self,
293 entities: Union[
294 _ColumnsClauseArgument[Any], Iterable[_ColumnsClauseArgument[Any]]
295 ],
296 ) -> None:
297 self._raw_columns = [
298 coercions.expect(
299 roles.ColumnsClauseRole,
300 ent,
301 apply_propagate_attrs=self,
302 post_inspect=True,
303 )
304 for ent in util.to_list(entities)
305 ]
307 @deprecated(
308 "2.1.0",
309 "The :meth:`.Query.tuples` method is deprecated, :class:`.Row` "
310 "now behaves like a tuple and can unpack types directly.",
311 )
312 def tuples(self: Query[_O]) -> Query[Tuple[_O]]:
313 """return a tuple-typed form of this :class:`.Query`.
315 This method invokes the :meth:`.Query.only_return_tuples`
316 method with a value of ``True``, which by itself ensures that this
317 :class:`.Query` will always return :class:`.Row` objects, even
318 if the query is made against a single entity. It then also
319 at the typing level will return a "typed" query, if possible,
320 that will type result rows as ``Tuple`` objects with typed
321 elements.
323 This method can be compared to the :meth:`.Result.tuples` method,
324 which returns "self", but from a typing perspective returns an object
325 that will yield typed ``Tuple`` objects for results. Typing
326 takes effect only if this :class:`.Query` object is a typed
327 query object already.
329 .. versionadded:: 2.0
331 .. seealso::
333 :ref:`change_10635` - describes a migration path from this
334 workaround for SQLAlchemy 2.1.
336 :meth:`.Result.tuples` - v2 equivalent method.
338 """
339 return self.only_return_tuples(True) # type: ignore
341 def _entity_from_pre_ent_zero(self) -> Optional[_InternalEntityType[Any]]:
342 if not self._raw_columns:
343 return None
345 ent = self._raw_columns[0]
347 if "parententity" in ent._annotations:
348 return ent._annotations["parententity"] # type: ignore
349 elif "bundle" in ent._annotations:
350 return ent._annotations["bundle"] # type: ignore
351 else:
352 # label, other SQL expression
353 for element in visitors.iterate(ent):
354 if "parententity" in element._annotations:
355 return element._annotations["parententity"] # type: ignore # noqa: E501
356 else:
357 return None
359 def _only_full_mapper_zero(self, methname: str) -> Mapper[Any]:
360 if (
361 len(self._raw_columns) != 1
362 or "parententity" not in self._raw_columns[0]._annotations
363 or not self._raw_columns[0].is_selectable
364 ):
365 raise sa_exc.InvalidRequestError(
366 "%s() can only be used against "
367 "a single mapped class." % methname
368 )
370 return self._raw_columns[0]._annotations["parententity"] # type: ignore # noqa: E501
372 def _set_select_from(
373 self, obj: Iterable[_FromClauseArgument], set_base_alias: bool
374 ) -> None:
375 fa = [
376 coercions.expect(
377 roles.FromClauseRole,
378 elem,
379 apply_propagate_attrs=self,
380 )
381 for elem in obj
382 ]
384 self._compile_options += {"_set_base_alias": set_base_alias}
385 self._from_obj = tuple(fa)
387 @_generative
388 def _set_lazyload_from(self, state: InstanceState[Any]) -> Self:
389 self.load_options += {"_lazy_loaded_from": state}
390 return self
392 def _get_condition(self) -> None:
393 """used by legacy BakedQuery"""
394 self._no_criterion_condition("get", order_by=False, distinct=False)
396 def _get_existing_condition(self) -> None:
397 self._no_criterion_assertion("get", order_by=False, distinct=False)
399 def _no_criterion_assertion(
400 self, meth: str, order_by: bool = True, distinct: bool = True
401 ) -> None:
402 if not self._enable_assertions:
403 return
404 if (
405 self._where_criteria
406 or self._statement is not None
407 or self._from_obj
408 or self._setup_joins
409 or self._limit_clause is not None
410 or self._offset_clause is not None
411 or self._group_by_clauses
412 or (order_by and self._order_by_clauses)
413 or (distinct and self._distinct)
414 ):
415 raise sa_exc.InvalidRequestError(
416 "Query.%s() being called on a "
417 "Query with existing criterion. " % meth
418 )
420 def _no_criterion_condition(
421 self, meth: str, order_by: bool = True, distinct: bool = True
422 ) -> None:
423 self._no_criterion_assertion(meth, order_by, distinct)
425 self._from_obj = self._setup_joins = ()
426 if self._statement is not None:
427 self._compile_options += {"_statement": None}
428 self._where_criteria = ()
429 self._distinct = False
431 self._order_by_clauses = self._group_by_clauses = ()
433 def _no_clauseelement_condition(self, meth: str) -> None:
434 if not self._enable_assertions:
435 return
436 if self._order_by_clauses:
437 raise sa_exc.InvalidRequestError(
438 "Query.%s() being called on a "
439 "Query with existing criterion. " % meth
440 )
441 self._no_criterion_condition(meth)
443 def _no_statement_condition(self, meth: str) -> None:
444 if not self._enable_assertions:
445 return
446 if self._statement is not None:
447 raise sa_exc.InvalidRequestError(
448 (
449 "Query.%s() being called on a Query with an existing full "
450 "statement - can't apply criterion."
451 )
452 % meth
453 )
455 def _no_limit_offset(self, meth: str) -> None:
456 if not self._enable_assertions:
457 return
458 if self._limit_clause is not None or self._offset_clause is not None:
459 raise sa_exc.InvalidRequestError(
460 "Query.%s() being called on a Query which already has LIMIT "
461 "or OFFSET applied. Call %s() before limit() or offset() "
462 "are applied." % (meth, meth)
463 )
465 @property
466 def _has_row_limiting_clause(self) -> bool:
467 return (
468 self._limit_clause is not None or self._offset_clause is not None
469 )
471 def _get_options(
472 self,
473 populate_existing: Optional[bool] = None,
474 version_check: Optional[bool] = None,
475 only_load_props: Optional[Sequence[str]] = None,
476 refresh_state: Optional[InstanceState[Any]] = None,
477 identity_token: Optional[Any] = None,
478 ) -> Self:
479 load_options: Dict[str, Any] = {}
480 compile_options: Dict[str, Any] = {}
482 if version_check:
483 load_options["_version_check"] = version_check
484 if populate_existing:
485 load_options["_populate_existing"] = populate_existing
486 if refresh_state:
487 load_options["_refresh_state"] = refresh_state
488 compile_options["_for_refresh_state"] = True
489 if only_load_props:
490 compile_options["_only_load_props"] = frozenset(only_load_props)
491 if identity_token:
492 load_options["_identity_token"] = identity_token
494 if load_options:
495 self.load_options += load_options
496 if compile_options:
497 self._compile_options += compile_options
499 return self
501 def _clone(self, **kw: Any) -> Self:
502 return self._generate()
504 def _get_select_statement_only(self) -> Select[_T]:
505 if self._statement is not None:
506 raise sa_exc.InvalidRequestError(
507 "Can't call this method on a Query that uses from_statement()"
508 )
509 return cast("Select[_T]", self.statement)
511 @property
512 def statement(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
513 """The full SELECT statement represented by this Query.
515 The statement by default will not have disambiguating labels
516 applied to the construct unless with_labels(True) is called
517 first.
519 """
521 # .statement can return the direct future.Select() construct here, as
522 # long as we are not using subsequent adaption features that
523 # are made against raw entities, e.g. from_self(), with_polymorphic(),
524 # select_entity_from(). If these features are being used, then
525 # the Select() we return will not have the correct .selected_columns
526 # collection and will not embed in subsequent queries correctly.
527 # We could find a way to make this collection "correct", however
528 # this would not be too different from doing the full compile as
529 # we are doing in any case, the Select() would still not have the
530 # proper state for other attributes like whereclause, order_by,
531 # and these features are all deprecated in any case.
532 #
533 # for these reasons, Query is not a Select, it remains an ORM
534 # object for which __clause_element__() must be called in order for
535 # it to provide a real expression object.
536 #
537 # from there, it starts to look much like Query itself won't be
538 # passed into the execute process and won't generate its own cache
539 # key; this will all occur in terms of the ORM-enabled Select.
540 stmt: Union[Select[_T], FromStatement[_T], UpdateBase]
542 if not self._compile_options._set_base_alias:
543 # if we don't have legacy top level aliasing features in use
544 # then convert to a future select() directly
545 stmt = self._statement_20(for_statement=True)
546 else:
547 stmt = self._compile_state(for_statement=True).statement
549 if self._params:
550 stmt = stmt.params(self._params)
552 return stmt
554 def _final_statement(
555 self, legacy_query_style: bool = True
556 ) -> Select[Unpack[TupleAny]]:
557 """Return the 'final' SELECT statement for this :class:`.Query`.
559 This is used by the testing suite only and is fairly inefficient.
561 This is the Core-only select() that will be rendered by a complete
562 compilation of this query, and is what .statement used to return
563 in 1.3.
566 """
568 q = self._clone()
570 return q._compile_state(
571 use_legacy_query_style=legacy_query_style
572 ).statement # type: ignore
574 def _statement_20(
575 self, for_statement: bool = False, use_legacy_query_style: bool = True
576 ) -> Union[Select[_T], FromStatement[_T]]:
577 # TODO: this event needs to be deprecated, as it currently applies
578 # only to ORM query and occurs at this spot that is now more
579 # or less an artificial spot
580 if self.dispatch.before_compile:
581 for fn in self.dispatch.before_compile:
582 new_query = fn(self)
583 if new_query is not None and new_query is not self:
584 self = new_query
585 if not fn._bake_ok: # type: ignore
586 self._compile_options += {"_bake_ok": False}
588 compile_options = self._compile_options
589 compile_options += {
590 "_for_statement": for_statement,
591 "_use_legacy_query_style": use_legacy_query_style,
592 }
594 stmt: Union[Select[_T], FromStatement[_T]]
596 if self._statement is not None:
597 stmt = FromStatement(self._raw_columns, self._statement)
598 stmt.__dict__.update(
599 _with_options=self._with_options,
600 _with_context_options=self._compile_state_funcs,
601 _compile_options=compile_options,
602 _execution_options=self._execution_options,
603 _propagate_attrs=self._propagate_attrs,
604 )
605 else:
606 # Query / select() internal attributes are 99% cross-compatible
607 stmt = Select._create_raw_select(**self.__dict__)
609 stmt.__dict__.update(
610 _label_style=self._label_style,
611 _compile_options=compile_options,
612 _propagate_attrs=self._propagate_attrs,
613 )
614 for ext in self._syntax_extensions:
615 stmt._apply_syntax_extension_to_self(ext)
616 stmt.__dict__.pop("session", None)
618 # ensure the ORM context is used to compile the statement, even
619 # if it has no ORM entities. This is so ORM-only things like
620 # _legacy_joins are picked up that wouldn't be picked up by the
621 # Core statement context
622 if "compile_state_plugin" not in stmt._propagate_attrs:
623 stmt._propagate_attrs = stmt._propagate_attrs.union(
624 {"compile_state_plugin": "orm", "plugin_subject": None}
625 )
627 return stmt
629 def subquery(
630 self,
631 name: Optional[str] = None,
632 with_labels: bool = False,
633 reduce_columns: bool = False,
634 ) -> Subquery:
635 """Return the full SELECT statement represented by
636 this :class:`_query.Query`, embedded within an
637 :class:`_expression.Alias`.
639 Eager JOIN generation within the query is disabled.
641 .. seealso::
643 :meth:`_sql.Select.subquery` - v2 comparable method.
645 :param name: string name to be assigned as the alias;
646 this is passed through to :meth:`_expression.FromClause.alias`.
647 If ``None``, a name will be deterministically generated
648 at compile time.
650 :param with_labels: if True, :meth:`.with_labels` will be called
651 on the :class:`_query.Query` first to apply table-qualified labels
652 to all columns.
654 :param reduce_columns: if True,
655 :meth:`_expression.Select.reduce_columns` will
656 be called on the resulting :func:`_expression.select` construct,
657 to remove same-named columns where one also refers to the other
658 via foreign key or WHERE clause equivalence.
660 """
661 q = self.enable_eagerloads(False)
662 if with_labels:
663 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
665 stmt = q._get_select_statement_only()
667 if TYPE_CHECKING:
668 assert isinstance(stmt, Select)
670 if reduce_columns:
671 stmt = stmt.reduce_columns()
672 return stmt.subquery(name=name)
674 def cte(
675 self,
676 name: Optional[str] = None,
677 recursive: bool = False,
678 nesting: bool = False,
679 ) -> CTE:
680 r"""Return the full SELECT statement represented by this
681 :class:`_query.Query` represented as a common table expression (CTE).
683 Parameters and usage are the same as those of the
684 :meth:`_expression.SelectBase.cte` method; see that method for
685 further details.
687 Here is the `PostgreSQL WITH
688 RECURSIVE example
689 <https://www.postgresql.org/docs/current/static/queries-with.html>`_.
690 Note that, in this example, the ``included_parts`` cte and the
691 ``incl_alias`` alias of it are Core selectables, which
692 means the columns are accessed via the ``.c.`` attribute. The
693 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
694 ``Part`` entity, so column-mapped attributes are available
695 directly::
697 from sqlalchemy.orm import aliased
700 class Part(Base):
701 __tablename__ = "part"
702 part = Column(String, primary_key=True)
703 sub_part = Column(String, primary_key=True)
704 quantity = Column(Integer)
707 included_parts = (
708 session.query(Part.sub_part, Part.part, Part.quantity)
709 .filter(Part.part == "our part")
710 .cte(name="included_parts", recursive=True)
711 )
713 incl_alias = aliased(included_parts, name="pr")
714 parts_alias = aliased(Part, name="p")
715 included_parts = included_parts.union_all(
716 session.query(
717 parts_alias.sub_part, parts_alias.part, parts_alias.quantity
718 ).filter(parts_alias.part == incl_alias.c.sub_part)
719 )
721 q = session.query(
722 included_parts.c.sub_part,
723 func.sum(included_parts.c.quantity).label("total_quantity"),
724 ).group_by(included_parts.c.sub_part)
726 .. seealso::
728 :meth:`_sql.Select.cte` - v2 equivalent method.
730 """ # noqa: E501
731 return (
732 self.enable_eagerloads(False)
733 ._get_select_statement_only()
734 .cte(name=name, recursive=recursive, nesting=nesting)
735 )
737 def label(self, name: Optional[str]) -> Label[Any]:
738 """Return the full SELECT statement represented by this
739 :class:`_query.Query`, converted
740 to a scalar subquery with a label of the given name.
742 .. seealso::
744 :meth:`_sql.Select.label` - v2 comparable method.
746 """
748 return (
749 self.enable_eagerloads(False)
750 ._get_select_statement_only()
751 .label(name)
752 )
754 @overload
755 def as_scalar( # type: ignore[overload-overlap]
756 self: Query[Tuple[_MAYBE_ENTITY]],
757 ) -> ScalarSelect[_MAYBE_ENTITY]: ...
759 @overload
760 def as_scalar(
761 self: Query[Tuple[_NOT_ENTITY]],
762 ) -> ScalarSelect[_NOT_ENTITY]: ...
764 @overload
765 def as_scalar(self) -> ScalarSelect[Any]: ...
767 @util.deprecated(
768 "1.4",
769 "The :meth:`_query.Query.as_scalar` method is deprecated and will be "
770 "removed in a future release. Please refer to "
771 ":meth:`_query.Query.scalar_subquery`.",
772 )
773 def as_scalar(self) -> ScalarSelect[Any]:
774 """Return the full SELECT statement represented by this
775 :class:`_query.Query`, converted to a scalar subquery.
777 """
778 return self.scalar_subquery()
780 @overload
781 def scalar_subquery(
782 self: Query[Tuple[_MAYBE_ENTITY]],
783 ) -> ScalarSelect[Any]: ...
785 @overload
786 def scalar_subquery(
787 self: Query[Tuple[_NOT_ENTITY]],
788 ) -> ScalarSelect[_NOT_ENTITY]: ...
790 @overload
791 def scalar_subquery(self) -> ScalarSelect[Any]: ...
793 def scalar_subquery(self) -> ScalarSelect[Any]:
794 """Return the full SELECT statement represented by this
795 :class:`_query.Query`, converted to a scalar subquery.
797 Analogous to
798 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
800 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
801 method replaces the :meth:`_query.Query.as_scalar` method.
803 .. seealso::
805 :meth:`_sql.Select.scalar_subquery` - v2 comparable method.
807 """
809 return (
810 self.enable_eagerloads(False)
811 ._get_select_statement_only()
812 .scalar_subquery()
813 )
815 @property
816 def selectable(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
817 """Return the :class:`_expression.Select` object emitted by this
818 :class:`_query.Query`.
820 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
822 query.enable_eagerloads(False).with_labels().statement
824 """
825 return self.__clause_element__()
827 def __clause_element__(
828 self,
829 ) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
830 return (
831 self._with_compile_options(
832 _enable_eagerloads=False, _render_for_subquery=True
833 )
834 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
835 .statement
836 )
838 @overload
839 def only_return_tuples(
840 self: Query[_O], value: Literal[True]
841 ) -> RowReturningQuery[_O]: ...
843 @overload
844 def only_return_tuples(
845 self: Query[_O], value: Literal[False]
846 ) -> Query[_O]: ...
848 @_generative
849 def only_return_tuples(self, value: bool) -> Query[Any]:
850 """When set to True, the query results will always be a
851 :class:`.Row` object.
853 This can change a query that normally returns a single entity
854 as a scalar to return a :class:`.Row` result in all cases.
856 .. seealso::
858 :meth:`.Query.tuples` - returns tuples, but also at the typing
859 level will type results as ``Tuple``.
861 :meth:`_query.Query.is_single_entity`
863 :meth:`_engine.Result.tuples` - v2 comparable method.
865 """
866 self.load_options += dict(_only_return_tuples=value)
867 return self
869 @property
870 def is_single_entity(self) -> bool:
871 """Indicates if this :class:`_query.Query`
872 returns tuples or single entities.
874 Returns True if this query returns a single entity for each instance
875 in its result list, and False if this query returns a tuple of entities
876 for each result.
878 .. seealso::
880 :meth:`_query.Query.only_return_tuples`
882 """
883 return (
884 not self.load_options._only_return_tuples
885 and len(self._raw_columns) == 1
886 and "parententity" in self._raw_columns[0]._annotations
887 and isinstance(
888 self._raw_columns[0]._annotations["parententity"],
889 ORMColumnsClauseRole,
890 )
891 )
893 @_generative
894 def enable_eagerloads(self, value: bool) -> Self:
895 """Control whether or not eager joins and subqueries are
896 rendered.
898 When set to False, the returned Query will not render
899 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
900 :func:`~sqlalchemy.orm.subqueryload` options
901 or mapper-level ``lazy='joined'``/``lazy='subquery'``
902 configurations.
904 This is used primarily when nesting the Query's
905 statement into a subquery or other
906 selectable, or when using :meth:`_query.Query.yield_per`.
908 """
909 self._compile_options += {"_enable_eagerloads": value}
910 return self
912 @_generative
913 def _with_compile_options(self, **opt: Any) -> Self:
914 self._compile_options += opt
915 return self
917 @util.became_legacy_20(
918 ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`",
919 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
920 "instead.",
921 )
922 def with_labels(self) -> Self:
923 return self.set_label_style(
924 SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL
925 )
927 apply_labels = with_labels
929 @property
930 def get_label_style(self) -> SelectLabelStyle:
931 """
932 Retrieve the current label style.
934 .. versionadded:: 1.4
936 .. seealso::
938 :meth:`_sql.Select.get_label_style` - v2 equivalent method.
940 """
941 return self._label_style
943 def set_label_style(self, style: SelectLabelStyle) -> Self:
944 """Apply column labels to the return value of Query.statement.
946 Indicates that this Query's `statement` accessor should return
947 a SELECT statement that applies labels to all columns in the
948 form <tablename>_<columnname>; this is commonly used to
949 disambiguate columns from multiple tables which have the same
950 name.
952 When the `Query` actually issues SQL to load rows, it always
953 uses column labeling.
955 .. note:: The :meth:`_query.Query.set_label_style` method *only* applies
956 the output of :attr:`_query.Query.statement`, and *not* to any of
957 the result-row invoking systems of :class:`_query.Query` itself,
958 e.g.
959 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
960 To execute
961 a query using :meth:`_query.Query.set_label_style`, invoke the
962 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
964 result = session.execute(
965 query.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).statement
966 )
968 .. versionadded:: 1.4
971 .. seealso::
973 :meth:`_sql.Select.set_label_style` - v2 equivalent method.
975 """ # noqa
976 if self._label_style is not style:
977 self = self._generate()
978 self._label_style = style
979 return self
981 @_generative
982 def enable_assertions(self, value: bool) -> Self:
983 """Control whether assertions are generated.
985 When set to False, the returned Query will
986 not assert its state before certain operations,
987 including that LIMIT/OFFSET has not been applied
988 when filter() is called, no criterion exists
989 when get() is called, and no "from_statement()"
990 exists when filter()/order_by()/group_by() etc.
991 is called. This more permissive mode is used by
992 custom Query subclasses to specify criterion or
993 other modifiers outside of the usual usage patterns.
995 Care should be taken to ensure that the usage
996 pattern is even possible. A statement applied
997 by from_statement() will override any criterion
998 set by filter() or order_by(), for example.
1000 """
1001 self._enable_assertions = value
1002 return self
1004 @property
1005 def whereclause(self) -> Optional[ColumnElement[bool]]:
1006 """A readonly attribute which returns the current WHERE criterion for
1007 this Query.
1009 This returned value is a SQL expression construct, or ``None`` if no
1010 criterion has been established.
1012 .. seealso::
1014 :attr:`_sql.Select.whereclause` - v2 equivalent property.
1016 """
1017 return BooleanClauseList._construct_for_whereclause(
1018 self._where_criteria
1019 )
1021 @_generative
1022 def _with_current_path(self, path: PathRegistry) -> Self:
1023 """indicate that this query applies to objects loaded
1024 within a certain path.
1026 Used by deferred loaders (see strategies.py) which transfer
1027 query options from an originating query to a newly generated
1028 query intended for the deferred load.
1030 """
1031 self._compile_options += {"_current_path": path}
1032 return self
1034 @_generative
1035 def yield_per(self, count: int) -> Self:
1036 r"""Yield only ``count`` rows at a time.
1038 The purpose of this method is when fetching very large result sets
1039 (> 10K rows), to batch results in sub-collections and yield them
1040 out partially, so that the Python interpreter doesn't need to declare
1041 very large areas of memory which is both time consuming and leads
1042 to excessive memory use. The performance from fetching hundreds of
1043 thousands of rows can often double when a suitable yield-per setting
1044 (e.g. approximately 1000) is used, even with DBAPIs that buffer
1045 rows (which are most).
1047 As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
1048 equivalent to using the ``yield_per`` execution option at the ORM
1049 level. See the section :ref:`orm_queryguide_yield_per` for further
1050 background on this option.
1052 .. seealso::
1054 :ref:`orm_queryguide_yield_per`
1056 """
1057 self.load_options += {"_yield_per": count}
1058 return self
1060 @util.became_legacy_20(
1061 ":meth:`_orm.Query.get`",
1062 alternative="The method is now available as :meth:`_orm.Session.get`",
1063 )
1064 def get(self, ident: _PKIdentityArgument) -> Optional[_T]:
1065 """Return an instance based on the given primary key identifier,
1066 or ``None`` if not found.
1068 E.g.::
1070 my_user = session.query(User).get(5)
1072 some_object = session.query(VersionedFoo).get((5, 10))
1074 some_object = session.query(VersionedFoo).get({"id": 5, "version_id": 10})
1076 :meth:`_query.Query.get` is special in that it provides direct
1077 access to the identity map of the owning :class:`.Session`.
1078 If the given primary key identifier is present
1079 in the local identity map, the object is returned
1080 directly from this collection and no SQL is emitted,
1081 unless the object has been marked fully expired.
1082 If not present,
1083 a SELECT is performed in order to locate the object.
1085 :meth:`_query.Query.get` also will perform a check if
1086 the object is present in the identity map and
1087 marked as expired - a SELECT
1088 is emitted to refresh the object as well as to
1089 ensure that the row is still present.
1090 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
1092 :meth:`_query.Query.get` is only used to return a single
1093 mapped instance, not multiple instances or
1094 individual column constructs, and strictly
1095 on a single primary key value. The originating
1096 :class:`_query.Query` must be constructed in this way,
1097 i.e. against a single mapped entity,
1098 with no additional filtering criterion. Loading
1099 options via :meth:`_query.Query.options` may be applied
1100 however, and will be used if the object is not
1101 yet locally present.
1103 :param ident: A scalar, tuple, or dictionary representing the
1104 primary key. For a composite (e.g. multiple column) primary key,
1105 a tuple or dictionary should be passed.
1107 For a single-column primary key, the scalar calling form is typically
1108 the most expedient. If the primary key of a row is the value "5",
1109 the call looks like::
1111 my_object = query.get(5)
1113 The tuple form contains primary key values typically in
1114 the order in which they correspond to the mapped
1115 :class:`_schema.Table`
1116 object's primary key columns, or if the
1117 :paramref:`_orm.Mapper.primary_key` configuration parameter were
1118 used, in
1119 the order used for that parameter. For example, if the primary key
1120 of a row is represented by the integer
1121 digits "5, 10" the call would look like::
1123 my_object = query.get((5, 10))
1125 The dictionary form should include as keys the mapped attribute names
1126 corresponding to each element of the primary key. If the mapped class
1127 has the attributes ``id``, ``version_id`` as the attributes which
1128 store the object's primary key value, the call would look like::
1130 my_object = query.get({"id": 5, "version_id": 10})
1132 :return: The object instance, or ``None``.
1134 """ # noqa: E501
1135 self._no_criterion_assertion("get", order_by=False, distinct=False)
1137 # we still implement _get_impl() so that baked query can override
1138 # it
1139 return self._get_impl(ident, loading._load_on_pk_identity)
1141 def _get_impl(
1142 self,
1143 primary_key_identity: _PKIdentityArgument,
1144 db_load_fn: Callable[..., Any],
1145 identity_token: Optional[Any] = None,
1146 ) -> Optional[Any]:
1147 mapper = self._only_full_mapper_zero("get")
1148 return self.session._get_impl(
1149 mapper,
1150 primary_key_identity,
1151 db_load_fn,
1152 populate_existing=self.load_options._populate_existing,
1153 with_for_update=self._for_update_arg,
1154 options=self._with_options,
1155 identity_token=identity_token,
1156 execution_options=self._execution_options,
1157 )
1159 @property
1160 def lazy_loaded_from(self) -> Optional[InstanceState[Any]]:
1161 """An :class:`.InstanceState` that is using this :class:`_query.Query`
1162 for a lazy load operation.
1164 .. deprecated:: 1.4 This attribute should be viewed via the
1165 :attr:`.ORMExecuteState.lazy_loaded_from` attribute, within
1166 the context of the :meth:`.SessionEvents.do_orm_execute`
1167 event.
1169 .. seealso::
1171 :attr:`.ORMExecuteState.lazy_loaded_from`
1173 """
1174 return self.load_options._lazy_loaded_from # type: ignore
1176 @property
1177 def _current_path(self) -> PathRegistry:
1178 return self._compile_options._current_path # type: ignore
1180 @_generative
1181 def correlate(
1182 self,
1183 *fromclauses: Union[Literal[None, False], _FromClauseArgument],
1184 ) -> Self:
1185 """Return a :class:`.Query` construct which will correlate the given
1186 FROM clauses to that of an enclosing :class:`.Query` or
1187 :func:`~.expression.select`.
1189 The method here accepts mapped classes, :func:`.aliased` constructs,
1190 and :class:`_orm.Mapper` constructs as arguments, which are resolved
1191 into expression constructs, in addition to appropriate expression
1192 constructs.
1194 The correlation arguments are ultimately passed to
1195 :meth:`_expression.Select.correlate`
1196 after coercion to expression constructs.
1198 The correlation arguments take effect in such cases
1199 as when :meth:`_query.Query.from_self` is used, or when
1200 a subquery as returned by :meth:`_query.Query.subquery` is
1201 embedded in another :func:`_expression.select` construct.
1203 .. seealso::
1205 :meth:`_sql.Select.correlate` - v2 equivalent method.
1207 """
1209 self._auto_correlate = False
1210 if fromclauses and fromclauses[0] in {None, False}:
1211 self._correlate = ()
1212 else:
1213 self._correlate = self._correlate + tuple(
1214 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
1215 )
1216 return self
1218 @_generative
1219 def autoflush(self, setting: bool) -> Self:
1220 """Return a Query with a specific 'autoflush' setting.
1222 As of SQLAlchemy 1.4, the :meth:`_orm.Query.autoflush` method
1223 is equivalent to using the ``autoflush`` execution option at the
1224 ORM level. See the section :ref:`orm_queryguide_autoflush` for
1225 further background on this option.
1227 """
1228 self.load_options += {"_autoflush": setting}
1229 return self
1231 @_generative
1232 def populate_existing(self) -> Self:
1233 """Return a :class:`_query.Query`
1234 that will expire and refresh all instances
1235 as they are loaded, or reused from the current :class:`.Session`.
1237 As of SQLAlchemy 1.4, the :meth:`_orm.Query.populate_existing` method
1238 is equivalent to using the ``populate_existing`` execution option at
1239 the ORM level. See the section :ref:`orm_queryguide_populate_existing`
1240 for further background on this option.
1242 """
1243 self.load_options += {"_populate_existing": True}
1244 return self
1246 @_generative
1247 def _with_invoke_all_eagers(self, value: bool) -> Self:
1248 """Set the 'invoke all eagers' flag which causes joined- and
1249 subquery loaders to traverse into already-loaded related objects
1250 and collections.
1252 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1254 """
1255 self.load_options += {"_invoke_all_eagers": value}
1256 return self
1258 @util.became_legacy_20(
1259 ":meth:`_orm.Query.with_parent`",
1260 alternative="Use the :func:`_orm.with_parent` standalone construct.",
1261 )
1262 @util.preload_module("sqlalchemy.orm.relationships")
1263 def with_parent(
1264 self,
1265 instance: object,
1266 property: Optional[ # noqa: A002
1267 attributes.QueryableAttribute[Any]
1268 ] = None,
1269 from_entity: Optional[_ExternalEntityType[Any]] = None,
1270 ) -> Self:
1271 """Add filtering criterion that relates the given instance
1272 to a child object or collection, using its attribute state
1273 as well as an established :func:`_orm.relationship()`
1274 configuration.
1276 The method uses the :func:`.with_parent` function to generate
1277 the clause, the result of which is passed to
1278 :meth:`_query.Query.filter`.
1280 Parameters are the same as :func:`.with_parent`, with the exception
1281 that the given property can be None, in which case a search is
1282 performed against this :class:`_query.Query` object's target mapper.
1284 :param instance:
1285 An instance which has some :func:`_orm.relationship`.
1287 :param property:
1288 Class bound attribute which indicates
1289 what relationship from the instance should be used to reconcile the
1290 parent/child relationship.
1292 :param from_entity:
1293 Entity in which to consider as the left side. This defaults to the
1294 "zero" entity of the :class:`_query.Query` itself.
1296 """
1297 relationships = util.preloaded.orm_relationships
1299 if from_entity:
1300 entity_zero = inspect(from_entity)
1301 else:
1302 entity_zero = _legacy_filter_by_entity_zero(self)
1303 if property is None:
1304 # TODO: deprecate, property has to be supplied
1305 mapper = object_mapper(instance)
1307 for prop in mapper.iterate_properties:
1308 if (
1309 isinstance(prop, relationships.RelationshipProperty)
1310 and prop.mapper is entity_zero.mapper # type: ignore
1311 ):
1312 property = prop # type: ignore # noqa: A001
1313 break
1314 else:
1315 raise sa_exc.InvalidRequestError(
1316 "Could not locate a property which relates instances "
1317 "of class '%s' to instances of class '%s'"
1318 % (
1319 entity_zero.mapper.class_.__name__, # type: ignore
1320 instance.__class__.__name__,
1321 )
1322 )
1324 return self.filter(
1325 with_parent(
1326 instance,
1327 property, # type: ignore
1328 entity_zero.entity, # type: ignore
1329 )
1330 )
1332 @_generative
1333 def add_entity(
1334 self,
1335 entity: _EntityType[Any],
1336 alias: Optional[Union[Alias, Subquery]] = None,
1337 ) -> Query[Any]:
1338 """add a mapped entity to the list of result columns
1339 to be returned.
1341 .. seealso::
1343 :meth:`_sql.Select.add_columns` - v2 comparable method.
1344 """
1346 if alias is not None:
1347 # TODO: deprecate
1348 entity = AliasedClass(entity, alias)
1350 self._raw_columns = list(self._raw_columns)
1352 self._raw_columns.append(
1353 coercions.expect(
1354 roles.ColumnsClauseRole, entity, apply_propagate_attrs=self
1355 )
1356 )
1357 return self
1359 @_generative
1360 def with_session(self, session: Session) -> Self:
1361 """Return a :class:`_query.Query` that will use the given
1362 :class:`.Session`.
1364 While the :class:`_query.Query`
1365 object is normally instantiated using the
1366 :meth:`.Session.query` method, it is legal to build the
1367 :class:`_query.Query`
1368 directly without necessarily using a :class:`.Session`. Such a
1369 :class:`_query.Query` object, or any :class:`_query.Query`
1370 already associated
1371 with a different :class:`.Session`, can produce a new
1372 :class:`_query.Query`
1373 object associated with a target session using this method::
1375 from sqlalchemy.orm import Query
1377 query = Query([MyClass]).filter(MyClass.id == 5)
1379 result = query.with_session(my_session).one()
1381 """
1383 self.session = session
1384 return self
1386 def _legacy_from_self(
1387 self, *entities: _ColumnsClauseArgument[Any]
1388 ) -> Self:
1389 # used for query.count() as well as for the same
1390 # function in BakedQuery, as well as some old tests in test_baked.py.
1392 fromclause = (
1393 self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
1394 .correlate(None)
1395 .subquery()
1396 ._anonymous_fromclause()
1397 )
1399 q = self._from_selectable(fromclause)
1401 if entities:
1402 q._set_entities(entities)
1403 return q
1405 @_generative
1406 def _set_enable_single_crit(self, val: bool) -> Self:
1407 self._compile_options += {"_enable_single_crit": val}
1408 return self
1410 @_generative
1411 def _from_selectable(
1412 self, fromclause: FromClause, set_entity_from: bool = True
1413 ) -> Self:
1414 for attr in (
1415 "_where_criteria",
1416 "_order_by_clauses",
1417 "_group_by_clauses",
1418 "_limit_clause",
1419 "_offset_clause",
1420 "_last_joined_entity",
1421 "_setup_joins",
1422 "_memoized_select_entities",
1423 "_distinct",
1424 "_distinct_on",
1425 "_having_criteria",
1426 "_prefixes",
1427 "_suffixes",
1428 "_syntax_extensions",
1429 ):
1430 self.__dict__.pop(attr, None)
1431 self._set_select_from([fromclause], set_entity_from)
1432 self._compile_options += {
1433 "_enable_single_crit": False,
1434 }
1436 return self
1438 @util.deprecated(
1439 "1.4",
1440 ":meth:`_query.Query.values` "
1441 "is deprecated and will be removed in a "
1442 "future release. Please use :meth:`_query.Query.with_entities`",
1443 )
1444 def values(self, *columns: _ColumnsClauseArgument[Any]) -> Iterable[Any]:
1445 """Return an iterator yielding result tuples corresponding
1446 to the given list of columns
1448 """
1449 return self._values_no_warn(*columns)
1451 _values = values
1453 def _values_no_warn(
1454 self, *columns: _ColumnsClauseArgument[Any]
1455 ) -> Iterable[Any]:
1456 if not columns:
1457 return iter(())
1458 q = self._clone().enable_eagerloads(False)
1459 q._set_entities(columns)
1460 if not q.load_options._yield_per:
1461 q.load_options += {"_yield_per": 10}
1462 return iter(q)
1464 @util.deprecated(
1465 "1.4",
1466 ":meth:`_query.Query.value` "
1467 "is deprecated and will be removed in a "
1468 "future release. Please use :meth:`_query.Query.with_entities` "
1469 "in combination with :meth:`_query.Query.scalar`",
1470 )
1471 def value(self, column: _ColumnExpressionArgument[Any]) -> Any:
1472 """Return a scalar result corresponding to the given
1473 column expression.
1475 """
1476 try:
1477 return next(self._values_no_warn(column))[0] # type: ignore
1478 except StopIteration:
1479 return None
1481 @overload
1482 def with_entities(self, _entity: _EntityType[_O]) -> Query[_O]: ...
1484 @overload
1485 def with_entities(
1486 self,
1487 _colexpr: roles.TypedColumnsClauseRole[_T],
1488 ) -> RowReturningQuery[Tuple[_T]]: ...
1490 # START OVERLOADED FUNCTIONS self.with_entities RowReturningQuery 2-8
1492 # code within this block is **programmatically,
1493 # statically generated** by tools/generate_tuple_map_overloads.py
1495 @overload
1496 def with_entities(
1497 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1498 ) -> RowReturningQuery[_T0, _T1]: ...
1500 @overload
1501 def with_entities(
1502 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1503 ) -> RowReturningQuery[_T0, _T1, _T2]: ...
1505 @overload
1506 def with_entities(
1507 self,
1508 __ent0: _TCCA[_T0],
1509 __ent1: _TCCA[_T1],
1510 __ent2: _TCCA[_T2],
1511 __ent3: _TCCA[_T3],
1512 /,
1513 ) -> RowReturningQuery[_T0, _T1, _T2, _T3]: ...
1515 @overload
1516 def with_entities(
1517 self,
1518 __ent0: _TCCA[_T0],
1519 __ent1: _TCCA[_T1],
1520 __ent2: _TCCA[_T2],
1521 __ent3: _TCCA[_T3],
1522 __ent4: _TCCA[_T4],
1523 /,
1524 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4]: ...
1526 @overload
1527 def with_entities(
1528 self,
1529 __ent0: _TCCA[_T0],
1530 __ent1: _TCCA[_T1],
1531 __ent2: _TCCA[_T2],
1532 __ent3: _TCCA[_T3],
1533 __ent4: _TCCA[_T4],
1534 __ent5: _TCCA[_T5],
1535 /,
1536 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1538 @overload
1539 def with_entities(
1540 self,
1541 __ent0: _TCCA[_T0],
1542 __ent1: _TCCA[_T1],
1543 __ent2: _TCCA[_T2],
1544 __ent3: _TCCA[_T3],
1545 __ent4: _TCCA[_T4],
1546 __ent5: _TCCA[_T5],
1547 __ent6: _TCCA[_T6],
1548 /,
1549 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1551 @overload
1552 def with_entities(
1553 self,
1554 __ent0: _TCCA[_T0],
1555 __ent1: _TCCA[_T1],
1556 __ent2: _TCCA[_T2],
1557 __ent3: _TCCA[_T3],
1558 __ent4: _TCCA[_T4],
1559 __ent5: _TCCA[_T5],
1560 __ent6: _TCCA[_T6],
1561 __ent7: _TCCA[_T7],
1562 /,
1563 *entities: _ColumnsClauseArgument[Any],
1564 ) -> RowReturningQuery[
1565 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1566 ]: ...
1568 # END OVERLOADED FUNCTIONS self.with_entities
1570 @overload
1571 def with_entities(
1572 self, *entities: _ColumnsClauseArgument[Any]
1573 ) -> Query[Any]: ...
1575 @_generative
1576 def with_entities(
1577 self, *entities: _ColumnsClauseArgument[Any], **__kw: Any
1578 ) -> Query[Any]:
1579 r"""Return a new :class:`_query.Query`
1580 replacing the SELECT list with the
1581 given entities.
1583 e.g.::
1585 # Users, filtered on some arbitrary criterion
1586 # and then ordered by related email address
1587 q = (
1588 session.query(User)
1589 .join(User.address)
1590 .filter(User.name.like("%ed%"))
1591 .order_by(Address.email)
1592 )
1594 # given *only* User.id==5, Address.email, and 'q', what
1595 # would the *next* User in the result be ?
1596 subq = (
1597 q.with_entities(Address.email)
1598 .order_by(None)
1599 .filter(User.id == 5)
1600 .subquery()
1601 )
1602 q = q.join((subq, subq.c.email < Address.email)).limit(1)
1604 .. seealso::
1606 :meth:`_sql.Select.with_only_columns` - v2 comparable method.
1607 """
1608 if __kw:
1609 raise _no_kw()
1611 # Query has all the same fields as Select for this operation
1612 # this could in theory be based on a protocol but not sure if it's
1613 # worth it
1614 _MemoizedSelectEntities._generate_for_statement(self) # type: ignore
1615 self._set_entities(entities)
1616 return self
1618 @_generative
1619 def add_columns(
1620 self, *column: _ColumnExpressionArgument[Any]
1621 ) -> Query[Any]:
1622 """Add one or more column expressions to the list
1623 of result columns to be returned.
1625 .. seealso::
1627 :meth:`_sql.Select.add_columns` - v2 comparable method.
1628 """
1630 self._raw_columns = list(self._raw_columns)
1632 self._raw_columns.extend(
1633 coercions.expect(
1634 roles.ColumnsClauseRole,
1635 c,
1636 apply_propagate_attrs=self,
1637 post_inspect=True,
1638 )
1639 for c in column
1640 )
1641 return self
1643 @util.deprecated(
1644 "1.4",
1645 ":meth:`_query.Query.add_column` "
1646 "is deprecated and will be removed in a "
1647 "future release. Please use :meth:`_query.Query.add_columns`",
1648 )
1649 def add_column(self, column: _ColumnExpressionArgument[Any]) -> Query[Any]:
1650 """Add a column expression to the list of result columns to be
1651 returned.
1653 """
1654 return self.add_columns(column)
1656 @_generative
1657 def options(self, *args: ExecutableOption) -> Self:
1658 """Return a new :class:`_query.Query` object,
1659 applying the given list of
1660 mapper options.
1662 Most supplied options regard changing how column- and
1663 relationship-mapped attributes are loaded.
1665 .. seealso::
1667 :ref:`loading_columns`
1669 :ref:`relationship_loader_options`
1671 """
1673 opts = tuple(util.flatten_iterator(args))
1674 if self._compile_options._current_path:
1675 # opting for lower method overhead for the checks
1676 for opt in opts:
1677 if not opt._is_core and opt._is_legacy_option: # type: ignore
1678 opt.process_query_conditionally(self) # type: ignore
1679 else:
1680 for opt in opts:
1681 if not opt._is_core and opt._is_legacy_option: # type: ignore
1682 opt.process_query(self) # type: ignore
1684 self._with_options += opts
1685 return self
1687 def with_transformation(
1688 self, fn: Callable[[Query[Any]], Query[Any]]
1689 ) -> Query[Any]:
1690 """Return a new :class:`_query.Query` object transformed by
1691 the given function.
1693 E.g.::
1695 def filter_something(criterion):
1696 def transform(q):
1697 return q.filter(criterion)
1699 return transform
1702 q = q.with_transformation(filter_something(x == 5))
1704 This allows ad-hoc recipes to be created for :class:`_query.Query`
1705 objects.
1707 """
1708 return fn(self)
1710 def get_execution_options(self) -> _ImmutableExecuteOptions:
1711 """Get the non-SQL options which will take effect during execution.
1713 .. seealso::
1715 :meth:`_query.Query.execution_options`
1717 :meth:`_sql.Select.get_execution_options` - v2 comparable method.
1719 """
1720 return self._execution_options
1722 @overload
1723 def execution_options(
1724 self,
1725 *,
1726 compiled_cache: Optional[CompiledCacheType] = ...,
1727 logging_token: str = ...,
1728 isolation_level: IsolationLevel = ...,
1729 no_parameters: bool = False,
1730 stream_results: bool = False,
1731 max_row_buffer: int = ...,
1732 yield_per: int = ...,
1733 driver_column_names: bool = ...,
1734 insertmanyvalues_page_size: int = ...,
1735 schema_translate_map: Optional[SchemaTranslateMapType] = ...,
1736 populate_existing: bool = False,
1737 autoflush: bool = False,
1738 preserve_rowcount: bool = False,
1739 **opt: Any,
1740 ) -> Self: ...
1742 @overload
1743 def execution_options(self, **opt: Any) -> Self: ...
1745 @_generative
1746 def execution_options(self, **kwargs: Any) -> Self:
1747 """Set non-SQL options which take effect during execution.
1749 Options allowed here include all of those accepted by
1750 :meth:`_engine.Connection.execution_options`, as well as a series
1751 of ORM specific options:
1753 ``populate_existing=True`` - equivalent to using
1754 :meth:`_orm.Query.populate_existing`
1756 ``autoflush=True|False`` - equivalent to using
1757 :meth:`_orm.Query.autoflush`
1759 ``yield_per=<value>`` - equivalent to using
1760 :meth:`_orm.Query.yield_per`
1762 Note that the ``stream_results`` execution option is enabled
1763 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1764 method or execution option is used.
1766 .. versionadded:: 1.4 - added ORM options to
1767 :meth:`_orm.Query.execution_options`
1769 The execution options may also be specified on a per execution basis
1770 when using :term:`2.0 style` queries via the
1771 :paramref:`_orm.Session.execution_options` parameter.
1773 .. warning:: The
1774 :paramref:`_engine.Connection.execution_options.stream_results`
1775 parameter should not be used at the level of individual ORM
1776 statement executions, as the :class:`_orm.Session` will not track
1777 objects from different schema translate maps within a single
1778 session. For multiple schema translate maps within the scope of a
1779 single :class:`_orm.Session`, see :ref:`examples_sharding`.
1782 .. seealso::
1784 :ref:`engine_stream_results`
1786 :meth:`_query.Query.get_execution_options`
1788 :meth:`_sql.Select.execution_options` - v2 equivalent method.
1790 """
1791 self._execution_options = self._execution_options.union(kwargs)
1792 return self
1794 @_generative
1795 def with_for_update(
1796 self,
1797 *,
1798 nowait: bool = False,
1799 read: bool = False,
1800 of: Optional[_ForUpdateOfArgument] = None,
1801 skip_locked: bool = False,
1802 key_share: bool = False,
1803 ) -> Self:
1804 """return a new :class:`_query.Query`
1805 with the specified options for the
1806 ``FOR UPDATE`` clause.
1808 The behavior of this method is identical to that of
1809 :meth:`_expression.GenerativeSelect.with_for_update`.
1810 When called with no arguments,
1811 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1812 appended. When additional arguments are specified, backend-specific
1813 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1814 can take effect.
1816 E.g.::
1818 q = (
1819 sess.query(User)
1820 .populate_existing()
1821 .with_for_update(nowait=True, of=User)
1822 )
1824 The above query on a PostgreSQL backend will render like:
1826 .. sourcecode:: sql
1828 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1830 .. warning::
1832 Using ``with_for_update`` in the context of eager loading
1833 relationships is not officially supported or recommended by
1834 SQLAlchemy and may not work with certain queries on various
1835 database backends. When ``with_for_update`` is successfully used
1836 with a query that involves :func:`_orm.joinedload`, SQLAlchemy will
1837 attempt to emit SQL that locks all involved tables.
1839 .. note:: It is generally a good idea to combine the use of the
1840 :meth:`_orm.Query.populate_existing` method when using the
1841 :meth:`_orm.Query.with_for_update` method. The purpose of
1842 :meth:`_orm.Query.populate_existing` is to force all the data read
1843 from the SELECT to be populated into the ORM objects returned,
1844 even if these objects are already in the :term:`identity map`.
1846 .. seealso::
1848 :meth:`_expression.GenerativeSelect.with_for_update`
1849 - Core level method with
1850 full argument and behavioral description.
1852 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1853 objects already loaded in the identity map.
1855 """ # noqa: E501
1857 self._for_update_arg = ForUpdateArg(
1858 read=read,
1859 nowait=nowait,
1860 of=of,
1861 skip_locked=skip_locked,
1862 key_share=key_share,
1863 )
1864 return self
1866 @_generative
1867 def params(
1868 self, __params: Optional[Dict[str, Any]] = None, /, **kw: Any
1869 ) -> Self:
1870 r"""Add values for bind parameters which may have been
1871 specified in filter().
1873 Parameters may be specified using \**kwargs, or optionally a single
1874 dictionary as the first positional argument. The reason for both is
1875 that \**kwargs is convenient, however some parameter dictionaries
1876 contain unicode keys in which case \**kwargs cannot be used.
1878 """
1879 if __params:
1880 kw.update(__params)
1881 self._params = self._params.union(kw)
1882 return self
1884 def where(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1885 """A synonym for :meth:`.Query.filter`.
1887 .. versionadded:: 1.4
1889 .. seealso::
1891 :meth:`_sql.Select.where` - v2 equivalent method.
1893 """
1894 return self.filter(*criterion)
1896 @_generative
1897 @_assertions(_no_statement_condition, _no_limit_offset)
1898 def filter(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1899 r"""Apply the given filtering criterion to a copy
1900 of this :class:`_query.Query`, using SQL expressions.
1902 e.g.::
1904 session.query(MyClass).filter(MyClass.name == "some name")
1906 Multiple criteria may be specified as comma separated; the effect
1907 is that they will be joined together using the :func:`.and_`
1908 function::
1910 session.query(MyClass).filter(MyClass.name == "some name", MyClass.id > 5)
1912 The criterion is any SQL expression object applicable to the
1913 WHERE clause of a select. String expressions are coerced
1914 into SQL expression constructs via the :func:`_expression.text`
1915 construct.
1917 .. seealso::
1919 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1921 :meth:`_sql.Select.where` - v2 equivalent method.
1923 """ # noqa: E501
1924 for crit in list(criterion):
1925 crit = coercions.expect(
1926 roles.WhereHavingRole, crit, apply_propagate_attrs=self
1927 )
1929 self._where_criteria += (crit,)
1930 return self
1932 @util.memoized_property
1933 def _last_joined_entity(
1934 self,
1935 ) -> Optional[Union[_InternalEntityType[Any], _JoinTargetElement]]:
1936 if self._setup_joins:
1937 return _determine_last_joined_entity(
1938 self._setup_joins,
1939 )
1940 else:
1941 return None
1943 def _filter_by_zero(self) -> Any:
1944 """for the filter_by() method, return the target entity for which
1945 we will attempt to derive an expression from based on string name.
1947 """
1949 if self._setup_joins:
1950 _last_joined_entity = self._last_joined_entity
1951 if _last_joined_entity is not None:
1952 return _last_joined_entity
1954 # discussion related to #7239
1955 # special check determines if we should try to derive attributes
1956 # for filter_by() from the "from object", i.e., if the user
1957 # called query.select_from(some selectable).filter_by(some_attr=value).
1958 # We don't want to do that in the case that methods like
1959 # from_self(), select_entity_from(), or a set op like union() were
1960 # called; while these methods also place a
1961 # selectable in the _from_obj collection, they also set up
1962 # the _set_base_alias boolean which turns on the whole "adapt the
1963 # entity to this selectable" thing, meaning the query still continues
1964 # to construct itself in terms of the lead entity that was passed
1965 # to query(), e.g. query(User).from_self() is still in terms of User,
1966 # and not the subquery that from_self() created. This feature of
1967 # "implicitly adapt all occurrences of entity X to some arbitrary
1968 # subquery" is the main thing I am trying to do away with in 2.0 as
1969 # users should now used aliased() for that, but I can't entirely get
1970 # rid of it due to query.union() and other set ops relying upon it.
1971 #
1972 # compare this to the base Select()._filter_by_zero() which can
1973 # just return self._from_obj[0] if present, because there is no
1974 # "_set_base_alias" feature.
1975 #
1976 # IOW, this conditional essentially detects if
1977 # "select_from(some_selectable)" has been called, as opposed to
1978 # "select_entity_from()", "from_self()"
1979 # or "union() / some_set_op()".
1980 if self._from_obj and not self._compile_options._set_base_alias:
1981 return self._from_obj[0]
1983 return self._raw_columns[0]
1985 def filter_by(self, **kwargs: Any) -> Self:
1986 r"""Apply the given filtering criterion to a copy
1987 of this :class:`_query.Query`, using keyword expressions.
1989 e.g.::
1991 session.query(MyClass).filter_by(name="some name")
1993 Multiple criteria may be specified as comma separated; the effect
1994 is that they will be joined together using the :func:`.and_`
1995 function::
1997 session.query(MyClass).filter_by(name="some name", id=5)
1999 The keyword expressions are extracted from the primary
2000 entity of the query, or the last entity that was the
2001 target of a call to :meth:`_query.Query.join`.
2003 .. note::
2005 :class:`_query.Query` is a legacy construct as of SQLAlchemy 2.0.
2006 See :meth:`_sql.Select.filter_by` for the comparable method on
2007 2.0-style :func:`_sql.select` constructs, where the behavior has
2008 been enhanced in version 2.1 to search across all FROM clause
2009 entities. See :ref:`change_8601` for background.
2011 .. seealso::
2013 :meth:`_query.Query.filter` - filter on SQL expressions.
2015 :meth:`_sql.Select.filter_by` - v2 comparable method.
2017 """
2018 from_entity = self._filter_by_zero()
2020 clauses = [
2021 _entity_namespace_key(from_entity, key) == value
2022 for key, value in kwargs.items()
2023 ]
2024 return self.filter(*clauses)
2026 @_generative
2027 def order_by(
2028 self,
2029 __first: Union[
2030 Literal[None, False, _NoArg.NO_ARG],
2031 _ColumnExpressionOrStrLabelArgument[Any],
2032 ] = _NoArg.NO_ARG,
2033 /,
2034 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2035 ) -> Self:
2036 """Apply one or more ORDER BY criteria to the query and return
2037 the newly resulting :class:`_query.Query`.
2039 e.g.::
2041 q = session.query(Entity).order_by(Entity.id, Entity.name)
2043 Calling this method multiple times is equivalent to calling it once
2044 with all the clauses concatenated. All existing ORDER BY criteria may
2045 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
2046 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
2048 # will erase all ORDER BY and ORDER BY new_col alone
2049 q = q.order_by(None).order_by(new_col)
2051 .. seealso::
2053 These sections describe ORDER BY in terms of :term:`2.0 style`
2054 invocation but apply to :class:`_orm.Query` as well:
2056 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
2058 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2060 :meth:`_sql.Select.order_by` - v2 equivalent method.
2062 """
2064 for assertion in (self._no_statement_condition, self._no_limit_offset):
2065 assertion("order_by")
2067 if not clauses and (__first is None or __first is False):
2068 self._order_by_clauses = ()
2069 elif __first is not _NoArg.NO_ARG:
2070 criterion = tuple(
2071 coercions.expect(roles.OrderByRole, clause)
2072 for clause in (__first,) + clauses
2073 )
2074 self._order_by_clauses += criterion
2076 return self
2078 @_generative
2079 def group_by(
2080 self,
2081 __first: Union[
2082 Literal[None, False, _NoArg.NO_ARG],
2083 _ColumnExpressionOrStrLabelArgument[Any],
2084 ] = _NoArg.NO_ARG,
2085 /,
2086 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2087 ) -> Self:
2088 """Apply one or more GROUP BY criterion to the query and return
2089 the newly resulting :class:`_query.Query`.
2091 All existing GROUP BY settings can be suppressed by
2092 passing ``None`` - this will suppress any GROUP BY configured
2093 on mappers as well.
2095 .. seealso::
2097 These sections describe GROUP BY in terms of :term:`2.0 style`
2098 invocation but apply to :class:`_orm.Query` as well:
2100 :ref:`tutorial_group_by_w_aggregates` - in the
2101 :ref:`unified_tutorial`
2103 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2105 :meth:`_sql.Select.group_by` - v2 equivalent method.
2107 """
2109 for assertion in (self._no_statement_condition, self._no_limit_offset):
2110 assertion("group_by")
2112 if not clauses and (__first is None or __first is False):
2113 self._group_by_clauses = ()
2114 elif __first is not _NoArg.NO_ARG:
2115 criterion = tuple(
2116 coercions.expect(roles.GroupByRole, clause)
2117 for clause in (__first,) + clauses
2118 )
2119 self._group_by_clauses += criterion
2120 return self
2122 @_generative
2123 @_assertions(_no_statement_condition, _no_limit_offset)
2124 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:
2125 r"""Apply a HAVING criterion to the query and return the
2126 newly resulting :class:`_query.Query`.
2128 :meth:`_query.Query.having` is used in conjunction with
2129 :meth:`_query.Query.group_by`.
2131 HAVING criterion makes it possible to use filters on aggregate
2132 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
2134 q = (
2135 session.query(User.id)
2136 .join(User.addresses)
2137 .group_by(User.id)
2138 .having(func.count(Address.id) > 2)
2139 )
2141 .. seealso::
2143 :meth:`_sql.Select.having` - v2 equivalent method.
2145 """
2147 for criterion in having:
2148 having_criteria = coercions.expect(
2149 roles.WhereHavingRole, criterion
2150 )
2151 self._having_criteria += (having_criteria,)
2152 return self
2154 def _set_op(self, expr_fn: Any, *q: Query[Any]) -> Self:
2155 list_of_queries = (self,) + q
2156 return self._from_selectable(expr_fn(*(list_of_queries)).subquery())
2158 def union(self, *q: Query[Any]) -> Self:
2159 """Produce a UNION of this Query against one or more queries.
2161 e.g.::
2163 q1 = sess.query(SomeClass).filter(SomeClass.foo == "bar")
2164 q2 = sess.query(SomeClass).filter(SomeClass.bar == "foo")
2166 q3 = q1.union(q2)
2168 The method accepts multiple Query objects so as to control
2169 the level of nesting. A series of ``union()`` calls such as::
2171 x.union(y).union(z).all()
2173 will nest on each ``union()``, and produces:
2175 .. sourcecode:: sql
2177 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
2178 SELECT * FROM y) UNION SELECT * FROM Z)
2180 Whereas::
2182 x.union(y, z).all()
2184 produces:
2186 .. sourcecode:: sql
2188 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
2189 SELECT * FROM Z)
2191 Note that many database backends do not allow ORDER BY to
2192 be rendered on a query called within UNION, EXCEPT, etc.
2193 To disable all ORDER BY clauses including those configured
2194 on mappers, issue ``query.order_by(None)`` - the resulting
2195 :class:`_query.Query` object will not render ORDER BY within
2196 its SELECT statement.
2198 .. seealso::
2200 :meth:`_sql.Select.union` - v2 equivalent method.
2202 """
2203 return self._set_op(expression.union, *q)
2205 def union_all(self, *q: Query[Any]) -> Self:
2206 """Produce a UNION ALL of this Query against one or more queries.
2208 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2209 that method for usage examples.
2211 .. seealso::
2213 :meth:`_sql.Select.union_all` - v2 equivalent method.
2215 """
2216 return self._set_op(expression.union_all, *q)
2218 def intersect(self, *q: Query[Any]) -> Self:
2219 """Produce an INTERSECT of this Query against one or more queries.
2221 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2222 that method for usage examples.
2224 .. seealso::
2226 :meth:`_sql.Select.intersect` - v2 equivalent method.
2228 """
2229 return self._set_op(expression.intersect, *q)
2231 def intersect_all(self, *q: Query[Any]) -> Self:
2232 """Produce an INTERSECT ALL of this Query against one or more queries.
2234 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2235 that method for usage examples.
2237 .. seealso::
2239 :meth:`_sql.Select.intersect_all` - v2 equivalent method.
2241 """
2242 return self._set_op(expression.intersect_all, *q)
2244 def except_(self, *q: Query[Any]) -> Self:
2245 """Produce an EXCEPT of this Query against one or more queries.
2247 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2248 that method for usage examples.
2250 .. seealso::
2252 :meth:`_sql.Select.except_` - v2 equivalent method.
2254 """
2255 return self._set_op(expression.except_, *q)
2257 def except_all(self, *q: Query[Any]) -> Self:
2258 """Produce an EXCEPT ALL of this Query against one or more queries.
2260 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2261 that method for usage examples.
2263 .. seealso::
2265 :meth:`_sql.Select.except_all` - v2 equivalent method.
2267 """
2268 return self._set_op(expression.except_all, *q)
2270 @_generative
2271 @_assertions(_no_statement_condition, _no_limit_offset)
2272 def join(
2273 self,
2274 target: _JoinTargetArgument,
2275 onclause: Optional[_OnClauseArgument] = None,
2276 *,
2277 isouter: bool = False,
2278 full: bool = False,
2279 ) -> Self:
2280 r"""Create a SQL JOIN against this :class:`_query.Query`
2281 object's criterion
2282 and apply generatively, returning the newly resulting
2283 :class:`_query.Query`.
2285 **Simple Relationship Joins**
2287 Consider a mapping between two classes ``User`` and ``Address``,
2288 with a relationship ``User.addresses`` representing a collection
2289 of ``Address`` objects associated with each ``User``. The most
2290 common usage of :meth:`_query.Query.join`
2291 is to create a JOIN along this
2292 relationship, using the ``User.addresses`` attribute as an indicator
2293 for how this should occur::
2295 q = session.query(User).join(User.addresses)
2297 Where above, the call to :meth:`_query.Query.join` along
2298 ``User.addresses`` will result in SQL approximately equivalent to:
2300 .. sourcecode:: sql
2302 SELECT user.id, user.name
2303 FROM user JOIN address ON user.id = address.user_id
2305 In the above example we refer to ``User.addresses`` as passed to
2306 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2307 how the "ON" portion of the JOIN should be constructed.
2309 To construct a chain of joins, multiple :meth:`_query.Query.join`
2310 calls may be used. The relationship-bound attribute implies both
2311 the left and right side of the join at once::
2313 q = (
2314 session.query(User)
2315 .join(User.orders)
2316 .join(Order.items)
2317 .join(Item.keywords)
2318 )
2320 .. note:: as seen in the above example, **the order in which each
2321 call to the join() method occurs is important**. Query would not,
2322 for example, know how to join correctly if we were to specify
2323 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2324 such a case, depending on the arguments passed, it may raise an
2325 error that it doesn't know how to join, or it may produce invalid
2326 SQL in which case the database will raise an error. In correct
2327 practice, the
2328 :meth:`_query.Query.join` method is invoked in such a way that lines
2329 up with how we would want the JOIN clauses in SQL to be
2330 rendered, and each call should represent a clear link from what
2331 precedes it.
2333 **Joins to a Target Entity or Selectable**
2335 A second form of :meth:`_query.Query.join` allows any mapped entity or
2336 core selectable construct as a target. In this usage,
2337 :meth:`_query.Query.join` will attempt to create a JOIN along the
2338 natural foreign key relationship between two entities::
2340 q = session.query(User).join(Address)
2342 In the above calling form, :meth:`_query.Query.join` is called upon to
2343 create the "on clause" automatically for us. This calling form will
2344 ultimately raise an error if either there are no foreign keys between
2345 the two entities, or if there are multiple foreign key linkages between
2346 the target entity and the entity or entities already present on the
2347 left side such that creating a join requires more information. Note
2348 that when indicating a join to a target without any ON clause, ORM
2349 configured relationships are not taken into account.
2351 **Joins to a Target with an ON Clause**
2353 The third calling form allows both the target entity as well
2354 as the ON clause to be passed explicitly. A example that includes
2355 a SQL expression as the ON clause is as follows::
2357 q = session.query(User).join(Address, User.id == Address.user_id)
2359 The above form may also use a relationship-bound attribute as the
2360 ON clause as well::
2362 q = session.query(User).join(Address, User.addresses)
2364 The above syntax can be useful for the case where we wish
2365 to join to an alias of a particular target entity. If we wanted
2366 to join to ``Address`` twice, it could be achieved using two
2367 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2369 a1 = aliased(Address)
2370 a2 = aliased(Address)
2372 q = (
2373 session.query(User)
2374 .join(a1, User.addresses)
2375 .join(a2, User.addresses)
2376 .filter(a1.email_address == "ed@foo.com")
2377 .filter(a2.email_address == "ed@bar.com")
2378 )
2380 The relationship-bound calling form can also specify a target entity
2381 using the :meth:`_orm.PropComparator.of_type` method; a query
2382 equivalent to the one above would be::
2384 a1 = aliased(Address)
2385 a2 = aliased(Address)
2387 q = (
2388 session.query(User)
2389 .join(User.addresses.of_type(a1))
2390 .join(User.addresses.of_type(a2))
2391 .filter(a1.email_address == "ed@foo.com")
2392 .filter(a2.email_address == "ed@bar.com")
2393 )
2395 **Augmenting Built-in ON Clauses**
2397 As a substitute for providing a full custom ON condition for an
2398 existing relationship, the :meth:`_orm.PropComparator.and_` function
2399 may be applied to a relationship attribute to augment additional
2400 criteria into the ON clause; the additional criteria will be combined
2401 with the default criteria using AND::
2403 q = session.query(User).join(
2404 User.addresses.and_(Address.email_address != "foo@bar.com")
2405 )
2407 .. versionadded:: 1.4
2409 **Joining to Tables and Subqueries**
2412 The target of a join may also be any table or SELECT statement,
2413 which may be related to a target entity or not. Use the
2414 appropriate ``.subquery()`` method in order to make a subquery
2415 out of a query::
2417 subq = (
2418 session.query(Address)
2419 .filter(Address.email_address == "ed@foo.com")
2420 .subquery()
2421 )
2424 q = session.query(User).join(subq, User.id == subq.c.user_id)
2426 Joining to a subquery in terms of a specific relationship and/or
2427 target entity may be achieved by linking the subquery to the
2428 entity using :func:`_orm.aliased`::
2430 subq = (
2431 session.query(Address)
2432 .filter(Address.email_address == "ed@foo.com")
2433 .subquery()
2434 )
2436 address_subq = aliased(Address, subq)
2438 q = session.query(User).join(User.addresses.of_type(address_subq))
2440 **Controlling what to Join From**
2442 In cases where the left side of the current state of
2443 :class:`_query.Query` is not in line with what we want to join from,
2444 the :meth:`_query.Query.select_from` method may be used::
2446 q = (
2447 session.query(Address)
2448 .select_from(User)
2449 .join(User.addresses)
2450 .filter(User.name == "ed")
2451 )
2453 Which will produce SQL similar to:
2455 .. sourcecode:: sql
2457 SELECT address.* FROM user
2458 JOIN address ON user.id=address.user_id
2459 WHERE user.name = :name_1
2461 .. seealso::
2463 :meth:`_sql.Select.join` - v2 equivalent method.
2465 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2466 the props collection in modern use should be considered to be a one
2467 or two argument form, either as a single "target" entity or ORM
2468 attribute-bound relationship, or as a target entity plus an "on
2469 clause" which may be a SQL expression or ORM attribute-bound
2470 relationship.
2472 :param isouter=False: If True, the join used will be a left outer join,
2473 just as if the :meth:`_query.Query.outerjoin` method were called.
2475 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2477 """
2479 join_target = coercions.expect(
2480 roles.JoinTargetRole,
2481 target,
2482 apply_propagate_attrs=self,
2483 legacy=True,
2484 )
2485 if onclause is not None:
2486 onclause_element = coercions.expect(
2487 roles.OnClauseRole, onclause, legacy=True
2488 )
2489 else:
2490 onclause_element = None
2492 self._setup_joins += (
2493 (
2494 join_target,
2495 onclause_element,
2496 None,
2497 {
2498 "isouter": isouter,
2499 "full": full,
2500 },
2501 ),
2502 )
2504 self.__dict__.pop("_last_joined_entity", None)
2505 return self
2507 def outerjoin(
2508 self,
2509 target: _JoinTargetArgument,
2510 onclause: Optional[_OnClauseArgument] = None,
2511 *,
2512 full: bool = False,
2513 ) -> Self:
2514 """Create a left outer join against this ``Query`` object's criterion
2515 and apply generatively, returning the newly resulting ``Query``.
2517 Usage is the same as the ``join()`` method.
2519 .. seealso::
2521 :meth:`_sql.Select.outerjoin` - v2 equivalent method.
2523 """
2524 return self.join(target, onclause=onclause, isouter=True, full=full)
2526 @_generative
2527 @_assertions(_no_statement_condition)
2528 def reset_joinpoint(self) -> Self:
2529 """Return a new :class:`.Query`, where the "join point" has
2530 been reset back to the base FROM entities of the query.
2532 This method is usually used in conjunction with the
2533 ``aliased=True`` feature of the :meth:`~.Query.join`
2534 method. See the example in :meth:`~.Query.join` for how
2535 this is used.
2537 """
2538 self._last_joined_entity = None
2540 return self
2542 @_generative
2543 @_assertions(_no_clauseelement_condition)
2544 def select_from(self, *from_obj: _FromClauseArgument) -> Self:
2545 r"""Set the FROM clause of this :class:`.Query` explicitly.
2547 :meth:`.Query.select_from` is often used in conjunction with
2548 :meth:`.Query.join` in order to control which entity is selected
2549 from on the "left" side of the join.
2551 The entity or selectable object here effectively replaces the
2552 "left edge" of any calls to :meth:`~.Query.join`, when no
2553 joinpoint is otherwise established - usually, the default "join
2554 point" is the leftmost entity in the :class:`~.Query` object's
2555 list of entities to be selected.
2557 A typical example::
2559 q = (
2560 session.query(Address)
2561 .select_from(User)
2562 .join(User.addresses)
2563 .filter(User.name == "ed")
2564 )
2566 Which produces SQL equivalent to:
2568 .. sourcecode:: sql
2570 SELECT address.* FROM user
2571 JOIN address ON user.id=address.user_id
2572 WHERE user.name = :name_1
2574 :param \*from_obj: collection of one or more entities to apply
2575 to the FROM clause. Entities can be mapped classes,
2576 :class:`.AliasedClass` objects, :class:`.Mapper` objects
2577 as well as core :class:`.FromClause` elements like subqueries.
2579 .. seealso::
2581 :meth:`~.Query.join`
2583 :meth:`.Query.select_entity_from`
2585 :meth:`_sql.Select.select_from` - v2 equivalent method.
2587 """
2589 self._set_select_from(from_obj, False)
2590 return self
2592 @overload
2593 def __getitem__(self, item: slice) -> List[_T]: ...
2595 @overload
2596 def __getitem__(self, item: SupportsIndex) -> _T: ...
2598 def __getitem__(self, item: Any) -> Any:
2599 return orm_util._getitem(
2600 self,
2601 item,
2602 )
2604 @_generative
2605 @_assertions(_no_statement_condition)
2606 def slice(
2607 self,
2608 start: int,
2609 stop: int,
2610 ) -> Self:
2611 """Computes the "slice" of the :class:`_query.Query` represented by
2612 the given indices and returns the resulting :class:`_query.Query`.
2614 The start and stop indices behave like the argument to Python's
2615 built-in :func:`range` function. This method provides an
2616 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
2617 query.
2619 For example, ::
2621 session.query(User).order_by(User.id).slice(1, 3)
2623 renders as
2625 .. sourcecode:: sql
2627 SELECT users.id AS users_id,
2628 users.name AS users_name
2629 FROM users ORDER BY users.id
2630 LIMIT ? OFFSET ?
2631 (2, 1)
2633 .. seealso::
2635 :meth:`_query.Query.limit`
2637 :meth:`_query.Query.offset`
2639 :meth:`_sql.Select.slice` - v2 equivalent method.
2641 """
2643 self._limit_clause, self._offset_clause = sql_util._make_slice(
2644 self._limit_clause, self._offset_clause, start, stop
2645 )
2646 return self
2648 @_generative
2649 @_assertions(_no_statement_condition)
2650 def limit(self, limit: _LimitOffsetType) -> Self:
2651 """Apply a ``LIMIT`` to the query and return the newly resulting
2652 ``Query``.
2654 .. seealso::
2656 :meth:`_sql.Select.limit` - v2 equivalent method.
2658 """
2659 self._limit_clause = sql_util._offset_or_limit_clause(limit)
2660 return self
2662 @_generative
2663 @_assertions(_no_statement_condition)
2664 def offset(self, offset: _LimitOffsetType) -> Self:
2665 """Apply an ``OFFSET`` to the query and return the newly resulting
2666 ``Query``.
2668 .. seealso::
2670 :meth:`_sql.Select.offset` - v2 equivalent method.
2671 """
2672 self._offset_clause = sql_util._offset_or_limit_clause(offset)
2673 return self
2675 @_generative
2676 @_assertions(_no_statement_condition)
2677 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:
2678 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
2679 ``Query``.
2682 .. note::
2684 The ORM-level :meth:`.distinct` call includes logic that will
2685 automatically add columns from the ORDER BY of the query to the
2686 columns clause of the SELECT statement, to satisfy the common need
2687 of the database backend that ORDER BY columns be part of the SELECT
2688 list when DISTINCT is used. These columns *are not* added to the
2689 list of columns actually fetched by the :class:`_query.Query`,
2690 however,
2691 so would not affect results. The columns are passed through when
2692 using the :attr:`_query.Query.statement` accessor, however.
2694 .. deprecated:: 2.0 This logic is deprecated and will be removed
2695 in SQLAlchemy 2.0. See :ref:`migration_20_query_distinct`
2696 for a description of this use case in 2.0.
2698 .. seealso::
2700 :meth:`_sql.Select.distinct` - v2 equivalent method.
2702 :param \*expr: optional column expressions. When present,
2703 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2704 construct.
2706 .. deprecated:: 2.1 Passing expressions to
2707 :meth:`_orm.Query.distinct` is deprecated, use
2708 :func:`_postgresql.distinct_on` instead.
2710 """
2711 if expr:
2712 warn_deprecated(
2713 "Passing expression to ``distinct`` to generate a DISTINCT "
2714 "ON clause is deprecated. Use instead the "
2715 "``postgresql.distinct_on`` function as an extension.",
2716 "2.1",
2717 )
2718 self._distinct = True
2719 self._distinct_on = self._distinct_on + tuple(
2720 coercions.expect(roles.ByOfRole, e) for e in expr
2721 )
2722 else:
2723 self._distinct = True
2724 return self
2726 @_generative
2727 def ext(self, extension: SyntaxExtension) -> Self:
2728 """Applies a SQL syntax extension to this statement.
2730 .. seealso::
2732 :ref:`examples_syntax_extensions`
2734 :func:`_mysql.limit` - DML LIMIT for MySQL
2736 :func:`_postgresql.distinct_on` - DISTINCT ON for PostgreSQL
2738 .. versionadded:: 2.1
2740 """
2742 extension = coercions.expect(roles.SyntaxExtensionRole, extension)
2743 self._syntax_extensions += (extension,)
2744 return self
2746 def all(self) -> List[_T]:
2747 """Return the results represented by this :class:`_query.Query`
2748 as a list.
2750 This results in an execution of the underlying SQL statement.
2752 .. warning:: The :class:`_query.Query` object,
2753 when asked to return either
2754 a sequence or iterator that consists of full ORM-mapped entities,
2755 will **deduplicate entries based on primary key**. See the FAQ for
2756 more details.
2758 .. seealso::
2760 :ref:`faq_query_deduplicating`
2762 .. seealso::
2764 :meth:`_engine.Result.all` - v2 comparable method.
2766 :meth:`_engine.Result.scalars` - v2 comparable method.
2767 """
2768 return self._iter().all() # type: ignore
2770 @_generative
2771 @_assertions(_no_clauseelement_condition)
2772 def from_statement(self, statement: roles.SelectStatementRole) -> Self:
2773 """Execute the given SELECT statement and return results.
2775 This method bypasses all internal statement compilation, and the
2776 statement is executed without modification.
2778 The statement is typically either a :func:`_expression.text`
2779 or :func:`_expression.select` construct, and should return the set
2780 of columns
2781 appropriate to the entity class represented by this
2782 :class:`_query.Query`.
2784 .. seealso::
2786 :meth:`_sql.Select.from_statement` - v2 comparable method.
2788 """
2789 _statement = coercions.expect(
2790 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2791 )
2792 self._statement = _statement
2793 return self
2795 def first(self) -> Optional[_T]:
2796 """Return the first result of this ``Query`` or
2797 None if the result doesn't contain any row.
2799 first() applies a limit of one within the generated SQL, so that
2800 only one primary entity row is generated on the server side
2801 (note this may consist of multiple result rows if join-loaded
2802 collections are present).
2804 Calling :meth:`_query.Query.first`
2805 results in an execution of the underlying
2806 query.
2808 .. seealso::
2810 :meth:`_query.Query.one`
2812 :meth:`_query.Query.one_or_none`
2814 :meth:`_engine.Result.first` - v2 comparable method.
2816 :meth:`_engine.Result.scalars` - v2 comparable method.
2818 """
2819 # replicates limit(1) behavior
2820 if self._statement is not None:
2821 return self._iter().first() # type: ignore
2822 else:
2823 return self.limit(1)._iter().first() # type: ignore
2825 def one_or_none(self) -> Optional[_T]:
2826 """Return at most one result or raise an exception.
2828 Returns ``None`` if the query selects
2829 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2830 if multiple object identities are returned, or if multiple
2831 rows are returned for a query that returns only scalar values
2832 as opposed to full identity-mapped entities.
2834 Calling :meth:`_query.Query.one_or_none`
2835 results in an execution of the
2836 underlying query.
2838 .. seealso::
2840 :meth:`_query.Query.first`
2842 :meth:`_query.Query.one`
2844 :meth:`_engine.Result.one_or_none` - v2 comparable method.
2846 :meth:`_engine.Result.scalar_one_or_none` - v2 comparable method.
2848 """
2849 return self._iter().one_or_none() # type: ignore
2851 def one(self) -> _T:
2852 """Return exactly one result or raise an exception.
2854 Raises :class:`_exc.NoResultFound` if the query selects no rows.
2855 Raises :class:`_exc.MultipleResultsFound` if multiple object identities
2856 are returned, or if multiple rows are returned for a query that returns
2857 only scalar values as opposed to full identity-mapped entities.
2859 Calling :meth:`.one` results in an execution of the underlying query.
2861 .. seealso::
2863 :meth:`_query.Query.first`
2865 :meth:`_query.Query.one_or_none`
2867 :meth:`_engine.Result.one` - v2 comparable method.
2869 :meth:`_engine.Result.scalar_one` - v2 comparable method.
2871 """
2872 return self._iter().one() # type: ignore
2874 def scalar(self) -> Any:
2875 """Return the first element of the first result or None
2876 if no rows present. If multiple rows are returned,
2877 raises :class:`_exc.MultipleResultsFound`.
2879 >>> session.query(Item).scalar()
2880 <Item>
2881 >>> session.query(Item.id).scalar()
2882 1
2883 >>> session.query(Item.id).filter(Item.id < 0).scalar()
2884 None
2885 >>> session.query(Item.id, Item.name).scalar()
2886 1
2887 >>> session.query(func.count(Parent.id)).scalar()
2888 20
2890 This results in an execution of the underlying query.
2892 .. seealso::
2894 :meth:`_engine.Result.scalar` - v2 comparable method.
2896 """
2897 # TODO: not sure why we can't use result.scalar() here
2898 try:
2899 ret = self.one()
2900 if not isinstance(ret, collections_abc.Sequence):
2901 return ret
2902 return ret[0]
2903 except sa_exc.NoResultFound:
2904 return None
2906 def __iter__(self) -> Iterator[_T]:
2907 result = self._iter()
2908 try:
2909 yield from result # type: ignore
2910 except GeneratorExit:
2911 # issue #8710 - direct iteration is not reusable after
2912 # an iterable block is broken, so close the result
2913 result._soft_close()
2914 raise
2916 def _iter(self) -> Union[ScalarResult[_T], Result[_T]]:
2917 # new style execution.
2918 params = self._params
2920 statement = self._statement_20()
2921 result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
2922 statement,
2923 params,
2924 execution_options={"_sa_orm_load_options": self.load_options},
2925 )
2927 # legacy: automatically set scalars, unique
2928 if result._attributes.get("is_single_entity", False):
2929 result = cast("Result[_T]", result).scalars()
2931 if (
2932 result._attributes.get("filtered", False)
2933 and not self.load_options._yield_per
2934 ):
2935 result = result.unique()
2937 return result
2939 def __str__(self) -> str:
2940 statement = self._statement_20()
2942 try:
2943 bind = (
2944 self.session.get_bind(clause=statement)
2945 if self.session
2946 else None
2947 )
2948 except sa_exc.UnboundExecutionError:
2949 bind = None
2951 return str(statement.compile(bind))
2953 @property
2954 def column_descriptions(self) -> List[ORMColumnDescription]:
2955 """Return metadata about the columns which would be
2956 returned by this :class:`_query.Query`.
2958 Format is a list of dictionaries::
2960 user_alias = aliased(User, name="user2")
2961 q = sess.query(User, User.id, user_alias)
2963 # this expression:
2964 q.column_descriptions
2966 # would return:
2967 [
2968 {
2969 "name": "User",
2970 "type": User,
2971 "aliased": False,
2972 "expr": User,
2973 "entity": User,
2974 },
2975 {
2976 "name": "id",
2977 "type": Integer(),
2978 "aliased": False,
2979 "expr": User.id,
2980 "entity": User,
2981 },
2982 {
2983 "name": "user2",
2984 "type": User,
2985 "aliased": True,
2986 "expr": user_alias,
2987 "entity": user_alias,
2988 },
2989 ]
2991 .. seealso::
2993 This API is available using :term:`2.0 style` queries as well,
2994 documented at:
2996 * :ref:`queryguide_inspection`
2998 * :attr:`.Select.column_descriptions`
3000 """
3002 return _column_descriptions(self, legacy=True)
3004 @util.deprecated(
3005 "2.0",
3006 "The :meth:`_orm.Query.instances` method is deprecated and will "
3007 "be removed in a future release. "
3008 "Use the Select.from_statement() method or aliased() construct in "
3009 "conjunction with Session.execute() instead.",
3010 )
3011 def instances(
3012 self,
3013 result_proxy: CursorResult[Any],
3014 context: Optional[QueryContext] = None,
3015 ) -> Any:
3016 """Return an ORM result given a :class:`_engine.CursorResult` and
3017 :class:`.QueryContext`.
3019 """
3020 if context is None:
3021 util.warn_deprecated(
3022 "Using the Query.instances() method without a context "
3023 "is deprecated and will be disallowed in a future release. "
3024 "Please make use of :meth:`_query.Query.from_statement` "
3025 "for linking ORM results to arbitrary select constructs.",
3026 version="1.4",
3027 )
3028 compile_state = self._compile_state(for_statement=False)
3030 context = QueryContext(
3031 compile_state,
3032 compile_state.statement,
3033 compile_state.statement,
3034 self._params,
3035 self.session,
3036 self.load_options,
3037 )
3039 result = loading.instances(result_proxy, context)
3041 # legacy: automatically set scalars, unique
3042 if result._attributes.get("is_single_entity", False):
3043 result = result.scalars() # type: ignore
3045 if result._attributes.get("filtered", False):
3046 result = result.unique()
3048 # TODO: isn't this supposed to be a list?
3049 return result
3051 @util.became_legacy_20(
3052 ":meth:`_orm.Query.merge_result`",
3053 alternative="The method is superseded by the "
3054 ":func:`_orm.merge_frozen_result` function.",
3055 enable_warnings=False, # warnings occur via loading.merge_result
3056 )
3057 def merge_result(
3058 self,
3059 iterator: Union[
3060 FrozenResult[Any], Iterable[Sequence[Any]], Iterable[object]
3061 ],
3062 load: bool = True,
3063 ) -> Union[FrozenResult[Any], Iterable[Any]]:
3064 """Merge a result into this :class:`_query.Query` object's Session.
3066 Given an iterator returned by a :class:`_query.Query`
3067 of the same structure
3068 as this one, return an identical iterator of results, with all mapped
3069 instances merged into the session using :meth:`.Session.merge`. This
3070 is an optimized method which will merge all mapped instances,
3071 preserving the structure of the result rows and unmapped columns with
3072 less method overhead than that of calling :meth:`.Session.merge`
3073 explicitly for each value.
3075 The structure of the results is determined based on the column list of
3076 this :class:`_query.Query` - if these do not correspond,
3077 unchecked errors
3078 will occur.
3080 The 'load' argument is the same as that of :meth:`.Session.merge`.
3082 For an example of how :meth:`_query.Query.merge_result` is used, see
3083 the source code for the example :ref:`examples_caching`, where
3084 :meth:`_query.Query.merge_result` is used to efficiently restore state
3085 from a cache back into a target :class:`.Session`.
3087 """
3089 return loading.merge_result(self, iterator, load)
3091 def exists(self) -> Exists:
3092 """A convenience method that turns a query into an EXISTS subquery
3093 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3095 e.g.::
3097 q = session.query(User).filter(User.name == "fred")
3098 session.query(q.exists())
3100 Producing SQL similar to:
3102 .. sourcecode:: sql
3104 SELECT EXISTS (
3105 SELECT 1 FROM users WHERE users.name = :name_1
3106 ) AS anon_1
3108 The EXISTS construct is usually used in the WHERE clause::
3110 session.query(User.id).filter(q.exists()).scalar()
3112 Note that some databases such as SQL Server don't allow an
3113 EXISTS expression to be present in the columns clause of a
3114 SELECT. To select a simple boolean value based on the exists
3115 as a WHERE, use :func:`.literal`::
3117 from sqlalchemy import literal
3119 session.query(literal(True)).filter(q.exists()).scalar()
3121 .. seealso::
3123 :meth:`_sql.Select.exists` - v2 comparable method.
3125 """
3127 # .add_columns() for the case that we are a query().select_from(X),
3128 # so that ".statement" can be produced (#2995) but also without
3129 # omitting the FROM clause from a query(X) (#2818);
3130 # .with_only_columns() after we have a core select() so that
3131 # we get just "SELECT 1" without any entities.
3133 inner = (
3134 self.enable_eagerloads(False)
3135 .add_columns(sql.literal_column("1"))
3136 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3137 ._get_select_statement_only()
3138 .with_only_columns(1)
3139 )
3141 ezero = self._entity_from_pre_ent_zero()
3142 if ezero is not None:
3143 inner = inner.select_from(ezero)
3145 return sql.exists(inner)
3147 def count(self) -> int:
3148 r"""Return a count of rows this the SQL formed by this :class:`Query`
3149 would return.
3151 This generates the SQL for this Query as follows:
3153 .. sourcecode:: sql
3155 SELECT count(1) AS count_1 FROM (
3156 SELECT <rest of query follows...>
3157 ) AS anon_1
3159 The above SQL returns a single row, which is the aggregate value
3160 of the count function; the :meth:`_query.Query.count`
3161 method then returns
3162 that single integer value.
3164 .. warning::
3166 It is important to note that the value returned by
3167 count() is **not the same as the number of ORM objects that this
3168 Query would return from a method such as the .all() method**.
3169 The :class:`_query.Query` object,
3170 when asked to return full entities,
3171 will **deduplicate entries based on primary key**, meaning if the
3172 same primary key value would appear in the results more than once,
3173 only one object of that primary key would be present. This does
3174 not apply to a query that is against individual columns.
3176 .. seealso::
3178 :ref:`faq_query_deduplicating`
3180 For fine grained control over specific columns to count, to skip the
3181 usage of a subquery or otherwise control of the FROM clause, or to use
3182 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3183 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3185 from sqlalchemy import func
3187 # count User records, without
3188 # using a subquery.
3189 session.query(func.count(User.id))
3191 # return count of user "id" grouped
3192 # by "name"
3193 session.query(func.count(User.id)).group_by(User.name)
3195 from sqlalchemy import distinct
3197 # count distinct "name" values
3198 session.query(func.count(distinct(User.name)))
3200 .. seealso::
3202 :ref:`migration_20_query_usage`
3204 """
3205 col = sql.func.count(sql.literal_column("*"))
3206 return ( # type: ignore
3207 self._legacy_from_self(col).enable_eagerloads(False).scalar()
3208 )
3210 def delete(
3211 self,
3212 synchronize_session: SynchronizeSessionArgument = "auto",
3213 delete_args: Optional[Dict[Any, Any]] = None,
3214 ) -> int:
3215 r"""Perform a DELETE with an arbitrary WHERE clause.
3217 Deletes rows matched by this query from the database.
3219 E.g.::
3221 sess.query(User).filter(User.age == 25).delete(synchronize_session=False)
3223 sess.query(User).filter(User.age == 25).delete(
3224 synchronize_session="evaluate"
3225 )
3227 .. warning::
3229 See the section :ref:`orm_expression_update_delete` for important
3230 caveats and warnings, including limitations when using bulk UPDATE
3231 and DELETE with mapper inheritance configurations.
3233 :param synchronize_session: chooses the strategy to update the
3234 attributes on objects in the session. See the section
3235 :ref:`orm_expression_update_delete` for a discussion of these
3236 strategies.
3238 :param delete_args: Optional dictionary, if present will be passed
3239 to the underlying :func:`_expression.delete` construct as the ``**kw``
3240 for the object. May be used to pass dialect-specific arguments such
3241 as ``mysql_limit``.
3243 .. versionadded:: 2.0.37
3245 :return: the count of rows matched as returned by the database's
3246 "row count" feature.
3248 .. seealso::
3250 :ref:`orm_expression_update_delete`
3252 """ # noqa: E501
3254 bulk_del = BulkDelete(self, delete_args)
3255 if self.dispatch.before_compile_delete:
3256 for fn in self.dispatch.before_compile_delete:
3257 new_query = fn(bulk_del.query, bulk_del)
3258 if new_query is not None:
3259 bulk_del.query = new_query
3261 self = bulk_del.query
3263 delete_ = sql.delete(*self._raw_columns) # type: ignore
3265 if delete_args:
3266 delete_ = delete_.with_dialect_options(**delete_args)
3268 delete_._where_criteria = self._where_criteria
3270 for ext in self._syntax_extensions:
3271 delete_._apply_syntax_extension_to_self(ext)
3273 result = cast(
3274 "CursorResult[Any]",
3275 self.session.execute(
3276 delete_,
3277 self._params,
3278 execution_options=self._execution_options.union(
3279 {"synchronize_session": synchronize_session}
3280 ),
3281 ),
3282 )
3283 bulk_del.result = result # type: ignore
3284 self.session.dispatch.after_bulk_delete(bulk_del)
3285 result.close()
3287 return result.rowcount
3289 def update(
3290 self,
3291 values: Dict[_DMLColumnArgument, Any],
3292 synchronize_session: SynchronizeSessionArgument = "auto",
3293 update_args: Optional[Dict[Any, Any]] = None,
3294 ) -> int:
3295 r"""Perform an UPDATE with an arbitrary WHERE clause.
3297 Updates rows matched by this query in the database.
3299 E.g.::
3301 sess.query(User).filter(User.age == 25).update(
3302 {User.age: User.age - 10}, synchronize_session=False
3303 )
3305 sess.query(User).filter(User.age == 25).update(
3306 {"age": User.age - 10}, synchronize_session="evaluate"
3307 )
3309 .. warning::
3311 See the section :ref:`orm_expression_update_delete` for important
3312 caveats and warnings, including limitations when using arbitrary
3313 UPDATE and DELETE with mapper inheritance configurations.
3315 :param values: a dictionary with attributes names, or alternatively
3316 mapped attributes or SQL expressions, as keys, and literal
3317 values or sql expressions as values. If :ref:`parameter-ordered
3318 mode <tutorial_parameter_ordered_updates>` is desired, the values can
3319 be passed as a list of 2-tuples; this requires that the
3320 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3321 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3322 as well.
3324 :param synchronize_session: chooses the strategy to update the
3325 attributes on objects in the session. See the section
3326 :ref:`orm_expression_update_delete` for a discussion of these
3327 strategies.
3329 :param update_args: Optional dictionary, if present will be passed
3330 to the underlying :func:`_expression.update` construct as the ``**kw``
3331 for the object. May be used to pass dialect-specific arguments such
3332 as ``mysql_limit``, as well as other special arguments such as
3333 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3335 :return: the count of rows matched as returned by the database's
3336 "row count" feature.
3339 .. seealso::
3341 :ref:`orm_expression_update_delete`
3343 """
3345 update_args = update_args or {}
3347 bulk_ud = BulkUpdate(self, values, update_args)
3349 if self.dispatch.before_compile_update:
3350 for fn in self.dispatch.before_compile_update:
3351 new_query = fn(bulk_ud.query, bulk_ud)
3352 if new_query is not None:
3353 bulk_ud.query = new_query
3354 self = bulk_ud.query
3356 upd = sql.update(*self._raw_columns) # type: ignore
3358 ppo = update_args.pop("preserve_parameter_order", False)
3359 if ppo:
3360 upd = upd.ordered_values(*values) # type: ignore
3361 else:
3362 upd = upd.values(values)
3363 if update_args:
3364 upd = upd.with_dialect_options(**update_args)
3366 upd._where_criteria = self._where_criteria
3368 for ext in self._syntax_extensions:
3369 upd._apply_syntax_extension_to_self(ext)
3371 result = cast(
3372 "CursorResult[Any]",
3373 self.session.execute(
3374 upd,
3375 self._params,
3376 execution_options=self._execution_options.union(
3377 {"synchronize_session": synchronize_session}
3378 ),
3379 ),
3380 )
3381 bulk_ud.result = result # type: ignore
3382 self.session.dispatch.after_bulk_update(bulk_ud)
3383 result.close()
3384 return result.rowcount
3386 def _compile_state(
3387 self, for_statement: bool = False, **kw: Any
3388 ) -> _ORMCompileState:
3389 """Create an out-of-compiler ORMCompileState object.
3391 The ORMCompileState object is normally created directly as a result
3392 of the SQLCompiler.process() method being handed a Select()
3393 or FromStatement() object that uses the "orm" plugin. This method
3394 provides a means of creating this ORMCompileState object directly
3395 without using the compiler.
3397 This method is used only for deprecated cases, which include
3398 the .from_self() method for a Query that has multiple levels
3399 of .from_self() in use, as well as the instances() method. It is
3400 also used within the test suite to generate ORMCompileState objects
3401 for test purposes.
3403 """
3405 stmt = self._statement_20(for_statement=for_statement, **kw)
3406 assert for_statement == stmt._compile_options._for_statement
3408 # this chooses between ORMFromStatementCompileState and
3409 # ORMSelectCompileState. We could also base this on
3410 # query._statement is not None as we have the ORM Query here
3411 # however this is the more general path.
3412 compile_state_cls = cast(
3413 _ORMCompileState,
3414 _ORMCompileState._get_plugin_class_for_plugin(stmt, "orm"),
3415 )
3417 return compile_state_cls._create_orm_context(
3418 stmt, toplevel=True, compiler=None
3419 )
3421 def _compile_context(self, for_statement: bool = False) -> QueryContext:
3422 compile_state = self._compile_state(for_statement=for_statement)
3423 context = QueryContext(
3424 compile_state,
3425 compile_state.statement,
3426 compile_state.statement,
3427 self._params,
3428 self.session,
3429 self.load_options,
3430 )
3432 return context
3435class AliasOption(interfaces.LoaderOption):
3436 inherit_cache = False
3438 @util.deprecated(
3439 "1.4",
3440 "The :class:`.AliasOption` object is not necessary "
3441 "for entities to be matched up to a query that is established "
3442 "via :meth:`.Query.from_statement` and now does nothing.",
3443 )
3444 def __init__(self, alias: Union[Alias, Subquery]):
3445 r"""Return a :class:`.MapperOption` that will indicate to the
3446 :class:`_query.Query`
3447 that the main table has been aliased.
3449 """
3451 def process_compile_state(self, compile_state: _ORMCompileState) -> None:
3452 pass
3455class BulkUD:
3456 """State used for the orm.Query version of update() / delete().
3458 This object is now specific to Query only.
3460 """
3462 def __init__(self, query: Query[Any]):
3463 self.query = query.enable_eagerloads(False)
3464 self._validate_query_state()
3465 self.mapper = self.query._entity_from_pre_ent_zero()
3467 def _validate_query_state(self) -> None:
3468 for attr, methname, notset, op in (
3469 ("_limit_clause", "limit()", None, operator.is_),
3470 ("_offset_clause", "offset()", None, operator.is_),
3471 ("_order_by_clauses", "order_by()", (), operator.eq),
3472 ("_group_by_clauses", "group_by()", (), operator.eq),
3473 ("_distinct", "distinct()", False, operator.is_),
3474 (
3475 "_from_obj",
3476 "join(), outerjoin(), select_from(), or from_self()",
3477 (),
3478 operator.eq,
3479 ),
3480 (
3481 "_setup_joins",
3482 "join(), outerjoin(), select_from(), or from_self()",
3483 (),
3484 operator.eq,
3485 ),
3486 ):
3487 if not op(getattr(self.query, attr), notset):
3488 raise sa_exc.InvalidRequestError(
3489 "Can't call Query.update() or Query.delete() "
3490 "when %s has been called" % (methname,)
3491 )
3493 @property
3494 def session(self) -> Session:
3495 return self.query.session
3498class BulkUpdate(BulkUD):
3499 """BulkUD which handles UPDATEs."""
3501 def __init__(
3502 self,
3503 query: Query[Any],
3504 values: Dict[_DMLColumnArgument, Any],
3505 update_kwargs: Optional[Dict[Any, Any]],
3506 ):
3507 super().__init__(query)
3508 self.values = values
3509 self.update_kwargs = update_kwargs
3512class BulkDelete(BulkUD):
3513 """BulkUD which handles DELETEs."""
3515 def __init__(
3516 self,
3517 query: Query[Any],
3518 delete_kwargs: Optional[Dict[Any, Any]],
3519 ):
3520 super().__init__(query)
3521 self.delete_kwargs = delete_kwargs
3524class RowReturningQuery(Query[Row[Unpack[_Ts]]]):
3525 if TYPE_CHECKING:
3527 def tuples(self) -> Query[Tuple[Unpack[_Ts]]]: # type: ignore
3528 ...