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

1176 {printsql}SELECT table1.columna AS table1_columna, table1.columnb AS table1_columnb, table2.columna AS table2_columna, table2.columnc AS table2_columnc

1177 FROM table1 JOIN table2 ON true

1178

1179 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method.

1180 Equivalent to the legacy method ``Select.apply_labels()``;

1181 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` is SQLAlchemy's legacy

1182 auto-labeling style. :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` provides a

1183 less intrusive approach to disambiguation of same-named column expressions.

1184

1185

1186 .. versionadded:: 1.4

1187

1188 """ # noqa: E501

1189

1190 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

1191 """Label style indicating that columns with a name that conflicts with

1192 an existing name should be labeled with a semi-anonymizing label

1193 when generating the columns clause of a SELECT statement.

1194

1195 Below, most column names are left unaffected, except for the second

1196 occurrence of the name ``columna``, which is labeled using the

1197 label ``columna_1`` to disambiguate it from that of ``tablea.columna``:

1198

1199 .. sourcecode:: pycon+sql

1200

1201 >>> from sqlalchemy import (

1202 ... table,

1203 ... column,

1204 ... select,

1205 ... true,

1206 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1207 ... )

1208 >>> table1 = table("table1", column("columna"), column("columnb"))

1209 >>> table2 = table("table2", column("columna"), column("columnc"))

1210 >>> print(

1211 ... select(table1, table2)

1212 ... .join(table2, true())

1213 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1214 ... )

1215 {printsql}SELECT table1.columna, table1.columnb, table2.columna AS columna_1, table2.columnc

1216 FROM table1 JOIN table2 ON true

1217

1218 Used with the :meth:`_sql.GenerativeSelect.set_label_style` method,

