Hide keyboard shortcuts

Hot-keys on this page

r m x p   toggle line displays

j k   next/prev highlighted chunk

0   (zero) top of page

1   (one) first highlighted chunk

1# sql/selectable.py 

2# Copyright (C) 2005-2020 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 :class:`_expression.FromClause` class of SQL expression elements, 

9representing 

10SQL tables and derived rowsets. 

11 

12""" 

13 

14import collections 

15import itertools 

16import operator 

17from operator import attrgetter 

18 

19from sqlalchemy.sql.visitors import Visitable 

20from . import operators 

21from . import type_api 

22from .annotation import Annotated 

23from .base import _from_objects 

24from .base import _generative 

25from .base import ColumnCollection 

26from .base import ColumnSet 

27from .base import Executable 

28from .base import Generative 

29from .base import Immutable 

30from .elements import _anonymous_label 

31from .elements import _clause_element_as_expr 

32from .elements import _clone 

33from .elements import _cloned_difference 

34from .elements import _cloned_intersection 

35from .elements import _document_text_coercion 

36from .elements import _expand_cloned 

37from .elements import _interpret_as_column_or_from 

38from .elements import _literal_and_labels_as_label_reference 

39from .elements import _literal_as_label_reference 

40from .elements import _literal_as_text 

41from .elements import _no_text_coercion 

42from .elements import _select_iterables 

43from .elements import and_ 

44from .elements import BindParameter 

45from .elements import ClauseElement 

46from .elements import ClauseList 

47from .elements import Grouping 

48from .elements import literal_column 

49from .elements import True_ 

50from .elements import UnaryExpression 

51from .. import exc 

52from .. import inspection 

53from .. import util 

54 

55 

56def _interpret_as_from(element): 

57 insp = inspection.inspect(element, raiseerr=False) 

58 if insp is None: 

59 if isinstance(element, util.string_types): 

60 _no_text_coercion(element) 

61 try: 

62 return insp.selectable 

63 except AttributeError as err: 

64 util.raise_( 

65 exc.ArgumentError("FROM expression expected"), replace_context=err 

66 ) 

67 

68 

69def _interpret_as_select(element): 

70 element = _interpret_as_from(element) 

71 if isinstance(element, Alias): 

72 element = element.original 

73 if not isinstance(element, SelectBase): 

74 element = element.select() 

75 return element 

76 

77 

78class _OffsetLimitParam(BindParameter): 

79 @property 

80 def _limit_offset_value(self): 

81 return self.effective_value 

82 

83 

84def _offset_or_limit_clause(element, name=None, type_=None): 

85 """Convert the given value to an "offset or limit" clause. 

86 

87 This handles incoming integers and converts to an expression; if 

88 an expression is already given, it is passed through. 

89 

90 """ 

91 if element is None: 

92 return None 

93 elif hasattr(element, "__clause_element__"): 

94 return element.__clause_element__() 

95 elif isinstance(element, Visitable): 

96 return element 

97 else: 

98 value = util.asint(element) 

99 return _OffsetLimitParam(name, value, type_=type_, unique=True) 

100 

101 

102def _offset_or_limit_clause_asint(clause, attrname): 

103 """Convert the "offset or limit" clause of a select construct to an 

104 integer. 

105 

106 This is only possible if the value is stored as a simple bound parameter. 

107 Otherwise, a compilation error is raised. 

108 

109 """ 

110 if clause is None: 

111 return None 

112 try: 

113 value = clause._limit_offset_value 

114 except AttributeError as err: 

115 util.raise_( 

116 exc.CompileError( 

117 "This SELECT structure does not use a simple " 

118 "integer value for %s" % attrname 

119 ), 

120 replace_context=err, 

121 ) 

122 else: 

123 return util.asint(value) 

124 

125 

126def subquery(alias, *args, **kwargs): 

127 r"""Return an :class:`_expression.Alias` object derived 

128 from a :class:`_expression.Select`. 

129 

130 name 

131 alias name 

132 

133 \*args, \**kwargs 

134 

135 all other arguments are delivered to the 

136 :func:`select` function. 

137 

138 """ 

139 return Select(*args, **kwargs).alias(alias) 

140 

141 

142class Selectable(ClauseElement): 

143 """mark a class as being selectable""" 

144 

145 __visit_name__ = "selectable" 

146 

147 is_selectable = True 

148 

149 @property 

150 def selectable(self): 

151 return self 

152 

153 

154class HasPrefixes(object): 

155 _prefixes = () 

156 

157 @_generative 

158 @_document_text_coercion( 

159 "expr", 

160 ":meth:`_expression.HasPrefixes.prefix_with`", 

161 ":paramref:`.HasPrefixes.prefix_with.*expr`", 

162 ) 

163 def prefix_with(self, *expr, **kw): 

164 r"""Add one or more expressions following the statement keyword, i.e. 

165 SELECT, INSERT, UPDATE, or DELETE. Generative. 

166 

167 This is used to support backend-specific prefix keywords such as those 

168 provided by MySQL. 

169 

170 E.g.:: 

171 

172 stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql") 

173 

174 # MySQL 5.7 optimizer hints 

175 stmt = select([table]).prefix_with( 

176 "/*+ BKA(t1) */", dialect="mysql") 

177 

178 Multiple prefixes can be specified by multiple calls 

179 to :meth:`_expression.HasPrefixes.prefix_with`. 

180 

181 :param \*expr: textual or :class:`_expression.ClauseElement` 

182 construct which 

183 will be rendered following the INSERT, UPDATE, or DELETE 

184 keyword. 

185 :param \**kw: A single keyword 'dialect' is accepted. This is an 

186 optional string dialect name which will 

187 limit rendering of this prefix to only that dialect. 

188 

189 """ 

190 dialect = kw.pop("dialect", None) 

191 if kw: 

192 raise exc.ArgumentError( 

193 "Unsupported argument(s): %s" % ",".join(kw) 

194 ) 

195 self._setup_prefixes(expr, dialect) 

196 

197 def _setup_prefixes(self, prefixes, dialect=None): 

198 self._prefixes = self._prefixes + tuple( 

199 [ 

200 (_literal_as_text(p, allow_coercion_to_text=True), dialect) 

201 for p in prefixes 

202 ] 

203 ) 

204 

205 

206class HasSuffixes(object): 

207 _suffixes = () 

208 

209 @_generative 

210 @_document_text_coercion( 

211 "expr", 

212 ":meth:`_expression.HasSuffixes.suffix_with`", 

213 ":paramref:`.HasSuffixes.suffix_with.*expr`", 

214 ) 

215 def suffix_with(self, *expr, **kw): 

216 r"""Add one or more expressions following the statement as a whole. 

217 

218 This is used to support backend-specific suffix keywords on 

219 certain constructs. 

220 

221 E.g.:: 

222 

223 stmt = select([col1, col2]).cte().suffix_with( 

224 "cycle empno set y_cycle to 1 default 0", dialect="oracle") 

225 

226 Multiple suffixes can be specified by multiple calls 

227 to :meth:`_expression.HasSuffixes.suffix_with`. 

228 

229 :param \*expr: textual or :class:`_expression.ClauseElement` 

230 construct which 

231 will be rendered following the target clause. 

232 :param \**kw: A single keyword 'dialect' is accepted. This is an 

233 optional string dialect name which will 

234 limit rendering of this suffix to only that dialect. 

235 

236 """ 

237 dialect = kw.pop("dialect", None) 

238 if kw: 

239 raise exc.ArgumentError( 

240 "Unsupported argument(s): %s" % ",".join(kw) 

241 ) 

242 self._setup_suffixes(expr, dialect) 

243 

244 def _setup_suffixes(self, suffixes, dialect=None): 

245 self._suffixes = self._suffixes + tuple( 

246 [ 

247 (_literal_as_text(p, allow_coercion_to_text=True), dialect) 

248 for p in suffixes 

249 ] 

250 ) 

251 

252 

253class FromClause(Selectable): 

254 """Represent an element that can be used within the ``FROM`` 

255 clause of a ``SELECT`` statement. 

256 

257 The most common forms of :class:`_expression.FromClause` are the 

258 :class:`_schema.Table` and the :func:`_expression.select` constructs. Key 

259 features common to all :class:`_expression.FromClause` objects include: 

260 

261 * a :attr:`.c` collection, which provides per-name access to a collection 

262 of :class:`_expression.ColumnElement` objects. 

263 * a :attr:`.primary_key` attribute, which is a collection of all those 

264 :class:`_expression.ColumnElement` 

265 objects that indicate the ``primary_key`` flag. 

266 * Methods to generate various derivations of a "from" clause, including 

267 :meth:`_expression.FromClause.alias`, 

268 :meth:`_expression.FromClause.join`, 

269 :meth:`_expression.FromClause.select`. 

270 

271 

272 """ 

273 

274 __visit_name__ = "fromclause" 

275 named_with_column = False 

276 _hide_froms = [] 

277 

278 _is_join = False 

279 _is_select = False 

280 _is_from_container = False 

281 

282 _is_lateral = False 

283 

284 _textual = False 

285 """a marker that allows us to easily distinguish a :class:`.TextAsFrom` 

286 or similar object from other kinds of :class:`_expression.FromClause` 

287 objects.""" 

288 

289 schema = None 

290 """Define the 'schema' attribute for this :class:`_expression.FromClause`. 

291 

292 This is typically ``None`` for most objects except that of 

293 :class:`_schema.Table`, where it is taken as the value of the 

294 :paramref:`_schema.Table.schema` argument. 

295 

296 """ 

297 

298 def _translate_schema(self, effective_schema, map_): 

299 return effective_schema 

300 

301 _memoized_property = util.group_expirable_memoized_property(["_columns"]) 

302 

303 @util.deprecated( 

304 "1.1", 

305 message="The :meth:`.FromClause.count` method is deprecated, " 

306 "and will be removed in a future release. Please use the " 

307 ":class:`_functions.count` function available from the " 

308 ":attr:`.func` namespace.", 

309 ) 

310 @util.dependencies("sqlalchemy.sql.functions") 

311 def count(self, functions, whereclause=None, **params): 

312 """return a SELECT COUNT generated against this 

313 :class:`_expression.FromClause`. 

314 

315 .. seealso:: 

316 

317 :class:`_functions.count` 

318 

319 """ 

320 

321 if self.primary_key: 

322 col = list(self.primary_key)[0] 

323 else: 

324 col = list(self.columns)[0] 

325 return Select( 

326 [functions.func.count(col).label("tbl_row_count")], 

327 whereclause, 

328 from_obj=[self], 

329 **params 

330 ) 

331 

332 def select(self, whereclause=None, **params): 

333 """return a SELECT of this :class:`_expression.FromClause`. 

334 

335 .. seealso:: 

336 

337 :func:`_expression.select` - general purpose 

338 method which allows for arbitrary column lists. 

339 

340 """ 

341 

342 return Select([self], whereclause, **params) 

343 

344 def join(self, right, onclause=None, isouter=False, full=False): 

345 """Return a :class:`_expression.Join` from this 

346 :class:`_expression.FromClause` 

347 to another :class:`FromClause`. 

348 

349 E.g.:: 

350 

351 from sqlalchemy import join 

352 

353 j = user_table.join(address_table, 

354 user_table.c.id == address_table.c.user_id) 

355 stmt = select([user_table]).select_from(j) 

356 

357 would emit SQL along the lines of:: 

358 

359 SELECT user.id, user.name FROM user 

360 JOIN address ON user.id = address.user_id 

361 

362 :param right: the right side of the join; this is any 

363 :class:`_expression.FromClause` object such as a 

364 :class:`_schema.Table` object, and 

365 may also be a selectable-compatible object such as an ORM-mapped 

366 class. 

367 

368 :param onclause: a SQL expression representing the ON clause of the 

369 join. If left at ``None``, :meth:`_expression.FromClause.join` 

370 will attempt to 

371 join the two tables based on a foreign key relationship. 

372 

373 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN. 

374 

375 :param full: if True, render a FULL OUTER JOIN, instead of LEFT OUTER 

376 JOIN. Implies :paramref:`.FromClause.join.isouter`. 

377 

378 .. versionadded:: 1.1 

379 

380 .. seealso:: 

381 

382 :func:`_expression.join` - standalone function 

383 

384 :class:`_expression.Join` - the type of object produced 

385 

386 """ 

387 

388 return Join(self, right, onclause, isouter, full) 

389 

390 def outerjoin(self, right, onclause=None, full=False): 

391 """Return a :class:`_expression.Join` from this 

392 :class:`_expression.FromClause` 

393 to another :class:`FromClause`, with the "isouter" flag set to 

394 True. 

395 

396 E.g.:: 

397 

398 from sqlalchemy import outerjoin 

399 

400 j = user_table.outerjoin(address_table, 

401 user_table.c.id == address_table.c.user_id) 

402 

403 The above is equivalent to:: 

404 

405 j = user_table.join( 

406 address_table, 

407 user_table.c.id == address_table.c.user_id, 

408 isouter=True) 

409 

410 :param right: the right side of the join; this is any 

411 :class:`_expression.FromClause` object such as a 

412 :class:`_schema.Table` object, and 

413 may also be a selectable-compatible object such as an ORM-mapped 

414 class. 

415 

416 :param onclause: a SQL expression representing the ON clause of the 

417 join. If left at ``None``, :meth:`_expression.FromClause.join` 

418 will attempt to 

419 join the two tables based on a foreign key relationship. 

420 

421 :param full: if True, render a FULL OUTER JOIN, instead of 

422 LEFT OUTER JOIN. 

423 

424 .. versionadded:: 1.1 

425 

426 .. seealso:: 

427 

428 :meth:`_expression.FromClause.join` 

429 

430 :class:`_expression.Join` 

431 

432 """ 

433 

434 return Join(self, right, onclause, True, full) 

435 

436 def alias(self, name=None, flat=False): 

437 """return an alias of this :class:`_expression.FromClause`. 

438 

439 E.g.:: 

440 

441 a2 = some_table.alias('a2') 

442 

443 The above code creates an :class:`_expression.Alias` 

444 object which can be used 

445 as a FROM clause in any SELECT statement. 

446 

447 .. seealso:: 

448 

449 :ref:`core_tutorial_aliases` 

450 

451 :func:`_expression.alias` 

452 

453 """ 

454 

455 return Alias._construct(self, name) 

456 

457 def lateral(self, name=None): 

458 """Return a LATERAL alias of this :class:`_expression.FromClause`. 

459 

460 The return value is the :class:`_expression.Lateral` construct also 

461 provided by the top-level :func:`_expression.lateral` function. 

462 

463 .. versionadded:: 1.1 

464 

465 .. seealso:: 

466 

467 :ref:`lateral_selects` - overview of usage. 

468 

469 """ 

470 return Lateral._construct(self, name) 

471 

472 def tablesample(self, sampling, name=None, seed=None): 

473 """Return a TABLESAMPLE alias of this :class:`_expression.FromClause`. 

474 

475 The return value is the :class:`_expression.TableSample` 

476 construct also 

477 provided by the top-level :func:`_expression.tablesample` function. 

478 

479 .. versionadded:: 1.1 

480 

481 .. seealso:: 

482 

483 :func:`_expression.tablesample` - usage guidelines and parameters 

484 

485 """ 

486 return TableSample._construct(self, sampling, name, seed) 

487 

488 def is_derived_from(self, fromclause): 

489 """Return True if this FromClause is 'derived' from the given 

490 FromClause. 

491 

492 An example would be an Alias of a Table is derived from that Table. 

493 

494 """ 

495 # this is essentially an "identity" check in the base class. 

496 # Other constructs override this to traverse through 

497 # contained elements. 

498 return fromclause in self._cloned_set 

499 

500 def _is_lexical_equivalent(self, other): 

501 """Return True if this FromClause and the other represent 

502 the same lexical identity. 

503 

504 This tests if either one is a copy of the other, or 

505 if they are the same via annotation identity. 

506 

507 """ 

508 return self._cloned_set.intersection(other._cloned_set) 

509 

510 @util.dependencies("sqlalchemy.sql.util") 

511 def replace_selectable(self, sqlutil, old, alias): 

512 """replace all occurrences of FromClause 'old' with the given Alias 

513 object, returning a copy of this :class:`_expression.FromClause`. 

514 

515 """ 

516 

517 return sqlutil.ClauseAdapter(alias).traverse(self) 

518 

519 def correspond_on_equivalents(self, column, equivalents): 

520 """Return corresponding_column for the given column, or if None 

521 search for a match in the given dictionary. 

522 

523 """ 

524 col = self.corresponding_column(column, require_embedded=True) 

525 if col is None and col in equivalents: 

526 for equiv in equivalents[col]: 

527 nc = self.corresponding_column(equiv, require_embedded=True) 

528 if nc: 

529 return nc 

530 return col 

531 

532 def corresponding_column(self, column, require_embedded=False): 

533 """Given a :class:`_expression.ColumnElement`, return the exported 

534 :class:`_expression.ColumnElement` object from this 

535 :class:`expression.Selectable` 

536 which corresponds to that original 

537 :class:`~sqlalchemy.schema.Column` via a common ancestor 

538 column. 

539 

540 :param column: the target :class:`_expression.ColumnElement` 

541 to be matched 

542 

543 :param require_embedded: only return corresponding columns for 

544 the given :class:`_expression.ColumnElement`, if the given 

545 :class:`_expression.ColumnElement` 

546 is actually present within a sub-element 

547 of this :class:`_expression.FromClause`. 

548 Normally the column will match if 

549 it merely shares a common ancestor with one of the exported 

550 columns of this :class:`_expression.FromClause`. 

551 

