Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.11/site-packages/sqlalchemy/sql/_selectable

1# sql/_selectable_constructors.py

3# <see AUTHORS file>

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

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

8from __future__ import annotations

10from typing import Any

11from typing import Optional

12from typing import overload

13from typing import Tuple

14from typing import TYPE_CHECKING

15from typing import Union

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

35if TYPE_CHECKING:

36 from ._typing import _FromClauseArgument

37 from ._typing import _OnClauseArgument

38 from ._typing import _SelectStatementForCompoundArgument

39 from ._typing import _T0

40 from ._typing import _T1

41 from ._typing import _T2

42 from ._typing import _T3

43 from ._typing import _T4

44 from ._typing import _T5

45 from ._typing import _T6

46 from ._typing import _T7

47 from ._typing import _T8

48 from ._typing import _T9

49 from ._typing import _TP

50 from ._typing import _TypedColumnClauseArgument as _TCCA

51 from .functions import Function

52 from .selectable import CTE

53 from .selectable import HasCTE

54 from .selectable import ScalarSelect

55 from .selectable import SelectBase

58def alias(

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

60) -> NamedFromClause:

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

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

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

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

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

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

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

71 Equivalent functionality is available via the

72 :meth:`_expression.FromClause.alias`

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

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

76 such as a table, select statement, etc.

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

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

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

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

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

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

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

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

88 """

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

92def cte(

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

94) -> CTE:

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

96 or Common Table Expression instance.

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

100 """

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

102 name=name, recursive=recursive

103 )

104

105

106# TODO: mypy requires the _TypedSelectable overloads in all compound select

107# constructors since _SelectStatementForCompoundArgument includes

108# untyped args that make it return CompoundSelect[Unpack[tuple[Never, ...]]]

109# pyright does not have this issue

110_TypedSelectable = Union["Select[_TP]", "CompoundSelect[_TP]"]

111

112

113@overload

114def except_(

115 *selects: _TypedSelectable[_TP],

116) -> CompoundSelect[_TP]: ...

117

118

119@overload

120def except_(

121 *selects: _SelectStatementForCompoundArgument[_TP],

122) -> CompoundSelect[_TP]: ...

123

124

125def except_(

126 *selects: _SelectStatementForCompoundArgument[_TP],

127) -> CompoundSelect[_TP]:

128 r"""Return an ``EXCEPT`` 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(*selects)

138

139

140@overload

141def except_all(

142 *selects: _TypedSelectable[_TP],

143) -> CompoundSelect[_TP]: ...

144

145

146@overload

147def except_all(

148 *selects: _SelectStatementForCompoundArgument[_TP],

149) -> CompoundSelect[_TP]: ...

150

151

152def except_all(

153 *selects: _SelectStatementForCompoundArgument[_TP],

154) -> CompoundSelect[_TP]:

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

156

157 The returned object is an instance of

158 :class:`_expression.CompoundSelect`.

159

160 :param \*selects:

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

162

163 """

164 return CompoundSelect._create_except_all(*selects)

165

166

167def exists(

168 __argument: Optional[

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

170 ] = None,

171) -> Exists:

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

173

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

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

176 criteria::

177

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

179

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

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

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

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

184

185 exists_criteria = (

186 select(table2.c.col2).where(table1.c.col1 == table2.c.col2).exists()

187 )

188

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

190

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

192

193 The above statement will then be of the form:

194

195 .. sourcecode:: sql

196

197 SELECT col1 FROM table1 WHERE EXISTS

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

199

200 .. seealso::

201

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

203

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

205 ``EXISTS`` clause.

206

207 """ # noqa: E501

208

209 return Exists(__argument)

210

211

212@overload

213def intersect(

214 *selects: _TypedSelectable[_TP],

215) -> CompoundSelect[_TP]: ...

216

217

218@overload

219def intersect(

220 *selects: _SelectStatementForCompoundArgument[_TP],

221) -> CompoundSelect[_TP]: ...

222

223

224def intersect(

225 *selects: _SelectStatementForCompoundArgument[_TP],

226) -> CompoundSelect[_TP]:

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

228

229 The returned object is an instance of

230 :class:`_expression.CompoundSelect`.

231

232 :param \*selects:

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

234

235 """

236 return CompoundSelect._create_intersect(*selects)

237

238

239@overload

240def intersect_all(

241 *selects: _TypedSelectable[_TP],

242) -> CompoundSelect[_TP]: ...

243

244

245@overload

246def intersect_all(

247 *selects: _SelectStatementForCompoundArgument[_TP],

248) -> CompoundSelect[_TP]: ...

249

250

251def intersect_all(

252 *selects: _SelectStatementForCompoundArgument[_TP],

253) -> CompoundSelect[_TP]:

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

