Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/orm/query.py: 41%
Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# orm/query.py
2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""The Query class and support.
10Defines the :class:`_query.Query` class, the central
11construct used by the ORM to construct database queries.
13The :class:`_query.Query` class should not be confused with the
14:class:`_expression.Select` class, which defines database
15SELECT operations at the SQL (non-ORM) level. ``Query`` differs from
16``Select`` in that it returns ORM-mapped objects and interacts with an
17ORM session, whereas the ``Select`` construct interacts directly with the
18database to return iterable result sets.
20"""
21import itertools
22import operator
23import types
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
73__all__ = ["Query", "QueryContext", "aliased"]
76@inspection._self_inspects
77@log.class_logger
78class Query(
79 _SelectFromElements,
80 SupportsCloneAnnotations,
81 HasPrefixes,
82 HasSuffixes,
83 HasHints,
84 Executable,
85):
87 """ORM-level SQL construction object.
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.
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.
104 For a full walk through of :class:`_query.Query` usage, see the
105 :ref:`ormtutorial_toplevel`.
107 """
109 # elements that are in Core and can be cached in the same way
110 _where_criteria = ()
111 _having_criteria = ()
113 _order_by_clauses = ()
114 _group_by_clauses = ()
115 _limit_clause = None
116 _offset_clause = None
118 _distinct = False
119 _distinct_on = ()
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
129 _memoized_select_entities = ()
131 _compile_options = ORMCompileState.default_compile_options
133 load_options = QueryContext.default_load_options + {
134 "_legacy_uniquing": True
135 }
137 _params = util.EMPTY_DICT
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
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()
151 def __init__(self, entities, session=None):
152 """Construct a :class:`_query.Query` directly.
154 E.g.::
156 q = Query([User, Address], session=some_session)
158 The above is equivalent to::
160 q = some_session.query(User, Address)
162 :param entities: a sequence of entities and/or SQL expressions.
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.
171 .. seealso::
173 :meth:`.Session.query`
175 :meth:`_query.Query.with_session`
177 """
179 self.session = session
180 self._set_entities(entities)
182 def _set_propagate_attrs(self, values):
183 self._propagate_attrs = util.immutabledict(values)
184 return self
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 ]
197 def _entity_from_pre_ent_zero(self):
198 if not self._raw_columns:
199 return None
201 ent = self._raw_columns[0]
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
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 )
228 return self._raw_columns[0]._annotations["parententity"]
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 ]
241 self._compile_options += {"_set_base_alias": set_base_alias}
242 self._from_obj = tuple(fa)
244 @_generative
245 def _set_lazyload_from(self, state):
246 self.load_options += {"_lazy_loaded_from": state}
248 def _get_condition(self):
249 return self._no_criterion_condition(
250 "get", order_by=False, distinct=False
251 )
253 def _get_existing_condition(self):
254 self._no_criterion_assertion("get", order_by=False, distinct=False)
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 )
275 def _no_criterion_condition(self, meth, order_by=True, distinct=True):
276 self._no_criterion_assertion(meth, order_by, distinct)
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
284 self._order_by_clauses = self._group_by_clauses = ()
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)
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 )
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 )
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 )
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 = {}
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
347 if load_options:
348 self.load_options += load_options
349 if compile_options:
350 self._compile_options += compile_options
352 return self
354 def _clone(self):
355 return self._generate()
357 @property
358 def statement(self):
359 """The full SELECT statement represented by this Query.
361 The statement by default will not have disambiguating labels
362 applied to the construct unless with_labels(True) is called
363 first.
365 """
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
396 if self._params:
397 stmt = stmt.params(self._params)
399 return stmt
401 def _final_statement(self, legacy_query_style=True):
402 """Return the 'final' SELECT statement for this :class:`.Query`.
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.
408 This method creates a complete compile state so is fairly expensive.
410 """
412 q = self._clone()
414 return q._compile_state(
415 use_legacy_query_style=legacy_query_style
416 ).statement
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}
430 compile_options = self._compile_options
431 compile_options += {
432 "_for_statement": for_statement,
433 "_use_legacy_query_style": use_legacy_query_style,
434 }
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)
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 )
464 return stmt
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`.
476 Eager JOIN generation within the query is disabled.
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.
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.
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.
493 """
494 q = self.enable_eagerloads(False)
495 if with_labels:
496 q = q.set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
498 q = q.statement
500 if reduce_columns:
501 q = q.reduce_columns()
502 return q.alias(name=name)
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).
508 Parameters and usage are the same as those of the
509 :meth:`_expression.SelectBase.cte` method; see that method for
510 further details.
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::
522 from sqlalchemy.orm import aliased
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)
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)
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 )
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)
554 .. seealso::
556 :meth:`_expression.HasCTE.cte`
558 """
559 return self.enable_eagerloads(False).statement.cte(
560 name=name, recursive=recursive, nesting=nesting
561 )
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.
568 Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.label`.
570 """
572 return self.enable_eagerloads(False).statement.label(name)
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.
584 """
585 return self.scalar_subquery()
587 def scalar_subquery(self):
588 """Return the full SELECT statement represented by this
589 :class:`_query.Query`, converted to a scalar subquery.
591 Analogous to
592 :meth:`sqlalchemy.sql.expression.SelectBase.scalar_subquery`.
594 .. versionchanged:: 1.4 The :meth:`_query.Query.scalar_subquery`
595 method replaces the :meth:`_query.Query.as_scalar` method.
597 """
599 return self.enable_eagerloads(False).statement.scalar_subquery()
601 @property
602 def selectable(self):
603 """Return the :class:`_expression.Select` object emitted by this
604 :class:`_query.Query`.
606 Used for :func:`_sa.inspect` compatibility, this is equivalent to::
608 query.enable_eagerloads(False).with_labels().statement
610 """
611 return self.__clause_element__()
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 )
622 @_generative
623 def only_return_tuples(self, value):
624 """When set to True, the query results will always be a tuple.
626 This is specifically for single element queries. The default is False.
628 .. versionadded:: 1.2.5
630 .. seealso::
632 :meth:`_query.Query.is_single_entity`
634 """
635 self.load_options += dict(_only_return_tuples=value)
637 @property
638 def is_single_entity(self):
639 """Indicates if this :class:`_query.Query`
640 returns tuples or single entities.
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.
646 .. versionadded:: 1.3.11
648 .. seealso::
650 :meth:`_query.Query.only_return_tuples`
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 )
663 @_generative
664 def enable_eagerloads(self, value):
665 """Control whether or not eager joins and subqueries are
666 rendered.
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.
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`.
678 """
679 self._compile_options += {"_enable_eagerloads": value}
681 @_generative
682 def _with_compile_options(self, **opt):
683 self._compile_options += opt
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)
693 apply_labels = with_labels
695 @property
696 def get_label_style(self):
697 """
698 Retrieve the current label style.
700 .. versionadded:: 1.4
702 """
703 return self._label_style
705 def set_label_style(self, style):
706 """Apply column labels to the return value of Query.statement.
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.
714 When the `Query` actually issues SQL to load rows, it always
715 uses column labeling.
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`::
726 result = session.execute(
727 query
728 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)
729 .statement
730 )
732 .. versionadded:: 1.4
734 """ # noqa
735 if self._label_style is not style:
736 self = self._generate()
737 self._label_style = style
738 return self
740 @_generative
741 def enable_assertions(self, value):
742 """Control whether assertions are generated.
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.
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.
759 """
760 self._enable_assertions = value
762 @property
763 def whereclause(self):
764 """A readonly attribute which returns the current WHERE criterion for
765 this Query.
767 This returned value is a SQL expression construct, or ``None`` if no
768 criterion has been established.
770 """
771 return sql.elements.BooleanClauseList._construct_for_whereclause(
772 self._where_criteria
773 )
775 @_generative
776 def _with_current_path(self, path):
777 """indicate that this query applies to objects loaded
778 within a certain path.
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.
784 """
785 self._compile_options += {"_current_path": path}
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.
798 This is a legacy method which is replaced by the
799 :func:`_orm.with_polymorphic` function.
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`.
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.
816 .. seealso::
818 :ref:`with_polymorphic` - illustrates current patterns
820 """
822 entity = _legacy_filter_by_entity_zero(self)
824 wp = with_polymorphic(
825 entity,
826 cls_or_mappers,
827 selectable=selectable,
828 polymorphic_on=polymorphic_on,
829 )
831 self._compile_options = self._compile_options.add_to_element(
832 "_with_polymorphic_adapt_map", ((entity, inspect(wp)),)
833 )
835 @_generative
836 def yield_per(self, count):
837 r"""Yield only ``count`` rows at a time.
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).
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.
853 .. seealso::
855 :ref:`orm_queryguide_yield_per`
857 """
858 self.load_options += {"_yield_per": count}
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.
869 E.g.::
871 my_user = session.query(User).get(5)
873 some_object = session.query(VersionedFoo).get((5, 10))
875 some_object = session.query(VersionedFoo).get(
876 {"id": 5, "version_id": 10})
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.
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.
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.
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.
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::
913 my_object = query.get(5)
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::
925 my_object = query.get((5, 10))
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::
932 my_object = query.get({"id": 5, "version_id": 10})
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.
940 :return: The object instance, or ``None``.
942 """
943 self._no_criterion_assertion("get", order_by=False, distinct=False)
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)
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 )
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.
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.
972 .. seealso::
974 :attr:`.ORMExecuteState.lazy_loaded_from`
976 """
977 return self.load_options._lazy_loaded_from
979 @property
980 def _current_path(self):
981 return self._compile_options._current_path
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`.
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.
994 The correlation arguments are ultimately passed to
995 :meth:`_expression.Select.correlate`
996 after coercion to expression constructs.
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.
1003 """
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 )
1013 @_generative
1014 def autoflush(self, setting):
1015 """Return a Query with a specific 'autoflush' setting.
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.
1022 """
1023 self.load_options += {"_autoflush": setting}
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`.
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.
1036 """
1037 self.load_options += {"_populate_existing": True}
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.
1045 Default is that of :attr:`_query.Query._invoke_all_eagers`.
1047 """
1048 self.load_options += {"_invoke_all_eagers": value}
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.
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`.
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.
1070 :param instance:
1071 An instance which has some :func:`_orm.relationship`.
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.
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.
1082 """
1083 relationships = util.preloaded.orm_relationships
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)
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 )
1110 return self.filter(with_parent(instance, property, entity_zero.entity))
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."""
1117 if alias is not None:
1118 # TODO: deprecate
1119 entity = aliased(entity, alias)
1121 self._raw_columns = list(self._raw_columns)
1123 self._raw_columns.append(
1124 coercions.expect(
1125 roles.ColumnsClauseRole, entity, apply_propagate_attrs=self
1126 )
1127 )
1129 @_generative
1130 def with_session(self, session):
1131 """Return a :class:`_query.Query` that will use the given
1132 :class:`.Session`.
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::
1145 from sqlalchemy.orm import Query
1147 query = Query([MyClass]).filter(MyClass.id == 5)
1149 result = query.with_session(my_session).one()
1151 """
1153 self.session = session
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.
1167 :meth:`_query.Query.from_self` essentially turns the SELECT statement
1168 into a SELECT of itself. Given a query such as::
1170 q = session.query(User).filter(User.name.like('e%'))
1172 Given the :meth:`_query.Query.from_self` version::
1174 q = session.query(User).filter(User.name.like('e%')).from_self()
1176 This query renders as:
1178 .. sourcecode:: sql
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
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::
1192 q = session.query(User).filter(User.name.like('e%')).\
1193 limit(5).from_self().\
1194 join(User.addresses).filter(Address.email.like('q%'))
1196 The above query joins to the ``Address`` entity but only against the
1197 first five results of the ``User`` query:
1199 .. sourcecode:: sql
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
1210 **Automatic Aliasing**
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::
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)
1224 The ORDER BY against ``User.name`` is aliased to be in terms of the
1225 inner subquery:
1227 .. sourcecode:: sql
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
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!
1247 **Changing the Entities**
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::
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%'))
1260 yielding:
1262 .. sourcecode:: sql
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
1272 **Looking out for Inner / Outer Columns**
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::
1283 q = session.query(Address).join(Address.user).\
1284 filter(User.name.like('e%'))
1286 q = q.add_entity(User).from_self().\
1287 options(contains_eager(Address.user))
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:
1296 .. sourcecode:: sql
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
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:
1318 .. sourcecode:: sql
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"
1333 :param \*entities: optional list of entities which will replace
1334 those being selected.
1336 """
1337 return self._from_self(*entities)
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 )
1347 q = self._from_selectable(fromclause)
1349 if entities:
1350 q._set_entities(entities)
1351 return q
1353 @_generative
1354 def _set_enable_single_crit(self, val):
1355 self._compile_options += {"_enable_single_crit": val}
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 }
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}
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
1397 """
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)
1407 _values = values
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.
1420 """
1421 try:
1422 return next(self.values(column))[0]
1423 except StopIteration:
1424 return None
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.
1432 e.g.::
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)
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)
1450 """
1451 _MemoizedSelectEntities._generate_for_statement(self)
1452 self._set_entities(entities)
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."""
1459 self._raw_columns = list(self._raw_columns)
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 )
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.
1481 """
1482 return self.add_columns(column)
1484 @_generative
1485 def options(self, *args):
1486 """Return a new :class:`_query.Query` object,
1487 applying the given list of
1488 mapper options.
1490 Most supplied options regard changing how column- and
1491 relationship-mapped attributes are loaded.
1493 .. seealso::
1495 :ref:`deferred_options`
1497 :ref:`relationship_loader_options`
1499 """
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)
1511 self._with_options += opts
1513 def with_transformation(self, fn):
1514 """Return a new :class:`_query.Query` object transformed by
1515 the given function.
1517 E.g.::
1519 def filter_something(criterion):
1520 def transform(q):
1521 return q.filter(criterion)
1522 return transform
1524 q = q.with_transformation(filter_something(x==5))
1526 This allows ad-hoc recipes to be created for :class:`_query.Query`
1527 objects. See the example at :ref:`hybrid_transformers`.
1529 """
1530 return fn(self)
1532 def get_execution_options(self):
1533 """Get the non-SQL options which will take effect during execution.
1535 .. versionadded:: 1.3
1537 .. seealso::
1539 :meth:`_query.Query.execution_options`
1540 """
1541 return self._execution_options
1543 @_generative
1544 def execution_options(self, **kwargs):
1545 """Set non-SQL options which take effect during execution.
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:
1551 ``populate_existing=True`` - equivalent to using
1552 :meth:`_orm.Query.populate_existing`
1554 ``autoflush=True|False`` - equivalent to using
1555 :meth:`_orm.Query.autoflush`
1557 ``yield_per=<value>`` - equivalent to using
1558 :meth:`_orm.Query.yield_per`
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.
1564 .. versionadded:: 1.4 - added ORM options to
1565 :meth:`_orm.Query.execution_options`
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.
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`.
1580 .. seealso::
1582 :ref:`engine_stream_results`
1584 :meth:`_query.Query.get_execution_options`
1586 """
1587 self._execution_options = self._execution_options.union(kwargs)
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.
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.
1610 E.g.::
1612 q = sess.query(User).populate_existing().with_for_update(nowait=True, of=User)
1614 The above query on a PostgreSQL backend will render like::
1616 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT
1618 .. warning::
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.
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`.
1634 .. seealso::
1636 :meth:`_expression.GenerativeSelect.with_for_update`
1637 - Core level method with
1638 full argument and behavioral description.
1640 :meth:`_orm.Query.populate_existing` - overwrites attributes of
1641 objects already loaded in the identity map.
1643 """ # noqa: E501
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 )
1653 @_generative
1654 def params(self, *args, **kwargs):
1655 r"""Add values for bind parameters which may have been
1656 specified in filter().
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.
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)
1673 def where(self, *criterion):
1674 """A synonym for :meth:`.Query.filter`.
1676 .. versionadded:: 1.4
1678 """
1679 return self.filter(*criterion)
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.
1687 e.g.::
1689 session.query(MyClass).filter(MyClass.name == 'some name')
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::
1695 session.query(MyClass).\
1696 filter(MyClass.name == 'some name', MyClass.id > 5)
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.
1703 .. seealso::
1705 :meth:`_query.Query.filter_by` - filter on keyword expressions.
1707 """
1708 for criterion in list(criterion):
1709 criterion = coercions.expect(
1710 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1711 )
1713 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
1714 if self._aliased_generation:
1715 criterion = sql_util._deep_annotate(
1716 criterion, {"aliased_generation": self._aliased_generation}
1717 )
1718 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^^
1720 self._where_criteria += (criterion,)
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
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.
1735 """
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
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]
1771 return self._raw_columns[0]
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.
1777 e.g.::
1779 session.query(MyClass).filter_by(name = 'some name')
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::
1785 session.query(MyClass).\
1786 filter_by(name = 'some name', id = 5)
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`.
1792 .. seealso::
1794 :meth:`_query.Query.filter` - filter on SQL expressions.
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 )
1806 clauses = [
1807 _entity_namespace_key(from_entity, key) == value
1808 for key, value in kwargs.items()
1809 ]
1810 return self.filter(*clauses)
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`.
1818 e.g.::
1820 q = session.query(Entity).order_by(Entity.id, Entity.name)
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.::
1827 # will erase all ORDER BY and ORDER BY new_col alone
1828 q = q.order_by(None).order_by(new_col)
1830 .. seealso::
1832 These sections describe ORDER BY in terms of :term:`2.0 style`
1833 invocation but apply to :class:`_orm.Query` as well:
1835 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`
1837 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
1839 """
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 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
1860 self._order_by_clauses += criterion
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`.
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.
1872 .. seealso::
1874 These sections describe GROUP BY in terms of :term:`2.0 style`
1875 invocation but apply to :class:`_orm.Query` as well:
1877 :ref:`tutorial_group_by_w_aggregates` - in the
1878 :ref:`unified_tutorial`
1880 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`
1882 """
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 ^^^^^^^^^^^^^^^^^^^^^^^^^^
1903 self._group_by_clauses += criterion
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`.
1911 :meth:`_query.Query.having` is used in conjunction with
1912 :meth:`_query.Query.group_by`.
1914 HAVING criterion makes it possible to use filters on aggregate
1915 functions like COUNT, SUM, AVG, MAX, and MIN, eg.::
1917 q = session.query(User.id).\
1918 join(User.addresses).\
1919 group_by(User.id).\
1920 having(func.count(Address.id) > 2)
1922 """
1924 self._having_criteria += (
1925 coercions.expect(
1926 roles.WhereHavingRole, criterion, apply_propagate_attrs=self
1927 ),
1928 )
1930 def _set_op(self, expr_fn, *q):
1931 return self._from_selectable(expr_fn(*([self] + list(q))).subquery())
1933 def union(self, *q):
1934 """Produce a UNION of this Query against one or more queries.
1936 e.g.::
1938 q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar')
1939 q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo')
1941 q3 = q1.union(q2)
1943 The method accepts multiple Query objects so as to control
1944 the level of nesting. A series of ``union()`` calls such as::
1946 x.union(y).union(z).all()
1948 will nest on each ``union()``, and produces::
1950 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION
1951 SELECT * FROM y) UNION SELECT * FROM Z)
1953 Whereas::
1955 x.union(y, z).all()
1957 produces::
1959 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION
1960 SELECT * FROM Z)
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.
1969 """
1970 return self._set_op(expression.union, *q)
1972 def union_all(self, *q):
1973 """Produce a UNION ALL of this Query against one or more queries.
1975 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
1976 that method for usage examples.
1978 """
1979 return self._set_op(expression.union_all, *q)
1981 def intersect(self, *q):
1982 """Produce an INTERSECT of this Query against one or more queries.
1984 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
1985 that method for usage examples.
1987 """
1988 return self._set_op(expression.intersect, *q)
1990 def intersect_all(self, *q):
1991 """Produce an INTERSECT ALL of this Query against one or more queries.
1993 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
1994 that method for usage examples.
1996 """
1997 return self._set_op(expression.intersect_all, *q)
1999 def except_(self, *q):
2000 """Produce an EXCEPT of this Query against one or more queries.
2002 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2003 that method for usage examples.
2005 """
2006 return self._set_op(expression.except_, *q)
2008 def except_all(self, *q):
2009 """Produce an EXCEPT ALL of this Query against one or more queries.
2011 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See
2012 that method for usage examples.
2014 """
2015 return self._set_op(expression.except_all, *q)
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
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`.
2031 **Simple Relationship Joins**
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::
2041 q = session.query(User).join(User.addresses)
2043 Where above, the call to :meth:`_query.Query.join` along
2044 ``User.addresses`` will result in SQL approximately equivalent to::
2046 SELECT user.id, user.name
2047 FROM user JOIN address ON user.id = address.user_id
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.
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::
2057 q = session.query(User).\
2058 join(User.orders).\
2059 join(Order.items).\
2060 join(Item.keywords)
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.
2075 **Joins to a Target Entity or Selectable**
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::
2082 q = session.query(User).join(Address)
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.
2093 **Joins to a Target with an ON Clause**
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::
2099 q = session.query(User).join(Address, User.id==Address.user_id)
2101 The above form may also use a relationship-bound attribute as the
2102 ON clause as well::
2104 q = session.query(User).join(Address, User.addresses)
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::
2111 a1 = aliased(Address)
2112 a2 = aliased(Address)
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')
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::
2124 a1 = aliased(Address)
2125 a2 = aliased(Address)
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')
2133 **Augmenting Built-in ON Clauses**
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::
2141 q = session.query(User).join(
2142 User.addresses.and_(Address.email_address != 'foo@bar.com')
2143 )
2145 .. versionadded:: 1.4
2147 **Joining to Tables and Subqueries**
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::
2155 subq = session.query(Address).\
2156 filter(Address.email_address == 'ed@foo.com').\
2157 subquery()
2160 q = session.query(User).join(
2161 subq, User.id == subq.c.user_id
2162 )
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`::
2168 subq = session.query(Address).\
2169 filter(Address.email_address == 'ed@foo.com').\
2170 subquery()
2172 address_subq = aliased(Address, subq)
2174 q = session.query(User).join(
2175 User.addresses.of_type(address_subq)
2176 )
2179 **Controlling what to Join From**
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::
2185 q = session.query(Address).select_from(User).\
2186 join(User.addresses).\
2187 filter(User.name == 'ed')
2189 Which will produce SQL similar to::
2191 SELECT address.* FROM user
2192 JOIN address ON user.id=address.user_id
2193 WHERE user.name = :name_1
2195 **Legacy Features of Query.join()**
2197 .. deprecated:: 1.4 The following features are deprecated and will
2198 be removed in SQLAlchemy 2.0.
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:
2206 * Joining on relationship names rather than attributes::
2208 session.query(User).join("addresses")
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.
2220 **Modern calling pattern**: Use the actual relationship,
2221 e.g. ``User.addresses`` in the above case::
2223 session.query(User).join(User.addresses)
2225 * Automatic aliasing with the ``aliased=True`` flag::
2227 session.query(Node).join(Node.children, aliased=True).\
2228 filter(Node.name == 'some name')
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.
2238 **Modern calling pattern**: Use the :func:`_orm.aliased` construct
2239 explicitly::
2241 from sqlalchemy.orm import aliased
2243 n1 = aliased(Node)
2245 session.query(Node).join(Node.children.of_type(n1)).\
2246 filter(n1.name == 'some name')
2248 * Multiple joins in one call::
2250 session.query(User).join("orders", "items")
2252 session.query(User).join(User.orders, Order.items)
2254 session.query(User).join(
2255 (Order, User.orders),
2256 (Item, Item.order_id == Order.id)
2257 )
2259 session.query(User).join(Order, Item)
2261 # ... and several more forms actually
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.
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::
2275 session.query(User).join(User.orders).join(
2276 Item, Item.order_id == Order.id)
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.
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.
2289 :param full=False: render FULL OUTER JOIN; implies ``isouter``.
2291 .. versionadded:: 1.1
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.
2298 .. note:: This flag is considered legacy.
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.
2305 .. note:: This flag is considered legacy.
2307 .. seealso::
2309 :ref:`ormtutorial_joins` in the ORM tutorial.
2311 :ref:`inheritance_toplevel` for details on how
2312 :meth:`_query.Query.join` is used for inheritance relationships.
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.
2318 """
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 )
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 )
2334 if kwargs:
2335 raise TypeError(
2336 "unknown arguments: %s" % ", ".join(sorted(kwargs))
2337 )
2339 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
2340 if not from_joinpoint:
2341 self._last_joined_entity = None
2342 self._aliased_generation = None
2343 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^^
2345 if props:
2346 onclause, legacy = props[0], props[1:]
2347 else:
2348 onclause = legacy = None
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 = []
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])
2395 _props.append(prop)
2396 else:
2397 _single.append(prop)
2398 if _single:
2399 _props.extend((_s,) for _s in _single)
2401 # legacy vvvvvvvvvvvvvvvvvvvvvvvvvvv
2402 if aliased:
2403 self._aliased_generation = self._next_aliased_generation()
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 ]
2421 # legacy ^^^^^^^^^^^^^^^^^^^^^^^^^^^
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 )
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 )
2457 self._legacy_setup_joins += joins_to_add
2459 self.__dict__.pop("_last_joined_entity", None)
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``.
2465 Usage is the same as the ``join()`` method.
2467 """
2468 kwargs["isouter"] = True
2469 return self.join(target, *props, **kwargs)
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.
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.
2482 """
2483 self._last_joined_entity = None
2484 self._aliased_generation = None
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.
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.
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.
2501 A typical example::
2503 q = session.query(Address).select_from(User).\
2504 join(User.addresses).\
2505 filter(User.name == 'ed')
2507 Which produces SQL equivalent to::
2509 SELECT address.* FROM user
2510 JOIN address ON user.id=address.user_id
2511 WHERE user.name = :name_1
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.
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.
2525 .. seealso::
2527 :meth:`~.Query.join`
2529 :meth:`.Query.select_entity_from`
2531 """
2533 self._set_select_from(from_obj, False)
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.
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.
2554 Given a case for :func:`.aliased` such as selecting ``User``
2555 objects from a SELECT statement::
2557 select_stmt = select(User).where(User.id == 7)
2558 user_alias = aliased(User, select_stmt)
2560 q = session.query(user_alias).\
2561 filter(user_alias.name == 'ed')
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::
2569 q = session.query(User).\
2570 select_entity_from(select_stmt.subquery()).\
2571 filter(User.name == 'ed')
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:
2576 .. sourcecode:: sql
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
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:
2592 .. sourcecode:: sql
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
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::
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)
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::
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)
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.
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`.
2647 .. seealso::
2649 :meth:`_query.Query.select_from`
2651 """
2653 self._set_select_from([from_obj], True)
2654 self._compile_options += {"_enable_single_crit": False}
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 )
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`.
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.
2674 For example, ::
2676 session.query(User).order_by(User.id).slice(1, 3)
2678 renders as
2680 .. sourcecode:: sql
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)
2688 .. seealso::
2690 :meth:`_query.Query.limit`
2692 :meth:`_query.Query.offset`
2694 """
2696 self._limit_clause, self._offset_clause = sql_util._make_slice(
2697 self._limit_clause, self._offset_clause, start, stop
2698 )
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``.
2706 """
2707 self._limit_clause = sql_util._offset_or_limit_clause(limit)
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``.
2715 """
2716 self._offset_clause = sql_util._offset_or_limit_clause(offset)
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``.
2725 .. note::
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.
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.
2741 :param \*expr: optional column expressions. When present,
2742 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``
2743 construct.
2745 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated
2746 and will raise :class:`_exc.CompileError` in a future version.
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
2757 def all(self):
2758 """Return the results represented by this :class:`_query.Query`
2759 as a list.
2761 This results in an execution of the underlying SQL statement.
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.
2769 .. seealso::
2771 :ref:`faq_query_deduplicating`
2772 """
2773 return self._iter().all()
2775 @_generative
2776 @_assertions(_no_clauseelement_condition)
2777 def from_statement(self, statement):
2778 """Execute the given SELECT statement and return results.
2780 This method bypasses all internal statement compilation, and the
2781 statement is executed without modification.
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`.
2789 .. seealso::
2791 :ref:`orm_tutorial_literal_sql` - usage examples in the
2792 ORM tutorial
2794 """
2795 statement = coercions.expect(
2796 roles.SelectStatementRole, statement, apply_propagate_attrs=self
2797 )
2798 self._statement = statement
2800 def first(self):
2801 """Return the first result of this ``Query`` or
2802 None if the result doesn't contain any row.
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).
2809 Calling :meth:`_query.Query.first`
2810 results in an execution of the underlying
2811 query.
2813 .. seealso::
2815 :meth:`_query.Query.one`
2817 :meth:`_query.Query.one_or_none`
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()
2826 def one_or_none(self):
2827 """Return at most one result or raise an exception.
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.
2835 Calling :meth:`_query.Query.one_or_none`
2836 results in an execution of the
2837 underlying query.
2839 .. versionadded:: 1.0.9
2841 Added :meth:`_query.Query.one_or_none`
2843 .. seealso::
2845 :meth:`_query.Query.first`
2847 :meth:`_query.Query.one`
2849 """
2850 return self._iter().one_or_none()
2852 def one(self):
2853 """Return exactly one result or raise an exception.
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.
2861 Calling :meth:`.one` results in an execution of the underlying query.
2863 .. seealso::
2865 :meth:`_query.Query.first`
2867 :meth:`_query.Query.one_or_none`
2869 """
2870 return self._iter().one()
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.
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
2888 This results in an execution of the underlying query.
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
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
2911 def _iter(self):
2912 # new style execution.
2913 params = self._params
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 )
2922 # legacy: automatically set scalars, unique
2923 if result._attributes.get("is_single_entity", False):
2924 result = result.scalars()
2926 if (
2927 result._attributes.get("filtered", False)
2928 and not self.load_options._yield_per
2929 ):
2930 result = result.unique()
2932 return result
2934 def __str__(self):
2935 statement = self._statement_20()
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
2946 return str(statement.compile(bind))
2948 def _get_bind_args(self, statement, fn, **kw):
2949 return fn(clause=statement, **kw)
2951 @property
2952 def column_descriptions(self):
2953 """Return metadata about the columns which would be
2954 returned by this :class:`_query.Query`.
2956 Format is a list of dictionaries::
2958 user_alias = aliased(User, name='user2')
2959 q = sess.query(User, User.id, user_alias)
2961 # this expression:
2962 q.column_descriptions
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 ]
2989 .. seealso::
2991 This API is available using :term:`2.0 style` queries as well,
2992 documented at:
2994 * :ref:`queryguide_inspection`
2996 * :attr:`.Select.column_descriptions`
2998 """
3000 return _column_descriptions(self, legacy=True)
3002 def instances(self, result_proxy, context=None):
3003 """Return an ORM result given a :class:`_engine.CursorResult` and
3004 :class:`.QueryContext`.
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)
3017 context = QueryContext(
3018 compile_state,
3019 compile_state.statement,
3020 self._params,
3021 self.session,
3022 self.load_options,
3023 )
3025 result = loading.instances(result_proxy, context)
3027 # legacy: automatically set scalars, unique
3028 if result._attributes.get("is_single_entity", False):
3029 result = result.scalars()
3031 if result._attributes.get("filtered", False):
3032 result = result.unique()
3034 return result
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.
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.
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.
3060 The 'load' argument is the same as that of :meth:`.Session.merge`.
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`.
3067 """
3069 return loading.merge_result(self, iterator, load)
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 ...).
3075 e.g.::
3077 q = session.query(User).filter(User.name == 'fred')
3078 session.query(q.exists())
3080 Producing SQL similar to::
3082 SELECT EXISTS (
3083 SELECT 1 FROM users WHERE users.name = :name_1
3084 ) AS anon_1
3086 The EXISTS construct is usually used in the WHERE clause::
3088 session.query(User.id).filter(q.exists()).scalar()
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`::
3095 from sqlalchemy import literal
3097 session.query(literal(True)).filter(q.exists()).scalar()
3099 """
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.
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 )
3114 ezero = self._entity_from_pre_ent_zero()
3115 if ezero is not None:
3116 inner = inner.select_from(ezero)
3118 return sql.exists(inner)
3120 def count(self):
3121 r"""Return a count of rows this the SQL formed by this :class:`Query`
3122 would return.
3124 This generates the SQL for this Query as follows::
3126 SELECT count(1) AS count_1 FROM (
3127 SELECT <rest of query follows...>
3128 ) AS anon_1
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.
3135 .. warning::
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.
3147 .. seealso::
3149 :ref:`faq_query_deduplicating`
3151 :ref:`orm_tutorial_query_returning`
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.::
3158 from sqlalchemy import func
3160 # count User records, without
3161 # using a subquery.
3162 session.query(func.count(User.id))
3164 # return count of user "id" grouped
3165 # by "name"
3166 session.query(func.count(User.id)).\
3167 group_by(User.name)
3169 from sqlalchemy import distinct
3171 # count distinct "name" values
3172 session.query(func.count(distinct(User.name)))
3174 """
3175 col = sql.func.count(sql.literal_column("*"))
3176 return self._from_self(col).enable_eagerloads(False).scalar()
3178 def delete(self, synchronize_session="evaluate"):
3179 r"""Perform a DELETE with an arbitrary WHERE clause.
3181 Deletes rows matched by this query from the database.
3183 E.g.::
3185 sess.query(User).filter(User.age == 25).\
3186 delete(synchronize_session=False)
3188 sess.query(User).filter(User.age == 25).\
3189 delete(synchronize_session='evaluate')
3191 .. warning::
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.
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.
3202 :return: the count of rows matched as returned by the database's
3203 "row count" feature.
3205 .. seealso::
3207 :ref:`orm_expression_update_delete`
3209 """
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
3218 self = bulk_del.query
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()
3231 return result.rowcount
3233 def update(self, values, synchronize_session="evaluate", update_args=None):
3234 r"""Perform an UPDATE with an arbitrary WHERE clause.
3236 Updates rows matched by this query in the database.
3238 E.g.::
3240 sess.query(User).filter(User.age == 25).\
3241 update({User.age: User.age - 10}, synchronize_session=False)
3243 sess.query(User).filter(User.age == 25).\
3244 update({"age": User.age - 10}, synchronize_session='evaluate')
3246 .. warning::
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.
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.
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.
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`.
3273 :return: the count of rows matched as returned by the database's
3274 "row count" feature.
3277 .. seealso::
3279 :ref:`orm_expression_update_delete`
3282 """
3284 update_args = update_args or {}
3286 bulk_ud = BulkUpdate(self, values, update_args)
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
3295 upd = sql.update(*self._raw_columns)
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)
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
3316 def _compile_state(self, for_statement=False, **kw):
3317 """Create an out-of-compiler ORMCompileState object.
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.
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.
3331 """
3333 stmt = self._statement_20(for_statement=for_statement, **kw)
3334 assert for_statement == stmt._compile_options._for_statement
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 )
3344 return compile_state_cls.create_for_statement(stmt, None)
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 )
3356 return context
3359class FromStatement(GroupedElement, SelectBase, Executable):
3360 """Core construct that represents a load of ORM objects from a finished
3361 select or text construct.
3363 """
3365 __visit_name__ = "orm_from_statement"
3367 _compile_options = ORMFromStatementCompileState.default_compile_options
3369 _compile_state_factory = ORMFromStatementCompileState.create_for_statement
3371 _for_update_arg = None
3373 _traverse_internals = [
3374 ("_raw_columns", InternalTraversal.dp_clauseelement_list),
3375 ("element", InternalTraversal.dp_clauseelement),
3376 ] + Executable._executable_traverse_internals
3378 _cache_key_traversal = _traverse_internals + [
3379 ("_compile_options", InternalTraversal.dp_has_cache_key)
3380 ]
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
3394 def get_label_style(self):
3395 return self._label_style
3397 def set_label_style(self, label_style):
3398 return SelectStatementGrouping(
3399 self.element.set_label_style(label_style)
3400 )
3402 @property
3403 def _label_style(self):
3404 return self.element._label_style
3406 def _compiler_dispatch(self, compiler, **kw):
3408 """provide a fixed _compiler_dispatch method.
3410 This is roughly similar to using the sqlalchemy.ext.compiler
3411 ``@compiles`` extension.
3413 """
3415 compile_state = self._compile_state_factory(self, compiler, **kw)
3417 toplevel = not compiler.stack
3419 if toplevel:
3420 compiler.compile_state = compile_state
3422 return compiler.process(compile_state.statement, **kw)
3424 def _ensure_disambiguated_names(self):
3425 return self
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
3435 @property
3436 def _returning(self):
3437 return self.element._returning if self.element.is_dml else None
3439 @property
3440 def _inline(self):
3441 return self.element._inline if self.element.is_dml else None
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.
3456 """
3458 inherit_cache = False
3460 def process_compile_state(self, compile_state):
3461 pass
3464class BulkUD(object):
3465 """State used for the orm.Query version of update() / delete().
3467 This object is now specific to Query only.
3469 """
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()
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 )
3502 @property
3503 def session(self):
3504 return self.query.session
3507class BulkUpdate(BulkUD):
3508 """BulkUD which handles UPDATEs."""
3510 def __init__(self, query, values, update_kwargs):
3511 super(BulkUpdate, self).__init__(query)
3512 self.values = values
3513 self.update_kwargs = update_kwargs
3516class BulkDelete(BulkUD):
3517 """BulkUD which handles DELETEs."""