552 """ 

553 

554 def embedded(expanded_proxy_set, target_set): 

555 for t in target_set.difference(expanded_proxy_set): 

556 if not set(_expand_cloned([t])).intersection( 

557 expanded_proxy_set 

558 ): 

559 return False 

560 return True 

561 

562 # don't dig around if the column is locally present 

563 if self.c.contains_column(column): 

564 return column 

565 col, intersect = None, None 

566 target_set = column.proxy_set 

567 cols = self.c._all_columns 

568 for c in cols: 

569 expanded_proxy_set = set(_expand_cloned(c.proxy_set)) 

570 i = target_set.intersection(expanded_proxy_set) 

571 if i and ( 

572 not require_embedded 

573 or embedded(expanded_proxy_set, target_set) 

574 ): 

575 if col is None: 

576 

577 # no corresponding column yet, pick this one. 

578 

579 col, intersect = c, i 

580 elif len(i) > len(intersect): 

581 

582 # 'c' has a larger field of correspondence than 

583 # 'col'. i.e. selectable.c.a1_x->a1.c.x->table.c.x 

584 # matches a1.c.x->table.c.x better than 

585 # selectable.c.x->table.c.x does. 

586 

587 col, intersect = c, i 

588 elif i == intersect: 

589 

590 # they have the same field of correspondence. see 

591 # which proxy_set has fewer columns in it, which 

592 # indicates a closer relationship with the root 

593 # column. Also take into account the "weight" 

594 # attribute which CompoundSelect() uses to give 

595 # higher precedence to columns based on vertical 

596 # position in the compound statement, and discard 

597 # columns that have no reference to the target 

598 # column (also occurs with CompoundSelect) 

599 

600 col_distance = util.reduce( 

601 operator.add, 

602 [ 

603 sc._annotations.get("weight", 1) 

604 for sc in col._uncached_proxy_set() 

605 if sc.shares_lineage(column) 

606 ], 

607 ) 

608 c_distance = util.reduce( 

609 operator.add, 

610 [ 

611 sc._annotations.get("weight", 1) 

612 for sc in c._uncached_proxy_set() 

613 if sc.shares_lineage(column) 

614 ], 

615 ) 

616 if c_distance < col_distance: 

617 col, intersect = c, i 

618 return col 

619 

620 @property 

621 def description(self): 

622 """a brief description of this FromClause. 

623 

624 Used primarily for error message formatting. 

625 

626 """ 

627 return getattr(self, "name", self.__class__.__name__ + " object") 

628 

629 def _reset_exported(self): 

630 """delete memoized collections when a FromClause is cloned.""" 

631 

632 self._memoized_property.expire_instance(self) 

633 

634 @_memoized_property 

635 def columns(self): 

636 """A named-based collection of :class:`_expression.ColumnElement` 

637 objects 

638 maintained by this :class:`_expression.FromClause`. 

639 

640 The :attr:`.columns`, or :attr:`.c` collection, is the gateway 

641 to the construction of SQL expressions using table-bound or 

642 other selectable-bound columns:: 

643 

644 select([mytable]).where(mytable.c.somecolumn == 5) 

645 

646 """ 

647 

648 if "_columns" not in self.__dict__: 

649 self._init_collections() 

650 self._populate_column_collection() 

651 return self._columns.as_immutable() 

652 

653 @_memoized_property 

654 def primary_key(self): 

655 """Return the collection of Column objects which comprise the 

656 primary key of this FromClause.""" 

657 

658 self._init_collections() 

659 self._populate_column_collection() 

660 return self.primary_key 

661 

662 @_memoized_property 

663 def foreign_keys(self): 

664 """Return the collection of ForeignKey objects which this 

665 FromClause references.""" 

666 

667 self._init_collections() 

668 self._populate_column_collection() 

669 return self.foreign_keys 

670 

671 c = property( 

672 attrgetter("columns"), 

673 doc="An alias for the :attr:`.columns` attribute.", 

674 ) 

675 _select_iterable = property(attrgetter("columns")) 

676 

677 def _init_collections(self): 

678 assert "_columns" not in self.__dict__ 

679 assert "primary_key" not in self.__dict__ 

680 assert "foreign_keys" not in self.__dict__ 

681 

682 self._columns = ColumnCollection() 

683 self.primary_key = ColumnSet() 

684 self.foreign_keys = set() 

685 

686 @property 

687 def _cols_populated(self): 

688 return "_columns" in self.__dict__ 

689 

690 def _populate_column_collection(self): 

691 """Called on subclasses to establish the .c collection. 

692 

693 Each implementation has a different way of establishing 

694 this collection. 

695 

696 """ 

697 

698 def _refresh_for_new_column(self, column): 

699 """Given a column added to the .c collection of an underlying 

700 selectable, produce the local version of that column, assuming this 

701 selectable ultimately should proxy this column. 

702 

703 this is used to "ping" a derived selectable to add a new column 

704 to its .c. collection when a Column has been added to one of the 

705 Table objects it ultimtely derives from. 

706 

707 If the given selectable hasn't populated its .c. collection yet, 

708 it should at least pass on the message to the contained selectables, 

709 but it will return None. 

710 

711 This method is currently used by Declarative to allow Table 

712 columns to be added to a partially constructed inheritance 

713 mapping that may have already produced joins. The method 

714 isn't public right now, as the full span of implications 

715 and/or caveats aren't yet clear. 

716 

717 It's also possible that this functionality could be invoked by 

718 default via an event, which would require that 

719 selectables maintain a weak referencing collection of all 

720 derivations. 

721 

722 """ 

723 if not self._cols_populated: 

724 return None 

725 elif column.key in self.columns and self.columns[column.key] is column: 

726 return column 

727 else: 

728 return None 

729 

730 

731class Join(FromClause): 

732 """represent a ``JOIN`` construct between two 

733 :class:`_expression.FromClause` 

734 elements. 

735 

736 The public constructor function for :class:`_expression.Join` 

737 is the module-level 

738 :func:`_expression.join()` function, as well as the 

739 :meth:`_expression.FromClause.join` method 

740 of any :class:`_expression.FromClause` (e.g. such as 

741 :class:`_schema.Table`). 

742 

743 .. seealso:: 

744 

745 :func:`_expression.join` 

746 

747 :meth:`_expression.FromClause.join` 

748 

749 """ 

750 

751 __visit_name__ = "join" 

752 

753 _is_join = True 

754 

755 def __init__(self, left, right, onclause=None, isouter=False, full=False): 

756 """Construct a new :class:`_expression.Join`. 

757 

758 The usual entrypoint here is the :func:`_expression.join` 

759 function or the :meth:`_expression.FromClause.join` method of any 

760 :class:`_expression.FromClause` object. 

761 

762 """ 

763 self.left = _interpret_as_from(left) 

764 self.right = _interpret_as_from(right).self_group() 

765 

766 if onclause is None: 

767 self.onclause = self._match_primaries(self.left, self.right) 

768 else: 

769 self.onclause = onclause 

770 

771 self.isouter = isouter 

772 self.full = full 

773 

774 @classmethod 

775 def _create_outerjoin(cls, left, right, onclause=None, full=False): 

776 """Return an ``OUTER JOIN`` clause element. 

777 

778 The returned object is an instance of :class:`_expression.Join`. 

779 

780 Similar functionality is also available via the 

781 :meth:`_expression.FromClause.outerjoin()` method on any 

782 :class:`_expression.FromClause`. 

783 

784 :param left: The left side of the join. 

785 

786 :param right: The right side of the join. 

787 

788 :param onclause: Optional criterion for the ``ON`` clause, is 

789 derived from foreign key relationships established between 

790 left and right otherwise. 

791 

792 To chain joins together, use the :meth:`_expression.FromClause.join` 

793 or 

794 :meth:`_expression.FromClause.outerjoin` methods on the resulting 

795 :class:`_expression.Join` object. 

796 

797 """ 

798 return cls(left, right, onclause, isouter=True, full=full) 

799 

800 @classmethod 

801 def _create_join( 

802 cls, left, right, onclause=None, isouter=False, full=False 

803 ): 

804 """Produce a :class:`_expression.Join` object, given two 

805 :class:`_expression.FromClause` 

806 expressions. 

807 

808 E.g.:: 

809 

810 j = join(user_table, address_table, 

811 user_table.c.id == address_table.c.user_id) 

812 stmt = select([user_table]).select_from(j) 

813 

814 would emit SQL along the lines of:: 

815 

816 SELECT user.id, user.name FROM user 

817 JOIN address ON user.id = address.user_id 

818 

819 Similar functionality is available given any 

820 :class:`_expression.FromClause` object (e.g. such as a 

821 :class:`_schema.Table`) using 

822 the :meth:`_expression.FromClause.join` method. 

823 

824 :param left: The left side of the join. 

825 

826 :param right: the right side of the join; this is any 

827 :class:`_expression.FromClause` object such as a 

828 :class:`_schema.Table` object, and 

829 may also be a selectable-compatible object such as an ORM-mapped 

830 class. 

831 

832 :param onclause: a SQL expression representing the ON clause of the 

833 join. If left at ``None``, :meth:`_expression.FromClause.join` 

834 will attempt to 

835 join the two tables based on a foreign key relationship. 

836 

837 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN. 

838 

839 :param full: if True, render a FULL OUTER JOIN, instead of JOIN. 

840 

841 .. versionadded:: 1.1 

842 

843 .. seealso:: 

844 

845 :meth:`_expression.FromClause.join` - method form, 

846 based on a given left side 

847 

848 :class:`_expression.Join` - the type of object produced 

849 

850 """ 

851 

852 return cls(left, right, onclause, isouter, full) 

853 

854 @property 

855 def description(self): 

856 return "Join object on %s(%d) and %s(%d)" % ( 

857 self.left.description, 

858 id(self.left), 

859 self.right.description, 

860 id(self.right), 

861 ) 

862 

863 def is_derived_from(self, fromclause): 

864 return ( 

865 fromclause is self 

866 or self.left.is_derived_from(fromclause) 

867 or self.right.is_derived_from(fromclause) 

868 ) 

869 

870 def self_group(self, against=None): 

871 return FromGrouping(self) 

872 

873 @util.dependencies("sqlalchemy.sql.util") 

874 def _populate_column_collection(self, sqlutil): 

875 columns = [c for c in self.left.columns] + [ 

876 c for c in self.right.columns 

877 ] 

878 

879 self.primary_key.extend( 

880 sqlutil.reduce_columns( 

881 (c for c in columns if c.primary_key), self.onclause 

882 ) 

883 ) 

884 self._columns.update((col._label, col) for col in columns) 

885 self.foreign_keys.update( 

886 itertools.chain(*[col.foreign_keys for col in columns]) 

887 ) 

888 

889 def _refresh_for_new_column(self, column): 

890 col = self.left._refresh_for_new_column(column) 

891 if col is None: 

892 col = self.right._refresh_for_new_column(column) 

893 if col is not None: 

894 if self._cols_populated: 

895 self._columns[col._label] = col 

896 self.foreign_keys.update(col.foreign_keys) 

897 if col.primary_key: 

898 self.primary_key.add(col) 

899 return col 

900 return None 

901 

902 def _copy_internals(self, clone=_clone, **kw): 

903 self._reset_exported() 

904 self.left = clone(self.left, **kw) 

905 self.right = clone(self.right, **kw) 

906 self.onclause = clone(self.onclause, **kw) 

907 

908 def get_children(self, **kwargs): 

909 return self.left, self.right, self.onclause 

910 

911 def _match_primaries(self, left, right): 

912 if isinstance(left, Join): 

913 left_right = left.right 

914 else: 

915 left_right = None 

916 return self._join_condition(left, right, a_subset=left_right) 

917 

918 @classmethod 

919 @util.deprecated_params( 

920 ignore_nonexistent_tables=( 

921 "0.9", 

922 "The :paramref:`.join_condition.ignore_nonexistent_tables` " 

923 "parameter is deprecated and will be removed in a future " 

924 "release. Tables outside of the two tables being handled " 

925 "are no longer considered.", 

926 ) 

927 ) 

928 def _join_condition( 

929 cls, 

930 a, 

931 b, 

932 ignore_nonexistent_tables=False, 

933 a_subset=None, 

934 consider_as_foreign_keys=None, 

935 ): 

936 """create a join condition between two tables or selectables. 

937 

938 e.g.:: 

939 

940 join_condition(tablea, tableb) 

941 

942 would produce an expression along the lines of:: 

943 

944 tablea.c.id==tableb.c.tablea_id 

945 

946 The join is determined based on the foreign key relationships 

947 between the two selectables. If there are multiple ways 

948 to join, or no way to join, an error is raised. 

949 

950 :param ignore_nonexistent_tables: unused - tables outside of the 

951 two tables being handled are not considered. 

952 

953 :param a_subset: An optional expression that is a sub-component 

954 of ``a``. An attempt will be made to join to just this sub-component 

955 first before looking at the full ``a`` construct, and if found 

956 will be successful even if there are other ways to join to ``a``. 

957 This allows the "right side" of a join to be passed thereby 

958 providing a "natural join". 

959 

960 """ 

961 constraints = cls._joincond_scan_left_right( 

962 a, a_subset, b, consider_as_foreign_keys 

963 ) 

964 

965 if len(constraints) > 1: 

966 cls._joincond_trim_constraints( 

967 a, b, constraints, consider_as_foreign_keys 

968 ) 

969 

970 if len(constraints) == 0: 

971 if isinstance(b, FromGrouping): 

972 hint = ( 

973 " Perhaps you meant to convert the right side to a " 

974 "subquery using alias()?" 

975 ) 

976 else: 

977 hint = "" 

978 raise exc.NoForeignKeysError( 

979 "Can't find any foreign key relationships " 

980 "between '%s' and '%s'.%s" 

981 % (a.description, b.description, hint) 

982 ) 

983 

984 crit = [(x == y) for x, y in list(constraints.values())[0]] 

985 if len(crit) == 1: 

986 return crit[0] 

987 else: 

988 return and_(*crit) 

989 

990 @classmethod 

991 def _can_join(cls, left, right, consider_as_foreign_keys=None): 

992 if isinstance(left, Join): 

993 left_right = left.right 

994 else: 

995 left_right = None 

996 

997 constraints = cls._joincond_scan_left_right( 

998 a=left, 

999 b=right, 

1000 a_subset=left_right, 

1001 consider_as_foreign_keys=consider_as_foreign_keys, 

1002 ) 

1003 

1004 return bool(constraints) 

1005 

1006 @classmethod 

1007 def _joincond_scan_left_right( 

1008 cls, a, a_subset, b, consider_as_foreign_keys 

1009 ): 

1010 constraints = collections.defaultdict(list) 

1011 

1012 for left in (a_subset, a): 

1013 if left is None: 

1014 continue 

1015 for fk in sorted( 

1016 b.foreign_keys, key=lambda fk: fk.parent._creation_order 

1017 ): 

1018 if ( 

1019 consider_as_foreign_keys is not None 

1020 and fk.parent not in consider_as_foreign_keys 

1021 ): 

1022 continue 

1023 try: 

1024 col = fk.get_referent(left) 

1025 except exc.NoReferenceError as nrte: 

1026 if nrte.table_name == left.name: 

1027 raise 

1028 else: 

1029 continue 

1030 

1031 if col is not None: 

1032 constraints[fk.constraint].append((col, fk.parent)) 

1033 if left is not b: 

1034 for fk in sorted( 

1035 left.foreign_keys, key=lambda fk: fk.parent._creation_order 

1036 ): 

1037 if ( 

1038 consider_as_foreign_keys is not None 

1039 and fk.parent not in consider_as_foreign_keys 

1040 ): 

1041 continue 

1042 try: 

1043 col = fk.get_referent(b) 

1044 except exc.NoReferenceError as nrte: 

1045 if nrte.table_name == b.name: 

1046 raise 

1047 else: 

1048 continue 

1049 

1050 if col is not None: 

1051 constraints[fk.constraint].append((col, fk.parent)) 

1052 if constraints: 

1053 break 

1054 return constraints 

1055 

1056 @classmethod 

1057 def _joincond_trim_constraints( 

1058 cls, a, b, constraints, consider_as_foreign_keys 

1059 ): 

1060 # more than one constraint matched. narrow down the list 

1061 # to include just those FKCs that match exactly to 

1062 # "consider_as_foreign_keys". 

1063 if consider_as_foreign_keys: 

1064 for const in list(constraints): 

1065 if set(f.parent for f in const.elements) != set( 

1066 consider_as_foreign_keys 

1067 ): 

1068 del constraints[const] 

1069 

1070 # if still multiple constraints, but 

1071 # they all refer to the exact same end result, use it. 

1072 if len(constraints) > 1: 

1073 dedupe = set(tuple(crit) for crit in constraints.values()) 

1074 if len(dedupe) == 1: 

1075 key = list(constraints)[0] 

1076 constraints = {key: constraints[key]} 

1077 

1078 if len(constraints) != 1: 

1079 raise exc.AmbiguousForeignKeysError( 

1080 "Can't determine join between '%s' and '%s'; " 

1081 "tables have more than one foreign key " 

1082 "constraint relationship between them. " 

1083 "Please specify the 'onclause' of this " 

1084 "join explicitly." % (a.description, b.description) 

1085 ) 

1086 

1087 def select(self, whereclause=None, **kwargs): 

1088 r"""Create a :class:`_expression.Select` from this 

1089 :class:`_expression.Join`. 

1090 

1091 The equivalent long-hand form, given a :class:`_expression.Join` 

1092 object 

1093 ``j``, is:: 

1094 

1095 from sqlalchemy import select 

1096 j = select([j.left, j.right], **kw).\ 

1097 where(whereclause).\ 

1098 select_from(j) 

1099 

1100 :param whereclause: the WHERE criterion that will be sent to 

1101 the :func:`select()` function 

1102 

1103 :param \**kwargs: all other kwargs are sent to the 

1104 underlying :func:`select()` function. 

1105 

