Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/_selectable_constructors.py: 64%

1# sql/_selectable_constructors.py 

2# Copyright (C) 2005-2024 the SQLAlchemy authors and contributors 

3# <see AUTHORS file> 

4# 

5# This module is part of SQLAlchemy and is released under 

6# the MIT License: https://www.opensource.org/licenses/mit-license.php 

7 

8from __future__ import annotations 

9 

10from typing import Any 

11from typing import Optional 

12from typing import overload 

13from typing import TYPE_CHECKING 

14from typing import TypeVar 

15from typing import Union 

16 

17from . import coercions 

18from . import roles 

19from ._typing import _ColumnsClauseArgument 

20from ._typing import _no_kw 

21from .elements import ColumnClause 

22from .selectable import Alias 

23from .selectable import CompoundSelect 

24from .selectable import Exists 

25from .selectable import FromClause 

26from .selectable import Join 

27from .selectable import Lateral 

28from .selectable import LateralFromClause 

29from .selectable import NamedFromClause 

30from .selectable import Select 

31from .selectable import TableClause 

32from .selectable import TableSample 

33from .selectable import Values 

34from ..util.typing import TupleAny 

35from ..util.typing import Unpack 

36 

37if TYPE_CHECKING: 

38 from ._typing import _FromClauseArgument 

39 from ._typing import _OnClauseArgument 

40 from ._typing import _SelectStatementForCompoundArgument 

41 from ._typing import _T0 

42 from ._typing import _T1 

43 from ._typing import _T2 

44 from ._typing import _T3 

45 from ._typing import _T4 

46 from ._typing import _T5 

47 from ._typing import _T6 

48 from ._typing import _T7 

49 from ._typing import _T8 

50 from ._typing import _T9 

51 from ._typing import _TypedColumnClauseArgument as _TCCA 

52 from .functions import Function 

53 from .selectable import CTE 

54 from .selectable import HasCTE 

55 from .selectable import ScalarSelect 

56 from .selectable import SelectBase 

57 

58 

59_T = TypeVar("_T", bound=Any) 

60 

61 

62def alias( 

63 selectable: FromClause, name: Optional[str] = None, flat: bool = False 

64) -> NamedFromClause: 

65 """Return a named alias of the given :class:`.FromClause`. 

66 

67 For :class:`.Table` and :class:`.Join` objects, the return type is the 

68 :class:`_expression.Alias` object. Other kinds of :class:`.NamedFromClause` 

69 objects may be returned for other kinds of :class:`.FromClause` objects. 

70 

71 The named alias represents any :class:`_expression.FromClause` with an 

72 alternate name assigned within SQL, typically using the ``AS`` clause when 

73 generated, e.g. ``SELECT * FROM table AS aliasname``. 

74 

75 Equivalent functionality is available via the 

76 :meth:`_expression.FromClause.alias` 

77 method available on all :class:`_expression.FromClause` objects. 

78 

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

80 such as a table, select statement, etc. 

81 

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

83 If ``None``, a name will be deterministically generated at compile 

84 time. Deterministic means the name is guaranteed to be unique against 

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

86 name for each successive compilation of the same statement object. 

87 

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

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

90 :meth:`_expression.Join.alias` for details. 

91 

92 """ 

93 return Alias._factory(selectable, name=name, flat=flat) 

94 

95 

96def cte( 

97 selectable: HasCTE, name: Optional[str] = None, recursive: bool = False 

98) -> CTE: 

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

100 or Common Table Expression instance. 

101 

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

103 

104 """ 

105 return coercions.expect(roles.HasCTERole, selectable).cte( 

106 name=name, recursive=recursive 

107 ) 

108 

109 

110def except_( 

111 *selects: _SelectStatementForCompoundArgument, 

112) -> CompoundSelect: 

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

114 

115 The returned object is an instance of 

116 :class:`_expression.CompoundSelect`. 

117 

118 :param \*selects: 

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

120 

121 """ 

122 return CompoundSelect._create_except(*selects) 

123 

124 

125def except_all( 

126 *selects: _SelectStatementForCompoundArgument, 

127) -> CompoundSelect: 

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

129 

130 The returned object is an instance of 

131 :class:`_expression.CompoundSelect`. 

132 

133 :param \*selects: 

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

135 

136 """ 

137 return CompoundSelect._create_except_all(*selects) 

138 

139 

140def exists( 

141 __argument: Optional[ 

142 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]] 

143 ] = None, 

144 /, 

145) -> Exists: 

146 """Construct a new :class:`_expression.Exists` construct. 

147 

