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

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

1155 FROM table1 JOIN table2 ON true

1156

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

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

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

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

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

1162

1163

1164 .. versionadded:: 1.4

1165

1166 """ # noqa: E501

1167

1168 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1171 when generating the columns clause of a SELECT statement.

1172

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

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

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

1176

1177 .. sourcecode:: pycon+sql

1178

1179 >>> from sqlalchemy import (

1180 ... table,

1181 ... column,

1182 ... select,

1183 ... true,

1184 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1185 ... )

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

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

1188 >>> print(

1189 ... select(table1, table2)

1190 ... .join(table2, true())

1191 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1192 ... )

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

1194 FROM table1 JOIN table2 ON true

1195

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

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

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

1199

1200 .. versionadded:: 1.4

1201

1202 """ # noqa: E501

1203

1204 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1205 """The default label style, refers to

1206 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1207

1208 .. versionadded:: 1.4

1209

1210 """

1211

1212 LABEL_STYLE_LEGACY_ORM = 3

1213

1214

1215(

1216 LABEL_STYLE_NONE,

1217 LABEL_STYLE_TABLENAME_PLUS_COL,

1218 LABEL_STYLE_DISAMBIGUATE_ONLY,

1219 _,

1220) = list(SelectLabelStyle)

1221

1222LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1223

1224

1225class Join(roles.DMLTableRole, FromClause):

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

1227 :class:`_expression.FromClause`

1228 elements.

1229

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

1231 is the module-level

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

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

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

1235 :class:`_schema.Table`).

1236

1237 .. seealso::

1238

1239 :func:`_expression.join`

1240

1241 :meth:`_expression.FromClause.join`

1242

1243 """

1244

1245 __visit_name__ = "join"

1246

1247 _traverse_internals: _TraverseInternalsType = [

1248 ("left", InternalTraversal.dp_clauseelement),

1249 ("right", InternalTraversal.dp_clauseelement),

1250 ("onclause", InternalTraversal.dp_clauseelement),

1251 ("isouter", InternalTraversal.dp_boolean),

1252 ("full", InternalTraversal.dp_boolean),

1253 ]

1254

1255 _is_join = True

1256

1257 left: FromClause

1258 right: FromClause

1259 onclause: Optional[ColumnElement[bool]]

1260 isouter: bool

1261 full: bool

1262

1263 def __init__(

1264 self,

1265 left: _FromClauseArgument,

1266 right: _FromClauseArgument,

1267 onclause: Optional[_OnClauseArgument] = None,

1268 isouter: bool = False,

1269 full: bool = False,

1270 ):

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

1272

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

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

1275 :class:`_expression.FromClause` object.

1276

1277 """

1278

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

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

1281 # annotated objects. test_orm.py -> test_fetch_results

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

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

1284 # callcounts for a single compilation in that particular test

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

1286 # 29200 -> 30373

1287

1288 self.left = coercions.expect(

1289 roles.FromClauseRole,

1290 left,

1291 )

1292 self.right = coercions.expect(

1293 roles.FromClauseRole,

1294 right,

1295 ).self_group()

1296

1297 if onclause is None:

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

1299 else:

1300 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1301 # not merged yet

1302 self.onclause = coercions.expect(

1303 roles.OnClauseRole, onclause

1304 ).self_group(against=operators._asbool)

1305

1306 self.isouter = isouter

1307 self.full = full

1308

1309 @util.ro_non_memoized_property

1310 def description(self) -> str:

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

1312 self.left.description,

1313 id(self.left),

1314 self.right.description,

1315 id(self.right),

1316 )

1317

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

1319 return (

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

1321 # as well

1322 hash(fromclause) == hash(self)

1323 or self.left.is_derived_from(fromclause)

1324 or self.right.is_derived_from(fromclause)

1325 )

1326

1327 def self_group(

1328 self, against: Optional[OperatorType] = None

1329 ) -> FromGrouping:

1330 return FromGrouping(self)

1331

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

1333 def _populate_column_collection(

1334 self,

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

1336 primary_key: ColumnSet,

1337 foreign_keys: Set[KeyedColumnElement[Any]],

1338 ) -> None:

1339 sqlutil = util.preloaded.sql_util

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

1341 c for c in self.right.c

1342 ]

1343

1344 primary_key.extend(

1345 sqlutil.reduce_columns(

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

1347 )

1348 )

1349 columns._populate_separate_keys(

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

1351 )

1352 foreign_keys.update(

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

1354 )

1355

1356 def _copy_internals(

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

1358 ) -> None:

1359 # see Select._copy_internals() for similar concept

1360

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

1362 # determine the new FROM clauses

1363 all_the_froms = set(

1364 itertools.chain(

1365 _from_objects(self.left),

1366 _from_objects(self.right),

1367 )

1368 )

1369

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

1371 # cache used by the clone function

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

1373

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

1375 # ColumnClause with parent table referring to those

1376 # replaced FromClause objects

1377 def replace(

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

1379 **kw: Any,

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

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

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

1383 return newelem

1384 return None

1385

1386 kw["replace"] = replace

1387

1388 # run normal _copy_internals. the clones for

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

1390 # cache

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

1392

1393 self._reset_memoizations()

1394

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

1396 super()._refresh_for_new_column(column)

1397 self.left._refresh_for_new_column(column)

1398 self.right._refresh_for_new_column(column)

1399

1400 def _match_primaries(

1401 self,

1402 left: FromClause,

1403 right: FromClause,

1404 ) -> ColumnElement[bool]:

1405 if isinstance(left, Join):

1406 left_right = left.right

1407 else:

1408 left_right = None

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

1410

1411 @classmethod

1412 def _join_condition(

1413 cls,

1414 a: FromClause,

1415 b: FromClause,

1416 *,

1417 a_subset: Optional[FromClause] = None,

1418 consider_as_foreign_keys: Optional[

1419 AbstractSet[ColumnClause[Any]]

1420 ] = None,

1421 ) -> ColumnElement[bool]:

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

1423

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

1425

1426 """

1427 constraints = cls._joincond_scan_left_right(

1428 a, a_subset, b, consider_as_foreign_keys

1429 )

1430

1431 if len(constraints) > 1:

1432 cls._joincond_trim_constraints(

1433 a, b, constraints, consider_as_foreign_keys

1434 )

1435

1436 if len(constraints) == 0:

1437 if isinstance(b, FromGrouping):

1438 hint = (

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

1440 "subquery using alias()?"

1441 )

1442 else:

1443 hint = ""

1444 raise exc.NoForeignKeysError(

1445 "Can't find any foreign key relationships "

1446 "between '%s' and '%s'.%s"

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

1448 )

1449

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

1451 if len(crit) == 1:

1452 return crit[0]

1453 else:

1454 return and_(*crit)

1455

1456 @classmethod

1457 def _can_join(

1458 cls,

1459 left: FromClause,

1460 right: FromClause,

1461 *,

1462 consider_as_foreign_keys: Optional[

1463 AbstractSet[ColumnClause[Any]]

1464 ] = None,

1465 ) -> bool:

1466 if isinstance(left, Join):

1467 left_right = left.right

1468 else:

1469 left_right = None

1470

1471 constraints = cls._joincond_scan_left_right(

1472 a=left,

1473 b=right,

1474 a_subset=left_right,

1475 consider_as_foreign_keys=consider_as_foreign_keys,

1476 )

1477

1478 return bool(constraints)

1479

1480 @classmethod

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

1482 def _joincond_scan_left_right(

1483 cls,

1484 a: FromClause,

1485 a_subset: Optional[FromClause],

1486 b: FromClause,

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

1488 ) -> collections.defaultdict[

1489 Optional[ForeignKeyConstraint],

1490 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1491 ]:

1492 sql_util = util.preloaded.sql_util

1493

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

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

1496

1497 constraints: collections.defaultdict[

1498 Optional[ForeignKeyConstraint],

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

1500 ] = collections.defaultdict(list)

1501

1502 for left in (a_subset, a):

1503 if left is None:

1504 continue

1505 for fk in sorted(

1506 b.foreign_keys,

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

1508 ):

1509 if (

1510 consider_as_foreign_keys is not None

1511 and fk.parent not in consider_as_foreign_keys

1512 ):

1513 continue

1514 try:

1515 col = fk.get_referent(left)

1516 except exc.NoReferenceError as nrte:

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

1518 if nrte.table_name in table_names:

1519 raise

1520 else:

1521 continue

1522

1523 if col is not None:

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

1525 if left is not b:

1526 for fk in sorted(

1527 left.foreign_keys,

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

1529 ):

1530 if (

1531 consider_as_foreign_keys is not None

1532 and fk.parent not in consider_as_foreign_keys

1533 ):

1534 continue

1535 try:

1536 col = fk.get_referent(b)

1537 except exc.NoReferenceError as nrte:

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

1539 if nrte.table_name in table_names:

1540 raise

1541 else:

1542 continue

1543

1544 if col is not None:

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

1546 if constraints:

1547 break

1548 return constraints

1549

1550 @classmethod

1551 def _joincond_trim_constraints(

1552 cls,

1553 a: FromClause,

1554 b: FromClause,

1555 constraints: Dict[Any, Any],

1556 consider_as_foreign_keys: Optional[Any],

1557 ) -> None:

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

1559 # to include just those FKCs that match exactly to

1560 # "consider_as_foreign_keys".

1561 if consider_as_foreign_keys:

1562 for const in list(constraints):

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

1564 consider_as_foreign_keys

1565 ):

1566 del constraints[const]

1567

1568 # if still multiple constraints, but

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

1570 if len(constraints) > 1:

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

1572 if len(dedupe) == 1:

1573 key = list(constraints)[0]

1574 constraints = {key: constraints[key]}

1575

1576 if len(constraints) != 1:

1577 raise exc.AmbiguousForeignKeysError(

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

1579 "tables have more than one foreign key "

1580 "constraint relationship between them. "

1581 "Please specify the 'onclause' of this "

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

1583 )

1584

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

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

1587 :class:`_expression.Join`.

1588

1589 E.g.::

1590

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

1592

1593 stmt = stmt.select()

1594

1595 The above will produce a SQL string resembling:

1596

1597 .. sourcecode:: sql

1598

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

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

1601

1602 """

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

1604

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

1606 def _anonymous_fromclause(

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

1608 ) -> TODO_Any:

1609 sqlutil = util.preloaded.sql_util

1610 if flat:

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

1612 left_name = name # will recurse

1613 else:

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

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

1616 else:

1617 left_name = name

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

1619 right_name = name # will recurse

1620 else:

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

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

1623 else:

1624 right_name = name

1625 left_a, right_a = (

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

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

1628 )

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

1630 sqlutil.ClauseAdapter(right_a)

1631 )

1632

1633 return left_a.join(

1634 right_a,

1635 adapter.traverse(self.onclause),

1636 isouter=self.isouter,

1637 full=self.full,

1638 )

1639 else:

1640 return (

1641 self.select()

1642 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1643 .correlate(None)

1644 .alias(name)

1645 )

1646

1647 @util.ro_non_memoized_property

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

1649 return itertools.chain(

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

1651 )

1652

1653 @util.ro_non_memoized_property

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

1655 self_list: List[FromClause] = [self]

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

1657

1658

1659class NoInit:

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

1661 raise NotImplementedError(

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

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

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

1665 "selectable objects."

1666 % (

1667 self.__class__.__name__,

1668 self.__class__.__name__.lower(),

1669 self.__class__.__name__.lower(),

1670 )

1671 )

1672

1673

1674class LateralFromClause(NamedFromClause):

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

1676

1677

1678# FromClause ->

1679# AliasedReturnsRows

1680# -> Alias only for FromClause

1681# -> Subquery only for SelectBase

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

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

1684# w/ non-deprecated coercion

1685# -> TableSample -> only for FromClause

1686

1687

1688class AliasedReturnsRows(NoInit, NamedFromClause):

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

1690 selectables."""

1691

1692 _is_from_container = True

1693

1694 _supports_derived_columns = False

1695

1696 element: ReturnsRows

1697

1698 _traverse_internals: _TraverseInternalsType = [

1699 ("element", InternalTraversal.dp_clauseelement),

1700 ("name", InternalTraversal.dp_anon_name),

1701 ]

1702

1703 @classmethod

1704 def _construct(

1705 cls,

1706 selectable: Any,

1707 *,

1708 name: Optional[str] = None,

1709 **kw: Any,

1710 ) -> Self:

1711 obj = cls.__new__(cls)

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

1713 return obj

1714

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

1716 self.element = coercions.expect(

1717 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1718 )

1719 self.element = selectable

1720 self._orig_name = name

1721 if name is None:

1722 if (

1723 isinstance(selectable, FromClause)

1724 and selectable.named_with_column

1725 ):

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

1727 if isinstance(name, _anonymous_label):

1728 name = None

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

1730 self.name = name

1731

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

1733 super()._refresh_for_new_column(column)

1734 self.element._refresh_for_new_column(column)

1735

1736 def _populate_column_collection(

1737 self,

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

1739 primary_key: ColumnSet,

1740 foreign_keys: Set[KeyedColumnElement[Any]],

1741 ) -> None:

1742 self.element._generate_fromclause_column_proxies(

1743 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1744 )

1745

1746 @util.ro_non_memoized_property

1747 def description(self) -> str:

1748 name = self.name

1749 if isinstance(name, _anonymous_label):

1750 return "anon_1"

1751

1752 return name

1753

1754 @util.ro_non_memoized_property

1755 def implicit_returning(self) -> bool:

1756 return self.element.implicit_returning # type: ignore

1757

1758 @property

1759 def original(self) -> ReturnsRows:

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

1761 return self.element

1762

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

1764 if fromclause in self._cloned_set:

1765 return True

1766 return self.element.is_derived_from(fromclause)

1767

1768 def _copy_internals(

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

1770 ) -> None:

1771 existing_element = self.element

1772

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

1774

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

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

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

1778 # performance.

1779 if existing_element is not self.element:

1780 self._reset_column_collection()

1781

1782 @property

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

1784 return [self]

1785

1786

1787class FromClauseAlias(AliasedReturnsRows):

1788 element: FromClause

1789

1790 @util.ro_non_memoized_property

1791 def description(self) -> str:

1792 name = self.name

1793 if isinstance(name, _anonymous_label):

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

1795

1796 return name

1797

1798

1799class Alias(roles.DMLTableRole, FromClauseAlias):

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

1801

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

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

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

1805

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

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

1808 method available

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

1810

1811 .. seealso::

1812

1813 :meth:`_expression.FromClause.alias`

1814

1815 """

1816

1817 __visit_name__ = "alias"

1818

1819 inherit_cache = True

1820

1821 element: FromClause

1822

1823 @classmethod

1824 def _factory(

1825 cls,

1826 selectable: FromClause,

1827 name: Optional[str] = None,

1828 flat: bool = False,

1829 ) -> NamedFromClause:

1830 return coercions.expect(

1831 roles.FromClauseRole, selectable, allow_select=True

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

1833

1834

1835class TableValuedAlias(LateralFromClause, Alias):

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

1837

1838 This construct provides for a SQL function that returns columns

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

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

1841 method, e.g.:

1842

1843 .. sourcecode:: pycon+sql

1844

1845 >>> from sqlalchemy import select, func

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

1847 ... "value"

1848 ... )

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

1850 {printsql}SELECT anon_1.value

1851 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1852

1853 .. versionadded:: 1.4.0b2

1854

1855 .. seealso::

1856

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

1858

1859 """ # noqa: E501

1860

1861 __visit_name__ = "table_valued_alias"

1862

1863 _supports_derived_columns = True

1864 _render_derived = False

1865 _render_derived_w_types = False

1866 joins_implicitly = False

1867

1868 _traverse_internals: _TraverseInternalsType = [

1869 ("element", InternalTraversal.dp_clauseelement),

1870 ("name", InternalTraversal.dp_anon_name),

1871 ("_tableval_type", InternalTraversal.dp_type),

1872 ("_render_derived", InternalTraversal.dp_boolean),

1873 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1874 ]

1875

1876 def _init(

1877 self,

1878 selectable: Any,

1879 *,

1880 name: Optional[str] = None,

1881 table_value_type: Optional[TableValueType] = None,

1882 joins_implicitly: bool = False,

1883 ) -> None:

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

1885

1886 self.joins_implicitly = joins_implicitly

1887 self._tableval_type = (

1888 type_api.TABLEVALUE

1889 if table_value_type is None

1890 else table_value_type

1891 )

1892

1893 @HasMemoized.memoized_attribute

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

1895 """Return a column expression representing this

1896 :class:`_sql.TableValuedAlias`.

1897

1898 This accessor is used to implement the

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

1900 method for further details.

1901

1902 E.g.:

1903

1904 .. sourcecode:: pycon+sql

1905

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

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

1908

1909 .. seealso::

1910

1911 :meth:`_functions.FunctionElement.column_valued`

1912

1913 """

1914

1915 return TableValuedColumn(self, self._tableval_type)

1916

1917 def alias(

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

1919 ) -> TableValuedAlias:

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

1921

1922 This creates a distinct FROM object that will be distinguished

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

1924

1925 """

1926

1927 tva: TableValuedAlias = TableValuedAlias._construct(

1928 self,

1929 name=name,

1930 table_value_type=self._tableval_type,

1931 joins_implicitly=self.joins_implicitly,

1932 )

1933

1934 if self._render_derived:

1935 tva._render_derived = True

1936 tva._render_derived_w_types = self._render_derived_w_types

1937

1938 return tva

1939

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

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

1942 set, so that it renders as LATERAL.

1943

1944 .. seealso::

1945

1946 :func:`_expression.lateral`

1947

1948 """

1949 tva = self.alias(name=name)

1950 tva._is_lateral = True

1951 return tva

1952

1953 def render_derived(

1954 self,

1955 name: Optional[str] = None,

1956 with_types: bool = False,

1957 ) -> TableValuedAlias:

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

1959

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

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

1962

1963 .. sourcecode:: pycon+sql

1964

1965 >>> print(

1966 ... select(

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

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

1969 ... .render_derived()

1970 ... )

1971 ... )

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

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

1974

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

1976 the alias expression (this syntax currently applies to the

1977 PostgreSQL database):

1978

1979 .. sourcecode:: pycon+sql

1980

1981 >>> print(

1982 ... select(

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

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

1985 ... .render_derived(with_types=True)

1986 ... )

1987 ... )

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

1989 AS anon_1(a INTEGER, b VARCHAR)

1990

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

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

1993

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

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

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

1997

1998 """ # noqa: E501

1999

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

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

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

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

2004 # (just saw it happen on CI)

2005

2006 # construct against original to prevent memory growth

2007 # for repeated generations