1106 """ 

1107 collist = [self.left, self.right] 

1108 

1109 return Select(collist, whereclause, from_obj=[self], **kwargs) 

1110 

1111 @property 

1112 def bind(self): 

1113 return self.left.bind or self.right.bind 

1114 

1115 @util.dependencies("sqlalchemy.sql.util") 

1116 def alias(self, sqlutil, name=None, flat=False): 

1117 r"""return an alias of this :class:`_expression.Join`. 

1118 

1119 The default behavior here is to first produce a SELECT 

1120 construct from this :class:`_expression.Join`, then to produce an 

1121 :class:`_expression.Alias` from that. So given a join of the form:: 

1122 

1123 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id) 

1124 

1125 The JOIN by itself would look like:: 

1126 

1127 table_a JOIN table_b ON table_a.id = table_b.a_id 

1128 

1129 Whereas the alias of the above, ``j.alias()``, would in a 

1130 SELECT context look like:: 

1131 

1132 (SELECT table_a.id AS table_a_id, table_b.id AS table_b_id, 

1133 table_b.a_id AS table_b_a_id 

1134 FROM table_a 

1135 JOIN table_b ON table_a.id = table_b.a_id) AS anon_1 

1136 

1137 The equivalent long-hand form, given a :class:`_expression.Join` 

1138 object 

1139 ``j``, is:: 

1140 

1141 from sqlalchemy import select, alias 

1142 j = alias( 

1143 select([j.left, j.right]).\ 

1144 select_from(j).\ 

1145 with_labels(True).\ 

1146 correlate(False), 

1147 name=name 

1148 ) 

1149 

1150 The selectable produced by :meth:`_expression.Join.alias` 

1151 features the same 

1152 columns as that of the two individual selectables presented under 

1153 a single name - the individual columns are "auto-labeled", meaning 

1154 the ``.c.`` collection of the resulting :class:`_expression.Alias` 

1155 represents 

1156 the names of the individual columns using a 

1157 ``<tablename>_<columname>`` scheme:: 

1158 

1159 j.c.table_a_id 

1160 j.c.table_b_a_id 

1161 

1162 :meth:`_expression.Join.alias` also features an alternate 

1163 option for aliasing joins which produces no enclosing SELECT and 

1164 does not normally apply labels to the column names. The 

1165 ``flat=True`` option will call :meth:`_expression.FromClause.alias` 

1166 against the left and right sides individually. 

1167 Using this option, no new ``SELECT`` is produced; 

1168 we instead, from a construct as below:: 

1169 

1170 j = table_a.join(table_b, table_a.c.id == table_b.c.a_id) 

1171 j = j.alias(flat=True) 

1172 

1173 we get a result like this:: 

1174 

1175 table_a AS table_a_1 JOIN table_b AS table_b_1 ON 

1176 table_a_1.id = table_b_1.a_id 

1177 

1178 The ``flat=True`` argument is also propagated to the contained 

1179 selectables, so that a composite join such as:: 

1180 

1181 j = table_a.join( 

1182 table_b.join(table_c, 

1183 table_b.c.id == table_c.c.b_id), 

1184 table_b.c.a_id == table_a.c.id 

1185 ).alias(flat=True) 

1186 

1187 Will produce an expression like:: 

1188 

1189 table_a AS table_a_1 JOIN ( 

1190 table_b AS table_b_1 JOIN table_c AS table_c_1 

1191 ON table_b_1.id = table_c_1.b_id 

1192 ) ON table_a_1.id = table_b_1.a_id 

1193 

1194 The standalone :func:`_expression.alias` function as well as the 

1195 base :meth:`_expression.FromClause.alias` 

1196 method also support the ``flat=True`` 

1197 argument as a no-op, so that the argument can be passed to the 

1198 ``alias()`` method of any selectable. 

1199 

1200 .. versionadded:: 0.9.0 Added the ``flat=True`` option to create 

1201 "aliases" of joins without enclosing inside of a SELECT 

1202 subquery. 

1203 

1204 :param name: name given to the alias. 

1205 

1206 :param flat: if True, produce an alias of the left and right 

1207 sides of this :class:`_expression.Join` and return the join of those 

1208 two selectables. This produces join expression that does not 

1209 include an enclosing SELECT. 

1210 

1211 .. versionadded:: 0.9.0 

1212 

1213 .. seealso:: 

1214 

1215 :ref:`core_tutorial_aliases` 

1216 

1217 :func:`_expression.alias` 

1218 

1219 """ 

1220 if flat: 

1221 assert name is None, "Can't send name argument with flat" 

1222 left_a, right_a = ( 

1223 self.left.alias(flat=True), 

1224 self.right.alias(flat=True), 

1225 ) 

1226 adapter = sqlutil.ClauseAdapter(left_a).chain( 

1227 sqlutil.ClauseAdapter(right_a) 

1228 ) 

1229 

1230 return left_a.join( 

1231 right_a, 

1232 adapter.traverse(self.onclause), 

1233 isouter=self.isouter, 

1234 full=self.full, 

1235 ) 

1236 else: 

1237 return self.select(use_labels=True, correlate=False).alias(name) 

1238 

1239 @property 

1240 def _hide_froms(self): 

1241 return itertools.chain( 

1242 *[_from_objects(x.left, x.right) for x in self._cloned_set] 

1243 ) 

1244 

1245 @property 

1246 def _from_objects(self): 

1247 return ( 

1248 [self] 

1249 + self.onclause._from_objects 

1250 + self.left._from_objects 

1251 + self.right._from_objects 

1252 ) 

1253 

1254 

1255class Alias(FromClause): 

1256 """Represents an table or selectable alias (AS). 

1257 

1258 Represents an alias, as typically applied to any table or 

1259 sub-select within a SQL statement using the ``AS`` keyword (or 

1260 without the keyword on certain databases such as Oracle). 

1261 

1262 This object is constructed from the :func:`_expression.alias` module 

1263 level function as well as the :meth:`_expression.FromClause.alias` 

1264 method available 

1265 on all :class:`_expression.FromClause` subclasses. 

1266 

1267 """ 

1268 

1269 __visit_name__ = "alias" 

1270 named_with_column = True 

1271 

1272 _is_from_container = True 

1273 

1274 def __init__(self, *arg, **kw): 

1275 raise NotImplementedError( 

1276 "The %s class is not intended to be constructed " 

1277 "directly. Please use the %s() standalone " 

1278 "function or the %s() method available from appropriate " 

1279 "selectable objects." 

1280 % ( 

1281 self.__class__.__name__, 

1282 self.__class__.__name__.lower(), 

1283 self.__class__.__name__.lower(), 

1284 ) 

1285 ) 

1286 

1287 @classmethod 

1288 def _construct(cls, *arg, **kw): 

1289 obj = cls.__new__(cls) 

1290 obj._init(*arg, **kw) 

1291 return obj 

1292 

1293 @classmethod 

1294 def _factory(cls, selectable, name=None, flat=False): 

1295 """Return an :class:`_expression.Alias` object. 

1296 

1297 An :class:`_expression.Alias` represents any 

1298 :class:`_expression.FromClause` 

1299 with an alternate name assigned within SQL, typically using the ``AS`` 

1300 clause when generated, e.g. ``SELECT * FROM table AS aliasname``. 

1301 

1302 Similar functionality is available via the 

1303 :meth:`_expression.FromClause.alias` method 

1304 available on all :class:`_expression.FromClause` subclasses. 

1305 In terms of a 

1306 SELECT object as generated from the :func:`_expression.select` 

1307 function, the 

1308 :meth:`_expression.SelectBase.alias` method returns an 

1309 :class:`_expression.Alias` or 

1310 similar object which represents a named, parenthesized subquery. 

1311 

1312 When an :class:`_expression.Alias` is created from a 

1313 :class:`_schema.Table` object, 

1314 this has the effect of the table being rendered 

1315 as ``tablename AS aliasname`` in a SELECT statement. 

1316 

1317 For :func:`_expression.select` objects, 

1318 the effect is that of creating a named 

1319 subquery, i.e. ``(select ...) AS aliasname``. 

1320 

1321 The ``name`` parameter is optional, and provides the name 

1322 to use in the rendered SQL. If blank, an "anonymous" name 

1323 will be deterministically generated at compile time. 

1324 Deterministic means the name is guaranteed to be unique against 

1325 other constructs used in the same statement, and will also be the 

1326 same name for each successive compilation of the same statement 

1327 object. 

1328 

1329 :param selectable: any :class:`_expression.FromClause` subclass, 

1330 such as a table, select statement, etc. 

1331 

1332 :param name: string name to be assigned as the alias. 

1333 If ``None``, a name will be deterministically generated 

1334 at compile time. 

1335 

1336 :param flat: Will be passed through to if the given selectable 

1337 is an instance of :class:`_expression.Join` - see 

1338 :meth:`_expression.Join.alias` 

1339 for details. 

1340 

1341 .. versionadded:: 0.9.0 

1342 

1343 """ 

1344 return _interpret_as_from(selectable).alias(name=name, flat=flat) 

1345 

1346 def _init(self, selectable, name=None): 

1347 baseselectable = selectable 

1348 while isinstance(baseselectable, Alias): 

1349 baseselectable = baseselectable.element 

1350 self.original = baseselectable 

1351 self.supports_execution = baseselectable.supports_execution 

1352 if self.supports_execution: 

1353 self._execution_options = baseselectable._execution_options 

1354 self.element = selectable 

1355 if name is None: 

1356 if self.original.named_with_column: 

1357 name = getattr(self.original, "name", None) 

1358 name = _anonymous_label("%%(%d %s)s" % (id(self), name or "anon")) 

1359 self.name = name 

1360 

1361 def self_group(self, against=None): 

1362 if ( 

1363 isinstance(against, CompoundSelect) 

1364 and isinstance(self.original, Select) 

1365 and self.original._needs_parens_for_grouping() 

1366 ): 

1367 return FromGrouping(self) 

1368 

1369 return super(Alias, self).self_group(against=against) 

1370 

1371 @property 

1372 def description(self): 

1373 if util.py3k: 

1374 return self.name 

1375 else: 

1376 return self.name.encode("ascii", "backslashreplace") 

1377 

1378 def as_scalar(self): 

1379 try: 

1380 return self.element.as_scalar() 

1381 except AttributeError as err: 

1382 util.raise_( 

1383 AttributeError( 

1384 "Element %s does not support " 

1385 "'as_scalar()'" % self.element 

1386 ), 

1387 replace_context=err, 

1388 ) 

1389 

1390 def is_derived_from(self, fromclause): 

1391 if fromclause in self._cloned_set: 

1392 return True 

1393 return self.element.is_derived_from(fromclause) 

1394 

1395 def _populate_column_collection(self): 

1396 for col in self.element.columns._all_columns: 

1397 col._make_proxy(self) 

1398 

1399 def _refresh_for_new_column(self, column): 

1400 col = self.element._refresh_for_new_column(column) 

1401 if col is not None: 

1402 if not self._cols_populated: 

1403 return None 

1404 else: 

1405 return col._make_proxy(self) 

1406 else: 

1407 return None 

1408 

1409 def _copy_internals(self, clone=_clone, **kw): 

1410 # don't apply anything to an aliased Table 

1411 # for now. May want to drive this from 

1412 # the given **kw. 

1413 if isinstance(self.element, TableClause): 

1414 return 

1415 self._reset_exported() 

1416 self.element = clone(self.element, **kw) 

1417 baseselectable = self.element 

1418 while isinstance(baseselectable, Alias): 

1419 baseselectable = baseselectable.element 

1420 self.original = baseselectable 

1421 

1422 def get_children(self, column_collections=True, **kw): 

1423 if column_collections: 

1424 for c in self.c: 

1425 yield c 

1426 yield self.element 

1427 

1428 @property 

1429 def _from_objects(self): 

1430 return [self] 

1431 

1432 @property 

1433 def bind(self): 

1434 return self.element.bind 

1435 

1436 

1437class Lateral(Alias): 

1438 """Represent a LATERAL subquery. 

1439 

1440 This object is constructed from the :func:`_expression.lateral` module 

1441 level function as well as the :meth:`_expression.FromClause.lateral` 

1442 method available 

1443 on all :class:`_expression.FromClause` subclasses. 

1444 

1445 While LATERAL is part of the SQL standard, currently only more recent 

1446 PostgreSQL versions provide support for this keyword. 

1447 

1448 .. versionadded:: 1.1 

1449 

1450 .. seealso:: 

1451 

1452 :ref:`lateral_selects` - overview of usage. 

1453 

1454 """ 

1455 

1456 __visit_name__ = "lateral" 

1457 _is_lateral = True 

1458 

1459 @classmethod 

1460 def _factory(cls, selectable, name=None): 

1461 """Return a :class:`_expression.Lateral` object. 

1462 

1463 :class:`_expression.Lateral` is an :class:`_expression.Alias` 

1464 subclass that represents 

1465 a subquery with the LATERAL keyword applied to it. 

1466 

1467 The special behavior of a LATERAL subquery is that it appears in the 

1468 FROM clause of an enclosing SELECT, but may correlate to other 

1469 FROM clauses of that SELECT. It is a special case of subquery 

1470 only supported by a small number of backends, currently more recent 

1471 PostgreSQL versions. 

1472 

1473 .. versionadded:: 1.1 

1474 

1475 .. seealso:: 

1476 

1477 :ref:`lateral_selects` - overview of usage. 

1478 

1479 """ 

1480 return _interpret_as_from(selectable).lateral(name=name) 

1481 

1482 

1483class TableSample(Alias): 

1484 """Represent a TABLESAMPLE clause. 

1485 

1486 This object is constructed from the :func:`_expression.tablesample` module 

1487 level function as well as the :meth:`_expression.FromClause.tablesample` 

1488 method 

1489 available on all :class:`_expression.FromClause` subclasses. 

1490 

1491 .. versionadded:: 1.1 

1492 

1493 .. seealso:: 

1494 

1495 :func:`_expression.tablesample` 

1496 

1497 """ 

1498 

1499 __visit_name__ = "tablesample" 

1500 

1501 @classmethod 

1502 def _factory(cls, selectable, sampling, name=None, seed=None): 

1503 """Return a :class:`_expression.TableSample` object. 

1504 

1505 :class:`_expression.TableSample` is an :class:`_expression.Alias` 

1506 subclass that represents 

1507 a table with the TABLESAMPLE clause applied to it. 

1508 :func:`_expression.tablesample` 

1509 is also available from the :class:`_expression.FromClause` 

1510 class via the 

1511 :meth:`_expression.FromClause.tablesample` method. 

1512 

1513 The TABLESAMPLE clause allows selecting a randomly selected approximate 

1514 percentage of rows from a table. It supports multiple sampling methods, 

1515 most commonly BERNOULLI and SYSTEM. 

1516 

1517 e.g.:: 

1518 

1519 from sqlalchemy import func 

1520 

1521 selectable = people.tablesample( 

1522 func.bernoulli(1), 

1523 name='alias', 

1524 seed=func.random()) 

1525 stmt = select([selectable.c.people_id]) 

1526 

1527 Assuming ``people`` with a column ``people_id``, the above 

1528 statement would render as:: 

1529 

1530 SELECT alias.people_id FROM 

1531 people AS alias TABLESAMPLE bernoulli(:bernoulli_1) 

1532 REPEATABLE (random()) 

1533 

1534 .. versionadded:: 1.1 

1535 

1536 :param sampling: a ``float`` percentage between 0 and 100 or 

1537 :class:`_functions.Function`. 

1538 

1539 :param name: optional alias name 

1540 

1541 :param seed: any real-valued SQL expression. When specified, the 

1542 REPEATABLE sub-clause is also rendered. 

1543 

1544 """ 

1545 return _interpret_as_from(selectable).tablesample( 

1546 sampling, name=name, seed=seed 

1547 ) 

1548 

1549 def _init(self, selectable, sampling, name=None, seed=None): 

1550 self.sampling = sampling 

1551 self.seed = seed 

1552 super(TableSample, self)._init(selectable, name=name) 

1553 

1554 @util.dependencies("sqlalchemy.sql.functions") 

1555 def _get_method(self, functions): 

1556 if isinstance(self.sampling, functions.Function): 

1557 return self.sampling 

1558 else: 

1559 return functions.func.system(self.sampling) 

1560 

1561 

1562class CTE(Generative, HasPrefixes, HasSuffixes, Alias): 

1563 """Represent a Common Table Expression. 

1564 

1565 The :class:`_expression.CTE` object is obtained using the 

1566 :meth:`_expression.SelectBase.cte` method from any selectable. 

1567 See that method for complete examples. 

1568 

1569 """ 

1570 

1571 __visit_name__ = "cte" 

1572 

1573 @classmethod 

1574 def _factory(cls, selectable, name=None, recursive=False): 

1575 r"""Return a new :class:`_expression.CTE`, 

1576 or Common Table Expression instance. 

1577 

1578 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage. 

1579 

1580 """ 

1581 return _interpret_as_from(selectable).cte( 

1582 name=name, recursive=recursive 

1583 ) 

1584 

1585 def _init( 

1586 self, 

1587 selectable, 

1588 name=None, 

1589 recursive=False, 

1590 _cte_alias=None, 

1591 _restates=frozenset(), 

1592 _prefixes=None, 

1593 _suffixes=None, 

1594 ): 

1595 self.recursive = recursive 

1596 self._cte_alias = _cte_alias 

1597 self._restates = _restates 

1598 if _prefixes: 

1599 self._prefixes = _prefixes 

1600 if _suffixes: 

1601 self._suffixes = _suffixes 

1602 super(CTE, self)._init(selectable, name=name) 

1603 

1604 def _copy_internals(self, clone=_clone, **kw): 

1605 super(CTE, self)._copy_internals(clone, **kw) 

1606 if self._cte_alias is not None: 

1607 self._cte_alias = clone(self._cte_alias, **kw) 

1608 self._restates = frozenset( 

1609 [clone(elem, **kw) for elem in self._restates] 

1610 ) 

1611 

1612 @util.dependencies("sqlalchemy.sql.dml") 

1613 def _populate_column_collection(self, dml): 

1614 if isinstance(self.element, dml.UpdateBase): 

1615 for col in self.element._returning: 

1616 col._make_proxy(self) 

1617 else: 

1618 for col in self.element.columns._all_columns: 

1619 col._make_proxy(self) 

1620 

1621 def alias(self, name=None, flat=False): 

1622 """Return an :class:`_expression.Alias` of this 

