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

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

1164 FROM table1 JOIN table2 ON true

1165

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

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

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

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

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

1171

1172

1173 .. versionadded:: 1.4

1174

1175 """ # noqa: E501

1176

1177 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1180 when generating the columns clause of a SELECT statement.

1181

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

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

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

1185

1186 .. sourcecode:: pycon+sql

1187

1188 >>> from sqlalchemy import (

1189 ... table,

1190 ... column,

1191 ... select,

1192 ... true,

1193 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1194 ... )

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

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

1197 >>> print(

1198 ... select(table1, table2)

1199 ... .join(table2, true())

1200 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1201 ... )

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

1203 FROM table1 JOIN table2 ON true

1204

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

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

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

1208

1209 .. versionadded:: 1.4

1210

1211 """ # noqa: E501

1212

1213 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1214 """The default label style, refers to

1215 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1216

1217 .. versionadded:: 1.4

1218

1219 """

1220

1221 LABEL_STYLE_LEGACY_ORM = 3

1222

1223

1224(

1225 LABEL_STYLE_NONE,

1226 LABEL_STYLE_TABLENAME_PLUS_COL,

1227 LABEL_STYLE_DISAMBIGUATE_ONLY,

1228 _,

1229) = list(SelectLabelStyle)

1230

1231LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1232

1233

1234class Join(roles.DMLTableRole, FromClause):

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

1236 :class:`_expression.FromClause`

1237 elements.

1238

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

1240 is the module-level

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

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

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

1244 :class:`_schema.Table`).

1245

1246 .. seealso::

1247

1248 :func:`_expression.join`

1249

1250 :meth:`_expression.FromClause.join`

1251

1252 """

1253

1254 __visit_name__ = "join"

1255

1256 _traverse_internals: _TraverseInternalsType = [

1257 ("left", InternalTraversal.dp_clauseelement),

1258 ("right", InternalTraversal.dp_clauseelement),

1259 ("onclause", InternalTraversal.dp_clauseelement),

1260 ("isouter", InternalTraversal.dp_boolean),

1261 ("full", InternalTraversal.dp_boolean),

1262 ]

1263

1264 _is_join = True

1265

1266 left: FromClause

1267 right: FromClause

1268 onclause: Optional[ColumnElement[bool]]

1269 isouter: bool

1270 full: bool

1271

1272 def __init__(

1273 self,

1274 left: _FromClauseArgument,

1275 right: _FromClauseArgument,

1276 onclause: Optional[_OnClauseArgument] = None,

1277 isouter: bool = False,

1278 full: bool = False,

1279 ):

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

1281

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

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

1284 :class:`_expression.FromClause` object.

1285

1286 """

1287

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

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

1290 # annotated objects. test_orm.py -> test_fetch_results

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

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

1293 # callcounts for a single compilation in that particular test

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

1295 # 29200 -> 30373

1296

1297 self.left = coercions.expect(

1298 roles.FromClauseRole,

1299 left,

1300 )

1301 self.right = coercions.expect(

1302 roles.FromClauseRole,

1303 right,

1304 ).self_group()

1305

1306 if onclause is None:

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

1308 else:

1309 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1310 # not merged yet

1311 self.onclause = coercions.expect(

1312 roles.OnClauseRole, onclause

1313 ).self_group(against=operators._asbool)

1314

1315 self.isouter = isouter

1316 self.full = full

1317

1318 @util.ro_non_memoized_property

1319 def description(self) -> str:

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

1321 self.left.description,

1322 id(self.left),

1323 self.right.description,

1324 id(self.right),

1325 )

1326

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

1328 return (

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

1330 # as well

1331 hash(fromclause) == hash(self)

1332 or self.left.is_derived_from(fromclause)

1333 or self.right.is_derived_from(fromclause)

1334 )

1335

1336 def self_group(

1337 self, against: Optional[OperatorType] = None

1338 ) -> FromGrouping:

1339 return FromGrouping(self)

1340

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

1342 def _populate_column_collection(

1343 self,

1344 columns: ColumnCollection[str, KeyedColumnElement[Any]],

1345 primary_key: ColumnSet,

1346 foreign_keys: Set[KeyedColumnElement[Any]],

1347 ) -> None:

1348 sqlutil = util.preloaded.sql_util

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

1350 c for c in self.right.c

1351 ]

1352

1353 primary_key.extend(

1354 sqlutil.reduce_columns(

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

1356 )

1357 )

1358 columns._populate_separate_keys(

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

1360 )

1361 foreign_keys.update(

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

1363 )

1364

1365 def _copy_internals(

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

1367 ) -> None:

1368 # see Select._copy_internals() for similar concept

1369

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

1371 # determine the new FROM clauses

1372 all_the_froms = set(

1373 itertools.chain(

1374 _from_objects(self.left),

1375 _from_objects(self.right),

1376 )

1377 )

1378

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

1380 # cache used by the clone function

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

1382

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

1384 # ColumnClause with parent table referring to those

1385 # replaced FromClause objects

1386 def replace(

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

1388 **kw: Any,

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

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

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

1392 return newelem

1393 return None

1394

1395 kw["replace"] = replace

1396

1397 # run normal _copy_internals. the clones for

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

1399 # cache

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

1401

1402 self._reset_memoizations()

1403

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

1405 super()._refresh_for_new_column(column)

1406 self.left._refresh_for_new_column(column)

1407 self.right._refresh_for_new_column(column)

1408

1409 def _match_primaries(

1410 self,

1411 left: FromClause,

1412 right: FromClause,

1413 ) -> ColumnElement[bool]:

1414 if isinstance(left, Join):

1415 left_right = left.right

1416 else:

1417 left_right = None

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

1419

1420 @classmethod

1421 def _join_condition(

1422 cls,

1423 a: FromClause,

1424 b: FromClause,

1425 *,

1426 a_subset: Optional[FromClause] = None,

1427 consider_as_foreign_keys: Optional[

1428 AbstractSet[ColumnClause[Any]]

1429 ] = None,

1430 ) -> ColumnElement[bool]:

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

1432

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

1434

1435 """

1436 constraints = cls._joincond_scan_left_right(

1437 a, a_subset, b, consider_as_foreign_keys

1438 )

1439

1440 if len(constraints) > 1:

1441 cls._joincond_trim_constraints(

1442 a, b, constraints, consider_as_foreign_keys

1443 )

1444

1445 if len(constraints) == 0:

1446 if isinstance(b, FromGrouping):

1447 hint = (

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

1449 "subquery using alias()?"

1450 )

1451 else:

1452 hint = ""

1453 raise exc.NoForeignKeysError(

1454 "Can't find any foreign key relationships "

1455 "between '%s' and '%s'.%s"

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

1457 )

1458

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

1460 if len(crit) == 1:

1461 return crit[0]

1462 else:

1463 return and_(*crit)

1464

1465 @classmethod

1466 def _can_join(

1467 cls,

1468 left: FromClause,

1469 right: FromClause,

1470 *,

1471 consider_as_foreign_keys: Optional[

1472 AbstractSet[ColumnClause[Any]]

1473 ] = None,

1474 ) -> bool:

1475 if isinstance(left, Join):

1476 left_right = left.right

1477 else:

1478 left_right = None

1479

1480 constraints = cls._joincond_scan_left_right(

1481 a=left,

1482 b=right,

1483 a_subset=left_right,

1484 consider_as_foreign_keys=consider_as_foreign_keys,

1485 )

1486

1487 return bool(constraints)

1488

1489 @classmethod

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

1491 def _joincond_scan_left_right(

1492 cls,

1493 a: FromClause,

1494 a_subset: Optional[FromClause],

1495 b: FromClause,

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

1497 ) -> collections.defaultdict[

1498 Optional[ForeignKeyConstraint],

1499 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1500 ]:

1501 sql_util = util.preloaded.sql_util

1502

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

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

1505

1506 constraints: collections.defaultdict[

1507 Optional[ForeignKeyConstraint],

1508 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1509 ] = collections.defaultdict(list)

1510

1511 for left in (a_subset, a):

1512 if left is None:

1513 continue

1514 for fk in sorted(

1515 b.foreign_keys,

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

1517 ):

1518 if (

1519 consider_as_foreign_keys is not None

1520 and fk.parent not in consider_as_foreign_keys

1521 ):

1522 continue

1523 try:

1524 col = fk.get_referent(left)

1525 except exc.NoReferenceError as nrte:

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

1527 if nrte.table_name in table_names:

1528 raise

1529 else:

1530 continue

1531

1532 if col is not None:

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

1534 if left is not b:

1535 for fk in sorted(

1536 left.foreign_keys,

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

1538 ):

1539 if (

1540 consider_as_foreign_keys is not None

1541 and fk.parent not in consider_as_foreign_keys

1542 ):

1543 continue

1544 try:

1545 col = fk.get_referent(b)

1546 except exc.NoReferenceError as nrte:

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

1548 if nrte.table_name in table_names:

1549 raise

1550 else:

1551 continue

1552

1553 if col is not None:

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

1555 if constraints:

1556 break

1557 return constraints

1558

1559 @classmethod

1560 def _joincond_trim_constraints(

1561 cls,

1562 a: FromClause,

1563 b: FromClause,

1564 constraints: Dict[Any, Any],

1565 consider_as_foreign_keys: Optional[Any],

1566 ) -> None:

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

1568 # to include just those FKCs that match exactly to

1569 # "consider_as_foreign_keys".

1570 if consider_as_foreign_keys:

1571 for const in list(constraints):

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

1573 consider_as_foreign_keys

1574 ):

1575 del constraints[const]

1576

1577 # if still multiple constraints, but

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

1579 if len(constraints) > 1:

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

1581 if len(dedupe) == 1:

1582 key = list(constraints)[0]

1583 constraints = {key: constraints[key]}

1584

1585 if len(constraints) != 1:

1586 raise exc.AmbiguousForeignKeysError(

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

1588 "tables have more than one foreign key "

1589 "constraint relationship between them. "

1590 "Please specify the 'onclause' of this "

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

1592 )

1593

1594 def select(self) -> Select[Any]:

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

1596 :class:`_expression.Join`.

1597

1598 E.g.::

1599

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

1601

1602 stmt = stmt.select()

1603

1604 The above will produce a SQL string resembling:

1605

1606 .. sourcecode:: sql

1607

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

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

1610

1611 """

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

1613

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

1615 def _anonymous_fromclause(

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

1617 ) -> TODO_Any:

1618 sqlutil = util.preloaded.sql_util

1619 if flat:

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

1621 left_name = name # will recurse

1622 else:

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

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

1625 else:

1626 left_name = name

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

1628 right_name = name # will recurse

1629 else:

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

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

1632 else:

1633 right_name = name

1634 left_a, right_a = (

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

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

1637 )

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

1639 sqlutil.ClauseAdapter(right_a)

1640 )

1641

1642 return left_a.join(

1643 right_a,

1644 adapter.traverse(self.onclause),

1645 isouter=self.isouter,

1646 full=self.full,

1647 )

1648 else:

1649 return (

1650 self.select()

1651 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1652 .correlate(None)

1653 .alias(name)

1654 )

1655

1656 @util.ro_non_memoized_property

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

1658 return itertools.chain(

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

1660 )

1661

1662 @util.ro_non_memoized_property

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

1664 self_list: List[FromClause] = [self]

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

1666

1667

1668class NoInit:

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

1670 raise NotImplementedError(

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

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

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

1674 "selectable objects."

1675 % (

1676 self.__class__.__name__,

1677 self.__class__.__name__.lower(),

1678 self.__class__.__name__.lower(),

1679 )

1680 )

1681

1682

1683class LateralFromClause(NamedFromClause):

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

1685

1686

1687# FromClause ->

1688# AliasedReturnsRows

1689# -> Alias only for FromClause

1690# -> Subquery only for SelectBase

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

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

1693# w/ non-deprecated coercion

1694# -> TableSample -> only for FromClause

1695

1696

1697class AliasedReturnsRows(NoInit, NamedFromClause):

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

1699 selectables."""

1700

1701 _is_from_container = True

1702

1703 _supports_derived_columns = False

1704

1705 element: ReturnsRows

1706

1707 _traverse_internals: _TraverseInternalsType = [

1708 ("element", InternalTraversal.dp_clauseelement),

1709 ("name", InternalTraversal.dp_anon_name),

1710 ]

1711

1712 @classmethod

1713 def _construct(

1714 cls,

1715 selectable: Any,

1716 *,

1717 name: Optional[str] = None,

1718 **kw: Any,

1719 ) -> Self:

1720 obj = cls.__new__(cls)

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

1722 return obj

1723

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

1725 self.element = coercions.expect(

1726 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1727 )

1728 self.element = selectable

1729 self._orig_name = name

1730 if name is None:

1731 if (

1732 isinstance(selectable, FromClause)

1733 and selectable.named_with_column

1734 ):

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

1736 if isinstance(name, _anonymous_label):

1737 name = None

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

1739 self.name = name

1740

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

1742 super()._refresh_for_new_column(column)

1743 self.element._refresh_for_new_column(column)

1744

1745 def _populate_column_collection(

1746 self,

1747 columns: ColumnCollection[str, KeyedColumnElement[Any]],

1748 primary_key: ColumnSet,

1749 foreign_keys: Set[KeyedColumnElement[Any]],

1750 ) -> None:

1751 self.element._generate_fromclause_column_proxies(

1752 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1753 )

1754

1755 @util.ro_non_memoized_property

1756 def description(self) -> str:

1757 name = self.name

1758 if isinstance(name, _anonymous_label):

1759 return "anon_1"

1760

1761 return name

1762

1763 @util.ro_non_memoized_property

1764 def implicit_returning(self) -> bool:

1765 return self.element.implicit_returning # type: ignore

1766

1767 @property

1768 def original(self) -> ReturnsRows:

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

1770 return self.element

1771

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

1773 if fromclause in self._cloned_set:

1774 return True

1775 return self.element.is_derived_from(fromclause)

1776

1777 def _copy_internals(

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

1779 ) -> None:

1780 existing_element = self.element

1781

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

1783

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

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

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

1787 # performance.

1788 if existing_element is not self.element:

1789 self._reset_column_collection()

1790

1791 @property

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

1793 return [self]

1794

1795

1796class FromClauseAlias(AliasedReturnsRows):

1797 element: FromClause

1798

1799 @util.ro_non_memoized_property

1800 def description(self) -> str:

1801 name = self.name

1802 if isinstance(name, _anonymous_label):

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

1804

1805 return name

1806

1807

1808class Alias(roles.DMLTableRole, FromClauseAlias):

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

1810

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

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

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

1814

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

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

1817 method available

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

1819

1820 .. seealso::

1821

1822 :meth:`_expression.FromClause.alias`

1823

1824 """

1825

1826 __visit_name__ = "alias"

1827

1828 inherit_cache = True

1829

1830 element: FromClause

1831

1832 @classmethod

1833 def _factory(

1834 cls,

1835 selectable: FromClause,

1836 name: Optional[str] = None,

1837 flat: bool = False,

1838 ) -> NamedFromClause:

1839 return coercions.expect(

1840 roles.FromClauseRole, selectable, allow_select=True

1841 ).alias(name=name, flat=flat)

1842

1843

1844class TableValuedAlias(LateralFromClause, Alias):

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

1846

1847 This construct provides for a SQL function that returns columns

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

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

1850 method, e.g.:

1851

1852 .. sourcecode:: pycon+sql

1853

1854 >>> from sqlalchemy import select, func

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

1856 ... "value"

1857 ... )

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

1859 {printsql}SELECT anon_1.value

1860 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1861

1862 .. versionadded:: 1.4.0b2

1863

1864 .. seealso::

1865

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

1867

1868 """ # noqa: E501

1869

1870 __visit_name__ = "table_valued_alias"

1871

1872 _supports_derived_columns = True

1873 _render_derived = False

1874 _render_derived_w_types = False

1875 joins_implicitly = False

1876

1877 _traverse_internals: _TraverseInternalsType = [

1878 ("element", InternalTraversal.dp_clauseelement),

1879 ("name", InternalTraversal.dp_anon_name),

1880 ("_tableval_type", InternalTraversal.dp_type),

1881 ("_render_derived", InternalTraversal.dp_boolean),

1882 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1883 ]

1884

1885 def _init(

1886 self,

1887 selectable: Any,

1888 *,

1889 name: Optional[str] = None,

1890 table_value_type: Optional[TableValueType] = None,

1891 joins_implicitly: bool = False,

1892 ) -> None:

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

1894

1895 self.joins_implicitly = joins_implicitly

1896 self._tableval_type = (

1897 type_api.TABLEVALUE

1898 if table_value_type is None

1899 else table_value_type

1900 )

1901

1902 @HasMemoized.memoized_attribute

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

1904 """Return a column expression representing this

1905 :class:`_sql.TableValuedAlias`.

1906

1907 This accessor is used to implement the

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

1909 method for further details.

1910

1911 E.g.:

1912

1913 .. sourcecode:: pycon+sql

1914

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

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

1917

1918 .. seealso::

1919

1920 :meth:`_functions.FunctionElement.column_valued`

1921

1922 """

1923

1924 return TableValuedColumn(self, self._tableval_type)

1925

1926 def alias(

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

1928 ) -> TableValuedAlias:

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

1930

1931 This creates a distinct FROM object that will be distinguished

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

1933

1934 """

1935

1936 tva: TableValuedAlias = TableValuedAlias._construct(

1937 self,

1938 name=name,

1939 table_value_type=self._tableval_type,

1940 joins_implicitly=self.joins_implicitly,

1941 )

1942

1943 if self._render_derived:

1944 tva._render_derived = True

1945 tva._render_derived_w_types = self._render_derived_w_types

1946

1947 return tva

1948

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

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

1951 set, so that it renders as LATERAL.

1952

1953 .. seealso::

1954

1955 :func:`_expression.lateral`

1956

1957 """

1958 tva = self.alias(name=name)

1959 tva._is_lateral = True

1960 return tva

1961

1962 def render_derived(

1963 self,

1964 name: Optional[str] = None,

1965 with_types: bool = False,

1966 ) -> TableValuedAlias:

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

1968

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

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

1971

1972 .. sourcecode:: pycon+sql

1973

1974 >>> print(

1975 ... select(

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

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

1978 ... .render_derived()

1979 ... )

1980 ... )

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

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

1983

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

1985 the alias expression (this syntax currently applies to the

1986 PostgreSQL database):

1987

1988 .. sourcecode:: pycon+sql

1989

1990 >>> print(

1991 ... select(

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

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

1994 ... .render_derived(with_types=True)

1995 ... )

1996 ... )

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

1998 AS anon_1(a INTEGER, b VARCHAR)

1999

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

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

2002

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

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

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

2006

2007 """ # noqa: E501

2008

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

2010 # to the original object. otherwise you can have re-use of the

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

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

2013 # (just saw it happen on CI)

2014

2015 # construct against original to prevent memory growth

2016 # for repeated generations

2017 new_alias: TableValuedAlias = TableValuedAlias._construct(

2018 self.element,

2019 name=name,

2020 table_value_type=self._tableval_type,

2021 joins_implicitly=self.joins_implicitly,

2022 )