255

256 The returned object is an instance of

257 :class:`_expression.CompoundSelect`.

258

259 :param \*selects:

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

261

262

263 """

264 return CompoundSelect._create_intersect_all(*selects)

265

266

267def join(

268 left: _FromClauseArgument,

269 right: _FromClauseArgument,

270 onclause: Optional[_OnClauseArgument] = None,

271 isouter: bool = False,

272 full: bool = False,

273) -> Join:

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

275 :class:`_expression.FromClause`

276 expressions.

277

278 E.g.::

279

280 j = join(

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

282 )

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

284

285 would emit SQL along the lines of:

286

287 .. sourcecode:: sql

288

289 SELECT user.id, user.name FROM user

290 JOIN address ON user.id = address.user_id

291

292 Similar functionality is available given any

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

294 :class:`_schema.Table`) using

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

296

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

298

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

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

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

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

303 class.

304

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

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

307 will attempt to

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

309

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

311

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

313

314 .. seealso::

315

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

317 based on a given left side.

318

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

320

321 """ # noqa: E501

322

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

324

325

326def lateral(

327 selectable: Union[SelectBase, _FromClauseArgument],

328 name: Optional[str] = None,

329) -> LateralFromClause:

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

331

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

333 subclass that represents

334 a subquery with the LATERAL keyword applied to it.

335

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

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

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

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

340 PostgreSQL versions.

341

342 .. seealso::

343

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

345

346 """

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

348

349

350def outerjoin(

351 left: _FromClauseArgument,

352 right: _FromClauseArgument,

353 onclause: Optional[_OnClauseArgument] = None,

354 full: bool = False,

355) -> Join:

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

357

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

359

360 Similar functionality is also available via the

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

362 :class:`_expression.FromClause`.

363

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

365

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

367

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

369 derived from foreign key relationships established between

370 left and right otherwise.

371

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

373 or

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

375 :class:`_expression.Join` object.

376

377 """

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

379

380

381# START OVERLOADED FUNCTIONS select Select 1-10

382

383# code within this block is **programmatically,

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

385

386

387@overload

388def select(__ent0: _TCCA[_T0]) -> Select[Tuple[_T0]]: ...

389

390

391@overload

392def select(

393 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]

394) -> Select[Tuple[_T0, _T1]]: ...

395

396

397@overload

398def select(

399 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]

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

401

402

403@overload

