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"""
21import itertools
22import operator
23import types
24
25from . import exc as orm_exc
26from . import interfaces
27from . import loading
28from . import util as orm_util
29from .base import _assertions
30from .context import _column_descriptions
31from .context import _legacy_determine_last_joined_entity
32from .context import _legacy_filter_by_entity_zero
33from .context import LABEL_STYLE_LEGACY_ORM
34from .context import ORMCompileState
35from .context import ORMFromStatementCompileState
36from .context import QueryContext
37from .interfaces import ORMColumnsClauseRole
38from .util import aliased
39from .util import AliasedClass
40from .util import object_mapper
41from .util import with_parent
42from .util import with_polymorphic
43from .. import exc as sa_exc
44from .. import inspect
45from .. import inspection
46from .. import log
47from .. import sql
48from .. import util
49from ..sql import coercions
50from ..sql import elements
51from ..sql import expression
52from ..sql import roles
53from ..sql import Select
54from ..sql import util as sql_util
55from ..sql import visitors
56from ..sql.annotation import SupportsCloneAnnotations
57from ..sql.base import _entity_namespace_key
58from ..sql.base import _generative
59from ..sql.base import Executable
60from ..sql.selectable import _MemoizedSelectEntities
61from ..sql.selectable import _SelectFromElements
62from ..sql.selectable import ForUpdateArg
63from ..sql.selectable import GroupedElement
64from ..sql.selectable import HasHints
65from ..sql.selectable import HasPrefixes
66from ..sql.selectable import HasSuffixes
67from ..sql.selectable import LABEL_STYLE_TABLENAME_PLUS_COL
68from ..sql.selectable import SelectBase
69from ..sql.selectable import SelectStatementGrouping
70from ..sql.visitors import InternalTraversal
71from ..util import collections_abc
72
73__all__ = ["Query", "QueryContext", "aliased"]
74
75
76@inspection._self_inspects
77@log.class_logger
78class Query(
79 _SelectFromElements,
80 SupportsCloneAnnotations,
81 HasPrefixes,
82 HasSuffixes,
83 HasHints,
84 Executable,
85):
86
87 """ORM-level SQL construction object.
88
89 :class:`_query.Query`
90 is the source of all SELECT statements generated by the
91 ORM, both those formulated by end-user query operations as well as by
92 high level internal operations such as related collection loading. It
93 features a generative interface whereby successive calls return a new
94 :class:`_query.Query` object, a copy of the former with additional
95 criteria and options associated with it.
96
97 :class:`_query.Query` objects are normally initially generated using the
98 :meth:`~.Session.query` method of :class:`.Session`, and in
99 less common cases by instantiating the :class:`_query.Query` directly and
100 associating with a :class:`.Session` using the
101 :meth:`_query.Query.with_session`
102 method.
103
104 For a full walk through of :class:`_query.Query` usage, see the
105 :ref:`ormtutorial_toplevel`.
106
107 """
108
109 # elements that are in Core and can be cached in the same way
110 _where_criteria = ()
111 _having_criteria = ()
112
113 _order_by_clauses = ()
114 _group_by_clauses = ()
115 _limit_clause = None
116 _offset_clause = None
117
118 _distinct = False
119 _distinct_on = ()
120
121 _for_update_arg = None
122 _correlate = ()
123 _auto_correlate = True
124 _from_obj = ()
125 _setup_joins = ()
126 _legacy_setup_joins = ()
127 _label_style = LABEL_STYLE_LEGACY_ORM
128
129 _memoized_select_entities = ()
130
131 _compile_options = ORMCompileState.default_compile_options
132
133 load_options = QueryContext.default_load_options + {
134 "_legacy_uniquing": True
135 }
136
137 _params = util.EMPTY_DICT
138
139 # local Query builder state, not needed for
140 # compilation or execution
141 _aliased_generation = None
142 _enable_assertions = True
143 _last_joined_entity = None
144 _statement = None
145
146 # mirrors that of ClauseElement, used to propagate the "orm"
147 # plugin as well as the "subject" of the plugin, e.g. the mapper
148 # we are querying against.
149 _propagate_attrs = util.immutabledict()
150
151 def __init__(self, entities, session=None):
152 """Construct a :class:`_query.Query` directly.
153
154 E.g.::
155
156 q = Query([User, Address], session=some_session)
157
158 The above is equivalent to::
159
160 q = some_session.query(User, Address)
161
162 :param entities: a sequence of entities and/or SQL expressions.
163
164 :param session: a :class:`.Session` with which the
165 :class:`_query.Query`
166 will be associated. Optional; a :class:`_query.Query`
167 can be associated
168 with a :class:`.Session` generatively via the
169 :meth:`_query.Query.with_session` method as well.
170
171 .. seealso::
172
173 :meth:`.Session.query`
174
175 :meth:`_query.Query.with_session`
176
177 """
178
179 self.session = session
180 self._set_entities(entities)
181
182 def _set_propagate_attrs(self, values):
183 self._propagate_attrs = util.immutabledict(values)
184 return self
185
186 def _set_entities(self, entities):
187 self._raw_columns = [
188 coercions.expect(
189 roles.ColumnsClauseRole,
190 ent,
191 apply_propagate_attrs=self,
192 post_inspect=True,
193 )
194 for ent in util.to_list(entities)
195 ]
196
197 def _entity_from_pre_ent_zero(self):
198 if not self._raw_columns:
199 return None
200
201 ent = self._raw_columns[0]
202
203 if "parententity" in ent._annotations:
204 return ent._annotations["parententity"]
205 elif isinstance(ent, ORMColumnsClauseRole):
206 return ent.entity
207 elif "bundle" in ent._annotations:
208 return ent._annotations["bundle"]
209 else:
210 # label, other SQL expression
211 for element in visitors.iterate(ent):
212 if "parententity" in element._annotations:
213 return element._annotations["parententity"]
214 else:
215 return None
216
217 def _only_full_mapper_zero(self, methname):
218 if (
219 len(self._raw_columns) != 1
220 or "parententity" not in self._raw_columns[0]._annotations
221 or not self._raw_columns[0].is_selectable
222 ):
223 raise sa_exc.InvalidRequestError(
224 "%s() can only be used against "
225 "a single mapped class." % methname
226 )
227
228 return self._raw_columns[0]._annotations["parententity"]
229
230 def _set_select_from(self, obj, set_base_alias):
231 fa = [
232 coercions.expect(
233 roles.StrictFromClauseRole,
234 elem,
235 allow_select=True,
236 apply_propagate_attrs=self,
237 )
238 for elem in obj
239 ]
240
241 self._compile_options += {"_set_base_alias": set_base_alias}
242 self._from_obj = tuple(fa)
243
244 @_generative
245 def _set_lazyload_from(self, state):
246 self.load_options += {"_lazy_loaded_from": state}
247
248 def _get_condition(self):
249 return self._no_criterion_condition(
250 "get", order_by=False, distinct=False
251 )
252
253 def _get_existing_condition(self):
254 self._no_criterion_assertion("get", order_by=False, distinct=False)
255
256 def _no_criterion_assertion(self, meth, order_by=True, distinct=True):
257 if not self._enable_assertions:
258 return
259 if (
260 self._where_criteria
261 or self._statement is not None
262 or self._from_obj
263 or self._legacy_setup_joins
264 or self._limit_clause is not None
265 or self._offset_clause is not None
266 or self._group_by_clauses
267 or (order_by and self._order_by_clauses)
268 or (distinct and self._distinct)
269 ):
270 raise sa_exc.InvalidRequestError(
271 "Query.%s() being called on a "
272 "Query with existing criterion. " % meth
273 )
274
275 def _no_criterion_condition(self, meth, order_by=True, distinct=True):
276 self._no_criterion_assertion(meth, order_by, distinct)
277
278 self._from_obj = self._legacy_setup_joins = ()
279 if self._statement is not None:
280 self._compile_options += {"_statement": None}
281 self._where_criteria = ()
282 self._distinct = False
283
284 self._order_by_clauses = self._group_by_clauses = ()
285
286 def _no_clauseelement_condition(self, meth):
287 if not self._enable_assertions:
288 return
289 if self._order_by_clauses:
290 raise sa_exc.InvalidRequestError(
291 "Query.%s() being called on a "
292 "Query with existing criterion. " % meth
293 )
294 self._no_criterion_condition(meth)
295
296 def _no_statement_condition(self, meth):
297 if not self._enable_assertions:
298 return
299 if self._statement is not None:
300 raise sa_exc.InvalidRequestError(
301 (
302 "Query.%s() being called on a Query with an existing full "
303 "statement - can't apply criterion."
304 )
305 % meth
306 )
307
308 def _no_limit_offset(self, meth):
309 if not self._enable_assertions:
310 return
311 if self._limit_clause is not None or self._offset_clause is not None:
312 raise sa_exc.InvalidRequestError(
313 "Query.%s() being called on a Query which already has LIMIT "
314 "or OFFSET applied. Call %s() before limit() or offset() "
315 "are applied." % (meth, meth)
316 )
317
318 @property
319 def _has_row_limiting_clause(self):
320 return (
321 self._limit_clause is not None or self._offset_clause is not None
322 )
323
324 def _get_options(
325 self,
326 populate_existing=None,
327 version_check=None,
328 only_load_props=None,
329 refresh_state=None,
330 identity_token=None,
331 ):
332 load_options = {}
333 compile_options = {}
334
335 if version_check:
336 load_options["_version_check"] = version_check
337 if populate_existing:
338 load_options["_populate_existing"] = populate_existing
339 if refresh_state:
340 load_options["_refresh_state"] = refresh_state
341 compile_options["_for_refresh_state"] = True
342 if only_load_props:
343 compile_options["_only_load_props"] = frozenset(only_load_props)
344 if identity_token:
345 load_options["_refresh_identity_token"] = identity_token
346
347 if load_options:
348 self.load_options += load_options
349 if compile_options:
350 self._compile_options += compile_options
351
352 return self
353
354 def _clone(self):
355 return self._generate()
356
357 @property
358 def statement(self):
359 """The full SELECT statement represented by this Query.
360
361 The statement by default will not have disambiguating labels
362 applied to the construct unless with_labels(True) is called
363 first.
364
365 """
366
367 # .statement can return the direct future.Select() construct here, as
368 # long as we are not using subsequent adaption features that
369 # are made against raw entities, e.g. from_self(), with_polymorphic(),
370 # select_entity_from(). If these features are being used, then
371 # the Select() we return will not have the correct .selected_columns
372 # collection and will not embed in subsequent queries correctly.
373 # We could find a way to make this collection "correct", however
374 # this would not be too different from doing the full compile as
375 # we are doing in any case, the Select() would still not have the
376 # proper state for other attributes like whereclause, order_by,
377 # and these features are all deprecated in any case.
378 #
379 # for these reasons, Query is not a Select, it remains an ORM
380 # object for which __clause_element__() must be called in order for
381 # it to provide a real expression object.
382 #
383 # from there, it starts to look much like Query itself won't be
384 # passed into the execute process and wont generate its own cache
385 # key; this will all occur in terms of the ORM-enabled Select.
386 if (
387 not self._compile_options._set_base_alias
388 and not self._compile_options._with_polymorphic_adapt_map
389 ):
390 # if we don't have legacy top level aliasing features in use
391 # then convert to a future select() directly
392 stmt = self._statement_20(for_statement=True)
393 else:
394 stmt = self._compile_state(for_statement=True).statement
395
396 if self._params:
397 stmt = stmt.params(self._params)
398
399 return stmt
400
401 def _final_statement(self, legacy_query_style=True):
402 """Return the 'final' SELECT statement for this :class:`.Query`.
403
404 This is the Core-only select() that will be rendered by a complete
405 compilation of this query, and is what .statement used to return
406 in 1.3.
407
408 This method creates a complete compile state so is fairly expensive.
409
410 """
411
412 q = self._clone()
413
414 return q._compile_state(
415 use_legacy_query_style=legacy_query_style
416 ).statement
417
418 def _statement_20(self, for_statement=False, use_legacy_query_style=True):
419 # TODO: this event needs to be deprecated, as it currently applies
420 # only to ORM query and occurs at this spot that is now more
421 # or less an artificial spot
422 if self.dispatch.before_compile:
423 for fn in self.dispatch.before_compile:
424 new_query = fn(self)
425 if new_query is not None and new_query is not self:
426 self = new_query
427 if not fn._bake_ok:
428 self._compile_options += {"_bake_ok": False}
429
430 compile_options = self._compile_options
431 compile_options += {
432 "_for_statement": for_statement,
433 "_use_legacy_query_style": use_legacy_query_style,
434 }
435
436 if self._statement is not None:
437 stmt = FromStatement(self._raw_columns, self._statement)
438 stmt.__dict__.update(
439 _with_options=self._with_options,
440 _with_context_options=self._with_context_options,
441 _compile_options=compile_options,
442 _execution_options=self._execution_options,
443 _propagate_attrs=self._propagate_attrs,
444 )
445 else:
446 # Query / select() internal attributes are 99% cross-compatible
447 stmt = Select._create_raw_select(**self.__dict__)
448 stmt.__dict__.update(
449 _label_style=self._label_style,
450 _compile_options=compile_options,
451 _propagate_attrs=self._propagate_attrs,
452 )
453 stmt.__dict__.pop("session", None)
454
455 # ensure the ORM context is used to compile the statement, even
456 # if it has no ORM entities. This is so ORM-only things like
457 # _legacy_joins are picked up that wouldn't be picked up by the
458 # Core statement context
459 if "compile_state_plugin" not in stmt._propagate_attrs:
460 stmt._propagate_attrs = stmt._propagate_attrs.union(
461 {"compile_state_plugin": "orm", "plugin_subject": None}
462 )
463
464 return stmt
465
466 def subquery(
467 self,
468 name=None,
469 with_labels=False,
470 reduce_columns=False,
471 ):
472 """Return the full SELECT statement represented by
473 this :class:`_query.Query`, embedded within an
474 :class:`_expression.Alias`.
475
476 Eager JOIN generation within the query is disabled.
477
478 :param name: string name to be assigned as the alias;
479 this is passed through to :meth:`_expression.FromClause.alias`.
480 If ``None``, a name will be deterministically generated
481 at compile time.
482
483 :param with_labels: if True, :meth:`.with_labels` will be called
484 on the :class:`_query.Query` first to apply table-qualified labels
485 to all columns.
486
487 :param reduce_columns: if True,
488 :meth:`_expression.Select.reduce_columns` will
489 be called on the resulting :func:`_expression.select` construct,
490 to remove same-named columns where one also refers to the other
491 via foreign key or WHERE clause equivalence.
492
493 """
494 q = self.enable_eagerloads(False)
495 if with_labels:
496 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
497
498 q = q.statement
499
500 if reduce_columns:
501 q = q.reduce_columns()
502 return q.alias(name=name)
503
504 def cte(self, name=None, recursive=False, nesting=False):
505 r"""Return the full SELECT statement represented by this
506 :class:`_query.Query` represented as a common table expression (CTE).
507
508 Parameters and usage are the same as those of the
509 :meth:`_expression.SelectBase.cte` method; see that method for
510 further details.
511
512 Here is the `PostgreSQL WITH
513 RECURSIVE example
514 <https://www.postgresql.org/docs/current/static/queries-with.html>`_.
515 Note that, in this example, the ``included_parts`` cte and the
516 ``incl_alias`` alias of it are Core selectables, which
517 means the columns are accessed via the ``.c.`` attribute. The
518 ``parts_alias`` object is an :func:`_orm.aliased` instance of the
519 ``Part`` entity, so column-mapped attributes are available
520 directly::
521
522 from sqlalchemy.orm import aliased
523
524 class Part(Base):
525 __tablename__ = 'part'
526 part = Column(String, primary_key=True)
527 sub_part = Column(String, primary_key=True)
528 quantity = Column(Integer)
529
530 included_parts = session.query(
531 Part.sub_part,
532 Part.part,
533 Part.quantity).\
534 filter(Part.part=="our part").\
535 cte(name="included_parts", recursive=True)
536
537 incl_alias = aliased(included_parts, name="pr")
538 parts_alias = aliased(Part, name="p")
539 included_parts = included_parts.union_all(
540 session.query(
541 parts_alias.sub_part,
542 parts_alias.part,
543 parts_alias.quantity).\
544 filter(parts_alias.part==incl_alias.c.sub_part)
545 )
546
547 q = session.query(
548 included_parts.c.sub_part,
549 func.sum(included_parts.c.quantity).
550 label('total_quantity')
551 ).\
552 group_by(included_parts.c.sub_part)
553
554 .. seealso::
555
556 :meth:`_expression.HasCTE.cte`
557
558 """
559 return self.enable_eagerloads(False).statement.cte(
560 name=name, recursive=recursive, nesting=nesting
561 )
562
563 def label(self, name):
564 """Return the full SELECT statement represented by this
565 :class:`_query.Query`, converted
566 to a scalar subquery with a label of the given name.
567
568 Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.label`.
569
570 """
571
572 return self.enable_eagerloads(False).statement.label(name)
573
574 @util.deprecated(
575 "1.4",
576 "The :meth:`_query.Query.as_scalar` method is deprecated and will be "
577 "removed in a future release. Please refer to "
578 ":meth:`_query.Query.scalar_subquery`.",
579 )
580 def as_scalar(self):
581 """Return the full SELECT statement represented by this
582 :class:`_query.Query`, converted to a scalar subquery.
583
584 """
585 return self.scalar_subquery()
586
587 def scalar_subquery(self):
588 """Return the full SELECT statement represented by this
589 :class:`_query.Query`, converted to a scalar subquery.
590
591 Analogous to
592 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
593
594 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
595 method replaces the :meth:`_query.Query.as_scalar` method.
596
597 """
598
599 return self.enable_eagerloads(False).statement.scalar_subquery()
600
601 @property
602 def selectable(self):
603 """Return the :class:`_expression.Select` object emitted by this
604 :class:`_query.Query`.
605
606 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
607
608 query.enable_eagerloads(False).with_labels().statement
609
610 """
611 return self.__clause_element__()
612
613 def __clause_element__(self):
614 return (
615 self._with_compile_options(
616 _enable_eagerloads=False, _render_for_subquery=True
617 )
618 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
619 .statement
620 )
621
622 @_generative
623 def only_return_tuples(self, value):
624 """When set to True, the query results will always be a tuple.
625
626 This is specifically for single element queries. The default is False.
627
628 .. versionadded:: 1.2.5
629
630 .. seealso::
631
632 :meth:`_query.Query.is_single_entity`
633
634 """
635 self.load_options += dict(_only_return_tuples=value)
636
637 @property
638 def is_single_entity(self):
639 """Indicates if this :class:`_query.Query`
640 returns tuples or single entities.
641
642 Returns True if this query returns a single entity for each instance
643 in its result list, and False if this query returns a tuple of entities
644 for each result.
645
646 .. versionadded:: 1.3.11
647
648 .. seealso::
649
650 :meth:`_query.Query.only_return_tuples`
651
652 """
653 return (
654 not self.load_options._only_return_tuples
655 and len(self._raw_columns) == 1
656 and "parententity" in self._raw_columns[0]._annotations
657 and isinstance(
658 self._raw_columns[0]._annotations["parententity"],
659 ORMColumnsClauseRole,
660 )
661 )
662
663 @_generative
664 def enable_eagerloads(self, value):
665 """Control whether or not eager joins and subqueries are
666 rendered.
667
668 When set to False, the returned Query will not render
669 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`,
670 :func:`~sqlalchemy.orm.subqueryload` options
671 or mapper-level ``lazy='joined'``/``lazy='subquery'``
672 configurations.
673
674 This is used primarily when nesting the Query's
675 statement into a subquery or other
676 selectable, or when using :meth:`_query.Query.yield_per`.
677
678 """
679 self._compile_options += {"_enable_eagerloads": value}
680
681 @_generative
682 def _with_compile_options(self, **opt):
683 self._compile_options += opt
684
685 @util.deprecated_20(
686 ":meth:`_orm.Query.with_labels` and :meth:`_orm.Query.apply_labels`",
687 alternative="Use set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL) "
688 "instead.",
689 )
690 def with_labels(self):
691 return self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
692
693 apply_labels = with_labels
694
695 @property
696 def get_label_style(self):
697 """
698 Retrieve the current label style.
699
700 .. versionadded:: 1.4
701
702 """
703 return self._label_style
704
705 def set_label_style(self, style):
706 """Apply column labels to the return value of Query.statement.
707
708 Indicates that this Query's `statement` accessor should return
709 a SELECT statement that applies labels to all columns in the
710 form <tablename>_<columnname>; this is commonly used to
711 disambiguate columns from multiple tables which have the same
712 name.
713
714 When the `Query` actually issues SQL to load rows, it always
715 uses column labeling.
716
717 .. note:: The :meth:`_query.Query.set_label_style` method *only* applies
718 the output of :attr:`_query.Query.statement`, and *not* to any of
719 the result-row invoking systems of :class:`_query.Query` itself,
720 e.g.
721 :meth:`_query.Query.first`, :meth:`_query.Query.all`, etc.
722 To execute
723 a query using :meth:`_query.Query.set_label_style`, invoke the
724 :attr:`_query.Query.statement` using :meth:`.Session.execute`::
725
726 result = session.execute(
727 query
728 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
729 .statement
730 )
731
732 .. versionadded:: 1.4
733
734 """ # noqa
735 if self._label_style is not style:
736 self = self._generate()
737 self._label_style = style
738 return self
739
740 @_generative
741 def enable_assertions(self, value):
742 """Control whether assertions are generated.
743
744 When set to False, the returned Query will
745 not assert its state before certain operations,
746 including that LIMIT/OFFSET has not been applied
747 when filter() is called, no criterion exists
748 when get() is called, and no "from_statement()"
749 exists when filter()/order_by()/group_by() etc.
750 is called. This more permissive mode is used by
751 custom Query subclasses to specify criterion or
752 other modifiers outside of the usual usage patterns.
753
754 Care should be taken to ensure that the usage
755 pattern is even possible. A statement applied
756 by from_statement() will override any criterion
757 set by filter() or order_by(), for example.
758
759 """
760 self._enable_assertions = value
761
762 @property
763 def whereclause(self):
764 """A readonly attribute which returns the current WHERE criterion for
765 this Query.
766
767 This returned value is a SQL expression construct, or ``None`` if no
768 criterion has been established.
769
770 """
771 return sql.elements.BooleanClauseList._construct_for_whereclause(
772 self._where_criteria
773 )
774
775 @_generative
776 def _with_current_path(self, path):
777 """indicate that this query applies to objects loaded
778 within a certain path.
779
780 Used by deferred loaders (see strategies.py) which transfer
781 query options from an originating query to a newly generated
782 query intended for the deferred load.
783
784 """
785 self._compile_options += {"_current_path": path}
786
787 @_generative
788 @_assertions(_no_clauseelement_condition)
789 @util.deprecated_20(
790 ":meth:`_orm.Query.with_polymorphic`",
791 alternative="Use the orm.with_polymorphic() standalone function",
792 )
793 def with_polymorphic(
794 self, cls_or_mappers, selectable=None, polymorphic_on=None
795 ):
796 """Load columns for inheriting classes.
797
798 This is a legacy method which is replaced by the
799 :func:`_orm.with_polymorphic` function.
800
801 .. warning:: The :meth:`_orm.Query.with_polymorphic` method does
802 **not** support 1.4/2.0 style features including
803 :func:`_orm.with_loader_criteria`. Please migrate code
804 to use :func:`_orm.with_polymorphic`.
805
806 :meth:`_query.Query.with_polymorphic` applies transformations
807 to the "main" mapped class represented by this :class:`_query.Query`.
808 The "main" mapped class here means the :class:`_query.Query`
809 object's first argument is a full class, i.e.
810 ``session.query(SomeClass)``. These transformations allow additional
811 tables to be present in the FROM clause so that columns for a
812 joined-inheritance subclass are available in the query, both for the
813 purposes of load-time efficiency as well as the ability to use
814 these columns at query time.
815
816 .. seealso::
817
818 :ref:`with_polymorphic` - illustrates current patterns
819
820 """
821
822 entity = _legacy_filter_by_entity_zero(self)
823
824 wp = with_polymorphic(
825 entity,
826 cls_or_mappers,
827 selectable=selectable,
828 polymorphic_on=polymorphic_on,
829 )
830
831 self._compile_options = self._compile_options.add_to_element(
832 "_with_polymorphic_adapt_map", ((entity, inspect(wp)),)
833 )
834
835 @_generative
836 def yield_per(self, count):
837 r"""Yield only ``count`` rows at a time.
838
839 The purpose of this method is when fetching very large result sets
840 (> 10K rows), to batch results in sub-collections and yield them
841 out partially, so that the Python interpreter doesn't need to declare
842 very large areas of memory which is both time consuming and leads
843 to excessive memory use. The performance from fetching hundreds of
844 thousands of rows can often double when a suitable yield-per setting
845 (e.g. approximately 1000) is used, even with DBAPIs that buffer
846 rows (which are most).
847
848 As of SQLAlchemy 1.4, the :meth:`_orm.Query.yield_per` method is
849 equivalent to using the ``yield_per`` execution option at the ORM
850 level. See the section :ref:`orm_queryguide_yield_per` for further
851 background on this option.
852
853 .. seealso::
854
855 :ref:`orm_queryguide_yield_per`
856
857 """
858 self.load_options += {"_yield_per": count}
859
860 @util.deprecated_20(
861 ":meth:`_orm.Query.get`",
862 alternative="The method is now available as :meth:`_orm.Session.get`",
863 becomes_legacy=True,
864 )
865 def get(self, ident):
866 """Return an instance based on the given primary key identifier,
867 or ``None`` if not found.
868
869 E.g.::
870
871 my_user = session.query(User).get(5)
872
873 some_object = session.query(VersionedFoo).get((5, 10))
874
875 some_object = session.query(VersionedFoo).get(
876 {"id": 5, "version_id": 10})
877
878 :meth:`_query.Query.get` is special in that it provides direct
879 access to the identity map of the owning :class:`.Session`.
880 If the given primary key identifier is present
881 in the local identity map, the object is returned
882 directly from this collection and no SQL is emitted,
883 unless the object has been marked fully expired.
884 If not present,
885 a SELECT is performed in order to locate the object.
886
887 :meth:`_query.Query.get` also will perform a check if
888 the object is present in the identity map and
889 marked as expired - a SELECT
890 is emitted to refresh the object as well as to
891 ensure that the row is still present.
892 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised.
893
894 :meth:`_query.Query.get` is only used to return a single
895 mapped instance, not multiple instances or
896 individual column constructs, and strictly
897 on a single primary key value. The originating
898 :class:`_query.Query` must be constructed in this way,
899 i.e. against a single mapped entity,
900 with no additional filtering criterion. Loading
901 options via :meth:`_query.Query.options` may be applied
902 however, and will be used if the object is not
903 yet locally present.
904
905 :param ident: A scalar, tuple, or dictionary representing the
906 primary key. For a composite (e.g. multiple column) primary key,
907 a tuple or dictionary should be passed.
908
909 For a single-column primary key, the scalar calling form is typically
910 the most expedient. If the primary key of a row is the value "5",
911 the call looks like::
912
913 my_object = query.get(5)
914
915 The tuple form contains primary key values typically in
916 the order in which they correspond to the mapped
917 :class:`_schema.Table`
918 object's primary key columns, or if the
919 :paramref:`_orm.Mapper.primary_key` configuration parameter were
920 used, in
921 the order used for that parameter. For example, if the primary key
922 of a row is represented by the integer
923 digits "5, 10" the call would look like::
924
925 my_object = query.get((5, 10))
926
927 The dictionary form should include as keys the mapped attribute names
928 corresponding to each element of the primary key. If the mapped class
929 has the attributes ``id``, ``version_id`` as the attributes which
930 store the object's primary key value, the call would look like::
931
932 my_object = query.get({"id": 5, "version_id": 10})
933
934 .. versionadded:: 1.3 the :meth:`_query.Query.get`
935 method now optionally
936 accepts a dictionary of attribute names to values in order to
937 indicate a primary key identifier.
938
939
940 :return: The object instance, or ``None``.
941
942 """
943 self._no_criterion_assertion("get", order_by=False, distinct=False)
944
945 # we still implement _get_impl() so that baked query can override
946 # it
947 return self._get_impl(ident, loading.load_on_pk_identity)
948
949 def _get_impl(self, primary_key_identity, db_load_fn, identity_token=None):
950 mapper = self._only_full_mapper_zero("get")
951 return self.session._get_impl(
952 mapper,
953 primary_key_identity,
954 db_load_fn,
955 populate_existing=self.load_options._populate_existing,
956 with_for_update=self._for_update_arg,
957 options=self._with_options,
958 identity_token=identity_token,
959 execution_options=self._execution_options,
960 )
961
962 @property
963 def lazy_loaded_from(self):
964 """An :class:`.InstanceState` that is using this :class:`_query.Query`
965 for a lazy load operation.
966
967 .. deprecated:: 1.4 This attribute should be viewed via the
968 :attr:`.ORMExecuteState.lazy_loaded_from` attribute, within
969 the context of the :meth:`.SessionEvents.do_orm_execute`
970 event.
971
972 .. seealso::
973
974 :attr:`.ORMExecuteState.lazy_loaded_from`
975
976 """
977 return self.load_options._lazy_loaded_from
978
979 @property
980 def _current_path(self):
981 return self._compile_options._current_path
982
983 @_generative
984 def correlate(self, *fromclauses):
985 """Return a :class:`.Query` construct which will correlate the given
986 FROM clauses to that of an enclosing :class:`.Query` or
987 :func:`~.expression.select`.
988
989 The method here accepts mapped classes, :func:`.aliased` constructs,
990 and :func:`.mapper` constructs as arguments, which are resolved into
991 expression constructs, in addition to appropriate expression
992 constructs.
993
994 The correlation arguments are ultimately passed to
995 :meth:`_expression.Select.correlate`
996 after coercion to expression constructs.
997
998 The correlation arguments take effect in such cases
999 as when :meth:`_query.Query.from_self` is used, or when
1000 a subquery as returned by :meth:`_query.Query.subquery` is
1001 embedded in another :func:`_expression.select` construct.
1002
1003 """
1004
1005 self._auto_correlate = False
1006 if fromclauses and fromclauses[0] in {None, False}:
1007 self._correlate = ()
1008 else:
1009 self._correlate = set(self._correlate).union(
1010 coercions.expect(roles.FromClauseRole, f) for f in fromclauses
1011 )
1012
1013 @_generative
1014 def autoflush(self, setting):
1015 """Return a Query with a specific 'autoflush' setting.
1016
1017 As of SQLAlchemy 1.4, the :meth:`_orm.Query.autoflush` method
1018 is equivalent to using the ``autoflush`` execution option at the
1019 ORM level. See the section :ref:`orm_queryguide_autoflush` for
1020 further background on this option.
1021
1022 """
1023 self.load_options += {"_autoflush": setting}
1024
1025 @_generative
1026 def populate_existing(self):
1027 """Return a :class:`_query.Query`
1028 that will expire and refresh all instances
1029 as they are loaded, or reused from the current :class:`.Session`.
1030
1031 As of SQLAlchemy 1.4, the :meth:`_orm.Query.populate_existing` method
1032 is equivalent to using the ``populate_existing`` execution option at
1033 the ORM level. See the section :ref:`orm_queryguide_populate_existing`
1034 for further background on this option.
1035
1036 """
1037 self.load_options += {"_populate_existing": True}
1038
1039 @_generative
1040 def _with_invoke_all_eagers(self, value):
1041 """Set the 'invoke all eagers' flag which causes joined- and
1042 subquery loaders to traverse into already-loaded related objects
1043 and collections.
1044
1045 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1046
1047 """
1048 self.load_options += {"_invoke_all_eagers": value}
1049
1050 @util.deprecated_20(
1051 ":meth:`_orm.Query.with_parent`",
1052 alternative="Use the :func:`_orm.with_parent` standalone construct.",
1053 becomes_legacy=True,
1054 )
1055 @util.preload_module("sqlalchemy.orm.relationships")
1056 def with_parent(self, instance, property=None, from_entity=None): # noqa
1057 """Add filtering criterion that relates the given instance
1058 to a child object or collection, using its attribute state
1059 as well as an established :func:`_orm.relationship()`
1060 configuration.
1061
1062 The method uses the :func:`.with_parent` function to generate
1063 the clause, the result of which is passed to
1064 :meth:`_query.Query.filter`.
1065
1066 Parameters are the same as :func:`.with_parent`, with the exception
1067 that the given property can be None, in which case a search is
1068 performed against this :class:`_query.Query` object's target mapper.
1069
1070 :param instance:
1071 An instance which has some :func:`_orm.relationship`.
1072
1073 :param property:
1074 String property name, or class-bound attribute, which indicates
1075 what relationship from the instance should be used to reconcile the
1076 parent/child relationship.
1077
1078 :param from_entity:
1079 Entity in which to consider as the left side. This defaults to the
1080 "zero" entity of the :class:`_query.Query` itself.
1081
1082 """
1083 relationships = util.preloaded.orm_relationships
1084
1085 if from_entity:
1086 entity_zero = inspect(from_entity)
1087 else:
1088 entity_zero = _legacy_filter_by_entity_zero(self)
1089 if property is None:
1090 # TODO: deprecate, property has to be supplied
1091 mapper = object_mapper(instance)
1092
1093 for prop in mapper.iterate_properties:
1094 if (
1095 isinstance(prop, relationships.RelationshipProperty)
1096 and prop.mapper is entity_zero.mapper
1097 ):
1098 property = prop # noqa
1099 break
1100 else:
1101 raise sa_exc.InvalidRequestError(
1102 "Could not locate a property which relates instances "
1103 "of class '%s' to instances of class '%s'"
1104 % (
1105 entity_zero.mapper.class_.__name__,
1106 instance.__class__.__name__,
1107 )
1108 )
1109
1110 return self.filter(with_parent(instance, property, entity_zero.entity))
1111
1112 @_generative
1113 def add_entity(self, entity, alias=None):
1114 """add a mapped entity to the list of result columns
1115 to be returned."""
1116
1117 if alias is not None:
1118 # TODO: deprecate
1119 entity = aliased(entity, alias)
1120
1121 self._raw_columns = list(self._raw_columns)
1122
1123 self._raw_columns.append(
1124 coercions.expect(
1125 roles.ColumnsClauseRole, entity, apply_propagate_attrs=self
1126 )
1127 )
1128
1129 @_generative
1130 def with_session(self, session):
1131 """Return a :class:`_query.Query` that will use the given
1132 :class:`.Session`.
1133
1134 While the :class:`_query.Query`
1135 object is normally instantiated using the
1136 :meth:`.Session.query` method, it is legal to build the
1137 :class:`_query.Query`
1138 directly without necessarily using a :class:`.Session`. Such a
1139 :class:`_query.Query` object, or any :class:`_query.Query`
1140 already associated
1141 with a different :class:`.Session`, can produce a new
1142 :class:`_query.Query`
1143 object associated with a target session using this method::
1144
1145 from sqlalchemy.orm import Query
1146
1147 query = Query([MyClass]).filter(MyClass.id == 5)
1148
1149 result = query.with_session(my_session).one()
1150
1151 """
1152
1153 self.session = session
1154
1155 @util.deprecated_20(
1156 ":meth:`_query.Query.from_self`",
1157 alternative="The new approach is to use the :func:`.orm.aliased` "
1158 "construct in conjunction with a subquery. See the section "
1159 ":ref:`Selecting from the query itself as a subquery "
1160 "<migration_20_query_from_self>` in the 2.0 migration notes for an "
1161 "example.",
1162 )
1163 def from_self(self, *entities):
1164 r"""return a Query that selects from this Query's
1165 SELECT statement.
1166
1167 :meth:`_query.Query.from_self` essentially turns the SELECT statement
1168 into a SELECT of itself. Given a query such as::
1169
1170 q = session.query(User).filter(User.name.like('e%'))
1171
1172 Given the :meth:`_query.Query.from_self` version::
1173
1174 q = session.query(User).filter(User.name.like('e%')).from_self()
1175
1176 This query renders as:
1177
1178 .. sourcecode:: sql
1179
1180 SELECT anon_1.user_id AS anon_1_user_id,
1181 anon_1.user_name AS anon_1_user_name
1182 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1183 FROM "user"
1184 WHERE "user".name LIKE :name_1) AS anon_1
1185
1186 There are lots of cases where :meth:`_query.Query.from_self`
1187 may be useful.
1188 A simple one is where above, we may want to apply a row LIMIT to
1189 the set of user objects we query against, and then apply additional
1190 joins against that row-limited set::
1191
1192 q = session.query(User).filter(User.name.like('e%')).\
1193 limit(5).from_self().\
1194 join(User.addresses).filter(Address.email.like('q%'))
1195
1196 The above query joins to the ``Address`` entity but only against the
1197 first five results of the ``User`` query:
1198
1199 .. sourcecode:: sql
1200
1201 SELECT anon_1.user_id AS anon_1_user_id,
1202 anon_1.user_name AS anon_1_user_name
1203 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1204 FROM "user"
1205 WHERE "user".name LIKE :name_1
1206 LIMIT :param_1) AS anon_1
1207 JOIN address ON anon_1.user_id = address.user_id
1208 WHERE address.email LIKE :email_1
1209
1210 **Automatic Aliasing**
1211
1212 Another key behavior of :meth:`_query.Query.from_self`
1213 is that it applies
1214 **automatic aliasing** to the entities inside the subquery, when
1215 they are referenced on the outside. Above, if we continue to
1216 refer to the ``User`` entity without any additional aliasing applied
1217 to it, those references will be in terms of the subquery::
1218
1219 q = session.query(User).filter(User.name.like('e%')).\
1220 limit(5).from_self().\
1221 join(User.addresses).filter(Address.email.like('q%')).\
1222 order_by(User.name)
1223
1224 The ORDER BY against ``User.name`` is aliased to be in terms of the
1225 inner subquery:
1226
1227 .. sourcecode:: sql
1228
1229 SELECT anon_1.user_id AS anon_1_user_id,
1230 anon_1.user_name AS anon_1_user_name
1231 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1232 FROM "user"
1233 WHERE "user".name LIKE :name_1
1234 LIMIT :param_1) AS anon_1
1235 JOIN address ON anon_1.user_id = address.user_id
1236 WHERE address.email LIKE :email_1 ORDER BY anon_1.user_name
1237
1238 The automatic aliasing feature only works in a **limited** way,
1239 for simple filters and orderings. More ambitious constructions
1240 such as referring to the entity in joins should prefer to use
1241 explicit subquery objects, typically making use of the
1242 :meth:`_query.Query.subquery`
1243 method to produce an explicit subquery object.
1244 Always test the structure of queries by viewing the SQL to ensure
1245 a particular structure does what's expected!
1246
1247 **Changing the Entities**
1248
1249 :meth:`_query.Query.from_self`
1250 also includes the ability to modify what
1251 columns are being queried. In our example, we want ``User.id``
1252 to be queried by the inner query, so that we can join to the
1253 ``Address`` entity on the outside, but we only wanted the outer
1254 query to return the ``Address.email`` column::
1255
1256 q = session.query(User).filter(User.name.like('e%')).\
1257 limit(5).from_self(Address.email).\
1258 join(User.addresses).filter(Address.email.like('q%'))
1259
1260 yielding:
1261
1262 .. sourcecode:: sql
1263
1264 SELECT address.email AS address_email
1265 FROM (SELECT "user".id AS user_id, "user".name AS user_name
1266 FROM "user"
1267 WHERE "user".name LIKE :name_1
1268 LIMIT :param_1) AS anon_1
1269 JOIN address ON anon_1.user_id = address.user_id
1270 WHERE address.email LIKE :email_1
1271
1272 **Looking out for Inner / Outer Columns**
1273
1274 Keep in mind that when referring to columns that originate from
1275 inside the subquery, we need to ensure they are present in the
1276 columns clause of the subquery itself; this is an ordinary aspect of
1277 SQL. For example, if we wanted to load from a joined entity inside
1278 the subquery using :func:`.contains_eager`, we need to add those
1279 columns. Below illustrates a join of ``Address`` to ``User``,
1280 then a subquery, and then we'd like :func:`.contains_eager` to access
1281 the ``User`` columns::
1282
1283 q = session.query(Address).join(Address.user).\
1284 filter(User.name.like('e%'))
1285
1286 q = q.add_entity(User).from_self().\
1287 options(contains_eager(Address.user))
1288
1289 We use :meth:`_query.Query.add_entity` above **before** we call
1290 :meth:`_query.Query.from_self`
1291 so that the ``User`` columns are present
1292 in the inner subquery, so that they are available to the
1293 :func:`.contains_eager` modifier we are using on the outside,
1294 producing:
1295
1296 .. sourcecode:: sql
1297
1298 SELECT anon_1.address_id AS anon_1_address_id,
1299 anon_1.address_email AS anon_1_address_email,
1300 anon_1.address_user_id AS anon_1_address_user_id,
1301 anon_1.user_id AS anon_1_user_id,
1302 anon_1.user_name AS anon_1_user_name
1303 FROM (
1304 SELECT address.id AS address_id,
1305 address.email AS address_email,
1306 address.user_id AS address_user_id,
1307 "user".id AS user_id,
1308 "user".name AS user_name
1309 FROM address JOIN "user" ON "user".id = address.user_id
1310 WHERE "user".name LIKE :name_1) AS anon_1
1311
1312 If we didn't call ``add_entity(User)``, but still asked
1313 :func:`.contains_eager` to load the ``User`` entity, it would be
1314 forced to add the table on the outside without the correct
1315 join criteria - note the ``anon1, "user"`` phrase at
1316 the end:
1317
1318 .. sourcecode:: sql
1319
1320 -- incorrect query
1321 SELECT anon_1.address_id AS anon_1_address_id,
1322 anon_1.address_email AS anon_1_address_email,
1323 anon_1.address_user_id AS anon_1_address_user_id,
1324 "user".id AS user_id,
1325 "user".name AS user_name
1326 FROM (
1327 SELECT address.id AS address_id,
1328 address.email AS address_email,
1329 address.user_id AS address_user_id
1330 FROM address JOIN "user" ON "user".id = address.user_id
1331 WHERE "user".name LIKE :name_1) AS anon_1, "user"
1332
1333 :param \*entities: optional list of entities which will replace
1334 those being selected.
1335
1336 """
1337 return self._from_self(*entities)
1338
1339 def _from_self(self, *entities):
1340 fromclause = (
1341 self.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
1342 .correlate(None)
1343 .subquery()
1344 ._anonymous_fromclause()
1345 )
1346
1347 q = self._from_selectable(fromclause)
1348
1349 if entities:
1350 q._set_entities(entities)
1351 return q
1352
1353 @_generative
1354 def _set_enable_single_crit(self, val):
1355 self._compile_options += {"_enable_single_crit": val}
1356
1357 @_generative
1358 def _from_selectable(self, fromclause, set_entity_from=True):
1359 for attr in (
1360 "_where_criteria",
1361 "_order_by_clauses",
1362 "_group_by_clauses",
1363 "_limit_clause",
1364 "_offset_clause",
1365 "_last_joined_entity",
1366 "_legacy_setup_joins",
1367 "_memoized_select_entities",
1368 "_distinct",
1369 "_distinct_on",
1370 "_having_criteria",
1371 "_prefixes",
1372 "_suffixes",
1373 ):
1374 self.__dict__.pop(attr, None)
1375 self._set_select_from([fromclause], set_entity_from)
1376 self._compile_options += {
1377 "_enable_single_crit": False,
1378 }
1379
1380 # this enables clause adaptation for non-ORM
1381 # expressions.
1382 # legacy. see test/orm/test_froms.py for various
1383 # "oldstyle" tests that rely on this and the corresponding
1384 # "newtyle" that do not.
1385 self._compile_options += {"_orm_only_from_obj_alias": False}
1386
1387 @util.deprecated(
1388 "1.4",
1389 ":meth:`_query.Query.values` "
1390 "is deprecated and will be removed in a "
1391 "future release. Please use :meth:`_query.Query.with_entities`",
1392 )
1393 def values(self, *columns):
1394 """Return an iterator yielding result tuples corresponding
1395 to the given list of columns
1396
1397 """
1398
1399 if not columns:
1400 return iter(())
1401 q = self._clone().enable_eagerloads(False)
1402 q._set_entities(columns)
1403 if not q.load_options._yield_per:
1404 q.load_options += {"_yield_per": 10}
1405 return iter(q)
1406
1407 _values = values
1408
1409 @util.deprecated(
1410 "1.4",
1411 ":meth:`_query.Query.value` "
1412 "is deprecated and will be removed in a "
1413 "future release. Please use :meth:`_query.Query.with_entities` "
1414 "in combination with :meth:`_query.Query.scalar`",
1415 )
1416 def value(self, column):
1417 """Return a scalar result corresponding to the given
1418 column expression.
1419
1420 """
1421 try:
1422 return next(self.values(column))[0]
1423 except StopIteration:
1424 return None
1425
1426 @_generative
1427 def with_entities(self, *entities):
1428 r"""Return a new :class:`_query.Query`
1429 replacing the SELECT list with the
1430 given entities.
1431
1432 e.g.::
1433
1434 # Users, filtered on some arbitrary criterion
1435 # and then ordered by related email address
1436 q = session.query(User).\
1437 join(User.address).\
1438 filter(User.name.like('%ed%')).\
1439 order_by(Address.email)
1440
1441 # given *only* User.id==5, Address.email, and 'q', what
1442 # would the *next* User in the result be ?
1443 subq = q.with_entities(Address.email).\
1444 order_by(None).\
1445 filter(User.id==5).\
1446 subquery()
1447 q = q.join((subq, subq.c.email < Address.email)).\
1448 limit(1)
1449
1450 """
1451 _MemoizedSelectEntities._generate_for_statement(self)
1452 self._set_entities(entities)
1453
1454 @_generative
1455 def add_columns(self, *column):
1456 """Add one or more column expressions to the list
1457 of result columns to be returned."""
1458
1459 self._raw_columns = list(self._raw_columns)
1460
1461 self._raw_columns.extend(
1462 coercions.expect(
1463 roles.ColumnsClauseRole,
1464 c,
1465 apply_propagate_attrs=self,
1466 post_inspect=True,
1467 )
1468 for c in column
1469 )
1470
1471 @util.deprecated(
1472 "1.4",
1473 ":meth:`_query.Query.add_column` "
1474 "is deprecated and will be removed in a "
1475 "future release. Please use :meth:`_query.Query.add_columns`",
1476 )
1477 def add_column(self, column):
1478 """Add a column expression to the list of result columns to be
1479 returned.
1480
1481 """
1482 return self.add_columns(column)
1483
1484 @_generative
1485 def options(self, *args):
1486 """Return a new :class:`_query.Query` object,
1487 applying the given list of
1488 mapper options.
1489
1490 Most supplied options regard changing how column- and
1491 relationship-mapped attributes are loaded.
1492
1493 .. seealso::
1494
1495 :ref:`deferred_options`
1496
1497 :ref:`relationship_loader_options`
1498
1499 """
1500
1501 opts = tuple(util.flatten_iterator(args))
1502 if self._compile_options._current_path:
1503 for opt in opts:
1504 if opt._is_legacy_option:
1505 opt.process_query_conditionally(self)
1506 else:
1507 for opt in opts:
1508 if opt._is_legacy_option:
1509 opt.process_query(self)
1510
1511 self._with_options += opts
1512
1513 def with_transformation(self, fn):
1514 """Return a new :class:`_query.Query` object transformed by
1515 the given function.
1516
1517 E.g.::
1518
1519 def filter_something(criterion):
1520 def transform(q):
1521 return q.filter(criterion)
1522 return transform
1523
1524 q = q.with_transformation(filter_something(x==5))
1525
1526 This allows ad-hoc recipes to be created for :class:`_query.Query`
1527 objects. See the example at :ref:`hybrid_transformers`.
1528
1529 """
1530 return fn(self)
1531
1532 def get_execution_options(self):
1533 """Get the non-SQL options which will take effect during execution.
1534
1535 .. versionadded:: 1.3
1536
1537 .. seealso::
1538
1539 :meth:`_query.Query.execution_options`
1540 """
1541 return self._execution_options
1542
1543 @_generative
1544 def execution_options(self, **kwargs):
1545 """Set non-SQL options which take effect during execution.
1546
1547 Options allowed here include all of those accepted by
1548 :meth:`_engine.Connection.execution_options`, as well as a series
1549 of ORM specific options:
1550
1551 ``populate_existing=True`` - equivalent to using
1552 :meth:`_orm.Query.populate_existing`
1553
1554 ``autoflush=True|False`` - equivalent to using
1555 :meth:`_orm.Query.autoflush`
1556
1557 ``yield_per=<value>`` - equivalent to using
1558 :meth:`_orm.Query.yield_per`
1559
1560 Note that the ``stream_results`` execution option is enabled
1561 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()`
1562 method or execution option is used.
1563
1564 .. versionadded:: 1.4 - added ORM options to
1565 :meth:`_orm.Query.execution_options`
1566
1567 The execution options may also be specified on a per execution basis
1568 when using :term:`2.0 style` queries via the
1569 :paramref:`_orm.Session.execution_options` parameter.
1570
1571 .. warning:: The
1572 :paramref:`_engine.Connection.execution_options.stream_results`
1573 parameter should not be used at the level of individual ORM
1574 statement executions, as the :class:`_orm.Session` will not track
1575 objects from different schema translate maps within a single
1576 session. For multiple schema translate maps within the scope of a
1577 single :class:`_orm.Session`, see :ref:`examples_sharding`.
1578
1579
1580 .. seealso::
1581
1582 :ref:`engine_stream_results`
1583
1584 :meth:`_query.Query.get_execution_options`
1585
1586 """
1587 self._execution_options = self._execution_options.union(kwargs)
1588
1589 @_generative
1590 def with_for_update(
1591 self,
1592 read=False,
1593 nowait=False,
1594 of=None,
1595 skip_locked=False,
1596 key_share=False,
1597 ):
1598 """return a new :class:`_query.Query`
1599 with the specified options for the
1600 ``FOR UPDATE`` clause.
1601
1602 The behavior of this method is identical to that of
1603 :meth:`_expression.GenerativeSelect.with_for_update`.
1604 When called with no arguments,
1605 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause
1606 appended. When additional arguments are specified, backend-specific
1607 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE``
1608 can take effect.
1609
1610 E.g.::
1611
1612 q = sess.query(User).populate_existing().with_for_update(nowait=True, of=User)
1613
1614 The above query on a PostgreSQL backend will render like::
1615
1616 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1617
1618 .. warning::
1619
1620 Using ``with_for_update`` in the context of eager loading
1621 relationships is not officially supported or recommended by
1622 SQLAlchemy and may not work with certain queries on various
1623 database backends. When ``with_for_update`` is successfully used
1624 with a query that involves :func:`_orm.joinedload`, SQLAlchemy will
1625 attempt to emit SQL that locks all involved tables.
1626
1627 .. note:: It is generally a good idea to combine the use of the
1628 :meth:`_orm.Query.populate_existing` method when using the
1629 :meth:`_orm.Query.with_for_update` method. The purpose of
1630 :meth:`_orm.Query.populate_existing` is to force all the data read
1631 from the SELECT to be populated into the ORM objects returned,
1632 even if these objects are already in the :term:`identity map`.
1633
1634 .. seealso::
1635
1636 :meth:`_expression.GenerativeSelect.with_for_update`
1637 - Core level method with
1638 full argument and behavioral description.
1639
1640 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1641 objects already loaded in the identity map.
1642
1643 """ # noqa: E501
1644
1645 self._for_update_arg = ForUpdateArg(
1646 read=read,
1647 nowait=nowait,
1648 of=of,
1649 skip_locked=skip_locked,
1650 key_share=key_share,
1651 )
1652
1653 @_generative
1654 def params(self, *args, **kwargs):
1655 r"""Add values for bind parameters which may have been
1656 specified in filter().
1657
1658 Parameters may be specified using \**kwargs, or optionally a single
1659 dictionary as the first positional argument. The reason for both is
1660 that \**kwargs is convenient, however some parameter dictionaries
1661 contain unicode keys in which case \**kwargs cannot be used.
1662
1663 """
1664 if len(args) == 1:
1665 kwargs.update(args[0])
1666 elif len(args) > 0:
1667 raise sa_exc.ArgumentError(
1668 "params() takes zero or one positional argument, "
1669 "which is a dictionary."
1670 )
1671 self._params = self._params.union(kwargs)
1672
1673 def where(self, *criterion):
1674 """A synonym for :meth:`.Query.filter`.
1675
1676 .. versionadded:: 1.4
1677
1678 """
1679 return self.filter(*criterion)
1680
1681 @_generative
1682 @_assertions(_no_statement_condition, _no_limit_offset)
1683 def filter(self, *criterion):
1684 r"""Apply the given filtering criterion to a copy
1685 of this :class:`_query.Query`, using SQL expressions.
1686
1687 e.g.::
1688
1689 session.query(MyClass).filter(MyClass.name == 'some name')
1690
1691 Multiple criteria may be specified as comma separated; the effect
1692 is that they will be joined together using the :func:`.and_`
1693 function::
1694
1695 session.query(MyClass).\
1696 filter(MyClass.name == 'some name', MyClass.id > 5)
1697
1698 The criterion is any SQL expression object applicable to the
1699 WHERE clause of a select. String expressions are coerced
1700 into SQL expression constructs via the :func:`_expression.text`
1701 construct.
1702
1703 .. seealso::
1704
1705 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1706
1707 """
1708 for criterion in list(criterion):
1709 criterion = coercions.expect(
1710 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1711 )
1712
1713 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
1714 if self._aliased_generation:
1715 criterion = sql_util._deep_annotate(
1716 criterion, {"aliased_generation": self._aliased_generation}
1717 )
1718 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^^
1719
1720 self._where_criteria += (criterion,)
1721
1722 @util.memoized_property
1723 def _last_joined_entity(self): # noqa: F811
1724 if self._legacy_setup_joins:
1725 return _legacy_determine_last_joined_entity(
1726 self._legacy_setup_joins, self._entity_from_pre_ent_zero()
1727 )
1728 else:
1729 return None
1730
1731 def _filter_by_zero(self):
1732 """for the filter_by() method, return the target entity for which
1733 we will attempt to derive an expression from based on string name.
1734
1735 """
1736
1737 if self._legacy_setup_joins:
1738 _last_joined_entity = self._last_joined_entity
1739 if _last_joined_entity is not None:
1740 return _last_joined_entity
1741
1742 # discussion related to #7239
1743 # special check determines if we should try to derive attributes
1744 # for filter_by() from the "from object", i.e., if the user
1745 # called query.select_from(some selectable).filter_by(some_attr=value).
1746 # We don't want to do that in the case that methods like
1747 # from_self(), select_entity_from(), or a set op like union() were
1748 # called; while these methods also place a
1749 # selectable in the _from_obj collection, they also set up
1750 # the _set_base_alias boolean which turns on the whole "adapt the
1751 # entity to this selectable" thing, meaning the query still continues
1752 # to construct itself in terms of the lead entity that was passed
1753 # to query(), e.g. query(User).from_self() is still in terms of User,
1754 # and not the subquery that from_self() created. This feature of
1755 # "implicitly adapt all occurrences of entity X to some arbitrary
1756 # subquery" is the main thing I am trying to do away with in 2.0 as
1757 # users should now used aliased() for that, but I can't entirely get
1758 # rid of it due to query.union() and other set ops relying upon it.
1759 #
1760 # compare this to the base Select()._filter_by_zero() which can
1761 # just return self._from_obj[0] if present, because there is no
1762 # "_set_base_alias" feature.
1763 #
1764 # IOW, this conditional essentially detects if
1765 # "select_from(some_selectable)" has been called, as opposed to
1766 # "select_entity_from()", "from_self()"
1767 # or "union() / some_set_op()".
1768 if self._from_obj and not self._compile_options._set_base_alias:
1769 return self._from_obj[0]
1770
1771 return self._raw_columns[0]
1772
1773 def filter_by(self, **kwargs):
1774 r"""Apply the given filtering criterion to a copy
1775 of this :class:`_query.Query`, using keyword expressions.
1776
1777 e.g.::
1778
1779 session.query(MyClass).filter_by(name = 'some name')
1780
1781 Multiple criteria may be specified as comma separated; the effect
1782 is that they will be joined together using the :func:`.and_`
1783 function::
1784
1785 session.query(MyClass).\
1786 filter_by(name = 'some name', id = 5)
1787
1788 The keyword expressions are extracted from the primary
1789 entity of the query, or the last entity that was the
1790 target of a call to :meth:`_query.Query.join`.
1791
1792 .. seealso::
1793
1794 :meth:`_query.Query.filter` - filter on SQL expressions.
1795
1796 """
1797 from_entity = self._filter_by_zero()
1798 if from_entity is None:
1799 raise sa_exc.InvalidRequestError(
1800 "Can't use filter_by when the first entity '%s' of a query "
1801 "is not a mapped class. Please use the filter method instead, "
1802 "or change the order of the entities in the query"
1803 % self._query_entity_zero()
1804 )
1805
1806 clauses = [
1807 _entity_namespace_key(from_entity, key) == value
1808 for key, value in kwargs.items()
1809 ]
1810 return self.filter(*clauses)
1811
1812 @_generative
1813 @_assertions(_no_statement_condition, _no_limit_offset)
1814 def order_by(self, *clauses):
1815 """Apply one or more ORDER BY criteria to the query and return
1816 the newly resulting :class:`_query.Query`.
1817
1818 e.g.::
1819
1820 q = session.query(Entity).order_by(Entity.id, Entity.name)
1821
1822 Calling this method multiple times is equivalent to calling it once
1823 with all the clauses concatenated. All existing ORDER BY criteria may
1824 be cancelled by passing ``None`` by itself. New ORDER BY criteria may
1825 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::
1826
1827 # will erase all ORDER BY and ORDER BY new_col alone
1828 q = q.order_by(None).order_by(new_col)
1829
1830 .. seealso::
1831
1832 These sections describe ORDER BY in terms of :term:`2.0 style`
1833 invocation but apply to :class:`_orm.Query` as well:
1834
1835 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
1836
1837 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
1838
1839 """
1840
1841 if len(clauses) == 1 and (clauses[0] is None or clauses[0] is False):
1842 self._order_by_clauses = ()
1843 else:
1844 criterion = tuple(
1845 coercions.expect(roles.OrderByRole, clause)
1846 for clause in clauses
1847 )
1848 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
1849 if self._aliased_generation:
1850 criterion = tuple(
1851 [
1852 sql_util._deep_annotate(
1853 o, {"aliased_generation": self._aliased_generation}
1854 )
1855 for o in criterion
1856 ]
1857 )
1858 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1859
1860 self._order_by_clauses += criterion
1861
1862 @_generative
1863 @_assertions(_no_statement_condition, _no_limit_offset)
1864 def group_by(self, *clauses):
1865 """Apply one or more GROUP BY criterion to the query and return
1866 the newly resulting :class:`_query.Query`.
1867
1868 All existing GROUP BY settings can be suppressed by
1869 passing ``None`` - this will suppress any GROUP BY configured
1870 on mappers as well.
1871
1872 .. seealso::
1873
1874 These sections describe GROUP BY in terms of :term:`2.0 style`
1875 invocation but apply to :class:`_orm.Query` as well:
1876
1877 :ref:`tutorial_group_by_w_aggregates` - in the
1878 :ref:`unified_tutorial`
1879
1880 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
1881
1882 """
1883
1884 if len(clauses) == 1 and (clauses[0] is None or clauses[0] is False):
1885 self._group_by_clauses = ()
1886 else:
1887 criterion = tuple(
1888 coercions.expect(roles.GroupByRole, clause)
1889 for clause in clauses
1890 )
1891 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
1892 if self._aliased_generation:
1893 criterion = tuple(
1894 [
1895 sql_util._deep_annotate(
1896 o, {"aliased_generation": self._aliased_generation}
1897 )
1898 for o in criterion
1899 ]
1900 )
1901 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^
1902
1903 self._group_by_clauses += criterion
1904
1905 @_generative
1906 @_assertions(_no_statement_condition, _no_limit_offset)
1907 def having(self, criterion):
1908 r"""Apply a HAVING criterion to the query and return the
1909 newly resulting :class:`_query.Query`.
1910
1911 :meth:`_query.Query.having` is used in conjunction with
1912 :meth:`_query.Query.group_by`.
1913
1914 HAVING criterion makes it possible to use filters on aggregate
1915 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
1916
1917 q = session.query(User.id).\
1918 join(User.addresses).\
1919 group_by(User.id).\
1920 having(func.count(Address.id) > 2)
1921
1922 """
1923
1924 self._having_criteria += (
1925 coercions.expect(
1926 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1927 ),
1928 )
1929
1930 def _set_op(self, expr_fn, *q):
1931 return self._from_selectable(expr_fn(*([self] + list(q))).subquery())
1932
1933 def union(self, *q):
1934 """Produce a UNION of this Query against one or more queries.
1935
1936 e.g.::
1937
1938 q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
1939 q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
1940
1941 q3 = q1.union(q2)
1942
1943 The method accepts multiple Query objects so as to control
1944 the level of nesting. A series of ``union()`` calls such as::
1945
1946 x.union(y).union(z).all()
1947
1948 will nest on each ``union()``, and produces::
1949
1950 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
1951 SELECT * FROM y) UNION SELECT * FROM Z)
1952
1953 Whereas::
1954
1955 x.union(y, z).all()
1956
1957 produces::
1958
1959 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
1960 SELECT * FROM Z)
1961
1962 Note that many database backends do not allow ORDER BY to
1963 be rendered on a query called within UNION, EXCEPT, etc.
1964 To disable all ORDER BY clauses including those configured
1965 on mappers, issue ``query.order_by(None)`` - the resulting
1966 :class:`_query.Query` object will not render ORDER BY within
1967 its SELECT statement.
1968
1969 """
1970 return self._set_op(expression.union, *q)
1971
1972 def union_all(self, *q):
1973 """Produce a UNION ALL of this Query against one or more queries.
1974
1975 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
1976 that method for usage examples.
1977
1978 """
1979 return self._set_op(expression.union_all, *q)
1980
1981 def intersect(self, *q):
1982 """Produce an INTERSECT of this Query against one or more queries.
1983
1984 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
1985 that method for usage examples.
1986
1987 """
1988 return self._set_op(expression.intersect, *q)
1989
1990 def intersect_all(self, *q):
1991 """Produce an INTERSECT ALL of this Query against one or more queries.
1992
1993 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
1994 that method for usage examples.
1995
1996 """
1997 return self._set_op(expression.intersect_all, *q)
1998
1999 def except_(self, *q):
2000 """Produce an EXCEPT of this Query against one or more queries.
2001
2002 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2003 that method for usage examples.
2004
2005 """
2006 return self._set_op(expression.except_, *q)
2007
2008 def except_all(self, *q):
2009 """Produce an EXCEPT ALL of this Query against one or more queries.
2010
2011 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2012 that method for usage examples.
2013
2014 """
2015 return self._set_op(expression.except_all, *q)
2016
2017 def _next_aliased_generation(self):
2018 if "_aliased_generation_counter" not in self.__dict__:
2019 self._aliased_generation_counter = 0
2020 self._aliased_generation_counter += 1
2021 return self._aliased_generation_counter
2022
2023 @_generative
2024 @_assertions(_no_statement_condition, _no_limit_offset)
2025 def join(self, target, *props, **kwargs):
2026 r"""Create a SQL JOIN against this :class:`_query.Query`
2027 object's criterion
2028 and apply generatively, returning the newly resulting
2029 :class:`_query.Query`.
2030
2031 **Simple Relationship Joins**
2032
2033 Consider a mapping between two classes ``User`` and ``Address``,
2034 with a relationship ``User.addresses`` representing a collection
2035 of ``Address`` objects associated with each ``User``. The most
2036 common usage of :meth:`_query.Query.join`
2037 is to create a JOIN along this
2038 relationship, using the ``User.addresses`` attribute as an indicator
2039 for how this should occur::
2040
2041 q = session.query(User).join(User.addresses)
2042
2043 Where above, the call to :meth:`_query.Query.join` along
2044 ``User.addresses`` will result in SQL approximately equivalent to::
2045
2046 SELECT user.id, user.name
2047 FROM user JOIN address ON user.id = address.user_id
2048
2049 In the above example we refer to ``User.addresses`` as passed to
2050 :meth:`_query.Query.join` as the "on clause", that is, it indicates
2051 how the "ON" portion of the JOIN should be constructed.
2052
2053 To construct a chain of joins, multiple :meth:`_query.Query.join`
2054 calls may be used. The relationship-bound attribute implies both
2055 the left and right side of the join at once::
2056
2057 q = session.query(User).\
2058 join(User.orders).\
2059 join(Order.items).\
2060 join(Item.keywords)
2061
2062 .. note:: as seen in the above example, **the order in which each
2063 call to the join() method occurs is important**. Query would not,
2064 for example, know how to join correctly if we were to specify
2065 ``User``, then ``Item``, then ``Order``, in our chain of joins; in
2066 such a case, depending on the arguments passed, it may raise an
2067 error that it doesn't know how to join, or it may produce invalid
2068 SQL in which case the database will raise an error. In correct
2069 practice, the
2070 :meth:`_query.Query.join` method is invoked in such a way that lines
2071 up with how we would want the JOIN clauses in SQL to be
2072 rendered, and each call should represent a clear link from what
2073 precedes it.
2074
2075 **Joins to a Target Entity or Selectable**
2076
2077 A second form of :meth:`_query.Query.join` allows any mapped entity or
2078 core selectable construct as a target. In this usage,
2079 :meth:`_query.Query.join` will attempt to create a JOIN along the
2080 natural foreign key relationship between two entities::
2081
2082 q = session.query(User).join(Address)
2083
2084 In the above calling form, :meth:`_query.Query.join` is called upon to
2085 create the "on clause" automatically for us. This calling form will
2086 ultimately raise an error if either there are no foreign keys between
2087 the two entities, or if there are multiple foreign key linkages between
2088 the target entity and the entity or entities already present on the
2089 left side such that creating a join requires more information. Note
2090 that when indicating a join to a target without any ON clause, ORM
2091 configured relationships are not taken into account.
2092
2093 **Joins to a Target with an ON Clause**
2094
2095 The third calling form allows both the target entity as well
2096 as the ON clause to be passed explicitly. A example that includes
2097 a SQL expression as the ON clause is as follows::
2098
2099 q = session.query(User).join(Address, User.id==Address.user_id)
2100
2101 The above form may also use a relationship-bound attribute as the
2102 ON clause as well::
2103
2104 q = session.query(User).join(Address, User.addresses)
2105
2106 The above syntax can be useful for the case where we wish
2107 to join to an alias of a particular target entity. If we wanted
2108 to join to ``Address`` twice, it could be achieved using two
2109 aliases set up using the :func:`~sqlalchemy.orm.aliased` function::
2110
2111 a1 = aliased(Address)
2112 a2 = aliased(Address)
2113
2114 q = session.query(User).\
2115 join(a1, User.addresses).\
2116 join(a2, User.addresses).\
2117 filter(a1.email_address=='ed@foo.com').\
2118 filter(a2.email_address=='ed@bar.com')
2119
2120 The relationship-bound calling form can also specify a target entity
2121 using the :meth:`_orm.PropComparator.of_type` method; a query
2122 equivalent to the one above would be::
2123
2124 a1 = aliased(Address)
2125 a2 = aliased(Address)
2126
2127 q = session.query(User).\
2128 join(User.addresses.of_type(a1)).\
2129 join(User.addresses.of_type(a2)).\
2130 filter(a1.email_address == 'ed@foo.com').\
2131 filter(a2.email_address == 'ed@bar.com')
2132
2133 **Augmenting Built-in ON Clauses**
2134
2135 As a substitute for providing a full custom ON condition for an
2136 existing relationship, the :meth:`_orm.PropComparator.and_` function
2137 may be applied to a relationship attribute to augment additional
2138 criteria into the ON clause; the additional criteria will be combined
2139 with the default criteria using AND::
2140
2141 q = session.query(User).join(
2142 User.addresses.and_(Address.email_address != 'foo@bar.com')
2143 )
2144
2145 .. versionadded:: 1.4
2146
2147 **Joining to Tables and Subqueries**
2148
2149
2150 The target of a join may also be any table or SELECT statement,
2151 which may be related to a target entity or not. Use the
2152 appropriate ``.subquery()`` method in order to make a subquery
2153 out of a query::
2154
2155 subq = session.query(Address).\
2156 filter(Address.email_address == 'ed@foo.com').\
2157 subquery()
2158
2159
2160 q = session.query(User).join(
2161 subq, User.id == subq.c.user_id
2162 )
2163
2164 Joining to a subquery in terms of a specific relationship and/or
2165 target entity may be achieved by linking the subquery to the
2166 entity using :func:`_orm.aliased`::
2167
2168 subq = session.query(Address).\
2169 filter(Address.email_address == 'ed@foo.com').\
2170 subquery()
2171
2172 address_subq = aliased(Address, subq)
2173
2174 q = session.query(User).join(
2175 User.addresses.of_type(address_subq)
2176 )
2177
2178
2179 **Controlling what to Join From**
2180
2181 In cases where the left side of the current state of
2182 :class:`_query.Query` is not in line with what we want to join from,
2183 the :meth:`_query.Query.select_from` method may be used::
2184
2185 q = session.query(Address).select_from(User).\
2186 join(User.addresses).\
2187 filter(User.name == 'ed')
2188
2189 Which will produce SQL similar to::
2190
2191 SELECT address.* FROM user
2192 JOIN address ON user.id=address.user_id
2193 WHERE user.name = :name_1
2194
2195 **Legacy Features of Query.join()**
2196
2197 .. deprecated:: 1.4 The following features are deprecated and will
2198 be removed in SQLAlchemy 2.0.
2199
2200 The :meth:`_query.Query.join` method currently supports several
2201 usage patterns and arguments that are considered to be legacy
2202 as of SQLAlchemy 1.3. A deprecation path will follow
2203 in the 1.4 series for the following features:
2204
2205
2206 * Joining on relationship names rather than attributes::
2207
2208 session.query(User).join("addresses")
2209
2210 **Why it's legacy**: the string name does not provide enough context
2211 for :meth:`_query.Query.join` to always know what is desired,
2212 notably in that there is no indication of what the left side
2213 of the join should be. This gives rise to flags like
2214 ``from_joinpoint`` as well as the ability to place several
2215 join clauses in a single :meth:`_query.Query.join` call
2216 which don't solve the problem fully while also
2217 adding new calling styles that are unnecessary and expensive to
2218 accommodate internally.
2219
2220 **Modern calling pattern**: Use the actual relationship,
2221 e.g. ``User.addresses`` in the above case::
2222
2223 session.query(User).join(User.addresses)
2224
2225 * Automatic aliasing with the ``aliased=True`` flag::
2226
2227 session.query(Node).join(Node.children, aliased=True).\
2228 filter(Node.name == 'some name')
2229
2230 **Why it's legacy**: the automatic aliasing feature of
2231 :class:`_query.Query` is intensely complicated, both in its internal
2232 implementation as well as in its observed behavior, and is almost
2233 never used. It is difficult to know upon inspection where and when
2234 its aliasing of a target entity, ``Node`` in the above case, will be
2235 applied and when it won't, and additionally the feature has to use
2236 very elaborate heuristics to achieve this implicit behavior.
2237
2238 **Modern calling pattern**: Use the :func:`_orm.aliased` construct
2239 explicitly::
2240
2241 from sqlalchemy.orm import aliased
2242
2243 n1 = aliased(Node)
2244
2245 session.query(Node).join(Node.children.of_type(n1)).\
2246 filter(n1.name == 'some name')
2247
2248 * Multiple joins in one call::
2249
2250 session.query(User).join("orders", "items")
2251
2252 session.query(User).join(User.orders, Order.items)
2253
2254 session.query(User).join(
2255 (Order, User.orders),
2256 (Item, Item.order_id == Order.id)
2257 )
2258
2259 session.query(User).join(Order, Item)
2260
2261 # ... and several more forms actually
2262
2263 **Why it's legacy**: being able to chain multiple ON clauses in one
2264 call to :meth:`_query.Query.join` is yet another attempt to solve
2265 the problem of being able to specify what entity to join from,
2266 and is the source of a large variety of potential calling patterns
2267 that are internally expensive and complicated to parse and
2268 accommodate.
2269
2270 **Modern calling pattern**: Use relationship-bound attributes
2271 or SQL-oriented ON clauses within separate calls, so that
2272 each call to :meth:`_query.Query.join` knows what the left
2273 side should be::
2274
2275 session.query(User).join(User.orders).join(
2276 Item, Item.order_id == Order.id)
2277
2278
2279 :param \*props: Incoming arguments for :meth:`_query.Query.join`,
2280 the props collection in modern use should be considered to be a one
2281 or two argument form, either as a single "target" entity or ORM
2282 attribute-bound relationship, or as a target entity plus an "on
2283 clause" which may be a SQL expression or ORM attribute-bound
2284 relationship.
2285
2286 :param isouter=False: If True, the join used will be a left outer join,
2287 just as if the :meth:`_query.Query.outerjoin` method were called.
2288
2289 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2290
2291 .. versionadded:: 1.1
2292
2293 :param from_joinpoint=False: When using ``aliased=True``, a setting
2294 of True here will cause the join to be from the most recent
2295 joined target, rather than starting back from the original
2296 FROM clauses of the query.
2297
2298 .. note:: This flag is considered legacy.
2299
2300 :param aliased=False: If True, indicate that the JOIN target should be
2301 anonymously aliased. Subsequent calls to :meth:`_query.Query.filter`
2302 and similar will adapt the incoming criterion to the target
2303 alias, until :meth:`_query.Query.reset_joinpoint` is called.
2304
2305 .. note:: This flag is considered legacy.
2306
2307 .. seealso::
2308
2309 :ref:`ormtutorial_joins` in the ORM tutorial.
2310
2311 :ref:`inheritance_toplevel` for details on how
2312 :meth:`_query.Query.join` is used for inheritance relationships.
2313
2314 :func:`_orm.join` - a standalone ORM-level join function,
2315 used internally by :meth:`_query.Query.join`, which in previous
2316 SQLAlchemy versions was the primary ORM-level joining interface.
2317
2318 """
2319
2320 aliased, from_joinpoint, isouter, full = (
2321 kwargs.pop("aliased", False),
2322 kwargs.pop("from_joinpoint", False),
2323 kwargs.pop("isouter", False),
2324 kwargs.pop("full", False),
2325 )
2326
2327 if aliased or from_joinpoint:
2328 util.warn_deprecated_20(
2329 "The ``aliased`` and ``from_joinpoint`` keyword arguments "
2330 "to Query.join() are deprecated and will be removed "
2331 "in SQLAlchemy 2.0."
2332 )
2333
2334 if kwargs:
2335 raise TypeError(
2336 "unknown arguments: %s" % ", ".join(sorted(kwargs))
2337 )
2338
2339 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
2340 if not from_joinpoint:
2341 self._last_joined_entity = None
2342 self._aliased_generation = None
2343 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^^
2344
2345 if props:
2346 onclause, legacy = props[0], props[1:]
2347 else:
2348 onclause = legacy = None
2349
2350 if not legacy and onclause is None and not isinstance(target, tuple):
2351 # non legacy argument form
2352 _props = [(target,)]
2353 elif (
2354 not legacy
2355 and isinstance(
2356 target,
2357 (
2358 expression.Selectable,
2359 type,
2360 AliasedClass,
2361 types.FunctionType,
2362 ),
2363 )
2364 and isinstance(
2365 onclause,
2366 (
2367 elements.ColumnElement,
2368 str,
2369 interfaces.PropComparator,
2370 types.FunctionType,
2371 ),
2372 )
2373 ):
2374 # non legacy argument form
2375 _props = [(target, onclause)]
2376 else:
2377 # legacy forms. more time consuming :)
2378 _props = []
2379 _single = []
2380 for prop in (target,) + props:
2381 if isinstance(prop, tuple):
2382 util.warn_deprecated_20(
2383 "Query.join() will no longer accept tuples as "
2384 "arguments in SQLAlchemy 2.0."
2385 )
2386 if _single:
2387 _props.extend((_s,) for _s in _single)
2388 _single = []
2389
2390 # this checks for an extremely ancient calling form of
2391 # reversed tuples.
2392 if isinstance(prop[0], (str, interfaces.PropComparator)):
2393 prop = (prop[1], prop[0])
2394
2395 _props.append(prop)
2396 else:
2397 _single.append(prop)
2398 if _single:
2399 _props.extend((_s,) for _s in _single)
2400
2401 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
2402 if aliased:
2403 self._aliased_generation = self._next_aliased_generation()
2404
2405 if self._aliased_generation:
2406 _props = [
2407 (
2408 prop[0],
2409 sql_util._deep_annotate(
2410 prop[1],
2411 {"aliased_generation": self._aliased_generation},
2412 )
2413 if isinstance(prop[1], expression.ClauseElement)
2414 else prop[1],
2415 )
2416 if len(prop) == 2
2417 else prop
2418 for prop in _props
2419 ]
2420
2421 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^^
2422
2423 joins_to_add = tuple(
2424 (
2425 coercions.expect(
2426 roles.JoinTargetRole,
2427 prop[0],
2428 legacy=True,
2429 apply_propagate_attrs=self,
2430 ),
2431 (
2432 coercions.expect(roles.OnClauseRole, prop[1], legacy=True)
2433 # if not isinstance(prop[1], str)
2434 # else prop[1]
2435 )
2436 if len(prop) == 2
2437 else None,
2438 None,
2439 {
2440 "isouter": isouter,
2441 "aliased": aliased,
2442 "from_joinpoint": True if i > 0 else from_joinpoint,
2443 "full": full,
2444 "aliased_generation": self._aliased_generation,
2445 },
2446 )
2447 for i, prop in enumerate(_props)
2448 )
2449
2450 if len(joins_to_add) > 1:
2451 util.warn_deprecated_20(
2452 "Passing a chain of multiple join conditions to Query.join() "
2453 "is deprecated and will be removed in SQLAlchemy 2.0. "
2454 "Please use individual join() calls per relationship."
2455 )
2456
2457 self._legacy_setup_joins += joins_to_add
2458
2459 self.__dict__.pop("_last_joined_entity", None)
2460
2461 def outerjoin(self, target, *props, **kwargs):
2462 """Create a left outer join against this ``Query`` object's criterion
2463 and apply generatively, returning the newly resulting ``Query``.
2464
2465 Usage is the same as the ``join()`` method.
2466
2467 """
2468 kwargs["isouter"] = True
2469 return self.join(target, *props, **kwargs)
2470
2471 @_generative
2472 @_assertions(_no_statement_condition)
2473 def reset_joinpoint(self):
2474 """Return a new :class:`.Query`, where the "join point" has
2475 been reset back to the base FROM entities of the query.
2476
2477 This method is usually used in conjunction with the
2478 ``aliased=True`` feature of the :meth:`~.Query.join`
2479 method. See the example in :meth:`~.Query.join` for how
2480 this is used.
2481
2482 """
2483 self._last_joined_entity = None
2484 self._aliased_generation = None
2485
2486 @_generative
2487 @_assertions(_no_clauseelement_condition)
2488 def select_from(self, *from_obj):
2489 r"""Set the FROM clause of this :class:`.Query` explicitly.
2490
2491 :meth:`.Query.select_from` is often used in conjunction with
2492 :meth:`.Query.join` in order to control which entity is selected
2493 from on the "left" side of the join.
2494
2495 The entity or selectable object here effectively replaces the
2496 "left edge" of any calls to :meth:`~.Query.join`, when no
2497 joinpoint is otherwise established - usually, the default "join
2498 point" is the leftmost entity in the :class:`~.Query` object's
2499 list of entities to be selected.
2500
2501 A typical example::
2502
2503 q = session.query(Address).select_from(User).\
2504 join(User.addresses).\
2505 filter(User.name == 'ed')
2506
2507 Which produces SQL equivalent to::
2508
2509 SELECT address.* FROM user
2510 JOIN address ON user.id=address.user_id
2511 WHERE user.name = :name_1
2512
2513 :param \*from_obj: collection of one or more entities to apply
2514 to the FROM clause. Entities can be mapped classes,
2515 :class:`.AliasedClass` objects, :class:`.Mapper` objects
2516 as well as core :class:`.FromClause` elements like subqueries.
2517
2518 .. versionchanged:: 0.9
2519 This method no longer applies the given FROM object
2520 to be the selectable from which matching entities
2521 select from; the :meth:`.select_entity_from` method
2522 now accomplishes this. See that method for a description
2523 of this behavior.
2524
2525 .. seealso::
2526
2527 :meth:`~.Query.join`
2528
2529 :meth:`.Query.select_entity_from`
2530
2531 """
2532
2533 self._set_select_from(from_obj, False)
2534
2535 @util.deprecated_20(
2536 ":meth:`_orm.Query.select_entity_from`",
2537 alternative="Use the :func:`_orm.aliased` construct instead",
2538 )
2539 @_generative
2540 @_assertions(_no_clauseelement_condition)
2541 def select_entity_from(self, from_obj):
2542 r"""Set the FROM clause of this :class:`_query.Query` to a
2543 core selectable, applying it as a replacement FROM clause
2544 for corresponding mapped entities.
2545
2546 The :meth:`_query.Query.select_entity_from`
2547 method supplies an alternative
2548 approach to the use case of applying an :func:`.aliased` construct
2549 explicitly throughout a query. Instead of referring to the
2550 :func:`.aliased` construct explicitly,
2551 :meth:`_query.Query.select_entity_from` automatically *adapts* all
2552 occurrences of the entity to the target selectable.
2553
2554 Given a case for :func:`.aliased` such as selecting ``User``
2555 objects from a SELECT statement::
2556
2557 select_stmt = select(User).where(User.id == 7)
2558 user_alias = aliased(User, select_stmt)
2559
2560 q = session.query(user_alias).\
2561 filter(user_alias.name == 'ed')
2562
2563 Above, we apply the ``user_alias`` object explicitly throughout the
2564 query. When it's not feasible for ``user_alias`` to be referenced
2565 explicitly in many places, :meth:`_query.Query.select_entity_from`
2566 may be
2567 used at the start of the query to adapt the existing ``User`` entity::
2568
2569 q = session.query(User).\
2570 select_entity_from(select_stmt.subquery()).\
2571 filter(User.name == 'ed')
2572
2573 Above, the generated SQL will show that the ``User`` entity is
2574 adapted to our statement, even in the case of the WHERE clause:
2575
2576 .. sourcecode:: sql
2577
2578 SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
2579 FROM (SELECT "user".id AS id, "user".name AS name
2580 FROM "user"
2581 WHERE "user".id = :id_1) AS anon_1
2582 WHERE anon_1.name = :name_1
2583
2584 The :meth:`_query.Query.select_entity_from` method is similar to the
2585 :meth:`_query.Query.select_from` method,
2586 in that it sets the FROM clause
2587 of the query. The difference is that it additionally applies
2588 adaptation to the other parts of the query that refer to the
2589 primary entity. If above we had used :meth:`_query.Query.select_from`
2590 instead, the SQL generated would have been:
2591
2592 .. sourcecode:: sql
2593
2594 -- uses plain select_from(), not select_entity_from()
2595 SELECT "user".id AS user_id, "user".name AS user_name
2596 FROM "user", (SELECT "user".id AS id, "user".name AS name
2597 FROM "user"
2598 WHERE "user".id = :id_1) AS anon_1
2599 WHERE "user".name = :name_1
2600
2601 To supply textual SQL to the :meth:`_query.Query.select_entity_from`
2602 method,
2603 we can make use of the :func:`_expression.text` construct. However,
2604 the
2605 :func:`_expression.text`
2606 construct needs to be aligned with the columns of our
2607 entity, which is achieved by making use of the
2608 :meth:`_expression.TextClause.columns` method::
2609
2610 text_stmt = text("select id, name from user").columns(
2611 User.id, User.name).subquery()
2612 q = session.query(User).select_entity_from(text_stmt)
2613
2614 :meth:`_query.Query.select_entity_from` itself accepts an
2615 :func:`.aliased`
2616 object, so that the special options of :func:`.aliased` such as
2617 :paramref:`.aliased.adapt_on_names` may be used within the
2618 scope of the :meth:`_query.Query.select_entity_from`
2619 method's adaptation
2620 services. Suppose
2621 a view ``user_view`` also returns rows from ``user``. If
2622 we reflect this view into a :class:`_schema.Table`, this view has no
2623 relationship to the :class:`_schema.Table` to which we are mapped,
2624 however
2625 we can use name matching to select from it::
2626
2627 user_view = Table('user_view', metadata,
2628 autoload_with=engine)
2629 user_view_alias = aliased(
2630 User, user_view, adapt_on_names=True)
2631 q = session.query(User).\
2632 select_entity_from(user_view_alias).\
2633 order_by(User.name)
2634
2635 .. versionchanged:: 1.1.7 The :meth:`_query.Query.select_entity_from`
2636 method now accepts an :func:`.aliased` object as an alternative
2637 to a :class:`_expression.FromClause` object.
2638
2639 :param from_obj: a :class:`_expression.FromClause`
2640 object that will replace
2641 the FROM clause of this :class:`_query.Query`.
2642 It also may be an instance
2643 of :func:`.aliased`.
2644
2645
2646
2647 .. seealso::
2648
2649 :meth:`_query.Query.select_from`
2650
2651 """
2652
2653 self._set_select_from([from_obj], True)
2654 self._compile_options += {"_enable_single_crit": False}
2655
2656 def __getitem__(self, item):
2657 return orm_util._getitem(
2658 self,
2659 item,
2660 allow_negative=not self.session or not self.session.future,
2661 )
2662
2663 @_generative
2664 @_assertions(_no_statement_condition)
2665 def slice(self, start, stop):
2666 """Computes the "slice" of the :class:`_query.Query` represented by
2667 the given indices and returns the resulting :class:`_query.Query`.
2668
2669 The start and stop indices behave like the argument to Python's
2670 built-in :func:`range` function. This method provides an
2671 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the
2672 query.
2673
2674 For example, ::
2675
2676 session.query(User).order_by(User.id).slice(1, 3)
2677
2678 renders as
2679
2680 .. sourcecode:: sql
2681
2682 SELECT users.id AS users_id,
2683 users.name AS users_name
2684 FROM users ORDER BY users.id
2685 LIMIT ? OFFSET ?
2686 (2, 1)
2687
2688 .. seealso::
2689
2690 :meth:`_query.Query.limit`
2691
2692 :meth:`_query.Query.offset`
2693
2694 """
2695
2696 self._limit_clause, self._offset_clause = sql_util._make_slice(
2697 self._limit_clause, self._offset_clause, start, stop
2698 )
2699
2700 @_generative
2701 @_assertions(_no_statement_condition)
2702 def limit(self, limit):
2703 """Apply a ``LIMIT`` to the query and return the newly resulting
2704 ``Query``.
2705
2706 """
2707 self._limit_clause = sql_util._offset_or_limit_clause(limit)
2708
2709 @_generative
2710 @_assertions(_no_statement_condition)
2711 def offset(self, offset):
2712 """Apply an ``OFFSET`` to the query and return the newly resulting
2713 ``Query``.
2714
2715 """
2716 self._offset_clause = sql_util._offset_or_limit_clause(offset)
2717
2718 @_generative
2719 @_assertions(_no_statement_condition)
2720 def distinct(self, *expr):
2721 r"""Apply a ``DISTINCT`` to the query and return the newly resulting
2722 ``Query``.
2723
2724
2725 .. note::
2726
2727 The ORM-level :meth:`.distinct` call includes logic that will
2728 automatically add columns from the ORDER BY of the query to the
2729 columns clause of the SELECT statement, to satisfy the common need
2730 of the database backend that ORDER BY columns be part of the SELECT
2731 list when DISTINCT is used. These columns *are not* added to the
2732 list of columns actually fetched by the :class:`_query.Query`,
2733 however,
2734 so would not affect results. The columns are passed through when
2735 using the :attr:`_query.Query.statement` accessor, however.
2736
2737 .. deprecated:: 2.0 This logic is deprecated and will be removed
2738 in SQLAlchemy 2.0. See :ref:`migration_20_query_distinct`
2739 for a description of this use case in 2.0.
2740
2741 :param \*expr: optional column expressions. When present,
2742 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2743 construct.
2744
2745 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
2746 and will raise :class:`_exc.CompileError` in a future version.
2747
2748 """
2749 if expr:
2750 self._distinct = True
2751 self._distinct_on = self._distinct_on + tuple(
2752 coercions.expect(roles.ByOfRole, e) for e in expr
2753 )
2754 else:
2755 self._distinct = True
2756
2757 def all(self):
2758 """Return the results represented by this :class:`_query.Query`
2759 as a list.
2760
2761 This results in an execution of the underlying SQL statement.
2762
2763 .. warning:: The :class:`_query.Query` object,
2764 when asked to return either
2765 a sequence or iterator that consists of full ORM-mapped entities,
2766 will **deduplicate entries based on primary key**. See the FAQ for
2767 more details.
2768
2769 .. seealso::
2770
2771 :ref:`faq_query_deduplicating`
2772 """
2773 return self._iter().all()
2774
2775 @_generative
2776 @_assertions(_no_clauseelement_condition)
2777 def from_statement(self, statement):
2778 """Execute the given SELECT statement and return results.
2779
2780 This method bypasses all internal statement compilation, and the
2781 statement is executed without modification.
2782
2783 The statement is typically either a :func:`_expression.text`
2784 or :func:`_expression.select` construct, and should return the set
2785 of columns
2786 appropriate to the entity class represented by this
2787 :class:`_query.Query`.
2788
2789 .. seealso::
2790
2791 :ref:`orm_tutorial_literal_sql` - usage examples in the
2792 ORM tutorial
2793
2794 """
2795 statement = coercions.expect(
2796 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2797 )
2798 self._statement = statement
2799
2800 def first(self):
2801 """Return the first result of this ``Query`` or
2802 None if the result doesn't contain any row.
2803
2804 first() applies a limit of one within the generated SQL, so that
2805 only one primary entity row is generated on the server side
2806 (note this may consist of multiple result rows if join-loaded
2807 collections are present).
2808
2809 Calling :meth:`_query.Query.first`
2810 results in an execution of the underlying
2811 query.
2812
2813 .. seealso::
2814
2815 :meth:`_query.Query.one`
2816
2817 :meth:`_query.Query.one_or_none`
2818
2819 """
2820 # replicates limit(1) behavior
2821 if self._statement is not None:
2822 return self._iter().first()
2823 else:
2824 return self.limit(1)._iter().first()
2825
2826 def one_or_none(self):
2827 """Return at most one result or raise an exception.
2828
2829 Returns ``None`` if the query selects
2830 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2831 if multiple object identities are returned, or if multiple
2832 rows are returned for a query that returns only scalar values
2833 as opposed to full identity-mapped entities.
2834
2835 Calling :meth:`_query.Query.one_or_none`
2836 results in an execution of the
2837 underlying query.
2838
2839 .. versionadded:: 1.0.9
2840
2841 Added :meth:`_query.Query.one_or_none`
2842
2843 .. seealso::
2844
2845 :meth:`_query.Query.first`
2846
2847 :meth:`_query.Query.one`
2848
2849 """
2850 return self._iter().one_or_none()
2851
2852 def one(self):
2853 """Return exactly one result or raise an exception.
2854
2855 Raises ``sqlalchemy.orm.exc.NoResultFound`` if the query selects
2856 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound``
2857 if multiple object identities are returned, or if multiple
2858 rows are returned for a query that returns only scalar values
2859 as opposed to full identity-mapped entities.
2860
2861 Calling :meth:`.one` results in an execution of the underlying query.
2862
2863 .. seealso::
2864
2865 :meth:`_query.Query.first`
2866
2867 :meth:`_query.Query.one_or_none`
2868
2869 """
2870 return self._iter().one()
2871
2872 def scalar(self):
2873 """Return the first element of the first result or None
2874 if no rows present. If multiple rows are returned,
2875 raises MultipleResultsFound.
2876
2877 >>> session.query(Item).scalar()
2878 <Item>
2879 >>> session.query(Item.id).scalar()
2880 1
2881 >>> session.query(Item.id).filter(Item.id < 0).scalar()
2882 None
2883 >>> session.query(Item.id, Item.name).scalar()
2884 1
2885 >>> session.query(func.count(Parent.id)).scalar()
2886 20
2887
2888 This results in an execution of the underlying query.
2889
2890 """
2891 # TODO: not sure why we can't use result.scalar() here
2892 try:
2893 ret = self.one()
2894 if not isinstance(ret, collections_abc.Sequence):
2895 return ret
2896 return ret[0]
2897 except orm_exc.NoResultFound:
2898 return None
2899
2900 def __iter__(self):
2901 result = self._iter()
2902 try:
2903 for row in result:
2904 yield row
2905 except GeneratorExit:
2906 # issue #8710 - direct iteration is not re-usable after
2907 # an iterable block is broken, so close the result
2908 result._soft_close()
2909 raise
2910
2911 def _iter(self):
2912 # new style execution.
2913 params = self._params
2914
2915 statement = self._statement_20()
2916 result = self.session.execute(
2917 statement,
2918 params,
2919 execution_options={"_sa_orm_load_options": self.load_options},
2920 )
2921
2922 # legacy: automatically set scalars, unique
2923 if result._attributes.get("is_single_entity", False):
2924 result = result.scalars()
2925
2926 if (
2927 result._attributes.get("filtered", False)
2928 and not self.load_options._yield_per
2929 ):
2930 result = result.unique()
2931
2932 return result
2933
2934 def __str__(self):
2935 statement = self._statement_20()
2936
2937 try:
2938 bind = (
2939 self._get_bind_args(statement, self.session.get_bind)
2940 if self.session
2941 else None
2942 )
2943 except sa_exc.UnboundExecutionError:
2944 bind = None
2945
2946 return str(statement.compile(bind))
2947
2948 def _get_bind_args(self, statement, fn, **kw):
2949 return fn(clause=statement, **kw)
2950
2951 @property
2952 def column_descriptions(self):
2953 """Return metadata about the columns which would be
2954 returned by this :class:`_query.Query`.
2955
2956 Format is a list of dictionaries::
2957
2958 user_alias = aliased(User, name='user2')
2959 q = sess.query(User, User.id, user_alias)
2960
2961 # this expression:
2962 q.column_descriptions
2963
2964 # would return:
2965 [
2966 {
2967 'name':'User',
2968 'type':User,
2969 'aliased':False,
2970 'expr':User,
2971 'entity': User
2972 },
2973 {
2974 'name':'id',
2975 'type':Integer(),
2976 'aliased':False,
2977 'expr':User.id,
2978 'entity': User
2979 },
2980 {
2981 'name':'user2',
2982 'type':User,
2983 'aliased':True,
2984 'expr':user_alias,
2985 'entity': user_alias
2986 }
2987 ]
2988
2989 .. seealso::
2990
2991 This API is available using :term:`2.0 style` queries as well,
2992 documented at:
2993
2994 * :ref:`queryguide_inspection`
2995
2996 * :attr:`.Select.column_descriptions`
2997
2998 """
2999
3000 return _column_descriptions(self, legacy=True)
3001
3002 def instances(self, result_proxy, context=None):
3003 """Return an ORM result given a :class:`_engine.CursorResult` and
3004 :class:`.QueryContext`.
3005
3006 """
3007 if context is None:
3008 util.warn_deprecated(
3009 "Using the Query.instances() method without a context "
3010 "is deprecated and will be disallowed in a future release. "
3011 "Please make use of :meth:`_query.Query.from_statement` "
3012 "for linking ORM results to arbitrary select constructs.",
3013 version="1.4",
3014 )
3015 compile_state = self._compile_state(for_statement=False)
3016
3017 context = QueryContext(
3018 compile_state,
3019 compile_state.statement,
3020 self._params,
3021 self.session,
3022 self.load_options,
3023 )
3024
3025 result = loading.instances(result_proxy, context)
3026
3027 # legacy: automatically set scalars, unique
3028 if result._attributes.get("is_single_entity", False):
3029 result = result.scalars()
3030
3031 if result._attributes.get("filtered", False):
3032 result = result.unique()
3033
3034 return result
3035
3036 @util.deprecated_20(
3037 ":meth:`_orm.Query.merge_result`",
3038 alternative="The method is superseded by the "
3039 ":func:`_orm.merge_frozen_result` function.",
3040 becomes_legacy=True,
3041 enable_warnings=False, # warnings occur via loading.merge_result
3042 )
3043 def merge_result(self, iterator, load=True):
3044 """Merge a result into this :class:`_query.Query` object's Session.
3045
3046 Given an iterator returned by a :class:`_query.Query`
3047 of the same structure
3048 as this one, return an identical iterator of results, with all mapped
3049 instances merged into the session using :meth:`.Session.merge`. This
3050 is an optimized method which will merge all mapped instances,
3051 preserving the structure of the result rows and unmapped columns with
3052 less method overhead than that of calling :meth:`.Session.merge`
3053 explicitly for each value.
3054
3055 The structure of the results is determined based on the column list of
3056 this :class:`_query.Query` - if these do not correspond,
3057 unchecked errors
3058 will occur.
3059
3060 The 'load' argument is the same as that of :meth:`.Session.merge`.
3061
3062 For an example of how :meth:`_query.Query.merge_result` is used, see
3063 the source code for the example :ref:`examples_caching`, where
3064 :meth:`_query.Query.merge_result` is used to efficiently restore state
3065 from a cache back into a target :class:`.Session`.
3066
3067 """
3068
3069 return loading.merge_result(self, iterator, load)
3070
3071 def exists(self):
3072 """A convenience method that turns a query into an EXISTS subquery
3073 of the form EXISTS (SELECT 1 FROM ... WHERE ...).
3074
3075 e.g.::
3076
3077 q = session.query(User).filter(User.name == 'fred')
3078 session.query(q.exists())
3079
3080 Producing SQL similar to::
3081
3082 SELECT EXISTS (
3083 SELECT 1 FROM users WHERE users.name = :name_1
3084 ) AS anon_1
3085
3086 The EXISTS construct is usually used in the WHERE clause::
3087
3088 session.query(User.id).filter(q.exists()).scalar()
3089
3090 Note that some databases such as SQL Server don't allow an
3091 EXISTS expression to be present in the columns clause of a
3092 SELECT. To select a simple boolean value based on the exists
3093 as a WHERE, use :func:`.literal`::
3094
3095 from sqlalchemy import literal
3096
3097 session.query(literal(True)).filter(q.exists()).scalar()
3098
3099 """
3100
3101 # .add_columns() for the case that we are a query().select_from(X),
3102 # so that ".statement" can be produced (#2995) but also without
3103 # omitting the FROM clause from a query(X) (#2818);
3104 # .with_only_columns() after we have a core select() so that
3105 # we get just "SELECT 1" without any entities.
3106
3107 inner = (
3108 self.enable_eagerloads(False)
3109 .add_columns(sql.literal_column("1"))
3110 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
3111 .statement.with_only_columns(1)
3112 )
3113
3114 ezero = self._entity_from_pre_ent_zero()
3115 if ezero is not None:
3116 inner = inner.select_from(ezero)
3117
3118 return sql.exists(inner)
3119
3120 def count(self):
3121 r"""Return a count of rows this the SQL formed by this :class:`Query`
3122 would return.
3123
3124 This generates the SQL for this Query as follows::
3125
3126 SELECT count(1) AS count_1 FROM (
3127 SELECT <rest of query follows...>
3128 ) AS anon_1
3129
3130 The above SQL returns a single row, which is the aggregate value
3131 of the count function; the :meth:`_query.Query.count`
3132 method then returns
3133 that single integer value.
3134
3135 .. warning::
3136
3137 It is important to note that the value returned by
3138 count() is **not the same as the number of ORM objects that this
3139 Query would return from a method such as the .all() method**.
3140 The :class:`_query.Query` object,
3141 when asked to return full entities,
3142 will **deduplicate entries based on primary key**, meaning if the
3143 same primary key value would appear in the results more than once,
3144 only one object of that primary key would be present. This does
3145 not apply to a query that is against individual columns.
3146
3147 .. seealso::
3148
3149 :ref:`faq_query_deduplicating`
3150
3151 :ref:`orm_tutorial_query_returning`
3152
3153 For fine grained control over specific columns to count, to skip the
3154 usage of a subquery or otherwise control of the FROM clause, or to use
3155 other aggregate functions, use :attr:`~sqlalchemy.sql.expression.func`
3156 expressions in conjunction with :meth:`~.Session.query`, i.e.::
3157
3158 from sqlalchemy import func
3159
3160 # count User records, without
3161 # using a subquery.
3162 session.query(func.count(User.id))
3163
3164 # return count of user "id" grouped
3165 # by "name"
3166 session.query(func.count(User.id)).\
3167 group_by(User.name)
3168
3169 from sqlalchemy import distinct
3170
3171 # count distinct "name" values
3172 session.query(func.count(distinct(User.name)))
3173
3174 """
3175 col = sql.func.count(sql.literal_column("*"))
3176 return self._from_self(col).enable_eagerloads(False).scalar()
3177
3178 def delete(self, synchronize_session="evaluate"):
3179 r"""Perform a DELETE with an arbitrary WHERE clause.
3180
3181 Deletes rows matched by this query from the database.
3182
3183 E.g.::
3184
3185 sess.query(User).filter(User.age == 25).\
3186 delete(synchronize_session=False)
3187
3188 sess.query(User).filter(User.age == 25).\
3189 delete(synchronize_session='evaluate')
3190
3191 .. warning::
3192
3193 See the section :ref:`orm_expression_update_delete` for important
3194 caveats and warnings, including limitations when using bulk UPDATE
3195 and DELETE with mapper inheritance configurations.
3196
3197 :param synchronize_session: chooses the strategy to update the
3198 attributes on objects in the session. See the section
3199 :ref:`orm_expression_update_delete` for a discussion of these
3200 strategies.
3201
3202 :return: the count of rows matched as returned by the database's
3203 "row count" feature.
3204
3205 .. seealso::
3206
3207 :ref:`orm_expression_update_delete`
3208
3209 """
3210
3211 bulk_del = BulkDelete(self)
3212 if self.dispatch.before_compile_delete:
3213 for fn in self.dispatch.before_compile_delete:
3214 new_query = fn(bulk_del.query, bulk_del)
3215 if new_query is not None:
3216 bulk_del.query = new_query
3217
3218 self = bulk_del.query
3219
3220 delete_ = sql.delete(*self._raw_columns)
3221 delete_._where_criteria = self._where_criteria
3222 result = self.session.execute(
3223 delete_,
3224 self._params,
3225 execution_options={"synchronize_session": synchronize_session},
3226 )
3227 bulk_del.result = result
3228 self.session.dispatch.after_bulk_delete(bulk_del)
3229 result.close()
3230
3231 return result.rowcount
3232
3233 def update(self, values, synchronize_session="evaluate", update_args=None):
3234 r"""Perform an UPDATE with an arbitrary WHERE clause.
3235
3236 Updates rows matched by this query in the database.
3237
3238 E.g.::
3239
3240 sess.query(User).filter(User.age == 25).\
3241 update({User.age: User.age - 10}, synchronize_session=False)
3242
3243 sess.query(User).filter(User.age == 25).\
3244 update({"age": User.age - 10}, synchronize_session='evaluate')
3245
3246 .. warning::
3247
3248 See the section :ref:`orm_expression_update_delete` for important
3249 caveats and warnings, including limitations when using arbitrary
3250 UPDATE and DELETE with mapper inheritance configurations.
3251
3252 :param values: a dictionary with attributes names, or alternatively
3253 mapped attributes or SQL expressions, as keys, and literal
3254 values or sql expressions as values. If :ref:`parameter-ordered
3255 mode <tutorial_parameter_ordered_updates>` is desired, the values can
3256 be passed as a list of 2-tuples; this requires that the
3257 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`
3258 flag is passed to the :paramref:`.Query.update.update_args` dictionary
3259 as well.
3260
3261 :param synchronize_session: chooses the strategy to update the
3262 attributes on objects in the session. See the section
3263 :ref:`orm_expression_update_delete` for a discussion of these
3264 strategies.
3265
3266 :param update_args: Optional dictionary, if present will be passed
3267 to the underlying :func:`_expression.update`
3268 construct as the ``**kw`` for
3269 the object. May be used to pass dialect-specific arguments such
3270 as ``mysql_limit``, as well as other special arguments such as
3271 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`.
3272
3273 :return: the count of rows matched as returned by the database's
3274 "row count" feature.
3275
3276
3277 .. seealso::
3278
3279 :ref:`orm_expression_update_delete`
3280
3281
3282 """
3283
3284 update_args = update_args or {}
3285
3286 bulk_ud = BulkUpdate(self, values, update_args)
3287
3288 if self.dispatch.before_compile_update:
3289 for fn in self.dispatch.before_compile_update:
3290 new_query = fn(bulk_ud.query, bulk_ud)
3291 if new_query is not None:
3292 bulk_ud.query = new_query
3293 self = bulk_ud.query
3294
3295 upd = sql.update(*self._raw_columns)
3296
3297 ppo = update_args.pop("preserve_parameter_order", False)
3298 if ppo:
3299 upd = upd.ordered_values(*values)
3300 else:
3301 upd = upd.values(values)
3302 if update_args:
3303 upd = upd.with_dialect_options(**update_args)
3304
3305 upd._where_criteria = self._where_criteria
3306 result = self.session.execute(
3307 upd,
3308 self._params,
3309 execution_options={"synchronize_session": synchronize_session},
3310 )
3311 bulk_ud.result = result
3312 self.session.dispatch.after_bulk_update(bulk_ud)
3313 result.close()
3314 return result.rowcount
3315
3316 def _compile_state(self, for_statement=False, **kw):
3317 """Create an out-of-compiler ORMCompileState object.
3318
3319 The ORMCompileState object is normally created directly as a result
3320 of the SQLCompiler.process() method being handed a Select()
3321 or FromStatement() object that uses the "orm" plugin. This method
3322 provides a means of creating this ORMCompileState object directly
3323 without using the compiler.
3324
3325 This method is used only for deprecated cases, which include
3326 the .from_self() method for a Query that has multiple levels
3327 of .from_self() in use, as well as the instances() method. It is
3328 also used within the test suite to generate ORMCompileState objects
3329 for test purposes.
3330
3331 """
3332
3333 stmt = self._statement_20(for_statement=for_statement, **kw)
3334 assert for_statement == stmt._compile_options._for_statement
3335
3336 # this chooses between ORMFromStatementCompileState and
3337 # ORMSelectCompileState. We could also base this on
3338 # query._statement is not None as we have the ORM Query here
3339 # however this is the more general path.
3340 compile_state_cls = ORMCompileState._get_plugin_class_for_plugin(
3341 stmt, "orm"
3342 )
3343
3344 return compile_state_cls.create_for_statement(stmt, None)
3345
3346 def _compile_context(self, for_statement=False):
3347 compile_state = self._compile_state(for_statement=for_statement)
3348 context = QueryContext(
3349 compile_state,
3350 compile_state.statement,
3351 self._params,
3352 self.session,
3353 self.load_options,
3354 )
3355
3356 return context
3357
3358
3359class FromStatement(GroupedElement, SelectBase, Executable):
3360 """Core construct that represents a load of ORM objects from a finished
3361 select or text construct.
3362
3363 """
3364
3365 __visit_name__ = "orm_from_statement"
3366
3367 _compile_options = ORMFromStatementCompileState.default_compile_options
3368
3369 _compile_state_factory = ORMFromStatementCompileState.create_for_statement
3370
3371 _for_update_arg = None
3372
3373 _traverse_internals = [
3374 ("_raw_columns", InternalTraversal.dp_clauseelement_list),
3375 ("element", InternalTraversal.dp_clauseelement),
3376 ] + Executable._executable_traverse_internals
3377
3378 _cache_key_traversal = _traverse_internals + [
3379 ("_compile_options", InternalTraversal.dp_has_cache_key)
3380 ]
3381
3382 def __init__(self, entities, element):
3383 self._raw_columns = [
3384 coercions.expect(
3385 roles.ColumnsClauseRole,
3386 ent,
3387 apply_propagate_attrs=self,
3388 post_inspect=True,
3389 )
3390 for ent in util.to_list(entities)
3391 ]
3392 self.element = element
3393
3394 def get_label_style(self):
3395 return self._label_style
3396
3397 def set_label_style(self, label_style):
3398 return SelectStatementGrouping(
3399 self.element.set_label_style(label_style)
3400 )
3401
3402 @property
3403 def _label_style(self):
3404 return self.element._label_style
3405
3406 def _compiler_dispatch(self, compiler, **kw):
3407
3408 """provide a fixed _compiler_dispatch method.
3409
3410 This is roughly similar to using the sqlalchemy.ext.compiler
3411 ``@compiles`` extension.
3412
3413 """
3414
3415 compile_state = self._compile_state_factory(self, compiler, **kw)
3416
3417 toplevel = not compiler.stack
3418
3419 if toplevel:
3420 compiler.compile_state = compile_state
3421
3422 return compiler.process(compile_state.statement, **kw)
3423
3424 def _ensure_disambiguated_names(self):
3425 return self
3426
3427 def get_children(self, **kw):
3428 for elem in itertools.chain.from_iterable(
3429 element._from_objects for element in self._raw_columns
3430 ):
3431 yield elem
3432 for elem in super(FromStatement, self).get_children(**kw):
3433 yield elem
3434
3435 @property
3436 def _returning(self):
3437 return self.element._returning if self.element.is_dml else None
3438
3439 @property
3440 def _inline(self):
3441 return self.element._inline if self.element.is_dml else None
3442
3443
3444class AliasOption(interfaces.LoaderOption):
3445 @util.deprecated(
3446 "1.4",
3447 "The :class:`.AliasOption` is not necessary "
3448 "for entities to be matched up to a query that is established "
3449 "via :meth:`.Query.from_statement` and now does nothing.",
3450 )
3451 def __init__(self, alias):
3452 r"""Return a :class:`.MapperOption` that will indicate to the
3453 :class:`_query.Query`
3454 that the main table has been aliased.
3455
3456 """
3457
3458 inherit_cache = False
3459
3460 def process_compile_state(self, compile_state):
3461 pass
3462
3463
3464class BulkUD(object):
3465 """State used for the orm.Query version of update() / delete().
3466
3467 This object is now specific to Query only.
3468
3469 """
3470
3471 def __init__(self, query):
3472 self.query = query.enable_eagerloads(False)
3473 self._validate_query_state()
3474 self.mapper = self.query._entity_from_pre_ent_zero()
3475
3476 def _validate_query_state(self):
3477 for attr, methname, notset, op in (
3478 ("_limit_clause", "limit()", None, operator.is_),
3479 ("_offset_clause", "offset()", None, operator.is_),
3480 ("_order_by_clauses", "order_by()", (), operator.eq),
3481 ("_group_by_clauses", "group_by()", (), operator.eq),
3482 ("_distinct", "distinct()", False, operator.is_),
3483 (
3484 "_from_obj",
3485 "join(), outerjoin(), select_from(), or from_self()",
3486 (),
3487 operator.eq,
3488 ),
3489 (
3490 "_legacy_setup_joins",
3491 "join(), outerjoin(), select_from(), or from_self()",
3492 (),
3493 operator.eq,
3494 ),
3495 ):
3496 if not op(getattr(self.query, attr), notset):
3497 raise sa_exc.InvalidRequestError(
3498 "Can't call Query.update() or Query.delete() "
3499 "when %s has been called" % (methname,)
3500 )
3501
3502 @property
3503 def session(self):
3504 return self.query.session
3505
3506
3507class BulkUpdate(BulkUD):
3508 """BulkUD which handles UPDATEs."""
3509
3510 def __init__(self, query, values, update_kwargs):
3511 super(BulkUpdate, self).__init__(query)
3512 self.values = values
3513 self.update_kwargs = update_kwargs
3514
3515
3516class BulkDelete(BulkUD):
3517 """BulkUD which handles DELETEs."""