Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py: 43%
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-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""The Query class and support.
10Defines the :class:`_query.Query` class, the central
11construct used by the ORM to construct database queries.
13The :class:`_query.Query` class should not be confused with the
14:class:`_expression.Select` class, which defines database
15SELECT operations at the SQL (non-ORM) level. ``Query`` differs from
16``Select`` in that it returns ORM-mapped objects and interacts with an
17ORM session, whereas the ``Select`` construct interacts directly with the
18database to return iterable result sets.
20"""
21from __future__ import annotations
23import collections.abc as collections_abc
24import operator
25from typing import Any
26from typing import Callable
27from typing import cast
28from typing import Dict
29from typing import Generic
30from typing import Iterable
31from typing import Iterator
32from typing import List
33from typing import Mapping
34from typing import Optional
35from typing import overload
36from typing import Sequence
37from typing import Tuple
38from typing import Type
39from typing import TYPE_CHECKING
40from typing import TypeVar
41from typing import Union
43from . import attributes
44from . import interfaces
45from . import loading
46from . import util as orm_util
47from ._typing import _O
48from .base import _assertions
49from .context import _column_descriptions
50from .context import _determine_last_joined_entity
51from .context import _legacy_filter_by_entity_zero
52from .context import FromStatement
53from .context import ORMCompileState
54from .context import QueryContext
55from .interfaces import ORMColumnDescription
56from .interfaces import ORMColumnsClauseRole
57from .util import AliasedClass
58from .util import object_mapper
59from .util import with_parent
60from .. import exc as sa_exc
61from .. import inspect
62from .. import inspection
63from .. import log
64from .. import sql
65from .. import util
66from ..engine import Result
67from ..engine import Row
68from ..event import dispatcher
69from ..event import EventTarget
70from ..sql import coercions
71from ..sql import expression
72from ..sql import roles
73from ..sql import Select
74from ..sql import util as sql_util
75from ..sql import visitors
76from ..sql._typing import _FromClauseArgument
77from ..sql.annotation import SupportsCloneAnnotations
78from ..sql.base import _entity_namespace_key
79from ..sql.base import _generative
80from ..sql.base import _NoArg
81from ..sql.base import Executable
82from ..sql.base import Generative
83from ..sql.elements import BooleanClauseList
84from ..sql.expression import Exists
85from ..sql.selectable import _MemoizedSelectEntities
86from ..sql.selectable import _SelectFromElements
87from ..sql.selectable import ForUpdateArg
88from ..sql.selectable import HasHints
89from ..sql.selectable import HasPrefixes
90from ..sql.selectable import HasSuffixes
91from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL
92from ..sql.selectable import SelectLabelStyle
93from ..util import deprecated
94from ..util.typing import Literal
95from ..util.typing import Self
96from ..util.typing import TupleAny
97from ..util.typing import TypeVarTuple
98from ..util.typing import Unpack
101if TYPE_CHECKING:
102 from ._typing import _EntityType
103 from ._typing import _ExternalEntityType
104 from ._typing import _InternalEntityType
105 from ._typing import SynchronizeSessionArgument
106 from .mapper import Mapper
107 from .path_registry import PathRegistry
108 from .session import _PKIdentityArgument
109 from .session import Session
110 from .state import InstanceState
111 from ..engine.cursor import CursorResult
112 from ..engine.interfaces import _ImmutableExecuteOptions
113 from ..engine.interfaces import CompiledCacheType
114 from ..engine.interfaces import IsolationLevel
115 from ..engine.interfaces import SchemaTranslateMapType
116 from ..engine.result import FrozenResult
117 from ..engine.result import ScalarResult
118 from ..sql._typing import _ColumnExpressionArgument
119 from ..sql._typing import _ColumnExpressionOrStrLabelArgument
120 from ..sql._typing import _ColumnsClauseArgument
121 from ..sql._typing import _DMLColumnArgument
122 from ..sql._typing import _JoinTargetArgument
123 from ..sql._typing import _LimitOffsetType
124 from ..sql._typing import _MAYBE_ENTITY
125 from ..sql._typing import _no_kw
126 from ..sql._typing import _NOT_ENTITY
127 from ..sql._typing import _OnClauseArgument
128 from ..sql._typing import _PropagateAttrsType
129 from ..sql._typing import _T0
130 from ..sql._typing import _T1
131 from ..sql._typing import _T2
132 from ..sql._typing import _T3
133 from ..sql._typing import _T4
134 from ..sql._typing import _T5
135 from ..sql._typing import _T6
136 from ..sql._typing import _T7
137 from ..sql._typing import _TypedColumnClauseArgument as _TCCA
138 from ..sql.base import CacheableOptions
139 from ..sql.base import ExecutableOption
140 from ..sql.elements import ColumnElement
141 from ..sql.elements import Label
142 from ..sql.selectable import _ForUpdateOfArgument
143 from ..sql.selectable import _JoinTargetElement
144 from ..sql.selectable import _SetupJoinsElement
145 from ..sql.selectable import Alias
146 from ..sql.selectable import CTE
147 from ..sql.selectable import ExecutableReturnsRows
148 from ..sql.selectable import FromClause
149 from ..sql.selectable import ScalarSelect
150 from ..sql.selectable import Subquery
153__all__ = ["Query", "QueryContext"]
155_T = TypeVar("_T", bound=Any)
156_Ts = TypeVarTuple("_Ts")
159@inspection._self_inspects
160@log.class_logger
161class Query(
162 _SelectFromElements,
163 SupportsCloneAnnotations,
164 HasPrefixes,
165 HasSuffixes,
166 HasHints,
167 EventTarget,
168 log.Identified,
169 Generative,
170 Executable,
171 Generic[_T],
172):
173 """ORM-level SQL construction object.
175 .. legacy:: The ORM :class:`.Query` object is a legacy construct
176 as of SQLAlchemy 2.0. See the notes at the top of
177 :ref:`query_api_toplevel` for an overview, including links to migration
178 documentation.
180 :class:`_query.Query` objects are normally initially generated using the
181 :meth:`~.Session.query` method of :class:`.Session`, and in
182 less common cases by instantiating the :class:`_query.Query` directly and
183 associating with a :class:`.Session` using the
184 :meth:`_query.Query.with_session`
185 method.
187 """
189 # elements that are in Core and can be cached in the same way
190 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
191 _having_criteria: Tuple[ColumnElement[Any], ...] = ()
193 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()
194 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()
195 _limit_clause: Optional[ColumnElement[Any]] = None
196 _offset_clause: Optional[ColumnElement[Any]] = None
198 _distinct: bool = False
199 _distinct_on: Tuple[ColumnElement[Any], ...] = ()
201 _for_update_arg: Optional[ForUpdateArg] = None
202 _correlate: Tuple[FromClause, ...] = ()
203 _auto_correlate: bool = True
204 _from_obj: Tuple[FromClause, ...] = ()
205 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()
207 _label_style: SelectLabelStyle = SelectLabelStyle.LABEL_STYLE_LEGACY_ORM
209 _memoized_select_entities = ()
211 _compile_options: Union[Type[CacheableOptions], CacheableOptions] = (
212 ORMCompileState.default_compile_options
213 )
215 _with_options: Tuple[ExecutableOption, ...]
216 load_options = QueryContext.default_load_options + {
217 "_legacy_uniquing": True
218 }
220 _params: util.immutabledict[str, Any] = util.EMPTY_DICT
222 # local Query builder state, not needed for
223 # compilation or execution
224 _enable_assertions = True
226 _statement: Optional[ExecutableReturnsRows] = None
228 session: Session
230 dispatch: dispatcher[Query[_T]]
232 # mirrors that of ClauseElement, used to propagate the "orm"
233 # plugin as well as the "subject" of the plugin, e.g. the mapper
234 # we are querying against.
235 @util.memoized_property
236 def _propagate_attrs(self) -> _PropagateAttrsType:
237 return util.EMPTY_DICT
239 def __init__(
240 self,
241 entities: Union[
242 _ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]
243 ],
244 session: Optional[Session] = None,
245 ):
246 """Construct a :class:`_query.Query` directly.
248 E.g.::
250 q = Query([User, Address], session=some_session)
252 The above is equivalent to::
254 q = some_session.query(User, Address)
256 :param entities: a sequence of entities and/or SQL expressions.
258 :param session: a :class:`.Session` with which the
259 :class:`_query.Query`
260 will be associated. Optional; a :class:`_query.Query`
261 can be associated
262 with a :class:`.Session` generatively via the
263 :meth:`_query.Query.with_session` method as well.
265 .. seealso::
267 :meth:`.Session.query`
269 :meth:`_query.Query.with_session`
271 """
273 # session is usually present. There's one case in subqueryloader
274 # where it stores a Query without a Session and also there are tests
275 # for the query(Entity).with_session(session) API which is likely in
276 # some old recipes, however these are legacy as select() can now be
277 # used.
278 self.session = session # type: ignore
279 self._set_entities(entities)
281 def _set_propagate_attrs(self, values: Mapping[str, Any]) -> Self:
282 self._propagate_attrs = util.immutabledict(values)
283 return self
285 def _set_entities(
286 self,
287 entities: Union[
288 _ColumnsClauseArgument[Any], Iterable[_ColumnsClauseArgument[Any]]
289 ],
290 ) -> None:
291 self._raw_columns = [
292 coercions.expect(
293 roles.ColumnsClauseRole,
294 ent,
295 apply_propagate_attrs=self,
296 post_inspect=True,
297 )
298 for ent in util.to_list(entities)
299 ]
301 @deprecated(
302 "2.1.0",
303 "The :meth:`.Query.tuples` method is deprecated, :class:`.Row` "
304 "now behaves like a tuple and can unpack types directly.",
305 )
306 def tuples(self: Query[_O]) -> Query[Tuple[_O]]:
307 """return a tuple-typed form of this :class:`.Query`.
309 This method invokes the :meth:`.Query.only_return_tuples`
310 method with a value of ``True``, which by itself ensures that this
311 :class:`.Query` will always return :class:`.Row` objects, even
312 if the query is made against a single entity. It then also
313 at the typing level will return a "typed" query, if possible,
314 that will type result rows as ``Tuple`` objects with typed
315 elements.
317 This method can be compared to the :meth:`.Result.tuples` method,
318 which returns "self", but from a typing perspective returns an object
319 that will yield typed ``Tuple`` objects for results. Typing
320 takes effect only if this :class:`.Query` object is a typed
321 query object already.
323 .. versionadded:: 2.0
325 .. seealso::
327 :ref:`change_10635` - describes a migration path from this
328 workaround for SQLAlchemy 2.1.
330 :meth:`.Result.tuples` - v2 equivalent method.
332 """
333 return self.only_return_tuples(True) # type: ignore
335 def _entity_from_pre_ent_zero(self) -> Optional[_InternalEntityType[Any]]:
336 if not self._raw_columns:
337 return None
339 ent = self._raw_columns[0]
341 if "parententity" in ent._annotations:
342 return ent._annotations["parententity"] # type: ignore
343 elif "bundle" in ent._annotations:
344 return ent._annotations["bundle"] # type: ignore
345 else:
346 # label, other SQL expression
347 for element in visitors.iterate(ent):
348 if "parententity" in element._annotations:
349 return element._annotations["parententity"] # type: ignore # noqa: E501
350 else:
351 return None
353 def _only_full_mapper_zero(self, methname: str) -> Mapper[Any]:
354 if (
355 len(self._raw_columns) != 1
356 or "parententity" not in self._raw_columns[0]._annotations
357 or not self._raw_columns[0].is_selectable
358 ):
359 raise sa_exc.InvalidRequestError(
360 "%s() can only be used against "
361 "a single mapped class." % methname
362 )
364 return self._raw_columns[0]._annotations["parententity"] # type: ignore # noqa: E501
366 def _set_select_from(
367 self, obj: Iterable[_FromClauseArgument], set_base_alias: bool
368 ) -> None:
369 fa = [
370 coercions.expect(
371 roles.StrictFromClauseRole,
372 elem,
373 allow_select=True,
374 apply_propagate_attrs=self,
375 )
376 for elem in obj
377 ]
379 self._compile_options += {"_set_base_alias": set_base_alias}
380 self._from_obj = tuple(fa)
382 @_generative
383 def _set_lazyload_from(self, state: InstanceState[Any]) -> Self:
384 self.load_options += {"_lazy_loaded_from": state}
385 return self
387 def _get_condition(self) -> None:
388 """used by legacy BakedQuery"""
389 self._no_criterion_condition("get", order_by=False, distinct=False)
391 def _get_existing_condition(self) -> None:
392 self._no_criterion_assertion("get", order_by=False, distinct=False)
394 def _no_criterion_assertion(
395 self, meth: str, order_by: bool = True, distinct: bool = True
396 ) -> None:
397 if not self._enable_assertions:
398 return
399 if (
400 self._where_criteria
401 or self._statement is not None
402 or self._from_obj
403 or self._setup_joins
404 or self._limit_clause is not None
405 or self._offset_clause is not None
406 or self._group_by_clauses
407 or (order_by and self._order_by_clauses)
408 or (distinct and self._distinct)
409 ):
410 raise sa_exc.InvalidRequestError(
411 "Query.%s() being called on a "
412 "Query with existing criterion. " % meth
413 )
415 def _no_criterion_condition(
416 self, meth: str, order_by: bool = True, distinct: bool = True
417 ) -> None:
418 self._no_criterion_assertion(meth, order_by, distinct)
420 self._from_obj = self._setup_joins = ()
421 if self._statement is not None:
422 self._compile_options += {"_statement": None}
423 self._where_criteria = ()
424 self._distinct = False
426 self._order_by_clauses = self._group_by_clauses = ()
428 def _no_clauseelement_condition(self, meth: str) -> None:
429 if not self._enable_assertions:
430 return
431 if self._order_by_clauses:
432 raise sa_exc.InvalidRequestError(
433 "Query.%s() being called on a "
434 "Query with existing criterion. " % meth
435 )
436 self._no_criterion_condition(meth)
438 def _no_statement_condition(self, meth: str) -> None:
439 if not self._enable_assertions:
440 return
441 if self._statement is not None:
442 raise sa_exc.InvalidRequestError(
443 (
444 "Query.%s() being called on a Query with an existing full "
445 "statement - can't apply criterion."
446 )
447 % meth
448 )
450 def _no_limit_offset(self, meth: str) -> None:
451 if not self._enable_assertions:
452 return
453 if self._limit_clause is not None or self._offset_clause is not None:
454 raise sa_exc.InvalidRequestError(
455 "Query.%s() being called on a Query which already has LIMIT "
456 "or OFFSET applied. Call %s() before limit() or offset() "
457 "are applied." % (meth, meth)
458 )
460 @property
461 def _has_row_limiting_clause(self) -> bool:
462 return (
463 self._limit_clause is not None or self._offset_clause is not None
464 )
466 def _get_options(
467 self,
468 populate_existing: Optional[bool] = None,
469 version_check: Optional[bool] = None,
470 only_load_props: Optional[Sequence[str]] = None,
471 refresh_state: Optional[InstanceState[Any]] = None,
472 identity_token: Optional[Any] = None,
473 ) -> Self:
474 load_options: Dict[str, Any] = {}
475 compile_options: Dict[str, Any] = {}
477 if version_check:
478 load_options["_version_check"] = version_check
479 if populate_existing:
480 load_options["_populate_existing"] = populate_existing
481 if refresh_state:
482 load_options["_refresh_state"] = refresh_state
483 compile_options["_for_refresh_state"] = True
484 if only_load_props:
485 compile_options["_only_load_props"] = frozenset(only_load_props)
486 if identity_token:
487 load_options["_identity_token"] = identity_token
489 if load_options:
490 self.load_options += load_options
491 if compile_options:
492 self._compile_options += compile_options
494 return self
496 def _clone(self, **kw: Any) -> Self:
497 return self._generate()
499 def _get_select_statement_only(self) -> Select[_T]:
500 if self._statement is not None:
501 raise sa_exc.InvalidRequestError(
502 "Can't call this method on a Query that uses from_statement()"
503 )
504 return cast("Select[_T]", self.statement)
506 @property
507 def statement(self) -> Union[Select[_T], FromStatement[_T]]:
508 """The full SELECT statement represented by this Query.
510 The statement by default will not have disambiguating labels
511 applied to the construct unless with_labels(True) is called
512 first.
514 """
516 # .statement can return the direct future.Select() construct here, as
517 # long as we are not using subsequent adaption features that
518 # are made against raw entities, e.g. from_self(), with_polymorphic(),
519 # select_entity_from(). If these features are being used, then
520 # the Select() we return will not have the correct .selected_columns
521 # collection and will not embed in subsequent queries correctly.
522 # We could find a way to make this collection "correct", however
523 # this would not be too different from doing the full compile as
524 # we are doing in any case, the Select() would still not have the
525 # proper state for other attributes like whereclause, order_by,
526 # and these features are all deprecated in any case.
527 #
528 # for these reasons, Query is not a Select, it remains an ORM
529 # object for which __clause_element__() must be called in order for
530 # it to provide a real expression object.
531 #
532 # from there, it starts to look much like Query itself won't be
533 # passed into the execute process and won't generate its own cache
534 # key; this will all occur in terms of the ORM-enabled Select.
535 if not self._compile_options._set_base_alias:
536 # if we don't have legacy top level aliasing features in use
537 # then convert to a future select() directly
538 stmt = self._statement_20(for_statement=True)
539 else:
540 stmt = self._compile_state(for_statement=True).statement
542 if self._params:
543 stmt = stmt.params(self._params)
545 return stmt
547 def _final_statement(
548 self, legacy_query_style: bool = True
549 ) -> Select[Unpack[TupleAny]]:
550 """Return the 'final' SELECT statement for this :class:`.Query`.
552 This is used by the testing suite only and is fairly inefficient.
554 This is the Core-only select() that will be rendered by a complete
555 compilation of this query, and is what .statement used to return
556 in 1.3.
559 """
561 q = self._clone()
563 return q._compile_state(
564 use_legacy_query_style=legacy_query_style
565 ).statement # type: ignore
567 def _statement_20(
568 self, for_statement: bool = False, use_legacy_query_style: bool = True
569 ) -> Union[Select[_T], FromStatement[_T]]:
570 # TODO: this event needs to be deprecated, as it currently applies
571 # only to ORM query and occurs at this spot that is now more
572 # or less an artificial spot
573 if self.dispatch.before_compile:
574 for fn in self.dispatch.before_compile:
575 new_query = fn(self)
576 if new_query is not None and new_query is not self:
577 self = new_query
578 if not fn._bake_ok: # type: ignore
579 self._compile_options += {"_bake_ok": False}
581 compile_options = self._compile_options
582 compile_options += {
583 "_for_statement": for_statement,
584 "_use_legacy_query_style": use_legacy_query_style,
585 }
587 stmt: Union[Select[_T], FromStatement[_T]]
589 if self._statement is not None:
590 stmt = FromStatement(self._raw_columns, self._statement)
591 stmt.__dict__.update(
592 _with_options=self._with_options,
593 _with_context_options=self._with_context_options,
594 _compile_options=compile_options,
595 _execution_options=self._execution_options,
596 _propagate_attrs=self._propagate_attrs,
597 )
598 else:
599 # Query / select() internal attributes are 99% cross-compatible
600 stmt = Select._create_raw_select(**self.__dict__)
601 stmt.__dict__.update(
602 _label_style=self._label_style,
603 _compile_options=compile_options,
604 _propagate_attrs=self._propagate_attrs,
605 )
606 stmt.__dict__.pop("session", None)
608 # ensure the ORM context is used to compile the statement, even
609 # if it has no ORM entities. This is so ORM-only things like
610 # _legacy_joins are picked up that wouldn't be picked up by the
611 # Core statement context
612 if "compile_state_plugin" not in stmt._propagate_attrs:
613 stmt._propagate_attrs = stmt._propagate_attrs.union(
614 {"compile_state_plugin": "orm", "plugin_subject": None}
615 )
617 return stmt
619 def subquery(
620 self,
621 name: Optional[str] = None,
622 with_labels: bool = False,
623 reduce_columns: bool = False,
624 ) -> Subquery:
625 """Return the full SELECT statement represented by
626 this :class:`_query.Query`, embedded within an
627 :class:`_expression.Alias`.
629 Eager JOIN generation within the query is disabled.
631 .. seealso::
633 :meth:`_sql.Select.subquery` - v2 comparable method.
635 :param name: string name to be assigned as the alias;
636 this is passed through to :meth:`_expression.FromClause.alias`.
637 If ``None``, a name will be deterministically generated
638 at compile time.
640 :param with_labels: if True, :meth:`.with_labels` will be called
641 on the :class:`_query.Query` first to apply table-qualified labels
642 to all columns.
644 :param reduce_columns: if True,
645 :meth:`_expression.Select.reduce_columns` will
646 be called on the resulting :func:`_expression.select` construct,
647 to remove same-named columns where one also refers to the other
648 via foreign key or WHERE clause equivalence.
650 """
651 q = self.enable_eagerloads(False)
652 if with_labels:
653 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
655 stmt = q._get_select_statement_only()
657 if TYPE_CHECKING:
658 assert isinstance(stmt, Select)
660 if reduce_columns:
661 stmt = stmt.reduce_columns()
662 return stmt.subquery(name=name)
664 def cte(
665 self,
666 name: Optional[str] = None,
667 recursive: bool = False,
668 nesting: bool = False,
669 ) -> CTE:
670 r"""Return the full SELECT statement represented by this
671 :class:`_query.Query` represented as a common table expression (CTE).
673 Parameters and usage are the same as those of the
674 :meth:`_expression.SelectBase.cte` method; see that method for
675 further details.
677 Here is the `PostgreSQL WITH
678 RECURSIVE example
679 <https://www.postgresql.org/docs/current/static/queries-with.html>`_.
680 Note that, in this example, the ``included_parts`` cte and the
681 ``incl_alias`` alias of it are Core selectables, which
682 means the columns are accessed via the ``.c.`` attribute. The
683 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
684 ``Part`` entity, so column-mapped attributes are available
685 directly::
687 from sqlalchemy.orm import aliased
689 class Part(Base):
690 __tablename__ = 'part'
691 part = Column(String, primary_key=True)
692 sub_part = Column(String, primary_key=True)
693 quantity = Column(Integer)
695 included_parts = session.query(
696 Part.sub_part,
697 Part.part,
698 Part.quantity).\
699 filter(Part.part=="our part").\
700 cte(name="included_parts", recursive=True)
702 incl_alias = aliased(included_parts, name="pr")
703 parts_alias = aliased(Part, name="p")
704 included_parts = included_parts.union_all(
705 session.query(
706 parts_alias.sub_part,
707 parts_alias.part,
708 parts_alias.quantity).\
709 filter(parts_alias.part==incl_alias.c.sub_part)
710 )
712 q = session.query(
713 included_parts.c.sub_part,
714 func.sum(included_parts.c.quantity).
715 label('total_quantity')
716 ).\
717 group_by(included_parts.c.sub_part)
719 .. seealso::
721 :meth:`_sql.Select.cte` - v2 equivalent method.
723 """
724 return (
725 self.enable_eagerloads(False)
726 ._get_select_statement_only()
727 .cte(name=name, recursive=recursive, nesting=nesting)
728 )
730 def label(self, name: Optional[str]) -> Label[Any]:
731 """Return the full SELECT statement represented by this
732 :class:`_query.Query`, converted
733 to a scalar subquery with a label of the given name.
735 .. seealso::
737 :meth:`_sql.Select.label` - v2 comparable method.
739 """
741 return (
742 self.enable_eagerloads(False)
743 ._get_select_statement_only()
744 .label(name)
745 )
747 @overload
748 def as_scalar(
749 self: Query[Tuple[_MAYBE_ENTITY]],
750 ) -> ScalarSelect[_MAYBE_ENTITY]: ...
752 @overload
753 def as_scalar(
754 self: Query[Tuple[_NOT_ENTITY]],
755 ) -> ScalarSelect[_NOT_ENTITY]: ...
757 @overload
758 def as_scalar(self) -> ScalarSelect[Any]: ...
760 @util.deprecated(
761 "1.4",
762 "The :meth:`_query.Query.as_scalar` method is deprecated and will be "
763 "removed in a future release. Please refer to "
764 ":meth:`_query.Query.scalar_subquery`.",
765 )
766 def as_scalar(self) -> ScalarSelect[Any]:
767 """Return the full SELECT statement represented by this
768 :class:`_query.Query`, converted to a scalar subquery.
770 """
771 return self.scalar_subquery()
773 @overload
774 def scalar_subquery(
775 self: Query[Tuple[_MAYBE_ENTITY]],
776 ) -> ScalarSelect[Any]: ...
778 @overload
779 def scalar_subquery(
780 self: Query[Tuple[_NOT_ENTITY]],
781 ) -> ScalarSelect[_NOT_ENTITY]: ...
783 @overload
784 def scalar_subquery(self) -> ScalarSelect[Any]: ...
786 def scalar_subquery(self) -> ScalarSelect[Any]:
787 """Return the full SELECT statement represented by this
788 :class:`_query.Query`, converted to a scalar subquery.
790 Analogous to
791 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
793 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
794 method replaces the :meth:`_query.Query.as_scalar` method.
796 .. seealso::
798 :meth:`_sql.Select.scalar_subquery` - v2 comparable method.
800 """
802 return (
803 self.enable_eagerloads(False)
804 ._get_select_statement_only()
805 .scalar_subquery()
806 )
808 @property
809 def selectable(self) -> Union[Select[_T], FromStatement[_T]]:
810 """Return the :class:`_expression.Select` object emitted by this
811 :class:`_query.Query`.
813 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
815 query.enable_eagerloads(False).with_labels().statement
817 """
818 return self.__clause_element__()
820 def __clause_element__(self) -> Union[Select[_T], FromStatement[_T]]:
821 return (
822 self._with_compile_options(
823 _enable_eagerloads=False, _render_for_subquery=True
824 )
825 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
826 .statement
827 )
829 @overload
830 def only_return_tuples(
831 self: Query[_O], value: Literal[True]
832 ) -> RowReturningQuery[_O]: ...
834 @overload
835 def only_return_tuples(
836 self: Query[_O], value: Literal[False]
837 ) -> Query[_O]: ...
839 @_generative
840 def only_return_tuples(self, value: bool) -> Query[Any]:
841 """When set to True, the query results will always be a
842 :class:`.Row` object.
844 This can change a query that normally returns a single entity
845 as a scalar to return a :class:`.Row` result in all cases.
847 .. seealso::
849 :meth:`.Query.tuples` - returns tuples, but also at the typing
850 level will type results as ``Tuple``.
852 :meth:`_query.Query.is_single_entity`
854 :meth:`_engine.Result.tuples` - v2 comparable method.
856 """
857 self.load_options += dict(_only_return_tuples=value)
858 return self
860 @property
861 def is_single_entity(self) -> bool:
862 """Indicates if this :class:`_query.Query`
863 returns tuples or single entities.
865 Returns True if this query returns a single entity for each instance
866 in its result list, and False if this query returns a tuple of entities
867 for each result.
869 .. versionadded:: 1.3.11
871 .. seealso::
873 :meth:`_query.Query.only_return_tuples`
875 """
876 return (
877 not self.load_options._only_return_tuples
878 and len(self._raw_columns) == 1
879 and "parententity" in self._raw_columns[0]._annotations
880 and isinstance(
881 self._raw_columns[0]._annotations["parententity"],
882 ORMColumnsClauseRole,
883 )
884 )
886 @_generative
887 def enable_eagerloads(self, value: bool) -> Self:
888 """Control whether or not eager joins and subqueries are
889 rendered.
891 When set to False, the returned Query will not render
892 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
893 :func:`~sqlalchemy.orm.subqueryload` options
894 or mapper-level ``lazy='joined'``/``lazy='subquery'``
895 configurations.
897 This is used primarily when nesting the Query's
898 statement into a subquery or other
899 selectable, or when using :meth:`_query.Query.yield_per`.
901 """
902 self._compile_options += {"_enable_eagerloads": value}
903 return self
905 @_generative
906 def _with_compile_options(self, **opt: Any) -> Self:
907 self._compile_options += opt
908 return self
910 @util.became_legacy_20(
911 ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`",
912 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
913 "instead.",
914 )
915 def with_labels(self) -> Self:
916 return self.set_label_style(
917 SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL
918 )
920 apply_labels = with_labels
922 @property
923 def get_label_style(self) -> SelectLabelStyle:
924 """
925 Retrieve the current label style.
927 .. versionadded:: 1.4
929 .. seealso::
931 :meth:`_sql.Select.get_label_style` - v2 equivalent method.
933 """
934 return self._label_style
936 def set_label_style(self, style: SelectLabelStyle) -> Self:
937 """Apply column labels to the return value of Query.statement.
939 Indicates that this Query's `statement` accessor should return
940 a SELECT statement that applies labels to all columns in the
941 form <tablename>_<columnname>; this is commonly used to
942 disambiguate columns from multiple tables which have the same
943 name.
945 When the `Query` actually issues SQL to load rows, it always
946 uses column labeling.
948 .. note:: The :meth:`_query.Query.set_label_style` method *only* applies
949 the output of :attr:`_query.Query.statement`, and *not* to any of
950 the result-row invoking systems of :class:`_query.Query` itself,
951 e.g.
952 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
953 To execute
954 a query using :meth:`_query.Query.set_label_style`, invoke the
955 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
957 result = session.execute(
958 query
959 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
960 .statement
961 )
963 .. versionadded:: 1.4
966 .. seealso::
968 :meth:`_sql.Select.set_label_style` - v2 equivalent method.
970 """ # noqa
971 if self._label_style is not style:
972 self = self._generate()
973 self._label_style = style
974 return self
976 @_generative
977 def enable_assertions(self, value: bool) -> Self:
978 """Control whether assertions are generated.
980 When set to False, the returned Query will
981 not assert its state before certain operations,
982 including that LIMIT/OFFSET has not been applied
983 when filter() is called, no criterion exists
984 when get() is called, and no "from_statement()"
985 exists when filter()/order_by()/group_by() etc.
986 is called. This more permissive mode is used by
987 custom Query subclasses to specify criterion or
988 other modifiers outside of the usual usage patterns.
990 Care should be taken to ensure that the usage
991 pattern is even possible. A statement applied
992 by from_statement() will override any criterion
993 set by filter() or order_by(), for example.
995 """
996 self._enable_assertions = value
997 return self
999 @property
1000 def whereclause(self) -> Optional[ColumnElement[bool]]:
1001 """A readonly attribute which returns the current WHERE criterion for
1002 this Query.
1004 This returned value is a SQL expression construct, or ``None`` if no
1005 criterion has been established.
1007 .. seealso::
1009 :attr:`_sql.Select.whereclause` - v2 equivalent property.
1011 """
1012 return BooleanClauseList._construct_for_whereclause(
1013 self._where_criteria
1014 )
1016 @_generative
1017 def _with_current_path(self, path: PathRegistry) -> Self:
1018 """indicate that this query applies to objects loaded
1019 within a certain path.
1021 Used by deferred loaders (see strategies.py) which transfer
1022 query options from an originating query to a newly generated
1023 query intended for the deferred load.
1025 """
1026 self._compile_options += {"_current_path": path}
1027 return self
1029 @_generative
1030 def yield_per(self, count: int) -> Self:
1031 r"""Yield only ``count`` rows at a time.
1033 The purpose of this method is when fetching very large result sets
1034 (> 10K rows), to batch results in sub-collections and yield them
1035 out partially, so that the Python interpreter doesn't need to declare
1036 very large areas of memory which is both time consuming and leads
1037 to excessive memory use. The performance from fetching hundreds of
1038 thousands of rows can often double when a suitable yield-per setting
1039 (e.g. approximately 1000) is used, even with DBAPIs that buffer
1040 rows (which are most).
1042 As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
1043 equivalent to using the ``yield_per`` execution option at the ORM
1044 level. See the section :ref:`orm_queryguide_yield_per` for further
1045 background on this option.
1047 .. seealso::
1049 :ref:`orm_queryguide_yield_per`
1051 """
1052 self.load_options += {"_yield_per": count}
1053 return self
1055 @util.became_legacy_20(
1056 ":meth:`_orm.Query.get`",
1057 alternative="The method is now available as :meth:`_orm.Session.get`",
1058 )
1059 def get(self, ident: _PKIdentityArgument) -> Optional[Any]:
1060 """Return an instance based on the given primary key identifier,
1061 or ``None`` if not found.
1063 E.g.::
1065 my_user = session.query(User).get(5)
1067 some_object = session.query(VersionedFoo).get((5, 10))
1069 some_object = session.query(VersionedFoo).get(
1070 {"id": 5, "version_id": 10})
1072 :meth:`_query.Query.get` is special in that it provides direct
1073 access to the identity map of the owning :class:`.Session`.
1074 If the given primary key identifier is present
1075 in the local identity map, the object is returned
1076 directly from this collection and no SQL is emitted,
1077 unless the object has been marked fully expired.
1078 If not present,
1079 a SELECT is performed in order to locate the object.
1081 :meth:`_query.Query.get` also will perform a check if
1082 the object is present in the identity map and
1083 marked as expired - a SELECT
1084 is emitted to refresh the object as well as to
1085 ensure that the row is still present.
1086 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
1088 :meth:`_query.Query.get` is only used to return a single
1089 mapped instance, not multiple instances or
1090 individual column constructs, and strictly
1091 on a single primary key value. The originating
1092 :class:`_query.Query` must be constructed in this way,
1093 i.e. against a single mapped entity,
1094 with no additional filtering criterion. Loading
1095 options via :meth:`_query.Query.options` may be applied
1096 however, and will be used if the object is not
1097 yet locally present.
1099 :param ident: A scalar, tuple, or dictionary representing the
1100 primary key. For a composite (e.g. multiple column) primary key,
1101 a tuple or dictionary should be passed.
1103 For a single-column primary key, the scalar calling form is typically
1104 the most expedient. If the primary key of a row is the value "5",
1105 the call looks like::
1107 my_object = query.get(5)
1109 The tuple form contains primary key values typically in
1110 the order in which they correspond to the mapped
1111 :class:`_schema.Table`
1112 object's primary key columns, or if the
1113 :paramref:`_orm.Mapper.primary_key` configuration parameter were
1114 used, in
1115 the order used for that parameter. For example, if the primary key
1116 of a row is represented by the integer
1117 digits "5, 10" the call would look like::
1119 my_object = query.get((5, 10))
1121 The dictionary form should include as keys the mapped attribute names
1122 corresponding to each element of the primary key. If the mapped class
1123 has the attributes ``id``, ``version_id`` as the attributes which
1124 store the object's primary key value, the call would look like::
1126 my_object = query.get({"id": 5, "version_id": 10})
1128 .. versionadded:: 1.3 the :meth:`_query.Query.get`
1129 method now optionally
1130 accepts a dictionary of attribute names to values in order to
1131 indicate a primary key identifier.
1134 :return: The object instance, or ``None``.
1136 """
1137 self._no_criterion_assertion("get", order_by=False, distinct=False)
1139 # we still implement _get_impl() so that baked query can override
1140 # it
1141 return self._get_impl(ident, loading.load_on_pk_identity)
1143 def _get_impl(
1144 self,
1145 primary_key_identity: _PKIdentityArgument,
1146 db_load_fn: Callable[..., Any],
1147 identity_token: Optional[Any] = None,
1148 ) -> Optional[Any]:
1149 mapper = self._only_full_mapper_zero("get")
1150 return self.session._get_impl(
1151 mapper,
1152 primary_key_identity,
1153 db_load_fn,
1154 populate_existing=self.load_options._populate_existing,
1155 with_for_update=self._for_update_arg,
1156 options=self._with_options,
1157 identity_token=identity_token,
1158 execution_options=self._execution_options,
1159 )
1161 @property
1162 def lazy_loaded_from(self) -> Optional[InstanceState[Any]]:
1163 """An :class:`.InstanceState` that is using this :class:`_query.Query`
1164 for a lazy load operation.
1166 .. deprecated:: 1.4 This attribute should be viewed via the
1167 :attr:`.ORMExecuteState.lazy_loaded_from` attribute, within
1168 the context of the :meth:`.SessionEvents.do_orm_execute`
1169 event.
1171 .. seealso::
1173 :attr:`.ORMExecuteState.lazy_loaded_from`
1175 """
1176 return self.load_options._lazy_loaded_from # type: ignore
1178 @property
1179 def _current_path(self) -> PathRegistry:
1180 return self._compile_options._current_path # type: ignore
1182 @_generative
1183 def correlate(
1184 self,
1185 *fromclauses: Union[Literal[None, False], _FromClauseArgument],
1186 ) -> Self:
1187 """Return a :class:`.Query` construct which will correlate the given
1188 FROM clauses to that of an enclosing :class:`.Query` or
1189 :func:`~.expression.select`.
1191 The method here accepts mapped classes, :func:`.aliased` constructs,
1192 and :class:`_orm.Mapper` constructs as arguments, which are resolved
1193 into expression constructs, in addition to appropriate expression
1194 constructs.
1196 The correlation arguments are ultimately passed to
1197 :meth:`_expression.Select.correlate`
1198 after coercion to expression constructs.
1200 The correlation arguments take effect in such cases
1201 as when :meth:`_query.Query.from_self` is used, or when
1202 a subquery as returned by :meth:`_query.Query.subquery` is
1203 embedded in another :func:`_expression.select` construct.
1205 .. seealso::
1207 :meth:`_sql.Select.correlate` - v2 equivalent method.
1209 """
1211 self._auto_correlate = False
1212 if fromclauses and fromclauses[0] in {None, False}:
1213 self._correlate = ()
1214 else:
1215 self._correlate = self._correlate + tuple(
1216 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
1217 )
1218 return self
1220 @_generative
1221 def autoflush(self, setting: bool) -> Self:
1222 """Return a Query with a specific 'autoflush' setting.
1224 As of SQLAlchemy 1.4, the :meth:`_orm.Query.autoflush` method
1225 is equivalent to using the ``autoflush`` execution option at the
1226 ORM level. See the section :ref:`orm_queryguide_autoflush` for
1227 further background on this option.
1229 """
1230 self.load_options += {"_autoflush": setting}
1231 return self
1233 @_generative
1234 def populate_existing(self) -> Self:
1235 """Return a :class:`_query.Query`
1236 that will expire and refresh all instances
1237 as they are loaded, or reused from the current :class:`.Session`.
1239 As of SQLAlchemy 1.4, the :meth:`_orm.Query.populate_existing` method
1240 is equivalent to using the ``populate_existing`` execution option at
1241 the ORM level. See the section :ref:`orm_queryguide_populate_existing`
1242 for further background on this option.
1244 """
1245 self.load_options += {"_populate_existing": True}
1246 return self
1248 @_generative
1249 def _with_invoke_all_eagers(self, value: bool) -> Self:
1250 """Set the 'invoke all eagers' flag which causes joined- and
1251 subquery loaders to traverse into already-loaded related objects
1252 and collections.
1254 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1256 """
1257 self.load_options += {"_invoke_all_eagers": value}
1258 return self
1260 @util.became_legacy_20(
1261 ":meth:`_orm.Query.with_parent`",
1262 alternative="Use the :func:`_orm.with_parent` standalone construct.",
1263 )
1264 @util.preload_module("sqlalchemy.orm.relationships")
1265 def with_parent(
1266 self,
1267 instance: object,
1268 property: Optional[ # noqa: A002
1269 attributes.QueryableAttribute[Any]
1270 ] = None,
1271 from_entity: Optional[_ExternalEntityType[Any]] = None,
1272 ) -> Self:
1273 """Add filtering criterion that relates the given instance
1274 to a child object or collection, using its attribute state
1275 as well as an established :func:`_orm.relationship()`
1276 configuration.
1278 The method uses the :func:`.with_parent` function to generate
1279 the clause, the result of which is passed to
1280 :meth:`_query.Query.filter`.
1282 Parameters are the same as :func:`.with_parent`, with the exception
1283 that the given property can be None, in which case a search is
1284 performed against this :class:`_query.Query` object's target mapper.
1286 :param instance:
1287 An instance which has some :func:`_orm.relationship`.
1289 :param property:
1290 Class bound attribute which indicates
1291 what relationship from the instance should be used to reconcile the
1292 parent/child relationship.
1294 :param from_entity:
1295 Entity in which to consider as the left side. This defaults to the
1296 "zero" entity of the :class:`_query.Query` itself.
1298 """
1299 relationships = util.preloaded.orm_relationships
1301 if from_entity:
1302 entity_zero = inspect(from_entity)
1303 else:
1304 entity_zero = _legacy_filter_by_entity_zero(self)
1305 if property is None:
1306 # TODO: deprecate, property has to be supplied
1307 mapper = object_mapper(instance)
1309 for prop in mapper.iterate_properties:
1310 if (
1311 isinstance(prop, relationships.RelationshipProperty)
1312 and prop.mapper is entity_zero.mapper # type: ignore
1313 ):
1314 property = prop # type: ignore # noqa: A001
1315 break
1316 else:
1317 raise sa_exc.InvalidRequestError(
1318 "Could not locate a property which relates instances "
1319 "of class '%s' to instances of class '%s'"
1320 % (
1321 entity_zero.mapper.class_.__name__, # type: ignore
1322 instance.__class__.__name__,
1323 )
1324 )
1326 return self.filter(
1327 with_parent(
1328 instance,
1329 property, # type: ignore
1330 entity_zero.entity, # type: ignore
1331 )
1332 )
1334 @_generative
1335 def add_entity(
1336 self,
1337 entity: _EntityType[Any],
1338 alias: Optional[Union[Alias, Subquery]] = None,
1339 ) -> Query[Any]:
1340 """add a mapped entity to the list of result columns
1341 to be returned.
1343 .. seealso::
1345 :meth:`_sql.Select.add_columns` - v2 comparable method.
1346 """
1348 if alias is not None:
1349 # TODO: deprecate
1350 entity = AliasedClass(entity, alias)
1352 self._raw_columns = list(self._raw_columns)
1354 self._raw_columns.append(
1355 coercions.expect(
1356 roles.ColumnsClauseRole, entity, apply_propagate_attrs=self
1357 )
1358 )
1359 return self
1361 @_generative
1362 def with_session(self, session: Session) -> Self:
1363 """Return a :class:`_query.Query` that will use the given
1364 :class:`.Session`.
1366 While the :class:`_query.Query`
1367 object is normally instantiated using the
1368 :meth:`.Session.query` method, it is legal to build the
1369 :class:`_query.Query`
1370 directly without necessarily using a :class:`.Session`. Such a
1371 :class:`_query.Query` object, or any :class:`_query.Query`
1372 already associated
1373 with a different :class:`.Session`, can produce a new
1374 :class:`_query.Query`
1375 object associated with a target session using this method::
1377 from sqlalchemy.orm import Query
1379 query = Query([MyClass]).filter(MyClass.id == 5)
1381 result = query.with_session(my_session).one()
1383 """
1385 self.session = session
1386 return self
1388 def _legacy_from_self(
1389 self, *entities: _ColumnsClauseArgument[Any]
1390 ) -> Self:
1391 # used for query.count() as well as for the same
1392 # function in BakedQuery, as well as some old tests in test_baked.py.
1394 fromclause = (
1395 self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
1396 .correlate(None)
1397 .subquery()
1398 ._anonymous_fromclause()
1399 )
1401 q = self._from_selectable(fromclause)
1403 if entities:
1404 q._set_entities(entities)
1405 return q
1407 @_generative
1408 def _set_enable_single_crit(self, val: bool) -> Self:
1409 self._compile_options += {"_enable_single_crit": val}
1410 return self
1412 @_generative
1413 def _from_selectable(
1414 self, fromclause: FromClause, set_entity_from: bool = True
1415 ) -> Self:
1416 for attr in (
1417 "_where_criteria",
1418 "_order_by_clauses",
1419 "_group_by_clauses",
1420 "_limit_clause",
1421 "_offset_clause",
1422 "_last_joined_entity",
1423 "_setup_joins",
1424 "_memoized_select_entities",
1425 "_distinct",
1426 "_distinct_on",
1427 "_having_criteria",
1428 "_prefixes",
1429 "_suffixes",
1430 ):
1431 self.__dict__.pop(attr, None)
1432 self._set_select_from([fromclause], set_entity_from)
1433 self._compile_options += {
1434 "_enable_single_crit": False,
1435 }
1437 return self
1439 @util.deprecated(
1440 "1.4",
1441 ":meth:`_query.Query.values` "
1442 "is deprecated and will be removed in a "
1443 "future release. Please use :meth:`_query.Query.with_entities`",
1444 )
1445 def values(self, *columns: _ColumnsClauseArgument[Any]) -> Iterable[Any]:
1446 """Return an iterator yielding result tuples corresponding
1447 to the given list of columns
1449 """
1450 return self._values_no_warn(*columns)
1452 _values = values
1454 def _values_no_warn(
1455 self, *columns: _ColumnsClauseArgument[Any]
1456 ) -> Iterable[Any]:
1457 if not columns:
1458 return iter(())
1459 q = self._clone().enable_eagerloads(False)
1460 q._set_entities(columns)
1461 if not q.load_options._yield_per:
1462 q.load_options += {"_yield_per": 10}
1463 return iter(q)
1465 @util.deprecated(
1466 "1.4",
1467 ":meth:`_query.Query.value` "
1468 "is deprecated and will be removed in a "
1469 "future release. Please use :meth:`_query.Query.with_entities` "
1470 "in combination with :meth:`_query.Query.scalar`",
1471 )
1472 def value(self, column: _ColumnExpressionArgument[Any]) -> Any:
1473 """Return a scalar result corresponding to the given
1474 column expression.
1476 """
1477 try:
1478 return next(self._values_no_warn(column))[0] # type: ignore
1479 except StopIteration:
1480 return None
1482 @overload
1483 def with_entities(self, _entity: _EntityType[_O]) -> Query[_O]: ...
1485 @overload
1486 def with_entities(
1487 self,
1488 _colexpr: roles.TypedColumnsClauseRole[_T],
1489 ) -> RowReturningQuery[Tuple[_T]]: ...
1491 # START OVERLOADED FUNCTIONS self.with_entities RowReturningQuery 2-8
1493 # code within this block is **programmatically,
1494 # statically generated** by tools/generate_tuple_map_overloads.py
1496 @overload
1497 def with_entities(
1498 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /
1499 ) -> RowReturningQuery[_T0, _T1]: ...
1501 @overload
1502 def with_entities(
1503 self, __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
1504 ) -> RowReturningQuery[_T0, _T1, _T2]: ...
1506 @overload
1507 def with_entities(
1508 self,
1509 __ent0: _TCCA[_T0],
1510 __ent1: _TCCA[_T1],
1511 __ent2: _TCCA[_T2],
1512 __ent3: _TCCA[_T3],
1513 /,
1514 ) -> RowReturningQuery[_T0, _T1, _T2, _T3]: ...
1516 @overload
1517 def with_entities(
1518 self,
1519 __ent0: _TCCA[_T0],
1520 __ent1: _TCCA[_T1],
1521 __ent2: _TCCA[_T2],
1522 __ent3: _TCCA[_T3],
1523 __ent4: _TCCA[_T4],
1524 /,
1525 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4]: ...
1527 @overload
1528 def with_entities(
1529 self,
1530 __ent0: _TCCA[_T0],
1531 __ent1: _TCCA[_T1],
1532 __ent2: _TCCA[_T2],
1533 __ent3: _TCCA[_T3],
1534 __ent4: _TCCA[_T4],
1535 __ent5: _TCCA[_T5],
1536 /,
1537 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4, _T5]: ...
1539 @overload
1540 def with_entities(
1541 self,
1542 __ent0: _TCCA[_T0],
1543 __ent1: _TCCA[_T1],
1544 __ent2: _TCCA[_T2],
1545 __ent3: _TCCA[_T3],
1546 __ent4: _TCCA[_T4],
1547 __ent5: _TCCA[_T5],
1548 __ent6: _TCCA[_T6],
1549 /,
1550 ) -> RowReturningQuery[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
1552 @overload
1553 def with_entities(
1554 self,
1555 __ent0: _TCCA[_T0],
1556 __ent1: _TCCA[_T1],
1557 __ent2: _TCCA[_T2],
1558 __ent3: _TCCA[_T3],
1559 __ent4: _TCCA[_T4],
1560 __ent5: _TCCA[_T5],
1561 __ent6: _TCCA[_T6],
1562 __ent7: _TCCA[_T7],
1563 /,
1564 *entities: _ColumnsClauseArgument[Any],
1565 ) -> RowReturningQuery[
1566 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]
1567 ]: ...
1569 # END OVERLOADED FUNCTIONS self.with_entities
1571 @overload
1572 def with_entities(
1573 self, *entities: _ColumnsClauseArgument[Any]
1574 ) -> Query[Any]: ...
1576 @_generative
1577 def with_entities(
1578 self, *entities: _ColumnsClauseArgument[Any], **__kw: Any
1579 ) -> Query[Any]:
1580 r"""Return a new :class:`_query.Query`
1581 replacing the SELECT list with the
1582 given entities.
1584 e.g.::
1586 # Users, filtered on some arbitrary criterion
1587 # and then ordered by related email address
1588 q = session.query(User).\
1589 join(User.address).\
1590 filter(User.name.like('%ed%')).\
1591 order_by(Address.email)
1593 # given *only* User.id==5, Address.email, and 'q', what
1594 # would the *next* User in the result be ?
1595 subq = q.with_entities(Address.email).\
1596 order_by(None).\
1597 filter(User.id==5).\
1598 subquery()
1599 q = q.join((subq, subq.c.email < Address.email)).\
1600 limit(1)
1602 .. seealso::
1604 :meth:`_sql.Select.with_only_columns` - v2 comparable method.
1605 """
1606 if __kw:
1607 raise _no_kw()
1609 # Query has all the same fields as Select for this operation
1610 # this could in theory be based on a protocol but not sure if it's
1611 # worth it
1612 _MemoizedSelectEntities._generate_for_statement(self) # type: ignore
1613 self._set_entities(entities)
1614 return self
1616 @_generative
1617 def add_columns(
1618 self, *column: _ColumnExpressionArgument[Any]
1619 ) -> Query[Any]:
1620 """Add one or more column expressions to the list
1621 of result columns to be returned.
1623 .. seealso::
1625 :meth:`_sql.Select.add_columns` - v2 comparable method.
1626 """
1628 self._raw_columns = list(self._raw_columns)
1630 self._raw_columns.extend(
1631 coercions.expect(
1632 roles.ColumnsClauseRole,
1633 c,
1634 apply_propagate_attrs=self,
1635 post_inspect=True,
1636 )
1637 for c in column
1638 )
1639 return self
1641 @util.deprecated(
1642 "1.4",
1643 ":meth:`_query.Query.add_column` "
1644 "is deprecated and will be removed in a "
1645 "future release. Please use :meth:`_query.Query.add_columns`",
1646 )
1647 def add_column(self, column: _ColumnExpressionArgument[Any]) -> Query[Any]:
1648 """Add a column expression to the list of result columns to be
1649 returned.
1651 """
1652 return self.add_columns(column)
1654 @_generative
1655 def options(self, *args: ExecutableOption) -> Self:
1656 """Return a new :class:`_query.Query` object,
1657 applying the given list of
1658 mapper options.
1660 Most supplied options regard changing how column- and
1661 relationship-mapped attributes are loaded.
1663 .. seealso::
1665 :ref:`loading_columns`
1667 :ref:`relationship_loader_options`
1669 """
1671 opts = tuple(util.flatten_iterator(args))
1672 if self._compile_options._current_path:
1673 # opting for lower method overhead for the checks
1674 for opt in opts:
1675 if not opt._is_core and opt._is_legacy_option: # type: ignore
1676 opt.process_query_conditionally(self) # type: ignore
1677 else:
1678 for opt in opts:
1679 if not opt._is_core and opt._is_legacy_option: # type: ignore
1680 opt.process_query(self) # type: ignore
1682 self._with_options += opts
1683 return self
1685 def with_transformation(
1686 self, fn: Callable[[Query[Any]], Query[Any]]
1687 ) -> Query[Any]:
1688 """Return a new :class:`_query.Query` object transformed by
1689 the given function.
1691 E.g.::
1693 def filter_something(criterion):
1694 def transform(q):
1695 return q.filter(criterion)
1696 return transform
1698 q = q.with_transformation(filter_something(x==5))
1700 This allows ad-hoc recipes to be created for :class:`_query.Query`
1701 objects.
1703 """
1704 return fn(self)
1706 def get_execution_options(self) -> _ImmutableExecuteOptions:
1707 """Get the non-SQL options which will take effect during execution.
1709 .. versionadded:: 1.3
1711 .. seealso::
1713 :meth:`_query.Query.execution_options`
1715 :meth:`_sql.Select.get_execution_options` - v2 comparable method.
1717 """
1718 return self._execution_options
1720 @overload
1721 def execution_options(
1722 self,
1723 *,
1724 compiled_cache: Optional[CompiledCacheType] = ...,
1725 logging_token: str = ...,
1726 isolation_level: IsolationLevel = ...,
1727 no_parameters: bool = False,
1728 stream_results: bool = False,
1729 max_row_buffer: int = ...,
1730 yield_per: int = ...,
1731 insertmanyvalues_page_size: int = ...,
1732 schema_translate_map: Optional[SchemaTranslateMapType] = ...,
1733 populate_existing: bool = False,
1734 autoflush: bool = False,
1735 preserve_rowcount: bool = False,
1736 **opt: Any,
1737 ) -> Self: ...
1739 @overload
1740 def execution_options(self, **opt: Any) -> Self: ...
1742 @_generative
1743 def execution_options(self, **kwargs: Any) -> Self:
1744 """Set non-SQL options which take effect during execution.
1746 Options allowed here include all of those accepted by
1747 :meth:`_engine.Connection.execution_options`, as well as a series
1748 of ORM specific options:
1750 ``populate_existing=True`` - equivalent to using
1751 :meth:`_orm.Query.populate_existing`
1753 ``autoflush=True|False`` - equivalent to using
1754 :meth:`_orm.Query.autoflush`
1756 ``yield_per=<value>`` - equivalent to using
1757 :meth:`_orm.Query.yield_per`
1759 Note that the ``stream_results`` execution option is enabled
1760 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1761 method or execution option is used.
1763 .. versionadded:: 1.4 - added ORM options to
1764 :meth:`_orm.Query.execution_options`
1766 The execution options may also be specified on a per execution basis
1767 when using :term:`2.0 style` queries via the
1768 :paramref:`_orm.Session.execution_options` parameter.
1770 .. warning:: The
1771 :paramref:`_engine.Connection.execution_options.stream_results`
1772 parameter should not be used at the level of individual ORM
1773 statement executions, as the :class:`_orm.Session` will not track
1774 objects from different schema translate maps within a single
1775 session. For multiple schema translate maps within the scope of a
1776 single :class:`_orm.Session`, see :ref:`examples_sharding`.
1779 .. seealso::
1781 :ref:`engine_stream_results`
1783 :meth:`_query.Query.get_execution_options`
1785 :meth:`_sql.Select.execution_options` - v2 equivalent method.
1787 """
1788 self._execution_options = self._execution_options.union(kwargs)
1789 return self
1791 @_generative
1792 def with_for_update(
1793 self,
1794 *,
1795 nowait: bool = False,
1796 read: bool = False,
1797 of: Optional[_ForUpdateOfArgument] = None,
1798 skip_locked: bool = False,
1799 key_share: bool = False,
1800 ) -> Self:
1801 """return a new :class:`_query.Query`
1802 with the specified options for the
1803 ``FOR UPDATE`` clause.
1805 The behavior of this method is identical to that of
1806 :meth:`_expression.GenerativeSelect.with_for_update`.
1807 When called with no arguments,
1808 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1809 appended. When additional arguments are specified, backend-specific
1810 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1811 can take effect.
1813 E.g.::
1815 q = sess.query(User).populate_existing().with_for_update(nowait=True, of=User)
1817 The above query on a PostgreSQL backend will render like::
1819 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1821 .. warning::
1823 Using ``with_for_update`` in the context of eager loading
1824 relationships is not officially supported or recommended by
1825 SQLAlchemy and may not work with certain queries on various
1826 database backends. When ``with_for_update`` is successfully used
1827 with a query that involves :func:`_orm.joinedload`, SQLAlchemy will
1828 attempt to emit SQL that locks all involved tables.
1830 .. note:: It is generally a good idea to combine the use of the
1831 :meth:`_orm.Query.populate_existing` method when using the
1832 :meth:`_orm.Query.with_for_update` method. The purpose of
1833 :meth:`_orm.Query.populate_existing` is to force all the data read
1834 from the SELECT to be populated into the ORM objects returned,
1835 even if these objects are already in the :term:`identity map`.
1837 .. seealso::
1839 :meth:`_expression.GenerativeSelect.with_for_update`
1840 - Core level method with
1841 full argument and behavioral description.
1843 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1844 objects already loaded in the identity map.
1846 """ # noqa: E501
1848 self._for_update_arg = ForUpdateArg(
1849 read=read,
1850 nowait=nowait,
1851 of=of,
1852 skip_locked=skip_locked,
1853 key_share=key_share,
1854 )
1855 return self
1857 @_generative
1858 def params(
1859 self, __params: Optional[Dict[str, Any]] = None, /, **kw: Any
1860 ) -> Self:
1861 r"""Add values for bind parameters which may have been
1862 specified in filter().
1864 Parameters may be specified using \**kwargs, or optionally a single
1865 dictionary as the first positional argument. The reason for both is
1866 that \**kwargs is convenient, however some parameter dictionaries
1867 contain unicode keys in which case \**kwargs cannot be used.
1869 """
1870 if __params:
1871 kw.update(__params)
1872 self._params = self._params.union(kw)
1873 return self
1875 def where(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1876 """A synonym for :meth:`.Query.filter`.
1878 .. versionadded:: 1.4
1880 .. seealso::
1882 :meth:`_sql.Select.where` - v2 equivalent method.
1884 """
1885 return self.filter(*criterion)
1887 @_generative
1888 @_assertions(_no_statement_condition, _no_limit_offset)
1889 def filter(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1890 r"""Apply the given filtering criterion to a copy
1891 of this :class:`_query.Query`, using SQL expressions.
1893 e.g.::
1895 session.query(MyClass).filter(MyClass.name == 'some name')
1897 Multiple criteria may be specified as comma separated; the effect
1898 is that they will be joined together using the :func:`.and_`
1899 function::
1901 session.query(MyClass).\
1902 filter(MyClass.name == 'some name', MyClass.id > 5)
1904 The criterion is any SQL expression object applicable to the
1905 WHERE clause of a select. String expressions are coerced
1906 into SQL expression constructs via the :func:`_expression.text`
1907 construct.
1909 .. seealso::
1911 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1913 :meth:`_sql.Select.where` - v2 equivalent method.
1915 """
1916 for crit in list(criterion):
1917 crit = coercions.expect(
1918 roles.WhereHavingRole, crit, apply_propagate_attrs=self
1919 )
1921 self._where_criteria += (crit,)
1922 return self
1924 @util.memoized_property
1925 def _last_joined_entity(
1926 self,
1927 ) -> Optional[Union[_InternalEntityType[Any], _JoinTargetElement]]:
1928 if self._setup_joins:
1929 return _determine_last_joined_entity(
1930 self._setup_joins,
1931 )
1932 else:
1933 return None
1935 def _filter_by_zero(self) -> Any:
1936 """for the filter_by() method, return the target entity for which
1937 we will attempt to derive an expression from based on string name.
1939 """
1941 if self._setup_joins:
1942 _last_joined_entity = self._last_joined_entity
1943 if _last_joined_entity is not None:
1944 return _last_joined_entity
1946 # discussion related to #7239
1947 # special check determines if we should try to derive attributes
1948 # for filter_by() from the "from object", i.e., if the user
1949 # called query.select_from(some selectable).filter_by(some_attr=value).
1950 # We don't want to do that in the case that methods like
1951 # from_self(), select_entity_from(), or a set op like union() were
1952 # called; while these methods also place a
1953 # selectable in the _from_obj collection, they also set up
1954 # the _set_base_alias boolean which turns on the whole "adapt the
1955 # entity to this selectable" thing, meaning the query still continues
1956 # to construct itself in terms of the lead entity that was passed
1957 # to query(), e.g. query(User).from_self() is still in terms of User,
1958 # and not the subquery that from_self() created. This feature of
1959 # "implicitly adapt all occurrences of entity X to some arbitrary
1960 # subquery" is the main thing I am trying to do away with in 2.0 as
1961 # users should now used aliased() for that, but I can't entirely get
1962 # rid of it due to query.union() and other set ops relying upon it.
1963 #
1964 # compare this to the base Select()._filter_by_zero() which can
1965 # just return self._from_obj[0] if present, because there is no
1966 # "_set_base_alias" feature.
1967 #
1968 # IOW, this conditional essentially detects if
1969 # "select_from(some_selectable)" has been called, as opposed to
1970 # "select_entity_from()", "from_self()"
1971 # or "union() / some_set_op()".
1972 if self._from_obj and not self._compile_options._set_base_alias:
1973 return self._from_obj[0]
1975 return self._raw_columns[0]
1977 def filter_by(self, **kwargs: Any) -> Self:
1978 r"""Apply the given filtering criterion to a copy
1979 of this :class:`_query.Query`, using keyword expressions.
1981 e.g.::
1983 session.query(MyClass).filter_by(name = 'some name')
1985 Multiple criteria may be specified as comma separated; the effect
1986 is that they will be joined together using the :func:`.and_`
1987 function::
1989 session.query(MyClass).\
1990 filter_by(name = 'some name', id = 5)
1992 The keyword expressions are extracted from the primary
1993 entity of the query, or the last entity that was the
1994 target of a call to :meth:`_query.Query.join`.
1996 .. seealso::
1998 :meth:`_query.Query.filter` - filter on SQL expressions.
2000 :meth:`_sql.Select.filter_by` - v2 comparable method.
2002 """
2003 from_entity = self._filter_by_zero()
2005 clauses = [
2006 _entity_namespace_key(from_entity, key) == value
2007 for key, value in kwargs.items()
2008 ]
2009 return self.filter(*clauses)
2011 @_generative
2012 def order_by(
2013 self,
2014 __first: Union[
2015 Literal[None, False, _NoArg.NO_ARG],
2016 _ColumnExpressionOrStrLabelArgument[Any],
2017 ] = _NoArg.NO_ARG,
2018 /,
2019 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2020 ) -> Self:
2021 """Apply one or more ORDER BY criteria to the query and return
2022 the newly resulting :class:`_query.Query`.
2024 e.g.::
2026 q = session.query(Entity).order_by(Entity.id, Entity.name)
2028 Calling this method multiple times is equivalent to calling it once
2029 with all the clauses concatenated. All existing ORDER BY criteria may
2030 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
2031 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
2033 # will erase all ORDER BY and ORDER BY new_col alone
2034 q = q.order_by(None).order_by(new_col)
2036 .. seealso::
2038 These sections describe ORDER BY in terms of :term:`2.0 style`
2039 invocation but apply to :class:`_orm.Query` as well:
2041 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
2043 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2045 :meth:`_sql.Select.order_by` - v2 equivalent method.
2047 """
2049 for assertion in (self._no_statement_condition, self._no_limit_offset):
2050 assertion("order_by")
2052 if not clauses and (__first is None or __first is False):
2053 self._order_by_clauses = ()
2054 elif __first is not _NoArg.NO_ARG:
2055 criterion = tuple(
2056 coercions.expect(roles.OrderByRole, clause)
2057 for clause in (__first,) + clauses
2058 )
2059 self._order_by_clauses += criterion
2061 return self
2063 @_generative
2064 def group_by(
2065 self,
2066 __first: Union[
2067 Literal[None, False, _NoArg.NO_ARG],
2068 _ColumnExpressionOrStrLabelArgument[Any],
2069 ] = _NoArg.NO_ARG,
2070 /,
2071 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2072 ) -> Self:
2073 """Apply one or more GROUP BY criterion to the query and return
2074 the newly resulting :class:`_query.Query`.
2076 All existing GROUP BY settings can be suppressed by
2077 passing ``None`` - this will suppress any GROUP BY configured
2078 on mappers as well.
2080 .. seealso::
2082 These sections describe GROUP BY in terms of :term:`2.0 style`
2083 invocation but apply to :class:`_orm.Query` as well:
2085 :ref:`tutorial_group_by_w_aggregates` - in the
2086 :ref:`unified_tutorial`
2088 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2090 :meth:`_sql.Select.group_by` - v2 equivalent method.
2092 """
2094 for assertion in (self._no_statement_condition, self._no_limit_offset):
2095 assertion("group_by")
2097 if not clauses and (__first is None or __first is False):
2098 self._group_by_clauses = ()
2099 elif __first is not _NoArg.NO_ARG:
2100 criterion = tuple(
2101 coercions.expect(roles.GroupByRole, clause)
2102 for clause in (__first,) + clauses
2103 )
2104 self._group_by_clauses += criterion
2105 return self
2107 @_generative
2108 @_assertions(_no_statement_condition, _no_limit_offset)
2109 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:
2110 r"""Apply a HAVING criterion to the query and return the
2111 newly resulting :class:`_query.Query`.
2113 :meth:`_query.Query.having` is used in conjunction with
2114 :meth:`_query.Query.group_by`.
2116 HAVING criterion makes it possible to use filters on aggregate
2117 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
2119 q = session.query(User.id).\
2120 join(User.addresses).\
2121 group_by(User.id).\
2122 having(func.count(Address.id) > 2)
2124 .. seealso::
2126 :meth:`_sql.Select.having` - v2 equivalent method.
2128 """
2130 for criterion in having:
2131 having_criteria = coercions.expect(
2132 roles.WhereHavingRole, criterion
2133 )
2134 self._having_criteria += (having_criteria,)
2135 return self
2137 def _set_op(self, expr_fn: Any, *q: Query[Any]) -> Self:
2138 list_of_queries = (self,) + q
2139 return self._from_selectable(expr_fn(*(list_of_queries)).subquery())
2141 def union(self, *q: Query[Any]) -> Self:
2142 """Produce a UNION of this Query against one or more queries.
2144 e.g.::
2146 q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
2147 q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
2149 q3 = q1.union(q2)
2151 The method accepts multiple Query objects so as to control
2152 the level of nesting. A series of ``union()`` calls such as::
2154 x.union(y).union(z).all()
2156 will nest on each ``union()``, and produces::
2158 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
2159 SELECT * FROM y) UNION SELECT * FROM Z)
2161 Whereas::
2163 x.union(y, z).all()
2165 produces::
2167 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
2168 SELECT * FROM Z)
2170 Note that many database backends do not allow ORDER BY to
2171 be rendered on a query called within UNION, EXCEPT, etc.
2172 To disable all ORDER BY clauses including those configured
2173 on mappers, issue ``query.order_by(None)`` - the resulting
2174 :class:`_query.Query` object will not render ORDER BY within
2175 its SELECT statement.
2177 .. seealso::
2179 :meth:`_sql.Select.union` - v2 equivalent method.
2181 """
2182 return self._set_op(expression.union, *q)
2184 def union_all(self, *q: Query[Any]) -> Self:
2185 """Produce a UNION ALL of this Query against one or more queries.
2187 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2188 that method for usage examples.
2190 .. seealso::
2192 :meth:`_sql.Select.union_all` - v2 equivalent method.
2194 """
2195 return self._set_op(expression.union_all, *q)
2197 def intersect(self, *q: Query[Any]) -> Self:
2198 """Produce an INTERSECT of this Query against one or more queries.
2200 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2201 that method for usage examples.
2203 .. seealso::
2205 :meth:`_sql.Select.intersect` - v2 equivalent method.
2207 """
2208 return self._set_op(expression.intersect, *q)
2210 def intersect_all(self, *q: Query[Any]) -> Self:
2211 """Produce an INTERSECT ALL of this Query against one or more queries.
2213 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2214 that method for usage examples.
2216 .. seealso::
2218 :meth:`_sql.Select.intersect_all` - v2 equivalent method.
2220 """
2221 return self._set_op(expression.intersect_all, *q)
2223 def except_(self, *q: Query[Any]) -> Self:
2224 """Produce an EXCEPT of this Query against one or more queries.
2226 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2227 that method for usage examples.
2229 .. seealso::
2231 :meth:`_sql.Select.except_` - v2 equivalent method.
2233 """
2234 return self._set_op(expression.except_, *q)
2236 def except_all(self, *q: Query[Any]) -> Self:
2237 """Produce an EXCEPT ALL of this Query against one or more queries.
2239 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2240 that method for usage examples.
2242 .. seealso::
2244 :meth:`_sql.Select.except_all` - v2 equivalent method.
2246 """
2247 return self._set_op(expression.except_all, *q)
2249 @_generative
2250 @_assertions(_no_statement_condition, _no_limit_offset)
2251 def join(
2252 self,
2253 target: _JoinTargetArgument,
2254 onclause: Optional[_OnClauseArgument] = None,
2255 *,
2256 isouter: bool = False,
2257 full: bool = False,
2258 ) -> Self:
2259 r"""Create a SQL JOIN against this :class:`_query.Query`
2260 object's criterion
2261 and apply generatively, returning the newly resulting
2262 :class:`_query.Query`.
2264 **Simple Relationship Joins**
2266 Consider a mapping between two classes ``User`` and ``Address``,
2267 with a relationship ``User.addresses`` representing a collection
2268 of ``Address`` objects associated with each ``User``. The most
2269 common usage of :meth:`_query.Query.join`
2270 is to create a JOIN along this
2271 relationship, using the ``User.addresses`` attribute as an indicator
2272 for how this should occur::
2274 q = session.query(User).join(User.addresses)
2276 Where above, the call to :meth:`_query.Query.join` along
2277 ``User.addresses`` will result in SQL approximately equivalent to::
2279 SELECT user.id, user.name
2280 FROM user JOIN address ON user.id = address.user_id
2282 In the above example we refer to ``User.addresses`` as passed to
2283 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2284 how the "ON" portion of the JOIN should be constructed.
2286 To construct a chain of joins, multiple :meth:`_query.Query.join`
2287 calls may be used. The relationship-bound attribute implies both
2288 the left and right side of the join at once::
2290 q = session.query(User).\
2291 join(User.orders).\
2292 join(Order.items).\
2293 join(Item.keywords)
2295 .. note:: as seen in the above example, **the order in which each
2296 call to the join() method occurs is important**. Query would not,
2297 for example, know how to join correctly if we were to specify
2298 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2299 such a case, depending on the arguments passed, it may raise an
2300 error that it doesn't know how to join, or it may produce invalid
2301 SQL in which case the database will raise an error. In correct
2302 practice, the
2303 :meth:`_query.Query.join` method is invoked in such a way that lines
2304 up with how we would want the JOIN clauses in SQL to be
2305 rendered, and each call should represent a clear link from what
2306 precedes it.
2308 **Joins to a Target Entity or Selectable**
2310 A second form of :meth:`_query.Query.join` allows any mapped entity or
2311 core selectable construct as a target. In this usage,
2312 :meth:`_query.Query.join` will attempt to create a JOIN along the
2313 natural foreign key relationship between two entities::
2315 q = session.query(User).join(Address)
2317 In the above calling form, :meth:`_query.Query.join` is called upon to
2318 create the "on clause" automatically for us. This calling form will
2319 ultimately raise an error if either there are no foreign keys between
2320 the two entities, or if there are multiple foreign key linkages between
2321 the target entity and the entity or entities already present on the
2322 left side such that creating a join requires more information. Note
2323 that when indicating a join to a target without any ON clause, ORM
2324 configured relationships are not taken into account.
2326 **Joins to a Target with an ON Clause**
2328 The third calling form allows both the target entity as well
2329 as the ON clause to be passed explicitly. A example that includes
2330 a SQL expression as the ON clause is as follows::
2332 q = session.query(User).join(Address, User.id==Address.user_id)
2334 The above form may also use a relationship-bound attribute as the
2335 ON clause as well::
2337 q = session.query(User).join(Address, User.addresses)
2339 The above syntax can be useful for the case where we wish
2340 to join to an alias of a particular target entity. If we wanted
2341 to join to ``Address`` twice, it could be achieved using two
2342 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2344 a1 = aliased(Address)
2345 a2 = aliased(Address)
2347 q = session.query(User).\
2348 join(a1, User.addresses).\
2349 join(a2, User.addresses).\
2350 filter(a1.email_address=='ed@foo.com').\
2351 filter(a2.email_address=='ed@bar.com')
2353 The relationship-bound calling form can also specify a target entity
2354 using the :meth:`_orm.PropComparator.of_type` method; a query
2355 equivalent to the one above would be::
2357 a1 = aliased(Address)
2358 a2 = aliased(Address)
2360 q = session.query(User).\
2361 join(User.addresses.of_type(a1)).\
2362 join(User.addresses.of_type(a2)).\
2363 filter(a1.email_address == 'ed@foo.com').\
2364 filter(a2.email_address == 'ed@bar.com')
2366 **Augmenting Built-in ON Clauses**
2368 As a substitute for providing a full custom ON condition for an
2369 existing relationship, the :meth:`_orm.PropComparator.and_` function
2370 may be applied to a relationship attribute to augment additional
2371 criteria into the ON clause; the additional criteria will be combined
2372 with the default criteria using AND::
2374 q = session.query(User).join(
2375 User.addresses.and_(Address.email_address != 'foo@bar.com')
2376 )
2378 .. versionadded:: 1.4
2380 **Joining to Tables and Subqueries**
2383 The target of a join may also be any table or SELECT statement,
2384 which may be related to a target entity or not. Use the
2385 appropriate ``.subquery()`` method in order to make a subquery
2386 out of a query::
2388 subq = session.query(Address).\
2389 filter(Address.email_address == 'ed@foo.com').\
2390 subquery()
2393 q = session.query(User).join(
2394 subq, User.id == subq.c.user_id
2395 )
2397 Joining to a subquery in terms of a specific relationship and/or
2398 target entity may be achieved by linking the subquery to the
2399 entity using :func:`_orm.aliased`::
2401 subq = session.query(Address).\
2402 filter(Address.email_address == 'ed@foo.com').\
2403 subquery()
2405 address_subq = aliased(Address, subq)
2407 q = session.query(User).join(
2408 User.addresses.of_type(address_subq)
2409 )
2412 **Controlling what to Join From**
2414 In cases where the left side of the current state of
2415 :class:`_query.Query` is not in line with what we want to join from,
2416 the :meth:`_query.Query.select_from` method may be used::
2418 q = session.query(Address).select_from(User).\
2419 join(User.addresses).\
2420 filter(User.name == 'ed')
2422 Which will produce SQL similar to::
2424 SELECT address.* FROM user
2425 JOIN address ON user.id=address.user_id
2426 WHERE user.name = :name_1
2428 .. seealso::
2430 :meth:`_sql.Select.join` - v2 equivalent method.
2432 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2433 the props collection in modern use should be considered to be a one
2434 or two argument form, either as a single "target" entity or ORM
2435 attribute-bound relationship, or as a target entity plus an "on
2436 clause" which may be a SQL expression or ORM attribute-bound
2437 relationship.
2439 :param isouter=False: If True, the join used will be a left outer join,
2440 just as if the :meth:`_query.Query.outerjoin` method were called.
2442 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2444 """
2446 join_target = coercions.expect(
2447 roles.JoinTargetRole,
2448 target,
2449 apply_propagate_attrs=self,
2450 legacy=True,
2451 )
2452 if onclause is not None:
2453 onclause_element = coercions.expect(
2454 roles.OnClauseRole, onclause, legacy=True
2455 )
2456 else:
2457 onclause_element = None
2459 self._setup_joins += (
2460 (
2461 join_target,
2462 onclause_element,
2463 None,
2464 {
2465 "isouter": isouter,
2466 "full": full,
2467 },
2468 ),
2469 )
2471 self.__dict__.pop("_last_joined_entity", None)
2472 return self
2474 def outerjoin(
2475 self,
2476 target: _JoinTargetArgument,
2477 onclause: Optional[_OnClauseArgument] = None,
2478 *,
2479 full: bool = False,
2480 ) -> Self:
2481 """Create a left outer join against this ``Query`` object's criterion
2482 and apply generatively, returning the newly resulting ``Query``.
2484 Usage is the same as the ``join()`` method.
2486 .. seealso::
2488 :meth:`_sql.Select.outerjoin` - v2 equivalent method.
2490 """
2491 return self.join(target, onclause=onclause, isouter=True, full=full)
2493 @_generative
2494 @_assertions(_no_statement_condition)
2495 def reset_joinpoint(self) -> Self:
2496 """Return a new :class:`.Query`, where the "join point" has
2497 been reset back to the base FROM entities of the query.
2499 This method is usually used in conjunction with the
2500 ``aliased=True`` feature of the :meth:`~.Query.join`
2501 method. See the example in :meth:`~.Query.join` for how
2502 this is used.
2504 """
2505 self._last_joined_entity = None
2507 return self
2509 @_generative
2510 @_assertions(_no_clauseelement_condition)
2511 def select_from(self, *from_obj: _FromClauseArgument) -> Self:
2512 r"""Set the FROM clause of this :class:`.Query` explicitly.
2514 :meth:`.Query.select_from` is often used in conjunction with
2515 :meth:`.Query.join` in order to control which entity is selected
2516 from on the "left" side of the join.
2518 The entity or selectable object here effectively replaces the
2519 "left edge" of any calls to :meth:`~.Query.join`, when no
2520 joinpoint is otherwise established - usually, the default "join
2521 point" is the leftmost entity in the :class:`~.Query` object's
2522 list of entities to be selected.
2524 A typical example::
2526 q = session.query(Address).select_from(User).\
2527 join(User.addresses).\
2528 filter(User.name == 'ed')
2530 Which produces SQL equivalent to::
2532 SELECT address.* FROM user
2533 JOIN address ON user.id=address.user_id
2534 WHERE user.name = :name_1
2536 :param \*from_obj: collection of one or more entities to apply
2537 to the FROM clause. Entities can be mapped classes,
2538 :class:`.AliasedClass` objects, :class:`.Mapper` objects
2539 as well as core :class:`.FromClause` elements like subqueries.
2541 .. seealso::
2543 :meth:`~.Query.join`
2545 :meth:`.Query.select_entity_from`
2547 :meth:`_sql.Select.select_from` - v2 equivalent method.
2549 """
2551 self._set_select_from(from_obj, False)
2552 return self
2554 def __getitem__(self, item: Any) -> Any:
2555 return orm_util._getitem(
2556 self,
2557 item,
2558 )
2560 @_generative
2561 @_assertions(_no_statement_condition)
2562 def slice(
2563 self,
2564 start: int,
2565 stop: int,
2566 ) -> Self:
2567 """Computes the "slice" of the :class:`_query.Query` represented by
2568 the given indices and returns the resulting :class:`_query.Query`.
2570 The start and stop indices behave like the argument to Python's
2571 built-in :func:`range` function. This method provides an
2572 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
2573 query.
2575 For example, ::
2577 session.query(User).order_by(User.id).slice(1, 3)
2579 renders as
2581 .. sourcecode:: sql
2583 SELECT users.id AS users_id,
2584 users.name AS users_name
2585 FROM users ORDER BY users.id
2586 LIMIT ? OFFSET ?
2587 (2, 1)
2589 .. seealso::
2591 :meth:`_query.Query.limit`
2593 :meth:`_query.Query.offset`
2595 :meth:`_sql.Select.slice` - v2 equivalent method.
2597 """
2599 self._limit_clause, self._offset_clause = sql_util._make_slice(
2600 self._limit_clause, self._offset_clause, start, stop
2601 )
2602 return self
2604 @_generative
2605 @_assertions(_no_statement_condition)
2606 def limit(self, limit: _LimitOffsetType) -> Self:
2607 """Apply a ``LIMIT`` to the query and return the newly resulting
2608 ``Query``.
2610 .. seealso::
2612 :meth:`_sql.Select.limit` - v2 equivalent method.
2614 """
2615 self._limit_clause = sql_util._offset_or_limit_clause(limit)
2616 return self
2618 @_generative
2619 @_assertions(_no_statement_condition)
2620 def offset(self, offset: _LimitOffsetType) -> Self:
2621 """Apply an ``OFFSET`` to the query and return the newly resulting
2622 ``Query``.
2624 .. seealso::
2626 :meth:`_sql.Select.offset` - v2 equivalent method.
2627 """
2628 self._offset_clause = sql_util._offset_or_limit_clause(offset)
2629 return self
2631 @_generative
2632 @_assertions(_no_statement_condition)
2633 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:
2634 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
2635 ``Query``.
2638 .. note::
2640 The ORM-level :meth:`.distinct` call includes logic that will
2641 automatically add columns from the ORDER BY of the query to the
2642 columns clause of the SELECT statement, to satisfy the common need
2643 of the database backend that ORDER BY columns be part of the SELECT
2644 list when DISTINCT is used. These columns *are not* added to the
2645 list of columns actually fetched by the :class:`_query.Query`,
2646 however,
2647 so would not affect results. The columns are passed through when
2648 using the :attr:`_query.Query.statement` accessor, however.
2650 .. deprecated:: 2.0 This logic is deprecated and will be removed
2651 in SQLAlchemy 2.0. See :ref:`migration_20_query_distinct`
2652 for a description of this use case in 2.0.
2654 .. seealso::
2656 :meth:`_sql.Select.distinct` - v2 equivalent method.
2658 :param \*expr: optional column expressions. When present,
2659 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2660 construct.
2662 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
2663 and will raise :class:`_exc.CompileError` in a future version.
2665 """
2666 if expr:
2667 self._distinct = True
2668 self._distinct_on = self._distinct_on + tuple(
2669 coercions.expect(roles.ByOfRole, e) for e in expr
2670 )
2671 else:
2672 self._distinct = True
2673 return self
2675 def all(self) -> List[_T]:
2676 """Return the results represented by this :class:`_query.Query`
2677 as a list.
2679 This results in an execution of the underlying SQL statement.
2681 .. warning:: The :class:`_query.Query` object,
2682 when asked to return either
2683 a sequence or iterator that consists of full ORM-mapped entities,
2684 will **deduplicate entries based on primary key**. See the FAQ for
2685 more details.
2687 .. seealso::
2689 :ref:`faq_query_deduplicating`
2691 .. seealso::
2693 :meth:`_engine.Result.all` - v2 comparable method.
2695 :meth:`_engine.Result.scalars` - v2 comparable method.
2696 """
2697 return self._iter().all() # type: ignore
2699 @_generative
2700 @_assertions(_no_clauseelement_condition)
2701 def from_statement(self, statement: ExecutableReturnsRows) -> Self:
2702 """Execute the given SELECT statement and return results.
2704 This method bypasses all internal statement compilation, and the
2705 statement is executed without modification.
2707 The statement is typically either a :func:`_expression.text`
2708 or :func:`_expression.select` construct, and should return the set
2709 of columns
2710 appropriate to the entity class represented by this
2711 :class:`_query.Query`.
2713 .. seealso::
2715 :meth:`_sql.Select.from_statement` - v2 comparable method.
2717 """
2718 statement = coercions.expect(
2719 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2720 )
2721 self._statement = statement
2722 return self
2724 def first(self) -> Optional[_T]:
2725 """Return the first result of this ``Query`` or
2726 None if the result doesn't contain any row.
2728 first() applies a limit of one within the generated SQL, so that
2729 only one primary entity row is generated on the server side
2730 (note this may consist of multiple result rows if join-loaded
2731 collections are present).
2733 Calling :meth:`_query.Query.first`
2734 results in an execution of the underlying
2735 query.
2737 .. seealso::
2739 :meth:`_query.Query.one`
2741 :meth:`_query.Query.one_or_none`
2743 :meth:`_engine.Result.first` - v2 comparable method.
2745 :meth:`_engine.Result.scalars` - v2 comparable method.
2747 """
2748 # replicates limit(1) behavior
2749 if self._statement is not None:
2750 return self._iter().first() # type: ignore
2751 else:
2752 return self.limit(1)._iter().first() # type: ignore
2754 def one_or_none(self) -> Optional[_T]:
2755 """Return at most one result or raise an exception.
2757 Returns ``None`` if the query selects
2758 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2759 if multiple object identities are returned, or if multiple
2760 rows are returned for a query that returns only scalar values
2761 as opposed to full identity-mapped entities.
2763 Calling :meth:`_query.Query.one_or_none`
2764 results in an execution of the
2765 underlying query.
2767 .. seealso::
2769 :meth:`_query.Query.first`
2771 :meth:`_query.Query.one`
2773 :meth:`_engine.Result.one_or_none` - v2 comparable method.
2775 :meth:`_engine.Result.scalar_one_or_none` - v2 comparable method.
2777 """
2778 return self._iter().one_or_none() # type: ignore
2780 def one(self) -> _T:
2781 """Return exactly one result or raise an exception.
2783 Raises ``sqlalchemy.orm.exc.NoResultFound`` if the query selects
2784 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2785 if multiple object identities are returned, or if multiple
2786 rows are returned for a query that returns only scalar values
2787 as opposed to full identity-mapped entities.
2789 Calling :meth:`.one` results in an execution of the underlying query.
2791 .. seealso::
2793 :meth:`_query.Query.first`
2795 :meth:`_query.Query.one_or_none`
2797 :meth:`_engine.Result.one` - v2 comparable method.
2799 :meth:`_engine.Result.scalar_one` - v2 comparable method.
2801 """
2802 return self._iter().one() # type: ignore
2804 def scalar(self) -> Any:
2805 """Return the first element of the first result or None
2806 if no rows present. If multiple rows are returned,
2807 raises MultipleResultsFound.
2809 >>> session.query(Item).scalar()
2810 <Item>
2811 >>> session.query(Item.id).scalar()
2812 1
2813 >>> session.query(Item.id).filter(Item.id < 0).scalar()
2814 None
2815 >>> session.query(Item.id, Item.name).scalar()
2816 1
2817 >>> session.query(func.count(Parent.id)).scalar()
2818 20
2820 This results in an execution of the underlying query.
2822 .. seealso::
2824 :meth:`_engine.Result.scalar` - v2 comparable method.
2826 """
2827 # TODO: not sure why we can't use result.scalar() here
2828 try:
2829 ret = self.one()
2830 if not isinstance(ret, collections_abc.Sequence):
2831 return ret
2832 return ret[0]
2833 except sa_exc.NoResultFound:
2834 return None
2836 def __iter__(self) -> Iterator[_T]:
2837 result = self._iter()
2838 try:
2839 yield from result # type: ignore
2840 except GeneratorExit:
2841 # issue #8710 - direct iteration is not re-usable after
2842 # an iterable block is broken, so close the result
2843 result._soft_close()
2844 raise
2846 def _iter(self) -> Union[ScalarResult[_T], Result[_T]]:
2847 # new style execution.
2848 params = self._params
2850 statement = self._statement_20()
2851 result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
2852 statement,
2853 params,
2854 execution_options={"_sa_orm_load_options": self.load_options},
2855 )
2857 # legacy: automatically set scalars, unique
2858 if result._attributes.get("is_single_entity", False):
2859 result = cast("Result[_T]", result).scalars()
2861 if (
2862 result._attributes.get("filtered", False)
2863 and not self.load_options._yield_per
2864 ):
2865 result = result.unique()
2867 return result
2869 def __str__(self) -> str:
2870 statement = self._statement_20()
2872 try:
2873 bind = (
2874 self.session.get_bind(clause=statement)
2875 if self.session
2876 else None
2877 )
2878 except sa_exc.UnboundExecutionError:
2879 bind = None
2881 return str(statement.compile(bind))
2883 @property
2884 def column_descriptions(self) -> List[ORMColumnDescription]:
2885 """Return metadata about the columns which would be
2886 returned by this :class:`_query.Query`.
2888 Format is a list of dictionaries::
2890 user_alias = aliased(User, name='user2')
2891 q = sess.query(User, User.id, user_alias)
2893 # this expression:
2894 q.column_descriptions
2896 # would return:
2897 [
2898 {
2899 'name':'User',
2900 'type':User,
2901 'aliased':False,
2902 'expr':User,
2903 'entity': User
2904 },
2905 {
2906 'name':'id',
2907 'type':Integer(),
2908 'aliased':False,
2909 'expr':User.id,
2910 'entity': User
2911 },
2912 {
2913 'name':'user2',
2914 'type':User,
2915 'aliased':True,
2916 'expr':user_alias,
2917 'entity': user_alias
2918 }
2919 ]
2921 .. seealso::
2923 This API is available using :term:`2.0 style` queries as well,
2924 documented at:
2926 * :ref:`queryguide_inspection`
2928 * :attr:`.Select.column_descriptions`
2930 """
2932 return _column_descriptions(self, legacy=True)
2934 @util.deprecated(
2935 "2.0",
2936 "The :meth:`_orm.Query.instances` method is deprecated and will "
2937 "be removed in a future release. "
2938 "Use the Select.from_statement() method or aliased() construct in "
2939 "conjunction with Session.execute() instead.",
2940 )
2941 def instances(
2942 self,
2943 result_proxy: CursorResult[Any],
2944 context: Optional[QueryContext] = None,
2945 ) -> Any:
2946 """Return an ORM result given a :class:`_engine.CursorResult` and
2947 :class:`.QueryContext`.
2949 """
2950 if context is None:
2951 util.warn_deprecated(
2952 "Using the Query.instances() method without a context "
2953 "is deprecated and will be disallowed in a future release. "
2954 "Please make use of :meth:`_query.Query.from_statement` "
2955 "for linking ORM results to arbitrary select constructs.",
2956 version="1.4",
2957 )
2958 compile_state = self._compile_state(for_statement=False)
2960 context = QueryContext(
2961 compile_state,
2962 compile_state.statement,
2963 self._params,
2964 self.session,
2965 self.load_options,
2966 )
2968 result = loading.instances(result_proxy, context)
2970 # legacy: automatically set scalars, unique
2971 if result._attributes.get("is_single_entity", False):
2972 result = result.scalars() # type: ignore
2974 if result._attributes.get("filtered", False):
2975 result = result.unique()
2977 # TODO: isn't this supposed to be a list?
2978 return result
2980 @util.became_legacy_20(
2981 ":meth:`_orm.Query.merge_result`",
2982 alternative="The method is superseded by the "
2983 ":func:`_orm.merge_frozen_result` function.",
2984 enable_warnings=False, # warnings occur via loading.merge_result
2985 )
2986 def merge_result(
2987 self,
2988 iterator: Union[
2989 FrozenResult[Any], Iterable[Sequence[Any]], Iterable[object]
2990 ],
2991 load: bool = True,
2992 ) -> Union[FrozenResult[Any], Iterable[Any]]:
2993 """Merge a result into this :class:`_query.Query` object's Session.
2995 Given an iterator returned by a :class:`_query.Query`
2996 of the same structure
2997 as this one, return an identical iterator of results, with all mapped
2998 instances merged into the session using :meth:`.Session.merge`. This
2999 is an optimized method which will merge all mapped instances,
3000 preserving the structure of the result rows and unmapped columns with
3001 less method overhead than that of calling :meth:`.Session.merge`
3002 explicitly for each value.
3004 The structure of the results is determined based on the column list of
3005 this :class:`_query.Query` - if these do not correspond,
3006 unchecked errors
3007 will occur.
3009 The 'load' argument is the same as that of :meth:`.Session.merge`.
3011 For an example of how :meth:`_query.Query.merge_result` is used, see
3012 the source code for the example :ref:`examples_caching`, where
3013 :meth:`_query.Query.merge_result` is used to efficiently restore state
3014 from a cache back into a target :class:`.Session`.
3016 """
3018 return loading.merge_result(self, iterator, load)
3020 def exists(self) -> Exists:
3021 """A convenience method that turns a query into an EXISTS subquery
3022 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3024 e.g.::
3026 q = session.query(User).filter(User.name == 'fred')
3027 session.query(q.exists())
3029 Producing SQL similar to::
3031 SELECT EXISTS (
3032 SELECT 1 FROM users WHERE users.name = :name_1
3033 ) AS anon_1
3035 The EXISTS construct is usually used in the WHERE clause::
3037 session.query(User.id).filter(q.exists()).scalar()
3039 Note that some databases such as SQL Server don't allow an
3040 EXISTS expression to be present in the columns clause of a
3041 SELECT. To select a simple boolean value based on the exists
3042 as a WHERE, use :func:`.literal`::
3044 from sqlalchemy import literal
3046 session.query(literal(True)).filter(q.exists()).scalar()
3048 .. seealso::
3050 :meth:`_sql.Select.exists` - v2 comparable method.
3052 """
3054 # .add_columns() for the case that we are a query().select_from(X),
3055 # so that ".statement" can be produced (#2995) but also without
3056 # omitting the FROM clause from a query(X) (#2818);
3057 # .with_only_columns() after we have a core select() so that
3058 # we get just "SELECT 1" without any entities.
3060 inner = (
3061 self.enable_eagerloads(False)
3062 .add_columns(sql.literal_column("1"))
3063 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3064 ._get_select_statement_only()
3065 .with_only_columns(1)
3066 )
3068 ezero = self._entity_from_pre_ent_zero()
3069 if ezero is not None:
3070 inner = inner.select_from(ezero)
3072 return sql.exists(inner)
3074 def count(self) -> int:
3075 r"""Return a count of rows this the SQL formed by this :class:`Query`
3076 would return.
3078 This generates the SQL for this Query as follows::
3080 SELECT count(1) AS count_1 FROM (
3081 SELECT <rest of query follows...>
3082 ) AS anon_1
3084 The above SQL returns a single row, which is the aggregate value
3085 of the count function; the :meth:`_query.Query.count`
3086 method then returns
3087 that single integer value.
3089 .. warning::
3091 It is important to note that the value returned by
3092 count() is **not the same as the number of ORM objects that this
3093 Query would return from a method such as the .all() method**.
3094 The :class:`_query.Query` object,
3095 when asked to return full entities,
3096 will **deduplicate entries based on primary key**, meaning if the
3097 same primary key value would appear in the results more than once,
3098 only one object of that primary key would be present. This does
3099 not apply to a query that is against individual columns.
3101 .. seealso::
3103 :ref:`faq_query_deduplicating`
3105 For fine grained control over specific columns to count, to skip the
3106 usage of a subquery or otherwise control of the FROM clause, or to use
3107 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3108 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3110 from sqlalchemy import func
3112 # count User records, without
3113 # using a subquery.
3114 session.query(func.count(User.id))
3116 # return count of user "id" grouped
3117 # by "name"
3118 session.query(func.count(User.id)).\
3119 group_by(User.name)
3121 from sqlalchemy import distinct
3123 # count distinct "name" values
3124 session.query(func.count(distinct(User.name)))
3126 .. seealso::
3128 :ref:`migration_20_query_usage`
3130 """
3131 col = sql.func.count(sql.literal_column("*"))
3132 return ( # type: ignore
3133 self._legacy_from_self(col).enable_eagerloads(False).scalar()
3134 )
3136 def delete(
3137 self, synchronize_session: SynchronizeSessionArgument = "auto"
3138 ) -> int:
3139 r"""Perform a DELETE with an arbitrary WHERE clause.
3141 Deletes rows matched by this query from the database.
3143 E.g.::
3145 sess.query(User).filter(User.age == 25).\
3146 delete(synchronize_session=False)
3148 sess.query(User).filter(User.age == 25).\
3149 delete(synchronize_session='evaluate')
3151 .. warning::
3153 See the section :ref:`orm_expression_update_delete` for important
3154 caveats and warnings, including limitations when using bulk UPDATE
3155 and DELETE with mapper inheritance configurations.
3157 :param synchronize_session: chooses the strategy to update the
3158 attributes on objects in the session. See the section
3159 :ref:`orm_expression_update_delete` for a discussion of these
3160 strategies.
3162 :return: the count of rows matched as returned by the database's
3163 "row count" feature.
3165 .. seealso::
3167 :ref:`orm_expression_update_delete`
3169 """
3171 bulk_del = BulkDelete(self)
3172 if self.dispatch.before_compile_delete:
3173 for fn in self.dispatch.before_compile_delete:
3174 new_query = fn(bulk_del.query, bulk_del)
3175 if new_query is not None:
3176 bulk_del.query = new_query
3178 self = bulk_del.query
3180 delete_ = sql.delete(*self._raw_columns) # type: ignore
3181 delete_._where_criteria = self._where_criteria
3182 result: CursorResult[Any] = self.session.execute(
3183 delete_,
3184 self._params,
3185 execution_options=self._execution_options.union(
3186 {"synchronize_session": synchronize_session}
3187 ),
3188 )
3189 bulk_del.result = result # type: ignore
3190 self.session.dispatch.after_bulk_delete(bulk_del)
3191 result.close()
3193 return result.rowcount
3195 def update(
3196 self,
3197 values: Dict[_DMLColumnArgument, Any],
3198 synchronize_session: SynchronizeSessionArgument = "auto",
3199 update_args: Optional[Dict[Any, Any]] = None,
3200 ) -> int:
3201 r"""Perform an UPDATE with an arbitrary WHERE clause.
3203 Updates rows matched by this query in the database.
3205 E.g.::
3207 sess.query(User).filter(User.age == 25).\
3208 update({User.age: User.age - 10}, synchronize_session=False)
3210 sess.query(User).filter(User.age == 25).\
3211 update({"age": User.age - 10}, synchronize_session='evaluate')
3213 .. warning::
3215 See the section :ref:`orm_expression_update_delete` for important
3216 caveats and warnings, including limitations when using arbitrary
3217 UPDATE and DELETE with mapper inheritance configurations.
3219 :param values: a dictionary with attributes names, or alternatively
3220 mapped attributes or SQL expressions, as keys, and literal
3221 values or sql expressions as values. If :ref:`parameter-ordered
3222 mode <tutorial_parameter_ordered_updates>` is desired, the values can
3223 be passed as a list of 2-tuples; this requires that the
3224 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3225 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3226 as well.
3228 :param synchronize_session: chooses the strategy to update the
3229 attributes on objects in the session. See the section
3230 :ref:`orm_expression_update_delete` for a discussion of these
3231 strategies.
3233 :param update_args: Optional dictionary, if present will be passed
3234 to the underlying :func:`_expression.update`
3235 construct as the ``**kw`` for
3236 the object. May be used to pass dialect-specific arguments such
3237 as ``mysql_limit``, as well as other special arguments such as
3238 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3240 :return: the count of rows matched as returned by the database's
3241 "row count" feature.
3244 .. seealso::
3246 :ref:`orm_expression_update_delete`
3248 """
3250 update_args = update_args or {}
3252 bulk_ud = BulkUpdate(self, values, update_args)
3254 if self.dispatch.before_compile_update:
3255 for fn in self.dispatch.before_compile_update:
3256 new_query = fn(bulk_ud.query, bulk_ud)
3257 if new_query is not None:
3258 bulk_ud.query = new_query
3259 self = bulk_ud.query
3261 upd = sql.update(*self._raw_columns) # type: ignore
3263 ppo = update_args.pop("preserve_parameter_order", False)
3264 if ppo:
3265 upd = upd.ordered_values(*values) # type: ignore
3266 else:
3267 upd = upd.values(values)
3268 if update_args:
3269 upd = upd.with_dialect_options(**update_args)
3271 upd._where_criteria = self._where_criteria
3272 result: CursorResult[Any] = self.session.execute(
3273 upd,
3274 self._params,
3275 execution_options=self._execution_options.union(
3276 {"synchronize_session": synchronize_session}
3277 ),
3278 )
3279 bulk_ud.result = result # type: ignore
3280 self.session.dispatch.after_bulk_update(bulk_ud)
3281 result.close()
3282 return result.rowcount
3284 def _compile_state(
3285 self, for_statement: bool = False, **kw: Any
3286 ) -> ORMCompileState:
3287 """Create an out-of-compiler ORMCompileState object.
3289 The ORMCompileState object is normally created directly as a result
3290 of the SQLCompiler.process() method being handed a Select()
3291 or FromStatement() object that uses the "orm" plugin. This method
3292 provides a means of creating this ORMCompileState object directly
3293 without using the compiler.
3295 This method is used only for deprecated cases, which include
3296 the .from_self() method for a Query that has multiple levels
3297 of .from_self() in use, as well as the instances() method. It is
3298 also used within the test suite to generate ORMCompileState objects
3299 for test purposes.
3301 """
3303 stmt = self._statement_20(for_statement=for_statement, **kw)
3304 assert for_statement == stmt._compile_options._for_statement
3306 # this chooses between ORMFromStatementCompileState and
3307 # ORMSelectCompileState. We could also base this on
3308 # query._statement is not None as we have the ORM Query here
3309 # however this is the more general path.
3310 compile_state_cls = cast(
3311 ORMCompileState,
3312 ORMCompileState._get_plugin_class_for_plugin(stmt, "orm"),
3313 )
3315 return compile_state_cls.create_for_statement(stmt, None)
3317 def _compile_context(self, for_statement: bool = False) -> QueryContext:
3318 compile_state = self._compile_state(for_statement=for_statement)
3319 context = QueryContext(
3320 compile_state,
3321 compile_state.statement,
3322 self._params,
3323 self.session,
3324 self.load_options,
3325 )
3327 return context
3330class AliasOption(interfaces.LoaderOption):
3331 inherit_cache = False
3333 @util.deprecated(
3334 "1.4",
3335 "The :class:`.AliasOption` object is not necessary "
3336 "for entities to be matched up to a query that is established "
3337 "via :meth:`.Query.from_statement` and now does nothing.",
3338 )
3339 def __init__(self, alias: Union[Alias, Subquery]):
3340 r"""Return a :class:`.MapperOption` that will indicate to the
3341 :class:`_query.Query`
3342 that the main table has been aliased.
3344 """
3346 def process_compile_state(self, compile_state: ORMCompileState) -> None:
3347 pass
3350class BulkUD:
3351 """State used for the orm.Query version of update() / delete().
3353 This object is now specific to Query only.
3355 """
3357 def __init__(self, query: Query[Any]):
3358 self.query = query.enable_eagerloads(False)
3359 self._validate_query_state()
3360 self.mapper = self.query._entity_from_pre_ent_zero()
3362 def _validate_query_state(self) -> None:
3363 for attr, methname, notset, op in (
3364 ("_limit_clause", "limit()", None, operator.is_),
3365 ("_offset_clause", "offset()", None, operator.is_),
3366 ("_order_by_clauses", "order_by()", (), operator.eq),
3367 ("_group_by_clauses", "group_by()", (), operator.eq),
3368 ("_distinct", "distinct()", False, operator.is_),
3369 (
3370 "_from_obj",
3371 "join(), outerjoin(), select_from(), or from_self()",
3372 (),
3373 operator.eq,
3374 ),
3375 (
3376 "_setup_joins",
3377 "join(), outerjoin(), select_from(), or from_self()",
3378 (),
3379 operator.eq,
3380 ),
3381 ):
3382 if not op(getattr(self.query, attr), notset):
3383 raise sa_exc.InvalidRequestError(
3384 "Can't call Query.update() or Query.delete() "
3385 "when %s has been called" % (methname,)
3386 )
3388 @property
3389 def session(self) -> Session:
3390 return self.query.session
3393class BulkUpdate(BulkUD):
3394 """BulkUD which handles UPDATEs."""
3396 def __init__(
3397 self,
3398 query: Query[Any],
3399 values: Dict[_DMLColumnArgument, Any],
3400 update_kwargs: Optional[Dict[Any, Any]],
3401 ):
3402 super().__init__(query)
3403 self.values = values
3404 self.update_kwargs = update_kwargs
3407class BulkDelete(BulkUD):
3408 """BulkUD which handles DELETEs."""
3411class RowReturningQuery(Query[Row[Unpack[_Ts]]]):
3412 if TYPE_CHECKING:
3414 def tuples(self) -> Query[Tuple[Unpack[_Ts]]]: # type: ignore
3415 ...