1219 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` is the default labeling style

1220 for all SELECT statements outside of :term:`1.x style` ORM queries.

1221

1222 .. versionadded:: 1.4

1223

1224 """ # noqa: E501

1225

1226 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1227 """The default label style, refers to

1228 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1229

1230 .. versionadded:: 1.4

1231

1232 """

1233

1234 LABEL_STYLE_LEGACY_ORM = 3

1235

1236

1237(

1238 LABEL_STYLE_NONE,

1239 LABEL_STYLE_TABLENAME_PLUS_COL,

1240 LABEL_STYLE_DISAMBIGUATE_ONLY,

1241 _,

1242) = list(SelectLabelStyle)

1243

1244LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1245

1246

1247class Join(roles.DMLTableRole, FromClause):

1248 """Represent a ``JOIN`` construct between two

1249 :class:`_expression.FromClause`

1250 elements.

1251

1252 The public constructor function for :class:`_expression.Join`

1253 is the module-level

1254 :func:`_expression.join()` function, as well as the

1255 :meth:`_expression.FromClause.join` method

1256 of any :class:`_expression.FromClause` (e.g. such as

1257 :class:`_schema.Table`).

1258

1259 .. seealso::

1260

1261 :func:`_expression.join`

1262

1263 :meth:`_expression.FromClause.join`

1264

1265 """

1266

1267 __visit_name__ = "join"

1268

1269 _traverse_internals: _TraverseInternalsType = [

1270 ("left", InternalTraversal.dp_clauseelement),

1271 ("right", InternalTraversal.dp_clauseelement),

1272 ("onclause", InternalTraversal.dp_clauseelement),

1273 ("isouter", InternalTraversal.dp_boolean),

1274 ("full", InternalTraversal.dp_boolean),

1275 ]

1276

1277 _is_join = True

1278

1279 left: FromClause

1280 right: FromClause

1281 onclause: Optional[ColumnElement[bool]]

1282 isouter: bool

1283 full: bool

1284

1285 def __init__(

1286 self,

1287 left: _FromClauseArgument,

1288 right: _FromClauseArgument,

1289 onclause: Optional[_OnClauseArgument] = None,

1290 isouter: bool = False,

1291 full: bool = False,

1292 ):

1293 """Construct a new :class:`_expression.Join`.

1294

1295 The usual entrypoint here is the :func:`_expression.join`

1296 function or the :meth:`_expression.FromClause.join` method of any

1297 :class:`_expression.FromClause` object.

1298

1299 """

1300

1301 # when deannotate was removed here, callcounts went up for ORM

1302 # compilation of eager joins, since there were more comparisons of

1303 # annotated objects. test_orm.py -> test_fetch_results

1304 # was therefore changed to show a more real-world use case, where the

1305 # compilation is cached; there's no change in post-cache callcounts.

1306 # callcounts for a single compilation in that particular test

1307 # that includes about eight joins about 1100 extra fn calls, from

1308 # 29200 -> 30373

1309

1310 self.left = coercions.expect(

1311 roles.FromClauseRole,

1312 left,

1313 )

1314 self.right = coercions.expect(

1315 roles.FromClauseRole,

1316 right,

1317 ).self_group()

1318

1319 if onclause is None:

1320 self.onclause = self._match_primaries(self.left, self.right)

1321 else:

1322 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1323 # not merged yet

1324 self.onclause = coercions.expect(

1325 roles.OnClauseRole, onclause

1326 ).self_group(against=operators._asbool)

1327

1328 self.isouter = isouter

1329 self.full = full

1330

1331 @util.ro_non_memoized_property

1332 def description(self) -> str:

1333 return "Join object on %s(%d) and %s(%d)" % (

1334 self.left.description,

1335 id(self.left),

1336 self.right.description,

1337 id(self.right),

1338 )

1339

1340 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

1341 return (

1342 # use hash() to ensure direct comparison to annotated works

1343 # as well

1344 hash(fromclause) == hash(self)

1345 or self.left.is_derived_from(fromclause)

1346 or self.right.is_derived_from(fromclause)

1347 )

1348

1349 def self_group(

1350 self, against: Optional[OperatorType] = None

1351 ) -> FromGrouping:

1352 return FromGrouping(self)

1353

1354 @util.preload_module("sqlalchemy.sql.util")

1355 def _populate_column_collection(

1356 self,

1357 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

1358 primary_key: ColumnSet,

1359 foreign_keys: Set[KeyedColumnElement[Any]],

1360 ) -> None:

1361 sqlutil = util.preloaded.sql_util

1362 _columns: List[KeyedColumnElement[Any]] = [c for c in self.left.c] + [

1363 c for c in self.right.c

1364 ]

1365

1366 primary_key.extend(

1367 sqlutil.reduce_columns(

1368 (c for c in _columns if c.primary_key), self.onclause

1369 )

1370 )

1371 columns._populate_separate_keys(

1372 (col._tq_key_label, col) for col in _columns # type: ignore

1373 )

1374 foreign_keys.update(

1375 itertools.chain(*[col.foreign_keys for col in _columns]) # type: ignore # noqa: E501

1376 )

1377

1378 def _copy_internals(

1379 self, clone: _CloneCallableType = _clone, **kw: Any

1380 ) -> None:

1381 # see Select._copy_internals() for similar concept

1382

1383 # here we pre-clone "left" and "right" so that we can

1384 # determine the new FROM clauses

1385 all_the_froms = set(

1386 itertools.chain(

1387 _from_objects(self.left),

1388 _from_objects(self.right),

1389 )

1390 )

1391

1392 # run the clone on those. these will be placed in the

1393 # cache used by the clone function

1394 new_froms = {f: clone(f, **kw) for f in all_the_froms}

1395

1396 # set up a special replace function that will replace for

1397 # ColumnClause with parent table referring to those

1398 # replaced FromClause objects

1399 def replace(

1400 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

1401 **kw: Any,

1402 ) -> Optional[KeyedColumnElement[Any]]:

1403 if isinstance(obj, ColumnClause) and obj.table in new_froms:

1404 newelem = new_froms[obj.table].corresponding_column(obj)

1405 return newelem

1406 return None

1407

1408 kw["replace"] = replace

1409

1410 # run normal _copy_internals. the clones for

1411 # left and right will come from the clone function's

1412 # cache

1413 super()._copy_internals(clone=clone, **kw)

1414

1415 self._reset_memoizations()

1416

1417 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

1418 super()._refresh_for_new_column(column)

1419 self.left._refresh_for_new_column(column)

1420 self.right._refresh_for_new_column(column)

1421

1422 def _match_primaries(

1423 self,

1424 left: FromClause,

1425 right: FromClause,

1426 ) -> ColumnElement[bool]:

1427 if isinstance(left, Join):

1428 left_right = left.right

1429 else:

1430 left_right = None

1431 return self._join_condition(left, right, a_subset=left_right)

1432

1433 @classmethod

1434 def _join_condition(

1435 cls,

1436 a: FromClause,

1437 b: FromClause,

1438 *,

1439 a_subset: Optional[FromClause] = None,

1440 consider_as_foreign_keys: Optional[

1441 AbstractSet[ColumnClause[Any]]

1442 ] = None,

1443 ) -> ColumnElement[bool]:

1444 """Create a join condition between two tables or selectables.

1445

1446 See sqlalchemy.sql.util.join_condition() for full docs.

1447

1448 """

1449 constraints = cls._joincond_scan_left_right(

1450 a, a_subset, b, consider_as_foreign_keys

1451 )

1452

1453 if len(constraints) > 1:

1454 cls._joincond_trim_constraints(

1455 a, b, constraints, consider_as_foreign_keys

1456 )

1457

1458 if len(constraints) == 0:

1459 if isinstance(b, FromGrouping):

1460 hint = (

1461 " Perhaps you meant to convert the right side to a "

1462 "subquery using alias()?"

1463 )

1464 else:

1465 hint = ""

1466 raise exc.NoForeignKeysError(

1467 "Can't find any foreign key relationships "

1468 "between '%s' and '%s'.%s"

1469 % (a.description, b.description, hint)

1470 )

1471

1472 crit = [(x == y) for x, y in list(constraints.values())[0]]

1473 if len(crit) == 1:

1474 return crit[0]

1475 else:

1476 return and_(*crit)

1477

1478 @classmethod

1479 def _can_join(

1480 cls,

1481 left: FromClause,

1482 right: FromClause,

1483 *,

1484 consider_as_foreign_keys: Optional[

1485 AbstractSet[ColumnClause[Any]]

1486 ] = None,

1487 ) -> bool:

1488 if isinstance(left, Join):

1489 left_right = left.right

1490 else:

1491 left_right = None

1492

1493 constraints = cls._joincond_scan_left_right(

1494 a=left,

1495 b=right,

1496 a_subset=left_right,

1497 consider_as_foreign_keys=consider_as_foreign_keys,

1498 )

1499

1500 return bool(constraints)

1501

1502 @classmethod

1503 @util.preload_module("sqlalchemy.sql.util")

1504 def _joincond_scan_left_right(

1505 cls,

1506 a: FromClause,

1507 a_subset: Optional[FromClause],

1508 b: FromClause,

1509 consider_as_foreign_keys: Optional[AbstractSet[ColumnClause[Any]]],

1510 ) -> collections.defaultdict[

1511 Optional[ForeignKeyConstraint],

1512 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1513 ]:

1514 sql_util = util.preloaded.sql_util

1515

1516 a = coercions.expect(roles.FromClauseRole, a)

1517 b = coercions.expect(roles.FromClauseRole, b)

1518

1519 constraints: collections.defaultdict[

1520 Optional[ForeignKeyConstraint],

1521 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1522 ] = collections.defaultdict(list)

1523

1524 for left in (a_subset, a):

1525 if left is None:

1526 continue

1527 for fk in sorted(

1528 b.foreign_keys,

1529 key=lambda fk: fk.parent._creation_order,

1530 ):

1531 if (

1532 consider_as_foreign_keys is not None

1533 and fk.parent not in consider_as_foreign_keys

1534 ):

1535 continue

1536 try:

1537 col = fk.get_referent(left)

1538 except exc.NoReferenceError as nrte:

1539 table_names = {t.name for t in sql_util.find_tables(left)}

1540 if nrte.table_name in table_names:

1541 raise

1542 else:

1543 continue

1544

1545 if col is not None:

1546 constraints[fk.constraint].append((col, fk.parent))

1547 if left is not b:

1548 for fk in sorted(

1549 left.foreign_keys,

1550 key=lambda fk: fk.parent._creation_order,

1551 ):

1552 if (

1553 consider_as_foreign_keys is not None

1554 and fk.parent not in consider_as_foreign_keys

1555 ):

1556 continue

1557 try:

1558 col = fk.get_referent(b)

1559 except exc.NoReferenceError as nrte:

1560 table_names = {t.name for t in sql_util.find_tables(b)}

1561 if nrte.table_name in table_names:

1562 raise

1563 else:

1564 continue

1565

1566 if col is not None:

1567 constraints[fk.constraint].append((col, fk.parent))

1568 if constraints:

1569 break

1570 return constraints

1571

1572 @classmethod

1573 def _joincond_trim_constraints(

1574 cls,

1575 a: FromClause,

1576 b: FromClause,

1577 constraints: Dict[Any, Any],

1578 consider_as_foreign_keys: Optional[Any],

1579 ) -> None:

1580 # more than one constraint matched. narrow down the list

1581 # to include just those FKCs that match exactly to

1582 # "consider_as_foreign_keys".

1583 if consider_as_foreign_keys:

1584 for const in list(constraints):

1585 if {f.parent for f in const.elements} != set(

1586 consider_as_foreign_keys

1587 ):

1588 del constraints[const]

1589

1590 # if still multiple constraints, but

1591 # they all refer to the exact same end result, use it.

1592 if len(constraints) > 1:

1593 dedupe = {tuple(crit) for crit in constraints.values()}

1594 if len(dedupe) == 1:

1595 key = list(constraints)[0]

1596 constraints = {key: constraints[key]}

1597

1598 if len(constraints) != 1:

1599 raise exc.AmbiguousForeignKeysError(

1600 "Can't determine join between '%s' and '%s'; "

1601 "tables have more than one foreign key "

1602 "constraint relationship between them. "

1603 "Please specify the 'onclause' of this "

1604 "join explicitly." % (a.description, b.description)

1605 )

1606

1607 def select(self) -> Select[Unpack[TupleAny]]:

1608 r"""Create a :class:`_expression.Select` from this

1609 :class:`_expression.Join`.

1610

1611 E.g.::

1612

1613 stmt = table_a.join(table_b, table_a.c.id == table_b.c.a_id)

1614

1615 stmt = stmt.select()

1616

1617 The above will produce a SQL string resembling:

1618

1619 .. sourcecode:: sql

1620

1621 SELECT table_a.id, table_a.col, table_b.id, table_b.a_id

1622 FROM table_a JOIN table_b ON table_a.id = table_b.a_id

1623

1624 """

1625 return Select(self.left, self.right).select_from(self)

1626

1627 @util.preload_module("sqlalchemy.sql.util")

1628 def _anonymous_fromclause(

1629 self, name: Optional[str] = None, flat: bool = False

1630 ) -> TODO_Any:

1631 sqlutil = util.preloaded.sql_util

1632 if flat:

1633 if isinstance(self.left, (FromGrouping, Join)):

1634 left_name = name # will recurse

1635 else:

1636 if name and isinstance(self.left, NamedFromClause):

1637 left_name = f"{name}_{self.left.name}"

1638 else:

1639 left_name = name

1640 if isinstance(self.right, (FromGrouping, Join)):

1641 right_name = name # will recurse

1642 else:

1643 if name and isinstance(self.right, NamedFromClause):

1644 right_name = f"{name}_{self.right.name}"

1645 else:

1646 right_name = name

1647 left_a, right_a = (

1648 self.left._anonymous_fromclause(name=left_name, flat=flat),

1649 self.right._anonymous_fromclause(name=right_name, flat=flat),

1650 )

1651 adapter = sqlutil.ClauseAdapter(left_a).chain(

1652 sqlutil.ClauseAdapter(right_a)

1653 )

1654

1655 return left_a.join(

1656 right_a,

1657 adapter.traverse(self.onclause),

1658 isouter=self.isouter,

1659 full=self.full,

1660 )

1661 else:

1662 return (

1663 self.select()

1664 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1665 .correlate(None)

1666 .alias(name)

1667 )

1668

1669 @util.ro_non_memoized_property

1670 def _hide_froms(self) -> Iterable[FromClause]:

1671 return itertools.chain(

1672 *[_from_objects(x.left, x.right) for x in self._cloned_set]

1673 )

1674

1675 @util.ro_non_memoized_property

1676 def _from_objects(self) -> List[FromClause]:

1677 self_list: List[FromClause] = [self]

1678 return self_list + self.left._from_objects + self.right._from_objects

1679

1680

1681class NoInit:

1682 def __init__(self, *arg: Any, **kw: Any):

1683 raise NotImplementedError(

1684 "The %s class is not intended to be constructed "

1685 "directly. Please use the %s() standalone "

1686 "function or the %s() method available from appropriate "

1687 "selectable objects."

1688 % (

1689 self.__class__.__name__,

1690 self.__class__.__name__.lower(),

1691 self.__class__.__name__.lower(),

1692 )

1693 )

1694

1695

1696class LateralFromClause(NamedFromClause):

1697 """mark a FROM clause as being able to render directly as LATERAL"""

1698

1699

1700# FromClause ->

1701# AliasedReturnsRows

1702# -> Alias only for FromClause

1703# -> Subquery only for SelectBase

1704# -> CTE only for HasCTE -> SelectBase, DML

1705# -> Lateral -> FromClause, but we accept SelectBase

1706# w/ non-deprecated coercion

1707# -> TableSample -> only for FromClause

1708

1709

1710class AliasedReturnsRows(NoInit, NamedFromClause):

1711 """Base class of aliases against tables, subqueries, and other

1712 selectables."""

1713

1714 _is_from_container = True

1715

1716 _supports_derived_columns = False

1717

1718 element: ReturnsRows

1719

1720 _traverse_internals: _TraverseInternalsType = [

1721 ("element", InternalTraversal.dp_clauseelement),

1722 ("name", InternalTraversal.dp_anon_name),

1723 ]

1724

1725 @classmethod

1726 def _construct(

1727 cls,

1728 selectable: Any,

1729 *,

1730 name: Optional[str] = None,

1731 **kw: Any,

1732 ) -> Self:

1733 obj = cls.__new__(cls)

1734 obj._init(selectable, name=name, **kw)

1735 return obj

1736

1737 def _init(self, selectable: Any, *, name: Optional[str] = None) -> None:

1738 self.element = coercions.expect(

1739 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1740 )

1741 self.element = selectable

1742 self._orig_name = name

1743 if name is None:

1744 if (

1745 isinstance(selectable, FromClause)

1746 and selectable.named_with_column

1747 ):

1748 name = getattr(selectable, "name", None)

1749 if isinstance(name, _anonymous_label):

1750 name = None

1751 name = _anonymous_label.safe_construct(

1752 os.urandom(10).hex(), name or "anon"

1753 )

1754 self.name = name

1755

1756 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

1757 super()._refresh_for_new_column(column)

1758 self.element._refresh_for_new_column(column)

1759

1760 def _populate_column_collection(

1761 self,

1762 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

1763 primary_key: ColumnSet,

1764 foreign_keys: Set[KeyedColumnElement[Any]],

1765 ) -> None:

1766 self.element._generate_fromclause_column_proxies(

1767 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1768 )

1769

1770 @util.ro_non_memoized_property

1771 def description(self) -> str:

1772 name = self.name

1773 if isinstance(name, _anonymous_label):

1774 return "anon_1"

1775

1776 return name

1777

1778 @util.ro_non_memoized_property

1779 def implicit_returning(self) -> bool:

1780 return self.element.implicit_returning # type: ignore

1781

1782 @property

1783 def original(self) -> ReturnsRows:

1784 """Legacy for dialects that are referring to Alias.original."""

1785 return self.element

1786

1787 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

1788 if fromclause in self._cloned_set:

1789 return True

1790 return self.element.is_derived_from(fromclause)

1791

1792 def _copy_internals(

1793 self, clone: _CloneCallableType = _clone, **kw: Any

1794 ) -> None:

1795 existing_element = self.element

1796

1797 super()._copy_internals(clone=clone, **kw)

1798

1799 # the element clone is usually against a Table that returns the

1800 # same object. don't reset exported .c. collections and other

1801 # memoized details if it was not changed. this saves a lot on

1802 # performance.

1803 if existing_element is not self.element:

1804 self._reset_column_collection()

1805

1806 @property

1807 def _from_objects(self) -> List[FromClause]:

1808 return [self]

1809

1810

1811class FromClauseAlias(AliasedReturnsRows):

1812 element: FromClause

1813

1814 @util.ro_non_memoized_property

1815 def description(self) -> str:

1816 name = self.name

1817 if isinstance(name, _anonymous_label):

1818 return f"Anonymous alias of {self.element.description}"

1819

1820 return name

1821

1822

1823class Alias(roles.DMLTableRole, FromClauseAlias):

1824 """Represents an table or selectable alias (AS).

1825

1826 Represents an alias, as typically applied to any table or

1827 sub-select within a SQL statement using the ``AS`` keyword (or

1828 without the keyword on certain databases such as Oracle Database).

1829

1830 This object is constructed from the :func:`_expression.alias` module

1831 level function as well as the :meth:`_expression.FromClause.alias`

1832 method available

1833 on all :class:`_expression.FromClause` subclasses.

1834

1835 .. seealso::

1836

1837 :meth:`_expression.FromClause.alias`

1838

1839 """

1840

1841 __visit_name__ = "alias"

1842

1843 inherit_cache = True

1844

1845 element: FromClause

1846

1847 @classmethod

1848 def _factory(

1849 cls,

1850 selectable: FromClause,

1851 name: Optional[str] = None,

1852 flat: bool = False,

1853 ) -> NamedFromClause:

1854 return coercions.expect(roles.FromClauseRole, selectable).alias(

1855 name=name, flat=flat

1856 )

1857

1858

1859class TableValuedAlias(LateralFromClause, Alias):

1860 """An alias against a "table valued" SQL function.

1861

1862 This construct provides for a SQL function that returns columns

1863 to be used in the FROM clause of a SELECT statement. The

1864 object is generated using the :meth:`_functions.FunctionElement.table_valued`

1865 method, e.g.:

1866

1867 .. sourcecode:: pycon+sql

1868

1869 >>> from sqlalchemy import select, func

1870 >>> fn = func.json_array_elements_text('["one", "two", "three"]').table_valued(

1871 ... "value"

1872 ... )

1873 >>> print(select(fn.c.value))

1874 {printsql}SELECT anon_1.value

1875 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1876

1877 .. versionadded:: 1.4.0b2

1878

1879 .. seealso::

1880

1881 :ref:`tutorial_functions_table_valued` - in the :ref:`unified_tutorial`

1882

1883 """ # noqa: E501

1884

1885 __visit_name__ = "table_valued_alias"

1886

1887 _supports_derived_columns = True

1888 _render_derived = False

1889 _render_derived_w_types = False

1890 joins_implicitly = False

1891

1892 _traverse_internals: _TraverseInternalsType = [

1893 ("element", InternalTraversal.dp_clauseelement),

1894 ("name", InternalTraversal.dp_anon_name),

1895 ("_tableval_type", InternalTraversal.dp_type),

1896 ("_render_derived", InternalTraversal.dp_boolean),

1897 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1898 ]

1899

1900 def _init(

1901 self,

1902 selectable: Any,

1903 *,

1904 name: Optional[str] = None,

1905 table_value_type: Optional[TableValueType] = None,

1906 joins_implicitly: bool = False,

1907 ) -> None:

1908 super()._init(selectable, name=name)

1909

1910 self.joins_implicitly = joins_implicitly

1911 self._tableval_type = (

1912 type_api.TABLEVALUE

1913 if table_value_type is None

1914 else table_value_type

1915 )

1916

1917 @HasMemoized.memoized_attribute

1918 def column(self) -> TableValuedColumn[Any]:

1919 """Return a column expression representing this

1920 :class:`_sql.TableValuedAlias`.

1921

1922 This accessor is used to implement the

1923 :meth:`_functions.FunctionElement.column_valued` method. See that

1924 method for further details.

1925

1926 E.g.:

1927

1928 .. sourcecode:: pycon+sql

1929

1930 >>> print(select(func.some_func().table_valued("value").column))

1931 {printsql}SELECT anon_1 FROM some_func() AS anon_1

1932

1933 .. seealso::

1934

1935 :meth:`_functions.FunctionElement.column_valued`

1936

1937 """

1938

1939 return TableValuedColumn(self, self._tableval_type)

1940

1941 def alias(

1942 self, name: Optional[str] = None, flat: bool = False

1943 ) -> TableValuedAlias:

1944 """Return a new alias of this :class:`_sql.TableValuedAlias`.

1945

1946 This creates a distinct FROM object that will be distinguished

1947 from the original one when used in a SQL statement.

1948

1949 """

1950

1951 tva: TableValuedAlias = TableValuedAlias._construct(

1952 self,

1953 name=name,

1954 table_value_type=self._tableval_type,

1955 joins_implicitly=self.joins_implicitly,

1956 )

1957

1958 if self._render_derived:

1959 tva._render_derived = True

1960 tva._render_derived_w_types = self._render_derived_w_types

1961

1962 return tva

1963

1964 def lateral(self, name: Optional[str] = None) -> LateralFromClause:

1965 """Return a new :class:`_sql.TableValuedAlias` with the lateral flag

1966 set, so that it renders as LATERAL.

1967

1968 .. seealso::

1969

1970 :func:`_expression.lateral`

1971

1972 """

1973 tva = self.alias(name=name)

1974 tva._is_lateral = True

1975 return tva

1976

1977 def render_derived(

1978 self,

1979 name: Optional[str] = None,

1980 with_types: bool = False,

1981 ) -> TableValuedAlias:

1982 """Apply "render derived" to this :class:`_sql.TableValuedAlias`.

1983

1984 This has the effect of the individual column names listed out

1985 after the alias name in the "AS" sequence, e.g.:

1986

1987 .. sourcecode:: pycon+sql

1988

1989 >>> print(

1990 ... select(

1991 ... func.unnest(array(["one", "two", "three"]))

1992 ... .table_valued("x", with_ordinality="o")

1993 ... .render_derived()

1994 ... )

1995 ... )

1996 {printsql}SELECT anon_1.x, anon_1.o

1997 FROM unnest(ARRAY[%(param_1)s, %(param_2)s, %(param_3)s]) WITH ORDINALITY AS anon_1(x, o)

1998

1999 The ``with_types`` keyword will render column types inline within

2000 the alias expression (this syntax currently applies to the

2001 PostgreSQL database):

2002

2003 .. sourcecode:: pycon+sql

2004

2005 >>> print(

2006 ... select(

2007 ... func.json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","c":"bar"}]')

2008 ... .table_valued(column("a", Integer), column("b", String))

2009 ... .render_derived(with_types=True)

2010 ... )

2011 ... )

2012 {printsql}SELECT anon_1.a, anon_1.b FROM json_to_recordset(:json_to_recordset_1)

2013 AS anon_1(a INTEGER, b VARCHAR)

2014

2015 :param name: optional string name that will be applied to the alias

2016 generated. If left as None, a unique anonymizing name will be used.

2017

2018 :param with_types: if True, the derived columns will include the

2019 datatype specification with each column. This is a special syntax

2020 currently known to be required by PostgreSQL for some SQL functions.

2021

2022 """ # noqa: E501

2023

2024 # note: don't use the @_generative system here, keep a reference

2025 # to the original object. otherwise you can have reuse of the

2026 # python id() of the original which can cause name conflicts if

2027 # a new anon-name grabs the same identifier as the local anon-name

2028 # (just saw it happen on CI)

2029

2030 # construct against original to prevent memory growth

2031 # for repeated generations

2032 new_alias: TableValuedAlias = TableValuedAlias._construct(

2033 self.element,

2034 name=name,

2035 table_value_type=self._tableval_type,

2036 joins_implicitly=self.joins_implicitly,

2037 )

2038 new_alias._render_derived = True

2039 new_alias._render_derived_w_types = with_types

2040 return new_alias

2041

2042

2043class Lateral(FromClauseAlias, LateralFromClause):

2044 """Represent a LATERAL subquery.

2045

2046 This object is constructed from the :func:`_expression.lateral` module

2047 level function as well as the :meth:`_expression.FromClause.lateral`

2048 method available

2049 on all :class:`_expression.FromClause` subclasses.

2050

2051 While LATERAL is part of the SQL standard, currently only more recent

2052 PostgreSQL versions provide support for this keyword.

2053

2054 .. seealso::

2055

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

2057

2058 """

2059

2060 __visit_name__ = "lateral"

2061 _is_lateral = True

2062

2063 inherit_cache = True

2064

2065 @classmethod

2066 def _factory(

2067 cls,

2068 selectable: Union[SelectBase, _FromClauseArgument],

2069 name: Optional[str] = None,

2070 ) -> LateralFromClause:

2071 return coercions.expect(

2072 roles.FromClauseRole, selectable, explicit_subquery=True

2073 ).lateral(name=name)

2074

2075

2076class TableSample(FromClauseAlias):

2077 """Represent a TABLESAMPLE clause.

2078

2079 This object is constructed from the :func:`_expression.tablesample` module

2080 level function as well as the :meth:`_expression.FromClause.tablesample`

2081 method

2082 available on all :class:`_expression.FromClause` subclasses.

2083

2084 .. seealso::

2085

2086 :func:`_expression.tablesample`

2087

2088 """

2089

2090 __visit_name__ = "tablesample"

2091

2092 _traverse_internals: _TraverseInternalsType = (

2093 AliasedReturnsRows._traverse_internals

2094 + [

2095 ("sampling", InternalTraversal.dp_clauseelement),

2096 ("seed", InternalTraversal.dp_clauseelement),

2097 ]

2098 )

2099

2100 @classmethod

2101 def _factory(

2102 cls,

2103 selectable: _FromClauseArgument,

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

2105 name: Optional[str] = None,

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

2107 ) -> TableSample:

2108 return coercions.expect(roles.FromClauseRole, selectable).tablesample(

2109 sampling, name=name, seed=seed

2110 )

2111

2112 @util.preload_module("sqlalchemy.sql.functions")

2113 def _init( # type: ignore[override]

2114 self,

2115 selectable: Any,

2116 *,

2117 name: Optional[str] = None,

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

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

2120 ) -> None:

2121 assert sampling is not None

2122 functions = util.preloaded.sql_functions

2123 if not isinstance(sampling, functions.Function):

2124 sampling = functions.func.system(sampling)

2125

2126 self.sampling: Function[Any] = sampling

2127 self.seed = seed

2128 super()._init(selectable, name=name)

2129

2130 def _get_method(self) -> Function[Any]:

2131 return self.sampling

2132

2133

2134class CTE(

2135 roles.DMLTableRole,

2136 roles.IsCTERole,

2137 Generative,

2138 HasPrefixes,

2139 HasSuffixes,

2140 AliasedReturnsRows,

2141):

2142 """Represent a Common Table Expression.

2143

2144 The :class:`_expression.CTE` object is obtained using the

2145 :meth:`_sql.SelectBase.cte` method from any SELECT statement. A less often

2146 available syntax also allows use of the :meth:`_sql.HasCTE.cte` method

2147 present on :term:`DML` constructs such as :class:`_sql.Insert`,

2148 :class:`_sql.Update` and

2149 :class:`_sql.Delete`. See the :meth:`_sql.HasCTE.cte` method for

2150 usage details on CTEs.

2151

2152 .. seealso::

2153

2154 :ref:`tutorial_subqueries_ctes` - in the 2.0 tutorial

2155

2156 :meth:`_sql.HasCTE.cte` - examples of calling styles

2157

2158 """

2159

2160 __visit_name__ = "cte"

2161

2162 _traverse_internals: _TraverseInternalsType = (

2163 AliasedReturnsRows._traverse_internals

2164 + [

2165 ("_cte_alias", InternalTraversal.dp_clauseelement),

2166 ("_restates", InternalTraversal.dp_clauseelement),

2167 ("recursive", InternalTraversal.dp_boolean),

2168 ("nesting", InternalTraversal.dp_boolean),

2169 ]

2170 + HasPrefixes._has_prefixes_traverse_internals

2171 + HasSuffixes._has_suffixes_traverse_internals

2172 )

2173

2174 element: HasCTE

2175

2176 @classmethod

2177 def _factory(

2178 cls,

2179 selectable: HasCTE,

2180 name: Optional[str] = None,

2181 recursive: bool = False,

2182 ) -> CTE:

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

2184 or Common Table Expression instance.

2185

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

2187

2188 """

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

2190 name=name, recursive=recursive

2191 )

2192

2193 def _init(

2194 self,

2195 selectable: HasCTE,

2196 *,

2197 name: Optional[str] = None,

2198 recursive: bool = False,

2199 nesting: bool = False,

2200 _cte_alias: Optional[CTE] = None,

2201 _restates: Optional[CTE] = None,

2202 _prefixes: Optional[Tuple[()]] = None,

2203 _suffixes: Optional[Tuple[()]] = None,

2204 ) -> None:

2205 self.recursive = recursive

2206 self.nesting = nesting

2207 self._cte_alias = _cte_alias

2208 # Keep recursivity reference with union/union_all

2209 self._restates = _restates

2210 if _prefixes:

2211 self._prefixes = _prefixes

2212 if _suffixes:

2213 self._suffixes = _suffixes

2214 super()._init(selectable, name=name)

2215

2216 def _populate_column_collection(

2217 self,

2218 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

2219 primary_key: ColumnSet,

2220 foreign_keys: Set[KeyedColumnElement[Any]],

2221 ) -> None:

2222 if self._cte_alias is not None:

2223 self._cte_alias._generate_fromclause_column_proxies(

2224 self,

2225 columns,

2226 primary_key=primary_key,

2227 foreign_keys=foreign_keys,

2228 )

2229 else:

2230 self.element._generate_fromclause_column_proxies(

2231 self,

2232 columns,

2233 primary_key=primary_key,

2234 foreign_keys=foreign_keys,

2235 )

2236

2237 def alias(self, name: Optional[str] = None, flat: bool = False) -> CTE:

2238 """Return an :class:`_expression.Alias` of this

2239 :class:`_expression.CTE`.

2240

2241 This method is a CTE-specific specialization of the

2242 :meth:`_expression.FromClause.alias` method.

2243

2244 .. seealso::

2245

2246 :ref:`tutorial_using_aliases`

2247

2248 :func:`_expression.alias`

2249

2250 """

2251 return CTE._construct(

2252 self.element,

2253 name=name,

2254 recursive=self.recursive,

2255 nesting=self.nesting,

2256 _cte_alias=self,

2257 _prefixes=self._prefixes,

2258 _suffixes=self._suffixes,

2259 )

2260

2261 def union(self, *other: _SelectStatementForCompoundArgument[Any]) -> CTE:

2262 r"""Return a new :class:`_expression.CTE` with a SQL ``UNION``

2263 of the original CTE against the given selectables provided

2264 as positional arguments.

2265

2266 :param \*other: one or more elements with which to create a

2267 UNION.

2268

2269 .. versionchanged:: 1.4.28 multiple elements are now accepted.

2270

2271 .. seealso::

2272

2273 :meth:`_sql.HasCTE.cte` - examples of calling styles

2274

2275 """

2276 assert is_select_statement(

2277 self.element

2278 ), f"CTE element f{self.element} does not support union()"

2279

2280 return CTE._construct(

2281 self.element.union(*other),

2282 name=self.name,

2283 recursive=self.recursive,

2284 nesting=self.nesting,

2285 _restates=self,

2286 _prefixes=self._prefixes,

2287 _suffixes=self._suffixes,

2288 )

2289

2290 def union_all(

2291 self, *other: _SelectStatementForCompoundArgument[Any]

2292 ) -> CTE:

2293 r"""Return a new :class:`_expression.CTE` with a SQL ``UNION ALL``

2294 of the original CTE against the given selectables provided

2295 as positional arguments.

2296

2297 :param \*other: one or more elements with which to create a

2298 UNION.

2299

2300 .. versionchanged:: 1.4.28 multiple elements are now accepted.

2301

2302 .. seealso::

2303

2304 :meth:`_sql.HasCTE.cte` - examples of calling styles

2305

2306 """

2307

2308 assert is_select_statement(

2309 self.element

2310 ), f"CTE element f{self.element} does not support union_all()"

2311

2312 return CTE._construct(

2313 self.element.union_all(*other),

2314 name=self.name,

2315 recursive=self.recursive,

2316 nesting=self.nesting,

2317 _restates=self,

2318 _prefixes=self._prefixes,

2319 _suffixes=self._suffixes,

2320 )

2321

2322 def _get_reference_cte(self) -> CTE:

2323 """

2324 A recursive CTE is updated to attach the recursive part.

2325 Updated CTEs should still refer to the original CTE.

2326 This function returns this reference identifier.

2327 """

2328 return self._restates if self._restates is not None else self

2329

2330

2331class _CTEOpts(NamedTuple):

2332 nesting: bool

2333

2334

2335class _ColumnsPlusNames(NamedTuple):

2336 required_label_name: Optional[str]

2337 """

2338 string label name, if non-None, must be rendered as a

2339 label, i.e. "AS <name>"

2340 """

2341

2342 proxy_key: Optional[str]

2343 """

2344 proxy_key that is to be part of the result map for this

2345 col. this is also the key in a fromclause.c or

2346 select.selected_columns collection

2347 """

2348

2349 fallback_label_name: Optional[str]

2350 """

2351 name that can be used to render an "AS <name>" when

2352 we have to render a label even though

2353 required_label_name was not given

2354 """

2355

2356 column: Union[ColumnElement[Any], AbstractTextClause]

2357 """

2358 the ColumnElement itself

2359 """

2360

2361 repeated: bool

2362 """

2363 True if this is a duplicate of a previous column

2364 in the list of columns

2365 """

2366

2367

2368class SelectsRows(ReturnsRows):

2369 """Sub-base of ReturnsRows for elements that deliver rows

2370 directly, namely SELECT and INSERT/UPDATE/DELETE..RETURNING"""

2371

2372 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2373

2374 def _generate_columns_plus_names(

2375 self,

2376 anon_for_dupe_key: bool,

2377 cols: Optional[_SelectIterable] = None,

2378 ) -> List[_ColumnsPlusNames]:

2379 """Generate column names as rendered in a SELECT statement by

2380 the compiler, as well as tokens used to populate the .c. collection

2381 on a :class:`.FromClause`.

2382

2383 This is distinct from the _column_naming_convention generator that's

2384 intended for population of the Select.selected_columns collection,

2385 different rules. the collection returned here calls upon the

2386 _column_naming_convention as well.

2387

2388 """

2389

2390 if cols is None:

2391 cols = self._all_selected_columns

2392

2393 key_naming_convention = SelectState._column_naming_convention(

2394 self._label_style

2395 )

2396

2397 names = {}

2398

2399 result: List[_ColumnsPlusNames] = []

2400 result_append = result.append

2401

2402 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2403 label_style_none = self._label_style is LABEL_STYLE_NONE

2404

2405 # a counter used for "dedupe" labels, which have double underscores

2406 # in them and are never referred by name; they only act

2407 # as positional placeholders. they need only be unique within

2408 # the single columns clause they're rendered within (required by

2409 # some dbs such as mysql). So their anon identity is tracked against

2410 # a fixed counter rather than hash() identity.

2411 dedupe_hash = 1

2412

2413 for c in cols:

2414 repeated = False

2415

2416 if not c._render_label_in_columns_clause:

2417 effective_name = required_label_name = fallback_label_name = (

2418 None

2419 )

2420 elif label_style_none:

2421 if TYPE_CHECKING:

2422 assert is_column_element(c)

2423

2424 effective_name = required_label_name = None

2425 fallback_label_name = c._non_anon_label or c._anon_name_label

2426 else:

2427 if TYPE_CHECKING:

2428 assert is_column_element(c)

2429

2430 if table_qualified:

2431 required_label_name = effective_name = (

2432 fallback_label_name

2433 ) = c._tq_label

2434 else:

2435 effective_name = fallback_label_name = c._non_anon_label

2436 required_label_name = None

2437

2438 if effective_name is None:

2439 # it seems like this could be _proxy_key and we would

2440 # not need _expression_label but it isn't

2441 # giving us a clue when to use anon_label instead

2442 expr_label = c._expression_label

2443 if expr_label is None:

2444 repeated = c._anon_name_label in names

2445 names[c._anon_name_label] = c

2446 effective_name = required_label_name = None

2447

2448 if repeated:

2449 # here, "required_label_name" is sent as

2450 # "None" and "fallback_label_name" is sent.

2451 if table_qualified:

2452 fallback_label_name = (

2453 c._dedupe_anon_tq_label_idx(dedupe_hash)

2454 )

2455 dedupe_hash += 1

2456 else:

2457 fallback_label_name = c._dedupe_anon_label_idx(

2458 dedupe_hash

2459 )

2460 dedupe_hash += 1

2461 else:

2462 fallback_label_name = c._anon_name_label

2463 else:

2464 required_label_name = effective_name = (

2465 fallback_label_name

2466 ) = expr_label

2467

2468 if effective_name is not None:

2469 if TYPE_CHECKING:

2470 assert is_column_element(c)

2471

2472 if effective_name in names:

2473 # when looking to see if names[name] is the same column as

2474 # c, use hash(), so that an annotated version of the column

2475 # is seen as the same as the non-annotated

2476 if hash(names[effective_name]) != hash(c):

2477 # different column under the same name. apply

2478 # disambiguating label

2479 if table_qualified:

2480 required_label_name = fallback_label_name = (

2481 c._anon_tq_label

2482 )

2483 else:

2484 required_label_name = fallback_label_name = (

2485 c._anon_name_label

2486 )

2487

2488 if anon_for_dupe_key and required_label_name in names:

2489 # here, c._anon_tq_label is definitely unique to

2490 # that column identity (or annotated version), so

2491 # this should always be true.

2492 # this is also an infrequent codepath because

2493 # you need two levels of duplication to be here

2494 assert hash(names[required_label_name]) == hash(c)

2495

2496 # the column under the disambiguating label is

2497 # already present. apply the "dedupe" label to

2498 # subsequent occurrences of the column so that the

2499 # original stays non-ambiguous

2500 if table_qualified:

2501 required_label_name = fallback_label_name = (

2502 c._dedupe_anon_tq_label_idx(dedupe_hash)

2503 )

2504 dedupe_hash += 1

2505 else:

2506 required_label_name = fallback_label_name = (

2507 c._dedupe_anon_label_idx(dedupe_hash)

2508 )

2509 dedupe_hash += 1

2510 repeated = True

2511 else:

2512 names[required_label_name] = c

2513 elif anon_for_dupe_key:

2514 # same column under the same name. apply the "dedupe"

2515 # label so that the original stays non-ambiguous

2516 if table_qualified:

2517 required_label_name = fallback_label_name = (

2518 c._dedupe_anon_tq_label_idx(dedupe_hash)

2519 )

2520 dedupe_hash += 1

2521 else:

2522 required_label_name = fallback_label_name = (

2523 c._dedupe_anon_label_idx(dedupe_hash)

2524 )

2525 dedupe_hash += 1

2526 repeated = True

2527 else:

2528 names[effective_name] = c

2529

2530 result_append(

2531 _ColumnsPlusNames(

2532 required_label_name,

2533 key_naming_convention(c),

2534 fallback_label_name,

2535 c,

2536 repeated,

2537 )

2538 )

2539

2540 return result

2541

2542

2543class HasCTE(roles.HasCTERole, SelectsRows):

2544 """Mixin that declares a class to include CTE support."""

2545

2546 _has_ctes_traverse_internals: _TraverseInternalsType = [

2547 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2548 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2549 ]

2550

2551 _independent_ctes: Tuple[CTE, ...] = ()

2552 _independent_ctes_opts: Tuple[_CTEOpts, ...] = ()

2553

2554 name_cte_columns: bool = False

2555 """indicates if this HasCTE as contained within a CTE should compel the CTE

2556 to render the column names of this object in the WITH clause.

2557

2558 .. versionadded:: 2.0.42

2559

2560 """

2561

2562 @_generative

2563 def add_cte(self, *ctes: CTE, nest_here: bool = False) -> Self:

2564 r"""Add one or more :class:`_sql.CTE` constructs to this statement.

2565

2566 This method will associate the given :class:`_sql.CTE` constructs with

2567 the parent statement such that they will each be unconditionally

2568 rendered in the WITH clause of the final statement, even if not

2569 referenced elsewhere within the statement or any sub-selects.

2570

2571 The optional :paramref:`.HasCTE.add_cte.nest_here` parameter when set

2572 to True will have the effect that each given :class:`_sql.CTE` will

2573 render in a WITH clause rendered directly along with this statement,

2574 rather than being moved to the top of the ultimate rendered statement,

2575 even if this statement is rendered as a subquery within a larger

2576 statement.

2577

2578 This method has two general uses. One is to embed CTE statements that

2579 serve some purpose without being referenced explicitly, such as the use

2580 case of embedding a DML statement such as an INSERT or UPDATE as a CTE

2581 inline with a primary statement that may draw from its results

2582 indirectly. The other is to provide control over the exact placement

2583 of a particular series of CTE constructs that should remain rendered

2584 directly in terms of a particular statement that may be nested in a

2585 larger statement.

2586

2587 E.g.::

2588

2589 from sqlalchemy import table, column, select

2590

2591 t = table("t", column("c1"), column("c2"))

2592

2593 ins = t.insert().values({"c1": "x", "c2": "y"}).cte()

2594

2595 stmt = select(t).add_cte(ins)

2596

2597 Would render:

2598

2599 .. sourcecode:: sql

2600

2601 WITH anon_1 AS (

2602 INSERT INTO t (c1, c2) VALUES (:param_1, :param_2)

2603 )

2604 SELECT t.c1, t.c2

2605 FROM t

2606

2607 Above, the "anon_1" CTE is not referenced in the SELECT

2608 statement, however still accomplishes the task of running an INSERT

2609 statement.

2610

2611 Similarly in a DML-related context, using the PostgreSQL

2612 :class:`_postgresql.Insert` construct to generate an "upsert"::

2613

2614 from sqlalchemy import table, column

2615 from sqlalchemy.dialects.postgresql import insert

2616

2617 t = table("t", column("c1"), column("c2"))

2618

2619 delete_statement_cte = t.delete().where(t.c.c1 < 1).cte("deletions")

2620

2621 insert_stmt = insert(t).values({"c1": 1, "c2": 2})

2622 update_statement = insert_stmt.on_conflict_do_update(

2623 index_elements=[t.c.c1],

2624 set_={

2625 "c1": insert_stmt.excluded.c1,

2626 "c2": insert_stmt.excluded.c2,

2627 },

2628 ).add_cte(delete_statement_cte)

2629

2630 print(update_statement)

2631

2632 The above statement renders as:

2633

2634 .. sourcecode:: sql

2635

2636 WITH deletions AS (

2637 DELETE FROM t WHERE t.c1 < %(c1_1)s

2638 )

2639 INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s)

2640 ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2

2641

2642 .. versionadded:: 1.4.21

2643

2644 :param \*ctes: zero or more :class:`.CTE` constructs.

2645

2646 .. versionchanged:: 2.0 Multiple CTE instances are accepted

2647

2648 :param nest_here: if True, the given CTE or CTEs will be rendered

2649 as though they specified the :paramref:`.HasCTE.cte.nesting` flag

2650 to ``True`` when they were added to this :class:`.HasCTE`.

2651 Assuming the given CTEs are not referenced in an outer-enclosing

2652 statement as well, the CTEs given should render at the level of

2653 this statement when this flag is given.

2654

2655 .. versionadded:: 2.0

2656

2657 .. seealso::

2658

2659 :paramref:`.HasCTE.cte.nesting`

2660

2661

2662 """ # noqa: E501

2663 opt = _CTEOpts(nest_here)

2664 for cte in ctes:

2665 cte = coercions.expect(roles.IsCTERole, cte)

2666 self._independent_ctes += (cte,)

2667 self._independent_ctes_opts += (opt,)

2668 return self

2669

2670 def cte(

2671 self,

2672 name: Optional[str] = None,

2673 recursive: bool = False,

2674 nesting: bool = False,

2675 ) -> CTE:

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

2677 or Common Table Expression instance.

2678

2679 Common table expressions are a SQL standard whereby SELECT

2680 statements can draw upon secondary statements specified along

2681 with the primary statement, using a clause called "WITH".

2682 Special semantics regarding UNION can also be employed to

2683 allow "recursive" queries, where a SELECT statement can draw

2684 upon the set of rows that have previously been selected.

2685

2686 CTEs can also be applied to DML constructs UPDATE, INSERT

2687 and DELETE on some databases, both as a source of CTE rows

2688 when combined with RETURNING, as well as a consumer of

2689 CTE rows.

2690

2691 SQLAlchemy detects :class:`_expression.CTE` objects, which are treated

2692 similarly to :class:`_expression.Alias` objects, as special elements

2693 to be delivered to the FROM clause of the statement as well

2694 as to a WITH clause at the top of the statement.

2695

2696 For special prefixes such as PostgreSQL "MATERIALIZED" and

2697 "NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`

2698 method may be

2699 used to establish these.

2700

2701 :param name: name given to the common table expression. Like

2702 :meth:`_expression.FromClause.alias`, the name can be left as

2703 ``None`` in which case an anonymous symbol will be used at query

2704 compile time.

2705 :param recursive: if ``True``, will render ``WITH RECURSIVE``.

2706 A recursive common table expression is intended to be used in

2707 conjunction with UNION ALL in order to derive rows

2708 from those already selected.

2709 :param nesting: if ``True``, will render the CTE locally to the

2710 statement in which it is referenced. For more complex scenarios,

2711 the :meth:`.HasCTE.add_cte` method using the

2712 :paramref:`.HasCTE.add_cte.nest_here`

