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

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

1202 FROM table1 JOIN table2 ON true

1203

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

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

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

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

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

1209

1210

1211 .. versionadded:: 1.4

1212

1213 """ # noqa: E501

1214

1215 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1218 when generating the columns clause of a SELECT statement.

1219

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

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

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

1223

1224 .. sourcecode:: pycon+sql

1225

1226 >>> from sqlalchemy import (

1227 ... table,

1228 ... column,

1229 ... select,

1230 ... true,

1231 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1232 ... )

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

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

1235 >>> print(

1236 ... select(table1, table2)

1237 ... .join(table2, true())

1238 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1239 ... )

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

1241 FROM table1 JOIN table2 ON true

1242

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

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

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

1246

1247 .. versionadded:: 1.4

1248

1249 """ # noqa: E501

1250

1251 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1252 """The default label style, refers to

1253 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1254

1255 .. versionadded:: 1.4

1256

1257 """

1258

1259 LABEL_STYLE_LEGACY_ORM = 3

1260

1261

1262(

1263 LABEL_STYLE_NONE,

1264 LABEL_STYLE_TABLENAME_PLUS_COL,

1265 LABEL_STYLE_DISAMBIGUATE_ONLY,

1266 _,

1267) = list(SelectLabelStyle)

1268

1269LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1270

1271

1272class Join(roles.DMLTableRole, FromClause[_KeyColCC_co]):

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

1274 :class:`_expression.FromClause`

1275 elements.

1276

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

1278 is the module-level

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

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

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

1282 :class:`_schema.Table`).

1283

1284 .. seealso::

1285

1286 :func:`_expression.join`

1287

1288 :meth:`_expression.FromClause.join`

1289

1290 """

1291

1292 __visit_name__ = "join"

1293

1294 _traverse_internals: _TraverseInternalsType = [

1295 ("left", InternalTraversal.dp_clauseelement),

1296 ("right", InternalTraversal.dp_clauseelement),

1297 ("onclause", InternalTraversal.dp_clauseelement),

1298 ("isouter", InternalTraversal.dp_boolean),

1299 ("full", InternalTraversal.dp_boolean),

1300 ]

1301

1302 _is_join = True

1303

1304 left: FromClause

1305 right: FromClause

1306 onclause: Optional[ColumnElement[bool]]

1307 isouter: bool

1308 full: bool

1309

1310 def __init__(

1311 self,

1312 left: _FromClauseArgument,

1313 right: _FromClauseArgument,

1314 onclause: Optional[_OnClauseArgument] = None,

1315 isouter: bool = False,

1316 full: bool = False,

1317 ):

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

1319

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

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

1322 :class:`_expression.FromClause` object.

1323

1324 """

1325

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

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

1328 # annotated objects. test_orm.py -> test_fetch_results

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

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

1331 # callcounts for a single compilation in that particular test

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

1333 # 29200 -> 30373

1334

1335 self.left = coercions.expect(

1336 roles.FromClauseRole,

1337 left,

1338 )

1339 self.right = coercions.expect(

1340 roles.FromClauseRole,

1341 right,

1342 ).self_group()

1343

1344 if onclause is None:

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

1346 else:

1347 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1348 # not merged yet

1349 self.onclause = coercions.expect(

1350 roles.OnClauseRole, onclause

1351 ).self_group(against=operators._asbool)

1352

1353 self.isouter = isouter

1354 self.full = full

1355

1356 @util.ro_non_memoized_property

1357 def description(self) -> str:

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

1359 self.left.description,

1360 id(self.left),

1361 self.right.description,

1362 id(self.right),

1363 )

1364

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

1366 return (

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

1368 # as well

1369 hash(fromclause) == hash(self)

1370 or self.left.is_derived_from(fromclause)

1371 or self.right.is_derived_from(fromclause)

1372 )

1373

1374 def self_group(

1375 self, against: Optional[OperatorType] = None

1376 ) -> FromGrouping:

1377 return FromGrouping(self)

1378

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

1380 def _populate_column_collection(

1381 self,

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

1383 primary_key: ColumnSet,

1384 foreign_keys: Set[KeyedColumnElement[Any]],

1385 ) -> None:

1386 sqlutil = util.preloaded.sql_util

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

1388 c for c in self.right.c

1389 ]

1390

1391 primary_key.extend(

1392 sqlutil.reduce_columns(

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

1394 )

1395 )

1396 columns._populate_separate_keys(

1397 (col._tq_key_label, col) for col in _columns # type: ignore[misc]

1398 )

1399 foreign_keys.update(

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

1401 )

1402

1403 def _copy_internals(

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

1405 ) -> None:

1406 # see Select._copy_internals() for similar concept

1407

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

1409 # determine the new FROM clauses

1410 all_the_froms = set(

1411 itertools.chain(

1412 _from_objects(self.left),

1413 _from_objects(self.right),

1414 )

1415 )

1416

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

1418 # cache used by the clone function

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

1420

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

1422 # ColumnClause with parent table referring to those

1423 # replaced FromClause objects

1424 def replace(

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

1426 **kw: Any,

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

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

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

1430 return newelem

1431 return None

1432

1433 kw["replace"] = replace

1434

1435 # run normal _copy_internals. the clones for

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

1437 # cache

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

1439

1440 self._reset_memoizations()

1441

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

1443 super()._refresh_for_new_column(column)

1444 self.left._refresh_for_new_column(column)

1445 self.right._refresh_for_new_column(column)

1446

1447 def _match_primaries(

1448 self,

1449 left: FromClause,

1450 right: FromClause,

1451 ) -> ColumnElement[bool]:

1452 if isinstance(left, Join):

1453 left_right = left.right

1454 else:

1455 left_right = None

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

1457

1458 @classmethod

1459 def _join_condition(

1460 cls,

1461 a: FromClause,

1462 b: FromClause,

1463 *,

1464 a_subset: Optional[FromClause] = None,

1465 consider_as_foreign_keys: Optional[

1466 AbstractSet[ColumnClause[Any]]

1467 ] = None,

1468 ) -> ColumnElement[bool]:

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

1470

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

1472

1473 """

1474 constraints = cls._joincond_scan_left_right(

1475 a, a_subset, b, consider_as_foreign_keys

1476 )

1477

1478 if len(constraints) > 1:

1479 cls._joincond_trim_constraints(

1480 a, b, constraints, consider_as_foreign_keys

1481 )

1482

1483 if len(constraints) == 0:

1484 if isinstance(b, FromGrouping):

1485 hint = (

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

1487 "subquery using alias()?"

1488 )

1489 else:

1490 hint = ""

1491 raise exc.NoForeignKeysError(

1492 "Can't find any foreign key relationships "

1493 "between '%s' and '%s'.%s"

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

1495 )

1496

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

1498 if len(crit) == 1:

1499 return crit[0]

1500 else:

1501 return and_(*crit)

1502

1503 @classmethod

1504 def _can_join(

1505 cls,

1506 left: FromClause,

1507 right: FromClause,

1508 *,

1509 consider_as_foreign_keys: Optional[

1510 AbstractSet[ColumnClause[Any]]

1511 ] = None,

1512 ) -> bool:

1513 if isinstance(left, Join):

1514 left_right = left.right

1515 else:

1516 left_right = None

1517

1518 constraints = cls._joincond_scan_left_right(

1519 a=left,

1520 b=right,

1521 a_subset=left_right,

1522 consider_as_foreign_keys=consider_as_foreign_keys,

1523 )

1524

1525 return bool(constraints)

1526

1527 @classmethod

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

1529 def _joincond_scan_left_right(

1530 cls,

1531 a: FromClause,

1532 a_subset: Optional[FromClause],

1533 b: FromClause,

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

1535 ) -> collections.defaultdict[

1536 Optional[ForeignKeyConstraint],

1537 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1538 ]:

1539 sql_util = util.preloaded.sql_util

1540

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

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

1543

1544 constraints: collections.defaultdict[

1545 Optional[ForeignKeyConstraint],

1546 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1547 ] = collections.defaultdict(list)

1548

1549 for left in (a_subset, a):

1550 if left is None:

1551 continue

1552 for fk in sorted(

1553 b.foreign_keys,

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

1555 ):

1556 if (

1557 consider_as_foreign_keys is not None

1558 and fk.parent not in consider_as_foreign_keys

1559 ):

1560 continue

1561 try:

1562 col = fk.get_referent(left)

1563 except exc.NoReferenceError as nrte:

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

1565 if nrte.table_name in table_names:

1566 raise

1567 else:

1568 continue

1569

1570 if col is not None:

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

1572 if left is not b:

1573 for fk in sorted(

1574 left.foreign_keys,

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

1576 ):

1577 if (

1578 consider_as_foreign_keys is not None

1579 and fk.parent not in consider_as_foreign_keys

1580 ):

1581 continue

1582 try:

1583 col = fk.get_referent(b)

1584 except exc.NoReferenceError as nrte:

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

1586 if nrte.table_name in table_names:

1587 raise

1588 else:

1589 continue

1590

1591 if col is not None:

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

1593 if constraints:

1594 break

1595 return constraints

1596

1597 @classmethod

1598 def _joincond_trim_constraints(

1599 cls,

1600 a: FromClause,

1601 b: FromClause,

1602 constraints: Dict[Any, Any],

1603 consider_as_foreign_keys: Optional[Any],

1604 ) -> None:

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

1606 # to include just those FKCs that match exactly to

1607 # "consider_as_foreign_keys".

1608 if consider_as_foreign_keys:

1609 for const in list(constraints):

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

1611 consider_as_foreign_keys

1612 ):

1613 del constraints[const]

1614

1615 # if still multiple constraints, but

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

1617 if len(constraints) > 1:

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

1619 if len(dedupe) == 1:

1620 key = list(constraints)[0]

1621 constraints = {key: constraints[key]}

1622

1623 if len(constraints) != 1:

1624 raise exc.AmbiguousForeignKeysError(

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

1626 "tables have more than one foreign key "

1627 "constraint relationship between them. "

1628 "Please specify the 'onclause' of this "

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

1630 )

1631

1632 @overload

1633 def select(

1634 self: Join[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]

1635 ) -> Select[Unpack[_Ts]]: ...

1636 @overload

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

1638

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

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

1641 :class:`_expression.Join`.

1642

1643 E.g.::

1644

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

1646

1647 stmt = stmt.select()

1648

1649 The above will produce a SQL string resembling:

1650

1651 .. sourcecode:: sql

1652

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

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

1655

1656 """

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

1658

1659 @staticmethod

1660 def _flat_element_name(

1661 name: Optional[str], element: FromClause

1662 ) -> Optional[str]:

1663 if name and isinstance(element, NamedFromClause):

1664 if isinstance(element.name, _anonymous_label):

1665 # an anonymous name can't be embedded within a new name;

1666 # alias the element anonymously, #13583

1667 return None

1668 return f"{name}_{element.name}"

1669 else:

1670 return name

1671

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

1673 def _anonymous_fromclause(

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

1675 ) -> TODO_Any:

1676 sqlutil = util.preloaded.sql_util

1677 if flat:

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

1679 left_name = name # will recurse

1680 else:

1681 left_name = self._flat_element_name(name, self.left)

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

1683 right_name = name # will recurse

1684 else:

1685 right_name = self._flat_element_name(name, self.right)

1686 left_a, right_a = (

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

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

1689 )

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

1691 sqlutil.ClauseAdapter(right_a)

1692 )

1693

1694 return left_a.join(

1695 right_a,

1696 adapter.traverse(self.onclause),

1697 isouter=self.isouter,

1698 full=self.full,

1699 )

1700 else:

1701 return (

1702 self.select()

1703 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1704 .correlate(None)

1705 .alias(name)

1706 )

1707

1708 @util.ro_non_memoized_property

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

1710 return itertools.chain(

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

1712 )

1713

1714 @util.ro_non_memoized_property

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

1716 self_list: List[FromClause] = [self]

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

1718

1719 if TYPE_CHECKING:

1720

1721 def with_cols(self, type_: type[_TC_co]) -> Join[_TC_co]: ...

1722

1723

1724class NoInit:

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

1726 raise NotImplementedError(

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

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

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

1730 "selectable objects."

1731 % (

1732 self.__class__.__name__,

1733 self.__class__.__name__.lower(),

1734 self.__class__.__name__.lower(),

1735 )

1736 )

1737

1738

1739class LateralFromClause(NamedFromClause):

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

1741

1742

1743# FromClause ->

1744# AliasedReturnsRows

1745# -> Alias only for FromClause

1746# -> Subquery only for SelectBase

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

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

1749# w/ non-deprecated coercion

1750# -> TableSample -> only for FromClause

1751

1752

1753class AliasedReturnsRows(NoInit, NamedFromClause[_KeyColCC_co]):

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

1755 selectables."""

1756

1757 _is_from_container = True

1758

1759 _supports_derived_columns = False

1760

1761 element: ReturnsRows

1762

1763 _traverse_internals: _TraverseInternalsType = [

1764 ("element", InternalTraversal.dp_clauseelement),

1765 ("name", InternalTraversal.dp_anon_name),

1766 ]

1767

1768 @classmethod

1769 def _construct(

1770 cls,

1771 selectable: Any,

1772 *,

1773 name: Optional[str] = None,

1774 **kw: Any,

1775 ) -> Self:

1776 obj = cls.__new__(cls)

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

1778 return obj

1779

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

1781 self.element = coercions.expect(

1782 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1783 )

1784 self.element = selectable

1785 self._orig_name = name

1786 if name is None:

1787 if (

1788 isinstance(selectable, FromClause)

1789 and selectable.named_with_column

1790 ):

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

1792 if isinstance(name, _anonymous_label):

1793 name = None

1794 name = _anonymous_label.safe_construct(

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

1796 )

1797 self.name = name

1798

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

1800 super()._refresh_for_new_column(column)

1801 self.element._refresh_for_new_column(column)

1802

1803 def _populate_column_collection(

1804 self,

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

1806 primary_key: ColumnSet,

1807 foreign_keys: Set[KeyedColumnElement[Any]],

1808 ) -> None:

1809 self.element._generate_fromclause_column_proxies(

1810 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1811 )

1812

1813 @util.ro_non_memoized_property

1814 def description(self) -> str:

1815 name = self.name

1816 if isinstance(name, _anonymous_label):

1817 return "anon_1"

1818

1819 return name

1820

1821 @util.ro_non_memoized_property

1822 def implicit_returning(self) -> bool:

1823 return self.element.implicit_returning # type: ignore[attr-defined, no-any-return] # noqa: E501

1824

1825 @property

1826 def original(self) -> ReturnsRows:

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

1828 return self.element

1829

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

1831 if fromclause in self._cloned_set:

1832 return True

1833 return self.element.is_derived_from(fromclause)

1834

1835 def _copy_internals(

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

1837 ) -> None:

1838 existing_element = self.element

1839

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

1841

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

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

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

1845 # performance.

1846 if existing_element is not self.element:

1847 self._reset_column_collection()

1848

1849 @property

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

1851 return [self]

1852

1853

1854class FromClauseAlias(AliasedReturnsRows[_KeyColCC_co]):

1855 element: FromClause[_KeyColCC_co]

1856

1857 @util.ro_non_memoized_property

1858 def description(self) -> str:

1859 name = self.name

1860 if isinstance(name, _anonymous_label):

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

1862

1863 return name

1864

1865

1866class Alias(roles.DMLTableRole, FromClauseAlias[_KeyColCC_co]):

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

1868

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

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

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

1872

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

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

1875 method available

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

1877

1878 .. seealso::

1879

1880 :meth:`_expression.FromClause.alias`

1881

1882 """

1883

1884 __visit_name__ = "alias"

1885

1886 inherit_cache = True

1887

1888 element: FromClause[_KeyColCC_co]

1889

1890 @classmethod

1891 def _factory(

1892 cls,

1893 selectable: FromClause[_KeyColCC_co],

1894 name: Optional[str] = None,

1895 flat: bool = False,

1896 ) -> NamedFromClause[_KeyColCC_co]:

1897 # mypy refuses to see the overload that has this returning

1898 # NamedFromClause[Any]. Pylance sees it just fine.

1899 return coercions.expect(roles.FromClauseRole, selectable).alias( # type: ignore[no-any-return] # noqa: E501

1900 name=name, flat=flat

1901 )

1902

1903

1904class TableValuedAlias(LateralFromClause, Alias):

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

1906

1907 This construct provides for a SQL function that returns columns

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

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

1910 method, e.g.:

1911

1912 .. sourcecode:: pycon+sql

1913

1914 >>> from sqlalchemy import select, func

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

1916 ... "value"

1917 ... )

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

1919 {printsql}SELECT anon_1.value

1920 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1921

1922 .. versionadded:: 1.4.0b2

1923

1924 .. seealso::

1925

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

1927

1928 """ # noqa: E501

1929

1930 __visit_name__ = "table_valued_alias"

1931

1932 _supports_derived_columns = True

1933 _render_derived = False

1934 _render_derived_w_types = False

1935 joins_implicitly = False

1936

1937 _traverse_internals: _TraverseInternalsType = [

1938 ("element", InternalTraversal.dp_clauseelement),

1939 ("name", InternalTraversal.dp_anon_name),

1940 ("_tableval_type", InternalTraversal.dp_type),

1941 ("_render_derived", InternalTraversal.dp_boolean),

1942 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1943 ]

1944

1945 def _init(

1946 self,

1947 selectable: Any,

1948 *,

1949 name: Optional[str] = None,

1950 table_value_type: Optional[TableValueType] = None,

1951 joins_implicitly: bool = False,

1952 ) -> None:

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

1954

1955 self.joins_implicitly = joins_implicitly

1956 self._tableval_type = (

1957 type_api.TABLEVALUE

1958 if table_value_type is None

1959 else table_value_type

1960 )

1961

1962 @HasMemoized.memoized_attribute

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

1964 """Return a column expression representing this

1965 :class:`_sql.TableValuedAlias`.

1966

1967 This accessor is used to implement the

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

1969 method for further details.

1970

1971 E.g.:

1972

1973 .. sourcecode:: pycon+sql

1974

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

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

1977

1978 .. seealso::

1979

1980 :meth:`_functions.FunctionElement.column_valued`

1981

1982 """

1983

1984 return TableValuedColumn(self, self._tableval_type)

1985

1986 def alias(

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

1988 ) -> TableValuedAlias:

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

1990

1991 This creates a distinct FROM object that will be distinguished

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

1993

1994 """

1995

1996 tva: TableValuedAlias = TableValuedAlias._construct(

1997 self,

1998 name=name,

1999 table_value_type=self._tableval_type,

2000 joins_implicitly=self.joins_implicitly,

2001 )

2002

2003 if self._render_derived:

2004 tva._render_derived = True

2005 tva._render_derived_w_types = self._render_derived_w_types

2006

2007 return tva

2008

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

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

2011 set, so that it renders as LATERAL.

2012

2013 .. seealso::

2014

2015 :func:`_expression.lateral`

2016

2017 """

2018 tva = self.alias(name=name)

2019 tva._is_lateral = True

2020 return tva

2021

2022 def render_derived(

2023 self,

2024 name: Optional[str] = None,

2025 with_types: bool = False,

2026 ) -> TableValuedAlias:

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

2028

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

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

2031

2032 .. sourcecode:: pycon+sql

2033

2034 >>> print(

2035 ... select(

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

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

2038 ... .render_derived()

2039 ... )

2040 ... )

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

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

2043

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

2045 the alias expression (this syntax currently applies to the

2046 PostgreSQL database):

2047

2048 .. sourcecode:: pycon+sql

2049

2050 >>> print(

2051 ... select(

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

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

2054 ... .render_derived(with_types=True)

2055 ... )

2056 ... )

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

2058 AS anon_1(a INTEGER, b VARCHAR)

2059

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

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

2062

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

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

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

2066

2067 """ # noqa: E501

2068

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

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

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

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

2073 # (just saw it happen on CI)

2074

2075 # construct against original to prevent memory growth

2076 # for repeated generations

2077 new_alias: TableValuedAlias = TableValuedAlias._construct(

2078 self.element,

2079 name=name,

2080 table_value_type=self._tableval_type,

2081 joins_implicitly=self.joins_implicitly,

2082 )

2083 new_alias._render_derived = True

2084 new_alias._render_derived_w_types = with_types

2085 return new_alias

2086

2087

2088class Lateral(FromClauseAlias, LateralFromClause):

