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-2025 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
98if TYPE_CHECKING:
99 from ._typing import _EntityType
100 from ._typing import _ExternalEntityType
101 from ._typing import _InternalEntityType
102 from ._typing import SynchronizeSessionArgument
103 from .mapper import Mapper
104 from .path_registry import PathRegistry
105 from .session import _PKIdentityArgument
106 from .session import Session
107 from .state import InstanceState
108 from ..engine.cursor import CursorResult
109 from ..engine.interfaces import _ImmutableExecuteOptions
110 from ..engine.interfaces import CompiledCacheType
111 from ..engine.interfaces import IsolationLevel
112 from ..engine.interfaces import SchemaTranslateMapType
113 from ..engine.result import FrozenResult
114 from ..engine.result import ScalarResult
115 from ..sql._typing import _ColumnExpressionArgument
116 from ..sql._typing import _ColumnExpressionOrStrLabelArgument
117 from ..sql._typing import _ColumnsClauseArgument
118 from ..sql._typing import _DMLColumnArgument
119 from ..sql._typing import _JoinTargetArgument
120 from ..sql._typing import _LimitOffsetType
121 from ..sql._typing import _MAYBE_ENTITY
122 from ..sql._typing import _no_kw
123 from ..sql._typing import _NOT_ENTITY
124 from ..sql._typing import _OnClauseArgument
125 from ..sql._typing import _PropagateAttrsType
126 from ..sql._typing import _T0
127 from ..sql._typing import _T1
128 from ..sql._typing import _T2
129 from ..sql._typing import _T3
130 from ..sql._typing import _T4
131 from ..sql._typing import _T5
132 from ..sql._typing import _T6
133 from ..sql._typing import _T7
134 from ..sql._typing import _TypedColumnClauseArgument as _TCCA
135 from ..sql.base import CacheableOptions
136 from ..sql.base import ExecutableOption
137 from ..sql.dml import UpdateBase
138 from ..sql.elements import ColumnElement
139 from ..sql.elements import Label
140 from ..sql.selectable import _ForUpdateOfArgument
141 from ..sql.selectable import _JoinTargetElement
142 from ..sql.selectable import _SetupJoinsElement
143 from ..sql.selectable import Alias
144 from ..sql.selectable import CTE
145 from ..sql.selectable import ExecutableReturnsRows
146 from ..sql.selectable import FromClause
147 from ..sql.selectable import ScalarSelect
148 from ..sql.selectable import Subquery
151__all__ = ["Query", "QueryContext"]
153_T = TypeVar("_T", bound=Any)
156@inspection._self_inspects
157@log.class_logger
158class Query(
159 _SelectFromElements,
160 SupportsCloneAnnotations,
161 HasPrefixes,
162 HasSuffixes,
163 HasHints,
164 EventTarget,
165 log.Identified,
166 Generative,
167 Executable,
168 Generic[_T],
169):
170 """ORM-level SQL construction object.
172 .. legacy:: The ORM :class:`.Query` object is a legacy construct
173 as of SQLAlchemy 2.0. See the notes at the top of
174 :ref:`query_api_toplevel` for an overview, including links to migration
175 documentation.
177 :class:`_query.Query` objects are normally initially generated using the
178 :meth:`~.Session.query` method of :class:`.Session`, and in
179 less common cases by instantiating the :class:`_query.Query` directly and
180 associating with a :class:`.Session` using the
181 :meth:`_query.Query.with_session`
182 method.
184 """
186 # elements that are in Core and can be cached in the same way
187 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
188 _having_criteria: Tuple[ColumnElement[Any], ...] = ()
190 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()
191 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()
192 _limit_clause: Optional[ColumnElement[Any]] = None
193 _offset_clause: Optional[ColumnElement[Any]] = None
195 _distinct: bool = False
196 _distinct_on: Tuple[ColumnElement[Any], ...] = ()
198 _for_update_arg: Optional[ForUpdateArg] = None
199 _correlate: Tuple[FromClause, ...] = ()
200 _auto_correlate: bool = True
201 _from_obj: Tuple[FromClause, ...] = ()
202 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()
204 _label_style: SelectLabelStyle = SelectLabelStyle.LABEL_STYLE_LEGACY_ORM
206 _memoized_select_entities = ()
208 _compile_options: Union[Type[CacheableOptions], CacheableOptions] = (
209 ORMCompileState.default_compile_options
210 )
212 _with_options: Tuple[ExecutableOption, ...]
213 load_options = QueryContext.default_load_options + {
214 "_legacy_uniquing": True
215 }
217 _params: util.immutabledict[str, Any] = util.EMPTY_DICT
219 # local Query builder state, not needed for
220 # compilation or execution
221 _enable_assertions = True
223 _statement: Optional[ExecutableReturnsRows] = None
225 session: Session
227 dispatch: dispatcher[Query[_T]]
229 # mirrors that of ClauseElement, used to propagate the "orm"
230 # plugin as well as the "subject" of the plugin, e.g. the mapper
231 # we are querying against.
232 @util.memoized_property
233 def _propagate_attrs(self) -> _PropagateAttrsType:
234 return util.EMPTY_DICT
236 def __init__(
237 self,
238 entities: Union[
239 _ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]
240 ],
241 session: Optional[Session] = None,
242 ):
243 """Construct a :class:`_query.Query` directly.
245 E.g.::
247 q = Query([User, Address], session=some_session)
249 The above is equivalent to::
251 q = some_session.query(User, Address)
253 :param entities: a sequence of entities and/or SQL expressions.
255 :param session: a :class:`.Session` with which the
256 :class:`_query.Query`
257 will be associated. Optional; a :class:`_query.Query`
258 can be associated
259 with a :class:`.Session` generatively via the
260 :meth:`_query.Query.with_session` method as well.
262 .. seealso::
264 :meth:`.Session.query`
266 :meth:`_query.Query.with_session`
268 """
270 # session is usually present. There's one case in subqueryloader
271 # where it stores a Query without a Session and also there are tests
272 # for the query(Entity).with_session(session) API which is likely in
273 # some old recipes, however these are legacy as select() can now be
274 # used.
275 self.session = session # type: ignore
276 self._set_entities(entities)
278 def _set_propagate_attrs(self, values: Mapping[str, Any]) -> Self:
279 self._propagate_attrs = util.immutabledict(values)
280 return self
282 def _set_entities(
283 self,
284 entities: Union[
285 _ColumnsClauseArgument[Any], Iterable[_ColumnsClauseArgument[Any]]
286 ],
287 ) -> None:
288 self._raw_columns = [
289 coercions.expect(
290 roles.ColumnsClauseRole,
291 ent,
292 apply_propagate_attrs=self,
293 post_inspect=True,
294 )
295 for ent in util.to_list(entities)
296 ]
298 def tuples(self: Query[_O]) -> Query[Tuple[_O]]:
299 """return a tuple-typed form of this :class:`.Query`.
301 This method invokes the :meth:`.Query.only_return_tuples`
302 method with a value of ``True``, which by itself ensures that this
303 :class:`.Query` will always return :class:`.Row` objects, even
304 if the query is made against a single entity. It then also
305 at the typing level will return a "typed" query, if possible,
306 that will type result rows as ``Tuple`` objects with typed
307 elements.
309 This method can be compared to the :meth:`.Result.tuples` method,
310 which returns "self", but from a typing perspective returns an object
311 that will yield typed ``Tuple`` objects for results. Typing
312 takes effect only if this :class:`.Query` object is a typed
313 query object already.
315 .. versionadded:: 2.0
317 .. seealso::
319 :meth:`.Result.tuples` - v2 equivalent method.
321 """
322 return self.only_return_tuples(True) # type: ignore
324 def _entity_from_pre_ent_zero(self) -> Optional[_InternalEntityType[Any]]:
325 if not self._raw_columns:
326 return None
328 ent = self._raw_columns[0]
330 if "parententity" in ent._annotations:
331 return ent._annotations["parententity"] # type: ignore
332 elif "bundle" in ent._annotations:
333 return ent._annotations["bundle"] # type: ignore
334 else:
335 # label, other SQL expression
336 for element in visitors.iterate(ent):
337 if "parententity" in element._annotations:
338 return element._annotations["parententity"] # type: ignore # noqa: E501
339 else:
340 return None
342 def _only_full_mapper_zero(self, methname: str) -> Mapper[Any]:
343 if (
344 len(self._raw_columns) != 1
345 or "parententity" not in self._raw_columns[0]._annotations
346 or not self._raw_columns[0].is_selectable
347 ):
348 raise sa_exc.InvalidRequestError(
349 "%s() can only be used against "
350 "a single mapped class." % methname
351 )
353 return self._raw_columns[0]._annotations["parententity"] # type: ignore # noqa: E501
355 def _set_select_from(
356 self, obj: Iterable[_FromClauseArgument], set_base_alias: bool
357 ) -> None:
358 fa = [
359 coercions.expect(
360 roles.StrictFromClauseRole,
361 elem,
362 allow_select=True,
363 apply_propagate_attrs=self,
364 )
365 for elem in obj
366 ]
368 self._compile_options += {"_set_base_alias": set_base_alias}
369 self._from_obj = tuple(fa)
371 @_generative
372 def _set_lazyload_from(self, state: InstanceState[Any]) -> Self:
373 self.load_options += {"_lazy_loaded_from": state}
374 return self
376 def _get_condition(self) -> None:
377 """used by legacy BakedQuery"""
378 self._no_criterion_condition("get", order_by=False, distinct=False)
380 def _get_existing_condition(self) -> None:
381 self._no_criterion_assertion("get", order_by=False, distinct=False)
383 def _no_criterion_assertion(
384 self, meth: str, order_by: bool = True, distinct: bool = True
385 ) -> None:
386 if not self._enable_assertions:
387 return
388 if (
389 self._where_criteria
390 or self._statement is not None
391 or self._from_obj
392 or self._setup_joins
393 or self._limit_clause is not None
394 or self._offset_clause is not None
395 or self._group_by_clauses
396 or (order_by and self._order_by_clauses)
397 or (distinct and self._distinct)
398 ):
399 raise sa_exc.InvalidRequestError(
400 "Query.%s() being called on a "
401 "Query with existing criterion. " % meth
402 )
404 def _no_criterion_condition(
405 self, meth: str, order_by: bool = True, distinct: bool = True
406 ) -> None:
407 self._no_criterion_assertion(meth, order_by, distinct)
409 self._from_obj = self._setup_joins = ()
410 if self._statement is not None:
411 self._compile_options += {"_statement": None}
412 self._where_criteria = ()
413 self._distinct = False
415 self._order_by_clauses = self._group_by_clauses = ()
417 def _no_clauseelement_condition(self, meth: str) -> None:
418 if not self._enable_assertions:
419 return
420 if self._order_by_clauses:
421 raise sa_exc.InvalidRequestError(
422 "Query.%s() being called on a "
423 "Query with existing criterion. " % meth
424 )
425 self._no_criterion_condition(meth)
427 def _no_statement_condition(self, meth: str) -> None:
428 if not self._enable_assertions:
429 return
430 if self._statement is not None:
431 raise sa_exc.InvalidRequestError(
432 (
433 "Query.%s() being called on a Query with an existing full "
434 "statement - can't apply criterion."
435 )
436 % meth
437 )
439 def _no_limit_offset(self, meth: str) -> None:
440 if not self._enable_assertions:
441 return
442 if self._limit_clause is not None or self._offset_clause is not None:
443 raise sa_exc.InvalidRequestError(
444 "Query.%s() being called on a Query which already has LIMIT "
445 "or OFFSET applied. Call %s() before limit() or offset() "
446 "are applied." % (meth, meth)
447 )
449 @property
450 def _has_row_limiting_clause(self) -> bool:
451 return (
452 self._limit_clause is not None or self._offset_clause is not None
453 )
455 def _get_options(
456 self,
457 populate_existing: Optional[bool] = None,
458 version_check: Optional[bool] = None,
459 only_load_props: Optional[Sequence[str]] = None,
460 refresh_state: Optional[InstanceState[Any]] = None,
461 identity_token: Optional[Any] = None,
462 ) -> Self:
463 load_options: Dict[str, Any] = {}
464 compile_options: Dict[str, Any] = {}
466 if version_check:
467 load_options["_version_check"] = version_check
468 if populate_existing:
469 load_options["_populate_existing"] = populate_existing
470 if refresh_state:
471 load_options["_refresh_state"] = refresh_state
472 compile_options["_for_refresh_state"] = True
473 if only_load_props:
474 compile_options["_only_load_props"] = frozenset(only_load_props)
475 if identity_token:
476 load_options["_identity_token"] = identity_token
478 if load_options:
479 self.load_options += load_options
480 if compile_options:
481 self._compile_options += compile_options
483 return self
485 def _clone(self, **kw: Any) -> Self:
486 return self._generate()
488 def _get_select_statement_only(self) -> Select[_T]:
489 if self._statement is not None:
490 raise sa_exc.InvalidRequestError(
491 "Can't call this method on a Query that uses from_statement()"
492 )
493 return cast("Select[_T]", self.statement)
495 @property
496 def statement(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
497 """The full SELECT statement represented by this Query.
499 The statement by default will not have disambiguating labels
500 applied to the construct unless with_labels(True) is called
501 first.
503 """
505 # .statement can return the direct future.Select() construct here, as
506 # long as we are not using subsequent adaption features that
507 # are made against raw entities, e.g. from_self(), with_polymorphic(),
508 # select_entity_from(). If these features are being used, then
509 # the Select() we return will not have the correct .selected_columns
510 # collection and will not embed in subsequent queries correctly.
511 # We could find a way to make this collection "correct", however
512 # this would not be too different from doing the full compile as
513 # we are doing in any case, the Select() would still not have the
514 # proper state for other attributes like whereclause, order_by,
515 # and these features are all deprecated in any case.
516 #
517 # for these reasons, Query is not a Select, it remains an ORM
518 # object for which __clause_element__() must be called in order for
519 # it to provide a real expression object.
520 #
521 # from there, it starts to look much like Query itself won't be
522 # passed into the execute process and won't generate its own cache
523 # key; this will all occur in terms of the ORM-enabled Select.
524 stmt: Union[Select[_T], FromStatement[_T], UpdateBase]
526 if not self._compile_options._set_base_alias:
527 # if we don't have legacy top level aliasing features in use
528 # then convert to a future select() directly
529 stmt = self._statement_20(for_statement=True)
530 else:
531 stmt = self._compile_state(for_statement=True).statement
533 if self._params:
534 stmt = stmt.params(self._params)
536 return stmt
538 def _final_statement(self, legacy_query_style: bool = True) -> Select[Any]:
539 """Return the 'final' SELECT statement for this :class:`.Query`.
541 This is used by the testing suite only and is fairly inefficient.
543 This is the Core-only select() that will be rendered by a complete
544 compilation of this query, and is what .statement used to return
545 in 1.3.
548 """
550 q = self._clone()
552 return q._compile_state(
553 use_legacy_query_style=legacy_query_style
554 ).statement # type: ignore
556 def _statement_20(
557 self, for_statement: bool = False, use_legacy_query_style: bool = True
558 ) -> Union[Select[_T], FromStatement[_T]]:
559 # TODO: this event needs to be deprecated, as it currently applies
560 # only to ORM query and occurs at this spot that is now more
561 # or less an artificial spot
562 if self.dispatch.before_compile:
563 for fn in self.dispatch.before_compile:
564 new_query = fn(self)
565 if new_query is not None and new_query is not self:
566 self = new_query
567 if not fn._bake_ok: # type: ignore
568 self._compile_options += {"_bake_ok": False}
570 compile_options = self._compile_options
571 compile_options += {
572 "_for_statement": for_statement,
573 "_use_legacy_query_style": use_legacy_query_style,
574 }
576 stmt: Union[Select[_T], FromStatement[_T]]
578 if self._statement is not None:
579 stmt = FromStatement(self._raw_columns, self._statement)
580 stmt.__dict__.update(
581 _with_options=self._with_options,
582 _with_context_options=self._with_context_options,
583 _compile_options=compile_options,
584 _execution_options=self._execution_options,
585 _propagate_attrs=self._propagate_attrs,
586 )
587 else:
588 # Query / select() internal attributes are 99% cross-compatible
589 stmt = Select._create_raw_select(**self.__dict__)
590 stmt.__dict__.update(
591 _label_style=self._label_style,
592 _compile_options=compile_options,
593 _propagate_attrs=self._propagate_attrs,
594 )
595 stmt.__dict__.pop("session", None)
597 # ensure the ORM context is used to compile the statement, even
598 # if it has no ORM entities. This is so ORM-only things like
599 # _legacy_joins are picked up that wouldn't be picked up by the
600 # Core statement context
601 if "compile_state_plugin" not in stmt._propagate_attrs:
602 stmt._propagate_attrs = stmt._propagate_attrs.union(
603 {"compile_state_plugin": "orm", "plugin_subject": None}
604 )
606 return stmt
608 def subquery(
609 self,
610 name: Optional[str] = None,
611 with_labels: bool = False,
612 reduce_columns: bool = False,
613 ) -> Subquery:
614 """Return the full SELECT statement represented by
615 this :class:`_query.Query`, embedded within an
616 :class:`_expression.Alias`.
618 Eager JOIN generation within the query is disabled.
620 .. seealso::
622 :meth:`_sql.Select.subquery` - v2 comparable method.
624 :param name: string name to be assigned as the alias;
625 this is passed through to :meth:`_expression.FromClause.alias`.
626 If ``None``, a name will be deterministically generated
627 at compile time.
629 :param with_labels: if True, :meth:`.with_labels` will be called
630 on the :class:`_query.Query` first to apply table-qualified labels
631 to all columns.
633 :param reduce_columns: if True,
634 :meth:`_expression.Select.reduce_columns` will
635 be called on the resulting :func:`_expression.select` construct,
636 to remove same-named columns where one also refers to the other
637 via foreign key or WHERE clause equivalence.
639 """
640 q = self.enable_eagerloads(False)
641 if with_labels:
642 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
644 stmt = q._get_select_statement_only()
646 if TYPE_CHECKING:
647 assert isinstance(stmt, Select)
649 if reduce_columns:
650 stmt = stmt.reduce_columns()
651 return stmt.subquery(name=name)
653 def cte(
654 self,
655 name: Optional[str] = None,
656 recursive: bool = False,
657 nesting: bool = False,
658 ) -> CTE:
659 r"""Return the full SELECT statement represented by this
660 :class:`_query.Query` represented as a common table expression (CTE).
662 Parameters and usage are the same as those of the
663 :meth:`_expression.SelectBase.cte` method; see that method for
664 further details.
666 Here is the `PostgreSQL WITH
667 RECURSIVE example
668 <https://www.postgresql.org/docs/current/static/queries-with.html>`_.
669 Note that, in this example, the ``included_parts`` cte and the
670 ``incl_alias`` alias of it are Core selectables, which
671 means the columns are accessed via the ``.c.`` attribute. The
672 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
673 ``Part`` entity, so column-mapped attributes are available
674 directly::
676 from sqlalchemy.orm import aliased
679 class Part(Base):
680 __tablename__ = "part"
681 part = Column(String, primary_key=True)
682 sub_part = Column(String, primary_key=True)
683 quantity = Column(Integer)
686 included_parts = (
687 session.query(Part.sub_part, Part.part, Part.quantity)
688 .filter(Part.part == "our part")
689 .cte(name="included_parts", recursive=True)
690 )
692 incl_alias = aliased(included_parts, name="pr")
693 parts_alias = aliased(Part, name="p")
694 included_parts = included_parts.union_all(
695 session.query(
696 parts_alias.sub_part, parts_alias.part, parts_alias.quantity
697 ).filter(parts_alias.part == incl_alias.c.sub_part)
698 )
700 q = session.query(
701 included_parts.c.sub_part,
702 func.sum(included_parts.c.quantity).label("total_quantity"),
703 ).group_by(included_parts.c.sub_part)
705 .. seealso::
707 :meth:`_sql.Select.cte` - v2 equivalent method.
709 """ # noqa: E501
710 return (
711 self.enable_eagerloads(False)
712 ._get_select_statement_only()
713 .cte(name=name, recursive=recursive, nesting=nesting)
714 )
716 def label(self, name: Optional[str]) -> Label[Any]:
717 """Return the full SELECT statement represented by this
718 :class:`_query.Query`, converted
719 to a scalar subquery with a label of the given name.
721 .. seealso::
723 :meth:`_sql.Select.label` - v2 comparable method.
725 """
727 return (
728 self.enable_eagerloads(False)
729 ._get_select_statement_only()
730 .label(name)
731 )
733 @overload
734 def as_scalar( # type: ignore[overload-overlap]
735 self: Query[Tuple[_MAYBE_ENTITY]],
736 ) -> ScalarSelect[_MAYBE_ENTITY]: ...
738 @overload
739 def as_scalar(
740 self: Query[Tuple[_NOT_ENTITY]],
741 ) -> ScalarSelect[_NOT_ENTITY]: ...
743 @overload
744 def as_scalar(self) -> ScalarSelect[Any]: ...
746 @util.deprecated(
747 "1.4",
748 "The :meth:`_query.Query.as_scalar` method is deprecated and will be "
749 "removed in a future release. Please refer to "
750 ":meth:`_query.Query.scalar_subquery`.",
751 )
752 def as_scalar(self) -> ScalarSelect[Any]:
753 """Return the full SELECT statement represented by this
754 :class:`_query.Query`, converted to a scalar subquery.
756 """
757 return self.scalar_subquery()
759 @overload
760 def scalar_subquery(
761 self: Query[Tuple[_MAYBE_ENTITY]],
762 ) -> ScalarSelect[Any]: ...
764 @overload
765 def scalar_subquery(
766 self: Query[Tuple[_NOT_ENTITY]],
767 ) -> ScalarSelect[_NOT_ENTITY]: ...
769 @overload
770 def scalar_subquery(self) -> ScalarSelect[Any]: ...
772 def scalar_subquery(self) -> ScalarSelect[Any]:
773 """Return the full SELECT statement represented by this
774 :class:`_query.Query`, converted to a scalar subquery.
776 Analogous to
777 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
779 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
780 method replaces the :meth:`_query.Query.as_scalar` method.
782 .. seealso::
784 :meth:`_sql.Select.scalar_subquery` - v2 comparable method.
786 """
788 return (
789 self.enable_eagerloads(False)
790 ._get_select_statement_only()
791 .scalar_subquery()
792 )
794 @property
795 def selectable(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
796 """Return the :class:`_expression.Select` object emitted by this
797 :class:`_query.Query`.
799 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
801 query.enable_eagerloads(False).with_labels().statement
803 """
804 return self.__clause_element__()
806 def __clause_element__(
807 self,
808 ) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
809 return (
810 self._with_compile_options(
811 _enable_eagerloads=False, _render_for_subquery=True
812 )
813 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
814 .statement
815 )
817 @overload
818 def only_return_tuples(
819 self: Query[_O], value: Literal[True]
820 ) -> RowReturningQuery[Tuple[_O]]: ...
822 @overload
823 def only_return_tuples(
824 self: Query[_O], value: Literal[False]
825 ) -> Query[_O]: ...
827 @_generative
828 def only_return_tuples(self, value: bool) -> Query[Any]:
829 """When set to True, the query results will always be a
830 :class:`.Row` object.
832 This can change a query that normally returns a single entity
833 as a scalar to return a :class:`.Row` result in all cases.
835 .. seealso::
837 :meth:`.Query.tuples` - returns tuples, but also at the typing
838 level will type results as ``Tuple``.
840 :meth:`_query.Query.is_single_entity`
842 :meth:`_engine.Result.tuples` - v2 comparable method.
844 """
845 self.load_options += dict(_only_return_tuples=value)
846 return self
848 @property
849 def is_single_entity(self) -> bool:
850 """Indicates if this :class:`_query.Query`
851 returns tuples or single entities.
853 Returns True if this query returns a single entity for each instance
854 in its result list, and False if this query returns a tuple of entities
855 for each result.
857 .. versionadded:: 1.3.11
859 .. seealso::
861 :meth:`_query.Query.only_return_tuples`
863 """
864 return (
865 not self.load_options._only_return_tuples
866 and len(self._raw_columns) == 1
867 and "parententity" in self._raw_columns[0]._annotations
868 and isinstance(
869 self._raw_columns[0]._annotations["parententity"],
870 ORMColumnsClauseRole,
871 )
872 )
874 @_generative
875 def enable_eagerloads(self, value: bool) -> Self:
876 """Control whether or not eager joins and subqueries are
877 rendered.
879 When set to False, the returned Query will not render
880 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
881 :func:`~sqlalchemy.orm.subqueryload` options
882 or mapper-level ``lazy='joined'``/``lazy='subquery'``
883 configurations.
885 This is used primarily when nesting the Query's
886 statement into a subquery or other
887 selectable, or when using :meth:`_query.Query.yield_per`.
889 """
890 self._compile_options += {"_enable_eagerloads": value}
891 return self
893 @_generative
894 def _with_compile_options(self, **opt: Any) -> Self:
895 self._compile_options += opt
896 return self
898 @util.became_legacy_20(
899 ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`",
900 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
901 "instead.",
902 )
903 def with_labels(self) -> Self:
904 return self.set_label_style(
905 SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL
906 )
908 apply_labels = with_labels
910 @property
911 def get_label_style(self) -> SelectLabelStyle:
912 """
913 Retrieve the current label style.
915 .. versionadded:: 1.4
917 .. seealso::
919 :meth:`_sql.Select.get_label_style` - v2 equivalent method.
921 """
922 return self._label_style
924 def set_label_style(self, style: SelectLabelStyle) -> Self:
925 """Apply column labels to the return value of Query.statement.
927 Indicates that this Query's `statement` accessor should return
928 a SELECT statement that applies labels to all columns in the
929 form <tablename>_<columnname>; this is commonly used to
930 disambiguate columns from multiple tables which have the same
931 name.
933 When the `Query` actually issues SQL to load rows, it always
934 uses column labeling.
936 .. note:: The :meth:`_query.Query.set_label_style` method *only* applies
937 the output of :attr:`_query.Query.statement`, and *not* to any of
938 the result-row invoking systems of :class:`_query.Query` itself,
939 e.g.
940 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
941 To execute
942 a query using :meth:`_query.Query.set_label_style`, invoke the
943 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
945 result = session.execute(
946 query.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).statement
947 )
949 .. versionadded:: 1.4
952 .. seealso::
954 :meth:`_sql.Select.set_label_style` - v2 equivalent method.
956 """ # noqa
957 if self._label_style is not style:
958 self = self._generate()
959 self._label_style = style
960 return self
962 @_generative
963 def enable_assertions(self, value: bool) -> Self:
964 """Control whether assertions are generated.
966 When set to False, the returned Query will
967 not assert its state before certain operations,
968 including that LIMIT/OFFSET has not been applied
969 when filter() is called, no criterion exists
970 when get() is called, and no "from_statement()"
971 exists when filter()/order_by()/group_by() etc.
972 is called. This more permissive mode is used by
973 custom Query subclasses to specify criterion or
974 other modifiers outside of the usual usage patterns.
976 Care should be taken to ensure that the usage
977 pattern is even possible. A statement applied
978 by from_statement() will override any criterion
979 set by filter() or order_by(), for example.
981 """
982 self._enable_assertions = value
983 return self
985 @property
986 def whereclause(self) -> Optional[ColumnElement[bool]]:
987 """A readonly attribute which returns the current WHERE criterion for
988 this Query.
990 This returned value is a SQL expression construct, or ``None`` if no
991 criterion has been established.
993 .. seealso::
995 :attr:`_sql.Select.whereclause` - v2 equivalent property.
997 """
998 return BooleanClauseList._construct_for_whereclause(
999 self._where_criteria
1000 )
1002 @_generative
1003 def _with_current_path(self, path: PathRegistry) -> Self:
1004 """indicate that this query applies to objects loaded
1005 within a certain path.
1007 Used by deferred loaders (see strategies.py) which transfer
1008 query options from an originating query to a newly generated
1009 query intended for the deferred load.
1011 """
1012 self._compile_options += {"_current_path": path}
1013 return self
1015 @_generative
1016 def yield_per(self, count: int) -> Self:
1017 r"""Yield only ``count`` rows at a time.
1019 The purpose of this method is when fetching very large result sets
1020 (> 10K rows), to batch results in sub-collections and yield them
1021 out partially, so that the Python interpreter doesn't need to declare
1022 very large areas of memory which is both time consuming and leads
1023 to excessive memory use. The performance from fetching hundreds of
1024 thousands of rows can often double when a suitable yield-per setting
1025 (e.g. approximately 1000) is used, even with DBAPIs that buffer
1026 rows (which are most).
1028 As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
1029 equivalent to using the ``yield_per`` execution option at the ORM
1030 level. See the section :ref:`orm_queryguide_yield_per` for further
1031 background on this option.
1033 .. seealso::
1035 :ref:`orm_queryguide_yield_per`
1037 """
1038 self.load_options += {"_yield_per": count}
1039 return self
1041 @util.became_legacy_20(
1042 ":meth:`_orm.Query.get`",
1043 alternative="The method is now available as :meth:`_orm.Session.get`",
1044 )
1045 def get(self, ident: _PKIdentityArgument) -> Optional[Any]:
1046 """Return an instance based on the given primary key identifier,
1047 or ``None`` if not found.
1049 E.g.::
1051 my_user = session.query(User).get(5)
1053 some_object = session.query(VersionedFoo).get((5, 10))
1055 some_object = session.query(VersionedFoo).get({"id": 5, "version_id": 10})
1057 :meth:`_query.Query.get` is special in that it provides direct
1058 access to the identity map of the owning :class:`.Session`.
1059 If the given primary key identifier is present
1060 in the local identity map, the object is returned
1061 directly from this collection and no SQL is emitted,
1062 unless the object has been marked fully expired.
1063 If not present,
1064 a SELECT is performed in order to locate the object.
1066 :meth:`_query.Query.get` also will perform a check if
1067 the object is present in the identity map and
1068 marked as expired - a SELECT
1069 is emitted to refresh the object as well as to
1070 ensure that the row is still present.
1071 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
1073 :meth:`_query.Query.get` is only used to return a single
1074 mapped instance, not multiple instances or
1075 individual column constructs, and strictly
1076 on a single primary key value. The originating
1077 :class:`_query.Query` must be constructed in this way,
1078 i.e. against a single mapped entity,
1079 with no additional filtering criterion. Loading
1080 options via :meth:`_query.Query.options` may be applied
1081 however, and will be used if the object is not
1082 yet locally present.
1084 :param ident: A scalar, tuple, or dictionary representing the
1085 primary key. For a composite (e.g. multiple column) primary key,
1086 a tuple or dictionary should be passed.
1088 For a single-column primary key, the scalar calling form is typically
1089 the most expedient. If the primary key of a row is the value "5",
1090 the call looks like::
1092 my_object = query.get(5)
1094 The tuple form contains primary key values typically in
1095 the order in which they correspond to the mapped
1096 :class:`_schema.Table`
1097 object's primary key columns, or if the
1098 :paramref:`_orm.Mapper.primary_key` configuration parameter were
1099 used, in
1100 the order used for that parameter. For example, if the primary key
1101 of a row is represented by the integer
1102 digits "5, 10" the call would look like::
1104 my_object = query.get((5, 10))
1106 The dictionary form should include as keys the mapped attribute names
1107 corresponding to each element of the primary key. If the mapped class
1108 has the attributes ``id``, ``version_id`` as the attributes which
1109 store the object's primary key value, the call would look like::
1111 my_object = query.get({"id": 5, "version_id": 10})
1113 .. versionadded:: 1.3 the :meth:`_query.Query.get`
1114 method now optionally
1115 accepts a dictionary of attribute names to values in order to
1116 indicate a primary key identifier.
1119 :return: The object instance, or ``None``.
1121 """ # noqa: E501
1122 self._no_criterion_assertion("get", order_by=False, distinct=False)
1124 # we still implement _get_impl() so that baked query can override
1125 # it
1126 return self._get_impl(ident, loading.load_on_pk_identity)
1128 def _get_impl(
1129 self,
1130 primary_key_identity: _PKIdentityArgument,
1131 db_load_fn: Callable[..., Any],
1132 identity_token: Optional[Any] = None,
1133 ) -> Optional[Any]:
1134 mapper = self._only_full_mapper_zero("get")
1135 return self.session._get_impl(
1136 mapper,
1137 primary_key_identity,
1138 db_load_fn,
1139 populate_existing=self.load_options._populate_existing,
1140 with_for_update=self._for_update_arg,
1141 options=self._with_options,
1142 identity_token=identity_token,
1143 execution_options=self._execution_options,
1144 )
1146 @property
1147 def lazy_loaded_from(self) -> Optional[InstanceState[Any]]:
1148 """An :class:`.InstanceState` that is using this :class:`_query.Query`
1149 for a lazy load operation.
1151 .. deprecated:: 1.4 This attribute should be viewed via the
1152 :attr:`.ORMExecuteState.lazy_loaded_from` attribute, within
1153 the context of the :meth:`.SessionEvents.do_orm_execute`
1154 event.
1156 .. seealso::
1158 :attr:`.ORMExecuteState.lazy_loaded_from`
1160 """
1161 return self.load_options._lazy_loaded_from # type: ignore
1163 @property
1164 def _current_path(self) -> PathRegistry:
1165 return self._compile_options._current_path # type: ignore
1167 @_generative
1168 def correlate(
1169 self,
1170 *fromclauses: Union[Literal[None, False], _FromClauseArgument],
1171 ) -> Self:
1172 """Return a :class:`.Query` construct which will correlate the given
1173 FROM clauses to that of an enclosing :class:`.Query` or
1174 :func:`~.expression.select`.
1176 The method here accepts mapped classes, :func:`.aliased` constructs,
1177 and :class:`_orm.Mapper` constructs as arguments, which are resolved
1178 into expression constructs, in addition to appropriate expression
1179 constructs.
1181 The correlation arguments are ultimately passed to
1182 :meth:`_expression.Select.correlate`
1183 after coercion to expression constructs.
1185 The correlation arguments take effect in such cases
1186 as when :meth:`_query.Query.from_self` is used, or when
1187 a subquery as returned by :meth:`_query.Query.subquery` is
1188 embedded in another :func:`_expression.select` construct.
1190 .. seealso::
1192 :meth:`_sql.Select.correlate` - v2 equivalent method.
1194 """
1196 self._auto_correlate = False
1197 if fromclauses and fromclauses[0] in {None, False}:
1198 self._correlate = ()
1199 else:
1200 self._correlate = self._correlate + tuple(
1201 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
1202 )
1203 return self
1205 @_generative
1206 def autoflush(self, setting: bool) -> Self:
1207 """Return a Query with a specific 'autoflush' setting.
1209 As of SQLAlchemy 1.4, the :meth:`_orm.Query.autoflush` method
1210 is equivalent to using the ``autoflush`` execution option at the
1211 ORM level. See the section :ref:`orm_queryguide_autoflush` for
1212 further background on this option.
1214 """
1215 self.load_options += {"_autoflush": setting}
1216 return self
1218 @_generative
1219 def populate_existing(self) -> Self:
1220 """Return a :class:`_query.Query`
1221 that will expire and refresh all instances
1222 as they are loaded, or reused from the current :class:`.Session`.
1224 As of SQLAlchemy 1.4, the :meth:`_orm.Query.populate_existing` method
1225 is equivalent to using the ``populate_existing`` execution option at
1226 the ORM level. See the section :ref:`orm_queryguide_populate_existing`
1227 for further background on this option.
1229 """
1230 self.load_options += {"_populate_existing": True}
1231 return self
1233 @_generative
1234 def _with_invoke_all_eagers(self, value: bool) -> Self:
1235 """Set the 'invoke all eagers' flag which causes joined- and
1236 subquery loaders to traverse into already-loaded related objects
1237 and collections.
1239 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1241 """
1242 self.load_options += {"_invoke_all_eagers": value}
1243 return self
1245 @util.became_legacy_20(
1246 ":meth:`_orm.Query.with_parent`",
1247 alternative="Use the :func:`_orm.with_parent` standalone construct.",
1248 )
1249 @util.preload_module("sqlalchemy.orm.relationships")
1250 def with_parent(
1251 self,
1252 instance: object,
1253 property: Optional[ # noqa: A002
1254 attributes.QueryableAttribute[Any]
1255 ] = None,
1256 from_entity: Optional[_ExternalEntityType[Any]] = None,
1257 ) -> Self:
1258 """Add filtering criterion that relates the given instance
1259 to a child object or collection, using its attribute state
1260 as well as an established :func:`_orm.relationship()`
1261 configuration.
1263 The method uses the :func:`.with_parent` function to generate
1264 the clause, the result of which is passed to
1265 :meth:`_query.Query.filter`.
1267 Parameters are the same as :func:`.with_parent`, with the exception
1268 that the given property can be None, in which case a search is
1269 performed against this :class:`_query.Query` object's target mapper.
1271 :param instance:
1272 An instance which has some :func:`_orm.relationship`.
1274 :param property:
1275 Class bound attribute which indicates
1276 what relationship from the instance should be used to reconcile the
1277 parent/child relationship.
1279 :param from_entity:
1280 Entity in which to consider as the left side. This defaults to the
1281 "zero" entity of the :class:`_query.Query` itself.
1283 """
1284 relationships = util.preloaded.orm_relationships
1286 if from_entity:
1287 entity_zero = inspect(from_entity)
1288 else:
1289 entity_zero = _legacy_filter_by_entity_zero(self)
1290 if property is None:
1291 # TODO: deprecate, property has to be supplied
1292 mapper = object_mapper(instance)
1294 for prop in mapper.iterate_properties:
1295 if (
1296 isinstance(prop, relationships.RelationshipProperty)
1297 and prop.mapper is entity_zero.mapper # type: ignore
1298 ):
1299 property = prop # type: ignore # noqa: A001
1300 break
1301 else:
1302 raise sa_exc.InvalidRequestError(
1303 "Could not locate a property which relates instances "
1304 "of class '%s' to instances of class '%s'"
1305 % (
1306 entity_zero.mapper.class_.__name__, # type: ignore
1307 instance.__class__.__name__,
1308 )
1309 )
1311 return self.filter(
1312 with_parent(
1313 instance,
1314 property, # type: ignore
1315 entity_zero.entity, # type: ignore
1316 )
1317 )
1319 @_generative
1320 def add_entity(
1321 self,
1322 entity: _EntityType[Any],
1323 alias: Optional[Union[Alias, Subquery]] = None,
1324 ) -> Query[Any]:
1325 """add a mapped entity to the list of result columns
1326 to be returned.
1328 .. seealso::
1330 :meth:`_sql.Select.add_columns` - v2 comparable method.
1331 """
1333 if alias is not None:
1334 # TODO: deprecate
1335 entity = AliasedClass(entity, alias)
1337 self._raw_columns = list(self._raw_columns)
1339 self._raw_columns.append(
1340 coercions.expect(
1341 roles.ColumnsClauseRole, entity, apply_propagate_attrs=self
1342 )
1343 )
1344 return self
1346 @_generative
1347 def with_session(self, session: Session) -> Self:
1348 """Return a :class:`_query.Query` that will use the given
1349 :class:`.Session`.
1351 While the :class:`_query.Query`
1352 object is normally instantiated using the
1353 :meth:`.Session.query` method, it is legal to build the
1354 :class:`_query.Query`
1355 directly without necessarily using a :class:`.Session`. Such a
1356 :class:`_query.Query` object, or any :class:`_query.Query`
1357 already associated
1358 with a different :class:`.Session`, can produce a new
1359 :class:`_query.Query`
1360 object associated with a target session using this method::
1362 from sqlalchemy.orm import Query
1364 query = Query([MyClass]).filter(MyClass.id == 5)
1366 result = query.with_session(my_session).one()
1368 """
1370 self.session = session
1371 return self
1373 def _legacy_from_self(
1374 self, *entities: _ColumnsClauseArgument[Any]
1375 ) -> Self:
1376 # used for query.count() as well as for the same
1377 # function in BakedQuery, as well as some old tests in test_baked.py.
1379 fromclause = (
1380 self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
1381 .correlate(None)
1382 .subquery()
1383 ._anonymous_fromclause()
1384 )
1386 q = self._from_selectable(fromclause)
1388 if entities:
1389 q._set_entities(entities)
1390 return q
1392 @_generative
1393 def _set_enable_single_crit(self, val: bool) -> Self:
1394 self._compile_options += {"_enable_single_crit": val}
1395 return self
1397 @_generative
1398 def _from_selectable(
1399 self, fromclause: FromClause, set_entity_from: bool = True
1400 ) -> Self:
1401 for attr in (
1402 "_where_criteria",
1403 "_order_by_clauses",
1404 "_group_by_clauses",
1405 "_limit_clause",
1406 "_offset_clause",
1407 "_last_joined_entity",
1408 "_setup_joins",
1409 "_memoized_select_entities",
1410 "_distinct",
1411 "_distinct_on",
1412 "_having_criteria",
1413 "_prefixes",
1414 "_suffixes",
1415 ):
1416 self.__dict__.pop(attr, None)
1417 self._set_select_from([fromclause], set_entity_from)
1418 self._compile_options += {
1419 "_enable_single_crit": False,
1420 }
1422 return self
1424 @util.deprecated(
1425 "1.4",
1426 ":meth:`_query.Query.values` "
1427 "is deprecated and will be removed in a "
1428 "future release. Please use :meth:`_query.Query.with_entities`",
1429 )
1430 def values(self, *columns: _ColumnsClauseArgument[Any]) -> Iterable[Any]:
1431 """Return an iterator yielding result tuples corresponding
1432 to the given list of columns
1434 """
1435 return self._values_no_warn(*columns)
1437 _values = values
1439 def _values_no_warn(
1440 self, *columns: _ColumnsClauseArgument[Any]
1441 ) -> Iterable[Any]:
1442 if not columns:
1443 return iter(())
1444 q = self._clone().enable_eagerloads(False)
1445 q._set_entities(columns)
1446 if not q.load_options._yield_per:
1447 q.load_options += {"_yield_per": 10}
1448 return iter(q)
1450 @util.deprecated(
1451 "1.4",
1452 ":meth:`_query.Query.value` "
1453 "is deprecated and will be removed in a "
1454 "future release. Please use :meth:`_query.Query.with_entities` "
1455 "in combination with :meth:`_query.Query.scalar`",
1456 )
1457 def value(self, column: _ColumnExpressionArgument[Any]) -> Any:
1458 """Return a scalar result corresponding to the given
1459 column expression.
1461 """
1462 try:
1463 return next(self._values_no_warn(column))[0] # type: ignore
1464 except StopIteration:
1465 return None
1467 @overload
1468 def with_entities(self, _entity: _EntityType[_O]) -> Query[_O]: ...
1470 @overload
1471 def with_entities(
1472 self,
1473 _colexpr: roles.TypedColumnsClauseRole[_T],
1474 ) -> RowReturningQuery[Tuple[_T]]: ...
1476 # START OVERLOADED FUNCTIONS self.with_entities RowReturningQuery 2-8
1478 # code within this block is **programmatically,
1479 # statically generated** by tools/generate_tuple_map_overloads.py
1481 @overload
1482 def with_entities(
1483 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
1484 ) -> RowReturningQuery[Tuple[_T0, _T1]]: ...
1486 @overload
1487 def with_entities(
1488 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
1489 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2]]: ...
1491 @overload
1492 def with_entities(
1493 self,
1494 __ent0: _TCCA[_T0],
1495 __ent1: _TCCA[_T1],
1496 __ent2: _TCCA[_T2],
1497 __ent3: _TCCA[_T3],
1498 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3]]: ...
1500 @overload
1501 def with_entities(
1502 self,
1503 __ent0: _TCCA[_T0],
1504 __ent1: _TCCA[_T1],
1505 __ent2: _TCCA[_T2],
1506 __ent3: _TCCA[_T3],
1507 __ent4: _TCCA[_T4],
1508 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
1510 @overload
1511 def with_entities(
1512 self,
1513 __ent0: _TCCA[_T0],
1514 __ent1: _TCCA[_T1],
1515 __ent2: _TCCA[_T2],
1516 __ent3: _TCCA[_T3],
1517 __ent4: _TCCA[_T4],
1518 __ent5: _TCCA[_T5],
1519 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
1521 @overload
1522 def with_entities(
1523 self,
1524 __ent0: _TCCA[_T0],
1525 __ent1: _TCCA[_T1],
1526 __ent2: _TCCA[_T2],
1527 __ent3: _TCCA[_T3],
1528 __ent4: _TCCA[_T4],
1529 __ent5: _TCCA[_T5],
1530 __ent6: _TCCA[_T6],
1531 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
1533 @overload
1534 def with_entities(
1535 self,
1536 __ent0: _TCCA[_T0],
1537 __ent1: _TCCA[_T1],
1538 __ent2: _TCCA[_T2],
1539 __ent3: _TCCA[_T3],
1540 __ent4: _TCCA[_T4],
1541 __ent5: _TCCA[_T5],
1542 __ent6: _TCCA[_T6],
1543 __ent7: _TCCA[_T7],
1544 ) -> RowReturningQuery[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...
1546 # END OVERLOADED FUNCTIONS self.with_entities
1548 @overload
1549 def with_entities(
1550 self, *entities: _ColumnsClauseArgument[Any]
1551 ) -> Query[Any]: ...
1553 @_generative
1554 def with_entities(
1555 self, *entities: _ColumnsClauseArgument[Any], **__kw: Any
1556 ) -> Query[Any]:
1557 r"""Return a new :class:`_query.Query`
1558 replacing the SELECT list with the
1559 given entities.
1561 e.g.::
1563 # Users, filtered on some arbitrary criterion
1564 # and then ordered by related email address
1565 q = (
1566 session.query(User)
1567 .join(User.address)
1568 .filter(User.name.like("%ed%"))
1569 .order_by(Address.email)
1570 )
1572 # given *only* User.id==5, Address.email, and 'q', what
1573 # would the *next* User in the result be ?
1574 subq = (
1575 q.with_entities(Address.email)
1576 .order_by(None)
1577 .filter(User.id == 5)
1578 .subquery()
1579 )
1580 q = q.join((subq, subq.c.email < Address.email)).limit(1)
1582 .. seealso::
1584 :meth:`_sql.Select.with_only_columns` - v2 comparable method.
1585 """
1586 if __kw:
1587 raise _no_kw()
1589 # Query has all the same fields as Select for this operation
1590 # this could in theory be based on a protocol but not sure if it's
1591 # worth it
1592 _MemoizedSelectEntities._generate_for_statement(self) # type: ignore
1593 self._set_entities(entities)
1594 return self
1596 @_generative
1597 def add_columns(
1598 self, *column: _ColumnExpressionArgument[Any]
1599 ) -> Query[Any]:
1600 """Add one or more column expressions to the list
1601 of result columns to be returned.
1603 .. seealso::
1605 :meth:`_sql.Select.add_columns` - v2 comparable method.
1606 """
1608 self._raw_columns = list(self._raw_columns)
1610 self._raw_columns.extend(
1611 coercions.expect(
1612 roles.ColumnsClauseRole,
1613 c,
1614 apply_propagate_attrs=self,
1615 post_inspect=True,
1616 )
1617 for c in column
1618 )
1619 return self
1621 @util.deprecated(
1622 "1.4",
1623 ":meth:`_query.Query.add_column` "
1624 "is deprecated and will be removed in a "
1625 "future release. Please use :meth:`_query.Query.add_columns`",
1626 )
1627 def add_column(self, column: _ColumnExpressionArgument[Any]) -> Query[Any]:
1628 """Add a column expression to the list of result columns to be
1629 returned.
1631 """
1632 return self.add_columns(column)
1634 @_generative
1635 def options(self, *args: ExecutableOption) -> Self:
1636 """Return a new :class:`_query.Query` object,
1637 applying the given list of
1638 mapper options.
1640 Most supplied options regard changing how column- and
1641 relationship-mapped attributes are loaded.
1643 .. seealso::
1645 :ref:`loading_columns`
1647 :ref:`relationship_loader_options`
1649 """
1651 opts = tuple(util.flatten_iterator(args))
1652 if self._compile_options._current_path:
1653 # opting for lower method overhead for the checks
1654 for opt in opts:
1655 if not opt._is_core and opt._is_legacy_option: # type: ignore
1656 opt.process_query_conditionally(self) # type: ignore
1657 else:
1658 for opt in opts:
1659 if not opt._is_core and opt._is_legacy_option: # type: ignore
1660 opt.process_query(self) # type: ignore
1662 self._with_options += opts
1663 return self
1665 def with_transformation(
1666 self, fn: Callable[[Query[Any]], Query[Any]]
1667 ) -> Query[Any]:
1668 """Return a new :class:`_query.Query` object transformed by
1669 the given function.
1671 E.g.::
1673 def filter_something(criterion):
1674 def transform(q):
1675 return q.filter(criterion)
1677 return transform
1680 q = q.with_transformation(filter_something(x == 5))
1682 This allows ad-hoc recipes to be created for :class:`_query.Query`
1683 objects.
1685 """
1686 return fn(self)
1688 def get_execution_options(self) -> _ImmutableExecuteOptions:
1689 """Get the non-SQL options which will take effect during execution.
1691 .. versionadded:: 1.3
1693 .. seealso::
1695 :meth:`_query.Query.execution_options`
1697 :meth:`_sql.Select.get_execution_options` - v2 comparable method.
1699 """
1700 return self._execution_options
1702 @overload
1703 def execution_options(
1704 self,
1705 *,
1706 compiled_cache: Optional[CompiledCacheType] = ...,
1707 logging_token: str = ...,
1708 isolation_level: IsolationLevel = ...,
1709 no_parameters: bool = False,
1710 stream_results: bool = False,
1711 max_row_buffer: int = ...,
1712 yield_per: int = ...,
1713 insertmanyvalues_page_size: int = ...,
1714 schema_translate_map: Optional[SchemaTranslateMapType] = ...,
1715 populate_existing: bool = False,
1716 autoflush: bool = False,
1717 preserve_rowcount: bool = False,
1718 **opt: Any,
1719 ) -> Self: ...
1721 @overload
1722 def execution_options(self, **opt: Any) -> Self: ...
1724 @_generative
1725 def execution_options(self, **kwargs: Any) -> Self:
1726 """Set non-SQL options which take effect during execution.
1728 Options allowed here include all of those accepted by
1729 :meth:`_engine.Connection.execution_options`, as well as a series
1730 of ORM specific options:
1732 ``populate_existing=True`` - equivalent to using
1733 :meth:`_orm.Query.populate_existing`
1735 ``autoflush=True|False`` - equivalent to using
1736 :meth:`_orm.Query.autoflush`
1738 ``yield_per=<value>`` - equivalent to using
1739 :meth:`_orm.Query.yield_per`
1741 Note that the ``stream_results`` execution option is enabled
1742 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1743 method or execution option is used.
1745 .. versionadded:: 1.4 - added ORM options to
1746 :meth:`_orm.Query.execution_options`
1748 The execution options may also be specified on a per execution basis
1749 when using :term:`2.0 style` queries via the
1750 :paramref:`_orm.Session.execution_options` parameter.
1752 .. warning:: The
1753 :paramref:`_engine.Connection.execution_options.stream_results`
1754 parameter should not be used at the level of individual ORM
1755 statement executions, as the :class:`_orm.Session` will not track
1756 objects from different schema translate maps within a single
1757 session. For multiple schema translate maps within the scope of a
1758 single :class:`_orm.Session`, see :ref:`examples_sharding`.
1761 .. seealso::
1763 :ref:`engine_stream_results`
1765 :meth:`_query.Query.get_execution_options`
1767 :meth:`_sql.Select.execution_options` - v2 equivalent method.
1769 """
1770 self._execution_options = self._execution_options.union(kwargs)
1771 return self
1773 @_generative
1774 def with_for_update(
1775 self,
1776 *,
1777 nowait: bool = False,
1778 read: bool = False,
1779 of: Optional[_ForUpdateOfArgument] = None,
1780 skip_locked: bool = False,
1781 key_share: bool = False,
1782 ) -> Self:
1783 """return a new :class:`_query.Query`
1784 with the specified options for the
1785 ``FOR UPDATE`` clause.
1787 The behavior of this method is identical to that of
1788 :meth:`_expression.GenerativeSelect.with_for_update`.
1789 When called with no arguments,
1790 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1791 appended. When additional arguments are specified, backend-specific
1792 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1793 can take effect.
1795 E.g.::
1797 q = (
1798 sess.query(User)
1799 .populate_existing()
1800 .with_for_update(nowait=True, of=User)
1801 )
1803 The above query on a PostgreSQL backend will render like:
1805 .. sourcecode:: sql
1807 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1809 .. warning::
1811 Using ``with_for_update`` in the context of eager loading
1812 relationships is not officially supported or recommended by
1813 SQLAlchemy and may not work with certain queries on various
1814 database backends. When ``with_for_update`` is successfully used
1815 with a query that involves :func:`_orm.joinedload`, SQLAlchemy will
1816 attempt to emit SQL that locks all involved tables.
1818 .. note:: It is generally a good idea to combine the use of the
1819 :meth:`_orm.Query.populate_existing` method when using the
1820 :meth:`_orm.Query.with_for_update` method. The purpose of
1821 :meth:`_orm.Query.populate_existing` is to force all the data read
1822 from the SELECT to be populated into the ORM objects returned,
1823 even if these objects are already in the :term:`identity map`.
1825 .. seealso::
1827 :meth:`_expression.GenerativeSelect.with_for_update`
1828 - Core level method with
1829 full argument and behavioral description.
1831 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1832 objects already loaded in the identity map.
1834 """ # noqa: E501
1836 self._for_update_arg = ForUpdateArg(
1837 read=read,
1838 nowait=nowait,
1839 of=of,
1840 skip_locked=skip_locked,
1841 key_share=key_share,
1842 )
1843 return self
1845 @_generative
1846 def params(
1847 self, __params: Optional[Dict[str, Any]] = None, **kw: Any
1848 ) -> Self:
1849 r"""Add values for bind parameters which may have been
1850 specified in filter().
1852 Parameters may be specified using \**kwargs, or optionally a single
1853 dictionary as the first positional argument. The reason for both is
1854 that \**kwargs is convenient, however some parameter dictionaries
1855 contain unicode keys in which case \**kwargs cannot be used.
1857 """
1858 if __params:
1859 kw.update(__params)
1860 self._params = self._params.union(kw)
1861 return self
1863 def where(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1864 """A synonym for :meth:`.Query.filter`.
1866 .. versionadded:: 1.4
1868 .. seealso::
1870 :meth:`_sql.Select.where` - v2 equivalent method.
1872 """
1873 return self.filter(*criterion)
1875 @_generative
1876 @_assertions(_no_statement_condition, _no_limit_offset)
1877 def filter(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1878 r"""Apply the given filtering criterion to a copy
1879 of this :class:`_query.Query`, using SQL expressions.
1881 e.g.::
1883 session.query(MyClass).filter(MyClass.name == "some name")
1885 Multiple criteria may be specified as comma separated; the effect
1886 is that they will be joined together using the :func:`.and_`
1887 function::
1889 session.query(MyClass).filter(MyClass.name == "some name", MyClass.id > 5)
1891 The criterion is any SQL expression object applicable to the
1892 WHERE clause of a select. String expressions are coerced
1893 into SQL expression constructs via the :func:`_expression.text`
1894 construct.
1896 .. seealso::
1898 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1900 :meth:`_sql.Select.where` - v2 equivalent method.
1902 """ # noqa: E501
1903 for crit in list(criterion):
1904 crit = coercions.expect(
1905 roles.WhereHavingRole, crit, apply_propagate_attrs=self
1906 )
1908 self._where_criteria += (crit,)
1909 return self
1911 @util.memoized_property
1912 def _last_joined_entity(
1913 self,
1914 ) -> Optional[Union[_InternalEntityType[Any], _JoinTargetElement]]:
1915 if self._setup_joins:
1916 return _determine_last_joined_entity(
1917 self._setup_joins,
1918 )
1919 else:
1920 return None
1922 def _filter_by_zero(self) -> Any:
1923 """for the filter_by() method, return the target entity for which
1924 we will attempt to derive an expression from based on string name.
1926 """
1928 if self._setup_joins:
1929 _last_joined_entity = self._last_joined_entity
1930 if _last_joined_entity is not None:
1931 return _last_joined_entity
1933 # discussion related to #7239
1934 # special check determines if we should try to derive attributes
1935 # for filter_by() from the "from object", i.e., if the user
1936 # called query.select_from(some selectable).filter_by(some_attr=value).
1937 # We don't want to do that in the case that methods like
1938 # from_self(), select_entity_from(), or a set op like union() were
1939 # called; while these methods also place a
1940 # selectable in the _from_obj collection, they also set up
1941 # the _set_base_alias boolean which turns on the whole "adapt the
1942 # entity to this selectable" thing, meaning the query still continues
1943 # to construct itself in terms of the lead entity that was passed
1944 # to query(), e.g. query(User).from_self() is still in terms of User,
1945 # and not the subquery that from_self() created. This feature of
1946 # "implicitly adapt all occurrences of entity X to some arbitrary
1947 # subquery" is the main thing I am trying to do away with in 2.0 as
1948 # users should now used aliased() for that, but I can't entirely get
1949 # rid of it due to query.union() and other set ops relying upon it.
1950 #
1951 # compare this to the base Select()._filter_by_zero() which can
1952 # just return self._from_obj[0] if present, because there is no
1953 # "_set_base_alias" feature.
1954 #
1955 # IOW, this conditional essentially detects if
1956 # "select_from(some_selectable)" has been called, as opposed to
1957 # "select_entity_from()", "from_self()"
1958 # or "union() / some_set_op()".
1959 if self._from_obj and not self._compile_options._set_base_alias:
1960 return self._from_obj[0]
1962 return self._raw_columns[0]
1964 def filter_by(self, **kwargs: Any) -> Self:
1965 r"""Apply the given filtering criterion to a copy
1966 of this :class:`_query.Query`, using keyword expressions.
1968 e.g.::
1970 session.query(MyClass).filter_by(name="some name")
1972 Multiple criteria may be specified as comma separated; the effect
1973 is that they will be joined together using the :func:`.and_`
1974 function::
1976 session.query(MyClass).filter_by(name="some name", id=5)
1978 The keyword expressions are extracted from the primary
1979 entity of the query, or the last entity that was the
1980 target of a call to :meth:`_query.Query.join`.
1982 .. seealso::
1984 :meth:`_query.Query.filter` - filter on SQL expressions.
1986 :meth:`_sql.Select.filter_by` - v2 comparable method.
1988 """
1989 from_entity = self._filter_by_zero()
1991 clauses = [
1992 _entity_namespace_key(from_entity, key) == value
1993 for key, value in kwargs.items()
1994 ]
1995 return self.filter(*clauses)
1997 @_generative
1998 def order_by(
1999 self,
2000 __first: Union[
2001 Literal[None, False, _NoArg.NO_ARG],
2002 _ColumnExpressionOrStrLabelArgument[Any],
2003 ] = _NoArg.NO_ARG,
2004 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2005 ) -> Self:
2006 """Apply one or more ORDER BY criteria to the query and return
2007 the newly resulting :class:`_query.Query`.
2009 e.g.::
2011 q = session.query(Entity).order_by(Entity.id, Entity.name)
2013 Calling this method multiple times is equivalent to calling it once
2014 with all the clauses concatenated. All existing ORDER BY criteria may
2015 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
2016 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
2018 # will erase all ORDER BY and ORDER BY new_col alone
2019 q = q.order_by(None).order_by(new_col)
2021 .. seealso::
2023 These sections describe ORDER BY in terms of :term:`2.0 style`
2024 invocation but apply to :class:`_orm.Query` as well:
2026 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
2028 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2030 :meth:`_sql.Select.order_by` - v2 equivalent method.
2032 """
2034 for assertion in (self._no_statement_condition, self._no_limit_offset):
2035 assertion("order_by")
2037 if not clauses and (__first is None or __first is False):
2038 self._order_by_clauses = ()
2039 elif __first is not _NoArg.NO_ARG:
2040 criterion = tuple(
2041 coercions.expect(roles.OrderByRole, clause)
2042 for clause in (__first,) + clauses
2043 )
2044 self._order_by_clauses += criterion
2046 return self
2048 @_generative
2049 def group_by(
2050 self,
2051 __first: Union[
2052 Literal[None, False, _NoArg.NO_ARG],
2053 _ColumnExpressionOrStrLabelArgument[Any],
2054 ] = _NoArg.NO_ARG,
2055 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2056 ) -> Self:
2057 """Apply one or more GROUP BY criterion to the query and return
2058 the newly resulting :class:`_query.Query`.
2060 All existing GROUP BY settings can be suppressed by
2061 passing ``None`` - this will suppress any GROUP BY configured
2062 on mappers as well.
2064 .. seealso::
2066 These sections describe GROUP BY in terms of :term:`2.0 style`
2067 invocation but apply to :class:`_orm.Query` as well:
2069 :ref:`tutorial_group_by_w_aggregates` - in the
2070 :ref:`unified_tutorial`
2072 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2074 :meth:`_sql.Select.group_by` - v2 equivalent method.
2076 """
2078 for assertion in (self._no_statement_condition, self._no_limit_offset):
2079 assertion("group_by")
2081 if not clauses and (__first is None or __first is False):
2082 self._group_by_clauses = ()
2083 elif __first is not _NoArg.NO_ARG:
2084 criterion = tuple(
2085 coercions.expect(roles.GroupByRole, clause)
2086 for clause in (__first,) + clauses
2087 )
2088 self._group_by_clauses += criterion
2089 return self
2091 @_generative
2092 @_assertions(_no_statement_condition, _no_limit_offset)
2093 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:
2094 r"""Apply a HAVING criterion to the query and return the
2095 newly resulting :class:`_query.Query`.
2097 :meth:`_query.Query.having` is used in conjunction with
2098 :meth:`_query.Query.group_by`.
2100 HAVING criterion makes it possible to use filters on aggregate
2101 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
2103 q = (
2104 session.query(User.id)
2105 .join(User.addresses)
2106 .group_by(User.id)
2107 .having(func.count(Address.id) > 2)
2108 )
2110 .. seealso::
2112 :meth:`_sql.Select.having` - v2 equivalent method.
2114 """
2116 for criterion in having:
2117 having_criteria = coercions.expect(
2118 roles.WhereHavingRole, criterion
2119 )
2120 self._having_criteria += (having_criteria,)
2121 return self
2123 def _set_op(self, expr_fn: Any, *q: Query[Any]) -> Self:
2124 list_of_queries = (self,) + q
2125 return self._from_selectable(expr_fn(*(list_of_queries)).subquery())
2127 def union(self, *q: Query[Any]) -> Self:
2128 """Produce a UNION of this Query against one or more queries.
2130 e.g.::
2132 q1 = sess.query(SomeClass).filter(SomeClass.foo == "bar")
2133 q2 = sess.query(SomeClass).filter(SomeClass.bar == "foo")
2135 q3 = q1.union(q2)
2137 The method accepts multiple Query objects so as to control
2138 the level of nesting. A series of ``union()`` calls such as::
2140 x.union(y).union(z).all()
2142 will nest on each ``union()``, and produces:
2144 .. sourcecode:: sql
2146 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
2147 SELECT * FROM y) UNION SELECT * FROM Z)
2149 Whereas::
2151 x.union(y, z).all()
2153 produces:
2155 .. sourcecode:: sql
2157 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
2158 SELECT * FROM Z)
2160 Note that many database backends do not allow ORDER BY to
2161 be rendered on a query called within UNION, EXCEPT, etc.
2162 To disable all ORDER BY clauses including those configured
2163 on mappers, issue ``query.order_by(None)`` - the resulting
2164 :class:`_query.Query` object will not render ORDER BY within
2165 its SELECT statement.
2167 .. seealso::
2169 :meth:`_sql.Select.union` - v2 equivalent method.
2171 """
2172 return self._set_op(expression.union, *q)
2174 def union_all(self, *q: Query[Any]) -> Self:
2175 """Produce a UNION ALL of this Query against one or more queries.
2177 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2178 that method for usage examples.
2180 .. seealso::
2182 :meth:`_sql.Select.union_all` - v2 equivalent method.
2184 """
2185 return self._set_op(expression.union_all, *q)
2187 def intersect(self, *q: Query[Any]) -> Self:
2188 """Produce an INTERSECT of this Query against one or more queries.
2190 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2191 that method for usage examples.
2193 .. seealso::
2195 :meth:`_sql.Select.intersect` - v2 equivalent method.
2197 """
2198 return self._set_op(expression.intersect, *q)
2200 def intersect_all(self, *q: Query[Any]) -> Self:
2201 """Produce an INTERSECT ALL of this Query against one or more queries.
2203 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2204 that method for usage examples.
2206 .. seealso::
2208 :meth:`_sql.Select.intersect_all` - v2 equivalent method.
2210 """
2211 return self._set_op(expression.intersect_all, *q)
2213 def except_(self, *q: Query[Any]) -> Self:
2214 """Produce an EXCEPT of this Query against one or more queries.
2216 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2217 that method for usage examples.
2219 .. seealso::
2221 :meth:`_sql.Select.except_` - v2 equivalent method.
2223 """
2224 return self._set_op(expression.except_, *q)
2226 def except_all(self, *q: Query[Any]) -> Self:
2227 """Produce an EXCEPT ALL of this Query against one or more queries.
2229 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2230 that method for usage examples.
2232 .. seealso::
2234 :meth:`_sql.Select.except_all` - v2 equivalent method.
2236 """
2237 return self._set_op(expression.except_all, *q)
2239 @_generative
2240 @_assertions(_no_statement_condition, _no_limit_offset)
2241 def join(
2242 self,
2243 target: _JoinTargetArgument,
2244 onclause: Optional[_OnClauseArgument] = None,
2245 *,
2246 isouter: bool = False,
2247 full: bool = False,
2248 ) -> Self:
2249 r"""Create a SQL JOIN against this :class:`_query.Query`
2250 object's criterion
2251 and apply generatively, returning the newly resulting
2252 :class:`_query.Query`.
2254 **Simple Relationship Joins**
2256 Consider a mapping between two classes ``User`` and ``Address``,
2257 with a relationship ``User.addresses`` representing a collection
2258 of ``Address`` objects associated with each ``User``. The most
2259 common usage of :meth:`_query.Query.join`
2260 is to create a JOIN along this
2261 relationship, using the ``User.addresses`` attribute as an indicator
2262 for how this should occur::
2264 q = session.query(User).join(User.addresses)
2266 Where above, the call to :meth:`_query.Query.join` along
2267 ``User.addresses`` will result in SQL approximately equivalent to:
2269 .. sourcecode:: sql
2271 SELECT user.id, user.name
2272 FROM user JOIN address ON user.id = address.user_id
2274 In the above example we refer to ``User.addresses`` as passed to
2275 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2276 how the "ON" portion of the JOIN should be constructed.
2278 To construct a chain of joins, multiple :meth:`_query.Query.join`
2279 calls may be used. The relationship-bound attribute implies both
2280 the left and right side of the join at once::
2282 q = (
2283 session.query(User)
2284 .join(User.orders)
2285 .join(Order.items)
2286 .join(Item.keywords)
2287 )
2289 .. note:: as seen in the above example, **the order in which each
2290 call to the join() method occurs is important**. Query would not,
2291 for example, know how to join correctly if we were to specify
2292 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2293 such a case, depending on the arguments passed, it may raise an
2294 error that it doesn't know how to join, or it may produce invalid
2295 SQL in which case the database will raise an error. In correct
2296 practice, the
2297 :meth:`_query.Query.join` method is invoked in such a way that lines
2298 up with how we would want the JOIN clauses in SQL to be
2299 rendered, and each call should represent a clear link from what
2300 precedes it.
2302 **Joins to a Target Entity or Selectable**
2304 A second form of :meth:`_query.Query.join` allows any mapped entity or
2305 core selectable construct as a target. In this usage,
2306 :meth:`_query.Query.join` will attempt to create a JOIN along the
2307 natural foreign key relationship between two entities::
2309 q = session.query(User).join(Address)
2311 In the above calling form, :meth:`_query.Query.join` is called upon to
2312 create the "on clause" automatically for us. This calling form will
2313 ultimately raise an error if either there are no foreign keys between
2314 the two entities, or if there are multiple foreign key linkages between
2315 the target entity and the entity or entities already present on the
2316 left side such that creating a join requires more information. Note
2317 that when indicating a join to a target without any ON clause, ORM
2318 configured relationships are not taken into account.
2320 **Joins to a Target with an ON Clause**
2322 The third calling form allows both the target entity as well
2323 as the ON clause to be passed explicitly. A example that includes
2324 a SQL expression as the ON clause is as follows::
2326 q = session.query(User).join(Address, User.id == Address.user_id)
2328 The above form may also use a relationship-bound attribute as the
2329 ON clause as well::
2331 q = session.query(User).join(Address, User.addresses)
2333 The above syntax can be useful for the case where we wish
2334 to join to an alias of a particular target entity. If we wanted
2335 to join to ``Address`` twice, it could be achieved using two
2336 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2338 a1 = aliased(Address)
2339 a2 = aliased(Address)
2341 q = (
2342 session.query(User)
2343 .join(a1, User.addresses)
2344 .join(a2, User.addresses)
2345 .filter(a1.email_address == "ed@foo.com")
2346 .filter(a2.email_address == "ed@bar.com")
2347 )
2349 The relationship-bound calling form can also specify a target entity
2350 using the :meth:`_orm.PropComparator.of_type` method; a query
2351 equivalent to the one above would be::
2353 a1 = aliased(Address)
2354 a2 = aliased(Address)
2356 q = (
2357 session.query(User)
2358 .join(User.addresses.of_type(a1))
2359 .join(User.addresses.of_type(a2))
2360 .filter(a1.email_address == "ed@foo.com")
2361 .filter(a2.email_address == "ed@bar.com")
2362 )
2364 **Augmenting Built-in ON Clauses**
2366 As a substitute for providing a full custom ON condition for an
2367 existing relationship, the :meth:`_orm.PropComparator.and_` function
2368 may be applied to a relationship attribute to augment additional
2369 criteria into the ON clause; the additional criteria will be combined
2370 with the default criteria using AND::
2372 q = session.query(User).join(
2373 User.addresses.and_(Address.email_address != "foo@bar.com")
2374 )
2376 .. versionadded:: 1.4
2378 **Joining to Tables and Subqueries**
2381 The target of a join may also be any table or SELECT statement,
2382 which may be related to a target entity or not. Use the
2383 appropriate ``.subquery()`` method in order to make a subquery
2384 out of a query::
2386 subq = (
2387 session.query(Address)
2388 .filter(Address.email_address == "ed@foo.com")
2389 .subquery()
2390 )
2393 q = session.query(User).join(subq, User.id == subq.c.user_id)
2395 Joining to a subquery in terms of a specific relationship and/or
2396 target entity may be achieved by linking the subquery to the
2397 entity using :func:`_orm.aliased`::
2399 subq = (
2400 session.query(Address)
2401 .filter(Address.email_address == "ed@foo.com")
2402 .subquery()
2403 )
2405 address_subq = aliased(Address, subq)
2407 q = session.query(User).join(User.addresses.of_type(address_subq))
2409 **Controlling what to Join From**
2411 In cases where the left side of the current state of
2412 :class:`_query.Query` is not in line with what we want to join from,
2413 the :meth:`_query.Query.select_from` method may be used::
2415 q = (
2416 session.query(Address)
2417 .select_from(User)
2418 .join(User.addresses)
2419 .filter(User.name == "ed")
2420 )
2422 Which will produce SQL similar to:
2424 .. sourcecode:: sql
2426 SELECT address.* FROM user
2427 JOIN address ON user.id=address.user_id
2428 WHERE user.name = :name_1
2430 .. seealso::
2432 :meth:`_sql.Select.join` - v2 equivalent method.
2434 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2435 the props collection in modern use should be considered to be a one
2436 or two argument form, either as a single "target" entity or ORM
2437 attribute-bound relationship, or as a target entity plus an "on
2438 clause" which may be a SQL expression or ORM attribute-bound
2439 relationship.
2441 :param isouter=False: If True, the join used will be a left outer join,
2442 just as if the :meth:`_query.Query.outerjoin` method were called.
2444 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2446 """
2448 join_target = coercions.expect(
2449 roles.JoinTargetRole,
2450 target,
2451 apply_propagate_attrs=self,
2452 legacy=True,
2453 )
2454 if onclause is not None:
2455 onclause_element = coercions.expect(
2456 roles.OnClauseRole, onclause, legacy=True
2457 )
2458 else:
2459 onclause_element = None
2461 self._setup_joins += (
2462 (
2463 join_target,
2464 onclause_element,
2465 None,
2466 {
2467 "isouter": isouter,
2468 "full": full,
2469 },
2470 ),
2471 )
2473 self.__dict__.pop("_last_joined_entity", None)
2474 return self
2476 def outerjoin(
2477 self,
2478 target: _JoinTargetArgument,
2479 onclause: Optional[_OnClauseArgument] = None,
2480 *,
2481 full: bool = False,
2482 ) -> Self:
2483 """Create a left outer join against this ``Query`` object's criterion
2484 and apply generatively, returning the newly resulting ``Query``.
2486 Usage is the same as the ``join()`` method.
2488 .. seealso::
2490 :meth:`_sql.Select.outerjoin` - v2 equivalent method.
2492 """
2493 return self.join(target, onclause=onclause, isouter=True, full=full)
2495 @_generative
2496 @_assertions(_no_statement_condition)
2497 def reset_joinpoint(self) -> Self:
2498 """Return a new :class:`.Query`, where the "join point" has
2499 been reset back to the base FROM entities of the query.
2501 This method is usually used in conjunction with the
2502 ``aliased=True`` feature of the :meth:`~.Query.join`
2503 method. See the example in :meth:`~.Query.join` for how
2504 this is used.
2506 """
2507 self._last_joined_entity = None
2509 return self
2511 @_generative
2512 @_assertions(_no_clauseelement_condition)
2513 def select_from(self, *from_obj: _FromClauseArgument) -> Self:
2514 r"""Set the FROM clause of this :class:`.Query` explicitly.
2516 :meth:`.Query.select_from` is often used in conjunction with
2517 :meth:`.Query.join` in order to control which entity is selected
2518 from on the "left" side of the join.
2520 The entity or selectable object here effectively replaces the
2521 "left edge" of any calls to :meth:`~.Query.join`, when no
2522 joinpoint is otherwise established - usually, the default "join
2523 point" is the leftmost entity in the :class:`~.Query` object's
2524 list of entities to be selected.
2526 A typical example::
2528 q = (
2529 session.query(Address)
2530 .select_from(User)
2531 .join(User.addresses)
2532 .filter(User.name == "ed")
2533 )
2535 Which produces SQL equivalent to:
2537 .. sourcecode:: sql
2539 SELECT address.* FROM user
2540 JOIN address ON user.id=address.user_id
2541 WHERE user.name = :name_1
2543 :param \*from_obj: collection of one or more entities to apply
2544 to the FROM clause. Entities can be mapped classes,
2545 :class:`.AliasedClass` objects, :class:`.Mapper` objects
2546 as well as core :class:`.FromClause` elements like subqueries.
2548 .. seealso::
2550 :meth:`~.Query.join`
2552 :meth:`.Query.select_entity_from`
2554 :meth:`_sql.Select.select_from` - v2 equivalent method.
2556 """
2558 self._set_select_from(from_obj, False)
2559 return self
2561 def __getitem__(self, item: Any) -> Any:
2562 return orm_util._getitem(
2563 self,
2564 item,
2565 )
2567 @_generative
2568 @_assertions(_no_statement_condition)
2569 def slice(
2570 self,
2571 start: int,
2572 stop: int,
2573 ) -> Self:
2574 """Computes the "slice" of the :class:`_query.Query` represented by
2575 the given indices and returns the resulting :class:`_query.Query`.
2577 The start and stop indices behave like the argument to Python's
2578 built-in :func:`range` function. This method provides an
2579 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
2580 query.
2582 For example, ::
2584 session.query(User).order_by(User.id).slice(1, 3)
2586 renders as
2588 .. sourcecode:: sql
2590 SELECT users.id AS users_id,
2591 users.name AS users_name
2592 FROM users ORDER BY users.id
2593 LIMIT ? OFFSET ?
2594 (2, 1)
2596 .. seealso::
2598 :meth:`_query.Query.limit`
2600 :meth:`_query.Query.offset`
2602 :meth:`_sql.Select.slice` - v2 equivalent method.
2604 """
2606 self._limit_clause, self._offset_clause = sql_util._make_slice(
2607 self._limit_clause, self._offset_clause, start, stop
2608 )
2609 return self
2611 @_generative
2612 @_assertions(_no_statement_condition)
2613 def limit(self, limit: _LimitOffsetType) -> Self:
2614 """Apply a ``LIMIT`` to the query and return the newly resulting
2615 ``Query``.
2617 .. seealso::
2619 :meth:`_sql.Select.limit` - v2 equivalent method.
2621 """
2622 self._limit_clause = sql_util._offset_or_limit_clause(limit)
2623 return self
2625 @_generative
2626 @_assertions(_no_statement_condition)
2627 def offset(self, offset: _LimitOffsetType) -> Self:
2628 """Apply an ``OFFSET`` to the query and return the newly resulting
2629 ``Query``.
2631 .. seealso::
2633 :meth:`_sql.Select.offset` - v2 equivalent method.
2634 """
2635 self._offset_clause = sql_util._offset_or_limit_clause(offset)
2636 return self
2638 @_generative
2639 @_assertions(_no_statement_condition)
2640 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:
2641 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
2642 ``Query``.
2645 .. note::
2647 The ORM-level :meth:`.distinct` call includes logic that will
2648 automatically add columns from the ORDER BY of the query to the
2649 columns clause of the SELECT statement, to satisfy the common need
2650 of the database backend that ORDER BY columns be part of the SELECT
2651 list when DISTINCT is used. These columns *are not* added to the
2652 list of columns actually fetched by the :class:`_query.Query`,
2653 however,
2654 so would not affect results. The columns are passed through when
2655 using the :attr:`_query.Query.statement` accessor, however.
2657 .. deprecated:: 2.0 This logic is deprecated and will be removed
2658 in SQLAlchemy 2.0. See :ref:`migration_20_query_distinct`
2659 for a description of this use case in 2.0.
2661 .. seealso::
2663 :meth:`_sql.Select.distinct` - v2 equivalent method.
2665 :param \*expr: optional column expressions. When present,
2666 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2667 construct.
2669 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
2670 and will raise :class:`_exc.CompileError` in a future version.
2672 """
2673 if expr:
2674 self._distinct = True
2675 self._distinct_on = self._distinct_on + tuple(
2676 coercions.expect(roles.ByOfRole, e) for e in expr
2677 )
2678 else:
2679 self._distinct = True
2680 return self
2682 def all(self) -> List[_T]:
2683 """Return the results represented by this :class:`_query.Query`
2684 as a list.
2686 This results in an execution of the underlying SQL statement.
2688 .. warning:: The :class:`_query.Query` object,
2689 when asked to return either
2690 a sequence or iterator that consists of full ORM-mapped entities,
2691 will **deduplicate entries based on primary key**. See the FAQ for
2692 more details.
2694 .. seealso::
2696 :ref:`faq_query_deduplicating`
2698 .. seealso::
2700 :meth:`_engine.Result.all` - v2 comparable method.
2702 :meth:`_engine.Result.scalars` - v2 comparable method.
2703 """
2704 return self._iter().all() # type: ignore
2706 @_generative
2707 @_assertions(_no_clauseelement_condition)
2708 def from_statement(self, statement: ExecutableReturnsRows) -> Self:
2709 """Execute the given SELECT statement and return results.
2711 This method bypasses all internal statement compilation, and the
2712 statement is executed without modification.
2714 The statement is typically either a :func:`_expression.text`
2715 or :func:`_expression.select` construct, and should return the set
2716 of columns
2717 appropriate to the entity class represented by this
2718 :class:`_query.Query`.
2720 .. seealso::
2722 :meth:`_sql.Select.from_statement` - v2 comparable method.
2724 """
2725 statement = coercions.expect(
2726 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2727 )
2728 self._statement = statement
2729 return self
2731 def first(self) -> Optional[_T]:
2732 """Return the first result of this ``Query`` or
2733 None if the result doesn't contain any row.
2735 first() applies a limit of one within the generated SQL, so that
2736 only one primary entity row is generated on the server side
2737 (note this may consist of multiple result rows if join-loaded
2738 collections are present).
2740 Calling :meth:`_query.Query.first`
2741 results in an execution of the underlying
2742 query.
2744 .. seealso::
2746 :meth:`_query.Query.one`
2748 :meth:`_query.Query.one_or_none`
2750 :meth:`_engine.Result.first` - v2 comparable method.
2752 :meth:`_engine.Result.scalars` - v2 comparable method.
2754 """
2755 # replicates limit(1) behavior
2756 if self._statement is not None:
2757 return self._iter().first() # type: ignore
2758 else:
2759 return self.limit(1)._iter().first() # type: ignore
2761 def one_or_none(self) -> Optional[_T]:
2762 """Return at most one result or raise an exception.
2764 Returns ``None`` if the query selects
2765 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2766 if multiple object identities are returned, or if multiple
2767 rows are returned for a query that returns only scalar values
2768 as opposed to full identity-mapped entities.
2770 Calling :meth:`_query.Query.one_or_none`
2771 results in an execution of the
2772 underlying query.
2774 .. seealso::
2776 :meth:`_query.Query.first`
2778 :meth:`_query.Query.one`
2780 :meth:`_engine.Result.one_or_none` - v2 comparable method.
2782 :meth:`_engine.Result.scalar_one_or_none` - v2 comparable method.
2784 """
2785 return self._iter().one_or_none() # type: ignore
2787 def one(self) -> _T:
2788 """Return exactly one result or raise an exception.
2790 Raises :class:`_exc.NoResultFound` if the query selects no rows.
2791 Raises :class:`_exc.MultipleResultsFound` if multiple object identities
2792 are returned, or if multiple rows are returned for a query that returns
2793 only scalar values as opposed to full identity-mapped entities.
2795 Calling :meth:`.one` results in an execution of the underlying query.
2797 .. seealso::
2799 :meth:`_query.Query.first`
2801 :meth:`_query.Query.one_or_none`
2803 :meth:`_engine.Result.one` - v2 comparable method.
2805 :meth:`_engine.Result.scalar_one` - v2 comparable method.
2807 """
2808 return self._iter().one() # type: ignore
2810 def scalar(self) -> Any:
2811 """Return the first element of the first result or None
2812 if no rows present. If multiple rows are returned,
2813 raises :class:`_exc.MultipleResultsFound`.
2815 >>> session.query(Item).scalar()
2816 <Item>
2817 >>> session.query(Item.id).scalar()
2818 1
2819 >>> session.query(Item.id).filter(Item.id < 0).scalar()
2820 None
2821 >>> session.query(Item.id, Item.name).scalar()
2822 1
2823 >>> session.query(func.count(Parent.id)).scalar()
2824 20
2826 This results in an execution of the underlying query.
2828 .. seealso::
2830 :meth:`_engine.Result.scalar` - v2 comparable method.
2832 """
2833 # TODO: not sure why we can't use result.scalar() here
2834 try:
2835 ret = self.one()
2836 if not isinstance(ret, collections_abc.Sequence):
2837 return ret
2838 return ret[0]
2839 except sa_exc.NoResultFound:
2840 return None
2842 def __iter__(self) -> Iterator[_T]:
2843 result = self._iter()
2844 try:
2845 yield from result # type: ignore
2846 except GeneratorExit:
2847 # issue #8710 - direct iteration is not re-usable after
2848 # an iterable block is broken, so close the result
2849 result._soft_close()
2850 raise
2852 def _iter(self) -> Union[ScalarResult[_T], Result[_T]]:
2853 # new style execution.
2854 params = self._params
2856 statement = self._statement_20()
2857 result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
2858 statement,
2859 params,
2860 execution_options={"_sa_orm_load_options": self.load_options},
2861 )
2863 # legacy: automatically set scalars, unique
2864 if result._attributes.get("is_single_entity", False):
2865 result = cast("Result[_T]", result).scalars()
2867 if (
2868 result._attributes.get("filtered", False)
2869 and not self.load_options._yield_per
2870 ):
2871 result = result.unique()
2873 return result
2875 def __str__(self) -> str:
2876 statement = self._statement_20()
2878 try:
2879 bind = (
2880 self._get_bind_args(statement, self.session.get_bind)
2881 if self.session
2882 else None
2883 )
2884 except sa_exc.UnboundExecutionError:
2885 bind = None
2887 return str(statement.compile(bind))
2889 def _get_bind_args(self, statement: Any, fn: Any, **kw: Any) -> Any:
2890 return fn(clause=statement, **kw)
2892 @property
2893 def column_descriptions(self) -> List[ORMColumnDescription]:
2894 """Return metadata about the columns which would be
2895 returned by this :class:`_query.Query`.
2897 Format is a list of dictionaries::
2899 user_alias = aliased(User, name="user2")
2900 q = sess.query(User, User.id, user_alias)
2902 # this expression:
2903 q.column_descriptions
2905 # would return:
2906 [
2907 {
2908 "name": "User",
2909 "type": User,
2910 "aliased": False,
2911 "expr": User,
2912 "entity": User,
2913 },
2914 {
2915 "name": "id",
2916 "type": Integer(),
2917 "aliased": False,
2918 "expr": User.id,
2919 "entity": User,
2920 },
2921 {
2922 "name": "user2",
2923 "type": User,
2924 "aliased": True,
2925 "expr": user_alias,
2926 "entity": user_alias,
2927 },
2928 ]
2930 .. seealso::
2932 This API is available using :term:`2.0 style` queries as well,
2933 documented at:
2935 * :ref:`queryguide_inspection`
2937 * :attr:`.Select.column_descriptions`
2939 """
2941 return _column_descriptions(self, legacy=True)
2943 @util.deprecated(
2944 "2.0",
2945 "The :meth:`_orm.Query.instances` method is deprecated and will "
2946 "be removed in a future release. "
2947 "Use the Select.from_statement() method or aliased() construct in "
2948 "conjunction with Session.execute() instead.",
2949 )
2950 def instances(
2951 self,
2952 result_proxy: CursorResult[Any],
2953 context: Optional[QueryContext] = None,
2954 ) -> Any:
2955 """Return an ORM result given a :class:`_engine.CursorResult` and
2956 :class:`.QueryContext`.
2958 """
2959 if context is None:
2960 util.warn_deprecated(
2961 "Using the Query.instances() method without a context "
2962 "is deprecated and will be disallowed in a future release. "
2963 "Please make use of :meth:`_query.Query.from_statement` "
2964 "for linking ORM results to arbitrary select constructs.",
2965 version="1.4",
2966 )
2967 compile_state = self._compile_state(for_statement=False)
2969 context = QueryContext(
2970 compile_state,
2971 compile_state.statement,
2972 compile_state.statement,
2973 self._params,
2974 self.session,
2975 self.load_options,
2976 )
2978 result = loading.instances(result_proxy, context)
2980 # legacy: automatically set scalars, unique
2981 if result._attributes.get("is_single_entity", False):
2982 result = result.scalars() # type: ignore
2984 if result._attributes.get("filtered", False):
2985 result = result.unique()
2987 # TODO: isn't this supposed to be a list?
2988 return result
2990 @util.became_legacy_20(
2991 ":meth:`_orm.Query.merge_result`",
2992 alternative="The method is superseded by the "
2993 ":func:`_orm.merge_frozen_result` function.",
2994 enable_warnings=False, # warnings occur via loading.merge_result
2995 )
2996 def merge_result(
2997 self,
2998 iterator: Union[
2999 FrozenResult[Any], Iterable[Sequence[Any]], Iterable[object]
3000 ],
3001 load: bool = True,
3002 ) -> Union[FrozenResult[Any], Iterable[Any]]:
3003 """Merge a result into this :class:`_query.Query` object's Session.
3005 Given an iterator returned by a :class:`_query.Query`
3006 of the same structure
3007 as this one, return an identical iterator of results, with all mapped
3008 instances merged into the session using :meth:`.Session.merge`. This
3009 is an optimized method which will merge all mapped instances,
3010 preserving the structure of the result rows and unmapped columns with
3011 less method overhead than that of calling :meth:`.Session.merge`
3012 explicitly for each value.
3014 The structure of the results is determined based on the column list of
3015 this :class:`_query.Query` - if these do not correspond,
3016 unchecked errors
3017 will occur.
3019 The 'load' argument is the same as that of :meth:`.Session.merge`.
3021 For an example of how :meth:`_query.Query.merge_result` is used, see
3022 the source code for the example :ref:`examples_caching`, where
3023 :meth:`_query.Query.merge_result` is used to efficiently restore state
3024 from a cache back into a target :class:`.Session`.
3026 """
3028 return loading.merge_result(self, iterator, load)
3030 def exists(self) -> Exists:
3031 """A convenience method that turns a query into an EXISTS subquery
3032 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3034 e.g.::
3036 q = session.query(User).filter(User.name == "fred")
3037 session.query(q.exists())
3039 Producing SQL similar to:
3041 .. sourcecode:: sql
3043 SELECT EXISTS (
3044 SELECT 1 FROM users WHERE users.name = :name_1
3045 ) AS anon_1
3047 The EXISTS construct is usually used in the WHERE clause::
3049 session.query(User.id).filter(q.exists()).scalar()
3051 Note that some databases such as SQL Server don't allow an
3052 EXISTS expression to be present in the columns clause of a
3053 SELECT. To select a simple boolean value based on the exists
3054 as a WHERE, use :func:`.literal`::
3056 from sqlalchemy import literal
3058 session.query(literal(True)).filter(q.exists()).scalar()
3060 .. seealso::
3062 :meth:`_sql.Select.exists` - v2 comparable method.
3064 """
3066 # .add_columns() for the case that we are a query().select_from(X),
3067 # so that ".statement" can be produced (#2995) but also without
3068 # omitting the FROM clause from a query(X) (#2818);
3069 # .with_only_columns() after we have a core select() so that
3070 # we get just "SELECT 1" without any entities.
3072 inner = (
3073 self.enable_eagerloads(False)
3074 .add_columns(sql.literal_column("1"))
3075 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3076 ._get_select_statement_only()
3077 .with_only_columns(1)
3078 )
3080 ezero = self._entity_from_pre_ent_zero()
3081 if ezero is not None:
3082 inner = inner.select_from(ezero)
3084 return sql.exists(inner)
3086 def count(self) -> int:
3087 r"""Return a count of rows this the SQL formed by this :class:`Query`
3088 would return.
3090 This generates the SQL for this Query as follows:
3092 .. sourcecode:: sql
3094 SELECT count(1) AS count_1 FROM (
3095 SELECT <rest of query follows...>
3096 ) AS anon_1
3098 The above SQL returns a single row, which is the aggregate value
3099 of the count function; the :meth:`_query.Query.count`
3100 method then returns
3101 that single integer value.
3103 .. warning::
3105 It is important to note that the value returned by
3106 count() is **not the same as the number of ORM objects that this
3107 Query would return from a method such as the .all() method**.
3108 The :class:`_query.Query` object,
3109 when asked to return full entities,
3110 will **deduplicate entries based on primary key**, meaning if the
3111 same primary key value would appear in the results more than once,
3112 only one object of that primary key would be present. This does
3113 not apply to a query that is against individual columns.
3115 .. seealso::
3117 :ref:`faq_query_deduplicating`
3119 For fine grained control over specific columns to count, to skip the
3120 usage of a subquery or otherwise control of the FROM clause, or to use
3121 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3122 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3124 from sqlalchemy import func
3126 # count User records, without
3127 # using a subquery.
3128 session.query(func.count(User.id))
3130 # return count of user "id" grouped
3131 # by "name"
3132 session.query(func.count(User.id)).group_by(User.name)
3134 from sqlalchemy import distinct
3136 # count distinct "name" values
3137 session.query(func.count(distinct(User.name)))
3139 .. seealso::
3141 :ref:`migration_20_query_usage`
3143 """
3144 col = sql.func.count(sql.literal_column("*"))
3145 return ( # type: ignore
3146 self._legacy_from_self(col).enable_eagerloads(False).scalar()
3147 )
3149 def delete(
3150 self,
3151 synchronize_session: SynchronizeSessionArgument = "auto",
3152 delete_args: Optional[Dict[Any, Any]] = None,
3153 ) -> int:
3154 r"""Perform a DELETE with an arbitrary WHERE clause.
3156 Deletes rows matched by this query from the database.
3158 E.g.::
3160 sess.query(User).filter(User.age == 25).delete(synchronize_session=False)
3162 sess.query(User).filter(User.age == 25).delete(
3163 synchronize_session="evaluate"
3164 )
3166 .. warning::
3168 See the section :ref:`orm_expression_update_delete` for important
3169 caveats and warnings, including limitations when using bulk UPDATE
3170 and DELETE with mapper inheritance configurations.
3172 :param synchronize_session: chooses the strategy to update the
3173 attributes on objects in the session. See the section
3174 :ref:`orm_expression_update_delete` for a discussion of these
3175 strategies.
3177 :param delete_args: Optional dictionary, if present will be passed
3178 to the underlying :func:`_expression.delete` construct as the ``**kw``
3179 for the object. May be used to pass dialect-specific arguments such
3180 as ``mysql_limit``.
3182 .. versionadded:: 2.0.37
3184 :return: the count of rows matched as returned by the database's
3185 "row count" feature.
3187 .. seealso::
3189 :ref:`orm_expression_update_delete`
3191 """ # noqa: E501
3193 bulk_del = BulkDelete(self, delete_args)
3194 if self.dispatch.before_compile_delete:
3195 for fn in self.dispatch.before_compile_delete:
3196 new_query = fn(bulk_del.query, bulk_del)
3197 if new_query is not None:
3198 bulk_del.query = new_query
3200 self = bulk_del.query
3202 delete_ = sql.delete(*self._raw_columns) # type: ignore
3204 if delete_args:
3205 delete_ = delete_.with_dialect_options(**delete_args)
3207 delete_._where_criteria = self._where_criteria
3208 result: CursorResult[Any] = self.session.execute(
3209 delete_,
3210 self._params,
3211 execution_options=self._execution_options.union(
3212 {"synchronize_session": synchronize_session}
3213 ),
3214 )
3215 bulk_del.result = result # type: ignore
3216 self.session.dispatch.after_bulk_delete(bulk_del)
3217 result.close()
3219 return result.rowcount
3221 def update(
3222 self,
3223 values: Dict[_DMLColumnArgument, Any],
3224 synchronize_session: SynchronizeSessionArgument = "auto",
3225 update_args: Optional[Dict[Any, Any]] = None,
3226 ) -> int:
3227 r"""Perform an UPDATE with an arbitrary WHERE clause.
3229 Updates rows matched by this query in the database.
3231 E.g.::
3233 sess.query(User).filter(User.age == 25).update(
3234 {User.age: User.age - 10}, synchronize_session=False
3235 )
3237 sess.query(User).filter(User.age == 25).update(
3238 {"age": User.age - 10}, synchronize_session="evaluate"
3239 )
3241 .. warning::
3243 See the section :ref:`orm_expression_update_delete` for important
3244 caveats and warnings, including limitations when using arbitrary
3245 UPDATE and DELETE with mapper inheritance configurations.
3247 :param values: a dictionary with attributes names, or alternatively
3248 mapped attributes or SQL expressions, as keys, and literal
3249 values or sql expressions as values. If :ref:`parameter-ordered
3250 mode <tutorial_parameter_ordered_updates>` is desired, the values can
3251 be passed as a list of 2-tuples; this requires that the
3252 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3253 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3254 as well.
3256 :param synchronize_session: chooses the strategy to update the
3257 attributes on objects in the session. See the section
3258 :ref:`orm_expression_update_delete` for a discussion of these
3259 strategies.
3261 :param update_args: Optional dictionary, if present will be passed
3262 to the underlying :func:`_expression.update` construct as the ``**kw``
3263 for the object. May be used to pass dialect-specific arguments such
3264 as ``mysql_limit``, as well as other special arguments such as
3265 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3267 :return: the count of rows matched as returned by the database's
3268 "row count" feature.
3271 .. seealso::
3273 :ref:`orm_expression_update_delete`
3275 """
3277 update_args = update_args or {}
3279 bulk_ud = BulkUpdate(self, values, update_args)
3281 if self.dispatch.before_compile_update:
3282 for fn in self.dispatch.before_compile_update:
3283 new_query = fn(bulk_ud.query, bulk_ud)
3284 if new_query is not None:
3285 bulk_ud.query = new_query
3286 self = bulk_ud.query
3288 upd = sql.update(*self._raw_columns) # type: ignore
3290 ppo = update_args.pop("preserve_parameter_order", False)
3291 if ppo:
3292 upd = upd.ordered_values(*values) # type: ignore
3293 else:
3294 upd = upd.values(values)
3295 if update_args:
3296 upd = upd.with_dialect_options(**update_args)
3298 upd._where_criteria = self._where_criteria
3299 result: CursorResult[Any] = self.session.execute(
3300 upd,
3301 self._params,
3302 execution_options=self._execution_options.union(
3303 {"synchronize_session": synchronize_session}
3304 ),
3305 )
3306 bulk_ud.result = result # type: ignore
3307 self.session.dispatch.after_bulk_update(bulk_ud)
3308 result.close()
3309 return result.rowcount
3311 def _compile_state(
3312 self, for_statement: bool = False, **kw: Any
3313 ) -> ORMCompileState:
3314 """Create an out-of-compiler ORMCompileState object.
3316 The ORMCompileState object is normally created directly as a result
3317 of the SQLCompiler.process() method being handed a Select()
3318 or FromStatement() object that uses the "orm" plugin. This method
3319 provides a means of creating this ORMCompileState object directly
3320 without using the compiler.
3322 This method is used only for deprecated cases, which include
3323 the .from_self() method for a Query that has multiple levels
3324 of .from_self() in use, as well as the instances() method. It is
3325 also used within the test suite to generate ORMCompileState objects
3326 for test purposes.
3328 """
3330 stmt = self._statement_20(for_statement=for_statement, **kw)
3331 assert for_statement == stmt._compile_options._for_statement
3333 # this chooses between ORMFromStatementCompileState and
3334 # ORMSelectCompileState. We could also base this on
3335 # query._statement is not None as we have the ORM Query here
3336 # however this is the more general path.
3337 compile_state_cls = cast(
3338 ORMCompileState,
3339 ORMCompileState._get_plugin_class_for_plugin(stmt, "orm"),
3340 )
3342 return compile_state_cls._create_orm_context(
3343 stmt, toplevel=True, compiler=None
3344 )
3346 def _compile_context(self, for_statement: bool = False) -> QueryContext:
3347 compile_state = self._compile_state(for_statement=for_statement)
3348 context = QueryContext(
3349 compile_state,
3350 compile_state.statement,
3351 compile_state.statement,
3352 self._params,
3353 self.session,
3354 self.load_options,
3355 )
3357 return context
3360class AliasOption(interfaces.LoaderOption):
3361 inherit_cache = False
3363 @util.deprecated(
3364 "1.4",
3365 "The :class:`.AliasOption` object is not necessary "
3366 "for entities to be matched up to a query that is established "
3367 "via :meth:`.Query.from_statement` and now does nothing.",
3368 )
3369 def __init__(self, alias: Union[Alias, Subquery]):
3370 r"""Return a :class:`.MapperOption` that will indicate to the
3371 :class:`_query.Query`
3372 that the main table has been aliased.
3374 """
3376 def process_compile_state(self, compile_state: ORMCompileState) -> None:
3377 pass
3380class BulkUD:
3381 """State used for the orm.Query version of update() / delete().
3383 This object is now specific to Query only.
3385 """
3387 def __init__(self, query: Query[Any]):
3388 self.query = query.enable_eagerloads(False)
3389 self._validate_query_state()
3390 self.mapper = self.query._entity_from_pre_ent_zero()
3392 def _validate_query_state(self) -> None:
3393 for attr, methname, notset, op in (
3394 ("_limit_clause", "limit()", None, operator.is_),
3395 ("_offset_clause", "offset()", None, operator.is_),
3396 ("_order_by_clauses", "order_by()", (), operator.eq),
3397 ("_group_by_clauses", "group_by()", (), operator.eq),
3398 ("_distinct", "distinct()", False, operator.is_),
3399 (
3400 "_from_obj",
3401 "join(), outerjoin(), select_from(), or from_self()",
3402 (),
3403 operator.eq,
3404 ),
3405 (
3406 "_setup_joins",
3407 "join(), outerjoin(), select_from(), or from_self()",
3408 (),
3409 operator.eq,
3410 ),
3411 ):
3412 if not op(getattr(self.query, attr), notset):
3413 raise sa_exc.InvalidRequestError(
3414 "Can't call Query.update() or Query.delete() "
3415 "when %s has been called" % (methname,)
3416 )
3418 @property
3419 def session(self) -> Session:
3420 return self.query.session
3423class BulkUpdate(BulkUD):
3424 """BulkUD which handles UPDATEs."""
3426 def __init__(
3427 self,
3428 query: Query[Any],
3429 values: Dict[_DMLColumnArgument, Any],
3430 update_kwargs: Optional[Dict[Any, Any]],
3431 ):
3432 super().__init__(query)
3433 self.values = values
3434 self.update_kwargs = update_kwargs
3437class BulkDelete(BulkUD):
3438 """BulkUD which handles DELETEs."""
3440 def __init__(
3441 self,
3442 query: Query[Any],
3443 delete_kwargs: Optional[Dict[Any, Any]],
3444 ):
3445 super().__init__(query)
3446 self.delete_kwargs = delete_kwargs
3449class RowReturningQuery(Query[Row[_TP]]):
3450 if TYPE_CHECKING:
3452 def tuples(self) -> Query[_TP]: # type: ignore
3453 ...