2713 parameter may also be used to more carefully

2714 control the exact placement of a particular CTE.

2715

2716 .. versionadded:: 1.4.24

2717

2718 .. seealso::

2719

2720 :meth:`.HasCTE.add_cte`

2721

2722 The following examples include two from PostgreSQL's documentation at

2723 https://www.postgresql.org/docs/current/static/queries-with.html,

2724 as well as additional examples.

2725

2726 Example 1, non recursive::

2727

2728 from sqlalchemy import (

2729 Table,

2730 Column,

2731 String,

2732 Integer,

2733 MetaData,

2734 select,

2735 func,

2736 )

2737

2738 metadata = MetaData()

2739

2740 orders = Table(

2741 "orders",

2742 metadata,

2743 Column("region", String),

2744 Column("amount", Integer),

2745 Column("product", String),

2746 Column("quantity", Integer),

2747 )

2748

2749 regional_sales = (

2750 select(orders.c.region, func.sum(orders.c.amount).label("total_sales"))

2751 .group_by(orders.c.region)

2752 .cte("regional_sales")

2753 )

2754

2755

2756 top_regions = (

2757 select(regional_sales.c.region)

2758 .where(

2759 regional_sales.c.total_sales

2760 > select(func.sum(regional_sales.c.total_sales) / 10)

2761 )

2762 .cte("top_regions")

2763 )

2764

2765 statement = (

2766 select(

2767 orders.c.region,

2768 orders.c.product,

2769 func.sum(orders.c.quantity).label("product_units"),

2770 func.sum(orders.c.amount).label("product_sales"),

2771 )

2772 .where(orders.c.region.in_(select(top_regions.c.region)))

2773 .group_by(orders.c.region, orders.c.product)

2774 )

2775

2776 result = conn.execute(statement).fetchall()

2777

2778 Example 2, WITH RECURSIVE::

2779

2780 from sqlalchemy import (

2781 Table,

2782 Column,

2783 String,

2784 Integer,

2785 MetaData,

2786 select,

2787 func,

2788 )

2789

2790 metadata = MetaData()

2791

2792 parts = Table(

2793 "parts",

2794 metadata,

2795 Column("part", String),

2796 Column("sub_part", String),

2797 Column("quantity", Integer),

2798 )

2799

2800 included_parts = (

2801 select(parts.c.sub_part, parts.c.part, parts.c.quantity)

2802 .where(parts.c.part == "our part")

2803 .cte(recursive=True)

2804 )

2805

2806

2807 incl_alias = included_parts.alias()

2808 parts_alias = parts.alias()

2809 included_parts = included_parts.union_all(

2810 select(

2811 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

2812 ).where(parts_alias.c.part == incl_alias.c.sub_part)

2813 )

2814

2815 statement = select(

2816 included_parts.c.sub_part,

2817 func.sum(included_parts.c.quantity).label("total_quantity"),

2818 ).group_by(included_parts.c.sub_part)

2819

2820 result = conn.execute(statement).fetchall()

2821

2822 Example 3, an upsert using UPDATE and INSERT with CTEs::

2823

2824 from datetime import date

2825 from sqlalchemy import (

2826 MetaData,

2827 Table,

2828 Column,

2829 Integer,

2830 Date,

2831 select,

2832 literal,

2833 and_,

2834 exists,

2835 )

2836

2837 metadata = MetaData()

2838

2839 visitors = Table(

2840 "visitors",

2841 metadata,

2842 Column("product_id", Integer, primary_key=True),

2843 Column("date", Date, primary_key=True),

2844 Column("count", Integer),

2845 )

2846

2847 # add 5 visitors for the product_id == 1

2848 product_id = 1

2849 day = date.today()

2850 count = 5

2851

2852 update_cte = (

2853 visitors.update()

2854 .where(

2855 and_(visitors.c.product_id == product_id, visitors.c.date == day)

2856 )

2857 .values(count=visitors.c.count + count)

2858 .returning(literal(1))

2859 .cte("update_cte")

2860 )

2861

2862 upsert = visitors.insert().from_select(

2863 [visitors.c.product_id, visitors.c.date, visitors.c.count],

2864 select(literal(product_id), literal(day), literal(count)).where(

2865 ~exists(update_cte.select())

2866 ),

2867 )

2868

2869 connection.execute(upsert)

2870

2871 Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above)::

2872

2873 value_a = select(literal("root").label("n")).cte("value_a")

2874

2875 # A nested CTE with the same name as the root one

2876 value_a_nested = select(literal("nesting").label("n")).cte(

2877 "value_a", nesting=True

2878 )

2879

2880 # Nesting CTEs takes ascendency locally

2881 # over the CTEs at a higher level

2882 value_b = select(value_a_nested.c.n).cte("value_b")

2883

2884 value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

2885

2886 The above query will render the second CTE nested inside the first,

2887 shown with inline parameters below as:

2888

2889 .. sourcecode:: sql

2890

2891 WITH

2892 value_a AS

2893 (SELECT 'root' AS n),

2894 value_b AS

2895 (WITH value_a AS

2896 (SELECT 'nesting' AS n)

2897 SELECT value_a.n AS n FROM value_a)

2898 SELECT value_a.n AS a, value_b.n AS b

2899 FROM value_a, value_b

2900

2901 The same CTE can be set up using the :meth:`.HasCTE.add_cte` method

2902 as follows (SQLAlchemy 2.0 and above)::

2903

2904 value_a = select(literal("root").label("n")).cte("value_a")

2905

2906 # A nested CTE with the same name as the root one

2907 value_a_nested = select(literal("nesting").label("n")).cte("value_a")

2908

2909 # Nesting CTEs takes ascendency locally

2910 # over the CTEs at a higher level

2911 value_b = (

2912 select(value_a_nested.c.n)

2913 .add_cte(value_a_nested, nest_here=True)

2914 .cte("value_b")

2915 )

2916

2917 value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))

2918

2919 Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above)::

2920

2921 edge = Table(

2922 "edge",

2923 metadata,

2924 Column("id", Integer, primary_key=True),

2925 Column("left", Integer),

2926 Column("right", Integer),

2927 )

2928

2929 root_node = select(literal(1).label("node")).cte("nodes", recursive=True)

2930

2931 left_edge = select(edge.c.left).join(

2932 root_node, edge.c.right == root_node.c.node

2933 )

2934 right_edge = select(edge.c.right).join(

2935 root_node, edge.c.left == root_node.c.node

2936 )

2937

2938 subgraph_cte = root_node.union(left_edge, right_edge)

2939

2940 subgraph = select(subgraph_cte)

2941

2942 The above query will render 2 UNIONs inside the recursive CTE:

2943

2944 .. sourcecode:: sql

2945

2946 WITH RECURSIVE nodes(node) AS (

2947 SELECT 1 AS node

2948 UNION

2949 SELECT edge."left" AS "left"

2950 FROM edge JOIN nodes ON edge."right" = nodes.node

2951 UNION

2952 SELECT edge."right" AS "right"

2953 FROM edge JOIN nodes ON edge."left" = nodes.node

2954 )

2955 SELECT nodes.node FROM nodes

2956

2957 .. seealso::

2958

2959 :meth:`_orm.Query.cte` - ORM version of

2960 :meth:`_expression.HasCTE.cte`.

2961

2962 """ # noqa: E501

2963 return CTE._construct(

2964 self, name=name, recursive=recursive, nesting=nesting

2965 )

2966

2967

2968class Subquery(AliasedReturnsRows):

2969 """Represent a subquery of a SELECT.

2970

2971 A :class:`.Subquery` is created by invoking the

2972 :meth:`_expression.SelectBase.subquery` method, or for convenience the

2973 :meth:`_expression.SelectBase.alias` method, on any

2974 :class:`_expression.SelectBase` subclass

2975 which includes :class:`_expression.Select`,

2976 :class:`_expression.CompoundSelect`, and

2977 :class:`_expression.TextualSelect`. As rendered in a FROM clause,

2978 it represents the

2979 body of the SELECT statement inside of parenthesis, followed by the usual

2980 "AS <somename>" that defines all "alias" objects.

2981

2982 The :class:`.Subquery` object is very similar to the

2983 :class:`_expression.Alias`

2984 object and can be used in an equivalent way. The difference between

2985 :class:`_expression.Alias` and :class:`.Subquery` is that

2986 :class:`_expression.Alias` always

2987 contains a :class:`_expression.FromClause` object whereas

2988 :class:`.Subquery`

2989 always contains a :class:`_expression.SelectBase` object.

2990

2991 .. versionadded:: 1.4 The :class:`.Subquery` class was added which now

2992 serves the purpose of providing an aliased version of a SELECT

2993 statement.

2994

2995 """

2996

2997 __visit_name__ = "subquery"

2998

2999 _is_subquery = True

3000

3001 inherit_cache = True

3002

3003 element: SelectBase

3004

3005 @classmethod

3006 def _factory(

3007 cls, selectable: SelectBase, name: Optional[str] = None

3008 ) -> Subquery:

3009 """Return a :class:`.Subquery` object."""

3010

3011 return coercions.expect(

3012 roles.SelectStatementRole, selectable

3013 ).subquery(name=name)

3014

3015 @util.deprecated(

3016 "1.4",

3017 "The :meth:`.Subquery.as_scalar` method, which was previously "

3018 "``Alias.as_scalar()`` prior to version 1.4, is deprecated and "

3019 "will be removed in a future release; Please use the "

3020 ":meth:`_expression.Select.scalar_subquery` method of the "

3021 ":func:`_expression.select` "

3022 "construct before constructing a subquery object, or with the ORM "

3023 "use the :meth:`_query.Query.scalar_subquery` method.",

3024 )

3025 def as_scalar(self) -> ScalarSelect[Any]:

3026 return self.element.set_label_style(LABEL_STYLE_NONE).scalar_subquery()

3027

3028

3029class FromGrouping(GroupedElement, FromClause):

3030 """Represent a grouping of a FROM clause"""

3031

3032 _traverse_internals: _TraverseInternalsType = [

3033 ("element", InternalTraversal.dp_clauseelement)

3034 ]

3035

3036 element: FromClause

3037

3038 def __init__(self, element: FromClause):

3039 self.element = coercions.expect(roles.FromClauseRole, element)

3040

3041 @util.ro_non_memoized_property

3042 def columns(

3043 self,

3044 ) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

3045 return self.element.columns

3046

3047 @util.ro_non_memoized_property

3048 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

3049 return self.element.columns

3050

3051 @property

3052 def primary_key(self) -> Iterable[NamedColumn[Any]]:

3053 return self.element.primary_key

3054

3055 @property

3056 def foreign_keys(self) -> Iterable[ForeignKey]:

3057 return self.element.foreign_keys

3058

3059 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

3060 return self.element.is_derived_from(fromclause)

3061

3062 def alias(

3063 self, name: Optional[str] = None, flat: bool = False

3064 ) -> NamedFromGrouping:

3065 return NamedFromGrouping(self.element.alias(name=name, flat=flat))

3066

3067 def _anonymous_fromclause(self, **kw: Any) -> FromGrouping:

3068 return FromGrouping(self.element._anonymous_fromclause(**kw))

3069

3070 @util.ro_non_memoized_property

3071 def _hide_froms(self) -> Iterable[FromClause]:

3072 return self.element._hide_froms

3073

3074 @util.ro_non_memoized_property

3075 def _from_objects(self) -> List[FromClause]:

3076 return self.element._from_objects

3077

3078 def __getstate__(self) -> Dict[str, FromClause]:

3079 return {"element": self.element}

3080

3081 def __setstate__(self, state: Dict[str, FromClause]) -> None:

3082 self.element = state["element"]

3083

3084 if TYPE_CHECKING:

3085

3086 def self_group(

3087 self, against: Optional[OperatorType] = None

3088 ) -> Self: ...

3089

3090

3091class NamedFromGrouping(FromGrouping, NamedFromClause):

3092 """represent a grouping of a named FROM clause

3093

3094 .. versionadded:: 2.0

3095

3096 """

3097

3098 inherit_cache = True

3099

3100 if TYPE_CHECKING:

3101

3102 def self_group(

3103 self, against: Optional[OperatorType] = None

3104 ) -> Self: ...

3105

3106

3107class TableClause(roles.DMLTableRole, Immutable, NamedFromClause):

3108 """Represents a minimal "table" construct.

3109

3110 This is a lightweight table object that has only a name, a

3111 collection of columns, which are typically produced

3112 by the :func:`_expression.column` function, and a schema::

3113

3114 from sqlalchemy import table, column

3115

3116 user = table(

3117 "user",

3118 column("id"),

3119 column("name"),

3120 column("description"),

3121 )

3122

3123 The :class:`_expression.TableClause` construct serves as the base for

3124 the more commonly used :class:`_schema.Table` object, providing

3125 the usual set of :class:`_expression.FromClause` services including

3126 the ``.c.`` collection and statement generation methods.

3127

3128 It does **not** provide all the additional schema-level services

3129 of :class:`_schema.Table`, including constraints, references to other

3130 tables, or support for :class:`_schema.MetaData`-level services.

3131 It's useful

3132 on its own as an ad-hoc construct used to generate quick SQL

3133 statements when a more fully fledged :class:`_schema.Table`

3134 is not on hand.

3135

3136 """

3137

3138 __visit_name__ = "table"

3139

3140 _traverse_internals: _TraverseInternalsType = [

3141 (

3142 "columns",

3143 InternalTraversal.dp_fromclause_canonical_column_collection,

3144 ),

3145 ("name", InternalTraversal.dp_string),

3146 ("schema", InternalTraversal.dp_string),

3147 ]

3148

3149 _is_table = True

3150

3151 fullname: str

3152

3153 implicit_returning = False

3154 """:class:`_expression.TableClause`

3155 doesn't support having a primary key or column

3156 -level defaults, so implicit returning doesn't apply."""

3157

3158 _columns: DedupeColumnCollection[ColumnClause[Any]]

3159

3160 @util.ro_memoized_property

3161 def _autoincrement_column(self) -> Optional[ColumnClause[Any]]:

3162 """No PK or default support so no autoincrement column."""

3163 return None

3164

3165 def __init__(self, name: str, *columns: ColumnClause[Any], **kw: Any):

3166 super().__init__()

3167 self.name = name

3168 self._columns = DedupeColumnCollection() # type: ignore[unused-ignore]

3169 self.primary_key = ColumnSet() # type: ignore

3170 self.foreign_keys = set() # type: ignore

3171 for c in columns:

3172 self.append_column(c)

3173

3174 schema = kw.pop("schema", None)

3175 if schema is not None:

3176 self.schema = schema

3177 if self.schema is not None:

3178 self.fullname = "%s.%s" % (self.schema, self.name)

3179 else:

3180 self.fullname = self.name

3181 if kw:

3182 raise exc.ArgumentError("Unsupported argument(s): %s" % list(kw))

3183

3184 if TYPE_CHECKING:

3185

3186 @util.ro_non_memoized_property

3187 def columns(

3188 self,

3189 ) -> ReadOnlyColumnCollection[str, ColumnClause[Any]]: ...

3190

3191 @util.ro_non_memoized_property

3192 def c(self) -> ReadOnlyColumnCollection[str, ColumnClause[Any]]: ...

3193

3194 def __str__(self) -> str:

3195 if self.schema is not None:

3196 return self.schema + "." + self.name

3197 else:

3198 return self.name

3199

3200 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

3201 pass

3202

3203 @util.ro_memoized_property

3204 def description(self) -> str:

3205 return self.name

3206

3207 def _insert_col_impl(

3208 self,

3209 c: ColumnClause[Any],

3210 *,

3211 index: Optional[int] = None,

3212 ) -> None:

3213 existing = c.table

3214 if existing is not None and existing is not self:

3215 raise exc.ArgumentError(

3216 "column object '%s' already assigned to table '%s'"

3217 % (c.key, existing)

3218 )

3219 self._columns.add(c, index=index)

3220 c.table = self

3221

3222 def append_column(self, column: ColumnClause[Any]) -> None:

3223 """Append a :class:`.ColumnClause` to this :class:`.TableClause`."""

3224 self._insert_col_impl(column)

3225

3226 def insert_column(self, column: ColumnClause[Any], index: int) -> None:

3227 """Insert a :class:`.ColumnClause` to this :class:`.TableClause` at

3228 a specific position.

3229

3230 .. versionadded:: 2.1

3231

3232 """

3233 self._insert_col_impl(column, index=index)

3234

3235 @util.preload_module("sqlalchemy.sql.dml")

3236 def insert(self) -> util.preloaded.sql_dml.Insert:

3237 """Generate an :class:`_sql.Insert` construct against this

3238 :class:`_expression.TableClause`.

3239

3240 E.g.::

3241

3242 table.insert().values(name="foo")

3243

3244 See :func:`_expression.insert` for argument and usage information.

3245

3246 """

3247

3248 return util.preloaded.sql_dml.Insert(self)

3249

3250 @util.preload_module("sqlalchemy.sql.dml")

3251 def update(self) -> Update:

3252 """Generate an :func:`_expression.update` construct against this

3253 :class:`_expression.TableClause`.

3254

3255 E.g.::

3256

3257 table.update().where(table.c.id == 7).values(name="foo")

3258

3259 See :func:`_expression.update` for argument and usage information.

3260

3261 """

3262 return util.preloaded.sql_dml.Update(

3263 self,

3264 )

3265

3266 @util.preload_module("sqlalchemy.sql.dml")

3267 def delete(self) -> Delete:

3268 """Generate a :func:`_expression.delete` construct against this

3269 :class:`_expression.TableClause`.

3270

3271 E.g.::

3272

3273 table.delete().where(table.c.id == 7)

3274

3275 See :func:`_expression.delete` for argument and usage information.

3276

3277 """

3278 return util.preloaded.sql_dml.Delete(self)

3279

3280 @util.ro_non_memoized_property

3281 def _from_objects(self) -> List[FromClause]:

3282 return [self]

3283

3284

3285ForUpdateParameter = Union["ForUpdateArg", None, bool, Dict[str, Any]]

3286

3287

3288class ForUpdateArg(ClauseElement):

3289 _traverse_internals: _TraverseInternalsType = [

3290 ("of", InternalTraversal.dp_clauseelement_list),

3291 ("nowait", InternalTraversal.dp_boolean),

3292 ("read", InternalTraversal.dp_boolean),

3293 ("skip_locked", InternalTraversal.dp_boolean),

3294 ("key_share", InternalTraversal.dp_boolean),

3295 ]

3296

3297 of: Optional[Sequence[ClauseElement]]

3298 nowait: bool

3299 read: bool

3300 skip_locked: bool

3301

3302 @classmethod

3303 def _from_argument(

3304 cls, with_for_update: ForUpdateParameter

3305 ) -> Optional[ForUpdateArg]:

3306 if isinstance(with_for_update, ForUpdateArg):

3307 return with_for_update

3308 elif with_for_update in (None, False):

3309 return None

3310 elif with_for_update is True:

3311 return ForUpdateArg()

3312 else:

3313 return ForUpdateArg(**cast("Dict[str, Any]", with_for_update))

3314

3315 def __eq__(self, other: Any) -> bool:

3316 return (

3317 isinstance(other, ForUpdateArg)

3318 and other.nowait == self.nowait

3319 and other.read == self.read

3320 and other.skip_locked == self.skip_locked

3321 and other.key_share == self.key_share

3322 and other.of is self.of

3323 )

3324

3325 def __ne__(self, other: Any) -> bool:

3326 return not self.__eq__(other)

3327

3328 def __hash__(self) -> int:

3329 return id(self)

3330

3331 def __init__(

3332 self,

3333 *,

3334 nowait: bool = False,

3335 read: bool = False,

3336 of: Optional[_ForUpdateOfArgument] = None,

3337 skip_locked: bool = False,

3338 key_share: bool = False,

3339 ):

3340 """Represents arguments specified to

3341 :meth:`_expression.Select.for_update`.

3342

3343 """

3344

3345 self.nowait = nowait

3346 self.read = read

3347 self.skip_locked = skip_locked

3348 self.key_share = key_share

3349 if of is not None:

3350 self.of = [

3351 coercions.expect(roles.ColumnsClauseRole, elem)

3352 for elem in util.to_list(of)

3353 ]

3354 else:

3355 self.of = None

3356

3357

3358class Values(roles.InElementRole, HasCTE, Generative, LateralFromClause):

3359 """Represent a ``VALUES`` construct that can be used as a FROM element

3360 in a statement.

3361

3362 The :class:`_expression.Values` object is created from the

3363 :func:`_expression.values` function.

3364

3365 .. versionadded:: 1.4

3366

3367 """

3368

3369 __visit_name__ = "values"

3370

3371 _data: Tuple[Sequence[Tuple[Any, ...]], ...] = ()

3372 _column_args: Tuple[NamedColumn[Any], ...]

3373

3374 _unnamed: bool

3375 _traverse_internals: _TraverseInternalsType = [

3376 ("_column_args", InternalTraversal.dp_clauseelement_list),

3377 ("_data", InternalTraversal.dp_dml_multi_values),

3378 ("name", InternalTraversal.dp_string),

3379 ("literal_binds", InternalTraversal.dp_boolean),

3380 ] + HasCTE._has_ctes_traverse_internals

3381

3382 name_cte_columns = True

3383

3384 def __init__(

3385 self,

3386 *columns: _OnlyColumnArgument[Any],

3387 name: Optional[str] = None,

3388 literal_binds: bool = False,

3389 ):

3390 super().__init__()

3391 self._column_args = tuple(

3392 coercions.expect(roles.LabeledColumnExprRole, col)

3393 for col in columns

3394 )

3395

3396 if name is None:

3397 self._unnamed = True

3398 self.name = _anonymous_label.safe_construct(id(self), "anon")

3399 else:

3400 self._unnamed = False

3401 self.name = name

3402 self.literal_binds = literal_binds

3403 self.named_with_column = not self._unnamed

3404

3405 @property

3406 def _column_types(self) -> List[TypeEngine[Any]]:

3407 return [col.type for col in self._column_args]

3408

3409 @util.ro_non_memoized_property

3410 def _all_selected_columns(self) -> _SelectIterable:

3411 return self._column_args

3412

3413 @_generative

3414 def alias(self, name: Optional[str] = None, flat: bool = False) -> Self:

3415 """Return a new :class:`_expression.Values`

3416 construct that is a copy of this

3417 one with the given name.

3418

3419 This method is a VALUES-specific specialization of the

3420 :meth:`_expression.FromClause.alias` method.

3421

3422 .. seealso::

3423

3424 :ref:`tutorial_using_aliases`

3425

3426 :func:`_expression.alias`

3427

3428 """

3429 non_none_name: str

3430

3431 if name is None:

3432 non_none_name = _anonymous_label.safe_construct(id(self), "anon")

3433 else:

3434 non_none_name = name

3435

3436 self.name = non_none_name

3437 self.named_with_column = True

3438 self._unnamed = False

3439 return self

3440

3441 @_generative

3442 def lateral(self, name: Optional[str] = None) -> Self:

3443 """Return a new :class:`_expression.Values` with the lateral flag set,

3444 so that

3445 it renders as LATERAL.

3446

3447 .. seealso::

3448

3449 :func:`_expression.lateral`

3450

3451 """

3452 non_none_name: str

3453

3454 if name is None:

3455 non_none_name = self.name

3456 else:

3457 non_none_name = name

3458

3459 self._is_lateral = True

3460 self.name = non_none_name

3461 self._unnamed = False

3462 return self

3463

3464 @_generative

3465 def data(self, values: Sequence[Tuple[Any, ...]]) -> Self:

3466 """Return a new :class:`_expression.Values` construct,

3467 adding the given data to the data list.

3468

3469 E.g.::

3470

3471 my_values = my_values.data([(1, "value 1"), (2, "value2")])

3472

3473 :param values: a sequence (i.e. list) of tuples that map to the

3474 column expressions given in the :class:`_expression.Values`

3475 constructor.

3476

3477 """

3478

3479 self._data += (values,)

3480 return self

3481

3482 def scalar_values(self) -> ScalarValues:

3483 """Returns a scalar ``VALUES`` construct that can be used as a

3484 COLUMN element in a statement.

3485

3486 .. versionadded:: 2.0.0b4

3487

3488 """

3489 return ScalarValues(self._column_args, self._data, self.literal_binds)

3490

3491 def _populate_column_collection(

3492 self,

3493 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

3494 primary_key: ColumnSet,

3495 foreign_keys: Set[KeyedColumnElement[Any]],

3496 ) -> None:

3497 for c in self._column_args:

3498 if c.table is not None and c.table is not self:

3499 _, c = c._make_proxy(

3500 self, primary_key=primary_key, foreign_keys=foreign_keys

3501 )

3502 else:

3503 # if the column was used in other contexts, ensure

3504 # no memoizations of other FROM clauses.

3505 # see test_values.py -> test_auto_proxy_select_direct_col

3506 c._reset_memoizations()

3507 columns.add(c)

3508 c.table = self

3509

3510 @util.ro_non_memoized_property

