codekingpro/portable-devtools

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 Tuple
14from typing import TYPE_CHECKING
15from typing import TypeVar
16from typing import Union
17
18from . import coercions
19from . import roles
20from ._typing import _ColumnsClauseArgument
21from ._typing import _no_kw
22from .elements import ColumnClause
23from .selectable import Alias
24from .selectable import CompoundSelect
25from .selectable import Exists
26from .selectable import FromClause
27from .selectable import Join
28from .selectable import Lateral
29from .selectable import LateralFromClause
30from .selectable import NamedFromClause
31from .selectable import Select
32from .selectable import TableClause
33from .selectable import TableSample
34from .selectable import Values
35
36if TYPE_CHECKING:
37    from ._typing import _FromClauseArgument
38    from ._typing import _OnClauseArgument
39    from ._typing import _SelectStatementForCompoundArgument
40    from ._typing import _T0
41    from ._typing import _T1
42    from ._typing import _T2
43    from ._typing import _T3
44    from ._typing import _T4
45    from ._typing import _T5
46    from ._typing import _T6
47    from ._typing import _T7
48    from ._typing import _T8
49    from ._typing import _T9
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
56
57
58_T = TypeVar("_T", bound=Any)
59
60
61def alias(
62    selectable: FromClause, name: Optional[str] = None, flat: bool = False
63) -> NamedFromClause:
64    """Return a named alias of the given :class:`.FromClause`.
65
66    For :class:`.Table` and :class:`.Join` objects, the return type is the
67    :class:`_expression.Alias` object. Other kinds of :class:`.NamedFromClause`
68    objects may be returned for other kinds of :class:`.FromClause` objects.
69
70    The named alias represents any :class:`_expression.FromClause` with an
71    alternate name assigned within SQL, typically using the ``AS`` clause when
72    generated, e.g. ``SELECT * FROM table AS aliasname``.
73
74    Equivalent functionality is available via the
75    :meth:`_expression.FromClause.alias`
76    method available on all :class:`_expression.FromClause` objects.
77
78    :param selectable: any :class:`_expression.FromClause` subclass,
79        such as a table, select statement, etc.
80
81    :param name: string name to be assigned as the alias.
82        If ``None``, a name will be deterministically generated at compile
83        time. Deterministic means the name is guaranteed to be unique against
84        other constructs used in the same statement, and will also be the same
85        name for each successive compilation of the same statement object.
86
87    :param flat: Will be passed through to if the given selectable
88     is an instance of :class:`_expression.Join` - see
89     :meth:`_expression.Join.alias` for details.
90
91    """
92    return Alias._factory(selectable, name=name, flat=flat)
93
94
95def cte(
96    selectable: HasCTE, name: Optional[str] = None, recursive: bool = False
97) -> CTE:
98    r"""Return a new :class:`_expression.CTE`,
99    or Common Table Expression instance.
100
101    Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
102
103    """
104    return coercions.expect(roles.HasCTERole, selectable).cte(
105        name=name, recursive=recursive
106    )
107
108
109def except_(
110    *selects: _SelectStatementForCompoundArgument,
111) -> CompoundSelect:
112    r"""Return an ``EXCEPT`` of multiple selectables.
113
114    The returned object is an instance of
115    :class:`_expression.CompoundSelect`.
116
117    :param \*selects:
118      a list of :class:`_expression.Select` instances.
119
120    """
121    return CompoundSelect._create_except(*selects)
122
123
124def except_all(
125    *selects: _SelectStatementForCompoundArgument,
126) -> CompoundSelect:
127    r"""Return an ``EXCEPT ALL`` of multiple selectables.
128
129    The returned object is an instance of
130    :class:`_expression.CompoundSelect`.
131
132    :param \*selects:
133      a list of :class:`_expression.Select` instances.
134
135    """
136    return CompoundSelect._create_except_all(*selects)
137
138
139def exists(
140    __argument: Optional[
141        Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]
142    ] = None,
143) -> Exists:
144    """Construct a new :class:`_expression.Exists` construct.
145
146    The :func:`_sql.exists` can be invoked by itself to produce an
147    :class:`_sql.Exists` construct, which will accept simple WHERE
148    criteria::
149
150        exists_criteria = exists().where(table1.c.col1 == table2.c.col2)
151
152    However, for greater flexibility in constructing the SELECT, an
153    existing :class:`_sql.Select` construct may be converted to an
154    :class:`_sql.Exists`, most conveniently by making use of the
155    :meth:`_sql.SelectBase.exists` method::
156
157        exists_criteria = (
158            select(table2.c.col2).
159            where(table1.c.col1 == table2.c.col2).
160            exists()
161        )
162
163    The EXISTS criteria is then used inside of an enclosing SELECT::
164
165        stmt = select(table1.c.col1).where(exists_criteria)
166
167    The above statement will then be of the form::
168
169        SELECT col1 FROM table1 WHERE EXISTS
170        (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)
171
172    .. seealso::
173
174        :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
175
176        :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
177        ``EXISTS`` clause.
178
179    """  # noqa: E501
180
181    return Exists(__argument)
182
183
184def intersect(
185    *selects: _SelectStatementForCompoundArgument,
186) -> CompoundSelect:
187    r"""Return an ``INTERSECT`` of multiple selectables.
188
189    The returned object is an instance of
190    :class:`_expression.CompoundSelect`.
191
192    :param \*selects:
193      a list of :class:`_expression.Select` instances.
194
195    """
196    return CompoundSelect._create_intersect(*selects)
197
198
199def intersect_all(
200    *selects: _SelectStatementForCompoundArgument,
201) -> CompoundSelect:
202    r"""Return an ``INTERSECT ALL`` of multiple selectables.
203
204    The returned object is an instance of
205    :class:`_expression.CompoundSelect`.
206
207    :param \*selects:
208      a list of :class:`_expression.Select` instances.
209
210
211    """
212    return CompoundSelect._create_intersect_all(*selects)
213
214
215def join(
216    left: _FromClauseArgument,
217    right: _FromClauseArgument,
218    onclause: Optional[_OnClauseArgument] = None,
219    isouter: bool = False,
220    full: bool = False,
221) -> Join:
222    """Produce a :class:`_expression.Join` object, given two
223    :class:`_expression.FromClause`
224    expressions.
225
226    E.g.::
227
228        j = join(user_table, address_table,
229                 user_table.c.id == address_table.c.user_id)
230        stmt = select(user_table).select_from(j)
231
232    would emit SQL along the lines of::
233
234        SELECT user.id, user.name FROM user
235        JOIN address ON user.id = address.user_id
236
237    Similar functionality is available given any
238    :class:`_expression.FromClause` object (e.g. such as a
239    :class:`_schema.Table`) using
240    the :meth:`_expression.FromClause.join` method.
241
242    :param left: The left side of the join.
243
244    :param right: the right side of the join; this is any
245     :class:`_expression.FromClause` object such as a
246     :class:`_schema.Table` object, and
247     may also be a selectable-compatible object such as an ORM-mapped
248     class.
249
250    :param onclause: a SQL expression representing the ON clause of the
251     join.  If left at ``None``, :meth:`_expression.FromClause.join`
252     will attempt to
253     join the two tables based on a foreign key relationship.
254
255    :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
256
257    :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
258
259    .. seealso::
260
261        :meth:`_expression.FromClause.join` - method form,
262        based on a given left side.
263
264        :class:`_expression.Join` - the type of object produced.
265
266    """
267
268    return Join(left, right, onclause, isouter, full)
269
270
271def lateral(
272    selectable: Union[SelectBase, _FromClauseArgument],
273    name: Optional[str] = None,
274) -> LateralFromClause:
275    """Return a :class:`_expression.Lateral` object.
276
277    :class:`_expression.Lateral` is an :class:`_expression.Alias`
278    subclass that represents
279    a subquery with the LATERAL keyword applied to it.
280
281    The special behavior of a LATERAL subquery is that it appears in the
282    FROM clause of an enclosing SELECT, but may correlate to other
283    FROM clauses of that SELECT.   It is a special case of subquery
284    only supported by a small number of backends, currently more recent
285    PostgreSQL versions.
286
287    .. seealso::
288
289        :ref:`tutorial_lateral_correlation` -  overview of usage.
290
291    """
292    return Lateral._factory(selectable, name=name)
293
294
295def outerjoin(
296    left: _FromClauseArgument,
297    right: _FromClauseArgument,
298    onclause: Optional[_OnClauseArgument] = None,
299    full: bool = False,
300) -> Join:
301    """Return an ``OUTER JOIN`` clause element.
302
303    The returned object is an instance of :class:`_expression.Join`.
304
305    Similar functionality is also available via the
306    :meth:`_expression.FromClause.outerjoin` method on any
307    :class:`_expression.FromClause`.
308
309    :param left: The left side of the join.
310
311    :param right: The right side of the join.
312
313    :param onclause:  Optional criterion for the ``ON`` clause, is
314      derived from foreign key relationships established between
315      left and right otherwise.
316
317    To chain joins together, use the :meth:`_expression.FromClause.join`
318    or
319    :meth:`_expression.FromClause.outerjoin` methods on the resulting
320    :class:`_expression.Join` object.
321
322    """
323    return Join(left, right, onclause, isouter=True, full=full)
324
325
326# START OVERLOADED FUNCTIONS select Select 1-10
327
328# code within this block is **programmatically,
329# statically generated** by tools/generate_tuple_map_overloads.py
330
331
332@overload
333def select(__ent0: _TCCA[_T0]) -> Select[Tuple[_T0]]: ...
334
335
336@overload
337def select(
338    __ent0: _TCCA[_T0], __ent1: _TCCA[_T1]
339) -> Select[Tuple[_T0, _T1]]: ...
340
341
342@overload
343def select(
344    __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2]
345) -> Select[Tuple[_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) -> Select[Tuple[_T0, _T1, _T2, _T3]]: ...
355
356
357@overload
358def select(
359    __ent0: _TCCA[_T0],
360    __ent1: _TCCA[_T1],
361    __ent2: _TCCA[_T2],
362    __ent3: _TCCA[_T3],
363    __ent4: _TCCA[_T4],
364) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...
365
366
367@overload
368def select(
369    __ent0: _TCCA[_T0],
370    __ent1: _TCCA[_T1],
371    __ent2: _TCCA[_T2],
372    __ent3: _TCCA[_T3],
373    __ent4: _TCCA[_T4],
374    __ent5: _TCCA[_T5],
375) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...
376
377
378@overload
379def select(
380    __ent0: _TCCA[_T0],
381    __ent1: _TCCA[_T1],
382    __ent2: _TCCA[_T2],
383    __ent3: _TCCA[_T3],
384    __ent4: _TCCA[_T4],
385    __ent5: _TCCA[_T5],
386    __ent6: _TCCA[_T6],
387) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...
388
389
390@overload
391def select(
392    __ent0: _TCCA[_T0],
393    __ent1: _TCCA[_T1],
394    __ent2: _TCCA[_T2],
395    __ent3: _TCCA[_T3],
396    __ent4: _TCCA[_T4],
397    __ent5: _TCCA[_T5],
398    __ent6: _TCCA[_T6],
399    __ent7: _TCCA[_T7],
400) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...
401
402
403@overload
404def select(
405    __ent0: _TCCA[_T0],
406    __ent1: _TCCA[_T1],
407    __ent2: _TCCA[_T2],
408    __ent3: _TCCA[_T3],
409    __ent4: _TCCA[_T4],
410    __ent5: _TCCA[_T5],
411    __ent6: _TCCA[_T6],
412    __ent7: _TCCA[_T7],
413    __ent8: _TCCA[_T8],
414) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8]]: ...
415
416
417@overload
418def select(
419    __ent0: _TCCA[_T0],
420    __ent1: _TCCA[_T1],
421    __ent2: _TCCA[_T2],
422    __ent3: _TCCA[_T3],
423    __ent4: _TCCA[_T4],
424    __ent5: _TCCA[_T5],
425    __ent6: _TCCA[_T6],
426    __ent7: _TCCA[_T7],
427    __ent8: _TCCA[_T8],
428    __ent9: _TCCA[_T9],
429) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8, _T9]]: ...
430
431
432# END OVERLOADED FUNCTIONS select
433
434
435@overload
436def select(
437    *entities: _ColumnsClauseArgument[Any], **__kw: Any
438) -> Select[Any]: ...
439
440
441def select(*entities: _ColumnsClauseArgument[Any], **__kw: Any) -> Select[Any]:
442    r"""Construct a new :class:`_expression.Select`.
443
444
445    .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts
446       column arguments positionally.   The top-level :func:`_sql.select`
447       function will automatically use the 1.x or 2.x style API based on
448       the incoming arguments; using :func:`_sql.select` from the
449       ``sqlalchemy.future`` module will enforce that only the 2.x style
450       constructor is used.
451
452    Similar functionality is also available via the
453    :meth:`_expression.FromClause.select` method on any
454    :class:`_expression.FromClause`.
455
456    .. seealso::
457
458        :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`
459
460    :param \*entities:
461      Entities to SELECT from.  For Core usage, this is typically a series
462      of :class:`_expression.ColumnElement` and / or
463      :class:`_expression.FromClause`
464      objects which will form the columns clause of the resulting
465      statement.   For those objects that are instances of
466      :class:`_expression.FromClause` (typically :class:`_schema.Table`
467      or :class:`_expression.Alias`
468      objects), the :attr:`_expression.FromClause.c`
469      collection is extracted
470      to form a collection of :class:`_expression.ColumnElement` objects.
471
472      This parameter will also accept :class:`_expression.TextClause`
473      constructs as
474      given, as well as ORM-mapped classes.
475
476    """
477    # the keyword args are a necessary element in order for the typing
478    # to work out w/ the varargs vs. having named "keyword" arguments that
479    # aren't always present.
480    if __kw:
481        raise _no_kw()
482    return Select(*entities)
483
484
485def table(name: str, *columns: ColumnClause[Any], **kw: Any) -> TableClause:
486    """Produce a new :class:`_expression.TableClause`.
487
488    The object returned is an instance of
489    :class:`_expression.TableClause`, which
490    represents the "syntactical" portion of the schema-level
491    :class:`_schema.Table` object.
492    It may be used to construct lightweight table constructs.
493
494    :param name: Name of the table.
495
496    :param columns: A collection of :func:`_expression.column` constructs.
497
498    :param schema: The schema name for this table.
499
500        .. versionadded:: 1.3.18 :func:`_expression.table` can now
501           accept a ``schema`` argument.
502    """
503
504    return TableClause(name, *columns, **kw)
505
506
507def tablesample(
508    selectable: _FromClauseArgument,
509    sampling: Union[float, Function[Any]],
510    name: Optional[str] = None,
511    seed: Optional[roles.ExpressionElementRole[Any]] = None,
512) -> TableSample:
513    """Return a :class:`_expression.TableSample` object.
514
515    :class:`_expression.TableSample` is an :class:`_expression.Alias`
516    subclass that represents
517    a table with the TABLESAMPLE clause applied to it.
518    :func:`_expression.tablesample`
519    is also available from the :class:`_expression.FromClause`
520    class via the
521    :meth:`_expression.FromClause.tablesample` method.
522
523    The TABLESAMPLE clause allows selecting a randomly selected approximate
524    percentage of rows from a table. It supports multiple sampling methods,
525    most commonly BERNOULLI and SYSTEM.
526
527    e.g.::
528
529        from sqlalchemy import func
530
531        selectable = people.tablesample(
532                    func.bernoulli(1),
533                    name='alias',
534                    seed=func.random())
535        stmt = select(selectable.c.people_id)
536
537    Assuming ``people`` with a column ``people_id``, the above
538    statement would render as::
539
540        SELECT alias.people_id FROM
541        people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
542        REPEATABLE (random())
543
544    :param sampling: a ``float`` percentage between 0 and 100 or
545        :class:`_functions.Function`.
546
547    :param name: optional alias name
548
549    :param seed: any real-valued SQL expression.  When specified, the
550     REPEATABLE sub-clause is also rendered.
551
552    """
553    return TableSample._factory(selectable, sampling, name=name, seed=seed)
554
555
556def union(
557    *selects: _SelectStatementForCompoundArgument,
558) -> CompoundSelect:
559    r"""Return a ``UNION`` of multiple selectables.
560
561    The returned object is an instance of
562    :class:`_expression.CompoundSelect`.
563
564    A similar :func:`union()` method is available on all
565    :class:`_expression.FromClause` subclasses.
566
567    :param \*selects:
568      a list of :class:`_expression.Select` instances.
569
570    :param \**kwargs:
571      available keyword arguments are the same as those of
572      :func:`select`.
573
574    """
575    return CompoundSelect._create_union(*selects)
576
577
578def union_all(
579    *selects: _SelectStatementForCompoundArgument,
580) -> CompoundSelect:
581    r"""Return a ``UNION ALL`` of multiple selectables.
582
583    The returned object is an instance of
584    :class:`_expression.CompoundSelect`.
585
586    A similar :func:`union_all()` method is available on all
587    :class:`_expression.FromClause` subclasses.
588
589    :param \*selects:
590      a list of :class:`_expression.Select` instances.
591
592    """
593    return CompoundSelect._create_union_all(*selects)
594
595
596def values(
597    *columns: ColumnClause[Any],
598    name: Optional[str] = None,
599    literal_binds: bool = False,
600) -> Values:
601    r"""Construct a :class:`_expression.Values` construct.
602
603    The column expressions and the actual data for
604    :class:`_expression.Values` are given in two separate steps.  The
605    constructor receives the column expressions typically as
606    :func:`_expression.column` constructs,
607    and the data is then passed via the
608    :meth:`_expression.Values.data` method as a list,
609    which can be called multiple
610    times to add more data, e.g.::
611
612        from sqlalchemy import column
613        from sqlalchemy import values
614
615        value_expr = values(
616            column('id', Integer),
617            column('name', String),
618            name="my_values"
619        ).data(
620            [(1, 'name1'), (2, 'name2'), (3, 'name3')]
621        )
622
623    :param \*columns: column expressions, typically composed using
624     :func:`_expression.column` objects.
625
626    :param name: the name for this VALUES construct.  If omitted, the
627     VALUES construct will be unnamed in a SQL expression.   Different
628     backends may have different requirements here.
629
630    :param literal_binds: Defaults to False.  Whether or not to render
631     the data values inline in the SQL output, rather than using bound
632     parameters.
633
634    """
635    return Values(*columns, literal_binds=literal_binds, name=name)
636