2023 new_alias._render_derived = True

2024 new_alias._render_derived_w_types = with_types

2025 return new_alias

2026

2027

2028class Lateral(FromClauseAlias, LateralFromClause):

2029 """Represent a LATERAL subquery.

2030

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

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

2033 method available

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

2035

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

2037 PostgreSQL versions provide support for this keyword.

2038

2039 .. seealso::

2040

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

2042

2043 """

2044

2045 __visit_name__ = "lateral"

2046 _is_lateral = True

2047

2048 inherit_cache = True

2049

2050 @classmethod

2051 def _factory(

2052 cls,

2053 selectable: Union[SelectBase, _FromClauseArgument],

2054 name: Optional[str] = None,

2055 ) -> LateralFromClause:

2056 return coercions.expect(

2057 roles.FromClauseRole, selectable, explicit_subquery=True

2058 ).lateral(name=name)

2059

2060

2061class TableSample(FromClauseAlias):

2062 """Represent a TABLESAMPLE clause.

2063

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

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

2066 method

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

2068

2069 .. seealso::

2070

2071 :func:`_expression.tablesample`

2072

2073 """

2074

2075 __visit_name__ = "tablesample"

2076

2077 _traverse_internals: _TraverseInternalsType = (

2078 AliasedReturnsRows._traverse_internals

2079 + [

2080 ("sampling", InternalTraversal.dp_clauseelement),

2081 ("seed", InternalTraversal.dp_clauseelement),

2082 ]

2083 )

2084

2085 @classmethod

2086 def _factory(

2087 cls,

2088 selectable: _FromClauseArgument,

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

2090 name: Optional[str] = None,

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

2092 ) -> TableSample:

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

2094 sampling, name=name, seed=seed

2095 )

2096

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

2098 def _init( # type: ignore[override]

2099 self,

2100 selectable: Any,

2101 *,

2102 name: Optional[str] = None,

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

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

2105 ) -> None:

2106 assert sampling is not None

2107 functions = util.preloaded.sql_functions

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

2109 sampling = functions.func.system(sampling)

2110

2111 self.sampling: Function[Any] = sampling

2112 self.seed = seed

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

2114

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

2116 return self.sampling

2117

2118

2119class CTE(

2120 roles.DMLTableRole,

2121 roles.IsCTERole,

2122 Generative,

2123 HasPrefixes,

2124 HasSuffixes,

2125 AliasedReturnsRows,

2126):

2127 """Represent a Common Table Expression.

2128

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

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

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

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

2133 :class:`_sql.Update` and

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

2135 usage details on CTEs.

2136

2137 .. seealso::

2138

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

2140

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

2142

2143 """

2144

2145 __visit_name__ = "cte"

2146

2147 _traverse_internals: _TraverseInternalsType = (

2148 AliasedReturnsRows._traverse_internals

2149 + [

2150 ("_cte_alias", InternalTraversal.dp_clauseelement),

2151 ("_restates", InternalTraversal.dp_clauseelement),

2152 ("recursive", InternalTraversal.dp_boolean),

2153 ("nesting", InternalTraversal.dp_boolean),

2154 ]

2155 + HasPrefixes._has_prefixes_traverse_internals

2156 + HasSuffixes._has_suffixes_traverse_internals

2157 )

2158

2159 element: HasCTE

2160

2161 @classmethod

2162 def _factory(

2163 cls,

2164 selectable: HasCTE,

2165 name: Optional[str] = None,

2166 recursive: bool = False,

2167 ) -> CTE:

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

2169 or Common Table Expression instance.

2170

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

2172

2173 """

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

2175 name=name, recursive=recursive

2176 )

2177

2178 def _init(

2179 self,

2180 selectable: Select[Any],

2181 *,

2182 name: Optional[str] = None,

2183 recursive: bool = False,

2184 nesting: bool = False,

2185 _cte_alias: Optional[CTE] = None,

2186 _restates: Optional[CTE] = None,

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

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

2189 ) -> None:

2190 self.recursive = recursive

2191 self.nesting = nesting

2192 self._cte_alias = _cte_alias

2193 # Keep recursivity reference with union/union_all

2194 self._restates = _restates

2195 if _prefixes:

2196 self._prefixes = _prefixes

2197 if _suffixes:

2198 self._suffixes = _suffixes

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

2200

2201 def _populate_column_collection(

2202 self,

2203 columns: ColumnCollection[str, KeyedColumnElement[Any]],

2204 primary_key: ColumnSet,

2205 foreign_keys: Set[KeyedColumnElement[Any]],

2206 ) -> None:

2207 if self._cte_alias is not None:

2208 self._cte_alias._generate_fromclause_column_proxies(

2209 self,

2210 columns,

2211 primary_key=primary_key,

2212 foreign_keys=foreign_keys,

2213 )

2214 else:

2215 self.element._generate_fromclause_column_proxies(

2216 self,

2217 columns,

2218 primary_key=primary_key,

2219 foreign_keys=foreign_keys,

2220 )

2221

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

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

2224 :class:`_expression.CTE`.

2225

2226 This method is a CTE-specific specialization of the

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

2228

2229 .. seealso::

2230

2231 :ref:`tutorial_using_aliases`

2232

2233 :func:`_expression.alias`

2234

2235 """

2236 return CTE._construct(

2237 self.element,

2238 name=name,

2239 recursive=self.recursive,

2240 nesting=self.nesting,

2241 _cte_alias=self,

2242 _prefixes=self._prefixes,

2243 _suffixes=self._suffixes,

2244 )

2245

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

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

2248 of the original CTE against the given selectables provided

2249 as positional arguments.

2250

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

2252 UNION.

2253

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

2255

2256 .. seealso::

2257

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

2259

2260 """

2261 assert is_select_statement(

2262 self.element

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

2264

2265 return CTE._construct(

2266 self.element.union(*other),

2267 name=self.name,

2268 recursive=self.recursive,

2269 nesting=self.nesting,

2270 _restates=self,

2271 _prefixes=self._prefixes,

2272 _suffixes=self._suffixes,

2273 )

2274

2275 def union_all(

2276 self, *other: _SelectStatementForCompoundArgument[Any]

2277 ) -> CTE:

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

2279 of the original CTE against the given selectables provided

2280 as positional arguments.

2281

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

2283 UNION.

2284

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

2286

2287 .. seealso::

2288

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

2290

2291 """

2292