2089 """Represent a LATERAL subquery.

2090

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

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

2093 method available

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

2095

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

2097 PostgreSQL versions provide support for this keyword.

2098

2099 .. seealso::

2100

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

2102

2103 """

2104

2105 __visit_name__ = "lateral"

2106 _is_lateral = True

2107

2108 inherit_cache = True

2109

2110 @classmethod

2111 def _factory(

2112 cls,

2113 selectable: Union[SelectBase, _FromClauseArgument],

2114 name: Optional[str] = None,

2115 ) -> LateralFromClause:

2116 return coercions.expect(

2117 roles.FromClauseRole, selectable, explicit_subquery=True

2118 ).lateral(name=name)

2119

2120

2121class TableSample(FromClauseAlias):

2122 """Represent a TABLESAMPLE clause.

2123

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

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

2126 method

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

2128

2129 .. seealso::

2130

2131 :func:`_expression.tablesample`

2132

2133 """

2134

2135 __visit_name__ = "tablesample"

2136

2137 _traverse_internals: _TraverseInternalsType = (

2138 AliasedReturnsRows._traverse_internals

2139 + [

2140 ("sampling", InternalTraversal.dp_clauseelement),

2141 ("seed", InternalTraversal.dp_clauseelement),

2142 ]

2143 )

2144

2145 @classmethod

2146 def _factory(

2147 cls,

2148 selectable: _FromClauseArgument,

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

2150 name: Optional[str] = None,

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

2152 ) -> TableSample:

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

2154 sampling, name=name, seed=seed

2155 )

2156

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

2158 def _init( # type: ignore[override]

2159 self,

2160 selectable: Any,

2161 *,

2162 name: Optional[str] = None,

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

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

2165 ) -> None:

2166 assert sampling is not None

2167 functions = util.preloaded.sql_functions

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

2169 sampling = functions.func.system(sampling)

2170

2171 self.sampling: Function[Any] = sampling

2172 self.seed = seed

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

2174

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

2176 return self.sampling

2177

2178

2179class CTE(

2180 roles.DMLTableRole,

2181 roles.IsCTERole,

2182 Generative,

2183 HasPrefixes,

2184 HasSuffixes,

2185 AliasedReturnsRows[_KeyColCC_co],

2186):

2187 """Represent a Common Table Expression.

2188

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

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

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

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

2193 :class:`_sql.Update` and

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

2195 usage details on CTEs.

2196

2197 .. seealso::

2198

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

2200

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

2202

2203 """

2204

2205 __visit_name__ = "cte"

2206

2207 _traverse_internals: _TraverseInternalsType = (

2208 AliasedReturnsRows._traverse_internals

2209 + [

2210 ("_cte_alias", InternalTraversal.dp_clauseelement),

2211 ("_restates", InternalTraversal.dp_clauseelement),

2212 ("recursive", InternalTraversal.dp_boolean),

2213 ("nesting", InternalTraversal.dp_boolean),

2214 ]

2215 + HasPrefixes._has_prefixes_traverse_internals

2216 + HasSuffixes._has_suffixes_traverse_internals

2217 )

2218

2219 element: HasCTE

2220

2221 @classmethod

2222 def _factory(

2223 cls,

2224 selectable: HasCTE,

2225 name: Optional[str] = None,

2226 recursive: bool = False,

2227 ) -> CTE:

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

2229 or Common Table Expression instance.

2230

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

2232

2233 """

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

2235 name=name, recursive=recursive

2236 )

2237

2238 def _init(

2239 self,

2240 selectable: HasCTE,

2241 *,

2242 name: Optional[str] = None,

2243 recursive: bool = False,

2244 nesting: bool = False,

2245 _cte_alias: Optional[CTE[_KeyColCC_co]] = None,

2246 _restates: Optional[CTE[_KeyColCC_co]] = None,

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

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

2249 ) -> None:

2250 self.recursive = recursive

2251 self.nesting = nesting

2252 self._cte_alias = _cte_alias

2253 # Keep recursivity reference with union/union_all

2254 self._restates = _restates

2255 if _prefixes:

2256 self._prefixes = _prefixes

2257 if _suffixes:

2258 self._suffixes = _suffixes

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

2260

2261 def _populate_column_collection(

2262 self,

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

2264 primary_key: ColumnSet,

2265 foreign_keys: Set[KeyedColumnElement[Any]],

2266 ) -> None:

2267 if self._cte_alias is not None:

2268 self._cte_alias._generate_fromclause_column_proxies(

2269 self,

2270 columns,

2271 primary_key=primary_key,

2272 foreign_keys=foreign_keys,

2273 )

2274 else:

2275 self.element._generate_fromclause_column_proxies(

2276 self,

2277 columns,

2278 primary_key=primary_key,

2279 foreign_keys=foreign_keys,

2280 )

2281

2282 def alias(

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

2284 ) -> CTE[_KeyColCC_co]:

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

2286 :class:`_expression.CTE`.

2287

2288 This method is a CTE-specific specialization of the

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

2290

2291 .. seealso::

2292

2293 :ref:`tutorial_using_aliases`

2294

2295 :func:`_expression.alias`

2296

2297 """

2298 return CTE._construct(

2299 self.element,

2300 name=name,

2301 recursive=self.recursive,

2302 nesting=self.nesting,

2303 # an alias of an alias refers to the original CTE, #13583

2304 _cte_alias=(

2305 self._cte_alias if self._cte_alias is not None else self

2306 ),

2307 _prefixes=self._prefixes,

2308 _suffixes=self._suffixes,

2309 )

2310

2311 def union(

2312 self, *other: _SelectStatementForCompoundArgument[Any]

2313 ) -> CTE[_KeyColCC_co]:

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

2315 of the original CTE against the given selectables provided

2316 as positional arguments.

2317

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

2319 UNION.

2320

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

2322

2323 .. seealso::

2324

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

2326

2327 """

2328 assert is_select_statement(

2329 self.element

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

2331

2332 return CTE._construct(

2333 self.element.union(*other),

2334 name=self.name,

2335 recursive=self.recursive,

2336 nesting=self.nesting,

2337 _restates=self,

2338 _prefixes=self._prefixes,

2339 _suffixes=self._suffixes,

2340 )

2341

2342 def union_all(

2343 self, *other: _SelectStatementForCompoundArgument[Any]

2344 ) -> CTE[_KeyColCC_co]:

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

2346 of the original CTE against the given selectables provided

2347 as positional arguments.

2348

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

2350 UNION.

2351

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

2353

2354 .. seealso::

2355

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

2357

2358 """

2359