3511 def _from_objects(self) -> List[FromClause]:

3512 return [self]

3513

3514

3515class ScalarValues(roles.InElementRole, GroupedElement, ColumnElement[Any]):

3516 """Represent a scalar ``VALUES`` construct that can be used as a

3517 COLUMN element in a statement.

3518

3519 The :class:`_expression.ScalarValues` object is created from the

3520 :meth:`_expression.Values.scalar_values` method. It's also

3521 automatically generated when a :class:`_expression.Values` is used in

3522 an ``IN`` or ``NOT IN`` condition.

3523

3524 .. versionadded:: 2.0.0b4

3525

3526 """

3527

3528 __visit_name__ = "scalar_values"

3529

3530 _traverse_internals: _TraverseInternalsType = [

3531 ("_column_args", InternalTraversal.dp_clauseelement_list),

3532 ("_data", InternalTraversal.dp_dml_multi_values),

3533 ("literal_binds", InternalTraversal.dp_boolean),

3534 ]

3535

3536 def __init__(

3537 self,

3538 columns: Sequence[NamedColumn[Any]],

3539 data: Tuple[Sequence[Tuple[Any, ...]], ...],

3540 literal_binds: bool,

3541 ):

3542 super().__init__()

3543 self._column_args = columns

3544 self._data = data

3545 self.literal_binds = literal_binds

3546

3547 @property

3548 def _column_types(self) -> List[TypeEngine[Any]]:

3549 return [col.type for col in self._column_args]

3550

3551 def __clause_element__(self) -> ScalarValues:

3552 return self

3553

3554 if TYPE_CHECKING:

3555

3556 def self_group(

3557 self, against: Optional[OperatorType] = None

3558 ) -> Self: ...

3559

3560 def _ungroup(self) -> ColumnElement[Any]: ...

3561

3562

3563class SelectBase(

3564 roles.SelectStatementRole,

3565 roles.DMLSelectRole,

3566 roles.CompoundElementRole,

3567 roles.InElementRole,

3568 HasCTE,

3569 SupportsCloneAnnotations,

3570 Selectable,

3571):

3572 """Base class for SELECT statements.

3573

3574

3575 This includes :class:`_expression.Select`,

3576 :class:`_expression.CompoundSelect` and

3577 :class:`_expression.TextualSelect`.

3578

3579

3580 """

3581

3582 _is_select_base = True

3583 is_select = True

3584

3585 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3586

3587 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

3588 self._reset_memoizations()

3589

3590 @util.ro_non_memoized_property

3591 def selected_columns(

3592 self,

3593 ) -> ColumnCollection[str, ColumnElement[Any]]:

3594 """A :class:`_expression.ColumnCollection`

3595 representing the columns that

3596 this SELECT statement or similar construct returns in its result set.

3597

3598 This collection differs from the :attr:`_expression.FromClause.columns`

3599 collection of a :class:`_expression.FromClause` in that the columns

3600 within this collection cannot be directly nested inside another SELECT

3601 statement; a subquery must be applied first which provides for the

3602 necessary parenthesization required by SQL.

3603

3604 .. note::

3605

3606 The :attr:`_sql.SelectBase.selected_columns` collection does not

3607 include expressions established in the columns clause using the

3608 :func:`_sql.text` construct; these are silently omitted from the

3609 collection. To use plain textual column expressions inside of a

3610 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

3611 construct.

3612

3613 .. seealso::

3614

3615 :attr:`_sql.Select.selected_columns`

3616

3617 .. versionadded:: 1.4

3618

3619 """

3620 raise NotImplementedError()

3621

3622 def _generate_fromclause_column_proxies(

3623 self,

3624 subquery: FromClause,

3625 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

3626 primary_key: ColumnSet,

3627 foreign_keys: Set[KeyedColumnElement[Any]],

3628 *,

3629 proxy_compound_columns: Optional[

3630 Iterable[Sequence[ColumnElement[Any]]]

3631 ] = None,

3632 ) -> None:

3633 raise NotImplementedError()

3634

3635 @util.ro_non_memoized_property

3636 def _all_selected_columns(self) -> _SelectIterable:

3637 """A sequence of expressions that correspond to what is rendered

3638 in the columns clause, including :class:`_sql.TextClause`

3639 constructs.

3640

3641 .. versionadded:: 1.4.12

3642

3643 .. seealso::

3644

3645 :attr:`_sql.SelectBase.exported_columns`

3646

3647 """

3648 raise NotImplementedError()

3649

3650 @property

3651 def exported_columns(

3652 self,

3653 ) -> ReadOnlyColumnCollection[str, ColumnElement[Any]]:

3654 """A :class:`_expression.ColumnCollection`

3655 that represents the "exported"

3656 columns of this :class:`_expression.Selectable`, not including

3657 :class:`_sql.TextClause` constructs.

3658

3659 The "exported" columns for a :class:`_expression.SelectBase`

3660 object are synonymous

3661 with the :attr:`_expression.SelectBase.selected_columns` collection.

3662

3663 .. versionadded:: 1.4

3664

3665 .. seealso::

3666

3667 :attr:`_expression.Select.exported_columns`

3668

3669 :attr:`_expression.Selectable.exported_columns`

3670

3671 :attr:`_expression.FromClause.exported_columns`

3672

3673

3674 """

3675 return self.selected_columns._as_readonly()

3676

3677 def get_label_style(self) -> SelectLabelStyle:

3678 """

3679 Retrieve the current label style.

3680

3681 Implemented by subclasses.

3682

3683 """

3684 raise NotImplementedError()

3685

3686 def set_label_style(self, style: SelectLabelStyle) -> Self:

3687 """Return a new selectable with the specified label style.

3688

3689 Implemented by subclasses.

3690

3691 """

3692

3693 raise NotImplementedError()

3694

3695 def _scalar_type(self) -> TypeEngine[Any]:

3696 raise NotImplementedError()

3697

3698 @util.deprecated(

3699 "1.4",

3700 "The :meth:`_expression.SelectBase.as_scalar` "

3701 "method is deprecated and will be "

3702 "removed in a future release. Please refer to "

3703 ":meth:`_expression.SelectBase.scalar_subquery`.",

3704 )

3705 def as_scalar(self) -> ScalarSelect[Any]:

3706 return self.scalar_subquery()

3707

3708 def exists(self) -> Exists:

3709 """Return an :class:`_sql.Exists` representation of this selectable,

3710 which can be used as a column expression.

3711

3712 The returned object is an instance of :class:`_sql.Exists`.

3713

3714 .. seealso::

3715

3716 :func:`_sql.exists`

3717

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

3719

3720 .. versionadded:: 1.4

3721

3722 """

3723 return Exists(self)

3724

3725 def scalar_subquery(self) -> ScalarSelect[Any]:

3726 """Return a 'scalar' representation of this selectable, which can be

3727 used as a column expression.

3728

3729 The returned object is an instance of :class:`_sql.ScalarSelect`.

3730

3731 Typically, a select statement which has only one column in its columns

3732 clause is eligible to be used as a scalar expression. The scalar

3733 subquery can then be used in the WHERE clause or columns clause of

3734 an enclosing SELECT.

3735

3736 Note that the scalar subquery differentiates from the FROM-level

3737 subquery that can be produced using the

3738 :meth:`_expression.SelectBase.subquery`

3739 method.

3740

3741 .. versionchanged:: 1.4 - the ``.as_scalar()`` method was renamed to

3742 :meth:`_expression.SelectBase.scalar_subquery`.

3743

3744 .. seealso::

3745

3746 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

3747

3748 """

3749 if self._label_style is not LABEL_STYLE_NONE:

3750 self = self.set_label_style(LABEL_STYLE_NONE)

3751

3752 return ScalarSelect(self)

3753

3754 def label(self, name: Optional[str]) -> Label[Any]:

3755 """Return a 'scalar' representation of this selectable, embedded as a

3756 subquery with a label.

3757

3758 .. seealso::

3759

3760 :meth:`_expression.SelectBase.scalar_subquery`.

3761

3762 """

3763 return self.scalar_subquery().label(name)

3764

3765 def lateral(self, name: Optional[str] = None) -> LateralFromClause:

3766 """Return a LATERAL alias of this :class:`_expression.Selectable`.

3767

3768 The return value is the :class:`_expression.Lateral` construct also

3769 provided by the top-level :func:`_expression.lateral` function.

3770

3771 .. seealso::

3772

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

3774

3775 """

3776 return Lateral._factory(self, name)

3777

3778 def subquery(self, name: Optional[str] = None) -> Subquery:

3779 """Return a subquery of this :class:`_expression.SelectBase`.

3780

3781 A subquery is from a SQL perspective a parenthesized, named

3782 construct that can be placed in the FROM clause of another

3783 SELECT statement.

3784

3785 Given a SELECT statement such as::

3786

3787 stmt = select(table.c.id, table.c.name)

3788

3789 The above statement might look like:

3790

3791 .. sourcecode:: sql

3792

3793 SELECT table.id, table.name FROM table

3794

3795 The subquery form by itself renders the same way, however when

3796 embedded into the FROM clause of another SELECT statement, it becomes

3797 a named sub-element::

3798

3799 subq = stmt.subquery()

3800 new_stmt = select(subq)

3801

3802 The above renders as:

3803

3804 .. sourcecode:: sql

3805

3806 SELECT anon_1.id, anon_1.name

3807 FROM (SELECT table.id, table.name FROM table) AS anon_1

3808

3809 Historically, :meth:`_expression.SelectBase.subquery`

3810 is equivalent to calling

3811 the :meth:`_expression.FromClause.alias`

3812 method on a FROM object; however,

3813 as a :class:`_expression.SelectBase`

3814 object is not directly FROM object,

3815 the :meth:`_expression.SelectBase.subquery`

3816 method provides clearer semantics.

3817

3818 .. versionadded:: 1.4

3819

3820 """

3821

3822 return Subquery._construct(

3823 self._ensure_disambiguated_names(), name=name

3824 )

3825

3826 @util.preload_module("sqlalchemy.sql.ddl")

3827 def into(

3828 self,

3829 target: str,

3830 *,

3831 metadata: Optional["MetaData"] = None,

3832 schema: Optional[str] = None,

3833 temporary: bool = False,

3834 if_not_exists: bool = False,

3835 ) -> CreateTableAs:

3836 """Create a :class:`_schema.CreateTableAs` construct from this SELECT.

3837

3838 This method provides a convenient way to create a ``CREATE TABLE ...

3839 AS`` statement from a SELECT, as well as compound SELECTs like UNION.

3840 The new table will be created with columns matching the SELECT list.

3841

3842 Supported on all included backends, the construct emits

3843 ``CREATE TABLE...AS`` for all backends except SQL Server, which instead

3844 emits a ``SELECT..INTO`` statement.

3845

3846 e.g.::

3847

3848 from sqlalchemy import select

3849

3850 # Create a new table from a SELECT

3851 stmt = (

3852 select(users.c.id, users.c.name)

3853 .where(users.c.status == "active")

3854 .into("active_users")

3855 )

3856

3857 with engine.begin() as conn:

3858 conn.execute(stmt)

3859

3860 # With optional flags

3861 stmt = (

3862 select(users.c.id)

3863 .where(users.c.status == "inactive")

3864 .into("inactive_users", schema="analytics", if_not_exists=True)

3865 )

3866

3867 .. versionadded:: 2.1

3868

3869 :param target: Name of the table to create as a string. Must be

3870 unqualified; use the ``schema`` parameter for qualification.

3871

3872 :param metadata: :class:`_schema.MetaData`, optional

3873 If provided, the :class:`_schema.Table` object available via the

3874 :attr:`.CreateTableAs.table` attribute will be associated with this

3875 :class:`.MetaData`. Otherwise, a new, empty :class:`.MetaData`

3876 is created.

3877

3878 :param schema: Optional schema name for the new table.

3879

3880 :param temporary: If True, create a temporary table where supported

3881

3882 :param if_not_exists: If True, add IF NOT EXISTS clause where supported

3883

3884 :return: A :class:`_schema.CreateTableAs` construct.

3885

3886 .. seealso::

3887

3888 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3889

3890 :class:`_schema.CreateTableAs`

3891

3892 """

3893 sql_ddl = util.preloaded.sql_ddl

3894

3895 return sql_ddl.CreateTableAs(

3896 self,

3897 target,

3898 metadata=metadata,

3899 schema=schema,

3900 temporary=temporary,

3901 if_not_exists=if_not_exists,

3902 )

3903

3904 def _ensure_disambiguated_names(self) -> Self:

3905 """Ensure that the names generated by this selectbase will be

3906 disambiguated in some way, if possible.

3907

3908 """

3909

3910 raise NotImplementedError()

3911

3912 def alias(

3913 self, name: Optional[str] = None, flat: bool = False

3914 ) -> Subquery:

3915 """Return a named subquery against this

3916 :class:`_expression.SelectBase`.

3917

3918 For a :class:`_expression.SelectBase` (as opposed to a

3919 :class:`_expression.FromClause`),

3920 this returns a :class:`.Subquery` object which behaves mostly the

3921 same as the :class:`_expression.Alias` object that is used with a

3922 :class:`_expression.FromClause`.

3923

3924 .. versionchanged:: 1.4 The :meth:`_expression.SelectBase.alias`

3925 method is now

3926 a synonym for the :meth:`_expression.SelectBase.subquery` method.

3927

3928 """

3929 return self.subquery(name=name)

3930

3931

3932_SB = TypeVar("_SB", bound=SelectBase)

3933

3934

3935class SelectStatementGrouping(GroupedElement, SelectBase, Generic[_SB]):

3936 """Represent a grouping of a :class:`_expression.SelectBase`.

3937

3938 This differs from :class:`.Subquery` in that we are still

3939 an "inner" SELECT statement, this is strictly for grouping inside of

3940 compound selects.

3941

3942 """

3943

3944 __visit_name__ = "select_statement_grouping"