1623 :class:`_expression.CTE`. 

1624 

1625 This method is a CTE-specific specialization of the 

1626 :class:`_expression.FromClause.alias` method. 

1627 

1628 .. seealso:: 

1629 

1630 :ref:`core_tutorial_aliases` 

1631 

1632 :func:`_expression.alias` 

1633 

1634 """ 

1635 return CTE._construct( 

1636 self.original, 

1637 name=name, 

1638 recursive=self.recursive, 

1639 _cte_alias=self, 

1640 _prefixes=self._prefixes, 

1641 _suffixes=self._suffixes, 

1642 ) 

1643 

1644 def union(self, other): 

1645 return CTE._construct( 

1646 self.original.union(other), 

1647 name=self.name, 

1648 recursive=self.recursive, 

1649 _restates=self._restates.union([self]), 

1650 _prefixes=self._prefixes, 

1651 _suffixes=self._suffixes, 

1652 ) 

1653 

1654 def union_all(self, other): 

1655 return CTE._construct( 

1656 self.original.union_all(other), 

1657 name=self.name, 

1658 recursive=self.recursive, 

1659 _restates=self._restates.union([self]), 

1660 _prefixes=self._prefixes, 

1661 _suffixes=self._suffixes, 

1662 ) 

1663 

1664 

1665class HasCTE(object): 

1666 """Mixin that declares a class to include CTE support. 

1667 

1668 .. versionadded:: 1.1 

1669 

1670 """ 

1671 

1672 def cte(self, name=None, recursive=False): 

1673 r"""Return a new :class:`_expression.CTE`, 

1674 or Common Table Expression instance. 

1675 

1676 Common table expressions are a SQL standard whereby SELECT 

1677 statements can draw upon secondary statements specified along 

1678 with the primary statement, using a clause called "WITH". 

1679 Special semantics regarding UNION can also be employed to 

1680 allow "recursive" queries, where a SELECT statement can draw 

1681 upon the set of rows that have previously been selected. 

1682 

1683 CTEs can also be applied to DML constructs UPDATE, INSERT 

1684 and DELETE on some databases, both as a source of CTE rows 

1685 when combined with RETURNING, as well as a consumer of 

1686 CTE rows. 

1687 

1688 .. versionchanged:: 1.1 Added support for UPDATE/INSERT/DELETE as 

1689 CTE, CTEs added to UPDATE/INSERT/DELETE. 

1690 

1691 SQLAlchemy detects :class:`_expression.CTE` objects, which are treated 

1692 similarly to :class:`_expression.Alias` objects, as special elements 

1693 to be delivered to the FROM clause of the statement as well 

1694 as to a WITH clause at the top of the statement. 

1695 

1696 For special prefixes such as PostgreSQL "MATERIALIZED" and 

1697 "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with` 

1698 method may be 

1699 used to establish these. 

1700 

1701 .. versionchanged:: 1.3.13 Added support for prefixes. 

1702 In particular - MATERIALIZED and NOT MATERIALIZED. 

1703 

1704 :param name: name given to the common table expression. Like 

1705 :meth:`._FromClause.alias`, the name can be left as ``None`` 

1706 in which case an anonymous symbol will be used at query 

1707 compile time. 

1708 :param recursive: if ``True``, will render ``WITH RECURSIVE``. 

1709 A recursive common table expression is intended to be used in 

1710 conjunction with UNION ALL in order to derive rows 

1711 from those already selected. 

1712 

1713 The following examples include two from PostgreSQL's documentation at 

1714 http://www.postgresql.org/docs/current/static/queries-with.html, 

1715 as well as additional examples. 

1716 

1717 Example 1, non recursive:: 

1718 

1719 from sqlalchemy import (Table, Column, String, Integer, 

1720 MetaData, select, func) 

1721 

1722 metadata = MetaData() 

1723 

1724 orders = Table('orders', metadata, 

1725 Column('region', String), 

1726 Column('amount', Integer), 

1727 Column('product', String), 

1728 Column('quantity', Integer) 

1729 ) 

1730 

1731 regional_sales = select([ 

1732 orders.c.region, 

1733 func.sum(orders.c.amount).label('total_sales') 

1734 ]).group_by(orders.c.region).cte("regional_sales") 

1735 

1736 

1737 top_regions = select([regional_sales.c.region]).\ 

1738 where( 

1739 regional_sales.c.total_sales > 

1740 select([ 

1741 func.sum(regional_sales.c.total_sales)/10 

1742 ]) 

1743 ).cte("top_regions") 

1744 

1745 statement = select([ 

1746 orders.c.region, 

1747 orders.c.product, 

1748 func.sum(orders.c.quantity).label("product_units"), 

1749 func.sum(orders.c.amount).label("product_sales") 

1750 ]).where(orders.c.region.in_( 

1751 select([top_regions.c.region]) 

1752 )).group_by(orders.c.region, orders.c.product) 

1753 

1754 result = conn.execute(statement).fetchall() 

1755 

1756 Example 2, WITH RECURSIVE:: 

1757 

1758 from sqlalchemy import (Table, Column, String, Integer, 

1759 MetaData, select, func) 

1760 

1761 metadata = MetaData() 

1762 

1763 parts = Table('parts', metadata, 

1764 Column('part', String), 

1765 Column('sub_part', String), 

1766 Column('quantity', Integer), 

1767 ) 

1768 

1769 included_parts = select([ 

1770 parts.c.sub_part, 

1771 parts.c.part, 

1772 parts.c.quantity]).\ 

1773 where(parts.c.part=='our part').\ 

1774 cte(recursive=True) 

1775 

1776 

1777 incl_alias = included_parts.alias() 

1778 parts_alias = parts.alias() 

1779 included_parts = included_parts.union_all( 

1780 select([ 

1781 parts_alias.c.sub_part, 

1782 parts_alias.c.part, 

1783 parts_alias.c.quantity 

1784 ]). 

1785 where(parts_alias.c.part==incl_alias.c.sub_part) 

1786 ) 

1787 

1788 statement = select([ 

1789 included_parts.c.sub_part, 

1790 func.sum(included_parts.c.quantity). 

1791 label('total_quantity') 

1792 ]).\ 

1793 group_by(included_parts.c.sub_part) 

1794 

1795 result = conn.execute(statement).fetchall() 

1796 

1797 Example 3, an upsert using UPDATE and INSERT with CTEs:: 

1798 

1799 from datetime import date 

1800 from sqlalchemy import (MetaData, Table, Column, Integer, 

1801 Date, select, literal, and_, exists) 

1802 

1803 metadata = MetaData() 

1804 

1805 visitors = Table('visitors', metadata, 

1806 Column('product_id', Integer, primary_key=True), 

1807 Column('date', Date, primary_key=True), 

1808 Column('count', Integer), 

1809 ) 

1810 

1811 # add 5 visitors for the product_id == 1 

1812 product_id = 1 

1813 day = date.today() 

1814 count = 5 

1815 

1816 update_cte = ( 

1817 visitors.update() 

1818 .where(and_(visitors.c.product_id == product_id, 

1819 visitors.c.date == day)) 

1820 .values(count=visitors.c.count + count) 

1821 .returning(literal(1)) 

1822 .cte('update_cte') 

1823 ) 

1824 

1825 upsert = visitors.insert().from_select( 

1826 [visitors.c.product_id, visitors.c.date, visitors.c.count], 

1827 select([literal(product_id), literal(day), literal(count)]) 

1828 .where(~exists(update_cte.select())) 

1829 ) 

1830 

1831 connection.execute(upsert) 

1832 

1833 .. seealso:: 

1834 

1835 :meth:`.orm.query.Query.cte` - ORM version of 

1836 :meth:`_expression.HasCTE.cte`. 

1837 

1838 """ 

1839 return CTE._construct(self, name=name, recursive=recursive) 

1840 

1841 

1842class FromGrouping(FromClause): 

1843 """Represent a grouping of a FROM clause""" 

1844 

1845 __visit_name__ = "grouping" 

1846 

1847 def __init__(self, element): 

1848 self.element = element 

1849 

1850 def _init_collections(self): 

1851 pass 

1852 

1853 @property 

1854 def columns(self): 

1855 return self.element.columns 

1856 

1857 @property 

1858 def primary_key(self): 

1859 return self.element.primary_key 

1860 

1861 @property 

1862 def foreign_keys(self): 

1863 return self.element.foreign_keys 

1864 

1865 def is_derived_from(self, element): 

1866 return self.element.is_derived_from(element) 

1867 

1868 def alias(self, **kw): 

1869 return FromGrouping(self.element.alias(**kw)) 

1870 

1871 @property 

1872 def _hide_froms(self): 

1873 return self.element._hide_froms 

1874 

1875 def get_children(self, **kwargs): 

1876 return (self.element,) 

1877 

1878 def _copy_internals(self, clone=_clone, **kw): 

1879 self.element = clone(self.element, **kw) 

1880 

1881 @property 

1882 def _from_objects(self): 

1883 return self.element._from_objects 

1884 

1885 def __getattr__(self, attr): 

1886 return getattr(self.element, attr) 

1887 

1888 def __getstate__(self): 

1889 return {"element": self.element} 

1890 

1891 def __setstate__(self, state): 

1892 self.element = state["element"] 

1893 

1894 

1895class TableClause(Immutable, FromClause): 

1896 """Represents a minimal "table" construct. 

1897 

1898 This is a lightweight table object that has only a name, a 

1899 collection of columns, which are typically produced 

1900 by the :func:`_expression.column` function, and a schema:: 

1901 

1902 from sqlalchemy import table, column 

1903 

1904 user = table("user", 

1905 column("id"), 

1906 column("name"), 

1907 column("description"), 

1908 ) 

1909 

1910 The :class:`_expression.TableClause` construct serves as the base for 

1911 the more commonly used :class:`_schema.Table` object, providing 

1912 the usual set of :class:`_expression.FromClause` services including 

1913 the ``.c.`` collection and statement generation methods. 

1914 

1915 It does **not** provide all the additional schema-level services 

1916 of :class:`_schema.Table`, including constraints, references to other 

1917 tables, or support for :class:`_schema.MetaData`-level services. 

1918 It's useful 

1919 on its own as an ad-hoc construct used to generate quick SQL 

1920 statements when a more fully fledged :class:`_schema.Table` 

1921 is not on hand. 

1922 

1923 """ 

1924 

1925 __visit_name__ = "table" 

1926 

1927 named_with_column = True 

1928 

1929 implicit_returning = False 

1930 """:class:`_expression.TableClause` 

1931 doesn't support having a primary key or column 

1932 -level defaults, so implicit returning doesn't apply.""" 

1933 

1934 _autoincrement_column = None 

1935 """No PK or default support so no autoincrement column.""" 

1936 

1937 def __init__(self, name, *columns, **kw): 

1938 """Produce a new :class:`_expression.TableClause`. 

1939 

1940 The object returned is an instance of :class:`_expression.TableClause` 

1941 , which 

1942 represents the "syntactical" portion of the schema-level 

1943 :class:`_schema.Table` object. 

1944 It may be used to construct lightweight table constructs. 

1945 

1946 .. versionchanged:: 1.0.0 :func:`_expression.table` can now 

1947 be imported from the plain ``sqlalchemy`` namespace like any 

1948 other SQL element. 

1949 

1950 

1951 :param name: Name of the table. 

1952 

1953 :param columns: A collection of :func:`_expression.column` constructs. 

1954 

1955 :param schema: The schema name for this table. 

1956 

1957 .. versionadded:: 1.3.18 :func:`_expression.table` can now 

1958 accept a ``schema`` argument. 

1959 """ 

1960 

1961 super(TableClause, self).__init__() 

1962 self.name = self.fullname = name 

1963 self._columns = ColumnCollection() 

1964 self.primary_key = ColumnSet() 

1965 self.foreign_keys = set() 

1966 for c in columns: 

1967 self.append_column(c) 

1968 

1969 schema = kw.pop("schema", None) 

1970 if schema is not None: 

1971 self.schema = schema 

1972 if kw: 

1973 raise exc.ArgumentError("Unsupported argument(s): %s" % list(kw)) 

1974 

1975 def _init_collections(self): 

1976 pass 

1977 

1978 @util.memoized_property 

1979 def description(self): 

1980 if util.py3k: 

1981 return self.name 

1982 else: 

1983 return self.name.encode("ascii", "backslashreplace") 

1984 

1985 def append_column(self, c): 

1986 self._columns[c.key] = c 

1987 c.table = self 

1988 

1989 def get_children(self, column_collections=True, **kwargs): 

1990 if column_collections: 

1991 return [c for c in self.c] 

1992 else: 

1993 return [] 

1994 

1995 @util.dependencies("sqlalchemy.sql.dml") 

1996 def insert(self, dml, values=None, inline=False, **kwargs): 

1997 """Generate an :func:`_expression.insert` construct against this 

1998 :class:`_expression.TableClause`. 

1999 

2000 E.g.:: 

2001 

2002 table.insert().values(name='foo') 

2003 

2004 See :func:`_expression.insert` for argument and usage information. 

2005 

2006 """ 

2007 

2008 return dml.Insert(self, values=values, inline=inline, **kwargs) 

2009 

2010 @util.dependencies("sqlalchemy.sql.dml") 

2011 def update( 

2012 self, dml, whereclause=None, values=None, inline=False, **kwargs 

2013 ): 

2014 """Generate an :func:`_expression.update` construct against this 

2015 :class:`_expression.TableClause`. 

2016 

2017 E.g.:: 

2018 

2019 table.update().where(table.c.id==7).values(name='foo') 

2020 

2021 See :func:`_expression.update` for argument and usage information. 

2022 

2023 """ 

2024 

2025 return dml.Update( 

2026 self, 

2027 whereclause=whereclause, 

2028 values=values, 

2029 inline=inline, 

2030 **kwargs 

2031 ) 

2032 

2033 @util.dependencies("sqlalchemy.sql.dml") 

2034 def delete(self, dml, whereclause=None, **kwargs): 

2035 """Generate a :func:`_expression.delete` construct against this 

2036 :class:`_expression.TableClause`. 

2037 

2038 E.g.:: 

2039 

2040 table.delete().where(table.c.id==7) 

2041 

2042 See :func:`_expression.delete` for argument and usage information. 

2043 

2044 """ 

2045 

2046 return dml.Delete(self, whereclause, **kwargs) 

2047 

2048 @property 

2049 def _from_objects(self): 

2050 return [self] 

2051 

2052 

2053class ForUpdateArg(ClauseElement): 

2054 @classmethod 

2055 def parse_legacy_select(self, arg): 

2056 """Parse the for_update argument of :func:`_expression.select`. 

2057 

2058 :param mode: Defines the lockmode to use. 

2059 

2060 ``None`` - translates to no lockmode 

2061 

2062 ``'update'`` - translates to ``FOR UPDATE`` 

2063 (standard SQL, supported by most dialects) 

2064 

2065 ``'nowait'`` - translates to ``FOR UPDATE NOWAIT`` 

2066 (supported by Oracle, PostgreSQL 8.1 upwards) 

2067 

2068 ``'read'`` - translates to ``LOCK IN SHARE MODE`` (for MySQL), 

2069 and ``FOR SHARE`` (for PostgreSQL) 

2070 

2071 ``'read_nowait'`` - translates to ``FOR SHARE NOWAIT`` 

2072 (supported by PostgreSQL). ``FOR SHARE`` and 

2073 ``FOR SHARE NOWAIT`` (PostgreSQL). 

2074 

2075 """ 

2076 if arg in (None, False): 

2077 return None 

2078 

2079 nowait = read = False 

2080 if arg == "nowait": 

2081 nowait = True 

2082 elif arg == "read": 

2083 read = True 

2084 elif arg == "read_nowait": 

2085 read = nowait = True 

2086 elif arg is not True: 

2087 raise exc.ArgumentError("Unknown for_update argument: %r" % arg) 

2088 

2089 return ForUpdateArg(read=read, nowait=nowait) 

2090 

2091 @property 

2092 def legacy_for_update_value(self): 

2093 if self.read and not self.nowait: 

2094 return "read" 

2095 elif self.read and self.nowait: 

2096 return "read_nowait" 

2097 elif self.nowait: 

2098 return "nowait" 

2099 else: 

2100 return True 

2101 

2102 def __eq__(self, other): 

2103 return ( 

2104 isinstance(other, ForUpdateArg) 

2105 and other.nowait == self.nowait 

2106 and other.read == self.read 

2107 and other.skip_locked == self.skip_locked 

2108 and other.key_share == self.key_share 

2109 and other.of is self.of 

2110 ) 

2111 

2112 def __hash__(self): 

2113 return id(self) 

2114 

2115 def _copy_internals(self, clone=_clone, **kw): 

2116 if self.of is not None: 

2117 self.of = [clone(col, **kw) for col in self.of] 

2118 

2119 def __init__( 

2120 self, 

2121 nowait=False, 

2122 read=False, 

2123 of=None, 

2124 skip_locked=False, 

2125 key_share=False, 

2126 ): 

2127 """Represents arguments specified to 

2128 :meth:`_expression.Select.for_update`. 

2129 

2130 .. versionadded:: 0.9.0 

2131 

2132 """ 

2133 

2134 self.nowait = nowait 

2135 self.read = read 

2136 self.skip_locked = skip_locked 

2137 self.key_share = key_share 

2138 if of is not None: 

