codekingpro/portable-devtools
114k
1# orm/query.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8"""The Query class and support.
9
10Defines the :class:`_query.Query` class, the central
11construct used by the ORM to construct database queries.
12
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.
19
20"""
21from __future__ import annotations
22
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
42
43from . import attributes
44from . import interfaces
45from . import loading
46from . import util as orm_util
47from ._typing import _O
48from .base import _assertions
49from .context import _column_descriptions
50from .context import _determine_last_joined_entity
51from .context import _legacy_filter_by_entity_zero
52from .context import FromStatement
53from .context import ORMCompileState
54from .context import QueryContext
55from .interfaces import ORMColumnDescription
56from .interfaces import ORMColumnsClauseRole
57from .util import AliasedClass
58from .util import object_mapper
59from .util import with_parent
60from .. import exc as sa_exc
61from .. import inspect
62from .. import inspection
63from .. import log
64from .. import sql
65from .. import util
66from ..engine import Result
67from ..engine import Row
68from ..event import dispatcher
69from ..event import EventTarget
70from ..sql import coercions
71from ..sql import expression
72from ..sql import roles
73from ..sql import Select
74from ..sql import util as sql_util
75from ..sql import visitors
76from ..sql._typing import _FromClauseArgument
77from ..sql._typing import _TP
78from ..sql.annotation import SupportsCloneAnnotations
79from ..sql.base import _entity_namespace_key
80from ..sql.base import _generative
81from ..sql.base import _NoArg
82from ..sql.base import Executable
83from ..sql.base import Generative
84from ..sql.elements import BooleanClauseList
85from ..sql.expression import Exists
86from ..sql.selectable import _MemoizedSelectEntities
87from ..sql.selectable import _SelectFromElements
88from ..sql.selectable import ForUpdateArg
89from ..sql.selectable import HasHints
90from ..sql.selectable import HasPrefixes
91from ..sql.selectable import HasSuffixes
92from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL
93from ..sql.selectable import SelectLabelStyle
94from ..util.typing import Literal
95from ..util.typing import Self
96
97
98if TYPE_CHECKING:
99 from ._typing import _EntityType
100 from ._typing import _ExternalEntityType
101 from ._typing import _InternalEntityType
102 from ._typing import SynchronizeSessionArgument
103 from .mapper import Mapper
104 from .path_registry import PathRegistry
105 from .session import _PKIdentityArgument
106 from .session import Session
107 from .state import InstanceState
108 from ..engine.cursor import CursorResult
109 from ..engine.interfaces import _ImmutableExecuteOptions
110 from ..engine.interfaces import CompiledCacheType
111 from ..engine.interfaces import IsolationLevel
112 from ..engine.interfaces import SchemaTranslateMapType
113 from ..engine.result import FrozenResult
114 from ..engine.result import ScalarResult
115 from ..sql._typing import _ColumnExpressionArgument
116 from ..sql._typing import _ColumnExpressionOrStrLabelArgument
117 from ..sql._typing import _ColumnsClauseArgument
118 from ..sql._typing import _DMLColumnArgument
119 from ..sql._typing import _JoinTargetArgument
120 from ..sql._typing import _LimitOffsetType
121 from ..sql._typing import _MAYBE_ENTITY
122 from ..sql._typing import _no_kw
123 from ..sql._typing import _NOT_ENTITY
124 from ..sql._typing import _OnClauseArgument
125 from ..sql._typing import _PropagateAttrsType
126 from ..sql._typing import _T0
127 from ..sql._typing import _T1
128 from ..sql._typing import _T2
129 from ..sql._typing import _T3
130 from ..sql._typing import _T4
131 from ..sql._typing import _T5
132 from ..sql._typing import _T6
133 from ..sql._typing import _T7
134 from ..sql._typing import _TypedColumnClauseArgument as _TCCA
135 from ..sql.base import CacheableOptions
136 from ..sql.base import ExecutableOption
137 from ..sql.elements import ColumnElement
138 from ..sql.elements import Label
139 from ..sql.selectable import _ForUpdateOfArgument
140 from ..sql.selectable import _JoinTargetElement
141 from ..sql.selectable import _SetupJoinsElement
142 from ..sql.selectable import Alias
143 from ..sql.selectable import CTE
144 from ..sql.selectable import ExecutableReturnsRows
145 from ..sql.selectable import FromClause
146 from ..sql.selectable import ScalarSelect
147 from ..sql.selectable import Subquery
148
149
150__all__ = ["Query", "QueryContext"]
151
152_T = TypeVar("_T", bound=Any)
153
154
155@inspection._self_inspects
156@log.class_logger
157class Query(
158 _SelectFromElements,
159 SupportsCloneAnnotations,
160 HasPrefixes,
161 HasSuffixes,
162 HasHints,
163 EventTarget,
164 log.Identified,
165 Generative,
166 Executable,
167 Generic[_T],
168):
169 """ORM-level SQL construction object.
170
171 .. legacy:: The ORM :class:`.Query` object is a legacy construct
172 as of SQLAlchemy 2.0. See the notes at the top of
173 :ref:`query_api_toplevel` for an overview, including links to migration
174 documentation.
175
176 :class:`_query.Query` objects are normally initially generated using the
177 :meth:`~.Session.query` method of :class:`.Session`, and in
178 less common cases by instantiating the :class:`_query.Query` directly and
179 associating with a :class:`.Session` using the
180 :meth:`_query.Query.with_session`
181 method.
182
183 """
184
185 # elements that are in Core and can be cached in the same way
186 _where_criteria: Tuple[ColumnElement[Any], ...] = ()
187 _having_criteria: Tuple[ColumnElement[Any], ...] = ()
188
189 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()
190 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()
191 _limit_clause: Optional[ColumnElement[Any]] = None
192 _offset_clause: Optional[ColumnElement[Any]] = None
193
194 _distinct: bool = False
195 _distinct_on: Tuple[ColumnElement[Any], ...] = ()
196
197 _for_update_arg: Optional[ForUpdateArg] = None
198 _correlate: Tuple[FromClause, ...] = ()
199 _auto_correlate: bool = True
200 _from_obj: Tuple[FromClause, ...] = ()
201 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()
202
203 _label_style: SelectLabelStyle = SelectLabelStyle.LABEL_STYLE_LEGACY_ORM
204
205 _memoized_select_entities = ()
206
207 _compile_options: Union[Type[CacheableOptions], CacheableOptions] = (
208 ORMCompileState.default_compile_options
209 )
210
211 _with_options: Tuple[ExecutableOption, ...]
212 load_options = QueryContext.default_load_options + {
213 "_legacy_uniquing": True
214 }
215
216 _params: util.immutabledict[str, Any] = util.EMPTY_DICT
217
218 # local Query builder state, not needed for
219 # compilation or execution
220 _enable_assertions = True
221
222 _statement: Optional[ExecutableReturnsRows] = None
223
224 session: Session
225
226 dispatch: dispatcher[Query[_T]]
227
228 # mirrors that of ClauseElement, used to propagate the "orm"
229 # plugin as well as the "subject" of the plugin, e.g. the mapper
230 # we are querying against.
231 @util.memoized_property
232 def _propagate_attrs(self) -> _PropagateAttrsType:
233 return util.EMPTY_DICT
234
235 def __init__(
236 self,
237 entities: Union[
238 _ColumnsClauseArgument[Any], Sequence[_ColumnsClauseArgument[Any]]
239 ],
240 session: Optional[Session] = None,
241 ):
242 """Construct a :class:`_query.Query` directly.
243
244 E.g.::
245
246 q = Query([User, Address], session=some_session)
247
248 The above is equivalent to::
249
250 q = some_session.query(User, Address)
251
252 :param entities: a sequence of entities and/or SQL expressions.
253
254 :param session: a :class:`.Session` with which the
255 :class:`_query.Query`
256 will be associated. Optional; a :class:`_query.Query`
257 can be associated
258 with a :class:`.Session` generatively via the
259 :meth:`_query.Query.with_session` method as well.
260
261 .. seealso::
262
263 :meth:`.Session.query`
264
265 :meth:`_query.Query.with_session`
266
267 """
268
269 # session is usually present. There's one case in subqueryloader
270 # where it stores a Query without a Session and also there are tests
271 # for the query(Entity).with_session(session) API which is likely in
272 # some old recipes, however these are legacy as select() can now be
273 # used.
274 self.session = session # type: ignore
275 self._set_entities(entities)
276
277 def _set_propagate_attrs(self, values: Mapping[str, Any]) -> Self:
278 self._propagate_attrs = util.immutabledict(values)
279 return self
280
281 def _set_entities(
282 self,
283 entities: Union[
284 _ColumnsClauseArgument[Any], Iterable[_ColumnsClauseArgument[Any]]
285 ],
286 ) -> None:
287 self._raw_columns = [
288 coercions.expect(
289 roles.ColumnsClauseRole,
290 ent,
291 apply_propagate_attrs=self,
292 post_inspect=True,
293 )
294 for ent in util.to_list(entities)
295 ]
296
297 def tuples(self: Query[_O]) -> Query[Tuple[_O]]:
298 """return a tuple-typed form of this :class:`.Query`.
299
300 This method invokes the :meth:`.Query.only_return_tuples`
301 method with a value of ``True``, which by itself ensures that this
302 :class:`.Query` will always return :class:`.Row` objects, even
303 if the query is made against a single entity. It then also
304 at the typing level will return a "typed" query, if possible,
305 that will type result rows as ``Tuple`` objects with typed
306 elements.
307
308 This method can be compared to the :meth:`.Result.tuples` method,
309 which returns "self", but from a typing perspective returns an object
310 that will yield typed ``Tuple`` objects for results. Typing
311 takes effect only if this :class:`.Query` object is a typed
312 query object already.
313
314 .. versionadded:: 2.0
315
316 .. seealso::
317
318 :meth:`.Result.tuples` - v2 equivalent method.
319
320 """
321 return self.only_return_tuples(True) # type: ignore
322
323 def _entity_from_pre_ent_zero(self) -> Optional[_InternalEntityType[Any]]:
324 if not self._raw_columns:
325 return None
326
327 ent = self._raw_columns[0]
328
329 if "parententity" in ent._annotations:
330 return ent._annotations["parententity"] # type: ignore
331 elif "bundle" in ent._annotations:
332 return ent._annotations["bundle"] # type: ignore
333 else:
334 # label, other SQL expression
335 for element in visitors.iterate(ent):
336 if "parententity" in element._annotations:
337 return element._annotations["parententity"] # type: ignore # noqa: E501
338 else:
339 return None
340
341 def _only_full_mapper_zero(self, methname: str) -> Mapper[Any]:
342 if (
343 len(self._raw_columns) != 1
344 or "parententity" not in self._raw_columns[0]._annotations
345 or not self._raw_columns[0].is_selectable
346 ):
347 raise sa_exc.InvalidRequestError(
348 "%s() can only be used against "
349 "a single mapped class." % methname
350 )
351
352 return self._raw_columns[0]._annotations["parententity"] # type: ignore # noqa: E501
353
354 def _set_select_from(
355 self, obj: Iterable[_FromClauseArgument], set_base_alias: bool
356 ) -> None:
357 fa = [
358 coercions.expect(
359 roles.StrictFromClauseRole,
360 elem,
361 allow_select=True,
362 apply_propagate_attrs=self,
363 )
364 for elem in obj
365 ]
366
367 self._compile_options += {"_set_base_alias": set_base_alias}
368 self._from_obj = tuple(fa)
369
370 @_generative
371 def _set_lazyload_from(self, state: InstanceState[Any]) -> Self:
372 self.load_options += {"_lazy_loaded_from": state}
373 return self
374
375 def _get_condition(self) -> None:
376 """used by legacy BakedQuery"""
377 self._no_criterion_condition("get", order_by=False, distinct=False)
378
379 def _get_existing_condition(self) -> None:
380 self._no_criterion_assertion("get", order_by=False, distinct=False)
381
382 def _no_criterion_assertion(
383 self, meth: str, order_by: bool = True, distinct: bool = True
384 ) -> None:
385 if not self._enable_assertions:
386 return
387 if (
388 self._where_criteria
389 or self._statement is not None
390 or self._from_obj
391 or self._setup_joins
392 or self._limit_clause is not None
393 or self._offset_clause is not None
394 or self._group_by_clauses
395 or (order_by and self._order_by_clauses)
396 or (distinct and self._distinct)
397 ):
398 raise sa_exc.InvalidRequestError(
399 "Query.%s() being called on a "
400 "Query with existing criterion. " % meth
401 )
402
403 def _no_criterion_condition(
404 self, meth: str, order_by: bool = True, distinct: bool = True
405 ) -> None:
406 self._no_criterion_assertion(meth, order_by, distinct)
407
408 self._from_obj = self._setup_joins = ()
409 if self._statement is not None:
410 self._compile_options += {"_statement": None}
411 self._where_criteria = ()
412 self._distinct = False
413
414 self._order_by_clauses = self._group_by_clauses = ()
415
416 def _no_clauseelement_condition(self, meth: str) -> None:
417 if not self._enable_assertions:
418 return
419 if self._order_by_clauses:
420 raise sa_exc.InvalidRequestError(
421 "Query.%s() being called on a "
422 "Query with existing criterion. " % meth
423 )
424 self._no_criterion_condition(meth)
425
426 def _no_statement_condition(self, meth: str) -> None:
427 if not self._enable_assertions:
428 return
429 if self._statement is not None:
430 raise sa_exc.InvalidRequestError(
431 (
432 "Query.%s() being called on a Query with an existing full "
433 "statement - can't apply criterion."
434 )
435 % meth
436 )
437
438 def _no_limit_offset(self, meth: str) -> None:
439 if not self._enable_assertions:
440 return
441 if self._limit_clause is not None or self._offset_clause is not None:
442 raise sa_exc.InvalidRequestError(
443 "Query.%s() being called on a Query which already has LIMIT "
444 "or OFFSET applied. Call %s() before limit() or offset() "
445 "are applied." % (meth, meth)
446 )
447
448 @property
449 def _has_row_limiting_clause(self) -> bool:
450 return (
451 self._limit_clause is not None or self._offset_clause is not None
452 )
453
454 def _get_options(
455 self,
456 populate_existing: Optional[bool] = None,
457 version_check: Optional[bool] = None,
458 only_load_props: Optional[Sequence[str]] = None,
459 refresh_state: Optional[InstanceState[Any]] = None,
460 identity_token: Optional[Any] = None,
461 ) -> Self:
462 load_options: Dict[str, Any] = {}
463 compile_options: Dict[str, Any] = {}
464
465 if version_check:
466 load_options["_version_check"] = version_check
467 if populate_existing:
468 load_options["_populate_existing"] = populate_existing
469 if refresh_state:
470 load_options["_refresh_state"] = refresh_state
471 compile_options["_for_refresh_state"] = True
472 if only_load_props:
473 compile_options["_only_load_props"] = frozenset(only_load_props)
474 if identity_token:
475 load_options["_identity_token"] = identity_token
476
477 if load_options:
478 self.load_options += load_options
479 if compile_options:
480 self._compile_options += compile_options
481
482 return self
483
484 def _clone(self, **kw: Any) -> Self:
485 return self._generate()
486
487 def _get_select_statement_only(self) -> Select[_T]:
488 if self._statement is not None:
489 raise sa_exc.InvalidRequestError(
490 "Can't call this method on a Query that uses from_statement()"
491 )
492 return cast("Select[_T]", self.statement)
493
494 @property
495 def statement(self) -> Union[Select[_T], FromStatement[_T]]:
496 """The full SELECT statement represented by this Query.
497
498 The statement by default will not have disambiguating labels
499 applied to the construct unless with_labels(True) is called
500 first.
501
502 """
503
504 # .statement can return the direct future.Select() construct here, as
505 # long as we are not using subsequent adaption features that
506 # are made against raw entities, e.g. from_self(), with_polymorphic(),
507 # select_entity_from(). If these features are being used, then
508 # the Select() we return will not have the correct .selected_columns
509 # collection and will not embed in subsequent queries correctly.
510 # We could find a way to make this collection "correct", however
511 # this would not be too different from doing the full compile as
512 # we are doing in any case, the Select() would still not have the
513 # proper state for other attributes like whereclause, order_by,
514 # and these features are all deprecated in any case.
515 #
516 # for these reasons, Query is not a Select, it remains an ORM
517 # object for which __clause_element__() must be called in order for
518 # it to provide a real expression object.
519 #
520 # from there, it starts to look much like Query itself won't be
521 # passed into the execute process and won't generate its own cache
522 # key; this will all occur in terms of the ORM-enabled Select.
523 if not self._compile_options._set_base_alias:
524 # if we don't have legacy top level aliasing features in use
525 # then convert to a future select() directly
526 stmt = self._statement_20(for_statement=True)
527 else:
528 stmt = self._compile_state(for_statement=True).statement
529
530 if self._params:
531 stmt = stmt.params(self._params)
532
533 return stmt
534
535 def _final_statement(self, legacy_query_style: bool = True) -> Select[Any]:
536 """Return the 'final' SELECT statement for this :class:`.Query`.
537
538 This is used by the testing suite only and is fairly inefficient.
539
540 This is the Core-only select() that will be rendered by a complete
541 compilation of this query, and is what .statement used to return
542 in 1.3.
543
544
545 """
546
547 q = self._clone()
548
549 return q._compile_state(
550 use_legacy_query_style=legacy_query_style
551 ).statement # type: ignore
552
553 def _statement_20(
554 self, for_statement: bool = False, use_legacy_query_style: bool = True
555 ) -> Union[Select[_T], FromStatement[_T]]:
556 # TODO: this event needs to be deprecated, as it currently applies
557 # only to ORM query and occurs at this spot that is now more
558 # or less an artificial spot
559 if self.dispatch.before_compile:
560 for fn in self.dispatch.before_compile:
561 new_query = fn(self)
562 if new_query is not None and new_query is not self:
563 self = new_query
564 if not fn._bake_ok: # type: ignore
565 self._compile_options += {"_bake_ok": False}
566
567 compile_options = self._compile_options
568 compile_options += {
569 "_for_statement": for_statement,
570 "_use_legacy_query_style": use_legacy_query_style,
571 }
572
573 stmt: Union[Select[_T], FromStatement[_T]]
574
575 if self._statement is not None:
576 stmt = FromStatement(self._raw_columns, self._statement)
577 stmt.__dict__.update(
578 _with_options=self._with_options,
579 _with_context_options=self._with_context_options,
580 _compile_options=compile_options,
581 _execution_options=self._execution_options,
582 _propagate_attrs=self._propagate_attrs,
583 )
584 else:
585 # Query / select() internal attributes are 99% cross-compatible
586 stmt = Select._create_raw_select(**self.__dict__)
587 stmt.__dict__.update(
588 _label_style=self._label_style,
589 _compile_options=compile_options,
590 _propagate_attrs=self._propagate_attrs,
591 )
592 stmt.__dict__.pop("session", None)
593
594 # ensure the ORM context is used to compile the statement, even
595 # if it has no ORM entities. This is so ORM-only things like
596 # _legacy_joins are picked up that wouldn't be picked up by the
597 # Core statement context
598 if "compile_state_plugin" not in stmt._propagate_attrs:
599 stmt._propagate_attrs = stmt._propagate_attrs.union(
600 {"compile_state_plugin": "orm", "plugin_subject": None}
601 )
602
603 return stmt
604
605 def subquery(
606 self,
607 name: Optional[str] = None,
608 with_labels: bool = False,
609 reduce_columns: bool = False,
610 ) -> Subquery:
611 """Return the full SELECT statement represented by
612 this :class:`_query.Query`, embedded within an
613 :class:`_expression.Alias`.
614
615 Eager JOIN generation within the query is disabled.
616
617 .. seealso::
618
619 :meth:`_sql.Select.subquery` - v2 comparable method.
620
621 :param name: string name to be assigned as the alias;
622 this is passed through to :meth:`_expression.FromClause.alias`.
623 If ``None``, a name will be deterministically generated
624 at compile time.
625
626 :param with_labels: if True, :meth:`.with_labels` will be called
627 on the :class:`_query.Query` first to apply table-qualified labels
628 to all columns.
629
630 :param reduce_columns: if True,
631 :meth:`_expression.Select.reduce_columns` will
632 be called on the resulting :func:`_expression.select` construct,
633 to remove same-named columns where one also refers to the other
634 via foreign key or WHERE clause equivalence.
635
636 """
637 q = self.enable_eagerloads(False)
638 if with_labels:
639 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
640
641 stmt = q._get_select_statement_only()
642
643 if TYPE_CHECKING:
644 assert isinstance(stmt, Select)
645
646 if reduce_columns:
647 stmt = stmt.reduce_columns()
648 return stmt.subquery(name=name)
649
650 def cte(
651 self,
652 name: Optional[str] = None,
653 recursive: bool = False,
654 nesting: bool = False,
655 ) -> CTE:
656 r"""Return the full SELECT statement represented by this
657 :class:`_query.Query` represented as a common table expression (CTE).
658
659 Parameters and usage are the same as those of the
660 :meth:`_expression.SelectBase.cte` method; see that method for
661 further details.
662
663 Here is the `PostgreSQL WITH
664 RECURSIVE example
665 <https://www.postgresql.org/docs/current/static/queries-with.html>`_.
666 Note that, in this example, the ``included_parts`` cte and the
667 ``incl_alias`` alias of it are Core selectables, which
668 means the columns are accessed via the ``.c.`` attribute. The
669 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
670 ``Part`` entity, so column-mapped attributes are available
671 directly::
672
673 from sqlalchemy.orm import aliased
674
675 class Part(Base):
676 __tablename__ = 'part'
677 part = Column(String, primary_key=True)
678 sub_part = Column(String, primary_key=True)
679 quantity = Column(Integer)
680
681 included_parts = session.query(
682 Part.sub_part,
683 Part.part,
684 Part.quantity).\
685 filter(Part.part=="our part").\
686 cte(name="included_parts", recursive=True)
687
688 incl_alias = aliased(included_parts, name="pr")
689 parts_alias = aliased(Part, name="p")
690 included_parts = included_parts.union_all(
691 session.query(
692 parts_alias.sub_part,
693 parts_alias.part,
694 parts_alias.quantity).\
695 filter(parts_alias.part==incl_alias.c.sub_part)
696 )
697
698 q = session.query(
699 included_parts.c.sub_part,
700 func.sum(included_parts.c.quantity).
701 label('total_quantity')
702 ).\
703 group_by(included_parts.c.sub_part)
704
705 .. seealso::
706
707 :meth:`_sql.Select.cte` - v2 equivalent method.
708
709 """
710 return (
711 self.enable_eagerloads(False)
712 ._get_select_statement_only()
713 .cte(name=name, recursive=recursive, nesting=nesting)
714 )
715
716 def label(self, name: Optional[str]) -> Label[Any]:
717 """Return the full SELECT statement represented by this
718 :class:`_query.Query`, converted
719 to a scalar subquery with a label of the given name.
720
721 .. seealso::
722
723 :meth:`_sql.Select.label` - v2 comparable method.
724
725 """
726
727 return (
728 self.enable_eagerloads(False)
729 ._get_select_statement_only()
730 .label(name)
731 )
732
733 @overload
734 def as_scalar(
735 self: Query[Tuple[_MAYBE_ENTITY]],
736 ) -> ScalarSelect[_MAYBE_ENTITY]: ...
737
738 @overload
739 def as_scalar(
740 self: Query[Tuple[_NOT_ENTITY]],
741 ) -> ScalarSelect[_NOT_ENTITY]: ...
742
743 @overload
744 def as_scalar(self) -> ScalarSelect[Any]: ...
745
746 @util.deprecated(
747 "1.4",
748 "The :meth:`_query.Query.as_scalar` method is deprecated and will be "
749 "removed in a future release. Please refer to "
750 ":meth:`_query.Query.scalar_subquery`.",
751 )
752 def as_scalar(self) -> ScalarSelect[Any]:
753 """Return the full SELECT statement represented by this
754 :class:`_query.Query`, converted to a scalar subquery.
755
756 """
757 return self.scalar_subquery()
758
759 @overload
760 def scalar_subquery(
761 self: Query[Tuple[_MAYBE_ENTITY]],
762 ) -> ScalarSelect[Any]: ...
763
764 @overload
765 def scalar_subquery(
766 self: Query[Tuple[_NOT_ENTITY]],
767 ) -> ScalarSelect[_NOT_ENTITY]: ...
768
769 @overload
770 def scalar_subquery(self) -> ScalarSelect[Any]: ...
771
772 def scalar_subquery(self) -> ScalarSelect[Any]:
773 """Return the full SELECT statement represented by this
774 :class:`_query.Query`, converted to a scalar subquery.
775
776 Analogous to
777 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
778
779 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
780 method replaces the :meth:`_query.Query.as_scalar` method.
781
782 .. seealso::
783
784 :meth:`_sql.Select.scalar_subquery` - v2 comparable method.
785
786 """
787
788 return (
789 self.enable_eagerloads(False)
790 ._get_select_statement_only()
791 .scalar_subquery()
792 )
793
794 @property
795 def selectable(self) -> Union[Select[_T], FromStatement[_T]]:
796 """Return the :class:`_expression.Select` object emitted by this
797 :class:`_query.Query`.
798
799 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
800
801 query.enable_eagerloads(False).with_labels().statement
802
803 """
804 return self.__clause_element__()
805
806 def __clause_element__(self) -> Union[Select[_T], FromStatement[_T]]:
807 return (
808 self._with_compile_options(
809 _enable_eagerloads=False, _render_for_subquery=True
810 )
811 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
812 .statement
813 )
814
815 @overload
816 def only_return_tuples(
817 self: Query[_O], value: Literal[True]
818 ) -> RowReturningQuery[Tuple[_O]]: ...
819
820 @overload
821 def only_return_tuples(
822 self: Query[_O], value: Literal[False]
823 ) -> Query[_O]: ...
824
825 @_generative
826 def only_return_tuples(self, value: bool) -> Query[Any]:
827 """When set to True, the query results will always be a
828 :class:`.Row` object.
829
830 This can change a query that normally returns a single entity
831 as a scalar to return a :class:`.Row` result in all cases.
832
833 .. seealso::
834
835 :meth:`.Query.tuples` - returns tuples, but also at the typing
836 level will type results as ``Tuple``.
837
838 :meth:`_query.Query.is_single_entity`
839
840 :meth:`_engine.Result.tuples` - v2 comparable method.
841
842 """
843 self.load_options += dict(_only_return_tuples=value)
844 return self
845
846 @property
847 def is_single_entity(self) -> bool:
848 """Indicates if this :class:`_query.Query`
849 returns tuples or single entities.
850
851 Returns True if this query returns a single entity for each instance
852 in its result list, and False if this query returns a tuple of entities
853 for each result.
854
855 .. versionadded:: 1.3.11
856
857 .. seealso::
858
859 :meth:`_query.Query.only_return_tuples`
860
861 """
862 return (
863 not self.load_options._only_return_tuples
864 and len(self._raw_columns) == 1
865 and "parententity" in self._raw_columns[0]._annotations
866 and isinstance(
867 self._raw_columns[0]._annotations["parententity"],
868 ORMColumnsClauseRole,
869 )
870 )
871
872 @_generative
873 def enable_eagerloads(self, value: bool) -> Self:
874 """Control whether or not eager joins and subqueries are
875 rendered.
876
877 When set to False, the returned Query will not render
878 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
879 :func:`~sqlalchemy.orm.subqueryload` options
880 or mapper-level ``lazy='joined'``/``lazy='subquery'``
881 configurations.
882
883 This is used primarily when nesting the Query's
884 statement into a subquery or other
885 selectable, or when using :meth:`_query.Query.yield_per`.
886
887 """
888 self._compile_options += {"_enable_eagerloads": value}
889 return self
890
891 @_generative
892 def _with_compile_options(self, **opt: Any) -> Self:
893 self._compile_options += opt
894 return self
895
896 @util.became_legacy_20(
897 ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`",
898 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
899 "instead.",
900 )
901 def with_labels(self) -> Self:
902 return self.set_label_style(
903 SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL
904 )
905
906 apply_labels = with_labels
907
908 @property
909 def get_label_style(self) -> SelectLabelStyle:
910 """
911 Retrieve the current label style.
912
913 .. versionadded:: 1.4
914
915 .. seealso::
916
917 :meth:`_sql.Select.get_label_style` - v2 equivalent method.
918
919 """
920 return self._label_style
921
922 def set_label_style(self, style: SelectLabelStyle) -> Self:
923 """Apply column labels to the return value of Query.statement.
924
925 Indicates that this Query's `statement` accessor should return
926 a SELECT statement that applies labels to all columns in the
927 form <tablename>_<columnname>; this is commonly used to
928 disambiguate columns from multiple tables which have the same
929 name.
930
931 When the `Query` actually issues SQL to load rows, it always
932 uses column labeling.
933
934 .. note:: The :meth:`_query.Query.set_label_style` method *only* applies
935 the output of :attr:`_query.Query.statement`, and *not* to any of
936 the result-row invoking systems of :class:`_query.Query` itself,
937 e.g.
938 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
939 To execute
940 a query using :meth:`_query.Query.set_label_style`, invoke the
941 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
942
943 result = session.execute(
944 query
945 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
946 .statement
947 )
948
949 .. versionadded:: 1.4
950
951
952 .. seealso::
953
954 :meth:`_sql.Select.set_label_style` - v2 equivalent method.
955
956 """ # noqa
957 if self._label_style is not style:
958 self = self._generate()
959 self._label_style = style
960 return self
961
962 @_generative
963 def enable_assertions(self, value: bool) -> Self:
964 """Control whether assertions are generated.
965
966 When set to False, the returned Query will
967 not assert its state before certain operations,
968 including that LIMIT/OFFSET has not been applied
969 when filter() is called, no criterion exists
970 when get() is called, and no "from_statement()"
971 exists when filter()/order_by()/group_by() etc.
972 is called. This more permissive mode is used by
973 custom Query subclasses to specify criterion or
974 other modifiers outside of the usual usage patterns.
975
976 Care should be taken to ensure that the usage
977 pattern is even possible. A statement applied
978 by from_statement() will override any criterion
979 set by filter() or order_by(), for example.
980
981 """
982 self._enable_assertions = value
983 return self
984
985 @property
986 def whereclause(self) -> Optional[ColumnElement[bool]]:
987 """A readonly attribute which returns the current WHERE criterion for
988 this Query.
989
990 This returned value is a SQL expression construct, or ``None`` if no
991 criterion has been established.
992
993 .. seealso::
994
995 :attr:`_sql.Select.whereclause` - v2 equivalent property.
996
997 """
998 return BooleanClauseList._construct_for_whereclause(
999 self._where_criteria
1000 )
1001
1002 @_generative
1003 def _with_current_path(self, path: PathRegistry) -> Self:
1004 """indicate that this query applies to objects loaded
1005 within a certain path.
1006
1007 Used by deferred loaders (see strategies.py) which transfer
1008 query options from an originating query to a newly generated
1009 query intended for the deferred load.
1010
1011 """
1012 self._compile_options += {"_current_path": path}
1013 return self
1014
1015 @_generative
1016 def yield_per(self, count: int) -> Self:
1017 r"""Yield only ``count`` rows at a time.
1018
1019 The purpose of this method is when fetching very large result sets
1020 (> 10K rows), to batch results in sub-collections and yield them
1021 out partially, so that the Python interpreter doesn't need to declare
1022 very large areas of memory which is both time consuming and leads
1023 to excessive memory use. The performance from fetching hundreds of
1024 thousands of rows can often double when a suitable yield-per setting
1025 (e.g. approximately 1000) is used, even with DBAPIs that buffer
1026 rows (which are most).
1027
1028 As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
1029 equivalent to using the ``yield_per`` execution option at the ORM
1030 level. See the section :ref:`orm_queryguide_yield_per` for further
1031 background on this option.
1032
1033 .. seealso::
1034
1035 :ref:`orm_queryguide_yield_per`
1036
1037 """
1038 self.load_options += {"_yield_per": count}
1039 return self
1040
1041 @util.became_legacy_20(
1042 ":meth:`_orm.Query.get`",
1043 alternative="The method is now available as :meth:`_orm.Session.get`",
1044 )
1045 def get(self, ident: _PKIdentityArgument) -> Optional[Any]:
1046 """Return an instance based on the given primary key identifier,
1047 or ``None`` if not found.
1048
1049 E.g.::
1050
1051 my_user = session.query(User).get(5)
1052
1053 some_object = session.query(VersionedFoo).get((5, 10))
1054
1055 some_object = session.query(VersionedFoo).get(
1056 {"id": 5, "version_id": 10})
1057
1058 :meth:`_query.Query.get` is special in that it provides direct
1059 access to the identity map of the owning :class:`.Session`.
1060 If the given primary key identifier is present
1061 in the local identity map, the object is returned
1062 directly from this collection and no SQL is emitted,
1063 unless the object has been marked fully expired.
1064 If not present,
1065 a SELECT is performed in order to locate the object.
1066
1067 :meth:`_query.Query.get` also will perform a check if
1068 the object is present in the identity map and
1069 marked as expired - a SELECT
1070 is emitted to refresh the object as well as to
1071 ensure that the row is still present.
1072 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
1073
1074 :meth:`_query.Query.get` is only used to return a single
1075 mapped instance, not multiple instances or
1076 individual column constructs, and strictly
1077 on a single primary key value. The originating
1078 :class:`_query.Query` must be constructed in this way,
1079 i.e. against a single mapped entity,
1080 with no additional filtering criterion. Loading
1081 options via :meth:`_query.Query.options` may be applied
1082 however, and will be used if the object is not
1083 yet locally present.
1084
1085 :param ident: A scalar, tuple, or dictionary representing the
1086 primary key. For a composite (e.g. multiple column) primary key,
1087 a tuple or dictionary should be passed.
1088
1089 For a single-column primary key, the scalar calling form is typically
1090 the most expedient. If the primary key of a row is the value "5",
1091 the call looks like::
1092
1093 my_object = query.get(5)
1094
1095 The tuple form contains primary key values typically in
1096 the order in which they correspond to the mapped
1097 :class:`_schema.Table`
1098 object's primary key columns, or if the
1099 :paramref:`_orm.Mapper.primary_key` configuration parameter were
1100 used, in
1101 the order used for that parameter. For example, if the primary key
1102 of a row is represented by the integer
1103 digits "5, 10" the call would look like::
1104
1105 my_object = query.get((5, 10))
1106
1107 The dictionary form should include as keys the mapped attribute names
1108 corresponding to each element of the primary key. If the mapped class
1109 has the attributes ``id``, ``version_id`` as the attributes which
1110 store the object's primary key value, the call would look like::
1111
1112 my_object = query.get({"id": 5, "version_id": 10})
1113
1114 .. versionadded:: 1.3 the :meth:`_query.Query.get`
1115 method now optionally
1116 accepts a dictionary of attribute names to values in order to
1117 indicate a primary key identifier.
1118
1119
1120 :return: The object instance, or ``None``.
1121
1122 """
1123 self._no_criterion_assertion("get", order_by=False, distinct=False)
1124
1125 # we still implement _get_impl() so that baked query can override
1126 # it
1127 return self._get_impl(ident, loading.load_on_pk_identity)
1128
1129 def _get_impl(
1130 self,
1131 primary_key_identity: _PKIdentityArgument,
1132 db_load_fn: Callable[..., Any],
1133 identity_token: Optional[Any] = None,
1134 ) -> Optional[Any]:
1135 mapper = self._only_full_mapper_zero("get")
1136 return self.session._get_impl(
1137 mapper,
1138 primary_key_identity,
1139 db_load_fn,
1140 populate_existing=self.load_options._populate_existing,
1141 with_for_update=self._for_update_arg,
1142 options=self._with_options,
1143 identity_token=identity_token,
1144 execution_options=self._execution_options,
1145 )
1146
1147 @property
1148 def lazy_loaded_from(self) -> Optional[InstanceState[Any]]:
1149 """An :class:`.InstanceState` that is using this :class:`_query.Query`
1150 for a lazy load operation.
1151
1152 .. deprecated:: 1.4 This attribute should be viewed via the
1153 :attr:`.ORMExecuteState.lazy_loaded_from` attribute, within
1154 the context of the :meth:`.SessionEvents.do_orm_execute`
1155 event.
1156
1157 .. seealso::
1158
1159 :attr:`.ORMExecuteState.lazy_loaded_from`
1160
1161 """
1162 return self.load_options._lazy_loaded_from # type: ignore
1163
1164 @property
1165 def _current_path(self) -> PathRegistry:
1166 return self._compile_options._current_path # type: ignore
1167
1168 @_generative
1169 def correlate(
1170 self,
1171 *fromclauses: Union[Literal[None, False], _FromClauseArgument],
1172 ) -> Self:
1173 """Return a :class:`.Query` construct which will correlate the given
1174 FROM clauses to that of an enclosing :class:`.Query` or
1175 :func:`~.expression.select`.
1176
1177 The method here accepts mapped classes, :func:`.aliased` constructs,
1178 and :class:`_orm.Mapper` constructs as arguments, which are resolved
1179 into expression constructs, in addition to appropriate expression
1180 constructs.
1181
1182 The correlation arguments are ultimately passed to
1183 :meth:`_expression.Select.correlate`
1184 after coercion to expression constructs.
1185
1186 The correlation arguments take effect in such cases
1187 as when :meth:`_query.Query.from_self` is used, or when
1188 a subquery as returned by :meth:`_query.Query.subquery` is
1189 embedded in another :func:`_expression.select` construct.
1190
1191 .. seealso::
1192
1193 :meth:`_sql.Select.correlate` - v2 equivalent method.
1194
1195 """
1196
1197 self._auto_correlate = False
1198 if fromclauses and fromclauses[0] in {None, False}:
1199 self._correlate = ()
1200 else: