1# orm/query.py 2# Copyright (C) 2005-2018 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: http://www.opensource.org/licenses/mit-license.php 7 8"""The Query class and support. 9 10Defines the :class:`.Query` class, the central 11construct used by the ORM to construct database queries. 12 13The :class:`.Query` class should not be confused with the 14:class:`.Select` class, which defines database 15SELECT operations at the SQL (non-ORM) level. ``Query`` differs from 16``Select`` in that it returns ORM-mapped objects and interacts with an 17ORM session, whereas the ``Select`` construct interacts directly with the 18database to return iterable result sets. 19 20""" 21 22from itertools import chain 23 24from . import ( 25 attributes, interfaces, object_mapper, persistence, 26 exc as orm_exc, loading 27) 28from .base import _entity_descriptor, _is_aliased_class, \ 29 _is_mapped_class, _orm_columns, _generative, InspectionAttr 30from .path_registry import PathRegistry 31from .util import ( 32 AliasedClass, ORMAdapter, join as orm_join, with_parent, aliased 33) 34from .. import sql, util, log, exc as sa_exc, inspect, inspection 35from ..sql.expression import _interpret_as_from 36from ..sql import ( 37 util as sql_util, 38 expression, visitors 39) 40from ..sql.base import ColumnCollection 41from . import properties 42 43__all__ = ['Query', 'QueryContext', 'aliased'] 44 45 46_path_registry = PathRegistry.root 47 48 49@inspection._self_inspects 50@log.class_logger 51class Query(object): 52 """ORM-level SQL construction object. 53 54 :class:`.Query` is the source of all SELECT statements generated by the 55 ORM, both those formulated by end-user query operations as well as by 56 high level internal operations such as related collection loading. It 57 features a generative interface whereby successive calls return a new 58 :class:`.Query` object, a copy of the former with additional 59 criteria and options associated with it. 60 61 :class:`.Query` objects are normally initially generated using the 62 :meth:`~.Session.query` method of :class:`.Session`, and in 63 less common cases by instantiating the :class:`.Query` directly and 64 associating with a :class:`.Session` using the :meth:`.Query.with_session` 65 method. 66 67 For a full walkthrough of :class:`.Query` usage, see the 68 :ref:`ormtutorial_toplevel`. 69 70 """ 71 72 _enable_eagerloads = True 73 _enable_assertions = True 74 _with_labels = False 75 _criterion = None 76 _yield_per = None 77 _order_by = False 78 _group_by = False 79 _having = None 80 _distinct = False 81 _prefixes = None 82 _suffixes = None 83 _offset = None 84 _limit = None 85 _for_update_arg = None 86 _statement = None 87 _correlate = frozenset() 88 _populate_existing = False 89 _invoke_all_eagers = True 90 _version_check = False 91 _autoflush = True 92 _only_load_props = None 93 _refresh_state = None 94 _from_obj = () 95 _join_entities = () 96 _select_from_entity = None 97 _mapper_adapter_map = {} 98 _filter_aliases = None 99 _from_obj_alias = None 100 _joinpath = _joinpoint = util.immutabledict() 101 _execution_options = util.immutabledict() 102 _params = util.immutabledict() 103 _attributes = util.immutabledict() 104 _with_options = () 105 _with_hints = () 106 _enable_single_crit = True 107 _orm_only_adapt = True 108 _orm_only_from_obj_alias = True 109 _current_path = _path_registry 110 _has_mapper_entities = False 111 112 def __init__(self, entities, session=None): 113 """Construct a :class:`.Query` directly. 114 115 E.g.:: 116 117 q = Query([User, Address], session=some_session) 118 119 The above is equivalent to:: 120 121 q = some_session.query(User, Address) 122 123 :param entities: a sequence of entities and/or SQL expressions. 124 125 :param session: a :class:`.Session` with which the :class:`.Query` 126 will be associated. Optional; a :class:`.Query` can be associated 127 with a :class:`.Session` generatively via the 128 :meth:`.Query.with_session` method as well. 129 130 .. seealso:: 131 132 :meth:`.Session.query` 133 134 :meth:`.Query.with_session` 135 136 """ 137 self.session = session 138 self._polymorphic_adapters = {} 139 self._set_entities(entities) 140 141 def _set_entities(self, entities, entity_wrapper=None): 142 if entity_wrapper is None: 143 entity_wrapper = _QueryEntity 144 self._entities = [] 145 self._primary_entity = None 146 self._has_mapper_entities = False 147 for ent in util.to_list(entities): 148 entity_wrapper(self, ent) 149 150 self._set_entity_selectables(self._entities) 151 152 def _set_entity_selectables(self, entities): 153 self._mapper_adapter_map = d = self._mapper_adapter_map.copy() 154 155 for ent in entities: 156 for entity in ent.entities: 157 if entity not in d: 158 ext_info = inspect(entity) 159 if not ext_info.is_aliased_class and \ 160 ext_info.mapper.with_polymorphic: 161 if ext_info.mapper.mapped_table not in \ 162 self._polymorphic_adapters: 163 self._mapper_loads_polymorphically_with( 164 ext_info.mapper, 165 sql_util.ColumnAdapter( 166 ext_info.selectable, 167 ext_info.mapper._equivalent_columns 168 ) 169 ) 170 aliased_adapter = None 171 elif ext_info.is_aliased_class: 172 aliased_adapter = ext_info._adapter 173 else: 174 aliased_adapter = None 175 176 d[entity] = ( 177 ext_info, 178 aliased_adapter 179 ) 180 ent.setup_entity(*d[entity]) 181 182 def _mapper_loads_polymorphically_with(self, mapper, adapter): 183 for m2 in mapper._with_polymorphic_mappers or [mapper]: 184 self._polymorphic_adapters[m2] = adapter 185 for m in m2.iterate_to_root(): 186 self._polymorphic_adapters[m.local_table] = adapter 187 188 def _set_select_from(self, obj, set_base_alias): 189 fa = [] 190 select_from_alias = None 191 192 for from_obj in obj: 193 info = inspect(from_obj) 194 if hasattr(info, 'mapper') and \ 195 (info.is_mapper or info.is_aliased_class): 196 self._select_from_entity = info 197 if set_base_alias and not info.is_aliased_class: 198 raise sa_exc.ArgumentError( 199 "A selectable (FromClause) instance is " 200 "expected when the base alias is being set.") 201 fa.append(info.selectable) 202 elif not info.is_selectable: 203 raise sa_exc.ArgumentError( 204 "argument is not a mapped class, mapper, " 205 "aliased(), or FromClause instance.") 206 else: 207 if isinstance(from_obj, expression.SelectBase): 208 from_obj = from_obj.alias() 209 if set_base_alias: 210 select_from_alias = from_obj 211 fa.append(from_obj) 212 213 self._from_obj = tuple(fa) 214 215 if set_base_alias and \ 216 len(self._from_obj) == 1 and \ 217 isinstance(select_from_alias, expression.Alias): 218 equivs = self.__all_equivs() 219 self._from_obj_alias = sql_util.ColumnAdapter( 220 self._from_obj[0], equivs) 221 elif set_base_alias and \ 222 len(self._from_obj) == 1 and \ 223 hasattr(info, "mapper") and \ 224 info.is_aliased_class: 225 self._from_obj_alias = info._adapter 226 227 def _reset_polymorphic_adapter(self, mapper): 228 for m2 in mapper._with_polymorphic_mappers: 229 self._polymorphic_adapters.pop(m2, None) 230 for m in m2.iterate_to_root(): 231 self._polymorphic_adapters.pop(m.local_table, None) 232 233 def _adapt_polymorphic_element(self, element): 234 if "parententity" in element._annotations: 235 search = element._annotations['parententity'] 236 alias = self._polymorphic_adapters.get(search, None) 237 if alias: 238 return alias.adapt_clause(element) 239 240 if isinstance(element, expression.FromClause): 241 search = element 242 elif hasattr(element, 'table'): 243 search = element.table 244 else: 245 return None 246 247 alias = self._polymorphic_adapters.get(search, None) 248 if alias: 249 return alias.adapt_clause(element) 250 251 def _adapt_col_list(self, cols): 252 return [ 253 self._adapt_clause( 254 expression._literal_as_label_reference(o), 255 True, True) 256 for o in cols 257 ] 258 259 @_generative() 260 def _adapt_all_clauses(self): 261 self._orm_only_adapt = False 262 263 def _adapt_clause(self, clause, as_filter, orm_only): 264 """Adapt incoming clauses to transformations which 265 have been applied within this query.""" 266 267 adapters = [] 268 # do we adapt all expression elements or only those 269 # tagged as 'ORM' constructs ? 270 if not self._orm_only_adapt: 271 orm_only = False 272 273 if as_filter and self._filter_aliases: 274 for fa in self._filter_aliases._visitor_iterator: 275 adapters.append( 276 ( 277 orm_only, fa.replace 278 ) 279 ) 280 281 if self._from_obj_alias: 282 # for the "from obj" alias, apply extra rule to the 283 # 'ORM only' check, if this query were generated from a 284 # subquery of itself, i.e. _from_selectable(), apply adaption 285 # to all SQL constructs. 286 adapters.append( 287 ( 288 orm_only if self._orm_only_from_obj_alias else False, 289 self._from_obj_alias.replace 290 ) 291 ) 292 293 if self._polymorphic_adapters: 294 adapters.append( 295 ( 296 orm_only, self._adapt_polymorphic_element 297 ) 298 ) 299 300 if not adapters: 301 return clause 302 303 def replace(elem): 304 for _orm_only, adapter in adapters: 305 # if 'orm only', look for ORM annotations 306 # in the element before adapting. 307 if not _orm_only or \ 308 '_orm_adapt' in elem._annotations or \ 309 "parententity" in elem._annotations: 310 311 e = adapter(elem) 312 if e is not None: 313 return e 314 315 return visitors.replacement_traverse( 316 clause, 317 {}, 318 replace 319 ) 320 321 def _query_entity_zero(self): 322 """Return the first QueryEntity.""" 323 return self._entities[0] 324 325 def _mapper_zero(self): 326 """return the Mapper associated with the first QueryEntity.""" 327 return self._entities[0].mapper 328 329 def _entity_zero(self): 330 """Return the 'entity' (mapper or AliasedClass) associated 331 with the first QueryEntity, or alternatively the 'select from' 332 entity if specified.""" 333 334 return self._select_from_entity \ 335 if self._select_from_entity is not None \ 336 else self._query_entity_zero().entity_zero 337 338 @property 339 def _mapper_entities(self): 340 for ent in self._entities: 341 if isinstance(ent, _MapperEntity): 342 yield ent 343 344 def _joinpoint_zero(self): 345 return self._joinpoint.get( 346 '_joinpoint_entity', 347 self._entity_zero() 348 ) 349 350 def _bind_mapper(self): 351 ezero = self._entity_zero() 352 if ezero is not None: 353 insp = inspect(ezero) 354 if not insp.is_clause_element: 355 return insp.mapper 356 357 return None 358 359 def _only_full_mapper_zero(self, methname): 360 if self._entities != [self._primary_entity]: 361 raise sa_exc.InvalidRequestError( 362 "%s() can only be used against " 363 "a single mapped class." % methname) 364 return self._primary_entity.entity_zero 365 366 def _only_entity_zero(self, rationale=None): 367 if len(self._entities) > 1: 368 raise sa_exc.InvalidRequestError( 369 rationale or 370 "This operation requires a Query " 371 "against a single mapper." 372 ) 373 return self._entity_zero() 374 375 def __all_equivs(self): 376 equivs = {} 377 for ent in self._mapper_entities: 378 equivs.update(ent.mapper._equivalent_columns) 379 return equivs 380 381 def _get_condition(self): 382 return self._no_criterion_condition( 383 "get", order_by=False, distinct=False) 384 385 def _get_existing_condition(self): 386 self._no_criterion_assertion("get", order_by=False, distinct=False) 387 388 def _no_criterion_assertion(self, meth, order_by=True, distinct=True): 389 if not self._enable_assertions: 390 return 391 if self._criterion is not None or \ 392 self._statement is not None or self._from_obj or \ 393 self._limit is not None or self._offset is not None or \ 394 self._group_by or (order_by and self._order_by) or \ 395 (distinct and self._distinct): 396 raise sa_exc.InvalidRequestError( 397 "Query.%s() being called on a " 398 "Query with existing criterion. " % meth) 399 400 def _no_criterion_condition(self, meth, order_by=True, distinct=True): 401 self._no_criterion_assertion(meth, order_by, distinct) 402 403 self._from_obj = () 404 self._statement = self._criterion = None 405 self._order_by = self._group_by = self._distinct = False 406 407 def _no_clauseelement_condition(self, meth): 408 if not self._enable_assertions: 409 return 410 if self._order_by: 411 raise sa_exc.InvalidRequestError( 412 "Query.%s() being called on a " 413 "Query with existing criterion. " % meth) 414 self._no_criterion_condition(meth) 415 416 def _no_statement_condition(self, meth): 417 if not self._enable_assertions: 418 return 419 if self._statement is not None: 420 raise sa_exc.InvalidRequestError( 421 ("Query.%s() being called on a Query with an existing full " 422 "statement - can't apply criterion.") % meth) 423 424 def _no_limit_offset(self, meth): 425 if not self._enable_assertions: 426 return 427 if self._limit is not None or self._offset is not None: 428 raise sa_exc.InvalidRequestError( 429 "Query.%s() being called on a Query which already has LIMIT " 430 "or OFFSET applied. To modify the row-limited results of a " 431 " Query, call from_self() first. " 432 "Otherwise, call %s() before limit() or offset() " 433 "are applied." 434 % (meth, meth) 435 ) 436 437 def _get_options(self, populate_existing=None, 438 version_check=None, 439 only_load_props=None, 440 refresh_state=None): 441 if populate_existing: 442 self._populate_existing = populate_existing 443 if version_check: 444 self._version_check = version_check 445 if refresh_state: 446 self._refresh_state = refresh_state 447 if only_load_props: 448 self._only_load_props = set(only_load_props) 449 return self 450 451 def _clone(self): 452 cls = self.__class__ 453 q = cls.__new__(cls) 454 q.__dict__ = self.__dict__.copy() 455 return q 456 457 @property 458 def statement(self): 459 """The full SELECT statement represented by this Query. 460 461 The statement by default will not have disambiguating labels 462 applied to the construct unless with_labels(True) is called 463 first. 464 465 """ 466 467 stmt = self._compile_context(labels=self._with_labels).\ 468 statement 469 if self._params: 470 stmt = stmt.params(self._params) 471 472 # TODO: there's no tests covering effects of 473 # the annotation not being there 474 return stmt._annotate({'no_replacement_traverse': True}) 475 476 def subquery(self, name=None, with_labels=False, reduce_columns=False): 477 """return the full SELECT statement represented by 478 this :class:`.Query`, embedded within an :class:`.Alias`. 479 480 Eager JOIN generation within the query is disabled. 481 482 :param name: string name to be assigned as the alias; 483 this is passed through to :meth:`.FromClause.alias`. 484 If ``None``, a name will be deterministically generated 485 at compile time. 486 487 :param with_labels: if True, :meth:`.with_labels` will be called 488 on the :class:`.Query` first to apply table-qualified labels 489 to all columns. 490 491 :param reduce_columns: if True, :meth:`.Select.reduce_columns` will 492 be called on the resulting :func:`.select` construct, 493 to remove same-named columns where one also refers to the other 494 via foreign key or WHERE clause equivalence. 495 496 .. versionchanged:: 0.8 the ``with_labels`` and ``reduce_columns`` 497 keyword arguments were added. 498 499 """ 500 q = self.enable_eagerloads(False) 501 if with_labels: 502 q = q.with_labels() 503 q = q.statement 504 if reduce_columns: 505 q = q.reduce_columns() 506 return q.alias(name=name) 507 508 def cte(self, name=None, recursive=False): 509 r"""Return the full SELECT statement represented by this 510 :class:`.Query` represented as a common table expression (CTE). 511 512 Parameters and usage are the same as those of the 513 :meth:`.SelectBase.cte` method; see that method for 514 further details. 515 516 Here is the `PostgreSQL WITH 517 RECURSIVE example 518 <http://www.postgresql.org/docs/8.4/static/queries-with.html>`_. 519 Note that, in this example, the ``included_parts`` cte and the 520 ``incl_alias`` alias of it are Core selectables, which 521 means the columns are accessed via the ``.c.`` attribute. The 522 ``parts_alias`` object is an :func:`.orm.aliased` instance of the 523 ``Part`` entity, so column-mapped attributes are available 524 directly:: 525 526 from sqlalchemy.orm import aliased 527 528 class Part(Base): 529 __tablename__ = 'part' 530 part = Column(String, primary_key=True) 531 sub_part = Column(String, primary_key=True) 532 quantity = Column(Integer) 533 534 included_parts = session.query( 535 Part.sub_part, 536 Part.part, 537 Part.quantity).\ 538 filter(Part.part=="our part").\ 539 cte(name="included_parts", recursive=True) 540 541 incl_alias = aliased(included_parts, name="pr") 542 parts_alias = aliased(Part, name="p") 543 included_parts = included_parts.union_all( 544 session.query( 545 parts_alias.sub_part, 546 parts_alias.part, 547 parts_alias.quantity).\ 548 filter(parts_alias.part==incl_alias.c.sub_part) 549 ) 550 551 q = session.query( 552 included_parts.c.sub_part, 553 func.sum(included_parts.c.quantity). 554 label('total_quantity') 555 ).\ 556 group_by(included_parts.c.sub_part) 557 558 .. seealso:: 559 560 :meth:`.HasCTE.cte` 561 562 """ 563 return self.enable_eagerloads(False).\ 564 statement.cte(name=name, recursive=recursive) 565 566 def label(self, name): 567 """Return the full SELECT statement represented by this 568 :class:`.Query`, converted 569 to a scalar subquery with a label of the given name. 570 571 Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.label`. 572 573 .. versionadded:: 0.6.5 574 575 """ 576 577 return self.enable_eagerloads(False).statement.label(name) 578 579 def as_scalar(self): 580 """Return the full SELECT statement represented by this 581 :class:`.Query`, converted to a scalar subquery. 582 583 Analogous to :meth:`sqlalchemy.sql.expression.SelectBase.as_scalar`. 584 585 .. versionadded:: 0.6.5 586 587 """ 588 589 return self.enable_eagerloads(False).statement.as_scalar() 590 591 @property 592 def selectable(self): 593 """Return the :class:`.Select` object emitted by this :class:`.Query`. 594 595 Used for :func:`.inspect` compatibility, this is equivalent to:: 596 597 query.enable_eagerloads(False).with_labels().statement 598 599 """ 600 return self.__clause_element__() 601 602 def __clause_element__(self): 603 return self.enable_eagerloads(False).with_labels().statement 604 605 @_generative() 606 def enable_eagerloads(self, value): 607 """Control whether or not eager joins and subqueries are 608 rendered. 609 610 When set to False, the returned Query will not render 611 eager joins regardless of :func:`~sqlalchemy.orm.joinedload`, 612 :func:`~sqlalchemy.orm.subqueryload` options 613 or mapper-level ``lazy='joined'``/``lazy='subquery'`` 614 configurations. 615 616 This is used primarily when nesting the Query's 617 statement into a subquery or other 618 selectable, or when using :meth:`.Query.yield_per`. 619 620 """ 621 self._enable_eagerloads = value 622 623 def _no_yield_per(self, message): 624 raise sa_exc.InvalidRequestError( 625 "The yield_per Query option is currently not " 626 "compatible with %s eager loading. Please " 627 "specify lazyload('*') or query.enable_eagerloads(False) in " 628 "order to " 629 "proceed with query.yield_per()." % message) 630 631 @_generative() 632 def with_labels(self): 633 """Apply column labels to the return value of Query.statement. 634 635 Indicates that this Query's `statement` accessor should return 636 a SELECT statement that applies labels to all columns in the 637 form <tablename>_<columnname>; this is commonly used to 638 disambiguate columns from multiple tables which have the same 639 name. 640 641 When the `Query` actually issues SQL to load rows, it always 642 uses column labeling. 643 644 .. note:: The :meth:`.Query.with_labels` method *only* applies 645 the output of :attr:`.Query.statement`, and *not* to any of 646 the result-row invoking systems of :class:`.Query` itself, e.g. 647 :meth:`.Query.first`, :meth:`.Query.all`, etc. To execute 648 a query using :meth:`.Query.with_labels`, invoke the 649 :attr:`.Query.statement` using :meth:`.Session.execute`:: 650 651 result = session.execute(query.with_labels().statement) 652 653 654 """ 655 self._with_labels = True 656 657 @_generative() 658 def enable_assertions(self, value): 659 """Control whether assertions are generated. 660 661 When set to False, the returned Query will 662 not assert its state before certain operations, 663 including that LIMIT/OFFSET has not been applied 664 when filter() is called, no criterion exists 665 when get() is called, and no "from_statement()" 666 exists when filter()/order_by()/group_by() etc. 667 is called. This more permissive mode is used by 668 custom Query subclasses to specify criterion or 669 other modifiers outside of the usual usage patterns. 670 671 Care should be taken to ensure that the usage 672 pattern is even possible. A statement applied 673 by from_statement() will override any criterion 674 set by filter() or order_by(), for example. 675 676 """ 677 self._enable_assertions = value 678 679 @property 680 def whereclause(self): 681 """A readonly attribute which returns the current WHERE criterion for 682 this Query. 683 684 This returned value is a SQL expression construct, or ``None`` if no 685 criterion has been established. 686 687 """ 688 return self._criterion 689 690 @_generative() 691 def _with_current_path(self, path): 692 """indicate that this query applies to objects loaded 693 within a certain path. 694 695 Used by deferred loaders (see strategies.py) which transfer 696 query options from an originating query to a newly generated 697 query intended for the deferred load. 698 699 """ 700 self._current_path = path 701 702 @_generative(_no_clauseelement_condition) 703 def with_polymorphic(self, 704 cls_or_mappers, 705 selectable=None, 706 polymorphic_on=None): 707 """Load columns for inheriting classes. 708 709 :meth:`.Query.with_polymorphic` applies transformations 710 to the "main" mapped class represented by this :class:`.Query`. 711 The "main" mapped class here means the :class:`.Query` 712 object's first argument is a full class, i.e. 713 ``session.query(SomeClass)``. These transformations allow additional 714 tables to be present in the FROM clause so that columns for a 715 joined-inheritance subclass are available in the query, both for the 716 purposes of load-time efficiency as well as the ability to use 717 these columns at query time. 718 719 See the documentation section :ref:`with_polymorphic` for 720 details on how this method is used. 721 722 .. versionchanged:: 0.8 723 A new and more flexible function 724 :func:`.orm.with_polymorphic` supersedes 725 :meth:`.Query.with_polymorphic`, as it can apply the equivalent 726 functionality to any set of columns or classes in the 727 :class:`.Query`, not just the "zero mapper". See that 728 function for a description of arguments. 729 730 """ 731 732 if not self._primary_entity: 733 raise sa_exc.InvalidRequestError( 734 "No primary mapper set up for this Query.") 735 entity = self._entities[0]._clone() 736 self._entities = [entity] + self._entities[1:] 737 entity.set_with_polymorphic(self, 738 cls_or_mappers, 739 selectable=selectable, 740 polymorphic_on=polymorphic_on) 741 742 @_generative() 743 def yield_per(self, count): 744 r"""Yield only ``count`` rows at a time. 745 746 The purpose of this method is when fetching very large result sets 747 (> 10K rows), to batch results in sub-collections and yield them 748 out partially, so that the Python interpreter doesn't need to declare 749 very large areas of memory which is both time consuming and leads 750 to excessive memory use. The performance from fetching hundreds of 751 thousands of rows can often double when a suitable yield-per setting 752 (e.g. approximately 1000) is used, even with DBAPIs that buffer 753 rows (which are most). 754 755 The :meth:`.Query.yield_per` method **is not compatible with most 756 eager loading schemes, including subqueryload and joinedload with 757 collections**. For this reason, it may be helpful to disable 758 eager loads, either unconditionally with 759 :meth:`.Query.enable_eagerloads`:: 760 761 q = sess.query(Object).yield_per(100).enable_eagerloads(False) 762 763 Or more selectively using :func:`.lazyload`; such as with 764 an asterisk to specify the default loader scheme:: 765 766 q = sess.query(Object).yield_per(100).\ 767 options(lazyload('*'), joinedload(Object.some_related)) 768 769 .. warning:: 770 771 Use this method with caution; if the same instance is 772 present in more than one batch of rows, end-user changes 773 to attributes will be overwritten. 774 775 In particular, it's usually impossible to use this setting 776 with eagerly loaded collections (i.e. any lazy='joined' or 777 'subquery') since those collections will be cleared for a 778 new load when encountered in a subsequent result batch. 779 In the case of 'subquery' loading, the full result for all 780 rows is fetched which generally defeats the purpose of 781 :meth:`~sqlalchemy.orm.query.Query.yield_per`. 782 783 Also note that while 784 :meth:`~sqlalchemy.orm.query.Query.yield_per` will set the 785 ``stream_results`` execution option to True, currently 786 this is only understood by 787 :mod:`~sqlalchemy.dialects.postgresql.psycopg2`, 788 :mod:`~sqlalchemy.dialects.mysql.mysqldb` and 789 :mod:`~sqlalchemy.dialects.mysql.pymysql` dialects 790 which will stream results using server side cursors 791 instead of pre-buffer all rows for this query. Other 792 DBAPIs **pre-buffer all rows** before making them 793 available. The memory use of raw database rows is much less 794 than that of an ORM-mapped object, but should still be taken into 795 consideration when benchmarking. 796 797 .. seealso:: 798 799 :meth:`.Query.enable_eagerloads` 800 801 """ 802 self._yield_per = count 803 self._execution_options = self._execution_options.union( 804 {"stream_results": True, 805 "max_row_buffer": count}) 806 807 def get(self, ident): 808 """Return an instance based on the given primary key identifier, 809 or ``None`` if not found. 810 811 E.g.:: 812 813 my_user = session.query(User).get(5) 814 815 some_object = session.query(VersionedFoo).get((5, 10)) 816 817 :meth:`~.Query.get` is special in that it provides direct 818 access to the identity map of the owning :class:`.Session`. 819 If the given primary key identifier is present 820 in the local identity map, the object is returned 821 directly from this collection and no SQL is emitted, 822 unless the object has been marked fully expired. 823 If not present, 824 a SELECT is performed in order to locate the object. 825 826 :meth:`~.Query.get` also will perform a check if 827 the object is present in the identity map and 828 marked as expired - a SELECT 829 is emitted to refresh the object as well as to 830 ensure that the row is still present. 831 If not, :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is raised. 832 833 :meth:`~.Query.get` is only used to return a single 834 mapped instance, not multiple instances or 835 individual column constructs, and strictly 836 on a single primary key value. The originating 837 :class:`.Query` must be constructed in this way, 838 i.e. against a single mapped entity, 839 with no additional filtering criterion. Loading 840 options via :meth:`~.Query.options` may be applied 841 however, and will be used if the object is not 842 yet locally present. 843 844 A lazy-loading, many-to-one attribute configured 845 by :func:`.relationship`, using a simple 846 foreign-key-to-primary-key criterion, will also use an 847 operation equivalent to :meth:`~.Query.get` in order to retrieve 848 the target value from the local identity map 849 before querying the database. See :doc:`/orm/loading_relationships` 850 for further details on relationship loading. 851 852 :param ident: A scalar or tuple value representing 853 the primary key. For a composite primary key, 854 the order of identifiers corresponds in most cases 855 to that of the mapped :class:`.Table` object's 856 primary key columns. For a :func:`.mapper` that 857 was given the ``primary key`` argument during 858 construction, the order of identifiers corresponds 859 to the elements present in this collection. 860 861 :return: The object instance, or ``None``. 862 863 """ 864 return self._get_impl(ident, loading.load_on_ident) 865 866 def _get_impl(self, ident, fallback_fn): 867 # convert composite types to individual args 868 if hasattr(ident, '__composite_values__'): 869 ident = ident.__composite_values__() 870 871 ident = util.to_list(ident) 872 873 mapper = self._only_full_mapper_zero("get") 874 875 if len(ident) != len(mapper.primary_key): 876 raise sa_exc.InvalidRequestError( 877 "Incorrect number of values in identifier to formulate " 878 "primary key for query.get(); primary key columns are %s" % 879 ','.join("'%s'" % c for c in mapper.primary_key)) 880 881 key = mapper.identity_key_from_primary_key(ident) 882 883 if not self._populate_existing and \ 884 not mapper.always_refresh and \ 885 self._for_update_arg is None: 886 887 instance = loading.get_from_identity( 888 self.session, key, attributes.PASSIVE_OFF) 889 if instance is not None: 890 self._get_existing_condition() 891 # reject calls for id in identity map but class 892 # mismatch. 893 if not issubclass(instance.__class__, mapper.class_): 894 return None 895 return instance 896 897 return fallback_fn(self, key) 898 899 @_generative() 900 def correlate(self, *args): 901 """Return a :class:`.Query` construct which will correlate the given 902 FROM clauses to that of an enclosing :class:`.Query` or 903 :func:`~.expression.select`. 904 905 The method here accepts mapped classes, :func:`.aliased` constructs, 906 and :func:`.mapper` constructs as arguments, which are resolved into 907 expression constructs, in addition to appropriate expression 908 constructs. 909 910 The correlation arguments are ultimately passed to 911 :meth:`.Select.correlate` after coercion to expression constructs. 912 913 The correlation arguments take effect in such cases 914 as when :meth:`.Query.from_self` is used, or when 915 a subquery as returned by :meth:`.Query.subquery` is 916 embedded in another :func:`~.expression.select` construct. 917 918 """ 919 920 for s in args: 921 if s is None: 922 self._correlate = self._correlate.union([None]) 923 else: 924 self._correlate = self._correlate.union( 925 sql_util.surface_selectables(_interpret_as_from(s)) 926 ) 927 928 @_generative() 929 def autoflush(self, setting): 930 """Return a Query with a specific 'autoflush' setting. 931 932 Note that a Session with autoflush=False will 933 not autoflush, even if this flag is set to True at the 934 Query level. Therefore this flag is usually used only 935 to disable autoflush for a specific Query. 936 937 """ 938 self._autoflush = setting 939 940 @_generative() 941 def populate_existing(self): 942 """Return a :class:`.Query` that will expire and refresh all instances 943 as they are loaded, or reused from the current :class:`.Session`. 944 945 :meth:`.populate_existing` does not improve behavior when 946 the ORM is used normally - the :class:`.Session` object's usual 947 behavior of maintaining a transaction and expiring all attributes 948 after rollback or commit handles object state automatically. 949 This method is not intended for general use. 950 951 """ 952 self._populate_existing = True 953 954 @_generative() 955 def _with_invoke_all_eagers(self, value): 956 """Set the 'invoke all eagers' flag which causes joined- and 957 subquery loaders to traverse into already-loaded related objects 958 and collections. 959 960 Default is that of :attr:`.Query._invoke_all_eagers`. 961 962 """ 963 self._invoke_all_eagers = value 964 965 def with_parent(self, instance, property=None): 966 """Add filtering criterion that relates the given instance 967 to a child object or collection, using its attribute state 968 as well as an established :func:`.relationship()` 969 configuration. 970 971 The method uses the :func:`.with_parent` function to generate 972 the clause, the result of which is passed to :meth:`.Query.filter`. 973 974 Parameters are the same as :func:`.with_parent`, with the exception 975 that the given property can be None, in which case a search is 976 performed against this :class:`.Query` object's target mapper. 977 978 """ 979 980 if property is None: 981 mapper_zero = self._mapper_zero() 982 983 mapper = object_mapper(instance) 984 985 for prop in mapper.iterate_properties: 986 if isinstance(prop, properties.RelationshipProperty) and \ 987 prop.mapper is mapper_zero: 988 property = prop 989 break 990 else: 991 raise sa_exc.InvalidRequestError( 992 "Could not locate a property which relates instances " 993 "of class '%s' to instances of class '%s'" % 994 ( 995 self._mapper_zero().class_.__name__, 996 instance.__class__.__name__) 997 ) 998 999 return self.filter(with_parent(instance, property)) 1000 1001 @_generative() 1002 def add_entity(self, entity, alias=None): 1003 """add a mapped entity to the list of result columns 1004 to be returned.""" 1005 1006 if alias is not None: 1007 entity = aliased(entity, alias) 1008 1009 self._entities = list(self._entities) 1010 m = _MapperEntity(self, entity) 1011 self._set_entity_selectables([m]) 1012 1013 @_generative() 1014 def with_session(self, session): 1015 """Return a :class:`.Query` that will use the given :class:`.Session`. 1016 1017 While the :class:`.Query` object is normally instantiated using the 1018 :meth:`.Session.query` method, it is legal to build the :class:`.Query` 1019 directly without necessarily using a :class:`.Session`. Such a 1020 :class:`.Query` object, or any :class:`.Query` already associated 1021 with a different :class:`.Session`, can produce a new :class:`.Query` 1022 object associated with a target session using this method:: 1023 1024 from sqlalchemy.orm import Query 1025 1026 query = Query([MyClass]).filter(MyClass.id == 5) 1027 1028 result = query.with_session(my_session).one() 1029 1030 """ 1031 1032 self.session = session 1033 1034 def from_self(self, *entities): 1035 r"""return a Query that selects from this Query's 1036 SELECT statement. 1037 1038 :meth:`.Query.from_self` essentially turns the SELECT statement 1039 into a SELECT of itself. Given a query such as:: 1040 1041 q = session.query(User).filter(User.name.like('e%')) 1042 1043 Given the :meth:`.Query.from_self` version:: 1044 1045 q = session.query(User).filter(User.name.like('e%')).from_self() 1046 1047 This query renders as: 1048 1049 .. sourcecode:: sql 1050 1051 SELECT anon_1.user_id AS anon_1_user_id, 1052 anon_1.user_name AS anon_1_user_name 1053 FROM (SELECT "user".id AS user_id, "user".name AS user_name 1054 FROM "user" 1055 WHERE "user".name LIKE :name_1) AS anon_1 1056 1057 There are lots of cases where :meth:`.Query.from_self` may be useful. 1058 A simple one is where above, we may want to apply a row LIMIT to 1059 the set of user objects we query against, and then apply additional 1060 joins against that row-limited set:: 1061 1062 q = session.query(User).filter(User.name.like('e%')).\ 1063 limit(5).from_self().\ 1064 join(User.addresses).filter(Address.email.like('q%')) 1065 1066 The above query joins to the ``Address`` entity but only against the 1067 first five results of the ``User`` query: 1068 1069 .. sourcecode:: sql 1070 1071 SELECT anon_1.user_id AS anon_1_user_id, 1072 anon_1.user_name AS anon_1_user_name 1073 FROM (SELECT "user".id AS user_id, "user".name AS user_name 1074 FROM "user" 1075 WHERE "user".name LIKE :name_1 1076 LIMIT :param_1) AS anon_1 1077 JOIN address ON anon_1.user_id = address.user_id 1078 WHERE address.email LIKE :email_1 1079 1080 **Automatic Aliasing** 1081 1082 Another key behavior of :meth:`.Query.from_self` is that it applies 1083 **automatic aliasing** to the entities inside the subquery, when 1084 they are referenced on the outside. Above, if we continue to 1085 refer to the ``User`` entity without any additional aliasing applied 1086 to it, those references wil be in terms of the subquery:: 1087 1088 q = session.query(User).filter(User.name.like('e%')).\ 1089 limit(5).from_self().\ 1090 join(User.addresses).filter(Address.email.like('q%')).\ 1091 order_by(User.name) 1092 1093 The ORDER BY against ``User.name`` is aliased to be in terms of the 1094 inner subquery: 1095 1096 .. sourcecode:: sql 1097 1098 SELECT anon_1.user_id AS anon_1_user_id, 1099 anon_1.user_name AS anon_1_user_name 1100 FROM (SELECT "user".id AS user_id, "user".name AS user_name 1101 FROM "user" 1102 WHERE "user".name LIKE :name_1 1103 LIMIT :param_1) AS anon_1 1104 JOIN address ON anon_1.user_id = address.user_id 1105 WHERE address.email LIKE :email_1 ORDER BY anon_1.user_name 1106 1107 The automatic aliasing feature only works in a **limited** way, 1108 for simple filters and orderings. More ambitious constructions 1109 such as referring to the entity in joins should prefer to use 1110 explicit subquery objects, typically making use of the 1111 :meth:`.Query.subquery` method to produce an explicit subquery object. 1112 Always test the structure of queries by viewing the SQL to ensure 1113 a particular structure does what's expected! 1114 1115 **Changing the Entities** 1116 1117 :meth:`.Query.from_self` also includes the ability to modify what 1118 columns are being queried. In our example, we want ``User.id`` 1119 to be queried by the inner query, so that we can join to the 1120 ``Address`` entity on the outside, but we only wanted the outer 1121 query to return the ``Address.email`` column:: 1122 1123 q = session.query(User).filter(User.name.like('e%')).\ 1124 limit(5).from_self(Address.email).\ 1125 join(User.addresses).filter(Address.email.like('q%')) 1126 1127 yielding: 1128 1129 .. sourcecode:: sql 1130 1131 SELECT address.email AS address_email 1132 FROM (SELECT "user".id AS user_id, "user".name AS user_name 1133 FROM "user" 1134 WHERE "user".name LIKE :name_1 1135 LIMIT :param_1) AS anon_1 1136 JOIN address ON anon_1.user_id = address.user_id 1137 WHERE address.email LIKE :email_1 1138 1139 **Looking out for Inner / Outer Columns** 1140 1141 Keep in mind that when referring to columns that originate from 1142 inside the subquery, we need to ensure they are present in the 1143 columns clause of the subquery itself; this is an ordinary aspect of 1144 SQL. For example, if we wanted to load from a joined entity inside 1145 the subquery using :func:`.contains_eager`, we need to add those 1146 columns. Below illustrates a join of ``Address`` to ``User``, 1147 then a subquery, and then we'd like :func:`.contains_eager` to access 1148 the ``User`` columns:: 1149 1150 q = session.query(Address).join(Address.user).\ 1151 filter(User.name.like('e%')) 1152 1153 q = q.add_entity(User).from_self().\ 1154 options(contains_eager(Address.user)) 1155 1156 We use :meth:`.Query.add_entity` above **before** we call 1157 :meth:`.Query.from_self` so that the ``User`` columns are present 1158 in the inner subquery, so that they are available to the 1159 :func:`.contains_eager` modifier we are using on the outside, 1160 producing: 1161 1162 .. sourcecode:: sql 1163 1164 SELECT anon_1.address_id AS anon_1_address_id, 1165 anon_1.address_email AS anon_1_address_email, 1166 anon_1.address_user_id AS anon_1_address_user_id, 1167 anon_1.user_id AS anon_1_user_id, 1168 anon_1.user_name AS anon_1_user_name 1169 FROM ( 1170 SELECT address.id AS address_id, 1171 address.email AS address_email, 1172 address.user_id AS address_user_id, 1173 "user".id AS user_id, 1174 "user".name AS user_name 1175 FROM address JOIN "user" ON "user".id = address.user_id 1176 WHERE "user".name LIKE :name_1) AS anon_1 1177 1178 If we didn't call ``add_entity(User)``, but still asked 1179 :func:`.contains_eager` to load the ``User`` entity, it would be 1180 forced to add the table on the outside without the correct 1181 join criteria - note the ``anon1, "user"`` phrase at 1182 the end: 1183 1184 .. sourcecode:: sql 1185 1186 -- incorrect query 1187 SELECT anon_1.address_id AS anon_1_address_id, 1188 anon_1.address_email AS anon_1_address_email, 1189 anon_1.address_user_id AS anon_1_address_user_id, 1190 "user".id AS user_id, 1191 "user".name AS user_name 1192 FROM ( 1193 SELECT address.id AS address_id, 1194 address.email AS address_email, 1195 address.user_id AS address_user_id 1196 FROM address JOIN "user" ON "user".id = address.user_id 1197 WHERE "user".name LIKE :name_1) AS anon_1, "user" 1198 1199 :param \*entities: optional list of entities which will replace 1200 those being selected. 1201 1202 """ 1203 fromclause = self.with_labels().enable_eagerloads(False).\ 1204 statement.correlate(None) 1205 q = self._from_selectable(fromclause) 1206 q._enable_single_crit = False 1207 q._select_from_entity = self._entity_zero() 1208 if entities: 1209 q._set_entities(entities) 1210 return q 1211 1212 @_generative() 1213 def _set_enable_single_crit(self, val): 1214 self._enable_single_crit = val 1215 1216 @_generative() 1217 def _from_selectable(self, fromclause): 1218 for attr in ( 1219 '_statement', '_criterion', 1220 '_order_by', '_group_by', 1221 '_limit', '_offset', 1222 '_joinpath', '_joinpoint', 1223 '_distinct', '_having', 1224 '_prefixes', '_suffixes' 1225 ): 1226 self.__dict__.pop(attr, None) 1227 self._set_select_from([fromclause], True) 1228 1229 # this enables clause adaptation for non-ORM 1230 # expressions. 1231 self._orm_only_from_obj_alias = False 1232 1233 old_entities = self._entities 1234 self._entities = [] 1235 for e in old_entities: 1236 e.adapt_to_selectable(self, self._from_obj[0]) 1237 1238 def values(self, *columns): 1239 """Return an iterator yielding result tuples corresponding 1240 to the given list of columns""" 1241 1242 if not columns: 1243 return iter(()) 1244 q = self._clone() 1245 q._set_entities(columns, entity_wrapper=_ColumnEntity) 1246 if not q._yield_per: 1247 q._yield_per = 10 1248 return iter(q) 1249 _values = values 1250 1251 def value(self, column): 1252 """Return a scalar result corresponding to the given 1253 column expression.""" 1254 try: 1255 return next(self.values(column))[0] 1256 except StopIteration: 1257 return None 1258 1259 @_generative() 1260 def with_entities(self, *entities): 1261 r"""Return a new :class:`.Query` replacing the SELECT list with the 1262 given entities. 1263 1264 e.g.:: 1265 1266 # Users, filtered on some arbitrary criterion 1267 # and then ordered by related email address 1268 q = session.query(User).\ 1269 join(User.address).\ 1270 filter(User.name.like('%ed%')).\ 1271 order_by(Address.email) 1272 1273 # given *only* User.id==5, Address.email, and 'q', what 1274 # would the *next* User in the result be ? 1275 subq = q.with_entities(Address.email).\ 1276 order_by(None).\ 1277 filter(User.id==5).\ 1278 subquery() 1279 q = q.join((subq, subq.c.email < Address.email)).\ 1280 limit(1) 1281 1282 .. versionadded:: 0.6.5 1283 1284 """ 1285 self._set_entities(entities) 1286 1287 @_generative() 1288 def add_columns(self, *column): 1289 """Add one or more column expressions to the list 1290 of result columns to be returned.""" 1291 1292 self._entities = list(self._entities) 1293 l = len(self._entities) 1294 for c in column: 1295 _ColumnEntity(self, c) 1296 # _ColumnEntity may add many entities if the 1297 # given arg is a FROM clause 1298 self._set_entity_selectables(self._entities[l:]) 1299 1300 @util.pending_deprecation("0.7", 1301 ":meth:`.add_column` is superseded " 1302 "by :meth:`.add_columns`", 1303 False) 1304 def add_column(self, column): 1305 """Add a column expression to the list of result columns to be 1306 returned. 1307 1308 Pending deprecation: :meth:`.add_column` will be superseded by 1309 :meth:`.add_columns`. 1310 1311 """ 1312 return self.add_columns(column) 1313 1314 def options(self, *args): 1315 """Return a new Query object, applying the given list of 1316 mapper options. 1317 1318 Most supplied options regard changing how column- and 1319 relationship-mapped attributes are loaded. See the sections 1320 :ref:`deferred` and :doc:`/orm/loading_relationships` for reference 1321 documentation. 1322 1323 """ 1324 return self._options(False, *args) 1325 1326 def _conditional_options(self, *args): 1327 return self._options(True, *args) 1328 1329 @_generative() 1330 def _options(self, conditional, *args): 1331 # most MapperOptions write to the '_attributes' dictionary, 1332 # so copy that as well 1333 self._attributes = self._attributes.copy() 1334 opts = tuple(util.flatten_iterator(args)) 1335 self._with_options = self._with_options + opts 1336 if conditional: 1337 for opt in opts: 1338 opt.process_query_conditionally(self) 1339 else: 1340 for opt in opts: 1341 opt.process_query(self) 1342 1343 def with_transformation(self, fn): 1344 """Return a new :class:`.Query` object transformed by 1345 the given function. 1346 1347 E.g.:: 1348 1349 def filter_something(criterion): 1350 def transform(q): 1351 return q.filter(criterion) 1352 return transform 1353 1354 q = q.with_transformation(filter_something(x==5)) 1355 1356 This allows ad-hoc recipes to be created for :class:`.Query` 1357 objects. See the example at :ref:`hybrid_transformers`. 1358 1359 .. versionadded:: 0.7.4 1360 1361 """ 1362 return fn(self) 1363 1364 @_generative() 1365 def with_hint(self, selectable, text, dialect_name='*'): 1366 """Add an indexing or other executional context 1367 hint for the given entity or selectable to 1368 this :class:`.Query`. 1369 1370 Functionality is passed straight through to 1371 :meth:`~sqlalchemy.sql.expression.Select.with_hint`, 1372 with the addition that ``selectable`` can be a 1373 :class:`.Table`, :class:`.Alias`, or ORM entity / mapped class 1374 /etc. 1375 1376 .. seealso:: 1377 1378 :meth:`.Query.with_statement_hint` 1379 1380 """ 1381 if selectable is not None: 1382 selectable = inspect(selectable).selectable 1383 1384 self._with_hints += ((selectable, text, dialect_name),) 1385 1386 def with_statement_hint(self, text, dialect_name='*'): 1387 """add a statement hint to this :class:`.Select`. 1388 1389 This method is similar to :meth:`.Select.with_hint` except that 1390 it does not require an individual table, and instead applies to the 1391 statement as a whole. 1392 1393 This feature calls down into :meth:`.Select.with_statement_hint`. 1394 1395 .. versionadded:: 1.0.0 1396 1397 .. seealso:: 1398 1399 :meth:`.Query.with_hint` 1400 1401 """ 1402 return self.with_hint(None, text, dialect_name) 1403 1404 @_generative() 1405 def execution_options(self, **kwargs): 1406 """ Set non-SQL options which take effect during execution. 1407 1408 The options are the same as those accepted by 1409 :meth:`.Connection.execution_options`. 1410 1411 Note that the ``stream_results`` execution option is enabled 1412 automatically if the :meth:`~sqlalchemy.orm.query.Query.yield_per()` 1413 method is used. 1414 1415 """ 1416 self._execution_options = self._execution_options.union(kwargs) 1417 1418 @_generative() 1419 def with_lockmode(self, mode): 1420 """Return a new :class:`.Query` object with the specified "locking mode", 1421 which essentially refers to the ``FOR UPDATE`` clause. 1422 1423 .. deprecated:: 0.9.0 superseded by :meth:`.Query.with_for_update`. 1424 1425 :param mode: a string representing the desired locking mode. 1426 Valid values are: 1427 1428 * ``None`` - translates to no lockmode 1429 1430 * ``'update'`` - translates to ``FOR UPDATE`` 1431 (standard SQL, supported by most dialects) 1432 1433 * ``'update_nowait'`` - translates to ``FOR UPDATE NOWAIT`` 1434 (supported by Oracle, PostgreSQL 8.1 upwards) 1435 1436 * ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL), 1437 and ``FOR SHARE`` (for PostgreSQL) 1438 1439 .. seealso:: 1440 1441 :meth:`.Query.with_for_update` - improved API for 1442 specifying the ``FOR UPDATE`` clause. 1443 1444 """ 1445 self._for_update_arg = LockmodeArg.parse_legacy_query(mode) 1446 1447 @_generative() 1448 def with_for_update(self, read=False, nowait=False, of=None, 1449 skip_locked=False, key_share=False): 1450 """return a new :class:`.Query` with the specified options for the 1451 ``FOR UPDATE`` clause. 1452 1453 The behavior of this method is identical to that of 1454 :meth:`.SelectBase.with_for_update`. When called with no arguments, 1455 the resulting ``SELECT`` statement will have a ``FOR UPDATE`` clause 1456 appended. When additional arguments are specified, backend-specific 1457 options such as ``FOR UPDATE NOWAIT`` or ``LOCK IN SHARE MODE`` 1458 can take effect. 1459 1460 E.g.:: 1461 1462 q = sess.query(User).with_for_update(nowait=True, of=User) 1463 1464 The above query on a PostgreSQL backend will render like:: 1465 1466 SELECT users.id AS users_id FROM users FOR UPDATE OF users NOWAIT 1467 1468 .. versionadded:: 0.9.0 :meth:`.Query.with_for_update` supersedes 1469 the :meth:`.Query.with_lockmode` method. 1470 1471 .. seealso:: 1472 1473 :meth:`.GenerativeSelect.with_for_update` - Core level method with 1474 full argument and behavioral description. 1475 1476 """ 1477 self._for_update_arg = LockmodeArg(read=read, nowait=nowait, of=of, 1478 skip_locked=skip_locked, 1479 key_share=key_share) 1480 1481 @_generative() 1482 def params(self, *args, **kwargs): 1483 r"""add values for bind parameters which may have been 1484 specified in filter(). 1485 1486 parameters may be specified using \**kwargs, or optionally a single 1487 dictionary as the first positional argument. The reason for both is 1488 that \**kwargs is convenient, however some parameter dictionaries 1489 contain unicode keys in which case \**kwargs cannot be used. 1490 1491 """ 1492 if len(args) == 1: 1493 kwargs.update(args[0]) 1494 elif len(args) > 0: 1495 raise sa_exc.ArgumentError( 1496 "params() takes zero or one positional argument, " 1497 "which is a dictionary.") 1498 self._params = self._params.copy() 1499 self._params.update(kwargs) 1500 1501 @_generative(_no_statement_condition, _no_limit_offset) 1502 def filter(self, *criterion): 1503 r"""apply the given filtering criterion to a copy 1504 of this :class:`.Query`, using SQL expressions. 1505 1506 e.g.:: 1507 1508 session.query(MyClass).filter(MyClass.name == 'some name') 1509 1510 Multiple criteria may be specified as comma separated; the effect 1511 is that they will be joined together using the :func:`.and_` 1512 function:: 1513 1514 session.query(MyClass).\ 1515 filter(MyClass.name == 'some name', MyClass.id > 5) 1516 1517 The criterion is any SQL expression object applicable to the 1518 WHERE clause of a select. String expressions are coerced 1519 into SQL expression constructs via the :func:`.text` construct. 1520 1521 .. seealso:: 1522 1523 :meth:`.Query.filter_by` - filter on keyword expressions. 1524 1525 """ 1526 for criterion in list(criterion): 1527 criterion = expression._expression_literal_as_text(criterion) 1528 1529 criterion = self._adapt_clause(criterion, True, True) 1530 1531 if self._criterion is not None: 1532 self._criterion = self._criterion & criterion 1533 else: 1534 self._criterion = criterion 1535 1536 def filter_by(self, **kwargs): 1537 r"""apply the given filtering criterion to a copy 1538 of this :class:`.Query`, using keyword expressions. 1539 1540 e.g.:: 1541 1542 session.query(MyClass).filter_by(name = 'some name') 1543 1544 Multiple criteria may be specified as comma separated; the effect 1545 is that they will be joined together using the :func:`.and_` 1546 function:: 1547 1548 session.query(MyClass).\ 1549 filter_by(name = 'some name', id = 5) 1550 1551 The keyword expressions are extracted from the primary 1552 entity of the query, or the last entity that was the 1553 target of a call to :meth:`.Query.join`. 1554 1555 .. seealso:: 1556 1557 :meth:`.Query.filter` - filter on SQL expressions. 1558 1559 """ 1560 1561 clauses = [_entity_descriptor(self._joinpoint_zero(), key) == value 1562 for key, value in kwargs.items()] 1563 return self.filter(sql.and_(*clauses)) 1564 1565 @_generative(_no_statement_condition, _no_limit_offset) 1566 def order_by(self, *criterion): 1567 """apply one or more ORDER BY criterion to the query and return 1568 the newly resulting ``Query`` 1569 1570 All existing ORDER BY settings can be suppressed by 1571 passing ``None`` - this will suppress any ORDER BY configured 1572 on mappers as well. 1573 1574 Alternatively, passing False will reset ORDER BY and additionally 1575 re-allow default mapper.order_by to take place. Note mapper.order_by 1576 is deprecated. 1577 1578 """ 1579 1580 if len(criterion) == 1: 1581 if criterion[0] is False: 1582 if '_order_by' in self.__dict__: 1583 self._order_by = False 1584 return 1585 if criterion[0] is None: 1586 self._order_by = None 1587 return 1588 1589 criterion = self._adapt_col_list(criterion) 1590 1591 if self._order_by is False or self._order_by is None: 1592 self._order_by = criterion 1593 else: 1594 self._order_by = self._order_by + criterion 1595 1596 @_generative(_no_statement_condition, _no_limit_offset) 1597 def group_by(self, *criterion): 1598 """apply one or more GROUP BY criterion to the query and return 1599 the newly resulting :class:`.Query` 1600 1601 All existing GROUP BY settings can be suppressed by 1602 passing ``None`` - this will suppress any GROUP BY configured 1603 on mappers as well. 1604 1605 .. versionadded:: 1.1 GROUP BY can be cancelled by passing None, 1606 in the same way as ORDER BY. 1607 1608 """ 1609 1610 if len(criterion) == 1: 1611 if criterion[0] is None: 1612 self._group_by = False 1613 return 1614 1615 criterion = list(chain(*[_orm_columns(c) for c in criterion])) 1616 criterion = self._adapt_col_list(criterion) 1617 1618 if self._group_by is False: 1619 self._group_by = criterion 1620 else: 1621 self._group_by = self._group_by + criterion 1622 1623 @_generative(_no_statement_condition, _no_limit_offset) 1624 def having(self, criterion): 1625 r"""apply a HAVING criterion to the query and return the 1626 newly resulting :class:`.Query`. 1627 1628 :meth:`~.Query.having` is used in conjunction with 1629 :meth:`~.Query.group_by`. 1630 1631 HAVING criterion makes it possible to use filters on aggregate 1632 functions like COUNT, SUM, AVG, MAX, and MIN, eg.:: 1633 1634 q = session.query(User.id).\ 1635 join(User.addresses).\ 1636 group_by(User.id).\ 1637 having(func.count(Address.id) > 2) 1638 1639 """ 1640 1641 criterion = expression._expression_literal_as_text(criterion) 1642 1643 if criterion is not None and \ 1644 not isinstance(criterion, sql.ClauseElement): 1645 raise sa_exc.ArgumentError( 1646 "having() argument must be of type " 1647 "sqlalchemy.sql.ClauseElement or string") 1648 1649 criterion = self._adapt_clause(criterion, True, True) 1650 1651 if self._having is not None: 1652 self._having = self._having & criterion 1653 else: 1654 self._having = criterion 1655 1656 def _set_op(self, expr_fn, *q): 1657 return self._from_selectable( 1658 expr_fn(*([self] + list(q))) 1659 )._set_enable_single_crit(False) 1660 1661 def union(self, *q): 1662 """Produce a UNION of this Query against one or more queries. 1663 1664 e.g.:: 1665 1666 q1 = sess.query(SomeClass).filter(SomeClass.foo=='bar') 1667 q2 = sess.query(SomeClass).filter(SomeClass.bar=='foo') 1668 1669 q3 = q1.union(q2) 1670 1671 The method accepts multiple Query objects so as to control 1672 the level of nesting. A series of ``union()`` calls such as:: 1673 1674 x.union(y).union(z).all() 1675 1676 will nest on each ``union()``, and produces:: 1677 1678 SELECT * FROM (SELECT * FROM (SELECT * FROM X UNION 1679 SELECT * FROM y) UNION SELECT * FROM Z) 1680 1681 Whereas:: 1682 1683 x.union(y, z).all() 1684 1685 produces:: 1686 1687 SELECT * FROM (SELECT * FROM X UNION SELECT * FROM y UNION 1688 SELECT * FROM Z) 1689 1690 Note that many database backends do not allow ORDER BY to 1691 be rendered on a query called within UNION, EXCEPT, etc. 1692 To disable all ORDER BY clauses including those configured 1693 on mappers, issue ``query.order_by(None)`` - the resulting 1694 :class:`.Query` object will not render ORDER BY within 1695 its SELECT statement. 1696 1697 """ 1698 return self._set_op(expression.union, *q) 1699 1700 def union_all(self, *q): 1701 """Produce a UNION ALL of this Query against one or more queries. 1702 1703 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See 1704 that method for usage examples. 1705 1706 """ 1707 return self._set_op(expression.union_all, *q) 1708 1709 def intersect(self, *q): 1710 """Produce an INTERSECT of this Query against one or more queries. 1711 1712 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See 1713 that method for usage examples. 1714 1715 """ 1716 return self._set_op(expression.intersect, *q) 1717 1718 def intersect_all(self, *q): 1719 """Produce an INTERSECT ALL of this Query against one or more queries. 1720 1721 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See 1722 that method for usage examples. 1723 1724 """ 1725 return self._set_op(expression.intersect_all, *q) 1726 1727 def except_(self, *q): 1728 """Produce an EXCEPT of this Query against one or more queries. 1729 1730 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See 1731 that method for usage examples. 1732 1733 """ 1734 return self._set_op(expression.except_, *q) 1735 1736 def except_all(self, *q): 1737 """Produce an EXCEPT ALL of this Query against one or more queries. 1738 1739 Works the same way as :meth:`~sqlalchemy.orm.query.Query.union`. See 1740 that method for usage examples. 1741 1742 """ 1743 return self._set_op(expression.except_all, *q) 1744 1745 def join(self, *props, **kwargs): 1746 r"""Create a SQL JOIN against this :class:`.Query` object's criterion 1747 and apply generatively, returning the newly resulting :class:`.Query`. 1748 1749 **Simple Relationship Joins** 1750 1751 Consider a mapping between two classes ``User`` and ``Address``, 1752 with a relationship ``User.addresses`` representing a collection 1753 of ``Address`` objects associated with each ``User``. The most 1754 common usage of :meth:`~.Query.join` is to create a JOIN along this 1755 relationship, using the ``User.addresses`` attribute as an indicator 1756 for how this should occur:: 1757 1758 q = session.query(User).join(User.addresses) 1759 1760 Where above, the call to :meth:`~.Query.join` along ``User.addresses`` 1761 will result in SQL equivalent to:: 1762 1763 SELECT user.* FROM user JOIN address ON user.id = address.user_id 1764 1765 In the above example we refer to ``User.addresses`` as passed to 1766 :meth:`~.Query.join` as the *on clause*, that is, it indicates 1767 how the "ON" portion of the JOIN should be constructed. For a 1768 single-entity query such as the one above (i.e. we start by selecting 1769 only from ``User`` and nothing else), the relationship can also be 1770 specified by its string name:: 1771 1772 q = session.query(User).join("addresses") 1773 1774 :meth:`~.Query.join` can also accommodate multiple 1775 "on clause" arguments to produce a chain of joins, such as below 1776 where a join across four related entities is constructed:: 1777 1778 q = session.query(User).join("orders", "items", "keywords") 1779 1780 The above would be shorthand for three separate calls to 1781 :meth:`~.Query.join`, each using an explicit attribute to indicate 1782 the source entity:: 1783 1784 q = session.query(User).\ 1785 join(User.orders).\ 1786 join(Order.items).\ 1787 join(Item.keywords) 1788 1789 **Joins to a Target Entity or Selectable** 1790 1791 A second form of :meth:`~.Query.join` allows any mapped entity 1792 or core selectable construct as a target. In this usage, 1793 :meth:`~.Query.join` will attempt 1794 to create a JOIN along the natural foreign key relationship between 1795 two entities:: 1796 1797 q = session.query(User).join(Address) 1798 1799 The above calling form of :meth:`~.Query.join` will raise an error if 1800 either there are no foreign keys between the two entities, or if 1801 there are multiple foreign key linkages between them. In the 1802 above calling form, :meth:`~.Query.join` is called upon to 1803 create the "on clause" automatically for us. The target can 1804 be any mapped entity or selectable, such as a :class:`.Table`:: 1805 1806 q = session.query(User).join(addresses_table) 1807 1808 **Joins to a Target with an ON Clause** 1809 1810 The third calling form allows both the target entity as well 1811 as the ON clause to be passed explicitly. Suppose for 1812 example we wanted to join to ``Address`` twice, using 1813 an alias the second time. We use :func:`~sqlalchemy.orm.aliased` 1814 to create a distinct alias of ``Address``, and join 1815 to it using the ``target, onclause`` form, so that the 1816 alias can be specified explicitly as the target along with 1817 the relationship to instruct how the ON clause should proceed:: 1818 1819 a_alias = aliased(Address) 1820 1821 q = session.query(User).\ 1822 join(User.addresses).\ 1823 join(a_alias, User.addresses).\ 1824 filter(Address.email_address=='ed@foo.com').\ 1825 filter(a_alias.email_address=='ed@bar.com') 1826 1827 Where above, the generated SQL would be similar to:: 1828 1829 SELECT user.* FROM user 1830 JOIN address ON user.id = address.user_id 1831 JOIN address AS address_1 ON user.id=address_1.user_id 1832 WHERE address.email_address = :email_address_1 1833 AND address_1.email_address = :email_address_2 1834 1835 The two-argument calling form of :meth:`~.Query.join` 1836 also allows us to construct arbitrary joins with SQL-oriented 1837 "on clause" expressions, not relying upon configured relationships 1838 at all. Any SQL expression can be passed as the ON clause 1839 when using the two-argument form, which should refer to the target 1840 entity in some way as well as an applicable source entity:: 1841 1842 q = session.query(User).join(Address, User.id==Address.user_id) 1843 1844 .. versionchanged:: 0.7 1845 In SQLAlchemy 0.6 and earlier, the two argument form of 1846 :meth:`~.Query.join` requires the usage of a tuple: 1847 ``query(User).join((Address, User.id==Address.user_id))``\ . 1848 This calling form is accepted in 0.7 and further, though 1849 is not necessary unless multiple join conditions are passed to 1850 a single :meth:`~.Query.join` call, which itself is also not 1851 generally necessary as it is now equivalent to multiple 1852 calls (this wasn't always the case). 1853 1854 **Advanced Join Targeting and Adaption** 1855 1856 There is a lot of flexibility in what the "target" can be when using 1857 :meth:`~.Query.join`. As noted previously, it also accepts 1858 :class:`.Table` constructs and other selectables such as 1859 :func:`.alias` and :func:`.select` constructs, with either the one 1860 or two-argument forms:: 1861 1862 addresses_q = select([Address.user_id]).\ 1863 where(Address.email_address.endswith("@bar.com")).\ 1864 alias() 1865 1866 q = session.query(User).\ 1867 join(addresses_q, addresses_q.c.user_id==User.id) 1868 1869 :meth:`~.Query.join` also features the ability to *adapt* a 1870 :meth:`~sqlalchemy.orm.relationship` -driven ON clause to the target 1871 selectable. Below we construct a JOIN from ``User`` to a subquery 1872 against ``Address``, allowing the relationship denoted by 1873 ``User.addresses`` to *adapt* itself to the altered target:: 1874 1875 address_subq = session.query(Address).\ 1876 filter(Address.email_address == 'ed@foo.com').\ 1877 subquery() 1878 1879 q = session.query(User).join(address_subq, User.addresses) 1880 1881 Producing SQL similar to:: 1882 1883 SELECT user.* FROM user 1884 JOIN ( 1885 SELECT address.id AS id, 1886 address.user_id AS user_id, 1887 address.email_address AS email_address 1888 FROM address 1889 WHERE address.email_address = :email_address_1 1890 ) AS anon_1 ON user.id = anon_1.user_id 1891 1892 The above form allows one to fall back onto an explicit ON 1893 clause at any time:: 1894 1895 q = session.query(User).\ 1896 join(address_subq, User.id==address_subq.c.user_id) 1897 1898 **Controlling what to Join From** 1899 1900 While :meth:`~.Query.join` exclusively deals with the "right" 1901 side of the JOIN, we can also control the "left" side, in those 1902 cases where it's needed, using :meth:`~.Query.select_from`. 1903 Below we construct a query against ``Address`` but can still 1904 make usage of ``User.addresses`` as our ON clause by instructing 1905 the :class:`.Query` to select first from the ``User`` 1906 entity:: 1907 1908 q = session.query(Address).select_from(User).\ 1909 join(User.addresses).\ 1910 filter(User.name == 'ed') 1911 1912 Which will produce SQL similar to:: 1913 1914 SELECT address.* FROM user 1915 JOIN address ON user.id=address.user_id 1916 WHERE user.name = :name_1 1917 1918 **Constructing Aliases Anonymously** 1919 1920 :meth:`~.Query.join` can construct anonymous aliases 1921 using the ``aliased=True`` flag. This feature is useful 1922 when a query is being joined algorithmically, such as 1923 when querying self-referentially to an arbitrary depth:: 1924 1925 q = session.query(Node).\ 1926 join("children", "children", aliased=True) 1927 1928 When ``aliased=True`` is used, the actual "alias" construct 1929 is not explicitly available. To work with it, methods such as 1930 :meth:`.Query.filter` will adapt the incoming entity to 1931 the last join point:: 1932 1933 q = session.query(Node).\ 1934 join("children", "children", aliased=True).\ 1935 filter(Node.name == 'grandchild 1') 1936 1937 When using automatic aliasing, the ``from_joinpoint=True`` 1938 argument can allow a multi-node join to be broken into 1939 multiple calls to :meth:`~.Query.join`, so that 1940 each path along the way can be further filtered:: 1941 1942 q = session.query(Node).\ 1943 join("children", aliased=True).\ 1944 filter(Node.name='child 1').\ 1945 join("children", aliased=True, from_joinpoint=True).\ 1946 filter(Node.name == 'grandchild 1') 1947 1948 The filtering aliases above can then be reset back to the 1949 original ``Node`` entity using :meth:`~.Query.reset_joinpoint`:: 1950 1951 q = session.query(Node).\ 1952 join("children", "children", aliased=True).\ 1953 filter(Node.name == 'grandchild 1').\ 1954 reset_joinpoint().\ 1955 filter(Node.name == 'parent 1) 1956 1957 For an example of ``aliased=True``, see the distribution 1958 example :ref:`examples_xmlpersistence` which illustrates 1959 an XPath-like query system using algorithmic joins. 1960 1961 :param \*props: A collection of one or more join conditions, 1962 each consisting of a relationship-bound attribute or string 1963 relationship name representing an "on clause", or a single 1964 target entity, or a tuple in the form of ``(target, onclause)``. 1965 A special two-argument calling form of the form ``target, onclause`` 1966 is also accepted. 1967 :param aliased=False: If True, indicate that the JOIN target should be 1968 anonymously aliased. Subsequent calls to :meth:`~.Query.filter` 1969 and similar will adapt the incoming criterion to the target 1970 alias, until :meth:`~.Query.reset_joinpoint` is called. 1971 :param isouter=False: If True, the join used will be a left outer join, 1972 just as if the :meth:`.Query.outerjoin` method were called. This 1973 flag is here to maintain consistency with the same flag as accepted 1974 by :meth:`.FromClause.join` and other Core constructs. 1975 1976 1977 .. versionadded:: 1.0.0 1978 1979 :param full=False: render FULL OUTER JOIN; implies ``isouter``. 1980 1981 .. versionadded:: 1.1 1982 1983 :param from_joinpoint=False: When using ``aliased=True``, a setting 1984 of True here will cause the join to be from the most recent 1985 joined target, rather than starting back from the original 1986 FROM clauses of the query. 1987 1988 .. seealso:: 1989 1990 :ref:`ormtutorial_joins` in the ORM tutorial. 1991 1992 :ref:`inheritance_toplevel` for details on how 1993 :meth:`~.Query.join` is used for inheritance relationships. 1994 1995 :func:`.orm.join` - a standalone ORM-level join function, 1996 used internally by :meth:`.Query.join`, which in previous 1997 SQLAlchemy versions was the primary ORM-level joining interface. 1998 1999 """ 2000 aliased, from_joinpoint, isouter, full = kwargs.pop('aliased', False),\ 2001 kwargs.pop('from_joinpoint', False),\ 2002 kwargs.pop('isouter', False),\ 2003 kwargs.pop('full', False) 2004 if kwargs: 2005 raise TypeError("unknown arguments: %s" % 2006 ', '.join(sorted(kwargs))) 2007 return self._join(props, 2008 outerjoin=isouter, full=full, 2009 create_aliases=aliased, 2010 from_joinpoint=from_joinpoint) 2011 2012 def outerjoin(self, *props, **kwargs): 2013 """Create a left outer join against this ``Query`` object's criterion 2014 and apply generatively, returning the newly resulting ``Query``. 2015 2016 Usage is the same as the ``join()`` method. 2017 2018 """ 2019 aliased, from_joinpoint, full = kwargs.pop('aliased', False), \ 2020 kwargs.pop('from_joinpoint', False), \ 2021 kwargs.pop('full', False) 2022 if kwargs: 2023 raise TypeError("unknown arguments: %s" % 2024 ', '.join(sorted(kwargs))) 2025 return self._join(props, 2026 outerjoin=True, full=full, create_aliases=aliased, 2027 from_joinpoint=from_joinpoint) 2028 2029 def _update_joinpoint(self, jp): 2030 self._joinpoint = jp 2031 # copy backwards to the root of the _joinpath 2032 # dict, so that no existing dict in the path is mutated 2033 while 'prev' in jp: 2034 f, prev = jp['prev'] 2035 prev = prev.copy() 2036 prev[f] = jp 2037 jp['prev'] = (f, prev) 2038 jp = prev 2039 self._joinpath = jp 2040 2041 @_generative(_no_statement_condition, _no_limit_offset) 2042 def _join(self, keys, outerjoin, full, create_aliases, from_joinpoint): 2043 """consumes arguments from join() or outerjoin(), places them into a 2044 consistent format with which to form the actual JOIN constructs. 2045 2046 """ 2047 2048 if not from_joinpoint: 2049 self._reset_joinpoint() 2050 2051 if len(keys) == 2 and \ 2052 isinstance(keys[0], (expression.FromClause, 2053 type, AliasedClass)) and \ 2054 isinstance(keys[1], (str, expression.ClauseElement, 2055 interfaces.PropComparator)): 2056 # detect 2-arg form of join and 2057 # convert to a tuple. 2058 keys = (keys,) 2059 2060 keylist = util.to_list(keys) 2061 for idx, arg1 in enumerate(keylist): 2062 if isinstance(arg1, tuple): 2063 # "tuple" form of join, multiple 2064 # tuples are accepted as well. The simpler 2065 # "2-arg" form is preferred. May deprecate 2066 # the "tuple" usage. 2067 arg1, arg2 = arg1 2068 else: 2069 arg2 = None 2070 2071 # determine onclause/right_entity. there 2072 # is a little bit of legacy behavior still at work here 2073 # which means they might be in either order. may possibly 2074 # lock this down to (right_entity, onclause) in 0.6. 2075 if isinstance( 2076 arg1, (interfaces.PropComparator, util.string_types)): 2077 right_entity, onclause = arg2, arg1 2078 else: 2079 right_entity, onclause = arg1, arg2 2080 2081 left_entity = prop = None 2082 2083 if isinstance(onclause, interfaces.PropComparator): 2084 of_type = getattr(onclause, '_of_type', None) 2085 else: 2086 of_type = None 2087 2088 if isinstance(onclause, util.string_types): 2089 left_entity = self._joinpoint_zero() 2090 2091 descriptor = _entity_descriptor(left_entity, onclause) 2092 onclause = descriptor 2093 2094 # check for q.join(Class.propname, from_joinpoint=True) 2095 # and Class is that of the current joinpoint 2096 elif from_joinpoint and \ 2097 isinstance(onclause, interfaces.PropComparator): 2098 left_entity = onclause._parententity 2099 2100 info = inspect(self._joinpoint_zero()) 2101 left_mapper, left_selectable, left_is_aliased = \ 2102 getattr(info, 'mapper', None), \ 2103 info.selectable, \ 2104 getattr(info, 'is_aliased_class', None) 2105 2106 if left_mapper is left_entity: 2107 left_entity = self._joinpoint_zero() 2108 descriptor = _entity_descriptor(left_entity, 2109 onclause.key) 2110 onclause = descriptor 2111 2112 if isinstance(onclause, interfaces.PropComparator): 2113 if right_entity is None: 2114 if of_type: 2115 right_entity = of_type 2116 else: 2117 right_entity = onclause.property.mapper 2118 2119 left_entity = onclause._parententity 2120 2121 prop = onclause.property 2122 if not isinstance(onclause, attributes.QueryableAttribute): 2123 onclause = prop 2124 2125 if not create_aliases: 2126 # check for this path already present. 2127 # don't render in that case. 2128 edge = (left_entity, right_entity, prop.key) 2129 if edge in self._joinpoint: 2130 # The child's prev reference might be stale -- 2131 # it could point to a parent older than the 2132 # current joinpoint. If this is the case, 2133 # then we need to update it and then fix the 2134 # tree's spine with _update_joinpoint. Copy 2135 # and then mutate the child, which might be 2136 # shared by a different query object. 2137 jp = self._joinpoint[edge].copy() 2138 jp['prev'] = (edge, self._joinpoint) 2139 self._update_joinpoint(jp) 2140 2141 if idx == len(keylist) - 1: 2142 util.warn( 2143 "Pathed join target %s has already " 2144 "been joined to; skipping" % prop) 2145 continue 2146 2147 elif onclause is not None and right_entity is None: 2148 # TODO: no coverage here 2149 raise NotImplementedError("query.join(a==b) not supported.") 2150 2151 self._join_left_to_right( 2152 left_entity, 2153 right_entity, onclause, 2154 outerjoin, full, create_aliases, prop) 2155 2156 def _join_left_to_right(self, left, right, 2157 onclause, outerjoin, full, create_aliases, prop): 2158 """append a JOIN to the query's from clause.""" 2159 2160 self._polymorphic_adapters = self._polymorphic_adapters.copy() 2161 2162 if left is None: 2163 if self._from_obj: 2164 left = self._from_obj[0] 2165 elif self._entities: 2166 left = self._entities[0].entity_zero_or_selectable 2167 2168 if left is None: 2169 if self._entities: 2170 problem = "Don't know how to join from %s" % self._entities[0] 2171 else: 2172 problem = "No entities to join from" 2173 2174 raise sa_exc.InvalidRequestError( 2175 "%s; please use " 2176 "select_from() to establish the left " 2177 "entity/selectable of this join" % problem) 2178 2179 if left is right and \ 2180 not create_aliases: 2181 raise sa_exc.InvalidRequestError( 2182 "Can't construct a join from %s to %s, they " 2183 "are the same entity" % 2184 (left, right)) 2185 2186 l_info = inspect(left) 2187 r_info = inspect(right) 2188 2189 overlap = False 2190 if not create_aliases: 2191 right_mapper = getattr(r_info, "mapper", None) 2192 # if the target is a joined inheritance mapping, 2193 # be more liberal about auto-aliasing. 2194 if right_mapper and ( 2195 right_mapper.with_polymorphic or 2196 isinstance(right_mapper.mapped_table, expression.Join) 2197 ): 2198 for from_obj in self._from_obj or [l_info.selectable]: 2199 if sql_util.selectables_overlap( 2200 l_info.selectable, from_obj) and \ 2201 sql_util.selectables_overlap( 2202 from_obj, r_info.selectable): 2203 overlap = True 2204 break 2205 2206 if (overlap or not create_aliases) and \ 2207 l_info.selectable is r_info.selectable: 2208 raise sa_exc.InvalidRequestError( 2209 "Can't join table/selectable '%s' to itself" % 2210 l_info.selectable) 2211 2212 right, onclause = self._prepare_right_side( 2213 r_info, right, onclause, 2214 create_aliases, 2215 prop, overlap) 2216 2217 # if joining on a MapperProperty path, 2218 # track the path to prevent redundant joins 2219 if not create_aliases and prop: 2220 self._update_joinpoint({ 2221 '_joinpoint_entity': right, 2222 'prev': ((left, right, prop.key), self._joinpoint) 2223 }) 2224 else: 2225 self._joinpoint = {'_joinpoint_entity': right} 2226 2227 self._join_to_left(l_info, left, right, onclause, outerjoin, full) 2228 2229 def _prepare_right_side(self, r_info, right, onclause, create_aliases, 2230 prop, overlap): 2231 info = r_info 2232 2233 right_mapper, right_selectable, right_is_aliased = \ 2234 getattr(info, 'mapper', None), \ 2235 info.selectable, \ 2236 getattr(info, 'is_aliased_class', False) 2237 2238 if right_mapper: 2239 self._join_entities += (info, ) 2240 2241 if right_mapper and prop and \ 2242 not right_mapper.common_parent(prop.mapper): 2243 raise sa_exc.InvalidRequestError( 2244 "Join target %s does not correspond to " 2245 "the right side of join condition %s" % (right, onclause) 2246 ) 2247 2248 if not right_mapper and prop: 2249 right_mapper = prop.mapper 2250 2251 need_adapter = False 2252 2253 if right_mapper and right is right_selectable: 2254 if not right_selectable.is_derived_from( 2255 right_mapper.mapped_table): 2256 raise sa_exc.InvalidRequestError( 2257 "Selectable '%s' is not derived from '%s'" % 2258 (right_selectable.description, 2259 right_mapper.mapped_table.description)) 2260 2261 if isinstance(right_selectable, expression.SelectBase): 2262 # TODO: this isn't even covered now! 2263 right_selectable = right_selectable.alias() 2264 need_adapter = True 2265 2266 right = aliased(right_mapper, right_selectable) 2267 2268 aliased_entity = right_mapper and \ 2269 not right_is_aliased and \ 2270 ( 2271 right_mapper.with_polymorphic and isinstance( 2272 right_mapper._with_polymorphic_selectable, 2273 expression.Alias) 2274 or 2275 overlap # test for overlap: 2276 # orm/inheritance/relationships.py 2277 # SelfReferentialM2MTest 2278 ) 2279 2280 if not need_adapter and (create_aliases or aliased_entity): 2281 right = aliased(right, flat=True) 2282 need_adapter = True 2283 2284 # if an alias() of the right side was generated here, 2285 # apply an adapter to all subsequent filter() calls 2286 # until reset_joinpoint() is called. 2287 if need_adapter: 2288 self._filter_aliases = ORMAdapter( 2289 right, 2290 equivalents=right_mapper and 2291 right_mapper._equivalent_columns or {}, 2292 chain_to=self._filter_aliases) 2293 2294 # if the onclause is a ClauseElement, adapt it with any 2295 # adapters that are in place right now 2296 if isinstance(onclause, expression.ClauseElement): 2297 onclause = self._adapt_clause(onclause, True, True) 2298 2299 # if an alias() on the right side was generated, 2300 # which is intended to wrap a the right side in a subquery, 2301 # ensure that columns retrieved from this target in the result 2302 # set are also adapted. 2303 if aliased_entity and not create_aliases: 2304 self._mapper_loads_polymorphically_with( 2305 right_mapper, 2306 ORMAdapter( 2307 right, 2308 equivalents=right_mapper._equivalent_columns 2309 ) 2310 ) 2311 2312 return right, onclause 2313 2314 def _join_to_left(self, l_info, left, right, onclause, outerjoin, full): 2315 info = l_info 2316 left_mapper = getattr(info, 'mapper', None) 2317 left_selectable = info.selectable 2318 2319 if self._from_obj: 2320 replace_clause_index, clause = sql_util.find_join_source( 2321 self._from_obj, 2322 left_selectable) 2323 if clause is not None: 2324 try: 2325 clause = orm_join(clause, 2326 right, 2327 onclause, isouter=outerjoin, full=full) 2328 except sa_exc.ArgumentError as ae: 2329 raise sa_exc.InvalidRequestError( 2330 "Could not find a FROM clause to join from. " 2331 "Tried joining to %s, but got: %s" % (right, ae)) 2332 2333 self._from_obj = \ 2334 self._from_obj[:replace_clause_index] + \ 2335 (clause, ) + \ 2336 self._from_obj[replace_clause_index + 1:] 2337 return 2338 2339 if left_mapper: 2340 for ent in self._entities: 2341 if ent.corresponds_to(left): 2342 clause = ent.selectable 2343 break 2344 else: 2345 clause = left 2346 else: 2347 clause = left_selectable 2348 2349 assert clause is not None 2350 try: 2351 clause = orm_join( 2352 clause, right, onclause, isouter=outerjoin, full=full) 2353 except sa_exc.ArgumentError as ae: 2354 raise sa_exc.InvalidRequestError( 2355 "Could not find a FROM clause to join from. " 2356 "Tried joining to %s, but got: %s" % (right, ae)) 2357 self._from_obj = self._from_obj + (clause,) 2358 2359 def _reset_joinpoint(self): 2360 self._joinpoint = self._joinpath 2361 self._filter_aliases = None 2362 2363 @_generative(_no_statement_condition) 2364 def reset_joinpoint(self): 2365 """Return a new :class:`.Query`, where the "join point" has 2366 been reset back to the base FROM entities of the query. 2367 2368 This method is usually used in conjunction with the 2369 ``aliased=True`` feature of the :meth:`~.Query.join` 2370 method. See the example in :meth:`~.Query.join` for how 2371 this is used. 2372 2373 """ 2374 self._reset_joinpoint() 2375 2376 @_generative(_no_clauseelement_condition) 2377 def select_from(self, *from_obj): 2378 r"""Set the FROM clause of this :class:`.Query` explicitly. 2379 2380 :meth:`.Query.select_from` is often used in conjunction with 2381 :meth:`.Query.join` in order to control which entity is selected 2382 from on the "left" side of the join. 2383 2384 The entity or selectable object here effectively replaces the 2385 "left edge" of any calls to :meth:`~.Query.join`, when no 2386 joinpoint is otherwise established - usually, the default "join 2387 point" is the leftmost entity in the :class:`~.Query` object's 2388 list of entities to be selected. 2389 2390 A typical example:: 2391 2392 q = session.query(Address).select_from(User).\ 2393 join(User.addresses).\ 2394 filter(User.name == 'ed') 2395 2396 Which produces SQL equivalent to:: 2397 2398 SELECT address.* FROM user 2399 JOIN address ON user.id=address.user_id 2400 WHERE user.name = :name_1 2401 2402 :param \*from_obj: collection of one or more entities to apply 2403 to the FROM clause. Entities can be mapped classes, 2404 :class:`.AliasedClass` objects, :class:`.Mapper` objects 2405 as well as core :class:`.FromClause` elements like subqueries. 2406 2407 .. versionchanged:: 0.9 2408 This method no longer applies the given FROM object 2409 to be the selectable from which matching entities 2410 select from; the :meth:`.select_entity_from` method 2411 now accomplishes this. See that method for a description 2412 of this behavior. 2413 2414 .. seealso:: 2415 2416 :meth:`~.Query.join` 2417 2418 :meth:`.Query.select_entity_from` 2419 2420 """ 2421 2422 self._set_select_from(from_obj, False) 2423 2424 @_generative(_no_clauseelement_condition) 2425 def select_entity_from(self, from_obj): 2426 r"""Set the FROM clause of this :class:`.Query` to a 2427 core selectable, applying it as a replacement FROM clause 2428 for corresponding mapped entities. 2429 2430 The :meth:`.Query.select_entity_from` method supplies an alternative 2431 approach to the use case of applying an :func:`.aliased` construct 2432 explicitly throughout a query. Instead of referring to the 2433 :func:`.aliased` construct explicitly, 2434 :meth:`.Query.select_entity_from` automatically *adapts* all occurences 2435 of the entity to the target selectable. 2436 2437 Given a case for :func:`.aliased` such as selecting ``User`` 2438 objects from a SELECT statement:: 2439 2440 select_stmt = select([User]).where(User.id == 7) 2441 user_alias = aliased(User, select_stmt) 2442 2443 q = session.query(user_alias).\ 2444 filter(user_alias.name == 'ed') 2445 2446 Above, we apply the ``user_alias`` object explicitly throughout the 2447 query. When it's not feasible for ``user_alias`` to be referenced 2448 explicitly in many places, :meth:`.Query.select_entity_from` may be 2449 used at the start of the query to adapt the existing ``User`` entity:: 2450 2451 q = session.query(User).\ 2452 select_entity_from(select_stmt).\ 2453 filter(User.name == 'ed') 2454 2455 Above, the generated SQL will show that the ``User`` entity is 2456 adapted to our statement, even in the case of the WHERE clause: 2457 2458 .. sourcecode:: sql 2459 2460 SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name 2461 FROM (SELECT "user".id AS id, "user".name AS name 2462 FROM "user" 2463 WHERE "user".id = :id_1) AS anon_1 2464 WHERE anon_1.name = :name_1 2465 2466 The :meth:`.Query.select_entity_from` method is similar to the 2467 :meth:`.Query.select_from` method, in that it sets the FROM clause 2468 of the query. The difference is that it additionally applies 2469 adaptation to the other parts of the query that refer to the 2470 primary entity. If above we had used :meth:`.Query.select_from` 2471 instead, the SQL generated would have been: 2472 2473 .. sourcecode:: sql 2474 2475 -- uses plain select_from(), not select_entity_from() 2476 SELECT "user".id AS user_id, "user".name AS user_name 2477 FROM "user", (SELECT "user".id AS id, "user".name AS name 2478 FROM "user" 2479 WHERE "user".id = :id_1) AS anon_1 2480 WHERE "user".name = :name_1 2481 2482 To supply textual SQL to the :meth:`.Query.select_entity_from` method, 2483 we can make use of the :func:`.text` construct. However, the 2484 :func:`.text` construct needs to be aligned with the columns of our 2485 entity, which is achieved by making use of the 2486 :meth:`.TextClause.columns` method:: 2487 2488 text_stmt = text("select id, name from user").columns( 2489 User.id, User.name) 2490 q = session.query(User).select_entity_from(text_stmt) 2491 2492 :meth:`.Query.select_entity_from` itself accepts an :func:`.aliased` 2493 object, so that the special options of :func:`.aliased` such as 2494 :paramref:`.aliased.adapt_on_names` may be used within the 2495 scope of the :meth:`.Query.select_entity_from` method's adaptation 2496 services. Suppose 2497 a view ``user_view`` also returns rows from ``user``. If 2498 we reflect this view into a :class:`.Table`, this view has no 2499 relationship to the :class:`.Table` to which we are mapped, however 2500 we can use name matching to select from it:: 2501 2502 user_view = Table('user_view', metadata, 2503 autoload_with=engine) 2504 user_view_alias = aliased( 2505 User, user_view, adapt_on_names=True) 2506 q = session.query(User).\ 2507 select_entity_from(user_view_alias).\ 2508 order_by(User.name) 2509 2510 .. versionchanged:: 1.1.7 The :meth:`.Query.select_entity_from` 2511 method now accepts an :func:`.aliased` object as an alternative 2512 to a :class:`.FromClause` object. 2513 2514 :param from_obj: a :class:`.FromClause` object that will replace 2515 the FROM clause of this :class:`.Query`. It also may be an instance 2516 of :func:`.aliased`. 2517 2518 2519 2520 .. seealso:: 2521 2522 :meth:`.Query.select_from` 2523 2524 """ 2525 2526 self._set_select_from([from_obj], True) 2527 2528 def __getitem__(self, item): 2529 if isinstance(item, slice): 2530 start, stop, step = util.decode_slice(item) 2531 2532 if isinstance(stop, int) and \ 2533 isinstance(start, int) and \ 2534 stop - start <= 0: 2535 return [] 2536 2537 # perhaps we should execute a count() here so that we 2538 # can still use LIMIT/OFFSET ? 2539 elif (isinstance(start, int) and start < 0) \ 2540 or (isinstance(stop, int) and stop < 0): 2541 return list(self)[item] 2542 2543 res = self.slice(start, stop) 2544 if step is not None: 2545 return list(res)[None:None:item.step] 2546 else: 2547 return list(res) 2548 else: 2549 if item == -1: 2550 return list(self)[-1] 2551 else: 2552 return list(self[item:item + 1])[0] 2553 2554 @_generative(_no_statement_condition) 2555 def slice(self, start, stop): 2556 """Computes the "slice" of the :class:`.Query` represented by 2557 the given indices and returns the resulting :class:`.Query`. 2558 2559 The start and stop indices behave like the argument to Python's 2560 built-in :func:`range` function. This method provides an 2561 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the 2562 query. 2563 2564 For example, :: 2565 2566 session.query(User).order_by(User.id).slice(1, 3) 2567 2568 renders as 2569 2570 .. sourcecode:: sql 2571 2572 SELECT users.id AS users_id, 2573 users.name AS users_name 2574 FROM users ORDER BY users.id 2575 LIMIT ? OFFSET ? 2576 (2, 1) 2577 2578 .. seealso:: 2579 2580 :meth:`.Query.limit` 2581 2582 :meth:`.Query.offset` 2583 2584 """ 2585 if start is not None and stop is not None: 2586 self._offset = (self._offset or 0) + start 2587 self._limit = stop - start 2588 elif start is None and stop is not None: 2589 self._limit = stop 2590 elif start is not None and stop is None: 2591 self._offset = (self._offset or 0) + start 2592 2593 if self._offset == 0: 2594 self._offset = None 2595 2596 @_generative(_no_statement_condition) 2597 def limit(self, limit): 2598 """Apply a ``LIMIT`` to the query and return the newly resulting 2599 ``Query``. 2600 2601 """ 2602 self._limit = limit 2603 2604 @_generative(_no_statement_condition) 2605 def offset(self, offset): 2606 """Apply an ``OFFSET`` to the query and return the newly resulting 2607 ``Query``. 2608 2609 """ 2610 self._offset = offset 2611 2612 @_generative(_no_statement_condition) 2613 def distinct(self, *criterion): 2614 r"""Apply a ``DISTINCT`` to the query and return the newly resulting 2615 ``Query``. 2616 2617 2618 .. note:: 2619 2620 The :meth:`.distinct` call includes logic that will automatically 2621 add columns from the ORDER BY of the query to the columns 2622 clause of the SELECT statement, to satisfy the common need 2623 of the database backend that ORDER BY columns be part of the 2624 SELECT list when DISTINCT is used. These columns *are not* 2625 added to the list of columns actually fetched by the 2626 :class:`.Query`, however, so would not affect results. 2627 The columns are passed through when using the 2628 :attr:`.Query.statement` accessor, however. 2629 2630 :param \*expr: optional column expressions. When present, 2631 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)`` 2632 construct. 2633 2634 """ 2635 if not criterion: 2636 self._distinct = True 2637 else: 2638 criterion = self._adapt_col_list(criterion) 2639 if isinstance(self._distinct, list): 2640 self._distinct += criterion 2641 else: 2642 self._distinct = criterion 2643 2644 @_generative() 2645 def prefix_with(self, *prefixes): 2646 r"""Apply the prefixes to the query and return the newly resulting 2647 ``Query``. 2648 2649 :param \*prefixes: optional prefixes, typically strings, 2650 not using any commas. In particular is useful for MySQL keywords. 2651 2652 e.g.:: 2653 2654 query = sess.query(User.name).\ 2655 prefix_with('HIGH_PRIORITY').\ 2656 prefix_with('SQL_SMALL_RESULT', 'ALL') 2657 2658 Would render:: 2659 2660 SELECT HIGH_PRIORITY SQL_SMALL_RESULT ALL users.name AS users_name 2661 FROM users 2662 2663 .. versionadded:: 0.7.7 2664 2665 .. seealso:: 2666 2667 :meth:`.HasPrefixes.prefix_with` 2668 2669 """ 2670 if self._prefixes: 2671 self._prefixes += prefixes 2672 else: 2673 self._prefixes = prefixes 2674 2675 @_generative() 2676 def suffix_with(self, *suffixes): 2677 r"""Apply the suffix to the query and return the newly resulting 2678 ``Query``. 2679 2680 :param \*suffixes: optional suffixes, typically strings, 2681 not using any commas. 2682 2683 .. versionadded:: 1.0.0 2684 2685 .. seealso:: 2686 2687 :meth:`.Query.prefix_with` 2688 2689 :meth:`.HasSuffixes.suffix_with` 2690 2691 """ 2692 if self._suffixes: 2693 self._suffixes += suffixes 2694 else: 2695 self._suffixes = suffixes 2696 2697 def all(self): 2698 """Return the results represented by this ``Query`` as a list. 2699 2700 This results in an execution of the underlying query. 2701 2702 """ 2703 return list(self) 2704 2705 @_generative(_no_clauseelement_condition) 2706 def from_statement(self, statement): 2707 """Execute the given SELECT statement and return results. 2708 2709 This method bypasses all internal statement compilation, and the 2710 statement is executed without modification. 2711 2712 The statement is typically either a :func:`~.expression.text` 2713 or :func:`~.expression.select` construct, and should return the set 2714 of columns 2715 appropriate to the entity class represented by this :class:`.Query`. 2716 2717 .. seealso:: 2718 2719 :ref:`orm_tutorial_literal_sql` - usage examples in the 2720 ORM tutorial 2721 2722 """ 2723 statement = expression._expression_literal_as_text(statement) 2724 2725 if not isinstance(statement, 2726 (expression.TextClause, 2727 expression.SelectBase)): 2728 raise sa_exc.ArgumentError( 2729 "from_statement accepts text(), select(), " 2730 "and union() objects only.") 2731 2732 self._statement = statement 2733 2734 def first(self): 2735 """Return the first result of this ``Query`` or 2736 None if the result doesn't contain any row. 2737 2738 first() applies a limit of one within the generated SQL, so that 2739 only one primary entity row is generated on the server side 2740 (note this may consist of multiple result rows if join-loaded 2741 collections are present). 2742 2743 Calling :meth:`.Query.first` results in an execution of the underlying query. 2744 2745 .. seealso:: 2746 2747 :meth:`.Query.one` 2748 2749 :meth:`.Query.one_or_none` 2750 2751 """ 2752 if self._statement is not None: 2753 ret = list(self)[0:1] 2754 else: 2755 ret = list(self[0:1]) 2756 if len(ret) > 0: 2757 return ret[0] 2758 else: 2759 return None 2760 2761 def one_or_none(self): 2762 """Return at most one result or raise an exception. 2763 2764 Returns ``None`` if the query selects 2765 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound`` 2766 if multiple object identities are returned, or if multiple 2767 rows are returned for a query that returns only scalar values 2768 as opposed to full identity-mapped entities. 2769 2770 Calling :meth:`.Query.one_or_none` results in an execution of the 2771 underlying query. 2772 2773 .. versionadded:: 1.0.9 2774 2775 Added :meth:`.Query.one_or_none` 2776 2777 .. seealso:: 2778 2779 :meth:`.Query.first` 2780 2781 :meth:`.Query.one` 2782 2783 """ 2784 ret = list(self) 2785 2786 l = len(ret) 2787 if l == 1: 2788 return ret[0] 2789 elif l == 0: 2790 return None 2791 else: 2792 raise orm_exc.MultipleResultsFound( 2793 "Multiple rows were found for one_or_none()") 2794 2795 def one(self): 2796 """Return exactly one result or raise an exception. 2797 2798 Raises ``sqlalchemy.orm.exc.NoResultFound`` if the query selects 2799 no rows. Raises ``sqlalchemy.orm.exc.MultipleResultsFound`` 2800 if multiple object identities are returned, or if multiple 2801 rows are returned for a query that returns only scalar values 2802 as opposed to full identity-mapped entities. 2803 2804 Calling :meth:`.one` results in an execution of the underlying query. 2805 2806 .. seealso:: 2807 2808 :meth:`.Query.first` 2809 2810 :meth:`.Query.one_or_none` 2811 2812 """ 2813 try: 2814 ret = self.one_or_none() 2815 except orm_exc.MultipleResultsFound: 2816 raise orm_exc.MultipleResultsFound( 2817 "Multiple rows were found for one()") 2818 else: 2819 if ret is None: 2820 raise orm_exc.NoResultFound("No row was found for one()") 2821 return ret 2822 2823 def scalar(self): 2824 """Return the first element of the first result or None 2825 if no rows present. If multiple rows are returned, 2826 raises MultipleResultsFound. 2827 2828 >>> session.query(Item).scalar() 2829 <Item> 2830 >>> session.query(Item.id).scalar() 2831 1 2832 >>> session.query(Item.id).filter(Item.id < 0).scalar() 2833 None 2834 >>> session.query(Item.id, Item.name).scalar() 2835 1 2836 >>> session.query(func.count(Parent.id)).scalar() 2837 20 2838 2839 This results in an execution of the underlying query. 2840 2841 """ 2842 try: 2843 ret = self.one() 2844 if not isinstance(ret, tuple): 2845 return ret 2846 return ret[0] 2847 except orm_exc.NoResultFound: 2848 return None 2849 2850 def __iter__(self): 2851 context = self._compile_context() 2852 context.statement.use_labels = True 2853 if self._autoflush and not self._populate_existing: 2854 self.session._autoflush() 2855 return self._execute_and_instances(context) 2856 2857 def __str__(self): 2858 context = self._compile_context() 2859 try: 2860 bind = self._get_bind_args( 2861 context, self.session.get_bind) if self.session else None 2862 except sa_exc.UnboundExecutionError: 2863 bind = None 2864 return str(context.statement.compile(bind)) 2865 2866 def _connection_from_session(self, **kw): 2867 conn = self.session.connection(**kw) 2868 if self._execution_options: 2869 conn = conn.execution_options(**self._execution_options) 2870 return conn 2871 2872 def _execute_and_instances(self, querycontext): 2873 conn = self._get_bind_args( 2874 querycontext, 2875 self._connection_from_session, 2876 close_with_result=True) 2877 2878 result = conn.execute(querycontext.statement, self._params) 2879 return loading.instances(querycontext.query, result, querycontext) 2880 2881 def _get_bind_args(self, querycontext, fn, **kw): 2882 return fn( 2883 mapper=self._bind_mapper(), 2884 clause=querycontext.statement, 2885 **kw 2886 ) 2887 2888 @property 2889 def column_descriptions(self): 2890 """Return metadata about the columns which would be 2891 returned by this :class:`.Query`. 2892 2893 Format is a list of dictionaries:: 2894 2895 user_alias = aliased(User, name='user2') 2896 q = sess.query(User, User.id, user_alias) 2897 2898 # this expression: 2899 q.column_descriptions 2900 2901 # would return: 2902 [ 2903 { 2904 'name':'User', 2905 'type':User, 2906 'aliased':False, 2907 'expr':User, 2908 'entity': User 2909 }, 2910 { 2911 'name':'id', 2912 'type':Integer(), 2913 'aliased':False, 2914 'expr':User.id, 2915 'entity': User 2916 }, 2917 { 2918 'name':'user2', 2919 'type':User, 2920 'aliased':True, 2921 'expr':user_alias, 2922 'entity': user_alias 2923 } 2924 ] 2925 2926 """ 2927 2928 return [ 2929 { 2930 'name': ent._label_name, 2931 'type': ent.type, 2932 'aliased': getattr(insp_ent, 'is_aliased_class', False), 2933 'expr': ent.expr, 2934 'entity': 2935 getattr(insp_ent, "entity", None) 2936 if ent.entity_zero is not None 2937 and not insp_ent.is_clause_element 2938 else None 2939 } 2940 for ent, insp_ent in [ 2941 ( 2942 _ent, 2943 (inspect(_ent.entity_zero) 2944 if _ent.entity_zero is not None else None) 2945 ) 2946 for _ent in self._entities 2947 ] 2948 ] 2949 2950 def instances(self, cursor, __context=None): 2951 """Given a ResultProxy cursor as returned by connection.execute(), 2952 return an ORM result as an iterator. 2953 2954 e.g.:: 2955 2956 result = engine.execute("select * from users") 2957 for u in session.query(User).instances(result): 2958 print u 2959 """ 2960 context = __context 2961 if context is None: 2962 context = QueryContext(self) 2963 2964 return loading.instances(self, cursor, context) 2965 2966 def merge_result(self, iterator, load=True): 2967 """Merge a result into this :class:`.Query` object's Session. 2968 2969 Given an iterator returned by a :class:`.Query` of the same structure 2970 as this one, return an identical iterator of results, with all mapped 2971 instances merged into the session using :meth:`.Session.merge`. This 2972 is an optimized method which will merge all mapped instances, 2973 preserving the structure of the result rows and unmapped columns with 2974 less method overhead than that of calling :meth:`.Session.merge` 2975 explicitly for each value. 2976 2977 The structure of the results is determined based on the column list of 2978 this :class:`.Query` - if these do not correspond, unchecked errors 2979 will occur. 2980 2981 The 'load' argument is the same as that of :meth:`.Session.merge`. 2982 2983 For an example of how :meth:`~.Query.merge_result` is used, see 2984 the source code for the example :ref:`examples_caching`, where 2985 :meth:`~.Query.merge_result` is used to efficiently restore state 2986 from a cache back into a target :class:`.Session`. 2987 2988 """ 2989 2990 return loading.merge_result(self, iterator, load) 2991 2992 @property 2993 def _select_args(self): 2994 return { 2995 'limit': self._limit, 2996 'offset': self._offset, 2997 'distinct': self._distinct, 2998 'prefixes': self._prefixes, 2999 'suffixes': self._suffixes, 3000 'group_by': self._group_by or None, 3001 'having': self._having 3002 } 3003 3004 @property 3005 def _should_nest_selectable(self): 3006 kwargs = self._select_args 3007 return (kwargs.get('limit') is not None or 3008 kwargs.get('offset') is not None or 3009 kwargs.get('distinct', False)) 3010 3011 def exists(self): 3012 """A convenience method that turns a query into an EXISTS subquery 3013 of the form EXISTS (SELECT 1 FROM ... WHERE ...). 3014 3015 e.g.:: 3016 3017 q = session.query(User).filter(User.name == 'fred') 3018 session.query(q.exists()) 3019 3020 Producing SQL similar to:: 3021 3022 SELECT EXISTS ( 3023 SELECT 1 FROM users WHERE users.name = :name_1 3024 ) AS anon_1 3025 3026 The EXISTS construct is usually used in the WHERE clause:: 3027 3028 session.query(User.id).filter(q.exists()).scalar() 3029 3030 Note that some databases such as SQL Server don't allow an 3031 EXISTS expression to be present in the columns clause of a 3032 SELECT. To select a simple boolean value based on the exists 3033 as a WHERE, use :func:`.literal`:: 3034 3035 from sqlalchemy import literal 3036 3037 session.query(literal(True)).filter(q.exists()).scalar() 3038 3039 .. versionadded:: 0.8.1 3040 3041 """ 3042 3043 # .add_columns() for the case that we are a query().select_from(X), 3044 # so that ".statement" can be produced (#2995) but also without 3045 # omitting the FROM clause from a query(X) (#2818); 3046 # .with_only_columns() after we have a core select() so that 3047 # we get just "SELECT 1" without any entities. 3048 return sql.exists(self.add_columns('1').with_labels(). 3049 statement.with_only_columns([1])) 3050 3051 def count(self): 3052 r"""Return a count of rows this Query would return. 3053 3054 This generates the SQL for this Query as follows:: 3055 3056 SELECT count(1) AS count_1 FROM ( 3057 SELECT <rest of query follows...> 3058 ) AS anon_1 3059 3060 .. versionchanged:: 0.7 3061 The above scheme is newly refined as of 0.7b3. 3062 3063 For fine grained control over specific columns 3064 to count, to skip the usage of a subquery or 3065 otherwise control of the FROM clause, 3066 or to use other aggregate functions, 3067 use :attr:`~sqlalchemy.sql.expression.func` 3068 expressions in conjunction 3069 with :meth:`~.Session.query`, i.e.:: 3070 3071 from sqlalchemy import func 3072 3073 # count User records, without 3074 # using a subquery. 3075 session.query(func.count(User.id)) 3076 3077 # return count of user "id" grouped 3078 # by "name" 3079 session.query(func.count(User.id)).\ 3080 group_by(User.name) 3081 3082 from sqlalchemy import distinct 3083 3084 # count distinct "name" values 3085 session.query(func.count(distinct(User.name))) 3086 3087 """ 3088 col = sql.func.count(sql.literal_column('*')) 3089 return self.from_self(col).scalar() 3090 3091 def delete(self, synchronize_session='evaluate'): 3092 r"""Perform a bulk delete query. 3093 3094 Deletes rows matched by this query from the database. 3095 3096 E.g.:: 3097 3098 sess.query(User).filter(User.age == 25).\ 3099 delete(synchronize_session=False) 3100 3101 sess.query(User).filter(User.age == 25).\ 3102 delete(synchronize_session='evaluate') 3103 3104 .. warning:: The :meth:`.Query.delete` method is a "bulk" operation, 3105 which bypasses ORM unit-of-work automation in favor of greater 3106 performance. **Please read all caveats and warnings below.** 3107 3108 :param synchronize_session: chooses the strategy for the removal of 3109 matched objects from the session. Valid values are: 3110 3111 ``False`` - don't synchronize the session. This option is the most 3112 efficient and is reliable once the session is expired, which 3113 typically occurs after a commit(), or explicitly using 3114 expire_all(). Before the expiration, objects may still remain in 3115 the session which were in fact deleted which can lead to confusing 3116 results if they are accessed via get() or already loaded 3117 collections. 3118 3119 ``'fetch'`` - performs a select query before the delete to find 3120 objects that are matched by the delete query and need to be 3121 removed from the session. Matched objects are removed from the 3122 session. 3123 3124 ``'evaluate'`` - Evaluate the query's criteria in Python straight 3125 on the objects in the session. If evaluation of the criteria isn't 3126 implemented, an error is raised. 3127 3128 The expression evaluator currently doesn't account for differing 3129 string collations between the database and Python. 3130 3131 :return: the count of rows matched as returned by the database's 3132 "row count" feature. 3133 3134 .. warning:: **Additional Caveats for bulk query deletes** 3135 3136 * This method does **not work for joined 3137 inheritance mappings**, since the **multiple table 3138 deletes are not supported by SQL** as well as that the 3139 **join condition of an inheritance mapper is not 3140 automatically rendered**. Care must be taken in any 3141 multiple-table delete to first accommodate via some other means 3142 how the related table will be deleted, as well as to 3143 explicitly include the joining 3144 condition between those tables, even in mappings where 3145 this is normally automatic. E.g. if a class ``Engineer`` 3146 subclasses ``Employee``, a DELETE against the ``Employee`` 3147 table would look like:: 3148 3149 session.query(Engineer).\ 3150 filter(Engineer.id == Employee.id).\ 3151 filter(Employee.name == 'dilbert').\ 3152 delete() 3153 3154 However the above SQL will not delete from the Engineer table, 3155 unless an ON DELETE CASCADE rule is established in the database 3156 to handle it. 3157 3158 Short story, **do not use this method for joined inheritance 3159 mappings unless you have taken the additional steps to make 3160 this feasible**. 3161 3162 * The polymorphic identity WHERE criteria is **not** included 3163 for single- or 3164 joined- table updates - this must be added **manually** even 3165 for single table inheritance. 3166 3167 * The method does **not** offer in-Python cascading of 3168 relationships - it is assumed that ON DELETE CASCADE/SET 3169 NULL/etc. is configured for any foreign key references 3170 which require it, otherwise the database may emit an 3171 integrity violation if foreign key references are being 3172 enforced. 3173 3174 After the DELETE, dependent objects in the 3175 :class:`.Session` which were impacted by an ON DELETE 3176 may not contain the current state, or may have been 3177 deleted. This issue is resolved once the 3178 :class:`.Session` is expired, which normally occurs upon 3179 :meth:`.Session.commit` or can be forced by using 3180 :meth:`.Session.expire_all`. Accessing an expired 3181 object whose row has been deleted will invoke a SELECT 3182 to locate the row; when the row is not found, an 3183 :class:`~sqlalchemy.orm.exc.ObjectDeletedError` is 3184 raised. 3185 3186 * The ``'fetch'`` strategy results in an additional 3187 SELECT statement emitted and will significantly reduce 3188 performance. 3189 3190 * The ``'evaluate'`` strategy performs a scan of 3191 all matching objects within the :class:`.Session`; if the 3192 contents of the :class:`.Session` are expired, such as 3193 via a proceeding :meth:`.Session.commit` call, **this will 3194 result in SELECT queries emitted for every matching object**. 3195 3196 * The :meth:`.MapperEvents.before_delete` and 3197 :meth:`.MapperEvents.after_delete` 3198 events **are not invoked** from this method. Instead, the 3199 :meth:`.SessionEvents.after_bulk_delete` method is provided to 3200 act upon a mass DELETE of entity rows. 3201 3202 .. seealso:: 3203 3204 :meth:`.Query.update` 3205 3206 :ref:`inserts_and_updates` - Core SQL tutorial 3207 3208 """ 3209 3210 delete_op = persistence.BulkDelete.factory( 3211 self, synchronize_session) 3212 delete_op.exec_() 3213 return delete_op.rowcount 3214 3215 def update(self, values, synchronize_session='evaluate', update_args=None): 3216 r"""Perform a bulk update query. 3217 3218 Updates rows matched by this query in the database. 3219 3220 E.g.:: 3221 3222 sess.query(User).filter(User.age == 25).\ 3223 update({User.age: User.age - 10}, synchronize_session=False) 3224 3225 sess.query(User).filter(User.age == 25).\ 3226 update({"age": User.age - 10}, synchronize_session='evaluate') 3227 3228 3229 .. warning:: The :meth:`.Query.update` method is a "bulk" operation, 3230 which bypasses ORM unit-of-work automation in favor of greater 3231 performance. **Please read all caveats and warnings below.** 3232 3233 3234 :param values: a dictionary with attributes names, or alternatively 3235 mapped attributes or SQL expressions, as keys, and literal 3236 values or sql expressions as values. If :ref:`parameter-ordered 3237 mode <updates_order_parameters>` is desired, the values can be 3238 passed as a list of 2-tuples; 3239 this requires that the :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order` 3240 flag is passed to the :paramref:`.Query.update.update_args` dictionary 3241 as well. 3242 3243 .. versionchanged:: 1.0.0 - string names in the values dictionary 3244 are now resolved against the mapped entity; previously, these 3245 strings were passed as literal column names with no mapper-level 3246 translation. 3247 3248 :param synchronize_session: chooses the strategy to update the 3249 attributes on objects in the session. Valid values are: 3250 3251 ``False`` - don't synchronize the session. This option is the most 3252 efficient and is reliable once the session is expired, which 3253 typically occurs after a commit(), or explicitly using 3254 expire_all(). Before the expiration, updated objects may still 3255 remain in the session with stale values on their attributes, which 3256 can lead to confusing results. 3257 3258 ``'fetch'`` - performs a select query before the update to find 3259 objects that are matched by the update query. The updated 3260 attributes are expired on matched objects. 3261 3262 ``'evaluate'`` - Evaluate the Query's criteria in Python straight 3263 on the objects in the session. If evaluation of the criteria isn't 3264 implemented, an exception is raised. 3265 3266 The expression evaluator currently doesn't account for differing 3267 string collations between the database and Python. 3268 3269 :param update_args: Optional dictionary, if present will be passed 3270 to the underlying :func:`.update` construct as the ``**kw`` for 3271 the object. May be used to pass dialect-specific arguments such 3272 as ``mysql_limit``, as well as other special arguments such as 3273 :paramref:`~sqlalchemy.sql.expression.update.preserve_parameter_order`. 3274 3275 .. versionadded:: 1.0.0 3276 3277 :return: the count of rows matched as returned by the database's 3278 "row count" feature. 3279 3280 .. warning:: **Additional Caveats for bulk query updates** 3281 3282 * The method does **not** offer in-Python cascading of 3283 relationships - it is assumed that ON UPDATE CASCADE is 3284 configured for any foreign key references which require 3285 it, otherwise the database may emit an integrity 3286 violation if foreign key references are being enforced. 3287 3288 After the UPDATE, dependent objects in the 3289 :class:`.Session` which were impacted by an ON UPDATE 3290 CASCADE may not contain the current state; this issue is 3291 resolved once the :class:`.Session` is expired, which 3292 normally occurs upon :meth:`.Session.commit` or can be 3293 forced by using :meth:`.Session.expire_all`. 3294 3295 * The ``'fetch'`` strategy results in an additional 3296 SELECT statement emitted and will significantly reduce 3297 performance. 3298 3299 * The ``'evaluate'`` strategy performs a scan of 3300 all matching objects within the :class:`.Session`; if the 3301 contents of the :class:`.Session` are expired, such as 3302 via a proceeding :meth:`.Session.commit` call, **this will 3303 result in SELECT queries emitted for every matching object**. 3304 3305 * The method supports multiple table updates, as detailed 3306 in :ref:`multi_table_updates`, and this behavior does 3307 extend to support updates of joined-inheritance and 3308 other multiple table mappings. However, the **join 3309 condition of an inheritance mapper is not 3310 automatically rendered**. Care must be taken in any 3311 multiple-table update to explicitly include the joining 3312 condition between those tables, even in mappings where 3313 this is normally automatic. E.g. if a class ``Engineer`` 3314 subclasses ``Employee``, an UPDATE of the ``Engineer`` 3315 local table using criteria against the ``Employee`` 3316 local table might look like:: 3317 3318 session.query(Engineer).\ 3319 filter(Engineer.id == Employee.id).\ 3320 filter(Employee.name == 'dilbert').\ 3321 update({"engineer_type": "programmer"}) 3322 3323 * The polymorphic identity WHERE criteria is **not** included 3324 for single- or 3325 joined- table updates - this must be added **manually**, even 3326 for single table inheritance. 3327 3328 * The :meth:`.MapperEvents.before_update` and 3329 :meth:`.MapperEvents.after_update` 3330 events **are not invoked from this method**. Instead, the 3331 :meth:`.SessionEvents.after_bulk_update` method is provided to 3332 act upon a mass UPDATE of entity rows. 3333 3334 .. seealso:: 3335 3336 :meth:`.Query.delete` 3337 3338 :ref:`inserts_and_updates` - Core SQL tutorial 3339 3340 """ 3341 3342 update_args = update_args or {} 3343 update_op = persistence.BulkUpdate.factory( 3344 self, synchronize_session, values, update_args) 3345 update_op.exec_() 3346 return update_op.rowcount 3347 3348 def _compile_context(self, labels=True): 3349 if self.dispatch.before_compile: 3350 for fn in self.dispatch.before_compile: 3351 new_query = fn(self) 3352 if new_query is not None: 3353 self = new_query 3354 3355 context = QueryContext(self) 3356 3357 if context.statement is not None: 3358 return context 3359 3360 context.labels = labels 3361 3362 context._for_update_arg = self._for_update_arg 3363 3364 for entity in self._entities: 3365 entity.setup_context(self, context) 3366 3367 for rec in context.create_eager_joins: 3368 strategy = rec[0] 3369 strategy(*rec[1:]) 3370 3371 if context.from_clause: 3372 # "load from explicit FROMs" mode, 3373 # i.e. when select_from() or join() is used 3374 context.froms = list(context.from_clause) 3375 # else "load from discrete FROMs" mode, 3376 # i.e. when each _MappedEntity has its own FROM 3377 3378 if self._enable_single_crit: 3379 self._adjust_for_single_inheritance(context) 3380 3381 if not context.primary_columns: 3382 if self._only_load_props: 3383 raise sa_exc.InvalidRequestError( 3384 "No column-based properties specified for " 3385 "refresh operation. Use session.expire() " 3386 "to reload collections and related items.") 3387 else: 3388 raise sa_exc.InvalidRequestError( 3389 "Query contains no columns with which to " 3390 "SELECT from.") 3391 3392 if context.multi_row_eager_loaders and self._should_nest_selectable: 3393 context.statement = self._compound_eager_statement(context) 3394 else: 3395 context.statement = self._simple_statement(context) 3396 3397 return context 3398 3399 def _compound_eager_statement(self, context): 3400 # for eager joins present and LIMIT/OFFSET/DISTINCT, 3401 # wrap the query inside a select, 3402 # then append eager joins onto that 3403 3404 if context.order_by: 3405 order_by_col_expr = \ 3406 sql_util.expand_column_list_from_order_by( 3407 context.primary_columns, 3408 context.order_by 3409 ) 3410 else: 3411 context.order_by = None 3412 order_by_col_expr = [] 3413 3414 inner = sql.select( 3415 context.primary_columns + order_by_col_expr, 3416 context.whereclause, 3417 from_obj=context.froms, 3418 use_labels=context.labels, 3419 # TODO: this order_by is only needed if 3420 # LIMIT/OFFSET is present in self._select_args, 3421 # else the application on the outside is enough 3422 order_by=context.order_by, 3423 **self._select_args 3424 ) 3425 3426 for hint in self._with_hints: 3427 inner = inner.with_hint(*hint) 3428 3429 if self._correlate: 3430 inner = inner.correlate(*self._correlate) 3431 3432 inner = inner.alias() 3433 3434 equivs = self.__all_equivs() 3435 3436 context.adapter = sql_util.ColumnAdapter(inner, equivs) 3437 3438 statement = sql.select( 3439 [inner] + context.secondary_columns, 3440 use_labels=context.labels) 3441 3442 statement._for_update_arg = context._for_update_arg 3443 3444 from_clause = inner 3445 for eager_join in context.eager_joins.values(): 3446 # EagerLoader places a 'stop_on' attribute on the join, 3447 # giving us a marker as to where the "splice point" of 3448 # the join should be 3449 from_clause = sql_util.splice_joins( 3450 from_clause, 3451 eager_join, eager_join.stop_on) 3452 3453 statement.append_from(from_clause) 3454 3455 if context.order_by: 3456 statement.append_order_by( 3457 *context.adapter.copy_and_process( 3458 context.order_by 3459 ) 3460 ) 3461 3462 statement.append_order_by(*context.eager_order_by) 3463 return statement 3464 3465 def _simple_statement(self, context): 3466 if not context.order_by: 3467 context.order_by = None 3468 3469 if self._distinct is True and context.order_by: 3470 context.primary_columns += \ 3471 sql_util.expand_column_list_from_order_by( 3472 context.primary_columns, 3473 context.order_by 3474 ) 3475 context.froms += tuple(context.eager_joins.values()) 3476 3477 statement = sql.select( 3478 context.primary_columns + 3479 context.secondary_columns, 3480 context.whereclause, 3481 from_obj=context.froms, 3482 use_labels=context.labels, 3483 order_by=context.order_by, 3484 **self._select_args 3485 ) 3486 statement._for_update_arg = context._for_update_arg 3487 3488 for hint in self._with_hints: 3489 statement = statement.with_hint(*hint) 3490 3491 if self._correlate: 3492 statement = statement.correlate(*self._correlate) 3493 3494 if context.eager_order_by: 3495 statement.append_order_by(*context.eager_order_by) 3496 return statement 3497 3498 def _adjust_for_single_inheritance(self, context): 3499 """Apply single-table-inheritance filtering. 3500 3501 For all distinct single-table-inheritance mappers represented in 3502 the columns clause of this query, add criterion to the WHERE 3503 clause of the given QueryContext such that only the appropriate 3504 subtypes are selected from the total results. 3505 3506 """ 3507 for (ext_info, adapter) in set(self._mapper_adapter_map.values()): 3508 if ext_info in self._join_entities: 3509 continue 3510 single_crit = ext_info.mapper._single_table_criterion 3511 if single_crit is not None: 3512 if adapter: 3513 single_crit = adapter.traverse(single_crit) 3514 single_crit = self._adapt_clause(single_crit, False, False) 3515 context.whereclause = sql.and_( 3516 sql.True_._ifnone(context.whereclause), 3517 single_crit) 3518 3519 3520from ..sql.selectable import ForUpdateArg 3521 3522 3523class LockmodeArg(ForUpdateArg): 3524 @classmethod 3525 def parse_legacy_query(self, mode): 3526 if mode in (None, False): 3527 return None 3528 3529 if mode == "read": 3530 read = True 3531 nowait = False 3532 elif mode == "update": 3533 read = nowait = False 3534 elif mode == "update_nowait": 3535 nowait = True 3536 read = False 3537 else: 3538 raise sa_exc.ArgumentError( 3539 "Unknown with_lockmode argument: %r" % mode) 3540 3541 return LockmodeArg(read=read, nowait=nowait) 3542 3543 3544class _QueryEntity(object): 3545 """represent an entity column returned within a Query result.""" 3546 3547 def __new__(cls, *args, **kwargs): 3548 if cls is _QueryEntity: 3549 entity = args[1] 3550 if not isinstance(entity, util.string_types) and \ 3551 _is_mapped_class(entity): 3552 cls = _MapperEntity 3553 elif isinstance(entity, Bundle): 3554 cls = _BundleEntity 3555 else: 3556 cls = _ColumnEntity 3557 return object.__new__(cls) 3558 3559 def _clone(self): 3560 q = self.__class__.__new__(self.__class__) 3561 q.__dict__ = self.__dict__.copy() 3562 return q 3563 3564 3565class _MapperEntity(_QueryEntity): 3566 """mapper/class/AliasedClass entity""" 3567 3568 def __init__(self, query, entity): 3569 if not query._primary_entity: 3570 query._primary_entity = self 3571 query._entities.append(self) 3572 query._has_mapper_entities = True 3573 self.entities = [entity] 3574 self.expr = entity 3575 3576 supports_single_entity = True 3577 3578 use_id_for_hash = True 3579 3580 def setup_entity(self, ext_info, aliased_adapter): 3581 self.mapper = ext_info.mapper 3582 self.aliased_adapter = aliased_adapter 3583 self.selectable = ext_info.selectable 3584 self.is_aliased_class = ext_info.is_aliased_class 3585 self._with_polymorphic = ext_info.with_polymorphic_mappers 3586 self._polymorphic_discriminator = \ 3587 ext_info.polymorphic_on 3588 self.entity_zero = ext_info 3589 if ext_info.is_aliased_class: 3590 self._label_name = self.entity_zero.name 3591 else: 3592 self._label_name = self.mapper.class_.__name__ 3593 self.path = self.entity_zero._path_registry 3594 3595 def set_with_polymorphic(self, query, cls_or_mappers, 3596 selectable, polymorphic_on): 3597 """Receive an update from a call to query.with_polymorphic(). 3598 3599 Note the newer style of using a free standing with_polymporphic() 3600 construct doesn't make use of this method. 3601 3602 3603 """ 3604 if self.is_aliased_class: 3605 # TODO: invalidrequest ? 3606 raise NotImplementedError( 3607 "Can't use with_polymorphic() against " 3608 "an Aliased object" 3609 ) 3610 3611 if cls_or_mappers is None: 3612 query._reset_polymorphic_adapter(self.mapper) 3613 return 3614 3615 mappers, from_obj = self.mapper._with_polymorphic_args( 3616 cls_or_mappers, selectable) 3617 self._with_polymorphic = mappers 3618 self._polymorphic_discriminator = polymorphic_on 3619 3620 self.selectable = from_obj 3621 query._mapper_loads_polymorphically_with( 3622 self.mapper, sql_util.ColumnAdapter( 3623 from_obj, self.mapper._equivalent_columns)) 3624 3625 @property 3626 def type(self): 3627 return self.mapper.class_ 3628 3629 @property 3630 def entity_zero_or_selectable(self): 3631 return self.entity_zero 3632 3633 def corresponds_to(self, entity): 3634 if entity.is_aliased_class: 3635 if self.is_aliased_class: 3636 if entity._base_alias is self.entity_zero._base_alias: 3637 return True 3638 return False 3639 elif self.is_aliased_class: 3640 if self.entity_zero._use_mapper_path: 3641 return entity in self._with_polymorphic 3642 else: 3643 return entity is self.entity_zero 3644 3645 return entity.common_parent(self.entity_zero) 3646 3647 def adapt_to_selectable(self, query, sel): 3648 query._entities.append(self) 3649 3650 def _get_entity_clauses(self, query, context): 3651 3652 adapter = None 3653 3654 if not self.is_aliased_class: 3655 if query._polymorphic_adapters: 3656 adapter = query._polymorphic_adapters.get(self.mapper, None) 3657 else: 3658 adapter = self.aliased_adapter 3659 3660 if adapter: 3661 if query._from_obj_alias: 3662 ret = adapter.wrap(query._from_obj_alias) 3663 else: 3664 ret = adapter 3665 else: 3666 ret = query._from_obj_alias 3667 3668 return ret 3669 3670 def row_processor(self, query, context, result): 3671 adapter = self._get_entity_clauses(query, context) 3672 3673 if context.adapter and adapter: 3674 adapter = adapter.wrap(context.adapter) 3675 elif not adapter: 3676 adapter = context.adapter 3677 3678 # polymorphic mappers which have concrete tables in 3679 # their hierarchy usually 3680 # require row aliasing unconditionally. 3681 if not adapter and self.mapper._requires_row_aliasing: 3682 adapter = sql_util.ColumnAdapter( 3683 self.selectable, 3684 self.mapper._equivalent_columns) 3685 3686 if query._primary_entity is self: 3687 only_load_props = query._only_load_props 3688 refresh_state = context.refresh_state 3689 else: 3690 only_load_props = refresh_state = None 3691 3692 _instance = loading._instance_processor( 3693 self.mapper, 3694 context, 3695 result, 3696 self.path, 3697 adapter, 3698 only_load_props=only_load_props, 3699 refresh_state=refresh_state, 3700 polymorphic_discriminator=self._polymorphic_discriminator 3701 ) 3702 3703 return _instance, self._label_name 3704 3705 def setup_context(self, query, context): 3706 adapter = self._get_entity_clauses(query, context) 3707 3708 # if self._adapted_selectable is None: 3709 context.froms += (self.selectable,) 3710 3711 if context.order_by is False and self.mapper.order_by: 3712 context.order_by = self.mapper.order_by 3713 3714 # apply adaptation to the mapper's order_by if needed. 3715 if adapter: 3716 context.order_by = adapter.adapt_list( 3717 util.to_list( 3718 context.order_by 3719 ) 3720 ) 3721 3722 loading._setup_entity_query( 3723 context, self.mapper, self, 3724 self.path, adapter, context.primary_columns, 3725 with_polymorphic=self._with_polymorphic, 3726 only_load_props=query._only_load_props, 3727 polymorphic_discriminator=self._polymorphic_discriminator) 3728 3729 def __str__(self): 3730 return str(self.mapper) 3731 3732 3733@inspection._self_inspects 3734class Bundle(InspectionAttr): 3735 """A grouping of SQL expressions that are returned by a :class:`.Query` 3736 under one namespace. 3737 3738 The :class:`.Bundle` essentially allows nesting of the tuple-based 3739 results returned by a column-oriented :class:`.Query` object. It also 3740 is extensible via simple subclassing, where the primary capability 3741 to override is that of how the set of expressions should be returned, 3742 allowing post-processing as well as custom return types, without 3743 involving ORM identity-mapped classes. 3744 3745 .. versionadded:: 0.9.0 3746 3747 .. seealso:: 3748 3749 :ref:`bundles` 3750 3751 """ 3752 3753 single_entity = False 3754 """If True, queries for a single Bundle will be returned as a single 3755 entity, rather than an element within a keyed tuple.""" 3756 3757 is_clause_element = False 3758 3759 is_mapper = False 3760 3761 is_aliased_class = False 3762 3763 def __init__(self, name, *exprs, **kw): 3764 r"""Construct a new :class:`.Bundle`. 3765 3766 e.g.:: 3767 3768 bn = Bundle("mybundle", MyClass.x, MyClass.y) 3769 3770 for row in session.query(bn).filter( 3771 bn.c.x == 5).filter(bn.c.y == 4): 3772 print(row.mybundle.x, row.mybundle.y) 3773 3774 :param name: name of the bundle. 3775 :param \*exprs: columns or SQL expressions comprising the bundle. 3776 :param single_entity=False: if True, rows for this :class:`.Bundle` 3777 can be returned as a "single entity" outside of any enclosing tuple 3778 in the same manner as a mapped entity. 3779 3780 """ 3781 self.name = self._label = name 3782 self.exprs = exprs 3783 self.c = self.columns = ColumnCollection() 3784 self.columns.update((getattr(col, "key", col._label), col) 3785 for col in exprs) 3786 self.single_entity = kw.pop('single_entity', self.single_entity) 3787 3788 columns = None 3789 """A namespace of SQL expressions referred to by this :class:`.Bundle`. 3790 3791 e.g.:: 3792 3793 bn = Bundle("mybundle", MyClass.x, MyClass.y) 3794 3795 q = sess.query(bn).filter(bn.c.x == 5) 3796 3797 Nesting of bundles is also supported:: 3798 3799 b1 = Bundle("b1", 3800 Bundle('b2', MyClass.a, MyClass.b), 3801 Bundle('b3', MyClass.x, MyClass.y) 3802 ) 3803 3804 q = sess.query(b1).filter( 3805 b1.c.b2.c.a == 5).filter(b1.c.b3.c.y == 9) 3806 3807 .. seealso:: 3808 3809 :attr:`.Bundle.c` 3810 3811 """ 3812 3813 c = None 3814 """An alias for :attr:`.Bundle.columns`.""" 3815 3816 def _clone(self): 3817 cloned = self.__class__.__new__(self.__class__) 3818 cloned.__dict__.update(self.__dict__) 3819 return cloned 3820 3821 def __clause_element__(self): 3822 return expression.ClauseList(group=False, *self.c) 3823 3824 @property 3825 def clauses(self): 3826 return self.__clause_element__().clauses 3827 3828 def label(self, name): 3829 """Provide a copy of this :class:`.Bundle` passing a new label.""" 3830 3831 cloned = self._clone() 3832 cloned.name = name 3833 return cloned 3834 3835 def create_row_processor(self, query, procs, labels): 3836 """Produce the "row processing" function for this :class:`.Bundle`. 3837 3838 May be overridden by subclasses. 3839 3840 .. seealso:: 3841 3842 :ref:`bundles` - includes an example of subclassing. 3843 3844 """ 3845 keyed_tuple = util.lightweight_named_tuple('result', labels) 3846 3847 def proc(row): 3848 return keyed_tuple([proc(row) for proc in procs]) 3849 return proc 3850 3851 3852class _BundleEntity(_QueryEntity): 3853 use_id_for_hash = False 3854 3855 def __init__(self, query, bundle, setup_entities=True): 3856 query._entities.append(self) 3857 self.bundle = self.expr = bundle 3858 self.type = type(bundle) 3859 self._label_name = bundle.name 3860 self._entities = [] 3861 3862 if setup_entities: 3863 for expr in bundle.exprs: 3864 if isinstance(expr, Bundle): 3865 _BundleEntity(self, expr) 3866 else: 3867 _ColumnEntity(self, expr, namespace=self) 3868 3869 self.supports_single_entity = self.bundle.single_entity 3870 3871 @property 3872 def entities(self): 3873 entities = [] 3874 for ent in self._entities: 3875 entities.extend(ent.entities) 3876 return entities 3877 3878 @property 3879 def entity_zero(self): 3880 for ent in self._entities: 3881 ezero = ent.entity_zero 3882 if ezero is not None: 3883 return ezero 3884 else: 3885 return None 3886 3887 def corresponds_to(self, entity): 3888 # TODO: this seems to have no effect for 3889 # _ColumnEntity either 3890 return False 3891 3892 @property 3893 def entity_zero_or_selectable(self): 3894 for ent in self._entities: 3895 ezero = ent.entity_zero_or_selectable 3896 if ezero is not None: 3897 return ezero 3898 else: 3899 return None 3900 3901 def adapt_to_selectable(self, query, sel): 3902 c = _BundleEntity(query, self.bundle, setup_entities=False) 3903 # c._label_name = self._label_name 3904 # c.entity_zero = self.entity_zero 3905 # c.entities = self.entities 3906 3907 for ent in self._entities: 3908 ent.adapt_to_selectable(c, sel) 3909 3910 def setup_entity(self, ext_info, aliased_adapter): 3911 for ent in self._entities: 3912 ent.setup_entity(ext_info, aliased_adapter) 3913 3914 def setup_context(self, query, context): 3915 for ent in self._entities: 3916 ent.setup_context(query, context) 3917 3918 def row_processor(self, query, context, result): 3919 procs, labels = zip( 3920 *[ent.row_processor(query, context, result) 3921 for ent in self._entities] 3922 ) 3923 3924 proc = self.bundle.create_row_processor(query, procs, labels) 3925 3926 return proc, self._label_name 3927 3928 3929class _ColumnEntity(_QueryEntity): 3930 """Column/expression based entity.""" 3931 3932 def __init__(self, query, column, namespace=None): 3933 self.expr = column 3934 self.namespace = namespace 3935 search_entities = True 3936 check_column = False 3937 3938 if isinstance(column, util.string_types): 3939 column = sql.literal_column(column) 3940 self._label_name = column.name 3941 search_entities = False 3942 check_column = True 3943 _entity = None 3944 elif isinstance(column, ( 3945 attributes.QueryableAttribute, 3946 interfaces.PropComparator 3947 )): 3948 _entity = getattr(column, '_parententity', None) 3949 if _entity is not None: 3950 search_entities = False 3951 self._label_name = column.key 3952 column = column._query_clause_element() 3953 check_column = True 3954 if isinstance(column, Bundle): 3955 _BundleEntity(query, column) 3956 return 3957 3958 if not isinstance(column, sql.ColumnElement): 3959 if hasattr(column, '_select_iterable'): 3960 # break out an object like Table into 3961 # individual columns 3962 for c in column._select_iterable: 3963 if c is column: 3964 break 3965 _ColumnEntity(query, c, namespace=column) 3966 else: 3967 return 3968 3969 raise sa_exc.InvalidRequestError( 3970 "SQL expression, column, or mapped entity " 3971 "expected - got '%r'" % (column, ) 3972 ) 3973 elif not check_column: 3974 self._label_name = getattr(column, 'key', None) 3975 search_entities = True 3976 3977 self.type = type_ = column.type 3978 self.use_id_for_hash = not type_.hashable 3979 3980 # If the Column is unnamed, give it a 3981 # label() so that mutable column expressions 3982 # can be located in the result even 3983 # if the expression's identity has been changed 3984 # due to adaption. 3985 3986 if not column._label and not getattr(column, 'is_literal', False): 3987 column = column.label(self._label_name) 3988 3989 query._entities.append(self) 3990 3991 self.column = column 3992 self.froms = set() 3993 3994 # look for ORM entities represented within the 3995 # given expression. Try to count only entities 3996 # for columns whose FROM object is in the actual list 3997 # of FROMs for the overall expression - this helps 3998 # subqueries which were built from ORM constructs from 3999 # leaking out their entities into the main select construct 4000 self.actual_froms = actual_froms = set(column._from_objects) 4001 4002 if not search_entities: 4003 self.entity_zero = _entity 4004 if _entity: 4005 self.entities = [_entity] 4006 self.mapper = _entity.mapper 4007 else: 4008 self.entities = [] 4009 self.mapper = None 4010 self._from_entities = set(self.entities) 4011 else: 4012 all_elements = [ 4013 elem for elem in sql_util.surface_column_elements( 4014 column, include_scalar_selects=False) 4015 if 'parententity' in elem._annotations 4016 ] 4017 4018 self.entities = util.unique_list([ 4019 elem._annotations['parententity'] 4020 for elem in all_elements 4021 if 'parententity' in elem._annotations 4022 ]) 4023 4024 self._from_entities = set([ 4025 elem._annotations['parententity'] 4026 for elem in all_elements 4027 if 'parententity' in elem._annotations 4028 and actual_froms.intersection(elem._from_objects) 4029 ]) 4030 if self.entities: 4031 self.entity_zero = self.entities[0] 4032 self.mapper = self.entity_zero.mapper 4033 elif self.namespace is not None: 4034 self.entity_zero = self.namespace 4035 self.mapper = None 4036 else: 4037 self.entity_zero = None 4038 self.mapper = None 4039 4040 supports_single_entity = False 4041 4042 @property 4043 def entity_zero_or_selectable(self): 4044 if self.entity_zero is not None: 4045 return self.entity_zero 4046 elif self.actual_froms: 4047 return list(self.actual_froms)[0] 4048 else: 4049 return None 4050 4051 def adapt_to_selectable(self, query, sel): 4052 c = _ColumnEntity(query, sel.corresponding_column(self.column)) 4053 c._label_name = self._label_name 4054 c.entity_zero = self.entity_zero 4055 c.entities = self.entities 4056 4057 def setup_entity(self, ext_info, aliased_adapter): 4058 if 'selectable' not in self.__dict__: 4059 self.selectable = ext_info.selectable 4060 4061 if self.actual_froms.intersection(ext_info.selectable._from_objects): 4062 self.froms.add(ext_info.selectable) 4063 4064 def corresponds_to(self, entity): 4065 # TODO: just returning False here, 4066 # no tests fail 4067 if self.entity_zero is None: 4068 return False 4069 elif _is_aliased_class(entity): 4070 # TODO: polymorphic subclasses ? 4071 return entity is self.entity_zero 4072 else: 4073 return not _is_aliased_class(self.entity_zero) and \ 4074 entity.common_parent(self.entity_zero) 4075 4076 def row_processor(self, query, context, result): 4077 if ('fetch_column', self) in context.attributes: 4078 column = context.attributes[('fetch_column', self)] 4079 else: 4080 column = query._adapt_clause(self.column, False, True) 4081 4082 if context.adapter: 4083 column = context.adapter.columns[column] 4084 4085 getter = result._getter(column) 4086 return getter, self._label_name 4087 4088 def setup_context(self, query, context): 4089 column = query._adapt_clause(self.column, False, True) 4090 context.froms += tuple(self.froms) 4091 context.primary_columns.append(column) 4092 4093 context.attributes[('fetch_column', self)] = column 4094 4095 def __str__(self): 4096 return str(self.column) 4097 4098 4099class QueryContext(object): 4100 __slots__ = ( 4101 'multi_row_eager_loaders', 'adapter', 'froms', 'for_update', 4102 'query', 'session', 'autoflush', 'populate_existing', 4103 'invoke_all_eagers', 'version_check', 'refresh_state', 4104 'primary_columns', 'secondary_columns', 'eager_order_by', 4105 'eager_joins', 'create_eager_joins', 'propagate_options', 4106 'attributes', 'statement', 'from_clause', 'whereclause', 4107 'order_by', 'labels', '_for_update_arg', 'runid', 'partials' 4108 ) 4109 4110 def __init__(self, query): 4111 4112 if query._statement is not None: 4113 if isinstance(query._statement, expression.SelectBase) and \ 4114 not query._statement._textual and \ 4115 not query._statement.use_labels: 4116 self.statement = query._statement.apply_labels() 4117 else: 4118 self.statement = query._statement 4119 else: 4120 self.statement = None 4121 self.from_clause = query._from_obj 4122 self.whereclause = query._criterion 4123 self.order_by = query._order_by 4124 4125 self.multi_row_eager_loaders = False 4126 self.adapter = None 4127 self.froms = () 4128 self.for_update = None 4129 self.query = query 4130 self.session = query.session 4131 self.autoflush = query._autoflush 4132 self.populate_existing = query._populate_existing 4133 self.invoke_all_eagers = query._invoke_all_eagers 4134 self.version_check = query._version_check 4135 self.refresh_state = query._refresh_state 4136 self.primary_columns = [] 4137 self.secondary_columns = [] 4138 self.eager_order_by = [] 4139 self.eager_joins = {} 4140 self.create_eager_joins = [] 4141 self.propagate_options = set(o for o in query._with_options if 4142 o.propagate_to_loaders) 4143 self.attributes = query._attributes.copy() 4144 4145 4146class AliasOption(interfaces.MapperOption): 4147 4148 def __init__(self, alias): 4149 r"""Return a :class:`.MapperOption` that will indicate to the :class:`.Query` 4150 that the main table has been aliased. 4151 4152 This is a seldom-used option to suit the 4153 very rare case that :func:`.contains_eager` 4154 is being used in conjunction with a user-defined SELECT 4155 statement that aliases the parent table. E.g.:: 4156 4157 # define an aliased UNION called 'ulist' 4158 ulist = users.select(users.c.user_id==7).\ 4159 union(users.select(users.c.user_id>7)).\ 4160 alias('ulist') 4161 4162 # add on an eager load of "addresses" 4163 statement = ulist.outerjoin(addresses).\ 4164 select().apply_labels() 4165 4166 # create query, indicating "ulist" will be an 4167 # alias for the main table, "addresses" 4168 # property should be eager loaded 4169 query = session.query(User).options( 4170 contains_alias(ulist), 4171 contains_eager(User.addresses)) 4172 4173 # then get results via the statement 4174 results = query.from_statement(statement).all() 4175 4176 :param alias: is the string name of an alias, or a 4177 :class:`~.sql.expression.Alias` object representing 4178 the alias. 4179 4180 """ 4181 self.alias = alias 4182 4183 def process_query(self, query): 4184 if isinstance(self.alias, util.string_types): 4185 alias = query._mapper_zero().mapped_table.alias(self.alias) 4186 else: 4187 alias = self.alias 4188 query._from_obj_alias = sql_util.ColumnAdapter(alias) 4189