codekingpro/portable-devtools

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