148 The :func:`_sql.exists` can be invoked by itself to produce an 

149 :class:`_sql.Exists` construct, which will accept simple WHERE 

150 criteria:: 

151 

152 exists_criteria = exists().where(table1.c.col1 == table2.c.col2) 

153 

154 However, for greater flexibility in constructing the SELECT, an 

155 existing :class:`_sql.Select` construct may be converted to an 

156 :class:`_sql.Exists`, most conveniently by making use of the 

157 :meth:`_sql.SelectBase.exists` method:: 

158 

159 exists_criteria = ( 

160 select(table2.c.col2). 

161 where(table1.c.col1 == table2.c.col2). 

162 exists() 

163 ) 

164 

165 The EXISTS criteria is then used inside of an enclosing SELECT:: 

166 

167 stmt = select(table1.c.col1).where(exists_criteria) 

168 

169 The above statement will then be of the form:: 

170 

171 SELECT col1 FROM table1 WHERE EXISTS 

172 (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1) 

173 

174 .. seealso:: 

175 

176 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial. 

177 

178 :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an 

179 ``EXISTS`` clause. 

180 

181 """ # noqa: E501 

182 

183 return Exists(__argument) 

184 

185 

186def intersect( 

187 *selects: _SelectStatementForCompoundArgument, 

188) -> CompoundSelect: 

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

190 

191 The returned object is an instance of 

192 :class:`_expression.CompoundSelect`. 

193 

194 :param \*selects: 

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

196 

197 """ 

198 return CompoundSelect._create_intersect(*selects) 

199 

200 

201def intersect_all( 

202 *selects: _SelectStatementForCompoundArgument, 

203) -> CompoundSelect: 

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

205 

206 The returned object is an instance of 

207 :class:`_expression.CompoundSelect`. 

208 

209 :param \*selects: 

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

211 

212 

213 """ 

214 return CompoundSelect._create_intersect_all(*selects) 

215 

216 

217def join( 

218 left: _FromClauseArgument, 

219 right: _FromClauseArgument, 

220 onclause: Optional[_OnClauseArgument] = None, 

221 isouter: bool = False, 

222 full: bool = False, 

223) -> Join: 

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

225 :class:`_expression.FromClause` 

226 expressions. 

227 

228 E.g.:: 

229 

230 j = join(user_table, address_table, 

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

232 stmt = select(user_table).select_from(j) 

233 

234 would emit SQL along the lines of:: 

235 

236 SELECT user.id, user.name FROM user 

237 JOIN address ON user.id = address.user_id 

238 

239 Similar functionality is available given any 

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

241 :class:`_schema.Table`) using 

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

243 

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

245 

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

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

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

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

250 class. 

251 

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

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

254 will attempt to 

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

256 

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

258 

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

260 

261 .. seealso:: 

262 

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

264 based on a given left side. 

265 

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

267 

268 """ 

269 

270 return Join(left, right, onclause, isouter, full) 

271 

272 

273def lateral( 

274 selectable: Union[SelectBase, _FromClauseArgument], 

275 name: Optional[str] = None, 

276) -> LateralFromClause: 

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

278 

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

280 subclass that represents 

281 a subquery with the LATERAL keyword applied to it. 

282 

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

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

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

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

287 PostgreSQL versions. 

288 

289 .. seealso:: 

290 

291 :ref:`tutorial_lateral_correlation` - overview of usage. 

292 

293 """ 

294 return Lateral._factory(selectable, name=name) 

295 

296 

297def outerjoin( 

298 left: _FromClauseArgument, 

299 right: _FromClauseArgument, 

300 onclause: Optional[_OnClauseArgument] = None, 

301 full: bool = False, 

302) -> Join: 

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

304 

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

306 

307 Similar functionality is also available via the 

308 :meth:`_expression.FromClause.outerjoin` method on any 

309 :class:`_expression.FromClause`. 

310 

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

312 

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

314 

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

316 derived from foreign key relationships established between 

317 left and right otherwise. 

318 

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

320 or 

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

322 :class:`_expression.Join` object. 

323 

324 """ 

325 return Join(left, right, onclause, isouter=True, full=full) 

326 

327 

328# START OVERLOADED FUNCTIONS select Select 1-10 

329 

330# code within this block is **programmatically, 

331# statically generated** by tools/generate_tuple_map_overloads.py 

332 

333 

334@overload 

335def select(__ent0: _TCCA[_T0], /) -> Select[_T0]: ... 

336 

337 

338@overload 

339def select(__ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /) -> Select[_T0, _T1]: ... 

340 

341 

