Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/orm/query.py: 42%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# orm/query.py
2# Copyright (C) 2005-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 Literal
34from typing import Mapping
35from typing import Optional
36from typing import overload
37from typing import Sequence
38from typing import Tuple
39from typing import Type
40from typing import TYPE_CHECKING
41from typing import TypeVar
42from typing import Union
44from . import attributes
45from . import interfaces
46from . import loading
47from . import util as orm_util
48from ._typing import _O
49from .base import _assertions
50from .context import _column_descriptions
51from .context import _determine_last_joined_entity
52from .context import _legacy_filter_by_entity_zero
53from .context import _ORMCompileState
54from .context import FromStatement
55from .context import QueryContext
56from .interfaces import ORMColumnDescription
57from .interfaces import ORMColumnsClauseRole
58from .util import AliasedClass
59from .util import object_mapper
60from .util import with_parent
61from .. import exc as sa_exc
62from .. import inspect
63from .. import inspection
64from .. import log
65from .. import sql
66from .. import util
67from ..engine import Result
68from ..engine import Row
69from ..event import dispatcher
70from ..event import EventTarget
71from ..sql import coercions
72from ..sql import expression
73from ..sql import roles
74from ..sql import Select
75from ..sql import util as sql_util
76from ..sql import visitors
77from ..sql._typing import _FromClauseArgument
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 import deprecated
95from ..util import warn_deprecated
96from ..util.typing import Self
97from ..util.typing import TupleAny
98from ..util.typing import TypeVarTuple
99from ..util.typing import Unpack
102if TYPE_CHECKING:
103 from ._typing import _EntityType
104 from ._typing import _ExternalEntityType
105 from ._typing import _InternalEntityType
106 from ._typing import SynchronizeSessionArgument
107 from .mapper import Mapper
108 from .path_registry import PathRegistry
109 from .session import _PKIdentityArgument
110 from .session import Session
111 from .state import InstanceState
112 from ..engine.cursor import CursorResult
113 from ..engine.interfaces import _ImmutableExecuteOptions
114 from ..engine.interfaces import CompiledCacheType
115 from ..engine.interfaces import IsolationLevel
116 from ..engine.interfaces import SchemaTranslateMapType
117 from ..engine.result import FrozenResult
118 from ..engine.result import ScalarResult
119 from ..sql._typing import _ColumnExpressionArgument
120 from ..sql._typing import _ColumnExpressionOrStrLabelArgument
121 from ..sql._typing import _ColumnsClauseArgument
122 from ..sql._typing import _DMLColumnArgument
123 from ..sql._typing import _JoinTargetArgument
124 from ..sql._typing import _LimitOffsetType
125 from ..sql._typing import _MAYBE_ENTITY
126 from ..sql._typing import _no_kw
127 from ..sql._typing import _NOT_ENTITY
128 from ..sql._typing import _OnClauseArgument
129 from ..sql._typing import _PropagateAttrsType
130 from ..sql._typing import _T0
131 from ..sql._typing import _T1
132 from ..sql._typing import _T2
133 from ..sql._typing import _T3
134 from ..sql._typing import _T4
135 from ..sql._typing import _T5
136 from ..sql._typing import _T6
137 from ..sql._typing import _T7
138 from ..sql._typing import _TypedColumnClauseArgument as _TCCA
139 from ..sql.base import CacheableOptions
140 from ..sql.base import ExecutableOption
141 from ..sql.base import SyntaxExtension
142 from ..sql.dml import UpdateBase
143 from ..sql.elements import ColumnElement
144 from ..sql.elements import Label
145 from ..sql.selectable import _ForUpdateOfArgument
146 from ..sql.selectable import _JoinTargetElement
147 from ..sql.selectable import _SetupJoinsElement
148 from ..sql.selectable import Alias
149 from ..sql.selectable import CTE
150 from ..sql.selectable import ExecutableReturnsRows
151 from ..sql.selectable import FromClause
152 from ..sql.selectable import ScalarSelect
153 from ..sql.selectable import Subquery
156__all__ = ["Query", "QueryContext"]
158_T = TypeVar("_T", bound=Any)
159_Ts = TypeVarTuple("_Ts")
162@inspection._self_inspects
163@log.class_logger
164class Query(
165 _SelectFromElements,
166 SupportsCloneAnnotations,
167 HasPrefixes,
168 HasSuffixes,
169 HasHints,
170 EventTarget,
171 log.Identified,
172 Generative,
173 Executable,
174 Generic[_T],
175):
176 """ORM-level SQL construction object.
178 .. legacy:: The ORM :class:`.Query` object is a legacy construct
179 as of SQLAlchemy 2.0. See the notes at the top of
180 :ref:`query_api_toplevel` for an overview, including links to migration
181 documentation.
183 :class:`_query.Query` objects are normally initially generated using the
184 :meth:`~.Session.query` method of :class:`.Session`, and in
185 less common cases by instantiating the :class:`_query.Query` directly and
186 associating with a :class:`.Session` using the
187 :meth:`_query.Query.with_session`
188 method.
190 """
192 # elements that are in Core and can be cached in the same way
193 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
194 _having_criteria: Tuple[ColumnElement[Any], ...] = ()
196 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()
197 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()
198 _limit_clause: Optional[ColumnElement[Any]] = None
199 _offset_clause: Optional[ColumnElement[Any]] = None
201 _distinct: bool = False
202 _distinct_on: Tuple[ColumnElement[Any], ...] = ()
204 _for_update_arg: Optional[ForUpdateArg] = None
205 _correlate: Tuple[FromClause, ...] = ()
206 _auto_correlate: bool = True
207 _from_obj: Tuple[FromClause, ...] = ()
208 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()
210 _label_style: SelectLabelStyle = SelectLabelStyle.LABEL_STYLE_LEGACY_ORM
212 _memoized_select_entities = ()
214 _syntax_extensions: Tuple[SyntaxExtension, ...] = ()
216 _compile_options: Union[Type[CacheableOptions], CacheableOptions] = (
217 _ORMCompileState.default_compile_options
218 )
220 _with_options: Tuple[ExecutableOption, ...]
221 load_options = QueryContext.default_load_options + {
222 "_legacy_uniquing": True
223 }
225 _params: util.immutabledict[str, Any] = util.EMPTY_DICT
227 # local Query builder state, not needed for
228 # compilation or execution
229 _enable_assertions = True
231 _statement: Optional[ExecutableReturnsRows] = None
233 session: Session
235 dispatch: dispatcher[Query[_T]]
237 # mirrors that of ClauseElement, used to propagate the "orm"
238 # plugin as well as the "subject" of the plugin, e.g. the mapper
239 # we are querying against.
240 @util.memoized_property
241 def _propagate_attrs(self) -> _PropagateAttrsType:
242 return util.EMPTY_DICT
244 def __init__(
245 self,
246 entities: Union[
247 _ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]
248 ],
249 session: Optional[Session] = None,
250 ):
251 """Construct a :class:`_query.Query` directly.
253 E.g.::
255 q = Query([User, Address], session=some_session)
257 The above is equivalent to::
259 q = some_session.query(User, Address)
261 :param entities: a sequence of entities and/or SQL expressions.
263 :param session: a :class:`.Session` with which the
264 :class:`_query.Query`
265 will be associated. Optional; a :class:`_query.Query`
266 can be associated
267 with a :class:`.Session` generatively via the
268 :meth:`_query.Query.with_session` method as well.
270 .. seealso::
272 :meth:`.Session.query`
274 :meth:`_query.Query.with_session`
276 """
278 # session is usually present. There's one case in subqueryloader
279 # where it stores a Query without a Session and also there are tests
280 # for the query(Entity).with_session(session) API which is likely in
281 # some old recipes, however these are legacy as select() can now be
282 # used.
283 self.session = session # type: ignore
284 self._set_entities(entities)
286 def _set_propagate_attrs(self, values: Mapping[str, Any]) -> Self:
287 self._propagate_attrs = util.immutabledict(values)
288 return self
290 def _set_entities(
291 self,
292 entities: Union[
293 _ColumnsClauseArgument[Any], Iterable[_ColumnsClauseArgument[Any]]
294 ],
295 ) -> None:
296 self._raw_columns = [
297 coercions.expect(
298 roles.ColumnsClauseRole,
299 ent,
300 apply_propagate_attrs=self,
301 post_inspect=True,
302 )
303 for ent in util.to_list(entities)
304 ]
306 @deprecated(
307 "2.1.0",
308 "The :meth:`.Query.tuples` method is deprecated, :class:`.Row` "
309 "now behaves like a tuple and can unpack types directly.",
310 )
311 def tuples(self: Query[_O]) -> Query[Tuple[_O]]:
312 """return a tuple-typed form of this :class:`.Query`.
314 This method invokes the :meth:`.Query.only_return_tuples`
315 method with a value of ``True``, which by itself ensures that this
316 :class:`.Query` will always return :class:`.Row` objects, even
317 if the query is made against a single entity. It then also
318 at the typing level will return a "typed" query, if possible,
319 that will type result rows as ``Tuple`` objects with typed
320 elements.
322 This method can be compared to the :meth:`.Result.tuples` method,
323 which returns "self", but from a typing perspective returns an object
324 that will yield typed ``Tuple`` objects for results. Typing
325 takes effect only if this :class:`.Query` object is a typed
326 query object already.
328 .. versionadded:: 2.0
330 .. seealso::
332 :ref:`change_10635` - describes a migration path from this
333 workaround for SQLAlchemy 2.1.
335 :meth:`.Result.tuples` - v2 equivalent method.
337 """
338 return self.only_return_tuples(True) # type: ignore
340 def _entity_from_pre_ent_zero(self) -> Optional[_InternalEntityType[Any]]:
341 if not self._raw_columns:
342 return None
344 ent = self._raw_columns[0]
346 if "parententity" in ent._annotations:
347 return ent._annotations["parententity"] # type: ignore
348 elif "bundle" in ent._annotations:
349 return ent._annotations["bundle"] # type: ignore
350 else:
351 # label, other SQL expression
352 for element in visitors.iterate(ent):
353 if "parententity" in element._annotations:
354 return element._annotations["parententity"] # type: ignore # noqa: E501
355 else:
356 return None
358 def _only_full_mapper_zero(self, methname: str) -> Mapper[Any]:
359 if (
360 len(self._raw_columns) != 1
361 or "parententity" not in self._raw_columns[0]._annotations
362 or not self._raw_columns[0].is_selectable
363 ):
364 raise sa_exc.InvalidRequestError(
365 "%s() can only be used against "
366 "a single mapped class." % methname
367 )
369 return self._raw_columns[0]._annotations["parententity"] # type: ignore # noqa: E501
371 def _set_select_from(
372 self, obj: Iterable[_FromClauseArgument], set_base_alias: bool
373 ) -> None:
374 fa = [
375 coercions.expect(
376 roles.FromClauseRole,
377 elem,
378 apply_propagate_attrs=self,
379 )
380 for elem in obj
381 ]
383 self._compile_options += {"_set_base_alias": set_base_alias}
384 self._from_obj = tuple(fa)
386 @_generative
387 def _set_lazyload_from(self, state: InstanceState[Any]) -> Self:
388 self.load_options += {"_lazy_loaded_from": state}
389 return self
391 def _get_condition(self) -> None:
392 """used by legacy BakedQuery"""
393 self._no_criterion_condition("get", order_by=False, distinct=False)
395 def _get_existing_condition(self) -> None:
396 self._no_criterion_assertion("get", order_by=False, distinct=False)
398 def _no_criterion_assertion(
399 self, meth: str, order_by: bool = True, distinct: bool = True
400 ) -> None:
401 if not self._enable_assertions:
402 return
403 if (
404 self._where_criteria
405 or self._statement is not None
406 or self._from_obj
407 or self._setup_joins
408 or self._limit_clause is not None
409 or self._offset_clause is not None
410 or self._group_by_clauses
411 or (order_by and self._order_by_clauses)
412 or (distinct and self._distinct)
413 ):
414 raise sa_exc.InvalidRequestError(
415 "Query.%s() being called on a "
416 "Query with existing criterion. " % meth
417 )
419 def _no_criterion_condition(
420 self, meth: str, order_by: bool = True, distinct: bool = True
421 ) -> None:
422 self._no_criterion_assertion(meth, order_by, distinct)
424 self._from_obj = self._setup_joins = ()
425 if self._statement is not None:
426 self._compile_options += {"_statement": None}
427 self._where_criteria = ()
428 self._distinct = False
430 self._order_by_clauses = self._group_by_clauses = ()
432 def _no_clauseelement_condition(self, meth: str) -> None:
433 if not self._enable_assertions:
434 return
435 if self._order_by_clauses:
436 raise sa_exc.InvalidRequestError(
437 "Query.%s() being called on a "
438 "Query with existing criterion. " % meth
439 )
440 self._no_criterion_condition(meth)
442 def _no_statement_condition(self, meth: str) -> None:
443 if not self._enable_assertions:
444 return
445 if self._statement is not None:
446 raise sa_exc.InvalidRequestError(
447 (
448 "Query.%s() being called on a Query with an existing full "
449 "statement - can't apply criterion."
450 )
451 % meth
452 )
454 def _no_limit_offset(self, meth: str) -> None:
455 if not self._enable_assertions:
456 return
457 if self._limit_clause is not None or self._offset_clause is not None:
458 raise sa_exc.InvalidRequestError(
459 "Query.%s() being called on a Query which already has LIMIT "
460 "or OFFSET applied. Call %s() before limit() or offset() "
461 "are applied." % (meth, meth)
462 )
464 @property
465 def _has_row_limiting_clause(self) -> bool:
466 return (
467 self._limit_clause is not None or self._offset_clause is not None
468 )
470 def _get_options(
471 self,
472 populate_existing: Optional[bool] = None,
473 version_check: Optional[bool] = None,
474 only_load_props: Optional[Sequence[str]] = None,
475 refresh_state: Optional[InstanceState[Any]] = None,
476 identity_token: Optional[Any] = None,
477 ) -> Self:
478 load_options: Dict[str, Any] = {}
479 compile_options: Dict[str, Any] = {}
481 if version_check:
482 load_options["_version_check"] = version_check
483 if populate_existing:
484 load_options["_populate_existing"] = populate_existing
485 if refresh_state:
486 load_options["_refresh_state"] = refresh_state
487 compile_options["_for_refresh_state"] = True
488 if only_load_props:
489 compile_options["_only_load_props"] = frozenset(only_load_props)
490 if identity_token:
491 load_options["_identity_token"] = identity_token
493 if load_options:
494 self.load_options += load_options
495 if compile_options:
496 self._compile_options += compile_options
498 return self
500 def _clone(self, **kw: Any) -> Self:
501 return self._generate()
503 def _get_select_statement_only(self) -> Select[_T]:
504 if self._statement is not None:
505 raise sa_exc.InvalidRequestError(
506 "Can't call this method on a Query that uses from_statement()"
507 )
508 return cast("Select[_T]", self.statement)
510 @property
511 def statement(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
512 """The full SELECT statement represented by this Query.
514 The statement by default will not have disambiguating labels
515 applied to the construct unless with_labels(True) is called
516 first.
518 """
520 # .statement can return the direct future.Select() construct here, as
521 # long as we are not using subsequent adaption features that
522 # are made against raw entities, e.g. from_self(), with_polymorphic(),
523 # select_entity_from(). If these features are being used, then
524 # the Select() we return will not have the correct .selected_columns
525 # collection and will not embed in subsequent queries correctly.
526 # We could find a way to make this collection "correct", however
527 # this would not be too different from doing the full compile as
528 # we are doing in any case, the Select() would still not have the
529 # proper state for other attributes like whereclause, order_by,
530 # and these features are all deprecated in any case.
531 #
532 # for these reasons, Query is not a Select, it remains an ORM
533 # object for which __clause_element__() must be called in order for
534 # it to provide a real expression object.
535 #
536 # from there, it starts to look much like Query itself won't be
537 # passed into the execute process and won't generate its own cache
538 # key; this will all occur in terms of the ORM-enabled Select.
539 stmt: Union[Select[_T], FromStatement[_T], UpdateBase]
541 if not self._compile_options._set_base_alias:
542 # if we don't have legacy top level aliasing features in use
543 # then convert to a future select() directly
544 stmt = self._statement_20(for_statement=True)
545 else:
546 stmt = self._compile_state(for_statement=True).statement
548 if self._params:
549 stmt = stmt.params(self._params)
551 return stmt
553 def _final_statement(
554 self, legacy_query_style: bool = True
555 ) -> Select[Unpack[TupleAny]]:
556 """Return the 'final' SELECT statement for this :class:`.Query`.
558 This is used by the testing suite only and is fairly inefficient.
560 This is the Core-only select() that will be rendered by a complete
561 compilation of this query, and is what .statement used to return
562 in 1.3.
565 """
567 q = self._clone()
569 return q._compile_state(
570 use_legacy_query_style=legacy_query_style
571 ).statement # type: ignore
573 def _statement_20(
574 self, for_statement: bool = False, use_legacy_query_style: bool = True
575 ) -> Union[Select[_T], FromStatement[_T]]:
576 # TODO: this event needs to be deprecated, as it currently applies
577 # only to ORM query and occurs at this spot that is now more
578 # or less an artificial spot
579 if self.dispatch.before_compile:
580 for fn in self.dispatch.before_compile:
581 new_query = fn(self)
582 if new_query is not None and new_query is not self:
583 self = new_query
584 if not fn._bake_ok: # type: ignore
585 self._compile_options += {"_bake_ok": False}
587 compile_options = self._compile_options
588 compile_options += {
589 "_for_statement": for_statement,
590 "_use_legacy_query_style": use_legacy_query_style,
591 }
593 stmt: Union[Select[_T], FromStatement[_T]]
595 if self._statement is not None:
596 stmt = FromStatement(self._raw_columns, self._statement)
597 stmt.__dict__.update(
598 _with_options=self._with_options,
599 _with_context_options=self._compile_state_funcs,
600 _compile_options=compile_options,
601 _execution_options=self._execution_options,
602 _propagate_attrs=self._propagate_attrs,
603 )
604 else:
605 # Query / select() internal attributes are 99% cross-compatible
606 stmt = Select._create_raw_select(**self.__dict__)
608 stmt.__dict__.update(
609 _label_style=self._label_style,
610 _compile_options=compile_options,
611 _propagate_attrs=self._propagate_attrs,
612 )
613 for ext in self._syntax_extensions:
614 stmt._apply_syntax_extension_to_self(ext)
615 stmt.__dict__.pop("session", None)
617 # ensure the ORM context is used to compile the statement, even
618 # if it has no ORM entities. This is so ORM-only things like
619 # _legacy_joins are picked up that wouldn't be picked up by the
620 # Core statement context
621 if "compile_state_plugin" not in stmt._propagate_attrs:
622 stmt._propagate_attrs = stmt._propagate_attrs.union(
623 {"compile_state_plugin": "orm", "plugin_subject": None}
624 )
626 return stmt
628 def subquery(
629 self,
630 name: Optional[str] = None,
631 with_labels: bool = False,
632 reduce_columns: bool = False,
633 ) -> Subquery:
634 """Return the full SELECT statement represented by
635 this :class:`_query.Query`, embedded within an
636 :class:`_expression.Alias`.
638 Eager JOIN generation within the query is disabled.
640 .. seealso::
642 :meth:`_sql.Select.subquery` - v2 comparable method.
644 :param name: string name to be assigned as the alias;
645 this is passed through to :meth:`_expression.FromClause.alias`.
646 If ``None``, a name will be deterministically generated
647 at compile time.
649 :param with_labels: if True, :meth:`.with_labels` will be called
650 on the :class:`_query.Query` first to apply table-qualified labels
651 to all columns.
653 :param reduce_columns: if True,
654 :meth:`_expression.Select.reduce_columns` will
655 be called on the resulting :func:`_expression.select` construct,
656 to remove same-named columns where one also refers to the other
657 via foreign key or WHERE clause equivalence.
659 """
660 q = self.enable_eagerloads(False)
661 if with_labels:
662 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
664 stmt = q._get_select_statement_only()
666 if TYPE_CHECKING:
667 assert isinstance(stmt, Select)
669 if reduce_columns:
670 stmt = stmt.reduce_columns()
671 return stmt.subquery(name=name)
673 def cte(
674 self,
675 name: Optional[str] = None,
676 recursive: bool = False,
677 nesting: bool = False,
678 ) -> CTE:
679 r"""Return the full SELECT statement represented by this
680 :class:`_query.Query` represented as a common table expression (CTE).
682 Parameters and usage are the same as those of the
683 :meth:`_expression.SelectBase.cte` method; see that method for
684 further details.
686 Here is the `PostgreSQL WITH
687 RECURSIVE example
688 <https://www.postgresql.org/docs/current/static/queries-with.html>`_.
689 Note that, in this example, the ``included_parts`` cte and the
690 ``incl_alias`` alias of it are Core selectables, which
691 means the columns are accessed via the ``.c.`` attribute. The
692 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
693 ``Part`` entity, so column-mapped attributes are available
694 directly::
696 from sqlalchemy.orm import aliased
699 class Part(Base):
700 __tablename__ = "part"
701 part = Column(String, primary_key=True)
702 sub_part = Column(String, primary_key=True)
703 quantity = Column(Integer)
706 included_parts = (
707 session.query(Part.sub_part, Part.part, Part.quantity)
708 .filter(Part.part == "our part")
709 .cte(name="included_parts", recursive=True)
710 )
712 incl_alias = aliased(included_parts, name="pr")
713 parts_alias = aliased(Part, name="p")
714 included_parts = included_parts.union_all(
715 session.query(
716 parts_alias.sub_part, parts_alias.part, parts_alias.quantity
717 ).filter(parts_alias.part == incl_alias.c.sub_part)
718 )
720 q = session.query(
721 included_parts.c.sub_part,
722 func.sum(included_parts.c.quantity).label("total_quantity"),
723 ).group_by(included_parts.c.sub_part)
725 .. seealso::
727 :meth:`_sql.Select.cte` - v2 equivalent method.
729 """ # noqa: E501
730 return (
731 self.enable_eagerloads(False)
732 ._get_select_statement_only()
733 .cte(name=name, recursive=recursive, nesting=nesting)
734 )
736 def label(self, name: Optional[str]) -> Label[Any]:
737 """Return the full SELECT statement represented by this
738 :class:`_query.Query`, converted
739 to a scalar subquery with a label of the given name.
741 .. seealso::
743 :meth:`_sql.Select.label` - v2 comparable method.
745 """
747 return (
748 self.enable_eagerloads(False)
749 ._get_select_statement_only()
750 .label(name)
751 )
753 @overload
754 def as_scalar( # type: ignore[overload-overlap]
755 self: Query[Tuple[_MAYBE_ENTITY]],
756 ) -> ScalarSelect[_MAYBE_ENTITY]: ...
758 @overload
759 def as_scalar(
760 self: Query[Tuple[_NOT_ENTITY]],
761 ) -> ScalarSelect[_NOT_ENTITY]: ...
763 @overload
764 def as_scalar(self) -> ScalarSelect[Any]: ...
766 @util.deprecated(
767 "1.4",
768 "The :meth:`_query.Query.as_scalar` method is deprecated and will be "
769 "removed in a future release. Please refer to "
770 ":meth:`_query.Query.scalar_subquery`.",
771 )
772 def as_scalar(self) -> ScalarSelect[Any]:
773 """Return the full SELECT statement represented by this
774 :class:`_query.Query`, converted to a scalar subquery.
776 """
777 return self.scalar_subquery()
779 @overload
780 def scalar_subquery(
781 self: Query[Tuple[_MAYBE_ENTITY]],
782 ) -> ScalarSelect[Any]: ...
784 @overload
785 def scalar_subquery(
786 self: Query[Tuple[_NOT_ENTITY]],
787 ) -> ScalarSelect[_NOT_ENTITY]: ...
789 @overload
790 def scalar_subquery(self) -> ScalarSelect[Any]: ...
792 def scalar_subquery(self) -> ScalarSelect[Any]:
793 """Return the full SELECT statement represented by this
794 :class:`_query.Query`, converted to a scalar subquery.
796 Analogous to
797 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
799 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
800 method replaces the :meth:`_query.Query.as_scalar` method.
802 .. seealso::
804 :meth:`_sql.Select.scalar_subquery` - v2 comparable method.
806 """
808 return (
809 self.enable_eagerloads(False)
810 ._get_select_statement_only()
811 .scalar_subquery()
812 )
814 @property
815 def selectable(self) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
816 """Return the :class:`_expression.Select` object emitted by this
817 :class:`_query.Query`.
819 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
821 query.enable_eagerloads(False).with_labels().statement
823 """
824 return self.__clause_element__()
826 def __clause_element__(
827 self,
828 ) -> Union[Select[_T], FromStatement[_T], UpdateBase]:
829 return (
830 self._with_compile_options(
831 _enable_eagerloads=False, _render_for_subquery=True
832 )
833 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
834 .statement
835 )
837 @overload
838 def only_return_tuples(
839 self: Query[_O], value: Literal[True]
840 ) -> RowReturningQuery[_O]: ...
842 @overload
843 def only_return_tuples(
844 self: Query[_O], value: Literal[False]
845 ) -> Query[_O]: ...
847 @_generative
848 def only_return_tuples(self, value: bool) -> Query[Any]:
849 """When set to True, the query results will always be a
850 :class:`.Row` object.
852 This can change a query that normally returns a single entity
853 as a scalar to return a :class:`.Row` result in all cases.
855 .. seealso::
857 :meth:`.Query.tuples` - returns tuples, but also at the typing
858 level will type results as ``Tuple``.
860 :meth:`_query.Query.is_single_entity`
862 :meth:`_engine.Result.tuples` - v2 comparable method.
864 """
865 self.load_options += dict(_only_return_tuples=value)
866 return self
868 @property
869 def is_single_entity(self) -> bool:
870 """Indicates if this :class:`_query.Query`
871 returns tuples or single entities.
873 Returns True if this query returns a single entity for each instance
874 in its result list, and False if this query returns a tuple of entities
875 for each result.
877 .. seealso::
879 :meth:`_query.Query.only_return_tuples`
881 """
882 return (
883 not self.load_options._only_return_tuples
884 and len(self._raw_columns) == 1
885 and "parententity" in self._raw_columns[0]._annotations
886 and isinstance(
887 self._raw_columns[0]._annotations["parententity"],
888 ORMColumnsClauseRole,
889 )
890 )
892 @_generative
893 def enable_eagerloads(self, value: bool) -> Self:
894 """Control whether or not eager joins and subqueries are
895 rendered.
897 When set to False, the returned Query will not render
898 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
899 :func:`~sqlalchemy.orm.subqueryload` options
900 or mapper-level ``lazy='joined'``/``lazy='subquery'``
901 configurations.
903 This is used primarily when nesting the Query's
904 statement into a subquery or other
905 selectable, or when using :meth:`_query.Query.yield_per`.
907 """
908 self._compile_options += {"_enable_eagerloads": value}
909 return self
911 @_generative
912 def _with_compile_options(self, **opt: Any) -> Self:
913 self._compile_options += opt
914 return self
916 @util.became_legacy_20(
917 ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`",
918 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
919 "instead.",
920 )
921 def with_labels(self) -> Self:
922 return self.set_label_style(
923 SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL
924 )
926 apply_labels = with_labels
928 @property
929 def get_label_style(self) -> SelectLabelStyle:
930 """
931 Retrieve the current label style.
933 .. versionadded:: 1.4
935 .. seealso::
937 :meth:`_sql.Select.get_label_style` - v2 equivalent method.
939 """
940 return self._label_style
942 def set_label_style(self, style: SelectLabelStyle) -> Self:
943 """Apply column labels to the return value of Query.statement.
945 Indicates that this Query's `statement` accessor should return
946 a SELECT statement that applies labels to all columns in the
947 form <tablename>_<columnname>; this is commonly used to
948 disambiguate columns from multiple tables which have the same
949 name.
951 When the `Query` actually issues SQL to load rows, it always
952 uses column labeling.
954 .. note:: The :meth:`_query.Query.set_label_style` method *only* applies
955 the output of :attr:`_query.Query.statement`, and *not* to any of
956 the result-row invoking systems of :class:`_query.Query` itself,
957 e.g.
958 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
959 To execute
960 a query using :meth:`_query.Query.set_label_style`, invoke the
961 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
963 result = session.execute(
964 query.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL).statement
965 )
967 .. versionadded:: 1.4
970 .. seealso::
972 :meth:`_sql.Select.set_label_style` - v2 equivalent method.
974 """ # noqa
975 if self._label_style is not style:
976 self = self._generate()
977 self._label_style = style
978 return self
980 @_generative
981 def enable_assertions(self, value: bool) -> Self:
982 """Control whether assertions are generated.
984 When set to False, the returned Query will
985 not assert its state before certain operations,
986 including that LIMIT/OFFSET has not been applied
987 when filter() is called, no criterion exists
988 when get() is called, and no "from_statement()"
989 exists when filter()/order_by()/group_by() etc.
990 is called. This more permissive mode is used by
991 custom Query subclasses to specify criterion or
992 other modifiers outside of the usual usage patterns.
994 Care should be taken to ensure that the usage
995 pattern is even possible. A statement applied
996 by from_statement() will override any criterion
997 set by filter() or order_by(), for example.
999 """
1000 self._enable_assertions = value
1001 return self
1003 @property
1004 def whereclause(self) -> Optional[ColumnElement[bool]]:
1005 """A readonly attribute which returns the current WHERE criterion for
1006 this Query.
1008 This returned value is a SQL expression construct, or ``None`` if no
1009 criterion has been established.
1011 .. seealso::
1013 :attr:`_sql.Select.whereclause` - v2 equivalent property.
1015 """
1016 return BooleanClauseList._construct_for_whereclause(
1017 self._where_criteria
1018 )
1020 @_generative
1021 def _with_current_path(self, path: PathRegistry) -> Self:
1022 """indicate that this query applies to objects loaded
1023 within a certain path.
1025 Used by deferred loaders (see strategies.py) which transfer
1026 query options from an originating query to a newly generated
1027 query intended for the deferred load.
1029 """
1030 self._compile_options += {"_current_path": path}
1031 return self
1033 @_generative
1034 def yield_per(self, count: int) -> Self:
1035 r"""Yield only ``count`` rows at a time.
1037 The purpose of this method is when fetching very large result sets
1038 (> 10K rows), to batch results in sub-collections and yield them
1039 out partially, so that the Python interpreter doesn't need to declare
1040 very large areas of memory which is both time consuming and leads
1041 to excessive memory use. The performance from fetching hundreds of
1042 thousands of rows can often double when a suitable yield-per setting
1043 (e.g. approximately 1000) is used, even with DBAPIs that buffer
1044 rows (which are most).
1046 As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
1047 equivalent to using the ``yield_per`` execution option at the ORM
1048 level. See the section :ref:`orm_queryguide_yield_per` for further
1049 background on this option.
1051 .. seealso::
1053 :ref:`orm_queryguide_yield_per`
1055 """
1056 self.load_options += {"_yield_per": count}
1057 return self
1059 @util.became_legacy_20(
1060 ":meth:`_orm.Query.get`",
1061 alternative="The method is now available as :meth:`_orm.Session.get`",
1062 )
1063 def get(self, ident: _PKIdentityArgument) -> Optional[_T]:
1064 """Return an instance based on the given primary key identifier,
1065 or ``None`` if not found.
1067 E.g.::
1069 my_user = session.query(User).get(5)
1071 some_object = session.query(VersionedFoo).get((5, 10))
1073 some_object = session.query(VersionedFoo).get({"id": 5, "version_id": 10})
1075 :meth:`_query.Query.get` is special in that it provides direct
1076 access to the identity map of the owning :class:`.Session`.
1077 If the given primary key identifier is present
1078 in the local identity map, the object is returned
1079 directly from this collection and no SQL is emitted,
1080 unless the object has been marked fully expired.
1081 If not present,
1082 a SELECT is performed in order to locate the object.
1084 :meth:`_query.Query.get` also will perform a check if
1085 the object is present in the identity map and
1086 marked as expired - a SELECT
1087 is emitted to refresh the object as well as to
1088 ensure that the row is still present.
1089 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
1091 :meth:`_query.Query.get` is only used to return a single
1092 mapped instance, not multiple instances or
1093 individual column constructs, and strictly
1094 on a single primary key value. The originating
1095 :class:`_query.Query` must be constructed in this way,
1096 i.e. against a single mapped entity,
1097 with no additional filtering criterion. Loading
1098 options via :meth:`_query.Query.options` may be applied
1099 however, and will be used if the object is not
1100 yet locally present.
1102 :param ident: A scalar, tuple, or dictionary representing the
1103 primary key. For a composite (e.g. multiple column) primary key,
1104 a tuple or dictionary should be passed.
1106 For a single-column primary key, the scalar calling form is typically
1107 the most expedient. If the primary key of a row is the value "5",
1108 the call looks like::
1110 my_object = query.get(5)
1112 The tuple form contains primary key values typically in
1113 the order in which they correspond to the mapped
1114 :class:`_schema.Table`
1115 object's primary key columns, or if the
1116 :paramref:`_orm.Mapper.primary_key` configuration parameter were
1117 used, in
1118 the order used for that parameter. For example, if the primary key
1119 of a row is represented by the integer
1120 digits "5, 10" the call would look like::
1122 my_object = query.get((5, 10))
1124 The dictionary form should include as keys the mapped attribute names
1125 corresponding to each element of the primary key. If the mapped class
1126 has the attributes ``id``, ``version_id`` as the attributes which
1127 store the object's primary key value, the call would look like::
1129 my_object = query.get({"id": 5, "version_id": 10})
1131 :return: The object instance, or ``None``.
1133 """ # noqa: E501
1134 self._no_criterion_assertion("get", order_by=False, distinct=False)
1136 # we still implement _get_impl() so that baked query can override
1137 # it
1138 return self._get_impl(ident, loading._load_on_pk_identity)
1140 def _get_impl(
1141 self,
1142 primary_key_identity: _PKIdentityArgument,
1143 db_load_fn: Callable[..., Any],
1144 identity_token: Optional[Any] = None,
1145 ) -> Optional[Any]:
1146 mapper = self._only_full_mapper_zero("get")
1147 return self.session._get_impl(
1148 mapper,
1149 primary_key_identity,
1150 db_load_fn,
1151 populate_existing=self.load_options._populate_existing,
1152 with_for_update=self._for_update_arg,
1153 options=self._with_options,
1154 identity_token=identity_token,
1155 execution_options=self._execution_options,
1156 )
1158 @property
1159 def lazy_loaded_from(self) -> Optional[InstanceState[Any]]:
1160 """An :class:`.InstanceState` that is using this :class:`_query.Query`
1161 for a lazy load operation.
1163 .. deprecated:: 1.4 This attribute should be viewed via the
1164 :attr:`.ORMExecuteState.lazy_loaded_from` attribute, within
1165 the context of the :meth:`.SessionEvents.do_orm_execute`
1166 event.
1168 .. seealso::
1170 :attr:`.ORMExecuteState.lazy_loaded_from`
1172 """
1173 return self.load_options._lazy_loaded_from # type: ignore
1175 @property
1176 def _current_path(self) -> PathRegistry:
1177 return self._compile_options._current_path # type: ignore
1179 @_generative
1180 def correlate(
1181 self,
1182 *fromclauses: Union[Literal[None, False], _FromClauseArgument],
1183 ) -> Self:
1184 """Return a :class:`.Query` construct which will correlate the given
1185 FROM clauses to that of an enclosing :class:`.Query` or
1186 :func:`~.expression.select`.
1188 The method here accepts mapped classes, :func:`.aliased` constructs,
1189 and :class:`_orm.Mapper` constructs as arguments, which are resolved
1190 into expression constructs, in addition to appropriate expression
1191 constructs.
1193 The correlation arguments are ultimately passed to
1194 :meth:`_expression.Select.correlate`
1195 after coercion to expression constructs.
1197 The correlation arguments take effect in such cases
1198 as when :meth:`_query.Query.from_self` is used, or when
1199 a subquery as returned by :meth:`_query.Query.subquery` is
1200 embedded in another :func:`_expression.select` construct.
1202 .. seealso::
1204 :meth:`_sql.Select.correlate` - v2 equivalent method.
1206 """
1208 self._auto_correlate = False
1209 if fromclauses and fromclauses[0] in {None, False}:
1210 self._correlate = ()
1211 else:
1212 self._correlate = self._correlate + tuple(
1213 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
1214 )
1215 return self
1217 @_generative
1218 def autoflush(self, setting: bool) -> Self:
1219 """Return a Query with a specific 'autoflush' setting.
1221 As of SQLAlchemy 1.4, the :meth:`_orm.Query.autoflush` method
1222 is equivalent to using the ``autoflush`` execution option at the
1223 ORM level. See the section :ref:`orm_queryguide_autoflush` for
1224 further background on this option.
1226 """
1227 self.load_options += {"_autoflush": setting}
1228 return self
1230 @_generative
1231 def populate_existing(self) -> Self:
1232 """Return a :class:`_query.Query`
1233 that will expire and refresh all instances
1234 as they are loaded, or reused from the current :class:`.Session`.
1236 As of SQLAlchemy 1.4, the :meth:`_orm.Query.populate_existing` method
1237 is equivalent to using the ``populate_existing`` execution option at
1238 the ORM level. See the section :ref:`orm_queryguide_populate_existing`
1239 for further background on this option.
1241 """
1242 self.load_options += {"_populate_existing": True}
1243 return self
1245 @_generative
1246 def _with_invoke_all_eagers(self, value: bool) -> Self:
1247 """Set the 'invoke all eagers' flag which causes joined- and
1248 subquery loaders to traverse into already-loaded related objects
1249 and collections.
1251 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1253 """
1254 self.load_options += {"_invoke_all_eagers": value}
1255 return self
1257 @util.became_legacy_20(
1258 ":meth:`_orm.Query.with_parent`",
1259 alternative="Use the :func:`_orm.with_parent` standalone construct.",
1260 )
1261 @util.preload_module("sqlalchemy.orm.relationships")
1262 def with_parent(
1263 self,
1264 instance: object,
1265 property: Optional[ # noqa: A002
1266 attributes.QueryableAttribute[Any]
1267 ] = None,
1268 from_entity: Optional[_ExternalEntityType[Any]] = None,
1269 ) -> Self:
1270 """Add filtering criterion that relates the given instance
1271 to a child object or collection, using its attribute state
1272 as well as an established :func:`_orm.relationship()`
1273 configuration.
1275 The method uses the :func:`.with_parent` function to generate
1276 the clause, the result of which is passed to
1277 :meth:`_query.Query.filter`.
1279 Parameters are the same as :func:`.with_parent`, with the exception
1280 that the given property can be None, in which case a search is
1281 performed against this :class:`_query.Query` object's target mapper.
1283 :param instance:
1284 An instance which has some :func:`_orm.relationship`.
1286 :param property:
1287 Class bound attribute which indicates
1288 what relationship from the instance should be used to reconcile the
1289 parent/child relationship.
1291 :param from_entity:
1292 Entity in which to consider as the left side. This defaults to the
1293 "zero" entity of the :class:`_query.Query` itself.
1295 """
1296 relationships = util.preloaded.orm_relationships
1298 if from_entity:
1299 entity_zero = inspect(from_entity)
1300 else:
1301 entity_zero = _legacy_filter_by_entity_zero(self)
1302 if property is None:
1303 # TODO: deprecate, property has to be supplied
1304 mapper = object_mapper(instance)
1306 for prop in mapper.iterate_properties:
1307 if (
1308 isinstance(prop, relationships.RelationshipProperty)
1309 and prop.mapper is entity_zero.mapper # type: ignore
1310 ):
1311 property = prop # type: ignore # noqa: A001
1312 break
1313 else:
1314 raise sa_exc.InvalidRequestError(
1315 "Could not locate a property which relates instances "
1316 "of class '%s' to instances of class '%s'"
1317 % (
1318 entity_zero.mapper.class_.__name__, # type: ignore
1319 instance.__class__.__name__,
1320 )
1321 )
1323 return self.filter(
1324 with_parent(
1325 instance,
1326 property, # type: ignore
1327 entity_zero.entity, # type: ignore
1328 )
1329 )
1331 @_generative
1332 def add_entity(
1333 self,
1334 entity: _EntityType[Any],
1335 alias: Optional[Union[Alias, Subquery]] = None,
1336 ) -> Query[Any]:
1337 """add a mapped entity to the list of result columns
1338 to be returned.
1340 .. seealso::
1342 :meth:`_sql.Select.add_columns` - v2 comparable method.
1343 """
1345 if alias is not None:
1346 # TODO: deprecate
1347 entity = AliasedClass(entity, alias)
1349 self._raw_columns = list(self._raw_columns)
1351 self._raw_columns.append(
1352 coercions.expect(
1353 roles.ColumnsClauseRole, entity, apply_propagate_attrs=self
1354 )
1355 )
1356 return self
1358 @_generative
1359 def with_session(self, session: Session) -> Self:
1360 """Return a :class:`_query.Query` that will use the given
1361 :class:`.Session`.
1363 While the :class:`_query.Query`
1364 object is normally instantiated using the
1365 :meth:`.Session.query` method, it is legal to build the
1366 :class:`_query.Query`
1367 directly without necessarily using a :class:`.Session`. Such a
1368 :class:`_query.Query` object, or any :class:`_query.Query`
1369 already associated
1370 with a different :class:`.Session`, can produce a new
1371 :class:`_query.Query`
1372 object associated with a target session using this method::
1374 from sqlalchemy.orm import Query
1376 query = Query([MyClass]).filter(MyClass.id == 5)
1378 result = query.with_session(my_session).one()
1380 """
1382 self.session = session
1383 return self
1385 def _legacy_from_self(
1386 self, *entities: _ColumnsClauseArgument[Any]
1387 ) -> Self:
1388 # used for query.count() as well as for the same
1389 # function in BakedQuery, as well as some old tests in test_baked.py.
1391 fromclause = (
1392 self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
1393 .correlate(None)
1394 .subquery()
1395 ._anonymous_fromclause()
1396 )
1398 q = self._from_selectable(fromclause)
1400 if entities:
1401 q._set_entities(entities)
1402 return q
1404 @_generative
1405 def _set_enable_single_crit(self, val: bool) -> Self:
1406 self._compile_options += {"_enable_single_crit": val}
1407 return self
1409 @_generative
1410 def _from_selectable(
1411 self, fromclause: FromClause, set_entity_from: bool = True
1412 ) -> Self:
1413 for attr in (
1414 "_where_criteria",
1415 "_order_by_clauses",
1416 "_group_by_clauses",
1417 "_limit_clause",
1418 "_offset_clause",
1419 "_last_joined_entity",
1420 "_setup_joins",
1421 "_memoized_select_entities",
1422 "_distinct",
1423 "_distinct_on",
1424 "_having_criteria",
1425 "_prefixes",
1426 "_suffixes",
1427 "_syntax_extensions",
1428 ):
1429 self.__dict__.pop(attr, None)
1430 self._set_select_from([fromclause], set_entity_from)
1431 self._compile_options += {
1432 "_enable_single_crit": False,
1433 }
1435 return self
1437 @util.deprecated(
1438 "1.4",
1439 ":meth:`_query.Query.values` "
1440 "is deprecated and will be removed in a "
1441 "future release. Please use :meth:`_query.Query.with_entities`",
1442 )
1443 def values(self, *columns: _ColumnsClauseArgument[Any]) -> Iterable[Any]:
1444 """Return an iterator yielding result tuples corresponding
1445 to the given list of columns
1447 """
1448 return self._values_no_warn(*columns)
1450 _values = values
1452 def _values_no_warn(
1453 self, *columns: _ColumnsClauseArgument[Any]
1454 ) -> Iterable[Any]:
1455 if not columns:
1456 return iter(())
1457 q = self._clone().enable_eagerloads(False)
1458 q._set_entities(columns)
1459 if not q.load_options._yield_per:
1460 q.load_options += {"_yield_per": 10}
1461 return iter(q)
1463 @util.deprecated(
1464 "1.4",
1465 ":meth:`_query.Query.value` "
1466 "is deprecated and will be removed in a "
1467 "future release. Please use :meth:`_query.Query.with_entities` "
1468 "in combination with :meth:`_query.Query.scalar`",
1469 )
1470 def value(self, column: _ColumnExpressionArgument[Any]) -> Any:
1471 """Return a scalar result corresponding to the given
1472 column expression.
1474 """
1475 try:
1476 return next(self._values_no_warn(column))[0] # type: ignore
1477 except StopIteration:
1478 return None
1480 @overload
1481 def with_entities(self, _entity: _EntityType[_O]) -> Query[_O]: ...
1483 @overload
1484 def with_entities(
1485 self,
1486 _colexpr: roles.TypedColumnsClauseRole[_T],
1487 ) -> RowReturningQuery[Tuple[_T]]: ...
1489 # START OVERLOADED FUNCTIONS self.with_entities RowReturningQuery 2-8
1491 # code within this block is **programmatically,
1492 # statically generated** by tools/generate_tuple_map_overloads.py
1494 @overload
1495 def with_entities(
1496 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1497 ) -> RowReturningQuery[_T0, _T1]: ...
1499 @overload
1500 def with_entities(
1501 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1502 ) -> RowReturningQuery[_T0, _T1, _T2]: ...
1504 @overload
1505 def with_entities(
1506 self,
1507 __ent0: _TCCA[_T0],
1508 __ent1: _TCCA[_T1],
1509 __ent2: _TCCA[_T2],
1510 __ent3: _TCCA[_T3],
1511 /,
1512 ) -> RowReturningQuery[_T0, _T1, _T2, _T3]: ...
1514 @overload
1515 def with_entities(
1516 self,
1517 __ent0: _TCCA[_T0],
1518 __ent1: _TCCA[_T1],
1519 __ent2: _TCCA[_T2],
1520 __ent3: _TCCA[_T3],
1521 __ent4: _TCCA[_T4],
1522 /,
1523 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4]: ...
1525 @overload
1526 def with_entities(
1527 self,
1528 __ent0: _TCCA[_T0],
1529 __ent1: _TCCA[_T1],
1530 __ent2: _TCCA[_T2],
1531 __ent3: _TCCA[_T3],
1532 __ent4: _TCCA[_T4],
1533 __ent5: _TCCA[_T5],
1534 /,
1535 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1537 @overload
1538 def with_entities(
1539 self,
1540 __ent0: _TCCA[_T0],
1541 __ent1: _TCCA[_T1],
1542 __ent2: _TCCA[_T2],
1543 __ent3: _TCCA[_T3],
1544 __ent4: _TCCA[_T4],
1545 __ent5: _TCCA[_T5],
1546 __ent6: _TCCA[_T6],
1547 /,
1548 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1550 @overload
1551 def with_entities(
1552 self,
1553 __ent0: _TCCA[_T0],
1554 __ent1: _TCCA[_T1],
1555 __ent2: _TCCA[_T2],
1556 __ent3: _TCCA[_T3],
1557 __ent4: _TCCA[_T4],
1558 __ent5: _TCCA[_T5],
1559 __ent6: _TCCA[_T6],
1560 __ent7: _TCCA[_T7],
1561 /,
1562 *entities: _ColumnsClauseArgument[Any],
1563 ) -> RowReturningQuery[
1564 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1565 ]: ...
1567 # END OVERLOADED FUNCTIONS self.with_entities
1569 @overload
1570 def with_entities(
1571 self, *entities: _ColumnsClauseArgument[Any]
1572 ) -> Query[Any]: ...
1574 @_generative
1575 def with_entities(
1576 self, *entities: _ColumnsClauseArgument[Any], **__kw: Any
1577 ) -> Query[Any]:
1578 r"""Return a new :class:`_query.Query`
1579 replacing the SELECT list with the
1580 given entities.
1582 e.g.::
1584 # Users, filtered on some arbitrary criterion
1585 # and then ordered by related email address
1586 q = (
1587 session.query(User)
1588 .join(User.address)
1589 .filter(User.name.like("%ed%"))
1590 .order_by(Address.email)
1591 )
1593 # given *only* User.id==5, Address.email, and 'q', what
1594 # would the *next* User in the result be ?
1595 subq = (
1596 q.with_entities(Address.email)
1597 .order_by(None)
1598 .filter(User.id == 5)
1599 .subquery()
1600 )
1601 q = q.join((subq, subq.c.email < Address.email)).limit(1)
1603 .. seealso::
1605 :meth:`_sql.Select.with_only_columns` - v2 comparable method.
1606 """
1607 if __kw:
1608 raise _no_kw()
1610 # Query has all the same fields as Select for this operation
1611 # this could in theory be based on a protocol but not sure if it's
1612 # worth it
1613 _MemoizedSelectEntities._generate_for_statement(self) # type: ignore
1614 self._set_entities(entities)
1615 return self
1617 @_generative
1618 def add_columns(
1619 self, *column: _ColumnExpressionArgument[Any]
1620 ) -> Query[Any]:
1621 """Add one or more column expressions to the list
1622 of result columns to be returned.
1624 .. seealso::
1626 :meth:`_sql.Select.add_columns` - v2 comparable method.
1627 """
1629 self._raw_columns = list(self._raw_columns)
1631 self._raw_columns.extend(
1632 coercions.expect(
1633 roles.ColumnsClauseRole,
1634 c,
1635 apply_propagate_attrs=self,
1636 post_inspect=True,
1637 )
1638 for c in column
1639 )
1640 return self
1642 @util.deprecated(
1643 "1.4",
1644 ":meth:`_query.Query.add_column` "
1645 "is deprecated and will be removed in a "
1646 "future release. Please use :meth:`_query.Query.add_columns`",
1647 )
1648 def add_column(self, column: _ColumnExpressionArgument[Any]) -> Query[Any]:
1649 """Add a column expression to the list of result columns to be
1650 returned.
1652 """
1653 return self.add_columns(column)
1655 @_generative
1656 def options(self, *args: ExecutableOption) -> Self:
1657 """Return a new :class:`_query.Query` object,
1658 applying the given list of
1659 mapper options.
1661 Most supplied options regard changing how column- and
1662 relationship-mapped attributes are loaded.
1664 .. seealso::
1666 :ref:`loading_columns`
1668 :ref:`relationship_loader_options`
1670 """
1672 opts = tuple(util.flatten_iterator(args))
1673 if self._compile_options._current_path:
1674 # opting for lower method overhead for the checks
1675 for opt in opts:
1676 if not opt._is_core and opt._is_legacy_option: # type: ignore
1677 opt.process_query_conditionally(self) # type: ignore
1678 else:
1679 for opt in opts:
1680 if not opt._is_core and opt._is_legacy_option: # type: ignore
1681 opt.process_query(self) # type: ignore
1683 self._with_options += opts
1684 return self
1686 def with_transformation(
1687 self, fn: Callable[[Query[Any]], Query[Any]]
1688 ) -> Query[Any]:
1689 """Return a new :class:`_query.Query` object transformed by
1690 the given function.
1692 E.g.::
1694 def filter_something(criterion):
1695 def transform(q):
1696 return q.filter(criterion)
1698 return transform
1701 q = q.with_transformation(filter_something(x == 5))
1703 This allows ad-hoc recipes to be created for :class:`_query.Query`
1704 objects.
1706 """
1707 return fn(self)
1709 def get_execution_options(self) -> _ImmutableExecuteOptions:
1710 """Get the non-SQL options which will take effect during execution.
1712 .. seealso::
1714 :meth:`_query.Query.execution_options`
1716 :meth:`_sql.Select.get_execution_options` - v2 comparable method.
1718 """
1719 return self._execution_options
1721 @overload
1722 def execution_options(
1723 self,
1724 *,
1725 compiled_cache: Optional[CompiledCacheType] = ...,
1726 logging_token: str = ...,
1727 isolation_level: IsolationLevel = ...,
1728 no_parameters: bool = False,
1729 stream_results: bool = False,
1730 max_row_buffer: int = ...,
1731 yield_per: int = ...,
1732 driver_column_names: bool = ...,
1733 insertmanyvalues_page_size: int = ...,
1734 schema_translate_map: Optional[SchemaTranslateMapType] = ...,
1735 populate_existing: bool = False,
1736 autoflush: bool = False,
1737 preserve_rowcount: bool = False,
1738 **opt: Any,
1739 ) -> Self: ...
1741 @overload
1742 def execution_options(self, **opt: Any) -> Self: ...
1744 @_generative
1745 def execution_options(self, **kwargs: Any) -> Self:
1746 """Set non-SQL options which take effect during execution.
1748 Options allowed here include all of those accepted by
1749 :meth:`_engine.Connection.execution_options`, as well as a series
1750 of ORM specific options:
1752 ``populate_existing=True`` - equivalent to using
1753 :meth:`_orm.Query.populate_existing`
1755 ``autoflush=True|False`` - equivalent to using
1756 :meth:`_orm.Query.autoflush`
1758 ``yield_per=<value>`` - equivalent to using
1759 :meth:`_orm.Query.yield_per`
1761 Note that the ``stream_results`` execution option is enabled
1762 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1763 method or execution option is used.
1765 .. versionadded:: 1.4 - added ORM options to
1766 :meth:`_orm.Query.execution_options`
1768 The execution options may also be specified on a per execution basis
1769 when using :term:`2.0 style` queries via the
1770 :paramref:`_orm.Session.execution_options` parameter.
1772 .. warning:: The
1773 :paramref:`_engine.Connection.execution_options.stream_results`
1774 parameter should not be used at the level of individual ORM
1775 statement executions, as the :class:`_orm.Session` will not track
1776 objects from different schema translate maps within a single
1777 session. For multiple schema translate maps within the scope of a
1778 single :class:`_orm.Session`, see :ref:`examples_sharding`.
1781 .. seealso::
1783 :ref:`engine_stream_results`
1785 :meth:`_query.Query.get_execution_options`
1787 :meth:`_sql.Select.execution_options` - v2 equivalent method.
1789 """
1790 self._execution_options = self._execution_options.union(kwargs)
1791 return self
1793 @_generative
1794 def with_for_update(
1795 self,
1796 *,
1797 nowait: bool = False,
1798 read: bool = False,
1799 of: Optional[_ForUpdateOfArgument] = None,
1800 skip_locked: bool = False,
1801 key_share: bool = False,
1802 ) -> Self:
1803 """return a new :class:`_query.Query`
1804 with the specified options for the
1805 ``FOR UPDATE`` clause.
1807 The behavior of this method is identical to that of
1808 :meth:`_expression.GenerativeSelect.with_for_update`.
1809 When called with no arguments,
1810 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1811 appended. When additional arguments are specified, backend-specific
1812 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1813 can take effect.
1815 E.g.::
1817 q = (
1818 sess.query(User)
1819 .populate_existing()
1820 .with_for_update(nowait=True, of=User)
1821 )
1823 The above query on a PostgreSQL backend will render like:
1825 .. sourcecode:: sql
1827 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1829 .. warning::
1831 Using ``with_for_update`` in the context of eager loading
1832 relationships is not officially supported or recommended by
1833 SQLAlchemy and may not work with certain queries on various
1834 database backends. When ``with_for_update`` is successfully used
1835 with a query that involves :func:`_orm.joinedload`, SQLAlchemy will
1836 attempt to emit SQL that locks all involved tables.
1838 .. note:: It is generally a good idea to combine the use of the
1839 :meth:`_orm.Query.populate_existing` method when using the
1840 :meth:`_orm.Query.with_for_update` method. The purpose of
1841 :meth:`_orm.Query.populate_existing` is to force all the data read
1842 from the SELECT to be populated into the ORM objects returned,
1843 even if these objects are already in the :term:`identity map`.
1845 .. seealso::
1847 :meth:`_expression.GenerativeSelect.with_for_update`
1848 - Core level method with
1849 full argument and behavioral description.
1851 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1852 objects already loaded in the identity map.
1854 """ # noqa: E501
1856 self._for_update_arg = ForUpdateArg(
1857 read=read,
1858 nowait=nowait,
1859 of=of,
1860 skip_locked=skip_locked,
1861 key_share=key_share,
1862 )
1863 return self
1865 @_generative
1866 def params(
1867 self, __params: Optional[Dict[str, Any]] = None, /, **kw: Any
1868 ) -> Self:
1869 r"""Add values for bind parameters which may have been
1870 specified in filter().
1872 Parameters may be specified using \**kwargs, or optionally a single
1873 dictionary as the first positional argument. The reason for both is
1874 that \**kwargs is convenient, however some parameter dictionaries
1875 contain unicode keys in which case \**kwargs cannot be used.
1877 """
1878 if __params:
1879 kw.update(__params)
1880 self._params = self._params.union(kw)
1881 return self
1883 def where(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1884 """A synonym for :meth:`.Query.filter`.
1886 .. versionadded:: 1.4
1888 .. seealso::
1890 :meth:`_sql.Select.where` - v2 equivalent method.
1892 """
1893 return self.filter(*criterion)
1895 @_generative
1896 @_assertions(_no_statement_condition, _no_limit_offset)
1897 def filter(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1898 r"""Apply the given filtering criterion to a copy
1899 of this :class:`_query.Query`, using SQL expressions.
1901 e.g.::
1903 session.query(MyClass).filter(MyClass.name == "some name")
1905 Multiple criteria may be specified as comma separated; the effect
1906 is that they will be joined together using the :func:`.and_`
1907 function::
1909 session.query(MyClass).filter(MyClass.name == "some name", MyClass.id > 5)
1911 The criterion is any SQL expression object applicable to the
1912 WHERE clause of a select. String expressions are coerced
1913 into SQL expression constructs via the :func:`_expression.text`
1914 construct.
1916 .. seealso::
1918 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1920 :meth:`_sql.Select.where` - v2 equivalent method.
1922 """ # noqa: E501
1923 for crit in list(criterion):
1924 crit = coercions.expect(
1925 roles.WhereHavingRole, crit, apply_propagate_attrs=self
1926 )
1928 self._where_criteria += (crit,)
1929 return self
1931 @util.memoized_property
1932 def _last_joined_entity(
1933 self,
1934 ) -> Optional[Union[_InternalEntityType[Any], _JoinTargetElement]]:
1935 if self._setup_joins:
1936 return _determine_last_joined_entity(
1937 self._setup_joins,
1938 )
1939 else:
1940 return None
1942 def _filter_by_zero(self) -> Any:
1943 """for the filter_by() method, return the target entity for which
1944 we will attempt to derive an expression from based on string name.
1946 """
1948 if self._setup_joins:
1949 _last_joined_entity = self._last_joined_entity
1950 if _last_joined_entity is not None:
1951 return _last_joined_entity
1953 # discussion related to #7239
1954 # special check determines if we should try to derive attributes
1955 # for filter_by() from the "from object", i.e., if the user
1956 # called query.select_from(some selectable).filter_by(some_attr=value).
1957 # We don't want to do that in the case that methods like
1958 # from_self(), select_entity_from(), or a set op like union() were
1959 # called; while these methods also place a
1960 # selectable in the _from_obj collection, they also set up
1961 # the _set_base_alias boolean which turns on the whole "adapt the
1962 # entity to this selectable" thing, meaning the query still continues
1963 # to construct itself in terms of the lead entity that was passed
1964 # to query(), e.g. query(User).from_self() is still in terms of User,
1965 # and not the subquery that from_self() created. This feature of
1966 # "implicitly adapt all occurrences of entity X to some arbitrary
1967 # subquery" is the main thing I am trying to do away with in 2.0 as
1968 # users should now used aliased() for that, but I can't entirely get
1969 # rid of it due to query.union() and other set ops relying upon it.
1970 #
1971 # compare this to the base Select()._filter_by_zero() which can
1972 # just return self._from_obj[0] if present, because there is no
1973 # "_set_base_alias" feature.
1974 #
1975 # IOW, this conditional essentially detects if
1976 # "select_from(some_selectable)" has been called, as opposed to
1977 # "select_entity_from()", "from_self()"
1978 # or "union() / some_set_op()".
1979 if self._from_obj and not self._compile_options._set_base_alias:
1980 return self._from_obj[0]
1982 return self._raw_columns[0]
1984 def filter_by(self, **kwargs: Any) -> Self:
1985 r"""Apply the given filtering criterion to a copy
1986 of this :class:`_query.Query`, using keyword expressions.
1988 e.g.::
1990 session.query(MyClass).filter_by(name="some name")
1992 Multiple criteria may be specified as comma separated; the effect
1993 is that they will be joined together using the :func:`.and_`
1994 function::
1996 session.query(MyClass).filter_by(name="some name", id=5)
1998 The keyword expressions are extracted from the primary
1999 entity of the query, or the last entity that was the
2000 target of a call to :meth:`_query.Query.join`.
2002 .. note::
2004 :class:`_query.Query` is a legacy construct as of SQLAlchemy 2.0.
2005 See :meth:`_sql.Select.filter_by` for the comparable method on
2006 2.0-style :func:`_sql.select` constructs, where the behavior has
2007 been enhanced in version 2.1 to search across all FROM clause
2008 entities. See :ref:`change_8601` for background.
2010 .. seealso::
2012 :meth:`_query.Query.filter` - filter on SQL expressions.
2014 :meth:`_sql.Select.filter_by` - v2 comparable method.
2016 """
2017 from_entity = self._filter_by_zero()
2019 clauses = [
2020 _entity_namespace_key(from_entity, key) == value
2021 for key, value in kwargs.items()
2022 ]
2023 return self.filter(*clauses)
2025 @_generative
2026 def order_by(
2027 self,
2028 __first: Union[
2029 Literal[None, False, _NoArg.NO_ARG],
2030 _ColumnExpressionOrStrLabelArgument[Any],
2031 ] = _NoArg.NO_ARG,
2032 /,
2033 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2034 ) -> Self:
2035 """Apply one or more ORDER BY criteria to the query and return
2036 the newly resulting :class:`_query.Query`.
2038 e.g.::
2040 q = session.query(Entity).order_by(Entity.id, Entity.name)
2042 Calling this method multiple times is equivalent to calling it once
2043 with all the clauses concatenated. All existing ORDER BY criteria may
2044 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
2045 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
2047 # will erase all ORDER BY and ORDER BY new_col alone
2048 q = q.order_by(None).order_by(new_col)
2050 .. seealso::
2052 These sections describe ORDER BY in terms of :term:`2.0 style`
2053 invocation but apply to :class:`_orm.Query` as well:
2055 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
2057 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2059 :meth:`_sql.Select.order_by` - v2 equivalent method.
2061 """
2063 for assertion in (self._no_statement_condition, self._no_limit_offset):
2064 assertion("order_by")
2066 if not clauses and (__first is None or __first is False):
2067 self._order_by_clauses = ()
2068 elif __first is not _NoArg.NO_ARG:
2069 criterion = tuple(
2070 coercions.expect(roles.OrderByRole, clause)
2071 for clause in (__first,) + clauses
2072 )
2073 self._order_by_clauses += criterion
2075 return self
2077 @_generative
2078 def group_by(
2079 self,
2080 __first: Union[
2081 Literal[None, False, _NoArg.NO_ARG],
2082 _ColumnExpressionOrStrLabelArgument[Any],
2083 ] = _NoArg.NO_ARG,
2084 /,
2085 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2086 ) -> Self:
2087 """Apply one or more GROUP BY criterion to the query and return
2088 the newly resulting :class:`_query.Query`.
2090 All existing GROUP BY settings can be suppressed by
2091 passing ``None`` - this will suppress any GROUP BY configured
2092 on mappers as well.
2094 .. seealso::
2096 These sections describe GROUP BY in terms of :term:`2.0 style`
2097 invocation but apply to :class:`_orm.Query` as well:
2099 :ref:`tutorial_group_by_w_aggregates` - in the
2100 :ref:`unified_tutorial`
2102 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2104 :meth:`_sql.Select.group_by` - v2 equivalent method.
2106 """
2108 for assertion in (self._no_statement_condition, self._no_limit_offset):
2109 assertion("group_by")
2111 if not clauses and (__first is None or __first is False):
2112 self._group_by_clauses = ()
2113 elif __first is not _NoArg.NO_ARG:
2114 criterion = tuple(
2115 coercions.expect(roles.GroupByRole, clause)
2116 for clause in (__first,) + clauses
2117 )
2118 self._group_by_clauses += criterion
2119 return self
2121 @_generative
2122 @_assertions(_no_statement_condition, _no_limit_offset)
2123 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:
2124 r"""Apply a HAVING criterion to the query and return the
2125 newly resulting :class:`_query.Query`.
2127 :meth:`_query.Query.having` is used in conjunction with
2128 :meth:`_query.Query.group_by`.
2130 HAVING criterion makes it possible to use filters on aggregate
2131 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
2133 q = (
2134 session.query(User.id)
2135 .join(User.addresses)
2136 .group_by(User.id)
2137 .having(func.count(Address.id) > 2)
2138 )
2140 .. seealso::
2142 :meth:`_sql.Select.having` - v2 equivalent method.
2144 """
2146 for criterion in having:
2147 having_criteria = coercions.expect(
2148 roles.WhereHavingRole, criterion
2149 )
2150 self._having_criteria += (having_criteria,)
2151 return self
2153 def _set_op(self, expr_fn: Any, *q: Query[Any]) -> Self:
2154 list_of_queries = (self,) + q
2155 return self._from_selectable(expr_fn(*(list_of_queries)).subquery())
2157 def union(self, *q: Query[Any]) -> Self:
2158 """Produce a UNION of this Query against one or more queries.
2160 e.g.::
2162 q1 = sess.query(SomeClass).filter(SomeClass.foo == "bar")
2163 q2 = sess.query(SomeClass).filter(SomeClass.bar == "foo")
2165 q3 = q1.union(q2)
2167 The method accepts multiple Query objects so as to control
2168 the level of nesting. A series of ``union()`` calls such as::
2170 x.union(y).union(z).all()
2172 will nest on each ``union()``, and produces:
2174 .. sourcecode:: sql
2176 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
2177 SELECT * FROM y) UNION SELECT * FROM Z)
2179 Whereas::
2181 x.union(y, z).all()
2183 produces:
2185 .. sourcecode:: sql
2187 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
2188 SELECT * FROM Z)
2190 Note that many database backends do not allow ORDER BY to
2191 be rendered on a query called within UNION, EXCEPT, etc.
2192 To disable all ORDER BY clauses including those configured
2193 on mappers, issue ``query.order_by(None)`` - the resulting
2194 :class:`_query.Query` object will not render ORDER BY within
2195 its SELECT statement.
2197 .. seealso::
2199 :meth:`_sql.Select.union` - v2 equivalent method.
2201 """
2202 return self._set_op(expression.union, *q)
2204 def union_all(self, *q: Query[Any]) -> Self:
2205 """Produce a UNION ALL of this Query against one or more queries.
2207 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2208 that method for usage examples.
2210 .. seealso::
2212 :meth:`_sql.Select.union_all` - v2 equivalent method.
2214 """
2215 return self._set_op(expression.union_all, *q)
2217 def intersect(self, *q: Query[Any]) -> Self:
2218 """Produce an INTERSECT of this Query against one or more queries.
2220 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2221 that method for usage examples.
2223 .. seealso::
2225 :meth:`_sql.Select.intersect` - v2 equivalent method.
2227 """
2228 return self._set_op(expression.intersect, *q)
2230 def intersect_all(self, *q: Query[Any]) -> Self:
2231 """Produce an INTERSECT ALL of this Query against one or more queries.
2233 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2234 that method for usage examples.
2236 .. seealso::
2238 :meth:`_sql.Select.intersect_all` - v2 equivalent method.
2240 """
2241 return self._set_op(expression.intersect_all, *q)
2243 def except_(self, *q: Query[Any]) -> Self:
2244 """Produce an EXCEPT of this Query against one or more queries.
2246 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2247 that method for usage examples.
2249 .. seealso::
2251 :meth:`_sql.Select.except_` - v2 equivalent method.
2253 """
2254 return self._set_op(expression.except_, *q)
2256 def except_all(self, *q: Query[Any]) -> Self:
2257 """Produce an EXCEPT ALL of this Query against one or more queries.
2259 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2260 that method for usage examples.
2262 .. seealso::
2264 :meth:`_sql.Select.except_all` - v2 equivalent method.
2266 """
2267 return self._set_op(expression.except_all, *q)
2269 @_generative
2270 @_assertions(_no_statement_condition, _no_limit_offset)
2271 def join(
2272 self,
2273 target: _JoinTargetArgument,
2274 onclause: Optional[_OnClauseArgument] = None,
2275 *,
2276 isouter: bool = False,
2277 full: bool = False,
2278 ) -> Self:
2279 r"""Create a SQL JOIN against this :class:`_query.Query`
2280 object's criterion
2281 and apply generatively, returning the newly resulting
2282 :class:`_query.Query`.
2284 **Simple Relationship Joins**
2286 Consider a mapping between two classes ``User`` and ``Address``,
2287 with a relationship ``User.addresses`` representing a collection
2288 of ``Address`` objects associated with each ``User``. The most
2289 common usage of :meth:`_query.Query.join`
2290 is to create a JOIN along this
2291 relationship, using the ``User.addresses`` attribute as an indicator
2292 for how this should occur::
2294 q = session.query(User).join(User.addresses)
2296 Where above, the call to :meth:`_query.Query.join` along
2297 ``User.addresses`` will result in SQL approximately equivalent to:
2299 .. sourcecode:: sql
2301 SELECT user.id, user.name
2302 FROM user JOIN address ON user.id = address.user_id
2304 In the above example we refer to ``User.addresses`` as passed to
2305 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2306 how the "ON" portion of the JOIN should be constructed.
2308 To construct a chain of joins, multiple :meth:`_query.Query.join`
2309 calls may be used. The relationship-bound attribute implies both
2310 the left and right side of the join at once::
2312 q = (
2313 session.query(User)
2314 .join(User.orders)
2315 .join(Order.items)
2316 .join(Item.keywords)
2317 )
2319 .. note:: as seen in the above example, **the order in which each
2320 call to the join() method occurs is important**. Query would not,
2321 for example, know how to join correctly if we were to specify
2322 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2323 such a case, depending on the arguments passed, it may raise an
2324 error that it doesn't know how to join, or it may produce invalid
2325 SQL in which case the database will raise an error. In correct
2326 practice, the
2327 :meth:`_query.Query.join` method is invoked in such a way that lines
2328 up with how we would want the JOIN clauses in SQL to be
2329 rendered, and each call should represent a clear link from what
2330 precedes it.
2332 **Joins to a Target Entity or Selectable**
2334 A second form of :meth:`_query.Query.join` allows any mapped entity or
2335 core selectable construct as a target. In this usage,
2336 :meth:`_query.Query.join` will attempt to create a JOIN along the
2337 natural foreign key relationship between two entities::
2339 q = session.query(User).join(Address)
2341 In the above calling form, :meth:`_query.Query.join` is called upon to
2342 create the "on clause" automatically for us. This calling form will
2343 ultimately raise an error if either there are no foreign keys between
2344 the two entities, or if there are multiple foreign key linkages between
2345 the target entity and the entity or entities already present on the
2346 left side such that creating a join requires more information. Note
2347 that when indicating a join to a target without any ON clause, ORM
2348 configured relationships are not taken into account.
2350 **Joins to a Target with an ON Clause**
2352 The third calling form allows both the target entity as well
2353 as the ON clause to be passed explicitly. A example that includes
2354 a SQL expression as the ON clause is as follows::
2356 q = session.query(User).join(Address, User.id == Address.user_id)
2358 The above form may also use a relationship-bound attribute as the
2359 ON clause as well::
2361 q = session.query(User).join(Address, User.addresses)
2363 The above syntax can be useful for the case where we wish
2364 to join to an alias of a particular target entity. If we wanted
2365 to join to ``Address`` twice, it could be achieved using two
2366 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2368 a1 = aliased(Address)
2369 a2 = aliased(Address)
2371 q = (
2372 session.query(User)
2373 .join(a1, User.addresses)
2374 .join(a2, User.addresses)
2375 .filter(a1.email_address == "ed@foo.com")
2376 .filter(a2.email_address == "ed@bar.com")
2377 )
2379 The relationship-bound calling form can also specify a target entity
2380 using the :meth:`_orm.PropComparator.of_type` method; a query
2381 equivalent to the one above would be::
2383 a1 = aliased(Address)
2384 a2 = aliased(Address)
2386 q = (
2387 session.query(User)
2388 .join(User.addresses.of_type(a1))
2389 .join(User.addresses.of_type(a2))
2390 .filter(a1.email_address == "ed@foo.com")
2391 .filter(a2.email_address == "ed@bar.com")
2392 )
2394 **Augmenting Built-in ON Clauses**
2396 As a substitute for providing a full custom ON condition for an
2397 existing relationship, the :meth:`_orm.PropComparator.and_` function
2398 may be applied to a relationship attribute to augment additional
2399 criteria into the ON clause; the additional criteria will be combined
2400 with the default criteria using AND::
2402 q = session.query(User).join(
2403 User.addresses.and_(Address.email_address != "foo@bar.com")
2404 )
2406 .. versionadded:: 1.4
2408 **Joining to Tables and Subqueries**
2411 The target of a join may also be any table or SELECT statement,
2412 which may be related to a target entity or not. Use the
2413 appropriate ``.subquery()`` method in order to make a subquery
2414 out of a query::
2416 subq = (
2417 session.query(Address)
2418 .filter(Address.email_address == "ed@foo.com")
2419 .subquery()
2420 )
2423 q = session.query(User).join(subq, User.id == subq.c.user_id)
2425 Joining to a subquery in terms of a specific relationship and/or
2426 target entity may be achieved by linking the subquery to the
2427 entity using :func:`_orm.aliased`::
2429 subq = (
2430 session.query(Address)
2431 .filter(Address.email_address == "ed@foo.com")
2432 .subquery()
2433 )
2435 address_subq = aliased(Address, subq)
2437 q = session.query(User).join(User.addresses.of_type(address_subq))
2439 **Controlling what to Join From**
2441 In cases where the left side of the current state of
2442 :class:`_query.Query` is not in line with what we want to join from,
2443 the :meth:`_query.Query.select_from` method may be used::
2445 q = (
2446 session.query(Address)
2447 .select_from(User)
2448 .join(User.addresses)
2449 .filter(User.name == "ed")
2450 )
2452 Which will produce SQL similar to:
2454 .. sourcecode:: sql
2456 SELECT address.* FROM user
2457 JOIN address ON user.id=address.user_id
2458 WHERE user.name = :name_1
2460 .. seealso::
2462 :meth:`_sql.Select.join` - v2 equivalent method.
2464 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2465 the props collection in modern use should be considered to be a one
2466 or two argument form, either as a single "target" entity or ORM
2467 attribute-bound relationship, or as a target entity plus an "on
2468 clause" which may be a SQL expression or ORM attribute-bound
2469 relationship.
2471 :param isouter=False: If True, the join used will be a left outer join,
2472 just as if the :meth:`_query.Query.outerjoin` method were called.
2474 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2476 """
2478 join_target = coercions.expect(
2479 roles.JoinTargetRole,
2480 target,
2481 apply_propagate_attrs=self,
2482 legacy=True,
2483 )
2484 if onclause is not None:
2485 onclause_element = coercions.expect(
2486 roles.OnClauseRole, onclause, legacy=True
2487 )
2488 else:
2489 onclause_element = None
2491 self._setup_joins += (
2492 (
2493 join_target,
2494 onclause_element,
2495 None,
2496 {
2497 "isouter": isouter,
2498 "full": full,
2499 },
2500 ),
2501 )
2503 self.__dict__.pop("_last_joined_entity", None)
2504 return self
2506 def outerjoin(
2507 self,
2508 target: _JoinTargetArgument,
2509 onclause: Optional[_OnClauseArgument] = None,
2510 *,
2511 full: bool = False,
2512 ) -> Self:
2513 """Create a left outer join against this ``Query`` object's criterion
2514 and apply generatively, returning the newly resulting ``Query``.
2516 Usage is the same as the ``join()`` method.
2518 .. seealso::
2520 :meth:`_sql.Select.outerjoin` - v2 equivalent method.
2522 """
2523 return self.join(target, onclause=onclause, isouter=True, full=full)
2525 @_generative
2526 @_assertions(_no_statement_condition)
2527 def reset_joinpoint(self) -> Self:
2528 """Return a new :class:`.Query`, where the "join point" has
2529 been reset back to the base FROM entities of the query.
2531 This method is usually used in conjunction with the
2532 ``aliased=True`` feature of the :meth:`~.Query.join`
2533 method. See the example in :meth:`~.Query.join` for how
2534 this is used.
2536 """
2537 self._last_joined_entity = None
2539 return self
2541 @_generative
2542 @_assertions(_no_clauseelement_condition)
2543 def select_from(self, *from_obj: _FromClauseArgument) -> Self:
2544 r"""Set the FROM clause of this :class:`.Query` explicitly.
2546 :meth:`.Query.select_from` is often used in conjunction with
2547 :meth:`.Query.join` in order to control which entity is selected
2548 from on the "left" side of the join.
2550 The entity or selectable object here effectively replaces the
2551 "left edge" of any calls to :meth:`~.Query.join`, when no
2552 joinpoint is otherwise established - usually, the default "join
2553 point" is the leftmost entity in the :class:`~.Query` object's
2554 list of entities to be selected.
2556 A typical example::
2558 q = (
2559 session.query(Address)
2560 .select_from(User)
2561 .join(User.addresses)
2562 .filter(User.name == "ed")
2563 )
2565 Which produces SQL equivalent to:
2567 .. sourcecode:: sql
2569 SELECT address.* FROM user
2570 JOIN address ON user.id=address.user_id
2571 WHERE user.name = :name_1
2573 :param \*from_obj: collection of one or more entities to apply
2574 to the FROM clause. Entities can be mapped classes,
2575 :class:`.AliasedClass` objects, :class:`.Mapper` objects
2576 as well as core :class:`.FromClause` elements like subqueries.
2578 .. seealso::
2580 :meth:`~.Query.join`
2582 :meth:`.Query.select_entity_from`
2584 :meth:`_sql.Select.select_from` - v2 equivalent method.
2586 """
2588 self._set_select_from(from_obj, False)
2589 return self
2591 def __getitem__(self, item: Any) -> Any:
2592 return orm_util._getitem(
2593 self,
2594 item,
2595 )
2597 @_generative
2598 @_assertions(_no_statement_condition)
2599 def slice(
2600 self,
2601 start: int,
2602 stop: int,
2603 ) -> Self:
2604 """Computes the "slice" of the :class:`_query.Query` represented by
2605 the given indices and returns the resulting :class:`_query.Query`.
2607 The start and stop indices behave like the argument to Python's
2608 built-in :func:`range` function. This method provides an
2609 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
2610 query.
2612 For example, ::
2614 session.query(User).order_by(User.id).slice(1, 3)
2616 renders as
2618 .. sourcecode:: sql
2620 SELECT users.id AS users_id,
2621 users.name AS users_name
2622 FROM users ORDER BY users.id
2623 LIMIT ? OFFSET ?
2624 (2, 1)
2626 .. seealso::
2628 :meth:`_query.Query.limit`
2630 :meth:`_query.Query.offset`
2632 :meth:`_sql.Select.slice` - v2 equivalent method.
2634 """
2636 self._limit_clause, self._offset_clause = sql_util._make_slice(
2637 self._limit_clause, self._offset_clause, start, stop
2638 )
2639 return self
2641 @_generative
2642 @_assertions(_no_statement_condition)
2643 def limit(self, limit: _LimitOffsetType) -> Self:
2644 """Apply a ``LIMIT`` to the query and return the newly resulting
2645 ``Query``.
2647 .. seealso::
2649 :meth:`_sql.Select.limit` - v2 equivalent method.
2651 """
2652 self._limit_clause = sql_util._offset_or_limit_clause(limit)
2653 return self
2655 @_generative
2656 @_assertions(_no_statement_condition)
2657 def offset(self, offset: _LimitOffsetType) -> Self:
2658 """Apply an ``OFFSET`` to the query and return the newly resulting
2659 ``Query``.
2661 .. seealso::
2663 :meth:`_sql.Select.offset` - v2 equivalent method.
2664 """
2665 self._offset_clause = sql_util._offset_or_limit_clause(offset)
2666 return self
2668 @_generative
2669 @_assertions(_no_statement_condition)
2670 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:
2671 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
2672 ``Query``.
2675 .. note::
2677 The ORM-level :meth:`.distinct` call includes logic that will
2678 automatically add columns from the ORDER BY of the query to the
2679 columns clause of the SELECT statement, to satisfy the common need
2680 of the database backend that ORDER BY columns be part of the SELECT
2681 list when DISTINCT is used. These columns *are not* added to the
2682 list of columns actually fetched by the :class:`_query.Query`,
2683 however,
2684 so would not affect results. The columns are passed through when
2685 using the :attr:`_query.Query.statement` accessor, however.
2687 .. deprecated:: 2.0 This logic is deprecated and will be removed
2688 in SQLAlchemy 2.0. See :ref:`migration_20_query_distinct`
2689 for a description of this use case in 2.0.
2691 .. seealso::
2693 :meth:`_sql.Select.distinct` - v2 equivalent method.
2695 :param \*expr: optional column expressions. When present,
2696 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2697 construct.
2699 .. deprecated:: 2.1 Passing expressions to
2700 :meth:`_orm.Query.distinct` is deprecated, use
2701 :func:`_postgresql.distinct_on` instead.
2703 """
2704 if expr:
2705 warn_deprecated(
2706 "Passing expression to ``distinct`` to generate a DISTINCT "
2707 "ON clause is deprecated. Use instead the "
2708 "``postgresql.distinct_on`` function as an extension.",
2709 "2.1",
2710 )
2711 self._distinct = True
2712 self._distinct_on = self._distinct_on + tuple(
2713 coercions.expect(roles.ByOfRole, e) for e in expr
2714 )
2715 else:
2716 self._distinct = True
2717 return self
2719 @_generative
2720 def ext(self, extension: SyntaxExtension) -> Self:
2721 """Applies a SQL syntax extension to this statement.
2723 .. seealso::
2725 :ref:`examples_syntax_extensions`
2727 :func:`_mysql.limit` - DML LIMIT for MySQL
2729 :func:`_postgresql.distinct_on` - DISTINCT ON for PostgreSQL
2731 .. versionadded:: 2.1
2733 """
2735 extension = coercions.expect(roles.SyntaxExtensionRole, extension)
2736 self._syntax_extensions += (extension,)
2737 return self
2739 def all(self) -> List[_T]:
2740 """Return the results represented by this :class:`_query.Query`
2741 as a list.
2743 This results in an execution of the underlying SQL statement.
2745 .. warning:: The :class:`_query.Query` object,
2746 when asked to return either
2747 a sequence or iterator that consists of full ORM-mapped entities,
2748 will **deduplicate entries based on primary key**. See the FAQ for
2749 more details.
2751 .. seealso::
2753 :ref:`faq_query_deduplicating`
2755 .. seealso::
2757 :meth:`_engine.Result.all` - v2 comparable method.
2759 :meth:`_engine.Result.scalars` - v2 comparable method.
2760 """
2761 return self._iter().all() # type: ignore
2763 @_generative
2764 @_assertions(_no_clauseelement_condition)
2765 def from_statement(self, statement: roles.SelectStatementRole) -> Self:
2766 """Execute the given SELECT statement and return results.
2768 This method bypasses all internal statement compilation, and the
2769 statement is executed without modification.
2771 The statement is typically either a :func:`_expression.text`
2772 or :func:`_expression.select` construct, and should return the set
2773 of columns
2774 appropriate to the entity class represented by this
2775 :class:`_query.Query`.
2777 .. seealso::
2779 :meth:`_sql.Select.from_statement` - v2 comparable method.
2781 """
2782 _statement = coercions.expect(
2783 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2784 )
2785 self._statement = _statement
2786 return self
2788 def first(self) -> Optional[_T]:
2789 """Return the first result of this ``Query`` or
2790 None if the result doesn't contain any row.
2792 first() applies a limit of one within the generated SQL, so that
2793 only one primary entity row is generated on the server side
2794 (note this may consist of multiple result rows if join-loaded
2795 collections are present).
2797 Calling :meth:`_query.Query.first`
2798 results in an execution of the underlying
2799 query.
2801 .. seealso::
2803 :meth:`_query.Query.one`
2805 :meth:`_query.Query.one_or_none`
2807 :meth:`_engine.Result.first` - v2 comparable method.
2809 :meth:`_engine.Result.scalars` - v2 comparable method.
2811 """
2812 # replicates limit(1) behavior
2813 if self._statement is not None:
2814 return self._iter().first() # type: ignore
2815 else:
2816 return self.limit(1)._iter().first() # type: ignore
2818 def one_or_none(self) -> Optional[_T]:
2819 """Return at most one result or raise an exception.
2821 Returns ``None`` if the query selects
2822 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2823 if multiple object identities are returned, or if multiple
2824 rows are returned for a query that returns only scalar values
2825 as opposed to full identity-mapped entities.
2827 Calling :meth:`_query.Query.one_or_none`
2828 results in an execution of the
2829 underlying query.
2831 .. seealso::
2833 :meth:`_query.Query.first`
2835 :meth:`_query.Query.one`
2837 :meth:`_engine.Result.one_or_none` - v2 comparable method.
2839 :meth:`_engine.Result.scalar_one_or_none` - v2 comparable method.
2841 """
2842 return self._iter().one_or_none() # type: ignore
2844 def one(self) -> _T:
2845 """Return exactly one result or raise an exception.
2847 Raises :class:`_exc.NoResultFound` if the query selects no rows.
2848 Raises :class:`_exc.MultipleResultsFound` if multiple object identities
2849 are returned, or if multiple rows are returned for a query that returns
2850 only scalar values as opposed to full identity-mapped entities.
2852 Calling :meth:`.one` results in an execution of the underlying query.
2854 .. seealso::
2856 :meth:`_query.Query.first`
2858 :meth:`_query.Query.one_or_none`
2860 :meth:`_engine.Result.one` - v2 comparable method.
2862 :meth:`_engine.Result.scalar_one` - v2 comparable method.
2864 """
2865 return self._iter().one() # type: ignore
2867 def scalar(self) -> Any:
2868 """Return the first element of the first result or None
2869 if no rows present. If multiple rows are returned,
2870 raises :class:`_exc.MultipleResultsFound`.
2872 >>> session.query(Item).scalar()
2873 <Item>
2874 >>> session.query(Item.id).scalar()
2875 1
2876 >>> session.query(Item.id).filter(Item.id < 0).scalar()
2877 None
2878 >>> session.query(Item.id, Item.name).scalar()
2879 1
2880 >>> session.query(func.count(Parent.id)).scalar()
2881 20
2883 This results in an execution of the underlying query.
2885 .. seealso::
2887 :meth:`_engine.Result.scalar` - v2 comparable method.
2889 """
2890 # TODO: not sure why we can't use result.scalar() here
2891 try:
2892 ret = self.one()
2893 if not isinstance(ret, collections_abc.Sequence):
2894 return ret
2895 return ret[0]
2896 except sa_exc.NoResultFound:
2897 return None
2899 def __iter__(self) -> Iterator[_T]:
2900 result = self._iter()
2901 try:
2902 yield from result # type: ignore
2903 except GeneratorExit:
2904 # issue #8710 - direct iteration is not reusable after
2905 # an iterable block is broken, so close the result
2906 result._soft_close()
2907 raise
2909 def _iter(self) -> Union[ScalarResult[_T], Result[_T]]:
2910 # new style execution.
2911 params = self._params
2913 statement = self._statement_20()
2914 result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
2915 statement,
2916 params,
2917 execution_options={"_sa_orm_load_options": self.load_options},
2918 )
2920 # legacy: automatically set scalars, unique
2921 if result._attributes.get("is_single_entity", False):
2922 result = cast("Result[_T]", result).scalars()
2924 if (
2925 result._attributes.get("filtered", False)
2926 and not self.load_options._yield_per
2927 ):
2928 result = result.unique()
2930 return result
2932 def __str__(self) -> str:
2933 statement = self._statement_20()
2935 try:
2936 bind = (
2937 self.session.get_bind(clause=statement)
2938 if self.session
2939 else None
2940 )
2941 except sa_exc.UnboundExecutionError:
2942 bind = None
2944 return str(statement.compile(bind))
2946 @property
2947 def column_descriptions(self) -> List[ORMColumnDescription]:
2948 """Return metadata about the columns which would be
2949 returned by this :class:`_query.Query`.
2951 Format is a list of dictionaries::
2953 user_alias = aliased(User, name="user2")
2954 q = sess.query(User, User.id, user_alias)
2956 # this expression:
2957 q.column_descriptions
2959 # would return:
2960 [
2961 {
2962 "name": "User",
2963 "type": User,
2964 "aliased": False,
2965 "expr": User,
2966 "entity": User,
2967 },
2968 {
2969 "name": "id",
2970 "type": Integer(),
2971 "aliased": False,
2972 "expr": User.id,
2973 "entity": User,
2974 },
2975 {
2976 "name": "user2",
2977 "type": User,
2978 "aliased": True,
2979 "expr": user_alias,
2980 "entity": user_alias,
2981 },
2982 ]
2984 .. seealso::
2986 This API is available using :term:`2.0 style` queries as well,
2987 documented at:
2989 * :ref:`queryguide_inspection`
2991 * :attr:`.Select.column_descriptions`
2993 """
2995 return _column_descriptions(self, legacy=True)
2997 @util.deprecated(
2998 "2.0",
2999 "The :meth:`_orm.Query.instances` method is deprecated and will "
3000 "be removed in a future release. "
3001 "Use the Select.from_statement() method or aliased() construct in "
3002 "conjunction with Session.execute() instead.",
3003 )
3004 def instances(
3005 self,
3006 result_proxy: CursorResult[Any],
3007 context: Optional[QueryContext] = None,
3008 ) -> Any:
3009 """Return an ORM result given a :class:`_engine.CursorResult` and
3010 :class:`.QueryContext`.
3012 """
3013 if context is None:
3014 util.warn_deprecated(
3015 "Using the Query.instances() method without a context "
3016 "is deprecated and will be disallowed in a future release. "
3017 "Please make use of :meth:`_query.Query.from_statement` "
3018 "for linking ORM results to arbitrary select constructs.",
3019 version="1.4",
3020 )
3021 compile_state = self._compile_state(for_statement=False)
3023 context = QueryContext(
3024 compile_state,
3025 compile_state.statement,
3026 compile_state.statement,
3027 self._params,
3028 self.session,
3029 self.load_options,
3030 )
3032 result = loading.instances(result_proxy, context)
3034 # legacy: automatically set scalars, unique
3035 if result._attributes.get("is_single_entity", False):
3036 result = result.scalars() # type: ignore
3038 if result._attributes.get("filtered", False):
3039 result = result.unique()
3041 # TODO: isn't this supposed to be a list?
3042 return result
3044 @util.became_legacy_20(
3045 ":meth:`_orm.Query.merge_result`",
3046 alternative="The method is superseded by the "
3047 ":func:`_orm.merge_frozen_result` function.",
3048 enable_warnings=False, # warnings occur via loading.merge_result
3049 )
3050 def merge_result(
3051 self,
3052 iterator: Union[
3053 FrozenResult[Any], Iterable[Sequence[Any]], Iterable[object]
3054 ],
3055 load: bool = True,
3056 ) -> Union[FrozenResult[Any], Iterable[Any]]:
3057 """Merge a result into this :class:`_query.Query` object's Session.
3059 Given an iterator returned by a :class:`_query.Query`
3060 of the same structure
3061 as this one, return an identical iterator of results, with all mapped
3062 instances merged into the session using :meth:`.Session.merge`. This
3063 is an optimized method which will merge all mapped instances,
3064 preserving the structure of the result rows and unmapped columns with
3065 less method overhead than that of calling :meth:`.Session.merge`
3066 explicitly for each value.
3068 The structure of the results is determined based on the column list of
3069 this :class:`_query.Query` - if these do not correspond,
3070 unchecked errors
3071 will occur.
3073 The 'load' argument is the same as that of :meth:`.Session.merge`.
3075 For an example of how :meth:`_query.Query.merge_result` is used, see
3076 the source code for the example :ref:`examples_caching`, where
3077 :meth:`_query.Query.merge_result` is used to efficiently restore state
3078 from a cache back into a target :class:`.Session`.
3080 """
3082 return loading.merge_result(self, iterator, load)
3084 def exists(self) -> Exists:
3085 """A convenience method that turns a query into an EXISTS subquery
3086 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3088 e.g.::
3090 q = session.query(User).filter(User.name == "fred")
3091 session.query(q.exists())
3093 Producing SQL similar to:
3095 .. sourcecode:: sql
3097 SELECT EXISTS (
3098 SELECT 1 FROM users WHERE users.name = :name_1
3099 ) AS anon_1
3101 The EXISTS construct is usually used in the WHERE clause::
3103 session.query(User.id).filter(q.exists()).scalar()
3105 Note that some databases such as SQL Server don't allow an
3106 EXISTS expression to be present in the columns clause of a
3107 SELECT. To select a simple boolean value based on the exists
3108 as a WHERE, use :func:`.literal`::
3110 from sqlalchemy import literal
3112 session.query(literal(True)).filter(q.exists()).scalar()
3114 .. seealso::
3116 :meth:`_sql.Select.exists` - v2 comparable method.
3118 """
3120 # .add_columns() for the case that we are a query().select_from(X),
3121 # so that ".statement" can be produced (#2995) but also without
3122 # omitting the FROM clause from a query(X) (#2818);
3123 # .with_only_columns() after we have a core select() so that
3124 # we get just "SELECT 1" without any entities.
3126 inner = (
3127 self.enable_eagerloads(False)
3128 .add_columns(sql.literal_column("1"))
3129 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3130 ._get_select_statement_only()
3131 .with_only_columns(1)
3132 )
3134 ezero = self._entity_from_pre_ent_zero()
3135 if ezero is not None:
3136 inner = inner.select_from(ezero)
3138 return sql.exists(inner)
3140 def count(self) -> int:
3141 r"""Return a count of rows this the SQL formed by this :class:`Query`
3142 would return.
3144 This generates the SQL for this Query as follows:
3146 .. sourcecode:: sql
3148 SELECT count(1) AS count_1 FROM (
3149 SELECT <rest of query follows...>
3150 ) AS anon_1
3152 The above SQL returns a single row, which is the aggregate value
3153 of the count function; the :meth:`_query.Query.count`
3154 method then returns
3155 that single integer value.
3157 .. warning::
3159 It is important to note that the value returned by
3160 count() is **not the same as the number of ORM objects that this
3161 Query would return from a method such as the .all() method**.
3162 The :class:`_query.Query` object,
3163 when asked to return full entities,
3164 will **deduplicate entries based on primary key**, meaning if the
3165 same primary key value would appear in the results more than once,
3166 only one object of that primary key would be present. This does
3167 not apply to a query that is against individual columns.
3169 .. seealso::
3171 :ref:`faq_query_deduplicating`
3173 For fine grained control over specific columns to count, to skip the
3174 usage of a subquery or otherwise control of the FROM clause, or to use
3175 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3176 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3178 from sqlalchemy import func
3180 # count User records, without
3181 # using a subquery.
3182 session.query(func.count(User.id))
3184 # return count of user "id" grouped
3185 # by "name"
3186 session.query(func.count(User.id)).group_by(User.name)
3188 from sqlalchemy import distinct
3190 # count distinct "name" values
3191 session.query(func.count(distinct(User.name)))
3193 .. seealso::
3195 :ref:`migration_20_query_usage`
3197 """
3198 col = sql.func.count(sql.literal_column("*"))
3199 return ( # type: ignore
3200 self._legacy_from_self(col).enable_eagerloads(False).scalar()
3201 )
3203 def delete(
3204 self,
3205 synchronize_session: SynchronizeSessionArgument = "auto",
3206 delete_args: Optional[Dict[Any, Any]] = None,
3207 ) -> int:
3208 r"""Perform a DELETE with an arbitrary WHERE clause.
3210 Deletes rows matched by this query from the database.
3212 E.g.::
3214 sess.query(User).filter(User.age == 25).delete(synchronize_session=False)
3216 sess.query(User).filter(User.age == 25).delete(
3217 synchronize_session="evaluate"
3218 )
3220 .. warning::
3222 See the section :ref:`orm_expression_update_delete` for important
3223 caveats and warnings, including limitations when using bulk UPDATE
3224 and DELETE with mapper inheritance configurations.
3226 :param synchronize_session: chooses the strategy to update the
3227 attributes on objects in the session. See the section
3228 :ref:`orm_expression_update_delete` for a discussion of these
3229 strategies.
3231 :param delete_args: Optional dictionary, if present will be passed
3232 to the underlying :func:`_expression.delete` construct as the ``**kw``
3233 for the object. May be used to pass dialect-specific arguments such
3234 as ``mysql_limit``.
3236 .. versionadded:: 2.0.37
3238 :return: the count of rows matched as returned by the database's
3239 "row count" feature.
3241 .. seealso::
3243 :ref:`orm_expression_update_delete`
3245 """ # noqa: E501
3247 bulk_del = BulkDelete(self, delete_args)
3248 if self.dispatch.before_compile_delete:
3249 for fn in self.dispatch.before_compile_delete:
3250 new_query = fn(bulk_del.query, bulk_del)
3251 if new_query is not None:
3252 bulk_del.query = new_query
3254 self = bulk_del.query
3256 delete_ = sql.delete(*self._raw_columns) # type: ignore
3258 if delete_args:
3259 delete_ = delete_.with_dialect_options(**delete_args)
3261 delete_._where_criteria = self._where_criteria
3263 for ext in self._syntax_extensions:
3264 delete_._apply_syntax_extension_to_self(ext)
3266 result = cast(
3267 "CursorResult[Any]",
3268 self.session.execute(
3269 delete_,
3270 self._params,
3271 execution_options=self._execution_options.union(
3272 {"synchronize_session": synchronize_session}
3273 ),
3274 ),
3275 )
3276 bulk_del.result = result # type: ignore
3277 self.session.dispatch.after_bulk_delete(bulk_del)
3278 result.close()
3280 return result.rowcount
3282 def update(
3283 self,
3284 values: Dict[_DMLColumnArgument, Any],
3285 synchronize_session: SynchronizeSessionArgument = "auto",
3286 update_args: Optional[Dict[Any, Any]] = None,
3287 ) -> int:
3288 r"""Perform an UPDATE with an arbitrary WHERE clause.
3290 Updates rows matched by this query in the database.
3292 E.g.::
3294 sess.query(User).filter(User.age == 25).update(
3295 {User.age: User.age - 10}, synchronize_session=False
3296 )
3298 sess.query(User).filter(User.age == 25).update(
3299 {"age": User.age - 10}, synchronize_session="evaluate"
3300 )
3302 .. warning::
3304 See the section :ref:`orm_expression_update_delete` for important
3305 caveats and warnings, including limitations when using arbitrary
3306 UPDATE and DELETE with mapper inheritance configurations.
3308 :param values: a dictionary with attributes names, or alternatively
3309 mapped attributes or SQL expressions, as keys, and literal
3310 values or sql expressions as values. If :ref:`parameter-ordered
3311 mode <tutorial_parameter_ordered_updates>` is desired, the values can
3312 be passed as a list of 2-tuples; this requires that the
3313 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3314 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3315 as well.
3317 :param synchronize_session: chooses the strategy to update the
3318 attributes on objects in the session. See the section
3319 :ref:`orm_expression_update_delete` for a discussion of these
3320 strategies.
3322 :param update_args: Optional dictionary, if present will be passed
3323 to the underlying :func:`_expression.update` construct as the ``**kw``
3324 for the object. May be used to pass dialect-specific arguments such
3325 as ``mysql_limit``, as well as other special arguments such as
3326 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3328 :return: the count of rows matched as returned by the database's
3329 "row count" feature.
3332 .. seealso::
3334 :ref:`orm_expression_update_delete`
3336 """
3338 update_args = update_args or {}
3340 bulk_ud = BulkUpdate(self, values, update_args)
3342 if self.dispatch.before_compile_update:
3343 for fn in self.dispatch.before_compile_update:
3344 new_query = fn(bulk_ud.query, bulk_ud)
3345 if new_query is not None:
3346 bulk_ud.query = new_query
3347 self = bulk_ud.query
3349 upd = sql.update(*self._raw_columns) # type: ignore
3351 ppo = update_args.pop("preserve_parameter_order", False)
3352 if ppo:
3353 upd = upd.ordered_values(*values) # type: ignore
3354 else:
3355 upd = upd.values(values)
3356 if update_args:
3357 upd = upd.with_dialect_options(**update_args)
3359 upd._where_criteria = self._where_criteria
3361 for ext in self._syntax_extensions:
3362 upd._apply_syntax_extension_to_self(ext)
3364 result = cast(
3365 "CursorResult[Any]",
3366 self.session.execute(
3367 upd,
3368 self._params,
3369 execution_options=self._execution_options.union(
3370 {"synchronize_session": synchronize_session}
3371 ),
3372 ),
3373 )
3374 bulk_ud.result = result # type: ignore
3375 self.session.dispatch.after_bulk_update(bulk_ud)
3376 result.close()
3377 return result.rowcount
3379 def _compile_state(
3380 self, for_statement: bool = False, **kw: Any
3381 ) -> _ORMCompileState:
3382 """Create an out-of-compiler ORMCompileState object.
3384 The ORMCompileState object is normally created directly as a result
3385 of the SQLCompiler.process() method being handed a Select()
3386 or FromStatement() object that uses the "orm" plugin. This method
3387 provides a means of creating this ORMCompileState object directly
3388 without using the compiler.
3390 This method is used only for deprecated cases, which include
3391 the .from_self() method for a Query that has multiple levels
3392 of .from_self() in use, as well as the instances() method. It is
3393 also used within the test suite to generate ORMCompileState objects
3394 for test purposes.
3396 """
3398 stmt = self._statement_20(for_statement=for_statement, **kw)
3399 assert for_statement == stmt._compile_options._for_statement
3401 # this chooses between ORMFromStatementCompileState and
3402 # ORMSelectCompileState. We could also base this on
3403 # query._statement is not None as we have the ORM Query here
3404 # however this is the more general path.
3405 compile_state_cls = cast(
3406 _ORMCompileState,
3407 _ORMCompileState._get_plugin_class_for_plugin(stmt, "orm"),
3408 )
3410 return compile_state_cls._create_orm_context(
3411 stmt, toplevel=True, compiler=None
3412 )
3414 def _compile_context(self, for_statement: bool = False) -> QueryContext:
3415 compile_state = self._compile_state(for_statement=for_statement)
3416 context = QueryContext(
3417 compile_state,
3418 compile_state.statement,
3419 compile_state.statement,
3420 self._params,
3421 self.session,
3422 self.load_options,
3423 )
3425 return context
3428class AliasOption(interfaces.LoaderOption):
3429 inherit_cache = False
3431 @util.deprecated(
3432 "1.4",
3433 "The :class:`.AliasOption` object is not necessary "
3434 "for entities to be matched up to a query that is established "
3435 "via :meth:`.Query.from_statement` and now does nothing.",
3436 )
3437 def __init__(self, alias: Union[Alias, Subquery]):
3438 r"""Return a :class:`.MapperOption` that will indicate to the
3439 :class:`_query.Query`
3440 that the main table has been aliased.
3442 """
3444 def process_compile_state(self, compile_state: _ORMCompileState) -> None:
3445 pass
3448class BulkUD:
3449 """State used for the orm.Query version of update() / delete().
3451 This object is now specific to Query only.
3453 """
3455 def __init__(self, query: Query[Any]):
3456 self.query = query.enable_eagerloads(False)
3457 self._validate_query_state()
3458 self.mapper = self.query._entity_from_pre_ent_zero()
3460 def _validate_query_state(self) -> None:
3461 for attr, methname, notset, op in (
3462 ("_limit_clause", "limit()", None, operator.is_),
3463 ("_offset_clause", "offset()", None, operator.is_),
3464 ("_order_by_clauses", "order_by()", (), operator.eq),
3465 ("_group_by_clauses", "group_by()", (), operator.eq),
3466 ("_distinct", "distinct()", False, operator.is_),
3467 (
3468 "_from_obj",
3469 "join(), outerjoin(), select_from(), or from_self()",
3470 (),
3471 operator.eq,
3472 ),
3473 (
3474 "_setup_joins",
3475 "join(), outerjoin(), select_from(), or from_self()",
3476 (),
3477 operator.eq,
3478 ),
3479 ):
3480 if not op(getattr(self.query, attr), notset):
3481 raise sa_exc.InvalidRequestError(
3482 "Can't call Query.update() or Query.delete() "
3483 "when %s has been called" % (methname,)
3484 )
3486 @property
3487 def session(self) -> Session:
3488 return self.query.session
3491class BulkUpdate(BulkUD):
3492 """BulkUD which handles UPDATEs."""
3494 def __init__(
3495 self,
3496 query: Query[Any],
3497 values: Dict[_DMLColumnArgument, Any],
3498 update_kwargs: Optional[Dict[Any, Any]],
3499 ):
3500 super().__init__(query)
3501 self.values = values
3502 self.update_kwargs = update_kwargs
3505class BulkDelete(BulkUD):
3506 """BulkUD which handles DELETEs."""
3508 def __init__(
3509 self,
3510 query: Query[Any],
3511 delete_kwargs: Optional[Dict[Any, Any]],
3512 ):
3513 super().__init__(query)
3514 self.delete_kwargs = delete_kwargs
3517class RowReturningQuery(Query[Row[Unpack[_Ts]]]):
3518 if TYPE_CHECKING:
3520 def tuples(self) -> Query[Tuple[Unpack[_Ts]]]: # type: ignore
3521 ...