3945 _traverse_internals: _TraverseInternalsType = [

3946 ("element", InternalTraversal.dp_clauseelement)

3947 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3948

3949 _is_select_container = True

3950

3951 element: _SB

3952

3953 def __init__(self, element: _SB) -> None:

3954 self.element = cast(

3955 _SB, coercions.expect(roles.SelectStatementRole, element)

3956 )

3957

3958 def _ensure_disambiguated_names(self) -> SelectStatementGrouping[_SB]:

3959 new_element = self.element._ensure_disambiguated_names()

3960 if new_element is not self.element:

3961 return SelectStatementGrouping(new_element)

3962 else:

3963 return self

3964

3965 def get_label_style(self) -> SelectLabelStyle:

3966 return self.element.get_label_style()

3967

3968 def set_label_style(

3969 self, label_style: SelectLabelStyle

3970 ) -> SelectStatementGrouping[_SB]:

3971 return SelectStatementGrouping(

3972 self.element.set_label_style(label_style)

3973 )

3974

3975 @property

3976 def select_statement(self) -> _SB:

3977 return self.element

3978

3979 def self_group(self, against: Optional[OperatorType] = None) -> Self:

3980 return self

3981

3982 if TYPE_CHECKING:

3983

3984 def _ungroup(self) -> _SB: ...

3985

3986 # def _generate_columns_plus_names(

3987 # self, anon_for_dupe_key: bool

3988 # ) -> List[Tuple[str, str, str, ColumnElement[Any], bool]]:

3989 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3990

3991 def _generate_fromclause_column_proxies(

3992 self,

3993 subquery: FromClause,

3994 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

3995 primary_key: ColumnSet,

3996 foreign_keys: Set[KeyedColumnElement[Any]],

3997 *,

3998 proxy_compound_columns: Optional[

3999 Iterable[Sequence[ColumnElement[Any]]]

4000 ] = None,

4001 ) -> None:

4002 self.element._generate_fromclause_column_proxies(

4003 subquery,

4004 columns,

4005 proxy_compound_columns=proxy_compound_columns,

4006 primary_key=primary_key,

4007 foreign_keys=foreign_keys,

4008 )

4009

4010 @util.ro_non_memoized_property

4011 def _all_selected_columns(self) -> _SelectIterable:

4012 return self.element._all_selected_columns

4013

4014 @util.ro_non_memoized_property

4015 def selected_columns(self) -> ColumnCollection[str, ColumnElement[Any]]:

4016 """A :class:`_expression.ColumnCollection`

4017 representing the columns that

4018 the embedded SELECT statement returns in its result set, not including

4019 :class:`_sql.TextClause` constructs.

4020

4021 .. versionadded:: 1.4

4022

4023 .. seealso::

4024

4025 :attr:`_sql.Select.selected_columns`

4026

4027 """

4028 return self.element.selected_columns

4029

4030 @util.ro_non_memoized_property

4031 def _from_objects(self) -> List[FromClause]:

4032 return self.element._from_objects

4033

4034 def _scalar_type(self) -> TypeEngine[Any]:

4035 return self.element._scalar_type()

4036

4037 def add_cte(self, *ctes: CTE, nest_here: bool = False) -> Self:

4038 # SelectStatementGrouping not generative: has no attribute '_generate'

4039 raise NotImplementedError

4040

4041

4042class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

4043 """Base class for SELECT statements where additional elements can be

4044 added.

4045

4046 This serves as the base for :class:`_expression.Select` and

4047 :class:`_expression.CompoundSelect`

4048 where elements such as ORDER BY, GROUP BY can be added and column

4049 rendering can be controlled. Compare to

4050 :class:`_expression.TextualSelect`, which,

4051 while it subclasses :class:`_expression.SelectBase`

4052 and is also a SELECT construct,

4053 represents a fixed textual string which cannot be altered at this level,

4054 only wrapped as a subquery.

4055

4056 """

4057

4058 _order_by_clauses: Tuple[ColumnElement[Any], ...] = ()

4059 _group_by_clauses: Tuple[ColumnElement[Any], ...] = ()

4060 _limit_clause: Optional[ColumnElement[Any]] = None

4061 _offset_clause: Optional[ColumnElement[Any]] = None

4062 _fetch_clause: Optional[ColumnElement[Any]] = None

4063 _fetch_clause_options: Optional[Dict[str, bool]] = None

4064 _for_update_arg: Optional[ForUpdateArg] = None

4065

4066 def __init__(self, _label_style: SelectLabelStyle = LABEL_STYLE_DEFAULT):

4067 self._label_style = _label_style

4068

4069 @_generative

4070 def with_for_update(

4071 self,

4072 *,

4073 nowait: bool = False,

4074 read: bool = False,

4075 of: Optional[_ForUpdateOfArgument] = None,

4076 skip_locked: bool = False,

4077 key_share: bool = False,

4078 ) -> Self:

4079 """Specify a ``FOR UPDATE`` clause for this

4080 :class:`_expression.GenerativeSelect`.

4081

4082 E.g.::

4083

4084 stmt = select(table).with_for_update(nowait=True)

4085

4086 On a database like PostgreSQL or Oracle Database, the above would

4087 render a statement like:

4088

4089 .. sourcecode:: sql

4090

4091 SELECT table.a, table.b FROM table FOR UPDATE NOWAIT

4092

4093 on other backends, the ``nowait`` option is ignored and instead

4094 would produce:

4095

4096 .. sourcecode:: sql

4097

4098 SELECT table.a, table.b FROM table FOR UPDATE

4099

4100 When called with no arguments, the statement will render with

4101 the suffix ``FOR UPDATE``. Additional arguments can then be

4102 provided which allow for common database-specific

4103 variants.

4104

4105 :param nowait: boolean; will render ``FOR UPDATE NOWAIT`` on Oracle

4106 Database and PostgreSQL dialects.

4107

4108 :param read: boolean; will render ``LOCK IN SHARE MODE`` on MySQL,

4109 ``FOR SHARE`` on PostgreSQL. On PostgreSQL, when combined with

4110 ``nowait``, will render ``FOR SHARE NOWAIT``.

4111

4112 :param of: SQL expression or list of SQL expression elements,

4113 (typically :class:`_schema.Column` objects or a compatible expression,

4114 for some backends may also be a table expression) which will render

4115 into a ``FOR UPDATE OF`` clause; supported by PostgreSQL, Oracle

4116 Database, some MySQL versions and possibly others. May render as a

4117 table or as a column depending on backend.

4118

4119 :param skip_locked: boolean, will render ``FOR UPDATE SKIP LOCKED`` on

4120 Oracle Database and PostgreSQL dialects or ``FOR SHARE SKIP LOCKED``

4121 if ``read=True`` is also specified.

4122

4123 :param key_share: boolean, will render ``FOR NO KEY UPDATE``,

4124 or if combined with ``read=True`` will render ``FOR KEY SHARE``,

4125 on the PostgreSQL dialect.

4126

4127 """

4128 self._for_update_arg = ForUpdateArg(

4129 nowait=nowait,

4130 read=read,

4131 of=of,

4132 skip_locked=skip_locked,

4133 key_share=key_share,

4134 )

4135 return self

4136

4137 def get_label_style(self) -> SelectLabelStyle:

4138 """

4139 Retrieve the current label style.

4140

4141 .. versionadded:: 1.4

4142

4143 """

4144 return self._label_style

4145

4146 def set_label_style(self, style: SelectLabelStyle) -> Self:

4147 """Return a new selectable with the specified label style.

4148

4149 There are three "label styles" available,

4150 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`,

4151 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`, and

4152 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`. The default style is

4153 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4154

4155 In modern SQLAlchemy, there is not generally a need to change the

4156 labeling style, as per-expression labels are more effectively used by

4157 making use of the :meth:`_sql.ColumnElement.label` method. In past

4158 versions, :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL` was used to

4159 disambiguate same-named columns from different tables, aliases, or

4160 subqueries; the newer :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY` now

4161 applies labels only to names that conflict with an existing name so

4162 that the impact of this labeling is minimal.

4163

4164 The rationale for disambiguation is mostly so that all column

4165 expressions are available from a given :attr:`_sql.FromClause.c`

4166 collection when a subquery is created.

4167

4168 .. versionadded:: 1.4 - the

4169 :meth:`_sql.GenerativeSelect.set_label_style` method replaces the

4170 previous combination of ``.apply_labels()``, ``.with_labels()`` and

4171 ``use_labels=True`` methods and/or parameters.

4172

4173 .. seealso::

4174

4175 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4176

4177 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4178

4179 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4180

4181 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4182

4183 """

4184 if self._label_style is not style:

4185 self = self._generate()

4186 self._label_style = style

4187 return self

4188

4189 @property

4190 def _group_by_clause(self) -> ClauseList:

4191 """ClauseList access to group_by_clauses for legacy dialects"""

4192 return ClauseList._construct_raw(

4193 operators.comma_op, self._group_by_clauses

4194 )

4195

4196 @property

4197 def _order_by_clause(self) -> ClauseList:

4198 """ClauseList access to order_by_clauses for legacy dialects"""

4199 return ClauseList._construct_raw(

4200 operators.comma_op, self._order_by_clauses

4201 )

4202

4203 def _offset_or_limit_clause(

4204 self,

4205 element: _LimitOffsetType,

4206 name: Optional[str] = None,

4207 type_: Optional[_TypeEngineArgument[int]] = None,

4208 ) -> ColumnElement[Any]:

4209 """Convert the given value to an "offset or limit" clause.

4210

4211 This handles incoming integers and converts to an expression; if

4212 an expression is already given, it is passed through.

4213

4214 """

4215 return coercions.expect(

4216 roles.LimitOffsetRole, element, name=name, type_=type_

4217 )

4218

4219 @overload

4220 def _offset_or_limit_clause_asint(

4221 self, clause: ColumnElement[Any], attrname: str

4222 ) -> NoReturn: ...

4223

4224 @overload

4225 def _offset_or_limit_clause_asint(

4226 self, clause: Optional[_OffsetLimitParam], attrname: str

4227 ) -> Optional[int]: ...

4228

4229 def _offset_or_limit_clause_asint(

4230 self, clause: Optional[ColumnElement[Any]], attrname: str

4231 ) -> Union[NoReturn, Optional[int]]:

4232 """Convert the "offset or limit" clause of a select construct to an

4233 integer.

4234

4235 This is only possible if the value is stored as a simple bound

4236 parameter. Otherwise, a compilation error is raised.

4237

4238 """

4239 if clause is None:

4240 return None

4241 try:

4242 value = clause._limit_offset_value

4243 except AttributeError as err:

4244 raise exc.CompileError(

4245 "This SELECT structure does not use a simple "

4246 "integer value for %s" % attrname

4247 ) from err

4248 else:

4249 return util.asint(value)

4250

4251 @property

4252 def _limit(self) -> Optional[int]:

4253 """Get an integer value for the limit. This should only be used

4254 by code that cannot support a limit as a BindParameter or

4255 other custom clause as it will throw an exception if the limit

4256 isn't currently set to an integer.

4257

4258 """

4259 return self._offset_or_limit_clause_asint(self._limit_clause, "limit")

4260

4261 def _simple_int_clause(self, clause: ClauseElement) -> bool:

4262 """True if the clause is a simple integer, False

4263 if it is not present or is a SQL expression.

4264 """

4265 return isinstance(clause, _OffsetLimitParam)

4266

4267 @property

4268 def _offset(self) -> Optional[int]:

4269 """Get an integer value for the offset. This should only be used

4270 by code that cannot support an offset as a BindParameter or

4271 other custom clause as it will throw an exception if the

4272 offset isn't currently set to an integer.

4273

4274 """

4275 return self._offset_or_limit_clause_asint(

4276 self._offset_clause, "offset"

4277 )

4278

4279 @property

4280 def _has_row_limiting_clause(self) -> bool:

4281 return (

4282 self._limit_clause is not None

4283 or self._offset_clause is not None

4284 or self._fetch_clause is not None

4285 )

4286

4287 @_generative

4288 def limit(self, limit: _LimitOffsetType) -> Self:

4289 """Return a new selectable with the given LIMIT criterion

4290 applied.

4291

4292 This is a numerical value which usually renders as a ``LIMIT``

4293 expression in the resulting select. Backends that don't

4294 support ``LIMIT`` will attempt to provide similar

4295 functionality.

4296

4297 .. note::

4298

4299 The :meth:`_sql.GenerativeSelect.limit` method will replace

4300 any clause applied with :meth:`_sql.GenerativeSelect.fetch`.

4301

4302 :param limit: an integer LIMIT parameter, or a SQL expression

4303 that provides an integer result. Pass ``None`` to reset it.

4304

4305 .. seealso::

4306

4307 :meth:`_sql.GenerativeSelect.fetch`

4308

4309 :meth:`_sql.GenerativeSelect.offset`

4310

4311 """

4312

4313 self._fetch_clause = self._fetch_clause_options = None

4314 self._limit_clause = self._offset_or_limit_clause(limit)

4315 return self

4316

4317 @_generative

4318 def fetch(

4319 self,

4320 count: _LimitOffsetType,

4321 with_ties: bool = False,

4322 percent: bool = False,

4323 **dialect_kw: Any,

4324 ) -> Self:

4325 r"""Return a new selectable with the given FETCH FIRST criterion

4326 applied.

4327

4328 This is a numeric value which usually renders as ``FETCH {FIRST | NEXT}

4329 [ count ] {ROW | ROWS} {ONLY | WITH TIES}`` expression in the resulting

4330 select. This functionality is is currently implemented for Oracle

4331 Database, PostgreSQL, MSSQL.

4332

4333 Use :meth:`_sql.GenerativeSelect.offset` to specify the offset.

4334

4335 .. note::

4336

4337 The :meth:`_sql.GenerativeSelect.fetch` method will replace

4338 any clause applied with :meth:`_sql.GenerativeSelect.limit`.

4339

4340 .. versionadded:: 1.4

4341

4342 :param count: an integer COUNT parameter, or a SQL expression

4343 that provides an integer result. When ``percent=True`` this will

4344 represent the percentage of rows to return, not the absolute value.

4345 Pass ``None`` to reset it.

4346

4347 :param with_ties: When ``True``, the WITH TIES option is used

4348 to return any additional rows that tie for the last place in the

4349 result set according to the ``ORDER BY`` clause. The

4350 ``ORDER BY`` may be mandatory in this case. Defaults to ``False``

4351

4352 :param percent: When ``True``, ``count`` represents the percentage

4353 of the total number of selected rows to return. Defaults to ``False``

4354

4355 :param \**dialect_kw: Additional dialect-specific keyword arguments

4356 may be accepted by dialects.

4357

4358 .. versionadded:: 2.0.41

4359

4360 .. seealso::

4361

4362 :meth:`_sql.GenerativeSelect.limit`

4363

4364 :meth:`_sql.GenerativeSelect.offset`

4365

4366 """

4367 self._validate_dialect_kwargs(dialect_kw)

4368 self._limit_clause = None

4369 if count is None:

4370 self._fetch_clause = self._fetch_clause_options = None

4371 else:

4372 self._fetch_clause = self._offset_or_limit_clause(count)

4373 self._fetch_clause_options = {

4374 "with_ties": with_ties,

4375 "percent": percent,

4376 }

4377 return self

4378

4379 @_generative

4380 def offset(self, offset: _LimitOffsetType) -> Self:

4381 """Return a new selectable with the given OFFSET criterion

4382 applied.

4383

4384

4385 This is a numeric value which usually renders as an ``OFFSET``

4386 expression in the resulting select. Backends that don't

4387 support ``OFFSET`` will attempt to provide similar

4388 functionality.

4389

4390 :param offset: an integer OFFSET parameter, or a SQL expression

4391 that provides an integer result. Pass ``None`` to reset it.

4392

4393 .. seealso::

4394

4395 :meth:`_sql.GenerativeSelect.limit`

4396

4397 :meth:`_sql.GenerativeSelect.fetch`

4398

4399 """

4400

4401 self._offset_clause = self._offset_or_limit_clause(offset)

4402 return self

4403

4404 @_generative

4405 @util.preload_module("sqlalchemy.sql.util")

4406 def slice(

4407 self,

4408 start: int,

4409 stop: int,

4410 ) -> Self:

4411 """Apply LIMIT / OFFSET to this statement based on a slice.

4412

4413 The start and stop indices behave like the argument to Python's

4414 built-in :func:`range` function. This method provides an

4415 alternative to using ``LIMIT``/``OFFSET`` to get a slice of the

4416 query.

4417

4418 For example, ::

4419

4420 stmt = select(User).order_by(User.id).slice(1, 3)

4421

4422 renders as

4423

4424 .. sourcecode:: sql

4425

4426 SELECT users.id AS users_id,

4427 users.name AS users_name

4428 FROM users ORDER BY users.id

4429 LIMIT ? OFFSET ?

4430 (2, 1)

4431

4432 .. note::

4433

4434 The :meth:`_sql.GenerativeSelect.slice` method will replace

4435 any clause applied with :meth:`_sql.GenerativeSelect.fetch`.

4436

4437 .. versionadded:: 1.4 Added the :meth:`_sql.GenerativeSelect.slice`

4438 method generalized from the ORM.

4439

4440 .. seealso::

4441

4442 :meth:`_sql.GenerativeSelect.limit`

4443

4444 :meth:`_sql.GenerativeSelect.offset`

4445

4446 :meth:`_sql.GenerativeSelect.fetch`

4447

4448 """

4449 sql_util = util.preloaded.sql_util

4450 self._fetch_clause = self._fetch_clause_options = None

4451 self._limit_clause, self._offset_clause = sql_util._make_slice(

4452 self._limit_clause, self._offset_clause, start, stop

4453 )

4454 return self

4455

4456 @_generative

4457 def order_by(

4458 self,

4459 __first: Union[

4460 Literal[None, _NoArg.NO_ARG],

4461 _ColumnExpressionOrStrLabelArgument[Any],

4462 ] = _NoArg.NO_ARG,

4463 /,

4464 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4465 ) -> Self:

4466 r"""Return a new selectable with the given list of ORDER BY

4467 criteria applied.

4468

4469 e.g.::

4470

4471 stmt = select(table).order_by(table.c.id, table.c.name)

4472

4473 Calling this method multiple times is equivalent to calling it once

4474 with all the clauses concatenated. All existing ORDER BY criteria may

4475 be cancelled by passing ``None`` by itself. New ORDER BY criteria may

4476 then be added by invoking :meth:`_orm.Query.order_by` again, e.g.::

4477

4478 # will erase all ORDER BY and ORDER BY new_col alone

4479 stmt = stmt.order_by(None).order_by(new_col)

4480

4481 :param \*clauses: a series of :class:`_expression.ColumnElement`

4482 constructs which will be used to generate an ORDER BY clause.

4483

4484 Alternatively, an individual entry may also be the string name of a

4485 label located elsewhere in the columns clause of the statement which

4486 will be matched and rendered in a backend-specific way based on

4487 context; see :ref:`tutorial_order_by_label` for background on string

4488 label matching in ORDER BY and GROUP BY expressions.

4489

4490 .. seealso::

4491

4492 :ref:`tutorial_order_by` - in the :ref:`unified_tutorial`

4493

4494 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

4495

4496 """

4497

4498 if not clauses and __first is None:

4499 self._order_by_clauses = ()

4500 elif __first is not _NoArg.NO_ARG:

4501 self._order_by_clauses += tuple(

4502 coercions.expect(

4503 roles.OrderByRole, clause, apply_propagate_attrs=self

4504 )

4505 for clause in (__first,) + clauses

4506 )

4507 return self

4508

4509 @_generative

4510 def group_by(

4511 self,

4512 __first: Union[

4513 Literal[None, _NoArg.NO_ARG],

4514 _ColumnExpressionOrStrLabelArgument[Any],

4515 ] = _NoArg.NO_ARG,

4516 /,

4517 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4518 ) -> Self:

4519 r"""Return a new selectable with the given list of GROUP BY

4520 criterion applied.

4521

4522 All existing GROUP BY settings can be suppressed by passing ``None``.

4523

4524 e.g.::

4525

4526 stmt = select(table.c.name, func.max(table.c.stat)).group_by(table.c.name)

4527

4528 :param \*clauses: a series of :class:`_expression.ColumnElement`

4529 constructs which will be used to generate an GROUP BY clause.

4530

4531 Alternatively, an individual entry may also be the string name of a

4532 label located elsewhere in the columns clause of the statement which

4533 will be matched and rendered in a backend-specific way based on

4534 context; see :ref:`tutorial_order_by_label` for background on string

4535 label matching in ORDER BY and GROUP BY expressions.

4536

4537 .. seealso::

4538

4539 :ref:`tutorial_group_by_w_aggregates` - in the

4540 :ref:`unified_tutorial`

4541

4542 :ref:`tutorial_order_by_label` - in the :ref:`unified_tutorial`

4543

4544 """ # noqa: E501

4545

4546 if not clauses and __first is None:

4547 self._group_by_clauses = ()

4548 elif __first is not _NoArg.NO_ARG:

4549 self._group_by_clauses += tuple(

4550 coercions.expect(

4551 roles.GroupByRole, clause, apply_propagate_attrs=self

4552 )

4553 for clause in (__first,) + clauses

4554 )

4555 return self

4556

4557

4558@CompileState.plugin_for("default", "compound_select")

4559class CompoundSelectState(CompileState):

4560 @util.memoized_property

4561 def _label_resolve_dict(

4562 self,

4563 ) -> Tuple[

4564 Dict[str, ColumnElement[Any]],

4565 Dict[str, ColumnElement[Any]],

4566 Dict[str, ColumnElement[Any]],

4567 ]:

4568 # TODO: this is hacky and slow

4569 hacky_subquery = self.statement.subquery()

4570 hacky_subquery.named_with_column = False

4571 d = {c.key: c for c in hacky_subquery.c}

4572 return d, d, d

4573

4574

4575class _CompoundSelectKeyword(Enum):

4576 UNION = "UNION"

4577 UNION_ALL = "UNION ALL"

4578 EXCEPT = "EXCEPT"

4579 EXCEPT_ALL = "EXCEPT ALL"

4580 INTERSECT = "INTERSECT"

4581 INTERSECT_ALL = "INTERSECT ALL"

4582

4583

4584class CompoundSelect(

4585 HasCompileState, GenerativeSelect, TypedReturnsRows[Unpack[_Ts]]

4586):

4587 """Forms the basis of ``UNION``, ``UNION ALL``, and other

4588 SELECT-based set operations.

4589

4590

4591 .. seealso::

4592

4593 :func:`_expression.union`

4594

4595 :func:`_expression.union_all`

4596

4597 :func:`_expression.intersect`

4598

4599 :func:`_expression.intersect_all`

4600

4601 :func:`_expression.except`

4602

4603 :func:`_expression.except_all`

4604

4605 """

4606

4607 __visit_name__ = "compound_select"

4608

4609 _traverse_internals: _TraverseInternalsType = (

4610 [

4611 ("selects", InternalTraversal.dp_clauseelement_list),

4612 ("_limit_clause", InternalTraversal.dp_clauseelement),

4613 ("_offset_clause", InternalTraversal.dp_clauseelement),

4614 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4615 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4616 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4617 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4618 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4619 ("keyword", InternalTraversal.dp_string),

4620 ]

4621 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4622 + HasCTE._has_ctes_traverse_internals

4623 + DialectKWArgs._dialect_kwargs_traverse_internals

4624 + ExecutableStatement._executable_traverse_internals

4625 )

4626

4627 selects: List[SelectBase]

4628

4629 _is_from_container = True

4630 _auto_correlate = False

4631

4632 def __init__(

4633 self,

4634 keyword: _CompoundSelectKeyword,

4635 *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]],

4636 ):

4637 self.keyword = keyword

4638 self.selects = [

4639 coercions.expect(

4640 roles.CompoundElementRole, s, apply_propagate_attrs=self

4641 ).self_group(against=self)

4642 for s in selects

4643 ]

4644

4645 GenerativeSelect.__init__(self)

4646

4647 @classmethod

4648 def _create_union(

4649 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4650 ) -> CompoundSelect[Unpack[_Ts]]:

4651 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4652

4653 @classmethod

4654 def _create_union_all(

4655 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4656 ) -> CompoundSelect[Unpack[_Ts]]:

4657 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4658

4659 @classmethod

4660 def _create_except(

4661 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4662 ) -> CompoundSelect[Unpack[_Ts]]:

4663 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4664

4665 @classmethod

4666 def _create_except_all(

4667 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4668 ) -> CompoundSelect[Unpack[_Ts]]:

4669 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4670

4671 @classmethod

4672 def _create_intersect(

4673 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4674 ) -> CompoundSelect[Unpack[_Ts]]:

4675 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4676

4677 @classmethod

4678 def _create_intersect_all(

4679 cls, *selects: _SelectStatementForCompoundArgument[Unpack[_Ts]]

4680 ) -> CompoundSelect[Unpack[_Ts]]:

4681 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4682

4683 def _scalar_type(self) -> TypeEngine[Any]:

4684 return self.selects[0]._scalar_type()

4685

4686 def self_group(

4687 self, against: Optional[OperatorType] = None

4688 ) -> GroupedElement:

4689 return SelectStatementGrouping(self)

4690

4691 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

4692 for s in self.selects:

4693 if s.is_derived_from(fromclause):

4694 return True

4695 return False

4696

4697 def set_label_style(self, style: SelectLabelStyle) -> Self:

4698 if self._label_style is not style:

4699 self = self._generate()

4700 select_0 = self.selects[0].set_label_style(style)

4701 self.selects = [select_0] + self.selects[1:]

4702

4703 return self

4704

4705 def _ensure_disambiguated_names(self) -> Self:

4706 new_select = self.selects[0]._ensure_disambiguated_names()

4707 if new_select is not self.selects[0]:

4708 self = self._generate()

4709 self.selects = [new_select] + self.selects[1:]

4710

4711 return self

4712

4713 def _generate_fromclause_column_proxies(

4714 self,

4715 subquery: FromClause,

4716 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

4717 primary_key: ColumnSet,

4718 foreign_keys: Set[KeyedColumnElement[Any]],

4719 *,

4720 proxy_compound_columns: Optional[

4721 Iterable[Sequence[ColumnElement[Any]]]

4722 ] = None,

4723 ) -> None:

4724 # this is a slightly hacky thing - the union exports a

4725 # column that resembles just that of the *first* selectable.

4726 # to get at a "composite" column, particularly foreign keys,

4727 # you have to dig through the proxies collection which we

4728 # generate below.

4729 select_0 = self.selects[0]

4730

4731 if self._label_style is not LABEL_STYLE_DEFAULT:

4732 select_0 = select_0.set_label_style(self._label_style)

4733

4734 # hand-construct the "_proxies" collection to include all

4735 # derived columns place a 'weight' annotation corresponding

4736 # to how low in the list of select()s the column occurs, so

4737 # that the corresponding_column() operation can resolve

4738 # conflicts

4739 extra_col_iterator = zip(

4740 *[

4741 [

4742 c._annotate(dd)

4743 for c in stmt._all_selected_columns

4744 if is_column_element(c)

4745 ]

4746 for dd, stmt in [

4747 ({"weight": i + 1}, stmt)

4748 for i, stmt in enumerate(self.selects)

4749 ]

4750 ]

4751 )

4752

4753 # the incoming proxy_compound_columns can be present also if this is

4754 # a compound embedded in a compound. it's probably more appropriate

4755 # that we generate new weights local to this nested compound, though

4756 # i haven't tried to think what it means for compound nested in

4757 # compound

4758 select_0._generate_fromclause_column_proxies(

4759 subquery,

4760 columns,

4761 proxy_compound_columns=extra_col_iterator,

4762 primary_key=primary_key,

4763 foreign_keys=foreign_keys,

4764 )

4765

4766 def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:

4767 super()._refresh_for_new_column(column)

4768 for select in self.selects:

4769 select._refresh_for_new_column(column)

4770

4771 @util.ro_non_memoized_property

4772 def _all_selected_columns(self) -> _SelectIterable:

4773 return self.selects[0]._all_selected_columns

4774

4775 @util.ro_non_memoized_property

4776 def selected_columns(

4777 self,

4778 ) -> ColumnCollection[str, ColumnElement[Any]]:

4779 """A :class:`_expression.ColumnCollection`

4780 representing the columns that

4781 this SELECT statement or similar construct returns in its result set,

4782 not including :class:`_sql.TextClause` constructs.

4783

4784 For a :class:`_expression.CompoundSelect`, the

4785 :attr:`_expression.CompoundSelect.selected_columns`

4786 attribute returns the selected

4787 columns of the first SELECT statement contained within the series of

4788 statements within the set operation.

4789

4790 .. seealso::

4791

4792 :attr:`_sql.Select.selected_columns`

4793

4794 .. versionadded:: 1.4

4795

4796 """

4797 return self.selects[0].selected_columns

4798

4799

4800# backwards compat

4801for elem in _CompoundSelectKeyword:

4802 setattr(CompoundSelect, elem.name, elem)

4803

4804

4805@CompileState.plugin_for("default", "select")

4806class SelectState(util.MemoizedSlots, CompileState):

4807 __slots__ = (

4808 "from_clauses",

4809 "froms",

4810 "columns_plus_names",

4811 "_label_resolve_dict",

4812 )

4813

4814 if TYPE_CHECKING:

4815 default_select_compile_options: CacheableOptions

4816 else:

4817

4818 class default_select_compile_options(CacheableOptions):

4819 _cache_key_traversal = []

4820

4821 if TYPE_CHECKING:

4822

4823 @classmethod

4824 def get_plugin_class(

4825 cls, statement: Executable

4826 ) -> Type[SelectState]: ...

4827

4828 def __init__(

4829 self,

4830 statement: Select[Unpack[TupleAny]],

4831 compiler: SQLCompiler,

4832 **kw: Any,

4833 ):

4834 self.statement = statement

4835 self.from_clauses = statement._from_obj

4836

4837 for memoized_entities in statement._memoized_select_entities:

4838 self._setup_joins(

4839 memoized_entities._setup_joins, memoized_entities._raw_columns

4840 )

4841

4842 if statement._setup_joins:

4843 self._setup_joins(statement._setup_joins, statement._raw_columns)

4844

4845 self.froms = self._get_froms(statement)

4846

4847 self.columns_plus_names = statement._generate_columns_plus_names(True)

4848

4849 @classmethod

4850 def _plugin_not_implemented(cls) -> NoReturn:

4851 raise NotImplementedError(

4852 "The default SELECT construct without plugins does not "

4853 "implement this method."

4854 )

4855

4856 @classmethod

4857 def get_column_descriptions(

4858 cls, statement: Select[Unpack[TupleAny]]

4859 ) -> List[Dict[str, Any]]:

4860 return [

4861 {

4862 "name": name,

4863 "type": element.type,

4864 "expr": element,

4865 }

4866 for _, name, _, element, _ in (

4867 statement._generate_columns_plus_names(False)

4868 )

4869 ]

4870

4871 @classmethod

4872 def from_statement(

4873 cls,

4874 statement: Select[Unpack[TupleAny]],

4875 from_statement: roles.ReturnsRowsRole,

4876 ) -> ExecutableReturnsRows:

4877 cls._plugin_not_implemented()

4878

4879 @classmethod

4880 def get_columns_clause_froms(

4881 cls, statement: Select[Unpack[TupleAny]]

4882 ) -> List[FromClause]:

4883 return cls._normalize_froms(

4884 itertools.chain.from_iterable(

4885 element._from_objects for element in statement._raw_columns

4886 )

4887 )

4888

4889 @classmethod

4890 def _column_naming_convention(

4891 cls, label_style: SelectLabelStyle

4892 ) -> _LabelConventionCallable:

4893 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4894

4895 dedupe = label_style is not LABEL_STYLE_NONE

4896

4897 pa = prefix_anon_map()

4898 names = set()

4899

4900 def go(

4901 c: Union[ColumnElement[Any], AbstractTextClause],

4902 col_name: Optional[str] = None,

4903 ) -> Optional[str]:

4904 if is_text_clause(c):

4905 return None

4906 elif TYPE_CHECKING:

4907 assert is_column_element(c)

4908

4909 if not dedupe:

4910 name = c._proxy_key

4911 if name is None:

4912 name = "_no_label"

4913 return name

4914

4915 name = c._tq_key_label if table_qualified else c._proxy_key

4916

4917 if name is None:

4918 name = "_no_label"

4919 if name in names:

4920 return c._anon_label(name) % pa

4921 else:

4922 names.add(name)

4923 return name

4924

4925 elif name in names:

4926 return (

4927 c._anon_tq_key_label % pa

4928 if table_qualified

4929 else c._anon_key_label % pa

4930 )

4931 else:

4932 names.add(name)

4933 return name

4934

4935 return go

4936

4937 def _get_froms(

4938 self, statement: Select[Unpack[TupleAny]]

4939 ) -> List[FromClause]:

4940 ambiguous_table_name_map: _AmbiguousTableNameMap

4941 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4942

4943 return self._normalize_froms(

4944 itertools.chain(

4945 self.from_clauses,

4946 itertools.chain.from_iterable(

4947 [

4948 element._from_objects

4949 for element in statement._raw_columns

4950 ]

4951 ),

4952 itertools.chain.from_iterable(

4953 [

4954 element._from_objects

4955 for element in statement._where_criteria

4956 ]

4957 ),

4958 ),

4959 check_statement=statement,

4960 ambiguous_table_name_map=ambiguous_table_name_map,

4961 )

4962

4963 @classmethod

4964 def _normalize_froms(

4965 cls,

4966 iterable_of_froms: Iterable[FromClause],

4967 check_statement: Optional[Select[Unpack[TupleAny]]] = None,

4968 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4969 ) -> List[FromClause]:

4970 """given an iterable of things to select FROM, reduce them to what

4971 would actually render in the FROM clause of a SELECT.

4972

4973 This does the job of checking for JOINs, tables, etc. that are in fact

4974 overlapping due to cloning, adaption, present in overlapping joins,

4975 etc.

4976

4977 """

4978 seen: Set[FromClause] = set()

4979 froms: List[FromClause] = []

4980

4981 for item in iterable_of_froms:

4982 if is_subquery(item) and item.element is check_statement:

4983 raise exc.InvalidRequestError(

4984 "select() construct refers to itself as a FROM"

4985 )

4986

4987 if not seen.intersection(item._cloned_set):

4988 froms.append(item)

4989 seen.update(item._cloned_set)

4990

4991 if froms:

4992 toremove = set(

4993 itertools.chain.from_iterable(

4994 [_expand_cloned(f._hide_froms) for f in froms]

4995 )

4996 )

4997 if toremove:

4998 # filter out to FROM clauses not in the list,

4999 # using a list to maintain ordering

5000 froms = [f for f in froms if f not in toremove]

5001

5002 if ambiguous_table_name_map is not None:

5003 ambiguous_table_name_map.update(

5004 (

5005 fr.name,

5006 _anonymous_label.safe_construct(

5007 hash(fr.name), fr.name

5008 ),

5009 )

5010 for item in froms

5011 for fr in item._from_objects

5012 if is_table(fr)

5013 and fr.schema

5014 and fr.name not in ambiguous_table_name_map

5015 )

5016

5017 return froms

5018

5019 def _get_display_froms(

5020 self,

5021 explicit_correlate_froms: Optional[Sequence[FromClause]] = None,

5022 implicit_correlate_froms: Optional[Sequence[FromClause]] = None,

5023 ) -> List[FromClause]:

5024 """Return the full list of 'from' clauses to be displayed.

5025

5026 Takes into account a set of existing froms which may be

5027 rendered in the FROM clause of enclosing selects; this Select

5028 may want to leave those absent if it is automatically

5029 correlating.

5030

5031 """

5032

5033 froms = self.froms

5034

5035 if self.statement._correlate:

5036 to_correlate = self.statement._correlate

5037 if to_correlate:

5038 froms = [

5039 f

5040 for f in froms

5041 if f

5042 not in _cloned_intersection(

5043 _cloned_intersection(

5044 froms, explicit_correlate_froms or ()

5045 ),

5046 to_correlate,

5047 )

5048 ]