342@overload 

343def select( 

344 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], / 

345) -> Select[_T0, _T1, _T2]: ... 

346 

347 

348@overload 

349def select( 

350 __ent0: _TCCA[_T0], 

351 __ent1: _TCCA[_T1], 

352 __ent2: _TCCA[_T2], 

353 __ent3: _TCCA[_T3], 

354 /, 

355) -> Select[_T0, _T1, _T2, _T3]: ... 

356 

357 

358@overload 

359def select( 

360 __ent0: _TCCA[_T0], 

361 __ent1: _TCCA[_T1], 

362 __ent2: _TCCA[_T2], 

363 __ent3: _TCCA[_T3], 

364 __ent4: _TCCA[_T4], 

365 /, 

366) -> Select[_T0, _T1, _T2, _T3, _T4]: ... 

367 

368 

369@overload 

370def select( 

371 __ent0: _TCCA[_T0], 

372 __ent1: _TCCA[_T1], 

373 __ent2: _TCCA[_T2], 

374 __ent3: _TCCA[_T3], 

375 __ent4: _TCCA[_T4], 

376 __ent5: _TCCA[_T5], 

377 /, 

378) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ... 

379 

380 

381@overload 

382def select( 

383 __ent0: _TCCA[_T0], 

384 __ent1: _TCCA[_T1], 

385 __ent2: _TCCA[_T2], 

386 __ent3: _TCCA[_T3], 

387 __ent4: _TCCA[_T4], 

388 __ent5: _TCCA[_T5], 

389 __ent6: _TCCA[_T6], 

390 /, 

391) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ... 

392 

393 

394@overload 

395def select( 

396 __ent0: _TCCA[_T0], 

397 __ent1: _TCCA[_T1], 

398 __ent2: _TCCA[_T2], 

399 __ent3: _TCCA[_T3], 

400 __ent4: _TCCA[_T4], 

401 __ent5: _TCCA[_T5], 

402 __ent6: _TCCA[_T6], 

403 __ent7: _TCCA[_T7], 

404 /, 

405) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]: ... 

406 

407 

408@overload 

409def select( 

410 __ent0: _TCCA[_T0], 

411 __ent1: _TCCA[_T1], 

412 __ent2: _TCCA[_T2], 

413 __ent3: _TCCA[_T3], 

414 __ent4: _TCCA[_T4], 

415 __ent5: _TCCA[_T5], 

416 __ent6: _TCCA[_T6], 

417 __ent7: _TCCA[_T7], 

418 __ent8: _TCCA[_T8], 

419 /, 

420) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8]: ... 

421 

422 

423@overload 

424def select( 

425 __ent0: _TCCA[_T0], 

426 __ent1: _TCCA[_T1], 

427 __ent2: _TCCA[_T2], 

428 __ent3: _TCCA[_T3], 

429 __ent4: _TCCA[_T4], 

430 __ent5: _TCCA[_T5], 

431 __ent6: _TCCA[_T6], 

432 __ent7: _TCCA[_T7], 

433 __ent8: _TCCA[_T8], 

434 __ent9: _TCCA[_T9], 

435 /, 

436 *entities: _ColumnsClauseArgument[Any], 

437) -> Select[ 

438 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8, _T9, Unpack[TupleAny] 

439]: ... 

440 

441 

442# END OVERLOADED FUNCTIONS select 

443 

444 

445@overload 

446def select( 

447 *entities: _ColumnsClauseArgument[Any], **__kw: Any 

448) -> Select[Unpack[TupleAny]]: ... 

449 

450 

451def select( 

452 *entities: _ColumnsClauseArgument[Any], **__kw: Any 

453) -> Select[Unpack[TupleAny]]: 

454 r"""Construct a new :class:`_expression.Select`. 

455 

456 

457 .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts 

458 column arguments positionally. The top-level :func:`_sql.select` 

459 function will automatically use the 1.x or 2.x style API based on 

460 the incoming arguments; using :func:`_sql.select` from the 

461 ``sqlalchemy.future`` module will enforce that only the 2.x style 

462 constructor is used. 

463 

464 Similar functionality is also available via the 

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

466 :class:`_expression.FromClause`. 

467 

468 .. seealso:: 

469 

470 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial` 

471 

472 :param \*entities: 

473 Entities to SELECT from. For Core usage, this is typically a series 

474 of :class:`_expression.ColumnElement` and / or 

475 :class:`_expression.FromClause` 

476 objects which will form the columns clause of the resulting 

477 statement. For those objects that are instances of 

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

479 or :class:`_expression.Alias` 

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

481 collection is extracted 

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

483 

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

485 constructs as 

486 given, as well as ORM-mapped classes. 

487 

488 """ 