2360 assert is_select_statement(

2361 self.element

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

2363

2364 return CTE._construct(

2365 self.element.union_all(*other),

2366 name=self.name,

2367 recursive=self.recursive,

2368 nesting=self.nesting,

2369 _restates=self,

2370 _prefixes=self._prefixes,

2371 _suffixes=self._suffixes,

2372 )

2373

2374 def _get_reference_cte(self) -> CTE[_KeyColCC_co]:

2375 """

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

2377 Updated CTEs should still refer to the original CTE.

2378 This function returns this reference identifier.

2379 """

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

2381

2382 if TYPE_CHECKING:

2383

2384 def with_cols(self, type_: type[_TC_co]) -> CTE[_TC_co]: ...

2385

2386

2387class _CTEOpts(NamedTuple):

2388 nesting: bool

2389

2390

2391class _ColumnsPlusNames(NamedTuple):

2392 required_label_name: Optional[str]

2393 """

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

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

2396 """

2397

2398 proxy_key: Optional[str]

2399 """

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

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

2402 select.selected_columns collection

2403 """

2404

2405 fallback_label_name: Optional[str]

2406 """

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

2408 we have to render a label even though

2409 required_label_name was not given

2410 """

2411

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

2413 """

2414 the ColumnElement itself

2415 """

2416

2417 repeated: bool

2418 """

2419 True if this is a duplicate of a previous column

2420 in the list of columns

2421 """

2422

2423

2424class SelectsRows(ReturnsRows):

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

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

2427

2428 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2429

2430 def _generate_columns_plus_names(

2431 self,

2432 anon_for_dupe_key: bool,

2433 cols: Optional[_SelectIterable] = None,

2434 ) -> List[_ColumnsPlusNames]:

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

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

2437 on a :class:`.FromClause`.

2438

2439 This is distinct from the _column_naming_convention generator that's

2440 intended for population of the Select.selected_columns collection,

2441 different rules. the collection returned here calls upon the

2442 _column_naming_convention as well.

2443

2444 """

2445

2446 if cols is None:

2447 cols = self._all_selected_columns

2448

2449 key_naming_convention = SelectState._column_naming_convention(

2450 self._label_style

2451 )

2452

2453 names = {}

2454

2455 result: List[_ColumnsPlusNames] = []

2456 result_append = result.append

2457

2458 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2459 label_style_none = self._label_style is LABEL_STYLE_NONE

2460

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

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

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

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

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

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

2467 dedupe_hash = 1

2468

2469 for c in cols:

2470 repeated = False

2471

2472 if not c._render_label_in_columns_clause:

2473 effective_name = required_label_name = fallback_label_name = (

2474 None

2475 )

2476 elif label_style_none:

2477 if TYPE_CHECKING:

2478 assert is_column_element(c)

2479

2480 effective_name = required_label_name = None

2481 fallback_label_name = c._non_anon_label or c._anon_name_label

2482 else:

2483 if TYPE_CHECKING:

2484 assert is_column_element(c)

2485

2486 if table_qualified:

2487 required_label_name = effective_name = (

2488 fallback_label_name

2489 ) = c._tq_label

2490 else:

2491 effective_name = fallback_label_name = c._non_anon_label

2492 required_label_name = None

2493

2494 if effective_name is None:

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

2496 # not need _expression_label but it isn't

2497 # giving us a clue when to use anon_label instead

2498 expr_label = c._expression_label

2499 if expr_label is None:

2500 repeated = c._anon_name_label in names

2501 names[c._anon_name_label] = c

2502 effective_name = required_label_name = None

2503

2504 if repeated:

2505 # here, "required_label_name" is sent as

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

2507 if table_qualified:

2508 fallback_label_name = (

2509 c._dedupe_anon_tq_label_idx(dedupe_hash)

2510 )

2511 dedupe_hash += 1

2512 else:

2513 fallback_label_name = c._dedupe_anon_label_idx(

2514 dedupe_hash

2515 )

2516 dedupe_hash += 1

2517 else:

2518 fallback_label_name = c._anon_name_label

2519 else:

2520 required_label_name = effective_name = (

2521 fallback_label_name

2522 ) = expr_label

2523

2524 if effective_name is not None:

2525 if TYPE_CHECKING:

2526 assert is_column_element(c)

2527

2528 if effective_name in names:

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

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

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

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

2533 # different column under the same name. apply

2534 # disambiguating label

2535 if table_qualified:

2536 required_label_name = fallback_label_name = (

2537 c._anon_tq_label

2538 )

2539 else:

2540 required_label_name = fallback_label_name = (

2541 c._anon_name_label

2542 )

2543

2544 if anon_for_dupe_key and required_label_name in names:

2545 # here, c._anon_tq_label is definitely unique to

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

2547 # this should always be true.

2548 # this is also an infrequent codepath because

2549 # you need two levels of duplication to be here

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

2551

2552 # the column under the disambiguating label is

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

2554 # subsequent occurrences of the column so that the

2555 # original stays non-ambiguous

2556 if table_qualified:

2557 required_label_name = fallback_label_name = (

2558 c._dedupe_anon_tq_label_idx(dedupe_hash)

2559 )

2560 dedupe_hash += 1

2561 else:

2562 required_label_name = fallback_label_name = (

2563 c._dedupe_anon_label_idx(dedupe_hash)

2564 )

2565 dedupe_hash += 1

2566 repeated = True

2567 else:

2568 names[required_label_name] = c

2569 elif anon_for_dupe_key:

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

2571 # label so that the original stays non-ambiguous

2572 if table_qualified:

2573 required_label_name = fallback_label_name = (

2574 c._dedupe_anon_tq_label_idx(dedupe_hash)

2575 )

2576 dedupe_hash += 1

2577 else:

2578 required_label_name = fallback_label_name = (

2579 c._dedupe_anon_label_idx(dedupe_hash)

2580 )

2581 dedupe_hash += 1

2582 repeated = True

2583 else:

2584 names[effective_name] = c

2585

2586 result_append(

2587 _ColumnsPlusNames(

2588 required_label_name,

2589 key_naming_convention(c),

2590 fallback_label_name,

2591 c,

2592 repeated,

2593 )

2594 )

2595

2596 return result

2597

2598

2599class HasCTE(roles.HasCTERole, SelectsRows):

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

2601

2602 _has_ctes_traverse_internals: _TraverseInternalsType = [

2603 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2604 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2605 ]

2606

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

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

2609

2610 name_cte_columns: bool = False

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

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

2613

2614 .. versionadded:: 2.0.42

2615

2616 """

2617

2618 @_generative

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

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

2621

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

2623 the parent statement such that they will each be unconditionally

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

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

2626

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

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

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

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

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

2632 statement.

2633

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

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

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

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

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

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

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

2641 larger statement.

2642

2643 E.g.::

2644

2645 from sqlalchemy import table, column, select

2646

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

2648

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

2650

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

2652

2653 Would render:

2654

2655 .. sourcecode:: sql

2656

2657 WITH anon_1 AS (

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

2659 )

2660 SELECT t.c1, t.c2

2661 FROM t

2662

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

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

2665 statement.

2666

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

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

2669

2670 from sqlalchemy import table, column

2671 from sqlalchemy.dialects.postgresql import insert

2672

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

2674

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

2676

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

2678 update_statement = insert_stmt.on_conflict_do_update(

2679 index_elements=[t.c.c1],

2680 set_={

2681 "c1": insert_stmt.excluded.c1,

2682 "c2": insert_stmt.excluded.c2,

2683 },

2684 ).add_cte(delete_statement_cte)

2685

2686 print(update_statement)

2687

2688 The above statement renders as:

2689

2690 .. sourcecode:: sql

2691

2692 WITH deletions AS (

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

2694 )

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

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

2697

2698 .. versionadded:: 1.4.21

2699

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

2701

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

2703

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

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

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

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

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

2709 this statement when this flag is given.

2710

2711 .. versionadded:: 2.0

2712

2713 .. seealso::

2714

2715 :paramref:`.HasCTE.cte.nesting`

2716

2717

2718 """ # noqa: E501

2719 opt = _CTEOpts(nest_here)

2720 for cte in ctes:

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

2722 self._independent_ctes += (cte,)

2723 self._independent_ctes_opts += (opt,)

2724 return self

2725

2726 def cte(

2727 self,

2728 name: Optional[str] = None,

2729 recursive: bool = False,

2730 nesting: bool = False,

2731 ) -> CTE:

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

2733 or Common Table Expression instance.

2734

2735 Common table expressions are a SQL standard whereby SELECT

2736 statements can draw upon secondary statements specified along

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

2738 Special semantics regarding UNION can also be employed to

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

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

2741

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

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

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

2745 CTE rows.

2746

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

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

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

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

2751

2752 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2754 method may be

2755 used to establish these.

2756

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

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

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

2760 compile time.

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

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

2763 conjunction with UNION ALL in order to derive rows

2764 from those already selected.

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

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

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

2768 :paramref:`.HasCTE.add_cte.nest_here`

2769 parameter may also be used to more carefully

2770 control the exact placement of a particular CTE.

2771

2772 .. versionadded:: 1.4.24

2773

2774 .. seealso::

2775

2776 :meth:`.HasCTE.add_cte`

2777

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

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

2780 as well as additional examples.

2781

2782 Example 1, non recursive::

2783

2784 from sqlalchemy import (

2785 Table,

2786 Column,

2787 String,

2788 Integer,

2789 MetaData,

2790 select,

2791 func,

2792 )

2793

2794 metadata = MetaData()

2795

2796 orders = Table(

2797 "orders",

2798 metadata,

2799 Column("region", String),

2800 Column("amount", Integer),

2801 Column("product", String),

2802 Column("quantity", Integer),

2803 )

2804

2805 regional_sales = (

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

2807 .group_by(orders.c.region)

2808 .cte("regional_sales")

2809 )

2810

2811

2812 top_regions = (

2813 select(regional_sales.c.region)

2814 .where(

2815 regional_sales.c.total_sales

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

2817 )

2818 .cte("top_regions")

2819 )

2820

2821 statement = (

2822 select(

2823 orders.c.region,

2824 orders.c.product,

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

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

2827 )

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

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

2830 )

2831

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

2833

2834 Example 2, WITH RECURSIVE::

2835

2836 from sqlalchemy import (

2837 Table,

2838 Column,

2839 String,

2840 Integer,

2841 MetaData,

2842 select,

2843 func,

2844 )

2845

2846 metadata = MetaData()

2847

2848 parts = Table(

2849 "parts",

2850 metadata,

2851 Column("part", String),

2852 Column("sub_part", String),

2853 Column("quantity", Integer),

2854 )

2855

2856 included_parts = (

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

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

2859 .cte(recursive=True)

2860 )

2861

2862

2863 incl_alias = included_parts.alias()

2864 parts_alias = parts.alias()

2865 included_parts = included_parts.union_all(

2866 select(

2867 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2869 )

2870

2871 statement = select(

2872 included_parts.c.sub_part,

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

2874 ).group_by(included_parts.c.sub_part)

2875

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

2877

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

2879

2880 from datetime import date

2881 from sqlalchemy import (

2882 MetaData,

2883 Table,

2884 Column,

2885 Integer,

2886 Date,

2887 select,

2888 literal,

2889 and_,

2890 exists,

2891 )

2892

2893 metadata = MetaData()

2894

2895 visitors = Table(

2896 "visitors",

2897 metadata,

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

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

2900 Column("count", Integer),

2901 )

2902

2903 # add 5 visitors for the product_id == 1

2904 product_id = 1

2905 day = date.today()

2906 count = 5

2907

2908 update_cte = (

2909 visitors.update()

2910 .where(

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

2912 )

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

2914 .returning(literal(1))

2915 .cte("update_cte")

2916 )

2917

2918 upsert = visitors.insert().from_select(

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

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

2921 ~exists(update_cte.select())

2922 ),

2923 )

2924

2925 connection.execute(upsert)

2926

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

2928

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

2930

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

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

2933 "value_a", nesting=True

2934 )

2935

2936 # Nesting CTEs takes ascendency locally

2937 # over the CTEs at a higher level

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

2939

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

2941

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

2943 shown with inline parameters below as:

2944

2945 .. sourcecode:: sql

2946

2947 WITH

2948 value_a AS

2949 (SELECT 'root' AS n),

2950 value_b AS

2951 (WITH value_a AS

2952 (SELECT 'nesting' AS n)

2953 SELECT value_a.n AS n FROM value_a)

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

2955 FROM value_a, value_b

2956

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

2958 as follows (SQLAlchemy 2.0 and above)::

2959

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

2961

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

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

2964

2965 # Nesting CTEs takes ascendency locally

2966 # over the CTEs at a higher level

2967 value_b = (

2968 select(value_a_nested.c.n)

2969 .add_cte(value_a_nested, nest_here=True)

2970 .cte("value_b")

2971 )

2972

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

2974

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

2976

2977 edge = Table(

2978 "edge",

2979 metadata,

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

2981 Column("left", Integer),

2982 Column("right", Integer),

2983 )

2984

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

2986

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

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

2989 )

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

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

2992 )

2993

2994 subgraph_cte = root_node.union(left_edge, right_edge)

2995

2996 subgraph = select(subgraph_cte)

2997

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

2999

3000 .. sourcecode:: sql

3001

3002 WITH RECURSIVE nodes(node) AS (

3003 SELECT 1 AS node

3004 UNION

3005 SELECT edge."left" AS "left"

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

3007 UNION

3008 SELECT edge."right" AS "right"

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

3010 )

3011 SELECT nodes.node FROM nodes

3012

3013 .. seealso::

3014

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

3016 :meth:`_expression.HasCTE.cte`.

3017

3018 """ # noqa: E501

3019 return CTE._construct(

3020 self, name=name, recursive=recursive, nesting=nesting

3021 )

3022

3023

3024class Subquery(AliasedReturnsRows[_KeyColCC_co]):

3025 """Represent a subquery of a SELECT.

3026

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

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

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

3030 :class:`_expression.SelectBase` subclass

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

3032 :class:`_expression.CompoundSelect`, and

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

3034 it represents the

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

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

3037

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

3039 :class:`_expression.Alias`

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

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

3042 :class:`_expression.Alias` always

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

3044 :class:`.Subquery`

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

3046

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

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

3049 statement.

3050

3051 """

3052

3053 __visit_name__ = "subquery"

3054

3055 _is_subquery = True

3056

3057 inherit_cache = True

3058

3059 element: SelectBase

3060

3061 @classmethod

3062 def _factory(

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

3064 ) -> Subquery:

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

3066

3067 return coercions.expect(

3068 roles.SelectStatementRole, selectable

3069 ).subquery(name=name)

3070

3071 @util.deprecated(

3072 "1.4",

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

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

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

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

3077 ":func:`_expression.select` "

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

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

3080 )

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

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

3083

3084

3085class FromGrouping(GroupedElement, FromClause[_KeyColCC_co]):

3086 """Represent a grouping of a FROM clause"""

3087

3088 _traverse_internals: _TraverseInternalsType = [

3089 ("element", InternalTraversal.dp_clauseelement)

3090 ]

3091

3092 element: FromClause[_KeyColCC_co]

3093

3094 def __init__(self, element: FromClause[_KeyColCC_co]):

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

3096

3097 @util.ro_non_memoized_property

3098 def columns(self) -> _KeyColCC_co:

3099 return self.element.columns

3100

3101 @util.ro_non_memoized_property

3102 def c(self) -> _KeyColCC_co:

3103 return self.element.columns

3104

3105 @property

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

3107 return self.element.primary_key

3108

3109 @property

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

3111 return self.element.foreign_keys

3112

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

3114 return self.element.is_derived_from(fromclause)

3115

3116 def alias(

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

3118 ) -> NamedFromGrouping[_KeyColCC_co]:

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

3120

3121 def _anonymous_fromclause(

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

3123 ) -> FromGrouping:

3124 return FromGrouping(

3125 self.element._anonymous_fromclause(name=name, flat=flat)

3126 )

3127

3128 @util.ro_non_memoized_property

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

3130 return self.element._hide_froms

3131

3132 @util.ro_non_memoized_property

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

3134 return self.element._from_objects

3135

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

3137 return {"element": self.element}

3138

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

3140 self.element = state["element"]

3141

3142 if TYPE_CHECKING:

3143

3144 def self_group(

3145 self, against: Optional[OperatorType] = None

3146 ) -> Self: ...

3147

3148

3149class NamedFromGrouping(

3150 FromGrouping[_KeyColCC_co], NamedFromClause[_KeyColCC_co]

3151):

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

3153

3154 .. versionadded:: 2.0

3155

3156 """

3157

3158 inherit_cache = True

3159

3160 if TYPE_CHECKING:

3161

3162 def self_group(

3163 self, against: Optional[OperatorType] = None

3164 ) -> Self: ...

3165

3166

3167class TableClause(

3168 roles.DMLTableRole, Immutable, NamedFromClause[_ColClauseCC_co]

3169):

3170 """Represents a minimal "table" construct.

3171

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

3173 collection of columns, which are typically produced

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

3175

3176 from sqlalchemy import table, column

3177

3178 user = table(

3179 "user",

3180 column("id"),

3181 column("name"),

3182 column("description"),

3183 )

3184

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

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

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

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

3189

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

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

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

3193 It's useful

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

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

3196 is not on hand.

3197

3198 """

3199

3200 __visit_name__ = "table"

3201

3202 _traverse_internals: _TraverseInternalsType = [

3203 (

3204 "columns",

3205 InternalTraversal.dp_fromclause_canonical_column_collection,

3206 ),

3207 ("name", InternalTraversal.dp_string),

3208 ("schema", InternalTraversal.dp_string),

3209 ]

3210

3211 _is_table = True

3212

3213 fullname: str

3214

3215 implicit_returning = False

3216 """:class:`_expression.TableClause`

3217 doesn't support having a primary key or column

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

3219

3220 _columns: DedupeColumnCollection[ColumnClause[Any]]

3221

3222 @util.ro_memoized_property

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

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

3225 return None

3226

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

3228 super().__init__()

3229 self.name = name

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

3231 self.primary_key = ColumnSet() # type: ignore[misc]

3232 self.foreign_keys = set() # type: ignore[misc]

3233 for c in columns:

3234 self.append_column(c)

3235

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

3237 if schema is not None:

3238 self.schema = schema

3239 if self.schema is not None:

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

3241 else:

3242 self.fullname = self.name

3243 if kw:

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

3245

3246 if TYPE_CHECKING:

3247

3248 def with_cols(self, type_: type[_TC_co]) -> TableClause[_TC_co]: ...

3249

3250 def __str__(self) -> str:

3251 if self.schema is not None:

3252 return self.schema + "." + self.name

3253 else:

3254 return self.name

3255

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

3257 pass

3258

3259 @util.ro_memoized_property

3260 def description(self) -> str:

3261 return self.name

3262

3263 def _insert_col_impl(

3264 self,

3265 c: ColumnClause[Any],

3266 *,

3267 index: Optional[int] = None,

3268 ) -> None:

3269 existing = c.table

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

3271 raise exc.ArgumentError(

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

3273 % (c.key, existing)

3274 )

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

3276 c.table = self

3277

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

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

3280 self._insert_col_impl(column)

3281

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

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

3284 a specific position.

3285

3286 .. versionadded:: 2.1

3287

3288 """

3289 self._insert_col_impl(column, index=index)

3290

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

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

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

3294 :class:`_expression.TableClause`.

3295

3296 E.g.::

3297

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

3299

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

3301

3302 """

3303

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

3305

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

3307 def update(self) -> Update:

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

3309 :class:`_expression.TableClause`.

3310

3311 E.g.::

3312

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

3314

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

3316

3317 """

3318 return util.preloaded.sql_dml.Update(

3319 self,

3320 )

3321

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

3323 def delete(self) -> Delete:

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

3325 :class:`_expression.TableClause`.

3326

3327 E.g.::

3328

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

3330

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

3332

3333 """

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

3335

3336 @util.ro_non_memoized_property

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

3338 return [self]

3339

3340

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

3342

3343

3344class ForUpdateArg(ClauseElement):

3345 _traverse_internals: _TraverseInternalsType = [

3346 ("of", InternalTraversal.dp_clauseelement_list),

3347 ("nowait", InternalTraversal.dp_boolean),

3348 ("read", InternalTraversal.dp_boolean),

3349 ("skip_locked", InternalTraversal.dp_boolean),

3350 ("key_share", InternalTraversal.dp_boolean),

3351 ]

3352

3353 of: Optional[Sequence[ClauseElement]]

3354 nowait: bool

3355 read: bool

3356 skip_locked: bool

3357

3358 @classmethod

3359 def _from_argument(

3360 cls, with_for_update: ForUpdateParameter

3361 ) -> Optional[ForUpdateArg]:

3362 if isinstance(with_for_update, ForUpdateArg):

3363 return with_for_update

3364 elif with_for_update in (None, False):

3365 return None

3366 elif with_for_update is True:

3367 return ForUpdateArg()

3368 else:

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

3370

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

3372 return (

3373 isinstance(other, ForUpdateArg)

3374 and other.nowait == self.nowait

3375 and other.read == self.read

3376 and other.skip_locked == self.skip_locked

3377 and other.key_share == self.key_share

3378 and other.of is self.of

3379 )

3380

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

3382 return not self.__eq__(other)

3383

3384 def __hash__(self) -> int:

3385 return id(self)

3386

3387 def __init__(

3388 self,

3389 *,

3390 nowait: bool = False,

3391 read: bool = False,

3392 of: Optional[_ForUpdateOfArgument] = None,

3393 skip_locked: bool = False,

3394 key_share: bool = False,

3395 ):

3396 """Represents arguments specified to

3397 :meth:`_expression.Select.for_update`.

3398

3399 """

3400

3401 self.nowait = nowait

3402 self.read = read

3403 self.skip_locked = skip_locked

3404 self.key_share = key_share

3405 if of is not None:

3406 self.of = [

3407 coercions.expect(roles.ColumnsClauseRole, elem)

3408 for elem in util.to_list(of)

3409 ]

3410 else:

3411 self.of = None

3412

3413

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

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

3416 in a statement.

3417

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

3419 :func:`_expression.values` function.

3420

3421 .. versionadded:: 1.4

3422

3423 """

3424

3425 __visit_name__ = "values"

3426

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

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

3429

3430 _unnamed: bool

3431 _traverse_internals: _TraverseInternalsType = [

3432 ("_column_args", InternalTraversal.dp_clauseelement_list),

3433 ("_data", InternalTraversal.dp_dml_multi_values),

3434 ("name", InternalTraversal.dp_string),

3435 ("literal_binds", InternalTraversal.dp_boolean),

3436 ] + HasCTE._has_ctes_traverse_internals

3437

3438 name_cte_columns = True

3439

3440 def __init__(

3441 self,

3442 *columns: _OnlyColumnArgument[Any],

3443 name: Optional[str] = None,

3444 literal_binds: bool = False,

3445 ):

3446 super().__init__()

3447 self._column_args = tuple(

3448 coercions.expect(roles.LabeledColumnExprRole, col)

3449 for col in columns

3450 )

3451

3452 if name is None:

3453 self._unnamed = True

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

3455 else:

3456 self._unnamed = False

3457 self.name = name

3458 self.literal_binds = literal_binds

3459 self.named_with_column = not self._unnamed

3460

3461 @property

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

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

3464

3465 @util.ro_non_memoized_property

3466 def _all_selected_columns(self) -> _SelectIterable:

3467 return self._column_args

3468

3469 @_generative

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

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

3472 construct that is a copy of this

3473 one with the given name.

3474

3475 This method is a VALUES-specific specialization of the

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

3477

3478 .. seealso::

3479

3480 :ref:`tutorial_using_aliases`

3481

3482 :func:`_expression.alias`

3483

3484 """

3485 non_none_name: str

3486

3487 if name is None:

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

3489 else:

3490 non_none_name = name

3491

3492 self.name = non_none_name

3493 self.named_with_column = True

3494 self._unnamed = False

3495 return self

3496

3497 @_generative

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

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

3500 so that

3501 it renders as LATERAL.

3502

3503 .. seealso::

3504

3505 :func:`_expression.lateral`

3506

3507 """

3508 non_none_name: str

3509

3510 if name is None:

3511 non_none_name = self.name

3512 else:

3513 non_none_name = name

3514

3515 self._is_lateral = True

3516 self.name = non_none_name

3517 self._unnamed = False

3518 return self

3519

3520 @_generative

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

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

3523 adding the given data to the data list.

3524

3525 E.g.::

3526

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

3528

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

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

3531 constructor.

3532

3533 """

3534

3535 self._data += (values,)

3536 return self

3537

3538 def scalar_values(self) -> ScalarValues:

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

3540 COLUMN element in a statement.

3541

3542 .. versionadded:: 2.0.0b4

3543

3544 """

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

3546

3547 def _populate_column_collection(

3548 self,

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

3550 primary_key: ColumnSet,

3551 foreign_keys: Set[KeyedColumnElement[Any]],

3552 ) -> None:

3553 for c in self._column_args:

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

3555 _, c = c._make_proxy(

3556 self, primary_key=primary_key, foreign_keys=foreign_keys

3557 )

3558 else:

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

3560 # no memoizations of other FROM clauses.

3561 # see test_values.py -> test_auto_proxy_select_direct_col

3562 c._reset_memoizations()

3563 columns.add(c)

3564 c.table = self

3565

3566 @util.ro_non_memoized_property

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

3568 return [self]

3569

3570

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

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

3573 COLUMN element in a statement.

3574

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

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

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

3578 an ``IN`` or ``NOT IN`` condition.

3579

3580 .. versionadded:: 2.0.0b4

3581

3582 """

3583

3584 __visit_name__ = "scalar_values"

3585

3586 _traverse_internals: _TraverseInternalsType = [

3587 ("_column_args", InternalTraversal.dp_clauseelement_list),

3588 ("_data", InternalTraversal.dp_dml_multi_values),

3589 ("literal_binds", InternalTraversal.dp_boolean),

3590 ]

3591

3592 def __init__(

3593 self,

3594 columns: Sequence[NamedColumn[Any]],

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

3596 literal_binds: bool,

3597 ):

3598 super().__init__()

3599 self._column_args = columns

3600 self._data = data

3601 self.literal_binds = literal_binds

3602

3603 @property

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

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

3606

3607 def __clause_element__(self) -> ScalarValues:

3608 return self

3609

3610 if TYPE_CHECKING:

3611

3612 def self_group(

3613 self, against: Optional[OperatorType] = None

3614 ) -> Self: ...

3615

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

3617

3618

3619class SelectBase(

3620 roles.SelectStatementRole,

3621 roles.DMLSelectRole,

3622 roles.CompoundElementRole,

3623 roles.InElementRole,

3624 HasCTE,

3625 SupportsCloneAnnotations,

3626 Selectable,

3627):

3628 """Base class for SELECT statements.

3629

3630

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

3632 :class:`_expression.CompoundSelect` and

3633 :class:`_expression.TextualSelect`.

3634

3635

3636 """

3637

3638 _is_select_base = True

3639 is_select = True

3640

3641 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3642

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

3644 self._reset_memoizations()

3645

3646 @util.ro_non_memoized_property

3647 def selected_columns(

3648 self,

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

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

3651 representing the columns that

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

3653

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

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

3656 within this collection cannot be directly nested inside another SELECT

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

3658 necessary parenthesization required by SQL.

3659

3660 .. note::

3661

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

3663 include expressions established in the columns clause using the

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

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

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

3667 construct.

3668

3669 .. seealso::

3670

3671 :attr:`_sql.Select.selected_columns`

3672

3673 .. versionadded:: 1.4

3674

3675 """

3676 raise NotImplementedError()

3677

3678 def _generate_fromclause_column_proxies(

3679 self,

3680 subquery: FromClause,

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

3682 primary_key: ColumnSet,

3683 foreign_keys: Set[KeyedColumnElement[Any]],

3684 *,

3685 proxy_compound_columns: Optional[

3686 Iterable[Sequence[ColumnElement[Any]]]

3687 ] = None,

3688 ) -> None:

3689 raise NotImplementedError()

3690

3691 @util.ro_non_memoized_property

3692 def _all_selected_columns(self) -> _SelectIterable:

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

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

3695 constructs.

3696

3697 .. versionadded:: 1.4.12

3698

3699 .. seealso::

3700

3701 :attr:`_sql.SelectBase.exported_columns`

3702

3703 """

3704 raise NotImplementedError()

3705

3706 @property

3707 def exported_columns(

3708 self,

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

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

3711 that represents the "exported"

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

3713 :class:`_sql.TextClause` constructs.

3714

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

3716 object are synonymous

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

3718

3719 .. versionadded:: 1.4

3720

3721 .. seealso::

3722

3723 :attr:`_expression.Select.exported_columns`

3724

3725 :attr:`_expression.Selectable.exported_columns`

3726

3727 :attr:`_expression.FromClause.exported_columns`

3728

3729

3730 """

3731 return self.selected_columns._as_readonly()

3732

3733 def get_label_style(self) -> SelectLabelStyle:

3734 """

3735 Retrieve the current label style.

3736

3737 Implemented by subclasses.

3738

3739 """

3740 raise NotImplementedError()

3741

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

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

3744

3745 Implemented by subclasses.

3746

3747 """

3748

3749 raise NotImplementedError()

3750

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

3752 raise NotImplementedError()

3753

3754 @util.deprecated(

3755 "1.4",

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

3757 "method is deprecated and will be "

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

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

3760 )

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

3762 return self.scalar_subquery()

3763

3764 def exists(self) -> Exists:

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

3766 which can be used as a column expression.

3767

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

3769

3770 .. seealso::

3771

3772 :func:`_sql.exists`

3773

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

3775

3776 .. versionadded:: 1.4

3777

3778 """

3779 return Exists(self)

3780

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

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

3783 used as a column expression.

3784

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

3786

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

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

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

3790 an enclosing SELECT.

3791

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

3793 subquery that can be produced using the

3794 :meth:`_expression.SelectBase.subquery`

3795 method.

3796

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

3798 :meth:`_expression.SelectBase.scalar_subquery`.

3799

3800 .. seealso::

3801

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

3803

3804 """

3805 if self._label_style is not LABEL_STYLE_NONE:

3806 self = self.set_label_style(LABEL_STYLE_NONE)

3807

3808 return ScalarSelect(self)

3809

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

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

3812 subquery with a label.

3813

3814 .. seealso::

3815

3816 :meth:`_expression.SelectBase.scalar_subquery`.

3817

3818 """

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

3820

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

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

3823

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

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

3826

3827 .. seealso::

3828

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

3830

3831 """

3832 return Lateral._factory(self, name)

3833

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

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

3836

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

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

3839 SELECT statement.

3840

3841 Given a SELECT statement such as::

3842

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

3844

3845 The above statement might look like:

3846

3847 .. sourcecode:: sql

3848

3849 SELECT table.id, table.name FROM table

3850

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

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

3853 a named sub-element::

3854

3855 subq = stmt.subquery()

3856 new_stmt = select(subq)

3857

3858 The above renders as:

3859

3860 .. sourcecode:: sql

3861

3862 SELECT anon_1.id, anon_1.name

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

3864

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

3866 is equivalent to calling

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

3868 method on a FROM object; however,

3869 as a :class:`_expression.SelectBase`

3870 object is not directly FROM object,

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

3872 method provides clearer semantics.

3873

3874 .. versionadded:: 1.4

3875

3876 """

3877

3878 return Subquery._construct(

3879 self._ensure_disambiguated_names(), name=name

3880 )

3881

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

3883 def into(

3884 self,

3885 target: str,

3886 *,

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

3888 schema: Optional[str] = None,

3889 temporary: bool = False,

3890 if_not_exists: bool = False,

3891 ) -> CreateTableAs:

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

3893

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

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

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

3897

3898 Supported on all included backends, the construct emits

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

3900 emits a ``SELECT..INTO`` statement.

3901

3902 e.g.::

3903

3904 from sqlalchemy import select

3905

3906 # Create a new table from a SELECT

3907 stmt = (

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

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

3910 .into("active_users")

3911 )

3912

3913 with engine.begin() as conn:

3914 conn.execute(stmt)

3915

3916 # With optional flags

3917 stmt = (

3918 select(users.c.id)

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

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

3921 )

3922

3923 .. versionadded:: 2.1

3924

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

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

3927

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

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

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

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

3932 is created.

3933

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

3935

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

3937

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

3939

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

3941

3942 .. seealso::

3943

3944 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3945

3946 :class:`_schema.CreateTableAs`

3947

3948 """

3949 sql_ddl = util.preloaded.sql_ddl

3950

3951 return sql_ddl.CreateTableAs(

3952 self,

3953 target,

3954 metadata=metadata,

3955 schema=schema,

3956 temporary=temporary,

3957 if_not_exists=if_not_exists,

3958 )

3959

3960 def _ensure_disambiguated_names(self) -> Self:

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

3962 disambiguated in some way, if possible.

3963

3964 """

3965

3966 raise NotImplementedError()

3967

3968 def alias(

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

3970 ) -> Subquery:

3971 """Return a named subquery against this

3972 :class:`_expression.SelectBase`.

3973

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

3975 :class:`_expression.FromClause`),

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

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

3978 :class:`_expression.FromClause`.

3979

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

3981 method is now

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

3983

3984 """

3985 return self.subquery(name=name)

3986

3987

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

3989

3990

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

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

3993

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

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

3996 compound selects.

3997

3998 """

3999

4000 __visit_name__ = "select_statement_grouping"

4001 _traverse_internals: _TraverseInternalsType = [

4002 ("element", InternalTraversal.dp_clauseelement)

4003 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

4004

4005 _is_select_container = True

4006

4007 element: _SB

4008

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

4010 self.element = cast(

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

4012 )

4013

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

4015 new_element = self.element._ensure_disambiguated_names()

4016 if new_element is not self.element:

4017 return SelectStatementGrouping(new_element)

4018 else:

4019 return self

4020

4021 def get_label_style(self) -> SelectLabelStyle:

4022 return self.element.get_label_style()

4023

4024 def set_label_style(

4025 self, label_style: SelectLabelStyle

4026 ) -> SelectStatementGrouping[_SB]:

4027 return SelectStatementGrouping(

4028 self.element.set_label_style(label_style)

4029 )

4030

4031 @property

4032 def select_statement(self) -> _SB:

4033 return self.element

4034

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

4036 return self

4037

4038 if TYPE_CHECKING:

4039

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

4041

4042 # def _generate_columns_plus_names(

4043 # self, anon_for_dupe_key: bool

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

4045 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

4046

4047 def _generate_fromclause_column_proxies(

4048 self,

4049 subquery: FromClause,

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

4051 primary_key: ColumnSet,

4052 foreign_keys: Set[KeyedColumnElement[Any]],

4053 *,

4054 proxy_compound_columns: Optional[

4055 Iterable[Sequence[ColumnElement[Any]]]

4056 ] = None,

4057 ) -> None:

4058 self.element._generate_fromclause_column_proxies(

4059 subquery,

4060 columns,

4061 proxy_compound_columns=proxy_compound_columns,

4062 primary_key=primary_key,

4063 foreign_keys=foreign_keys,

4064 )

4065

4066 @util.ro_non_memoized_property

4067 def _all_selected_columns(self) -> _SelectIterable:

4068 return self.element._all_selected_columns

4069

4070 @util.ro_non_memoized_property

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

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

4073 representing the columns that

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

4075 :class:`_sql.TextClause` constructs.

4076

4077 .. versionadded:: 1.4

4078

4079 .. seealso::

4080

4081 :attr:`_sql.Select.selected_columns`

4082

4083 """

4084 return self.element.selected_columns

4085

4086 @util.ro_non_memoized_property

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

4088 return self.element._from_objects

4089

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

4091 return self.element._scalar_type()

4092

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

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

4095 raise NotImplementedError

4096

4097

4098class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4100 added.

4101

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

4103 :class:`_expression.CompoundSelect`

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

4105 rendering can be controlled. Compare to

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

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

4108 and is also a SELECT construct,

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

4110 only wrapped as a subquery.

4111

4112 """

4113

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

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

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

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

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

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

4120 _for_update_arg: Optional[ForUpdateArg] = None

4121

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

4123 self._label_style = _label_style

4124

4125 @_generative

4126 def with_for_update(

4127 self,

4128 *,

4129 nowait: bool = False,

4130 read: bool = False,

4131 of: Optional[_ForUpdateOfArgument] = None,

4132 skip_locked: bool = False,

4133 key_share: bool = False,

4134 ) -> Self:

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

4136 :class:`_expression.GenerativeSelect`.

4137

4138 E.g.::

4139

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

4141

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

4143 render a statement like:

4144

4145 .. sourcecode:: sql

4146

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

4148

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

4150 would produce:

4151

4152 .. sourcecode:: sql

4153

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

4155

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

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

4158 provided which allow for common database-specific

4159 variants.

4160

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

4162 Database and PostgreSQL dialects.

4163

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

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

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

4167

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

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

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

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

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

4173 table or as a column depending on backend.

4174

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

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

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

4178

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

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

4181 on the PostgreSQL dialect.

4182

4183 """

4184 self._for_update_arg = ForUpdateArg(

4185 nowait=nowait,

4186 read=read,

4187 of=of,

4188 skip_locked=skip_locked,

4189 key_share=key_share,

4190 )

4191 return self

4192

4193 def get_label_style(self) -> SelectLabelStyle:

4194 """

4195 Retrieve the current label style.

4196

4197 .. versionadded:: 1.4

4198

4199 """

4200 return self._label_style

4201

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

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

4204

4205 There are three "label styles" available,

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

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

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

4209 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4210

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

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

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

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

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

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

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

4218 that the impact of this labeling is minimal.

4219

4220 The rationale for disambiguation is mostly so that all column

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

4222 collection when a subquery is created.

4223

4224 .. versionadded:: 1.4 - the

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

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

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

4228

4229 .. seealso::

4230

4231 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4232

4233 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4234

4235 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4236

4237 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4238

4239 """

4240 if self._label_style is not style:

4241 self = self._generate()

4242 self._label_style = style

4243 return self

4244

4245 @property

4246 def _group_by_clause(self) -> ClauseList:

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

4248 return ClauseList._construct_raw(

4249 operators.comma_op, self._group_by_clauses

4250 )

4251

4252 @property

4253 def _order_by_clause(self) -> ClauseList:

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

4255 return ClauseList._construct_raw(

4256 operators.comma_op, self._order_by_clauses

4257 )

4258

4259 def _offset_or_limit_clause(

4260 self,

4261 element: _LimitOffsetType,

4262 name: Optional[str] = None,

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

4264 ) -> ColumnElement[Any]:

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

4266

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

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

4269

4270 """

4271 return coercions.expect(

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

4273 )

4274

4275 @overload

4276 def _offset_or_limit_clause_asint(

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

4278 ) -> NoReturn: ...

4279

4280 @overload

4281 def _offset_or_limit_clause_asint(

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

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

4284

4285 def _offset_or_limit_clause_asint(

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

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

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

4289 integer.

4290

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

4292 parameter. Otherwise, a compilation error is raised.

4293

4294 """

4295 if clause is None:

4296 return None

4297 try:

4298 value = clause._limit_offset_value

4299 except AttributeError as err:

4300 raise exc.CompileError(

4301 "This SELECT structure does not use a simple "

4302 "integer value for %s" % attrname

4303 ) from err

4304 else:

4305 return util.asint(value)

4306

4307 @property

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

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

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

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

4312 isn't currently set to an integer.

4313

4314 """

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

4316

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

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

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

4320 """

4321 return isinstance(clause, _OffsetLimitParam)

4322

4323 @property

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

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

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

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

4328 offset isn't currently set to an integer.

4329

4330 """

4331 return self._offset_or_limit_clause_asint(

4332 self._offset_clause, "offset"

4333 )

4334

4335 @property

4336 def _has_row_limiting_clause(self) -> bool:

4337 return (

4338 self._limit_clause is not None

4339 or self._offset_clause is not None

4340 or self._fetch_clause is not None

4341 )

4342

4343 @_generative

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

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

4346 applied.

4347

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

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

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

4351 functionality.

4352

4353 .. note::

4354

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

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

4357

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

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

4360

4361 .. seealso::

4362

4363 :meth:`_sql.GenerativeSelect.fetch`

4364

4365 :meth:`_sql.GenerativeSelect.offset`

4366

4367 """

4368

4369 self._fetch_clause = self._fetch_clause_options = None

4370 self._limit_clause = self._offset_or_limit_clause(limit)

4371 return self

4372

4373 @_generative

4374 def fetch(

4375 self,

4376 count: _LimitOffsetType,

4377 with_ties: bool = False,

4378 percent: bool = False,

4379 **dialect_kw: Any,

4380 ) -> Self:

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

4382 applied.

4383

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

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

4386 select. This functionality is is currently implemented for Oracle

4387 Database, PostgreSQL, MSSQL.

4388

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

4390

4391 .. note::

4392

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

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

4395

4396 .. versionadded:: 1.4

4397

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

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

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

4401 Pass ``None`` to reset it.

4402

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

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

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

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

4407

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

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

4410

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

4412 may be accepted by dialects.

4413

4414 .. versionadded:: 2.0.41

4415

4416 .. seealso::

4417

4418 :meth:`_sql.GenerativeSelect.limit`

4419

4420 :meth:`_sql.GenerativeSelect.offset`

4421

4422 """

4423 self._validate_dialect_kwargs(dialect_kw)

4424 self._limit_clause = None

4425 if count is None:

4426 self._fetch_clause = self._fetch_clause_options = None

4427 else:

4428 self._fetch_clause = self._offset_or_limit_clause(count)

4429 self._fetch_clause_options = {

4430 "with_ties": with_ties,

4431 "percent": percent,

4432 }

4433 return self

4434

4435 @_generative

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

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

4438 applied.

4439

4440

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

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

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

4444 functionality.

4445

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

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

4448

4449 .. seealso::

4450

4451 :meth:`_sql.GenerativeSelect.limit`

4452

4453 :meth:`_sql.GenerativeSelect.fetch`

4454

4455 """

4456

4457 self._offset_clause = self._offset_or_limit_clause(offset)

4458 return self

4459

4460 @_generative

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

4462 def slice(

4463 self,

4464 start: int,

4465 stop: int,

4466 ) -> Self:

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

4468

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

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

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

4472 query.

4473

4474 For example, ::

4475

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

4477

4478 renders as

4479

4480 .. sourcecode:: sql

4481

4482 SELECT users.id AS users_id,

4483 users.name AS users_name

4484 FROM users ORDER BY users.id

4485 LIMIT ? OFFSET ?

4486 (2, 1)

4487

4488 .. note::

4489

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

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

4492

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

4494 method generalized from the ORM.

4495

4496 .. seealso::

4497

4498 :meth:`_sql.GenerativeSelect.limit`

4499

4500 :meth:`_sql.GenerativeSelect.offset`

4501

4502 :meth:`_sql.GenerativeSelect.fetch`

4503

4504 """

4505 sql_util = util.preloaded.sql_util

4506 self._fetch_clause = self._fetch_clause_options = None

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

4508 self._limit_clause, self._offset_clause, start, stop

4509 )

4510 return self

4511

4512 @_generative

4513 def order_by(

4514 self,

4515 __first: Union[

4516 Literal[None, _NoArg.NO_ARG],

4517 _ColumnExpressionOrStrLabelArgument[Any],

4518 roles.OrderByRole,

4519 ] = _NoArg.NO_ARG,

4520 /,

4521 *clauses: Union[

4522 _ColumnExpressionOrStrLabelArgument[Any], roles.OrderByRole

4523 ],

4524 ) -> Self:

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

4526 criteria applied.

4527

4528 e.g.::

4529

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

4531

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

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

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

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

4536

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

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

4539

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

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

4542

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

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

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

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

4547 label matching in ORDER BY and GROUP BY expressions.

4548

4549 .. seealso::

4550

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

4552

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

4554

4555 """

4556

4557 if not clauses and __first is None:

4558 self._order_by_clauses = ()

4559 elif __first is not _NoArg.NO_ARG:

4560 self._order_by_clauses += tuple(

4561 coercions.expect(

4562 roles.OrderByRole, clause, apply_propagate_attrs=self

4563 )

4564 for clause in (__first,) + clauses

4565 )

4566 return self

4567

4568 @_generative

4569 def group_by(

4570 self,

4571 __first: Union[

4572 Literal[None, _NoArg.NO_ARG],

4573 _ColumnExpressionOrStrLabelArgument[Any],

4574 ] = _NoArg.NO_ARG,

4575 /,

4576 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4577 ) -> Self:

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

4579 criterion applied.

4580

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

4582

4583 e.g.::

4584

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

4586

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

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

4589

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

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

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

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

4594 label matching in ORDER BY and GROUP BY expressions.

4595

4596 .. seealso::

4597

4598 :ref:`tutorial_group_by_w_aggregates` - in the

4599 :ref:`unified_tutorial`

4600

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

4602

4603 """ # noqa: E501

4604

4605 if not clauses and __first is None:

4606 self._group_by_clauses = ()

4607 elif __first is not _NoArg.NO_ARG:

4608 self._group_by_clauses += tuple(

4609 coercions.expect(

4610 roles.GroupByRole, clause, apply_propagate_attrs=self

4611 )

4612 for clause in (__first,) + clauses

4613 )

4614 return self

4615

4616

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

4618class CompoundSelectState(CompileState):

4619 @util.memoized_property

4620 def _label_resolve_dict(

4621 self,

4622 ) -> Tuple[

4623 Dict[str, ColumnElement[Any]],

4624 Dict[str, ColumnElement[Any]],

4625 Dict[str, ColumnElement[Any]],

4626 ]:

4627 # TODO: this is hacky and slow

4628 hacky_subquery = self.statement.subquery()

4629 hacky_subquery.named_with_column = False

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

4631 return d, d, d

4632

4633

4634class _CompoundSelectKeyword(Enum):

4635 UNION = "UNION"

4636 UNION_ALL = "UNION ALL"

4637 EXCEPT = "EXCEPT"

4638 EXCEPT_ALL = "EXCEPT ALL"

4639 INTERSECT = "INTERSECT"

4640 INTERSECT_ALL = "INTERSECT ALL"

4641

4642

4643class CompoundSelect(

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

4645):

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

4647 SELECT-based set operations.

4648

4649

4650 .. seealso::

4651

4652 :func:`_expression.union`

4653

4654 :func:`_expression.union_all`

4655

4656 :func:`_expression.intersect`

4657

4658 :func:`_expression.intersect_all`

4659

4660 :func:`_expression.except`

4661

4662 :func:`_expression.except_all`

4663

4664 """

4665

4666 __visit_name__ = "compound_select"

4667

4668 _traverse_internals: _TraverseInternalsType = (

4669 [

4670 ("selects", InternalTraversal.dp_clauseelement_list),

4671 ("_limit_clause", InternalTraversal.dp_clauseelement),

4672 ("_offset_clause", InternalTraversal.dp_clauseelement),

4673 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4674 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4675 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4676 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4677 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4678 ("keyword", InternalTraversal.dp_string),

4679 ]

4680 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4681 + HasCTE._has_ctes_traverse_internals

4682 + DialectKWArgs._dialect_kwargs_traverse_internals

4683 + ExecutableStatement._executable_traverse_internals

4684 )

4685

4686 selects: List[SelectBase]

4687

4688 _is_from_container = True

4689 _auto_correlate = False

4690

4691 def __init__(

4692 self,

4693 keyword: _CompoundSelectKeyword,

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

4695 ):

4696 self.keyword = keyword

4697 self.selects = [

4698 coercions.expect(

4699 roles.CompoundElementRole, s, apply_propagate_attrs=self

4700 ).self_group(against=self)

4701 for s in selects

4702 ]

4703

4704 GenerativeSelect.__init__(self)

4705

4706 @classmethod

4707 def _create_union(

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

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

4710 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4711

4712 @classmethod

4713 def _create_union_all(

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

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

4716 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4717

4718 @classmethod

4719 def _create_except(

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

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

4722 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4723

4724 @classmethod

4725 def _create_except_all(

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

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

4728 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4729

4730 @classmethod

4731 def _create_intersect(

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

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

4734 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4735

4736 @classmethod

4737 def _create_intersect_all(

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

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

4740 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4741

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

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

4744

4745 def self_group(

4746 self, against: Optional[OperatorType] = None

4747 ) -> GroupedElement:

4748 return SelectStatementGrouping(self)

4749

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

4751 for s in self.selects:

4752 if s.is_derived_from(fromclause):

4753 return True

4754 return False

4755

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

4757 if self._label_style is not style:

4758 self = self._generate()

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

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

4761

4762 return self

4763

4764 def _ensure_disambiguated_names(self) -> Self:

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

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

4767 self = self._generate()

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

4769

4770 return self

4771

4772 def _generate_fromclause_column_proxies(

4773 self,

4774 subquery: FromClause,

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

4776 primary_key: ColumnSet,

4777 foreign_keys: Set[KeyedColumnElement[Any]],

4778 *,

4779 proxy_compound_columns: Optional[

4780 Iterable[Sequence[ColumnElement[Any]]]

4781 ] = None,

4782 ) -> None:

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

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

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

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

4787 # generate below.

4788 select_0 = self.selects[0]

4789

4790 if self._label_style is not LABEL_STYLE_DEFAULT:

4791 select_0 = select_0.set_label_style(self._label_style)

4792

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

4794 # derived columns place a 'weight' annotation corresponding

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

4796 # that the corresponding_column() operation can resolve

4797 # conflicts

4798 extra_col_iterator = zip(

4799 *[

4800 [

4801 c._annotate(dd)

4802 for c in stmt._all_selected_columns

4803 if is_column_element(c)

4804 ]

4805 for dd, stmt in [

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

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

4808 ]

4809 ]

4810 )

4811

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

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

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

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

4816 # compound

4817 select_0._generate_fromclause_column_proxies(

4818 subquery,

4819 columns,

4820 proxy_compound_columns=extra_col_iterator,

4821 primary_key=primary_key,

4822 foreign_keys=foreign_keys,

4823 )

4824

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

4826 super()._refresh_for_new_column(column)

4827 for select in self.selects:

4828 select._refresh_for_new_column(column)

4829

4830 @util.ro_non_memoized_property

4831 def _all_selected_columns(self) -> _SelectIterable:

4832 return self.selects[0]._all_selected_columns

4833

4834 @util.ro_non_memoized_property

4835 def selected_columns(

4836 self,

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

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

4839 representing the columns that

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

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

4842

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

4844 :attr:`_expression.CompoundSelect.selected_columns`

4845 attribute returns the selected

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

4847 statements within the set operation.

4848

4849 .. seealso::

4850

4851 :attr:`_sql.Select.selected_columns`

4852

4853 .. versionadded:: 1.4

4854

4855 """

4856 return self.selects[0].selected_columns

4857

4858

4859# backwards compat

4860for elem in _CompoundSelectKeyword:

4861 setattr(CompoundSelect, elem.name, elem)

4862

4863

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

4865class SelectState(util.MemoizedSlots, CompileState):

4866 __slots__ = (

4867 "from_clauses",

4868 "froms",

4869 "columns_plus_names",

4870 "_label_resolve_dict",

4871 )

4872

4873 if TYPE_CHECKING:

4874 default_select_compile_options: CacheableOptions

4875 else:

4876

4877 class default_select_compile_options(CacheableOptions):

4878 _cache_key_traversal = []

4879

4880 if TYPE_CHECKING:

4881

4882 @classmethod

4883 def get_plugin_class(

4884 cls, statement: Executable

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

4886

4887 def __init__(

4888 self,

4889 statement: Select[Unpack[TupleAny]],

4890 compiler: SQLCompiler,

4891 **kw: Any,

4892 ):

4893 self.statement = statement

4894 self.from_clauses = statement._from_obj

4895

4896 for memoized_entities in statement._memoized_select_entities:

4897 self._setup_joins(

4898 memoized_entities._setup_joins, memoized_entities._raw_columns

4899 )

4900

4901 if statement._setup_joins:

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

4903

4904 self.froms = self._get_froms(statement)

4905

4906 self.columns_plus_names = statement._generate_columns_plus_names(True)

4907

4908 @classmethod

4909 def _plugin_not_implemented(cls) -> NoReturn:

4910 raise NotImplementedError(

4911 "The default SELECT construct without plugins does not "

4912 "implement this method."

4913 )

4914

4915 @classmethod

4916 def get_column_descriptions(

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

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

4919 return [

4920 {

4921 "name": name,

4922 "type": element.type,

4923 "expr": element,

4924 }

4925 for _, name, _, element, _ in (

4926 statement._generate_columns_plus_names(False)

4927 )

4928 ]

4929

4930 @classmethod

4931 def from_statement(

4932 cls,

4933 statement: Select[Unpack[TupleAny]],

4934 from_statement: roles.ReturnsRowsRole,

4935 ) -> ExecutableReturnsRows:

4936 cls._plugin_not_implemented()

4937

4938 @classmethod

4939 def get_columns_clause_froms(

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

4941 ) -> List[FromClause]:

4942 return cls._normalize_froms(

4943 itertools.chain.from_iterable(

4944 element._from_objects for element in statement._raw_columns

4945 )

4946 )

4947

4948 @classmethod

4949 def _column_naming_convention(

4950 cls, label_style: SelectLabelStyle

4951 ) -> _LabelConventionCallable:

4952 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4953

4954 dedupe = label_style is not LABEL_STYLE_NONE

4955

4956 pa = prefix_anon_map()

4957 names = set()

4958

4959 def go(

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

4961 col_name: Optional[str] = None,

4962 ) -> Optional[str]:

4963 if is_text_clause(c):

4964 return None

4965 elif TYPE_CHECKING:

4966 assert is_column_element(c)

4967

4968 if not dedupe:

4969 name = c._proxy_key

4970 if name is None:

4971 name = "_no_label"

4972 return name

4973

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

4975

4976 if name is None:

4977 name = "_no_label"

4978 if name in names:

4979 return c._anon_label(name) % pa

4980 else:

4981 names.add(name)

4982 return name

4983

4984 elif name in names:

4985 return (

4986 c._anon_tq_key_label % pa

4987 if table_qualified

4988 else c._anon_key_label % pa

4989 )

4990 else:

4991 names.add(name)

4992 return name

4993

4994 return go

4995

4996 def _get_froms(

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

4998 ) -> List[FromClause]:

4999 ambiguous_table_name_map: _AmbiguousTableNameMap

5000 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

5001

5002 return self._normalize_froms(

5003 itertools.chain(

5004 self.from_clauses,

5005 itertools.chain.from_iterable(

5006 [

5007 element._from_objects

5008 for element in statement._raw_columns

5009 ]

5010 ),

5011 itertools.chain.from_iterable(

5012 [

5013 element._from_objects

5014 for element in statement._where_criteria

5015 ]

5016 ),

5017 ),

5018 check_statement=statement,

5019 ambiguous_table_name_map=ambiguous_table_name_map,

5020 )

5021

5022 @classmethod

5023 def _normalize_froms(

5024 cls,

5025 iterable_of_froms: Iterable[FromClause],

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

5027 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

5028 ) -> List[FromClause]:

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

5030 would actually render in the FROM clause of a SELECT.

5031

5032 This does the job of checking for JOINs, tables, etc. that are in fact

5033 overlapping due to cloning, adaption, present in overlapping joins,

5034 etc.

5035

5036 """

5037 seen: Set[FromClause] = set()

5038 froms: List[FromClause] = []

5039

5040 for item in iterable_of_froms:

5041 if is_subquery(item) and item.element is check_statement:

5042 raise exc.InvalidRequestError(

5043 "select() construct refers to itself as a FROM"

5044 )

5045

5046 if not seen.intersection(item._cloned_set):

5047 froms.append(item)

5048 seen.update(item._cloned_set)

5049

5050 if froms:

5051 toremove = set(

5052 itertools.chain.from_iterable(

5053 [_expand_cloned(f._hide_froms) for f in froms]

5054 )

5055 )

5056 if toremove:

5057 # filter out to FROM clauses not in the list,

5058 # using a list to maintain ordering

5059 froms = [f for f in froms if f not in toremove]

5060

5061 if ambiguous_table_name_map is not None:

5062 ambiguous_table_name_map.update(

5063 (

5064 fr.name,

5065 _anonymous_label.safe_construct(

5066 hash(fr.name), fr.name

5067 ),

5068 )

5069 for item in froms

5070 for fr in item._from_objects

5071 if is_table(fr)

5072 and fr.schema

5073 and fr.name not in ambiguous_table_name_map

5074 )

5075

5076 return froms

5077

5078 def _get_display_froms(

5079 self,

5080 explicit_correlate_froms: Optional[Sequence[FromClause]] = None,

5081 implicit_correlate_froms: Optional[Sequence[FromClause]] = None,

5082 ) -> List[FromClause]:

5083 """Return the full list of 'from' clauses to be displayed.

5084

5085 Takes into account a set of existing froms which may be

5086 rendered in the FROM clause of enclosing selects; this Select

5087 may want to leave those absent if it is automatically

5088 correlating.

5089

5090 """

5091 statement = self.statement

5092 return self._correlate_froms(

5093 self.froms,

5094 statement,

5095 statement._correlate,

5096 statement._correlate_except,

5097 statement._auto_correlate,

5098 explicit_correlate_froms,

5099 implicit_correlate_froms,

5100 )

5101

5102 @classmethod

5103 def _correlate_froms(

5104 cls,

5105 froms: List[FromClause],

5106 statement: Select[Unpack[TupleAny]],

5107 to_correlate: Optional[Sequence[FromClause]],

5108 to_correlate_except: Optional[Sequence[FromClause]],

5109 auto_correlate: bool,

5110 explicit_correlate_froms: Optional[Sequence[FromClause]],

5111 implicit_correlate_froms: Optional[Sequence[FromClause]],

5112 ) -> List[FromClause]:

5113 """Given a list of FROM clauses and correlation settings, return

5114 the list of FROM clauses that remain after correlation.

5115

5116 This is the implementation for :meth:`.SelectState._get_display_froms`

5117 that is also used by the ORM, which needs to know which FROM clauses

5118 will be correlated before it has generated the final statement.

5119

5120 For ``explicit_correlate_froms`` and ``implicit_correlate_froms``,

5121 see :meth:`.SQLCompiler._enclosing_correlate_froms`.

5122

5123 """

5124

5125 if to_correlate:

5126 to_remove = _cloned_intersection(

5127 _cloned_intersection(froms, explicit_correlate_froms or ()),

5128 to_correlate,

5129 )

5130 froms = [f for f in froms if f not in to_remove]

5131

5132 if to_correlate_except is not None:

5133 to_remove = _cloned_difference(

5134 _cloned_intersection(froms, explicit_correlate_froms or ()),

5135 to_correlate_except,

5136 )

5137 froms = [f for f in froms if f not in to_remove]

5138

5139 if auto_correlate and implicit_correlate_froms and len(froms) > 1:

5140 to_remove = _cloned_intersection(froms, implicit_correlate_froms)

5141 froms = [f for f in froms if f not in to_remove]

5142

5143 if not len(froms):

5144 raise exc.InvalidRequestError(

5145 "Select statement '%r"

5146 "' returned no FROM clauses "

5147 "due to auto-correlation; "

5148 "specify correlate(<tables>) "

5149 "to control correlation "

5150 "manually." % statement

5151 )

5152

5153 return froms

5154

5155 def _memoized_attr__label_resolve_dict(

5156 self,

5157 ) -> Tuple[

5158 Dict[str, ColumnElement[Any]],

5159 Dict[str, ColumnElement[Any]],

5160 Dict[str, ColumnElement[Any]],

5161 ]:

5162 with_cols: Dict[str, ColumnElement[Any]] = {

5163 c._tq_label or c.key: c

5164 for c in self.statement._all_selected_columns

5165 if c._allow_label_resolve

5166 }

5167 only_froms: Dict[str, ColumnElement[Any]] = {

5168 c.key: c # type: ignore[misc]

5169 for c in _select_iterables(self.froms)

5170 if c._allow_label_resolve

5171 }

5172 only_cols: Dict[str, ColumnElement[Any]] = with_cols.copy()

5173 for key, value in only_froms.items():

5174 with_cols.setdefault(key, value)

5175

5176 return with_cols, only_froms, only_cols

5177

5178 @classmethod

5179 def _get_filter_by_entities(

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

5181 ) -> Collection[

5182 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5183 ]:

5184 """Return all entities to search for filter_by() attributes.

5185

5186 This includes:

5187

5188 * All joined entities from _setup_joins

5189 * Memoized entities from previous operations (e.g.,

5190 before with_only_columns)

5191 * Explicit FROM objects from _from_obj

5192 * Entities inferred from _raw_columns

5193

5194 .. versionadded:: 2.1

5195

5196 """

5197 entities: set[

5198 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5199 ]

5200

5201 entities = set(

5202 join_element[0] for join_element in statement._setup_joins

5203 )

5204

5205 for memoized in statement._memoized_select_entities:

5206 entities.update(

5207 join_element[0] for join_element in memoized._setup_joins

5208 )

5209

5210 entities.update(statement._from_obj)

5211

5212 for col in statement._raw_columns:

5213 entities.update(col._from_objects)

5214

5215 return entities

5216

5217 @classmethod

5218 def all_selected_columns(

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

5220 ) -> _SelectIterable:

5221 return [c for c in _select_iterables(statement._raw_columns)]

5222

5223 def _setup_joins(

5224 self,

5225 args: Tuple[_SetupJoinsElement, ...],

5226 raw_columns: List[_ColumnsClauseElement],

5227 ) -> None:

5228 for right, onclause, left, flags in args:

5229 if TYPE_CHECKING:

5230 if onclause is not None:

5231 assert isinstance(onclause, ColumnElement)

5232

5233 explicit_left = left

5234 isouter = flags["isouter"]

5235 full = flags["full"]

5236

5237 if left is None:

5238 (

5239 left,

5240 replace_from_obj_index,

5241 ) = self._join_determine_implicit_left_side(

5242 raw_columns, left, right, onclause

5243 )

5244 else:

5245 replace_from_obj_index = self._join_place_explicit_left_side(

5246 left

5247 )

5248

5249 # these assertions can be made here, as if the right/onclause

5250 # contained ORM elements, the select() statement would have been

5251 # upgraded to an ORM select, and this method would not be called;

5252 # orm.context.ORMSelectCompileState._join() would be

5253 # used instead.

5254 if TYPE_CHECKING:

5255 assert isinstance(right, FromClause)

5256 if onclause is not None:

5257 assert isinstance(onclause, ColumnElement)

5258

5259 if replace_from_obj_index is not None:

5260 # splice into an existing element in the

5261 # self._from_obj list

5262 left_clause = self.from_clauses[replace_from_obj_index]

5263

5264 if explicit_left is not None and onclause is None:

5265 onclause = Join._join_condition(explicit_left, right)

5266

5267 self.from_clauses = (

5268 self.from_clauses[:replace_from_obj_index]

5269 + (

5270 Join(

5271 left_clause,

5272 right,

5273 onclause,

5274 isouter=isouter,

5275 full=full,

5276 ),

5277 )

5278 + self.from_clauses[replace_from_obj_index + 1 :]

5279 )

5280 else:

5281 assert left is not None

5282 self.from_clauses = self.from_clauses + (

5283 Join(left, right, onclause, isouter=isouter, full=full),

5284 )

5285

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

5287 def _join_determine_implicit_left_side(

5288 self,

5289 raw_columns: List[_ColumnsClauseElement],

5290 left: Optional[FromClause],

5291 right: _JoinTargetElement,

5292 onclause: Optional[ColumnElement[Any]],

5293 ) -> Tuple[Optional[FromClause], Optional[int]]:

5294 """When join conditions don't express the left side explicitly,

5295 determine if an existing FROM or entity in this query

5296 can serve as the left hand side.

5297

5298 """

5299

5300 sql_util = util.preloaded.sql_util

5301

5302 replace_from_obj_index: Optional[int] = None

5303

5304 from_clauses = self.from_clauses

5305

5306 if from_clauses:

5307 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5308 from_clauses, right, onclause

5309 )

5310

5311 if len(indexes) == 1:

5312 replace_from_obj_index = indexes[0]

5313 left = from_clauses[replace_from_obj_index]

5314 else:

5315 potential = {}

5316 statement = self.statement

5317

5318 for from_clause in itertools.chain(

5319 itertools.chain.from_iterable(

5320 [element._from_objects for element in raw_columns]

5321 ),

5322 itertools.chain.from_iterable(

5323 [

5324 element._from_objects

5325 for element in statement._where_criteria

5326 ]

5327 ),

5328 ):

5329 potential[from_clause] = ()

5330

5331 all_clauses = list(potential.keys())

5332 indexes = sql_util.find_left_clause_to_join_from(

5333 all_clauses, right, onclause

5334 )

5335

5336 if len(indexes) == 1:

5337 left = all_clauses[indexes[0]]

5338

5339 if len(indexes) > 1:

5340 raise exc.InvalidRequestError(

5341 "Can't determine which FROM clause to join "

5342 "from, there are multiple FROMS which can "

5343 "join to this entity. Please use the .select_from() "

5344 "method to establish an explicit left side, as well as "

5345 "providing an explicit ON clause if not present already to "

5346 "help resolve the ambiguity."

5347 )

5348 elif not indexes:

5349 raise exc.InvalidRequestError(

5350 "Don't know how to join to %r. "

5351 "Please use the .select_from() "

5352 "method to establish an explicit left side, as well as "

5353 "providing an explicit ON clause if not present already to "

5354 "help resolve the ambiguity." % (right,)

5355 )

5356 return left, replace_from_obj_index

5357

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

5359 def _join_place_explicit_left_side(

5360 self, left: FromClause

5361 ) -> Optional[int]:

5362 replace_from_obj_index: Optional[int] = None

5363

5364 sql_util = util.preloaded.sql_util

5365

5366 from_clauses = list(self.statement._iterate_from_elements())

5367

5368 if from_clauses:

5369 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5370 self.from_clauses, left

5371 )

5372 else:

5373 indexes = []

5374

5375 if len(indexes) > 1:

5376 raise exc.InvalidRequestError(

5377 "Can't identify which entity in which to assign the "

5378 "left side of this join. Please use a more specific "

5379 "ON clause."

5380 )

5381

5382 # have an index, means the left side is already present in

5383 # an existing FROM in the self._from_obj tuple

5384 if indexes:

5385 replace_from_obj_index = indexes[0]

5386

5387 # no index, means we need to add a new element to the

5388 # self._from_obj tuple

5389

5390 return replace_from_obj_index

5391

5392

5393class _SelectFromElements:

5394 __slots__ = ()

5395

5396 _raw_columns: List[_ColumnsClauseElement]

5397 _where_criteria: Tuple[ColumnElement[Any], ...]

5398 _from_obj: Tuple[FromClause, ...]

5399

5400 def _iterate_from_elements(self) -> Iterator[FromClause]:

5401 # note this does not include elements

5402 # in _setup_joins

5403

5404 seen = set()

5405 for element in self._raw_columns:

5406 for fr in element._from_objects:

5407 if fr in seen:

5408 continue

5409 seen.add(fr)

5410 yield fr

5411 for element in self._where_criteria:

5412 for fr in element._from_objects:

5413 if fr in seen:

5414 continue

5415 seen.add(fr)

5416 yield fr

5417 for element in self._from_obj:

5418 if element in seen:

5419 continue

5420 seen.add(element)

5421 yield element

5422

5423

5424class _MemoizedSelectEntities(

5425 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5426):

5427 """represents partial state from a Select object, for the case

5428 where Select.columns() has redefined the set of columns/entities the

5429 statement will be SELECTing from. This object represents

5430 the entities from the SELECT before that transformation was applied,

5431 so that transformations that were made in terms of the SELECT at that

5432 time, such as join() as well as options(), can access the correct context.

5433

5434 In previous SQLAlchemy versions, this wasn't needed because these

5435 constructs calculated everything up front, like when you called join()

5436 or options(), it did everything to figure out how that would translate

5437 into specific SQL constructs that would be ready to send directly to the

5438 SQL compiler when needed. But as of

5439 1.4, all of that stuff is done in the compilation phase, during the

5440 "compile state" portion of the process, so that the work can all be

5441 cached. So it needs to be able to resolve joins/options2 based on what

5442 the list of entities was when those methods were called.

5443

5444

5445 """

5446

5447 __visit_name__ = "memoized_select_entities"

5448

5449 _traverse_internals: _TraverseInternalsType = [

5450 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5451 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5452 ("_with_options", InternalTraversal.dp_executable_options),

5453 ]

5454

5455 _is_clone_of: Optional[ClauseElement]

5456 _raw_columns: List[_ColumnsClauseElement]

5457 _setup_joins: Tuple[_SetupJoinsElement, ...]

5458 _with_options: Tuple[ExecutableOption, ...]

5459

5460 _annotations = util.EMPTY_DICT

5461

5462 def _clone(self, **kw: Any) -> Self:

5463 c = self.__class__.__new__(self.__class__)

5464 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5465

5466 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5467 return c

5468

5469 @classmethod

5470 def _generate_for_statement(

5471 cls, select_stmt: Select[Unpack[TupleAny]]

5472 ) -> None:

5473 if select_stmt._setup_joins or select_stmt._with_options:

5474 self = _MemoizedSelectEntities()

5475 self._raw_columns = select_stmt._raw_columns

5476 self._setup_joins = select_stmt._setup_joins

5477 self._with_options = select_stmt._with_options

5478

5479 select_stmt._memoized_select_entities += (self,)

5480 select_stmt._raw_columns = []

5481 select_stmt._setup_joins = select_stmt._with_options = ()

5482

5483

5484class Select(

5485 HasPrefixes,

5486 HasSuffixes,

5487 HasHints,

5488 HasCompileState,

5489 HasSyntaxExtensions[

5490 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5491 ],

5492 _SelectFromElements,

5493 GenerativeSelect,

5494 TypedReturnsRows[Unpack[_Ts]],

5495):

5496 """Represents a ``SELECT`` statement.

5497

5498 The :class:`_sql.Select` object is normally constructed using the

5499 :func:`_sql.select` function. See that function for details.

5500

5501 Available extension points:

5502

5503 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5504 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5505 keyword (if any) and the list of columns.

5506 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5507 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5508

5509 .. seealso::

5510

5511 :func:`_sql.select`

5512

5513 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5514

5515 """

5516

5517 __visit_name__ = "select"

5518

5519 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5520 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5521

5522 _raw_columns: List[_ColumnsClauseElement]

5523

5524 _distinct: bool = False

5525 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5526 _correlate: Tuple[FromClause, ...] = ()

5527 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5528 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5529 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5530 _from_obj: Tuple[FromClause, ...] = ()

5531

5532 _position_map = util.immutabledict(

5533 {

5534 "post_select": "_post_select_clause",

5535 "pre_columns": "_pre_columns_clause",

5536 "post_criteria": "_post_criteria_clause",

5537 "post_body": "_post_body_clause",

5538 }

5539 )

5540

5541 _post_select_clause: Optional[ClauseElement] = None

5542 """extension point for a ClauseElement that will be compiled directly

5543 after the SELECT keyword.

5544

5545 .. versionadded:: 2.1

5546

5547 """

5548

5549 _pre_columns_clause: Optional[ClauseElement] = None

5550 """extension point for a ClauseElement that will be compiled directly

5551 before the "columns" clause; after DISTINCT (if present).

5552

5553 .. versionadded:: 2.1

5554

5555 """

5556

5557 _post_criteria_clause: Optional[ClauseElement] = None

5558 """extension point for a ClauseElement that will be compiled directly

5559 after "criteria", following the HAVING clause but before ORDER BY.

5560

5561 .. versionadded:: 2.1

5562

5563 """

5564

5565 _post_body_clause: Optional[ClauseElement] = None

5566 """extension point for a ClauseElement that will be compiled directly

5567 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5568 of the SELECT.

5569

5570 .. versionadded:: 2.1

5571

5572 """

5573

5574 _auto_correlate = True

5575 _is_select_statement = True

5576 _compile_options: CacheableOptions = (

5577 SelectState.default_select_compile_options

5578 )

5579

5580 _traverse_internals: _TraverseInternalsType = (

5581 [

5582 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5583 (

5584 "_memoized_select_entities",

5585 InternalTraversal.dp_memoized_select_entities,

5586 ),

5587 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5588 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5589 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5590 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5591 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5592 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5593 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5594 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5595 ("_limit_clause", InternalTraversal.dp_clauseelement),

5596 ("_offset_clause", InternalTraversal.dp_clauseelement),

5597 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5598 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5599 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5600 ("_distinct", InternalTraversal.dp_boolean),

5601 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5602 ("_label_style", InternalTraversal.dp_plain_obj),

5603 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5604 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5605 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5606 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5607 ]

5608 + HasCTE._has_ctes_traverse_internals

5609 + HasPrefixes._has_prefixes_traverse_internals

5610 + HasSuffixes._has_suffixes_traverse_internals

5611 + HasHints._has_hints_traverse_internals

5612 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5613 + ExecutableStatement._executable_traverse_internals

5614 + DialectKWArgs._dialect_kwargs_traverse_internals

5615 )

5616

5617 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5618 ("_compile_options", InternalTraversal.dp_has_cache_key)

5619 ]

5620

5621 _compile_state_factory: Type[SelectState]

5622

5623 @classmethod

5624 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5625 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5626

5627 Used internally to build up :class:`.Select` constructs with

5628 pre-established state.

5629

5630 """

5631

5632 stmt = Select.__new__(Select)

5633 stmt.__dict__.update(kw)

5634 return stmt

5635

5636 def __init__(

5637 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5638 ):

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

5640

5641 The public constructor for :class:`_expression.Select` is the

5642 :func:`_sql.select` function.

5643

5644 """

5645 self._raw_columns = [

5646 coercions.expect(

5647 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5648 )

5649 for ent in entities

5650 ]

5651 GenerativeSelect.__init__(self)

5652

5653 def _apply_syntax_extension_to_self(

5654 self, extension: SyntaxExtension

5655 ) -> None:

5656 extension.apply_to_select(self)

5657

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

5659 if not self._raw_columns:

5660 return NULLTYPE

5661 elem = self._raw_columns[0]

5662 cols = list(elem._select_iterable)

5663 return cols[0].type

5664

5665 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5666 """A synonym for the :meth:`_sql.Select.where` method."""

5667

5668 return self.where(*criteria)

5669

5670 if TYPE_CHECKING:

5671

5672 @overload

5673 def scalar_subquery(

5674 self: Select[_MAYBE_ENTITY],

5675 ) -> ScalarSelect[Any]: ...

5676

5677 @overload

5678 def scalar_subquery(

5679 self: Select[_NOT_ENTITY],

5680 ) -> ScalarSelect[_NOT_ENTITY]: ...

5681

5682 @overload

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

5684

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

5686

5687 def filter_by(self, **kwargs: Any) -> Self:

5688 r"""Apply the given filtering criterion as a WHERE clause

5689 to this select, using keyword expressions.

5690

5691 E.g.::

5692

5693 stmt = select(User).filter_by(name="some name")

5694

5695 Multiple criteria may be specified as comma separated; the effect

5696 is that they will be joined together using the :func:`.and_`

5697 function::

5698

5699 stmt = select(User).filter_by(name="some name", id=5)

5700

5701 The keyword expressions are extracted by searching across **all

5702 entities present in the FROM clause** of the statement. If a

5703 keyword name is present in more than one entity,

5704 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5705 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5706 explicit column references::

5707

5708 # both User and Address have an 'id' attribute

5709 stmt = select(User).join(Address).filter_by(id=5)

5710 # raises AmbiguousColumnError

5711

5712 # use filter() with explicit qualification instead

5713 stmt = select(User).join(Address).filter(Address.id == 5)

5714

5715 .. versionchanged:: 2.1

5716

5717 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5718 entities rather than only searching the last joined entity or first

5719 FROM entity. This allows the method to locate attributes

5720 unambiguously across multiple joined tables. The new

5721 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5722 is present in more than one entity.

5723

5724 See :ref:`change_8601` for migration notes.

5725

5726 .. seealso::

5727

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

5729

5730 :meth:`_sql.Select.filter` - filter on SQL expressions.

5731

5732 :meth:`_sql.Select.where` - filter on SQL expressions.

5733

5734 """

5735 # Get all entities via plugin system

5736 all_entities = SelectState.get_plugin_class(

5737 self

5738 )._get_filter_by_entities(self)

5739

5740 clauses = [

5741 _entity_namespace_key_search_all(all_entities, key) == value

5742 for key, value in kwargs.items()

5743 ]

5744 return self.filter(*clauses)

5745

5746 @property

5747 def column_descriptions(self) -> Any:

5748 """Return a :term:`plugin-enabled` 'column descriptions' structure

5749 referring to the columns which are SELECTed by this statement.

5750

5751 This attribute is generally useful when using the ORM, as an

5752 extended structure which includes information about mapped

5753 entities is returned. The section :ref:`queryguide_inspection`

5754 contains more background.

5755

5756 For a Core-only statement, the structure returned by this accessor

5757 is derived from the same objects that are returned by the

5758 :attr:`.Select.selected_columns` accessor, formatted as a list of

5759 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5760 which indicate the column expressions to be selected::

5761

5762 >>> stmt = select(user_table)

5763 >>> stmt.column_descriptions

5764 [

5765 {

5766 'name': 'id',

5767 'type': Integer(),

5768 'expr': Column('id', Integer(), ...)},

5769 {

5770 'name': 'name',

5771 'type': String(length=30),

5772 'expr': Column('name', String(length=30), ...)}

5773 ]

5774

5775 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5776 attribute returns a structure for a Core-only set of entities,

5777 not just ORM-only entities.

5778

5779 .. seealso::

5780

5781 :attr:`.UpdateBase.entity_description` - entity information for

5782 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5783

5784 :ref:`queryguide_inspection` - ORM background

5785

5786 """

5787 meth = SelectState.get_plugin_class(self).get_column_descriptions

5788 return meth(self)

5789

5790 def from_statement(

5791 self, statement: roles.ReturnsRowsRole

5792 ) -> ExecutableReturnsRows:

5793 """Apply the columns which this :class:`.Select` would select

5794 onto another statement.

5795

5796 This operation is :term:`plugin-specific` and will raise a not

5797 supported exception if this :class:`_sql.Select` does not select from

5798 plugin-enabled entities.

5799

5800

5801 The statement is typically either a :func:`_expression.text` or

5802 :func:`_expression.select` construct, and should return the set of

5803 columns appropriate to the entities represented by this

5804 :class:`.Select`.

5805

5806 .. seealso::

5807

5808 :ref:`orm_queryguide_selecting_text` - usage examples in the

5809 ORM Querying Guide

5810

5811 """

5812 meth = SelectState.get_plugin_class(self).from_statement

5813 return meth(self, statement)

5814

5815 @_generative

5816 def join(

5817 self,

5818 target: _JoinTargetArgument,

5819 onclause: Optional[_OnClauseArgument] = None,

5820 *,

5821 isouter: bool = False,

5822 full: bool = False,

5823 ) -> Self:

5824 r"""Create a SQL JOIN against this :class:`_expression.Select`

5825 object's criterion

5826 and apply generatively, returning the newly resulting

5827 :class:`_expression.Select`.

5828

5829 E.g.::

5830

5831 stmt = select(user_table).join(

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

5833 )

5834

5835 The above statement generates SQL similar to:

5836

5837 .. sourcecode:: sql

5838

5839 SELECT user.id, user.name

5840 FROM user

5841 JOIN address ON user.id = address.user_id

5842

5843 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5844 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5845 source that is within the FROM clause of the existing SELECT,

5846 and a given target :class:`_sql.FromClause`, and then adds

5847 this :class:`_sql.Join` to the FROM clause of the newly generated

5848 SELECT statement. This is completely reworked from the behavior

5849 in 1.3, which would instead create a subquery of the entire

5850 :class:`_expression.Select` and then join that subquery to the

5851 target.

5852

5853 This is a **backwards incompatible change** as the previous behavior

5854 was mostly useless, producing an unnamed subquery rejected by

5855 most databases in any case. The new behavior is modeled after

5856 that of the very successful :meth:`_orm.Query.join` method in the

5857 ORM, in order to support the functionality of :class:`_orm.Query`

5858 being available by using a :class:`_sql.Select` object with an

5859 :class:`_orm.Session`.

5860

5861 See the notes for this change at :ref:`change_select_join`.

5862

5863

5864 :param target: target table to join towards

5865

5866 :param onclause: ON clause of the join. If omitted, an ON clause

5867 is generated automatically based on the :class:`_schema.ForeignKey`

5868 linkages between the two tables, if one can be unambiguously

5869 determined, otherwise an error is raised.

5870

5871 :param isouter: if True, generate LEFT OUTER join. Same as

5872 :meth:`_expression.Select.outerjoin`.

5873

5874 :param full: if True, generate FULL OUTER join.

5875

5876 .. seealso::

5877

5878 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5879

5880 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5881

5882 :meth:`_expression.Select.join_from`

5883

5884 :meth:`_expression.Select.outerjoin`

5885

5886 """ # noqa: E501

5887 join_target = coercions.expect(

5888 roles.JoinTargetRole, target, apply_propagate_attrs=self

5889 )

5890 if onclause is not None:

5891 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5892 else:

5893 onclause_element = None

5894

5895 self._setup_joins += (

5896 (

5897 join_target,

5898 onclause_element,

5899 None,

5900 {"isouter": isouter, "full": full},

5901 ),

5902 )

5903 return self

5904

5905 def outerjoin_from(

5906 self,

5907 from_: _FromClauseArgument,

5908 target: _JoinTargetArgument,

5909 onclause: Optional[_OnClauseArgument] = None,

5910 *,

5911 full: bool = False,

5912 ) -> Self:

5913 r"""Create a SQL LEFT OUTER JOIN against this

5914 :class:`_expression.Select` object's criterion and apply generatively,

5915 returning the newly resulting :class:`_expression.Select`.

5916

5917 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5918

5919 """

5920 return self.join_from(

5921 from_, target, onclause=onclause, isouter=True, full=full

5922 )

5923

5924 @_generative

5925 def join_from(

5926 self,

5927 from_: _FromClauseArgument,

5928 target: _JoinTargetArgument,

5929 onclause: Optional[_OnClauseArgument] = None,

5930 *,

5931 isouter: bool = False,

5932 full: bool = False,

5933 ) -> Self:

5934 r"""Create a SQL JOIN against this :class:`_expression.Select`

5935 object's criterion

5936 and apply generatively, returning the newly resulting

5937 :class:`_expression.Select`.

5938

5939 E.g.::

5940

5941 stmt = select(user_table, address_table).join_from(

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

5943 )

5944

5945 The above statement generates SQL similar to:

5946

5947 .. sourcecode:: sql

5948

5949 SELECT user.id, user.name, address.id, address.email, address.user_id

5950 FROM user JOIN address ON user.id = address.user_id

5951

5952 .. versionadded:: 1.4

5953

5954 :param from\_: the left side of the join, will be rendered in the

5955 FROM clause and is roughly equivalent to using the

5956 :meth:`.Select.select_from` method.

5957

5958 :param target: target table to join towards

5959

5960 :param onclause: ON clause of the join.

5961

5962 :param isouter: if True, generate LEFT OUTER join. Same as

5963 :meth:`_expression.Select.outerjoin`.

5964

5965 :param full: if True, generate FULL OUTER join.

5966

5967 .. seealso::

5968

5969 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5970

5971 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5972

5973 :meth:`_expression.Select.join`

5974

5975 """ # noqa: E501

5976

5977 # note the order of parsing from vs. target is important here, as we

5978 # are also deriving the source of the plugin (i.e. the subject mapper

5979 # in an ORM query) which should favor the "from_" over the "target"

5980

5981 from_ = coercions.expect(

5982 roles.FromClauseRole, from_, apply_propagate_attrs=self

5983 )

5984 join_target = coercions.expect(

5985 roles.JoinTargetRole, target, apply_propagate_attrs=self

5986 )

5987 if onclause is not None:

5988 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5989 else:

5990 onclause_element = None

5991

5992 self._setup_joins += (

5993 (

5994 join_target,

5995 onclause_element,

5996 from_,

5997 {"isouter": isouter, "full": full},

5998 ),

5999 )

6000 return self

6001

6002 def outerjoin(

6003 self,

6004 target: _JoinTargetArgument,

6005 onclause: Optional[_OnClauseArgument] = None,

6006 *,

6007 full: bool = False,

6008 ) -> Self:

6009 """Create a left outer join.

6010

6011 Parameters are the same as that of :meth:`_expression.Select.join`.

6012

6013 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

6014 creates a :class:`_sql.Join` object between a

6015 :class:`_sql.FromClause` source that is within the FROM clause of

6016 the existing SELECT, and a given target :class:`_sql.FromClause`,

6017 and then adds this :class:`_sql.Join` to the FROM clause of the

6018 newly generated SELECT statement. This is completely reworked

6019 from the behavior in 1.3, which would instead create a subquery of

6020 the entire

6021 :class:`_expression.Select` and then join that subquery to the

6022 target.

6023

6024 This is a **backwards incompatible change** as the previous behavior

6025 was mostly useless, producing an unnamed subquery rejected by

6026 most databases in any case. The new behavior is modeled after

6027 that of the very successful :meth:`_orm.Query.join` method in the

6028 ORM, in order to support the functionality of :class:`_orm.Query`

6029 being available by using a :class:`_sql.Select` object with an

6030 :class:`_orm.Session`.

6031

6032 See the notes for this change at :ref:`change_select_join`.

6033

6034 .. seealso::

6035

6036 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

6037

6038 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

6039

6040 :meth:`_expression.Select.join`

6041

6042 """

6043 return self.join(target, onclause=onclause, isouter=True, full=full)

6044

6045 def get_final_froms(self) -> Sequence[FromClause]:

6046 """Compute the final displayed list of :class:`_expression.FromClause`

6047 elements.

6048

6049 This method will run through the full computation required to

6050 determine what FROM elements will be displayed in the resulting

6051 SELECT statement, including shadowing individual tables with

6052 JOIN objects, as well as full computation for ORM use cases including

6053 eager loading clauses.

6054

6055 For ORM use, this accessor returns the **post compilation**

6056 list of FROM objects; this collection will include elements such as

6057 eagerly loaded tables and joins. The objects will **not** be

6058 ORM enabled and not work as a replacement for the

6059 :meth:`_sql.Select.select_froms` collection; additionally, the

6060 method is not well performing for an ORM enabled statement as it

6061 will incur the full ORM construction process.

6062

6063 To retrieve the FROM list that's implied by the "columns" collection

6064 passed to the :class:`_sql.Select` originally, use the

6065 :attr:`_sql.Select.columns_clause_froms` accessor.

6066

6067 To select from an alternative set of columns while maintaining the

6068 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

6069 pass the

6070 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6071 parameter.

6072

6073 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

6074 method replaces the previous :attr:`_sql.Select.froms` accessor,

6075 which is deprecated.

6076

6077 .. seealso::

6078

6079 :attr:`_sql.Select.columns_clause_froms`

6080

6081 """

6082 compiler = self._default_compiler()

6083

6084 return self._compile_state_factory(self, compiler)._get_display_froms()

6085

6086 @property

6087 @util.deprecated(

6088 "1.4.23",

6089 "The :attr:`_expression.Select.froms` attribute is moved to "

6090 "the :meth:`_expression.Select.get_final_froms` method.",

6091 )

6092 def froms(self) -> Sequence[FromClause]:

6093 """Return the displayed list of :class:`_expression.FromClause`

6094 elements.

6095

6096

6097 """

6098 return self.get_final_froms()

6099

6100 @property

6101 def columns_clause_froms(self) -> List[FromClause]:

6102 """Return the set of :class:`_expression.FromClause` objects implied

6103 by the columns clause of this SELECT statement.

6104

6105 .. versionadded:: 1.4.23

6106

6107 .. seealso::

6108

6109 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6110 statement into account

6111

6112 :meth:`_sql.Select.with_only_columns` - makes use of this

6113 collection to set up a new FROM list

6114

6115 """

6116

6117 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6118 self

6119 )

6120

6121 @property

6122 def inner_columns(self) -> _SelectIterable:

6123 """An iterator of all :class:`_expression.ColumnElement`

6124 expressions which would

6125 be rendered into the columns clause of the resulting SELECT statement.

6126

6127 This method is legacy as of 1.4 and is superseded by the

6128 :attr:`_expression.Select.exported_columns` collection.

6129

6130 """

6131

6132 return iter(self._all_selected_columns)

6133

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

6135 if fromclause is not None and self in fromclause._cloned_set:

6136 return True

6137

6138 for f in self._iterate_from_elements():

6139 if f.is_derived_from(fromclause):

6140 return True

6141 return False

6142

6143 def _copy_internals(

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

6145 ) -> None:

6146 # Select() object has been cloned and probably adapted by the

6147 # given clone function. Apply the cloning function to internal

6148 # objects

6149

6150 # 1. keep a dictionary of the froms we've cloned, and what

6151 # they've become. This allows us to ensure the same cloned from

6152 # is used when other items such as columns are "cloned"

6153

6154 all_the_froms = set(

6155 itertools.chain(

6156 _from_objects(*self._raw_columns),

6157 _from_objects(*self._where_criteria),

6158 _from_objects(*[elem[0] for elem in self._setup_joins]),

6159 )

6160 )

6161

6162 # do a clone for the froms we've gathered. what is important here

6163 # is if any of the things we are selecting from, like tables,

6164 # were converted into Join objects. if so, these need to be

6165 # added to _from_obj explicitly, because otherwise they won't be

6166 # part of the new state, as they don't associate themselves with

6167 # their columns.

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

6169

6170 # 2. copy FROM collections, adding in joins that we've created.

6171 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6172 add_froms = (

6173 {f for f in new_froms.values() if isinstance(f, Join)}

6174 .difference(all_the_froms)

6175 .difference(existing_from_obj)

6176 )

6177

6178 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6179

6180 # 3. clone everything else, making sure we use columns

6181 # corresponding to the froms we just made.

6182 def replace(

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

6184 **kw: Any,

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

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

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

6188 return newelem

6189 return None

6190

6191 kw["replace"] = replace

6192

6193 # copy everything else. for table-ish things like correlate,

6194 # correlate_except, setup_joins, these clone normally. For

6195 # column-expression oriented things like raw_columns, where_criteria,

6196 # order by, we get this from the new froms.

6197 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6198

6199 self._reset_memoizations()

6200

6201 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6202 return itertools.chain(

6203 super().get_children(

6204 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6205 **kw,

6206 ),

6207 self._iterate_from_elements(),

6208 )

6209

6210 @_generative

6211 def add_columns(

6212 self, *entities: _ColumnsClauseArgument[Any]

6213 ) -> Select[Unpack[TupleAny]]:

6214 r"""Return a new :func:`_expression.select` construct with

6215 the given entities appended to its columns clause.

6216

6217 E.g.::

6218

6219 my_select = my_select.add_columns(table.c.new_column)

6220

6221 The original expressions in the columns clause remain in place.

6222 To replace the original expressions with new ones, see the method

6223 :meth:`_expression.Select.with_only_columns`.

6224

6225 :param \*entities: column, table, or other entity expressions to be

6226 added to the columns clause

6227

6228 .. seealso::

6229

6230 :meth:`_expression.Select.with_only_columns` - replaces existing

6231 expressions rather than appending.

6232

6233 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6234 example

6235

6236 """

6237 self._reset_memoizations()

6238

6239 self._raw_columns = self._raw_columns + [

6240 coercions.expect(

6241 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6242 )

6243 for column in entities

6244 ]

6245 return self

6246

6247 def _set_entities(

6248 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6249 ) -> None:

6250 self._raw_columns = [

6251 coercions.expect(

6252 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6253 )

6254 for ent in util.to_list(entities)

6255 ]

6256

6257 @util.deprecated(

6258 "1.4",

6259 "The :meth:`_expression.Select.column` method is deprecated and will "

6260 "be removed in a future release. Please use "

6261 ":meth:`_expression.Select.add_columns`",

6262 )

6263 def column(

6264 self, column: _ColumnsClauseArgument[Any]

6265 ) -> Select[Unpack[TupleAny]]:

6266 """Return a new :func:`_expression.select` construct with

6267 the given column expression added to its columns clause.

6268

6269 E.g.::

6270

6271 my_select = my_select.column(table.c.new_column)

6272

6273 See the documentation for

6274 :meth:`_expression.Select.with_only_columns`

6275 for guidelines on adding /replacing the columns of a

6276 :class:`_expression.Select` object.

6277

6278 """

6279 return self.add_columns(column)

6280

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

6282 def reduce_columns(

6283 self, only_synonyms: bool = True

6284 ) -> Select[Unpack[TupleAny]]:

6285 """Return a new :func:`_expression.select` construct with redundantly

6286 named, equivalently-valued columns removed from the columns clause.

6287

6288 "Redundant" here means two columns where one refers to the

6289 other either based on foreign key, or via a simple equality

6290 comparison in the WHERE clause of the statement. The primary purpose

6291 of this method is to automatically construct a select statement

6292 with all uniquely-named columns, without the need to use

6293 table-qualified labels as

6294 :meth:`_expression.Select.set_label_style`

6295 does.

6296

6297 When columns are omitted based on foreign key, the referred-to

6298 column is the one that's kept. When columns are omitted based on

6299 WHERE equivalence, the first column in the columns clause is the

6300 one that's kept.

6301

6302 :param only_synonyms: when True, limit the removal of columns

6303 to those which have the same name as the equivalent. Otherwise,

6304 all columns that are equivalent to another are removed.

6305

6306 """

6307 woc: Select[Unpack[TupleAny]]

6308 woc = self.with_only_columns(

6309 *util.preloaded.sql_util.reduce_columns(

6310 self._all_selected_columns,

6311 only_synonyms=only_synonyms,

6312 *(self._where_criteria + self._from_obj),

6313 )

6314 )

6315 return woc

6316

6317 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6318

6319 # code within this block is **programmatically,

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

6321

6322 @overload

6323 def with_only_columns(

6324 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6325 ) -> Select[_T0]: ...

6326

6327 @overload

6328 def with_only_columns(

6329 self,

6330 __ent0: _TCCA[_T0],

6331 __ent1: _TCCA[_T1],

6332 /,

6333 *,

6334 maintain_column_froms: bool = ...,

6335 ) -> Select[_T0, _T1]: ...

6336

6337 @overload

6338 def with_only_columns(

6339 self,

6340 __ent0: _TCCA[_T0],

6341 __ent1: _TCCA[_T1],

6342 __ent2: _TCCA[_T2],

6343 /,

6344 *,

6345 maintain_column_froms: bool = ...,

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

6347

6348 @overload

6349 def with_only_columns(

6350 self,

6351 __ent0: _TCCA[_T0],

6352 __ent1: _TCCA[_T1],

6353 __ent2: _TCCA[_T2],

6354 __ent3: _TCCA[_T3],

6355 /,

6356 *,

6357 maintain_column_froms: bool = ...,

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

6359

6360 @overload

6361 def with_only_columns(

6362 self,

6363 __ent0: _TCCA[_T0],

6364 __ent1: _TCCA[_T1],

6365 __ent2: _TCCA[_T2],

6366 __ent3: _TCCA[_T3],

6367 __ent4: _TCCA[_T4],

6368 /,

6369 *,

6370 maintain_column_froms: bool = ...,

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

6372

6373 @overload

6374 def with_only_columns(

6375 self,

6376 __ent0: _TCCA[_T0],

6377 __ent1: _TCCA[_T1],

6378 __ent2: _TCCA[_T2],

6379 __ent3: _TCCA[_T3],

6380 __ent4: _TCCA[_T4],

6381 __ent5: _TCCA[_T5],

6382 /,

6383 *,

6384 maintain_column_froms: bool = ...,

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

6386

6387 @overload

6388 def with_only_columns(

6389 self,

6390 __ent0: _TCCA[_T0],

6391 __ent1: _TCCA[_T1],

6392 __ent2: _TCCA[_T2],

6393 __ent3: _TCCA[_T3],

6394 __ent4: _TCCA[_T4],

6395 __ent5: _TCCA[_T5],

6396 __ent6: _TCCA[_T6],

6397 /,

6398 *,

6399 maintain_column_froms: bool = ...,

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

6401

6402 @overload

6403 def with_only_columns(

6404 self,

6405 __ent0: _TCCA[_T0],

6406 __ent1: _TCCA[_T1],

6407 __ent2: _TCCA[_T2],

6408 __ent3: _TCCA[_T3],

6409 __ent4: _TCCA[_T4],

6410 __ent5: _TCCA[_T5],

6411 __ent6: _TCCA[_T6],

6412 __ent7: _TCCA[_T7],

6413 /,

6414 *entities: _ColumnsClauseArgument[Any],

6415 maintain_column_froms: bool = ...,

6416 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6417

6418 # END OVERLOADED FUNCTIONS self.with_only_columns

6419

6420 @overload

6421 def with_only_columns(

6422 self,

6423 *entities: _ColumnsClauseArgument[Any],

6424 maintain_column_froms: bool = False,

6425 **__kw: Any,

6426 ) -> Select[Unpack[TupleAny]]: ...

6427

6428 @_generative

6429 def with_only_columns(

6430 self,

6431 *entities: _ColumnsClauseArgument[Any],

6432 maintain_column_froms: bool = False,

6433 **__kw: Any,

6434 ) -> Select[Unpack[TupleAny]]:

6435 r"""Return a new :func:`_expression.select` construct with its columns

6436 clause replaced with the given entities.

6437

6438 By default, this method is exactly equivalent to as if the original

6439 :func:`_expression.select` had been called with the given entities.

6440 E.g. a statement::

6441

6442 s = select(table1.c.a, table1.c.b)

6443 s = s.with_only_columns(table1.c.b)

6444

6445 should be exactly equivalent to::

6446

6447 s = select(table1.c.b)

6448

6449 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6450 will also dynamically alter the FROM clause of the

6451 statement if it is not explicitly stated.

6452 To maintain the existing set of FROMs including those implied by the

6453 current columns clause, add the

6454 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6455 parameter::

6456

6457 s = select(table1.c.a, table2.c.b)

6458 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6459

6460 The above parameter performs a transfer of the effective FROMs

6461 in the columns collection to the :meth:`_sql.Select.select_from`

6462 method, as though the following were invoked::

6463

6464 s = select(table1.c.a, table2.c.b)

6465 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6466

6467 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6468 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6469 collection and performs an operation equivalent to the following::

6470

6471 s = select(table1.c.a, table2.c.b)

6472 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6473

6474 :param \*entities: column expressions to be used.

6475

6476 :param maintain_column_froms: boolean parameter that will ensure the

6477 FROM list implied from the current columns clause will be transferred

6478 to the :meth:`_sql.Select.select_from` method first.

6479

6480 .. versionadded:: 1.4.23

6481

6482 """ # noqa: E501

6483

6484 if __kw:

6485 raise _no_kw()

6486

6487 # memoizations should be cleared here as of

6488 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6489 # is the case for now.

6490 self._assert_no_memoizations()

6491

6492 if maintain_column_froms:

6493 self.select_from.non_generative( # type: ignore[attr-defined]

6494 self, *self.columns_clause_froms

6495 )

6496

6497 # then memoize the FROMs etc.

6498 _MemoizedSelectEntities._generate_for_statement(self)

6499

6500 self._raw_columns = [

6501 coercions.expect(roles.ColumnsClauseRole, c)

6502 for c in coercions._expression_collection_was_a_list(

6503 "entities", "Select.with_only_columns", entities

6504 )

6505 ]

6506 return self

6507

6508 @property

6509 def whereclause(self) -> Optional[ColumnElement[Any]]:

6510 """Return the completed WHERE clause for this

6511 :class:`_expression.Select` statement.

6512

6513 This assembles the current collection of WHERE criteria

6514 into a single :class:`_expression.BooleanClauseList` construct.

6515

6516

6517 .. versionadded:: 1.4

6518

6519 """

6520

6521 return BooleanClauseList._construct_for_whereclause(

6522 self._where_criteria

6523 )

6524

6525 _whereclause = whereclause

6526

6527 @_generative

6528 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6529 """Return a new :func:`_expression.select` construct with

6530 the given expression added to

6531 its WHERE clause, joined to the existing clause via AND, if any.

6532

6533 """

6534

6535 assert isinstance(self._where_criteria, tuple)

6536

6537 for criterion in whereclause:

6538 where_criteria: ColumnElement[Any] = coercions.expect(

6539 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6540 )

6541 self._where_criteria += (where_criteria,)

6542 return self

6543

6544 @_generative

6545 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6546 """Return a new :func:`_expression.select` construct with

6547 the given expression added to

6548 its HAVING clause, joined to the existing clause via AND, if any.

6549

6550 """

6551

6552 for criterion in having:

6553 having_criteria = coercions.expect(

6554 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6555 )

6556 self._having_criteria += (having_criteria,)

6557 return self

6558

6559 @_generative

6560 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6561 r"""Return a new :func:`_expression.select` construct which

6562 will apply DISTINCT to the SELECT statement overall.

6563

6564 E.g.::

6565

6566 from sqlalchemy import select

6567

6568 stmt = select(users_table.c.id, users_table.c.name).distinct()

6569

6570 The above would produce an statement resembling:

6571

6572 .. sourcecode:: sql

6573

6574 SELECT DISTINCT user.id, user.name FROM user

6575

6576 The method also historically accepted an ``*expr`` parameter which

6577 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6578 This is now replaced using the :func:`_postgresql.distinct_on`

6579 extension::

6580

6581 from sqlalchemy import select

6582 from sqlalchemy.dialects.postgresql import distinct_on

6583

6584 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6585

6586 Using this parameter on other backends which don't support this

6587 syntax will raise an error.

6588

6589 :param \*expr: optional column expressions. When present,

6590 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6591 construct. A deprecation warning and/or :class:`_exc.CompileError`

6592 will be raised on other backends.

6593

6594 .. deprecated:: 2.1 Passing expressions to

6595 :meth:`_sql.Select.distinct` is deprecated, use

6596 :func:`_postgresql.distinct_on` instead.

6597

6598 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6599 and will raise :class:`_exc.CompileError` in a future version.

6600

6601 .. seealso::

6602

6603 :func:`_postgresql.distinct_on`

6604

6605 :meth:`.ext`

6606 """

6607 self._distinct = True

6608 if expr:

6609 warn_deprecated(

6610 "Passing expression to ``distinct`` to generate a "

6611 "DISTINCT ON clause is deprecated. Use instead the "

6612 "``postgresql.distinct_on`` function as an extension.",

6613 "2.1",

6614 )

6615 self._distinct_on = self._distinct_on + tuple(

6616 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6617 for e in expr

6618 )

6619 return self

6620

6621 @_generative

6622 def select_from(self, *froms: _FromClauseArgument) -> Self:

6623 r"""Return a new :func:`_expression.select` construct with the

6624 given FROM expression(s)

6625 merged into its list of FROM objects.

6626

6627 E.g.::

6628

6629 table1 = table("t1", column("a"))

6630 table2 = table("t2", column("b"))

6631 s = select(table1.c.a).select_from(

6632 table1.join(table2, table1.c.a == table2.c.b)

6633 )

6634

6635 The "from" list is a unique set on the identity of each element,

6636 so adding an already present :class:`_schema.Table`

6637 or other selectable

6638 will have no effect. Passing a :class:`_expression.Join` that refers

6639 to an already present :class:`_schema.Table`

6640 or other selectable will have

6641 the effect of concealing the presence of that selectable as

6642 an individual element in the rendered FROM list, instead

6643 rendering it into a JOIN clause.

6644

6645 While the typical purpose of :meth:`_expression.Select.select_from`

6646 is to

6647 replace the default, derived FROM clause with a join, it can

6648 also be called with individual table elements, multiple times

6649 if desired, in the case that the FROM clause cannot be fully

6650 derived from the columns clause::

6651

6652 select(func.count("*")).select_from(table1)

6653

6654 """

6655

6656 self._from_obj += tuple(

6657 coercions.expect(

6658 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6659 )

6660 for fromclause in froms

6661 )

6662 return self

6663

6664 @_generative

6665 def correlate(

6666 self,

6667 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6668 ) -> Self:

6669 r"""Return a new :class:`_expression.Select`

6670 which will correlate the given FROM

6671 clauses to that of an enclosing :class:`_expression.Select`.

6672

6673 Calling this method turns off the :class:`_expression.Select` object's

6674 default behavior of "auto-correlation". Normally, FROM elements

6675 which appear in a :class:`_expression.Select`

6676 that encloses this one via

6677 its :term:`WHERE clause`, ORDER BY, HAVING or

6678 :term:`columns clause` will be omitted from this

6679 :class:`_expression.Select`

6680 object's :term:`FROM clause`.

6681 Setting an explicit correlation collection using the

6682 :meth:`_expression.Select.correlate`

6683 method provides a fixed list of FROM objects

6684 that can potentially take place in this process.

6685

6686 When :meth:`_expression.Select.correlate`

6687 is used to apply specific FROM clauses

6688 for correlation, the FROM elements become candidates for

6689 correlation regardless of how deeply nested this

6690 :class:`_expression.Select`

6691 object is, relative to an enclosing :class:`_expression.Select`

6692 which refers to

6693 the same FROM object. This is in contrast to the behavior of

6694 "auto-correlation" which only correlates to an immediate enclosing

6695 :class:`_expression.Select`.

6696 Multi-level correlation ensures that the link

6697 between enclosed and enclosing :class:`_expression.Select`

6698 is always via

6699 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6700 correlation to take place.

6701

6702 If ``None`` is passed, the :class:`_expression.Select`

6703 object will correlate

6704 none of its FROM entries, and all will render unconditionally

6705 in the local FROM clause.

6706

6707 :param \*fromclauses: one or more :class:`.FromClause` or other

6708 FROM-compatible construct such as an ORM mapped entity to become part

6709 of the correlate collection; alternatively pass a single value

6710 ``None`` to remove all existing correlations.

6711

6712 .. seealso::

6713

6714 :meth:`_expression.Select.correlate_except`

6715

6716 :ref:`tutorial_scalar_subquery`

6717

6718 """

6719

6720 # tests failing when we try to change how these

6721 # arguments are passed

6722

6723 self._auto_correlate = False

6724 if not fromclauses or fromclauses[0] in {None, False}:

6725 if len(fromclauses) > 1:

6726 raise exc.ArgumentError(

6727 "additional FROM objects not accepted when "

6728 "passing None/False to correlate()"

6729 )

6730 self._correlate = ()

6731 else:

6732 self._correlate = self._correlate + tuple(

6733 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6734 )

6735 return self

6736

6737 @_generative

6738 def correlate_except(

6739 self,

6740 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6741 ) -> Self:

6742 r"""Return a new :class:`_expression.Select`

6743 which will omit the given FROM

6744 clauses from the auto-correlation process.

6745

6746 Calling :meth:`_expression.Select.correlate_except` turns off the

6747 :class:`_expression.Select` object's default behavior of

6748 "auto-correlation" for the given FROM elements. An element

6749 specified here will unconditionally appear in the FROM list, while

6750 all other FROM elements remain subject to normal auto-correlation

6751 behaviors.

6752

6753 If ``None`` is passed, or no arguments are passed,

6754 the :class:`_expression.Select` object will correlate all of its

6755 FROM entries.

6756

6757 :param \*fromclauses: a list of one or more

6758 :class:`_expression.FromClause`

6759 constructs, or other compatible constructs (i.e. ORM-mapped

6760 classes) to become part of the correlate-exception collection.

6761

6762 .. seealso::

6763

6764 :meth:`_expression.Select.correlate`

6765

6766 :ref:`tutorial_scalar_subquery`

6767

6768 """

6769

6770 self._auto_correlate = False

6771 if not fromclauses or fromclauses[0] in {None, False}:

6772 if len(fromclauses) > 1:

6773 raise exc.ArgumentError(

6774 "additional FROM objects not accepted when "

6775 "passing None/False to correlate_except()"

6776 )

6777 self._correlate_except = ()

6778 else:

6779 self._correlate_except = (self._correlate_except or ()) + tuple(

6780 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6781 )

6782

6783 return self

6784

6785 @HasMemoized_ro_memoized_attribute

6786 def selected_columns(

6787 self,

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

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

6790 representing the columns that

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

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

6793

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

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

6796 within this collection cannot be directly nested inside another SELECT

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

6798 necessary parenthesization required by SQL.

6799

6800 For a :func:`_expression.select` construct, the collection here is

6801 exactly what would be rendered inside the "SELECT" statement, and the

6802 :class:`_expression.ColumnElement` objects are directly present as they

6803 were given, e.g.::

6804

6805 col1 = column("q", Integer)

6806 col2 = column("p", Integer)

6807 stmt = select(col1, col2)

6808

6809 Above, ``stmt.selected_columns`` would be a collection that contains

6810 the ``col1`` and ``col2`` objects directly. For a statement that is

6811 against a :class:`_schema.Table` or other

6812 :class:`_expression.FromClause`, the collection will use the

6813 :class:`_expression.ColumnElement` objects that are in the

6814 :attr:`_expression.FromClause.c` collection of the from element.

6815

6816 A use case for the :attr:`_sql.Select.selected_columns` collection is

6817 to allow the existing columns to be referenced when adding additional

6818 criteria, e.g.::

6819

6820 def filter_on_id(my_select, id):

6821 return my_select.where(my_select.selected_columns["id"] == id)

6822

6823

6824 stmt = select(MyModel)

6825

6826 # adds "WHERE id=:param" to the statement

6827 stmt = filter_on_id(stmt, 42)

6828

6829 .. note::

6830

6831 The :attr:`_sql.Select.selected_columns` collection does not

6832 include expressions established in the columns clause using the

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

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

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

6836 construct.

6837

6838

6839 .. versionadded:: 1.4

6840

6841 """

6842

6843 # compare to SelectState._generate_columns_plus_names, which

6844 # generates the actual names used in the SELECT string. that

6845 # method is more complex because it also renders columns that are

6846 # fully ambiguous, e.g. same column more than once.

6847 conv = cast(

6848 "Callable[[Any], str]",

6849 SelectState._column_naming_convention(self._label_style),

6850 )

6851

6852 cc: WriteableColumnCollection[str, ColumnElement[Any]] = (

6853 WriteableColumnCollection(

6854 [

6855 (conv(c), c)

6856 for c in self._all_selected_columns

6857 if is_column_element(c)

6858 ]

6859 )

6860 )

6861 return cc.as_readonly()

6862

6863 @HasMemoized_ro_memoized_attribute

6864 def _all_selected_columns(self) -> _SelectIterable:

6865 meth = SelectState.get_plugin_class(self).all_selected_columns

6866 return list(meth(self))

6867

6868 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6869 if self._label_style is LABEL_STYLE_NONE:

6870 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6871 return self

6872

6873 def _generate_fromclause_column_proxies(

6874 self,

6875 subquery: FromClause,

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

6877 primary_key: ColumnSet,

6878 foreign_keys: Set[KeyedColumnElement[Any]],

6879 *,

6880 proxy_compound_columns: Optional[

6881 Iterable[Sequence[ColumnElement[Any]]]

6882 ] = None,

6883 ) -> None:

6884 """Generate column proxies to place in the exported ``.c``

6885 collection of a subquery."""

6886

6887 if proxy_compound_columns:

6888 extra_col_iterator = proxy_compound_columns

6889 prox = [

6890 c._make_proxy(

6891 subquery,

6892 key=proxy_key,

6893 name=required_label_name,

6894 name_is_truncatable=True,

6895 compound_select_cols=extra_cols,

6896 primary_key=primary_key,

6897 foreign_keys=foreign_keys,

6898 )

6899 for (

6900 (

6901 required_label_name,

6902 proxy_key,

6903 fallback_label_name,

6904 c,

6905 repeated,

6906 ),

6907 extra_cols,

6908 ) in (

6909 zip(

6910 self._generate_columns_plus_names(False),

6911 extra_col_iterator,

6912 )

6913 )

6914 if is_column_element(c)

6915 ]

6916 else:

6917 prox = [

6918 c._make_proxy(

6919 subquery,

6920 key=proxy_key,

6921 name=required_label_name,

6922 name_is_truncatable=True,

6923 primary_key=primary_key,

6924 foreign_keys=foreign_keys,

6925 )

6926 for (

6927 required_label_name,

6928 proxy_key,

6929 fallback_label_name,

6930 c,

6931 repeated,

6932 ) in (self._generate_columns_plus_names(False))

6933 if is_column_element(c)

6934 ]

6935

6936 columns._populate_separate_keys(prox)

6937

6938 def _needs_parens_for_grouping(self) -> bool:

6939 return self._has_row_limiting_clause or bool(

6940 self._order_by_clause.clauses

6941 )

6942

6943 def self_group(

6944 self, against: Optional[OperatorType] = None

6945 ) -> Union[SelectStatementGrouping[Self], Self]:

6946 """Return a 'grouping' construct as per the

6947 :class:`_expression.ClauseElement` specification.

6948

6949 This produces an element that can be embedded in an expression. Note

6950 that this method is called automatically as needed when constructing

6951 expressions and should not require explicit use.

6952

6953 """

6954 if (

6955 isinstance(against, CompoundSelect)

6956 and not self._needs_parens_for_grouping()

6957 ):

6958 return self

6959 else:

6960 return SelectStatementGrouping(self)

6961

6962 def union(

6963 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6965 r"""Return a SQL ``UNION`` of this select() construct against

6966 the given selectables provided as positional arguments.

6967

6968 :param \*other: one or more elements with which to create a

6969 UNION.

6970

6971 .. versionchanged:: 1.4.28

6972

6973 multiple elements are now accepted.

6974

6975 :param \**kwargs: keyword arguments are forwarded to the constructor

6976 for the newly created :class:`_sql.CompoundSelect` object.

6977

6978 """

6979 return CompoundSelect._create_union(self, *other)

6980

6981 def union_all(

6982 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6983 ) -> CompoundSelect[Unpack[_Ts]]:

6984 r"""Return a SQL ``UNION ALL`` of this select() construct against

6985 the given selectables provided as positional arguments.

6986

6987 :param \*other: one or more elements with which to create a

6988 UNION.

6989

6990 .. versionchanged:: 1.4.28

6991

6992 multiple elements are now accepted.

6993

6994 :param \**kwargs: keyword arguments are forwarded to the constructor

6995 for the newly created :class:`_sql.CompoundSelect` object.

6996

6997 """

6998 return CompoundSelect._create_union_all(self, *other)

6999

7000 def except_(

7001 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7002 ) -> CompoundSelect[Unpack[_Ts]]:

7003 r"""Return a SQL ``EXCEPT`` of this select() construct against

7004 the given selectable provided as positional arguments.

7005

7006 :param \*other: one or more elements with which to create a

7007 UNION.

7008

7009 .. versionchanged:: 1.4.28

7010

7011 multiple elements are now accepted.

7012

7013 """

7014 return CompoundSelect._create_except(self, *other)

7015

7016 def except_all(

7017 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7018 ) -> CompoundSelect[Unpack[_Ts]]:

7019 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

7020 the given selectables provided as positional arguments.

7021

7022 :param \*other: one or more elements with which to create a

7023 UNION.

7024

7025 .. versionchanged:: 1.4.28

7026

7027 multiple elements are now accepted.

7028

7029 """

7030 return CompoundSelect._create_except_all(self, *other)

7031

7032 def intersect(

7033 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7034 ) -> CompoundSelect[Unpack[_Ts]]:

7035 r"""Return a SQL ``INTERSECT`` of this select() construct against

7036 the given selectables provided as positional arguments.

7037

7038 :param \*other: one or more elements with which to create a

7039 UNION.

7040

7041 .. versionchanged:: 1.4.28

7042

7043 multiple elements are now accepted.

7044

7045 :param \**kwargs: keyword arguments are forwarded to the constructor

7046 for the newly created :class:`_sql.CompoundSelect` object.

7047

7048 """

7049 return CompoundSelect._create_intersect(self, *other)

7050

7051 def intersect_all(

7052 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7053 ) -> CompoundSelect[Unpack[_Ts]]:

7054 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

7055 against the given selectables provided as positional arguments.

7056

7057 :param \*other: one or more elements with which to create a

7058 UNION.

7059

7060 .. versionchanged:: 1.4.28

7061

7062 multiple elements are now accepted.

7063

7064 :param \**kwargs: keyword arguments are forwarded to the constructor

7065 for the newly created :class:`_sql.CompoundSelect` object.

7066

7067 """

7068 return CompoundSelect._create_intersect_all(self, *other)

7069

7070

7071class ScalarSelect(

7072 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

7073):

7074 """Represent a scalar subquery.

7075

7076

7077 A :class:`_sql.ScalarSelect` is created by invoking the

7078 :meth:`_sql.SelectBase.scalar_subquery` method. The object

7079 then participates in other SQL expressions as a SQL column expression

7080 within the :class:`_sql.ColumnElement` hierarchy.

7081

7082 .. seealso::

7083

7084 :meth:`_sql.SelectBase.scalar_subquery`

7085

7086 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7087

7088 """

7089

7090 _traverse_internals: _TraverseInternalsType = [

7091 ("element", InternalTraversal.dp_clauseelement),

7092 ("type", InternalTraversal.dp_type),

7093 ]

7094

7095 _from_objects: List[FromClause] = []

7096 _is_from_container = True

7097 if not TYPE_CHECKING:

7098 _is_implicitly_boolean = False

7099 inherit_cache = True

7100

7101 element: SelectBase

7102

7103 def __init__(self, element: SelectBase) -> None:

7104 self.element = element

7105 self.type = element._scalar_type()

7106 self._propagate_attrs = element._propagate_attrs

7107

7108 def __getattr__(self, attr: str) -> Any:

7109 return getattr(self.element, attr)

7110

7111 def __getstate__(self) -> Dict[str, Any]:

7112 return {"element": self.element, "type": self.type}

7113

7114 def __setstate__(self, state: Dict[str, Any]) -> None:

7115 self.element = state["element"]

7116 self.type = state["type"]

7117

7118 @property

7119 def columns(self) -> NoReturn:

7120 raise exc.InvalidRequestError(

7121 "Scalar Select expression has no "

7122 "columns; use this object directly "

7123 "within a column-level expression."

7124 )

7125

7126 c = columns

7127

7128 @_generative

7129 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7130 """Apply a WHERE clause to the SELECT statement referred to

7131 by this :class:`_expression.ScalarSelect`.

7132

7133 """

7134 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7135 crit

7136 )

7137 return self

7138

7139 def self_group(self, against: Optional[OperatorType] = None) -> Self:

7140 return self

7141

7142 def _ungroup(self) -> Self:

7143 return self

7144

7145 @_generative

7146 def correlate(

7147 self,

7148 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7149 ) -> Self:

7150 r"""Return a new :class:`_expression.ScalarSelect`

7151 which will correlate the given FROM

7152 clauses to that of an enclosing :class:`_expression.Select`.

7153

7154 This method is mirrored from the :meth:`_sql.Select.correlate` method

7155 of the underlying :class:`_sql.Select`. The method applies the

7156 :meth:_sql.Select.correlate` method, then returns a new

7157 :class:`_sql.ScalarSelect` against that statement.

7158

7159 .. versionadded:: 1.4 Previously, the

7160 :meth:`_sql.ScalarSelect.correlate`

7161 method was only available from :class:`_sql.Select`.

7162

7163 :param \*fromclauses: a list of one or more

7164 :class:`_expression.FromClause`

7165 constructs, or other compatible constructs (i.e. ORM-mapped

7166 classes) to become part of the correlate collection.

7167

7168 .. seealso::

7169

7170 :meth:`_expression.ScalarSelect.correlate_except`

7171

7172 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7173

7174

7175 """

7176 self.element = cast(

7177 "Select[Unpack[TupleAny]]", self.element

7178 ).correlate(*fromclauses)

7179 return self

7180

7181 @_generative

7182 def correlate_except(

7183 self,

7184 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7185 ) -> Self:

7186 r"""Return a new :class:`_expression.ScalarSelect`

7187 which will omit the given FROM

7188 clauses from the auto-correlation process.

7189

7190 This method is mirrored from the

7191 :meth:`_sql.Select.correlate_except` method of the underlying

7192 :class:`_sql.Select`. The method applies the

7193 :meth:_sql.Select.correlate_except` method, then returns a new

7194 :class:`_sql.ScalarSelect` against that statement.

7195

7196 .. versionadded:: 1.4 Previously, the

7197 :meth:`_sql.ScalarSelect.correlate_except`

7198 method was only available from :class:`_sql.Select`.

7199

7200 :param \*fromclauses: a list of one or more

7201 :class:`_expression.FromClause`

7202 constructs, or other compatible constructs (i.e. ORM-mapped

7203 classes) to become part of the correlate-exception collection.

7204

7205 .. seealso::

7206

7207 :meth:`_expression.ScalarSelect.correlate`

7208

7209 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7210

7211

7212 """

7213

7214 self.element = cast(

7215 "Select[Unpack[TupleAny]]", self.element

7216 ).correlate_except(*fromclauses)

7217 return self

7218

7219

7220class Exists(UnaryExpression[bool]):

7221 """Represent an ``EXISTS`` clause.

7222

7223 See :func:`_sql.exists` for a description of usage.

7224

7225 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7226 instance by calling :meth:`_sql.SelectBase.exists`.

7227

7228 """

7229

7230 inherit_cache = True

7231

7232 def __init__(

7233 self,

7234 __argument: Optional[

7235 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7236 ] = None,

7237 /,

7238 ):

7239 s: ScalarSelect[Any]

7240

7241 # TODO: this seems like we should be using coercions for this

7242 if __argument is None:

7243 s = Select(literal_column("*")).scalar_subquery()

7244 elif isinstance(__argument, SelectBase):

7245 s = __argument.scalar_subquery()

7246 s._propagate_attrs = __argument._propagate_attrs

7247 elif isinstance(__argument, ScalarSelect):

7248 s = __argument

7249 else:

7250 s = Select(__argument).scalar_subquery()

7251

7252 UnaryExpression.__init__(

7253 self,

7254 s,

7255 operator=operators.exists,

7256 type_=type_api.BOOLEANTYPE,

7257 )

7258

7259 @util.ro_non_memoized_property

7260 def _from_objects(self) -> List[FromClause]:

7261 return []

7262

7263 def _regroup(

7264 self,

7265 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7266 ) -> ScalarSelect[Any]:

7267

7268 assert isinstance(self.element, ScalarSelect)

7269 element = self.element.element

7270 if not isinstance(element, Select):

7271 raise exc.InvalidRequestError(

7272 "Can only apply this operation to a plain SELECT construct"

7273 )

7274 new_element = fn(element)

7275

7276 return_value = new_element.scalar_subquery()

7277 return return_value

7278

7279 def select(self) -> Select[bool]:

7280 r"""Return a SELECT of this :class:`_expression.Exists`.

7281

7282 e.g.::

7283

7284 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7285

7286 This will produce a statement resembling:

7287

7288 .. sourcecode:: sql

7289

7290 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7291

7292 .. seealso::

7293

7294 :func:`_expression.select` - general purpose

7295 method which allows for arbitrary column lists.

7296

7297 """ # noqa

7298

7299 return Select(self)

7300

7301 def correlate(

7302 self,

7303 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7304 ) -> Self:

7305 """Apply correlation to the subquery noted by this

7306 :class:`_sql.Exists`.

7307

7308 .. seealso::

7309

7310 :meth:`_sql.ScalarSelect.correlate`

7311

7312 """

7313 e = self._clone()

7314 e.element = self._regroup(

7315 lambda element: element.correlate(*fromclauses)

7316 )

7317 return e

7318

7319 def correlate_except(

7320 self,

7321 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7322 ) -> Self:

7323 """Apply correlation to the subquery noted by this

7324 :class:`_sql.Exists`.

7325

7326 .. seealso::

7327

7328 :meth:`_sql.ScalarSelect.correlate_except`

7329

7330 """

7331 e = self._clone()

7332 e.element = self._regroup(

7333 lambda element: element.correlate_except(*fromclauses)

7334 )

7335 return e

7336

7337 def select_from(self, *froms: _FromClauseArgument) -> Self:

7338 """Return a new :class:`_expression.Exists` construct,

7339 applying the given

7340 expression to the :meth:`_expression.Select.select_from`

7341 method of the select

7342 statement contained.

7343

7344 .. note:: it is typically preferable to build a :class:`_sql.Select`

7345 statement first, including the desired WHERE clause, then use the

7346 :meth:`_sql.SelectBase.exists` method to produce an

7347 :class:`_sql.Exists` object at once.

7348

7349 """

7350 e = self._clone()

7351 e.element = self._regroup(lambda element: element.select_from(*froms))

7352 return e

7353

7354 def with_hint(

7355 self,

7356 selectable: _FromClauseArgument,

7357 text: str,

7358 dialect_name: str = "*",

7359 ) -> Self:

7360 r"""Return a new :class:`_expression.Exists` construct, applying

7361 the given arguments to the

7362 :meth:`_expression.Select.with_hint` method of the select

7363 statement contained.

7364

7365 The hint is therefore rendered against the SELECT that's enclosed

7366 by the EXISTS expression. This includes :class:`_expression.Exists`

7367 objects that were generated by ORM constructs such as

7368 :meth:`_orm.PropComparator.any` and

7369 :meth:`_orm.PropComparator.has`::

7370

7371 stmt = select(User).where(

7372 User.addresses.any().with_hint(

7373 Address.__table__, "WITH (NOLOCK)", "mssql"

7374 )

7375 )

7376

7377 The above statement renders as:

7378

7379 .. sourcecode:: sql

7380

7381 SELECT users.id, users.name

7382 FROM users

7383 WHERE EXISTS (SELECT 1

7384 FROM addresses WITH (NOLOCK)

7385 WHERE users.id = addresses.user_id)

7386

7387 .. versionadded:: 2.1

7388

7389 .. seealso::

7390

7391 :meth:`_expression.Select.with_hint`

7392

7393 :meth:`_expression.Exists.with_statement_hint`

7394

7395 """

7396 e = self._clone()

7397 e.element = self._regroup(

7398 lambda element: element.with_hint(selectable, text, dialect_name)

7399 )

7400 return e

7401

7402 def with_statement_hint(self, text: str, dialect_name: str = "*") -> Self:

7403 r"""Return a new :class:`_expression.Exists` construct, applying

7404 the given arguments to the

7405 :meth:`_expression.Select.with_statement_hint` method of the select

7406 statement contained.

7407

7408 The hint is therefore rendered at the trailing end of the SELECT

7409 that's enclosed by the EXISTS expression, rather than at the end of

7410 the enclosing statement. As with

7411 :meth:`_expression.Exists.with_hint`, this includes

7412 :class:`_expression.Exists` objects that were generated by ORM

7413 constructs such as :meth:`_orm.PropComparator.any` and

7414 :meth:`_orm.PropComparator.has`::

7415

7416 has_address = User.addresses.any().with_statement_hint(

7417 "WITH (NOLOCK)", "mssql"

7418 )

7419 stmt = select(User).where(has_address)

7420

7421 The above statement renders as:

7422

7423 .. sourcecode:: sql

7424

7425 SELECT users.id, users.name

7426 FROM users

7427 WHERE EXISTS (SELECT 1

7428 FROM addresses

7429 WHERE users.id = addresses.user_id WITH (NOLOCK))

7430

7431 .. versionadded:: 2.1

7432

7433 .. seealso::

7434

7435 :meth:`_expression.Select.with_statement_hint`

7436

7437 :meth:`_expression.Exists.with_hint`

7438

7439 """

7440 e = self._clone()

7441 e.element = self._regroup(

7442 lambda element: element.with_statement_hint(text, dialect_name)

7443 )

7444 return e

7445

7446 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7447 """Return a new :func:`_expression.exists` construct with the

7448 given expression added to

7449 its WHERE clause, joined to the existing clause via AND, if any.

7450

7451

7452 .. note:: it is typically preferable to build a :class:`_sql.Select`

7453 statement first, including the desired WHERE clause, then use the

7454 :meth:`_sql.SelectBase.exists` method to produce an

7455 :class:`_sql.Exists` object at once.

7456

7457 """

7458 e = self._clone()

7459 e.element = self._regroup(lambda element: element.where(*clause))

7460 return e

7461

7462

7463class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7464 """Wrap a :class:`_expression.TextClause` construct within a

7465 :class:`_expression.SelectBase`

7466 interface.

7467

7468 This allows the :class:`_expression.TextClause` object to gain a

7469 ``.c`` collection

7470 and other FROM-like capabilities such as

7471 :meth:`_expression.FromClause.alias`,

7472 :meth:`_expression.SelectBase.cte`, etc.

7473

7474 The :class:`_expression.TextualSelect` construct is produced via the

7475 :meth:`_expression.TextClause.columns`

7476 method - see that method for details.

7477

7478 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7479 class was renamed

7480 from ``TextAsFrom``, to more correctly suit its role as a

7481 SELECT-oriented object and not a FROM clause.

7482

7483 .. seealso::

7484

7485 :func:`_expression.text`

7486

7487 :meth:`_expression.TextClause.columns` - primary creation interface.

7488

7489 """

7490

7491 __visit_name__ = "textual_select"

7492

7493 _label_style = LABEL_STYLE_NONE

7494

7495 _traverse_internals: _TraverseInternalsType = (

7496 [

7497 ("element", InternalTraversal.dp_clauseelement),

7498 ("column_args", InternalTraversal.dp_clauseelement_list),

7499 ]

7500 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7501 + HasCTE._has_ctes_traverse_internals

7502 + ExecutableStatement._executable_traverse_internals

7503 )

7504

7505 _is_textual = True

7506

7507 is_text = True

7508 is_select = True

7509

7510 def __init__(

7511 self,

7512 text: TextClause,

7513 columns: List[_ColumnExpressionArgument[Any]],

7514 positional: bool = False,

7515 ) -> None:

7516 self._init(

7517 text,

7518 # convert for ORM attributes->columns, etc

7519 [

7520 coercions.expect(roles.LabeledColumnExprRole, c)

7521 for c in columns

7522 ],

7523 positional,

7524 )

7525

7526 def _init(

7527 self,

7528 text: AbstractTextClause,

7529 columns: List[NamedColumn[Any]],

7530 positional: bool = False,

7531 ) -> None:

7532 self.element = text

7533 self.column_args = columns

7534 self.positional = positional

7535

7536 @HasMemoized_ro_memoized_attribute

7537 def selected_columns(

7538 self,

7539 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7540 """A :class:`_expression.ColumnCollection`

7541 representing the columns that

7542 this SELECT statement or similar construct returns in its result set,

7543 not including :class:`_sql.TextClause` constructs.

7544

7545 This collection differs from the :attr:`_expression.FromClause.columns`

7546 collection of a :class:`_expression.FromClause` in that the columns

7547 within this collection cannot be directly nested inside another SELECT

7548 statement; a subquery must be applied first which provides for the

7549 necessary parenthesization required by SQL.

7550

7551 For a :class:`_expression.TextualSelect` construct, the collection

7552 contains the :class:`_expression.ColumnElement` objects that were

7553 passed to the constructor, typically via the

7554 :meth:`_expression.TextClause.columns` method.

7555

7556

7557 .. versionadded:: 1.4

7558

7559 """

7560 return WriteableColumnCollection(

7561 (c.key, c) for c in self.column_args

7562 ).as_readonly()

7563

7564 @util.ro_non_memoized_property

7565 def _all_selected_columns(self) -> _SelectIterable:

7566 return self.column_args

7567

7568 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7569 return self

7570

7571 def _ensure_disambiguated_names(self) -> TextualSelect:

7572 return self

7573

7574 @_generative

7575 def bindparams(

7576 self,

7577 *binds: BindParameter[Any],

7578 **bind_as_values: Any,

7579 ) -> Self:

7580 self.element = self.element.bindparams(*binds, **bind_as_values)

7581 return self

7582

7583 def _generate_fromclause_column_proxies(

7584 self,

7585 fromclause: FromClause,

7586 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

7587 primary_key: ColumnSet,

7588 foreign_keys: Set[KeyedColumnElement[Any]],

7589 *,

7590 proxy_compound_columns: Optional[

7591 Iterable[Sequence[ColumnElement[Any]]]

7592 ] = None,

7593 ) -> None:

7594 if TYPE_CHECKING:

7595 assert isinstance(fromclause, Subquery)

7596

7597 if proxy_compound_columns:

7598 columns._populate_separate_keys(

7599 c._make_proxy(

7600 fromclause,

7601 compound_select_cols=extra_cols,

7602 primary_key=primary_key,

7603 foreign_keys=foreign_keys,

7604 )

7605 for c, extra_cols in zip(

7606 self.column_args, proxy_compound_columns

7607 )

7608 )

7609 else:

7610 columns._populate_separate_keys(

7611 c._make_proxy(

7612 fromclause,

7613 primary_key=primary_key,

7614 foreign_keys=foreign_keys,

7615 )

7616 for c in self.column_args

7617 )

7618

7619 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7620 return self.column_args[0].type

7621

7622

7623TextAsFrom = TextualSelect

7624"""Backwards compatibility with the previous name"""

7625

7626

7627class AnnotatedFromClause(Annotated):

7628 def _copy_internals(

7629 self,

7630 _annotations_traversal: bool = False,

7631 ind_cols_on_fromclause: bool = False,

7632 **kw: Any,

7633 ) -> None:

7634 super()._copy_internals(**kw)

7635

7636 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7637 # the traversals used by annotations for these cases are not currently

7638 # designed around expecting that inner elements inside of

7639 # AnnotatedFromClause's element are also deep copied, so skip for these

7640 # cases. in other cases such as plain visitors.cloned_traverse(), we

7641 # expect this to happen. see issue #12915

7642 if not _annotations_traversal:

7643 ee = self._Annotated__element # type: ignore[attr-defined]

7644 ee._copy_internals(**kw)

7645

7646 if ind_cols_on_fromclause:

7647 # passed from annotations._deep_annotate(). See that function

7648 # for notes

7649 ee = self._Annotated__element # type: ignore[attr-defined]

7650 self.c = ee.__class__.c.fget(self) # type: ignore[misc]

7651

7652 @util.ro_memoized_property

7653 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7654 """proxy the .c collection of the underlying FromClause.

7655

7656 Originally implemented in 2008 as a simple load of the .c collection

7657 when the annotated construct was created (see d3621ae961a), in modern

7658 SQLAlchemy versions this can be expensive for statements constructed

7659 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7660 it, which works just as well.

7661

7662 Two different use cases seem to require the collection either copied

7663 from the underlying one, or unique to this AnnotatedFromClause.

7664

7665 See test_selectable->test_annotated_corresponding_column

7666

7667 """

7668 ee = self._Annotated__element # type: ignore[attr-defined]

7669 return ee.c # type: ignore[no-any-return]