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