Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
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
8from __future__ import annotations
10from typing import Any
11from typing import Optional
12from typing import overload
13from typing import TYPE_CHECKING
14from typing import Union
16from . import coercions
17from . import roles
18from ._annotated_cols import _KeyColCC_co
19from ._annotated_cols import HasRowPos
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
35from ..util.typing import TupleAny
36from ..util.typing import Unpack
38if TYPE_CHECKING:
39 from ._typing import _FromClauseArgument
40 from ._typing import _OnClauseArgument
41 from ._typing import _OnlyColumnArgument
42 from ._typing import _SelectStatementForCompoundArgument
43 from ._typing import _T0
44 from ._typing import _T1
45 from ._typing import _T2
46 from ._typing import _T3
47 from ._typing import _T4
48 from ._typing import _T5
49 from ._typing import _T6
50 from ._typing import _T7
51 from ._typing import _T8
52 from ._typing import _T9
53 from ._typing import _Ts
54 from ._typing import _Ts2
55 from ._typing import _TypedColumnClauseArgument as _TCCA
56 from .functions import Function
57 from .selectable import CTE
58 from .selectable import HasCTE
59 from .selectable import ScalarSelect
60 from .selectable import SelectBase
63def alias(
64 selectable: FromClause[_KeyColCC_co],
65 name: Optional[str] = None,
66 flat: bool = False,
67) -> NamedFromClause[_KeyColCC_co]:
68 """Return a named alias of the given :class:`.FromClause`.
70 For :class:`.Table` and :class:`.Join` objects, the return type is the
71 :class:`_expression.Alias` object. Other kinds of :class:`.NamedFromClause`
72 objects may be returned for other kinds of :class:`.FromClause` objects.
74 The named alias represents any :class:`_expression.FromClause` with an
75 alternate name assigned within SQL, typically using the ``AS`` clause when
76 generated, e.g. ``SELECT * FROM table AS aliasname``.
78 Equivalent functionality is available via the
79 :meth:`_expression.FromClause.alias`
80 method available on all :class:`_expression.FromClause` objects.
82 :param selectable: any :class:`_expression.FromClause` subclass,
83 such as a table, select statement, etc.
85 :param name: string name to be assigned as the alias.
86 If ``None``, a name will be deterministically generated at compile
87 time. Deterministic means the name is guaranteed to be unique against
88 other constructs used in the same statement, and will also be the same
89 name for each successive compilation of the same statement object.
91 :param flat: Will be passed through to if the given selectable
92 is an instance of :class:`_expression.Join` - see
93 :meth:`_expression.Join.alias` for details.
95 """
96 return Alias._factory(selectable, name=name, flat=flat)
99def cte(
100 selectable: HasCTE, name: Optional[str] = None, recursive: bool = False
101) -> CTE:
102 r"""Return a new :class:`_expression.CTE`,
103 or Common Table Expression instance.
105 Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage.
107 """
108 return coercions.expect(roles.HasCTERole, selectable).cte(
109 name=name, recursive=recursive
110 )
113# TODO: mypy requires the _TypedSelectable overloads in all compound select
114# constructors since _SelectStatementForCompoundArgument includes
115# untyped args that make it return CompoundSelect[Unpack[tuple[Never, ...]]]
116# pyright does not have this issue
117_TypedSelectable = Union["Select[Unpack[_Ts]]", "CompoundSelect[Unpack[_Ts]]"]
120@overload
121def except_(
122 *selects: _TypedSelectable[Unpack[_Ts]],
123) -> CompoundSelect[Unpack[_Ts]]: ...
126@overload
127def except_(
128 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
129) -> CompoundSelect[Unpack[_Ts]]: ...
132def except_(
133 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
134) -> CompoundSelect[Unpack[_Ts]]:
135 r"""Return an ``EXCEPT`` of multiple selectables.
137 The returned object is an instance of
138 :class:`_expression.CompoundSelect`.
140 :param \*selects:
141 a list of :class:`_expression.Select` instances.
143 """
144 return CompoundSelect._create_except(*selects)
147@overload
148def except_all(
149 *selects: _TypedSelectable[Unpack[_Ts]],
150) -> CompoundSelect[Unpack[_Ts]]: ...
153@overload
154def except_all(
155 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
156) -> CompoundSelect[Unpack[_Ts]]: ...
159def except_all(
160 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
161) -> CompoundSelect[Unpack[_Ts]]:
162 r"""Return an ``EXCEPT ALL`` of multiple selectables.
164 The returned object is an instance of
165 :class:`_expression.CompoundSelect`.
167 :param \*selects:
168 a list of :class:`_expression.Select` instances.
170 """
171 return CompoundSelect._create_except_all(*selects)
174def exists(
175 __argument: Optional[
176 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]
177 ] = None,
178 /,
179) -> Exists:
180 """Construct a new :class:`_expression.Exists` construct.
182 The :func:`_sql.exists` can be invoked by itself to produce an
183 :class:`_sql.Exists` construct, which will accept simple WHERE
184 criteria::
186 exists_criteria = exists().where(table1.c.col1 == table2.c.col2)
188 However, for greater flexibility in constructing the SELECT, an
189 existing :class:`_sql.Select` construct may be converted to an
190 :class:`_sql.Exists`, most conveniently by making use of the
191 :meth:`_sql.SelectBase.exists` method::
193 exists_criteria = (
194 select(table2.c.col2).where(table1.c.col1 == table2.c.col2).exists()
195 )
197 The EXISTS criteria is then used inside of an enclosing SELECT::
199 stmt = select(table1.c.col1).where(exists_criteria)
201 The above statement will then be of the form:
203 .. sourcecode:: sql
205 SELECT col1 FROM table1 WHERE EXISTS
206 (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1)
208 .. seealso::
210 :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial.
212 :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an
213 ``EXISTS`` clause.
215 """ # noqa: E501
217 return Exists(__argument)
220@overload
221def intersect(
222 *selects: _TypedSelectable[Unpack[_Ts]],
223) -> CompoundSelect[Unpack[_Ts]]: ...
226@overload
227def intersect(
228 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
229) -> CompoundSelect[Unpack[_Ts]]: ...
232def intersect(
233 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
234) -> CompoundSelect[Unpack[_Ts]]:
235 r"""Return an ``INTERSECT`` of multiple selectables.
237 The returned object is an instance of
238 :class:`_expression.CompoundSelect`.
240 :param \*selects:
241 a list of :class:`_expression.Select` instances.
243 """
244 return CompoundSelect._create_intersect(*selects)
247@overload
248def intersect_all(
249 *selects: _TypedSelectable[Unpack[_Ts]],
250) -> CompoundSelect[Unpack[_Ts]]: ...
253@overload
254def intersect_all(
255 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
256) -> CompoundSelect[Unpack[_Ts]]: ...
259def intersect_all(
260 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
261) -> CompoundSelect[Unpack[_Ts]]:
262 r"""Return an ``INTERSECT ALL`` of multiple selectables.
264 The returned object is an instance of
265 :class:`_expression.CompoundSelect`.
267 :param \*selects:
268 a list of :class:`_expression.Select` instances.
271 """
272 return CompoundSelect._create_intersect_all(*selects)
275def join(
276 left: _FromClauseArgument,
277 right: _FromClauseArgument,
278 onclause: Optional[_OnClauseArgument] = None,
279 isouter: bool = False,
280 full: bool = False,
281) -> Join:
282 """Produce a :class:`_expression.Join` object, given two
283 :class:`_expression.FromClause`
284 expressions.
286 E.g.::
288 j = join(
289 user_table, address_table, user_table.c.id == address_table.c.user_id
290 )
291 stmt = select(user_table).select_from(j)
293 would emit SQL along the lines of:
295 .. sourcecode:: sql
297 SELECT user.id, user.name FROM user
298 JOIN address ON user.id = address.user_id
300 Similar functionality is available given any
301 :class:`_expression.FromClause` object (e.g. such as a
302 :class:`_schema.Table`) using
303 the :meth:`_expression.FromClause.join` method.
305 :param left: The left side of the join.
307 :param right: the right side of the join; this is any
308 :class:`_expression.FromClause` object such as a
309 :class:`_schema.Table` object, and
310 may also be a selectable-compatible object such as an ORM-mapped
311 class.
313 :param onclause: a SQL expression representing the ON clause of the
314 join. If left at ``None``, :meth:`_expression.FromClause.join`
315 will attempt to
316 join the two tables based on a foreign key relationship.
318 :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN.
320 :param full: if True, render a FULL OUTER JOIN, instead of JOIN.
322 .. seealso::
324 :meth:`_expression.FromClause.join` - method form,
325 based on a given left side.
327 :class:`_expression.Join` - the type of object produced.
329 """ # noqa: E501
331 return Join(left, right, onclause, isouter, full)
334def lateral(
335 selectable: Union[SelectBase, _FromClauseArgument],
336 name: Optional[str] = None,
337) -> LateralFromClause:
338 """Return a :class:`_expression.Lateral` object.
340 :class:`_expression.Lateral` is an :class:`_expression.Alias`
341 subclass that represents
342 a subquery with the LATERAL keyword applied to it.
344 The special behavior of a LATERAL subquery is that it appears in the
345 FROM clause of an enclosing SELECT, but may correlate to other
346 FROM clauses of that SELECT. It is a special case of subquery
347 only supported by a small number of backends, currently more recent
348 PostgreSQL versions.
350 .. seealso::
352 :ref:`tutorial_lateral_correlation` - overview of usage.
354 """
355 return Lateral._factory(selectable, name=name)
358def outerjoin(
359 left: _FromClauseArgument,
360 right: _FromClauseArgument,
361 onclause: Optional[_OnClauseArgument] = None,
362 full: bool = False,
363) -> Join:
364 """Return an ``OUTER JOIN`` clause element.
366 The returned object is an instance of :class:`_expression.Join`.
368 Similar functionality is also available via the
369 :meth:`_expression.FromClause.outerjoin` method on any
370 :class:`_expression.FromClause`.
372 :param left: The left side of the join.
374 :param right: The right side of the join.
376 :param onclause: Optional criterion for the ``ON`` clause, is
377 derived from foreign key relationships established between
378 left and right otherwise.
380 To chain joins together, use the :meth:`_expression.FromClause.join`
381 or
382 :meth:`_expression.FromClause.outerjoin` methods on the resulting
383 :class:`_expression.Join` object.
385 """
386 return Join(left, right, onclause, isouter=True, full=full)
389# START OVERLOADED FUNCTIONS select Select 1-10
391# code within this block is **programmatically,
392# statically generated** by tools/generate_tuple_map_overloads.py
395@overload
396def select(__ent0: _TCCA[_T0], /) -> Select[_T0]: ...
399@overload
400def select(__ent0: _TCCA[_T0], __ent1: _TCCA[_T1], /) -> Select[_T0, _T1]: ...
403@overload
404def select(
405 __ent0: _TCCA[_T0], __ent1: _TCCA[_T1], __ent2: _TCCA[_T2], /
406) -> Select[_T0, _T1, _T2]: ...
409@overload
410def select(
411 __ent0: _TCCA[_T0],
412 __ent1: _TCCA[_T1],
413 __ent2: _TCCA[_T2],
414 __ent3: _TCCA[_T3],
415 /,
416) -> Select[_T0, _T1, _T2, _T3]: ...
419@overload
420def select(
421 __ent0: _TCCA[_T0],
422 __ent1: _TCCA[_T1],
423 __ent2: _TCCA[_T2],
424 __ent3: _TCCA[_T3],
425 __ent4: _TCCA[_T4],
426 /,
427) -> Select[_T0, _T1, _T2, _T3, _T4]: ...
430@overload
431def select(
432 __ent0: _TCCA[_T0],
433 __ent1: _TCCA[_T1],
434 __ent2: _TCCA[_T2],
435 __ent3: _TCCA[_T3],
436 __ent4: _TCCA[_T4],
437 __ent5: _TCCA[_T5],
438 /,
439) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...
442@overload
443def select(
444 __ent0: _TCCA[_T0],
445 __ent1: _TCCA[_T1],
446 __ent2: _TCCA[_T2],
447 __ent3: _TCCA[_T3],
448 __ent4: _TCCA[_T4],
449 __ent5: _TCCA[_T5],
450 __ent6: _TCCA[_T6],
451 /,
452) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...
455@overload
456def select(
457 __ent0: _TCCA[_T0],
458 __ent1: _TCCA[_T1],
459 __ent2: _TCCA[_T2],
460 __ent3: _TCCA[_T3],
461 __ent4: _TCCA[_T4],
462 __ent5: _TCCA[_T5],
463 __ent6: _TCCA[_T6],
464 __ent7: _TCCA[_T7],
465 /,
466) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]: ...
469@overload
470def select(
471 __ent0: _TCCA[_T0],
472 __ent1: _TCCA[_T1],
473 __ent2: _TCCA[_T2],
474 __ent3: _TCCA[_T3],
475 __ent4: _TCCA[_T4],
476 __ent5: _TCCA[_T5],
477 __ent6: _TCCA[_T6],
478 __ent7: _TCCA[_T7],
479 __ent8: _TCCA[_T8],
480 /,
481) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8]: ...
484@overload
485def select(
486 __ent0: _TCCA[_T0],
487 __ent1: _TCCA[_T1],
488 __ent2: _TCCA[_T2],
489 __ent3: _TCCA[_T3],
490 __ent4: _TCCA[_T4],
491 __ent5: _TCCA[_T5],
492 __ent6: _TCCA[_T6],
493 __ent7: _TCCA[_T7],
494 __ent8: _TCCA[_T8],
495 __ent9: _TCCA[_T9],
496 /,
497 *entities: _ColumnsClauseArgument[Any],
498) -> Select[
499 _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, _T8, _T9, Unpack[TupleAny]
500]: ...
503# END OVERLOADED FUNCTIONS select
504@overload
505def select(
506 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
507) -> Select[Unpack[_Ts]]: ...
510# NOTE: this seems to currently be interpreted by mypy as not allowed.
511# https://peps.python.org/pep-0646/#multiple-type-variable-tuples-not-allowed
512# https://github.com/python/mypy/issues/20188
513@overload
514def select(
515 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
516 __table2: FromClause[HasRowPos[Unpack[_Ts2]]], # type: ignore[type-var]
517) -> Select[Unpack[_Ts], Unpack[_Ts2]]: ... # type: ignore[misc]
520@overload
521def select(
522 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
523 __ent0: _TCCA[_T0],
524) -> Select[Unpack[_Ts], _T0]: ...
527@overload
528def select(
529 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
530 __ent0: _TCCA[_T0],
531 __ent1: _TCCA[_T1],
532) -> Select[Unpack[_Ts], _T0, _T1]: ...
535@overload
536def select(
537 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
538 __ent0: _TCCA[_T0],
539 __ent1: _TCCA[_T1],
540 __ent2: _TCCA[_T2],
541) -> Select[Unpack[_Ts], _T0, _T1, _T2]: ...
544@overload
545def select(
546 __ent0: _TCCA[_T0],
547 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
548) -> Select[_T0, Unpack[_Ts]]: ...
551@overload
552def select(
553 __ent0: _TCCA[_T0],
554 __ent1: _TCCA[_T1],
555 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
556) -> Select[_T0, _T1, Unpack[_Ts]]: ...
559@overload
560def select(
561 __ent0: _TCCA[_T0],
562 __ent1: _TCCA[_T1],
563 __ent2: _TCCA[_T2],
564 __table: FromClause[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]
565) -> Select[_T0, _T1, _T2, Unpack[_Ts]]: ...
568@overload
569def select(
570 *entities: _ColumnsClauseArgument[Any], **__kw: Any
571) -> Select[Unpack[TupleAny]]: ...
574def select(
575 *entities: _ColumnsClauseArgument[Any], **__kw: Any
576) -> Select[Unpack[TupleAny]]:
577 r"""Construct a new :class:`_expression.Select`.
580 .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts
581 column arguments positionally. The top-level :func:`_sql.select`
582 function will automatically use the 1.x or 2.x style API based on
583 the incoming arguments; using :func:`_sql.select` from the
584 ``sqlalchemy.future`` module will enforce that only the 2.x style
585 constructor is used.
587 Similar functionality is also available via the
588 :meth:`_expression.FromClause.select` method on any
589 :class:`_expression.FromClause`.
591 .. seealso::
593 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`
595 :param \*entities:
596 Entities to SELECT from. For Core usage, this is typically a series
597 of :class:`_expression.ColumnElement` and / or
598 :class:`_expression.FromClause`
599 objects which will form the columns clause of the resulting
600 statement. For those objects that are instances of
601 :class:`_expression.FromClause` (typically :class:`_schema.Table`
602 or :class:`_expression.Alias`
603 objects), the :attr:`_expression.FromClause.c`
604 collection is extracted
605 to form a collection of :class:`_expression.ColumnElement` objects.
607 This parameter will also accept :class:`_expression.TextClause`
608 constructs as
609 given, as well as ORM-mapped classes.
611 """
612 # the keyword args are a necessary element in order for the typing
613 # to work out w/ the varargs vs. having named "keyword" arguments that
614 # aren't always present.
615 if __kw:
616 raise _no_kw()
617 return Select(*entities)
620def table(name: str, *columns: ColumnClause[Any], **kw: Any) -> TableClause:
621 """Produce a new :class:`_expression.TableClause`.
623 The object returned is an instance of
624 :class:`_expression.TableClause`, which
625 represents the "syntactical" portion of the schema-level
626 :class:`_schema.Table` object.
627 It may be used to construct lightweight table constructs.
629 :param name: Name of the table.
631 :param columns: A collection of :func:`_expression.column` constructs.
633 :param schema: The schema name for this table.
635 """
637 return TableClause(name, *columns, **kw)
640def tablesample(
641 selectable: _FromClauseArgument,
642 sampling: Union[float, Function[Any]],
643 name: Optional[str] = None,
644 seed: Optional[roles.ExpressionElementRole[Any]] = None,
645) -> TableSample:
646 """Return a :class:`_expression.TableSample` object.
648 :class:`_expression.TableSample` is an :class:`_expression.Alias`
649 subclass that represents
650 a table with the TABLESAMPLE clause applied to it.
651 :func:`_expression.tablesample`
652 is also available from the :class:`_expression.FromClause`
653 class via the
654 :meth:`_expression.FromClause.tablesample` method.
656 The TABLESAMPLE clause allows selecting a randomly selected approximate
657 percentage of rows from a table. It supports multiple sampling methods,
658 most commonly BERNOULLI and SYSTEM.
660 e.g.::
662 from sqlalchemy import func
664 selectable = people.tablesample(
665 func.bernoulli(1), name="alias", seed=func.random()
666 )
667 stmt = select(selectable.c.people_id)
669 Assuming ``people`` with a column ``people_id``, the above
670 statement would render as:
672 .. sourcecode:: sql
674 SELECT alias.people_id FROM
675 people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
676 REPEATABLE (random())
678 :param sampling: a ``float`` percentage between 0 and 100 or
679 :class:`_functions.Function`.
681 :param name: optional alias name
683 :param seed: any real-valued SQL expression. When specified, the
684 REPEATABLE sub-clause is also rendered.
686 """
687 return TableSample._factory(selectable, sampling, name=name, seed=seed)
690@overload
691def union(
692 *selects: _TypedSelectable[Unpack[_Ts]],
693) -> CompoundSelect[Unpack[_Ts]]: ...
696@overload
697def union(
698 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
699) -> CompoundSelect[Unpack[_Ts]]: ...
702def union(
703 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
704) -> CompoundSelect[Unpack[_Ts]]:
705 r"""Return a ``UNION`` of multiple selectables.
707 The returned object is an instance of
708 :class:`_expression.CompoundSelect`.
710 A similar :func:`union()` method is available on all
711 :class:`_expression.FromClause` subclasses.
713 :param \*selects:
714 a list of :class:`_expression.Select` instances.
716 :param \**kwargs:
717 available keyword arguments are the same as those of
718 :func:`select`.
720 """
721 return CompoundSelect._create_union(*selects)
724@overload
725def union_all(
726 *selects: _TypedSelectable[Unpack[_Ts]],
727) -> CompoundSelect[Unpack[_Ts]]: ...
730@overload
731def union_all(
732 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
733) -> CompoundSelect[Unpack[_Ts]]: ...
736def union_all(
737 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],
738) -> CompoundSelect[Unpack[_Ts]]:
739 r"""Return a ``UNION ALL`` of multiple selectables.
741 The returned object is an instance of
742 :class:`_expression.CompoundSelect`.
744 A similar :func:`union_all()` method is available on all
745 :class:`_expression.FromClause` subclasses.
747 :param \*selects:
748 a list of :class:`_expression.Select` instances.
750 """
751 return CompoundSelect._create_union_all(*selects)
754def values(
755 *columns: _OnlyColumnArgument[Any],
756 name: Optional[str] = None,
757 literal_binds: bool = False,
758) -> Values:
759 r"""Construct a :class:`_expression.Values` construct representing the
760 SQL ``VALUES`` clause.
762 The column expressions and the actual data for :class:`_expression.Values`
763 are given in two separate steps. The constructor receives the column
764 expressions typically as :func:`_expression.column` constructs, and the
765 data is then passed via the :meth:`_expression.Values.data` method as a
766 list, which can be called multiple times to add more data, e.g.::
768 from sqlalchemy import column
769 from sqlalchemy import values
770 from sqlalchemy import Integer
771 from sqlalchemy import String
773 value_expr = (
774 values(
775 column("id", Integer),
776 column("name", String),
777 )
778 .data([(1, "name1"), (2, "name2")])
779 .data([(3, "name3")])
780 )
782 Would represent a SQL fragment like::
784 VALUES(1, "name1"), (2, "name2"), (3, "name3")
786 The :class:`_sql.values` construct has an optional
787 :paramref:`_sql.values.name` field; when using this field, the
788 PostgreSQL-specific "named VALUES" clause may be generated::
790 value_expr = values(
791 column("id", Integer), column("name", String), name="somename"
792 ).data([(1, "name1"), (2, "name2"), (3, "name3")])
794 When selecting from the above construct, the name and column names will
795 be listed out using a PostgreSQL-specific syntax::
797 >>> print(value_expr.select())
798 SELECT somename.id, somename.name
799 FROM (VALUES (:param_1, :param_2), (:param_3, :param_4),
800 (:param_5, :param_6)) AS somename (id, name)
802 For a more database-agnostic means of SELECTing named columns from a
803 VALUES expression, the :meth:`.Values.cte` method may be used, which
804 produces a named CTE with explicit column names against the VALUES
805 construct within; this syntax works on PostgreSQL, SQLite, and MariaDB::
807 value_expr = (
808 values(
809 column("id", Integer),
810 column("name", String),
811 )
812 .data([(1, "name1"), (2, "name2"), (3, "name3")])
813 .cte()
814 )
816 Rendering as::
818 >>> print(value_expr.select())
819 WITH anon_1(id, name) AS
820 (VALUES (:param_1, :param_2), (:param_3, :param_4), (:param_5, :param_6))
821 SELECT anon_1.id, anon_1.name
822 FROM anon_1
824 .. versionadded:: 2.0.42 Added the :meth:`.Values.cte` method to
825 :class:`.Values`
827 :param \*columns: column expressions, typically composed using
828 :func:`_expression.column` objects.
830 :param name: the name for this VALUES construct. If omitted, the
831 VALUES construct will be unnamed in a SQL expression. Different
832 backends may have different requirements here.
834 :param literal_binds: Defaults to False. Whether or not to render
835 the data values inline in the SQL output, rather than using bound
836 parameters.
838 """ # noqa: E501
840 return Values(*columns, literal_binds=literal_binds, name=name)