2008 new_alias: TableValuedAlias = TableValuedAlias._construct(

2009 self.element,

2010 name=name,

2011 table_value_type=self._tableval_type,

2012 joins_implicitly=self.joins_implicitly,

2013 )

2014 new_alias._render_derived = True

2015 new_alias._render_derived_w_types = with_types

2016 return new_alias

2017

2018

2019class Lateral(FromClauseAlias, LateralFromClause):

2020 """Represent a LATERAL subquery.

2021

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

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

2024 method available

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

2026

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

2028 PostgreSQL versions provide support for this keyword.

2029

2030 .. seealso::

2031

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

2033

2034 """

2035

2036 __visit_name__ = "lateral"

2037 _is_lateral = True

2038

2039 inherit_cache = True

2040

2041 @classmethod

2042 def _factory(

2043 cls,

2044 selectable: Union[SelectBase, _FromClauseArgument],

2045 name: Optional[str] = None,

2046 ) -> LateralFromClause:

2047 return coercions.expect(

2048 roles.FromClauseRole, selectable, explicit_subquery=True

2049 ).lateral(name=name)

2050

2051

2052class TableSample(FromClauseAlias):

2053 """Represent a TABLESAMPLE clause.

2054

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

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

2057 method

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

2059

2060 .. seealso::

2061

2062 :func:`_expression.tablesample`

2063

2064 """

2065

2066 __visit_name__ = "tablesample"

2067

2068 _traverse_internals: _TraverseInternalsType = (

2069 AliasedReturnsRows._traverse_internals

2070 + [

2071 ("sampling", InternalTraversal.dp_clauseelement),

2072 ("seed", InternalTraversal.dp_clauseelement),

2073 ]

2074 )

2075

2076 @classmethod

2077 def _factory(

2078 cls,

2079 selectable: _FromClauseArgument,

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

2081 name: Optional[str] = None,

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

2083 ) -> TableSample:

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

2085 sampling, name=name, seed=seed

2086 )

2087

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

2089 def _init( # type: ignore[override]

2090 self,

2091 selectable: Any,

2092 *,

2093 name: Optional[str] = None,

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

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

2096 ) -> None:

2097 assert sampling is not None

2098 functions = util.preloaded.sql_functions

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

2100 sampling = functions.func.system(sampling)

2101

2102 self.sampling: Function[Any] = sampling

2103 self.seed = seed

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

2105

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

2107 return self.sampling

2108

2109

2110class CTE(

2111 roles.DMLTableRole,

2112 roles.IsCTERole,

2113 Generative,

2114 HasPrefixes,

2115 HasSuffixes,

2116 AliasedReturnsRows,

2117):

2118 """Represent a Common Table Expression.

2119

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

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

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

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

2124 :class:`_sql.Update` and

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

2126 usage details on CTEs.

2127

2128 .. seealso::

2129

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

2131

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

2133

2134 """

2135

2136 __visit_name__ = "cte"

2137

2138 _traverse_internals: _TraverseInternalsType = (

2139 AliasedReturnsRows._traverse_internals

2140 + [

2141 ("_cte_alias", InternalTraversal.dp_clauseelement),

2142 ("_restates", InternalTraversal.dp_clauseelement),

2143 ("recursive", InternalTraversal.dp_boolean),

2144 ("nesting", InternalTraversal.dp_boolean),

2145 ]

2146 + HasPrefixes._has_prefixes_traverse_internals

2147 + HasSuffixes._has_suffixes_traverse_internals

2148 )

2149

2150 element: HasCTE

2151

2152 @classmethod

2153 def _factory(

2154 cls,

2155 selectable: HasCTE,

2156 name: Optional[str] = None,

2157 recursive: bool = False,

2158 ) -> CTE:

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

2160 or Common Table Expression instance.

2161

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

2163

2164 """

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

2166 name=name, recursive=recursive

2167 )

2168

2169 def _init(

2170 self,

2171 selectable: Select[Any],

2172 *,

2173 name: Optional[str] = None,

2174 recursive: bool = False,

2175 nesting: bool = False,

2176 _cte_alias: Optional[CTE] = None,

2177 _restates: Optional[CTE] = None,

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

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

2180 ) -> None:

2181 self.recursive = recursive

2182 self.nesting = nesting

2183 self._cte_alias = _cte_alias

2184 # Keep recursivity reference with union/union_all

2185 self._restates = _restates

2186 if _prefixes:

2187 self._prefixes = _prefixes

2188 if _suffixes:

2189 self._suffixes = _suffixes

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

2191

2192 def _populate_column_collection(

2193 self,

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

2195 primary_key: ColumnSet,

2196 foreign_keys: Set[KeyedColumnElement[Any]],

2197 ) -> None:

2198 if self._cte_alias is not None:

2199 self._cte_alias._generate_fromclause_column_proxies(

2200 self,

2201 columns,

2202 primary_key=primary_key,

2203 foreign_keys=foreign_keys,

2204 )

2205 else:

2206 self.element._generate_fromclause_column_proxies(

2207 self,

2208 columns,

2209 primary_key=primary_key,

2210 foreign_keys=foreign_keys,

2211 )

2212

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

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

2215 :class:`_expression.CTE`.

2216

2217 This method is a CTE-specific specialization of the

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

2219

2220 .. seealso::

2221

2222 :ref:`tutorial_using_aliases`

2223

2224 :func:`_expression.alias`

2225

2226 """

2227 return CTE._construct(

2228 self.element,

2229 name=name,

2230 recursive=self.recursive,

2231 nesting=self.nesting,

2232 _cte_alias=self,

2233 _prefixes=self._prefixes,

2234 _suffixes=self._suffixes,

2235 )

2236

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

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

2239 of the original CTE against the given selectables provided

2240 as positional arguments.

2241

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

2243 UNION.

2244

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

2246

2247 .. seealso::

2248

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

2250

2251 """

2252 assert is_select_statement(

2253 self.element

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

2255

2256 return CTE._construct(

2257 self.element.union(*other),

2258 name=self.name,

2259 recursive=self.recursive,

2260 nesting=self.nesting,

2261 _restates=self,

2262 _prefixes=self._prefixes,

2263 _suffixes=self._suffixes,

2264 )

2265

2266 def union_all(

2267 self, *other: _SelectStatementForCompoundArgument[Any]

2268 ) -> CTE:

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

2270 of the original CTE against the given selectables provided

2271 as positional arguments.

2272

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

2274 UNION.

2275

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

2277

2278 .. seealso::

2279

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

2281

2282 """

2283