5049

5050 if self.statement._correlate_except is not None:

5051 froms = [

5052 f

5053 for f in froms

5054 if f

5055 not in _cloned_difference(

5056 _cloned_intersection(

5057 froms, explicit_correlate_froms or ()

5058 ),

5059 self.statement._correlate_except,

5060 )

5061 ]

5062

5063 if (

5064 self.statement._auto_correlate

5065 and implicit_correlate_froms

5066 and len(froms) > 1

5067 ):

5068 froms = [

5069 f

5070 for f in froms

5071 if f

5072 not in _cloned_intersection(froms, implicit_correlate_froms)

5073 ]

5074

5075 if not len(froms):

5076 raise exc.InvalidRequestError(

5077 "Select statement '%r"

5078 "' returned no FROM clauses "

5079 "due to auto-correlation; "

5080 "specify correlate(<tables>) "

5081 "to control correlation "

5082 "manually." % self.statement

5083 )

5084

5085 return froms

5086

5087 def _memoized_attr__label_resolve_dict(

5088 self,

5089 ) -> Tuple[

5090 Dict[str, ColumnElement[Any]],

5091 Dict[str, ColumnElement[Any]],

5092 Dict[str, ColumnElement[Any]],

5093 ]:

5094 with_cols: Dict[str, ColumnElement[Any]] = {

5095 c._tq_label or c.key: c

5096 for c in self.statement._all_selected_columns

5097 if c._allow_label_resolve

5098 }

5099 only_froms: Dict[str, ColumnElement[Any]] = {

5100 c.key: c # type: ignore

5101 for c in _select_iterables(self.froms)

5102 if c._allow_label_resolve

5103 }

5104 only_cols: Dict[str, ColumnElement[Any]] = with_cols.copy()

5105 for key, value in only_froms.items():

5106 with_cols.setdefault(key, value)

5107

5108 return with_cols, only_froms, only_cols

5109

5110 @classmethod

5111 def _get_filter_by_entities(

5112 cls, statement: Select[Unpack[TupleAny]]

5113 ) -> Collection[

5114 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5115 ]:

5116 """Return all entities to search for filter_by() attributes.

5117

5118 This includes:

5119

5120 * All joined entities from _setup_joins

5121 * Memoized entities from previous operations (e.g.,

5122 before with_only_columns)

5123 * Explicit FROM objects from _from_obj

5124 * Entities inferred from _raw_columns

5125

5126 .. versionadded:: 2.1

5127

5128 """

5129 entities: set[

5130 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5131 ]

5132

5133 entities = set(

5134 join_element[0] for join_element in statement._setup_joins

5135 )

5136

5137 for memoized in statement._memoized_select_entities:

5138 entities.update(

5139 join_element[0] for join_element in memoized._setup_joins

5140 )

5141

5142 entities.update(statement._from_obj)

5143

5144 for col in statement._raw_columns:

5145 entities.update(col._from_objects)

5146

5147 return entities

5148

5149 @classmethod

5150 def all_selected_columns(

5151 cls, statement: Select[Unpack[TupleAny]]

5152 ) -> _SelectIterable:

5153 return [c for c in _select_iterables(statement._raw_columns)]

5154

5155 def _setup_joins(

5156 self,

5157 args: Tuple[_SetupJoinsElement, ...],

5158 raw_columns: List[_ColumnsClauseElement],

5159 ) -> None:

5160 for right, onclause, left, flags in args:

5161 if TYPE_CHECKING:

5162 if onclause is not None:

5163 assert isinstance(onclause, ColumnElement)

5164

5165 explicit_left = left

5166 isouter = flags["isouter"]

5167 full = flags["full"]

5168

5169 if left is None:

5170 (

5171 left,

5172 replace_from_obj_index,

5173 ) = self._join_determine_implicit_left_side(

5174 raw_columns, left, right, onclause

5175 )

5176 else:

5177 (replace_from_obj_index) = self._join_place_explicit_left_side(

5178 left

5179 )

5180

5181 # these assertions can be made here, as if the right/onclause

5182 # contained ORM elements, the select() statement would have been

5183 # upgraded to an ORM select, and this method would not be called;

5184 # orm.context.ORMSelectCompileState._join() would be

5185 # used instead.

5186 if TYPE_CHECKING:

5187 assert isinstance(right, FromClause)

5188 if onclause is not None:

5189 assert isinstance(onclause, ColumnElement)

5190

5191 if replace_from_obj_index is not None:

5192 # splice into an existing element in the

5193 # self._from_obj list

5194 left_clause = self.from_clauses[replace_from_obj_index]

5195

5196 if explicit_left is not None and onclause is None:

5197 onclause = Join._join_condition(explicit_left, right)

5198

5199 self.from_clauses = (

5200 self.from_clauses[:replace_from_obj_index]

5201 + (

5202 Join(

5203 left_clause,

5204 right,

5205 onclause,

5206 isouter=isouter,

5207 full=full,

5208 ),

5209 )

5210 + self.from_clauses[replace_from_obj_index + 1 :]

5211 )

5212 else:

5213 assert left is not None

5214 self.from_clauses = self.from_clauses + (

5215 Join(left, right, onclause, isouter=isouter, full=full),

5216 )

5217

5218 @util.preload_module("sqlalchemy.sql.util")

5219 def _join_determine_implicit_left_side(

5220 self,

5221 raw_columns: List[_ColumnsClauseElement],

5222 left: Optional[FromClause],

5223 right: _JoinTargetElement,

5224 onclause: Optional[ColumnElement[Any]],

5225 ) -> Tuple[Optional[FromClause], Optional[int]]:

5226 """When join conditions don't express the left side explicitly,

5227 determine if an existing FROM or entity in this query

5228 can serve as the left hand side.

5229

5230 """

5231

5232 sql_util = util.preloaded.sql_util

5233

5234 replace_from_obj_index: Optional[int] = None

5235

5236 from_clauses = self.from_clauses

5237

5238 if from_clauses:

5239 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5240 from_clauses, right, onclause

5241 )

5242

5243 if len(indexes) == 1:

5244 replace_from_obj_index = indexes[0]

5245 left = from_clauses[replace_from_obj_index]

5246 else:

5247 potential = {}

5248 statement = self.statement

5249

5250 for from_clause in itertools.chain(

5251 itertools.chain.from_iterable(

5252 [element._from_objects for element in raw_columns]

5253 ),

5254 itertools.chain.from_iterable(

5255 [

5256 element._from_objects

5257 for element in statement._where_criteria

5258 ]

5259 ),

5260 ):

5261 potential[from_clause] = ()

5262

5263 all_clauses = list(potential.keys())

5264 indexes = sql_util.find_left_clause_to_join_from(

5265 all_clauses, right, onclause

5266 )

5267

5268 if len(indexes) == 1:

5269 left = all_clauses[indexes[0]]

5270

5271 if len(indexes) > 1:

5272 raise exc.InvalidRequestError(

5273 "Can't determine which FROM clause to join "

5274 "from, there are multiple FROMS which can "

5275 "join to this entity. Please use the .select_from() "

5276 "method to establish an explicit left side, as well as "

5277 "providing an explicit ON clause if not present already to "

5278 "help resolve the ambiguity."

5279 )

5280 elif not indexes:

5281 raise exc.InvalidRequestError(

5282 "Don't know how to join to %r. "

5283 "Please use the .select_from() "

5284 "method to establish an explicit left side, as well as "

5285 "providing an explicit ON clause if not present already to "

5286 "help resolve the ambiguity." % (right,)

5287 )

5288 return left, replace_from_obj_index

5289

5290 @util.preload_module("sqlalchemy.sql.util")

5291 def _join_place_explicit_left_side(

5292 self, left: FromClause

5293 ) -> Optional[int]:

5294 replace_from_obj_index: Optional[int] = None

5295

5296 sql_util = util.preloaded.sql_util

5297

5298 from_clauses = list(self.statement._iterate_from_elements())

5299

5300 if from_clauses:

5301 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5302 self.from_clauses, left

5303 )

5304 else:

5305 indexes = []

5306

5307 if len(indexes) > 1:

5308 raise exc.InvalidRequestError(

5309 "Can't identify which entity in which to assign the "

5310 "left side of this join. Please use a more specific "

5311 "ON clause."

5312 )

5313

5314 # have an index, means the left side is already present in

5315 # an existing FROM in the self._from_obj tuple

5316 if indexes:

5317 replace_from_obj_index = indexes[0]

5318

5319 # no index, means we need to add a new element to the

5320 # self._from_obj tuple

5321

5322 return replace_from_obj_index

5323

5324

5325class _SelectFromElements:

5326 __slots__ = ()

5327

5328 _raw_columns: List[_ColumnsClauseElement]

5329 _where_criteria: Tuple[ColumnElement[Any], ...]

5330 _from_obj: Tuple[FromClause, ...]

5331

5332 def _iterate_from_elements(self) -> Iterator[FromClause]:

5333 # note this does not include elements

5334 # in _setup_joins

5335

5336 seen = set()

5337 for element in self._raw_columns:

5338 for fr in element._from_objects:

5339 if fr in seen:

5340 continue

5341 seen.add(fr)

5342 yield fr

5343 for element in self._where_criteria:

5344 for fr in element._from_objects:

5345 if fr in seen:

5346 continue

5347 seen.add(fr)

5348 yield fr

5349 for element in self._from_obj:

5350 if element in seen:

5351 continue

5352 seen.add(element)

5353 yield element

5354

5355

5356class _MemoizedSelectEntities(

5357 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5358):

5359 """represents partial state from a Select object, for the case

5360 where Select.columns() has redefined the set of columns/entities the

5361 statement will be SELECTing from. This object represents

5362 the entities from the SELECT before that transformation was applied,

5363 so that transformations that were made in terms of the SELECT at that

5364 time, such as join() as well as options(), can access the correct context.

5365

5366 In previous SQLAlchemy versions, this wasn't needed because these

5367 constructs calculated everything up front, like when you called join()

5368 or options(), it did everything to figure out how that would translate

5369 into specific SQL constructs that would be ready to send directly to the

5370 SQL compiler when needed. But as of

5371 1.4, all of that stuff is done in the compilation phase, during the

5372 "compile state" portion of the process, so that the work can all be

5373 cached. So it needs to be able to resolve joins/options2 based on what

5374 the list of entities was when those methods were called.

5375

5376

5377 """

5378

5379 __visit_name__ = "memoized_select_entities"

5380

5381 _traverse_internals: _TraverseInternalsType = [

5382 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5383 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5384 ("_with_options", InternalTraversal.dp_executable_options),

5385 ]

5386

5387 _is_clone_of: Optional[ClauseElement]

5388 _raw_columns: List[_ColumnsClauseElement]

5389 _setup_joins: Tuple[_SetupJoinsElement, ...]

5390 _with_options: Tuple[ExecutableOption, ...]

5391

5392 _annotations = util.EMPTY_DICT

5393

5394 def _clone(self, **kw: Any) -> Self:

5395 c = self.__class__.__new__(self.__class__)

5396 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5397

5398 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5399 return c

5400

5401 @classmethod

5402 def _generate_for_statement(

5403 cls, select_stmt: Select[Unpack[TupleAny]]

5404 ) -> None:

5405 if select_stmt._setup_joins or select_stmt._with_options:

5406 self = _MemoizedSelectEntities()

5407 self._raw_columns = select_stmt._raw_columns

5408 self._setup_joins = select_stmt._setup_joins

5409 self._with_options = select_stmt._with_options

5410

5411 select_stmt._memoized_select_entities += (self,)

5412 select_stmt._raw_columns = []

5413 select_stmt._setup_joins = select_stmt._with_options = ()

5414

5415

5416class Select(

5417 HasPrefixes,

5418 HasSuffixes,

5419 HasHints,

5420 HasCompileState,

5421 HasSyntaxExtensions[

5422 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5423 ],

5424 _SelectFromElements,

5425 GenerativeSelect,

5426 TypedReturnsRows[Unpack[_Ts]],

5427):

5428 """Represents a ``SELECT`` statement.

5429

5430 The :class:`_sql.Select` object is normally constructed using the

5431 :func:`_sql.select` function. See that function for details.

5432

5433 Available extension points:

5434

5435 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5436 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5437 keyword (if any) and the list of columns.

5438 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5439 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5440

5441 .. seealso::

5442

5443 :func:`_sql.select`

5444

5445 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5446

5447 """

5448

5449 __visit_name__ = "select"

5450

5451 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5452 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5453

5454 _raw_columns: List[_ColumnsClauseElement]

5455

5456 _distinct: bool = False

5457 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5458 _correlate: Tuple[FromClause, ...] = ()

5459 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5460 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5461 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5462 _from_obj: Tuple[FromClause, ...] = ()

5463

5464 _position_map = util.immutabledict(

5465 {

5466 "post_select": "_post_select_clause",

5467 "pre_columns": "_pre_columns_clause",

5468 "post_criteria": "_post_criteria_clause",

5469 "post_body": "_post_body_clause",

5470 }

5471 )

5472

5473 _post_select_clause: Optional[ClauseElement] = None

5474 """extension point for a ClauseElement that will be compiled directly

5475 after the SELECT keyword.

5476

5477 .. versionadded:: 2.1

5478

5479 """

5480

5481 _pre_columns_clause: Optional[ClauseElement] = None

5482 """extension point for a ClauseElement that will be compiled directly

5483 before the "columns" clause; after DISTINCT (if present).

5484

5485 .. versionadded:: 2.1

5486

5487 """

5488

5489 _post_criteria_clause: Optional[ClauseElement] = None

5490 """extension point for a ClauseElement that will be compiled directly

5491 after "criteria", following the HAVING clause but before ORDER BY.

5492

5493 .. versionadded:: 2.1

5494

5495 """

5496

5497 _post_body_clause: Optional[ClauseElement] = None

5498 """extension point for a ClauseElement that will be compiled directly

5499 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5500 of the SELECT.

5501

5502 .. versionadded:: 2.1

5503

5504 """

5505

5506 _auto_correlate = True

5507 _is_select_statement = True

5508 _compile_options: CacheableOptions = (

5509 SelectState.default_select_compile_options

5510 )

5511

5512 _traverse_internals: _TraverseInternalsType = (

5513 [

5514 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5515 (

5516 "_memoized_select_entities",

5517 InternalTraversal.dp_memoized_select_entities,

5518 ),

5519 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5520 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5521 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5522 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5523 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5524 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5525 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5526 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5527 ("_limit_clause", InternalTraversal.dp_clauseelement),

5528 ("_offset_clause", InternalTraversal.dp_clauseelement),

5529 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5530 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5531 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5532 ("_distinct", InternalTraversal.dp_boolean),

5533 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5534 ("_label_style", InternalTraversal.dp_plain_obj),

5535 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5536 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5537 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5538 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5539 ]

5540 + HasCTE._has_ctes_traverse_internals

5541 + HasPrefixes._has_prefixes_traverse_internals

5542 + HasSuffixes._has_suffixes_traverse_internals

5543 + HasHints._has_hints_traverse_internals

5544 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5545 + ExecutableStatement._executable_traverse_internals

5546 + DialectKWArgs._dialect_kwargs_traverse_internals

5547 )

5548

5549 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5550 ("_compile_options", InternalTraversal.dp_has_cache_key)

5551 ]

5552

5553 _compile_state_factory: Type[SelectState]

5554

5555 @classmethod

5556 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5557 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5558

5559 Used internally to build up :class:`.Select` constructs with

5560 pre-established state.

5561

5562 """

5563

5564 stmt = Select.__new__(Select)

5565 stmt.__dict__.update(kw)

5566 return stmt

5567

5568 def __init__(

5569 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5570 ):

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

5572

5573 The public constructor for :class:`_expression.Select` is the

5574 :func:`_sql.select` function.

5575

5576 """

5577 self._raw_columns = [

5578 coercions.expect(

5579 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5580 )

5581 for ent in entities

5582 ]

5583 GenerativeSelect.__init__(self)

5584

5585 def _apply_syntax_extension_to_self(

5586 self, extension: SyntaxExtension

5587 ) -> None:

5588 extension.apply_to_select(self)

5589

5590 def _scalar_type(self) -> TypeEngine[Any]:

5591 if not self._raw_columns:

5592 return NULLTYPE

5593 elem = self._raw_columns[0]

5594 cols = list(elem._select_iterable)

5595 return cols[0].type

5596

5597 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5598 """A synonym for the :meth:`_sql.Select.where` method."""

5599

5600 return self.where(*criteria)

5601

5602 if TYPE_CHECKING:

5603

5604 @overload

5605 def scalar_subquery(

5606 self: Select[_MAYBE_ENTITY],

5607 ) -> ScalarSelect[Any]: ...

5608

5609 @overload

5610 def scalar_subquery(

5611 self: Select[_NOT_ENTITY],

5612 ) -> ScalarSelect[_NOT_ENTITY]: ...

5613

5614 @overload

5615 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5616

5617 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5618

5619 def filter_by(self, **kwargs: Any) -> Self:

5620 r"""Apply the given filtering criterion as a WHERE clause

5621 to this select, using keyword expressions.

5622

5623 E.g.::

5624

5625 stmt = select(User).filter_by(name="some name")

5626

5627 Multiple criteria may be specified as comma separated; the effect

5628 is that they will be joined together using the :func:`.and_`

5629 function::

5630

5631 stmt = select(User).filter_by(name="some name", id=5)

5632

5633 The keyword expressions are extracted by searching across **all

5634 entities present in the FROM clause** of the statement. If a

5635 keyword name is present in more than one entity,

5636 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5637 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5638 explicit column references::

5639

5640 # both User and Address have an 'id' attribute

5641 stmt = select(User).join(Address).filter_by(id=5)

5642 # raises AmbiguousColumnError

5643

5644 # use filter() with explicit qualification instead

5645 stmt = select(User).join(Address).filter(Address.id == 5)

5646

5647 .. versionchanged:: 2.1

5648

5649 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5650 entities rather than only searching the last joined entity or first

5651 FROM entity. This allows the method to locate attributes

5652 unambiguously across multiple joined tables. The new

5653 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5654 is present in more than one entity.

5655

5656 See :ref:`change_8601` for migration notes.

5657

5658 .. seealso::

5659

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

5661

5662 :meth:`_sql.Select.filter` - filter on SQL expressions.

5663

5664 :meth:`_sql.Select.where` - filter on SQL expressions.

5665

5666 """

5667 # Get all entities via plugin system

5668 all_entities = SelectState.get_plugin_class(

5669 self

5670 )._get_filter_by_entities(self)

5671

5672 clauses = [

5673 _entity_namespace_key_search_all(all_entities, key) == value

5674 for key, value in kwargs.items()

5675 ]

5676 return self.filter(*clauses)

5677

5678 @property

5679 def column_descriptions(self) -> Any:

5680 """Return a :term:`plugin-enabled` 'column descriptions' structure

5681 referring to the columns which are SELECTed by this statement.

5682

5683 This attribute is generally useful when using the ORM, as an

5684 extended structure which includes information about mapped

5685 entities is returned. The section :ref:`queryguide_inspection`

5686 contains more background.

5687

5688 For a Core-only statement, the structure returned by this accessor

5689 is derived from the same objects that are returned by the

5690 :attr:`.Select.selected_columns` accessor, formatted as a list of

5691 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5692 which indicate the column expressions to be selected::

5693

5694 >>> stmt = select(user_table)

5695 >>> stmt.column_descriptions

5696 [

5697 {

5698 'name': 'id',

5699 'type': Integer(),

5700 'expr': Column('id', Integer(), ...)},

5701 {

5702 'name': 'name',

5703 'type': String(length=30),

5704 'expr': Column('name', String(length=30), ...)}

5705 ]

5706

5707 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5708 attribute returns a structure for a Core-only set of entities,

5709 not just ORM-only entities.

5710

5711 .. seealso::

5712

5713 :attr:`.UpdateBase.entity_description` - entity information for

5714 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5715

5716 :ref:`queryguide_inspection` - ORM background

5717

5718 """

5719 meth = SelectState.get_plugin_class(self).get_column_descriptions

5720 return meth(self)

5721

5722 def from_statement(

5723 self, statement: roles.ReturnsRowsRole

5724 ) -> ExecutableReturnsRows:

5725 """Apply the columns which this :class:`.Select` would select

5726 onto another statement.

5727

5728 This operation is :term:`plugin-specific` and will raise a not

5729 supported exception if this :class:`_sql.Select` does not select from

5730 plugin-enabled entities.

5731

5732

5733 The statement is typically either a :func:`_expression.text` or

5734 :func:`_expression.select` construct, and should return the set of

5735 columns appropriate to the entities represented by this

5736 :class:`.Select`.

5737

5738 .. seealso::

5739

5740 :ref:`orm_queryguide_selecting_text` - usage examples in the

5741 ORM Querying Guide

5742

5743 """

5744 meth = SelectState.get_plugin_class(self).from_statement

5745 return meth(self, statement)

5746

5747 @_generative

5748 def join(

5749 self,

5750 target: _JoinTargetArgument,

5751 onclause: Optional[_OnClauseArgument] = None,

5752 *,

5753 isouter: bool = False,

5754 full: bool = False,

5755 ) -> Self:

5756 r"""Create a SQL JOIN against this :class:`_expression.Select`

5757 object's criterion

5758 and apply generatively, returning the newly resulting

5759 :class:`_expression.Select`.

5760

5761 E.g.::

5762

5763 stmt = select(user_table).join(

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

5765 )

5766

5767 The above statement generates SQL similar to:

5768

5769 .. sourcecode:: sql

5770

5771 SELECT user.id, user.name

5772 FROM user

5773 JOIN address ON user.id = address.user_id

5774

5775 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5776 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5777 source that is within the FROM clause of the existing SELECT,

5778 and a given target :class:`_sql.FromClause`, and then adds

5779 this :class:`_sql.Join` to the FROM clause of the newly generated

5780 SELECT statement. This is completely reworked from the behavior

5781 in 1.3, which would instead create a subquery of the entire

5782 :class:`_expression.Select` and then join that subquery to the

5783 target.

5784

5785 This is a **backwards incompatible change** as the previous behavior

5786 was mostly useless, producing an unnamed subquery rejected by

5787 most databases in any case. The new behavior is modeled after

5788 that of the very successful :meth:`_orm.Query.join` method in the

5789 ORM, in order to support the functionality of :class:`_orm.Query`

5790 being available by using a :class:`_sql.Select` object with an

5791 :class:`_orm.Session`.

5792

5793 See the notes for this change at :ref:`change_select_join`.

5794

5795

5796 :param target: target table to join towards

5797

5798 :param onclause: ON clause of the join. If omitted, an ON clause

5799 is generated automatically based on the :class:`_schema.ForeignKey`

5800 linkages between the two tables, if one can be unambiguously

5801 determined, otherwise an error is raised.

5802

5803 :param isouter: if True, generate LEFT OUTER join. Same as

5804 :meth:`_expression.Select.outerjoin`.

5805

5806 :param full: if True, generate FULL OUTER join.

5807

5808 .. seealso::

5809

5810 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5811

5812 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5813

5814 :meth:`_expression.Select.join_from`

5815

5816 :meth:`_expression.Select.outerjoin`

5817

5818 """ # noqa: E501

5819 join_target = coercions.expect(

5820 roles.JoinTargetRole, target, apply_propagate_attrs=self

5821 )

5822 if onclause is not None:

5823 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5824 else:

5825 onclause_element = None

5826

5827 self._setup_joins += (

5828 (

5829 join_target,

5830 onclause_element,

5831 None,

5832 {"isouter": isouter, "full": full},

5833 ),

5834 )

5835 return self

5836

5837 def outerjoin_from(

5838 self,

5839 from_: _FromClauseArgument,

5840 target: _JoinTargetArgument,

5841 onclause: Optional[_OnClauseArgument] = None,

5842 *,

5843 full: bool = False,

5844 ) -> Self:

5845 r"""Create a SQL LEFT OUTER JOIN against this

5846 :class:`_expression.Select` object's criterion and apply generatively,

5847 returning the newly resulting :class:`_expression.Select`.

5848

5849 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5850

5851 """

5852 return self.join_from(

5853 from_, target, onclause=onclause, isouter=True, full=full

5854 )

5855

5856 @_generative

5857 def join_from(

5858 self,

5859 from_: _FromClauseArgument,

5860 target: _JoinTargetArgument,

5861 onclause: Optional[_OnClauseArgument] = None,

5862 *,

5863 isouter: bool = False,

5864 full: bool = False,

5865 ) -> Self:

5866 r"""Create a SQL JOIN against this :class:`_expression.Select`

5867 object's criterion

5868 and apply generatively, returning the newly resulting

5869 :class:`_expression.Select`.

5870

5871 E.g.::

5872

5873 stmt = select(user_table, address_table).join_from(

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

5875 )

5876

5877 The above statement generates SQL similar to:

5878

5879 .. sourcecode:: sql

5880

5881 SELECT user.id, user.name, address.id, address.email, address.user_id

5882 FROM user JOIN address ON user.id = address.user_id

5883

5884 .. versionadded:: 1.4

5885

5886 :param from\_: the left side of the join, will be rendered in the

5887 FROM clause and is roughly equivalent to using the

5888 :meth:`.Select.select_from` method.

5889

5890 :param target: target table to join towards

5891

5892 :param onclause: ON clause of the join.

5893

5894 :param isouter: if True, generate LEFT OUTER join. Same as

5895 :meth:`_expression.Select.outerjoin`.

5896

5897 :param full: if True, generate FULL OUTER join.

5898

5899 .. seealso::

5900

5901 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5902

5903 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5904

5905 :meth:`_expression.Select.join`

5906

5907 """ # noqa: E501

5908

5909 # note the order of parsing from vs. target is important here, as we

5910 # are also deriving the source of the plugin (i.e. the subject mapper

5911 # in an ORM query) which should favor the "from_" over the "target"

5912

5913 from_ = coercions.expect(

5914 roles.FromClauseRole, from_, apply_propagate_attrs=self

5915 )

5916 join_target = coercions.expect(

5917 roles.JoinTargetRole, target, apply_propagate_attrs=self

5918 )

5919 if onclause is not None:

5920 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5921 else:

5922 onclause_element = None

5923

5924 self._setup_joins += (

5925 (

5926 join_target,

5927 onclause_element,

5928 from_,

5929 {"isouter": isouter, "full": full},

5930 ),

5931 )

5932 return self

5933

5934 def outerjoin(

5935 self,

5936 target: _JoinTargetArgument,

5937 onclause: Optional[_OnClauseArgument] = None,

5938 *,

5939 full: bool = False,

5940 ) -> Self:

5941 """Create a left outer join.

5942

5943 Parameters are the same as that of :meth:`_expression.Select.join`.

5944

5945 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5946 creates a :class:`_sql.Join` object between a

5947 :class:`_sql.FromClause` source that is within the FROM clause of

5948 the existing SELECT, and a given target :class:`_sql.FromClause`,

5949 and then adds this :class:`_sql.Join` to the FROM clause of the

5950 newly generated SELECT statement. This is completely reworked

5951 from the behavior in 1.3, which would instead create a subquery of

5952 the entire

5953 :class:`_expression.Select` and then join that subquery to the

5954 target.

5955

5956 This is a **backwards incompatible change** as the previous behavior

5957 was mostly useless, producing an unnamed subquery rejected by

5958 most databases in any case. The new behavior is modeled after

5959 that of the very successful :meth:`_orm.Query.join` method in the

5960 ORM, in order to support the functionality of :class:`_orm.Query`

5961 being available by using a :class:`_sql.Select` object with an

5962 :class:`_orm.Session`.

5963

5964 See the notes for this change at :ref:`change_select_join`.

5965

5966 .. seealso::

5967

5968 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5969

5970 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5971

5972 :meth:`_expression.Select.join`

5973

5974 """

5975 return self.join(target, onclause=onclause, isouter=True, full=full)

5976

5977 def get_final_froms(self) -> Sequence[FromClause]:

5978 """Compute the final displayed list of :class:`_expression.FromClause`

5979 elements.

5980

5981 This method will run through the full computation required to

5982 determine what FROM elements will be displayed in the resulting

5983 SELECT statement, including shadowing individual tables with

5984 JOIN objects, as well as full computation for ORM use cases including

5985 eager loading clauses.

5986

5987 For ORM use, this accessor returns the **post compilation**

5988 list of FROM objects; this collection will include elements such as

5989 eagerly loaded tables and joins. The objects will **not** be

5990 ORM enabled and not work as a replacement for the

5991 :meth:`_sql.Select.select_froms` collection; additionally, the

5992 method is not well performing for an ORM enabled statement as it

5993 will incur the full ORM construction process.

5994

5995 To retrieve the FROM list that's implied by the "columns" collection

5996 passed to the :class:`_sql.Select` originally, use the

5997 :attr:`_sql.Select.columns_clause_froms` accessor.

5998

5999 To select from an alternative set of columns while maintaining the

6000 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

6001 pass the

6002 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6003 parameter.

6004

6005 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

6006 method replaces the previous :attr:`_sql.Select.froms` accessor,

6007 which is deprecated.

6008

6009 .. seealso::

6010

6011 :attr:`_sql.Select.columns_clause_froms`

6012

6013 """

6014 compiler = self._default_compiler()

6015

6016 return self._compile_state_factory(self, compiler)._get_display_froms()

6017

6018 @property

6019 @util.deprecated(

6020 "1.4.23",

6021 "The :attr:`_expression.Select.froms` attribute is moved to "

6022 "the :meth:`_expression.Select.get_final_froms` method.",

6023 )

6024 def froms(self) -> Sequence[FromClause]:

6025 """Return the displayed list of :class:`_expression.FromClause`

6026 elements.

6027

6028

6029 """

6030 return self.get_final_froms()

6031

6032 @property

6033 def columns_clause_froms(self) -> List[FromClause]:

6034 """Return the set of :class:`_expression.FromClause` objects implied

6035 by the columns clause of this SELECT statement.

6036

6037 .. versionadded:: 1.4.23

6038

6039 .. seealso::

6040

6041 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6042 statement into account

6043

6044 :meth:`_sql.Select.with_only_columns` - makes use of this

6045 collection to set up a new FROM list

6046

6047 """

6048

6049 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6050 self

6051 )

6052

6053 @property

6054 def inner_columns(self) -> _SelectIterable:

6055 """An iterator of all :class:`_expression.ColumnElement`

6056 expressions which would

6057 be rendered into the columns clause of the resulting SELECT statement.

6058

6059 This method is legacy as of 1.4 and is superseded by the

6060 :attr:`_expression.Select.exported_columns` collection.

6061

6062 """

6063

6064 return iter(self._all_selected_columns)

6065

6066 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

6067 if fromclause is not None and self in fromclause._cloned_set:

6068 return True

6069

6070 for f in self._iterate_from_elements():

6071 if f.is_derived_from(fromclause):

6072 return True

6073 return False

6074

6075 def _copy_internals(

6076 self, clone: _CloneCallableType = _clone, **kw: Any

6077 ) -> None:

6078 # Select() object has been cloned and probably adapted by the

6079 # given clone function. Apply the cloning function to internal

6080 # objects

6081

6082 # 1. keep a dictionary of the froms we've cloned, and what

6083 # they've become. This allows us to ensure the same cloned from

6084 # is used when other items such as columns are "cloned"

6085

6086 all_the_froms = set(

6087 itertools.chain(

6088 _from_objects(*self._raw_columns),

6089 _from_objects(*self._where_criteria),

6090 _from_objects(*[elem[0] for elem in self._setup_joins]),

6091 )

6092 )

6093

6094 # do a clone for the froms we've gathered. what is important here

6095 # is if any of the things we are selecting from, like tables,

6096 # were converted into Join objects. if so, these need to be

6097 # added to _from_obj explicitly, because otherwise they won't be

6098 # part of the new state, as they don't associate themselves with

6099 # their columns.

6100 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6101

6102 # 2. copy FROM collections, adding in joins that we've created.

6103 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6104 add_froms = (

6105 {f for f in new_froms.values() if isinstance(f, Join)}

6106 .difference(all_the_froms)

6107 .difference(existing_from_obj)

6108 )

6109

6110 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6111

6112 # 3. clone everything else, making sure we use columns

6113 # corresponding to the froms we just made.

6114 def replace(

6115 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6116 **kw: Any,

6117 ) -> Optional[KeyedColumnElement[Any]]:

6118 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6119 newelem = new_froms[obj.table].corresponding_column(obj)

6120 return newelem

6121 return None

6122

6123 kw["replace"] = replace

6124

6125 # copy everything else. for table-ish things like correlate,

6126 # correlate_except, setup_joins, these clone normally. For

6127 # column-expression oriented things like raw_columns, where_criteria,

6128 # order by, we get this from the new froms.

6129 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6130

6131 self._reset_memoizations()

6132

6133 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6134 return itertools.chain(

6135 super().get_children(

6136 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6137 **kw,

6138 ),

6139 self._iterate_from_elements(),

6140 )

6141

6142 @_generative