404def select(

405 __ent0: _TCCA[_T0],

406 __ent1: _TCCA[_T1],

407 __ent2: _TCCA[_T2],

408 __ent3: _TCCA[_T3],

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

410

411

412@overload

413def select(

414 __ent0: _TCCA[_T0],

415 __ent1: _TCCA[_T1],

416 __ent2: _TCCA[_T2],

417 __ent3: _TCCA[_T3],

418 __ent4: _TCCA[_T4],

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

420

421

422@overload

423def select(

424 __ent0: _TCCA[_T0],

425 __ent1: _TCCA[_T1],

426 __ent2: _TCCA[_T2],

427 __ent3: _TCCA[_T3],

428 __ent4: _TCCA[_T4],

429 __ent5: _TCCA[_T5],

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

431

432

433@overload

434def select(

435 __ent0: _TCCA[_T0],

436 __ent1: _TCCA[_T1],

437 __ent2: _TCCA[_T2],

438 __ent3: _TCCA[_T3],

439 __ent4: _TCCA[_T4],

440 __ent5: _TCCA[_T5],

441 __ent6: _TCCA[_T6],

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

443

444

445@overload

446def select(

447 __ent0: _TCCA[_T0],

448 __ent1: _TCCA[_T1],

449 __ent2: _TCCA[_T2],

450 __ent3: _TCCA[_T3],

451 __ent4: _TCCA[_T4],

452 __ent5: _TCCA[_T5],

453 __ent6: _TCCA[_T6],

454 __ent7: _TCCA[_T7],

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

456

457

458@overload

459def select(

460 __ent0: _TCCA[_T0],

461 __ent1: _TCCA[_T1],

462 __ent2: _TCCA[_T2],

463 __ent3: _TCCA[_T3],

464 __ent4: _TCCA[_T4],

465 __ent5: _TCCA[_T5],

466 __ent6: _TCCA[_T6],

467 __ent7: _TCCA[_T7],

468 __ent8: _TCCA[_T8],

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

470

471

472@overload

473def select(

474 __ent0: _TCCA[_T0],

475 __ent1: _TCCA[_T1],

476 __ent2: _TCCA[_T2],

477 __ent3: _TCCA[_T3],

478 __ent4: _TCCA[_T4],

479 __ent5: _TCCA[_T5],

480 __ent6: _TCCA[_T6],

481 __ent7: _TCCA[_T7],

482 __ent8: _TCCA[_T8],

483 __ent9: _TCCA[_T9],

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

485

486

487# END OVERLOADED FUNCTIONS select

488

489

490@overload

491def select(

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

493) -> Select[Any]: ...

494

495

496def select(*entities: _ColumnsClauseArgument[Any], **__kw: Any) -> Select[Any]:

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

498

499

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

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

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

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

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

505 constructor is used.

506

507 Similar functionality is also available via the

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

509 :class:`_expression.FromClause`.

510

511 .. seealso::

512

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

514

515 :param \*entities:

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

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

518 :class:`_expression.FromClause`

519 objects which will form the columns clause of the resulting

520 statement. For those objects that are instances of

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

522 or :class:`_expression.Alias`

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

524 collection is extracted

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

526

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

528 constructs as

529 given, as well as ORM-mapped classes.

530

531 """

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

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

534 # aren't always present.

535 if __kw:

536 raise _no_kw()

537 return Select(*entities)

538

539

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

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

542

543 The object returned is an instance of

544 :class:`_expression.TableClause`, which

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

546 :class:`_schema.Table` object.

547 It may be used to construct lightweight table constructs.

548

549 :param name: Name of the table.

550

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

552

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

554

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

556 accept a ``schema`` argument.

557 """

558

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

560

561

562def tablesample(

563 selectable: _FromClauseArgument,

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

565 name: Optional[str] = None,

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

567) -> TableSample:

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

569

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

571 subclass that represents

572 a table with the TABLESAMPLE clause applied to it.

573 :func:`_expression.tablesample`

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

575 class via the

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

577

578 The TABLESAMPLE clause allows selecting a randomly selected approximate

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

580 most commonly BERNOULLI and SYSTEM.

581

582 e.g.::

583

584 from sqlalchemy import func

585

586 selectable = people.tablesample(

587 func.bernoulli(1), name="alias", seed=func.random()

588 )

589 stmt = select(selectable.c.people_id)

590

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

592 statement would render as:

593

594 .. sourcecode:: sql

595

596 SELECT alias.people_id FROM

597 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)

598 REPEATABLE (random())

599

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

601 :class:`_functions.Function`.

602

603 :param name: optional alias name

604

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

606 REPEATABLE sub-clause is also rendered.

607

608 """

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

610

611

612@overload

613def union(

614 *selects: _TypedSelectable[_TP],

615) -> CompoundSelect[_TP]: ...

616

617

618@overload

619def union(

620 *selects: _SelectStatementForCompoundArgument[_TP],

621) -> CompoundSelect[_TP]: ...

622

623

624def union(

625 *selects: _SelectStatementForCompoundArgument[_TP],

626) -> CompoundSelect[_TP]:

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

628

629 The returned object is an instance of

630 :class:`_expression.CompoundSelect`.

631

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

633 :class:`_expression.FromClause` subclasses.

634

635 :param \*selects:

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

637

638 :param \**kwargs:

639 available keyword arguments are the same as those of

640 :func:`select`.

641

642 """

643 return CompoundSelect._create_union(*selects)

644

645

646@overload

647def union_all(

648 *selects: _TypedSelectable[_TP],

649) -> CompoundSelect[_TP]: ...

650

651

652@overload

653def union_all(

654 *selects: _SelectStatementForCompoundArgument[_TP],

655) -> CompoundSelect[_TP]: ...

656

657

658def union_all(

659 *selects: _SelectStatementForCompoundArgument[_TP],

660) -> CompoundSelect[_TP]:

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

662

663 The returned object is an instance of

664 :class:`_expression.CompoundSelect`.

665

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

667 :class:`_expression.FromClause` subclasses.

668

669 :param \*selects:

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

671

672 """

673 return CompoundSelect._create_union_all(*selects)

674

675

676def values(

677 *columns: ColumnClause[Any],

678 name: Optional[str] = None,

679 literal_binds: bool = False,

680) -> Values:

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

682

683 The column expressions and the actual data for

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

685 constructor receives the column expressions typically as

686 :func:`_expression.column` constructs,

687 and the data is then passed via the

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

689 which can be called multiple

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

691

692 from sqlalchemy import column

693 from sqlalchemy import values

694 from sqlalchemy import Integer

695 from sqlalchemy import String

696

697 value_expr = values(

698 column("id", Integer),

699 column("name", String),

700 name="my_values",

701 ).data([(1, "name1"), (2, "name2"), (3, "name3")])

702

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

704 :func:`_expression.column` objects.

705

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

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

708 backends may have different requirements here.

709

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

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

712 parameters.

713

714 """

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

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

127 statements