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( # type: ignore[overload-overlap]
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 driver_column_names: bool = ...,
1732 insertmanyvalues_page_size: int = ...,
1733 schema_translate_map: Optional[SchemaTranslateMapType] = ...,
1734 populate_existing: bool = False,
1735 autoflush: bool = False,
1736 preserve_rowcount: bool = False,
1737 **opt: Any,
1738 ) -> Self: ...
1740 @overload
1741 def execution_options(self, **opt: Any) -> Self: ...
1743 @_generative
1744 def execution_options(self, **kwargs: Any) -> Self:
1745 """Set non-SQL options which take effect during execution.
1747 Options allowed here include all of those accepted by
1748 :meth:`_engine.Connection.execution_options`, as well as a series
1749 of ORM specific options:
1751 ``populate_existing=True`` - equivalent to using
1752 :meth:`_orm.Query.populate_existing`
1754 ``autoflush=True|False`` - equivalent to using
1755 :meth:`_orm.Query.autoflush`
1757 ``yield_per=<value>`` - equivalent to using
1758 :meth:`_orm.Query.yield_per`
1760 Note that the ``stream_results`` execution option is enabled
1761 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1762 method or execution option is used.
1764 .. versionadded:: 1.4 - added ORM options to
1765 :meth:`_orm.Query.execution_options`
1767 The execution options may also be specified on a per execution basis
1768 when using :term:`2.0 style` queries via the
1769 :paramref:`_orm.Session.execution_options` parameter.
1771 .. warning:: The
1772 :paramref:`_engine.Connection.execution_options.stream_results`
1773 parameter should not be used at the level of individual ORM
1774 statement executions, as the :class:`_orm.Session` will not track
1775 objects from different schema translate maps within a single
1776 session. For multiple schema translate maps within the scope of a
1777 single :class:`_orm.Session`, see :ref:`examples_sharding`.
1780 .. seealso::
1782 :ref:`engine_stream_results`
1784 :meth:`_query.Query.get_execution_options`
1786 :meth:`_sql.Select.execution_options` - v2 equivalent method.
1788 """
1789 self._execution_options = self._execution_options.union(kwargs)
1790 return self
1792 @_generative
1793 def with_for_update(
1794 self,
1795 *,
1796 nowait: bool = False,
1797 read: bool = False,
1798 of: Optional[_ForUpdateOfArgument] = None,
1799 skip_locked: bool = False,
1800 key_share: bool = False,
1801 ) -> Self:
1802 """return a new :class:`_query.Query`
1803 with the specified options for the
1804 ``FOR UPDATE`` clause.
1806 The behavior of this method is identical to that of
1807 :meth:`_expression.GenerativeSelect.with_for_update`.
1808 When called with no arguments,
1809 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1810 appended. When additional arguments are specified, backend-specific
1811 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1812 can take effect.
1814 E.g.::
1816 q = sess.query(User).populate_existing().with_for_update(nowait=True, of=User)
1818 The above query on a PostgreSQL backend will render like::
1820 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1822 .. warning::
1824 Using ``with_for_update`` in the context of eager loading
1825 relationships is not officially supported or recommended by
1826 SQLAlchemy and may not work with certain queries on various
1827 database backends. When ``with_for_update`` is successfully used
1828 with a query that involves :func:`_orm.joinedload`, SQLAlchemy will
1829 attempt to emit SQL that locks all involved tables.
1831 .. note:: It is generally a good idea to combine the use of the
1832 :meth:`_orm.Query.populate_existing` method when using the
1833 :meth:`_orm.Query.with_for_update` method. The purpose of
1834 :meth:`_orm.Query.populate_existing` is to force all the data read
1835 from the SELECT to be populated into the ORM objects returned,
1836 even if these objects are already in the :term:`identity map`.
1838 .. seealso::
1840 :meth:`_expression.GenerativeSelect.with_for_update`
1841 - Core level method with
1842 full argument and behavioral description.
1844 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1845 objects already loaded in the identity map.
1847 """ # noqa: E501
1849 self._for_update_arg = ForUpdateArg(
1850 read=read,
1851 nowait=nowait,
1852 of=of,
1853 skip_locked=skip_locked,
1854 key_share=key_share,
1855 )
1856 return self
1858 @_generative
1859 def params(
1860 self, __params: Optional[Dict[str, Any]] = None, /, **kw: Any
1861 ) -> Self:
1862 r"""Add values for bind parameters which may have been
1863 specified in filter().
1865 Parameters may be specified using \**kwargs, or optionally a single
1866 dictionary as the first positional argument. The reason for both is
1867 that \**kwargs is convenient, however some parameter dictionaries
1868 contain unicode keys in which case \**kwargs cannot be used.
1870 """
1871 if __params:
1872 kw.update(__params)
1873 self._params = self._params.union(kw)
1874 return self
1876 def where(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1877 """A synonym for :meth:`.Query.filter`.
1879 .. versionadded:: 1.4
1881 .. seealso::
1883 :meth:`_sql.Select.where` - v2 equivalent method.
1885 """
1886 return self.filter(*criterion)
1888 @_generative
1889 @_assertions(_no_statement_condition, _no_limit_offset)
1890 def filter(self, *criterion: _ColumnExpressionArgument[bool]) -> Self:
1891 r"""Apply the given filtering criterion to a copy
1892 of this :class:`_query.Query`, using SQL expressions.
1894 e.g.::
1896 session.query(MyClass).filter(MyClass.name == 'some name')
1898 Multiple criteria may be specified as comma separated; the effect
1899 is that they will be joined together using the :func:`.and_`
1900 function::
1902 session.query(MyClass).\
1903 filter(MyClass.name == 'some name', MyClass.id > 5)
1905 The criterion is any SQL expression object applicable to the
1906 WHERE clause of a select. String expressions are coerced
1907 into SQL expression constructs via the :func:`_expression.text`
1908 construct.
1910 .. seealso::
1912 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1914 :meth:`_sql.Select.where` - v2 equivalent method.
1916 """
1917 for crit in list(criterion):
1918 crit = coercions.expect(
1919 roles.WhereHavingRole, crit, apply_propagate_attrs=self
1920 )
1922 self._where_criteria += (crit,)
1923 return self
1925 @util.memoized_property
1926 def _last_joined_entity(
1927 self,
1928 ) -> Optional[Union[_InternalEntityType[Any], _JoinTargetElement]]:
1929 if self._setup_joins:
1930 return _determine_last_joined_entity(
1931 self._setup_joins,
1932 )
1933 else:
1934 return None
1936 def _filter_by_zero(self) -> Any:
1937 """for the filter_by() method, return the target entity for which
1938 we will attempt to derive an expression from based on string name.
1940 """
1942 if self._setup_joins:
1943 _last_joined_entity = self._last_joined_entity
1944 if _last_joined_entity is not None:
1945 return _last_joined_entity
1947 # discussion related to #7239
1948 # special check determines if we should try to derive attributes
1949 # for filter_by() from the "from object", i.e., if the user
1950 # called query.select_from(some selectable).filter_by(some_attr=value).
1951 # We don't want to do that in the case that methods like
1952 # from_self(), select_entity_from(), or a set op like union() were
1953 # called; while these methods also place a
1954 # selectable in the _from_obj collection, they also set up
1955 # the _set_base_alias boolean which turns on the whole "adapt the
1956 # entity to this selectable" thing, meaning the query still continues
1957 # to construct itself in terms of the lead entity that was passed
1958 # to query(), e.g. query(User).from_self() is still in terms of User,
1959 # and not the subquery that from_self() created. This feature of
1960 # "implicitly adapt all occurrences of entity X to some arbitrary
1961 # subquery" is the main thing I am trying to do away with in 2.0 as
1962 # users should now used aliased() for that, but I can't entirely get
1963 # rid of it due to query.union() and other set ops relying upon it.
1964 #
1965 # compare this to the base Select()._filter_by_zero() which can
1966 # just return self._from_obj[0] if present, because there is no
1967 # "_set_base_alias" feature.
1968 #
1969 # IOW, this conditional essentially detects if
1970 # "select_from(some_selectable)" has been called, as opposed to
1971 # "select_entity_from()", "from_self()"
1972 # or "union() / some_set_op()".
1973 if self._from_obj and not self._compile_options._set_base_alias:
1974 return self._from_obj[0]
1976 return self._raw_columns[0]
1978 def filter_by(self, **kwargs: Any) -> Self:
1979 r"""Apply the given filtering criterion to a copy
1980 of this :class:`_query.Query`, using keyword expressions.
1982 e.g.::
1984 session.query(MyClass).filter_by(name = 'some name')
1986 Multiple criteria may be specified as comma separated; the effect
1987 is that they will be joined together using the :func:`.and_`
1988 function::
1990 session.query(MyClass).\
1991 filter_by(name = 'some name', id = 5)
1993 The keyword expressions are extracted from the primary
1994 entity of the query, or the last entity that was the
1995 target of a call to :meth:`_query.Query.join`.
1997 .. seealso::
1999 :meth:`_query.Query.filter` - filter on SQL expressions.
2001 :meth:`_sql.Select.filter_by` - v2 comparable method.
2003 """
2004 from_entity = self._filter_by_zero()
2006 clauses = [
2007 _entity_namespace_key(from_entity, key) == value
2008 for key, value in kwargs.items()
2009 ]
2010 return self.filter(*clauses)
2012 @_generative
2013 def order_by(
2014 self,
2015 __first: Union[
2016 Literal[None, False, _NoArg.NO_ARG],
2017 _ColumnExpressionOrStrLabelArgument[Any],
2018 ] = _NoArg.NO_ARG,
2019 /,
2020 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2021 ) -> Self:
2022 """Apply one or more ORDER BY criteria to the query and return
2023 the newly resulting :class:`_query.Query`.
2025 e.g.::
2027 q = session.query(Entity).order_by(Entity.id, Entity.name)
2029 Calling this method multiple times is equivalent to calling it once
2030 with all the clauses concatenated. All existing ORDER BY criteria may
2031 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
2032 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
2034 # will erase all ORDER BY and ORDER BY new_col alone
2035 q = q.order_by(None).order_by(new_col)
2037 .. seealso::
2039 These sections describe ORDER BY in terms of :term:`2.0 style`
2040 invocation but apply to :class:`_orm.Query` as well:
2042 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
2044 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2046 :meth:`_sql.Select.order_by` - v2 equivalent method.
2048 """
2050 for assertion in (self._no_statement_condition, self._no_limit_offset):
2051 assertion("order_by")
2053 if not clauses and (__first is None or __first is False):
2054 self._order_by_clauses = ()
2055 elif __first is not _NoArg.NO_ARG:
2056 criterion = tuple(
2057 coercions.expect(roles.OrderByRole, clause)
2058 for clause in (__first,) + clauses
2059 )
2060 self._order_by_clauses += criterion
2062 return self
2064 @_generative
2065 def group_by(
2066 self,
2067 __first: Union[
2068 Literal[None, False, _NoArg.NO_ARG],
2069 _ColumnExpressionOrStrLabelArgument[Any],
2070 ] = _NoArg.NO_ARG,
2071 /,
2072 *clauses: _ColumnExpressionOrStrLabelArgument[Any],
2073 ) -> Self:
2074 """Apply one or more GROUP BY criterion to the query and return
2075 the newly resulting :class:`_query.Query`.
2077 All existing GROUP BY settings can be suppressed by
2078 passing ``None`` - this will suppress any GROUP BY configured
2079 on mappers as well.
2081 .. seealso::
2083 These sections describe GROUP BY in terms of :term:`2.0 style`
2084 invocation but apply to :class:`_orm.Query` as well:
2086 :ref:`tutorial_group_by_w_aggregates` - in the
2087 :ref:`unified_tutorial`
2089 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
2091 :meth:`_sql.Select.group_by` - v2 equivalent method.
2093 """
2095 for assertion in (self._no_statement_condition, self._no_limit_offset):
2096 assertion("group_by")
2098 if not clauses and (__first is None or __first is False):
2099 self._group_by_clauses = ()
2100 elif __first is not _NoArg.NO_ARG:
2101 criterion = tuple(
2102 coercions.expect(roles.GroupByRole, clause)
2103 for clause in (__first,) + clauses
2104 )
2105 self._group_by_clauses += criterion
2106 return self
2108 @_generative
2109 @_assertions(_no_statement_condition, _no_limit_offset)
2110 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:
2111 r"""Apply a HAVING criterion to the query and return the
2112 newly resulting :class:`_query.Query`.
2114 :meth:`_query.Query.having` is used in conjunction with
2115 :meth:`_query.Query.group_by`.
2117 HAVING criterion makes it possible to use filters on aggregate
2118 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
2120 q = session.query(User.id).\
2121 join(User.addresses).\
2122 group_by(User.id).\
2123 having(func.count(Address.id) > 2)
2125 .. seealso::
2127 :meth:`_sql.Select.having` - v2 equivalent method.
2129 """
2131 for criterion in having:
2132 having_criteria = coercions.expect(
2133 roles.WhereHavingRole, criterion
2134 )
2135 self._having_criteria += (having_criteria,)
2136 return self
2138 def _set_op(self, expr_fn: Any, *q: Query[Any]) -> Self:
2139 list_of_queries = (self,) + q
2140 return self._from_selectable(expr_fn(*(list_of_queries)).subquery())
2142 def union(self, *q: Query[Any]) -> Self:
2143 """Produce a UNION of this Query against one or more queries.
2145 e.g.::
2147 q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
2148 q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
2150 q3 = q1.union(q2)
2152 The method accepts multiple Query objects so as to control
2153 the level of nesting. A series of ``union()`` calls such as::
2155 x.union(y).union(z).all()
2157 will nest on each ``union()``, and produces::
2159 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
2160 SELECT * FROM y) UNION SELECT * FROM Z)
2162 Whereas::
2164 x.union(y, z).all()
2166 produces::
2168 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
2169 SELECT * FROM Z)
2171 Note that many database backends do not allow ORDER BY to
2172 be rendered on a query called within UNION, EXCEPT, etc.
2173 To disable all ORDER BY clauses including those configured
2174 on mappers, issue ``query.order_by(None)`` - the resulting
2175 :class:`_query.Query` object will not render ORDER BY within
2176 its SELECT statement.
2178 .. seealso::
2180 :meth:`_sql.Select.union` - v2 equivalent method.
2182 """
2183 return self._set_op(expression.union, *q)
2185 def union_all(self, *q: Query[Any]) -> Self:
2186 """Produce a UNION ALL of this Query against one or more queries.
2188 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2189 that method for usage examples.
2191 .. seealso::
2193 :meth:`_sql.Select.union_all` - v2 equivalent method.
2195 """
2196 return self._set_op(expression.union_all, *q)
2198 def intersect(self, *q: Query[Any]) -> Self:
2199 """Produce an INTERSECT of this Query against one or more queries.
2201 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2202 that method for usage examples.
2204 .. seealso::
2206 :meth:`_sql.Select.intersect` - v2 equivalent method.
2208 """
2209 return self._set_op(expression.intersect, *q)
2211 def intersect_all(self, *q: Query[Any]) -> Self:
2212 """Produce an INTERSECT ALL of this Query against one or more queries.
2214 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2215 that method for usage examples.
2217 .. seealso::
2219 :meth:`_sql.Select.intersect_all` - v2 equivalent method.
2221 """
2222 return self._set_op(expression.intersect_all, *q)
2224 def except_(self, *q: Query[Any]) -> Self:
2225 """Produce an EXCEPT of this Query against one or more queries.
2227 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2228 that method for usage examples.
2230 .. seealso::
2232 :meth:`_sql.Select.except_` - v2 equivalent method.
2234 """
2235 return self._set_op(expression.except_, *q)
2237 def except_all(self, *q: Query[Any]) -> Self:
2238 """Produce an EXCEPT ALL of this Query against one or more queries.
2240 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2241 that method for usage examples.
2243 .. seealso::
2245 :meth:`_sql.Select.except_all` - v2 equivalent method.
2247 """
2248 return self._set_op(expression.except_all, *q)
2250 @_generative
2251 @_assertions(_no_statement_condition, _no_limit_offset)
2252 def join(
2253 self,
2254 target: _JoinTargetArgument,
2255 onclause: Optional[_OnClauseArgument] = None,
2256 *,
2257 isouter: bool = False,
2258 full: bool = False,
2259 ) -> Self:
2260 r"""Create a SQL JOIN against this :class:`_query.Query`
2261 object's criterion
2262 and apply generatively, returning the newly resulting
2263 :class:`_query.Query`.
2265 **Simple Relationship Joins**
2267 Consider a mapping between two classes ``User`` and ``Address``,
2268 with a relationship ``User.addresses`` representing a collection
2269 of ``Address`` objects associated with each ``User``. The most
2270 common usage of :meth:`_query.Query.join`
2271 is to create a JOIN along this
2272 relationship, using the ``User.addresses`` attribute as an indicator
2273 for how this should occur::
2275 q = session.query(User).join(User.addresses)
2277 Where above, the call to :meth:`_query.Query.join` along
2278 ``User.addresses`` will result in SQL approximately equivalent to::
2280 SELECT user.id, user.name
2281 FROM user JOIN address ON user.id = address.user_id
2283 In the above example we refer to ``User.addresses`` as passed to
2284 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2285 how the "ON" portion of the JOIN should be constructed.
2287 To construct a chain of joins, multiple :meth:`_query.Query.join`
2288 calls may be used. The relationship-bound attribute implies both
2289 the left and right side of the join at once::
2291 q = session.query(User).\
2292 join(User.orders).\
2293 join(Order.items).\
2294 join(Item.keywords)
2296 .. note:: as seen in the above example, **the order in which each
2297 call to the join() method occurs is important**. Query would not,
2298 for example, know how to join correctly if we were to specify
2299 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2300 such a case, depending on the arguments passed, it may raise an
2301 error that it doesn't know how to join, or it may produce invalid
2302 SQL in which case the database will raise an error. In correct
2303 practice, the
2304 :meth:`_query.Query.join` method is invoked in such a way that lines
2305 up with how we would want the JOIN clauses in SQL to be
2306 rendered, and each call should represent a clear link from what
2307 precedes it.
2309 **Joins to a Target Entity or Selectable**
2311 A second form of :meth:`_query.Query.join` allows any mapped entity or
2312 core selectable construct as a target. In this usage,
2313 :meth:`_query.Query.join` will attempt to create a JOIN along the
2314 natural foreign key relationship between two entities::
2316 q = session.query(User).join(Address)
2318 In the above calling form, :meth:`_query.Query.join` is called upon to
2319 create the "on clause" automatically for us. This calling form will
2320 ultimately raise an error if either there are no foreign keys between
2321 the two entities, or if there are multiple foreign key linkages between
2322 the target entity and the entity or entities already present on the
2323 left side such that creating a join requires more information. Note
2324 that when indicating a join to a target without any ON clause, ORM
2325 configured relationships are not taken into account.
2327 **Joins to a Target with an ON Clause**
2329 The third calling form allows both the target entity as well
2330 as the ON clause to be passed explicitly. A example that includes
2331 a SQL expression as the ON clause is as follows::
2333 q = session.query(User).join(Address, User.id==Address.user_id)
2335 The above form may also use a relationship-bound attribute as the
2336 ON clause as well::
2338 q = session.query(User).join(Address, User.addresses)
2340 The above syntax can be useful for the case where we wish
2341 to join to an alias of a particular target entity. If we wanted
2342 to join to ``Address`` twice, it could be achieved using two
2343 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2345 a1 = aliased(Address)
2346 a2 = aliased(Address)
2348 q = session.query(User).\
2349 join(a1, User.addresses).\
2350 join(a2, User.addresses).\
2351 filter(a1.email_address=='ed@foo.com').\
2352 filter(a2.email_address=='ed@bar.com')
2354 The relationship-bound calling form can also specify a target entity
2355 using the :meth:`_orm.PropComparator.of_type` method; a query
2356 equivalent to the one above would be::
2358 a1 = aliased(Address)
2359 a2 = aliased(Address)
2361 q = session.query(User).\
2362 join(User.addresses.of_type(a1)).\
2363 join(User.addresses.of_type(a2)).\
2364 filter(a1.email_address == 'ed@foo.com').\
2365 filter(a2.email_address == 'ed@bar.com')
2367 **Augmenting Built-in ON Clauses**
2369 As a substitute for providing a full custom ON condition for an
2370 existing relationship, the :meth:`_orm.PropComparator.and_` function
2371 may be applied to a relationship attribute to augment additional
2372 criteria into the ON clause; the additional criteria will be combined
2373 with the default criteria using AND::
2375 q = session.query(User).join(
2376 User.addresses.and_(Address.email_address != 'foo@bar.com')
2377 )
2379 .. versionadded:: 1.4
2381 **Joining to Tables and Subqueries**
2384 The target of a join may also be any table or SELECT statement,
2385 which may be related to a target entity or not. Use the
2386 appropriate ``.subquery()`` method in order to make a subquery
2387 out of a query::
2389 subq = session.query(Address).\
2390 filter(Address.email_address == 'ed@foo.com').\
2391 subquery()
2394 q = session.query(User).join(
2395 subq, User.id == subq.c.user_id
2396 )
2398 Joining to a subquery in terms of a specific relationship and/or
2399 target entity may be achieved by linking the subquery to the
2400 entity using :func:`_orm.aliased`::
2402 subq = session.query(Address).\
2403 filter(Address.email_address == 'ed@foo.com').\
2404 subquery()
2406 address_subq = aliased(Address, subq)
2408 q = session.query(User).join(
2409 User.addresses.of_type(address_subq)
2410 )
2413 **Controlling what to Join From**
2415 In cases where the left side of the current state of
2416 :class:`_query.Query` is not in line with what we want to join from,
2417 the :meth:`_query.Query.select_from` method may be used::
2419 q = session.query(Address).select_from(User).\
2420 join(User.addresses).\
2421 filter(User.name == 'ed')
2423 Which will produce SQL similar to::
2425 SELECT address.* FROM user
2426 JOIN address ON user.id=address.user_id
2427 WHERE user.name = :name_1
2429 .. seealso::
2431 :meth:`_sql.Select.join` - v2 equivalent method.
2433 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2434 the props collection in modern use should be considered to be a one
2435 or two argument form, either as a single "target" entity or ORM
2436 attribute-bound relationship, or as a target entity plus an "on
2437 clause" which may be a SQL expression or ORM attribute-bound
2438 relationship.
2440 :param isouter=False: If True, the join used will be a left outer join,
2441 just as if the :meth:`_query.Query.outerjoin` method were called.
2443 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2445 """
2447 join_target = coercions.expect(
2448 roles.JoinTargetRole,
2449 target,
2450 apply_propagate_attrs=self,
2451 legacy=True,
2452 )
2453 if onclause is not None:
2454 onclause_element = coercions.expect(
2455 roles.OnClauseRole, onclause, legacy=True
2456 )
2457 else:
2458 onclause_element = None
2460 self._setup_joins += (
2461 (
2462 join_target,
2463 onclause_element,
2464 None,
2465 {
2466 "isouter": isouter,
2467 "full": full,
2468 },
2469 ),
2470 )
2472 self.__dict__.pop("_last_joined_entity", None)
2473 return self
2475 def outerjoin(
2476 self,
2477 target: _JoinTargetArgument,
2478 onclause: Optional[_OnClauseArgument] = None,
2479 *,
2480 full: bool = False,
2481 ) -> Self:
2482 """Create a left outer join against this ``Query`` object's criterion
2483 and apply generatively, returning the newly resulting ``Query``.
2485 Usage is the same as the ``join()`` method.
2487 .. seealso::
2489 :meth:`_sql.Select.outerjoin` - v2 equivalent method.
2491 """
2492 return self.join(target, onclause=onclause, isouter=True, full=full)
2494 @_generative
2495 @_assertions(_no_statement_condition)
2496 def reset_joinpoint(self) -> Self:
2497 """Return a new :class:`.Query`, where the "join point" has
2498 been reset back to the base FROM entities of the query.
2500 This method is usually used in conjunction with the
2501 ``aliased=True`` feature of the :meth:`~.Query.join`
2502 method. See the example in :meth:`~.Query.join` for how
2503 this is used.
2505 """
2506 self._last_joined_entity = None
2508 return self
2510 @_generative
2511 @_assertions(_no_clauseelement_condition)
2512 def select_from(self, *from_obj: _FromClauseArgument) -> Self:
2513 r"""Set the FROM clause of this :class:`.Query` explicitly.
2515 :meth:`.Query.select_from` is often used in conjunction with
2516 :meth:`.Query.join` in order to control which entity is selected
2517 from on the "left" side of the join.
2519 The entity or selectable object here effectively replaces the
2520 "left edge" of any calls to :meth:`~.Query.join`, when no
2521 joinpoint is otherwise established - usually, the default "join
2522 point" is the leftmost entity in the :class:`~.Query` object's
2523 list of entities to be selected.
2525 A typical example::
2527 q = session.query(Address).select_from(User).\
2528 join(User.addresses).\
2529 filter(User.name == 'ed')
2531 Which produces SQL equivalent to::
2533 SELECT address.* FROM user
2534 JOIN address ON user.id=address.user_id
2535 WHERE user.name = :name_1
2537 :param \*from_obj: collection of one or more entities to apply
2538 to the FROM clause. Entities can be mapped classes,
2539 :class:`.AliasedClass` objects, :class:`.Mapper` objects
2540 as well as core :class:`.FromClause` elements like subqueries.
2542 .. seealso::
2544 :meth:`~.Query.join`
2546 :meth:`.Query.select_entity_from`
2548 :meth:`_sql.Select.select_from` - v2 equivalent method.
2550 """
2552 self._set_select_from(from_obj, False)
2553 return self
2555 def __getitem__(self, item: Any) -> Any:
2556 return orm_util._getitem(
2557 self,
2558 item,
2559 )
2561 @_generative
2562 @_assertions(_no_statement_condition)
2563 def slice(
2564 self,
2565 start: int,
2566 stop: int,
2567 ) -> Self:
2568 """Computes the "slice" of the :class:`_query.Query` represented by
2569 the given indices and returns the resulting :class:`_query.Query`.
2571 The start and stop indices behave like the argument to Python's
2572 built-in :func:`range` function. This method provides an
2573 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
2574 query.
2576 For example, ::
2578 session.query(User).order_by(User.id).slice(1, 3)
2580 renders as
2582 .. sourcecode:: sql
2584 SELECT users.id AS users_id,
2585 users.name AS users_name
2586 FROM users ORDER BY users.id
2587 LIMIT ? OFFSET ?
2588 (2, 1)
2590 .. seealso::
2592 :meth:`_query.Query.limit`
2594 :meth:`_query.Query.offset`
2596 :meth:`_sql.Select.slice` - v2 equivalent method.
2598 """
2600 self._limit_clause, self._offset_clause = sql_util._make_slice(
2601 self._limit_clause, self._offset_clause, start, stop
2602 )
2603 return self
2605 @_generative
2606 @_assertions(_no_statement_condition)
2607 def limit(self, limit: _LimitOffsetType) -> Self:
2608 """Apply a ``LIMIT`` to the query and return the newly resulting
2609 ``Query``.
2611 .. seealso::
2613 :meth:`_sql.Select.limit` - v2 equivalent method.
2615 """
2616 self._limit_clause = sql_util._offset_or_limit_clause(limit)
2617 return self
2619 @_generative
2620 @_assertions(_no_statement_condition)
2621 def offset(self, offset: _LimitOffsetType) -> Self:
2622 """Apply an ``OFFSET`` to the query and return the newly resulting
2623 ``Query``.
2625 .. seealso::
2627 :meth:`_sql.Select.offset` - v2 equivalent method.
2628 """
2629 self._offset_clause = sql_util._offset_or_limit_clause(offset)
2630 return self
2632 @_generative
2633 @_assertions(_no_statement_condition)
2634 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:
2635 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
2636 ``Query``.
2639 .. note::
2641 The ORM-level :meth:`.distinct` call includes logic that will
2642 automatically add columns from the ORDER BY of the query to the
2643 columns clause of the SELECT statement, to satisfy the common need
2644 of the database backend that ORDER BY columns be part of the SELECT
2645 list when DISTINCT is used. These columns *are not* added to the
2646 list of columns actually fetched by the :class:`_query.Query`,
2647 however,
2648 so would not affect results. The columns are passed through when
2649 using the :attr:`_query.Query.statement` accessor, however.
2651 .. deprecated:: 2.0 This logic is deprecated and will be removed
2652 in SQLAlchemy 2.0. See :ref:`migration_20_query_distinct`
2653 for a description of this use case in 2.0.
2655 .. seealso::
2657 :meth:`_sql.Select.distinct` - v2 equivalent method.
2659 :param \*expr: optional column expressions. When present,
2660 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2661 construct.
2663 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
2664 and will raise :class:`_exc.CompileError` in a future version.
2666 """
2667 if expr:
2668 self._distinct = True
2669 self._distinct_on = self._distinct_on + tuple(
2670 coercions.expect(roles.ByOfRole, e) for e in expr
2671 )
2672 else:
2673 self._distinct = True
2674 return self
2676 def all(self) -> List[_T]:
2677 """Return the results represented by this :class:`_query.Query`
2678 as a list.
2680 This results in an execution of the underlying SQL statement.
2682 .. warning:: The :class:`_query.Query` object,
2683 when asked to return either
2684 a sequence or iterator that consists of full ORM-mapped entities,
2685 will **deduplicate entries based on primary key**. See the FAQ for
2686 more details.
2688 .. seealso::
2690 :ref:`faq_query_deduplicating`
2692 .. seealso::
2694 :meth:`_engine.Result.all` - v2 comparable method.
2696 :meth:`_engine.Result.scalars` - v2 comparable method.
2697 """
2698 return self._iter().all() # type: ignore
2700 @_generative
2701 @_assertions(_no_clauseelement_condition)
2702 def from_statement(self, statement: ExecutableReturnsRows) -> Self:
2703 """Execute the given SELECT statement and return results.
2705 This method bypasses all internal statement compilation, and the
2706 statement is executed without modification.
2708 The statement is typically either a :func:`_expression.text`
2709 or :func:`_expression.select` construct, and should return the set
2710 of columns
2711 appropriate to the entity class represented by this
2712 :class:`_query.Query`.
2714 .. seealso::
2716 :meth:`_sql.Select.from_statement` - v2 comparable method.
2718 """
2719 statement = coercions.expect(
2720 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2721 )
2722 self._statement = statement
2723 return self
2725 def first(self) -> Optional[_T]:
2726 """Return the first result of this ``Query`` or
2727 None if the result doesn't contain any row.
2729 first() applies a limit of one within the generated SQL, so that
2730 only one primary entity row is generated on the server side
2731 (note this may consist of multiple result rows if join-loaded
2732 collections are present).
2734 Calling :meth:`_query.Query.first`
2735 results in an execution of the underlying
2736 query.
2738 .. seealso::
2740 :meth:`_query.Query.one`
2742 :meth:`_query.Query.one_or_none`
2744 :meth:`_engine.Result.first` - v2 comparable method.
2746 :meth:`_engine.Result.scalars` - v2 comparable method.
2748 """
2749 # replicates limit(1) behavior
2750 if self._statement is not None:
2751 return self._iter().first() # type: ignore
2752 else:
2753 return self.limit(1)._iter().first() # type: ignore
2755 def one_or_none(self) -> Optional[_T]:
2756 """Return at most one result or raise an exception.
2758 Returns ``None`` if the query selects
2759 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2760 if multiple object identities are returned, or if multiple
2761 rows are returned for a query that returns only scalar values
2762 as opposed to full identity-mapped entities.
2764 Calling :meth:`_query.Query.one_or_none`
2765 results in an execution of the
2766 underlying query.
2768 .. seealso::
2770 :meth:`_query.Query.first`
2772 :meth:`_query.Query.one`
2774 :meth:`_engine.Result.one_or_none` - v2 comparable method.
2776 :meth:`_engine.Result.scalar_one_or_none` - v2 comparable method.
2778 """
2779 return self._iter().one_or_none() # type: ignore
2781 def one(self) -> _T:
2782 """Return exactly one result or raise an exception.
2784 Raises ``sqlalchemy.orm.exc.NoResultFound`` if the query selects
2785 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2786 if multiple object identities are returned, or if multiple
2787 rows are returned for a query that returns only scalar values
2788 as opposed to full identity-mapped entities.
2790 Calling :meth:`.one` results in an execution of the underlying query.
2792 .. seealso::
2794 :meth:`_query.Query.first`
2796 :meth:`_query.Query.one_or_none`
2798 :meth:`_engine.Result.one` - v2 comparable method.
2800 :meth:`_engine.Result.scalar_one` - v2 comparable method.
2802 """
2803 return self._iter().one() # type: ignore
2805 def scalar(self) -> Any:
2806 """Return the first element of the first result or None
2807 if no rows present. If multiple rows are returned,
2808 raises MultipleResultsFound.
2810 >>> session.query(Item).scalar()
2811 <Item>
2812 >>> session.query(Item.id).scalar()
2813 1
2814 >>> session.query(Item.id).filter(Item.id < 0).scalar()
2815 None
2816 >>> session.query(Item.id, Item.name).scalar()
2817 1
2818 >>> session.query(func.count(Parent.id)).scalar()
2819 20
2821 This results in an execution of the underlying query.
2823 .. seealso::
2825 :meth:`_engine.Result.scalar` - v2 comparable method.
2827 """
2828 # TODO: not sure why we can't use result.scalar() here
2829 try:
2830 ret = self.one()
2831 if not isinstance(ret, collections_abc.Sequence):
2832 return ret
2833 return ret[0]
2834 except sa_exc.NoResultFound:
2835 return None
2837 def __iter__(self) -> Iterator[_T]:
2838 result = self._iter()
2839 try:
2840 yield from result # type: ignore
2841 except GeneratorExit:
2842 # issue #8710 - direct iteration is not re-usable after
2843 # an iterable block is broken, so close the result
2844 result._soft_close()
2845 raise
2847 def _iter(self) -> Union[ScalarResult[_T], Result[_T]]:
2848 # new style execution.
2849 params = self._params
2851 statement = self._statement_20()
2852 result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
2853 statement,
2854 params,
2855 execution_options={"_sa_orm_load_options": self.load_options},
2856 )
2858 # legacy: automatically set scalars, unique
2859 if result._attributes.get("is_single_entity", False):
2860 result = cast("Result[_T]", result).scalars()
2862 if (
2863 result._attributes.get("filtered", False)
2864 and not self.load_options._yield_per
2865 ):
2866 result = result.unique()
2868 return result
2870 def __str__(self) -> str:
2871 statement = self._statement_20()
2873 try:
2874 bind = (
2875 self.session.get_bind(clause=statement)
2876 if self.session
2877 else None
2878 )
2879 except sa_exc.UnboundExecutionError:
2880 bind = None
2882 return str(statement.compile(bind))
2884 @property
2885 def column_descriptions(self) -> List[ORMColumnDescription]:
2886 """Return metadata about the columns which would be
2887 returned by this :class:`_query.Query`.
2889 Format is a list of dictionaries::
2891 user_alias = aliased(User, name='user2')
2892 q = sess.query(User, User.id, user_alias)
2894 # this expression:
2895 q.column_descriptions
2897 # would return:
2898 [
2899 {
2900 'name':'User',
2901 'type':User,
2902 'aliased':False,
2903 'expr':User,
2904 'entity': User
2905 },
2906 {
2907 'name':'id',
2908 'type':Integer(),
2909 'aliased':False,
2910 'expr':User.id,
2911 'entity': User
2912 },
2913 {
2914 'name':'user2',
2915 'type':User,
2916 'aliased':True,
2917 'expr':user_alias,
2918 'entity': user_alias
2919 }
2920 ]
2922 .. seealso::
2924 This API is available using :term:`2.0 style` queries as well,
2925 documented at:
2927 * :ref:`queryguide_inspection`
2929 * :attr:`.Select.column_descriptions`
2931 """
2933 return _column_descriptions(self, legacy=True)
2935 @util.deprecated(
2936 "2.0",
2937 "The :meth:`_orm.Query.instances` method is deprecated and will "
2938 "be removed in a future release. "
2939 "Use the Select.from_statement() method or aliased() construct in "
2940 "conjunction with Session.execute() instead.",
2941 )
2942 def instances(
2943 self,
2944 result_proxy: CursorResult[Any],
2945 context: Optional[QueryContext] = None,
2946 ) -> Any:
2947 """Return an ORM result given a :class:`_engine.CursorResult` and
2948 :class:`.QueryContext`.
2950 """
2951 if context is None:
2952 util.warn_deprecated(
2953 "Using the Query.instances() method without a context "
2954 "is deprecated and will be disallowed in a future release. "
2955 "Please make use of :meth:`_query.Query.from_statement` "
2956 "for linking ORM results to arbitrary select constructs.",
2957 version="1.4",
2958 )
2959 compile_state = self._compile_state(for_statement=False)
2961 context = QueryContext(
2962 compile_state,
2963 compile_state.statement,
2964 self._params,
2965 self.session,
2966 self.load_options,
2967 )
2969 result = loading.instances(result_proxy, context)
2971 # legacy: automatically set scalars, unique
2972 if result._attributes.get("is_single_entity", False):
2973 result = result.scalars() # type: ignore
2975 if result._attributes.get("filtered", False):
2976 result = result.unique()
2978 # TODO: isn't this supposed to be a list?
2979 return result
2981 @util.became_legacy_20(
2982 ":meth:`_orm.Query.merge_result`",
2983 alternative="The method is superseded by the "
2984 ":func:`_orm.merge_frozen_result` function.",
2985 enable_warnings=False, # warnings occur via loading.merge_result
2986 )
2987 def merge_result(
2988 self,
2989 iterator: Union[
2990 FrozenResult[Any], Iterable[Sequence[Any]], Iterable[object]
2991 ],
2992 load: bool = True,
2993 ) -> Union[FrozenResult[Any], Iterable[Any]]:
2994 """Merge a result into this :class:`_query.Query` object's Session.
2996 Given an iterator returned by a :class:`_query.Query`
2997 of the same structure
2998 as this one, return an identical iterator of results, with all mapped
2999 instances merged into the session using :meth:`.Session.merge`. This
3000 is an optimized method which will merge all mapped instances,
3001 preserving the structure of the result rows and unmapped columns with
3002 less method overhead than that of calling :meth:`.Session.merge`
3003 explicitly for each value.
3005 The structure of the results is determined based on the column list of
3006 this :class:`_query.Query` - if these do not correspond,
3007 unchecked errors
3008 will occur.
3010 The 'load' argument is the same as that of :meth:`.Session.merge`.
3012 For an example of how :meth:`_query.Query.merge_result` is used, see
3013 the source code for the example :ref:`examples_caching`, where
3014 :meth:`_query.Query.merge_result` is used to efficiently restore state
3015 from a cache back into a target :class:`.Session`.
3017 """
3019 return loading.merge_result(self, iterator, load)
3021 def exists(self) -> Exists:
3022 """A convenience method that turns a query into an EXISTS subquery
3023 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3025 e.g.::
3027 q = session.query(User).filter(User.name == 'fred')
3028 session.query(q.exists())
3030 Producing SQL similar to::
3032 SELECT EXISTS (
3033 SELECT 1 FROM users WHERE users.name = :name_1
3034 ) AS anon_1
3036 The EXISTS construct is usually used in the WHERE clause::
3038 session.query(User.id).filter(q.exists()).scalar()
3040 Note that some databases such as SQL Server don't allow an
3041 EXISTS expression to be present in the columns clause of a
3042 SELECT. To select a simple boolean value based on the exists
3043 as a WHERE, use :func:`.literal`::
3045 from sqlalchemy import literal
3047 session.query(literal(True)).filter(q.exists()).scalar()
3049 .. seealso::
3051 :meth:`_sql.Select.exists` - v2 comparable method.
3053 """
3055 # .add_columns() for the case that we are a query().select_from(X),
3056 # so that ".statement" can be produced (#2995) but also without
3057 # omitting the FROM clause from a query(X) (#2818);
3058 # .with_only_columns() after we have a core select() so that
3059 # we get just "SELECT 1" without any entities.
3061 inner = (
3062 self.enable_eagerloads(False)
3063 .add_columns(sql.literal_column("1"))
3064 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3065 ._get_select_statement_only()
3066 .with_only_columns(1)
3067 )
3069 ezero = self._entity_from_pre_ent_zero()
3070 if ezero is not None:
3071 inner = inner.select_from(ezero)
3073 return sql.exists(inner)
3075 def count(self) -> int:
3076 r"""Return a count of rows this the SQL formed by this :class:`Query`
3077 would return.
3079 This generates the SQL for this Query as follows::
3081 SELECT count(1) AS count_1 FROM (
3082 SELECT <rest of query follows...>
3083 ) AS anon_1
3085 The above SQL returns a single row, which is the aggregate value
3086 of the count function; the :meth:`_query.Query.count`
3087 method then returns
3088 that single integer value.
3090 .. warning::
3092 It is important to note that the value returned by
3093 count() is **not the same as the number of ORM objects that this
3094 Query would return from a method such as the .all() method**.
3095 The :class:`_query.Query` object,
3096 when asked to return full entities,
3097 will **deduplicate entries based on primary key**, meaning if the
3098 same primary key value would appear in the results more than once,
3099 only one object of that primary key would be present. This does
3100 not apply to a query that is against individual columns.
3102 .. seealso::
3104 :ref:`faq_query_deduplicating`
3106 For fine grained control over specific columns to count, to skip the
3107 usage of a subquery or otherwise control of the FROM clause, or to use
3108 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3109 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3111 from sqlalchemy import func
3113 # count User records, without
3114 # using a subquery.
3115 session.query(func.count(User.id))
3117 # return count of user "id" grouped
3118 # by "name"
3119 session.query(func.count(User.id)).\
3120 group_by(User.name)
3122 from sqlalchemy import distinct
3124 # count distinct "name" values
3125 session.query(func.count(distinct(User.name)))
3127 .. seealso::
3129 :ref:`migration_20_query_usage`
3131 """
3132 col = sql.func.count(sql.literal_column("*"))
3133 return ( # type: ignore
3134 self._legacy_from_self(col).enable_eagerloads(False).scalar()
3135 )
3137 def delete(
3138 self, synchronize_session: SynchronizeSessionArgument = "auto"
3139 ) -> int:
3140 r"""Perform a DELETE with an arbitrary WHERE clause.
3142 Deletes rows matched by this query from the database.
3144 E.g.::
3146 sess.query(User).filter(User.age == 25).\
3147 delete(synchronize_session=False)
3149 sess.query(User).filter(User.age == 25).\
3150 delete(synchronize_session='evaluate')
3152 .. warning::
3154 See the section :ref:`orm_expression_update_delete` for important
3155 caveats and warnings, including limitations when using bulk UPDATE
3156 and DELETE with mapper inheritance configurations.
3158 :param synchronize_session: chooses the strategy to update the
3159 attributes on objects in the session. See the section
3160 :ref:`orm_expression_update_delete` for a discussion of these
3161 strategies.
3163 :return: the count of rows matched as returned by the database's
3164 "row count" feature.
3166 .. seealso::
3168 :ref:`orm_expression_update_delete`
3170 """
3172 bulk_del = BulkDelete(self)
3173 if self.dispatch.before_compile_delete:
3174 for fn in self.dispatch.before_compile_delete:
3175 new_query = fn(bulk_del.query, bulk_del)
3176 if new_query is not None:
3177 bulk_del.query = new_query
3179 self = bulk_del.query
3181 delete_ = sql.delete(*self._raw_columns) # type: ignore
3182 delete_._where_criteria = self._where_criteria
3183 result: CursorResult[Any] = self.session.execute(
3184 delete_,
3185 self._params,
3186 execution_options=self._execution_options.union(
3187 {"synchronize_session": synchronize_session}
3188 ),
3189 )
3190 bulk_del.result = result # type: ignore
3191 self.session.dispatch.after_bulk_delete(bulk_del)
3192 result.close()
3194 return result.rowcount
3196 def update(
3197 self,
3198 values: Dict[_DMLColumnArgument, Any],
3199 synchronize_session: SynchronizeSessionArgument = "auto",
3200 update_args: Optional[Dict[Any, Any]] = None,
3201 ) -> int:
3202 r"""Perform an UPDATE with an arbitrary WHERE clause.
3204 Updates rows matched by this query in the database.
3206 E.g.::
3208 sess.query(User).filter(User.age == 25).\
3209 update({User.age: User.age - 10}, synchronize_session=False)
3211 sess.query(User).filter(User.age == 25).\
3212 update({"age": User.age - 10}, synchronize_session='evaluate')
3214 .. warning::
3216 See the section :ref:`orm_expression_update_delete` for important
3217 caveats and warnings, including limitations when using arbitrary
3218 UPDATE and DELETE with mapper inheritance configurations.
3220 :param values: a dictionary with attributes names, or alternatively
3221 mapped attributes or SQL expressions, as keys, and literal
3222 values or sql expressions as values. If :ref:`parameter-ordered
3223 mode <tutorial_parameter_ordered_updates>` is desired, the values can
3224 be passed as a list of 2-tuples; this requires that the
3225 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3226 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3227 as well.
3229 :param synchronize_session: chooses the strategy to update the
3230 attributes on objects in the session. See the section
3231 :ref:`orm_expression_update_delete` for a discussion of these
3232 strategies.
3234 :param update_args: Optional dictionary, if present will be passed
3235 to the underlying :func:`_expression.update`
3236 construct as the ``**kw`` for
3237 the object. May be used to pass dialect-specific arguments such
3238 as ``mysql_limit``, as well as other special arguments such as
3239 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3241 :return: the count of rows matched as returned by the database's
3242 "row count" feature.
3245 .. seealso::
3247 :ref:`orm_expression_update_delete`
3249 """
3251 update_args = update_args or {}
3253 bulk_ud = BulkUpdate(self, values, update_args)
3255 if self.dispatch.before_compile_update:
3256 for fn in self.dispatch.before_compile_update:
3257 new_query = fn(bulk_ud.query, bulk_ud)
3258 if new_query is not None:
3259 bulk_ud.query = new_query
3260 self = bulk_ud.query
3262 upd = sql.update(*self._raw_columns) # type: ignore
3264 ppo = update_args.pop("preserve_parameter_order", False)
3265 if ppo:
3266 upd = upd.ordered_values(*values) # type: ignore
3267 else:
3268 upd = upd.values(values)
3269 if update_args:
3270 upd = upd.with_dialect_options(**update_args)
3272 upd._where_criteria = self._where_criteria
3273 result: CursorResult[Any] = self.session.execute(
3274 upd,
3275 self._params,
3276 execution_options=self._execution_options.union(
3277 {"synchronize_session": synchronize_session}
3278 ),
3279 )
3280 bulk_ud.result = result # type: ignore
3281 self.session.dispatch.after_bulk_update(bulk_ud)
3282 result.close()
3283 return result.rowcount
3285 def _compile_state(
3286 self, for_statement: bool = False, **kw: Any
3287 ) -> ORMCompileState:
3288 """Create an out-of-compiler ORMCompileState object.
3290 The ORMCompileState object is normally created directly as a result
3291 of the SQLCompiler.process() method being handed a Select()
3292 or FromStatement() object that uses the "orm" plugin. This method
3293 provides a means of creating this ORMCompileState object directly
3294 without using the compiler.
3296 This method is used only for deprecated cases, which include
3297 the .from_self() method for a Query that has multiple levels
3298 of .from_self() in use, as well as the instances() method. It is
3299 also used within the test suite to generate ORMCompileState objects
3300 for test purposes.
3302 """
3304 stmt = self._statement_20(for_statement=for_statement, **kw)
3305 assert for_statement == stmt._compile_options._for_statement
3307 # this chooses between ORMFromStatementCompileState and
3308 # ORMSelectCompileState. We could also base this on
3309 # query._statement is not None as we have the ORM Query here
3310 # however this is the more general path.
3311 compile_state_cls = cast(
3312 ORMCompileState,
3313 ORMCompileState._get_plugin_class_for_plugin(stmt, "orm"),
3314 )
3316 return compile_state_cls.create_for_statement(stmt, None)
3318 def _compile_context(self, for_statement: bool = False) -> QueryContext:
3319 compile_state = self._compile_state(for_statement=for_statement)
3320 context = QueryContext(
3321 compile_state,
3322 compile_state.statement,
3323 self._params,
3324 self.session,
3325 self.load_options,
3326 )
3328 return context
3331class AliasOption(interfaces.LoaderOption):
3332 inherit_cache = False
3334 @util.deprecated(
3335 "1.4",
3336 "The :class:`.AliasOption` object is not necessary "
3337 "for entities to be matched up to a query that is established "
3338 "via :meth:`.Query.from_statement` and now does nothing.",
3339 )
3340 def __init__(self, alias: Union[Alias, Subquery]):
3341 r"""Return a :class:`.MapperOption` that will indicate to the
3342 :class:`_query.Query`
3343 that the main table has been aliased.
3345 """
3347 def process_compile_state(self, compile_state: ORMCompileState) -> None:
3348 pass
3351class BulkUD:
3352 """State used for the orm.Query version of update() / delete().
3354 This object is now specific to Query only.
3356 """
3358 def __init__(self, query: Query[Any]):
3359 self.query = query.enable_eagerloads(False)
3360 self._validate_query_state()
3361 self.mapper = self.query._entity_from_pre_ent_zero()
3363 def _validate_query_state(self) -> None:
3364 for attr, methname, notset, op in (
3365 ("_limit_clause", "limit()", None, operator.is_),
3366 ("_offset_clause", "offset()", None, operator.is_),
3367 ("_order_by_clauses", "order_by()", (), operator.eq),
3368 ("_group_by_clauses", "group_by()", (), operator.eq),
3369 ("_distinct", "distinct()", False, operator.is_),
3370 (
3371 "_from_obj",
3372 "join(), outerjoin(), select_from(), or from_self()",
3373 (),
3374 operator.eq,
3375 ),
3376 (
3377 "_setup_joins",
3378 "join(), outerjoin(), select_from(), or from_self()",
3379 (),
3380 operator.eq,
3381 ),
3382 ):
3383 if not op(getattr(self.query, attr), notset):
3384 raise sa_exc.InvalidRequestError(
3385 "Can't call Query.update() or Query.delete() "
3386 "when %s has been called" % (methname,)
3387 )
3389 @property
3390 def session(self) -> Session:
3391 return self.query.session
3394class BulkUpdate(BulkUD):
3395 """BulkUD which handles UPDATEs."""
3397 def __init__(
3398 self,
3399 query: Query[Any],
3400 values: Dict[_DMLColumnArgument, Any],
3401 update_kwargs: Optional[Dict[Any, Any]],
3402 ):
3403 super().__init__(query)
3404 self.values = values
3405 self.update_kwargs = update_kwargs
3408class BulkDelete(BulkUD):
3409 """BulkUD which handles DELETEs."""
3412class RowReturningQuery(Query[Row[Unpack[_Ts]]]):
3413 if TYPE_CHECKING:
3415 def tuples(self) -> Query[Tuple[Unpack[_Ts]]]: # type: ignore
3416 ...