2139 self.of = [ 

2140 _interpret_as_column_or_from(elem) for elem in util.to_list(of) 

2141 ] 

2142 else: 

2143 self.of = None 

2144 

2145 

2146class SelectBase(HasCTE, Executable, FromClause): 

2147 """Base class for SELECT statements. 

2148 

2149 

2150 This includes :class:`_expression.Select`, 

2151 :class:`_selectable.CompoundSelect` and 

2152 :class:`.TextAsFrom`. 

2153 

2154 

2155 """ 

2156 

2157 def as_scalar(self): 

2158 """return a 'scalar' representation of this selectable, which can be 

2159 used as a column expression. 

2160 

2161 Typically, a select statement which has only one column in its columns 

2162 clause is eligible to be used as a scalar expression. 

2163 

2164 The returned object is an instance of 

2165 :class:`ScalarSelect`. 

2166 

2167 """ 

2168 return ScalarSelect(self) 

2169 

2170 def label(self, name): 

2171 """return a 'scalar' representation of this selectable, embedded as a 

2172 subquery with a label. 

2173 

2174 .. seealso:: 

2175 

2176 :meth:`_expression.SelectBase.as_scalar`. 

2177 

2178 """ 

2179 return self.as_scalar().label(name) 

2180 

2181 @_generative 

2182 @util.deprecated( 

2183 "0.6", 

2184 message="The :meth:`.SelectBase.autocommit` method is deprecated, " 

2185 "and will be removed in a future release. Please use the " 

2186 "the :paramref:`.Connection.execution_options.autocommit` " 

2187 "parameter in conjunction with the " 

2188 ":meth:`.Executable.execution_options` method.", 

2189 ) 

2190 def autocommit(self): 

2191 """return a new selectable with the 'autocommit' flag set to 

2192 True. 

2193 """ 

2194 

2195 self._execution_options = self._execution_options.union( 

2196 {"autocommit": True} 

2197 ) 

2198 

2199 def _generate(self): 

2200 """Override the default _generate() method to also clear out 

2201 exported collections.""" 

2202 

2203 s = self.__class__.__new__(self.__class__) 

2204 s.__dict__ = self.__dict__.copy() 

2205 s._reset_exported() 

2206 return s 

2207 

2208 @property 

2209 def _from_objects(self): 

2210 return [self] 

2211 

2212 

2213class GenerativeSelect(SelectBase): 

2214 """Base class for SELECT statements where additional elements can be 

2215 added. 

2216 

2217 This serves as the base for :class:`_expression.Select` and 

2218 :class:`_selectable.CompoundSelect` 

2219 where elements such as ORDER BY, GROUP BY can be added and column 

2220 rendering can be controlled. Compare to :class:`.TextAsFrom`, which, 

2221 while it subclasses :class:`_expression.SelectBase` 

2222 and is also a SELECT construct, 

2223 represents a fixed textual string which cannot be altered at this level, 

2224 only wrapped as a subquery. 

2225 

2226 .. versionadded:: 0.9.0 :class:`_expression.GenerativeSelect` was added to 

2227 provide functionality specific to :class:`_expression.Select` and 

2228 :class:`_selectable.CompoundSelect` while allowing 

2229 :class:`_expression.SelectBase` to be 

2230 used for other SELECT-like objects, e.g. :class:`.TextAsFrom`. 

2231 

2232 """ 

2233 

2234 _order_by_clause = ClauseList() 

2235 _group_by_clause = ClauseList() 

2236 _limit_clause = None 

2237 _offset_clause = None 

2238 _for_update_arg = None 

2239 

2240 def __init__( 

2241 self, 

2242 use_labels=False, 

2243 for_update=False, 

2244 limit=None, 

2245 offset=None, 

2246 order_by=None, 

2247 group_by=None, 

2248 bind=None, 

2249 autocommit=None, 

2250 ): 

2251 self.use_labels = use_labels 

2252 

2253 if for_update is not False: 

2254 self._for_update_arg = ForUpdateArg.parse_legacy_select(for_update) 

2255 

2256 if autocommit is not None: 

2257 util.warn_deprecated( 

2258 "The select.autocommit parameter is deprecated and will be " 

2259 "removed in a future release. Please refer to the " 

2260 "Select.execution_options.autocommit` parameter." 

2261 ) 

2262 self._execution_options = self._execution_options.union( 

2263 {"autocommit": autocommit} 

2264 ) 

2265 if limit is not None: 

2266 self._limit_clause = _offset_or_limit_clause(limit) 

2267 if offset is not None: 

2268 self._offset_clause = _offset_or_limit_clause(offset) 

2269 self._bind = bind 

2270 

2271 if order_by is not None: 

2272 self._order_by_clause = ClauseList( 

2273 *util.to_list(order_by), 

2274 _literal_as_text=_literal_and_labels_as_label_reference 

2275 ) 

2276 if group_by is not None: 

2277 self._group_by_clause = ClauseList( 

2278 *util.to_list(group_by), 

2279 _literal_as_text=_literal_as_label_reference 

2280 ) 

2281 

2282 @property 

2283 def for_update(self): 

2284 """Provide legacy dialect support for the ``for_update`` attribute. 

2285 """ 

2286 if self._for_update_arg is not None: 

2287 return self._for_update_arg.legacy_for_update_value 

2288 else: 

2289 return None 

2290 

2291 @for_update.setter 

2292 def for_update(self, value): 

2293 self._for_update_arg = ForUpdateArg.parse_legacy_select(value) 

2294 

2295 @_generative 

2296 def with_for_update( 

2297 self, 

2298 nowait=False, 

2299 read=False, 

2300 of=None, 

2301 skip_locked=False, 

2302 key_share=False, 

2303 ): 

2304 """Specify a ``FOR UPDATE`` clause for this 

2305 :class:`_expression.GenerativeSelect`. 

2306 

2307 E.g.:: 

2308 

2309 stmt = select([table]).with_for_update(nowait=True) 

2310 

2311 On a database like PostgreSQL or Oracle, the above would render a 

2312 statement like:: 

2313 

2314 SELECT table.a, table.b FROM table FOR UPDATE NOWAIT 

2315 

2316 on other backends, the ``nowait`` option is ignored and instead 

2317 would produce:: 

2318 

2319 SELECT table.a, table.b FROM table FOR UPDATE 

2320 

2321 When called with no arguments, the statement will render with 

2322 the suffix ``FOR UPDATE``. Additional arguments can then be 

2323 provided which allow for common database-specific 

2324 variants. 

2325 

2326 :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle 

2327 and PostgreSQL dialects. 

2328 

2329 :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL, 

2330 ``FOR SHARE`` on PostgreSQL. On PostgreSQL, when combined with 

2331 ``nowait``, will render ``FOR SHARE NOWAIT``. 

2332 

2333 :param of: SQL expression or list of SQL expression elements 

2334 (typically :class:`_schema.Column` 

2335 objects or a compatible expression) which 

2336 will render into a ``FOR UPDATE OF`` clause; supported by PostgreSQL 

2337 and Oracle. May render as a table or as a column depending on 

2338 backend. 

2339 

2340 :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED`` 

2341 on Oracle and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED`` if 

2342 ``read=True`` is also specified. 

2343 

2344 .. versionadded:: 1.1.0 

2345 

2346 :param key_share: boolean, will render ``FOR NO KEY UPDATE``, 

2347 or if combined with ``read=True`` will render ``FOR KEY SHARE``, 

2348 on the PostgreSQL dialect. 

2349 

2350 .. versionadded:: 1.1.0 

2351 

2352 """ 

2353 self._for_update_arg = ForUpdateArg( 

2354 nowait=nowait, 

2355 read=read, 

2356 of=of, 

2357 skip_locked=skip_locked, 

2358 key_share=key_share, 

2359 ) 

2360 

2361 @_generative 

2362 def apply_labels(self): 

2363 """return a new selectable with the 'use_labels' flag set to True. 

2364 

2365 This will result in column expressions being generated using labels 

2366 against their table name, such as "SELECT somecolumn AS 

2367 tablename_somecolumn". This allows selectables which contain multiple 

2368 FROM clauses to produce a unique set of column names regardless of 

2369 name conflicts among the individual FROM clauses. 

2370 

2371 """ 

2372 self.use_labels = True 

2373 

2374 @property 

2375 def _limit(self): 

2376 """Get an integer value for the limit. This should only be used 

2377 by code that cannot support a limit as a BindParameter or 

2378 other custom clause as it will throw an exception if the limit 

2379 isn't currently set to an integer. 

2380 

2381 """ 

2382 return _offset_or_limit_clause_asint(self._limit_clause, "limit") 

2383 

2384 @property 

2385 def _simple_int_limit(self): 

2386 """True if the LIMIT clause is a simple integer, False 

2387 if it is not present or is a SQL expression. 

2388 """ 

2389 return isinstance(self._limit_clause, _OffsetLimitParam) 

2390 

2391 @property 

2392 def _simple_int_offset(self): 

2393 """True if the OFFSET clause is a simple integer, False 

2394 if it is not present or is a SQL expression. 

2395 """ 

2396 return isinstance(self._offset_clause, _OffsetLimitParam) 

2397 

2398 @property 

2399 def _offset(self): 

2400 """Get an integer value for the offset. This should only be used 

2401 by code that cannot support an offset as a BindParameter or 

2402 other custom clause as it will throw an exception if the 

2403 offset isn't currently set to an integer. 

2404 

2405 """ 

2406 return _offset_or_limit_clause_asint(self._offset_clause, "offset") 

2407 

2408 @_generative 

2409 def limit(self, limit): 

2410 """return a new selectable with the given LIMIT criterion 

2411 applied. 

2412 

2413 This is a numerical value which usually renders as a ``LIMIT`` 

2414 expression in the resulting select. Backends that don't 

2415 support ``LIMIT`` will attempt to provide similar 

2416 functionality. 

2417 

2418 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.limit` can now 

2419 accept arbitrary SQL expressions as well as integer values. 

2420 

2421 :param limit: an integer LIMIT parameter, or a SQL expression 

2422 that provides an integer result. 

2423 

2424 """ 

2425 

2426 self._limit_clause = _offset_or_limit_clause(limit) 

2427 

2428 @_generative 

2429 def offset(self, offset): 

2430 """return a new selectable with the given OFFSET criterion 

2431 applied. 

2432 

2433 

2434 This is a numeric value which usually renders as an ``OFFSET`` 

2435 expression in the resulting select. Backends that don't 

2436 support ``OFFSET`` will attempt to provide similar 

2437 functionality. 

2438 

2439 

2440 .. versionchanged:: 1.0.0 - :meth:`_expression.Select.offset` can now 

2441 accept arbitrary SQL expressions as well as integer values. 

2442 

2443 :param offset: an integer OFFSET parameter, or a SQL expression 

2444 that provides an integer result. 

2445 

2446 """ 

2447 

2448 self._offset_clause = _offset_or_limit_clause(offset) 

2449 

2450 @_generative 

2451 def order_by(self, *clauses): 

2452 r"""return a new selectable with the given list of ORDER BY 

2453 criterion applied. 

2454 

2455 e.g.:: 

2456 

2457 stmt = select([table]).order_by(table.c.id, table.c.name) 

2458 

2459 :param \*order_by: a series of :class:`_expression.ColumnElement` 

2460 constructs 

2461 which will be used to generate an ORDER BY clause. 

2462 

2463 .. seealso:: 

2464 

2465 :ref:`core_tutorial_ordering` 

2466 

2467 """ 

2468 

2469 self.append_order_by(*clauses) 

2470 

2471 @_generative 

2472 def group_by(self, *clauses): 

2473 r"""return a new selectable with the given list of GROUP BY 

2474 criterion applied. 

2475 

2476 e.g.:: 

2477 

2478 stmt = select([table.c.name, func.max(table.c.stat)]).\ 

2479 group_by(table.c.name) 

2480 

2481 :param \*group_by: a series of :class:`_expression.ColumnElement` 

2482 constructs 

2483 which will be used to generate an GROUP BY clause. 

2484 

2485 .. seealso:: 

2486 

2487 :ref:`core_tutorial_ordering` 

2488 

2489 """ 

2490 

2491 self.append_group_by(*clauses) 

2492 

2493 def append_order_by(self, *clauses): 

2494 """Append the given ORDER BY criterion applied to this selectable. 

2495 

2496 The criterion will be appended to any pre-existing ORDER BY criterion. 

2497 

2498 This is an **in-place** mutation method; the 

2499 :meth:`_expression.GenerativeSelect.order_by` method is preferred, 

2500 as it 

2501 provides standard :term:`method chaining`. 

2502 

2503 .. seealso:: 

2504 

2505 :meth:`_expression.GenerativeSelect.order_by` 

2506 

2507 """ 

2508 if len(clauses) == 1 and clauses[0] is None: 

2509 self._order_by_clause = ClauseList() 

2510 else: 

2511 if getattr(self, "_order_by_clause", None) is not None: 

2512 clauses = list(self._order_by_clause) + list(clauses) 

2513 self._order_by_clause = ClauseList( 

2514 *clauses, 

2515 _literal_as_text=_literal_and_labels_as_label_reference 

2516 ) 

2517 

2518 def append_group_by(self, *clauses): 

2519 """Append the given GROUP BY criterion applied to this selectable. 

2520 

2521 The criterion will be appended to any pre-existing GROUP BY criterion. 

2522 

2523 This is an **in-place** mutation method; the 

2524 :meth:`_expression.GenerativeSelect.group_by` method is preferred, 

2525 as it 

2526 provides standard :term:`method chaining`. 

2527 

2528 .. seealso:: 

2529 

2530 :meth:`_expression.GenerativeSelect.group_by` 

2531 

2532 """ 

2533 if len(clauses) == 1 and clauses[0] is None: 

2534 self._group_by_clause = ClauseList() 

2535 else: 

2536 if getattr(self, "_group_by_clause", None) is not None: 

2537 clauses = list(self._group_by_clause) + list(clauses) 

2538 self._group_by_clause = ClauseList( 

2539 *clauses, _literal_as_text=_literal_as_label_reference 

2540 ) 

2541 

2542 @property 

2543 def _label_resolve_dict(self): 

2544 raise NotImplementedError() 

2545 

2546 def _copy_internals(self, clone=_clone, **kw): 

2547 if self._limit_clause is not None: 

2548 self._limit_clause = clone(self._limit_clause, **kw) 

2549 if self._offset_clause is not None: 

2550 self._offset_clause = clone(self._offset_clause, **kw) 

2551 

2552 

2553class CompoundSelect(GenerativeSelect): 

2554 """Forms the basis of ``UNION``, ``UNION ALL``, and other 

2555 SELECT-based set operations. 

2556 

2557 

2558 .. seealso:: 

2559 

2560 :func:`_expression.union` 

2561 

2562 :func:`_expression.union_all` 

2563 

2564 :func:`_expression.intersect` 

2565 

2566 :func:`_expression.intersect_all` 

2567 

2568 :func:`_expression.except` 

2569 

2570 :func:`_expression.except_all` 

2571 

2572 """ 

2573 

2574 __visit_name__ = "compound_select" 

2575 

2576 UNION = util.symbol("UNION") 

2577 UNION_ALL = util.symbol("UNION ALL") 

2578 EXCEPT = util.symbol("EXCEPT") 

2579 EXCEPT_ALL = util.symbol("EXCEPT ALL") 

2580 INTERSECT = util.symbol("INTERSECT") 

2581 INTERSECT_ALL = util.symbol("INTERSECT ALL") 

2582 

2583 _is_from_container = True 

2584 

2585 def __init__(self, keyword, *selects, **kwargs): 

2586 self._auto_correlate = kwargs.pop("correlate", False) 

2587 self.keyword = keyword 

2588 self.selects = [] 

2589 

2590 numcols = None 

2591 

2592 # some DBs do not like ORDER BY in the inner queries of a UNION, etc. 

2593 for n, s in enumerate(selects): 

2594 s = _clause_element_as_expr(s) 

2595 

2596 if not numcols: 

2597 numcols = len(s.c._all_columns) 

2598 elif len(s.c._all_columns) != numcols: 

2599 raise exc.ArgumentError( 

2600 "All selectables passed to " 

2601 "CompoundSelect must have identical numbers of " 

2602 "columns; select #%d has %d columns, select " 

2603 "#%d has %d" 

2604 % ( 

2605 1, 

2606 len(self.selects[0].c._all_columns), 

2607 n + 1, 

2608 len(s.c._all_columns), 

2609 ) 

2610 ) 

2611 

2612 self.selects.append(s.self_group(against=self)) 

2613 

2614 GenerativeSelect.__init__(self, **kwargs) 

2615 

2616 @property 

2617 def _label_resolve_dict(self): 

2618 d = dict((c.key, c) for c in self.c) 

2619 return d, d, d 

2620 

2621 @classmethod 

2622 def _create_union(cls, *selects, **kwargs): 

2623 r"""Return a ``UNION`` of multiple selectables. 

2624 

2625 The returned object is an instance of 

2626 :class:`_selectable.CompoundSelect`. 

2627 

2628 A similar :func:`union()` method is available on all 

2629 :class:`_expression.FromClause` subclasses. 

2630 

2631 \*selects 

2632 a list of :class:`_expression.Select` instances. 

2633 

2634 \**kwargs 

2635 available keyword arguments are the same as those of 

2636 :func:`select`. 

2637 

2638 """ 

2639 return CompoundSelect(CompoundSelect.UNION, *selects, **kwargs) 

2640 

2641 @classmethod 

2642 def _create_union_all(cls, *selects, **kwargs): 

2643 r"""Return a ``UNION ALL`` of multiple selectables. 

2644 

2645 The returned object is an instance of 

2646 :class:`_selectable.CompoundSelect`. 

2647 

2648 A similar :func:`union_all()` method is available on all 

2649 :class:`_expression.FromClause` subclasses. 

2650 

2651 \*selects 

2652 a list of :class:`_expression.Select` instances. 

2653 

2654 \**kwargs 

2655 available keyword arguments are the same as those of 

2656 :func:`select`. 

2657 

2658 """ 