2293 assert is_select_statement(

2294 self.element

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

2296

2297 return CTE._construct(

2298 self.element.union_all(*other),

2299 name=self.name,

2300 recursive=self.recursive,

2301 nesting=self.nesting,

2302 _restates=self,

2303 _prefixes=self._prefixes,

2304 _suffixes=self._suffixes,

2305 )

2306

2307 def _get_reference_cte(self) -> CTE:

2308 """

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

2310 Updated CTEs should still refer to the original CTE.

2311 This function returns this reference identifier.

2312 """

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

2314

2315

2316class _CTEOpts(NamedTuple):

2317 nesting: bool

2318

2319

2320class _ColumnsPlusNames(NamedTuple):

2321 required_label_name: Optional[str]

2322 """

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

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

2325 """

2326

2327 proxy_key: Optional[str]

2328 """

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

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

2331 select.selected_columns collection

2332 """

2333

2334 fallback_label_name: Optional[str]

2335 """

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

2337 we have to render a label even though

2338 required_label_name was not given

2339 """

2340

2341 column: Union[ColumnElement[Any], TextClause]

2342 """

2343 the ColumnElement itself

2344 """

2345

2346 repeated: bool

2347 """

2348 True if this is a duplicate of a previous column

2349 in the list of columns

2350 """

2351

2352

2353class SelectsRows(ReturnsRows):

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

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

2356

2357 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2358

2359 def _generate_columns_plus_names(

2360 self,

2361 anon_for_dupe_key: bool,

2362 cols: Optional[_SelectIterable] = None,

2363 ) -> List[_ColumnsPlusNames]:

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

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

2366 on a :class:`.FromClause`.

2367

2368 This is distinct from the _column_naming_convention generator that's

2369 intended for population of the Select.selected_columns collection,

2370 different rules. the collection returned here calls upon the

2371 _column_naming_convention as well.

2372

2373 """

2374

2375 if cols is None:

2376 cols = self._all_selected_columns

2377

2378 key_naming_convention = SelectState._column_naming_convention(

2379 self._label_style

2380 )

2381

2382 names = {}

2383

2384 result: List[_ColumnsPlusNames] = []

2385 result_append = result.append

2386

2387 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2388 label_style_none = self._label_style is LABEL_STYLE_NONE

2389

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

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

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

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

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

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

2396 dedupe_hash = 1

2397

2398 for c in cols:

2399 repeated = False

2400

2401 if not c._render_label_in_columns_clause:

2402 effective_name = required_label_name = fallback_label_name = (

2403 None

2404 )

2405 elif label_style_none:

2406 if TYPE_CHECKING:

2407 assert is_column_element(c)

2408

2409 effective_name = required_label_name = None

2410 fallback_label_name = c._non_anon_label or c._anon_name_label

2411 else:

2412 if TYPE_CHECKING:

2413 assert is_column_element(c)

2414

2415 if table_qualified:

2416 required_label_name = effective_name = (

2417 fallback_label_name

2418 ) = c._tq_label

2419 else:

2420 effective_name = fallback_label_name = c._non_anon_label

2421 required_label_name = None

2422

2423 if effective_name is None:

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

2425 # not need _expression_label but it isn't

2426 # giving us a clue when to use anon_label instead

2427 expr_label = c._expression_label

2428 if expr_label is None:

2429 repeated = c._anon_name_label in names

2430 names[c._anon_name_label] = c

2431 effective_name = required_label_name = None

2432

2433 if repeated:

2434 # here, "required_label_name" is sent as

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

2436 if table_qualified:

2437 fallback_label_name = (

2438 c._dedupe_anon_tq_label_idx(dedupe_hash)

2439 )

2440 dedupe_hash += 1

2441 else:

2442 fallback_label_name = c._dedupe_anon_label_idx(

2443 dedupe_hash

2444 )

2445 dedupe_hash += 1

2446 else:

2447 fallback_label_name = c._anon_name_label

2448 else:

2449 required_label_name = effective_name = (

2450 fallback_label_name

2451 ) = expr_label

2452

2453 if effective_name is not None:

2454 if TYPE_CHECKING:

2455 assert is_column_element(c)

2456

2457 if effective_name in names:

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

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

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

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

2462 # different column under the same name. apply

2463 # disambiguating label

2464 if table_qualified:

2465 required_label_name = fallback_label_name = (

2466 c._anon_tq_label

2467 )

2468 else:

2469 required_label_name = fallback_label_name = (

2470 c._anon_name_label

2471 )

2472

2473 if anon_for_dupe_key and required_label_name in names:

2474 # here, c._anon_tq_label is definitely unique to

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

2476 # this should always be true.

2477 # this is also an infrequent codepath because

2478 # you need two levels of duplication to be here

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

2480

2481 # the column under the disambiguating label is

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

2483 # subsequent occurrences of the column so that the

2484 # original stays non-ambiguous

2485 if table_qualified:

2486 required_label_name = fallback_label_name = (

2487 c._dedupe_anon_tq_label_idx(dedupe_hash)

2488 )

2489 dedupe_hash += 1

2490 else:

2491 required_label_name = fallback_label_name = (

2492 c._dedupe_anon_label_idx(dedupe_hash)

2493 )

2494 dedupe_hash += 1

2495 repeated = True

2496 else:

2497 names[required_label_name] = c

2498 elif anon_for_dupe_key:

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

2500 # label so that the original stays non-ambiguous

2501 if table_qualified:

2502 required_label_name = fallback_label_name = (

2503 c._dedupe_anon_tq_label_idx(dedupe_hash)

2504 )

2505 dedupe_hash += 1

2506 else:

2507 required_label_name = fallback_label_name = (

2508 c._dedupe_anon_label_idx(dedupe_hash)

2509 )

2510 dedupe_hash += 1

2511 repeated = True

2512 else:

2513 names[effective_name] = c

2514

2515 result_append(

2516 _ColumnsPlusNames(

2517 required_label_name,

2518 key_naming_convention(c),

2519 fallback_label_name,

2520 c,

2521 repeated,

2522 )

2523 )

2524

2525 return result

2526

2527

2528class HasCTE(roles.HasCTERole, SelectsRows):

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

2530

2531 _has_ctes_traverse_internals: _TraverseInternalsType = [

2532 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2533 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2534 ]

2535

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

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

2538

2539 name_cte_columns: bool = False

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

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

2542

2543 .. versionadded:: 2.0.42

2544

2545 """

2546

2547 @_generative

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

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

2550

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

2552 the parent statement such that they will each be unconditionally

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

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

2555

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

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

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

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

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

2561 statement.

2562

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

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

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

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

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

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

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

2570 larger statement.

2571

2572 E.g.::

2573

2574 from sqlalchemy import table, column, select

2575

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

2577

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

2579

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

2581

2582 Would render:

2583

2584 .. sourcecode:: sql

2585

2586 WITH anon_1 AS (

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

2588 )

2589 SELECT t.c1, t.c2

2590 FROM t

2591

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

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

2594 statement.

2595

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

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

2598

2599 from sqlalchemy import table, column

2600 from sqlalchemy.dialects.postgresql import insert

2601

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

2603

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

2605

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

2607 update_statement = insert_stmt.on_conflict_do_update(

2608 index_elements=[t.c.c1],

2609 set_={

2610 "c1": insert_stmt.excluded.c1,

2611 "c2": insert_stmt.excluded.c2,

2612 },

2613 ).add_cte(delete_statement_cte)

2614

2615 print(update_statement)

2616

2617 The above statement renders as:

2618

2619 .. sourcecode:: sql

2620

2621 WITH deletions AS (

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

2623 )

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

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

2626

2627 .. versionadded:: 1.4.21

2628

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

2630

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

2632

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

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

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

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

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

2638 this statement when this flag is given.

2639

2640 .. versionadded:: 2.0

2641

2642 .. seealso::

2643

2644 :paramref:`.HasCTE.cte.nesting`

2645

2646

2647 """ # noqa: E501

2648 opt = _CTEOpts(nest_here)

2649 for cte in ctes:

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

2651 self._independent_ctes += (cte,)

2652 self._independent_ctes_opts += (opt,)

2653 return self

2654

2655 def cte(

2656 self,

2657 name: Optional[str] = None,

2658 recursive: bool = False,

2659 nesting: bool = False,

2660 ) -> CTE:

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

2662 or Common Table Expression instance.

2663

2664 Common table expressions are a SQL standard whereby SELECT

2665 statements can draw upon secondary statements specified along

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

2667 Special semantics regarding UNION can also be employed to

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

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

2670

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

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

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

2674 CTE rows.

2675

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

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

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

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

2680

2681 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2683 method may be

2684 used to establish these.

2685

2686 .. versionchanged:: 1.3.13 Added support for prefixes.

2687 In particular - MATERIALIZED and NOT MATERIALIZED.

2688

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

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

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

2692 compile time.

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

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

2695 conjunction with UNION ALL in order to derive rows

2696 from those already selected.

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

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

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

2700 :paramref:`.HasCTE.add_cte.nest_here`

2701 parameter may also be used to more carefully

2702 control the exact placement of a particular CTE.

2703

2704 .. versionadded:: 1.4.24

2705

2706 .. seealso::

2707

2708 :meth:`.HasCTE.add_cte`

2709

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

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

2712 as well as additional examples.

2713

2714 Example 1, non recursive::

2715

2716 from sqlalchemy import (

2717 Table,

2718 Column,

2719 String,

2720 Integer,

2721 MetaData,

2722 select,

2723 func,

2724 )

2725

2726 metadata = MetaData()

2727

2728 orders = Table(

2729 "orders",

2730 metadata,

2731 Column("region", String),

2732 Column("amount", Integer),

2733 Column("product", String),

2734 Column("quantity", Integer),

2735 )

2736

2737 regional_sales = (

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

2739 .group_by(orders.c.region)

2740 .cte("regional_sales")

2741 )

2742

2743

2744 top_regions = (

2745 select(regional_sales.c.region)

2746 .where(

2747 regional_sales.c.total_sales

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

2749 )

2750 .cte("top_regions")

2751 )

2752

2753 statement = (

2754 select(

2755 orders.c.region,

2756 orders.c.product,

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

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

2759 )

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

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

2762 )

2763

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

2765

2766 Example 2, WITH RECURSIVE::

2767

2768 from sqlalchemy import (

2769 Table,

2770 Column,

2771 String,

2772 Integer,

2773 MetaData,

2774 select,

2775 func,

2776 )

2777

2778 metadata = MetaData()

2779

2780 parts = Table(

2781 "parts",

2782 metadata,

2783 Column("part", String),

2784 Column("sub_part", String),

2785 Column("quantity", Integer),

2786 )

2787

2788 included_parts = (

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

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

2791 .cte(recursive=True)

2792 )

2793

2794

2795 incl_alias = included_parts.alias()

2796 parts_alias = parts.alias()

2797 included_parts = included_parts.union_all(

2798 select(

2799 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2801 )

2802

2803 statement = select(

2804 included_parts.c.sub_part,

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

2806 ).group_by(included_parts.c.sub_part)

2807

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

2809

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

2811

2812 from datetime import date

2813 from sqlalchemy import (

2814 MetaData,

2815 Table,

2816 Column,

2817 Integer,

2818 Date,

2819 select,

2820 literal,

2821 and_,

2822 exists,

2823 )

2824

2825 metadata = MetaData()

2826

2827 visitors = Table(

2828 "visitors",

2829 metadata,

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

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

2832 Column("count", Integer),

2833 )

2834

2835 # add 5 visitors for the product_id == 1

2836 product_id = 1

2837 day = date.today()

2838 count = 5

2839

2840 update_cte = (

2841 visitors.update()

2842 .where(

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

2844 )

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

2846 .returning(literal(1))

2847 .cte("update_cte")

2848 )

2849

2850 upsert = visitors.insert().from_select(

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

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

2853 ~exists(update_cte.select())

2854 ),

2855 )

2856

2857 connection.execute(upsert)

2858

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

2860

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

2862

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

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

2865 "value_a", nesting=True

2866 )

2867

2868 # Nesting CTEs takes ascendency locally

2869 # over the CTEs at a higher level

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

2871

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

2873

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

2875 shown with inline parameters below as:

2876

2877 .. sourcecode:: sql

2878

2879 WITH

2880 value_a AS

2881 (SELECT 'root' AS n),

2882 value_b AS

2883 (WITH value_a AS

2884 (SELECT 'nesting' AS n)

2885 SELECT value_a.n AS n FROM value_a)

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

2887 FROM value_a, value_b

2888

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

2890 as follows (SQLAlchemy 2.0 and above)::

2891

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

2893

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

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

2896

2897 # Nesting CTEs takes ascendency locally

2898 # over the CTEs at a higher level

2899 value_b = (

2900 select(value_a_nested.c.n)

2901 .add_cte(value_a_nested, nest_here=True)

2902 .cte("value_b")

2903 )

2904

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

2906

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

2908

2909 edge = Table(

2910 "edge",

2911 metadata,

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

2913 Column("left", Integer),

2914 Column("right", Integer),

2915 )

2916

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

2918

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

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

2921 )

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

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

2924 )

2925

2926 subgraph_cte = root_node.union(left_edge, right_edge)

2927

2928 subgraph = select(subgraph_cte)

2929

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

2931

2932 .. sourcecode:: sql

2933

2934 WITH RECURSIVE nodes(node) AS (

2935 SELECT 1 AS node

2936 UNION

2937 SELECT edge."left" AS "left"

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

2939 UNION

2940 SELECT edge."right" AS "right"

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

2942 )

2943 SELECT nodes.node FROM nodes

2944

2945 .. seealso::

2946

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

2948 :meth:`_expression.HasCTE.cte`.

2949

2950 """ # noqa: E501

2951 return CTE._construct(

2952 self, name=name, recursive=recursive, nesting=nesting

2953 )

2954

2955

2956class Subquery(AliasedReturnsRows):

2957 """Represent a subquery of a SELECT.

2958

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

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

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

2962 :class:`_expression.SelectBase` subclass

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

2964 :class:`_expression.CompoundSelect`, and

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

2966 it represents the

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

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

2969

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

2971 :class:`_expression.Alias`

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

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

2974 :class:`_expression.Alias` always

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

2976 :class:`.Subquery`

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

2978

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

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

2981 statement.

2982

2983 """

2984

2985 __visit_name__ = "subquery"

2986

2987 _is_subquery = True

2988

2989 inherit_cache = True

2990

2991 element: SelectBase

2992

2993 @classmethod

2994 def _factory(

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

2996 ) -> Subquery:

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

2998

2999 return coercions.expect(

3000 roles.SelectStatementRole, selectable

3001 ).subquery(name=name)

3002

3003 @util.deprecated(

3004 "1.4",

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

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

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

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

3009 ":func:`_expression.select` "

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

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

3012 )

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

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

3015

3016

3017class FromGrouping(GroupedElement, FromClause):

3018 """Represent a grouping of a FROM clause"""

3019

3020 _traverse_internals: _TraverseInternalsType = [

3021 ("element", InternalTraversal.dp_clauseelement)

3022 ]

3023

3024 element: FromClause

3025

3026 def __init__(self, element: FromClause):

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

3028

3029 @util.ro_non_memoized_property

3030 def columns(

3031 self,

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

3033 return self.element.columns

3034

3035 @util.ro_non_memoized_property

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

3037 return self.element.columns

3038

3039 @property

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

3041 return self.element.primary_key

3042

3043 @property

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

3045 return self.element.foreign_keys

3046

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

3048 return self.element.is_derived_from(fromclause)

3049

3050 def alias(

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

3052 ) -> NamedFromGrouping:

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

3054

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

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

3057

3058 @util.ro_non_memoized_property

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

3060 return self.element._hide_froms

3061

3062 @util.ro_non_memoized_property

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

3064 return self.element._from_objects

3065

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

3067 return {"element": self.element}

3068

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

3070 self.element = state["element"]

3071

3072 if TYPE_CHECKING:

3073

3074 def self_group(

3075 self, against: Optional[OperatorType] = None

3076 ) -> Self: ...

3077

3078

3079class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3081

3082 .. versionadded:: 2.0

3083

3084 """

3085

3086 inherit_cache = True

3087

3088 if TYPE_CHECKING:

3089

3090 def self_group(

3091 self, against: Optional[OperatorType] = None

3092 ) -> Self: ...

3093

3094

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

3096 """Represents a minimal "table" construct.

3097

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

3099 collection of columns, which are typically produced

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

3101

3102 from sqlalchemy import table, column

3103

3104 user = table(

3105 "user",

3106 column("id"),

3107 column("name"),

3108 column("description"),

3109 )

3110

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

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

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

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

3115

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

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

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

3119 It's useful

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

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

3122 is not on hand.

3123

3124 """

3125

3126 __visit_name__ = "table"

3127

3128 _traverse_internals: _TraverseInternalsType = [

3129 (

3130 "columns",

3131 InternalTraversal.dp_fromclause_canonical_column_collection,

3132 ),

3133 ("name", InternalTraversal.dp_string),

3134 ("schema", InternalTraversal.dp_string),

3135 ]

3136

3137 _is_table = True

3138

3139 fullname: str

3140

3141 implicit_returning = False

3142 """:class:`_expression.TableClause`

3143 doesn't support having a primary key or column

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

3145

3146 @util.ro_memoized_property

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

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

3149 return None

3150

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

3152 super().__init__()

3153 self.name = name

3154 self._columns = DedupeColumnCollection()

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

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

3157 for c in columns:

3158 self.append_column(c)

3159

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

3161 if schema is not None:

3162 self.schema = schema

3163 if self.schema is not None:

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

3165 else:

3166 self.fullname = self.name

3167 if kw:

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

3169

3170 if TYPE_CHECKING:

3171

3172 @util.ro_non_memoized_property

3173 def columns(

3174 self,

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

3176

3177 @util.ro_non_memoized_property

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

3179

3180 def __str__(self) -> str:

3181 if self.schema is not None:

3182 return self.schema + "." + self.name

3183 else:

3184 return self.name

3185

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

3187 pass

3188

3189 @util.ro_memoized_property

3190 def description(self) -> str:

3191 return self.name

3192

3193 def append_column(self, c: ColumnClause[Any]) -> None:

3194 existing = c.table

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

3196 raise exc.ArgumentError(

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

3198 % (c.key, existing)

3199 )

3200

3201 self._columns.add(c)

3202 c.table = self

3203

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

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

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

3207 :class:`_expression.TableClause`.

3208

3209 E.g.::

3210

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

3212

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

3214

3215 """

3216

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

3218

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

3220 def update(self) -> Update:

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

3222 :class:`_expression.TableClause`.

3223

3224 E.g.::

3225

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

3227

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

3229

3230 """

3231 return util.preloaded.sql_dml.Update(

3232 self,

3233 )

3234

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

3236 def delete(self) -> Delete:

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

3238 :class:`_expression.TableClause`.

3239

3240 E.g.::

3241

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

3243

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

3245

3246 """

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

3248

3249 @util.ro_non_memoized_property

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

3251 return [self]

3252

3253

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

3255

3256

3257class ForUpdateArg(ClauseElement):

3258 _traverse_internals: _TraverseInternalsType = [

3259 ("of", InternalTraversal.dp_clauseelement_list),

3260 ("nowait", InternalTraversal.dp_boolean),

3261 ("read", InternalTraversal.dp_boolean),

3262 ("skip_locked", InternalTraversal.dp_boolean),

3263 ("key_share", InternalTraversal.dp_boolean),

3264 ]

3265

3266 of: Optional[Sequence[ClauseElement]]

3267 nowait: bool

3268 read: bool

3269 skip_locked: bool

3270

3271 @classmethod

3272 def _from_argument(

3273 cls, with_for_update: ForUpdateParameter

3274 ) -> Optional[ForUpdateArg]:

3275 if isinstance(with_for_update, ForUpdateArg):

3276 return with_for_update

3277 elif with_for_update in (None, False):

3278 return None

3279 elif with_for_update is True:

3280 return ForUpdateArg()

3281 else:

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

3283

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

3285 return (

3286 isinstance(other, ForUpdateArg)

3287 and other.nowait == self.nowait

3288 and other.read == self.read

3289 and other.skip_locked == self.skip_locked

3290 and other.key_share == self.key_share

3291 and other.of is self.of

3292 )

3293

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

3295 return not self.__eq__(other)

3296

3297 def __hash__(self) -> int:

3298 return id(self)

3299

3300 def __init__(

3301 self,

3302 *,

3303 nowait: bool = False,

3304 read: bool = False,

3305 of: Optional[_ForUpdateOfArgument] = None,

3306 skip_locked: bool = False,

3307 key_share: bool = False,

3308 ):

3309 """Represents arguments specified to

3310 :meth:`_expression.Select.for_update`.

3311

3312 """

3313

3314 self.nowait = nowait

3315 self.read = read

3316 self.skip_locked = skip_locked

3317 self.key_share = key_share

3318 if of is not None:

3319 self.of = [

3320 coercions.expect(roles.ColumnsClauseRole, elem)

3321 for elem in util.to_list(of)

3322 ]

3323 else:

3324 self.of = None

3325

3326

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

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

3329 in a statement.

3330

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

3332 :func:`_expression.values` function.

3333

3334 .. versionadded:: 1.4

3335

3336 """

3337

3338 __visit_name__ = "values"

3339

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

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

3342

3343 _unnamed: bool

3344 _traverse_internals: _TraverseInternalsType = [

3345 ("_column_args", InternalTraversal.dp_clauseelement_list),

3346 ("_data", InternalTraversal.dp_dml_multi_values),

3347 ("name", InternalTraversal.dp_string),

3348 ("literal_binds", InternalTraversal.dp_boolean),

3349 ] + HasCTE._has_ctes_traverse_internals

3350

3351 name_cte_columns = True

3352

3353 def __init__(

3354 self,

3355 *columns: _OnlyColumnArgument[Any],

3356 name: Optional[str] = None,

3357 literal_binds: bool = False,

3358 ):

3359 super().__init__()

3360 self._column_args = tuple(

3361 coercions.expect(roles.LabeledColumnExprRole, col)

3362 for col in columns

3363 )

3364

3365 if name is None:

3366 self._unnamed = True

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

3368 else:

3369 self._unnamed = False

3370 self.name = name

3371 self.literal_binds = literal_binds

3372 self.named_with_column = not self._unnamed

3373

3374 @property

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

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

3377

3378 @util.ro_non_memoized_property

3379 def _all_selected_columns(self) -> _SelectIterable:

3380 return self._column_args

3381

3382 @_generative

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

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

3385 construct that is a copy of this

3386 one with the given name.

3387

3388 This method is a VALUES-specific specialization of the

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

3390

3391 .. seealso::

3392

3393 :ref:`tutorial_using_aliases`

3394

3395 :func:`_expression.alias`

3396

3397 """

3398 non_none_name: str

3399

3400 if name is None:

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

3402 else:

3403 non_none_name = name

3404

3405 self.name = non_none_name

3406 self.named_with_column = True

3407 self._unnamed = False

3408 return self

3409

3410 @_generative

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

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

3413 so that

3414 it renders as LATERAL.

3415

3416 .. seealso::

3417

3418 :func:`_expression.lateral`

3419

3420 """

3421 non_none_name: str

3422

3423 if name is None:

3424 non_none_name = self.name

3425 else:

3426 non_none_name = name

3427

3428 self._is_lateral = True

3429 self.name = non_none_name

3430 self._unnamed = False

3431 return self

3432

3433 @_generative

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

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

3436 adding the given data to the data list.

3437

3438 E.g.::

3439

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

3441

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

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

3444 constructor.

3445

3446 """

3447

3448 self._data += (values,)

3449 return self

3450

3451 def scalar_values(self) -> ScalarValues:

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

3453 COLUMN element in a statement.

3454

3455 .. versionadded:: 2.0.0b4

3456

3457 """

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

3459

3460 def _populate_column_collection(

3461 self,

3462 columns: ColumnCollection[str, KeyedColumnElement[Any]],

3463 primary_key: ColumnSet,

3464 foreign_keys: Set[KeyedColumnElement[Any]],

3465 ) -> None:

3466 for c in self._column_args:

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

3468 _, c = c._make_proxy(

3469 self, primary_key=primary_key, foreign_keys=foreign_keys

3470 )

3471 else:

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

3473 # no memoizations of other FROM clauses.

3474 # see test_values.py -> test_auto_proxy_select_direct_col

3475 c._reset_memoizations()

3476 columns.add(c)

3477 c.table = self

3478

3479 @util.ro_non_memoized_property

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

3481 return [self]

3482

3483

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

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

3486 COLUMN element in a statement.

3487

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

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

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

3491 an ``IN`` or ``NOT IN`` condition.

3492

3493 .. versionadded:: 2.0.0b4

3494

3495 """

3496

3497 __visit_name__ = "scalar_values"

3498

3499 _traverse_internals: _TraverseInternalsType = [

3500 ("_column_args", InternalTraversal.dp_clauseelement_list),

3501 ("_data", InternalTraversal.dp_dml_multi_values),

3502 ("literal_binds", InternalTraversal.dp_boolean),

3503 ]

3504

3505 def __init__(

3506 self,

3507 columns: Sequence[NamedColumn[Any]],

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

3509 literal_binds: bool,

3510 ):

3511 super().__init__()

3512 self._column_args = columns

3513 self._data = data

3514 self.literal_binds = literal_binds

3515

3516 @property

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

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

3519

3520 def __clause_element__(self) -> ScalarValues:

3521 return self

3522

3523 if TYPE_CHECKING:

3524

3525 def self_group(

3526 self, against: Optional[OperatorType] = None

3527 ) -> Self: ...

3528

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

3530

3531

3532class SelectBase(

3533 roles.SelectStatementRole,

3534 roles.DMLSelectRole,

3535 roles.CompoundElementRole,

3536 roles.InElementRole,

3537 HasCTE,

3538 SupportsCloneAnnotations,

3539 Selectable,

3540):

3541 """Base class for SELECT statements.

3542

3543

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

3545 :class:`_expression.CompoundSelect` and

3546 :class:`_expression.TextualSelect`.

3547

3548

3549 """

3550

3551 _is_select_base = True

3552 is_select = True

3553

3554 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3555

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

3557 self._reset_memoizations()

3558

3559 @util.ro_non_memoized_property

3560 def selected_columns(

3561 self,

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

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

3564 representing the columns that

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

3566

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

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

3569 within this collection cannot be directly nested inside another SELECT

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

3571 necessary parenthesization required by SQL.

3572

3573 .. note::

3574

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

3576 include expressions established in the columns clause using the

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

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

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

3580 construct.

3581

3582 .. seealso::

3583

3584 :attr:`_sql.Select.selected_columns`

3585

3586 .. versionadded:: 1.4

3587

3588 """

3589 raise NotImplementedError()

3590

3591 def _generate_fromclause_column_proxies(

3592 self,

3593 subquery: FromClause,

3594 columns: ColumnCollection[str, KeyedColumnElement[Any]],

3595 primary_key: ColumnSet,

3596 foreign_keys: Set[KeyedColumnElement[Any]],

3597 *,

3598 proxy_compound_columns: Optional[

3599 Iterable[Sequence[ColumnElement[Any]]]

3600 ] = None,

3601 ) -> None:

3602 raise NotImplementedError()

3603

3604 @util.ro_non_memoized_property

3605 def _all_selected_columns(self) -> _SelectIterable:

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

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

3608 constructs.

3609

3610 .. versionadded:: 1.4.12

3611

3612 .. seealso::

3613

3614 :attr:`_sql.SelectBase.exported_columns`

3615

3616 """

3617 raise NotImplementedError()

3618

3619 @property

3620 def exported_columns(

3621 self,

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

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

3624 that represents the "exported"

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

3626 :class:`_sql.TextClause` constructs.

3627

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

3629 object are synonymous

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

3631

3632 .. versionadded:: 1.4

3633

3634 .. seealso::

3635

3636 :attr:`_expression.Select.exported_columns`

3637

3638 :attr:`_expression.Selectable.exported_columns`

3639

3640 :attr:`_expression.FromClause.exported_columns`

3641

3642

3643 """

3644 return self.selected_columns.as_readonly()

3645

3646 @property

3647 @util.deprecated(

3648 "1.4",

3649 "The :attr:`_expression.SelectBase.c` and "

3650 ":attr:`_expression.SelectBase.columns` attributes "

3651 "are deprecated and will be removed in a future release; these "

3652 "attributes implicitly create a subquery that should be explicit. "

3653 "Please call :meth:`_expression.SelectBase.subquery` "

3654 "first in order to create "

3655 "a subquery, which then contains this attribute. To access the "

3656 "columns that this SELECT object SELECTs "

3657 "from, use the :attr:`_expression.SelectBase.selected_columns` "

3658 "attribute.",

3659 )

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

3661 return self._implicit_subquery.columns

3662

3663 @property

3664 def columns(

3665 self,

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

3667 return self.c

3668

3669 def get_label_style(self) -> SelectLabelStyle:

3670 """

3671 Retrieve the current label style.

3672

3673 Implemented by subclasses.

3674

3675 """

3676 raise NotImplementedError()

3677

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

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

3680

3681 Implemented by subclasses.

3682

3683 """

3684

3685 raise NotImplementedError()

3686

3687 @util.deprecated(

3688 "1.4",

3689 "The :meth:`_expression.SelectBase.select` method is deprecated "

3690 "and will be removed in a future release; this method implicitly "

3691 "creates a subquery that should be explicit. "

3692 "Please call :meth:`_expression.SelectBase.subquery` "

3693 "first in order to create "

3694 "a subquery, which then can be selected.",

3695 )

3696 def select(self, *arg: Any, **kw: Any) -> Select[Any]:

3697 return self._implicit_subquery.select(*arg, **kw)

3698

3699 @HasMemoized.memoized_attribute

3700 def _implicit_subquery(self) -> Subquery:

3701 return self.subquery()

3702

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

3704 raise NotImplementedError()

3705

3706 @util.deprecated(

3707 "1.4",

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

3709 "method is deprecated and will be "

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

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

3712 )

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

3714 return self.scalar_subquery()

3715

3716 def exists(self) -> Exists:

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

3718 which can be used as a column expression.

3719

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

3721

3722 .. seealso::

3723

3724 :func:`_sql.exists`

3725

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

3727

3728 .. versionadded:: 1.4

3729

3730 """

3731 return Exists(self)

3732

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

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

3735 used as a column expression.

3736

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

3738

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

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

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

3742 an enclosing SELECT.

3743

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

3745 subquery that can be produced using the

3746 :meth:`_expression.SelectBase.subquery`

3747 method.

3748

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

3750 :meth:`_expression.SelectBase.scalar_subquery`.

3751

3752 .. seealso::

3753

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

3755

3756 """

3757 if self._label_style is not LABEL_STYLE_NONE:

3758 self = self.set_label_style(LABEL_STYLE_NONE)

3759

3760 return ScalarSelect(self)

3761

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

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

3764 subquery with a label.

3765

3766 .. seealso::

3767

3768 :meth:`_expression.SelectBase.scalar_subquery`.

3769

3770 """

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

3772

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

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

3775

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

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

3778

3779 .. seealso::

3780

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

3782

3783 """

3784 return Lateral._factory(self, name)

3785

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

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

3788

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

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

3791 SELECT statement.

3792

3793 Given a SELECT statement such as::

3794

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

3796

3797 The above statement might look like:

3798

3799 .. sourcecode:: sql

3800

3801 SELECT table.id, table.name FROM table

3802

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

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

3805 a named sub-element::

3806

3807 subq = stmt.subquery()

3808 new_stmt = select(subq)

3809

3810 The above renders as:

3811

3812 .. sourcecode:: sql

3813

3814 SELECT anon_1.id, anon_1.name

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

3816

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

3818 is equivalent to calling

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

3820 method on a FROM object; however,

3821 as a :class:`_expression.SelectBase`

3822 object is not directly FROM object,

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

3824 method provides clearer semantics.

3825

3826 .. versionadded:: 1.4

3827

3828 """

3829

3830 return Subquery._construct(

3831 self._ensure_disambiguated_names(), name=name

3832 )

3833

3834 def _ensure_disambiguated_names(self) -> Self:

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

3836 disambiguated in some way, if possible.

3837

3838 """

3839

3840 raise NotImplementedError()

3841

3842 def alias(

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

3844 ) -> Subquery:

3845 """Return a named subquery against this

3846 :class:`_expression.SelectBase`.

3847

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

3849 :class:`_expression.FromClause`),

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

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

3852 :class:`_expression.FromClause`.

3853

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

3855 method is now

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

3857

3858 """

3859 return self.subquery(name=name)

3860

3861

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

3863

3864

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

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

3867

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

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

3870 compound selects.

3871

3872 """

3873

3874 __visit_name__ = "select_statement_grouping"

3875 _traverse_internals: _TraverseInternalsType = [

3876 ("element", InternalTraversal.dp_clauseelement)

3877 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3878

3879 _is_select_container = True

3880

3881 element: _SB

3882

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

3884 self.element = cast(

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

3886 )

3887

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

3889 new_element = self.element._ensure_disambiguated_names()

3890 if new_element is not self.element:

3891 return SelectStatementGrouping(new_element)

3892 else:

3893 return self

3894

3895 def get_label_style(self) -> SelectLabelStyle:

3896 return self.element.get_label_style()

3897

3898 def set_label_style(

3899 self, label_style: SelectLabelStyle

3900 ) -> SelectStatementGrouping[_SB]:

3901 return SelectStatementGrouping(

3902 self.element.set_label_style(label_style)

3903 )

3904

3905 @property

3906 def select_statement(self) -> _SB:

3907 return self.element

3908

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

3910 return self

3911

3912 if TYPE_CHECKING:

3913

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

3915

3916 # def _generate_columns_plus_names(

3917 # self, anon_for_dupe_key: bool

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

3919 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3920

3921 def _generate_fromclause_column_proxies(

3922 self,

3923 subquery: FromClause,

3924 columns: ColumnCollection[str, KeyedColumnElement[Any]],

3925 primary_key: ColumnSet,

3926 foreign_keys: Set[KeyedColumnElement[Any]],

3927 *,

3928 proxy_compound_columns: Optional[

3929 Iterable[Sequence[ColumnElement[Any]]]

3930 ] = None,

3931 ) -> None:

3932 self.element._generate_fromclause_column_proxies(

3933 subquery,

3934 columns,

3935 proxy_compound_columns=proxy_compound_columns,

3936 primary_key=primary_key,

3937 foreign_keys=foreign_keys,

3938 )

3939

3940 @util.ro_non_memoized_property

3941 def _all_selected_columns(self) -> _SelectIterable:

3942 return self.element._all_selected_columns

3943

3944 @util.ro_non_memoized_property

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

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

3947 representing the columns that

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

3949 :class:`_sql.TextClause` constructs.

3950

3951 .. versionadded:: 1.4

3952

3953 .. seealso::

3954

3955 :attr:`_sql.Select.selected_columns`

3956

3957 """

3958 return self.element.selected_columns

3959

3960 @util.ro_non_memoized_property

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

3962 return self.element._from_objects

3963

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

3965 return self.element._scalar_type()

3966

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

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

3969 raise NotImplementedError

3970

3971

3972class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3974 added.

3975

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

3977 :class:`_expression.CompoundSelect`

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

3979 rendering can be controlled. Compare to

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

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

3982 and is also a SELECT construct,

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

3984 only wrapped as a subquery.

3985

3986 """

3987

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

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

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

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

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

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

3994 _for_update_arg: Optional[ForUpdateArg] = None

3995

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

3997 self._label_style = _label_style

3998

3999 @_generative

4000 def with_for_update(

4001 self,

4002 *,

4003 nowait: bool = False,

4004 read: bool = False,

4005 of: Optional[_ForUpdateOfArgument] = None,

4006 skip_locked: bool = False,

4007 key_share: bool = False,

4008 ) -> Self:

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

4010 :class:`_expression.GenerativeSelect`.

4011

4012 E.g.::

4013

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

4015

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

4017 render a statement like:

4018

4019 .. sourcecode:: sql

4020

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

4022

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

4024 would produce:

4025

4026 .. sourcecode:: sql

4027

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

4029

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

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

4032 provided which allow for common database-specific

4033 variants.

4034

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

4036 Database and PostgreSQL dialects.

4037

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

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

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

4041

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

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

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

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

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

4047 table or as a column depending on backend.

4048

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

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

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

4052

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

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

4055 on the PostgreSQL dialect.

4056

4057 """

4058 self._for_update_arg = ForUpdateArg(

4059 nowait=nowait,

4060 read=read,

4061 of=of,

4062 skip_locked=skip_locked,

4063 key_share=key_share,

4064 )

4065 return self

4066

4067 def get_label_style(self) -> SelectLabelStyle:

4068 """

4069 Retrieve the current label style.

4070

4071 .. versionadded:: 1.4

4072

4073 """

4074 return self._label_style

4075

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

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

4078

4079 There are three "label styles" available,

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

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

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

4083 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4084

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

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

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

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

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

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

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

4092 that the impact of this labeling is minimal.

4093

4094 The rationale for disambiguation is mostly so that all column

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

4096 collection when a subquery is created.

4097

4098 .. versionadded:: 1.4 - the

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

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

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

4102

4103 .. seealso::

4104

4105 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4106

4107 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4108

4109 :data:`_sql.LABEL_STYLE_NONE`

4110

4111 :data:`_sql.LABEL_STYLE_DEFAULT`

4112

4113 """

4114 if self._label_style is not style:

4115 self = self._generate()

4116 self._label_style = style

4117 return self

4118

4119 @property

4120 def _group_by_clause(self) -> ClauseList:

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

4122 return ClauseList._construct_raw(

4123 operators.comma_op, self._group_by_clauses

4124 )

4125

4126 @property

4127 def _order_by_clause(self) -> ClauseList:

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

4129 return ClauseList._construct_raw(

4130 operators.comma_op, self._order_by_clauses

4131 )

4132

4133 def _offset_or_limit_clause(

4134 self,

4135 element: _LimitOffsetType,

4136 name: Optional[str] = None,

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

4138 ) -> ColumnElement[Any]:

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

4140

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

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

4143

4144 """

4145 return coercions.expect(

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

4147 )

4148

4149 @overload

4150 def _offset_or_limit_clause_asint(

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

4152 ) -> NoReturn: ...

4153

4154 @overload

4155 def _offset_or_limit_clause_asint(

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

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

4158

4159 def _offset_or_limit_clause_asint(

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

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

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

4163 integer.

4164

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

4166 parameter. Otherwise, a compilation error is raised.

4167

4168 """

4169 if clause is None:

4170 return None

4171 try:

4172 value = clause._limit_offset_value

4173 except AttributeError as err:

4174 raise exc.CompileError(

4175 "This SELECT structure does not use a simple "

4176 "integer value for %s" % attrname

4177 ) from err

4178 else:

4179 return util.asint(value)

4180

4181 @property

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

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

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

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

4186 isn't currently set to an integer.

4187

4188 """

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

4190

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

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

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

4194 """

4195 return isinstance(clause, _OffsetLimitParam)

4196

4197 @property

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

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

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

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

4202 offset isn't currently set to an integer.

4203

4204 """

4205 return self._offset_or_limit_clause_asint(

4206 self._offset_clause, "offset"

4207 )

4208

4209 @property

4210 def _has_row_limiting_clause(self) -> bool:

4211 return (

4212 self._limit_clause is not None

4213 or self._offset_clause is not None

4214 or self._fetch_clause is not None

4215 )

4216

4217 @_generative

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

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

4220 applied.

4221

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

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

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

4225 functionality.

4226

4227 .. note::

4228

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

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

4231

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

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

4234

4235 .. seealso::

4236

4237 :meth:`_sql.GenerativeSelect.fetch`

4238

4239 :meth:`_sql.GenerativeSelect.offset`

4240

4241 """

4242

4243 self._fetch_clause = self._fetch_clause_options = None

4244 self._limit_clause = self._offset_or_limit_clause(limit)

4245 return self

4246

4247 @_generative

4248 def fetch(

4249 self,

4250 count: _LimitOffsetType,

4251 with_ties: bool = False,

4252 percent: bool = False,

4253 **dialect_kw: Any,

4254 ) -> Self:

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

4256 applied.

4257

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

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

4260 select. This functionality is is currently implemented for Oracle

4261 Database, PostgreSQL, MSSQL.

4262

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

4264

4265 .. note::

4266

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

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

4269

4270 .. versionadded:: 1.4

4271

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

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

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

4275 Pass ``None`` to reset it.

4276

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

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

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

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

4281

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

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

4284

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

4286 may be accepted by dialects.

4287

4288 .. versionadded:: 2.0.41

4289

4290 .. seealso::

4291

4292 :meth:`_sql.GenerativeSelect.limit`

4293

4294 :meth:`_sql.GenerativeSelect.offset`

4295

4296 """

4297 self._validate_dialect_kwargs(dialect_kw)

4298 self._limit_clause = None

4299 if count is None:

4300 self._fetch_clause = self._fetch_clause_options = None

4301 else:

4302 self._fetch_clause = self._offset_or_limit_clause(count)

4303 self._fetch_clause_options = {

4304 "with_ties": with_ties,

4305 "percent": percent,

4306 }

4307 return self

4308

4309 @_generative

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

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

4312 applied.

4313

4314

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

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

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

4318 functionality.

4319

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

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

4322

4323 .. seealso::

4324

4325 :meth:`_sql.GenerativeSelect.limit`

4326

4327 :meth:`_sql.GenerativeSelect.fetch`

4328

4329 """

4330

4331 self._offset_clause = self._offset_or_limit_clause(offset)

4332 return self

4333

4334 @_generative

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

4336 def slice(

4337 self,

4338 start: int,

4339 stop: int,

4340 ) -> Self:

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

4342

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

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

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

4346 query.

4347

4348 For example, ::

4349

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

4351

4352 renders as

4353

4354 .. sourcecode:: sql

4355

4356 SELECT users.id AS users_id,

4357 users.name AS users_name

4358 FROM users ORDER BY users.id

4359 LIMIT ? OFFSET ?

4360 (2, 1)

4361

4362 .. note::

4363

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

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

4366

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

4368 method generalized from the ORM.

4369

4370 .. seealso::

4371

4372 :meth:`_sql.GenerativeSelect.limit`

4373

4374 :meth:`_sql.GenerativeSelect.offset`

4375

4376 :meth:`_sql.GenerativeSelect.fetch`

4377

4378 """

4379 sql_util = util.preloaded.sql_util

4380 self._fetch_clause = self._fetch_clause_options = None

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

4382 self._limit_clause, self._offset_clause, start, stop

4383 )

4384 return self

4385

4386 @_generative

4387 def order_by(

4388 self,

4389 __first: Union[

4390 Literal[None, _NoArg.NO_ARG],

4391 _ColumnExpressionOrStrLabelArgument[Any],

4392 ] = _NoArg.NO_ARG,

4393 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4394 ) -> Self:

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

4396 criteria applied.

4397

4398 e.g.::

4399

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

4401

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

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

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

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

4406

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

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

4409

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

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

4412

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

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

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

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

4417 label matching in ORDER BY and GROUP BY expressions.

4418

4419 .. seealso::

4420

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

4422

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

4424

4425 """

4426

4427 if not clauses and __first is None:

4428 self._order_by_clauses = ()

4429 elif __first is not _NoArg.NO_ARG:

4430 self._order_by_clauses += tuple(

4431 coercions.expect(

4432 roles.OrderByRole, clause, apply_propagate_attrs=self

4433 )

4434 for clause in (__first,) + clauses

4435 )

4436 return self

4437

4438 @_generative

4439 def group_by(

4440 self,

4441 __first: Union[

4442 Literal[None, _NoArg.NO_ARG],

4443 _ColumnExpressionOrStrLabelArgument[Any],

4444 ] = _NoArg.NO_ARG,

4445 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4446 ) -> Self:

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

4448 criterion applied.

4449

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

4451

4452 e.g.::

4453

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

4455

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

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

4458

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

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

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

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

4463 label matching in ORDER BY and GROUP BY expressions.

4464

4465 .. seealso::

4466

4467 :ref:`tutorial_group_by_w_aggregates` - in the

4468 :ref:`unified_tutorial`

4469

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

4471

4472 """ # noqa: E501

4473

4474 if not clauses and __first is None:

4475 self._group_by_clauses = ()

4476 elif __first is not _NoArg.NO_ARG:

4477 self._group_by_clauses += tuple(

4478 coercions.expect(

4479 roles.GroupByRole, clause, apply_propagate_attrs=self

4480 )

4481 for clause in (__first,) + clauses

4482 )

4483 return self

4484

4485

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

4487class CompoundSelectState(CompileState):

4488 @util.memoized_property

4489 def _label_resolve_dict(

4490 self,

4491 ) -> Tuple[

4492 Dict[str, ColumnElement[Any]],

4493 Dict[str, ColumnElement[Any]],

4494 Dict[str, ColumnElement[Any]],

4495 ]:

4496 # TODO: this is hacky and slow

4497 hacky_subquery = self.statement.subquery()

4498 hacky_subquery.named_with_column = False

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

4500 return d, d, d

4501

4502

4503class _CompoundSelectKeyword(Enum):

4504 UNION = "UNION"

4505 UNION_ALL = "UNION ALL"

4506 EXCEPT = "EXCEPT"

4507 EXCEPT_ALL = "EXCEPT ALL"

4508 INTERSECT = "INTERSECT"

4509 INTERSECT_ALL = "INTERSECT ALL"

4510

4511

4512class CompoundSelect(HasCompileState, GenerativeSelect, TypedReturnsRows[_TP]):

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

4514 SELECT-based set operations.

4515

4516

4517 .. seealso::

4518

4519 :func:`_expression.union`

4520

4521 :func:`_expression.union_all`

4522

4523 :func:`_expression.intersect`

4524

4525 :func:`_expression.intersect_all`

4526

4527 :func:`_expression.except`

4528

4529 :func:`_expression.except_all`

4530

4531 """

4532

4533 __visit_name__ = "compound_select"

4534

4535 _traverse_internals: _TraverseInternalsType = (

4536 [

4537 ("selects", InternalTraversal.dp_clauseelement_list),

4538 ("_limit_clause", InternalTraversal.dp_clauseelement),

4539 ("_offset_clause", InternalTraversal.dp_clauseelement),

4540 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4541 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4542 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4543 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4544 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4545 ("keyword", InternalTraversal.dp_string),

4546 ]

4547 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4548 + HasCTE._has_ctes_traverse_internals

4549 + DialectKWArgs._dialect_kwargs_traverse_internals

4550 + Executable._executable_traverse_internals

4551 )

4552

4553 selects: List[SelectBase]

4554

4555 _is_from_container = True

4556 _auto_correlate = False

4557

4558 def __init__(

4559 self,

4560 keyword: _CompoundSelectKeyword,

4561 *selects: _SelectStatementForCompoundArgument[_TP],

4562 ):

4563 self.keyword = keyword

4564 self.selects = [

4565 coercions.expect(

4566 roles.CompoundElementRole, s, apply_propagate_attrs=self

4567 ).self_group(against=self)

4568 for s in selects

4569 ]

4570

4571 GenerativeSelect.__init__(self)

4572

4573 @classmethod

4574 def _create_union(

4575 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4576 ) -> CompoundSelect[_TP]:

4577 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4578

4579 @classmethod

4580 def _create_union_all(

4581 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4582 ) -> CompoundSelect[_TP]:

4583 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4584

4585 @classmethod

4586 def _create_except(

4587 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4588 ) -> CompoundSelect[_TP]:

4589 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4590

4591 @classmethod

4592 def _create_except_all(

4593 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4594 ) -> CompoundSelect[_TP]:

4595 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4596

4597 @classmethod

4598 def _create_intersect(

4599 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4600 ) -> CompoundSelect[_TP]:

4601 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4602

4603 @classmethod

4604 def _create_intersect_all(

4605 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4606 ) -> CompoundSelect[_TP]:

4607 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4608

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

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

4611

4612 def self_group(

4613 self, against: Optional[OperatorType] = None

4614 ) -> GroupedElement:

4615 return SelectStatementGrouping(self)

4616

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

4618 for s in self.selects:

4619 if s.is_derived_from(fromclause):

4620 return True

4621 return False

4622

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

4624 if self._label_style is not style:

4625 self = self._generate()

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

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

4628

4629 return self

4630

4631 def _ensure_disambiguated_names(self) -> Self:

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

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

4634 self = self._generate()

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

4636

4637 return self

4638

4639 def _generate_fromclause_column_proxies(

4640 self,

4641 subquery: FromClause,

4642 columns: ColumnCollection[str, KeyedColumnElement[Any]],

4643 primary_key: ColumnSet,

4644 foreign_keys: Set[KeyedColumnElement[Any]],

4645 *,

4646 proxy_compound_columns: Optional[

4647 Iterable[Sequence[ColumnElement[Any]]]

4648 ] = None,

4649 ) -> None:

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

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

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

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

4654 # generate below.

4655 select_0 = self.selects[0]

4656

4657 if self._label_style is not LABEL_STYLE_DEFAULT:

4658 select_0 = select_0.set_label_style(self._label_style)

4659

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

4661 # derived columns place a 'weight' annotation corresponding

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

4663 # that the corresponding_column() operation can resolve

4664 # conflicts

4665 extra_col_iterator = zip(

4666 *[

4667 [

4668 c._annotate(dd)

4669 for c in stmt._all_selected_columns

4670 if is_column_element(c)

4671 ]

4672 for dd, stmt in [

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

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

4675 ]

4676 ]

4677 )

4678

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

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

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

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

4683 # compound

4684 select_0._generate_fromclause_column_proxies(

4685 subquery,

4686 columns,

4687 proxy_compound_columns=extra_col_iterator,

4688 primary_key=primary_key,

4689 foreign_keys=foreign_keys,

4690 )

4691

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

4693 super()._refresh_for_new_column(column)

4694 for select in self.selects:

4695 select._refresh_for_new_column(column)

4696

4697 @util.ro_non_memoized_property

4698 def _all_selected_columns(self) -> _SelectIterable:

4699 return self.selects[0]._all_selected_columns

4700

4701 @util.ro_non_memoized_property

4702 def selected_columns(

4703 self,

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

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

4706 representing the columns that

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

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

4709

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

4711 :attr:`_expression.CompoundSelect.selected_columns`

4712 attribute returns the selected

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

4714 statements within the set operation.

4715

4716 .. seealso::

4717

4718 :attr:`_sql.Select.selected_columns`

4719

4720 .. versionadded:: 1.4

4721

4722 """

4723 return self.selects[0].selected_columns

4724

4725

4726# backwards compat

4727for elem in _CompoundSelectKeyword:

4728 setattr(CompoundSelect, elem.name, elem)

4729

4730

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

4732class SelectState(util.MemoizedSlots, CompileState):

4733 __slots__ = (

4734 "from_clauses",

4735 "froms",

4736 "columns_plus_names",

4737 "_label_resolve_dict",

4738 )

4739

4740 if TYPE_CHECKING:

4741 default_select_compile_options: CacheableOptions

4742 else:

4743

4744 class default_select_compile_options(CacheableOptions):

4745 _cache_key_traversal = []

4746

4747 if TYPE_CHECKING:

4748

4749 @classmethod

4750 def get_plugin_class(

4751 cls, statement: Executable

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

4753

4754 def __init__(

4755 self,

4756 statement: Select[Any],

4757 compiler: SQLCompiler,

4758 **kw: Any,

4759 ):

4760 self.statement = statement

4761 self.from_clauses = statement._from_obj

4762

4763 for memoized_entities in statement._memoized_select_entities:

4764 self._setup_joins(

4765 memoized_entities._setup_joins, memoized_entities._raw_columns

4766 )

4767

4768 if statement._setup_joins:

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

4770

4771 self.froms = self._get_froms(statement)

4772

4773 self.columns_plus_names = statement._generate_columns_plus_names(True)

4774

4775 @classmethod

4776 def _plugin_not_implemented(cls) -> NoReturn:

4777 raise NotImplementedError(

4778 "The default SELECT construct without plugins does not "

4779 "implement this method."

4780 )

4781

4782 @classmethod

4783 def get_column_descriptions(

4784 cls, statement: Select[Any]

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

4786 return [

4787 {

4788 "name": name,

4789 "type": element.type,

4790 "expr": element,

4791 }

4792 for _, name, _, element, _ in (

4793 statement._generate_columns_plus_names(False)

4794 )

4795 ]

4796

4797 @classmethod

4798 def from_statement(

4799 cls, statement: Select[Any], from_statement: roles.ReturnsRowsRole

4800 ) -> ExecutableReturnsRows:

4801 cls._plugin_not_implemented()

4802

4803 @classmethod

4804 def get_columns_clause_froms(

4805 cls, statement: Select[Any]

4806 ) -> List[FromClause]:

4807 return cls._normalize_froms(

4808 itertools.chain.from_iterable(

4809 element._from_objects for element in statement._raw_columns

4810 )

4811 )

4812

4813 @classmethod

4814 def _column_naming_convention(

4815 cls, label_style: SelectLabelStyle

4816 ) -> _LabelConventionCallable:

4817 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4818

4819 dedupe = label_style is not LABEL_STYLE_NONE

4820

4821 pa = prefix_anon_map()

4822 names = set()

4823

4824 def go(

4825 c: Union[ColumnElement[Any], TextClause],

4826 col_name: Optional[str] = None,

4827 ) -> Optional[str]:

4828 if is_text_clause(c):

4829 return None

4830 elif TYPE_CHECKING:

4831 assert is_column_element(c)

4832

4833 if not dedupe:

4834 name = c._proxy_key

4835 if name is None:

4836 name = "_no_label"

4837 return name

4838

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

4840

4841 if name is None:

4842 name = "_no_label"

4843 if name in names:

4844 return c._anon_label(name) % pa

4845 else:

4846 names.add(name)

4847 return name

4848

4849 elif name in names:

4850 return (

4851 c._anon_tq_key_label % pa

4852 if table_qualified

4853 else c._anon_key_label % pa

4854 )

4855 else:

4856 names.add(name)

4857 return name

4858

4859 return go

4860

4861 def _get_froms(self, statement: Select[Any]) -> List[FromClause]:

4862 ambiguous_table_name_map: _AmbiguousTableNameMap

4863 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4864

4865 return self._normalize_froms(

4866 itertools.chain(

4867 self.from_clauses,

4868 itertools.chain.from_iterable(

4869 [

4870 element._from_objects

4871 for element in statement._raw_columns

4872 ]

4873 ),

4874 itertools.chain.from_iterable(

4875 [

4876 element._from_objects

4877 for element in statement._where_criteria

4878 ]

4879 ),

4880 ),

4881 check_statement=statement,

4882 ambiguous_table_name_map=ambiguous_table_name_map,

4883 )

4884

4885 @classmethod

4886 def _normalize_froms(

4887 cls,

4888 iterable_of_froms: Iterable[FromClause],

4889 check_statement: Optional[Select[Any]] = None,

4890 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4891 ) -> List[FromClause]:

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

4893 would actually render in the FROM clause of a SELECT.

4894

4895 This does the job of checking for JOINs, tables, etc. that are in fact

4896 overlapping due to cloning, adaption, present in overlapping joins,

4897 etc.

4898

4899 """

4900 seen: Set[FromClause] = set()

4901 froms: List[FromClause] = []

4902

4903 for item in iterable_of_froms:

4904 if is_subquery(item) and item.element is check_statement:

4905 raise exc.InvalidRequestError(

4906 "select() construct refers to itself as a FROM"

4907 )

4908

4909 if not seen.intersection(item._cloned_set):

4910 froms.append(item)

4911 seen.update(item._cloned_set)

4912

4913 if froms:

4914 toremove = set(

4915 itertools.chain.from_iterable(

4916 [_expand_cloned(f._hide_froms) for f in froms]

4917 )

4918 )

4919 if toremove:

4920 # filter out to FROM clauses not in the list,

4921 # using a list to maintain ordering

4922 froms = [f for f in froms if f not in toremove]

4923

4924 if ambiguous_table_name_map is not None:

4925 ambiguous_table_name_map.update(

4926 (

4927 fr.name,

4928 _anonymous_label.safe_construct(

4929 hash(fr.name), fr.name

4930 ),

4931 )

4932 for item in froms

4933 for fr in item._from_objects

4934 if is_table(fr)

4935 and fr.schema

4936 and fr.name not in ambiguous_table_name_map

4937 )

4938

4939 return froms

4940

4941 def _get_display_froms(

4942 self,

4943 explicit_correlate_froms: Optional[Sequence[FromClause]] = None,

4944 implicit_correlate_froms: Optional[Sequence[FromClause]] = None,

4945 ) -> List[FromClause]:

4946 """Return the full list of 'from' clauses to be displayed.

4947

4948 Takes into account a set of existing froms which may be

4949 rendered in the FROM clause of enclosing selects; this Select

4950 may want to leave those absent if it is automatically

4951 correlating.

4952

4953 """

4954

4955 froms = self.froms

4956

4957 if self.statement._correlate:

4958 to_correlate = self.statement._correlate

4959 if to_correlate:

4960 froms = [

4961 f

4962 for f in froms

4963 if f

4964 not in _cloned_intersection(

4965 _cloned_intersection(

4966 froms, explicit_correlate_froms or ()

4967 ),

4968 to_correlate,

4969 )

4970 ]

4971

4972 if self.statement._correlate_except is not None:

4973 froms = [

4974 f

4975 for f in froms

4976 if f

4977 not in _cloned_difference(

4978 _cloned_intersection(

4979 froms, explicit_correlate_froms or ()

4980 ),

4981 self.statement._correlate_except,

4982 )

4983 ]

4984

4985 if (

4986 self.statement._auto_correlate

4987 and implicit_correlate_froms

4988 and len(froms) > 1

4989 ):

4990 froms = [

4991 f

4992 for f in froms

4993 if f

4994 not in _cloned_intersection(froms, implicit_correlate_froms)

4995 ]

4996

4997 if not len(froms):

4998 raise exc.InvalidRequestError(

4999 "Select statement '%r"

5000 "' returned no FROM clauses "

5001 "due to auto-correlation; "

5002 "specify correlate(<tables>) "

5003 "to control correlation "

5004 "manually." % self.statement

5005 )

5006

5007 return froms

5008

5009 def _memoized_attr__label_resolve_dict(

5010 self,

5011 ) -> Tuple[

5012 Dict[str, ColumnElement[Any]],

5013 Dict[str, ColumnElement[Any]],

5014 Dict[str, ColumnElement[Any]],

5015 ]:

5016 with_cols: Dict[str, ColumnElement[Any]] = {

5017 c._tq_label or c.key: c

5018 for c in self.statement._all_selected_columns

5019 if c._allow_label_resolve

5020 }

5021 only_froms: Dict[str, ColumnElement[Any]] = {

5022 c.key: c # type: ignore

5023 for c in _select_iterables(self.froms)

5024 if c._allow_label_resolve

5025 }

5026 only_cols: Dict[str, ColumnElement[Any]] = with_cols.copy()

5027 for key, value in only_froms.items():

5028 with_cols.setdefault(key, value)

5029

5030 return with_cols, only_froms, only_cols

5031

5032 @classmethod

5033 def determine_last_joined_entity(

5034 cls, stmt: Select[Any]

5035 ) -> Optional[_JoinTargetElement]:

5036 if stmt._setup_joins:

5037 return stmt._setup_joins[-1][0]

5038 else:

5039 return None

5040

5041 @classmethod

5042 def all_selected_columns(cls, statement: Select[Any]) -> _SelectIterable:

5043 return [c for c in _select_iterables(statement._raw_columns)]

5044

5045 def _setup_joins(

5046 self,

5047 args: Tuple[_SetupJoinsElement, ...],

5048 raw_columns: List[_ColumnsClauseElement],

5049 ) -> None:

5050 for right, onclause, left, flags in args:

5051 if TYPE_CHECKING:

5052 if onclause is not None:

5053 assert isinstance(onclause, ColumnElement)

5054

5055 isouter = flags["isouter"]

5056 full = flags["full"]

5057

5058 if left is None:

5059 (

5060 left,

5061 replace_from_obj_index,

5062 ) = self._join_determine_implicit_left_side(

5063 raw_columns, left, right, onclause

5064 )

5065 else:

5066 (replace_from_obj_index) = self._join_place_explicit_left_side(

5067 left

5068 )

5069

5070 # these assertions can be made here, as if the right/onclause

5071 # contained ORM elements, the select() statement would have been

5072 # upgraded to an ORM select, and this method would not be called;

5073 # orm.context.ORMSelectCompileState._join() would be

5074 # used instead.

5075 if TYPE_CHECKING:

5076 assert isinstance(right, FromClause)

5077 if onclause is not None:

5078 assert isinstance(onclause, ColumnElement)

5079

5080 if replace_from_obj_index is not None:

5081 # splice into an existing element in the

5082 # self._from_obj list

5083 left_clause = self.from_clauses[replace_from_obj_index]

5084

5085 self.from_clauses = (

5086 self.from_clauses[:replace_from_obj_index]

5087 + (

5088 Join(

5089 left_clause,

5090 right,

5091 onclause,

5092 isouter=isouter,

5093 full=full,

5094 ),

5095 )

5096 + self.from_clauses[replace_from_obj_index + 1 :]

5097 )

5098 else:

5099 assert left is not None

5100 self.from_clauses = self.from_clauses + (

5101 Join(left, right, onclause, isouter=isouter, full=full),

5102 )

5103

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

5105 def _join_determine_implicit_left_side(

5106 self,

5107 raw_columns: List[_ColumnsClauseElement],

5108 left: Optional[FromClause],

5109 right: _JoinTargetElement,

5110 onclause: Optional[ColumnElement[Any]],

5111 ) -> Tuple[Optional[FromClause], Optional[int]]:

5112 """When join conditions don't express the left side explicitly,

5113 determine if an existing FROM or entity in this query

5114 can serve as the left hand side.

5115

5116 """

5117

5118 sql_util = util.preloaded.sql_util

5119

5120 replace_from_obj_index: Optional[int] = None

5121

5122 from_clauses = self.from_clauses

5123

5124 if from_clauses:

5125 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5126 from_clauses, right, onclause

5127 )

5128

5129 if len(indexes) == 1:

5130 replace_from_obj_index = indexes[0]

5131 left = from_clauses[replace_from_obj_index]

5132 else:

5133 potential = {}

5134 statement = self.statement

5135

5136 for from_clause in itertools.chain(

5137 itertools.chain.from_iterable(

5138 [element._from_objects for element in raw_columns]

5139 ),

5140 itertools.chain.from_iterable(

5141 [

5142 element._from_objects

5143 for element in statement._where_criteria

5144 ]

5145 ),

5146 ):

5147 potential[from_clause] = ()

5148

5149 all_clauses = list(potential.keys())

5150 indexes = sql_util.find_left_clause_to_join_from(

5151 all_clauses, right, onclause

5152 )

5153

5154 if len(indexes) == 1:

5155 left = all_clauses[indexes[0]]

5156

5157 if len(indexes) > 1:

5158 raise exc.InvalidRequestError(

5159 "Can't determine which FROM clause to join "

5160 "from, there are multiple FROMS which can "

5161 "join to this entity. Please use the .select_from() "

5162 "method to establish an explicit left side, as well as "

5163 "providing an explicit ON clause if not present already to "

5164 "help resolve the ambiguity."

5165 )

5166 elif not indexes:

5167 raise exc.InvalidRequestError(

5168 "Don't know how to join to %r. "

5169 "Please use the .select_from() "

5170 "method to establish an explicit left side, as well as "

5171 "providing an explicit ON clause if not present already to "

5172 "help resolve the ambiguity." % (right,)

5173 )

5174 return left, replace_from_obj_index

5175

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

5177 def _join_place_explicit_left_side(

5178 self, left: FromClause

5179 ) -> Optional[int]:

5180 replace_from_obj_index: Optional[int] = None

5181

5182 sql_util = util.preloaded.sql_util

5183

5184 from_clauses = list(self.statement._iterate_from_elements())

5185

5186 if from_clauses:

5187 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5188 self.from_clauses, left

5189 )

5190 else:

5191 indexes = []

5192

5193 if len(indexes) > 1:

5194 raise exc.InvalidRequestError(

5195 "Can't identify which entity in which to assign the "

5196 "left side of this join. Please use a more specific "

5197 "ON clause."

5198 )

5199

5200 # have an index, means the left side is already present in

5201 # an existing FROM in the self._from_obj tuple

5202 if indexes:

5203 replace_from_obj_index = indexes[0]

5204

5205 # no index, means we need to add a new element to the

5206 # self._from_obj tuple

5207

5208 return replace_from_obj_index

5209

5210

5211class _SelectFromElements:

5212 __slots__ = ()

5213

5214 _raw_columns: List[_ColumnsClauseElement]

5215 _where_criteria: Tuple[ColumnElement[Any], ...]

5216 _from_obj: Tuple[FromClause, ...]

5217

5218 def _iterate_from_elements(self) -> Iterator[FromClause]:

5219 # note this does not include elements

5220 # in _setup_joins

5221

5222 seen = set()

5223 for element in self._raw_columns:

5224 for fr in element._from_objects:

5225 if fr in seen:

5226 continue

5227 seen.add(fr)

5228 yield fr

5229 for element in self._where_criteria:

5230 for fr in element._from_objects:

5231 if fr in seen:

5232 continue

5233 seen.add(fr)

5234 yield fr

5235 for element in self._from_obj:

5236 if element in seen:

5237 continue

5238 seen.add(element)

5239 yield element

5240

5241

5242class _MemoizedSelectEntities(

5243 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5244):

5245 """represents partial state from a Select object, for the case

5246 where Select.columns() has redefined the set of columns/entities the

5247 statement will be SELECTing from. This object represents

5248 the entities from the SELECT before that transformation was applied,

5249 so that transformations that were made in terms of the SELECT at that

5250 time, such as join() as well as options(), can access the correct context.

5251

5252 In previous SQLAlchemy versions, this wasn't needed because these

5253 constructs calculated everything up front, like when you called join()

5254 or options(), it did everything to figure out how that would translate

5255 into specific SQL constructs that would be ready to send directly to the

5256 SQL compiler when needed. But as of

5257 1.4, all of that stuff is done in the compilation phase, during the

5258 "compile state" portion of the process, so that the work can all be

5259 cached. So it needs to be able to resolve joins/options2 based on what

5260 the list of entities was when those methods were called.

5261

5262

5263 """

5264

5265 __visit_name__ = "memoized_select_entities"

5266

5267 _traverse_internals: _TraverseInternalsType = [

5268 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5269 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5270 ("_with_options", InternalTraversal.dp_executable_options),

5271 ]

5272

5273 _is_clone_of: Optional[ClauseElement]

5274 _raw_columns: List[_ColumnsClauseElement]

5275 _setup_joins: Tuple[_SetupJoinsElement, ...]

5276 _with_options: Tuple[ExecutableOption, ...]

5277

5278 _annotations = util.EMPTY_DICT

5279

5280 def _clone(self, **kw: Any) -> Self:

5281 c = self.__class__.__new__(self.__class__)

5282 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5283

5284 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5285 return c

5286

5287 @classmethod

5288 def _generate_for_statement(cls, select_stmt: Select[Any]) -> None:

5289 if select_stmt._setup_joins or select_stmt._with_options:

5290 self = _MemoizedSelectEntities()

5291 self._raw_columns = select_stmt._raw_columns

5292 self._setup_joins = select_stmt._setup_joins

5293 self._with_options = select_stmt._with_options

5294

5295 select_stmt._memoized_select_entities += (self,)

5296 select_stmt._raw_columns = []

5297 select_stmt._setup_joins = select_stmt._with_options = ()

5298

5299

5300class Select(

5301 HasPrefixes,

5302 HasSuffixes,

5303 HasHints,

5304 HasCompileState,

5305 _SelectFromElements,

5306 GenerativeSelect,

5307 TypedReturnsRows[_TP],

5308):

5309 """Represents a ``SELECT`` statement.

5310

5311 The :class:`_sql.Select` object is normally constructed using the

5312 :func:`_sql.select` function. See that function for details.

5313

5314 .. seealso::

5315

5316 :func:`_sql.select`

5317

5318 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5319

5320 """

5321

5322 __visit_name__ = "select"

5323

5324 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5325 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5326

5327 _raw_columns: List[_ColumnsClauseElement]

5328

5329 _distinct: bool = False

5330 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5331 _correlate: Tuple[FromClause, ...] = ()

5332 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5333 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5334 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5335 _from_obj: Tuple[FromClause, ...] = ()

5336 _auto_correlate = True

5337 _is_select_statement = True

5338 _compile_options: CacheableOptions = (

5339 SelectState.default_select_compile_options

5340 )

5341

5342 _traverse_internals: _TraverseInternalsType = (

5343 [

5344 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5345 (

5346 "_memoized_select_entities",

5347 InternalTraversal.dp_memoized_select_entities,

5348 ),

5349 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5350 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5351 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5352 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5353 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5354 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5355 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5356 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5357 ("_limit_clause", InternalTraversal.dp_clauseelement),

5358 ("_offset_clause", InternalTraversal.dp_clauseelement),

5359 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5360 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5361 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5362 ("_distinct", InternalTraversal.dp_boolean),

5363 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5364 ("_label_style", InternalTraversal.dp_plain_obj),

5365 ]

5366 + HasCTE._has_ctes_traverse_internals

5367 + HasPrefixes._has_prefixes_traverse_internals

5368 + HasSuffixes._has_suffixes_traverse_internals

5369 + HasHints._has_hints_traverse_internals

5370 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5371 + Executable._executable_traverse_internals

5372 + DialectKWArgs._dialect_kwargs_traverse_internals

5373 )

5374

5375 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5376 ("_compile_options", InternalTraversal.dp_has_cache_key)

5377 ]

5378

5379 _compile_state_factory: Type[SelectState]

5380

5381 @classmethod

5382 def _create_raw_select(cls, **kw: Any) -> Select[Any]:

5383 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5384

5385 Used internally to build up :class:`.Select` constructs with

5386 pre-established state.

5387

5388 """

5389

5390 stmt = Select.__new__(Select)

5391 stmt.__dict__.update(kw)

5392 return stmt

5393

5394 def __init__(

5395 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5396 ):

5397 r"""Construct a new :class:`_expression.Select`.

5398

5399 The public constructor for :class:`_expression.Select` is the

5400 :func:`_sql.select` function.

5401

5402 """

5403 self._raw_columns = [

5404 coercions.expect(

5405 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5406 )

5407 for ent in entities

5408 ]

5409 GenerativeSelect.__init__(self)

5410

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

5412 if not self._raw_columns:

5413 return NULLTYPE

5414 elem = self._raw_columns[0]

5415 cols = list(elem._select_iterable)

5416 return cols[0].type

5417

5418 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5419 """A synonym for the :meth:`_sql.Select.where` method."""

5420

5421 return self.where(*criteria)

5422

5423 def _filter_by_zero(

5424 self,

5425 ) -> Union[

5426 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5427 ]:

5428 if self._setup_joins:

5429 meth = SelectState.get_plugin_class(

5430 self

5431 ).determine_last_joined_entity

5432 _last_joined_entity = meth(self)

5433 if _last_joined_entity is not None:

5434 return _last_joined_entity

5435

5436 if self._from_obj:

5437 return self._from_obj[0]

5438

5439 return self._raw_columns[0]

5440

5441 if TYPE_CHECKING:

5442

5443 @overload

5444 def scalar_subquery(

5445 self: Select[Tuple[_MAYBE_ENTITY]],

5446 ) -> ScalarSelect[Any]: ...

5447

5448 @overload

5449 def scalar_subquery(

5450 self: Select[Tuple[_NOT_ENTITY]],

5451 ) -> ScalarSelect[_NOT_ENTITY]: ...

5452

5453 @overload

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

5455

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

5457

5458 def filter_by(self, **kwargs: Any) -> Self:

5459 r"""apply the given filtering criterion as a WHERE clause

5460 to this select.

5461

5462 """

5463 from_entity = self._filter_by_zero()

5464

5465 clauses = [

5466 _entity_namespace_key(from_entity, key) == value

5467 for key, value in kwargs.items()

5468 ]

5469 return self.filter(*clauses)

5470

5471 @property

5472 def column_descriptions(self) -> Any:

5473 """Return a :term:`plugin-enabled` 'column descriptions' structure

5474 referring to the columns which are SELECTed by this statement.

5475

5476 This attribute is generally useful when using the ORM, as an

5477 extended structure which includes information about mapped

5478 entities is returned. The section :ref:`queryguide_inspection`

5479 contains more background.

5480

5481 For a Core-only statement, the structure returned by this accessor

5482 is derived from the same objects that are returned by the

5483 :attr:`.Select.selected_columns` accessor, formatted as a list of

5484 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5485 which indicate the column expressions to be selected::

5486

5487 >>> stmt = select(user_table)

5488 >>> stmt.column_descriptions

5489 [

5490 {

5491 'name': 'id',

5492 'type': Integer(),

5493 'expr': Column('id', Integer(), ...)},

5494 {

5495 'name': 'name',

5496 'type': String(length=30),

5497 'expr': Column('name', String(length=30), ...)}

5498 ]

5499

5500 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5501 attribute returns a structure for a Core-only set of entities,

5502 not just ORM-only entities.

5503

5504 .. seealso::

5505

5506 :attr:`.UpdateBase.entity_description` - entity information for

5507 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5508

5509 :ref:`queryguide_inspection` - ORM background

5510

5511 """

5512 meth = SelectState.get_plugin_class(self).get_column_descriptions

5513 return meth(self)

5514

5515 def from_statement(

5516 self, statement: roles.ReturnsRowsRole

5517 ) -> ExecutableReturnsRows:

5518 """Apply the columns which this :class:`.Select` would select

5519 onto another statement.

5520

5521 This operation is :term:`plugin-specific` and will raise a not

5522 supported exception if this :class:`_sql.Select` does not select from

5523 plugin-enabled entities.

5524

5525

5526 The statement is typically either a :func:`_expression.text` or

5527 :func:`_expression.select` construct, and should return the set of

5528 columns appropriate to the entities represented by this

5529 :class:`.Select`.

5530

5531 .. seealso::

5532

5533 :ref:`orm_queryguide_selecting_text` - usage examples in the

5534 ORM Querying Guide

5535

5536 """

5537 meth = SelectState.get_plugin_class(self).from_statement

5538 return meth(self, statement)

5539

5540 @_generative

5541 def join(

5542 self,

5543 target: _JoinTargetArgument,

5544 onclause: Optional[_OnClauseArgument] = None,

5545 *,

5546 isouter: bool = False,

5547 full: bool = False,

5548 ) -> Self:

5549 r"""Create a SQL JOIN against this :class:`_expression.Select`

5550 object's criterion

5551 and apply generatively, returning the newly resulting

5552 :class:`_expression.Select`.

5553

5554 E.g.::

5555

5556 stmt = select(user_table).join(

5557 address_table, user_table.c.id == address_table.c.user_id

5558 )

5559

5560 The above statement generates SQL similar to:

5561

5562 .. sourcecode:: sql

5563

5564 SELECT user.id, user.name

5565 FROM user

5566 JOIN address ON user.id = address.user_id

5567

5568 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5569 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5570 source that is within the FROM clause of the existing SELECT,

5571 and a given target :class:`_sql.FromClause`, and then adds

5572 this :class:`_sql.Join` to the FROM clause of the newly generated

5573 SELECT statement. This is completely reworked from the behavior

5574 in 1.3, which would instead create a subquery of the entire

5575 :class:`_expression.Select` and then join that subquery to the

5576 target.

5577

5578 This is a **backwards incompatible change** as the previous behavior

5579 was mostly useless, producing an unnamed subquery rejected by

5580 most databases in any case. The new behavior is modeled after

5581 that of the very successful :meth:`_orm.Query.join` method in the

5582 ORM, in order to support the functionality of :class:`_orm.Query`

5583 being available by using a :class:`_sql.Select` object with an

5584 :class:`_orm.Session`.

5585

5586 See the notes for this change at :ref:`change_select_join`.

5587

5588

5589 :param target: target table to join towards

5590

5591 :param onclause: ON clause of the join. If omitted, an ON clause

5592 is generated automatically based on the :class:`_schema.ForeignKey`

5593 linkages between the two tables, if one can be unambiguously

5594 determined, otherwise an error is raised.

5595

5596 :param isouter: if True, generate LEFT OUTER join. Same as

5597 :meth:`_expression.Select.outerjoin`.

5598

5599 :param full: if True, generate FULL OUTER join.

5600

5601 .. seealso::

5602

5603 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5604

5605 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5606

5607 :meth:`_expression.Select.join_from`

5608

5609 :meth:`_expression.Select.outerjoin`

5610

5611 """ # noqa: E501

5612 join_target = coercions.expect(

5613 roles.JoinTargetRole, target, apply_propagate_attrs=self

5614 )

5615 if onclause is not None:

5616 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5617 else:

5618 onclause_element = None

5619

5620 self._setup_joins += (

5621 (

5622 join_target,

5623 onclause_element,

5624 None,

5625 {"isouter": isouter, "full": full},

5626 ),

5627 )

5628 return self

5629

5630 def outerjoin_from(

5631 self,

5632 from_: _FromClauseArgument,

5633 target: _JoinTargetArgument,

5634 onclause: Optional[_OnClauseArgument] = None,

5635 *,

5636 full: bool = False,

5637 ) -> Self:

5638 r"""Create a SQL LEFT OUTER JOIN against this

5639 :class:`_expression.Select` object's criterion and apply generatively,

5640 returning the newly resulting :class:`_expression.Select`.

5641

5642 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5643

5644 """

5645 return self.join_from(

5646 from_, target, onclause=onclause, isouter=True, full=full

5647 )

5648

5649 @_generative

5650 def join_from(

5651 self,

5652 from_: _FromClauseArgument,

5653 target: _JoinTargetArgument,

5654 onclause: Optional[_OnClauseArgument] = None,

5655 *,

5656 isouter: bool = False,

5657 full: bool = False,

5658 ) -> Self:

5659 r"""Create a SQL JOIN against this :class:`_expression.Select`

5660 object's criterion

5661 and apply generatively, returning the newly resulting

5662 :class:`_expression.Select`.

5663

5664 E.g.::

5665

5666 stmt = select(user_table, address_table).join_from(

5667 user_table, address_table, user_table.c.id == address_table.c.user_id

5668 )

5669

5670 The above statement generates SQL similar to:

5671

5672 .. sourcecode:: sql

5673

5674 SELECT user.id, user.name, address.id, address.email, address.user_id

5675 FROM user JOIN address ON user.id = address.user_id

5676

5677 .. versionadded:: 1.4

5678

5679 :param from\_: the left side of the join, will be rendered in the

5680 FROM clause and is roughly equivalent to using the

5681 :meth:`.Select.select_from` method.

5682

5683 :param target: target table to join towards

5684

5685 :param onclause: ON clause of the join.

5686

5687 :param isouter: if True, generate LEFT OUTER join. Same as

5688 :meth:`_expression.Select.outerjoin`.

5689

5690 :param full: if True, generate FULL OUTER join.

5691

5692 .. seealso::

5693

5694 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5695

5696 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5697

5698 :meth:`_expression.Select.join`

5699

5700 """ # noqa: E501

5701

5702 # note the order of parsing from vs. target is important here, as we

5703 # are also deriving the source of the plugin (i.e. the subject mapper

5704 # in an ORM query) which should favor the "from_" over the "target"

5705

5706 from_ = coercions.expect(

5707 roles.FromClauseRole, from_, apply_propagate_attrs=self

5708 )

5709 join_target = coercions.expect(

5710 roles.JoinTargetRole, target, apply_propagate_attrs=self

5711 )

5712 if onclause is not None:

5713 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5714 else:

5715 onclause_element = None

5716

5717 self._setup_joins += (

5718 (

5719 join_target,

5720 onclause_element,

5721 from_,

5722 {"isouter": isouter, "full": full},

5723 ),

5724 )

5725 return self

5726

5727 def outerjoin(

5728 self,

5729 target: _JoinTargetArgument,

5730 onclause: Optional[_OnClauseArgument] = None,

5731 *,

5732 full: bool = False,

5733 ) -> Self:

5734 """Create a left outer join.

5735

5736 Parameters are the same as that of :meth:`_expression.Select.join`.

5737

5738 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5739 creates a :class:`_sql.Join` object between a

5740 :class:`_sql.FromClause` source that is within the FROM clause of

5741 the existing SELECT, and a given target :class:`_sql.FromClause`,

5742 and then adds this :class:`_sql.Join` to the FROM clause of the

5743 newly generated SELECT statement. This is completely reworked

5744 from the behavior in 1.3, which would instead create a subquery of

5745 the entire

5746 :class:`_expression.Select` and then join that subquery to the

5747 target.

5748

5749 This is a **backwards incompatible change** as the previous behavior

5750 was mostly useless, producing an unnamed subquery rejected by

5751 most databases in any case. The new behavior is modeled after

5752 that of the very successful :meth:`_orm.Query.join` method in the

5753 ORM, in order to support the functionality of :class:`_orm.Query`

5754 being available by using a :class:`_sql.Select` object with an

5755 :class:`_orm.Session`.

5756

5757 See the notes for this change at :ref:`change_select_join`.

5758

5759 .. seealso::

5760

5761 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5762

5763 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5764

5765 :meth:`_expression.Select.join`

5766

5767 """

5768 return self.join(target, onclause=onclause, isouter=True, full=full)

5769

5770 def get_final_froms(self) -> Sequence[FromClause]:

5771 """Compute the final displayed list of :class:`_expression.FromClause`

5772 elements.

5773

5774 This method will run through the full computation required to

5775 determine what FROM elements will be displayed in the resulting

5776 SELECT statement, including shadowing individual tables with

5777 JOIN objects, as well as full computation for ORM use cases including

5778 eager loading clauses.

5779

5780 For ORM use, this accessor returns the **post compilation**

5781 list of FROM objects; this collection will include elements such as

5782 eagerly loaded tables and joins. The objects will **not** be

5783 ORM enabled and not work as a replacement for the

5784 :meth:`_sql.Select.select_froms` collection; additionally, the

5785 method is not well performing for an ORM enabled statement as it

5786 will incur the full ORM construction process.

5787

5788 To retrieve the FROM list that's implied by the "columns" collection

5789 passed to the :class:`_sql.Select` originally, use the

5790 :attr:`_sql.Select.columns_clause_froms` accessor.

5791

5792 To select from an alternative set of columns while maintaining the

5793 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5794 pass the

5795 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5796 parameter.

5797

5798 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5799 method replaces the previous :attr:`_sql.Select.froms` accessor,

5800 which is deprecated.

5801

5802 .. seealso::

5803

5804 :attr:`_sql.Select.columns_clause_froms`

5805

5806 """

5807 compiler = self._default_compiler()

5808

5809 return self._compile_state_factory(self, compiler)._get_display_froms()

5810

5811 @property

5812 @util.deprecated(

5813 "1.4.23",

5814 "The :attr:`_expression.Select.froms` attribute is moved to "

5815 "the :meth:`_expression.Select.get_final_froms` method.",

5816 )

5817 def froms(self) -> Sequence[FromClause]:

5818 """Return the displayed list of :class:`_expression.FromClause`

5819 elements.

5820

5821

5822 """

5823 return self.get_final_froms()

5824

5825 @property

5826 def columns_clause_froms(self) -> List[FromClause]:

5827 """Return the set of :class:`_expression.FromClause` objects implied

5828 by the columns clause of this SELECT statement.

5829

5830 .. versionadded:: 1.4.23

5831

5832 .. seealso::

5833

5834 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5835 statement into account

5836

5837 :meth:`_sql.Select.with_only_columns` - makes use of this

5838 collection to set up a new FROM list

5839

5840 """

5841

5842 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5843 self

5844 )

5845

5846 @property

5847 def inner_columns(self) -> _SelectIterable:

5848 """An iterator of all :class:`_expression.ColumnElement`

5849 expressions which would

5850 be rendered into the columns clause of the resulting SELECT statement.

5851

5852 This method is legacy as of 1.4 and is superseded by the

5853 :attr:`_expression.Select.exported_columns` collection.

5854

5855 """

5856

5857 return iter(self._all_selected_columns)

5858

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

5860 if fromclause is not None and self in fromclause._cloned_set:

5861 return True

5862

5863 for f in self._iterate_from_elements():

5864 if f.is_derived_from(fromclause):

5865 return True

5866 return False

5867

5868 def _copy_internals(

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

5870 ) -> None:

5871 # Select() object has been cloned and probably adapted by the

5872 # given clone function. Apply the cloning function to internal

5873 # objects

5874

5875 # 1. keep a dictionary of the froms we've cloned, and what

5876 # they've become. This allows us to ensure the same cloned from

5877 # is used when other items such as columns are "cloned"

5878

5879 all_the_froms = set(

5880 itertools.chain(

5881 _from_objects(*self._raw_columns),

5882 _from_objects(*self._where_criteria),

5883 _from_objects(*[elem[0] for elem in self._setup_joins]),

5884 )

5885 )

5886

5887 # do a clone for the froms we've gathered. what is important here

5888 # is if any of the things we are selecting from, like tables,

5889 # were converted into Join objects. if so, these need to be

5890 # added to _from_obj explicitly, because otherwise they won't be

5891 # part of the new state, as they don't associate themselves with

5892 # their columns.

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

5894

5895 # 2. copy FROM collections, adding in joins that we've created.

5896 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5897 add_froms = (

5898 {f for f in new_froms.values() if isinstance(f, Join)}

5899 .difference(all_the_froms)

5900 .difference(existing_from_obj)

5901 )

5902

5903 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5904

5905 # 3. clone everything else, making sure we use columns

5906 # corresponding to the froms we just made.

5907 def replace(

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

5909 **kw: Any,

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

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

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

5913 return newelem

5914 return None

5915

5916 kw["replace"] = replace

5917

5918 # copy everything else. for table-ish things like correlate,

5919 # correlate_except, setup_joins, these clone normally. For

5920 # column-expression oriented things like raw_columns, where_criteria,

5921 # order by, we get this from the new froms.

5922 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5923

5924 self._reset_memoizations()

5925

5926 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5927 return itertools.chain(

5928 super().get_children(

5929 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5930 **kw,

5931 ),

5932 self._iterate_from_elements(),

5933 )

5934

5935 @_generative

5936 def add_columns(

5937 self, *entities: _ColumnsClauseArgument[Any]

5938 ) -> Select[Any]:

5939 r"""Return a new :func:`_expression.select` construct with

5940 the given entities appended to its columns clause.

5941

5942 E.g.::

5943

5944 my_select = my_select.add_columns(table.c.new_column)

5945

5946 The original expressions in the columns clause remain in place.

5947 To replace the original expressions with new ones, see the method

5948 :meth:`_expression.Select.with_only_columns`.

5949

5950 :param \*entities: column, table, or other entity expressions to be

5951 added to the columns clause

5952

5953 .. seealso::

5954

5955 :meth:`_expression.Select.with_only_columns` - replaces existing

5956 expressions rather than appending.

5957

5958 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5959 example

5960

5961 """

5962 self._reset_memoizations()

5963

5964 self._raw_columns = self._raw_columns + [

5965 coercions.expect(

5966 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5967 )

5968 for column in entities

5969 ]

5970 return self

5971

5972 def _set_entities(

5973 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5974 ) -> None:

5975 self._raw_columns = [

5976 coercions.expect(

5977 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5978 )

5979 for ent in util.to_list(entities)

5980 ]

5981

5982 @util.deprecated(

5983 "1.4",

5984 "The :meth:`_expression.Select.column` method is deprecated and will "

5985 "be removed in a future release. Please use "

5986 ":meth:`_expression.Select.add_columns`",

5987 )

5988 def column(self, column: _ColumnsClauseArgument[Any]) -> Select[Any]:

5989 """Return a new :func:`_expression.select` construct with

5990 the given column expression added to its columns clause.

5991

5992 E.g.::

5993

5994 my_select = my_select.column(table.c.new_column)

5995

5996 See the documentation for

5997 :meth:`_expression.Select.with_only_columns`

5998 for guidelines on adding /replacing the columns of a

5999 :class:`_expression.Select` object.

6000

6001 """

6002 return self.add_columns(column)

6003

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

6005 def reduce_columns(self, only_synonyms: bool = True) -> Select[Any]:

6006 """Return a new :func:`_expression.select` construct with redundantly

6007 named, equivalently-valued columns removed from the columns clause.

6008

6009 "Redundant" here means two columns where one refers to the

6010 other either based on foreign key, or via a simple equality

6011 comparison in the WHERE clause of the statement. The primary purpose

6012 of this method is to automatically construct a select statement

6013 with all uniquely-named columns, without the need to use

6014 table-qualified labels as

6015 :meth:`_expression.Select.set_label_style`

6016 does.

6017

6018 When columns are omitted based on foreign key, the referred-to

6019 column is the one that's kept. When columns are omitted based on

6020 WHERE equivalence, the first column in the columns clause is the

6021 one that's kept.

6022

6023 :param only_synonyms: when True, limit the removal of columns

6024 to those which have the same name as the equivalent. Otherwise,

6025 all columns that are equivalent to another are removed.

6026

6027 """

6028 woc: Select[Any]

6029 woc = self.with_only_columns(

6030 *util.preloaded.sql_util.reduce_columns(

6031 self._all_selected_columns,

6032 only_synonyms=only_synonyms,

6033 *(self._where_criteria + self._from_obj),

6034 )

6035 )

6036 return woc

6037

6038 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6039

6040 # code within this block is **programmatically,

6041 # statically generated** by tools/generate_tuple_map_overloads.py

6042

6043 @overload

6044 def with_only_columns(

6045 self, __ent0: _TCCA[_T0], *, maintain_column_froms: bool = ...

6046 ) -> Select[Tuple[_T0]]: ...

6047

6048 @overload

6049 def with_only_columns(

6050 self,

6051 __ent0: _TCCA[_T0],

6052 __ent1: _TCCA[_T1],

6053 *,

6054 maintain_column_froms: bool = ...,

6055 ) -> Select[Tuple[_T0, _T1]]: ...

6056

6057 @overload

6058 def with_only_columns(

6059 self,

6060 __ent0: _TCCA[_T0],

6061 __ent1: _TCCA[_T1],

6062 __ent2: _TCCA[_T2],

6063 *,

6064 maintain_column_froms: bool = ...,

6065 ) -> Select[Tuple[_T0, _T1, _T2]]: ...

6066

6067 @overload

6068 def with_only_columns(

6069 self,

6070 __ent0: _TCCA[_T0],

6071 __ent1: _TCCA[_T1],

6072 __ent2: _TCCA[_T2],

6073 __ent3: _TCCA[_T3],

6074 *,

6075 maintain_column_froms: bool = ...,

6076 ) -> Select[Tuple[_T0, _T1, _T2, _T3]]: ...

6077

6078 @overload

6079 def with_only_columns(

6080 self,

6081 __ent0: _TCCA[_T0],

6082 __ent1: _TCCA[_T1],

6083 __ent2: _TCCA[_T2],

6084 __ent3: _TCCA[_T3],

6085 __ent4: _TCCA[_T4],

6086 *,

6087 maintain_column_froms: bool = ...,

6088 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...

6089

6090 @overload

6091 def with_only_columns(

6092 self,

6093 __ent0: _TCCA[_T0],

6094 __ent1: _TCCA[_T1],

6095 __ent2: _TCCA[_T2],

6096 __ent3: _TCCA[_T3],

6097 __ent4: _TCCA[_T4],

6098 __ent5: _TCCA[_T5],

6099 *,

6100 maintain_column_froms: bool = ...,

6101 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...

6102

6103 @overload

6104 def with_only_columns(

6105 self,

6106 __ent0: _TCCA[_T0],

6107 __ent1: _TCCA[_T1],

6108 __ent2: _TCCA[_T2],

6109 __ent3: _TCCA[_T3],

6110 __ent4: _TCCA[_T4],

6111 __ent5: _TCCA[_T5],

6112 __ent6: _TCCA[_T6],

6113 *,

6114 maintain_column_froms: bool = ...,

6115 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...

6116

6117 @overload

6118 def with_only_columns(

6119 self,

6120 __ent0: _TCCA[_T0],

6121 __ent1: _TCCA[_T1],

6122 __ent2: _TCCA[_T2],

6123 __ent3: _TCCA[_T3],

6124 __ent4: _TCCA[_T4],

6125 __ent5: _TCCA[_T5],

6126 __ent6: _TCCA[_T6],

6127 __ent7: _TCCA[_T7],

6128 *,

6129 maintain_column_froms: bool = ...,

6130 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...

6131

6132 # END OVERLOADED FUNCTIONS self.with_only_columns

6133

6134 @overload

6135 def with_only_columns(

6136 self,

6137 *entities: _ColumnsClauseArgument[Any],

6138 maintain_column_froms: bool = False,

6139 **__kw: Any,

6140 ) -> Select[Any]: ...

6141

6142 @_generative

6143 def with_only_columns(

6144 self,

6145 *entities: _ColumnsClauseArgument[Any],

6146 maintain_column_froms: bool = False,

6147 **__kw: Any,

6148 ) -> Select[Any]:

6149 r"""Return a new :func:`_expression.select` construct with its columns

6150 clause replaced with the given entities.

6151

6152 By default, this method is exactly equivalent to as if the original

6153 :func:`_expression.select` had been called with the given entities.

6154 E.g. a statement::

6155

6156 s = select(table1.c.a, table1.c.b)

6157 s = s.with_only_columns(table1.c.b)

6158

6159 should be exactly equivalent to::

6160

6161 s = select(table1.c.b)

6162

6163 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6164 will also dynamically alter the FROM clause of the

6165 statement if it is not explicitly stated.

6166 To maintain the existing set of FROMs including those implied by the

6167 current columns clause, add the

6168 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6169 parameter::

6170

6171 s = select(table1.c.a, table2.c.b)

6172 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6173

6174 The above parameter performs a transfer of the effective FROMs

6175 in the columns collection to the :meth:`_sql.Select.select_from`

6176 method, as though the following were invoked::

6177

6178 s = select(table1.c.a, table2.c.b)

6179 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6180

6181 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6182 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6183 collection and performs an operation equivalent to the following::

6184

6185 s = select(table1.c.a, table2.c.b)

6186 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6187

6188 :param \*entities: column expressions to be used.

6189

6190 :param maintain_column_froms: boolean parameter that will ensure the

6191 FROM list implied from the current columns clause will be transferred

6192 to the :meth:`_sql.Select.select_from` method first.

6193

6194 .. versionadded:: 1.4.23

6195

6196 """ # noqa: E501

6197

6198 if __kw:

6199 raise _no_kw()

6200

6201 # memoizations should be cleared here as of

6202 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6203 # is the case for now.

6204 self._assert_no_memoizations()

6205

6206 if maintain_column_froms:

6207 self.select_from.non_generative( # type: ignore

6208 self, *self.columns_clause_froms

6209 )

6210

6211 # then memoize the FROMs etc.

6212 _MemoizedSelectEntities._generate_for_statement(self)

6213

6214 self._raw_columns = [

6215 coercions.expect(roles.ColumnsClauseRole, c)

6216 for c in coercions._expression_collection_was_a_list(

6217 "entities", "Select.with_only_columns", entities

6218 )

6219 ]

6220 return self

6221

6222 @property

6223 def whereclause(self) -> Optional[ColumnElement[Any]]:

6224 """Return the completed WHERE clause for this

6225 :class:`_expression.Select` statement.

6226

6227 This assembles the current collection of WHERE criteria

6228 into a single :class:`_expression.BooleanClauseList` construct.

6229

6230

6231 .. versionadded:: 1.4

6232

6233 """

6234

6235 return BooleanClauseList._construct_for_whereclause(

6236 self._where_criteria

6237 )

6238

6239 _whereclause = whereclause

6240

6241 @_generative

6242 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6243 """Return a new :func:`_expression.select` construct with

6244 the given expression added to

6245 its WHERE clause, joined to the existing clause via AND, if any.

6246

6247 """

6248

6249 assert isinstance(self._where_criteria, tuple)

6250

6251 for criterion in whereclause:

6252 where_criteria: ColumnElement[Any] = coercions.expect(

6253 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6254 )

6255 self._where_criteria += (where_criteria,)

6256 return self

6257

6258 @_generative

6259 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6260 """Return a new :func:`_expression.select` construct with

6261 the given expression added to

6262 its HAVING clause, joined to the existing clause via AND, if any.

6263

6264 """

6265

6266 for criterion in having:

6267 having_criteria = coercions.expect(

6268 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6269 )

6270 self._having_criteria += (having_criteria,)

6271 return self

6272

6273 @_generative

6274 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6275 r"""Return a new :func:`_expression.select` construct which

6276 will apply DISTINCT to the SELECT statement overall.

6277

6278 E.g.::

6279

6280 from sqlalchemy import select

6281

6282 stmt = select(users_table.c.id, users_table.c.name).distinct()

6283

6284 The above would produce an statement resembling:

6285

6286 .. sourcecode:: sql

6287

6288 SELECT DISTINCT user.id, user.name FROM user

6289

6290 The method also accepts an ``*expr`` parameter which produces the

6291 PostgreSQL dialect-specific ``DISTINCT ON`` expression. Using this

6292 parameter on other backends which don't support this syntax will

6293 raise an error.

6294

6295 :param \*expr: optional column expressions. When present,

6296 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6297 construct. A deprecation warning and/or :class:`_exc.CompileError`

6298 will be raised on other backends.

6299

6300 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6301 and will raise :class:`_exc.CompileError` in a future version.

6302

6303 """

6304 if expr:

6305 self._distinct = True

6306 self._distinct_on = self._distinct_on + tuple(

6307 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6308 for e in expr

6309 )

6310 else:

6311 self._distinct = True

6312 return self

6313

6314 @_generative

6315 def select_from(self, *froms: _FromClauseArgument) -> Self:

6316 r"""Return a new :func:`_expression.select` construct with the

6317 given FROM expression(s)

6318 merged into its list of FROM objects.

6319

6320 E.g.::

6321

6322 table1 = table("t1", column("a"))

6323 table2 = table("t2", column("b"))

6324 s = select(table1.c.a).select_from(

6325 table1.join(table2, table1.c.a == table2.c.b)

6326 )

6327

6328 The "from" list is a unique set on the identity of each element,

6329 so adding an already present :class:`_schema.Table`

6330 or other selectable

6331 will have no effect. Passing a :class:`_expression.Join` that refers

6332 to an already present :class:`_schema.Table`

6333 or other selectable will have

6334 the effect of concealing the presence of that selectable as

6335 an individual element in the rendered FROM list, instead

6336 rendering it into a JOIN clause.

6337

6338 While the typical purpose of :meth:`_expression.Select.select_from`

6339 is to

6340 replace the default, derived FROM clause with a join, it can

6341 also be called with individual table elements, multiple times

6342 if desired, in the case that the FROM clause cannot be fully

6343 derived from the columns clause::

6344

6345 select(func.count("*")).select_from(table1)

6346

6347 """

6348

6349 self._from_obj += tuple(

6350 coercions.expect(

6351 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6352 )

6353 for fromclause in froms

6354 )

6355 return self

6356

6357 @_generative

6358 def correlate(

6359 self,

6360 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6361 ) -> Self:

6362 r"""Return a new :class:`_expression.Select`

6363 which will correlate the given FROM

6364 clauses to that of an enclosing :class:`_expression.Select`.

6365

6366 Calling this method turns off the :class:`_expression.Select` object's

6367 default behavior of "auto-correlation". Normally, FROM elements

6368 which appear in a :class:`_expression.Select`

6369 that encloses this one via

6370 its :term:`WHERE clause`, ORDER BY, HAVING or

6371 :term:`columns clause` will be omitted from this

6372 :class:`_expression.Select`

6373 object's :term:`FROM clause`.

6374 Setting an explicit correlation collection using the

6375 :meth:`_expression.Select.correlate`

6376 method provides a fixed list of FROM objects

6377 that can potentially take place in this process.

6378

6379 When :meth:`_expression.Select.correlate`

6380 is used to apply specific FROM clauses

6381 for correlation, the FROM elements become candidates for

6382 correlation regardless of how deeply nested this

6383 :class:`_expression.Select`

6384 object is, relative to an enclosing :class:`_expression.Select`

6385 which refers to

6386 the same FROM object. This is in contrast to the behavior of

6387 "auto-correlation" which only correlates to an immediate enclosing

6388 :class:`_expression.Select`.

6389 Multi-level correlation ensures that the link

6390 between enclosed and enclosing :class:`_expression.Select`

6391 is always via

6392 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6393 correlation to take place.

6394

6395 If ``None`` is passed, the :class:`_expression.Select`

6396 object will correlate

6397 none of its FROM entries, and all will render unconditionally

6398 in the local FROM clause.

6399

6400 :param \*fromclauses: one or more :class:`.FromClause` or other

6401 FROM-compatible construct such as an ORM mapped entity to become part

6402 of the correlate collection; alternatively pass a single value

6403 ``None`` to remove all existing correlations.

6404

6405 .. seealso::

6406

6407 :meth:`_expression.Select.correlate_except`

6408

6409 :ref:`tutorial_scalar_subquery`

6410

6411 """

6412

6413 # tests failing when we try to change how these

6414 # arguments are passed

6415

6416 self._auto_correlate = False

6417 if not fromclauses or fromclauses[0] in {None, False}:

6418 if len(fromclauses) > 1:

6419 raise exc.ArgumentError(

6420 "additional FROM objects not accepted when "

6421 "passing None/False to correlate()"

6422 )

6423 self._correlate = ()

6424 else:

6425 self._correlate = self._correlate + tuple(

6426 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6427 )

6428 return self

6429

6430 @_generative

6431 def correlate_except(

6432 self,

6433 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6434 ) -> Self:

6435 r"""Return a new :class:`_expression.Select`

6436 which will omit the given FROM

6437 clauses from the auto-correlation process.

6438

6439 Calling :meth:`_expression.Select.correlate_except` turns off the

6440 :class:`_expression.Select` object's default behavior of

6441 "auto-correlation" for the given FROM elements. An element

6442 specified here will unconditionally appear in the FROM list, while

6443 all other FROM elements remain subject to normal auto-correlation

6444 behaviors.

6445

6446 If ``None`` is passed, or no arguments are passed,

6447 the :class:`_expression.Select` object will correlate all of its

6448 FROM entries.

6449

6450 :param \*fromclauses: a list of one or more

6451 :class:`_expression.FromClause`

6452 constructs, or other compatible constructs (i.e. ORM-mapped

6453 classes) to become part of the correlate-exception collection.

6454

6455 .. seealso::

6456

6457 :meth:`_expression.Select.correlate`

6458

6459 :ref:`tutorial_scalar_subquery`

6460

6461 """

6462

6463 self._auto_correlate = False

6464 if not fromclauses or fromclauses[0] in {None, False}:

6465 if len(fromclauses) > 1:

6466 raise exc.ArgumentError(

6467 "additional FROM objects not accepted when "

6468 "passing None/False to correlate_except()"

6469 )

6470 self._correlate_except = ()

6471 else:

6472 self._correlate_except = (self._correlate_except or ()) + tuple(

6473 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6474 )

6475

6476 return self

6477

6478 @HasMemoized_ro_memoized_attribute

6479 def selected_columns(

6480 self,

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

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

6483 representing the columns that

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

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

6486

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

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

6489 within this collection cannot be directly nested inside another SELECT

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

6491 necessary parenthesization required by SQL.

6492

6493 For a :func:`_expression.select` construct, the collection here is

6494 exactly what would be rendered inside the "SELECT" statement, and the

6495 :class:`_expression.ColumnElement` objects are directly present as they

6496 were given, e.g.::

6497

6498 col1 = column("q", Integer)

6499 col2 = column("p", Integer)

6500 stmt = select(col1, col2)

6501

6502 Above, ``stmt.selected_columns`` would be a collection that contains

6503 the ``col1`` and ``col2`` objects directly. For a statement that is

6504 against a :class:`_schema.Table` or other

6505 :class:`_expression.FromClause`, the collection will use the

6506 :class:`_expression.ColumnElement` objects that are in the

6507 :attr:`_expression.FromClause.c` collection of the from element.

6508

6509 A use case for the :attr:`_sql.Select.selected_columns` collection is

6510 to allow the existing columns to be referenced when adding additional

6511 criteria, e.g.::

6512

6513 def filter_on_id(my_select, id):

6514 return my_select.where(my_select.selected_columns["id"] == id)

6515

6516

6517 stmt = select(MyModel)

6518

6519 # adds "WHERE id=:param" to the statement

6520 stmt = filter_on_id(stmt, 42)

6521

6522 .. note::

6523

6524 The :attr:`_sql.Select.selected_columns` collection does not

6525 include expressions established in the columns clause using the

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

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

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

6529 construct.

6530

6531

6532 .. versionadded:: 1.4

6533

6534 """

6535

6536 # compare to SelectState._generate_columns_plus_names, which

6537 # generates the actual names used in the SELECT string. that

6538 # method is more complex because it also renders columns that are

6539 # fully ambiguous, e.g. same column more than once.

6540 conv = cast(

6541 "Callable[[Any], str]",

6542 SelectState._column_naming_convention(self._label_style),

6543 )

6544

6545 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6546 [

6547 (conv(c), c)

6548 for c in self._all_selected_columns

6549 if is_column_element(c)

6550 ]

6551 )

6552 return cc.as_readonly()

6553

6554 @HasMemoized_ro_memoized_attribute

6555 def _all_selected_columns(self) -> _SelectIterable:

6556 meth = SelectState.get_plugin_class(self).all_selected_columns

6557 return list(meth(self))

6558

6559 def _ensure_disambiguated_names(self) -> Select[Any]:

6560 if self._label_style is LABEL_STYLE_NONE:

6561 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6562 return self

6563

6564 def _generate_fromclause_column_proxies(

6565 self,

6566 subquery: FromClause,

6567 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6568 primary_key: ColumnSet,

6569 foreign_keys: Set[KeyedColumnElement[Any]],

6570 *,

6571 proxy_compound_columns: Optional[

6572 Iterable[Sequence[ColumnElement[Any]]]

6573 ] = None,

6574 ) -> None:

6575 """Generate column proxies to place in the exported ``.c``

6576 collection of a subquery."""

6577

6578 if proxy_compound_columns:

6579 extra_col_iterator = proxy_compound_columns

6580 prox = [

6581 c._make_proxy(

6582 subquery,

6583 key=proxy_key,

6584 name=required_label_name,

6585 name_is_truncatable=True,

6586 compound_select_cols=extra_cols,

6587 primary_key=primary_key,

6588 foreign_keys=foreign_keys,

6589 )

6590 for (

6591 (

6592 required_label_name,

6593 proxy_key,

6594 fallback_label_name,

6595 c,

6596 repeated,

6597 ),

6598 extra_cols,

6599 ) in (

6600 zip(

6601 self._generate_columns_plus_names(False),

6602 extra_col_iterator,

6603 )

6604 )

6605 if is_column_element(c)

6606 ]

6607 else:

6608 prox = [

6609 c._make_proxy(

6610 subquery,

6611 key=proxy_key,

6612 name=required_label_name,

6613 name_is_truncatable=True,

6614 primary_key=primary_key,

6615 foreign_keys=foreign_keys,

6616 )

6617 for (

6618 required_label_name,

6619 proxy_key,

6620 fallback_label_name,

6621 c,

6622 repeated,

6623 ) in (self._generate_columns_plus_names(False))

6624 if is_column_element(c)

6625 ]

6626

6627 columns._populate_separate_keys(prox)

6628

6629 def _needs_parens_for_grouping(self) -> bool:

6630 return self._has_row_limiting_clause or bool(

6631 self._order_by_clause.clauses

6632 )

6633

6634 def self_group(

6635 self, against: Optional[OperatorType] = None

6636 ) -> Union[SelectStatementGrouping[Self], Self]:

6637 """Return a 'grouping' construct as per the

6638 :class:`_expression.ClauseElement` specification.

6639

6640 This produces an element that can be embedded in an expression. Note

6641 that this method is called automatically as needed when constructing

6642 expressions and should not require explicit use.

6643

6644 """

6645 if (

6646 isinstance(against, CompoundSelect)

6647 and not self._needs_parens_for_grouping()

6648 ):

6649 return self

6650 else:

6651 return SelectStatementGrouping(self)

6652

6653 def union(

6654 self, *other: _SelectStatementForCompoundArgument[_TP]

6655 ) -> CompoundSelect[_TP]:

6656 r"""Return a SQL ``UNION`` of this select() construct against

6657 the given selectables provided as positional arguments.

6658

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

6660 UNION.

6661

6662 .. versionchanged:: 1.4.28

6663

6664 multiple elements are now accepted.

6665

6666 :param \**kwargs: keyword arguments are forwarded to the constructor

6667 for the newly created :class:`_sql.CompoundSelect` object.

6668

6669 """

6670 return CompoundSelect._create_union(self, *other)

6671

6672 def union_all(

6673 self, *other: _SelectStatementForCompoundArgument[_TP]

6674 ) -> CompoundSelect[_TP]:

6675 r"""Return a SQL ``UNION ALL`` of this select() construct against

6676 the given selectables provided as positional arguments.

6677

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

6679 UNION.

6680

6681 .. versionchanged:: 1.4.28

6682

6683 multiple elements are now accepted.

6684

6685 :param \**kwargs: keyword arguments are forwarded to the constructor

6686 for the newly created :class:`_sql.CompoundSelect` object.

6687

6688 """

6689 return CompoundSelect._create_union_all(self, *other)

6690

6691 def except_(

6692 self, *other: _SelectStatementForCompoundArgument[_TP]

6693 ) -> CompoundSelect[_TP]:

6694 r"""Return a SQL ``EXCEPT`` of this select() construct against

6695 the given selectable provided as positional arguments.

6696

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

6698 UNION.

6699

6700 .. versionchanged:: 1.4.28

6701

6702 multiple elements are now accepted.

6703

6704 """

6705 return CompoundSelect._create_except(self, *other)

6706

6707 def except_all(

6708 self, *other: _SelectStatementForCompoundArgument[_TP]

6709 ) -> CompoundSelect[_TP]:

6710 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6711 the given selectables provided as positional arguments.

6712

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

6714 UNION.

6715

6716 .. versionchanged:: 1.4.28

6717

6718 multiple elements are now accepted.

6719

6720 """

6721 return CompoundSelect._create_except_all(self, *other)

6722

6723 def intersect(

6724 self, *other: _SelectStatementForCompoundArgument[_TP]

6725 ) -> CompoundSelect[_TP]:

6726 r"""Return a SQL ``INTERSECT`` of this select() construct against

6727 the given selectables provided as positional arguments.

6728

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

6730 UNION.

6731

6732 .. versionchanged:: 1.4.28

6733

6734 multiple elements are now accepted.

6735

6736 :param \**kwargs: keyword arguments are forwarded to the constructor

6737 for the newly created :class:`_sql.CompoundSelect` object.

6738

6739 """

6740 return CompoundSelect._create_intersect(self, *other)

6741

6742 def intersect_all(

6743 self, *other: _SelectStatementForCompoundArgument[_TP]

6744 ) -> CompoundSelect[_TP]:

6745 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6746 against the given selectables provided as positional arguments.

6747

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

6749 UNION.

6750

6751 .. versionchanged:: 1.4.28

6752

6753 multiple elements are now accepted.

6754

6755 :param \**kwargs: keyword arguments are forwarded to the constructor

6756 for the newly created :class:`_sql.CompoundSelect` object.

6757

6758 """

6759 return CompoundSelect._create_intersect_all(self, *other)

6760

6761

6762class ScalarSelect(

6763 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6764):

6765 """Represent a scalar subquery.

6766

6767

6768 A :class:`_sql.ScalarSelect` is created by invoking the

6769 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6770 then participates in other SQL expressions as a SQL column expression

6771 within the :class:`_sql.ColumnElement` hierarchy.

6772

6773 .. seealso::

6774

6775 :meth:`_sql.SelectBase.scalar_subquery`

6776

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

6778

6779 """

6780

6781 _traverse_internals: _TraverseInternalsType = [

6782 ("element", InternalTraversal.dp_clauseelement),

6783 ("type", InternalTraversal.dp_type),

6784 ]

6785

6786 _from_objects: List[FromClause] = []

6787 _is_from_container = True

6788 if not TYPE_CHECKING:

6789 _is_implicitly_boolean = False

6790 inherit_cache = True

6791

6792 element: SelectBase

6793

6794 def __init__(self, element: SelectBase) -> None:

6795 self.element = element

6796 self.type = element._scalar_type()

6797 self._propagate_attrs = element._propagate_attrs

6798

6799 def __getattr__(self, attr: str) -> Any:

6800 return getattr(self.element, attr)

6801

6802 def __getstate__(self) -> Dict[str, Any]:

6803 return {"element": self.element, "type": self.type}

6804

6805 def __setstate__(self, state: Dict[str, Any]) -> None:

6806 self.element = state["element"]

6807 self.type = state["type"]

6808

6809 @property

6810 def columns(self) -> NoReturn:

6811 raise exc.InvalidRequestError(

6812 "Scalar Select expression has no "

6813 "columns; use this object directly "

6814 "within a column-level expression."

6815 )

6816

6817 c = columns

6818

6819 @_generative

6820 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6821 """Apply a WHERE clause to the SELECT statement referred to

6822 by this :class:`_expression.ScalarSelect`.

6823

6824 """

6825 self.element = cast("Select[Any]", self.element).where(crit)

6826 return self

6827

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

6829 return self

6830

6831 def _ungroup(self) -> Self:

6832 return self

6833

6834 @_generative

6835 def correlate(

6836 self,

6837 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6838 ) -> Self:

6839 r"""Return a new :class:`_expression.ScalarSelect`

6840 which will correlate the given FROM

6841 clauses to that of an enclosing :class:`_expression.Select`.

6842

6843 This method is mirrored from the :meth:`_sql.Select.correlate` method

6844 of the underlying :class:`_sql.Select`. The method applies the

6845 :meth:_sql.Select.correlate` method, then returns a new

6846 :class:`_sql.ScalarSelect` against that statement.

6847

6848 .. versionadded:: 1.4 Previously, the

6849 :meth:`_sql.ScalarSelect.correlate`

6850 method was only available from :class:`_sql.Select`.

6851

6852 :param \*fromclauses: a list of one or more

6853 :class:`_expression.FromClause`

6854 constructs, or other compatible constructs (i.e. ORM-mapped

6855 classes) to become part of the correlate collection.

6856

6857 .. seealso::

6858

6859 :meth:`_expression.ScalarSelect.correlate_except`

6860

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

6862

6863

6864 """

6865 self.element = cast("Select[Any]", self.element).correlate(

6866 *fromclauses

6867 )

6868 return self

6869

6870 @_generative

6871 def correlate_except(

6872 self,

6873 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6874 ) -> Self:

6875 r"""Return a new :class:`_expression.ScalarSelect`

6876 which will omit the given FROM

6877 clauses from the auto-correlation process.

6878

6879 This method is mirrored from the

6880 :meth:`_sql.Select.correlate_except` method of the underlying

6881 :class:`_sql.Select`. The method applies the

6882 :meth:_sql.Select.correlate_except` method, then returns a new

6883 :class:`_sql.ScalarSelect` against that statement.

6884

6885 .. versionadded:: 1.4 Previously, the

6886 :meth:`_sql.ScalarSelect.correlate_except`

6887 method was only available from :class:`_sql.Select`.

6888

6889 :param \*fromclauses: a list of one or more

6890 :class:`_expression.FromClause`

6891 constructs, or other compatible constructs (i.e. ORM-mapped

6892 classes) to become part of the correlate-exception collection.

6893

6894 .. seealso::

6895

6896 :meth:`_expression.ScalarSelect.correlate`

6897

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

6899

6900

6901 """

6902

6903 self.element = cast("Select[Any]", self.element).correlate_except(

6904 *fromclauses

6905 )

6906 return self

6907

6908

6909class Exists(UnaryExpression[bool]):

6910 """Represent an ``EXISTS`` clause.

6911

6912 See :func:`_sql.exists` for a description of usage.

6913

6914 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6915 instance by calling :meth:`_sql.SelectBase.exists`.

6916

6917 """

6918

6919 inherit_cache = True

6920

6921 def __init__(

6922 self,

6923 __argument: Optional[

6924 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6925 ] = None,

6926 ):

6927 s: ScalarSelect[Any]

6928

6929 # TODO: this seems like we should be using coercions for this

6930 if __argument is None:

6931 s = Select(literal_column("*")).scalar_subquery()

6932 elif isinstance(__argument, SelectBase):

6933 s = __argument.scalar_subquery()

6934 s._propagate_attrs = __argument._propagate_attrs

6935 elif isinstance(__argument, ScalarSelect):

6936 s = __argument

6937 else:

6938 s = Select(__argument).scalar_subquery()

6939

6940 UnaryExpression.__init__(

6941 self,

6942 s,

6943 operator=operators.exists,

6944 type_=type_api.BOOLEANTYPE,

6945 )

6946

6947 @util.ro_non_memoized_property

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

6949 return []

6950

6951 def _regroup(

6952 self,

6953 fn: Callable[[Select[Any]], Select[Any]],

6954 ) -> ScalarSelect[Any]:

6955

6956 assert isinstance(self.element, ScalarSelect)

6957 element = self.element.element

6958 if not isinstance(element, Select):

6959 raise exc.InvalidRequestError(

6960 "Can only apply this operation to a plain SELECT construct"

6961 )

6962 new_element = fn(element)

6963

6964 return_value = new_element.scalar_subquery()

6965 return return_value

6966

6967 def select(self) -> Select[Tuple[bool]]:

6968 r"""Return a SELECT of this :class:`_expression.Exists`.

6969

6970 e.g.::

6971

6972 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

6973

6974 This will produce a statement resembling:

6975

6976 .. sourcecode:: sql

6977

6978 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

6979

6980 .. seealso::

6981

6982 :func:`_expression.select` - general purpose

6983 method which allows for arbitrary column lists.

6984

6985 """ # noqa

6986

6987 return Select(self)

6988

6989 def correlate(

6990 self,

6991 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6992 ) -> Self:

6993 """Apply correlation to the subquery noted by this

6994 :class:`_sql.Exists`.

6995

6996 .. seealso::

6997

6998 :meth:`_sql.ScalarSelect.correlate`

6999

7000 """

7001 e = self._clone()

7002 e.element = self._regroup(

7003 lambda element: element.correlate(*fromclauses)

7004 )

7005 return e

7006

7007 def correlate_except(

7008 self,

7009 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7010 ) -> Self:

7011 """Apply correlation to the subquery noted by this

7012 :class:`_sql.Exists`.

7013

7014 .. seealso::

7015

7016 :meth:`_sql.ScalarSelect.correlate_except`

7017

7018 """

7019 e = self._clone()

7020 e.element = self._regroup(

7021 lambda element: element.correlate_except(*fromclauses)

7022 )

7023 return e

7024

7025 def select_from(self, *froms: _FromClauseArgument) -> Self:

7026 """Return a new :class:`_expression.Exists` construct,

7027 applying the given

7028 expression to the :meth:`_expression.Select.select_from`

7029 method of the select

7030 statement contained.

7031

7032 .. note:: it is typically preferable to build a :class:`_sql.Select`

7033 statement first, including the desired WHERE clause, then use the

7034 :meth:`_sql.SelectBase.exists` method to produce an

7035 :class:`_sql.Exists` object at once.

7036

7037 """

7038 e = self._clone()

7039 e.element = self._regroup(lambda element: element.select_from(*froms))

7040 return e

7041

7042 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7043 """Return a new :func:`_expression.exists` construct with the

7044 given expression added to

7045 its WHERE clause, joined to the existing clause via AND, if any.

7046

7047

7048 .. note:: it is typically preferable to build a :class:`_sql.Select`

7049 statement first, including the desired WHERE clause, then use the

7050 :meth:`_sql.SelectBase.exists` method to produce an

7051 :class:`_sql.Exists` object at once.

7052

7053 """

7054 e = self._clone()

7055 e.element = self._regroup(lambda element: element.where(*clause))

7056 return e

7057

7058

7059class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7060 """Wrap a :class:`_expression.TextClause` construct within a

7061 :class:`_expression.SelectBase`

7062 interface.

7063

7064 This allows the :class:`_expression.TextClause` object to gain a

7065 ``.c`` collection

7066 and other FROM-like capabilities such as

7067 :meth:`_expression.FromClause.alias`,

7068 :meth:`_expression.SelectBase.cte`, etc.

7069

7070 The :class:`_expression.TextualSelect` construct is produced via the

7071 :meth:`_expression.TextClause.columns`

7072 method - see that method for details.

7073

7074 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7075 class was renamed

7076 from ``TextAsFrom``, to more correctly suit its role as a

7077 SELECT-oriented object and not a FROM clause.

7078

7079 .. seealso::

7080

7081 :func:`_expression.text`

7082

7083 :meth:`_expression.TextClause.columns` - primary creation interface.

7084

7085 """

7086

7087 __visit_name__ = "textual_select"

7088

7089 _label_style = LABEL_STYLE_NONE

7090

7091 _traverse_internals: _TraverseInternalsType = (

7092 [

7093 ("element", InternalTraversal.dp_clauseelement),

7094 ("column_args", InternalTraversal.dp_clauseelement_list),

7095 ]

7096 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7097 + HasCTE._has_ctes_traverse_internals

7098 + Executable._executable_traverse_internals

7099 )

7100

7101 _is_textual = True

7102

7103 is_text = True

7104 is_select = True

7105

7106 def __init__(

7107 self,

7108 text: TextClause,

7109 columns: List[_ColumnExpressionArgument[Any]],

7110 positional: bool = False,

7111 ) -> None:

7112 self._init(

7113 text,

7114 # convert for ORM attributes->columns, etc

7115 [

7116 coercions.expect(roles.LabeledColumnExprRole, c)

7117 for c in columns

7118 ],

7119 positional,

7120 )

7121

7122 def _init(

7123 self,

7124 text: TextClause,

7125 columns: List[NamedColumn[Any]],

7126 positional: bool = False,

7127 ) -> None:

7128 self.element = text

7129 self.column_args = columns

7130 self.positional = positional

7131

7132 @HasMemoized_ro_memoized_attribute

7133 def selected_columns(

7134 self,

7135 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

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

7137 representing the columns that

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

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

7140

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

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

7143 within this collection cannot be directly nested inside another SELECT

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

7145 necessary parenthesization required by SQL.

7146

7147 For a :class:`_expression.TextualSelect` construct, the collection

7148 contains the :class:`_expression.ColumnElement` objects that were

7149 passed to the constructor, typically via the

7150 :meth:`_expression.TextClause.columns` method.

7151

7152

7153 .. versionadded:: 1.4

7154

7155 """

7156 return ColumnCollection(

7157 (c.key, c) for c in self.column_args

7158 ).as_readonly()

7159

7160 @util.ro_non_memoized_property

7161 def _all_selected_columns(self) -> _SelectIterable:

7162 return self.column_args

7163

7164 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7165 return self

7166

7167 def _ensure_disambiguated_names(self) -> TextualSelect:

7168 return self

7169

7170 @_generative

7171 def bindparams(

7172 self,

7173 *binds: BindParameter[Any],

7174 **bind_as_values: Any,

7175 ) -> Self:

7176 self.element = self.element.bindparams(*binds, **bind_as_values)

7177 return self

7178

7179 def _generate_fromclause_column_proxies(

7180 self,

7181 fromclause: FromClause,

7182 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7183 primary_key: ColumnSet,

7184 foreign_keys: Set[KeyedColumnElement[Any]],

7185 *,

7186 proxy_compound_columns: Optional[

7187 Iterable[Sequence[ColumnElement[Any]]]

7188 ] = None,

7189 ) -> None:

7190 if TYPE_CHECKING:

7191 assert isinstance(fromclause, Subquery)

7192

7193 if proxy_compound_columns:

7194 columns._populate_separate_keys(

7195 c._make_proxy(

7196 fromclause,

7197 compound_select_cols=extra_cols,

7198 primary_key=primary_key,

7199 foreign_keys=foreign_keys,

7200 )

7201 for c, extra_cols in zip(

7202 self.column_args, proxy_compound_columns

7203 )

7204 )

7205 else:

7206 columns._populate_separate_keys(

7207 c._make_proxy(

7208 fromclause,

7209 primary_key=primary_key,

7210 foreign_keys=foreign_keys,

7211 )

7212 for c in self.column_args

7213 )

7214

7215 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7216 return self.column_args[0].type

7217

7218

7219TextAsFrom = TextualSelect

7220"""Backwards compatibility with the previous name"""

7221

7222

7223class AnnotatedFromClause(Annotated):

7224 def _copy_internals(

7225 self,

7226 _annotations_traversal: bool = False,

7227 ind_cols_on_fromclause: bool = False,

7228 **kw: Any,

7229 ) -> None:

7230 super()._copy_internals(**kw)

7231

7232 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7233 # the traversals used by annotations for these cases are not currently

7234 # designed around expecting that inner elements inside of

7235 # AnnotatedFromClause's element are also deep copied, so skip for these

7236 # cases. in other cases such as plain visitors.cloned_traverse(), we

7237 # expect this to happen. see issue #12915

7238 if not _annotations_traversal:

7239 ee = self._Annotated__element # type: ignore

7240 ee._copy_internals(**kw)

7241

7242 if ind_cols_on_fromclause:

7243 # passed from annotations._deep_annotate(). See that function

7244 # for notes

7245 ee = self._Annotated__element # type: ignore

7246 self.c = ee.__class__.c.fget(self) # type: ignore

7247

7248 @util.ro_memoized_property

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

7250 """proxy the .c collection of the underlying FromClause.

7251

7252 Originally implemented in 2008 as a simple load of the .c collection

7253 when the annotated construct was created (see d3621ae961a), in modern

7254 SQLAlchemy versions this can be expensive for statements constructed

7255 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7256 it, which works just as well.

7257

7258 Two different use cases seem to require the collection either copied

7259 from the underlying one, or unique to this AnnotatedFromClause.

7260

7261 See test_selectable->test_annotated_corresponding_column

7262

7263 """

7264 ee = self._Annotated__element # type: ignore

7265 return ee.c # type: ignore