6143 def add_columns(

6144 self, *entities: _ColumnsClauseArgument[Any]

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

6146 r"""Return a new :func:`_expression.select` construct with

6147 the given entities appended to its columns clause.

6148

6149 E.g.::

6150

6151 my_select = my_select.add_columns(table.c.new_column)

6152

6153 The original expressions in the columns clause remain in place.

6154 To replace the original expressions with new ones, see the method

6155 :meth:`_expression.Select.with_only_columns`.

6156

6157 :param \*entities: column, table, or other entity expressions to be

6158 added to the columns clause

6159

6160 .. seealso::

6161

6162 :meth:`_expression.Select.with_only_columns` - replaces existing

6163 expressions rather than appending.

6164

6165 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6166 example

6167

6168 """

6169 self._reset_memoizations()

6170

6171 self._raw_columns = self._raw_columns + [

6172 coercions.expect(

6173 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6174 )

6175 for column in entities

6176 ]

6177 return self

6178

6179 def _set_entities(

6180 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6181 ) -> None:

6182 self._raw_columns = [

6183 coercions.expect(

6184 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6185 )

6186 for ent in util.to_list(entities)

6187 ]

6188

6189 @util.deprecated(

6190 "1.4",

6191 "The :meth:`_expression.Select.column` method is deprecated and will "

6192 "be removed in a future release. Please use "

6193 ":meth:`_expression.Select.add_columns`",

6194 )

6195 def column(

6196 self, column: _ColumnsClauseArgument[Any]

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

6198 """Return a new :func:`_expression.select` construct with

6199 the given column expression added to its columns clause.

6200

6201 E.g.::

6202

6203 my_select = my_select.column(table.c.new_column)

6204

6205 See the documentation for

6206 :meth:`_expression.Select.with_only_columns`

6207 for guidelines on adding /replacing the columns of a

6208 :class:`_expression.Select` object.

6209

6210 """

6211 return self.add_columns(column)

6212

6213 @util.preload_module("sqlalchemy.sql.util")

6214 def reduce_columns(

6215 self, only_synonyms: bool = True

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

6217 """Return a new :func:`_expression.select` construct with redundantly

6218 named, equivalently-valued columns removed from the columns clause.

6219

6220 "Redundant" here means two columns where one refers to the

6221 other either based on foreign key, or via a simple equality

6222 comparison in the WHERE clause of the statement. The primary purpose

6223 of this method is to automatically construct a select statement

6224 with all uniquely-named columns, without the need to use

6225 table-qualified labels as

6226 :meth:`_expression.Select.set_label_style`

6227 does.

6228

6229 When columns are omitted based on foreign key, the referred-to

6230 column is the one that's kept. When columns are omitted based on

6231 WHERE equivalence, the first column in the columns clause is the

6232 one that's kept.

6233

6234 :param only_synonyms: when True, limit the removal of columns

6235 to those which have the same name as the equivalent. Otherwise,

6236 all columns that are equivalent to another are removed.

6237

6238 """

6239 woc: Select[Unpack[TupleAny]]

6240 woc = self.with_only_columns(

6241 *util.preloaded.sql_util.reduce_columns(

6242 self._all_selected_columns,

6243 only_synonyms=only_synonyms,

6244 *(self._where_criteria + self._from_obj),

6245 )

6246 )

6247 return woc

6248

6249 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6250

6251 # code within this block is **programmatically,

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

6253

6254 @overload

6255 def with_only_columns(

6256 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6257 ) -> Select[_T0]: ...

6258

6259 @overload

6260 def with_only_columns(

6261 self,

6262 __ent0: _TCCA[_T0],

6263 __ent1: _TCCA[_T1],

6264 /,

6265 *,

6266 maintain_column_froms: bool = ...,

6267 ) -> Select[_T0, _T1]: ...

6268

6269 @overload

6270 def with_only_columns(

6271 self,

6272 __ent0: _TCCA[_T0],

6273 __ent1: _TCCA[_T1],

6274 __ent2: _TCCA[_T2],

6275 /,

6276 *,

6277 maintain_column_froms: bool = ...,

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

6279

6280 @overload

6281 def with_only_columns(

6282 self,

6283 __ent0: _TCCA[_T0],

6284 __ent1: _TCCA[_T1],

6285 __ent2: _TCCA[_T2],

6286 __ent3: _TCCA[_T3],

6287 /,

6288 *,

6289 maintain_column_froms: bool = ...,

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

6291

6292 @overload

6293 def with_only_columns(

6294 self,

6295 __ent0: _TCCA[_T0],

6296 __ent1: _TCCA[_T1],

6297 __ent2: _TCCA[_T2],

6298 __ent3: _TCCA[_T3],

6299 __ent4: _TCCA[_T4],

6300 /,

6301 *,

6302 maintain_column_froms: bool = ...,

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

6304

6305 @overload

6306 def with_only_columns(

6307 self,

6308 __ent0: _TCCA[_T0],

6309 __ent1: _TCCA[_T1],

6310 __ent2: _TCCA[_T2],

6311 __ent3: _TCCA[_T3],

6312 __ent4: _TCCA[_T4],

6313 __ent5: _TCCA[_T5],

6314 /,

6315 *,

6316 maintain_column_froms: bool = ...,

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

6318

6319 @overload

6320 def with_only_columns(

6321 self,

6322 __ent0: _TCCA[_T0],

6323 __ent1: _TCCA[_T1],

6324 __ent2: _TCCA[_T2],

6325 __ent3: _TCCA[_T3],

6326 __ent4: _TCCA[_T4],

6327 __ent5: _TCCA[_T5],

6328 __ent6: _TCCA[_T6],

6329 /,

6330 *,

6331 maintain_column_froms: bool = ...,

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

6333

6334 @overload

6335 def with_only_columns(

6336 self,

6337 __ent0: _TCCA[_T0],

6338 __ent1: _TCCA[_T1],

6339 __ent2: _TCCA[_T2],

6340 __ent3: _TCCA[_T3],

6341 __ent4: _TCCA[_T4],

6342 __ent5: _TCCA[_T5],

6343 __ent6: _TCCA[_T6],

6344 __ent7: _TCCA[_T7],

6345 /,

6346 *entities: _ColumnsClauseArgument[Any],

6347 maintain_column_froms: bool = ...,

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

6349

6350 # END OVERLOADED FUNCTIONS self.with_only_columns

6351

6352 @overload

6353 def with_only_columns(

6354 self,

6355 *entities: _ColumnsClauseArgument[Any],

6356 maintain_column_froms: bool = False,

6357 **__kw: Any,

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

6359

6360 @_generative

6361 def with_only_columns(

6362 self,

6363 *entities: _ColumnsClauseArgument[Any],

6364 maintain_column_froms: bool = False,

6365 **__kw: Any,

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

6367 r"""Return a new :func:`_expression.select` construct with its columns

6368 clause replaced with the given entities.

6369

6370 By default, this method is exactly equivalent to as if the original

6371 :func:`_expression.select` had been called with the given entities.

6372 E.g. a statement::

6373

6374 s = select(table1.c.a, table1.c.b)

6375 s = s.with_only_columns(table1.c.b)

6376

6377 should be exactly equivalent to::

6378

6379 s = select(table1.c.b)

6380

6381 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6382 will also dynamically alter the FROM clause of the

6383 statement if it is not explicitly stated.

6384 To maintain the existing set of FROMs including those implied by the

6385 current columns clause, add the

6386 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6387 parameter::

6388

6389 s = select(table1.c.a, table2.c.b)

6390 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6391

6392 The above parameter performs a transfer of the effective FROMs

6393 in the columns collection to the :meth:`_sql.Select.select_from`

6394 method, as though the following were invoked::

6395

6396 s = select(table1.c.a, table2.c.b)

6397 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6398

6399 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6400 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6401 collection and performs an operation equivalent to the following::

6402

6403 s = select(table1.c.a, table2.c.b)

6404 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6405

6406 :param \*entities: column expressions to be used.

6407

6408 :param maintain_column_froms: boolean parameter that will ensure the

6409 FROM list implied from the current columns clause will be transferred

6410 to the :meth:`_sql.Select.select_from` method first.

6411

6412 .. versionadded:: 1.4.23

6413

6414 """ # noqa: E501

6415

6416 if __kw:

6417 raise _no_kw()

6418

6419 # memoizations should be cleared here as of

6420 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6421 # is the case for now.

6422 self._assert_no_memoizations()

6423

6424 if maintain_column_froms:

6425 self.select_from.non_generative( # type: ignore

6426 self, *self.columns_clause_froms

6427 )

6428

6429 # then memoize the FROMs etc.

6430 _MemoizedSelectEntities._generate_for_statement(self)

6431

6432 self._raw_columns = [

6433 coercions.expect(roles.ColumnsClauseRole, c)

6434 for c in coercions._expression_collection_was_a_list(

6435 "entities", "Select.with_only_columns", entities

6436 )

6437 ]

6438 return self

6439

6440 @property

6441 def whereclause(self) -> Optional[ColumnElement[Any]]:

6442 """Return the completed WHERE clause for this

6443 :class:`_expression.Select` statement.

6444

6445 This assembles the current collection of WHERE criteria

6446 into a single :class:`_expression.BooleanClauseList` construct.

6447

6448

6449 .. versionadded:: 1.4

6450

6451 """

6452

6453 return BooleanClauseList._construct_for_whereclause(

6454 self._where_criteria

6455 )

6456

6457 _whereclause = whereclause

6458

6459 @_generative

6460 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6461 """Return a new :func:`_expression.select` construct with

6462 the given expression added to

6463 its WHERE clause, joined to the existing clause via AND, if any.

6464

6465 """

6466

6467 assert isinstance(self._where_criteria, tuple)

6468

6469 for criterion in whereclause:

6470 where_criteria: ColumnElement[Any] = coercions.expect(

6471 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6472 )

6473 self._where_criteria += (where_criteria,)

6474 return self

6475

6476 @_generative

6477 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6478 """Return a new :func:`_expression.select` construct with

6479 the given expression added to

6480 its HAVING clause, joined to the existing clause via AND, if any.

6481

6482 """

6483

6484 for criterion in having:

6485 having_criteria = coercions.expect(

6486 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6487 )

6488 self._having_criteria += (having_criteria,)

6489 return self

6490

6491 @_generative

6492 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6493 r"""Return a new :func:`_expression.select` construct which

6494 will apply DISTINCT to the SELECT statement overall.

6495

6496 E.g.::

6497

6498 from sqlalchemy import select

6499

6500 stmt = select(users_table.c.id, users_table.c.name).distinct()

6501

6502 The above would produce an statement resembling:

6503

6504 .. sourcecode:: sql

6505

6506 SELECT DISTINCT user.id, user.name FROM user

6507

6508 The method also historically accepted an ``*expr`` parameter which

6509 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6510 This is now replaced using the :func:`_postgresql.distinct_on`

6511 extension::

6512

6513 from sqlalchemy import select

6514 from sqlalchemy.dialects.postgresql import distinct_on

6515

6516 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6517

6518 Using this parameter on other backends which don't support this

6519 syntax will raise an error.

6520

6521 :param \*expr: optional column expressions. When present,

6522 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6523 construct. A deprecation warning and/or :class:`_exc.CompileError`

6524 will be raised on other backends.

6525

6526 .. deprecated:: 2.1 Passing expressions to

6527 :meth:`_sql.Select.distinct` is deprecated, use

6528 :func:`_postgresql.distinct_on` instead.

6529

6530 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6531 and will raise :class:`_exc.CompileError` in a future version.

6532

6533 .. seealso::

6534

6535 :func:`_postgresql.distinct_on`

6536

6537 :meth:`.ext`

6538 """

6539 self._distinct = True

6540 if expr:

6541 warn_deprecated(

6542 "Passing expression to ``distinct`` to generate a "

6543 "DISTINCT ON clause is deprecated. Use instead the "

6544 "``postgresql.distinct_on`` function as an extension.",

6545 "2.1",

6546 )

6547 self._distinct_on = self._distinct_on + tuple(

6548 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6549 for e in expr

6550 )

6551 return self

6552

6553 @_generative

6554 def select_from(self, *froms: _FromClauseArgument) -> Self:

6555 r"""Return a new :func:`_expression.select` construct with the

6556 given FROM expression(s)

6557 merged into its list of FROM objects.

6558

6559 E.g.::

6560

6561 table1 = table("t1", column("a"))

6562 table2 = table("t2", column("b"))

6563 s = select(table1.c.a).select_from(

6564 table1.join(table2, table1.c.a == table2.c.b)

6565 )

6566

6567 The "from" list is a unique set on the identity of each element,

6568 so adding an already present :class:`_schema.Table`

6569 or other selectable

6570 will have no effect. Passing a :class:`_expression.Join` that refers

6571 to an already present :class:`_schema.Table`

6572 or other selectable will have

6573 the effect of concealing the presence of that selectable as

6574 an individual element in the rendered FROM list, instead

6575 rendering it into a JOIN clause.

6576

6577 While the typical purpose of :meth:`_expression.Select.select_from`

6578 is to

6579 replace the default, derived FROM clause with a join, it can

6580 also be called with individual table elements, multiple times

6581 if desired, in the case that the FROM clause cannot be fully

6582 derived from the columns clause::

6583

6584 select(func.count("*")).select_from(table1)

6585

6586 """

6587

6588 self._from_obj += tuple(

6589 coercions.expect(

6590 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6591 )

6592 for fromclause in froms

6593 )

6594 return self

6595

6596 @_generative

6597 def correlate(

6598 self,

6599 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6600 ) -> Self:

6601 r"""Return a new :class:`_expression.Select`

6602 which will correlate the given FROM

6603 clauses to that of an enclosing :class:`_expression.Select`.

6604

6605 Calling this method turns off the :class:`_expression.Select` object's

6606 default behavior of "auto-correlation". Normally, FROM elements

6607 which appear in a :class:`_expression.Select`

6608 that encloses this one via

6609 its :term:`WHERE clause`, ORDER BY, HAVING or

6610 :term:`columns clause` will be omitted from this

6611 :class:`_expression.Select`

6612 object's :term:`FROM clause`.

6613 Setting an explicit correlation collection using the

6614 :meth:`_expression.Select.correlate`

6615 method provides a fixed list of FROM objects

6616 that can potentially take place in this process.

6617

6618 When :meth:`_expression.Select.correlate`

6619 is used to apply specific FROM clauses

6620 for correlation, the FROM elements become candidates for

6621 correlation regardless of how deeply nested this

6622 :class:`_expression.Select`

6623 object is, relative to an enclosing :class:`_expression.Select`

6624 which refers to

6625 the same FROM object. This is in contrast to the behavior of

6626 "auto-correlation" which only correlates to an immediate enclosing

6627 :class:`_expression.Select`.

6628 Multi-level correlation ensures that the link

6629 between enclosed and enclosing :class:`_expression.Select`

6630 is always via

6631 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6632 correlation to take place.

6633

6634 If ``None`` is passed, the :class:`_expression.Select`

6635 object will correlate

6636 none of its FROM entries, and all will render unconditionally

6637 in the local FROM clause.

6638

6639 :param \*fromclauses: one or more :class:`.FromClause` or other

6640 FROM-compatible construct such as an ORM mapped entity to become part

6641 of the correlate collection; alternatively pass a single value

6642 ``None`` to remove all existing correlations.

6643

6644 .. seealso::

6645

6646 :meth:`_expression.Select.correlate_except`

6647

6648 :ref:`tutorial_scalar_subquery`

6649

6650 """

6651

6652 # tests failing when we try to change how these

6653 # arguments are passed

6654

6655 self._auto_correlate = False

6656 if not fromclauses or fromclauses[0] in {None, False}:

6657 if len(fromclauses) > 1:

6658 raise exc.ArgumentError(

6659 "additional FROM objects not accepted when "

6660 "passing None/False to correlate()"

6661 )

6662 self._correlate = ()

6663 else:

6664 self._correlate = self._correlate + tuple(

6665 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6666 )

6667 return self

6668

6669 @_generative

6670 def correlate_except(

6671 self,

6672 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6673 ) -> Self:

6674 r"""Return a new :class:`_expression.Select`

6675 which will omit the given FROM

6676 clauses from the auto-correlation process.

6677

6678 Calling :meth:`_expression.Select.correlate_except` turns off the

6679 :class:`_expression.Select` object's default behavior of

6680 "auto-correlation" for the given FROM elements. An element

6681 specified here will unconditionally appear in the FROM list, while

6682 all other FROM elements remain subject to normal auto-correlation

6683 behaviors.

6684

6685 If ``None`` is passed, or no arguments are passed,

6686 the :class:`_expression.Select` object will correlate all of its

6687 FROM entries.

6688

6689 :param \*fromclauses: a list of one or more

6690 :class:`_expression.FromClause`

6691 constructs, or other compatible constructs (i.e. ORM-mapped

6692 classes) to become part of the correlate-exception collection.

6693

6694 .. seealso::

6695

6696 :meth:`_expression.Select.correlate`

6697

6698 :ref:`tutorial_scalar_subquery`

6699

6700 """

6701

6702 self._auto_correlate = False

6703 if not fromclauses or fromclauses[0] in {None, False}:

6704 if len(fromclauses) > 1:

6705 raise exc.ArgumentError(

6706 "additional FROM objects not accepted when "

6707 "passing None/False to correlate_except()"

6708 )

6709 self._correlate_except = ()

6710 else:

6711 self._correlate_except = (self._correlate_except or ()) + tuple(

6712 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6713 )

6714

6715 return self

6716

6717 @HasMemoized_ro_memoized_attribute

6718 def selected_columns(

6719 self,

6720 ) -> ColumnCollection[str, ColumnElement[Any]]:

6721 """A :class:`_expression.ColumnCollection`

6722 representing the columns that

6723 this SELECT statement or similar construct returns in its result set,

6724 not including :class:`_sql.TextClause` constructs.

6725

6726 This collection differs from the :attr:`_expression.FromClause.columns`

6727 collection of a :class:`_expression.FromClause` in that the columns

6728 within this collection cannot be directly nested inside another SELECT

6729 statement; a subquery must be applied first which provides for the

6730 necessary parenthesization required by SQL.

6731

6732 For a :func:`_expression.select` construct, the collection here is

6733 exactly what would be rendered inside the "SELECT" statement, and the

6734 :class:`_expression.ColumnElement` objects are directly present as they

6735 were given, e.g.::

6736

6737 col1 = column("q", Integer)

6738 col2 = column("p", Integer)

6739 stmt = select(col1, col2)

6740

6741 Above, ``stmt.selected_columns`` would be a collection that contains

6742 the ``col1`` and ``col2`` objects directly. For a statement that is

6743 against a :class:`_schema.Table` or other

6744 :class:`_expression.FromClause`, the collection will use the

6745 :class:`_expression.ColumnElement` objects that are in the

6746 :attr:`_expression.FromClause.c` collection of the from element.

6747

6748 A use case for the :attr:`_sql.Select.selected_columns` collection is

6749 to allow the existing columns to be referenced when adding additional

6750 criteria, e.g.::

6751

6752 def filter_on_id(my_select, id):

6753 return my_select.where(my_select.selected_columns["id"] == id)

6754

6755

6756 stmt = select(MyModel)

6757

6758 # adds "WHERE id=:param" to the statement

6759 stmt = filter_on_id(stmt, 42)

6760

6761 .. note::

6762

6763 The :attr:`_sql.Select.selected_columns` collection does not

6764 include expressions established in the columns clause using the

6765 :func:`_sql.text` construct; these are silently omitted from the

6766 collection. To use plain textual column expressions inside of a

6767 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6768 construct.

6769

6770

6771 .. versionadded:: 1.4

6772

6773 """

6774

6775 # compare to SelectState._generate_columns_plus_names, which

6776 # generates the actual names used in the SELECT string. that

6777 # method is more complex because it also renders columns that are

6778 # fully ambiguous, e.g. same column more than once.

6779 conv = cast(

6780 "Callable[[Any], str]",

6781 SelectState._column_naming_convention(self._label_style),

6782 )

6783

6784 cc: WriteableColumnCollection[str, ColumnElement[Any]] = (

6785 WriteableColumnCollection(

6786 [

6787 (conv(c), c)

6788 for c in self._all_selected_columns

6789 if is_column_element(c)

6790 ]

6791 )

6792 )

6793 return cc.as_readonly()

6794

6795 @HasMemoized_ro_memoized_attribute

6796 def _all_selected_columns(self) -> _SelectIterable:

6797 meth = SelectState.get_plugin_class(self).all_selected_columns

6798 return list(meth(self))

6799

6800 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6801 if self._label_style is LABEL_STYLE_NONE:

6802 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6803 return self

6804

6805 def _generate_fromclause_column_proxies(

6806 self,

6807 subquery: FromClause,

6808 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

6809 primary_key: ColumnSet,

6810 foreign_keys: Set[KeyedColumnElement[Any]],

6811 *,

6812 proxy_compound_columns: Optional[

6813 Iterable[Sequence[ColumnElement[Any]]]

6814 ] = None,

6815 ) -> None:

6816 """Generate column proxies to place in the exported ``.c``

6817 collection of a subquery."""

6818

6819 if proxy_compound_columns:

6820 extra_col_iterator = proxy_compound_columns

6821 prox = [

6822 c._make_proxy(

6823 subquery,

6824 key=proxy_key,

6825 name=required_label_name,

6826 name_is_truncatable=True,

6827 compound_select_cols=extra_cols,

6828 primary_key=primary_key,

6829 foreign_keys=foreign_keys,

6830 )

6831 for (

6832 (

6833 required_label_name,

6834 proxy_key,

6835 fallback_label_name,

6836 c,

6837 repeated,

6838 ),

6839 extra_cols,

6840 ) in (

6841 zip(

6842 self._generate_columns_plus_names(False),

6843 extra_col_iterator,

6844 )

6845 )

6846 if is_column_element(c)

6847 ]

6848 else:

6849 prox = [

6850 c._make_proxy(

6851 subquery,

6852 key=proxy_key,

6853 name=required_label_name,

6854 name_is_truncatable=True,

6855 primary_key=primary_key,

6856 foreign_keys=foreign_keys,

6857 )

6858 for (

6859 required_label_name,

6860 proxy_key,

6861 fallback_label_name,

6862 c,

6863 repeated,

6864 ) in (self._generate_columns_plus_names(False))

6865 if is_column_element(c)

6866 ]

6867

6868 columns._populate_separate_keys(prox)

6869

6870 def _needs_parens_for_grouping(self) -> bool:

6871 return self._has_row_limiting_clause or bool(

6872 self._order_by_clause.clauses

6873 )

6874

6875 def self_group(

6876 self, against: Optional[OperatorType] = None

6877 ) -> Union[SelectStatementGrouping[Self], Self]:

6878 """Return a 'grouping' construct as per the

6879 :class:`_expression.ClauseElement` specification.

6880

6881 This produces an element that can be embedded in an expression. Note

6882 that this method is called automatically as needed when constructing

6883 expressions and should not require explicit use.

6884

6885 """

6886 if (

6887 isinstance(against, CompoundSelect)

6888 and not self._needs_parens_for_grouping()

6889 ):

6890 return self

6891 else:

6892 return SelectStatementGrouping(self)

6893

6894 def union(

6895 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6896 ) -> CompoundSelect[Unpack[_Ts]]:

6897 r"""Return a SQL ``UNION`` of this select() construct against

6898 the given selectables provided as positional arguments.

6899

6900 :param \*other: one or more elements with which to create a

6901 UNION.

6902

6903 .. versionchanged:: 1.4.28

6904

6905 multiple elements are now accepted.

6906

6907 :param \**kwargs: keyword arguments are forwarded to the constructor

6908 for the newly created :class:`_sql.CompoundSelect` object.

6909

6910 """

6911 return CompoundSelect._create_union(self, *other)

6912

6913 def union_all(

6914 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6915 ) -> CompoundSelect[Unpack[_Ts]]:

6916 r"""Return a SQL ``UNION ALL`` of this select() construct against

6917 the given selectables provided as positional arguments.

6918

6919 :param \*other: one or more elements with which to create a

6920 UNION.

6921

6922 .. versionchanged:: 1.4.28

6923

6924 multiple elements are now accepted.

6925

6926 :param \**kwargs: keyword arguments are forwarded to the constructor

6927 for the newly created :class:`_sql.CompoundSelect` object.

6928

6929 """

6930 return CompoundSelect._create_union_all(self, *other)

6931

6932 def except_(

6933 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6934 ) -> CompoundSelect[Unpack[_Ts]]:

6935 r"""Return a SQL ``EXCEPT`` of this select() construct against

6936 the given selectable provided as positional arguments.

6937

6938 :param \*other: one or more elements with which to create a

6939 UNION.

6940

6941 .. versionchanged:: 1.4.28

6942

6943 multiple elements are now accepted.

6944

6945 """

6946 return CompoundSelect._create_except(self, *other)

6947

6948 def except_all(

6949 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6950 ) -> CompoundSelect[Unpack[_Ts]]:

6951 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6952 the given selectables provided as positional arguments.

6953

6954 :param \*other: one or more elements with which to create a

6955 UNION.

6956

6957 .. versionchanged:: 1.4.28

6958

6959 multiple elements are now accepted.

6960

6961 """

6962 return CompoundSelect._create_except_all(self, *other)

6963

6964 def intersect(

6965 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6966 ) -> CompoundSelect[Unpack[_Ts]]:

6967 r"""Return a SQL ``INTERSECT`` of this select() construct against

6968 the given selectables provided as positional arguments.

6969

6970 :param \*other: one or more elements with which to create a

6971 UNION.

6972

6973 .. versionchanged:: 1.4.28

6974

6975 multiple elements are now accepted.

6976

6977 :param \**kwargs: keyword arguments are forwarded to the constructor

6978 for the newly created :class:`_sql.CompoundSelect` object.

6979

6980 """

6981 return CompoundSelect._create_intersect(self, *other)

6982

6983 def intersect_all(

6984 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6985 ) -> CompoundSelect[Unpack[_Ts]]:

6986 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6987 against the given selectables provided as positional arguments.

6988

6989 :param \*other: one or more elements with which to create a

6990 UNION.

6991

6992 .. versionchanged:: 1.4.28

6993

6994 multiple elements are now accepted.

6995

6996 :param \**kwargs: keyword arguments are forwarded to the constructor

6997 for the newly created :class:`_sql.CompoundSelect` object.

6998

6999 """

7000 return CompoundSelect._create_intersect_all(self, *other)

7001

7002

7003class ScalarSelect(

7004 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

7005):

7006 """Represent a scalar subquery.

7007

7008

7009 A :class:`_sql.ScalarSelect` is created by invoking the

7010 :meth:`_sql.SelectBase.scalar_subquery` method. The object

7011 then participates in other SQL expressions as a SQL column expression

7012 within the :class:`_sql.ColumnElement` hierarchy.

7013

7014 .. seealso::

7015

7016 :meth:`_sql.SelectBase.scalar_subquery`

7017

7018 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7019

7020 """

7021

7022 _traverse_internals: _TraverseInternalsType = [

7023 ("element", InternalTraversal.dp_clauseelement),

7024 ("type", InternalTraversal.dp_type),

7025 ]

7026

7027 _from_objects: List[FromClause] = []

7028 _is_from_container = True

7029 if not TYPE_CHECKING:

7030 _is_implicitly_boolean = False

7031 inherit_cache = True

7032

7033 element: SelectBase

7034

7035 def __init__(self, element: SelectBase) -> None:

7036 self.element = element

7037 self.type = element._scalar_type()

7038 self._propagate_attrs = element._propagate_attrs

7039

7040 def __getattr__(self, attr: str) -> Any:

7041 return getattr(self.element, attr)

7042

7043 def __getstate__(self) -> Dict[str, Any]:

7044 return {"element": self.element, "type": self.type}

7045

7046 def __setstate__(self, state: Dict[str, Any]) -> None:

7047 self.element = state["element"]

7048 self.type = state["type"]

7049

7050 @property

7051 def columns(self) -> NoReturn:

7052 raise exc.InvalidRequestError(

7053 "Scalar Select expression has no "

7054 "columns; use this object directly "

7055 "within a column-level expression."

7056 )

7057

7058 c = columns

7059

7060 @_generative

7061 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7062 """Apply a WHERE clause to the SELECT statement referred to

7063 by this :class:`_expression.ScalarSelect`.

7064

7065 """

7066 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7067 crit

7068 )

7069 return self

7070

7071 def self_group(self, against: Optional[OperatorType] = None) -> Self:

7072 return self

7073

7074 def _ungroup(self) -> Self:

7075 return self

7076

7077 @_generative

7078 def correlate(

7079 self,

7080 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7081 ) -> Self:

7082 r"""Return a new :class:`_expression.ScalarSelect`

7083 which will correlate the given FROM

7084 clauses to that of an enclosing :class:`_expression.Select`.

7085

7086 This method is mirrored from the :meth:`_sql.Select.correlate` method

7087 of the underlying :class:`_sql.Select`. The method applies the

7088 :meth:_sql.Select.correlate` method, then returns a new

7089 :class:`_sql.ScalarSelect` against that statement.

7090

7091 .. versionadded:: 1.4 Previously, the

7092 :meth:`_sql.ScalarSelect.correlate`

7093 method was only available from :class:`_sql.Select`.

7094

7095 :param \*fromclauses: a list of one or more

7096 :class:`_expression.FromClause`

7097 constructs, or other compatible constructs (i.e. ORM-mapped

7098 classes) to become part of the correlate collection.

7099

7100 .. seealso::

7101

7102 :meth:`_expression.ScalarSelect.correlate_except`

7103

7104 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7105

7106

7107 """

7108 self.element = cast(

7109 "Select[Unpack[TupleAny]]", self.element

7110 ).correlate(*fromclauses)

7111 return self

7112

7113 @_generative

7114 def correlate_except(

7115 self,

7116 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7117 ) -> Self:

7118 r"""Return a new :class:`_expression.ScalarSelect`

7119 which will omit the given FROM

7120 clauses from the auto-correlation process.

7121

7122 This method is mirrored from the

7123 :meth:`_sql.Select.correlate_except` method of the underlying

7124 :class:`_sql.Select`. The method applies the

7125 :meth:_sql.Select.correlate_except` method, then returns a new

7126 :class:`_sql.ScalarSelect` against that statement.

7127

7128 .. versionadded:: 1.4 Previously, the

7129 :meth:`_sql.ScalarSelect.correlate_except`

7130 method was only available from :class:`_sql.Select`.

7131

7132 :param \*fromclauses: a list of one or more

7133 :class:`_expression.FromClause`

7134 constructs, or other compatible constructs (i.e. ORM-mapped

7135 classes) to become part of the correlate-exception collection.

7136

7137 .. seealso::

7138

7139 :meth:`_expression.ScalarSelect.correlate`

7140

7141 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7142

7143

7144 """

7145

7146 self.element = cast(

7147 "Select[Unpack[TupleAny]]", self.element

7148 ).correlate_except(*fromclauses)

7149 return self

7150

7151

7152class Exists(UnaryExpression[bool]):

7153 """Represent an ``EXISTS`` clause.

7154

7155 See :func:`_sql.exists` for a description of usage.

7156

7157 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7158 instance by calling :meth:`_sql.SelectBase.exists`.

7159

7160 """

7161

7162 inherit_cache = True

7163

7164 def __init__(

7165 self,

7166 __argument: Optional[

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

7168 ] = None,

7169 /,

7170 ):

7171 s: ScalarSelect[Any]

7172

7173 # TODO: this seems like we should be using coercions for this

7174 if __argument is None:

7175 s = Select(literal_column("*")).scalar_subquery()

7176 elif isinstance(__argument, SelectBase):

7177 s = __argument.scalar_subquery()

7178 s._propagate_attrs = __argument._propagate_attrs

7179 elif isinstance(__argument, ScalarSelect):

7180 s = __argument

7181 else:

7182 s = Select(__argument).scalar_subquery()

7183

7184 UnaryExpression.__init__(

7185 self,

7186 s,

7187 operator=operators.exists,

7188 type_=type_api.BOOLEANTYPE,

7189 )

7190

7191 @util.ro_non_memoized_property

7192 def _from_objects(self) -> List[FromClause]:

7193 return []

7194

7195 def _regroup(

7196 self,

7197 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7198 ) -> ScalarSelect[Any]:

7199

7200 assert isinstance(self.element, ScalarSelect)

7201 element = self.element.element

7202 if not isinstance(element, Select):

7203 raise exc.InvalidRequestError(

7204 "Can only apply this operation to a plain SELECT construct"

7205 )

7206 new_element = fn(element)

7207

7208 return_value = new_element.scalar_subquery()

7209 return return_value

7210

7211 def select(self) -> Select[bool]:

7212 r"""Return a SELECT of this :class:`_expression.Exists`.

7213

7214 e.g.::

7215

7216 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7217

7218 This will produce a statement resembling:

7219

7220 .. sourcecode:: sql

7221

7222 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7223

7224 .. seealso::

7225

7226 :func:`_expression.select` - general purpose

7227 method which allows for arbitrary column lists.

7228

7229 """ # noqa

7230

7231 return Select(self)

7232

7233 def correlate(

7234 self,

7235 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7236 ) -> Self:

7237 """Apply correlation to the subquery noted by this

7238 :class:`_sql.Exists`.

7239

7240 .. seealso::

7241

7242 :meth:`_sql.ScalarSelect.correlate`

7243

7244 """

7245 e = self._clone()

7246 e.element = self._regroup(

7247 lambda element: element.correlate(*fromclauses)

7248 )

7249 return e

7250

7251 def correlate_except(

7252 self,

7253 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7254 ) -> Self:

7255 """Apply correlation to the subquery noted by this

7256 :class:`_sql.Exists`.

7257

7258 .. seealso::

7259

7260 :meth:`_sql.ScalarSelect.correlate_except`

7261

7262 """

7263 e = self._clone()

7264 e.element = self._regroup(

7265 lambda element: element.correlate_except(*fromclauses)

7266 )

7267 return e

7268

7269 def select_from(self, *froms: _FromClauseArgument) -> Self:

7270 """Return a new :class:`_expression.Exists` construct,

7271 applying the given

7272 expression to the :meth:`_expression.Select.select_from`

7273 method of the select

7274 statement contained.

7275

7276 .. note:: it is typically preferable to build a :class:`_sql.Select`

7277 statement first, including the desired WHERE clause, then use the

7278 :meth:`_sql.SelectBase.exists` method to produce an

7279 :class:`_sql.Exists` object at once.

7280

7281 """

7282 e = self._clone()

7283 e.element = self._regroup(lambda element: element.select_from(*froms))

7284 return e

7285

7286 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7287 """Return a new :func:`_expression.exists` construct with the

7288 given expression added to

7289 its WHERE clause, joined to the existing clause via AND, if any.

7290

7291

7292 .. note:: it is typically preferable to build a :class:`_sql.Select`

7293 statement first, including the desired WHERE clause, then use the

7294 :meth:`_sql.SelectBase.exists` method to produce an

7295 :class:`_sql.Exists` object at once.

7296

7297 """

7298 e = self._clone()

7299 e.element = self._regroup(lambda element: element.where(*clause))

7300 return e

7301

7302

7303class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7304 """Wrap a :class:`_expression.TextClause` construct within a

7305 :class:`_expression.SelectBase`

7306 interface.

7307

7308 This allows the :class:`_expression.TextClause` object to gain a

7309 ``.c`` collection

7310 and other FROM-like capabilities such as

7311 :meth:`_expression.FromClause.alias`,

7312 :meth:`_expression.SelectBase.cte`, etc.

7313

7314 The :class:`_expression.TextualSelect` construct is produced via the

7315 :meth:`_expression.TextClause.columns`

7316 method - see that method for details.

7317

7318 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7319 class was renamed

7320 from ``TextAsFrom``, to more correctly suit its role as a

7321 SELECT-oriented object and not a FROM clause.

7322

7323 .. seealso::

7324

7325 :func:`_expression.text`

7326

7327 :meth:`_expression.TextClause.columns` - primary creation interface.

7328

7329 """

7330

7331 __visit_name__ = "textual_select"

7332

7333 _label_style = LABEL_STYLE_NONE

7334

7335 _traverse_internals: _TraverseInternalsType = (

7336 [

7337 ("element", InternalTraversal.dp_clauseelement),

7338 ("column_args", InternalTraversal.dp_clauseelement_list),

7339 ]

7340 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7341 + HasCTE._has_ctes_traverse_internals

7342 + ExecutableStatement._executable_traverse_internals

7343 )

7344

7345 _is_textual = True

7346

7347 is_text = True

7348 is_select = True

7349

7350 def __init__(

7351 self,

7352 text: TextClause,

7353 columns: List[_ColumnExpressionArgument[Any]],

7354 positional: bool = False,

7355 ) -> None:

7356 self._init(

7357 text,

7358 # convert for ORM attributes->columns, etc

7359 [

7360 coercions.expect(roles.LabeledColumnExprRole, c)

7361 for c in columns

7362 ],

7363 positional,

7364 )

7365

7366 def _init(

7367 self,

7368 text: AbstractTextClause,

7369 columns: List[NamedColumn[Any]],

7370 positional: bool = False,

7371 ) -> None:

7372 self.element = text

7373 self.column_args = columns

7374 self.positional = positional

7375

7376 @HasMemoized_ro_memoized_attribute

7377 def selected_columns(

7378 self,

7379 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7380 """A :class:`_expression.ColumnCollection`

7381 representing the columns that

7382 this SELECT statement or similar construct returns in its result set,

7383 not including :class:`_sql.TextClause` constructs.

7384

7385 This collection differs from the :attr:`_expression.FromClause.columns`

7386 collection of a :class:`_expression.FromClause` in that the columns

7387 within this collection cannot be directly nested inside another SELECT

7388 statement; a subquery must be applied first which provides for the

7389 necessary parenthesization required by SQL.

7390

7391 For a :class:`_expression.TextualSelect` construct, the collection

7392 contains the :class:`_expression.ColumnElement` objects that were

7393 passed to the constructor, typically via the

7394 :meth:`_expression.TextClause.columns` method.

7395

7396

7397 .. versionadded:: 1.4

7398

7399 """

7400 return WriteableColumnCollection(

7401 (c.key, c) for c in self.column_args

7402 ).as_readonly()

7403

7404 @util.ro_non_memoized_property

7405 def _all_selected_columns(self) -> _SelectIterable:

7406 return self.column_args

7407

7408 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7409 return self

7410

7411 def _ensure_disambiguated_names(self) -> TextualSelect:

7412 return self

7413

7414 @_generative

7415 def bindparams(

7416 self,

7417 *binds: BindParameter[Any],

7418 **bind_as_values: Any,

7419 ) -> Self:

7420 self.element = self.element.bindparams(*binds, **bind_as_values)

7421 return self

7422

7423 def _generate_fromclause_column_proxies(

7424 self,

7425 fromclause: FromClause,

7426 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

7427 primary_key: ColumnSet,

7428 foreign_keys: Set[KeyedColumnElement[Any]],

7429 *,

7430 proxy_compound_columns: Optional[

7431 Iterable[Sequence[ColumnElement[Any]]]

7432 ] = None,

7433 ) -> None:

7434 if TYPE_CHECKING:

7435 assert isinstance(fromclause, Subquery)

7436

7437 if proxy_compound_columns:

7438 columns._populate_separate_keys(

7439 c._make_proxy(

7440 fromclause,

7441 compound_select_cols=extra_cols,

7442 primary_key=primary_key,

7443 foreign_keys=foreign_keys,

7444 )

7445 for c, extra_cols in zip(

7446 self.column_args, proxy_compound_columns

7447 )

7448 )

7449 else:

7450 columns._populate_separate_keys(

7451 c._make_proxy(

7452 fromclause,

7453 primary_key=primary_key,

7454 foreign_keys=foreign_keys,

7455 )

7456 for c in self.column_args

7457 )

7458

7459 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7460 return self.column_args[0].type

7461

7462

7463TextAsFrom = TextualSelect

7464"""Backwards compatibility with the previous name"""

7465

7466

7467class AnnotatedFromClause(Annotated):

7468 def _copy_internals(

7469 self,

7470 _annotations_traversal: bool = False,

7471 ind_cols_on_fromclause: bool = False,

7472 **kw: Any,

7473 ) -> None:

7474 super()._copy_internals(**kw)

7475

7476 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7477 # the traversals used by annotations for these cases are not currently

7478 # designed around expecting that inner elements inside of

7479 # AnnotatedFromClause's element are also deep copied, so skip for these

7480 # cases. in other cases such as plain visitors.cloned_traverse(), we

7481 # expect this to happen. see issue #12915

7482 if not _annotations_traversal:

7483 ee = self._Annotated__element # type: ignore

7484 ee._copy_internals(**kw)

7485

7486 if ind_cols_on_fromclause:

7487 # passed from annotations._deep_annotate(). See that function

7488 # for notes

7489 ee = self._Annotated__element # type: ignore

7490 self.c = ee.__class__.c.fget(self) # type: ignore

7491

7492 @util.ro_memoized_property

7493 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7494 """proxy the .c collection of the underlying FromClause.

7495

7496 Originally implemented in 2008 as a simple load of the .c collection

7497 when the annotated construct was created (see d3621ae961a), in modern

7498 SQLAlchemy versions this can be expensive for statements constructed

7499 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7500 it, which works just as well.

7501

7502 Two different use cases seem to require the collection either copied

7503 from the underlying one, or unique to this AnnotatedFromClause.

7504

7505 See test_selectable->test_annotated_corresponding_column

7506

7507 """

7508 ee = self._Annotated__element # type: ignore

7509 return ee.c # type: ignore