Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/query.py: 46%
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 Mapping
34from typing import Optional
35from typing import overload
36from typing import Sequence
37from typing import Tuple
38from typing import Type
39from typing import TYPE_CHECKING
40from typing import TypeVar
41from typing import Union
43from . import attributes
44from . import interfaces
45from . import loading
46from . import util as orm_util
47from ._typing import _O
48from .base import _assertions
49from .context import _column_descriptions
50from .context import _determine_last_joined_entity
51from .context import _legacy_filter_by_entity_zero
52from .context import FromStatement
53from .context import ORMCompileState
54from .context import QueryContext
55from .interfaces import ORMColumnDescription
56from .interfaces import ORMColumnsClauseRole
57from .util import AliasedClass
58from .util import object_mapper
59from .util import with_parent
60from .. import exc as sa_exc
61from .. import inspect
62from .. import inspection
63from .. import log
64from .. import sql
65from .. import util
66from ..engine import Result
67from ..engine import Row
68from ..event import dispatcher
69from ..event import EventTarget
70from ..sql import coercions
71from ..sql import expression
72from ..sql import roles
73from ..sql import Select
74from ..sql import util as sql_util
75from ..sql import visitors
76from ..sql._typing import _FromClauseArgument
77from ..sql._typing import _TP
78from ..sql.annotation import SupportsCloneAnnotations
79from ..sql.base import _entity_namespace_key
80from ..sql.base import _generative
81from ..sql.base import _NoArg
82from ..sql.base import Executable
83from ..sql.base import Generative
84from ..sql.elements import BooleanClauseList
85from ..sql.expression import Exists
86from ..sql.selectable import _MemoizedSelectEntities
87from ..sql.selectable import _SelectFromElements
88from ..sql.selectable import ForUpdateArg
89from ..sql.selectable import HasHints
90from ..sql.selectable import HasPrefixes
91from ..sql.selectable import HasSuffixes
92from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL
93from ..sql.selectable import SelectLabelStyle
94from ..util.typing import Literal
95from ..util.typing import Self
96from ..util.typing import SupportsIndex
99if TYPE_CHECKING:
100 from ._typing import _EntityType
101 from ._typing import _ExternalEntityType
102 from ._typing import _InternalEntityType
103 from ._typing import SynchronizeSessionArgument
104 from .mapper import Mapper
105 from .path_registry import PathRegistry
106 from .session import _PKIdentityArgument
107 from .session import Session
108 from .state import InstanceState
109 from ..engine.cursor import CursorResult
110 from ..engine.interfaces import _ImmutableExecuteOptions
111 from ..engine.interfaces import CompiledCacheType
112 from ..engine.interfaces import IsolationLevel
113 from ..engine.interfaces import SchemaTranslateMapType
114 from ..engine.result import FrozenResult
115 from ..engine.result import ScalarResult
116 from ..sql._typing import _ColumnExpressionArgument
117 from ..sql._typing import _ColumnExpressionOrStrLabelArgument
118 from ..sql._typing import _ColumnsClauseArgument
119 from ..sql._typing import _DMLColumnArgument
120 from ..sql._typing import _JoinTargetArgument
121 from ..sql._typing import _LimitOffsetType
122 from ..sql._typing import _MAYBE_ENTITY
123 from ..sql._typing import _no_kw
124 from ..sql._typing import _NOT_ENTITY
125 from ..sql._typing import _OnClauseArgument
126 from ..sql._typing import _PropagateAttrsType
127 from ..sql._typing import _T0
128 from ..sql._typing import _T1
129 from ..sql._typing import _T2
130 from ..sql._typing import _T3
131 from ..sql._typing import _T4
132 from ..sql._typing import _T5
133 from ..sql._typing import _T6
134 from ..sql._typing import _T7
135 from ..sql._typing import _TypedColumnClauseArgument as _TCCA
136 from ..sql.base import CacheableOptions
137 from ..sql.base import ExecutableOption
138 from ..sql.dml import UpdateBase
139 from ..sql.elements import ColumnElement
140 from ..sql.elements import Label
141 from ..sql.selectable import _ForUpdateOfArgument
142 from ..sql.selectable import _JoinTargetElement
143 from ..sql.selectable import _SetupJoinsElement
144 from ..sql.selectable import Alias
145 from ..sql.selectable import CTE
146 from ..sql.selectable import ExecutableReturnsRows
147 from ..sql.selectable import FromClause
148 from ..sql.selectable import ScalarSelect
149 from ..sql.selectable import Subquery
152__all__ = ["Query", "QueryContext"]
154_T = TypeVar("_T", bound=Any)
157@inspection._self_inspects
158@log.class_logger
159class Query(
160 _SelectFromElements,
161 SupportsCloneAnnotations,
162 HasPrefixes,
163 HasSuffixes,
164 HasHints,
165 EventTarget,
166 log.Identified,
167 Generative,
168 Executable,
169 Generic[_T],
170):
171 """ORM-level SQL construction object.
173 .. legacy:: The ORM :class:`.Query` object is a legacy construct
174 as of SQLAlchemy 2.0. See the notes at the top of
175 :ref:`query_api_toplevel` for an overview, including links to migration
176 documentation.
178 :class:`_query.Query` objects are normally initially generated using the
179 :meth:`~.Session.query` method of :class:`.Session`, and in
180 less common cases by instantiating the :class:`_query.Query` directly and
181 associating with a :class:`.Session` using the
182 :meth:`_query.Query.with_session`
183 method.
185 """
187 # elements that are in Core and can be cached in the same way
188 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
189 _having_criteria: Tuple[ColumnElement[Any], ...] = ()
191 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()
192 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()
193 _limit_clause: Optional[ColumnElement[Any]] = None
194 _offset_clause: Optional[ColumnElement[Any]] = None
196 _distinct: bool = False
197 _distinct_on: Tuple[ColumnElement[Any], ...] = ()
199 _for_update_arg: Optional[ForUpdateArg] = None
200 _correlate: Tuple[FromClause, ...] = ()
201 _auto_correlate: bool = True
202 _from_obj: Tuple[FromClause, ...] = ()
203 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()
205 _label_style: SelectLabelStyle = SelectLabelStyle.LABEL_STYLE_LEGACY_ORM
207 _memoized_select_entities = ()
209 _compile_options: Union[Type[CacheableOptions], CacheableOptions] = (
210 ORMCompileState.default_compile_options
211 )
213 _with_options: Tuple[ExecutableOption, ...]
214 load_options = QueryContext.default_load_options + {
215 "_legacy_uniquing": True
216 }
218 _params: util.immutabledict[str, Any] = util.EMPTY_DICT
220 # local Query builder state, not needed for
221 # compilation or execution
222 _enable_assertions = True
224 _statement: Optional[ExecutableReturnsRows] = None
226 session: Session
228 dispatch: dispatcher[Query[_T]]
230 # mirrors that of ClauseElement, used to propagate the "orm"
231 # plugin as well as the "subject" of the plugin, e.g. the mapper
232 # we are querying against.
233 @util.memoized_property
234 def _propagate_attrs(self) -> _PropagateAttrsType:
235 return util.EMPTY_DICT
237 def __init__(
238 self,
239 entities: Union[
240 _ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]
241 ],
242 session: Optional[Session] = None,
243 ):
244 """Construct a :class:`_query.Query` directly.
246 E.g.::
248 q = Query([User, Address], session=some_session)
250 The above is equivalent to::
252 q = some_session.query(User, Address)
254 :param entities: a sequence of entities and/or SQL expressions.
256 :param session: a :class:`.Session` with which the
257 :class:`_query.Query`
258 will be associated. Optional; a :class:`_query.Query`
259 can be associated
260 with a :class:`.Session` generatively via the
261 :meth:`_query.Query.with_session` method as well.
263 .. seealso::
265 :meth:`.Session.query`
267 :meth:`_query.Query.with_session`
269 """
271 # session is usually present. There's one case in subqueryloader
272 # where it stores a Query without a Session and also there are tests
273 # for the query(Entity).with_session(session) API which is likely in
274 # some old recipes, however these are legacy as select() can now be
275 # used.
276 self.session = session # type: ignore
277 self._set_entities(entities)
279 def _set_propagate_attrs(self, values: Mapping[str, Any]) -> Self:
280 self._propagate_attrs = util.immutabledict(values)
281 return self
283 def _set_entities(
284 self,
285 entities: Union[
286 _ColumnsClauseArgument[Any], Iterable[_ColumnsClauseArgument[Any]]
287 ],
288 ) -> None:
289 self._raw_columns = [
290 coercions.expect(
291 roles.ColumnsClauseRole,
292 ent,
293 apply_propagate_attrs=self,
294 post_inspect=True,
295 )
296 for ent in util.to_list(entities)
297 ]
299 def tuples(self: Query[_O]) -> Query[Tuple[_O]]:
300 """return a tuple-typed form of this :class:`.Query`.
302 This method invokes the :meth:`.Query.only_return_tuples`
303 method with a value of ``True``, which by itself ensures that this
304 :class:`.Query` will always return :class:`.Row` objects, even
305 if the query is made against a single entity. It then also
306 at the typing level will return a "typed" query, if possible,
307 that will type result rows as ``Tuple`` objects with typed
308 elements.
310 This method can be compared to the :meth:`.Result.tuples` method,
311 which returns "self", but from a typing perspective returns an object
312 that will yield typed ``Tuple`` objects for results. Typing
313 takes effect only if this :class:`.Query` object is a typed
314 query object already.
316 .. versionadded:: 2.0
318 .. seealso::
320 :meth:`.Result.tuples` - v2 equivalent method.
322 """
323 return self.only_return_tuples(True) # type: ignore
325 def _entity_from_pre_ent_zero(self) -> Optional[_InternalEntityType[Any]]:
326 if not self._raw_columns:
327 return None
329 ent = self._raw_columns[0]
331 if "parententity" in ent._annotations:
332 return ent._annotations["parententity"] # type: ignore
333 elif "bundle" in ent._annotations:
334 return ent._annotations["bundle"] # type: ignore
335 else:
336 # label, other SQL expression
337 for element in visitors.iterate(ent):
338 if "parententity" in element._annotations:
339 return element._annotations["parententity"] # type: ignore # noqa: E501
340 else:
341 return None
343 def _only_full_mapper_zero(self, methname: str) -> Mapper[Any]:
344 if (
345 len(self._raw_columns) != 1
346 or "parententity" not in self._raw_columns[0]._annotations
347 or not self._raw_columns[0].is_selectable
348 ):
349 raise sa_exc.InvalidRequestError(
350 "%s() can only be used against "
351 "a single mapped class." % methname
352 )
354 return self._raw_columns[0]._annotations["parententity"] # type: ignore # noqa: E501
356 def _set_select_from(
357 self, obj: Iterable[_FromClauseArgument], set_base_alias: bool
358 ) -> None:
359 fa = [
360 coercions.expect(
361 roles.StrictFromClauseRole,
362 elem,
363 allow_select=True,
364 apply_propagate_attrs=self,
365 )
366 for elem in obj
367 ]
369 self._compile_options += {"_set_base_alias": set_base_alias}
370 self._from_obj = tuple(fa)
372 @_generative
373 def _set_lazyload_from(self, state: InstanceState[Any]) -> Self:
374 self.load_options += {"_lazy_loaded_from": state}
375 return self
377 def _get_condition(self) -> None:
378 """used by legacy BakedQuery"""
379 self._no_criterion_condition("get", order_by=False, distinct=False)
381 def _get_existing_condition(self) -> None:
382 self._no_criterion_assertion("get", order_by=False, distinct=False)
384 def _no_criterion_assertion(
385 self, meth: str, order_by: bool = True, distinct: bool = True
386 ) -> None:
387 if not self._enable_assertions:
388 return
389 if (
390 self._where_criteria
391 or self._statement is not None
392 or self._from_obj
393 or self._setup_joins
394 or self._limit_clause is not None
395 or self._offset_clause is not None
396 or self._group_by_clauses
397 or (order_by and self._order_by_clauses)
398 or (distinct and self._distinct)
399 ):
400 raise sa_exc.InvalidRequestError(
401 "Query.%s() being called on a "
402 "Query with existing criterion. " % meth
403 )
405 def _no_criterion_condition(
406 self, meth: str, order_by: bool = True, distinct: bool = True
407 ) -> None:
408 self._no_criterion_assertion(meth, order_by, distinct)
410 self._from_obj = self._setup_joins = ()
411 if self._statement is not None:
412 self._compile_options += {"_statement": None}
413 self._where_criteria = ()
414 self._distinct = False
416 self._order_by_clauses = self._group_by_clauses = ()
418 def _no_clauseelement_condition(self, meth: str) -> None:
419 if not self._enable_assertions:
420 return
421 if self._order_by_clauses:
422 raise sa_exc.InvalidRequestError(
423 "Query.%s() being called on a "
424 "Query with existing criterion. " % meth
425 )
426 self._no_criterion_condition(meth)
428 def _no_statement_condition(self, meth: str) -> None:
429 if not self._enable_assertions:
430 return
431 if self._statement is not None:
432 raise sa_exc.InvalidRequestError(
433 (
434 "Query.%s() being called on a Query with an existing full "
435 "statement - can't apply criterion."
436 )
437 % meth
438 )
440 def _no_limit_offset(self, meth: str) -> None:
441 if not self._enable_assertions:
442 return
443 if self._limit_clause is not None or self._offset_clause is not None:
444 raise sa_exc.InvalidRequestError(
445 "Query.%s() being called on a Query which already has LIMIT "
446 "or OFFSET applied. Call %s() before limit() or offset() "
447 "are applied." % (meth, meth)
448 )
450 @property
451 def _has_row_limiting_clause(self) -> bool:
452 return (
453 self._limit_clause is not None or self._offset_clause is not None
454 )
456 def _get_options(
457 self,
458 populate_existing: Optional[bool] = None,
459 version_check: Optional[bool] = None,
460 only_load_props: Optional[Sequence[str]] = None,
461 refresh_state: Optional[InstanceState[Any]] = None,
462 identity_token: Optional[Any] = None,
463 ) -> Self:
464 load_options: Dict[str, Any] = {}
465 compile_options: Dict[str, Any] = {}
467 if version_check:
468 load_options["_version_check"] = version_check
469 if populate_existing:
470 load_options["_populate_existing"] = populate_existing
471 if refresh_state:
472 load_options["_refresh_state"] = refresh_state
473 compile_options["_for_refresh_state"] = True
474 if only_load_props:
475 compile_options["_only_load_props"] = frozenset(only_load_props)
476 if identity_token:
477 load_options["_identity_token"] = identity_token
479 if load_options:
480 self.load_options += load_options
481 if compile_options:
482 self._compile_options += compile_options
484 return self
486 def _clone(self, **kw: Any) -> Self:
487 return self._generate()
489 def _get_select_statement_only(self) -> Select[_T]:
490 if self._statement is not None:
491 raise sa_exc.InvalidRequestError(
492 "Can't call this method on a Query that uses from_statement()"
493 )
494 return cast("Select[_T]", self.statement)
496 @property
497 def statement(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
498 """The full SELECT statement represented by this Query.
500 The statement by default will not have disambiguating labels
501 applied to the construct unless with_labels(True) is called
502 first.
504 """
506 # .statement can return the direct future.Select() construct here, as
507 # long as we are not using subsequent adaption features that
508 # are made against raw entities, e.g. from_self(), with_polymorphic(),
509 # select_entity_from(). If these features are being used, then
510 # the Select() we return will not have the correct .selected_columns
511 # collection and will not embed in subsequent queries correctly.
512 # We could find a way to make this collection "correct", however
513 # this would not be too different from doing the full compile as
514 # we are doing in any case, the Select() would still not have the
515 # proper state for other attributes like whereclause, order_by,
516 # and these features are all deprecated in any case.
517 #
518 # for these reasons, Query is not a Select, it remains an ORM
519 # object for which __clause_element__() must be called in order for
520 # it to provide a real expression object.
521 #
522 # from there, it starts to look much like Query itself won't be
523 # passed into the execute process and won't generate its own cache
524 # key; this will all occur in terms of the ORM-enabled Select.
525 stmt: Union[Select[_T], FromStatement[_T], UpdateBase]
527 if not self._compile_options._set_base_alias:
528 # if we don't have legacy top level aliasing features in use
529 # then convert to a future select() directly
530 stmt = self._statement_20(for_statement=True)
531 else:
532 stmt = self._compile_state(for_statement=True).statement
534 if self._params:
535 stmt = stmt.params(self._params)
537 return stmt
539 def _final_statement(self, legacy_query_style: bool = True) -> Select[Any]:
540 """Return the 'final' SELECT statement for this :class:`.Query`.
542 This is used by the testing suite only and is fairly inefficient.
544 This is the Core-only select() that will be rendered by a complete
545 compilation of this query, and is what .statement used to return
546 in 1.3.
549 """
551 q = self._clone()
553 return q._compile_state(
554 use_legacy_query_style=legacy_query_style
555 ).statement # type: ignore
557 def _statement_20(
558 self, for_statement: bool = False, use_legacy_query_style: bool = True
559 ) -> Union[Select[_T], FromStatement[_T]]:
560 # TODO: this event needs to be deprecated, as it currently applies
561 # only to ORM query and occurs at this spot that is now more
562 # or less an artificial spot
563 if self.dispatch.before_compile:
564 for fn in self.dispatch.before_compile:
565 new_query = fn(self)
566 if new_query is not None and new_query is not self:
567 self = new_query
568 if not fn._bake_ok: # type: ignore
569 self._compile_options += {"_bake_ok": False}
571 compile_options = self._compile_options
572 compile_options += {
573 "_for_statement": for_statement,
574 "_use_legacy_query_style": use_legacy_query_style,
575 }
577 stmt: Union[Select[_T], FromStatement[_T]]
579 if self._statement is not None:
580 stmt = FromStatement(self._raw_columns, self._statement)
581 stmt.__dict__.update(
582 _with_options=self._with_options,
583 _with_context_options=self._with_context_options,
584 _compile_options=compile_options,
585 _execution_options=self._execution_options,
586 _propagate_attrs=self._propagate_attrs,
587 )
588 else:
589 # Query / select() internal attributes are 99% cross-compatible
590 stmt = Select._create_raw_select(**self.__dict__)
591 stmt.__dict__.update(
592 _label_style=self._label_style,
593 _compile_options=compile_options,
594 _propagate_attrs=self._propagate_attrs,
595 )
596 stmt.__dict__.pop("session", None)
598 # ensure the ORM context is used to compile the statement, even
599 # if it has no ORM entities. This is so ORM-only things like
600 # _legacy_joins are picked up that wouldn't be picked up by the
601 # Core statement context
602 if "compile_state_plugin" not in stmt._propagate_attrs:
603 stmt._propagate_attrs = stmt._propagate_attrs.union(
604 {"compile_state_plugin": "orm", "plugin_subject": None}
605 )
607 return stmt
609 def subquery(
610 self,
611 name: Optional[str] = None,
612 with_labels: bool = False,
613 reduce_columns: bool = False,
614 ) -> Subquery:
615 """Return the full SELECT statement represented by
616 this :class:`_query.Query`, embedded within an
617 :class:`_expression.Alias`.
619 Eager JOIN generation within the query is disabled.
621 .. seealso::
623 :meth:`_sql.Select.subquery` - v2 comparable method.
625 :param name: string name to be assigned as the alias;
626 this is passed through to :meth:`_expression.FromClause.alias`.
627 If ``None``, a name will be deterministically generated
628 at compile time.
630 :param with_labels: if True, :meth:`.with_labels` will be called
631 on the :class:`_query.Query` first to apply table-qualified labels
632 to all columns.
634 :param reduce_columns: if True,
635 :meth:`_expression.Select.reduce_columns` will
636 be called on the resulting :func:`_expression.select` construct,
637 to remove same-named columns where one also refers to the other
638 via foreign key or WHERE clause equivalence.
640 """
641 q = self.enable_eagerloads(False)
642 if with_labels:
643 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
645 stmt = q._get_select_statement_only()
647 if TYPE_CHECKING:
648 assert isinstance(stmt, Select)
650 if reduce_columns:
651 stmt = stmt.reduce_columns()
652 return stmt.subquery(name=name)
654 def cte(
655 self,
656 name: Optional[str] = None,
657 recursive: bool = False,
658 nesting: bool = False,
659 ) -> CTE:
660 r"""Return the full SELECT statement represented by this
661 :class:`_query.Query` represented as a common table expression (CTE).
663 Parameters and usage are the same as those of the
664 :meth:`_expression.SelectBase.cte` method; see that method for
665 further details.
667 Here is the `PostgreSQL WITH
668 RECURSIVE example
669 <https://www.postgresql.org/docs/current/static/queries-with.html>`_.
670 Note that, in this example, the ``included_parts`` cte and the
671 ``incl_alias`` alias of it are Core selectables, which
672 means the columns are accessed via the ``.c.`` attribute. The
673 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
674 ``Part`` entity, so column-mapped attributes are available
675 directly::
677 from sqlalchemy.orm import aliased
680 class Part(Base):
681 __tablename__ = "part"
682 part = Column(String, primary_key=True)
683 sub_part = Column(String, primary_key=True)
684 quantity = Column(Integer)
687 included_parts = (
688 session.query(Part.sub_part, Part.part, Part.quantity)
689 .filter(Part.part == "our part")
690 .cte(name="included_parts", recursive=True)
691 )
693 incl_alias = aliased(included_parts, name="pr")
694 parts_alias = aliased(Part, name="p")
695 included_parts = included_parts.union_all(
696 session.query(
697 parts_alias.sub_part, parts_alias.part, parts_alias.quantity
698 ).filter(parts_alias.part == incl_alias.c.sub_part)
699 )
701 q = session.query(
702 included_parts.c.sub_part,
703 func.sum(included_parts.c.quantity).label("total_quantity"),
704 ).group_by(included_parts.c.sub_part)
706 .. seealso::
708 :meth:`_sql.Select.cte` - v2 equivalent method.
710 """ # noqa: E501
711 return (
712 self.enable_eagerloads(False)
713 ._get_select_statement_only()
714 .cte(name=name, recursive=recursive, nesting=nesting)
715 )
717 def label(self, name: Optional[str]) -> Label[Any]:
718 """Return the full SELECT statement represented by this
719 :class:`_query.Query`, converted
720 to a scalar subquery with a label of the given name.
722 .. seealso::
724 :meth:`_sql.Select.label` - v2 comparable method.
726 """
728 return (
729 self.enable_eagerloads(False)
730 ._get_select_statement_only()
731 .label(name)
732 )
734 @overload
735 def as_scalar( # type: ignore[overload-overlap]
736 self: Query[Tuple[_MAYBE_ENTITY]],
737 ) -> ScalarSelect[_MAYBE_ENTITY]: ...
739 @overload
740 def as_scalar(
741 self: Query[Tuple[_NOT_ENTITY]],
742 ) -> ScalarSelect[_NOT_ENTITY]: ...
744 @overload
745 def as_scalar(self) -> ScalarSelect[Any]: ...
747 @util.deprecated(
748 "1.4",
749 "The :meth:`_query.Query.as_scalar` method is deprecated and will be "
750 "removed in a future release. Please refer to "
751 ":meth:`_query.Query.scalar_subquery`.",
752 )
753 def as_scalar(self) -> ScalarSelect[Any]:
754 """Return the full SELECT statement represented by this
755 :class:`_query.Query`, converted to a scalar subquery.
757 """
758 return self.scalar_subquery()
760 @overload
761 def scalar_subquery(
762 self: Query[Tuple[_MAYBE_ENTITY]],
763 ) -> ScalarSelect[Any]: ...
765 @overload
766 def scalar_subquery(
767 self: Query[Tuple[_NOT_ENTITY]],
768 ) -> ScalarSelect[_NOT_ENTITY]: ...
770 @overload
771 def scalar_subquery(self) -> ScalarSelect[Any]: ...
773 def scalar_subquery(self) -> ScalarSelect[Any]:
774 """Return the full SELECT statement represented by this
775 :class:`_query.Query`, converted to a scalar subquery.
777 Analogous to
778 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
780 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
781 method replaces the :meth:`_query.Query.as_scalar` method.
783 .. seealso::
785 :meth:`_sql.Select.scalar_subquery` - v2 comparable method.
787 """
789 return (
790 self.enable_eagerloads(False)
791 ._get_select_statement_only()
792 .scalar_subquery()
793 )
795 @property
796 def selectable(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
797 """Return the :class:`_expression.Select` object emitted by this
798 :class:`_query.Query`.
800 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
802 query.enable_eagerloads(False).with_labels().statement
804 """
805 return self.__clause_element__()
807 def __clause_element__(
808 self,
809 ) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
810 return (
811 self._with_compile_options(
812 _enable_eagerloads=False, _render_for_subquery=True
813 )
814 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
815 .statement
816 )
818 @overload
819 def only_return_tuples(
820 self: Query[_O], value: Literal[True]
821 ) -> RowReturningQuery[Tuple[_O]]: ...
823 @overload
824 def only_return_tuples(
825 self: Query[_O], value: Literal[False]
826 ) -> Query[_O]: ...
828 @_generative
829 def only_return_tuples(self, value: bool) -> Query[Any]:
830 """When set to True, the query results will always be a
831 :class:`.Row` object.
833 This can change a query that normally returns a single entity
834 as a scalar to return a :class:`.Row` result in all cases.
836 .. seealso::
838 :meth:`.Query.tuples` - returns tuples, but also at the typing
839 level will type results as ``Tuple``.
841 :meth:`_query.Query.is_single_entity`
843 :meth:`_engine.Result.tuples` - v2 comparable method.
845 """
846 self.load_options += dict(_only_return_tuples=value)
847 return self
849 @property
850 def is_single_entity(self) -> bool:
851 """Indicates if this :class:`_query.Query`
852 returns tuples or single entities.
854 Returns True if this query returns a single entity for each instance
855 in its result list, and False if this query returns a tuple of entities
856 for each result.
858 .. versionadded:: 1.3.11
860 .. seealso::
862 :meth:`_query.Query.only_return_tuples`
864 """
865 return (
866 not self.load_options._only_return_tuples
867 and len(self._raw_columns) == 1
868 and "parententity" in self._raw_columns[0]._annotations
869 and isinstance(
870 self._raw_columns[0]._annotations["parententity"],
871 ORMColumnsClauseRole,
872 )
873 )
875 @_generative
876 def enable_eagerloads(self, value: bool) -> Self:
877 """Control whether or not eager joins and subqueries are
878 rendered.
880 When set to False, the returned Query will not render
881 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
882 :func:`~sqlalchemy.orm.subqueryload` options
883 or mapper-level ``lazy='joined'``/``lazy='subquery'``
884 configurations.
886 This is used primarily when nesting the Query's
887 statement into a subquery or other
888 selectable, or when using :meth:`_query.Query.yield_per`.
890 """
891 self._compile_options += {"_enable_eagerloads": value}
892 return self
894 @_generative
895 def _with_compile_options(self, **opt: Any) -> Self:
896 self._compile_options += opt
897 return self
899 @util.became_legacy_20(
900 ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`",
901 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
902 "instead.",
903 )
904 def with_labels(self) -> Self:
905 return self.set_label_style(
906 SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL
907 )
909 apply_labels = with_labels
911 @property
912 def get_label_style(self) -> SelectLabelStyle:
913 """
914 Retrieve the current label style.
916 .. versionadded:: 1.4
918 .. seealso::
920 :meth:`_sql.Select.get_label_style` - v2 equivalent method.
922 """
923 return self._label_style
925 def set_label_style(self, style: SelectLabelStyle) -> Self:
926 """Apply column labels to the return value of Query.statement.
928 Indicates that this Query's `statement` accessor should return
929 a SELECT statement that applies labels to all columns in the
930 form <tablename>_<columnname>; this is commonly used to
931 disambiguate columns from multiple tables which have the same
932 name.
934 When the `Query` actually issues SQL to load rows, it always
935 uses column labeling.
937 .. note:: The :meth:`_query.Query.set_label_style` method *only* applies
938 the output of :attr:`_query.Query.statement`, and *not* to any of
939 the result-row invoking systems of :class:`_query.Query` itself,
940 e.g.
941 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
942 To execute
943 a query using :meth:`_query.Query.set_label_style`, invoke the
944 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
946 result = session.execute(
947 query.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).statement
948 )
950 .. versionadded:: 1.4
953 .. seealso::
955 :meth:`_sql.Select.set_label_style` - v2 equivalent method.
957 """ # noqa
958 if self._label_style is not style:
959 self = self._generate()
960 self._label_style = style
961 return self
963 @_generative
964 def enable_assertions(self, value: bool) -> Self:
965 """Control whether assertions are generated.
967 When set to False, the returned Query will
968 not assert its state before certain operations,
969 including that LIMIT/OFFSET has not been applied
970 when filter() is called, no criterion exists
971 when get() is called, and no "from_statement()"
972 exists when filter()/order_by()/group_by() etc.
973 is called. This more permissive mode is used by
974 custom Query subclasses to specify criterion or
975 other modifiers outside of the usual usage patterns.
977 Care should be taken to ensure that the usage
978 pattern is even possible. A statement applied
979 by from_statement() will override any criterion
980 set by filter() or order_by(), for example.
982 """
983 self._enable_assertions = value
984 return self
986 @property
987 def whereclause(self) -> Optional[ColumnElement[bool]]:
988 """A readonly attribute which returns the current WHERE criterion for
989 this Query.
991 This returned value is a SQL expression construct, or ``None`` if no
992 criterion has been established.
994 .. seealso::
996 :attr:`_sql.Select.whereclause` - v2 equivalent property.
998 """
999 return BooleanClauseList._construct_for_whereclause(
1000 self._where_criteria
1001 )
1003 @_generative
1004 def _with_current_path(self, path: PathRegistry) -> Self:
1005 """indicate that this query applies to objects loaded
1006 within a certain path.
1008 Used by deferred loaders (see strategies.py) which transfer
1009 query options from an originating query to a newly generated
1010 query intended for the deferred load.
1012 """
1013 self._compile_options += {"_current_path": path}
1014 return self
1016 @_generative
1017 def yield_per(self, count: int) -> Self:
1018 r"""Yield only ``count`` rows at a time.
1020 The purpose of this method is when fetching very large result sets
1021 (> 10K rows), to batch results in sub-collections and yield them
1022 out partially, so that the Python interpreter doesn't need to declare
1023 very large areas of memory which is both time consuming and leads
1024 to excessive memory use. The performance from fetching hundreds of
1025 thousands of rows can often double when a suitable yield-per setting
1026 (e.g. approximately 1000) is used, even with DBAPIs that buffer
1027 rows (which are most).
1029 As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
1030 equivalent to using the ``yield_per`` execution option at the ORM
1031 level. See the section :ref:`orm_queryguide_yield_per` for further
1032 background on this option.
1034 .. seealso::
1036 :ref:`orm_queryguide_yield_per`
1038 """
1039 self.load_options += {"_yield_per": count}
1040 return self
1042 @util.became_legacy_20(
1043 ":meth:`_orm.Query.get`",
1044 alternative="The method is now available as :meth:`_orm.Session.get`",
1045 )
1046 def get(self, ident: _PKIdentityArgument) -> Optional[_T]:
1047 """Return an instance based on the given primary key identifier,
1048 or ``None`` if not found.
1050 E.g.::
1052 my_user = session.query(User).get(5)
1054 some_object = session.query(VersionedFoo).get((5, 10))
1056 some_object = session.query(VersionedFoo).get({"id": 5, "version_id": 10})
1058 :meth:`_query.Query.get` is special in that it provides direct
1059 access to the identity map of the owning :class:`.Session`.
1060 If the given primary key identifier is present
1061 in the local identity map, the object is returned
1062 directly from this collection and no SQL is emitted,
1063 unless the object has been marked fully expired.
1064 If not present,
1065 a SELECT is performed in order to locate the object.
1067 :meth:`_query.Query.get` also will perform a check if
1068 the object is present in the identity map and
1069 marked as expired - a SELECT
1070 is emitted to refresh the object as well as to
1071 ensure that the row is still present.
1072 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
1074 :meth:`_query.Query.get` is only used to return a single
1075 mapped instance, not multiple instances or
1076 individual column constructs, and strictly
1077 on a single primary key value. The originating
1078 :class:`_query.Query` must be constructed in this way,
1079 i.e. against a single mapped entity,
1080 with no additional filtering criterion. Loading
1081 options via :meth:`_query.Query.options` may be applied
1082 however, and will be used if the object is not
1083 yet locally present.
1085 :param ident: A scalar, tuple, or dictionary representing the
1086 primary key. For a composite (e.g. multiple column) primary key,
1087 a tuple or dictionary should be passed.
1089 For a single-column primary key, the scalar calling form is typically
1090 the most expedient. If the primary key of a row is the value "5",
1091 the call looks like::
1093 my_object = query.get(5)
1095 The tuple form contains primary key values typically in
1096 the order in which they correspond to the mapped
1097 :class:`_schema.Table`
1098 object's primary key columns, or if the
1099 :paramref:`_orm.Mapper.primary_key` configuration parameter were
1100 used, in
1101 the order used for that parameter. For example, if the primary key
1102 of a row is represented by the integer
1103 digits "5, 10" the call would look like::
1105 my_object = query.get((5, 10))
1107 The dictionary form should include as keys the mapped attribute names
1108 corresponding to each element of the primary key. If the mapped class
1109 has the attributes ``id``, ``version_id`` as the attributes which
1110 store the object's primary key value, the call would look like::
1112 my_object = query.get({"id": 5, "version_id": 10})
1114 .. versionadded:: 1.3 the :meth:`_query.Query.get`
1115 method now optionally
1116 accepts a dictionary of attribute names to values in order to
1117 indicate a primary key identifier.
1120 :return: The object instance, or ``None``.
1122 """ # noqa: E501
1123 self._no_criterion_assertion("get", order_by=False, distinct=False)
1125 # we still implement _get_impl() so that baked query can override
1126 # it
1127 return self._get_impl(ident, loading.load_on_pk_identity)
1129 def _get_impl(
1130 self,
1131 primary_key_identity: _PKIdentityArgument,
1132 db_load_fn: Callable[..., Any],
1133 identity_token: Optional[Any] = None,
1134 ) -> Optional[Any]:
1135 mapper = self._only_full_mapper_zero("get")
1136 return self.session._get_impl(
1137 mapper,
1138 primary_key_identity,
1139 db_load_fn,
1140 populate_existing=self.load_options._populate_existing,
1141 with_for_update=self._for_update_arg,
1142 options=self._with_options,
1143 identity_token=identity_token,
1144 execution_options=self._execution_options,
1145 )
1147 @property
1148 def lazy_loaded_from(self) -> Optional[InstanceState[Any]]:
1149 """An :class:`.InstanceState` that is using this :class:`_query.Query`
1150 for a lazy load operation.
1152 .. deprecated:: 1.4 This attribute should be viewed via the
1153 :attr:`.ORMExecuteState.lazy_loaded_from` attribute, within
1154 the context of the :meth:`.SessionEvents.do_orm_execute`
1155 event.
1157 .. seealso::
1159 :attr:`.ORMExecuteState.lazy_loaded_from`
1161 """
1162 return self.load_options._lazy_loaded_from # type: ignore
1164 @property
1165 def _current_path(self) -> PathRegistry:
1166 return self._compile_options._current_path # type: ignore
1168 @_generative
1169 def correlate(
1170 self,
1171 *fromclauses: Union[Literal[None, False], _FromClauseArgument],
1172 ) -> Self:
1173 """Return a :class:`.Query` construct which will correlate the given
1174 FROM clauses to that of an enclosing :class:`.Query` or
1175 :func:`~.expression.select`.
1177 The method here accepts mapped classes, :func:`.aliased` constructs,
1178 and :class:`_orm.Mapper` constructs as arguments, which are resolved
1179 into expression constructs, in addition to appropriate expression
1180 constructs.
1182 The correlation arguments are ultimately passed to
1183 :meth:`_expression.Select.correlate`
1184 after coercion to expression constructs.
1186 The correlation arguments take effect in such cases
1187 as when :meth:`_query.Query.from_self` is used, or when
1188 a subquery as returned by :meth:`_query.Query.subquery` is
1189 embedded in another :func:`_expression.select` construct.
1191 .. seealso::
1193 :meth:`_sql.Select.correlate` - v2 equivalent method.
1195 """
1197 self._auto_correlate = False
1198 if fromclauses and fromclauses[0] in {None, False}:
1199 self._correlate = ()
1200 else:
1201 self._correlate = self._correlate + tuple(
1202 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
1203 )
1204 return self
1206 @_generative
1207 def autoflush(self, setting: bool) -> Self:
1208 """Return a Query with a specific 'autoflush' setting.
1210 As of SQLAlchemy 1.4, the :meth:`_orm.Query.autoflush` method
1211 is equivalent to using the ``autoflush`` execution option at the
1212 ORM level. See the section :ref:`orm_queryguide_autoflush` for
1213 further background on this option.
1215 """
1216 self.load_options += {"_autoflush": setting}
1217 return self
1219 @_generative
1220 def populate_existing(self) -> Self:
1221 """Return a :class:`_query.Query`
1222 that will expire and refresh all instances
1223 as they are loaded, or reused from the current :class:`.Session`.
1225 As of SQLAlchemy 1.4, the :meth:`_orm.Query.populate_existing` method
1226 is equivalent to using the ``populate_existing`` execution option at
1227 the ORM level. See the section :ref:`orm_queryguide_populate_existing`
1228 for further background on this option.
1230 """
1231 self.load_options += {"_populate_existing": True}
1232 return self
1234 @_generative
1235 def _with_invoke_all_eagers(self, value: bool) -> Self:
1236 """Set the 'invoke all eagers' flag which causes joined- and
1237 subquery loaders to traverse into already-loaded related objects
1238 and collections.
1240 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1242 """
1243 self.load_options += {"_invoke_all_eagers": value}
1244 return self
1246 @util.became_legacy_20(
1247 ":meth:`_orm.Query.with_parent`",
1248 alternative="Use the :func:`_orm.with_parent` standalone construct.",
1249 )
1250 @util.preload_module("sqlalchemy.orm.relationships")
1251 def with_parent(
1252 self,
1253 instance: object,
1254 property: Optional[ # noqa: A002
1255 attributes.QueryableAttribute[Any]
1256 ] = None,
1257 from_entity: Optional[_ExternalEntityType[Any]] = None,
1258 ) -> Self:
1259 """Add filtering criterion that relates the given instance
1260 to a child object or collection, using its attribute state
1261 as well as an established :func:`_orm.relationship()`
1262 configuration.
1264 The method uses the :func:`.with_parent` function to generate
1265 the clause, the result of which is passed to
1266 :meth:`_query.Query.filter`.
1268 Parameters are the same as :func:`.with_parent`, with the exception
1269 that the given property can be None, in which case a search is
1270 performed against this :class:`_query.Query` object's target mapper.
1272 :param instance:
1273 An instance which has some :func:`_orm.relationship`.
1275 :param property:
1276 Class bound attribute which indicates
1277 what relationship from the instance should be used to reconcile the
1278 parent/child relationship.
1280 :param from_entity:
1281 Entity in which to consider as the left side. This defaults to the
1282 "zero" entity of the :class:`_query.Query` itself.
1284 """
1285 relationships = util.preloaded.orm_relationships
1287 if from_entity:
1288 entity_zero = inspect(from_entity)
1289 else:
1290 entity_zero = _legacy_filter_by_entity_zero(self)
1291 if property is None:
1292 # TODO: deprecate, property has to be supplied
1293 mapper = object_mapper(instance)
1295 for prop in mapper.iterate_properties:
1296 if (
1297 isinstance(prop, relationships.RelationshipProperty)
1298 and prop.mapper is entity_zero.mapper # type: ignore
1299 ):
1300 property = prop # type: ignore # noqa: A001
1301 break
1302 else:
1303 raise sa_exc.InvalidRequestError(
1304 "Could not locate a property which relates instances "
1305 "of class '%s' to instances of class '%s'"
1306 % (
1307 entity_zero.mapper.class_.__name__, # type: ignore
1308 instance.__class__.__name__,
1309 )
1310 )
1312 return self.filter(
1313 with_parent(
1314 instance,
1315 property, # type: ignore
1316 entity_zero.entity, # type: ignore
1317 )
1318 )
1320 @_generative
1321 def add_entity(
1322 self,
1323 entity: _EntityType[Any],
1324 alias: Optional[Union[Alias, Subquery]] = None,
1325 ) -> Query[Any]:
1326 """add a mapped entity to the list of result columns
1327 to be returned.
1329 .. seealso::
1331 :meth:`_sql.Select.add_columns` - v2 comparable method.
1332 """
1334 if alias is not None:
1335 # TODO: deprecate
1336 entity = AliasedClass(entity, alias)
1338 self._raw_columns = list(self._raw_columns)
1340 self._raw_columns.append(
1341 coercions.expect(
1342 roles.ColumnsClauseRole, entity, apply_propagate_attrs=self
1343 )
1344 )
1345 return self
1347 @_generative
1348 def with_session(self, session: Session) -> Self:
1349 """Return a :class:`_query.Query` that will use the given
1350 :class:`.Session`.
1352 While the :class:`_query.Query`
1353 object is normally instantiated using the
1354 :meth:`.Session.query` method, it is legal to build the
1355 :class:`_query.Query`
1356 directly without necessarily using a :class:`.Session`. Such a
1357 :class:`_query.Query` object, or any :class:`_query.Query`
1358 already associated
1359 with a different :class:`.Session`, can produce a new
1360 :class:`_query.Query`
1361 object associated with a target session using this method::
1363 from sqlalchemy.orm import Query
1365 query = Query([MyClass]).filter(MyClass.id == 5)
1367 result = query.with_session(my_session).one()
1369 """
1371 self.session = session
1372 return self
1374 def _legacy_from_self(
1375 self, *entities: _ColumnsClauseArgument[Any]
1376 ) -> Self:
1377 # used for query.count() as well as for the same
1378 # function in BakedQuery, as well as some old tests in test_baked.py.
1380 fromclause = (
1381 self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
1382 .correlate(None)
1383 .subquery()
1384 ._anonymous_fromclause()
1385 )
1387 q = self._from_selectable(fromclause)
1389 if entities:
1390 q._set_entities(entities)
1391 return q
1393 @_generative
1394 def _set_enable_single_crit(self, val: bool) -> Self:
1395 self._compile_options += {"_enable_single_crit": val}
1396 return self
1398 @_generative
1399 def _from_selectable(
1400 self, fromclause: FromClause, set_entity_from: bool = True
1401 ) -> Self:
1402 for attr in (
1403 "_where_criteria",
1404 "_order_by_clauses",
1405 "_group_by_clauses",
1406 "_limit_clause",
1407 "_offset_clause",
1408 "_last_joined_entity",
1409 "_setup_joins",
1410 "_memoized_select_entities",
1411 "_distinct",
1412 "_distinct_on",
1413 "_having_criteria",
1414 "_prefixes",
1415 "_suffixes",
1416 ):
1417 self.__dict__.pop(attr, None)
1418 self._set_select_from([fromclause], set_entity_from)
1419 self._compile_options += {
1420 "_enable_single_crit": False,
1421 }
1423 return self
1425 @util.deprecated(
1426 "1.4",
1427 ":meth:`_query.Query.values` "
1428 "is deprecated and will be removed in a "
1429 "future release. Please use :meth:`_query.Query.with_entities`",
1430 )
1431 def values(self, *columns: _ColumnsClauseArgument[Any]) -> Iterable[Any]:
1432 """Return an iterator yielding result tuples corresponding
1433 to the given list of columns
1435 """
1436 return self._values_no_warn(*columns)
1438 _values = values
1440 def _values_no_warn(
1441 self, *columns: _ColumnsClauseArgument[Any]
1442 ) -> Iterable[Any]:
1443 if not columns:
1444 return iter(())
1445 q = self._clone().enable_eagerloads(False)
1446 q._set_entities(columns)
1447 if not q.load_options._yield_per:
1448 q.load_options += {"_yield_per": 10}
1449 return iter(q)
1451 @util.deprecated(
1452 "1.4",
1453 ":meth:`_query.Query.value` "
1454 "is deprecated and will be removed in a "
1455 "future release. Please use :meth:`_query.Query.with_entities` "
1456 "in combination with :meth:`_query.Query.scalar`",
1457 )
1458 def value(self, column: _ColumnExpressionArgument[Any]) -> Any:
1459 """Return a scalar result corresponding to the given
1460 column expression.
1462 """
1463 try:
1464 return next(self._values_no_warn(column))[0] # type: ignore
1465 except StopIteration:
1466 return None
1468 @overload
1469 def with_entities(self, _entity: _EntityType[_O]) -> Query[_O]: ...
1471 @overload
1472 def with_entities(
1473 self,
1474 _colexpr: roles.TypedColumnsClauseRole[_T],
1475 ) -> RowReturningQuery[Tuple[_T]]: ...
1477 # START OVERLOADED FUNCTIONS self.with_entities RowReturningQuery 2-8
1479 # code within this block is **programmatically,
1480 # statically generated** by tools/generate_tuple_map_overloads.py
1482 @overload
1483 def with_entities(
1484 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1485 ) -> RowReturningQuery[Tuple[_T0, _T1]]: ...
1487 @overload
1488 def with_entities(
1489 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1490 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2]]: ...
1492 @overload
1493 def with_entities(
1494 self,
1495 __ent0: _TCCA[_T0],
1496 __ent1: _TCCA[_T1],
1497 __ent2: _TCCA[_T2],
1498 __ent3: _TCCA[_T3],
1499 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3]]: ...
1501 @overload
1502 def with_entities(
1503 self,
1504 __ent0: _TCCA[_T0],
1505 __ent1: _TCCA[_T1],
1506 __ent2: _TCCA[_T2],
1507 __ent3: _TCCA[_T3],
1508 __ent4: _TCCA[_T4],
1509 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1511 @overload
1512 def with_entities(
1513 self,
1514 __ent0: _TCCA[_T0],
1515 __ent1: _TCCA[_T1],
1516 __ent2: _TCCA[_T2],
1517 __ent3: _TCCA[_T3],
1518 __ent4: _TCCA[_T4],
1519 __ent5: _TCCA[_T5],
1520 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1522 @overload
1523 def with_entities(
1524 self,
1525 __ent0: _TCCA[_T0],
1526 __ent1: _TCCA[_T1],
1527 __ent2: _TCCA[_T2],
1528 __ent3: _TCCA[_T3],
1529 __ent4: _TCCA[_T4],
1530 __ent5: _TCCA[_T5],
1531 __ent6: _TCCA[_T6],
1532 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1534 @overload
1535 def with_entities(
1536 self,
1537 __ent0: _TCCA[_T0],
1538 __ent1: _TCCA[_T1],
1539 __ent2: _TCCA[_T2],
1540 __ent3: _TCCA[_T3],
1541 __ent4: _TCCA[_T4],
1542 __ent5: _TCCA[_T5],
1543 __ent6: _TCCA[_T6],
1544 __ent7: _TCCA[_T7],
1545 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...
1547 # END OVERLOADED FUNCTIONS self.with_entities
1549 @overload
1550 def with_entities(
1551 self, *entities: _ColumnsClauseArgument[Any]
1552 ) -> Query[Any]: ...
1554 @_generative
1555 def with_entities(
1556 self, *entities: _ColumnsClauseArgument[Any], **__kw: Any
1557 ) -> Query[Any]:
1558 r"""Return a new :class:`_query.Query`
1559 replacing the SELECT list with the
1560 given entities.
1562 e.g.::
1564 # Users, filtered on some arbitrary criterion
1565 # and then ordered by related email address
1566 q = (
1567 session.query(User)
1568 .join(User.address)
1569 .filter(User.name.like("%ed%"))
1570 .order_by(Address.email)
1571 )
1573 # given *only* User.id==5, Address.email, and 'q', what
1574 # would the *next* User in the result be ?
1575 subq = (
1576 q.with_entities(Address.email)
1577 .order_by(None)
1578 .filter(User.id == 5)
1579 .subquery()
1580 )
1581 q = q.join((subq, subq.c.email < Address.email)).limit(1)
1583 .. seealso::
1585 :meth:`_sql.Select.with_only_columns` - v2 comparable method.
1586 """
1587 if __kw:
1588 raise _no_kw()
1590 # Query has all the same fields as Select for this operation
1591 # this could in theory be based on a protocol but not sure if it's
1592 # worth it
1593 _MemoizedSelectEntities._generate_for_statement(self) # type: ignore
1594 self._set_entities(entities)
1595 return self
1597 @_generative
1598 def add_columns(
1599 self, *column: _ColumnExpressionArgument[Any]
1600 ) -> Query[Any]:
1601 """Add one or more column expressions to the list
1602 of result columns to be returned.
1604 .. seealso::
1606 :meth:`_sql.Select.add_columns` - v2 comparable method.
1607 """
1609 self._raw_columns = list(self._raw_columns)
1611 self._raw_columns.extend(
1612 coercions.expect(
1613 roles.ColumnsClauseRole,
1614 c,
1615 apply_propagate_attrs=self,
1616 post_inspect=True,
1617 )
1618 for c in column
1619 )
1620 return self
1622 @util.deprecated(
1623 "1.4",
1624 ":meth:`_query.Query.add_column` "
1625 "is deprecated and will be removed in a "
1626 "future release. Please use :meth:`_query.Query.add_columns`",
1627 )
1628 def add_column(self, column: _ColumnExpressionArgument[Any]) -> Query[Any]:
1629 """Add a column expression to the list of result columns to be
1630 returned.
1632 """
1633 return self.add_columns(column)
1635 @_generative
1636 def options(self, *args: ExecutableOption) -> Self:
1637 """Return a new :class:`_query.Query` object,
1638 applying the given list of
1639 mapper options.
1641 Most supplied options regard changing how column- and
1642 relationship-mapped attributes are loaded.
1644 .. seealso::
1646 :ref:`loading_columns`
1648 :ref:`relationship_loader_options`
1650 """
1652 opts = tuple(util.flatten_iterator(args))
1653 if self._compile_options._current_path:
1654 # opting for lower method overhead for the checks
1655 for opt in opts:
1656 if not opt._is_core and opt._is_legacy_option: # type: ignore
1657 opt.process_query_conditionally(self) # type: ignore
1658 else:
1659 for opt in opts:
1660 if not opt._is_core and opt._is_legacy_option: # type: ignore
1661 opt.process_query(self) # type: ignore
1663 self._with_options += opts
1664 return self
1666 def with_transformation(
1667 self, fn: Callable[[Query[Any]], Query[Any]]
1668 ) -> Query[Any]:
1669 """Return a new :class:`_query.Query` object transformed by
1670 the given function.
1672 E.g.::
1674 def filter_something(criterion):
1675 def transform(q):
1676 return q.filter(criterion)
1678 return transform
1681 q = q.with_transformation(filter_something(x == 5))
1683 This allows ad-hoc recipes to be created for :class:`_query.Query`
1684 objects.
1686 """
1687 return fn(self)
1689 def get_execution_options(self) -> _ImmutableExecuteOptions:
1690 """Get the non-SQL options which will take effect during execution.
1692 .. versionadded:: 1.3
1694 .. seealso::
1696 :meth:`_query.Query.execution_options`
1698 :meth:`_sql.Select.get_execution_options` - v2 comparable method.
1700 """
1701 return self._execution_options
1703 @overload
1704 def execution_options(
1705 self,
1706 *,
1707 compiled_cache: Optional[CompiledCacheType] = ...,
1708 logging_token: str = ...,
1709 isolation_level: IsolationLevel = ...,
1710 no_parameters: bool = False,
1711 stream_results: bool = False,
1712 max_row_buffer: int = ...,
1713 yield_per: int = ...,
1714 insertmanyvalues_page_size: int = ...,
1715 schema_translate_map: Optional[SchemaTranslateMapType] = ...,
1716 populate_existing: bool = False,
1717 autoflush: bool = False,
1718 preserve_rowcount: bool = False,
1719 **opt: Any,
1720 ) -> Self: ...
1722 @overload
1723 def execution_options(self, **opt: Any) -> Self: ...
1725 @_generative
1726 def execution_options(self, **kwargs: Any) -> Self:
1727 """Set non-SQL options which take effect during execution.
1729 Options allowed here include all of those accepted by
1730 :meth:`_engine.Connection.execution_options`, as well as a series
1731 of ORM specific options:
1733 ``populate_existing=True`` - equivalent to using
1734 :meth:`_orm.Query.populate_existing`
1736 ``autoflush=True|False`` - equivalent to using
1737 :meth:`_orm.Query.autoflush`
1739 ``yield_per=<value>`` - equivalent to using
1740 :meth:`_orm.Query.yield_per`
1742 Note that the ``stream_results`` execution option is enabled
1743 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1744 method or execution option is used.
1746 .. versionadded:: 1.4 - added ORM options to
1747 :meth:`_orm.Query.execution_options`
1749 The execution options may also be specified on a per execution basis
1750 when using :term:`2.0 style` queries via the
1751 :paramref:`_orm.Session.execution_options` parameter.
1753 .. warning:: The
1754 :paramref:`_engine.Connection.execution_options.stream_results`
1755 parameter should not be used at the level of individual ORM
1756 statement executions, as the :class:`_orm.Session` will not track
1757 objects from different schema translate maps within a single
1758 session. For multiple schema translate maps within the scope of a
1759 single :class:`_orm.Session`, see :ref:`examples_sharding`.
1762 .. seealso::
1764 :ref:`engine_stream_results`
1766 :meth:`_query.Query.get_execution_options`
1768 :meth:`_sql.Select.execution_options` - v2 equivalent method.
1770 """
1771 self._execution_options = self._execution_options.union(kwargs)
1772 return self
1774 @_generative
1775 def with_for_update(
1776 self,
1777 *,
1778 nowait: bool = False,
1779 read: bool = False,
1780 of: Optional[_ForUpdateOfArgument] = None,
1781 skip_locked: bool = False,
1782 key_share: bool = False,
1783 ) -> Self:
1784 """return a new :class:`_query.Query`
1785 with the specified options for the
1786 ``FOR UPDATE`` clause.
1788 The behavior of this method is identical to that of
1789 :meth:`_expression.GenerativeSelect.with_for_update`.
1790 When called with no arguments,
1791 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1792 appended. When additional arguments are specified, backend-specific
1793 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1794 can take effect.
1796 E.g.::
1798 q = (
1799 sess.query(User)
1800 .populate_existing()
1801 .with_for_update(nowait=True, of=User)
1802 )
1804 The above query on a PostgreSQL backend will render like:
1806 .. sourcecode:: sql
1808 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1810 .. warning::
1812 Using ``with_for_update`` in the context of eager loading
1813 relationships is not officially supported or recommended by
1814 SQLAlchemy and may not work with certain queries on various
1815 database backends. When ``with_for_update`` is successfully used
1816 with a query that involves :func:`_orm.joinedload`, SQLAlchemy will
1817 attempt to emit SQL that locks all involved tables.
1819 .. note:: It is generally a good idea to combine the use of the
1820 :meth:`_orm.Query.populate_existing` method when using the
1821 :meth:`_orm.Query.with_for_update` method. The purpose of
1822 :meth:`_orm.Query.populate_existing` is to force all the data read
1823 from the SELECT to be populated into the ORM objects returned,
1824 even if these objects are already in the :term:`identity map`.
1826 .. seealso::
1828 :meth:`_expression.GenerativeSelect.with_for_update`
1829 - Core level method with
1830 full argument and behavioral description.
1832 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1833 objects already loaded in the identity map.
1835 """ # noqa: E501
1837 self._for_update_arg = ForUpdateArg(
1838 read=read,
1839 nowait=nowait,
1840 of=of,
1841 skip_locked=skip_locked,
1842 key_share=key_share,
1843 )
1844 return self
1846 @_generative
1847 def params(
1848 self, __params: Optional[Dict[str, Any]] = None, **kw: Any
1849 ) -> Self:
1850 r"""Add values for bind parameters which may have been
1851 specified in filter().
1853 Parameters may be specified using \**kwargs, or optionally a single
1854 dictionary as the first positional argument. The reason for both is
1855 that \**kwargs is convenient, however some parameter dictionaries
1856 contain unicode keys in which case \**kwargs cannot be used.
1858 """
1859 if __params:
1860 kw.update(__params)
1861 self._params = self._params.union(kw)
1862 return self
1864 def where(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1865 """A synonym for :meth:`.Query.filter`.
1867 .. versionadded:: 1.4
1869 .. seealso::
1871 :meth:`_sql.Select.where` - v2 equivalent method.
1873 """
1874 return self.filter(*criterion)
1876 @_generative
1877 @_assertions(_no_statement_condition, _no_limit_offset)
1878 def filter(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1879 r"""Apply the given filtering criterion to a copy
1880 of this :class:`_query.Query`, using SQL expressions.
1882 e.g.::
1884 session.query(MyClass).filter(MyClass.name == "some name")
1886 Multiple criteria may be specified as comma separated; the effect
1887 is that they will be joined together using the :func:`.and_`
1888 function::
1890 session.query(MyClass).filter(MyClass.name == "some name", MyClass.id > 5)
1892 The criterion is any SQL expression object applicable to the
1893 WHERE clause of a select. String expressions are coerced
1894 into SQL expression constructs via the :func:`_expression.text`
1895 construct.
1897 .. seealso::
1899 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1901 :meth:`_sql.Select.where` - v2 equivalent method.
1903 """ # noqa: E501
1904 for crit in list(criterion):
1905 crit = coercions.expect(
1906 roles.WhereHavingRole, crit, apply_propagate_attrs=self
1907 )
1909 self._where_criteria += (crit,)
1910 return self
1912 @util.memoized_property
1913 def _last_joined_entity(
1914 self,
1915 ) -> Optional[Union[_InternalEntityType[Any], _JoinTargetElement]]:
1916 if self._setup_joins:
1917 return _determine_last_joined_entity(
1918 self._setup_joins,
1919 )
1920 else:
1921 return None
1923 def _filter_by_zero(self) -> Any:
1924 """for the filter_by() method, return the target entity for which
1925 we will attempt to derive an expression from based on string name.
1927 """
1929 if self._setup_joins:
1930 _last_joined_entity = self._last_joined_entity
1931 if _last_joined_entity is not None:
1932 return _last_joined_entity
1934 # discussion related to #7239
1935 # special check determines if we should try to derive attributes
1936 # for filter_by() from the "from object", i.e., if the user
1937 # called query.select_from(some selectable).filter_by(some_attr=value).
1938 # We don't want to do that in the case that methods like
1939 # from_self(), select_entity_from(), or a set op like union() were
1940 # called; while these methods also place a
1941 # selectable in the _from_obj collection, they also set up
1942 # the _set_base_alias boolean which turns on the whole "adapt the
1943 # entity to this selectable" thing, meaning the query still continues
1944 # to construct itself in terms of the lead entity that was passed
1945 # to query(), e.g. query(User).from_self() is still in terms of User,
1946 # and not the subquery that from_self() created. This feature of
1947 # "implicitly adapt all occurrences of entity X to some arbitrary
1948 # subquery" is the main thing I am trying to do away with in 2.0 as
1949 # users should now used aliased() for that, but I can't entirely get
1950 # rid of it due to query.union() and other set ops relying upon it.
1951 #
1952 # compare this to the base Select()._filter_by_zero() which can
1953 # just return self._from_obj[0] if present, because there is no
1954 # "_set_base_alias" feature.
1955 #
1956 # IOW, this conditional essentially detects if
1957 # "select_from(some_selectable)" has been called, as opposed to
1958 # "select_entity_from()", "from_self()"
1959 # or "union() / some_set_op()".
1960 if self._from_obj and not self._compile_options._set_base_alias:
1961 return self._from_obj[0]
1963 return self._raw_columns[0]
1965 def filter_by(self, **kwargs: Any) -> Self:
1966 r"""Apply the given filtering criterion to a copy
1967 of this :class:`_query.Query`, using keyword expressions.
1969 e.g.::
1971 session.query(MyClass).filter_by(name="some name")
1973 Multiple criteria may be specified as comma separated; the effect
1974 is that they will be joined together using the :func:`.and_`
1975 function::
1977 session.query(MyClass).filter_by(name="some name", id=5)
1979 The keyword expressions are extracted from the primary
1980 entity of the query, or the last entity that was the
1981 target of a call to :meth:`_query.Query.join`.
1983 .. seealso::
1985 :meth:`_query.Query.filter` - filter on SQL expressions.
1987 :meth:`_sql.Select.filter_by` - v2 comparable method.
1989 """
1990 from_entity = self._filter_by_zero()
1992 clauses = [
1993 _entity_namespace_key(from_entity, key) == value
1994 for key, value in kwargs.items()
1995 ]
1996 return self.filter(*clauses)
1998 @_generative
1999 def order_by(
2000 self,
2001 __first: Union[
2002 Literal[None, False, _NoArg.NO_ARG],
2003 _ColumnExpressionOrStrLabelArgument[Any],
2004 ] = _NoArg.NO_ARG,
2005 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2006 ) -> Self:
2007 """Apply one or more ORDER BY criteria to the query and return
2008 the newly resulting :class:`_query.Query`.
2010 e.g.::
2012 q = session.query(Entity).order_by(Entity.id, Entity.name)
2014 Calling this method multiple times is equivalent to calling it once
2015 with all the clauses concatenated. All existing ORDER BY criteria may
2016 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
2017 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
2019 # will erase all ORDER BY and ORDER BY new_col alone
2020 q = q.order_by(None).order_by(new_col)
2022 .. seealso::
2024 These sections describe ORDER BY in terms of :term:`2.0 style`
2025 invocation but apply to :class:`_orm.Query` as well:
2027 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
2029 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2031 :meth:`_sql.Select.order_by` - v2 equivalent method.
2033 """
2035 for assertion in (self._no_statement_condition, self._no_limit_offset):
2036 assertion("order_by")
2038 if not clauses and (__first is None or __first is False):
2039 self._order_by_clauses = ()
2040 elif __first is not _NoArg.NO_ARG:
2041 criterion = tuple(
2042 coercions.expect(roles.OrderByRole, clause)
2043 for clause in (__first,) + clauses
2044 )
2045 self._order_by_clauses += criterion
2047 return self
2049 @_generative
2050 def group_by(
2051 self,
2052 __first: Union[
2053 Literal[None, False, _NoArg.NO_ARG],
2054 _ColumnExpressionOrStrLabelArgument[Any],
2055 ] = _NoArg.NO_ARG,
2056 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2057 ) -> Self:
2058 """Apply one or more GROUP BY criterion to the query and return
2059 the newly resulting :class:`_query.Query`.
2061 All existing GROUP BY settings can be suppressed by
2062 passing ``None`` - this will suppress any GROUP BY configured
2063 on mappers as well.
2065 .. seealso::
2067 These sections describe GROUP BY in terms of :term:`2.0 style`
2068 invocation but apply to :class:`_orm.Query` as well:
2070 :ref:`tutorial_group_by_w_aggregates` - in the
2071 :ref:`unified_tutorial`
2073 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2075 :meth:`_sql.Select.group_by` - v2 equivalent method.
2077 """
2079 for assertion in (self._no_statement_condition, self._no_limit_offset):
2080 assertion("group_by")
2082 if not clauses and (__first is None or __first is False):
2083 self._group_by_clauses = ()
2084 elif __first is not _NoArg.NO_ARG:
2085 criterion = tuple(
2086 coercions.expect(roles.GroupByRole, clause)
2087 for clause in (__first,) + clauses
2088 )
2089 self._group_by_clauses += criterion
2090 return self
2092 @_generative
2093 @_assertions(_no_statement_condition, _no_limit_offset)
2094 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:
2095 r"""Apply a HAVING criterion to the query and return the
2096 newly resulting :class:`_query.Query`.
2098 :meth:`_query.Query.having` is used in conjunction with
2099 :meth:`_query.Query.group_by`.
2101 HAVING criterion makes it possible to use filters on aggregate
2102 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
2104 q = (
2105 session.query(User.id)
2106 .join(User.addresses)
2107 .group_by(User.id)
2108 .having(func.count(Address.id) > 2)
2109 )
2111 .. seealso::
2113 :meth:`_sql.Select.having` - v2 equivalent method.
2115 """
2117 for criterion in having:
2118 having_criteria = coercions.expect(
2119 roles.WhereHavingRole, criterion
2120 )
2121 self._having_criteria += (having_criteria,)
2122 return self
2124 def _set_op(self, expr_fn: Any, *q: Query[Any]) -> Self:
2125 list_of_queries = (self,) + q
2126 return self._from_selectable(expr_fn(*(list_of_queries)).subquery())
2128 def union(self, *q: Query[Any]) -> Self:
2129 """Produce a UNION of this Query against one or more queries.
2131 e.g.::
2133 q1 = sess.query(SomeClass).filter(SomeClass.foo == "bar")
2134 q2 = sess.query(SomeClass).filter(SomeClass.bar == "foo")
2136 q3 = q1.union(q2)
2138 The method accepts multiple Query objects so as to control
2139 the level of nesting. A series of ``union()`` calls such as::
2141 x.union(y).union(z).all()
2143 will nest on each ``union()``, and produces:
2145 .. sourcecode:: sql
2147 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
2148 SELECT * FROM y) UNION SELECT * FROM Z)
2150 Whereas::
2152 x.union(y, z).all()
2154 produces:
2156 .. sourcecode:: sql
2158 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
2159 SELECT * FROM Z)
2161 Note that many database backends do not allow ORDER BY to
2162 be rendered on a query called within UNION, EXCEPT, etc.
2163 To disable all ORDER BY clauses including those configured
2164 on mappers, issue ``query.order_by(None)`` - the resulting
2165 :class:`_query.Query` object will not render ORDER BY within
2166 its SELECT statement.
2168 .. seealso::
2170 :meth:`_sql.Select.union` - v2 equivalent method.
2172 """
2173 return self._set_op(expression.union, *q)
2175 def union_all(self, *q: Query[Any]) -> Self:
2176 """Produce a UNION ALL of this Query against one or more queries.
2178 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2179 that method for usage examples.
2181 .. seealso::
2183 :meth:`_sql.Select.union_all` - v2 equivalent method.
2185 """
2186 return self._set_op(expression.union_all, *q)
2188 def intersect(self, *q: Query[Any]) -> Self:
2189 """Produce an INTERSECT of this Query against one or more queries.
2191 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2192 that method for usage examples.
2194 .. seealso::
2196 :meth:`_sql.Select.intersect` - v2 equivalent method.
2198 """
2199 return self._set_op(expression.intersect, *q)
2201 def intersect_all(self, *q: Query[Any]) -> Self:
2202 """Produce an INTERSECT ALL of this Query against one or more queries.
2204 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2205 that method for usage examples.
2207 .. seealso::
2209 :meth:`_sql.Select.intersect_all` - v2 equivalent method.
2211 """
2212 return self._set_op(expression.intersect_all, *q)
2214 def except_(self, *q: Query[Any]) -> Self:
2215 """Produce an EXCEPT of this Query against one or more queries.
2217 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2218 that method for usage examples.
2220 .. seealso::
2222 :meth:`_sql.Select.except_` - v2 equivalent method.
2224 """
2225 return self._set_op(expression.except_, *q)
2227 def except_all(self, *q: Query[Any]) -> Self:
2228 """Produce an EXCEPT ALL of this Query against one or more queries.
2230 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2231 that method for usage examples.
2233 .. seealso::
2235 :meth:`_sql.Select.except_all` - v2 equivalent method.
2237 """
2238 return self._set_op(expression.except_all, *q)
2240 @_generative
2241 @_assertions(_no_statement_condition, _no_limit_offset)
2242 def join(
2243 self,
2244 target: _JoinTargetArgument,
2245 onclause: Optional[_OnClauseArgument] = None,
2246 *,
2247 isouter: bool = False,
2248 full: bool = False,
2249 ) -> Self:
2250 r"""Create a SQL JOIN against this :class:`_query.Query`
2251 object's criterion
2252 and apply generatively, returning the newly resulting
2253 :class:`_query.Query`.
2255 **Simple Relationship Joins**
2257 Consider a mapping between two classes ``User`` and ``Address``,
2258 with a relationship ``User.addresses`` representing a collection
2259 of ``Address`` objects associated with each ``User``. The most
2260 common usage of :meth:`_query.Query.join`
2261 is to create a JOIN along this
2262 relationship, using the ``User.addresses`` attribute as an indicator
2263 for how this should occur::
2265 q = session.query(User).join(User.addresses)
2267 Where above, the call to :meth:`_query.Query.join` along
2268 ``User.addresses`` will result in SQL approximately equivalent to:
2270 .. sourcecode:: sql
2272 SELECT user.id, user.name
2273 FROM user JOIN address ON user.id = address.user_id
2275 In the above example we refer to ``User.addresses`` as passed to
2276 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2277 how the "ON" portion of the JOIN should be constructed.
2279 To construct a chain of joins, multiple :meth:`_query.Query.join`
2280 calls may be used. The relationship-bound attribute implies both
2281 the left and right side of the join at once::
2283 q = (
2284 session.query(User)
2285 .join(User.orders)
2286 .join(Order.items)
2287 .join(Item.keywords)
2288 )
2290 .. note:: as seen in the above example, **the order in which each
2291 call to the join() method occurs is important**. Query would not,
2292 for example, know how to join correctly if we were to specify
2293 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2294 such a case, depending on the arguments passed, it may raise an
2295 error that it doesn't know how to join, or it may produce invalid
2296 SQL in which case the database will raise an error. In correct
2297 practice, the
2298 :meth:`_query.Query.join` method is invoked in such a way that lines
2299 up with how we would want the JOIN clauses in SQL to be
2300 rendered, and each call should represent a clear link from what
2301 precedes it.
2303 **Joins to a Target Entity or Selectable**
2305 A second form of :meth:`_query.Query.join` allows any mapped entity or
2306 core selectable construct as a target. In this usage,
2307 :meth:`_query.Query.join` will attempt to create a JOIN along the
2308 natural foreign key relationship between two entities::
2310 q = session.query(User).join(Address)
2312 In the above calling form, :meth:`_query.Query.join` is called upon to
2313 create the "on clause" automatically for us. This calling form will
2314 ultimately raise an error if either there are no foreign keys between
2315 the two entities, or if there are multiple foreign key linkages between
2316 the target entity and the entity or entities already present on the
2317 left side such that creating a join requires more information. Note
2318 that when indicating a join to a target without any ON clause, ORM
2319 configured relationships are not taken into account.
2321 **Joins to a Target with an ON Clause**
2323 The third calling form allows both the target entity as well
2324 as the ON clause to be passed explicitly. A example that includes
2325 a SQL expression as the ON clause is as follows::
2327 q = session.query(User).join(Address, User.id == Address.user_id)
2329 The above form may also use a relationship-bound attribute as the
2330 ON clause as well::
2332 q = session.query(User).join(Address, User.addresses)
2334 The above syntax can be useful for the case where we wish
2335 to join to an alias of a particular target entity. If we wanted
2336 to join to ``Address`` twice, it could be achieved using two
2337 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2339 a1 = aliased(Address)
2340 a2 = aliased(Address)
2342 q = (
2343 session.query(User)
2344 .join(a1, User.addresses)
2345 .join(a2, User.addresses)
2346 .filter(a1.email_address == "ed@foo.com")
2347 .filter(a2.email_address == "ed@bar.com")
2348 )
2350 The relationship-bound calling form can also specify a target entity
2351 using the :meth:`_orm.PropComparator.of_type` method; a query
2352 equivalent to the one above would be::
2354 a1 = aliased(Address)
2355 a2 = aliased(Address)
2357 q = (
2358 session.query(User)
2359 .join(User.addresses.of_type(a1))
2360 .join(User.addresses.of_type(a2))
2361 .filter(a1.email_address == "ed@foo.com")
2362 .filter(a2.email_address == "ed@bar.com")
2363 )
2365 **Augmenting Built-in ON Clauses**
2367 As a substitute for providing a full custom ON condition for an
2368 existing relationship, the :meth:`_orm.PropComparator.and_` function
2369 may be applied to a relationship attribute to augment additional
2370 criteria into the ON clause; the additional criteria will be combined
2371 with the default criteria using AND::
2373 q = session.query(User).join(
2374 User.addresses.and_(Address.email_address != "foo@bar.com")
2375 )
2377 .. versionadded:: 1.4
2379 **Joining to Tables and Subqueries**
2382 The target of a join may also be any table or SELECT statement,
2383 which may be related to a target entity or not. Use the
2384 appropriate ``.subquery()`` method in order to make a subquery
2385 out of a query::
2387 subq = (
2388 session.query(Address)
2389 .filter(Address.email_address == "ed@foo.com")
2390 .subquery()
2391 )
2394 q = session.query(User).join(subq, User.id == subq.c.user_id)
2396 Joining to a subquery in terms of a specific relationship and/or
2397 target entity may be achieved by linking the subquery to the
2398 entity using :func:`_orm.aliased`::
2400 subq = (
2401 session.query(Address)
2402 .filter(Address.email_address == "ed@foo.com")
2403 .subquery()
2404 )
2406 address_subq = aliased(Address, subq)
2408 q = session.query(User).join(User.addresses.of_type(address_subq))
2410 **Controlling what to Join From**
2412 In cases where the left side of the current state of
2413 :class:`_query.Query` is not in line with what we want to join from,
2414 the :meth:`_query.Query.select_from` method may be used::
2416 q = (
2417 session.query(Address)
2418 .select_from(User)
2419 .join(User.addresses)
2420 .filter(User.name == "ed")
2421 )
2423 Which will produce SQL similar to:
2425 .. sourcecode:: sql
2427 SELECT address.* FROM user
2428 JOIN address ON user.id=address.user_id
2429 WHERE user.name = :name_1
2431 .. seealso::
2433 :meth:`_sql.Select.join` - v2 equivalent method.
2435 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2436 the props collection in modern use should be considered to be a one
2437 or two argument form, either as a single "target" entity or ORM
2438 attribute-bound relationship, or as a target entity plus an "on
2439 clause" which may be a SQL expression or ORM attribute-bound
2440 relationship.
2442 :param isouter=False: If True, the join used will be a left outer join,
2443 just as if the :meth:`_query.Query.outerjoin` method were called.
2445 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2447 """
2449 join_target = coercions.expect(
2450 roles.JoinTargetRole,
2451 target,
2452 apply_propagate_attrs=self,
2453 legacy=True,
2454 )
2455 if onclause is not None:
2456 onclause_element = coercions.expect(
2457 roles.OnClauseRole, onclause, legacy=True
2458 )
2459 else:
2460 onclause_element = None
2462 self._setup_joins += (
2463 (
2464 join_target,
2465 onclause_element,
2466 None,
2467 {
2468 "isouter": isouter,
2469 "full": full,
2470 },
2471 ),
2472 )
2474 self.__dict__.pop("_last_joined_entity", None)
2475 return self
2477 def outerjoin(
2478 self,
2479 target: _JoinTargetArgument,
2480 onclause: Optional[_OnClauseArgument] = None,
2481 *,
2482 full: bool = False,
2483 ) -> Self:
2484 """Create a left outer join against this ``Query`` object's criterion
2485 and apply generatively, returning the newly resulting ``Query``.
2487 Usage is the same as the ``join()`` method.
2489 .. seealso::
2491 :meth:`_sql.Select.outerjoin` - v2 equivalent method.
2493 """
2494 return self.join(target, onclause=onclause, isouter=True, full=full)
2496 @_generative
2497 @_assertions(_no_statement_condition)
2498 def reset_joinpoint(self) -> Self:
2499 """Return a new :class:`.Query`, where the "join point" has
2500 been reset back to the base FROM entities of the query.
2502 This method is usually used in conjunction with the
2503 ``aliased=True`` feature of the :meth:`~.Query.join`
2504 method. See the example in :meth:`~.Query.join` for how
2505 this is used.
2507 """
2508 self._last_joined_entity = None
2510 return self
2512 @_generative
2513 @_assertions(_no_clauseelement_condition)
2514 def select_from(self, *from_obj: _FromClauseArgument) -> Self:
2515 r"""Set the FROM clause of this :class:`.Query` explicitly.
2517 :meth:`.Query.select_from` is often used in conjunction with
2518 :meth:`.Query.join` in order to control which entity is selected
2519 from on the "left" side of the join.
2521 The entity or selectable object here effectively replaces the
2522 "left edge" of any calls to :meth:`~.Query.join`, when no
2523 joinpoint is otherwise established - usually, the default "join
2524 point" is the leftmost entity in the :class:`~.Query` object's
2525 list of entities to be selected.
2527 A typical example::
2529 q = (
2530 session.query(Address)
2531 .select_from(User)
2532 .join(User.addresses)
2533 .filter(User.name == "ed")
2534 )
2536 Which produces SQL equivalent to:
2538 .. sourcecode:: sql
2540 SELECT address.* FROM user
2541 JOIN address ON user.id=address.user_id
2542 WHERE user.name = :name_1
2544 :param \*from_obj: collection of one or more entities to apply
2545 to the FROM clause. Entities can be mapped classes,
2546 :class:`.AliasedClass` objects, :class:`.Mapper` objects
2547 as well as core :class:`.FromClause` elements like subqueries.
2549 .. seealso::
2551 :meth:`~.Query.join`
2553 :meth:`.Query.select_entity_from`
2555 :meth:`_sql.Select.select_from` - v2 equivalent method.
2557 """
2559 self._set_select_from(from_obj, False)
2560 return self
2562 @overload
2563 def __getitem__(self, item: slice) -> List[_T]: ...
2565 @overload
2566 def __getitem__(self, item: SupportsIndex) -> _T: ...
2568 def __getitem__(self, item: Any) -> Any:
2569 return orm_util._getitem(
2570 self,
2571 item,
2572 )
2574 @_generative
2575 @_assertions(_no_statement_condition)
2576 def slice(
2577 self,
2578 start: int,
2579 stop: int,
2580 ) -> Self:
2581 """Computes the "slice" of the :class:`_query.Query` represented by
2582 the given indices and returns the resulting :class:`_query.Query`.
2584 The start and stop indices behave like the argument to Python's
2585 built-in :func:`range` function. This method provides an
2586 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
2587 query.
2589 For example, ::
2591 session.query(User).order_by(User.id).slice(1, 3)
2593 renders as
2595 .. sourcecode:: sql
2597 SELECT users.id AS users_id,
2598 users.name AS users_name
2599 FROM users ORDER BY users.id
2600 LIMIT ? OFFSET ?
2601 (2, 1)
2603 .. seealso::
2605 :meth:`_query.Query.limit`
2607 :meth:`_query.Query.offset`
2609 :meth:`_sql.Select.slice` - v2 equivalent method.
2611 """
2613 self._limit_clause, self._offset_clause = sql_util._make_slice(
2614 self._limit_clause, self._offset_clause, start, stop
2615 )
2616 return self
2618 @_generative
2619 @_assertions(_no_statement_condition)
2620 def limit(self, limit: _LimitOffsetType) -> Self:
2621 """Apply a ``LIMIT`` to the query and return the newly resulting
2622 ``Query``.
2624 .. seealso::
2626 :meth:`_sql.Select.limit` - v2 equivalent method.
2628 """
2629 self._limit_clause = sql_util._offset_or_limit_clause(limit)
2630 return self
2632 @_generative
2633 @_assertions(_no_statement_condition)
2634 def offset(self, offset: _LimitOffsetType) -> Self:
2635 """Apply an ``OFFSET`` to the query and return the newly resulting
2636 ``Query``.
2638 .. seealso::
2640 :meth:`_sql.Select.offset` - v2 equivalent method.
2641 """
2642 self._offset_clause = sql_util._offset_or_limit_clause(offset)
2643 return self
2645 @_generative
2646 @_assertions(_no_statement_condition)
2647 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:
2648 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
2649 ``Query``.
2652 .. note::
2654 The ORM-level :meth:`.distinct` call includes logic that will
2655 automatically add columns from the ORDER BY of the query to the
2656 columns clause of the SELECT statement, to satisfy the common need
2657 of the database backend that ORDER BY columns be part of the SELECT
2658 list when DISTINCT is used. These columns *are not* added to the
2659 list of columns actually fetched by the :class:`_query.Query`,
2660 however,
2661 so would not affect results. The columns are passed through when
2662 using the :attr:`_query.Query.statement` accessor, however.
2664 .. deprecated:: 2.0 This logic is deprecated and will be removed
2665 in SQLAlchemy 2.0. See :ref:`migration_20_query_distinct`
2666 for a description of this use case in 2.0.
2668 .. seealso::
2670 :meth:`_sql.Select.distinct` - v2 equivalent method.
2672 :param \*expr: optional column expressions. When present,
2673 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2674 construct.
2676 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
2677 and will raise :class:`_exc.CompileError` in a future version.
2679 """
2680 if expr:
2681 self._distinct = True
2682 self._distinct_on = self._distinct_on + tuple(
2683 coercions.expect(roles.ByOfRole, e) for e in expr
2684 )
2685 else:
2686 self._distinct = True
2687 return self
2689 def all(self) -> List[_T]:
2690 """Return the results represented by this :class:`_query.Query`
2691 as a list.
2693 This results in an execution of the underlying SQL statement.
2695 .. warning:: The :class:`_query.Query` object,
2696 when asked to return either
2697 a sequence or iterator that consists of full ORM-mapped entities,
2698 will **deduplicate entries based on primary key**. See the FAQ for
2699 more details.
2701 .. seealso::
2703 :ref:`faq_query_deduplicating`
2705 .. seealso::
2707 :meth:`_engine.Result.all` - v2 comparable method.
2709 :meth:`_engine.Result.scalars` - v2 comparable method.
2710 """
2711 return self._iter().all() # type: ignore
2713 @_generative
2714 @_assertions(_no_clauseelement_condition)
2715 def from_statement(self, statement: ExecutableReturnsRows) -> Self:
2716 """Execute the given SELECT statement and return results.
2718 This method bypasses all internal statement compilation, and the
2719 statement is executed without modification.
2721 The statement is typically either a :func:`_expression.text`
2722 or :func:`_expression.select` construct, and should return the set
2723 of columns
2724 appropriate to the entity class represented by this
2725 :class:`_query.Query`.
2727 .. seealso::
2729 :meth:`_sql.Select.from_statement` - v2 comparable method.
2731 """
2732 statement = coercions.expect(
2733 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2734 )
2735 self._statement = statement
2736 return self
2738 def first(self) -> Optional[_T]:
2739 """Return the first result of this ``Query`` or
2740 None if the result doesn't contain any row.
2742 first() applies a limit of one within the generated SQL, so that
2743 only one primary entity row is generated on the server side
2744 (note this may consist of multiple result rows if join-loaded
2745 collections are present).
2747 Calling :meth:`_query.Query.first`
2748 results in an execution of the underlying
2749 query.
2751 .. seealso::
2753 :meth:`_query.Query.one`
2755 :meth:`_query.Query.one_or_none`
2757 :meth:`_engine.Result.first` - v2 comparable method.
2759 :meth:`_engine.Result.scalars` - v2 comparable method.
2761 """
2762 # replicates limit(1) behavior
2763 if self._statement is not None:
2764 return self._iter().first() # type: ignore
2765 else:
2766 return self.limit(1)._iter().first() # type: ignore
2768 def one_or_none(self) -> Optional[_T]:
2769 """Return at most one result or raise an exception.
2771 Returns ``None`` if the query selects
2772 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2773 if multiple object identities are returned, or if multiple
2774 rows are returned for a query that returns only scalar values
2775 as opposed to full identity-mapped entities.
2777 Calling :meth:`_query.Query.one_or_none`
2778 results in an execution of the
2779 underlying query.
2781 .. seealso::
2783 :meth:`_query.Query.first`
2785 :meth:`_query.Query.one`
2787 :meth:`_engine.Result.one_or_none` - v2 comparable method.
2789 :meth:`_engine.Result.scalar_one_or_none` - v2 comparable method.
2791 """
2792 return self._iter().one_or_none() # type: ignore
2794 def one(self) -> _T:
2795 """Return exactly one result or raise an exception.
2797 Raises :class:`_exc.NoResultFound` if the query selects no rows.
2798 Raises :class:`_exc.MultipleResultsFound` if multiple object identities
2799 are returned, or if multiple rows are returned for a query that returns
2800 only scalar values as opposed to full identity-mapped entities.
2802 Calling :meth:`.one` results in an execution of the underlying query.
2804 .. seealso::
2806 :meth:`_query.Query.first`
2808 :meth:`_query.Query.one_or_none`
2810 :meth:`_engine.Result.one` - v2 comparable method.
2812 :meth:`_engine.Result.scalar_one` - v2 comparable method.
2814 """
2815 return self._iter().one() # type: ignore
2817 def scalar(self) -> Any:
2818 """Return the first element of the first result or None
2819 if no rows present. If multiple rows are returned,
2820 raises :class:`_exc.MultipleResultsFound`.
2822 >>> session.query(Item).scalar()
2823 <Item>
2824 >>> session.query(Item.id).scalar()
2825 1
2826 >>> session.query(Item.id).filter(Item.id < 0).scalar()
2827 None
2828 >>> session.query(Item.id, Item.name).scalar()
2829 1
2830 >>> session.query(func.count(Parent.id)).scalar()
2831 20
2833 This results in an execution of the underlying query.
2835 .. seealso::
2837 :meth:`_engine.Result.scalar` - v2 comparable method.
2839 """
2840 # TODO: not sure why we can't use result.scalar() here
2841 try:
2842 ret = self.one()
2843 if not isinstance(ret, collections_abc.Sequence):
2844 return ret
2845 return ret[0]
2846 except sa_exc.NoResultFound:
2847 return None
2849 def __iter__(self) -> Iterator[_T]:
2850 result = self._iter()
2851 try:
2852 yield from result # type: ignore
2853 except GeneratorExit:
2854 # issue #8710 - direct iteration is not reusable after
2855 # an iterable block is broken, so close the result
2856 result._soft_close()
2857 raise
2859 def _iter(self) -> Union[ScalarResult[_T], Result[_T]]:
2860 # new style execution.
2861 params = self._params
2863 statement = self._statement_20()
2864 result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
2865 statement,
2866 params,
2867 execution_options={"_sa_orm_load_options": self.load_options},
2868 )
2870 # legacy: automatically set scalars, unique
2871 if result._attributes.get("is_single_entity", False):
2872 result = cast("Result[_T]", result).scalars()
2874 if (
2875 result._attributes.get("filtered", False)
2876 and not self.load_options._yield_per
2877 ):
2878 result = result.unique()
2880 return result
2882 def __str__(self) -> str:
2883 statement = self._statement_20()
2885 try:
2886 bind = (
2887 self._get_bind_args(statement, self.session.get_bind)
2888 if self.session
2889 else None
2890 )
2891 except sa_exc.UnboundExecutionError:
2892 bind = None
2894 return str(statement.compile(bind))
2896 def _get_bind_args(self, statement: Any, fn: Any, **kw: Any) -> Any:
2897 return fn(clause=statement, **kw)
2899 @property
2900 def column_descriptions(self) -> List[ORMColumnDescription]:
2901 """Return metadata about the columns which would be
2902 returned by this :class:`_query.Query`.
2904 Format is a list of dictionaries::
2906 user_alias = aliased(User, name="user2")
2907 q = sess.query(User, User.id, user_alias)
2909 # this expression:
2910 q.column_descriptions
2912 # would return:
2913 [
2914 {
2915 "name": "User",
2916 "type": User,
2917 "aliased": False,
2918 "expr": User,
2919 "entity": User,
2920 },
2921 {
2922 "name": "id",
2923 "type": Integer(),
2924 "aliased": False,
2925 "expr": User.id,
2926 "entity": User,
2927 },
2928 {
2929 "name": "user2",
2930 "type": User,
2931 "aliased": True,
2932 "expr": user_alias,
2933 "entity": user_alias,
2934 },
2935 ]
2937 .. seealso::
2939 This API is available using :term:`2.0 style` queries as well,
2940 documented at:
2942 * :ref:`queryguide_inspection`
2944 * :attr:`.Select.column_descriptions`
2946 """
2948 return _column_descriptions(self, legacy=True)
2950 @util.deprecated(
2951 "2.0",
2952 "The :meth:`_orm.Query.instances` method is deprecated and will "
2953 "be removed in a future release. "
2954 "Use the Select.from_statement() method or aliased() construct in "
2955 "conjunction with Session.execute() instead.",
2956 )
2957 def instances(
2958 self,
2959 result_proxy: CursorResult[Any],
2960 context: Optional[QueryContext] = None,
2961 ) -> Any:
2962 """Return an ORM result given a :class:`_engine.CursorResult` and
2963 :class:`.QueryContext`.
2965 """
2966 if context is None:
2967 util.warn_deprecated(
2968 "Using the Query.instances() method without a context "
2969 "is deprecated and will be disallowed in a future release. "
2970 "Please make use of :meth:`_query.Query.from_statement` "
2971 "for linking ORM results to arbitrary select constructs.",
2972 version="1.4",
2973 )
2974 compile_state = self._compile_state(for_statement=False)
2976 context = QueryContext(
2977 compile_state,
2978 compile_state.statement,
2979 compile_state.statement,
2980 self._params,
2981 self.session,
2982 self.load_options,
2983 )
2985 result = loading.instances(result_proxy, context)
2987 # legacy: automatically set scalars, unique
2988 if result._attributes.get("is_single_entity", False):
2989 result = result.scalars() # type: ignore
2991 if result._attributes.get("filtered", False):
2992 result = result.unique()
2994 # TODO: isn't this supposed to be a list?
2995 return result
2997 @util.became_legacy_20(
2998 ":meth:`_orm.Query.merge_result`",
2999 alternative="The method is superseded by the "
3000 ":func:`_orm.merge_frozen_result` function.",
3001 enable_warnings=False, # warnings occur via loading.merge_result
3002 )
3003 def merge_result(
3004 self,
3005 iterator: Union[
3006 FrozenResult[Any], Iterable[Sequence[Any]], Iterable[object]
3007 ],
3008 load: bool = True,
3009 ) -> Union[FrozenResult[Any], Iterable[Any]]:
3010 """Merge a result into this :class:`_query.Query` object's Session.
3012 Given an iterator returned by a :class:`_query.Query`
3013 of the same structure
3014 as this one, return an identical iterator of results, with all mapped
3015 instances merged into the session using :meth:`.Session.merge`. This
3016 is an optimized method which will merge all mapped instances,
3017 preserving the structure of the result rows and unmapped columns with
3018 less method overhead than that of calling :meth:`.Session.merge`
3019 explicitly for each value.
3021 The structure of the results is determined based on the column list of
3022 this :class:`_query.Query` - if these do not correspond,
3023 unchecked errors
3024 will occur.
3026 The 'load' argument is the same as that of :meth:`.Session.merge`.
3028 For an example of how :meth:`_query.Query.merge_result` is used, see
3029 the source code for the example :ref:`examples_caching`, where
3030 :meth:`_query.Query.merge_result` is used to efficiently restore state
3031 from a cache back into a target :class:`.Session`.
3033 """
3035 return loading.merge_result(self, iterator, load)
3037 def exists(self) -> Exists:
3038 """A convenience method that turns a query into an EXISTS subquery
3039 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3041 e.g.::
3043 q = session.query(User).filter(User.name == "fred")
3044 session.query(q.exists())
3046 Producing SQL similar to:
3048 .. sourcecode:: sql
3050 SELECT EXISTS (
3051 SELECT 1 FROM users WHERE users.name = :name_1
3052 ) AS anon_1
3054 The EXISTS construct is usually used in the WHERE clause::
3056 session.query(User.id).filter(q.exists()).scalar()
3058 Note that some databases such as SQL Server don't allow an
3059 EXISTS expression to be present in the columns clause of a
3060 SELECT. To select a simple boolean value based on the exists
3061 as a WHERE, use :func:`.literal`::
3063 from sqlalchemy import literal
3065 session.query(literal(True)).filter(q.exists()).scalar()
3067 .. seealso::
3069 :meth:`_sql.Select.exists` - v2 comparable method.
3071 """
3073 # .add_columns() for the case that we are a query().select_from(X),
3074 # so that ".statement" can be produced (#2995) but also without
3075 # omitting the FROM clause from a query(X) (#2818);
3076 # .with_only_columns() after we have a core select() so that
3077 # we get just "SELECT 1" without any entities.
3079 inner = (
3080 self.enable_eagerloads(False)
3081 .add_columns(sql.literal_column("1"))
3082 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3083 ._get_select_statement_only()
3084 .with_only_columns(1)
3085 )
3087 ezero = self._entity_from_pre_ent_zero()
3088 if ezero is not None:
3089 inner = inner.select_from(ezero)
3091 return sql.exists(inner)
3093 def count(self) -> int:
3094 r"""Return a count of rows this the SQL formed by this :class:`Query`
3095 would return.
3097 This generates the SQL for this Query as follows:
3099 .. sourcecode:: sql
3101 SELECT count(1) AS count_1 FROM (
3102 SELECT <rest of query follows...>
3103 ) AS anon_1
3105 The above SQL returns a single row, which is the aggregate value
3106 of the count function; the :meth:`_query.Query.count`
3107 method then returns
3108 that single integer value.
3110 .. warning::
3112 It is important to note that the value returned by
3113 count() is **not the same as the number of ORM objects that this
3114 Query would return from a method such as the .all() method**.
3115 The :class:`_query.Query` object,
3116 when asked to return full entities,
3117 will **deduplicate entries based on primary key**, meaning if the
3118 same primary key value would appear in the results more than once,
3119 only one object of that primary key would be present. This does
3120 not apply to a query that is against individual columns.
3122 .. seealso::
3124 :ref:`faq_query_deduplicating`
3126 For fine grained control over specific columns to count, to skip the
3127 usage of a subquery or otherwise control of the FROM clause, or to use
3128 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3129 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3131 from sqlalchemy import func
3133 # count User records, without
3134 # using a subquery.
3135 session.query(func.count(User.id))
3137 # return count of user "id" grouped
3138 # by "name"
3139 session.query(func.count(User.id)).group_by(User.name)
3141 from sqlalchemy import distinct
3143 # count distinct "name" values
3144 session.query(func.count(distinct(User.name)))
3146 .. seealso::
3148 :ref:`migration_20_query_usage`
3150 """
3151 col = sql.func.count(sql.literal_column("*"))
3152 return ( # type: ignore
3153 self._legacy_from_self(col).enable_eagerloads(False).scalar()
3154 )
3156 def delete(
3157 self,
3158 synchronize_session: SynchronizeSessionArgument = "auto",
3159 delete_args: Optional[Dict[Any, Any]] = None,
3160 ) -> int:
3161 r"""Perform a DELETE with an arbitrary WHERE clause.
3163 Deletes rows matched by this query from the database.
3165 E.g.::
3167 sess.query(User).filter(User.age == 25).delete(synchronize_session=False)
3169 sess.query(User).filter(User.age == 25).delete(
3170 synchronize_session="evaluate"
3171 )
3173 .. warning::
3175 See the section :ref:`orm_expression_update_delete` for important
3176 caveats and warnings, including limitations when using bulk UPDATE
3177 and DELETE with mapper inheritance configurations.
3179 :param synchronize_session: chooses the strategy to update the
3180 attributes on objects in the session. See the section
3181 :ref:`orm_expression_update_delete` for a discussion of these
3182 strategies.
3184 :param delete_args: Optional dictionary, if present will be passed
3185 to the underlying :func:`_expression.delete` construct as the ``**kw``
3186 for the object. May be used to pass dialect-specific arguments such
3187 as ``mysql_limit``.
3189 .. versionadded:: 2.0.37
3191 :return: the count of rows matched as returned by the database's
3192 "row count" feature.
3194 .. seealso::
3196 :ref:`orm_expression_update_delete`
3198 """ # noqa: E501
3200 bulk_del = BulkDelete(self, delete_args)
3201 if self.dispatch.before_compile_delete:
3202 for fn in self.dispatch.before_compile_delete:
3203 new_query = fn(bulk_del.query, bulk_del)
3204 if new_query is not None:
3205 bulk_del.query = new_query
3207 self = bulk_del.query
3209 delete_ = sql.delete(*self._raw_columns) # type: ignore
3211 if delete_args:
3212 delete_ = delete_.with_dialect_options(**delete_args)
3214 delete_._where_criteria = self._where_criteria
3215 result = cast(
3216 "CursorResult[Any]",
3217 self.session.execute(
3218 delete_,
3219 self._params,
3220 execution_options=self._execution_options.union(
3221 {"synchronize_session": synchronize_session}
3222 ),
3223 ),
3224 )
3225 bulk_del.result = result # type: ignore
3226 self.session.dispatch.after_bulk_delete(bulk_del)
3227 result.close()
3229 return result.rowcount
3231 def update(
3232 self,
3233 values: Dict[_DMLColumnArgument, Any],
3234 synchronize_session: SynchronizeSessionArgument = "auto",
3235 update_args: Optional[Dict[Any, Any]] = None,
3236 ) -> int:
3237 r"""Perform an UPDATE with an arbitrary WHERE clause.
3239 Updates rows matched by this query in the database.
3241 E.g.::
3243 sess.query(User).filter(User.age == 25).update(
3244 {User.age: User.age - 10}, synchronize_session=False
3245 )
3247 sess.query(User).filter(User.age == 25).update(
3248 {"age": User.age - 10}, synchronize_session="evaluate"
3249 )
3251 .. warning::
3253 See the section :ref:`orm_expression_update_delete` for important
3254 caveats and warnings, including limitations when using arbitrary
3255 UPDATE and DELETE with mapper inheritance configurations.
3257 :param values: a dictionary with attributes names, or alternatively
3258 mapped attributes or SQL expressions, as keys, and literal
3259 values or sql expressions as values. If :ref:`parameter-ordered
3260 mode <tutorial_parameter_ordered_updates>` is desired, the values can
3261 be passed as a list of 2-tuples; this requires that the
3262 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3263 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3264 as well.
3266 :param synchronize_session: chooses the strategy to update the
3267 attributes on objects in the session. See the section
3268 :ref:`orm_expression_update_delete` for a discussion of these
3269 strategies.
3271 :param update_args: Optional dictionary, if present will be passed
3272 to the underlying :func:`_expression.update` construct as the ``**kw``
3273 for the object. May be used to pass dialect-specific arguments such
3274 as ``mysql_limit``, as well as other special arguments such as
3275 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3277 :return: the count of rows matched as returned by the database's
3278 "row count" feature.
3281 .. seealso::
3283 :ref:`orm_expression_update_delete`
3285 """
3287 update_args = update_args or {}
3289 bulk_ud = BulkUpdate(self, values, update_args)
3291 if self.dispatch.before_compile_update:
3292 for fn in self.dispatch.before_compile_update:
3293 new_query = fn(bulk_ud.query, bulk_ud)
3294 if new_query is not None:
3295 bulk_ud.query = new_query
3296 self = bulk_ud.query
3298 upd = sql.update(*self._raw_columns) # type: ignore
3300 ppo = update_args.pop("preserve_parameter_order", False)
3301 if ppo:
3302 upd = upd.ordered_values(*values) # type: ignore
3303 else:
3304 upd = upd.values(values)
3305 if update_args:
3306 upd = upd.with_dialect_options(**update_args)
3308 upd._where_criteria = self._where_criteria
3309 result = cast(
3310 "CursorResult[Any]",
3311 self.session.execute(
3312 upd,
3313 self._params,
3314 execution_options=self._execution_options.union(
3315 {"synchronize_session": synchronize_session}
3316 ),
3317 ),
3318 )
3319 bulk_ud.result = result # type: ignore
3320 self.session.dispatch.after_bulk_update(bulk_ud)
3321 result.close()
3322 return result.rowcount
3324 def _compile_state(
3325 self, for_statement: bool = False, **kw: Any
3326 ) -> ORMCompileState:
3327 """Create an out-of-compiler ORMCompileState object.
3329 The ORMCompileState object is normally created directly as a result
3330 of the SQLCompiler.process() method being handed a Select()
3331 or FromStatement() object that uses the "orm" plugin. This method
3332 provides a means of creating this ORMCompileState object directly
3333 without using the compiler.
3335 This method is used only for deprecated cases, which include
3336 the .from_self() method for a Query that has multiple levels
3337 of .from_self() in use, as well as the instances() method. It is
3338 also used within the test suite to generate ORMCompileState objects
3339 for test purposes.
3341 """
3343 stmt = self._statement_20(for_statement=for_statement, **kw)
3344 assert for_statement == stmt._compile_options._for_statement
3346 # this chooses between ORMFromStatementCompileState and
3347 # ORMSelectCompileState. We could also base this on
3348 # query._statement is not None as we have the ORM Query here
3349 # however this is the more general path.
3350 compile_state_cls = cast(
3351 ORMCompileState,
3352 ORMCompileState._get_plugin_class_for_plugin(stmt, "orm"),
3353 )
3355 return compile_state_cls._create_orm_context(
3356 stmt, toplevel=True, compiler=None
3357 )
3359 def _compile_context(self, for_statement: bool = False) -> QueryContext:
3360 compile_state = self._compile_state(for_statement=for_statement)
3361 context = QueryContext(
3362 compile_state,
3363 compile_state.statement,
3364 compile_state.statement,
3365 self._params,
3366 self.session,
3367 self.load_options,
3368 )
3370 return context
3373class AliasOption(interfaces.LoaderOption):
3374 inherit_cache = False
3376 @util.deprecated(
3377 "1.4",
3378 "The :class:`.AliasOption` object is not necessary "
3379 "for entities to be matched up to a query that is established "
3380 "via :meth:`.Query.from_statement` and now does nothing.",
3381 )
3382 def __init__(self, alias: Union[Alias, Subquery]):
3383 r"""Return a :class:`.MapperOption` that will indicate to the
3384 :class:`_query.Query`
3385 that the main table has been aliased.
3387 """
3389 def process_compile_state(self, compile_state: ORMCompileState) -> None:
3390 pass
3393class BulkUD:
3394 """State used for the orm.Query version of update() / delete().
3396 This object is now specific to Query only.
3398 """
3400 def __init__(self, query: Query[Any]):
3401 self.query = query.enable_eagerloads(False)
3402 self._validate_query_state()
3403 self.mapper = self.query._entity_from_pre_ent_zero()
3405 def _validate_query_state(self) -> None:
3406 for attr, methname, notset, op in (
3407 ("_limit_clause", "limit()", None, operator.is_),
3408 ("_offset_clause", "offset()", None, operator.is_),
3409 ("_order_by_clauses", "order_by()", (), operator.eq),
3410 ("_group_by_clauses", "group_by()", (), operator.eq),
3411 ("_distinct", "distinct()", False, operator.is_),
3412 (
3413 "_from_obj",
3414 "join(), outerjoin(), select_from(), or from_self()",
3415 (),
3416 operator.eq,
3417 ),
3418 (
3419 "_setup_joins",
3420 "join(), outerjoin(), select_from(), or from_self()",
3421 (),
3422 operator.eq,
3423 ),
3424 ):
3425 if not op(getattr(self.query, attr), notset):
3426 raise sa_exc.InvalidRequestError(
3427 "Can't call Query.update() or Query.delete() "
3428 "when %s has been called" % (methname,)
3429 )
3431 @property
3432 def session(self) -> Session:
3433 return self.query.session
3436class BulkUpdate(BulkUD):
3437 """BulkUD which handles UPDATEs."""
3439 def __init__(
3440 self,
3441 query: Query[Any],
3442 values: Dict[_DMLColumnArgument, Any],
3443 update_kwargs: Optional[Dict[Any, Any]],
3444 ):
3445 super().__init__(query)
3446 self.values = values
3447 self.update_kwargs = update_kwargs
3450class BulkDelete(BulkUD):
3451 """BulkUD which handles DELETEs."""
3453 def __init__(
3454 self,
3455 query: Query[Any],
3456 delete_kwargs: Optional[Dict[Any, Any]],
3457 ):
3458 super().__init__(query)
3459 self.delete_kwargs = delete_kwargs
3462class RowReturningQuery(Query[Row[_TP]]):
3463 if TYPE_CHECKING:
3465 def tuples(self) -> Query[_TP]: # type: ignore
3466 ...