2284 assert is_select_statement(

2285 self.element

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

2287

2288 return CTE._construct(

2289 self.element.union_all(*other),

2290 name=self.name,

2291 recursive=self.recursive,

2292 nesting=self.nesting,

2293 _restates=self,

2294 _prefixes=self._prefixes,

2295 _suffixes=self._suffixes,

2296 )

2297

2298 def _get_reference_cte(self) -> CTE:

2299 """

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

2301 Updated CTEs should still refer to the original CTE.

2302 This function returns this reference identifier.

2303 """

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

2305

2306

2307class _CTEOpts(NamedTuple):

2308 nesting: bool

2309

2310

2311class _ColumnsPlusNames(NamedTuple):

2312 required_label_name: Optional[str]

2313 """

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

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

2316 """

2317

2318 proxy_key: Optional[str]

2319 """

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

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

2322 select.selected_columns collection

2323 """

2324

2325 fallback_label_name: Optional[str]

2326 """

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

2328 we have to render a label even though

2329 required_label_name was not given

2330 """

2331

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

2333 """

2334 the ColumnElement itself

2335 """

2336

2337 repeated: bool

2338 """

2339 True if this is a duplicate of a previous column

2340 in the list of columns

2341 """

2342

2343

2344class SelectsRows(ReturnsRows):

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

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

2347

2348 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2349

2350 def _generate_columns_plus_names(

2351 self,

2352 anon_for_dupe_key: bool,

2353 cols: Optional[_SelectIterable] = None,

2354 ) -> List[_ColumnsPlusNames]:

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

2356 the compiler.

2357

2358 This is distinct from the _column_naming_convention generator that's

2359 intended for population of .c collections and similar, which has

2360 different rules. the collection returned here calls upon the

2361 _column_naming_convention as well.

2362

2363 """

2364

2365 if cols is None:

2366 cols = self._all_selected_columns

2367

2368 key_naming_convention = SelectState._column_naming_convention(

2369 self._label_style

2370 )

2371

2372 names = {}

2373

2374 result: List[_ColumnsPlusNames] = []

2375 result_append = result.append

2376

2377 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2378 label_style_none = self._label_style is LABEL_STYLE_NONE

2379

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

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

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

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

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

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

2386 dedupe_hash = 1

2387

2388 for c in cols:

2389 repeated = False

2390

2391 if not c._render_label_in_columns_clause:

2392 effective_name = required_label_name = fallback_label_name = (

2393 None

2394 )

2395 elif label_style_none:

2396 if TYPE_CHECKING:

2397 assert is_column_element(c)

2398

2399 effective_name = required_label_name = None

2400 fallback_label_name = c._non_anon_label or c._anon_name_label

2401 else:

2402 if TYPE_CHECKING:

2403 assert is_column_element(c)

2404

2405 if table_qualified:

2406 required_label_name = effective_name = (

2407 fallback_label_name

2408 ) = c._tq_label

2409 else:

2410 effective_name = fallback_label_name = c._non_anon_label

2411 required_label_name = None

2412

2413 if effective_name is None:

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

2415 # not need _expression_label but it isn't

2416 # giving us a clue when to use anon_label instead

2417 expr_label = c._expression_label

2418 if expr_label is None:

2419 repeated = c._anon_name_label in names

2420 names[c._anon_name_label] = c

2421 effective_name = required_label_name = None

2422

2423 if repeated:

2424 # here, "required_label_name" is sent as

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

2426 if table_qualified:

2427 fallback_label_name = (

2428 c._dedupe_anon_tq_label_idx(dedupe_hash)

2429 )

2430 dedupe_hash += 1

2431 else:

2432 fallback_label_name = c._dedupe_anon_label_idx(

2433 dedupe_hash

2434 )

2435 dedupe_hash += 1

2436 else:

2437 fallback_label_name = c._anon_name_label

2438 else:

2439 required_label_name = effective_name = (

2440 fallback_label_name

2441 ) = expr_label

2442

2443 if effective_name is not None:

2444 if TYPE_CHECKING:

2445 assert is_column_element(c)

2446

2447 if effective_name in names:

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

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

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

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

2452 # different column under the same name. apply

2453 # disambiguating label

2454 if table_qualified:

2455 required_label_name = fallback_label_name = (

2456 c._anon_tq_label

2457 )

2458 else:

2459 required_label_name = fallback_label_name = (

2460 c._anon_name_label

2461 )

2462

2463 if anon_for_dupe_key and required_label_name in names:

2464 # here, c._anon_tq_label is definitely unique to

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

2466 # this should always be true.

2467 # this is also an infrequent codepath because

2468 # you need two levels of duplication to be here

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

2470

2471 # the column under the disambiguating label is

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

2473 # subsequent occurrences of the column so that the

2474 # original stays non-ambiguous

2475 if table_qualified:

2476 required_label_name = fallback_label_name = (

2477 c._dedupe_anon_tq_label_idx(dedupe_hash)

2478 )

2479 dedupe_hash += 1

2480 else:

2481 required_label_name = fallback_label_name = (

2482 c._dedupe_anon_label_idx(dedupe_hash)

2483 )

2484 dedupe_hash += 1

2485 repeated = True

2486 else:

2487 names[required_label_name] = c

2488 elif anon_for_dupe_key:

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

2490 # label so that the original stays non-ambiguous

2491 if table_qualified:

2492 required_label_name = fallback_label_name = (

2493 c._dedupe_anon_tq_label_idx(dedupe_hash)

2494 )

2495 dedupe_hash += 1

2496 else:

2497 required_label_name = fallback_label_name = (

2498 c._dedupe_anon_label_idx(dedupe_hash)

2499 )

2500 dedupe_hash += 1

2501 repeated = True

2502 else:

2503 names[effective_name] = c

2504

2505 result_append(

2506 _ColumnsPlusNames(

2507 required_label_name,

2508 key_naming_convention(c),

2509 fallback_label_name,

2510 c,

2511 repeated,

2512 )

2513 )

2514

2515 return result

2516

2517

2518class HasCTE(roles.HasCTERole, SelectsRows):

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

2520

2521 _has_ctes_traverse_internals: _TraverseInternalsType = [

2522 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2523 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2524 ]

2525

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

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

2528

2529 name_cte_columns: bool = False

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

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

2532

2533 .. versionadded:: 2.0.42

2534

2535 """

2536

2537 @_generative

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

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

2540

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

2542 the parent statement such that they will each be unconditionally

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

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

2545

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

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

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

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

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

2551 statement.

2552

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

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

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

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

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

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

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

2560 larger statement.

2561

2562 E.g.::

2563

2564 from sqlalchemy import table, column, select

2565

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

2567

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

2569

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

2571

2572 Would render:

2573

2574 .. sourcecode:: sql

2575

2576 WITH anon_1 AS (

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

2578 )

2579 SELECT t.c1, t.c2

2580 FROM t

2581

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

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

2584 statement.

2585

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

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

2588

2589 from sqlalchemy import table, column

2590 from sqlalchemy.dialects.postgresql import insert

2591

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

2593

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

2595

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

2597 update_statement = insert_stmt.on_conflict_do_update(

2598 index_elements=[t.c.c1],

2599 set_={

2600 "c1": insert_stmt.excluded.c1,

2601 "c2": insert_stmt.excluded.c2,

2602 },

2603 ).add_cte(delete_statement_cte)

2604

2605 print(update_statement)

2606

2607 The above statement renders as:

2608

2609 .. sourcecode:: sql

2610

2611 WITH deletions AS (

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

2613 )

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

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

2616

2617 .. versionadded:: 1.4.21

2618

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

2620

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

2622

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

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

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

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

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

2628 this statement when this flag is given.

2629

2630 .. versionadded:: 2.0

2631

2632 .. seealso::

2633

2634 :paramref:`.HasCTE.cte.nesting`

2635

2636

2637 """ # noqa: E501

2638 opt = _CTEOpts(nest_here)

2639 for cte in ctes:

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

2641 self._independent_ctes += (cte,)

2642 self._independent_ctes_opts += (opt,)

2643 return self

2644

2645 def cte(

2646 self,

2647 name: Optional[str] = None,

2648 recursive: bool = False,

2649 nesting: bool = False,

2650 ) -> CTE:

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

2652 or Common Table Expression instance.

2653

2654 Common table expressions are a SQL standard whereby SELECT

2655 statements can draw upon secondary statements specified along

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

2657 Special semantics regarding UNION can also be employed to

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

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

2660

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

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

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

2664 CTE rows.

2665

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

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

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

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

2670

2671 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2673 method may be

2674 used to establish these.

2675

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

2677 In particular - MATERIALIZED and NOT MATERIALIZED.

2678

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

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

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

2682 compile time.

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

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

2685 conjunction with UNION ALL in order to derive rows

2686 from those already selected.

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

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

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

2690 :paramref:`.HasCTE.add_cte.nest_here`

2691 parameter may also be used to more carefully

2692 control the exact placement of a particular CTE.

2693

2694 .. versionadded:: 1.4.24

2695

2696 .. seealso::

2697

2698 :meth:`.HasCTE.add_cte`

2699

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

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

2702 as well as additional examples.

2703

2704 Example 1, non recursive::

2705

2706 from sqlalchemy import (

2707 Table,

2708 Column,

2709 String,

2710 Integer,

2711 MetaData,

2712 select,

2713 func,

2714 )

2715

2716 metadata = MetaData()

2717

2718 orders = Table(

2719 "orders",

2720 metadata,

2721 Column("region", String),

2722 Column("amount", Integer),

2723 Column("product", String),

2724 Column("quantity", Integer),

2725 )

2726

2727 regional_sales = (

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

2729 .group_by(orders.c.region)

2730 .cte("regional_sales")

2731 )

2732

2733

2734 top_regions = (

2735 select(regional_sales.c.region)

2736 .where(

2737 regional_sales.c.total_sales

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

2739 )

2740 .cte("top_regions")

2741 )

2742

2743 statement = (

2744 select(

2745 orders.c.region,

2746 orders.c.product,

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

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

2749 )

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

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

2752 )

2753

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

2755

2756 Example 2, WITH RECURSIVE::

2757

2758 from sqlalchemy import (

2759 Table,

2760 Column,

2761 String,

2762 Integer,

2763 MetaData,

2764 select,

2765 func,

2766 )

2767

2768 metadata = MetaData()

2769

2770 parts = Table(

2771 "parts",

2772 metadata,

2773 Column("part", String),

2774 Column("sub_part", String),

2775 Column("quantity", Integer),

2776 )

2777

2778 included_parts = (

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

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

2781 .cte(recursive=True)

2782 )

2783

2784

2785 incl_alias = included_parts.alias()

2786 parts_alias = parts.alias()

2787 included_parts = included_parts.union_all(

2788 select(

2789 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2791 )

2792

2793 statement = select(

2794 included_parts.c.sub_part,

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

2796 ).group_by(included_parts.c.sub_part)

2797

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

2799

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

2801

2802 from datetime import date

2803 from sqlalchemy import (

2804 MetaData,

2805 Table,

2806 Column,

2807 Integer,

2808 Date,

2809 select,

2810 literal,

2811 and_,

2812 exists,

2813 )

2814

2815 metadata = MetaData()

2816

2817 visitors = Table(

2818 "visitors",

2819 metadata,

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

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

2822 Column("count", Integer),

2823 )

2824

2825 # add 5 visitors for the product_id == 1

2826 product_id = 1

2827 day = date.today()

2828 count = 5

2829

2830 update_cte = (

2831 visitors.update()

2832 .where(

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

2834 )

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

2836 .returning(literal(1))

2837 .cte("update_cte")

2838 )

2839

2840 upsert = visitors.insert().from_select(

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

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

2843 ~exists(update_cte.select())

2844 ),

2845 )

2846

2847 connection.execute(upsert)

2848

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

2850

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

2852

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

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

2855 "value_a", nesting=True

2856 )

2857

2858 # Nesting CTEs takes ascendency locally

2859 # over the CTEs at a higher level

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

2861

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

2863

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

2865 shown with inline parameters below as:

2866

2867 .. sourcecode:: sql

2868

2869 WITH

2870 value_a AS

2871 (SELECT 'root' AS n),

2872 value_b AS

2873 (WITH value_a AS

2874 (SELECT 'nesting' AS n)

2875 SELECT value_a.n AS n FROM value_a)

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

2877 FROM value_a, value_b

2878

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

2880 as follows (SQLAlchemy 2.0 and above)::

2881

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

2883

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

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

2886

2887 # Nesting CTEs takes ascendency locally

2888 # over the CTEs at a higher level

2889 value_b = (

2890 select(value_a_nested.c.n)

2891 .add_cte(value_a_nested, nest_here=True)

2892 .cte("value_b")

2893 )

2894

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

2896

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

2898

2899 edge = Table(

2900 "edge",

2901 metadata,

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

2903 Column("left", Integer),

2904 Column("right", Integer),

2905 )

2906

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

2908

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

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

2911 )

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

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

2914 )

2915

2916 subgraph_cte = root_node.union(left_edge, right_edge)

2917

2918 subgraph = select(subgraph_cte)

2919

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

2921

2922 .. sourcecode:: sql

2923

2924 WITH RECURSIVE nodes(node) AS (

2925 SELECT 1 AS node

2926 UNION

2927 SELECT edge."left" AS "left"

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

2929 UNION

2930 SELECT edge."right" AS "right"

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

2932 )

2933 SELECT nodes.node FROM nodes

2934

2935 .. seealso::

2936

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

2938 :meth:`_expression.HasCTE.cte`.

2939

2940 """ # noqa: E501

2941 return CTE._construct(

2942 self, name=name, recursive=recursive, nesting=nesting

2943 )

2944

2945

2946class Subquery(AliasedReturnsRows):

2947 """Represent a subquery of a SELECT.

2948

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

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

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

2952 :class:`_expression.SelectBase` subclass

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

2954 :class:`_expression.CompoundSelect`, and

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

2956 it represents the

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

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

2959

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

2961 :class:`_expression.Alias`

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

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

2964 :class:`_expression.Alias` always

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

2966 :class:`.Subquery`

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

2968

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

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

2971 statement.

2972

2973 """

2974

2975 __visit_name__ = "subquery"

2976

2977 _is_subquery = True

2978

2979 inherit_cache = True

2980

2981 element: SelectBase

2982

2983 @classmethod

2984 def _factory(

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

2986 ) -> Subquery:

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

2988

2989 return coercions.expect(

2990 roles.SelectStatementRole, selectable

2991 ).subquery(name=name)

2992

2993 @util.deprecated(

2994 "1.4",

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

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

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

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

2999 ":func:`_expression.select` "

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

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

3002 )

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

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

3005

3006

3007class FromGrouping(GroupedElement, FromClause):

3008 """Represent a grouping of a FROM clause"""

3009

3010 _traverse_internals: _TraverseInternalsType = [

3011 ("element", InternalTraversal.dp_clauseelement)

3012 ]

3013

3014 element: FromClause

3015

3016 def __init__(self, element: FromClause):

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

3018

3019 @util.ro_non_memoized_property

3020 def columns(

3021 self,

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

3023 return self.element.columns

3024

3025 @util.ro_non_memoized_property

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

3027 return self.element.columns

3028

3029 @property

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

3031 return self.element.primary_key

3032

3033 @property

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

3035 return self.element.foreign_keys

3036

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

3038 return self.element.is_derived_from(fromclause)

3039

3040 def alias(

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

3042 ) -> NamedFromGrouping:

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

3044

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

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

3047

3048 @util.ro_non_memoized_property

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

3050 return self.element._hide_froms

3051

3052 @util.ro_non_memoized_property

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

3054 return self.element._from_objects

3055

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

3057 return {"element": self.element}

3058

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

3060 self.element = state["element"]

3061

3062 if TYPE_CHECKING:

3063

3064 def self_group(

3065 self, against: Optional[OperatorType] = None

3066 ) -> Self: ...

3067

3068

3069class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3071

3072 .. versionadded:: 2.0

3073

3074 """

3075

3076 inherit_cache = True

3077

3078 if TYPE_CHECKING:

3079

3080 def self_group(

3081 self, against: Optional[OperatorType] = None

3082 ) -> Self: ...

3083

3084

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

3086 """Represents a minimal "table" construct.

3087

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

3089 collection of columns, which are typically produced

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

3091

3092 from sqlalchemy import table, column

3093

3094 user = table(

3095 "user",

3096 column("id"),

3097 column("name"),

3098 column("description"),

3099 )

3100

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

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

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

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

3105

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

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

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

3109 It's useful

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

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

3112 is not on hand.

3113

3114 """

3115

3116 __visit_name__ = "table"

3117

3118 _traverse_internals: _TraverseInternalsType = [

3119 (

3120 "columns",

3121 InternalTraversal.dp_fromclause_canonical_column_collection,

3122 ),

3123 ("name", InternalTraversal.dp_string),

3124 ("schema", InternalTraversal.dp_string),

3125 ]

3126

3127 _is_table = True

3128

3129 fullname: str

3130

3131 implicit_returning = False

3132 """:class:`_expression.TableClause`

3133 doesn't support having a primary key or column

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

3135

3136 @util.ro_memoized_property

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

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

3139 return None

3140

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

3142 super().__init__()

3143 self.name = name

3144 self._columns = DedupeColumnCollection()

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

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

3147 for c in columns:

3148 self.append_column(c)

3149

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

3151 if schema is not None:

3152 self.schema = schema

3153 if self.schema is not None:

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

3155 else:

3156 self.fullname = self.name

3157 if kw:

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

3159

3160 if TYPE_CHECKING:

3161

3162 @util.ro_non_memoized_property

3163 def columns(

3164 self,

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

3166

3167 @util.ro_non_memoized_property

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

3169

3170 def __str__(self) -> str:

3171 if self.schema is not None:

3172 return self.schema + "." + self.name

3173 else:

3174 return self.name

3175

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

3177 pass

3178

3179 @util.ro_memoized_property

3180 def description(self) -> str:

3181 return self.name

3182

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

3184 existing = c.table

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

3186 raise exc.ArgumentError(

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

3188 % (c.key, existing)

3189 )

3190

3191 self._columns.add(c)

3192 c.table = self

3193

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

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

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

3197 :class:`_expression.TableClause`.

3198

3199 E.g.::

3200

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

3202

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

3204

3205 """

3206

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

3208

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

3210 def update(self) -> Update:

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

3212 :class:`_expression.TableClause`.

3213

3214 E.g.::

3215

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

3217

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

3219

3220 """

3221 return util.preloaded.sql_dml.Update(

3222 self,

3223 )

3224

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

3226 def delete(self) -> Delete:

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

3228 :class:`_expression.TableClause`.

3229

3230 E.g.::

3231

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

3233

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

3235

3236 """

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

3238

3239 @util.ro_non_memoized_property

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

3241 return [self]

3242

3243

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

3245

3246

3247class ForUpdateArg(ClauseElement):

3248 _traverse_internals: _TraverseInternalsType = [

3249 ("of", InternalTraversal.dp_clauseelement_list),

3250 ("nowait", InternalTraversal.dp_boolean),

3251 ("read", InternalTraversal.dp_boolean),

3252 ("skip_locked", InternalTraversal.dp_boolean),

3253 ("key_share", InternalTraversal.dp_boolean),

3254 ]

3255

3256 of: Optional[Sequence[ClauseElement]]

3257 nowait: bool

3258 read: bool

3259 skip_locked: bool

3260

3261 @classmethod

3262 def _from_argument(

3263 cls, with_for_update: ForUpdateParameter

3264 ) -> Optional[ForUpdateArg]:

3265 if isinstance(with_for_update, ForUpdateArg):

3266 return with_for_update

3267 elif with_for_update in (None, False):

3268 return None

3269 elif with_for_update is True:

3270 return ForUpdateArg()

3271 else:

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

3273

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

3275 return (

3276 isinstance(other, ForUpdateArg)

3277 and other.nowait == self.nowait

3278 and other.read == self.read

3279 and other.skip_locked == self.skip_locked

3280 and other.key_share == self.key_share

3281 and other.of is self.of

3282 )

3283

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

3285 return not self.__eq__(other)

3286

3287 def __hash__(self) -> int:

3288 return id(self)

3289

3290 def __init__(

3291 self,

3292 *,

3293 nowait: bool = False,

3294 read: bool = False,

3295 of: Optional[_ForUpdateOfArgument] = None,

3296 skip_locked: bool = False,

3297 key_share: bool = False,

3298 ):

3299 """Represents arguments specified to

3300 :meth:`_expression.Select.for_update`.

3301

3302 """

3303

3304 self.nowait = nowait

3305 self.read = read

3306 self.skip_locked = skip_locked

3307 self.key_share = key_share

3308 if of is not None:

3309 self.of = [

3310 coercions.expect(roles.ColumnsClauseRole, elem)

3311 for elem in util.to_list(of)

3312 ]

3313 else:

3314 self.of = None

3315

3316

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

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

3319 in a statement.

3320

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

3322 :func:`_expression.values` function.

3323

3324 .. versionadded:: 1.4

3325

3326 """

3327

3328 __visit_name__ = "values"

3329

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

3331

3332 _unnamed: bool

3333 _traverse_internals: _TraverseInternalsType = [

3334 ("_column_args", InternalTraversal.dp_clauseelement_list),

3335 ("_data", InternalTraversal.dp_dml_multi_values),

3336 ("name", InternalTraversal.dp_string),

3337 ("literal_binds", InternalTraversal.dp_boolean),

3338 ] + HasCTE._has_ctes_traverse_internals

3339

3340 name_cte_columns = True

3341

3342 def __init__(

3343 self,

3344 *columns: ColumnClause[Any],

3345 name: Optional[str] = None,

3346 literal_binds: bool = False,

3347 ):

3348 super().__init__()

3349 self._column_args = columns

3350

3351 if name is None:

3352 self._unnamed = True

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

3354 else:

3355 self._unnamed = False

3356 self.name = name

3357 self.literal_binds = literal_binds

3358 self.named_with_column = not self._unnamed

3359

3360 @property

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

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

3363

3364 @util.ro_non_memoized_property

3365 def _all_selected_columns(self) -> _SelectIterable:

3366 return self._column_args

3367

3368 @_generative

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

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

3371 construct that is a copy of this

3372 one with the given name.

3373

3374 This method is a VALUES-specific specialization of the

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

3376

3377 .. seealso::

3378

3379 :ref:`tutorial_using_aliases`

3380

3381 :func:`_expression.alias`

3382

3383 """

3384 non_none_name: str

3385

3386 if name is None:

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

3388 else:

3389 non_none_name = name

3390

3391 self.name = non_none_name

3392 self.named_with_column = True

3393 self._unnamed = False

3394 return self

3395

3396 @_generative

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

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

3399 so that

3400 it renders as LATERAL.

3401

3402 .. seealso::

3403

3404 :func:`_expression.lateral`

3405

3406 """

3407 non_none_name: str

3408

3409 if name is None:

3410 non_none_name = self.name

3411 else:

3412 non_none_name = name

3413

3414 self._is_lateral = True

3415 self.name = non_none_name

3416 self._unnamed = False

3417 return self

3418

3419 @_generative

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

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

3422 adding the given data to the data list.

3423

3424 E.g.::

3425

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

3427

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

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

3430 constructor.

3431

3432 """

3433

3434 self._data += (values,)

3435 return self

3436

3437 def scalar_values(self) -> ScalarValues:

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

3439 COLUMN element in a statement.

3440

3441 .. versionadded:: 2.0.0b4

3442

3443 """

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

3445

3446 def _populate_column_collection(

3447 self,

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

3449 primary_key: ColumnSet,

3450 foreign_keys: Set[KeyedColumnElement[Any]],

3451 ) -> None:

3452 for c in self._column_args:

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

3454 _, c = c._make_proxy(

3455 self, primary_key=primary_key, foreign_keys=foreign_keys

3456 )

3457 else:

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

3459 # no memoizations of other FROM clauses.

3460 # see test_values.py -> test_auto_proxy_select_direct_col

3461 c._reset_memoizations()

3462 columns.add(c)

3463 c.table = self

3464

3465 @util.ro_non_memoized_property

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

3467 return [self]

3468

3469

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

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

3472 COLUMN element in a statement.

3473

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

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

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

3477 an ``IN`` or ``NOT IN`` condition.

3478

3479 .. versionadded:: 2.0.0b4

3480

3481 """

3482

3483 __visit_name__ = "scalar_values"

3484

3485 _traverse_internals: _TraverseInternalsType = [

3486 ("_column_args", InternalTraversal.dp_clauseelement_list),

3487 ("_data", InternalTraversal.dp_dml_multi_values),

3488 ("literal_binds", InternalTraversal.dp_boolean),

3489 ]

3490

3491 def __init__(

3492 self,

3493 columns: Sequence[ColumnClause[Any]],

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

3495 literal_binds: bool,

3496 ):

3497 super().__init__()

3498 self._column_args = columns

3499 self._data = data

3500 self.literal_binds = literal_binds

3501

3502 @property

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

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

3505

3506 def __clause_element__(self) -> ScalarValues:

3507 return self

3508

3509 if TYPE_CHECKING:

3510

3511 def self_group(

3512 self, against: Optional[OperatorType] = None

3513 ) -> Self: ...

3514

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

3516

3517

3518class SelectBase(

3519 roles.SelectStatementRole,

3520 roles.DMLSelectRole,

3521 roles.CompoundElementRole,

3522 roles.InElementRole,

3523 HasCTE,

3524 SupportsCloneAnnotations,

3525 Selectable,

3526):

3527 """Base class for SELECT statements.

3528

3529

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

3531 :class:`_expression.CompoundSelect` and

3532 :class:`_expression.TextualSelect`.

3533

3534

3535 """

3536

3537 _is_select_base = True

3538 is_select = True

3539

3540 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3541

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

3543 self._reset_memoizations()

3544

3545 @util.ro_non_memoized_property

3546 def selected_columns(

3547 self,

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

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

3550 representing the columns that

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

3552

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

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

3555 within this collection cannot be directly nested inside another SELECT

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

3557 necessary parenthesization required by SQL.

3558

3559 .. note::

3560

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

3562 include expressions established in the columns clause using the

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

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

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

3566 construct.

3567

3568 .. seealso::

3569

3570 :attr:`_sql.Select.selected_columns`

3571

3572 .. versionadded:: 1.4

3573

3574 """

3575 raise NotImplementedError()

3576

3577 def _generate_fromclause_column_proxies(

3578 self,

3579 subquery: FromClause,

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

3581 primary_key: ColumnSet,

3582 foreign_keys: Set[KeyedColumnElement[Any]],

3583 *,

3584 proxy_compound_columns: Optional[

3585 Iterable[Sequence[ColumnElement[Any]]]

3586 ] = None,

3587 ) -> None:

3588 raise NotImplementedError()

3589

3590 @util.ro_non_memoized_property

3591 def _all_selected_columns(self) -> _SelectIterable:

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

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

3594 constructs.

3595

3596 .. versionadded:: 1.4.12

3597

3598 .. seealso::

3599

3600 :attr:`_sql.SelectBase.exported_columns`

3601

3602 """

3603 raise NotImplementedError()

3604

3605 @property

3606 def exported_columns(

3607 self,

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

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

3610 that represents the "exported"

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

3612 :class:`_sql.TextClause` constructs.

3613

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

3615 object are synonymous

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

3617

3618 .. versionadded:: 1.4

3619

3620 .. seealso::

3621

3622 :attr:`_expression.Select.exported_columns`

3623

3624 :attr:`_expression.Selectable.exported_columns`

3625

3626 :attr:`_expression.FromClause.exported_columns`

3627

3628

3629 """

3630 return self.selected_columns.as_readonly()

3631

3632 @property

3633 @util.deprecated(

3634 "1.4",

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

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

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

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

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

3640 "first in order to create "

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

3642 "columns that this SELECT object SELECTs "

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

3644 "attribute.",

3645 )

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

3647 return self._implicit_subquery.columns

3648

3649 @property

3650 def columns(

3651 self,

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

3653 return self.c

3654

3655 def get_label_style(self) -> SelectLabelStyle:

3656 """

3657 Retrieve the current label style.

3658

3659 Implemented by subclasses.

3660

3661 """

3662 raise NotImplementedError()

3663

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

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

3666

3667 Implemented by subclasses.

3668

3669 """

3670

3671 raise NotImplementedError()

3672

3673 @util.deprecated(

3674 "1.4",

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

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

3677 "creates a subquery that should be explicit. "

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

3679 "first in order to create "

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

3681 )

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

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

3684

3685 @HasMemoized.memoized_attribute

3686 def _implicit_subquery(self) -> Subquery:

3687 return self.subquery()

3688

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

3690 raise NotImplementedError()

3691

3692 @util.deprecated(

3693 "1.4",

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

3695 "method is deprecated and will be "

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

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

3698 )

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

3700 return self.scalar_subquery()

3701

3702 def exists(self) -> Exists:

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

3704 which can be used as a column expression.

3705

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

3707

3708 .. seealso::

3709

3710 :func:`_sql.exists`

3711

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

3713

3714 .. versionadded:: 1.4

3715

3716 """

3717 return Exists(self)

3718

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

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

3721 used as a column expression.

3722

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

3724

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

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

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

3728 an enclosing SELECT.

3729

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

3731 subquery that can be produced using the

3732 :meth:`_expression.SelectBase.subquery`

3733 method.

3734

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

3736 :meth:`_expression.SelectBase.scalar_subquery`.

3737

3738 .. seealso::

3739

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

3741

3742 """

3743 if self._label_style is not LABEL_STYLE_NONE:

3744 self = self.set_label_style(LABEL_STYLE_NONE)

3745

3746 return ScalarSelect(self)

3747

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

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

3750 subquery with a label.

3751

3752 .. seealso::

3753

3754 :meth:`_expression.SelectBase.scalar_subquery`.

3755

3756 """

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

3758

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

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

3761

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

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

3764

3765 .. seealso::

3766

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

3768

3769 """

3770 return Lateral._factory(self, name)

3771

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

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

3774

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

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

3777 SELECT statement.

3778

3779 Given a SELECT statement such as::

3780

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

3782

3783 The above statement might look like:

3784

3785 .. sourcecode:: sql

3786

3787 SELECT table.id, table.name FROM table

3788

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

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

3791 a named sub-element::

3792

3793 subq = stmt.subquery()

3794 new_stmt = select(subq)

3795

3796 The above renders as:

3797

3798 .. sourcecode:: sql

3799

3800 SELECT anon_1.id, anon_1.name

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

3802

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

3804 is equivalent to calling

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

3806 method on a FROM object; however,

3807 as a :class:`_expression.SelectBase`

3808 object is not directly FROM object,

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

3810 method provides clearer semantics.

3811

3812 .. versionadded:: 1.4

3813

3814 """

3815

3816 return Subquery._construct(

3817 self._ensure_disambiguated_names(), name=name

3818 )

3819

3820 def _ensure_disambiguated_names(self) -> Self:

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

3822 disambiguated in some way, if possible.

3823

3824 """

3825

3826 raise NotImplementedError()

3827

3828 def alias(

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

3830 ) -> Subquery:

3831 """Return a named subquery against this

3832 :class:`_expression.SelectBase`.

3833

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

3835 :class:`_expression.FromClause`),

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

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

3838 :class:`_expression.FromClause`.

3839

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

3841 method is now

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

3843

3844 """

3845 return self.subquery(name=name)

3846

3847

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

3849

3850

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

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

3853

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

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

3856 compound selects.

3857

3858 """

3859

3860 __visit_name__ = "select_statement_grouping"

3861 _traverse_internals: _TraverseInternalsType = [

3862 ("element", InternalTraversal.dp_clauseelement)

3863 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3864

3865 _is_select_container = True

3866

3867 element: _SB

3868

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

3870 self.element = cast(

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

3872 )

3873

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

3875 new_element = self.element._ensure_disambiguated_names()

3876 if new_element is not self.element:

3877 return SelectStatementGrouping(new_element)

3878 else:

3879 return self

3880

3881 def get_label_style(self) -> SelectLabelStyle:

3882 return self.element.get_label_style()

3883

3884 def set_label_style(

3885 self, label_style: SelectLabelStyle

3886 ) -> SelectStatementGrouping[_SB]:

3887 return SelectStatementGrouping(

3888 self.element.set_label_style(label_style)

3889 )

3890

3891 @property

3892 def select_statement(self) -> _SB:

3893 return self.element

3894

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

3896 return self

3897

3898 if TYPE_CHECKING:

3899

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

3901

3902 # def _generate_columns_plus_names(

3903 # self, anon_for_dupe_key: bool

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

3905 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3906

3907 def _generate_fromclause_column_proxies(

3908 self,

3909 subquery: FromClause,

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

3911 primary_key: ColumnSet,

3912 foreign_keys: Set[KeyedColumnElement[Any]],

3913 *,

3914 proxy_compound_columns: Optional[

3915 Iterable[Sequence[ColumnElement[Any]]]

3916 ] = None,

3917 ) -> None:

3918 self.element._generate_fromclause_column_proxies(

3919 subquery,

3920 columns,

3921 proxy_compound_columns=proxy_compound_columns,

3922 primary_key=primary_key,

3923 foreign_keys=foreign_keys,

3924 )

3925

3926 @util.ro_non_memoized_property

3927 def _all_selected_columns(self) -> _SelectIterable:

3928 return self.element._all_selected_columns

3929

3930 @util.ro_non_memoized_property

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

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

3933 representing the columns that

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

3935 :class:`_sql.TextClause` constructs.

3936

3937 .. versionadded:: 1.4

3938

3939 .. seealso::

3940

3941 :attr:`_sql.Select.selected_columns`

3942

3943 """

3944 return self.element.selected_columns

3945

3946 @util.ro_non_memoized_property

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

3948 return self.element._from_objects

3949

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

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

3952 raise NotImplementedError

3953

3954

3955class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3957 added.

3958

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

3960 :class:`_expression.CompoundSelect`

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

3962 rendering can be controlled. Compare to

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

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

3965 and is also a SELECT construct,

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

3967 only wrapped as a subquery.

3968

3969 """

3970

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

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

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

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

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

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

3977 _for_update_arg: Optional[ForUpdateArg] = None

3978

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

3980 self._label_style = _label_style

3981

3982 @_generative

3983 def with_for_update(

3984 self,

3985 *,

3986 nowait: bool = False,

3987 read: bool = False,

3988 of: Optional[_ForUpdateOfArgument] = None,

3989 skip_locked: bool = False,

3990 key_share: bool = False,

3991 ) -> Self:

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

3993 :class:`_expression.GenerativeSelect`.

3994

3995 E.g.::

3996

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

3998

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

4000 render a statement like:

4001

4002 .. sourcecode:: sql

4003

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

4005

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

4007 would produce:

4008

4009 .. sourcecode:: sql

4010

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

4012

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

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

4015 provided which allow for common database-specific

4016 variants.

4017

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

4019 Database and PostgreSQL dialects.

4020

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

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

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

4024

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

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

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

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

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

4030 table or as a column depending on backend.

4031

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

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

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

4035

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

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

4038 on the PostgreSQL dialect.

4039

4040 """

4041 self._for_update_arg = ForUpdateArg(

4042 nowait=nowait,

4043 read=read,

4044 of=of,

4045 skip_locked=skip_locked,

4046 key_share=key_share,

4047 )

4048 return self

4049

4050 def get_label_style(self) -> SelectLabelStyle:

4051 """

4052 Retrieve the current label style.

4053

4054 .. versionadded:: 1.4

4055

4056 """

4057 return self._label_style

4058

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

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

4061

4062 There are three "label styles" available,

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

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

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

4066 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4067

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

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

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

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

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

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

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

4075 that the impact of this labeling is minimal.

4076

4077 The rationale for disambiguation is mostly so that all column

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

4079 collection when a subquery is created.

4080

4081 .. versionadded:: 1.4 - the

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

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

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

4085

4086 .. seealso::

4087

4088 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4089

4090 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4091

4092 :data:`_sql.LABEL_STYLE_NONE`

4093

4094 :data:`_sql.LABEL_STYLE_DEFAULT`

4095

4096 """

4097 if self._label_style is not style:

4098 self = self._generate()

4099 self._label_style = style

4100 return self

4101

4102 @property

4103 def _group_by_clause(self) -> ClauseList:

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

4105 return ClauseList._construct_raw(

4106 operators.comma_op, self._group_by_clauses

4107 )

4108

4109 @property

4110 def _order_by_clause(self) -> ClauseList:

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

4112 return ClauseList._construct_raw(

4113 operators.comma_op, self._order_by_clauses

4114 )

4115

4116 def _offset_or_limit_clause(

4117 self,

4118 element: _LimitOffsetType,

4119 name: Optional[str] = None,

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

4121 ) -> ColumnElement[Any]:

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

4123

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

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

4126

4127 """

4128 return coercions.expect(

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

4130 )

4131

4132 @overload

4133 def _offset_or_limit_clause_asint(

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

4135 ) -> NoReturn: ...

4136

4137 @overload

4138 def _offset_or_limit_clause_asint(

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

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

4141

4142 def _offset_or_limit_clause_asint(

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

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

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

4146 integer.

4147

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

4149 parameter. Otherwise, a compilation error is raised.

4150

4151 """

4152 if clause is None:

4153 return None

4154 try:

4155 value = clause._limit_offset_value

4156 except AttributeError as err:

4157 raise exc.CompileError(

4158 "This SELECT structure does not use a simple "

4159 "integer value for %s" % attrname

4160 ) from err

4161 else:

4162 return util.asint(value)

4163

4164 @property

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

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

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

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

4169 isn't currently set to an integer.

4170

4171 """

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

4173

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

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

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

4177 """

4178 return isinstance(clause, _OffsetLimitParam)

4179

4180 @property

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

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

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

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

4185 offset isn't currently set to an integer.

4186

4187 """

4188 return self._offset_or_limit_clause_asint(

4189 self._offset_clause, "offset"

4190 )

4191

4192 @property

4193 def _has_row_limiting_clause(self) -> bool:

4194 return (

4195 self._limit_clause is not None

4196 or self._offset_clause is not None

4197 or self._fetch_clause is not None

4198 )

4199

4200 @_generative

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

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

4203 applied.

4204

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

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

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

4208 functionality.

4209

4210 .. note::

4211

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

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

4214

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

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

4217

4218 .. seealso::

4219

4220 :meth:`_sql.GenerativeSelect.fetch`

4221

4222 :meth:`_sql.GenerativeSelect.offset`

4223

4224 """

4225

4226 self._fetch_clause = self._fetch_clause_options = None

4227 self._limit_clause = self._offset_or_limit_clause(limit)

4228 return self

4229

4230 @_generative

4231 def fetch(

4232 self,

4233 count: _LimitOffsetType,

4234 with_ties: bool = False,

4235 percent: bool = False,

4236 **dialect_kw: Any,

4237 ) -> Self:

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

4239 applied.

4240

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

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

4243 select. This functionality is is currently implemented for Oracle

4244 Database, PostgreSQL, MSSQL.

4245

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

4247

4248 .. note::

4249

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

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

4252

4253 .. versionadded:: 1.4

4254

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

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

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

4258 Pass ``None`` to reset it.

4259

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

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

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

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

4264

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

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

4267

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

4269 may be accepted by dialects.

4270

4271 .. versionadded:: 2.0.41

4272

4273 .. seealso::

4274

4275 :meth:`_sql.GenerativeSelect.limit`

4276

4277 :meth:`_sql.GenerativeSelect.offset`

4278

4279 """

4280 self._validate_dialect_kwargs(dialect_kw)

4281 self._limit_clause = None

4282 if count is None:

4283 self._fetch_clause = self._fetch_clause_options = None

4284 else:

4285 self._fetch_clause = self._offset_or_limit_clause(count)

4286 self._fetch_clause_options = {

4287 "with_ties": with_ties,

4288 "percent": percent,

4289 }

4290 return self

4291

4292 @_generative

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

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

4295 applied.

4296

4297

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

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

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

4301 functionality.

4302

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

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

4305

4306 .. seealso::

4307

4308 :meth:`_sql.GenerativeSelect.limit`

4309

4310 :meth:`_sql.GenerativeSelect.fetch`

4311

4312 """

4313

4314 self._offset_clause = self._offset_or_limit_clause(offset)

4315 return self

4316

4317 @_generative

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

4319 def slice(

4320 self,

4321 start: int,

4322 stop: int,

4323 ) -> Self:

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

4325

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

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

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

4329 query.

4330

4331 For example, ::

4332

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

4334

4335 renders as

4336

4337 .. sourcecode:: sql

4338

4339 SELECT users.id AS users_id,

4340 users.name AS users_name

4341 FROM users ORDER BY users.id

4342 LIMIT ? OFFSET ?

4343 (2, 1)

4344

4345 .. note::

4346

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

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

4349

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

4351 method generalized from the ORM.

4352

4353 .. seealso::

4354

4355 :meth:`_sql.GenerativeSelect.limit`

4356

4357 :meth:`_sql.GenerativeSelect.offset`

4358

4359 :meth:`_sql.GenerativeSelect.fetch`

4360

4361 """

4362 sql_util = util.preloaded.sql_util

4363 self._fetch_clause = self._fetch_clause_options = None

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

4365 self._limit_clause, self._offset_clause, start, stop

4366 )

4367 return self

4368

4369 @_generative

4370 def order_by(

4371 self,

4372 __first: Union[

4373 Literal[None, _NoArg.NO_ARG],

4374 _ColumnExpressionOrStrLabelArgument[Any],

4375 ] = _NoArg.NO_ARG,

4376 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4377 ) -> Self:

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

4379 criteria applied.

4380

4381 e.g.::

4382

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

4384

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

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

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

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

4389

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

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

4392

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

4394 constructs

4395 which will be used to generate an ORDER BY clause.

4396

4397 .. seealso::

4398

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

4400

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

4402

4403 """

4404

4405 if not clauses and __first is None:

4406 self._order_by_clauses = ()

4407 elif __first is not _NoArg.NO_ARG:

4408 self._order_by_clauses += tuple(

4409 coercions.expect(

4410 roles.OrderByRole, clause, apply_propagate_attrs=self

4411 )

4412 for clause in (__first,) + clauses

4413 )

4414 return self

4415

4416 @_generative

4417 def group_by(

4418 self,

4419 __first: Union[

4420 Literal[None, _NoArg.NO_ARG],

4421 _ColumnExpressionOrStrLabelArgument[Any],

4422 ] = _NoArg.NO_ARG,

4423 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4424 ) -> Self:

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

4426 criterion applied.

4427

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

4429

4430 e.g.::

4431

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

4433

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

4435 constructs

4436 which will be used to generate an GROUP BY clause.

4437

4438 .. seealso::

4439

4440 :ref:`tutorial_group_by_w_aggregates` - in the

4441 :ref:`unified_tutorial`

4442

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

4444

4445 """ # noqa: E501

4446

4447 if not clauses and __first is None:

4448 self._group_by_clauses = ()

4449 elif __first is not _NoArg.NO_ARG:

4450 self._group_by_clauses += tuple(

4451 coercions.expect(

4452 roles.GroupByRole, clause, apply_propagate_attrs=self

4453 )

4454 for clause in (__first,) + clauses

4455 )

4456 return self

4457

4458

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

4460class CompoundSelectState(CompileState):

4461 @util.memoized_property

4462 def _label_resolve_dict(

4463 self,

4464 ) -> Tuple[

4465 Dict[str, ColumnElement[Any]],

4466 Dict[str, ColumnElement[Any]],

4467 Dict[str, ColumnElement[Any]],

4468 ]:

4469 # TODO: this is hacky and slow

4470 hacky_subquery = self.statement.subquery()

4471 hacky_subquery.named_with_column = False

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

4473 return d, d, d

4474

4475

4476class _CompoundSelectKeyword(Enum):

4477 UNION = "UNION"

4478 UNION_ALL = "UNION ALL"

4479 EXCEPT = "EXCEPT"

4480 EXCEPT_ALL = "EXCEPT ALL"

4481 INTERSECT = "INTERSECT"

4482 INTERSECT_ALL = "INTERSECT ALL"

4483

4484

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

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

4487 SELECT-based set operations.

4488

4489

4490 .. seealso::

4491

4492 :func:`_expression.union`

4493

4494 :func:`_expression.union_all`

4495

4496 :func:`_expression.intersect`

4497

4498 :func:`_expression.intersect_all`

4499

4500 :func:`_expression.except`

4501

4502 :func:`_expression.except_all`

4503

4504 """

4505

4506 __visit_name__ = "compound_select"

4507

4508 _traverse_internals: _TraverseInternalsType = (

4509 [

4510 ("selects", InternalTraversal.dp_clauseelement_list),

4511 ("_limit_clause", InternalTraversal.dp_clauseelement),

4512 ("_offset_clause", InternalTraversal.dp_clauseelement),

4513 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4514 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4515 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4516 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4517 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4518 ("keyword", InternalTraversal.dp_string),

4519 ]

4520 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4521 + HasCTE._has_ctes_traverse_internals

4522 + DialectKWArgs._dialect_kwargs_traverse_internals

4523 )

4524

4525 selects: List[SelectBase]

4526

4527 _is_from_container = True

4528 _auto_correlate = False

4529

4530 def __init__(

4531 self,

4532 keyword: _CompoundSelectKeyword,

4533 *selects: _SelectStatementForCompoundArgument[_TP],

4534 ):

4535 self.keyword = keyword

4536 self.selects = [

4537 coercions.expect(

4538 roles.CompoundElementRole, s, apply_propagate_attrs=self

4539 ).self_group(against=self)

4540 for s in selects

4541 ]

4542

4543 GenerativeSelect.__init__(self)

4544

4545 @classmethod

4546 def _create_union(

4547 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4548 ) -> CompoundSelect[_TP]:

4549 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4550

4551 @classmethod

4552 def _create_union_all(

4553 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4554 ) -> CompoundSelect[_TP]:

4555 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4556

4557 @classmethod

4558 def _create_except(

4559 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4560 ) -> CompoundSelect[_TP]:

4561 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4562

4563 @classmethod

4564 def _create_except_all(

4565 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4566 ) -> CompoundSelect[_TP]:

4567 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4568

4569 @classmethod

4570 def _create_intersect(

4571 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4572 ) -> CompoundSelect[_TP]:

4573 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4574

4575 @classmethod

4576 def _create_intersect_all(

4577 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4578 ) -> CompoundSelect[_TP]:

4579 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4580

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

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

4583

4584 def self_group(

4585 self, against: Optional[OperatorType] = None

4586 ) -> GroupedElement:

4587 return SelectStatementGrouping(self)

4588

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

4590 for s in self.selects:

4591 if s.is_derived_from(fromclause):

4592 return True

4593 return False

4594

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

4596 if self._label_style is not style:

4597 self = self._generate()

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

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

4600

4601 return self

4602

4603 def _ensure_disambiguated_names(self) -> Self:

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

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

4606 self = self._generate()

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

4608

4609 return self

4610

4611 def _generate_fromclause_column_proxies(

4612 self,

4613 subquery: FromClause,

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

4615 primary_key: ColumnSet,

4616 foreign_keys: Set[KeyedColumnElement[Any]],

4617 *,

4618 proxy_compound_columns: Optional[

4619 Iterable[Sequence[ColumnElement[Any]]]

4620 ] = None,

4621 ) -> None:

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

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

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

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

4626 # generate below.

4627 select_0 = self.selects[0]

4628

4629 if self._label_style is not LABEL_STYLE_DEFAULT:

4630 select_0 = select_0.set_label_style(self._label_style)

4631

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

4633 # derived columns place a 'weight' annotation corresponding

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

4635 # that the corresponding_column() operation can resolve

4636 # conflicts

4637 extra_col_iterator = zip(

4638 *[

4639 [

4640 c._annotate(dd)

4641 for c in stmt._all_selected_columns

4642 if is_column_element(c)

4643 ]

4644 for dd, stmt in [

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

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

4647 ]

4648 ]

4649 )

4650

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

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

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

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

4655 # compound

4656 select_0._generate_fromclause_column_proxies(

4657 subquery,

4658 columns,

4659 proxy_compound_columns=extra_col_iterator,

4660 primary_key=primary_key,

4661 foreign_keys=foreign_keys,

4662 )

4663

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

4665 super()._refresh_for_new_column(column)

4666 for select in self.selects:

4667 select._refresh_for_new_column(column)

4668

4669 @util.ro_non_memoized_property

4670 def _all_selected_columns(self) -> _SelectIterable:

4671 return self.selects[0]._all_selected_columns

4672

4673 @util.ro_non_memoized_property

4674 def selected_columns(

4675 self,

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

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

4678 representing the columns that

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

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

4681

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

4683 :attr:`_expression.CompoundSelect.selected_columns`

4684 attribute returns the selected

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

4686 statements within the set operation.

4687

4688 .. seealso::

4689

4690 :attr:`_sql.Select.selected_columns`

4691

4692 .. versionadded:: 1.4

4693

4694 """

4695 return self.selects[0].selected_columns

4696

4697

4698# backwards compat

4699for elem in _CompoundSelectKeyword:

4700 setattr(CompoundSelect, elem.name, elem)

4701

4702

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

4704class SelectState(util.MemoizedSlots, CompileState):

4705 __slots__ = (

4706 "from_clauses",

4707 "froms",

4708 "columns_plus_names",

4709 "_label_resolve_dict",

4710 )

4711

4712 if TYPE_CHECKING:

4713 default_select_compile_options: CacheableOptions

4714 else:

4715

4716 class default_select_compile_options(CacheableOptions):

4717 _cache_key_traversal = []

4718

4719 if TYPE_CHECKING:

4720

4721 @classmethod

4722 def get_plugin_class(

4723 cls, statement: Executable

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

4725

4726 def __init__(

4727 self,

4728 statement: Select[Any],

4729 compiler: SQLCompiler,

4730 **kw: Any,

4731 ):

4732 self.statement = statement

4733 self.from_clauses = statement._from_obj

4734

4735 for memoized_entities in statement._memoized_select_entities:

4736 self._setup_joins(

4737 memoized_entities._setup_joins, memoized_entities._raw_columns

4738 )

4739

4740 if statement._setup_joins:

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

4742

4743 self.froms = self._get_froms(statement)

4744

4745 self.columns_plus_names = statement._generate_columns_plus_names(True)

4746

4747 @classmethod

4748 def _plugin_not_implemented(cls) -> NoReturn:

4749 raise NotImplementedError(

4750 "The default SELECT construct without plugins does not "

4751 "implement this method."

4752 )

4753

4754 @classmethod

4755 def get_column_descriptions(

4756 cls, statement: Select[Any]

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

4758 return [

4759 {

4760 "name": name,

4761 "type": element.type,

4762 "expr": element,

4763 }

4764 for _, name, _, element, _ in (

4765 statement._generate_columns_plus_names(False)

4766 )

4767 ]

4768

4769 @classmethod

4770 def from_statement(

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

4772 ) -> ExecutableReturnsRows:

4773 cls._plugin_not_implemented()

4774

4775 @classmethod

4776 def get_columns_clause_froms(

4777 cls, statement: Select[Any]

4778 ) -> List[FromClause]:

4779 return cls._normalize_froms(

4780 itertools.chain.from_iterable(

4781 element._from_objects for element in statement._raw_columns

4782 )

4783 )

4784

4785 @classmethod

4786 def _column_naming_convention(

4787 cls, label_style: SelectLabelStyle

4788 ) -> _LabelConventionCallable:

4789 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4790

4791 dedupe = label_style is not LABEL_STYLE_NONE

4792

4793 pa = prefix_anon_map()

4794 names = set()

4795

4796 def go(

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

4798 col_name: Optional[str] = None,

4799 ) -> Optional[str]:

4800 if is_text_clause(c):

4801 return None

4802 elif TYPE_CHECKING:

4803 assert is_column_element(c)

4804

4805 if not dedupe:

4806 name = c._proxy_key

4807 if name is None:

4808 name = "_no_label"

4809 return name

4810

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

4812

4813 if name is None:

4814 name = "_no_label"

4815 if name in names:

4816 return c._anon_label(name) % pa

4817 else:

4818 names.add(name)

4819 return name

4820

4821 elif name in names:

4822 return (

4823 c._anon_tq_key_label % pa

4824 if table_qualified

4825 else c._anon_key_label % pa

4826 )

4827 else:

4828 names.add(name)

4829 return name

4830

4831 return go

4832

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

4834 ambiguous_table_name_map: _AmbiguousTableNameMap

4835 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4836

4837 return self._normalize_froms(

4838 itertools.chain(

4839 self.from_clauses,

4840 itertools.chain.from_iterable(

4841 [

4842 element._from_objects

4843 for element in statement._raw_columns

4844 ]

4845 ),

4846 itertools.chain.from_iterable(

4847 [

4848 element._from_objects

4849 for element in statement._where_criteria

4850 ]

4851 ),

4852 ),

4853 check_statement=statement,

4854 ambiguous_table_name_map=ambiguous_table_name_map,

4855 )

4856

4857 @classmethod

4858 def _normalize_froms(

4859 cls,

4860 iterable_of_froms: Iterable[FromClause],

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

4862 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4863 ) -> List[FromClause]:

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

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

4866

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

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

4869 etc.

4870

4871 """

4872 seen: Set[FromClause] = set()

4873 froms: List[FromClause] = []

4874

4875 for item in iterable_of_froms:

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

4877 raise exc.InvalidRequestError(

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

4879 )

4880

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

4882 froms.append(item)

4883 seen.update(item._cloned_set)

4884

4885 if froms:

4886 toremove = set(

4887 itertools.chain.from_iterable(

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

4889 )

4890 )

4891 if toremove:

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

4893 # using a list to maintain ordering

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

4895

4896 if ambiguous_table_name_map is not None:

4897 ambiguous_table_name_map.update(

4898 (

4899 fr.name,

4900 _anonymous_label.safe_construct(

4901 hash(fr.name), fr.name

4902 ),

4903 )

4904 for item in froms

4905 for fr in item._from_objects

4906 if is_table(fr)

4907 and fr.schema

4908 and fr.name not in ambiguous_table_name_map

4909 )

4910

4911 return froms

4912

4913 def _get_display_froms(

4914 self,

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

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

4917 ) -> List[FromClause]:

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

4919

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

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

4922 may want to leave those absent if it is automatically

4923 correlating.

4924

4925 """

4926

4927 froms = self.froms

4928

4929 if self.statement._correlate:

4930 to_correlate = self.statement._correlate

4931 if to_correlate:

4932 froms = [

4933 f

4934 for f in froms

4935 if f

4936 not in _cloned_intersection(

4937 _cloned_intersection(

4938 froms, explicit_correlate_froms or ()

4939 ),

4940 to_correlate,

4941 )

4942 ]

4943

4944 if self.statement._correlate_except is not None:

4945 froms = [

4946 f

4947 for f in froms

4948 if f

4949 not in _cloned_difference(

4950 _cloned_intersection(

4951 froms, explicit_correlate_froms or ()

4952 ),

4953 self.statement._correlate_except,

4954 )

4955 ]

4956

4957 if (

4958 self.statement._auto_correlate

4959 and implicit_correlate_froms

4960 and len(froms) > 1

4961 ):

4962 froms = [

4963 f

4964 for f in froms

4965 if f

4966 not in _cloned_intersection(froms, implicit_correlate_froms)

4967 ]

4968

4969 if not len(froms):

4970 raise exc.InvalidRequestError(

4971 "Select statement '%r"

4972 "' returned no FROM clauses "

4973 "due to auto-correlation; "

4974 "specify correlate(<tables>) "

4975 "to control correlation "

4976 "manually." % self.statement

4977 )

4978

4979 return froms

4980

4981 def _memoized_attr__label_resolve_dict(

4982 self,

4983 ) -> Tuple[

4984 Dict[str, ColumnElement[Any]],

4985 Dict[str, ColumnElement[Any]],

4986 Dict[str, ColumnElement[Any]],

4987 ]:

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

4989 c._tq_label or c.key: c

4990 for c in self.statement._all_selected_columns

4991 if c._allow_label_resolve

4992 }

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

4994 c.key: c # type: ignore

4995 for c in _select_iterables(self.froms)

4996 if c._allow_label_resolve

4997 }

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

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

5000 with_cols.setdefault(key, value)

5001

5002 return with_cols, only_froms, only_cols

5003

5004 @classmethod

5005 def determine_last_joined_entity(

5006 cls, stmt: Select[Any]

5007 ) -> Optional[_JoinTargetElement]:

5008 if stmt._setup_joins:

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

5010 else:

5011 return None

5012

5013 @classmethod

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

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

5016

5017 def _setup_joins(

5018 self,

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

5020 raw_columns: List[_ColumnsClauseElement],

5021 ) -> None:

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

5023 if TYPE_CHECKING:

5024 if onclause is not None:

5025 assert isinstance(onclause, ColumnElement)

5026

5027 isouter = flags["isouter"]

5028 full = flags["full"]

5029

5030 if left is None:

5031 (

5032 left,

5033 replace_from_obj_index,

5034 ) = self._join_determine_implicit_left_side(

5035 raw_columns, left, right, onclause

5036 )

5037 else:

5038 (replace_from_obj_index) = self._join_place_explicit_left_side(

5039 left

5040 )

5041

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

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

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

5045 # orm.context.ORMSelectCompileState._join() would be

5046 # used instead.

5047 if TYPE_CHECKING:

5048 assert isinstance(right, FromClause)

5049 if onclause is not None:

5050 assert isinstance(onclause, ColumnElement)

5051

5052 if replace_from_obj_index is not None:

5053 # splice into an existing element in the

5054 # self._from_obj list

5055 left_clause = self.from_clauses[replace_from_obj_index]

5056

5057 self.from_clauses = (

5058 self.from_clauses[:replace_from_obj_index]

5059 + (

5060 Join(

5061 left_clause,

5062 right,

5063 onclause,

5064 isouter=isouter,

5065 full=full,

5066 ),

5067 )

5068 + self.from_clauses[replace_from_obj_index + 1 :]

5069 )

5070 else:

5071 assert left is not None

5072 self.from_clauses = self.from_clauses + (

5073 Join(left, right, onclause, isouter=isouter, full=full),

5074 )

5075

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

5077 def _join_determine_implicit_left_side(

5078 self,

5079 raw_columns: List[_ColumnsClauseElement],

5080 left: Optional[FromClause],

5081 right: _JoinTargetElement,

5082 onclause: Optional[ColumnElement[Any]],

5083 ) -> Tuple[Optional[FromClause], Optional[int]]:

5084 """When join conditions don't express the left side explicitly,

5085 determine if an existing FROM or entity in this query

5086 can serve as the left hand side.

5087

5088 """

5089

5090 sql_util = util.preloaded.sql_util

5091

5092 replace_from_obj_index: Optional[int] = None

5093

5094 from_clauses = self.from_clauses

5095

5096 if from_clauses:

5097 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5098 from_clauses, right, onclause

5099 )

5100

5101 if len(indexes) == 1:

5102 replace_from_obj_index = indexes[0]

5103 left = from_clauses[replace_from_obj_index]

5104 else:

5105 potential = {}

5106 statement = self.statement

5107

5108 for from_clause in itertools.chain(

5109 itertools.chain.from_iterable(

5110 [element._from_objects for element in raw_columns]

5111 ),

5112 itertools.chain.from_iterable(

5113 [

5114 element._from_objects

5115 for element in statement._where_criteria

5116 ]

5117 ),

5118 ):

5119 potential[from_clause] = ()

5120

5121 all_clauses = list(potential.keys())

5122 indexes = sql_util.find_left_clause_to_join_from(

5123 all_clauses, right, onclause

5124 )

5125

5126 if len(indexes) == 1:

5127 left = all_clauses[indexes[0]]

5128

5129 if len(indexes) > 1:

5130 raise exc.InvalidRequestError(

5131 "Can't determine which FROM clause to join "

5132 "from, there are multiple FROMS which can "

5133 "join to this entity. Please use the .select_from() "

5134 "method to establish an explicit left side, as well as "

5135 "providing an explicit ON clause if not present already to "

5136 "help resolve the ambiguity."

5137 )

5138 elif not indexes:

5139 raise exc.InvalidRequestError(

5140 "Don't know how to join to %r. "

5141 "Please use the .select_from() "

5142 "method to establish an explicit left side, as well as "

5143 "providing an explicit ON clause if not present already to "

5144 "help resolve the ambiguity." % (right,)

5145 )

5146 return left, replace_from_obj_index

5147

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

5149 def _join_place_explicit_left_side(

5150 self, left: FromClause

5151 ) -> Optional[int]:

5152 replace_from_obj_index: Optional[int] = None

5153

5154 sql_util = util.preloaded.sql_util

5155

5156 from_clauses = list(self.statement._iterate_from_elements())

5157

5158 if from_clauses:

5159 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5160 self.from_clauses, left

5161 )

5162 else:

5163 indexes = []

5164

5165 if len(indexes) > 1:

5166 raise exc.InvalidRequestError(

5167 "Can't identify which entity in which to assign the "

5168 "left side of this join. Please use a more specific "

5169 "ON clause."

5170 )

5171

5172 # have an index, means the left side is already present in

5173 # an existing FROM in the self._from_obj tuple

5174 if indexes:

5175 replace_from_obj_index = indexes[0]

5176

5177 # no index, means we need to add a new element to the

5178 # self._from_obj tuple

5179

5180 return replace_from_obj_index

5181

5182

5183class _SelectFromElements:

5184 __slots__ = ()

5185

5186 _raw_columns: List[_ColumnsClauseElement]

5187 _where_criteria: Tuple[ColumnElement[Any], ...]

5188 _from_obj: Tuple[FromClause, ...]

5189

5190 def _iterate_from_elements(self) -> Iterator[FromClause]:

5191 # note this does not include elements

5192 # in _setup_joins

5193

5194 seen = set()

5195 for element in self._raw_columns:

5196 for fr in element._from_objects:

5197 if fr in seen:

5198 continue

5199 seen.add(fr)

5200 yield fr

5201 for element in self._where_criteria:

5202 for fr in element._from_objects:

5203 if fr in seen:

5204 continue

5205 seen.add(fr)

5206 yield fr

5207 for element in self._from_obj:

5208 if element in seen:

5209 continue

5210 seen.add(element)

5211 yield element

5212

5213

5214class _MemoizedSelectEntities(

5215 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5216):

5217 """represents partial state from a Select object, for the case

5218 where Select.columns() has redefined the set of columns/entities the

5219 statement will be SELECTing from. This object represents

5220 the entities from the SELECT before that transformation was applied,

5221 so that transformations that were made in terms of the SELECT at that

5222 time, such as join() as well as options(), can access the correct context.

5223

5224 In previous SQLAlchemy versions, this wasn't needed because these

5225 constructs calculated everything up front, like when you called join()

5226 or options(), it did everything to figure out how that would translate

5227 into specific SQL constructs that would be ready to send directly to the

5228 SQL compiler when needed. But as of

5229 1.4, all of that stuff is done in the compilation phase, during the

5230 "compile state" portion of the process, so that the work can all be

5231 cached. So it needs to be able to resolve joins/options2 based on what

5232 the list of entities was when those methods were called.

5233

5234

5235 """

5236

5237 __visit_name__ = "memoized_select_entities"

5238

5239 _traverse_internals: _TraverseInternalsType = [

5240 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5241 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5242 ("_with_options", InternalTraversal.dp_executable_options),

5243 ]

5244

5245 _is_clone_of: Optional[ClauseElement]

5246 _raw_columns: List[_ColumnsClauseElement]

5247 _setup_joins: Tuple[_SetupJoinsElement, ...]

5248 _with_options: Tuple[ExecutableOption, ...]

5249

5250 _annotations = util.EMPTY_DICT

5251

5252 def _clone(self, **kw: Any) -> Self:

5253 c = self.__class__.__new__(self.__class__)

5254 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5255

5256 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5257 return c

5258

5259 @classmethod

5260 def _generate_for_statement(cls, select_stmt: Select[Any]) -> None:

5261 if select_stmt._setup_joins or select_stmt._with_options:

5262 self = _MemoizedSelectEntities()

5263 self._raw_columns = select_stmt._raw_columns

5264 self._setup_joins = select_stmt._setup_joins

5265 self._with_options = select_stmt._with_options

5266

5267 select_stmt._memoized_select_entities += (self,)

5268 select_stmt._raw_columns = []

5269 select_stmt._setup_joins = select_stmt._with_options = ()

5270

5271

5272class Select(

5273 HasPrefixes,

5274 HasSuffixes,

5275 HasHints,

5276 HasCompileState,

5277 _SelectFromElements,

5278 GenerativeSelect,

5279 TypedReturnsRows[_TP],

5280):

5281 """Represents a ``SELECT`` statement.

5282

5283 The :class:`_sql.Select` object is normally constructed using the

5284 :func:`_sql.select` function. See that function for details.

5285

5286 .. seealso::

5287

5288 :func:`_sql.select`

5289

5290 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5291

5292 """

5293

5294 __visit_name__ = "select"

5295

5296 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5297 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5298

5299 _raw_columns: List[_ColumnsClauseElement]

5300

5301 _distinct: bool = False

5302 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5303 _correlate: Tuple[FromClause, ...] = ()

5304 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5305 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5306 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5307 _from_obj: Tuple[FromClause, ...] = ()

5308 _auto_correlate = True

5309 _is_select_statement = True

5310 _compile_options: CacheableOptions = (

5311 SelectState.default_select_compile_options

5312 )

5313

5314 _traverse_internals: _TraverseInternalsType = (

5315 [

5316 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5317 (

5318 "_memoized_select_entities",

5319 InternalTraversal.dp_memoized_select_entities,

5320 ),

5321 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5322 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5323 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5324 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5325 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5326 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5327 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5328 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5329 ("_limit_clause", InternalTraversal.dp_clauseelement),

5330 ("_offset_clause", InternalTraversal.dp_clauseelement),

5331 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5332 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5333 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5334 ("_distinct", InternalTraversal.dp_boolean),

5335 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5336 ("_label_style", InternalTraversal.dp_plain_obj),

5337 ]

5338 + HasCTE._has_ctes_traverse_internals

5339 + HasPrefixes._has_prefixes_traverse_internals

5340 + HasSuffixes._has_suffixes_traverse_internals

5341 + HasHints._has_hints_traverse_internals

5342 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5343 + Executable._executable_traverse_internals

5344 + DialectKWArgs._dialect_kwargs_traverse_internals

5345 )

5346

5347 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5348 ("_compile_options", InternalTraversal.dp_has_cache_key)

5349 ]

5350

5351 _compile_state_factory: Type[SelectState]

5352

5353 @classmethod

5354 def _create_raw_select(cls, **kw: Any) -> Select[Any]:

5355 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5356

5357 Used internally to build up :class:`.Select` constructs with

5358 pre-established state.

5359

5360 """

5361

5362 stmt = Select.__new__(Select)

5363 stmt.__dict__.update(kw)

5364 return stmt

5365

5366 def __init__(

5367 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5368 ):

5369 r"""Construct a new :class:`_expression.Select`.

5370

5371 The public constructor for :class:`_expression.Select` is the

5372 :func:`_sql.select` function.

5373

5374 """

5375 self._raw_columns = [

5376 coercions.expect(

5377 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5378 )

5379 for ent in entities

5380 ]

5381 GenerativeSelect.__init__(self)

5382

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

5384 if not self._raw_columns:

5385 return NULLTYPE

5386 elem = self._raw_columns[0]

5387 cols = list(elem._select_iterable)

5388 return cols[0].type

5389

5390 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5391 """A synonym for the :meth:`_sql.Select.where` method."""

5392

5393 return self.where(*criteria)

5394

5395 def _filter_by_zero(

5396 self,

5397 ) -> Union[

5398 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5399 ]:

5400 if self._setup_joins:

5401 meth = SelectState.get_plugin_class(

5402 self

5403 ).determine_last_joined_entity

5404 _last_joined_entity = meth(self)

5405 if _last_joined_entity is not None:

5406 return _last_joined_entity

5407

5408 if self._from_obj:

5409 return self._from_obj[0]

5410

5411 return self._raw_columns[0]

5412

5413 if TYPE_CHECKING:

5414

5415 @overload

5416 def scalar_subquery(

5417 self: Select[Tuple[_MAYBE_ENTITY]],

5418 ) -> ScalarSelect[Any]: ...

5419

5420 @overload

5421 def scalar_subquery(

5422 self: Select[Tuple[_NOT_ENTITY]],

5423 ) -> ScalarSelect[_NOT_ENTITY]: ...

5424

5425 @overload

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

5427

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

5429

5430 def filter_by(self, **kwargs: Any) -> Self:

5431 r"""apply the given filtering criterion as a WHERE clause

5432 to this select.

5433

5434 """

5435 from_entity = self._filter_by_zero()

5436

5437 clauses = [

5438 _entity_namespace_key(from_entity, key) == value

5439 for key, value in kwargs.items()

5440 ]

5441 return self.filter(*clauses)

5442

5443 @property

5444 def column_descriptions(self) -> Any:

5445 """Return a :term:`plugin-enabled` 'column descriptions' structure

5446 referring to the columns which are SELECTed by this statement.

5447

5448 This attribute is generally useful when using the ORM, as an

5449 extended structure which includes information about mapped

5450 entities is returned. The section :ref:`queryguide_inspection`

5451 contains more background.

5452

5453 For a Core-only statement, the structure returned by this accessor

5454 is derived from the same objects that are returned by the

5455 :attr:`.Select.selected_columns` accessor, formatted as a list of

5456 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5457 which indicate the column expressions to be selected::

5458

5459 >>> stmt = select(user_table)

5460 >>> stmt.column_descriptions

5461 [

5462 {

5463 'name': 'id',

5464 'type': Integer(),

5465 'expr': Column('id', Integer(), ...)},

5466 {

5467 'name': 'name',

5468 'type': String(length=30),

5469 'expr': Column('name', String(length=30), ...)}

5470 ]

5471

5472 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5473 attribute returns a structure for a Core-only set of entities,

5474 not just ORM-only entities.

5475

5476 .. seealso::

5477

5478 :attr:`.UpdateBase.entity_description` - entity information for

5479 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5480

5481 :ref:`queryguide_inspection` - ORM background

5482

5483 """

5484 meth = SelectState.get_plugin_class(self).get_column_descriptions

5485 return meth(self)

5486

5487 def from_statement(

5488 self, statement: roles.ReturnsRowsRole

5489 ) -> ExecutableReturnsRows:

5490 """Apply the columns which this :class:`.Select` would select

5491 onto another statement.

5492

5493 This operation is :term:`plugin-specific` and will raise a not

5494 supported exception if this :class:`_sql.Select` does not select from

5495 plugin-enabled entities.

5496

5497

5498 The statement is typically either a :func:`_expression.text` or

5499 :func:`_expression.select` construct, and should return the set of

5500 columns appropriate to the entities represented by this

5501 :class:`.Select`.

5502

5503 .. seealso::

5504

5505 :ref:`orm_queryguide_selecting_text` - usage examples in the

5506 ORM Querying Guide

5507

5508 """

5509 meth = SelectState.get_plugin_class(self).from_statement

5510 return meth(self, statement)

5511

5512 @_generative

5513 def join(

5514 self,

5515 target: _JoinTargetArgument,

5516 onclause: Optional[_OnClauseArgument] = None,

5517 *,

5518 isouter: bool = False,

5519 full: bool = False,

5520 ) -> Self:

5521 r"""Create a SQL JOIN against this :class:`_expression.Select`

5522 object's criterion

5523 and apply generatively, returning the newly resulting

5524 :class:`_expression.Select`.

5525

5526 E.g.::

5527

5528 stmt = select(user_table).join(

5529 address_table, user_table.c.id == address_table.c.user_id

5530 )

5531

5532 The above statement generates SQL similar to:

5533

5534 .. sourcecode:: sql

5535

5536 SELECT user.id, user.name

5537 FROM user

5538 JOIN address ON user.id = address.user_id

5539

5540 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5541 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5542 source that is within the FROM clause of the existing SELECT,

5543 and a given target :class:`_sql.FromClause`, and then adds

5544 this :class:`_sql.Join` to the FROM clause of the newly generated

5545 SELECT statement. This is completely reworked from the behavior

5546 in 1.3, which would instead create a subquery of the entire

5547 :class:`_expression.Select` and then join that subquery to the

5548 target.

5549

5550 This is a **backwards incompatible change** as the previous behavior

5551 was mostly useless, producing an unnamed subquery rejected by

5552 most databases in any case. The new behavior is modeled after

5553 that of the very successful :meth:`_orm.Query.join` method in the

5554 ORM, in order to support the functionality of :class:`_orm.Query`

5555 being available by using a :class:`_sql.Select` object with an

5556 :class:`_orm.Session`.

5557

5558 See the notes for this change at :ref:`change_select_join`.

5559

5560

5561 :param target: target table to join towards

5562

5563 :param onclause: ON clause of the join. If omitted, an ON clause

5564 is generated automatically based on the :class:`_schema.ForeignKey`

5565 linkages between the two tables, if one can be unambiguously

5566 determined, otherwise an error is raised.

5567

5568 :param isouter: if True, generate LEFT OUTER join. Same as

5569 :meth:`_expression.Select.outerjoin`.

5570

5571 :param full: if True, generate FULL OUTER join.

5572

5573 .. seealso::

5574

5575 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5576

5577 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5578

5579 :meth:`_expression.Select.join_from`

5580

5581 :meth:`_expression.Select.outerjoin`

5582

5583 """ # noqa: E501

5584 join_target = coercions.expect(

5585 roles.JoinTargetRole, target, apply_propagate_attrs=self

5586 )

5587 if onclause is not None:

5588 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5589 else:

5590 onclause_element = None

5591

5592 self._setup_joins += (

5593 (

5594 join_target,

5595 onclause_element,

5596 None,

5597 {"isouter": isouter, "full": full},

5598 ),

5599 )

5600 return self

5601

5602 def outerjoin_from(

5603 self,

5604 from_: _FromClauseArgument,

5605 target: _JoinTargetArgument,

5606 onclause: Optional[_OnClauseArgument] = None,

5607 *,

5608 full: bool = False,

5609 ) -> Self:

5610 r"""Create a SQL LEFT OUTER JOIN against this

5611 :class:`_expression.Select` object's criterion and apply generatively,

5612 returning the newly resulting :class:`_expression.Select`.

5613

5614 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5615

5616 """

5617 return self.join_from(

5618 from_, target, onclause=onclause, isouter=True, full=full

5619 )

5620

5621 @_generative

5622 def join_from(

5623 self,

5624 from_: _FromClauseArgument,

5625 target: _JoinTargetArgument,

5626 onclause: Optional[_OnClauseArgument] = None,

5627 *,

5628 isouter: bool = False,

5629 full: bool = False,

5630 ) -> Self:

5631 r"""Create a SQL JOIN against this :class:`_expression.Select`

5632 object's criterion

5633 and apply generatively, returning the newly resulting

5634 :class:`_expression.Select`.

5635

5636 E.g.::

5637

5638 stmt = select(user_table, address_table).join_from(

5639 user_table, address_table, user_table.c.id == address_table.c.user_id

5640 )

5641

5642 The above statement generates SQL similar to:

5643

5644 .. sourcecode:: sql

5645

5646 SELECT user.id, user.name, address.id, address.email, address.user_id

5647 FROM user JOIN address ON user.id = address.user_id

5648

5649 .. versionadded:: 1.4

5650

5651 :param from\_: the left side of the join, will be rendered in the

5652 FROM clause and is roughly equivalent to using the

5653 :meth:`.Select.select_from` method.

5654

5655 :param target: target table to join towards

5656

5657 :param onclause: ON clause of the join.

5658

5659 :param isouter: if True, generate LEFT OUTER join. Same as

5660 :meth:`_expression.Select.outerjoin`.

5661

5662 :param full: if True, generate FULL OUTER join.

5663

5664 .. seealso::

5665

5666 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5667

5668 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5669

5670 :meth:`_expression.Select.join`

5671

5672 """ # noqa: E501

5673

5674 # note the order of parsing from vs. target is important here, as we

5675 # are also deriving the source of the plugin (i.e. the subject mapper

5676 # in an ORM query) which should favor the "from_" over the "target"

5677

5678 from_ = coercions.expect(

5679 roles.FromClauseRole, from_, apply_propagate_attrs=self

5680 )

5681 join_target = coercions.expect(

5682 roles.JoinTargetRole, target, apply_propagate_attrs=self

5683 )

5684 if onclause is not None:

5685 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5686 else:

5687 onclause_element = None

5688

5689 self._setup_joins += (

5690 (

5691 join_target,

5692 onclause_element,

5693 from_,

5694 {"isouter": isouter, "full": full},

5695 ),

5696 )

5697 return self

5698

5699 def outerjoin(

5700 self,

5701 target: _JoinTargetArgument,

5702 onclause: Optional[_OnClauseArgument] = None,

5703 *,

5704 full: bool = False,

5705 ) -> Self:

5706 """Create a left outer join.

5707

5708 Parameters are the same as that of :meth:`_expression.Select.join`.

5709

5710 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5711 creates a :class:`_sql.Join` object between a

5712 :class:`_sql.FromClause` source that is within the FROM clause of

5713 the existing SELECT, and a given target :class:`_sql.FromClause`,

5714 and then adds this :class:`_sql.Join` to the FROM clause of the

5715 newly generated SELECT statement. This is completely reworked

5716 from the behavior in 1.3, which would instead create a subquery of

5717 the entire

5718 :class:`_expression.Select` and then join that subquery to the

5719 target.

5720

5721 This is a **backwards incompatible change** as the previous behavior

5722 was mostly useless, producing an unnamed subquery rejected by

5723 most databases in any case. The new behavior is modeled after

5724 that of the very successful :meth:`_orm.Query.join` method in the

5725 ORM, in order to support the functionality of :class:`_orm.Query`

5726 being available by using a :class:`_sql.Select` object with an

5727 :class:`_orm.Session`.

5728

5729 See the notes for this change at :ref:`change_select_join`.

5730

5731 .. seealso::

5732

5733 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5734

5735 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5736

5737 :meth:`_expression.Select.join`

5738

5739 """

5740 return self.join(target, onclause=onclause, isouter=True, full=full)

5741

5742 def get_final_froms(self) -> Sequence[FromClause]:

5743 """Compute the final displayed list of :class:`_expression.FromClause`

5744 elements.

5745

5746 This method will run through the full computation required to

5747 determine what FROM elements will be displayed in the resulting

5748 SELECT statement, including shadowing individual tables with

5749 JOIN objects, as well as full computation for ORM use cases including

5750 eager loading clauses.

5751

5752 For ORM use, this accessor returns the **post compilation**

5753 list of FROM objects; this collection will include elements such as

5754 eagerly loaded tables and joins. The objects will **not** be

5755 ORM enabled and not work as a replacement for the

5756 :meth:`_sql.Select.select_froms` collection; additionally, the

5757 method is not well performing for an ORM enabled statement as it

5758 will incur the full ORM construction process.

5759

5760 To retrieve the FROM list that's implied by the "columns" collection

5761 passed to the :class:`_sql.Select` originally, use the

5762 :attr:`_sql.Select.columns_clause_froms` accessor.

5763

5764 To select from an alternative set of columns while maintaining the

5765 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5766 pass the

5767 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5768 parameter.

5769

5770 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5771 method replaces the previous :attr:`_sql.Select.froms` accessor,

5772 which is deprecated.

5773

5774 .. seealso::

5775

5776 :attr:`_sql.Select.columns_clause_froms`

5777

5778 """

5779 compiler = self._default_compiler()

5780

5781 return self._compile_state_factory(self, compiler)._get_display_froms()

5782

5783 @property

5784 @util.deprecated(

5785 "1.4.23",

5786 "The :attr:`_expression.Select.froms` attribute is moved to "

5787 "the :meth:`_expression.Select.get_final_froms` method.",

5788 )

5789 def froms(self) -> Sequence[FromClause]:

5790 """Return the displayed list of :class:`_expression.FromClause`

5791 elements.

5792

5793

5794 """

5795 return self.get_final_froms()

5796

5797 @property

5798 def columns_clause_froms(self) -> List[FromClause]:

5799 """Return the set of :class:`_expression.FromClause` objects implied

5800 by the columns clause of this SELECT statement.

5801

5802 .. versionadded:: 1.4.23

5803

5804 .. seealso::

5805

5806 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5807 statement into account

5808

5809 :meth:`_sql.Select.with_only_columns` - makes use of this

5810 collection to set up a new FROM list

5811

5812 """

5813

5814 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5815 self

5816 )

5817

5818 @property

5819 def inner_columns(self) -> _SelectIterable:

5820 """An iterator of all :class:`_expression.ColumnElement`

5821 expressions which would

5822 be rendered into the columns clause of the resulting SELECT statement.

5823

5824 This method is legacy as of 1.4 and is superseded by the

5825 :attr:`_expression.Select.exported_columns` collection.

5826

5827 """

5828

5829 return iter(self._all_selected_columns)

5830

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

5832 if fromclause is not None and self in fromclause._cloned_set:

5833 return True

5834

5835 for f in self._iterate_from_elements():

5836 if f.is_derived_from(fromclause):

5837 return True

5838 return False

5839

5840 def _copy_internals(

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

5842 ) -> None:

5843 # Select() object has been cloned and probably adapted by the

5844 # given clone function. Apply the cloning function to internal

5845 # objects

5846

5847 # 1. keep a dictionary of the froms we've cloned, and what

5848 # they've become. This allows us to ensure the same cloned from

5849 # is used when other items such as columns are "cloned"

5850

5851 all_the_froms = set(

5852 itertools.chain(

5853 _from_objects(*self._raw_columns),

5854 _from_objects(*self._where_criteria),

5855 _from_objects(*[elem[0] for elem in self._setup_joins]),

5856 )

5857 )

5858

5859 # do a clone for the froms we've gathered. what is important here

5860 # is if any of the things we are selecting from, like tables,

5861 # were converted into Join objects. if so, these need to be

5862 # added to _from_obj explicitly, because otherwise they won't be

5863 # part of the new state, as they don't associate themselves with

5864 # their columns.

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

5866

5867 # 2. copy FROM collections, adding in joins that we've created.

5868 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5869 add_froms = (

5870 {f for f in new_froms.values() if isinstance(f, Join)}

5871 .difference(all_the_froms)

5872 .difference(existing_from_obj)

5873 )

5874

5875 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5876

5877 # 3. clone everything else, making sure we use columns

5878 # corresponding to the froms we just made.

5879 def replace(

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

5881 **kw: Any,

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

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

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

5885 return newelem

5886 return None

5887

5888 kw["replace"] = replace

5889

5890 # copy everything else. for table-ish things like correlate,

5891 # correlate_except, setup_joins, these clone normally. For

5892 # column-expression oriented things like raw_columns, where_criteria,

5893 # order by, we get this from the new froms.

5894 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5895

5896 self._reset_memoizations()

5897

5898 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5899 return itertools.chain(

5900 super().get_children(

5901 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5902 **kw,

5903 ),

5904 self._iterate_from_elements(),

5905 )

5906

5907 @_generative

5908 def add_columns(

5909 self, *entities: _ColumnsClauseArgument[Any]

5910 ) -> Select[Any]:

5911 r"""Return a new :func:`_expression.select` construct with

5912 the given entities appended to its columns clause.

5913

5914 E.g.::

5915

5916 my_select = my_select.add_columns(table.c.new_column)

5917

5918 The original expressions in the columns clause remain in place.

5919 To replace the original expressions with new ones, see the method

5920 :meth:`_expression.Select.with_only_columns`.

5921

5922 :param \*entities: column, table, or other entity expressions to be

5923 added to the columns clause

5924

5925 .. seealso::

5926

5927 :meth:`_expression.Select.with_only_columns` - replaces existing

5928 expressions rather than appending.

5929

5930 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5931 example

5932

5933 """

5934 self._reset_memoizations()

5935

5936 self._raw_columns = self._raw_columns + [

5937 coercions.expect(

5938 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5939 )

5940 for column in entities

5941 ]

5942 return self

5943

5944 def _set_entities(

5945 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5946 ) -> None:

5947 self._raw_columns = [

5948 coercions.expect(

5949 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5950 )

5951 for ent in util.to_list(entities)

5952 ]

5953

5954 @util.deprecated(

5955 "1.4",

5956 "The :meth:`_expression.Select.column` method is deprecated and will "

5957 "be removed in a future release. Please use "

5958 ":meth:`_expression.Select.add_columns`",

5959 )

5960 def column(self, column: _ColumnsClauseArgument[Any]) -> Select[Any]:

5961 """Return a new :func:`_expression.select` construct with

5962 the given column expression added to its columns clause.

5963

5964 E.g.::

5965

5966 my_select = my_select.column(table.c.new_column)

5967

5968 See the documentation for

5969 :meth:`_expression.Select.with_only_columns`

5970 for guidelines on adding /replacing the columns of a

5971 :class:`_expression.Select` object.

5972

5973 """

5974 return self.add_columns(column)

5975

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

5977 def reduce_columns(self, only_synonyms: bool = True) -> Select[Any]:

5978 """Return a new :func:`_expression.select` construct with redundantly

5979 named, equivalently-valued columns removed from the columns clause.

5980

5981 "Redundant" here means two columns where one refers to the

5982 other either based on foreign key, or via a simple equality

5983 comparison in the WHERE clause of the statement. The primary purpose

5984 of this method is to automatically construct a select statement

5985 with all uniquely-named columns, without the need to use

5986 table-qualified labels as

5987 :meth:`_expression.Select.set_label_style`

5988 does.

5989

5990 When columns are omitted based on foreign key, the referred-to

5991 column is the one that's kept. When columns are omitted based on

5992 WHERE equivalence, the first column in the columns clause is the

5993 one that's kept.

5994

5995 :param only_synonyms: when True, limit the removal of columns

5996 to those which have the same name as the equivalent. Otherwise,

5997 all columns that are equivalent to another are removed.

5998

5999 """

6000 woc: Select[Any]

6001 woc = self.with_only_columns(

6002 *util.preloaded.sql_util.reduce_columns(

6003 self._all_selected_columns,

6004 only_synonyms=only_synonyms,

6005 *(self._where_criteria + self._from_obj),

6006 )

6007 )

6008 return woc

6009

6010 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6011

6012 # code within this block is **programmatically,

6013 # statically generated** by tools/generate_tuple_map_overloads.py

6014

6015 @overload

6016 def with_only_columns(

6017 self, __ent0: _TCCA[_T0], *, maintain_column_froms: bool = ...

6018 ) -> Select[Tuple[_T0]]: ...

6019

6020 @overload

6021 def with_only_columns(

6022 self,

6023 __ent0: _TCCA[_T0],

6024 __ent1: _TCCA[_T1],

6025 *,

6026 maintain_column_froms: bool = ...,

6027 ) -> Select[Tuple[_T0, _T1]]: ...

6028

6029 @overload

6030 def with_only_columns(

6031 self,

6032 __ent0: _TCCA[_T0],

6033 __ent1: _TCCA[_T1],

6034 __ent2: _TCCA[_T2],

6035 *,

6036 maintain_column_froms: bool = ...,

6037 ) -> Select[Tuple[_T0, _T1, _T2]]: ...

6038

6039 @overload

6040 def with_only_columns(

6041 self,

6042 __ent0: _TCCA[_T0],

6043 __ent1: _TCCA[_T1],

6044 __ent2: _TCCA[_T2],

6045 __ent3: _TCCA[_T3],

6046 *,

6047 maintain_column_froms: bool = ...,

6048 ) -> Select[Tuple[_T0, _T1, _T2, _T3]]: ...

6049

6050 @overload

6051 def with_only_columns(

6052 self,

6053 __ent0: _TCCA[_T0],

6054 __ent1: _TCCA[_T1],

6055 __ent2: _TCCA[_T2],

6056 __ent3: _TCCA[_T3],

6057 __ent4: _TCCA[_T4],

6058 *,

6059 maintain_column_froms: bool = ...,

6060 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...

6061

6062 @overload

6063 def with_only_columns(

6064 self,

6065 __ent0: _TCCA[_T0],

6066 __ent1: _TCCA[_T1],

6067 __ent2: _TCCA[_T2],

6068 __ent3: _TCCA[_T3],

6069 __ent4: _TCCA[_T4],

6070 __ent5: _TCCA[_T5],

6071 *,

6072 maintain_column_froms: bool = ...,

6073 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...

6074

6075 @overload

6076 def with_only_columns(

6077 self,

6078 __ent0: _TCCA[_T0],

6079 __ent1: _TCCA[_T1],

6080 __ent2: _TCCA[_T2],

6081 __ent3: _TCCA[_T3],

6082 __ent4: _TCCA[_T4],

6083 __ent5: _TCCA[_T5],

6084 __ent6: _TCCA[_T6],

6085 *,

6086 maintain_column_froms: bool = ...,

6087 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...

6088

6089 @overload

6090 def with_only_columns(

6091 self,

6092 __ent0: _TCCA[_T0],

6093 __ent1: _TCCA[_T1],

6094 __ent2: _TCCA[_T2],

6095 __ent3: _TCCA[_T3],

6096 __ent4: _TCCA[_T4],

6097 __ent5: _TCCA[_T5],

6098 __ent6: _TCCA[_T6],

6099 __ent7: _TCCA[_T7],

6100 *,

6101 maintain_column_froms: bool = ...,

6102 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...

6103

6104 # END OVERLOADED FUNCTIONS self.with_only_columns

6105

6106 @overload

6107 def with_only_columns(

6108 self,

6109 *entities: _ColumnsClauseArgument[Any],

6110 maintain_column_froms: bool = False,

6111 **__kw: Any,

6112 ) -> Select[Any]: ...

6113

6114 @_generative

6115 def with_only_columns(

6116 self,

6117 *entities: _ColumnsClauseArgument[Any],

6118 maintain_column_froms: bool = False,

6119 **__kw: Any,

6120 ) -> Select[Any]:

6121 r"""Return a new :func:`_expression.select` construct with its columns

6122 clause replaced with the given entities.

6123

6124 By default, this method is exactly equivalent to as if the original

6125 :func:`_expression.select` had been called with the given entities.

6126 E.g. a statement::

6127

6128 s = select(table1.c.a, table1.c.b)

6129 s = s.with_only_columns(table1.c.b)

6130

6131 should be exactly equivalent to::

6132

6133 s = select(table1.c.b)

6134

6135 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6136 will also dynamically alter the FROM clause of the

6137 statement if it is not explicitly stated.

6138 To maintain the existing set of FROMs including those implied by the

6139 current columns clause, add the

6140 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6141 parameter::

6142

6143 s = select(table1.c.a, table2.c.b)

6144 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6145

6146 The above parameter performs a transfer of the effective FROMs

6147 in the columns collection to the :meth:`_sql.Select.select_from`

6148 method, as though the following were invoked::

6149

6150 s = select(table1.c.a, table2.c.b)

6151 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6152

6153 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6154 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6155 collection and performs an operation equivalent to the following::

6156

6157 s = select(table1.c.a, table2.c.b)

6158 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6159

6160 :param \*entities: column expressions to be used.

6161

6162 :param maintain_column_froms: boolean parameter that will ensure the

6163 FROM list implied from the current columns clause will be transferred

6164 to the :meth:`_sql.Select.select_from` method first.

6165

6166 .. versionadded:: 1.4.23

6167

6168 """ # noqa: E501

6169

6170 if __kw:

6171 raise _no_kw()

6172

6173 # memoizations should be cleared here as of

6174 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6175 # is the case for now.

6176 self._assert_no_memoizations()

6177

6178 if maintain_column_froms:

6179 self.select_from.non_generative( # type: ignore

6180 self, *self.columns_clause_froms

6181 )

6182

6183 # then memoize the FROMs etc.

6184 _MemoizedSelectEntities._generate_for_statement(self)

6185

6186 self._raw_columns = [

6187 coercions.expect(roles.ColumnsClauseRole, c)

6188 for c in coercions._expression_collection_was_a_list(

6189 "entities", "Select.with_only_columns", entities

6190 )

6191 ]

6192 return self

6193

6194 @property

6195 def whereclause(self) -> Optional[ColumnElement[Any]]:

6196 """Return the completed WHERE clause for this

6197 :class:`_expression.Select` statement.

6198

6199 This assembles the current collection of WHERE criteria

6200 into a single :class:`_expression.BooleanClauseList` construct.

6201

6202

6203 .. versionadded:: 1.4

6204

6205 """

6206

6207 return BooleanClauseList._construct_for_whereclause(

6208 self._where_criteria

6209 )

6210

6211 _whereclause = whereclause

6212

6213 @_generative

6214 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6215 """Return a new :func:`_expression.select` construct with

6216 the given expression added to

6217 its WHERE clause, joined to the existing clause via AND, if any.

6218

6219 """

6220

6221 assert isinstance(self._where_criteria, tuple)

6222

6223 for criterion in whereclause:

6224 where_criteria: ColumnElement[Any] = coercions.expect(

6225 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6226 )

6227 self._where_criteria += (where_criteria,)

6228 return self

6229

6230 @_generative

6231 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6232 """Return a new :func:`_expression.select` construct with

6233 the given expression added to

6234 its HAVING clause, joined to the existing clause via AND, if any.

6235

6236 """

6237

6238 for criterion in having:

6239 having_criteria = coercions.expect(

6240 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6241 )

6242 self._having_criteria += (having_criteria,)

6243 return self

6244

6245 @_generative

6246 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6247 r"""Return a new :func:`_expression.select` construct which

6248 will apply DISTINCT to the SELECT statement overall.

6249

6250 E.g.::

6251

6252 from sqlalchemy import select

6253

6254 stmt = select(users_table.c.id, users_table.c.name).distinct()

6255

6256 The above would produce an statement resembling:

6257

6258 .. sourcecode:: sql

6259

6260 SELECT DISTINCT user.id, user.name FROM user

6261

6262 The method also accepts an ``*expr`` parameter which produces the

6263 PostgreSQL dialect-specific ``DISTINCT ON`` expression. Using this

6264 parameter on other backends which don't support this syntax will

6265 raise an error.

6266

6267 :param \*expr: optional column expressions. When present,

6268 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6269 construct. A deprecation warning and/or :class:`_exc.CompileError`

6270 will be raised on other backends.

6271

6272 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6273 and will raise :class:`_exc.CompileError` in a future version.

6274

6275 """

6276 if expr:

6277 self._distinct = True

6278 self._distinct_on = self._distinct_on + tuple(

6279 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6280 for e in expr

6281 )

6282 else:

6283 self._distinct = True

6284 return self

6285

6286 @_generative

6287 def select_from(self, *froms: _FromClauseArgument) -> Self:

6288 r"""Return a new :func:`_expression.select` construct with the

6289 given FROM expression(s)

6290 merged into its list of FROM objects.

6291

6292 E.g.::

6293

6294 table1 = table("t1", column("a"))

6295 table2 = table("t2", column("b"))

6296 s = select(table1.c.a).select_from(

6297 table1.join(table2, table1.c.a == table2.c.b)

6298 )

6299

6300 The "from" list is a unique set on the identity of each element,

6301 so adding an already present :class:`_schema.Table`

6302 or other selectable

6303 will have no effect. Passing a :class:`_expression.Join` that refers

6304 to an already present :class:`_schema.Table`

6305 or other selectable will have

6306 the effect of concealing the presence of that selectable as

6307 an individual element in the rendered FROM list, instead

6308 rendering it into a JOIN clause.

6309

6310 While the typical purpose of :meth:`_expression.Select.select_from`

6311 is to

6312 replace the default, derived FROM clause with a join, it can

6313 also be called with individual table elements, multiple times

6314 if desired, in the case that the FROM clause cannot be fully

6315 derived from the columns clause::

6316

6317 select(func.count("*")).select_from(table1)

6318

6319 """

6320

6321 self._from_obj += tuple(

6322 coercions.expect(

6323 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6324 )

6325 for fromclause in froms

6326 )

6327 return self

6328

6329 @_generative

6330 def correlate(

6331 self,

6332 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6333 ) -> Self:

6334 r"""Return a new :class:`_expression.Select`

6335 which will correlate the given FROM

6336 clauses to that of an enclosing :class:`_expression.Select`.

6337

6338 Calling this method turns off the :class:`_expression.Select` object's

6339 default behavior of "auto-correlation". Normally, FROM elements

6340 which appear in a :class:`_expression.Select`

6341 that encloses this one via

6342 its :term:`WHERE clause`, ORDER BY, HAVING or

6343 :term:`columns clause` will be omitted from this

6344 :class:`_expression.Select`

6345 object's :term:`FROM clause`.

6346 Setting an explicit correlation collection using the

6347 :meth:`_expression.Select.correlate`

6348 method provides a fixed list of FROM objects

6349 that can potentially take place in this process.

6350

6351 When :meth:`_expression.Select.correlate`

6352 is used to apply specific FROM clauses

6353 for correlation, the FROM elements become candidates for

6354 correlation regardless of how deeply nested this

6355 :class:`_expression.Select`

6356 object is, relative to an enclosing :class:`_expression.Select`

6357 which refers to

6358 the same FROM object. This is in contrast to the behavior of

6359 "auto-correlation" which only correlates to an immediate enclosing

6360 :class:`_expression.Select`.

6361 Multi-level correlation ensures that the link

6362 between enclosed and enclosing :class:`_expression.Select`

6363 is always via

6364 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6365 correlation to take place.

6366

6367 If ``None`` is passed, the :class:`_expression.Select`

6368 object will correlate

6369 none of its FROM entries, and all will render unconditionally

6370 in the local FROM clause.

6371

6372 :param \*fromclauses: one or more :class:`.FromClause` or other

6373 FROM-compatible construct such as an ORM mapped entity to become part

6374 of the correlate collection; alternatively pass a single value

6375 ``None`` to remove all existing correlations.

6376

6377 .. seealso::

6378

6379 :meth:`_expression.Select.correlate_except`

6380

6381 :ref:`tutorial_scalar_subquery`

6382

6383 """

6384

6385 # tests failing when we try to change how these

6386 # arguments are passed

6387

6388 self._auto_correlate = False

6389 if not fromclauses or fromclauses[0] in {None, False}:

6390 if len(fromclauses) > 1:

6391 raise exc.ArgumentError(

6392 "additional FROM objects not accepted when "

6393 "passing None/False to correlate()"

6394 )

6395 self._correlate = ()

6396 else:

6397 self._correlate = self._correlate + tuple(

6398 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6399 )

6400 return self

6401

6402 @_generative

6403 def correlate_except(

6404 self,

6405 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6406 ) -> Self:

6407 r"""Return a new :class:`_expression.Select`

6408 which will omit the given FROM

6409 clauses from the auto-correlation process.

6410

6411 Calling :meth:`_expression.Select.correlate_except` turns off the

6412 :class:`_expression.Select` object's default behavior of

6413 "auto-correlation" for the given FROM elements. An element

6414 specified here will unconditionally appear in the FROM list, while

6415 all other FROM elements remain subject to normal auto-correlation

6416 behaviors.

6417

6418 If ``None`` is passed, or no arguments are passed,

6419 the :class:`_expression.Select` object will correlate all of its

6420 FROM entries.

6421

6422 :param \*fromclauses: a list of one or more

6423 :class:`_expression.FromClause`

6424 constructs, or other compatible constructs (i.e. ORM-mapped

6425 classes) to become part of the correlate-exception collection.

6426

6427 .. seealso::

6428

6429 :meth:`_expression.Select.correlate`

6430

6431 :ref:`tutorial_scalar_subquery`

6432

6433 """

6434

6435 self._auto_correlate = False

6436 if not fromclauses or fromclauses[0] in {None, False}:

6437 if len(fromclauses) > 1:

6438 raise exc.ArgumentError(

6439 "additional FROM objects not accepted when "

6440 "passing None/False to correlate_except()"

6441 )

6442 self._correlate_except = ()

6443 else:

6444 self._correlate_except = (self._correlate_except or ()) + tuple(

6445 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6446 )

6447

6448 return self

6449

6450 @HasMemoized_ro_memoized_attribute

6451 def selected_columns(

6452 self,

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

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

6455 representing the columns that

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

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

6458

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

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

6461 within this collection cannot be directly nested inside another SELECT

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

6463 necessary parenthesization required by SQL.

6464

6465 For a :func:`_expression.select` construct, the collection here is

6466 exactly what would be rendered inside the "SELECT" statement, and the

6467 :class:`_expression.ColumnElement` objects are directly present as they

6468 were given, e.g.::

6469

6470 col1 = column("q", Integer)

6471 col2 = column("p", Integer)

6472 stmt = select(col1, col2)

6473

6474 Above, ``stmt.selected_columns`` would be a collection that contains

6475 the ``col1`` and ``col2`` objects directly. For a statement that is

6476 against a :class:`_schema.Table` or other

6477 :class:`_expression.FromClause`, the collection will use the

6478 :class:`_expression.ColumnElement` objects that are in the

6479 :attr:`_expression.FromClause.c` collection of the from element.

6480

6481 A use case for the :attr:`_sql.Select.selected_columns` collection is

6482 to allow the existing columns to be referenced when adding additional

6483 criteria, e.g.::

6484

6485 def filter_on_id(my_select, id):

6486 return my_select.where(my_select.selected_columns["id"] == id)

6487

6488

6489 stmt = select(MyModel)

6490

6491 # adds "WHERE id=:param" to the statement

6492 stmt = filter_on_id(stmt, 42)

6493

6494 .. note::

6495

6496 The :attr:`_sql.Select.selected_columns` collection does not

6497 include expressions established in the columns clause using the

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

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

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

6501 construct.

6502

6503

6504 .. versionadded:: 1.4

6505

6506 """

6507

6508 # compare to SelectState._generate_columns_plus_names, which

6509 # generates the actual names used in the SELECT string. that

6510 # method is more complex because it also renders columns that are

6511 # fully ambiguous, e.g. same column more than once.

6512 conv = cast(

6513 "Callable[[Any], str]",

6514 SelectState._column_naming_convention(self._label_style),

6515 )

6516

6517 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6518 [

6519 (conv(c), c)

6520 for c in self._all_selected_columns

6521 if is_column_element(c)

6522 ]

6523 )

6524 return cc.as_readonly()

6525

6526 @HasMemoized_ro_memoized_attribute

6527 def _all_selected_columns(self) -> _SelectIterable:

6528 meth = SelectState.get_plugin_class(self).all_selected_columns

6529 return list(meth(self))

6530

6531 def _ensure_disambiguated_names(self) -> Select[Any]:

6532 if self._label_style is LABEL_STYLE_NONE:

6533 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6534 return self

6535

6536 def _generate_fromclause_column_proxies(

6537 self,

6538 subquery: FromClause,

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

6540 primary_key: ColumnSet,

6541 foreign_keys: Set[KeyedColumnElement[Any]],

6542 *,

6543 proxy_compound_columns: Optional[

6544 Iterable[Sequence[ColumnElement[Any]]]

6545 ] = None,

6546 ) -> None:

6547 """Generate column proxies to place in the exported ``.c``

6548 collection of a subquery."""

6549

6550 if proxy_compound_columns:

6551 extra_col_iterator = proxy_compound_columns

6552 prox = [

6553 c._make_proxy(

6554 subquery,

6555 key=proxy_key,

6556 name=required_label_name,

6557 name_is_truncatable=True,

6558 compound_select_cols=extra_cols,

6559 primary_key=primary_key,

6560 foreign_keys=foreign_keys,

6561 )

6562 for (

6563 (

6564 required_label_name,

6565 proxy_key,

6566 fallback_label_name,

6567 c,

6568 repeated,

6569 ),

6570 extra_cols,

6571 ) in (

6572 zip(

6573 self._generate_columns_plus_names(False),

6574 extra_col_iterator,

6575 )

6576 )

6577 if is_column_element(c)

6578 ]

6579 else:

6580 prox = [

6581 c._make_proxy(

6582 subquery,

6583 key=proxy_key,

6584 name=required_label_name,

6585 name_is_truncatable=True,

6586 primary_key=primary_key,

6587 foreign_keys=foreign_keys,

6588 )

6589 for (

6590 required_label_name,

6591 proxy_key,

6592 fallback_label_name,

6593 c,

6594 repeated,

6595 ) in (self._generate_columns_plus_names(False))

6596 if is_column_element(c)

6597 ]

6598

6599 columns._populate_separate_keys(prox)

6600

6601 def _needs_parens_for_grouping(self) -> bool:

6602 return self._has_row_limiting_clause or bool(

6603 self._order_by_clause.clauses

6604 )

6605

6606 def self_group(

6607 self, against: Optional[OperatorType] = None

6608 ) -> Union[SelectStatementGrouping[Self], Self]:

6609 """Return a 'grouping' construct as per the

6610 :class:`_expression.ClauseElement` specification.

6611

6612 This produces an element that can be embedded in an expression. Note

6613 that this method is called automatically as needed when constructing

6614 expressions and should not require explicit use.

6615

6616 """

6617 if (

6618 isinstance(against, CompoundSelect)

6619 and not self._needs_parens_for_grouping()

6620 ):

6621 return self

6622 else:

6623 return SelectStatementGrouping(self)

6624

6625 def union(

6626 self, *other: _SelectStatementForCompoundArgument[_TP]

6627 ) -> CompoundSelect[_TP]:

6628 r"""Return a SQL ``UNION`` of this select() construct against

6629 the given selectables provided as positional arguments.

6630

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

6632 UNION.

6633

6634 .. versionchanged:: 1.4.28

6635

6636 multiple elements are now accepted.

6637

6638 :param \**kwargs: keyword arguments are forwarded to the constructor

6639 for the newly created :class:`_sql.CompoundSelect` object.

6640

6641 """

6642 return CompoundSelect._create_union(self, *other)

6643

6644 def union_all(

6645 self, *other: _SelectStatementForCompoundArgument[_TP]

6646 ) -> CompoundSelect[_TP]:

6647 r"""Return a SQL ``UNION ALL`` of this select() construct against

6648 the given selectables provided as positional arguments.

6649

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

6651 UNION.

6652

6653 .. versionchanged:: 1.4.28

6654

6655 multiple elements are now accepted.

6656

6657 :param \**kwargs: keyword arguments are forwarded to the constructor

6658 for the newly created :class:`_sql.CompoundSelect` object.

6659

6660 """

6661 return CompoundSelect._create_union_all(self, *other)

6662

6663 def except_(

6664 self, *other: _SelectStatementForCompoundArgument[_TP]

6665 ) -> CompoundSelect[_TP]:

6666 r"""Return a SQL ``EXCEPT`` of this select() construct against

6667 the given selectable provided as positional arguments.

6668

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

6670 UNION.

6671

6672 .. versionchanged:: 1.4.28

6673

6674 multiple elements are now accepted.

6675

6676 """

6677 return CompoundSelect._create_except(self, *other)

6678

6679 def except_all(

6680 self, *other: _SelectStatementForCompoundArgument[_TP]

6681 ) -> CompoundSelect[_TP]:

6682 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6683 the given selectables provided as positional arguments.

6684

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

6686 UNION.

6687

6688 .. versionchanged:: 1.4.28

6689

6690 multiple elements are now accepted.

6691

6692 """

6693 return CompoundSelect._create_except_all(self, *other)

6694

6695 def intersect(

6696 self, *other: _SelectStatementForCompoundArgument[_TP]

6697 ) -> CompoundSelect[_TP]:

6698 r"""Return a SQL ``INTERSECT`` of this select() construct against

6699 the given selectables provided as positional arguments.

6700

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

6702 UNION.

6703

6704 .. versionchanged:: 1.4.28

6705

6706 multiple elements are now accepted.

6707

6708 :param \**kwargs: keyword arguments are forwarded to the constructor

6709 for the newly created :class:`_sql.CompoundSelect` object.

6710

6711 """

6712 return CompoundSelect._create_intersect(self, *other)

6713

6714 def intersect_all(

6715 self, *other: _SelectStatementForCompoundArgument[_TP]

6716 ) -> CompoundSelect[_TP]:

6717 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6718 against the given selectables provided as positional arguments.

6719

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

6721 UNION.

6722

6723 .. versionchanged:: 1.4.28

6724

6725 multiple elements are now accepted.

6726

6727 :param \**kwargs: keyword arguments are forwarded to the constructor

6728 for the newly created :class:`_sql.CompoundSelect` object.

6729

6730 """

6731 return CompoundSelect._create_intersect_all(self, *other)

6732

6733

6734class ScalarSelect(

6735 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6736):

6737 """Represent a scalar subquery.

6738

6739

6740 A :class:`_sql.ScalarSelect` is created by invoking the

6741 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6742 then participates in other SQL expressions as a SQL column expression

6743 within the :class:`_sql.ColumnElement` hierarchy.

6744

6745 .. seealso::

6746

6747 :meth:`_sql.SelectBase.scalar_subquery`

6748

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

6750

6751 """

6752

6753 _traverse_internals: _TraverseInternalsType = [

6754 ("element", InternalTraversal.dp_clauseelement),

6755 ("type", InternalTraversal.dp_type),

6756 ]

6757

6758 _from_objects: List[FromClause] = []

6759 _is_from_container = True

6760 if not TYPE_CHECKING:

6761 _is_implicitly_boolean = False

6762 inherit_cache = True

6763

6764 element: SelectBase

6765

6766 def __init__(self, element: SelectBase) -> None:

6767 self.element = element

6768 self.type = element._scalar_type()

6769 self._propagate_attrs = element._propagate_attrs

6770

6771 def __getattr__(self, attr: str) -> Any:

6772 return getattr(self.element, attr)

6773

6774 def __getstate__(self) -> Dict[str, Any]:

6775 return {"element": self.element, "type": self.type}

6776

6777 def __setstate__(self, state: Dict[str, Any]) -> None:

6778 self.element = state["element"]

6779 self.type = state["type"]

6780

6781 @property

6782 def columns(self) -> NoReturn:

6783 raise exc.InvalidRequestError(

6784 "Scalar Select expression has no "

6785 "columns; use this object directly "

6786 "within a column-level expression."

6787 )

6788

6789 c = columns

6790

6791 @_generative

6792 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6793 """Apply a WHERE clause to the SELECT statement referred to

6794 by this :class:`_expression.ScalarSelect`.

6795

6796 """

6797 self.element = cast("Select[Any]", self.element).where(crit)

6798 return self

6799

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

6801 return self

6802

6803 def _ungroup(self) -> Self:

6804 return self

6805

6806 @_generative

6807 def correlate(

6808 self,

6809 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6810 ) -> Self:

6811 r"""Return a new :class:`_expression.ScalarSelect`

6812 which will correlate the given FROM

6813 clauses to that of an enclosing :class:`_expression.Select`.

6814

6815 This method is mirrored from the :meth:`_sql.Select.correlate` method

6816 of the underlying :class:`_sql.Select`. The method applies the

6817 :meth:_sql.Select.correlate` method, then returns a new

6818 :class:`_sql.ScalarSelect` against that statement.

6819

6820 .. versionadded:: 1.4 Previously, the

6821 :meth:`_sql.ScalarSelect.correlate`

6822 method was only available from :class:`_sql.Select`.

6823

6824 :param \*fromclauses: a list of one or more

6825 :class:`_expression.FromClause`

6826 constructs, or other compatible constructs (i.e. ORM-mapped

6827 classes) to become part of the correlate collection.

6828

6829 .. seealso::

6830

6831 :meth:`_expression.ScalarSelect.correlate_except`

6832

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

6834

6835

6836 """

6837 self.element = cast("Select[Any]", self.element).correlate(

6838 *fromclauses

6839 )

6840 return self

6841

6842 @_generative

6843 def correlate_except(

6844 self,

6845 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6846 ) -> Self:

6847 r"""Return a new :class:`_expression.ScalarSelect`

6848 which will omit the given FROM

6849 clauses from the auto-correlation process.

6850

6851 This method is mirrored from the

6852 :meth:`_sql.Select.correlate_except` method of the underlying

6853 :class:`_sql.Select`. The method applies the

6854 :meth:_sql.Select.correlate_except` method, then returns a new

6855 :class:`_sql.ScalarSelect` against that statement.

6856

6857 .. versionadded:: 1.4 Previously, the

6858 :meth:`_sql.ScalarSelect.correlate_except`

6859 method was only available from :class:`_sql.Select`.

6860

6861 :param \*fromclauses: a list of one or more

6862 :class:`_expression.FromClause`

6863 constructs, or other compatible constructs (i.e. ORM-mapped

6864 classes) to become part of the correlate-exception collection.

6865

6866 .. seealso::

6867

6868 :meth:`_expression.ScalarSelect.correlate`

6869

6870 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6871

6872

6873 """

6874

6875 self.element = cast("Select[Any]", self.element).correlate_except(

6876 *fromclauses

6877 )

6878 return self

6879

6880

6881class Exists(UnaryExpression[bool]):

6882 """Represent an ``EXISTS`` clause.

6883

6884 See :func:`_sql.exists` for a description of usage.

6885

6886 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6887 instance by calling :meth:`_sql.SelectBase.exists`.

6888

6889 """

6890

6891 inherit_cache = True

6892

6893 def __init__(

6894 self,

6895 __argument: Optional[

6896 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6897 ] = None,

6898 ):

6899 s: ScalarSelect[Any]

6900

6901 # TODO: this seems like we should be using coercions for this

6902 if __argument is None:

6903 s = Select(literal_column("*")).scalar_subquery()

6904 elif isinstance(__argument, SelectBase):

6905 s = __argument.scalar_subquery()

6906 s._propagate_attrs = __argument._propagate_attrs

6907 elif isinstance(__argument, ScalarSelect):

6908 s = __argument

6909 else:

6910 s = Select(__argument).scalar_subquery()

6911

6912 UnaryExpression.__init__(

6913 self,

6914 s,

6915 operator=operators.exists,

6916 type_=type_api.BOOLEANTYPE,

6917 )

6918

6919 @util.ro_non_memoized_property

6920 def _from_objects(self) -> List[FromClause]:

6921 return []

6922

6923 def _regroup(

6924 self,

6925 fn: Callable[[Select[Any]], Select[Any]],

6926 ) -> ScalarSelect[Any]:

6927

6928 assert isinstance(self.element, ScalarSelect)

6929 element = self.element.element

6930 if not isinstance(element, Select):

6931 raise exc.InvalidRequestError(

6932 "Can only apply this operation to a plain SELECT construct"

6933 )

6934 new_element = fn(element)

6935

6936 return_value = new_element.scalar_subquery()

6937 return return_value

6938

6939 def select(self) -> Select[Tuple[bool]]:

6940 r"""Return a SELECT of this :class:`_expression.Exists`.

6941

6942 e.g.::

6943

6944 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

6945

6946 This will produce a statement resembling:

6947

6948 .. sourcecode:: sql

6949

6950 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

6951

6952 .. seealso::

6953

6954 :func:`_expression.select` - general purpose

6955 method which allows for arbitrary column lists.

6956

6957 """ # noqa

6958

6959 return Select(self)

6960

6961 def correlate(

6962 self,

6963 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6964 ) -> Self:

6965 """Apply correlation to the subquery noted by this

6966 :class:`_sql.Exists`.

6967

6968 .. seealso::

6969

6970 :meth:`_sql.ScalarSelect.correlate`

6971

6972 """

6973 e = self._clone()

6974 e.element = self._regroup(

6975 lambda element: element.correlate(*fromclauses)

6976 )

6977 return e

6978

6979 def correlate_except(

6980 self,

6981 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6982 ) -> Self:

6983 """Apply correlation to the subquery noted by this

6984 :class:`_sql.Exists`.

6985

6986 .. seealso::

6987

6988 :meth:`_sql.ScalarSelect.correlate_except`

6989

6990 """

6991 e = self._clone()

6992 e.element = self._regroup(

6993 lambda element: element.correlate_except(*fromclauses)

6994 )

6995 return e

6996

6997 def select_from(self, *froms: _FromClauseArgument) -> Self:

6998 """Return a new :class:`_expression.Exists` construct,

6999 applying the given

7000 expression to the :meth:`_expression.Select.select_from`

7001 method of the select

7002 statement contained.

7003

7004 .. note:: it is typically preferable to build a :class:`_sql.Select`

7005 statement first, including the desired WHERE clause, then use the

7006 :meth:`_sql.SelectBase.exists` method to produce an

7007 :class:`_sql.Exists` object at once.

7008

7009 """

7010 e = self._clone()

7011 e.element = self._regroup(lambda element: element.select_from(*froms))

7012 return e

7013

7014 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7015 """Return a new :func:`_expression.exists` construct with the

7016 given expression added to

7017 its WHERE clause, joined to the existing clause via AND, if any.

7018

7019

7020 .. note:: it is typically preferable to build a :class:`_sql.Select`

7021 statement first, including the desired WHERE clause, then use the

7022 :meth:`_sql.SelectBase.exists` method to produce an

7023 :class:`_sql.Exists` object at once.

7024

7025 """

7026 e = self._clone()

7027 e.element = self._regroup(lambda element: element.where(*clause))

7028 return e

7029

7030

7031class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7032 """Wrap a :class:`_expression.TextClause` construct within a

7033 :class:`_expression.SelectBase`

7034 interface.

7035

7036 This allows the :class:`_expression.TextClause` object to gain a

7037 ``.c`` collection

7038 and other FROM-like capabilities such as

7039 :meth:`_expression.FromClause.alias`,

7040 :meth:`_expression.SelectBase.cte`, etc.

7041

7042 The :class:`_expression.TextualSelect` construct is produced via the

7043 :meth:`_expression.TextClause.columns`

7044 method - see that method for details.

7045

7046 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7047 class was renamed

7048 from ``TextAsFrom``, to more correctly suit its role as a

7049 SELECT-oriented object and not a FROM clause.

7050

7051 .. seealso::

7052

7053 :func:`_expression.text`

7054

7055 :meth:`_expression.TextClause.columns` - primary creation interface.

7056

7057 """

7058

7059 __visit_name__ = "textual_select"

7060

7061 _label_style = LABEL_STYLE_NONE

7062

7063 _traverse_internals: _TraverseInternalsType = (

7064 [

7065 ("element", InternalTraversal.dp_clauseelement),

7066 ("column_args", InternalTraversal.dp_clauseelement_list),

7067 ]

7068 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7069 + HasCTE._has_ctes_traverse_internals

7070 )

7071

7072 _is_textual = True

7073

7074 is_text = True

7075 is_select = True

7076

7077 def __init__(

7078 self,

7079 text: TextClause,

7080 columns: List[_ColumnExpressionArgument[Any]],

7081 positional: bool = False,

7082 ) -> None:

7083 self._init(

7084 text,

7085 # convert for ORM attributes->columns, etc

7086 [

7087 coercions.expect(roles.LabeledColumnExprRole, c)

7088 for c in columns

7089 ],

7090 positional,

7091 )

7092

7093 def _init(

7094 self,

7095 text: TextClause,

7096 columns: List[NamedColumn[Any]],

7097 positional: bool = False,

7098 ) -> None:

7099 self.element = text

7100 self.column_args = columns

7101 self.positional = positional

7102

7103 @HasMemoized_ro_memoized_attribute

7104 def selected_columns(

7105 self,

7106 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7107 """A :class:`_expression.ColumnCollection`

7108 representing the columns that

7109 this SELECT statement or similar construct returns in its result set,

7110 not including :class:`_sql.TextClause` constructs.

7111

7112 This collection differs from the :attr:`_expression.FromClause.columns`

7113 collection of a :class:`_expression.FromClause` in that the columns

7114 within this collection cannot be directly nested inside another SELECT

7115 statement; a subquery must be applied first which provides for the

7116 necessary parenthesization required by SQL.

7117

7118 For a :class:`_expression.TextualSelect` construct, the collection

7119 contains the :class:`_expression.ColumnElement` objects that were

7120 passed to the constructor, typically via the

7121 :meth:`_expression.TextClause.columns` method.

7122

7123

7124 .. versionadded:: 1.4

7125

7126 """

7127 return ColumnCollection(

7128 (c.key, c) for c in self.column_args

7129 ).as_readonly()

7130

7131 @util.ro_non_memoized_property

7132 def _all_selected_columns(self) -> _SelectIterable:

7133 return self.column_args

7134

7135 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7136 return self

7137

7138 def _ensure_disambiguated_names(self) -> TextualSelect:

7139 return self

7140

7141 @_generative

7142 def bindparams(

7143 self,

7144 *binds: BindParameter[Any],

7145 **bind_as_values: Any,

7146 ) -> Self:

7147 self.element = self.element.bindparams(*binds, **bind_as_values)

7148 return self

7149

7150 def _generate_fromclause_column_proxies(

7151 self,

7152 fromclause: FromClause,

7153 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7154 primary_key: ColumnSet,

7155 foreign_keys: Set[KeyedColumnElement[Any]],

7156 *,

7157 proxy_compound_columns: Optional[

7158 Iterable[Sequence[ColumnElement[Any]]]

7159 ] = None,

7160 ) -> None:

7161 if TYPE_CHECKING:

7162 assert isinstance(fromclause, Subquery)

7163

7164 if proxy_compound_columns:

7165 columns._populate_separate_keys(

7166 c._make_proxy(

7167 fromclause,

7168 compound_select_cols=extra_cols,

7169 primary_key=primary_key,

7170 foreign_keys=foreign_keys,

7171 )

7172 for c, extra_cols in zip(

7173 self.column_args, proxy_compound_columns

7174 )

7175 )

7176 else:

7177 columns._populate_separate_keys(

7178 c._make_proxy(

7179 fromclause,

7180 primary_key=primary_key,

7181 foreign_keys=foreign_keys,

7182 )

7183 for c in self.column_args

7184 )

7185

7186 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7187 return self.column_args[0].type

7188

7189

7190TextAsFrom = TextualSelect

7191"""Backwards compatibility with the previous name"""

7192

7193

7194class AnnotatedFromClause(Annotated):

7195 def _copy_internals(self, **kw: Any) -> None:

7196 super()._copy_internals(**kw)

7197 if kw.get("ind_cols_on_fromclause", False):

7198 ee = self._Annotated__element # type: ignore

7199

7200 self.c = ee.__class__.c.fget(self) # type: ignore

7201

7202 @util.ro_memoized_property

7203 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7204 """proxy the .c collection of the underlying FromClause.

7205

7206 Originally implemented in 2008 as a simple load of the .c collection

7207 when the annotated construct was created (see d3621ae961a), in modern

7208 SQLAlchemy versions this can be expensive for statements constructed

7209 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7210 it, which works just as well.

7211

7212 Two different use cases seem to require the collection either copied

7213 from the underlying one, or unique to this AnnotatedFromClause.

7214

7215 See test_selectable->test_annotated_corresponding_column

7216

7217 """

7218 ee = self._Annotated__element # type: ignore

7219 return ee.c # type: ignore