2659 return CompoundSelect(CompoundSelect.UNION_ALL, *selects, **kwargs) 

2660 

2661 @classmethod 

2662 def _create_except(cls, *selects, **kwargs): 

2663 r"""Return an ``EXCEPT`` of multiple selectables. 

2664 

2665 The returned object is an instance of 

2666 :class:`_selectable.CompoundSelect`. 

2667 

2668 \*selects 

2669 a list of :class:`_expression.Select` instances. 

2670 

2671 \**kwargs 

2672 available keyword arguments are the same as those of 

2673 :func:`select`. 

2674 

2675 """ 

2676 return CompoundSelect(CompoundSelect.EXCEPT, *selects, **kwargs) 

2677 

2678 @classmethod 

2679 def _create_except_all(cls, *selects, **kwargs): 

2680 r"""Return an ``EXCEPT ALL`` of multiple selectables. 

2681 

2682 The returned object is an instance of 

2683 :class:`_selectable.CompoundSelect`. 

2684 

2685 \*selects 

2686 a list of :class:`_expression.Select` instances. 

2687 

2688 \**kwargs 

2689 available keyword arguments are the same as those of 

2690 :func:`select`. 

2691 

2692 """ 

2693 return CompoundSelect(CompoundSelect.EXCEPT_ALL, *selects, **kwargs) 

2694 

2695 @classmethod 

2696 def _create_intersect(cls, *selects, **kwargs): 

2697 r"""Return an ``INTERSECT`` of multiple selectables. 

2698 

2699 The returned object is an instance of 

2700 :class:`_selectable.CompoundSelect`. 

2701 

2702 \*selects 

2703 a list of :class:`_expression.Select` instances. 

2704 

2705 \**kwargs 

2706 available keyword arguments are the same as those of 

2707 :func:`select`. 

2708 

2709 """ 

2710 return CompoundSelect(CompoundSelect.INTERSECT, *selects, **kwargs) 

2711 

2712 @classmethod 

2713 def _create_intersect_all(cls, *selects, **kwargs): 

2714 r"""Return an ``INTERSECT ALL`` of multiple selectables. 

2715 

2716 The returned object is an instance of 

2717 :class:`_selectable.CompoundSelect`. 

2718 

2719 \*selects 

2720 a list of :class:`_expression.Select` instances. 

2721 

2722 \**kwargs 

2723 available keyword arguments are the same as those of 

2724 :func:`select`. 

2725 

2726 """ 

2727 return CompoundSelect(CompoundSelect.INTERSECT_ALL, *selects, **kwargs) 

2728 

2729 def _scalar_type(self): 

2730 return self.selects[0]._scalar_type() 

2731 

2732 def self_group(self, against=None): 

2733 return FromGrouping(self) 

2734 

2735 def is_derived_from(self, fromclause): 

2736 for s in self.selects: 

2737 if s.is_derived_from(fromclause): 

2738 return True 

2739 return False 

2740 

2741 def _populate_column_collection(self): 

2742 for cols in zip(*[s.c._all_columns for s in self.selects]): 

2743 

2744 # this is a slightly hacky thing - the union exports a 

2745 # column that resembles just that of the *first* selectable. 

2746 # to get at a "composite" column, particularly foreign keys, 

2747 # you have to dig through the proxies collection which we 

2748 # generate below. We may want to improve upon this, such as 

2749 # perhaps _make_proxy can accept a list of other columns 

2750 # that are "shared" - schema.column can then copy all the 

2751 # ForeignKeys in. this would allow the union() to have all 

2752 # those fks too. 

2753 

2754 proxy = cols[0]._make_proxy( 

2755 self, 

2756 name=cols[0]._label if self.use_labels else None, 

2757 key=cols[0]._key_label if self.use_labels else None, 

2758 ) 

2759 

2760 # hand-construct the "_proxies" collection to include all 

2761 # derived columns place a 'weight' annotation corresponding 

2762 # to how low in the list of select()s the column occurs, so 

2763 # that the corresponding_column() operation can resolve 

2764 # conflicts 

2765 proxy._proxies = [ 

2766 c._annotate({"weight": i + 1}) for (i, c) in enumerate(cols) 

2767 ] 

2768 

2769 def _refresh_for_new_column(self, column): 

2770 for s in self.selects: 

2771 s._refresh_for_new_column(column) 

2772 

2773 if not self._cols_populated: 

2774 return None 

2775 

2776 raise NotImplementedError( 

2777 "CompoundSelect constructs don't support " 

2778 "addition of columns to underlying " 

2779 "selectables" 

2780 ) 

2781 

2782 def _copy_internals(self, clone=_clone, **kw): 

2783 super(CompoundSelect, self)._copy_internals(clone, **kw) 

2784 self._reset_exported() 

2785 self.selects = [clone(s, **kw) for s in self.selects] 

2786 if hasattr(self, "_col_map"): 

2787 del self._col_map 

2788 for attr in ( 

2789 "_order_by_clause", 

2790 "_group_by_clause", 

2791 "_for_update_arg", 

2792 ): 

2793 if getattr(self, attr) is not None: 

2794 setattr(self, attr, clone(getattr(self, attr), **kw)) 

2795 

2796 def get_children(self, column_collections=True, **kwargs): 

2797 return ( 

2798 (column_collections and list(self.c) or []) 

2799 + [self._order_by_clause, self._group_by_clause] 

2800 + list(self.selects) 

2801 ) 

2802 

2803 def bind(self): 

2804 if self._bind: 

2805 return self._bind 

2806 for s in self.selects: 

2807 e = s.bind 

2808 if e: 

2809 return e 

2810 else: 

2811 return None 

2812 

2813 def _set_bind(self, bind): 

2814 self._bind = bind 

2815 

2816 bind = property(bind, _set_bind) 

2817 

2818 

2819class Select(HasPrefixes, HasSuffixes, GenerativeSelect): 

2820 """Represents a ``SELECT`` statement. 

2821 

2822 """ 

2823 

2824 __visit_name__ = "select" 

2825 

2826 _prefixes = () 

2827 _suffixes = () 

2828 _hints = util.immutabledict() 

2829 _statement_hints = () 

2830 _distinct = False 

2831 _from_cloned = None 

2832 _correlate = () 

2833 _correlate_except = None 

2834 _memoized_property = SelectBase._memoized_property 

2835 _is_select = True 

2836 

2837 @util.deprecated_params( 

2838 autocommit=( 

2839 "0.6", 

2840 "The :paramref:`_expression.select.autocommit` " 

2841 "parameter is deprecated " 

2842 "and will be removed in a future release. Please refer to " 

2843 "the :paramref:`.Connection.execution_options.autocommit` " 

2844 "parameter in conjunction with the the " 

2845 ":meth:`.Executable.execution_options` method in order to " 

2846 "affect the autocommit behavior for a statement.", 

2847 ), 

2848 for_update=( 

2849 "0.9", 

2850 "The :paramref:`_expression.select.for_update` " 

2851 "parameter is deprecated and " 

2852 "will be removed in a future release. Please refer to the " 

2853 ":meth:`_expression.Select.with_for_update` to specify the " 

2854 "structure of the ``FOR UPDATE`` clause.", 

2855 ), 

2856 ) 

2857 def __init__( 

2858 self, 

2859 columns=None, 

2860 whereclause=None, 

2861 from_obj=None, 

2862 distinct=False, 

2863 having=None, 

2864 correlate=True, 

2865 prefixes=None, 

2866 suffixes=None, 

2867 **kwargs 

2868 ): 

2869 """Construct a new :class:`_expression.Select`. 

2870 

2871 Similar functionality is also available via the 

2872 :meth:`_expression.FromClause.select` method on any 

2873 :class:`_expression.FromClause`. 

2874 

2875 All arguments which accept :class:`_expression.ClauseElement` 

2876 arguments also 

2877 accept string arguments, which will be converted as appropriate into 

2878 either :func:`_expression.text()` or 

2879 :func:`_expression.literal_column()` constructs. 

2880 

2881 .. seealso:: 

2882 

2883 :ref:`coretutorial_selecting` - Core Tutorial description of 

2884 :func:`_expression.select`. 

2885 

2886 :param columns: 

2887 A list of :class:`_expression.ColumnElement` or 

2888 :class:`_expression.FromClause` 

2889 objects which will form the columns clause of the resulting 

2890 statement. For those objects that are instances of 

2891 :class:`_expression.FromClause` (typically :class:`_schema.Table` 

2892 or :class:`_expression.Alias` 

2893 objects), the :attr:`_expression.FromClause.c` 

2894 collection is extracted 

2895 to form a collection of :class:`_expression.ColumnElement` objects. 

2896 

2897 This parameter will also accept :class:`_expression.TextClause` 

2898 constructs as 

2899 given, as well as ORM-mapped classes. 

2900 

2901 .. note:: 

2902 

2903 The :paramref:`_expression.select.columns` 

2904 parameter is not available 

2905 in the method form of :func:`_expression.select`, e.g. 

2906 :meth:`_expression.FromClause.select`. 

2907 

2908 .. seealso:: 

2909 

2910 :meth:`_expression.Select.column` 

2911 

2912 :meth:`_expression.Select.with_only_columns` 

2913 

2914 :param whereclause: 

2915 A :class:`_expression.ClauseElement` 

2916 expression which will be used to form the 

2917 ``WHERE`` clause. It is typically preferable to add WHERE 

2918 criterion to an existing :class:`_expression.Select` 

2919 using method chaining 

2920 with :meth:`_expression.Select.where`. 

2921 

2922 .. seealso:: 

2923 

2924 :meth:`_expression.Select.where` 

2925 

2926 :param from_obj: 

2927 A list of :class:`_expression.ClauseElement` 

2928 objects which will be added to the 

2929 ``FROM`` clause of the resulting statement. This is equivalent 

2930 to calling :meth:`_expression.Select.select_from` 

2931 using method chaining on 

2932 an existing :class:`_expression.Select` object. 

2933 

2934 .. seealso:: 

2935 

2936 :meth:`_expression.Select.select_from` 

2937 - full description of explicit 

2938 FROM clause specification. 

2939 

2940 :param autocommit: legacy autocommit parameter. 

2941 

2942 :param bind=None: 

2943 an :class:`_engine.Engine` or :class:`_engine.Connection` instance 

2944 to which the 

2945 resulting :class:`_expression.Select` object will be bound. The 

2946 :class:`_expression.Select` 

2947 object will otherwise automatically bind to 

2948 whatever :class:`~.base.Connectable` instances can be located within 

2949 its contained :class:`_expression.ClauseElement` members. 

2950 

2951 :param correlate=True: 

2952 indicates that this :class:`_expression.Select` 

2953 object should have its 

2954 contained :class:`_expression.FromClause` 

2955 elements "correlated" to an enclosing 

2956 :class:`_expression.Select` object. 

2957 It is typically preferable to specify 

2958 correlations on an existing :class:`_expression.Select` 

2959 construct using 

2960 :meth:`_expression.Select.correlate`. 

2961 

2962 .. seealso:: 

2963 

2964 :meth:`_expression.Select.correlate` 

2965 - full description of correlation. 

2966 

2967 :param distinct=False: 

2968 when ``True``, applies a ``DISTINCT`` qualifier to the columns 

2969 clause of the resulting statement. 

2970 

2971 The boolean argument may also be a column expression or list 

2972 of column expressions - this is a special calling form which 

2973 is understood by the PostgreSQL dialect to render the 

2974 ``DISTINCT ON (<columns>)`` syntax. 

2975 

2976 ``distinct`` is also available on an existing 

2977 :class:`_expression.Select` 

2978 object via the :meth:`_expression.Select.distinct` method. 

2979 

2980 .. seealso:: 

2981 

2982 :meth:`_expression.Select.distinct` 

2983 

2984 :param for_update=False: 

2985 when ``True``, applies ``FOR UPDATE`` to the end of the 

2986 resulting statement. 

2987 

2988 ``for_update`` accepts various string values interpreted by 

2989 specific backends, including: 

2990 

2991 * ``"read"`` - on MySQL, translates to ``LOCK IN SHARE MODE``; 

2992 on PostgreSQL, translates to ``FOR SHARE``. 

2993 * ``"nowait"`` - on PostgreSQL and Oracle, translates to 

2994 ``FOR UPDATE NOWAIT``. 

2995 * ``"read_nowait"`` - on PostgreSQL, translates to 

2996 ``FOR SHARE NOWAIT``. 

2997 

2998 .. seealso:: 

2999 

3000 :meth:`_expression.Select.with_for_update` - improved API for 

3001 specifying the ``FOR UPDATE`` clause. 

3002 

3003 :param group_by: 

3004 a list of :class:`_expression.ClauseElement` 

3005 objects which will comprise the 

3006 ``GROUP BY`` clause of the resulting select. This parameter 

3007 is typically specified more naturally using the 

3008 :meth:`_expression.Select.group_by` method on an existing 

3009 :class:`_expression.Select`. 

3010 

3011 .. seealso:: 

3012 

3013 :meth:`_expression.Select.group_by` 

3014 

3015 :param having: 

3016 a :class:`_expression.ClauseElement` 

3017 that will comprise the ``HAVING`` clause 

3018 of the resulting select when ``GROUP BY`` is used. This parameter 

3019 is typically specified more naturally using the 

3020 :meth:`_expression.Select.having` method on an existing 

3021 :class:`_expression.Select`. 

3022 

3023 .. seealso:: 

3024 

3025 :meth:`_expression.Select.having` 

3026 

3027 :param limit=None: 

3028 a numerical value which usually renders as a ``LIMIT`` 

3029 expression in the resulting select. Backends that don't 

3030 support ``LIMIT`` will attempt to provide similar 

3031 functionality. This parameter is typically specified more 

3032 naturally using the :meth:`_expression.Select.limit` 

3033 method on an existing 

3034 :class:`_expression.Select`. 

3035 

3036 .. seealso:: 

3037 

3038 :meth:`_expression.Select.limit` 

3039 

3040 :param offset=None: 

3041 a numeric value which usually renders as an ``OFFSET`` 

3042 expression in the resulting select. Backends that don't 

3043 support ``OFFSET`` will attempt to provide similar 

3044 functionality. This parameter is typically specified more naturally 

3045 using the :meth:`_expression.Select.offset` method on an existing 

3046 :class:`_expression.Select`. 

3047 

3048 .. seealso:: 

3049 

3050 :meth:`_expression.Select.offset` 

3051 

3052 :param order_by: 

3053 a scalar or list of :class:`_expression.ClauseElement` 

3054 objects which will 

3055 comprise the ``ORDER BY`` clause of the resulting select. 

3056 This parameter is typically specified more naturally using the 

3057 :meth:`_expression.Select.order_by` method on an existing 

3058 :class:`_expression.Select`. 

3059 

3060 .. seealso:: 

3061 

3062 :meth:`_expression.Select.order_by` 

3063 

3064 :param use_labels=False: 

3065 when ``True``, the statement will be generated using labels 

3066 for each column in the columns clause, which qualify each 

3067 column with its parent table's (or aliases) name so that name 

3068 conflicts between columns in different tables don't occur. 

3069 The format of the label is <tablename>_<column>. The "c" 

3070 collection of the resulting :class:`_expression.Select` 

3071 object will use these 

3072 names as well for targeting column members. 

3073 

3074 This parameter can also be specified on an existing 

3075 :class:`_expression.Select` object using the 

3076 :meth:`_expression.Select.apply_labels` 

3077 method. 

3078 

3079 .. seealso:: 

3080 

3081 :meth:`_expression.Select.apply_labels` 

3082 

3083 """ 

3084 self._auto_correlate = correlate 

3085 if distinct is not False: 

3086 if distinct is True: 

3087 self._distinct = True 

3088 else: 

3089 self._distinct = [ 

3090 _literal_as_label_reference(e) 

3091 for e in util.to_list(distinct) 

3092 ] 

3093 

3094 if from_obj is not None: 

3095 self._from_obj = util.OrderedSet( 

3096 _interpret_as_from(f) for f in util.to_list(from_obj) 

3097 ) 

3098 else: 

3099 self._from_obj = util.OrderedSet() 

3100 

3101 try: 

3102 cols_present = bool(columns) 

3103 except TypeError as err: 

3104 util.raise_( 

3105 exc.ArgumentError( 

3106 "columns argument to select() must " 

3107 "be a Python list or other iterable" 

3108 ), 

3109 replace_context=err, 

3110 ) 

3111 

3112 if cols_present: 

3113 self._raw_columns = [] 

3114 for c in columns: 

3115 c = _interpret_as_column_or_from(c) 

3116 if isinstance(c, ScalarSelect): 

3117 c = c.self_group(against=operators.comma_op) 

3118 self._raw_columns.append(c) 

3119 else: 

3120 self._raw_columns = [] 

3121 

3122 if whereclause is not None: 

3123 self._whereclause = _literal_as_text(whereclause).self_group( 

3124 against=operators._asbool 

3125 ) 

3126 else: 

3127 self._whereclause = None 

3128 

3129 if having is not None: 

3130 self._having = _literal_as_text(having).self_group( 

3131 against=operators._asbool 

3132 ) 

3133 else: 

3134 self._having = None 

3135 

3136 if prefixes: 

3137 self._setup_prefixes(prefixes) 

3138 

3139 if suffixes: 

3140 self._setup_suffixes(suffixes) 

3141 

3142 GenerativeSelect.__init__(self, **kwargs) 

3143 

3144 @property 

3145 def _froms(self): 

3146 # would love to cache this, 

3147 # but there's just enough edge cases, particularly now that 

3148 # declarative encourages construction of SQL expressions 

3149 # without tables present, to just regen this each time. 

3150 froms = [] 

3151 seen = set() 