489 # the keyword args are a necessary element in order for the typing 

490 # to work out w/ the varargs vs. having named "keyword" arguments that 

491 # aren't always present. 

492 if __kw: 

493 raise _no_kw() 

494 return Select(*entities) 

495 

496 

497def table(name: str, *columns: ColumnClause[Any], **kw: Any) -> TableClause: 

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

499 

500 The object returned is an instance of 

501 :class:`_expression.TableClause`, which 

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

503 :class:`_schema.Table` object. 

504 It may be used to construct lightweight table constructs. 

505 

506 :param name: Name of the table. 

507 

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

509 

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

511 

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

513 accept a ``schema`` argument. 

514 """ 

515 

516 return TableClause(name, *columns, **kw) 

517 

518 

519def tablesample( 

520 selectable: _FromClauseArgument, 

521 sampling: Union[float, Function[Any]], 

522 name: Optional[str] = None, 

523 seed: Optional[roles.ExpressionElementRole[Any]] = None, 

524) -> TableSample: 

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

526 

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

528 subclass that represents 

529 a table with the TABLESAMPLE clause applied to it. 

530 :func:`_expression.tablesample` 

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

532 class via the 

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

534 

535 The TABLESAMPLE clause allows selecting a randomly selected approximate 

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

537 most commonly BERNOULLI and SYSTEM. 

538 

539 e.g.:: 

540 

541 from sqlalchemy import func 

542 

543 selectable = people.tablesample( 

544 func.bernoulli(1), 

545 name='alias', 

546 seed=func.random()) 

547 stmt = select(selectable.c.people_id) 

548 

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

550 statement would render as:: 

551 

552 SELECT alias.people_id FROM 

553 people AS alias TABLESAMPLE bernoulli(:bernoulli_1) 

554 REPEATABLE (random()) 

555 

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

557 :class:`_functions.Function`. 

558 

559 :param name: optional alias name 

560 

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

562 REPEATABLE sub-clause is also rendered. 

563 

564 """ 

565 return TableSample._factory(selectable, sampling, name=name, seed=seed) 

566 

567 

568def union( 

569 *selects: _SelectStatementForCompoundArgument, 

570) -> CompoundSelect: 

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

572 

573 The returned object is an instance of 

574 :class:`_expression.CompoundSelect`. 

575 

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

577 :class:`_expression.FromClause` subclasses. 

578 

579 :param \*selects: 

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

581 

582 :param \**kwargs: 

583 available keyword arguments are the same as those of 

584 :func:`select`. 

585 

586 """ 

587 return CompoundSelect._create_union(*selects) 

588 

589 

590def union_all( 

591 *selects: _SelectStatementForCompoundArgument, 

592) -> CompoundSelect: 

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

594 

595 The returned object is an instance of 

596 :class:`_expression.CompoundSelect`. 

597 

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

599 :class:`_expression.FromClause` subclasses. 

600 

601 :param \*selects: 

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

603 

604 """ 

605 return CompoundSelect._create_union_all(*selects) 

606 

607 

608def values( 

609 *columns: ColumnClause[Any], 

610 name: Optional[str] = None, 

611 literal_binds: bool = False, 

612) -> Values: 

613 r"""Construct a :class:`_expression.Values` construct. 

614 

615 The column expressions and the actual data for 

616 :class:`_expression.Values` are given in two separate steps. The 

617 constructor receives the column expressions typically as 

618 :func:`_expression.column` constructs, 

619 and the data is then passed via the 

620 :meth:`_expression.Values.data` method as a list, 

621 which can be called multiple 

622 times to add more data, e.g.:: 

623 

624 from sqlalchemy import column 

625 from sqlalchemy import values 

626 

627 value_expr = values( 

628 column('id', Integer), 

629 column('name', String), 

630 name="my_values" 

631 ).data( 

632 [(1, 'name1'), (2, 'name2'), (3, 'name3')] 

633 ) 

634 

635 :param \*columns: column expressions, typically composed using 

636 :func:`_expression.column` objects. 

637 

638 :param name: the name for this VALUES construct. If omitted, the 

639 VALUES construct will be unnamed in a SQL expression. Different 

640 backends may have different requirements here. 

641 

642 :param literal_binds: Defaults to False. Whether or not to render 

643 the data values inline in the SQL output, rather than using bound 

644 parameters. 

645 

646 """ 

647 return Values(*columns, literal_binds=literal_binds, name=name)