3152 translate = self._from_cloned 

3153 

3154 for item in itertools.chain( 

3155 _from_objects(*self._raw_columns), 

3156 _from_objects(self._whereclause) 

3157 if self._whereclause is not None 

3158 else (), 

3159 self._from_obj, 

3160 ): 

3161 if item is self: 

3162 raise exc.InvalidRequestError( 

3163 "select() construct refers to itself as a FROM" 

3164 ) 

3165 if translate and item in translate: 

3166 item = translate[item] 

3167 if not seen.intersection(item._cloned_set): 

3168 froms.append(item) 

3169 seen.update(item._cloned_set) 

3170 

3171 return froms 

3172 

3173 def _get_display_froms( 

3174 self, explicit_correlate_froms=None, implicit_correlate_froms=None 

3175 ): 

3176 """Return the full list of 'from' clauses to be displayed. 

3177 

3178 Takes into account a set of existing froms which may be 

3179 rendered in the FROM clause of enclosing selects; this Select 

3180 may want to leave those absent if it is automatically 

3181 correlating. 

3182 

3183 """ 

3184 froms = self._froms 

3185 

3186 toremove = set( 

3187 itertools.chain(*[_expand_cloned(f._hide_froms) for f in froms]) 

3188 ) 

3189 if toremove: 

3190 # if we're maintaining clones of froms, 

3191 # add the copies out to the toremove list. only include 

3192 # clones that are lexical equivalents. 

3193 if self._from_cloned: 

3194 toremove.update( 

3195 self._from_cloned[f] 

3196 for f in toremove.intersection(self._from_cloned) 

3197 if self._from_cloned[f]._is_lexical_equivalent(f) 

3198 ) 

3199 # filter out to FROM clauses not in the list, 

3200 # using a list to maintain ordering 

3201 froms = [f for f in froms if f not in toremove] 

3202 

3203 if self._correlate: 

3204 to_correlate = self._correlate 

3205 if to_correlate: 

3206 froms = [ 

3207 f 

3208 for f in froms 

3209 if f 

3210 not in _cloned_intersection( 

3211 _cloned_intersection( 

3212 froms, explicit_correlate_froms or () 

3213 ), 

3214 to_correlate, 

3215 ) 

3216 ] 

3217 

3218 if self._correlate_except is not None: 

3219 

3220 froms = [ 

3221 f 

3222 for f in froms 

3223 if f 

3224 not in _cloned_difference( 

3225 _cloned_intersection( 

3226 froms, explicit_correlate_froms or () 

3227 ), 

3228 self._correlate_except, 

3229 ) 

3230 ] 

3231 

3232 if ( 

3233 self._auto_correlate 

3234 and implicit_correlate_froms 

3235 and len(froms) > 1 

3236 ): 

3237 

3238 froms = [ 

3239 f 

3240 for f in froms 

3241 if f 

3242 not in _cloned_intersection(froms, implicit_correlate_froms) 

3243 ] 

3244 

3245 if not len(froms): 

3246 raise exc.InvalidRequestError( 

3247 "Select statement '%s" 

3248 "' returned no FROM clauses " 

3249 "due to auto-correlation; " 

3250 "specify correlate(<tables>) " 

3251 "to control correlation " 

3252 "manually." % self 

3253 ) 

3254 

3255 return froms 

3256 

3257 def _scalar_type(self): 

3258 elem = self._raw_columns[0] 

3259 cols = list(elem._select_iterable) 

3260 return cols[0].type 

3261 

3262 @property 

3263 def froms(self): 

3264 """Return the displayed list of FromClause elements.""" 

3265 

3266 return self._get_display_froms() 

3267 

3268 def with_statement_hint(self, text, dialect_name="*"): 

3269 """add a statement hint to this :class:`_expression.Select`. 

3270 

3271 This method is similar to :meth:`_expression.Select.with_hint` 

3272 except that 

3273 it does not require an individual table, and instead applies to the 

3274 statement as a whole. 

3275 

3276 Hints here are specific to the backend database and may include 

3277 directives such as isolation levels, file directives, fetch directives, 

3278 etc. 

3279 

3280 .. versionadded:: 1.0.0 

3281 

3282 .. seealso:: 

3283 

3284 :meth:`_expression.Select.with_hint` 

3285 

3286 :meth:`.Select.prefix_with` - generic SELECT prefixing which also 

3287 can suit some database-specific HINT syntaxes such as MySQL 

3288 optimizer hints 

3289 

3290 """ 

3291 return self.with_hint(None, text, dialect_name) 

3292 

3293 @_generative 

3294 def with_hint(self, selectable, text, dialect_name="*"): 

3295 r"""Add an indexing or other executional context hint for the given 

3296 selectable to this :class:`_expression.Select`. 

3297 

3298 The text of the hint is rendered in the appropriate 

3299 location for the database backend in use, relative 

3300 to the given :class:`_schema.Table` or :class:`_expression.Alias` 

3301 passed as the 

3302 ``selectable`` argument. The dialect implementation 

3303 typically uses Python string substitution syntax 

3304 with the token ``%(name)s`` to render the name of 

3305 the table or alias. E.g. when using Oracle, the 

3306 following:: 

3307 

3308 select([mytable]).\ 

3309 with_hint(mytable, "index(%(name)s ix_mytable)") 

3310 

3311 Would render SQL as:: 

3312 

3313 select /*+ index(mytable ix_mytable) */ ... from mytable 

3314 

3315 The ``dialect_name`` option will limit the rendering of a particular 

3316 hint to a particular backend. Such as, to add hints for both Oracle 

3317 and Sybase simultaneously:: 

3318 

3319 select([mytable]).\ 

3320 with_hint(mytable, "index(%(name)s ix_mytable)", 'oracle').\ 

3321 with_hint(mytable, "WITH INDEX ix_mytable", 'sybase') 

3322 

3323 .. seealso:: 

3324 

3325 :meth:`_expression.Select.with_statement_hint` 

3326 

3327 """ 

3328 if selectable is None: 

3329 self._statement_hints += ((dialect_name, text),) 

3330 else: 

3331 self._hints = self._hints.union({(selectable, dialect_name): text}) 

3332 

3333 @property 

3334 def type(self): 

3335 raise exc.InvalidRequestError( 

3336 "Select objects don't have a type. " 

3337 "Call as_scalar() on this Select " 

3338 "object to return a 'scalar' version " 

3339 "of this Select." 

3340 ) 

3341 

3342 @_memoized_property.method 

3343 def locate_all_froms(self): 

3344 """return a Set of all FromClause elements referenced by this Select. 

3345 

3346 This set is a superset of that returned by the ``froms`` property, 

3347 which is specifically for those FromClause elements that would 

3348 actually be rendered. 

3349 

3350 """ 

3351 froms = self._froms 

3352 return froms + list(_from_objects(*froms)) 

3353 

3354 @property 

3355 def inner_columns(self): 

3356 """an iterator of all ColumnElement expressions which would 

3357 be rendered into the columns clause of the resulting SELECT statement. 

3358 

3359 """ 

3360 return _select_iterables(self._raw_columns) 

3361 

3362 @_memoized_property 

3363 def _label_resolve_dict(self): 

3364 with_cols = dict( 

3365 (c._resolve_label or c._label or c.key, c) 

3366 for c in _select_iterables(self._raw_columns) 

3367 if c._allow_label_resolve 

3368 ) 

3369 only_froms = dict( 

3370 (c.key, c) 

3371 for c in _select_iterables(self.froms) 

3372 if c._allow_label_resolve 

3373 ) 

3374 only_cols = with_cols.copy() 

3375 for key, value in only_froms.items(): 

3376 with_cols.setdefault(key, value) 

3377 

3378 return with_cols, only_froms, only_cols 

3379 

3380 def is_derived_from(self, fromclause): 

3381 if self in fromclause._cloned_set: 

3382 return True 

3383 

3384 for f in self.locate_all_froms(): 

3385 if f.is_derived_from(fromclause): 

3386 return True 

3387 return False 

3388 

3389 def _copy_internals(self, clone=_clone, **kw): 

3390 super(Select, self)._copy_internals(clone, **kw) 

3391 

3392 # Select() object has been cloned and probably adapted by the 

3393 # given clone function. Apply the cloning function to internal 

3394 # objects 

3395 

3396 # 1. keep a dictionary of the froms we've cloned, and what 

3397 # they've become. This is consulted later when we derive 

3398 # additional froms from "whereclause" and the columns clause, 

3399 # which may still reference the uncloned parent table. 

3400 # as of 0.7.4 we also put the current version of _froms, which 

3401 # gets cleared on each generation. previously we were "baking" 

3402 # _froms into self._from_obj. 

3403 self._from_cloned = from_cloned = dict( 

3404 (f, clone(f, **kw)) for f in self._from_obj.union(self._froms) 

3405 ) 

3406 

3407 # 3. update persistent _from_obj with the cloned versions. 

3408 self._from_obj = util.OrderedSet( 

3409 from_cloned[f] for f in self._from_obj 

3410 ) 

3411 

3412 # the _correlate collection is done separately, what can happen 

3413 # here is the same item is _correlate as in _from_obj but the 

3414 # _correlate version has an annotation on it - (specifically 

3415 # RelationshipProperty.Comparator._criterion_exists() does 

3416 # this). Also keep _correlate liberally open with its previous 

3417 # contents, as this set is used for matching, not rendering. 

3418 self._correlate = set(clone(f, **kw) for f in self._correlate).union( 

3419 self._correlate 

3420 ) 

3421 

3422 # do something similar for _correlate_except - this is a more 

3423 # unusual case but same idea applies 

3424 if self._correlate_except: 

3425 self._correlate_except = set( 

3426 clone(f, **kw) for f in self._correlate_except 

3427 ).union(self._correlate_except) 

3428 

3429 # 4. clone other things. The difficulty here is that Column 

3430 # objects are not actually cloned, and refer to their original 

3431 # .table, resulting in the wrong "from" parent after a clone 

3432 # operation. Hence _from_cloned and _from_obj supersede what is 

3433 # present here. 

3434 self._raw_columns = [clone(c, **kw) for c in self._raw_columns] 

3435 for attr in ( 

3436 "_whereclause", 

3437 "_having", 

3438 "_order_by_clause", 

3439 "_group_by_clause", 

3440 "_for_update_arg", 

3441 ): 

3442 if getattr(self, attr) is not None: 

3443 setattr(self, attr, clone(getattr(self, attr), **kw)) 

3444 

3445 # erase exported column list, _froms collection, 

3446 # etc. 

3447 self._reset_exported() 

3448 

3449 def get_children(self, column_collections=True, **kwargs): 

3450 """return child elements as per the ClauseElement specification.""" 

3451 

3452 return ( 

3453 (column_collections and list(self.columns) or []) 

3454 + self._raw_columns 

3455 + list(self._froms) 

3456 + [ 

3457 x 

3458 for x in ( 

3459 self._whereclause, 

3460 self._having, 

3461 self._order_by_clause, 

3462 self._group_by_clause, 

3463 ) 

3464 if x is not None 

3465 ] 

3466 ) 

3467 

3468 @_generative 

3469 def column(self, column): 

3470 """return a new select() construct with the given column expression 

3471 added to its columns clause. 

3472 

3473 E.g.:: 

3474 

3475 my_select = my_select.column(table.c.new_column) 

3476 

3477 See the documentation for 

3478 :meth:`_expression.Select.with_only_columns` 

3479 for guidelines on adding /replacing the columns of a 

3480 :class:`_expression.Select` object. 

3481 

3482 """ 

3483 self.append_column(column) 

3484 

3485 @util.dependencies("sqlalchemy.sql.util") 

3486 def reduce_columns(self, sqlutil, only_synonyms=True): 

3487 """Return a new :func`.select` construct with redundantly 

3488 named, equivalently-valued columns removed from the columns clause. 

3489 

3490 "Redundant" here means two columns where one refers to the 

3491 other either based on foreign key, or via a simple equality 

3492 comparison in the WHERE clause of the statement. The primary purpose 

3493 of this method is to automatically construct a select statement 

3494 with all uniquely-named columns, without the need to use 

3495 table-qualified labels as :meth:`_expression.Select.apply_labels` does 

3496 . 

3497 

3498 When columns are omitted based on foreign key, the referred-to 

3499 column is the one that's kept. When columns are omitted based on 

3500 WHERE equivalence, the first column in the columns clause is the 

3501 one that's kept. 

3502 

3503 :param only_synonyms: when True, limit the removal of columns 

3504 to those which have the same name as the equivalent. Otherwise, 

3505 all columns that are equivalent to another are removed. 

3506 

3507 """ 

3508 return self.with_only_columns( 

3509 sqlutil.reduce_columns( 

3510 self.inner_columns, 

3511 only_synonyms=only_synonyms, 

3512 *(self._whereclause,) + tuple(self._from_obj) 

3513 ) 

3514 ) 

3515 

3516 @_generative 

3517 def with_only_columns(self, columns): 

3518 r"""Return a new :func:`_expression.select` construct with its columns 

3519 clause replaced with the given columns. 

3520 

3521 This method is exactly equivalent to as if the original 

3522 :func:`_expression.select` had been called with the given columns 

3523 clause. I.e. a statement:: 

3524 

3525 s = select([table1.c.a, table1.c.b]) 

3526 s = s.with_only_columns([table1.c.b]) 

3527 

3528 should be exactly equivalent to:: 

3529 

3530 s = select([table1.c.b]) 

3531 

3532 This means that FROM clauses which are only derived 

3533 from the column list will be discarded if the new column 

3534 list no longer contains that FROM:: 

3535 

3536 >>> table1 = table('t1', column('a'), column('b')) 

3537 >>> table2 = table('t2', column('a'), column('b')) 

3538 >>> s1 = select([table1.c.a, table2.c.b]) 

3539 >>> print(s1) 

3540 SELECT t1.a, t2.b FROM t1, t2 

3541 >>> s2 = s1.with_only_columns([table2.c.b]) 

3542 >>> print(s2) 

3543 SELECT t2.b FROM t1 

3544 

3545 The preferred way to maintain a specific FROM clause 

3546 in the construct, assuming it won't be represented anywhere 

3547 else (i.e. not in the WHERE clause, etc.) is to set it using 

3548 :meth:`_expression.Select.select_from`:: 

3549 

3550 >>> s1 = select([table1.c.a, table2.c.b]).\ 

3551 ... select_from(table1.join(table2, 

3552 ... table1.c.a==table2.c.a)) 

3553 >>> s2 = s1.with_only_columns([table2.c.b]) 

3554 >>> print(s2) 

3555 SELECT t2.b FROM t1 JOIN t2 ON t1.a=t2.a 

3556 

3557 Care should also be taken to use the correct 

3558 set of column objects passed to 

3559 :meth:`_expression.Select.with_only_columns`. 

3560 Since the method is essentially equivalent to calling the 

3561 :func:`_expression.select` construct in the first place with the given 

3562 columns, the columns passed to 

3563 :meth:`_expression.Select.with_only_columns` 

3564 should usually be a subset of those which were passed 

3565 to the :func:`_expression.select` construct, 

3566 not those which are available 

3567 from the ``.c`` collection of that :func:`_expression.select`. That 

3568 is:: 

3569 

3570 s = select([table1.c.a, table1.c.b]).select_from(table1) 

3571 s = s.with_only_columns([table1.c.b]) 

3572 

3573 and **not**:: 

3574 

3575 # usually incorrect 

3576 s = s.with_only_columns([s.c.b]) 

3577 

3578 The latter would produce the SQL:: 

3579 

3580 SELECT b 

3581 FROM (SELECT t1.a AS a, t1.b AS b 

3582 FROM t1), t1 

3583 

3584 Since the :func:`_expression.select` construct is essentially being 

3585 asked to select both from ``table1`` as well as itself. 

3586 

3587 """ 

3588 self._reset_exported() 

3589 rc = [] 

3590 for c in columns: 

3591 c = _interpret_as_column_or_from(c) 

3592 if isinstance(c, ScalarSelect): 

3593 c = c.self_group(against=operators.comma_op) 

3594 rc.append(c) 

3595 self._raw_columns = rc 

3596 

3597 @_generative 

3598 def where(self, whereclause): 

3599 """return a new select() construct with the given expression added to 

3600 its WHERE clause, joined to the existing clause via AND, if any. 

3601 

3602 """ 

3603 

3604 self.append_whereclause(whereclause) 

3605 

3606 @_generative 

3607 def having(self, having): 

3608 """return a new select() construct with the given expression added to 

3609 its HAVING clause, joined to the existing clause via AND, if any. 

3610 

3611 """ 

3612 self.append_having(having) 

3613 

3614 @_generative 

3615 def distinct(self, *expr): 

3616 r"""Return a new select() construct which will apply DISTINCT to its 

3617 columns clause. 

3618 

3619 :param \*expr: optional column expressions. When present, 

3620 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>>)`` 

3621 construct. 

3622 

3623 """ 

3624 if expr: 

3625 expr = [_literal_as_label_reference(e) for e in expr] 

3626 if isinstance(self._distinct, list): 

3627 self._distinct = self._distinct + expr 

3628 else: 

3629 self._distinct = expr 

3630 else: 

3631 self._distinct = True 

3632 

3633 @_generative 

3634 def select_from(self, fromclause): 

3635 r"""return a new :func:`_expression.select` construct with the 

3636 given FROM expression 

3637 merged into its list of FROM objects. 

3638 

3639 E.g.:: 

3640 

3641 table1 = table('t1', column('a')) 

3642 table2 = table('t2', column('b')) 

3643 s = select([table1.c.a]).\ 

3644 select_from( 

3645 table1.join(table2, table1.c.a==table2.c.b) 

3646 ) 

3647 

3648 The "from" list is a unique set on the identity of each element, 

3649 so adding an already present :class:`_schema.Table` 

3650 or other selectable 

3651 will have no effect. Passing a :class:`_expression.Join` that refers 

3652 to an already present :class:`_schema.Table` 

3653 or other selectable will have 

3654 the effect of concealing the presence of that selectable as 

3655 an individual element in the rendered FROM list, instead 

3656 rendering it into a JOIN clause. 

3657 

3658 While the typical purpose of :meth:`_expression.Select.select_from` 

3659 is to 

3660 replace the default, derived FROM clause with a join, it can 

3661 also be called with individual table elements, multiple times 

3662 if desired, in the case that the FROM clause cannot be fully 

3663 derived from the columns clause:: 

3664 

3665 select([func.count('*')]).select_from(table1) 

3666 

3667 """ 

3668 self.append_from(fromclause) 

3669 

3670 @_generative 

3671 def correlate(self, *fromclauses): 

3672 r"""return a new :class:`_expression.Select` 

3673 which will correlate the given FROM 

3674 clauses to that of an enclosing :class:`_expression.Select`. 

3675 

3676 Calling this method turns off the :class:`_expression.Select` object's 

3677 default behavior of "auto-correlation". Normally, FROM elements 

3678 which appear in a :class:`_expression.Select` 

3679 that encloses this one via 

3680 its :term:`WHERE clause`, ORDER BY, HAVING or 

3681 :term:`columns clause` will be omitted from this 

3682 :class:`_expression.Select` 

3683 object's :term:`FROM clause`. 

3684 Setting an explicit correlation collection using the 

3685 :meth:`_expression.Select.correlate` 

3686 method provides a fixed list of FROM objects 

3687 that can potentially take place in this process. 

3688 

3689 When :meth:`_expression.Select.correlate` 

3690 is used to apply specific FROM clauses 

3691 for correlation, the FROM elements become candidates for 

3692 correlation regardless of how deeply nested this 

3693 :class:`_expression.Select` 

3694 object is, relative to an enclosing :class:`_expression.Select` 

3695 which refers to 

3696 the same FROM object. This is in contrast to the behavior of 

3697 "auto-correlation" which only correlates to an immediate enclosing 

3698 :class:`_expression.Select`. 

3699 Multi-level correlation ensures that the link 

3700 between enclosed and enclosing :class:`_expression.Select` 

3701 is always via 

3702 at least one WHERE/ORDER BY/HAVING/columns clause in order for 

3703 correlation to take place. 

3704 

3705 If ``None`` is passed, the :class:`_expression.Select` 

3706 object will correlate 

3707 none of its FROM entries, and all will render unconditionally 

3708 in the local FROM clause. 

3709 

3710 :param \*fromclauses: a list of one or more 

3711 :class:`_expression.FromClause` 

3712 constructs, or other compatible constructs (i.e. ORM-mapped 

3713 classes) to become part of the correlate collection. 

3714 

3715 .. seealso:: 

3716 

3717 :meth:`_expression.Select.correlate_except` 

3718 

3719 :ref:`correlated_subqueries` 

3720 

3721 """ 

3722 self._auto_correlate = False 

3723 if fromclauses and fromclauses[0] is None: 

3724 self._correlate = () 

3725 else: 

3726 self._correlate = set(self._correlate).union( 

3727 _interpret_as_from(f) for f in fromclauses 

3728 ) 

3729 

3730 @_generative 

3731 def correlate_except(self, *fromclauses): 

3732 r"""return a new :class:`_expression.Select` 

3733 which will omit the given FROM 

3734 clauses from the auto-correlation process. 

3735 

3736 Calling :meth:`_expression.Select.correlate_except` turns off the 

3737 :class:`_expression.Select` object's default behavior of 

3738 "auto-correlation" for the given FROM elements. An element 

3739 specified here will unconditionally appear in the FROM list, while 

3740 all other FROM elements remain subject to normal auto-correlation 

3741 behaviors. 

3742 

3743 If ``None`` is passed, the :class:`_expression.Select` 

3744 object will correlate 

3745 all of its FROM entries. 

3746 

3747 :param \*fromclauses: a list of one or more 

3748 :class:`_expression.FromClause` 

3749 constructs, or other compatible constructs (i.e. ORM-mapped 

3750 classes) to become part of the correlate-exception collection. 

3751 

3752 .. seealso:: 

3753 

3754 :meth:`_expression.Select.correlate` 

3755 

3756 :ref:`correlated_subqueries` 

3757 

3758 """ 

3759 

3760 self._auto_correlate = False 

3761 if fromclauses and fromclauses[0] is None: 

3762 self._correlate_except = () 

3763 else: 

3764 self._correlate_except = set(self._correlate_except or ()).union( 

3765 _interpret_as_from(f) for f in fromclauses 

3766 ) 

3767 

3768 def append_correlation(self, fromclause): 

3769 """append the given correlation expression to this select() 

3770 construct. 

3771 

3772 This is an **in-place** mutation method; the 

3773 :meth:`_expression.Select.correlate` method is preferred, 

3774 as it provides 

3775 standard :term:`method chaining`. 

3776 

3777 """ 

3778 

3779 self._auto_correlate = False 

3780 self._correlate = set(self._correlate).union( 

3781 _interpret_as_from(f) for f in fromclause 

3782 ) 

3783 

3784 def append_column(self, column): 

3785 """append the given column expression to the columns clause of this 

3786 select() construct. 

3787 

3788 E.g.:: 

3789 

3790 my_select.append_column(some_table.c.new_column) 

3791 

3792 This is an **in-place** mutation method; the 

3793 :meth:`_expression.Select.column` method is preferred, 

3794 as it provides standard 

3795 :term:`method chaining`. 

3796 

3797 See the documentation for :meth:`_expression.Select.with_only_columns` 

3798 for guidelines on adding /replacing the columns of a 

3799 :class:`_expression.Select` object. 

3800 

3801 """ 

3802 self._reset_exported() 

3803 column = _interpret_as_column_or_from(column) 

3804 

3805 if isinstance(column, ScalarSelect): 

3806 column = column.self_group(against=operators.comma_op) 

3807 

3808 self._raw_columns = self._raw_columns + [column] 

3809 

3810 def append_prefix(self, clause): 

3811 """append the given columns clause prefix expression to this select() 

3812 construct. 

3813 

3814 This is an **in-place** mutation method; the 

3815 :meth:`_expression.Select.prefix_with` method is preferred, 

3816 as it provides 

3817 standard :term:`method chaining`. 

3818 

3819 """ 

3820 clause = _literal_as_text(clause) 

3821 self._prefixes = self._prefixes + (clause,) 

3822 

3823 def append_whereclause(self, whereclause): 

3824 """append the given expression to this select() construct's WHERE 

3825 criterion. 

3826 

3827 The expression will be joined to existing WHERE criterion via AND. 

3828 

3829 This is an **in-place** mutation method; the 

3830 :meth:`_expression.Select.where` method is preferred, 

3831 as it provides standard 

3832 :term:`method chaining`. 

3833 

3834 """ 

3835 

3836 self._reset_exported() 

3837 self._whereclause = and_(True_._ifnone(self._whereclause), whereclause) 

3838 

3839 def append_having(self, having): 

3840 """append the given expression to this select() construct's HAVING 

3841 criterion. 

3842 

3843 The expression will be joined to existing HAVING criterion via AND. 

3844 

3845 This is an **in-place** mutation method; the 

3846 :meth:`_expression.Select.having` method is preferred, 

3847 as it provides standard 

3848 :term:`method chaining`. 

3849 

3850 """ 

3851 self._reset_exported() 

3852 self._having = and_(True_._ifnone(self._having), having) 

3853 

3854 def append_from(self, fromclause): 

3855 """append the given FromClause expression to this select() construct's 

3856 FROM clause. 

3857 

3858 This is an **in-place** mutation method; the 

3859 :meth:`_expression.Select.select_from` method is preferred, 

3860 as it provides 

3861 standard :term:`method chaining`. 

3862 

3863 """ 

3864 self._reset_exported() 

3865 fromclause = _interpret_as_from(fromclause) 

3866 self._from_obj = self._from_obj.union([fromclause]) 

3867 

3868 @_memoized_property 

3869 def _columns_plus_names(self): 

3870 if self.use_labels: 

3871 names = set() 

3872 

3873 def name_for_col(c): 

3874 if c._label is None or not c._render_label_in_columns_clause: 

3875 return (None, c) 

3876 

3877 name = c._label 

3878 if name in names: 

3879 name = c.anon_label 

3880 else: 

3881 names.add(name) 

3882 return name, c 

3883 

3884 return [ 

3885 name_for_col(c) 

3886 for c in util.unique_list(_select_iterables(self._raw_columns)) 

3887 ] 

3888 else: 

3889 return [ 

3890 (None, c) 

3891 for c in util.unique_list(_select_iterables(self._raw_columns)) 

3892 ] 

3893 

3894 def _populate_column_collection(self): 

3895 for name, c in self._columns_plus_names: 

3896 if not hasattr(c, "_make_proxy"): 

3897 continue 

3898 if name is None: 

3899 key = None 

3900 elif self.use_labels: 

3901 key = c._key_label 

3902 if key is not None and key in self.c: 

3903 key = c.anon_label 

3904 else: 

3905 key = None 

3906 c._make_proxy(self, key=key, name=name, name_is_truncatable=True) 

3907 

3908 def _refresh_for_new_column(self, column): 

3909 for fromclause in self._froms: 

3910 col = fromclause._refresh_for_new_column(column) 

3911 if col is not None: 

3912 if col in self.inner_columns and self._cols_populated: 

3913 our_label = col._key_label if self.use_labels else col.key 

3914 if our_label not in self.c: 

3915 return col._make_proxy( 

3916 self, 

3917 name=col._label if self.use_labels else None, 

3918 key=col._key_label if self.use_labels else None, 

3919 name_is_truncatable=True, 

3920 ) 

3921 return None 

3922 return None 

3923 

3924 def _needs_parens_for_grouping(self): 

3925 return ( 

3926 self._limit_clause is not None 

3927 or self._offset_clause is not None 

3928 or bool(self._order_by_clause.clauses) 

3929 ) 

3930 

3931 def self_group(self, against=None): 

3932 """return a 'grouping' construct as per the ClauseElement 

3933 specification. 

3934 

3935 This produces an element that can be embedded in an expression. Note 

3936 that this method is called automatically as needed when constructing 

3937 expressions and should not require explicit use. 

3938 

3939 """ 

3940 if ( 

3941 isinstance(against, CompoundSelect) 

3942 and not self._needs_parens_for_grouping() 

3943 ): 

3944 return self 

3945 return FromGrouping(self) 

3946 

3947 def union(self, other, **kwargs): 

3948 """return a SQL UNION of this select() construct against the given 

3949 selectable.""" 

3950 

3951 return CompoundSelect._create_union(self, other, **kwargs) 

3952 

3953 def union_all(self, other, **kwargs): 

3954 """return a SQL UNION ALL of this select() construct against the given 

3955 selectable. 

3956 

3957 """ 

3958 return CompoundSelect._create_union_all(self, other, **kwargs) 

3959 

3960 def except_(self, other, **kwargs): 

3961 """return a SQL EXCEPT of this select() construct against the given 

3962 selectable.""" 

3963 

3964 return CompoundSelect._create_except(self, other, **kwargs) 

3965 

3966 def except_all(self, other, **kwargs): 

3967 """return a SQL EXCEPT ALL of this select() construct against the 

3968 given selectable. 

3969 

3970 """ 

3971 return CompoundSelect._create_except_all(self, other, **kwargs) 

3972 

3973 def intersect(self, other, **kwargs): 

3974 """return a SQL INTERSECT of this select() construct against the given 

3975 selectable. 

3976 

3977 """ 

3978 return CompoundSelect._create_intersect(self, other, **kwargs) 

3979 

3980 def intersect_all(self, other, **kwargs): 

3981 """return a SQL INTERSECT ALL of this select() construct against the 

3982 given selectable. 

3983 

3984 """ 

3985 return CompoundSelect._create_intersect_all(self, other, **kwargs) 

3986 

3987 def bind(self): 

3988 if self._bind: 

3989 return self._bind 

3990 froms = self._froms 

3991 if not froms: 

3992 for c in self._raw_columns: 

3993 e = c.bind 

3994 if e: 

3995 self._bind = e 

3996 return e 

3997 else: 

3998 e = list(froms)[0].bind 

3999 if e: 

4000 self._bind = e 

4001 return e 

4002 

4003 return None 

4004 

4005 def _set_bind(self, bind): 

4006 self._bind = bind 

4007 

4008 bind = property(bind, _set_bind) 

4009 

4010 

4011class ScalarSelect(Generative, Grouping): 

4012 _from_objects = [] 

4013 _is_from_container = True 

4014 _is_implicitly_boolean = False 

4015 

4016 def __init__(self, element): 

4017 self.element = element 

4018 self.type = element._scalar_type() 

4019 

4020 @property 

4021 def columns(self): 

4022 raise exc.InvalidRequestError( 

4023 "Scalar Select expression has no " 

4024 "columns; use this object directly " 

4025 "within a column-level expression." 

4026 ) 

4027 

4028 c = columns 

4029 

4030 @_generative 

4031 def where(self, crit): 

4032 """Apply a WHERE clause to the SELECT statement referred to 

4033 by this :class:`_expression.ScalarSelect`. 

4034 

4035 """ 

4036 self.element = self.element.where(crit) 

4037 

4038 def self_group(self, **kwargs): 

4039 return self 

4040 

4041 

4042class Exists(UnaryExpression): 

4043 """Represent an ``EXISTS`` clause. 

4044 

4045 """ 

4046 

4047 __visit_name__ = UnaryExpression.__visit_name__ 

4048 _from_objects = [] 

4049 

4050 def __init__(self, *args, **kwargs): 

4051 """Construct a new :class:`_expression.Exists` against an existing 

4052 :class:`_expression.Select` object. 

4053 

4054 Calling styles are of the following forms:: 

4055 

4056 # use on an existing select() 

4057 s = select([table.c.col1]).where(table.c.col2==5) 

4058 s = exists(s) 

4059 

4060 # construct a select() at once 

4061 exists(['*'], **select_arguments).where(criterion) 

4062 

4063 # columns argument is optional, generates "EXISTS (SELECT *)" 

4064 # by default. 

4065 exists().where(table.c.col2==5) 

4066 

4067 """ 

4068 if args and isinstance(args[0], (SelectBase, ScalarSelect)): 

4069 s = args[0] 

4070 else: 

4071 if not args: 

4072 args = ([literal_column("*")],) 

4073 s = Select(*args, **kwargs).as_scalar().self_group() 

4074 

4075 UnaryExpression.__init__( 

4076 self, 

4077 s, 

4078 operator=operators.exists, 

4079 type_=type_api.BOOLEANTYPE, 

4080 wraps_column_expression=True, 

4081 ) 

4082 

4083 def select(self, whereclause=None, **params): 

4084 return Select([self], whereclause, **params) 

4085 

4086 def correlate(self, *fromclause): 

4087 e = self._clone() 

4088 e.element = self.element.correlate(*fromclause).self_group() 

4089 return e 

4090 

4091 def correlate_except(self, *fromclause): 

4092 e = self._clone() 

4093 e.element = self.element.correlate_except(*fromclause).self_group() 

4094 return e 

4095 

4096 def select_from(self, clause): 

4097 """return a new :class:`_expression.Exists` construct, 

4098 applying the given 

4099 expression to the :meth:`_expression.Select.select_from` 

4100 method of the select 

4101 statement contained. 

4102 

4103 """ 

4104 e = self._clone() 

4105 e.element = self.element.select_from(clause).self_group() 

4106 return e 

4107 

4108 def where(self, clause): 

4109 """return a new exists() construct with the given expression added to 

4110 its WHERE clause, joined to the existing clause via AND, if any. 

4111 

4112 """ 

4113 e = self._clone() 

4114 e.element = self.element.where(clause).self_group() 

4115 return e 

4116 

4117 

4118class TextAsFrom(SelectBase): 

4119 """Wrap a :class:`_expression.TextClause` construct within a 

4120 :class:`_expression.SelectBase` 

4121 interface. 

4122 

4123 This allows the :class:`_expression.TextClause` object to gain a ``. 

4124 c`` collection 

4125 and other FROM-like capabilities such as 

4126 :meth:`_expression.FromClause.alias`, 

4127 :meth:`_expression.SelectBase.cte`, etc. 

4128 

4129 The :class:`.TextAsFrom` construct is produced via the 

4130 :meth:`_expression.TextClause.columns` 

4131 method - see that method for details. 

4132 

4133 .. versionadded:: 0.9.0 

4134 

4135 .. seealso:: 

4136 

4137 :func:`_expression.text` 

4138 

4139 :meth:`_expression.TextClause.columns` 

4140 

4141 """ 

4142 

4143 __visit_name__ = "text_as_from" 

4144 

4145 _textual = True 

4146 

4147 def __init__(self, text, columns, positional=False): 

4148 self.element = text 

4149 self.column_args = columns 

4150 self.positional = positional 

4151 

4152 @property 

4153 def _bind(self): 

4154 return self.element._bind 

4155 

4156 @_generative 

4157 def bindparams(self, *binds, **bind_as_values): 

4158 self.element = self.element.bindparams(*binds, **bind_as_values) 

4159 

4160 def _populate_column_collection(self): 

4161 for c in self.column_args: 

4162 c._make_proxy(self) 

4163 

4164 def _copy_internals(self, clone=_clone, **kw): 

4165 self._reset_exported() 

4166 self.element = clone(self.element, **kw) 

4167 

4168 def _scalar_type(self): 

4169 return self.column_args[0].type 

4170 

4171 

4172class AnnotatedFromClause(Annotated): 

4173 def __init__(self, element, values): 

4174 # force FromClause to generate their internal 

4175 # collections into __dict__ 

4176 element.c 

4177 Annotated.__init__(self, element, values)