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( # type: ignore

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 name = "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

1791class Alias(roles.DMLTableRole, FromClauseAlias):

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

1793

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

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

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

1797

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

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

1800 method available

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

1802

1803 .. seealso::

1804

1805 :meth:`_expression.FromClause.alias`

1806

1807 """

1808

1809 __visit_name__ = "alias"

1810

1811 inherit_cache = True

1812

1813 element: FromClause

1814

1815 @classmethod

1816 def _factory(

1817 cls,

1818 selectable: FromClause,

1819 name: Optional[str] = None,

1820 flat: bool = False,

1821 ) -> NamedFromClause:

1822 return coercions.expect(

1823 roles.FromClauseRole, selectable, allow_select=True

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

1825

1826

1827class TableValuedAlias(LateralFromClause, Alias):

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

1829

1830 This construct provides for a SQL function that returns columns

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

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

1833 method, e.g.:

1834

1835 .. sourcecode:: pycon+sql

1836

1837 >>> from sqlalchemy import select, func

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

1839 ... "value"

1840 ... )

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

1842 {printsql}SELECT anon_1.value

1843 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1844

1845 .. versionadded:: 1.4.0b2

1846

1847 .. seealso::

1848

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

1850

1851 """ # noqa: E501

1852

1853 __visit_name__ = "table_valued_alias"

1854

1855 _supports_derived_columns = True

1856 _render_derived = False

1857 _render_derived_w_types = False

1858 joins_implicitly = False

1859

1860 _traverse_internals: _TraverseInternalsType = [

1861 ("element", InternalTraversal.dp_clauseelement),

1862 ("name", InternalTraversal.dp_anon_name),

1863 ("_tableval_type", InternalTraversal.dp_type),

1864 ("_render_derived", InternalTraversal.dp_boolean),

1865 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1866 ]

1867

1868 def _init(

1869 self,

1870 selectable: Any,

1871 *,

1872 name: Optional[str] = None,

1873 table_value_type: Optional[TableValueType] = None,

1874 joins_implicitly: bool = False,

1875 ) -> None:

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

1877

1878 self.joins_implicitly = joins_implicitly

1879 self._tableval_type = (

1880 type_api.TABLEVALUE

1881 if table_value_type is None

1882 else table_value_type

1883 )

1884

1885 @HasMemoized.memoized_attribute

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

1887 """Return a column expression representing this

1888 :class:`_sql.TableValuedAlias`.

1889

1890 This accessor is used to implement the

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

1892 method for further details.

1893

1894 E.g.:

1895

1896 .. sourcecode:: pycon+sql

1897

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

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

1900

1901 .. seealso::

1902

1903 :meth:`_functions.FunctionElement.column_valued`

1904

1905 """

1906

1907 return TableValuedColumn(self, self._tableval_type)

1908

1909 def alias(

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

1911 ) -> TableValuedAlias:

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

1913

1914 This creates a distinct FROM object that will be distinguished

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

1916

1917 """

1918

1919 tva: TableValuedAlias = TableValuedAlias._construct(

1920 self,

1921 name=name,

1922 table_value_type=self._tableval_type,

1923 joins_implicitly=self.joins_implicitly,

1924 )

1925

1926 if self._render_derived:

1927 tva._render_derived = True

1928 tva._render_derived_w_types = self._render_derived_w_types

1929

1930 return tva

1931

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

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

1934 set, so that it renders as LATERAL.

1935

1936 .. seealso::

1937

1938 :func:`_expression.lateral`

1939

1940 """

1941 tva = self.alias(name=name)

1942 tva._is_lateral = True

1943 return tva

1944

1945 def render_derived(

1946 self,

1947 name: Optional[str] = None,

1948 with_types: bool = False,

1949 ) -> TableValuedAlias:

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

1951

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

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

1954

1955 .. sourcecode:: pycon+sql

1956

1957 >>> print(

1958 ... select(

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

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

1961 ... .render_derived()

1962 ... )

1963 ... )

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

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

1966

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

1968 the alias expression (this syntax currently applies to the

1969 PostgreSQL database):

1970

1971 .. sourcecode:: pycon+sql

1972

1973 >>> print(

1974 ... select(

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

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

1977 ... .render_derived(with_types=True)

1978 ... )

1979 ... )

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

1981 AS anon_1(a INTEGER, b VARCHAR)

1982

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

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

1985

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

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

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

1989

1990 """ # noqa: E501

1991

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

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

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

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

1996 # (just saw it happen on CI)

1997

1998 # construct against original to prevent memory growth

1999 # for repeated generations

2000 new_alias: TableValuedAlias = TableValuedAlias._construct(

2001 self.element,

2002 name=name,

2003 table_value_type=self._tableval_type,

2004 joins_implicitly=self.joins_implicitly,

2005 )

2006 new_alias._render_derived = True

2007 new_alias._render_derived_w_types = with_types

2008 return new_alias

2009

2010

2011class Lateral(FromClauseAlias, LateralFromClause):

2012 """Represent a LATERAL subquery.

2013

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

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

2016 method available

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

2018

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

2020 PostgreSQL versions provide support for this keyword.

2021

2022 .. seealso::

2023

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

2025

2026 """

2027

2028 __visit_name__ = "lateral"

2029 _is_lateral = True

2030

2031 inherit_cache = True

2032

2033 @classmethod

2034 def _factory(

2035 cls,

2036 selectable: Union[SelectBase, _FromClauseArgument],

2037 name: Optional[str] = None,

2038 ) -> LateralFromClause:

2039 return coercions.expect(

2040 roles.FromClauseRole, selectable, explicit_subquery=True

2041 ).lateral(name=name)

2042

2043

2044class TableSample(FromClauseAlias):

2045 """Represent a TABLESAMPLE clause.

2046

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

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

2049 method

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

2051

2052 .. seealso::

2053

2054 :func:`_expression.tablesample`

2055

2056 """

2057

2058 __visit_name__ = "tablesample"

2059

2060 _traverse_internals: _TraverseInternalsType = (

2061 AliasedReturnsRows._traverse_internals

2062 + [

2063 ("sampling", InternalTraversal.dp_clauseelement),

2064 ("seed", InternalTraversal.dp_clauseelement),

2065 ]

2066 )

2067

2068 @classmethod

2069 def _factory(

2070 cls,

2071 selectable: _FromClauseArgument,

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

2073 name: Optional[str] = None,

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

2075 ) -> TableSample:

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

2077 sampling, name=name, seed=seed

2078 )

2079

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

2081 def _init( # type: ignore[override]

2082 self,

2083 selectable: Any,

2084 *,

2085 name: Optional[str] = None,

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

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

2088 ) -> None:

2089 assert sampling is not None

2090 functions = util.preloaded.sql_functions

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

2092 sampling = functions.func.system(sampling)

2093

2094 self.sampling: Function[Any] = sampling

2095 self.seed = seed

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

2097

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

2099 return self.sampling

2100

2101

2102class CTE(

2103 roles.DMLTableRole,

2104 roles.IsCTERole,

2105 Generative,

2106 HasPrefixes,

2107 HasSuffixes,

2108 AliasedReturnsRows,

2109):

2110 """Represent a Common Table Expression.

2111

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

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

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

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

2116 :class:`_sql.Update` and

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

2118 usage details on CTEs.

2119

2120 .. seealso::

2121

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

2123

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

2125

2126 """

2127

2128 __visit_name__ = "cte"

2129

2130 _traverse_internals: _TraverseInternalsType = (

2131 AliasedReturnsRows._traverse_internals

2132 + [

2133 ("_cte_alias", InternalTraversal.dp_clauseelement),

2134 ("_restates", InternalTraversal.dp_clauseelement),

2135 ("recursive", InternalTraversal.dp_boolean),

2136 ("nesting", InternalTraversal.dp_boolean),

2137 ]

2138 + HasPrefixes._has_prefixes_traverse_internals

2139 + HasSuffixes._has_suffixes_traverse_internals

2140 )

2141

2142 element: HasCTE

2143

2144 @classmethod

2145 def _factory(

2146 cls,

2147 selectable: HasCTE,

2148 name: Optional[str] = None,

2149 recursive: bool = False,

2150 ) -> CTE:

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

2152 or Common Table Expression instance.

2153

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

2155

2156 """

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

2158 name=name, recursive=recursive

2159 )

2160

2161 def _init(

2162 self,

2163 selectable: Select[Any],

2164 *,

2165 name: Optional[str] = None,

2166 recursive: bool = False,

2167 nesting: bool = False,

2168 _cte_alias: Optional[CTE] = None,

2169 _restates: Optional[CTE] = None,

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

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

2172 ) -> None:

2173 self.recursive = recursive

2174 self.nesting = nesting

2175 self._cte_alias = _cte_alias

2176 # Keep recursivity reference with union/union_all

2177 self._restates = _restates

2178 if _prefixes:

2179 self._prefixes = _prefixes

2180 if _suffixes:

2181 self._suffixes = _suffixes

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

2183

2184 def _populate_column_collection(

2185 self,

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

2187 primary_key: ColumnSet,

2188 foreign_keys: Set[KeyedColumnElement[Any]],

2189 ) -> None:

2190 if self._cte_alias is not None:

2191 self._cte_alias._generate_fromclause_column_proxies(

2192 self,

2193 columns,

2194 primary_key=primary_key,

2195 foreign_keys=foreign_keys,

2196 )

2197 else:

2198 self.element._generate_fromclause_column_proxies(

2199 self,

2200 columns,

2201 primary_key=primary_key,

2202 foreign_keys=foreign_keys,

2203 )

2204

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

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

2207 :class:`_expression.CTE`.

2208

2209 This method is a CTE-specific specialization of the

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

2211

2212 .. seealso::

2213

2214 :ref:`tutorial_using_aliases`

2215

2216 :func:`_expression.alias`

2217

2218 """

2219 return CTE._construct(

2220 self.element,

2221 name=name,

2222 recursive=self.recursive,

2223 nesting=self.nesting,

2224 _cte_alias=self,

2225 _prefixes=self._prefixes,

2226 _suffixes=self._suffixes,

2227 )

2228

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

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

2231 of the original CTE against the given selectables provided

2232 as positional arguments.

2233

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

2235 UNION.

2236

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

2238

2239 .. seealso::

2240

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

2242

2243 """

2244 assert is_select_statement(

2245 self.element

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

2247

2248 return CTE._construct(

2249 self.element.union(*other),

2250 name=self.name,

2251 recursive=self.recursive,

2252 nesting=self.nesting,

2253 _restates=self,

2254 _prefixes=self._prefixes,

2255 _suffixes=self._suffixes,

2256 )

2257

2258 def union_all(

2259 self, *other: _SelectStatementForCompoundArgument[Any]

2260 ) -> CTE:

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

2262 of the original CTE against the given selectables provided

2263 as positional arguments.

2264

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

2266 UNION.

2267

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

2269

2270 .. seealso::

2271

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

2273

2274 """

2275

2276 assert is_select_statement(

2277 self.element

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

2279

2280 return CTE._construct(

2281 self.element.union_all(*other),

2282 name=self.name,

2283 recursive=self.recursive,

2284 nesting=self.nesting,

2285 _restates=self,

2286 _prefixes=self._prefixes,

2287 _suffixes=self._suffixes,

2288 )

2289

2290 def _get_reference_cte(self) -> CTE:

2291 """

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

2293 Updated CTEs should still refer to the original CTE.

2294 This function returns this reference identifier.

2295 """

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

2297

2298

2299class _CTEOpts(NamedTuple):

2300 nesting: bool

2301

2302

2303class _ColumnsPlusNames(NamedTuple):

2304 required_label_name: Optional[str]

2305 """

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

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

2308 """

2309

2310 proxy_key: Optional[str]

2311 """

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

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

2314 select.selected_columns collection

2315 """

2316

2317 fallback_label_name: Optional[str]

2318 """

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

2320 we have to render a label even though

2321 required_label_name was not given

2322 """

2323

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

2325 """

2326 the ColumnElement itself

2327 """

2328

2329 repeated: bool

2330 """

2331 True if this is a duplicate of a previous column

2332 in the list of columns

2333 """

2334

2335

2336class SelectsRows(ReturnsRows):

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

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

2339

2340 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2341

2342 def _generate_columns_plus_names(

2343 self,

2344 anon_for_dupe_key: bool,

2345 cols: Optional[_SelectIterable] = None,

2346 ) -> List[_ColumnsPlusNames]:

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

2348 the compiler.

2349

2350 This is distinct from the _column_naming_convention generator that's

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

2352 different rules. the collection returned here calls upon the

2353 _column_naming_convention as well.

2354

2355 """

2356

2357 if cols is None:

2358 cols = self._all_selected_columns

2359

2360 key_naming_convention = SelectState._column_naming_convention(

2361 self._label_style

2362 )

2363

2364 names = {}

2365

2366 result: List[_ColumnsPlusNames] = []

2367 result_append = result.append

2368

2369 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2370 label_style_none = self._label_style is LABEL_STYLE_NONE

2371

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

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

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

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

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

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

2378 dedupe_hash = 1

2379

2380 for c in cols:

2381 repeated = False

2382

2383 if not c._render_label_in_columns_clause:

2384 effective_name = required_label_name = fallback_label_name = (

2385 None

2386 )

2387 elif label_style_none:

2388 if TYPE_CHECKING:

2389 assert is_column_element(c)

2390

2391 effective_name = required_label_name = None

2392 fallback_label_name = c._non_anon_label or c._anon_name_label

2393 else:

2394 if TYPE_CHECKING:

2395 assert is_column_element(c)

2396

2397 if table_qualified:

2398 required_label_name = effective_name = (

2399 fallback_label_name

2400 ) = c._tq_label

2401 else:

2402 effective_name = fallback_label_name = c._non_anon_label

2403 required_label_name = None

2404

2405 if effective_name is None:

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

2407 # not need _expression_label but it isn't

2408 # giving us a clue when to use anon_label instead

2409 expr_label = c._expression_label

2410 if expr_label is None:

2411 repeated = c._anon_name_label in names

2412 names[c._anon_name_label] = c

2413 effective_name = required_label_name = None

2414

2415 if repeated:

2416 # here, "required_label_name" is sent as

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

2418 if table_qualified:

2419 fallback_label_name = (

2420 c._dedupe_anon_tq_label_idx(dedupe_hash)

2421 )

2422 dedupe_hash += 1

2423 else:

2424 fallback_label_name = c._dedupe_anon_label_idx(

2425 dedupe_hash

2426 )

2427 dedupe_hash += 1

2428 else:

2429 fallback_label_name = c._anon_name_label

2430 else:

2431 required_label_name = effective_name = (

2432 fallback_label_name

2433 ) = expr_label

2434

2435 if effective_name is not None:

2436 if TYPE_CHECKING:

2437 assert is_column_element(c)

2438

2439 if effective_name in names:

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

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

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

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

2444 # different column under the same name. apply

2445 # disambiguating label

2446 if table_qualified:

2447 required_label_name = fallback_label_name = (

2448 c._anon_tq_label

2449 )

2450 else:

2451 required_label_name = fallback_label_name = (

2452 c._anon_name_label

2453 )

2454

2455 if anon_for_dupe_key and required_label_name in names:

2456 # here, c._anon_tq_label is definitely unique to

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

2458 # this should always be true.

2459 # this is also an infrequent codepath because

2460 # you need two levels of duplication to be here

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

2462

2463 # the column under the disambiguating label is

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

2465 # subsequent occurrences of the column so that the

2466 # original stays non-ambiguous

2467 if table_qualified:

2468 required_label_name = fallback_label_name = (

2469 c._dedupe_anon_tq_label_idx(dedupe_hash)

2470 )

2471 dedupe_hash += 1

2472 else:

2473 required_label_name = fallback_label_name = (

2474 c._dedupe_anon_label_idx(dedupe_hash)

2475 )

2476 dedupe_hash += 1

2477 repeated = True

2478 else:

2479 names[required_label_name] = c

2480 elif anon_for_dupe_key:

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

2482 # label so that the original stays non-ambiguous

2483 if table_qualified:

2484 required_label_name = fallback_label_name = (

2485 c._dedupe_anon_tq_label_idx(dedupe_hash)

2486 )

2487 dedupe_hash += 1

2488 else:

2489 required_label_name = fallback_label_name = (

2490 c._dedupe_anon_label_idx(dedupe_hash)

2491 )

2492 dedupe_hash += 1

2493 repeated = True

2494 else:

2495 names[effective_name] = c

2496

2497 result_append(

2498 _ColumnsPlusNames(

2499 required_label_name,

2500 key_naming_convention(c),

2501 fallback_label_name,

2502 c,

2503 repeated,

2504 )

2505 )

2506

2507 return result

2508

2509

2510class HasCTE(roles.HasCTERole, SelectsRows):

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

2512

2513 _has_ctes_traverse_internals: _TraverseInternalsType = [

2514 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2515 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2516 ]

2517

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

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

2520

2521 @_generative

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

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

2524

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

2526 the parent statement such that they will each be unconditionally

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

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

2529

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

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

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

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

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

2535 statement.

2536

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

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

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

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

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

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

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

2544 larger statement.

2545

2546 E.g.::

2547

2548 from sqlalchemy import table, column, select

2549

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

2551

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

2553

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

2555

2556 Would render:

2557

2558 .. sourcecode:: sql

2559

2560 WITH anon_1 AS (

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

2562 )

2563 SELECT t.c1, t.c2

2564 FROM t

2565

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

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

2568 statement.

2569

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

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

2572

2573 from sqlalchemy import table, column

2574 from sqlalchemy.dialects.postgresql import insert

2575

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

2577

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

2579

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

2581 update_statement = insert_stmt.on_conflict_do_update(

2582 index_elements=[t.c.c1],

2583 set_={

2584 "c1": insert_stmt.excluded.c1,

2585 "c2": insert_stmt.excluded.c2,

2586 },

2587 ).add_cte(delete_statement_cte)

2588

2589 print(update_statement)

2590

2591 The above statement renders as:

2592

2593 .. sourcecode:: sql

2594

2595 WITH deletions AS (

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

2597 )

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

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

2600

2601 .. versionadded:: 1.4.21

2602

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

2604

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

2606

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

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

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

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

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

2612 this statement when this flag is given.

2613

2614 .. versionadded:: 2.0

2615

2616 .. seealso::

2617

2618 :paramref:`.HasCTE.cte.nesting`

2619

2620

2621 """ # noqa: E501

2622 opt = _CTEOpts(nest_here)

2623 for cte in ctes:

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

2625 self._independent_ctes += (cte,)

2626 self._independent_ctes_opts += (opt,)

2627 return self

2628

2629 def cte(

2630 self,

2631 name: Optional[str] = None,

2632 recursive: bool = False,

2633 nesting: bool = False,

2634 ) -> CTE:

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

2636 or Common Table Expression instance.

2637

2638 Common table expressions are a SQL standard whereby SELECT

2639 statements can draw upon secondary statements specified along

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

2641 Special semantics regarding UNION can also be employed to

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

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

2644

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

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

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

2648 CTE rows.

2649

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

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

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

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

2654

2655 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2657 method may be

2658 used to establish these.

2659

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

2661 In particular - MATERIALIZED and NOT MATERIALIZED.

2662

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

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

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

2666 compile time.

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

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

2669 conjunction with UNION ALL in order to derive rows

2670 from those already selected.

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

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

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

2674 :paramref:`.HasCTE.add_cte.nest_here`

2675 parameter may also be used to more carefully

2676 control the exact placement of a particular CTE.

2677

2678 .. versionadded:: 1.4.24

2679

2680 .. seealso::

2681

2682 :meth:`.HasCTE.add_cte`

2683

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

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

2686 as well as additional examples.

2687

2688 Example 1, non recursive::

2689

2690 from sqlalchemy import (

2691 Table,

2692 Column,

2693 String,

2694 Integer,

2695 MetaData,

2696 select,

2697 func,

2698 )

2699

2700 metadata = MetaData()

2701

2702 orders = Table(

2703 "orders",

2704 metadata,

2705 Column("region", String),

2706 Column("amount", Integer),

2707 Column("product", String),

2708 Column("quantity", Integer),

2709 )

2710

2711 regional_sales = (

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

2713 .group_by(orders.c.region)

2714 .cte("regional_sales")

2715 )

2716

2717

2718 top_regions = (

2719 select(regional_sales.c.region)

2720 .where(

2721 regional_sales.c.total_sales

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

2723 )

2724 .cte("top_regions")

2725 )

2726

2727 statement = (

2728 select(

2729 orders.c.region,

2730 orders.c.product,

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

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

2733 )

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

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

2736 )

2737

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

2739

2740 Example 2, WITH RECURSIVE::

2741

2742 from sqlalchemy import (

2743 Table,

2744 Column,

2745 String,

2746 Integer,

2747 MetaData,

2748 select,

2749 func,

2750 )

2751

2752 metadata = MetaData()

2753

2754 parts = Table(

2755 "parts",

2756 metadata,

2757 Column("part", String),

2758 Column("sub_part", String),

2759 Column("quantity", Integer),

2760 )

2761

2762 included_parts = (

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

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

2765 .cte(recursive=True)

2766 )

2767

2768

2769 incl_alias = included_parts.alias()

2770 parts_alias = parts.alias()

2771 included_parts = included_parts.union_all(

2772 select(

2773 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2775 )

2776

2777 statement = select(

2778 included_parts.c.sub_part,

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

2780 ).group_by(included_parts.c.sub_part)

2781

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

2783

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

2785

2786 from datetime import date

2787 from sqlalchemy import (

2788 MetaData,

2789 Table,

2790 Column,

2791 Integer,

2792 Date,

2793 select,

2794 literal,

2795 and_,

2796 exists,

2797 )

2798

2799 metadata = MetaData()

2800

2801 visitors = Table(

2802 "visitors",

2803 metadata,

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

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

2806 Column("count", Integer),

2807 )

2808

2809 # add 5 visitors for the product_id == 1

2810 product_id = 1

2811 day = date.today()

2812 count = 5

2813

2814 update_cte = (

2815 visitors.update()

2816 .where(

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

2818 )

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

2820 .returning(literal(1))

2821 .cte("update_cte")

2822 )

2823

2824 upsert = visitors.insert().from_select(

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

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

2827 ~exists(update_cte.select())

2828 ),

2829 )

2830

2831 connection.execute(upsert)

2832

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

2834

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

2836

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

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

2839 "value_a", nesting=True

2840 )

2841

2842 # Nesting CTEs takes ascendency locally

2843 # over the CTEs at a higher level

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

2845

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

2847

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

2849 shown with inline parameters below as:

2850

2851 .. sourcecode:: sql

2852

2853 WITH

2854 value_a AS

2855 (SELECT 'root' AS n),

2856 value_b AS

2857 (WITH value_a AS

2858 (SELECT 'nesting' AS n)

2859 SELECT value_a.n AS n FROM value_a)

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

2861 FROM value_a, value_b

2862

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

2864 as follows (SQLAlchemy 2.0 and above)::

2865

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

2867

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

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

2870

2871 # Nesting CTEs takes ascendency locally

2872 # over the CTEs at a higher level

2873 value_b = (

2874 select(value_a_nested.c.n)

2875 .add_cte(value_a_nested, nest_here=True)

2876 .cte("value_b")

2877 )

2878

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

2880

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

2882

2883 edge = Table(

2884 "edge",

2885 metadata,

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

2887 Column("left", Integer),

2888 Column("right", Integer),

2889 )

2890

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

2892

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

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

2895 )

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

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

2898 )

2899

2900 subgraph_cte = root_node.union(left_edge, right_edge)

2901

2902 subgraph = select(subgraph_cte)

2903

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

2905

2906 .. sourcecode:: sql

2907

2908 WITH RECURSIVE nodes(node) AS (

2909 SELECT 1 AS node

2910 UNION

2911 SELECT edge."left" AS "left"

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

2913 UNION

2914 SELECT edge."right" AS "right"

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

2916 )

2917 SELECT nodes.node FROM nodes

2918

2919 .. seealso::

2920

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

2922 :meth:`_expression.HasCTE.cte`.

2923

2924 """ # noqa: E501

2925 return CTE._construct(

2926 self, name=name, recursive=recursive, nesting=nesting

2927 )

2928

2929

2930class Subquery(AliasedReturnsRows):

2931 """Represent a subquery of a SELECT.

2932

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

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

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

2936 :class:`_expression.SelectBase` subclass

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

2938 :class:`_expression.CompoundSelect`, and

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

2940 it represents the

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

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

2943

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

2945 :class:`_expression.Alias`

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

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

2948 :class:`_expression.Alias` always

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

2950 :class:`.Subquery`

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

2952

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

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

2955 statement.

2956

2957 """

2958

2959 __visit_name__ = "subquery"

2960

2961 _is_subquery = True

2962

2963 inherit_cache = True

2964

2965 element: SelectBase

2966

2967 @classmethod

2968 def _factory(

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

2970 ) -> Subquery:

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

2972

2973 return coercions.expect(

2974 roles.SelectStatementRole, selectable

2975 ).subquery(name=name)

2976

2977 @util.deprecated(

2978 "1.4",

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

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

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

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

2983 ":func:`_expression.select` "

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

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

2986 )

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

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

2989

2990

2991class FromGrouping(GroupedElement, FromClause):

2992 """Represent a grouping of a FROM clause"""

2993

2994 _traverse_internals: _TraverseInternalsType = [

2995 ("element", InternalTraversal.dp_clauseelement)

2996 ]

2997

2998 element: FromClause

2999

3000 def __init__(self, element: FromClause):

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

3002

3003 @util.ro_non_memoized_property

3004 def columns(

3005 self,

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

3007 return self.element.columns

3008

3009 @util.ro_non_memoized_property

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

3011 return self.element.columns

3012

3013 @property

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

3015 return self.element.primary_key

3016

3017 @property

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

3019 return self.element.foreign_keys

3020

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

3022 return self.element.is_derived_from(fromclause)

3023

3024 def alias(

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

3026 ) -> NamedFromGrouping:

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

3028

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

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

3031

3032 @util.ro_non_memoized_property

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

3034 return self.element._hide_froms

3035

3036 @util.ro_non_memoized_property

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

3038 return self.element._from_objects

3039

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

3041 return {"element": self.element}

3042

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

3044 self.element = state["element"]

3045

3046 if TYPE_CHECKING:

3047

3048 def self_group(

3049 self, against: Optional[OperatorType] = None

3050 ) -> Self: ...

3051

3052

3053class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3055

3056 .. versionadded:: 2.0

3057

3058 """

3059

3060 inherit_cache = True

3061

3062 if TYPE_CHECKING:

3063

3064 def self_group(

3065 self, against: Optional[OperatorType] = None

3066 ) -> Self: ...

3067

3068

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

3070 """Represents a minimal "table" construct.

3071

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

3073 collection of columns, which are typically produced

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

3075

3076 from sqlalchemy import table, column

3077

3078 user = table(

3079 "user",

3080 column("id"),

3081 column("name"),

3082 column("description"),

3083 )

3084

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

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

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

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

3089

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

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

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

3093 It's useful

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

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

3096 is not on hand.

3097

3098 """

3099

3100 __visit_name__ = "table"

3101

3102 _traverse_internals: _TraverseInternalsType = [

3103 (

3104 "columns",

3105 InternalTraversal.dp_fromclause_canonical_column_collection,

3106 ),

3107 ("name", InternalTraversal.dp_string),

3108 ("schema", InternalTraversal.dp_string),

3109 ]

3110

3111 _is_table = True

3112

3113 fullname: str

3114

3115 implicit_returning = False

3116 """:class:`_expression.TableClause`

3117 doesn't support having a primary key or column

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

3119

3120 @util.ro_memoized_property

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

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

3123 return None

3124

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

3126 super().__init__()

3127 self.name = name

3128 self._columns = DedupeColumnCollection()

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

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

3131 for c in columns:

3132 self.append_column(c)

3133

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

3135 if schema is not None:

3136 self.schema = schema

3137 if self.schema is not None:

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

3139 else:

3140 self.fullname = self.name

3141 if kw:

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

3143

3144 if TYPE_CHECKING:

3145

3146 @util.ro_non_memoized_property

3147 def columns(

3148 self,

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

3150

3151 @util.ro_non_memoized_property

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

3153

3154 def __str__(self) -> str:

3155 if self.schema is not None:

3156 return self.schema + "." + self.name

3157 else:

3158 return self.name

3159

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

3161 pass

3162

3163 @util.ro_memoized_property

3164 def description(self) -> str:

3165 return self.name

3166

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

3168 existing = c.table

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

3170 raise exc.ArgumentError(

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

3172 % (c.key, existing)

3173 )

3174

3175 self._columns.add(c)

3176 c.table = self

3177

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

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

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

3181 :class:`_expression.TableClause`.

3182

3183 E.g.::

3184

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

3186

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

3188

3189 """

3190

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

3192

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

3194 def update(self) -> Update:

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

3196 :class:`_expression.TableClause`.

3197

3198 E.g.::

3199

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

3201

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

3203

3204 """

3205 return util.preloaded.sql_dml.Update(

3206 self,

3207 )

3208

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

3210 def delete(self) -> Delete:

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

3212 :class:`_expression.TableClause`.

3213

3214 E.g.::

3215

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

3217

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

3219

3220 """

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

3222

3223 @util.ro_non_memoized_property

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

3225 return [self]

3226

3227

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

3229

3230

3231class ForUpdateArg(ClauseElement):

3232 _traverse_internals: _TraverseInternalsType = [

3233 ("of", InternalTraversal.dp_clauseelement_list),

3234 ("nowait", InternalTraversal.dp_boolean),

3235 ("read", InternalTraversal.dp_boolean),

3236 ("skip_locked", InternalTraversal.dp_boolean),

3237 ("key_share", InternalTraversal.dp_boolean),

3238 ]

3239

3240 of: Optional[Sequence[ClauseElement]]

3241 nowait: bool

3242 read: bool

3243 skip_locked: bool

3244

3245 @classmethod

3246 def _from_argument(

3247 cls, with_for_update: ForUpdateParameter

3248 ) -> Optional[ForUpdateArg]:

3249 if isinstance(with_for_update, ForUpdateArg):

3250 return with_for_update

3251 elif with_for_update in (None, False):

3252 return None

3253 elif with_for_update is True:

3254 return ForUpdateArg()

3255 else:

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

3257

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

3259 return (

3260 isinstance(other, ForUpdateArg)

3261 and other.nowait == self.nowait

3262 and other.read == self.read

3263 and other.skip_locked == self.skip_locked

3264 and other.key_share == self.key_share

3265 and other.of is self.of

3266 )

3267

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

3269 return not self.__eq__(other)

3270

3271 def __hash__(self) -> int:

3272 return id(self)

3273

3274 def __init__(

3275 self,

3276 *,

3277 nowait: bool = False,

3278 read: bool = False,

3279 of: Optional[_ForUpdateOfArgument] = None,

3280 skip_locked: bool = False,

3281 key_share: bool = False,

3282 ):

3283 """Represents arguments specified to

3284 :meth:`_expression.Select.for_update`.

3285

3286 """

3287

3288 self.nowait = nowait

3289 self.read = read

3290 self.skip_locked = skip_locked

3291 self.key_share = key_share

3292 if of is not None:

3293 self.of = [

3294 coercions.expect(roles.ColumnsClauseRole, elem)

3295 for elem in util.to_list(of)

3296 ]

3297 else:

3298 self.of = None

3299

3300

3301class Values(roles.InElementRole, Generative, LateralFromClause):

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

3303 in a statement.

3304

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

3306 :func:`_expression.values` function.

3307

3308 .. versionadded:: 1.4

3309

3310 """

3311

3312 __visit_name__ = "values"

3313

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

3315

3316 _unnamed: bool

3317 _traverse_internals: _TraverseInternalsType = [

3318 ("_column_args", InternalTraversal.dp_clauseelement_list),

3319 ("_data", InternalTraversal.dp_dml_multi_values),

3320 ("name", InternalTraversal.dp_string),

3321 ("literal_binds", InternalTraversal.dp_boolean),

3322 ]

3323

3324 def __init__(

3325 self,

3326 *columns: ColumnClause[Any],

3327 name: Optional[str] = None,

3328 literal_binds: bool = False,

3329 ):

3330 super().__init__()

3331 self._column_args = columns

3332

3333 if name is None:

3334 self._unnamed = True

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

3336 else:

3337 self._unnamed = False

3338 self.name = name

3339 self.literal_binds = literal_binds

3340 self.named_with_column = not self._unnamed

3341

3342 @property

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

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

3345

3346 @_generative

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

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

3349 construct that is a copy of this

3350 one with the given name.

3351

3352 This method is a VALUES-specific specialization of the

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

3354

3355 .. seealso::

3356

3357 :ref:`tutorial_using_aliases`

3358

3359 :func:`_expression.alias`

3360

3361 """

3362 non_none_name: str

3363

3364 if name is None:

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

3366 else:

3367 non_none_name = name

3368

3369 self.name = non_none_name

3370 self.named_with_column = True

3371 self._unnamed = False

3372 return self

3373

3374 @_generative

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

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

3377 so that

3378 it renders as LATERAL.

3379

3380 .. seealso::

3381

3382 :func:`_expression.lateral`

3383

3384 """

3385 non_none_name: str

3386

3387 if name is None:

3388 non_none_name = self.name

3389 else:

3390 non_none_name = name

3391

3392 self._is_lateral = True

3393 self.name = non_none_name

3394 self._unnamed = False

3395 return self

3396

3397 @_generative

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

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

3400 adding the given data to the data list.

3401

3402 E.g.::

3403

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

3405

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

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

3408 constructor.

3409

3410 """

3411

3412 self._data += (values,)

3413 return self

3414

3415 def scalar_values(self) -> ScalarValues:

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

3417 COLUMN element in a statement.

3418

3419 .. versionadded:: 2.0.0b4

3420

3421 """

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

3423

3424 def _populate_column_collection(

3425 self,

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

3427 primary_key: ColumnSet,

3428 foreign_keys: Set[KeyedColumnElement[Any]],

3429 ) -> None:

3430 for c in self._column_args:

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

3432 _, c = c._make_proxy(

3433 self, primary_key=primary_key, foreign_keys=foreign_keys

3434 )

3435 else:

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

3437 # no memoizations of other FROM clauses.

3438 # see test_values.py -> test_auto_proxy_select_direct_col

3439 c._reset_memoizations()

3440 columns.add(c)

3441 c.table = self

3442

3443 @util.ro_non_memoized_property

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

3445 return [self]

3446

3447

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

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

3450 COLUMN element in a statement.

3451

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

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

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

3455 an ``IN`` or ``NOT IN`` condition.

3456

3457 .. versionadded:: 2.0.0b4

3458

3459 """

3460

3461 __visit_name__ = "scalar_values"

3462

3463 _traverse_internals: _TraverseInternalsType = [

3464 ("_column_args", InternalTraversal.dp_clauseelement_list),

3465 ("_data", InternalTraversal.dp_dml_multi_values),

3466 ("literal_binds", InternalTraversal.dp_boolean),

3467 ]

3468

3469 def __init__(

3470 self,

3471 columns: Sequence[ColumnClause[Any]],

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

3473 literal_binds: bool,

3474 ):

3475 super().__init__()

3476 self._column_args = columns

3477 self._data = data

3478 self.literal_binds = literal_binds

3479

3480 @property

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

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

3483

3484 def __clause_element__(self) -> ScalarValues:

3485 return self

3486

3487 if TYPE_CHECKING:

3488

3489 def self_group(

3490 self, against: Optional[OperatorType] = None

3491 ) -> Self: ...

3492

3493

3494class SelectBase(

3495 roles.SelectStatementRole,

3496 roles.DMLSelectRole,

3497 roles.CompoundElementRole,

3498 roles.InElementRole,

3499 HasCTE,

3500 SupportsCloneAnnotations,

3501 Selectable,

3502):

3503 """Base class for SELECT statements.

3504

3505

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

3507 :class:`_expression.CompoundSelect` and

3508 :class:`_expression.TextualSelect`.

3509

3510

3511 """

3512

3513 _is_select_base = True

3514 is_select = True

3515

3516 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3517

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

3519 self._reset_memoizations()

3520

3521 @util.ro_non_memoized_property

3522 def selected_columns(

3523 self,

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

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

3526 representing the columns that

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

3528

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

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

3531 within this collection cannot be directly nested inside another SELECT

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

3533 necessary parenthesization required by SQL.

3534

3535 .. note::

3536

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

3538 include expressions established in the columns clause using the

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

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

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

3542 construct.

3543

3544 .. seealso::

3545

3546 :attr:`_sql.Select.selected_columns`

3547

3548 .. versionadded:: 1.4

3549

3550 """

3551 raise NotImplementedError()

3552

3553 def _generate_fromclause_column_proxies(

3554 self,

3555 subquery: FromClause,

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

3557 primary_key: ColumnSet,

3558 foreign_keys: Set[KeyedColumnElement[Any]],

3559 *,

3560 proxy_compound_columns: Optional[

3561 Iterable[Sequence[ColumnElement[Any]]]

3562 ] = None,

3563 ) -> None:

3564 raise NotImplementedError()

3565

3566 @util.ro_non_memoized_property

3567 def _all_selected_columns(self) -> _SelectIterable:

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

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

3570 constructs.

3571

3572 .. versionadded:: 1.4.12

3573

3574 .. seealso::

3575

3576 :attr:`_sql.SelectBase.exported_columns`

3577

3578 """

3579 raise NotImplementedError()

3580

3581 @property

3582 def exported_columns(

3583 self,

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

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

3586 that represents the "exported"

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

3588 :class:`_sql.TextClause` constructs.

3589

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

3591 object are synonymous

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

3593

3594 .. versionadded:: 1.4

3595

3596 .. seealso::

3597

3598 :attr:`_expression.Select.exported_columns`

3599

3600 :attr:`_expression.Selectable.exported_columns`

3601

3602 :attr:`_expression.FromClause.exported_columns`

3603

3604

3605 """

3606 return self.selected_columns.as_readonly()

3607

3608 @property

3609 @util.deprecated(

3610 "1.4",

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

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

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

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

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

3616 "first in order to create "

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

3618 "columns that this SELECT object SELECTs "

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

3620 "attribute.",

3621 )

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

3623 return self._implicit_subquery.columns

3624

3625 @property

3626 def columns(

3627 self,

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

3629 return self.c

3630

3631 def get_label_style(self) -> SelectLabelStyle:

3632 """

3633 Retrieve the current label style.

3634

3635 Implemented by subclasses.

3636

3637 """

3638 raise NotImplementedError()

3639

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

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

3642

3643 Implemented by subclasses.

3644

3645 """

3646

3647 raise NotImplementedError()

3648

3649 @util.deprecated(

3650 "1.4",

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

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

3653 "creates a subquery that should be explicit. "

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

3655 "first in order to create "

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

3657 )

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

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

3660

3661 @HasMemoized.memoized_attribute

3662 def _implicit_subquery(self) -> Subquery:

3663 return self.subquery()

3664

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

3666 raise NotImplementedError()

3667

3668 @util.deprecated(

3669 "1.4",

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

3671 "method is deprecated and will be "

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

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

3674 )

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

3676 return self.scalar_subquery()

3677

3678 def exists(self) -> Exists:

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

3680 which can be used as a column expression.

3681

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

3683

3684 .. seealso::

3685

3686 :func:`_sql.exists`

3687

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

3689

3690 .. versionadded:: 1.4

3691

3692 """

3693 return Exists(self)

3694

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

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

3697 used as a column expression.

3698

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

3700

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

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

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

3704 an enclosing SELECT.

3705

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

3707 subquery that can be produced using the

3708 :meth:`_expression.SelectBase.subquery`

3709 method.

3710

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

3712 :meth:`_expression.SelectBase.scalar_subquery`.

3713

3714 .. seealso::

3715

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

3717

3718 """

3719 if self._label_style is not LABEL_STYLE_NONE:

3720 self = self.set_label_style(LABEL_STYLE_NONE)

3721

3722 return ScalarSelect(self)

3723

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

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

3726 subquery with a label.

3727

3728 .. seealso::

3729

3730 :meth:`_expression.SelectBase.scalar_subquery`.

3731

3732 """

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

3734

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

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

3737

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

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

3740

3741 .. seealso::

3742

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

3744

3745 """

3746 return Lateral._factory(self, name)

3747

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

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

3750

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

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

3753 SELECT statement.

3754

3755 Given a SELECT statement such as::

3756

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

3758

3759 The above statement might look like:

3760

3761 .. sourcecode:: sql

3762

3763 SELECT table.id, table.name FROM table

3764

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

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

3767 a named sub-element::

3768

3769 subq = stmt.subquery()

3770 new_stmt = select(subq)

3771

3772 The above renders as:

3773

3774 .. sourcecode:: sql

3775

3776 SELECT anon_1.id, anon_1.name

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

3778

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

3780 is equivalent to calling

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

3782 method on a FROM object; however,

3783 as a :class:`_expression.SelectBase`

3784 object is not directly FROM object,

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

3786 method provides clearer semantics.

3787

3788 .. versionadded:: 1.4

3789

3790 """

3791

3792 return Subquery._construct(

3793 self._ensure_disambiguated_names(), name=name

3794 )

3795

3796 def _ensure_disambiguated_names(self) -> Self:

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

3798 disambiguated in some way, if possible.

3799

3800 """

3801

3802 raise NotImplementedError()

3803

3804 def alias(

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

3806 ) -> Subquery:

3807 """Return a named subquery against this

3808 :class:`_expression.SelectBase`.

3809

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

3811 :class:`_expression.FromClause`),

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

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

3814 :class:`_expression.FromClause`.

3815

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

3817 method is now

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

3819

3820 """

3821 return self.subquery(name=name)

3822

3823

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

3825

3826

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

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

3829

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

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

3832 compound selects.

3833

3834 """

3835

3836 __visit_name__ = "select_statement_grouping"

3837 _traverse_internals: _TraverseInternalsType = [

3838 ("element", InternalTraversal.dp_clauseelement)

3839 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3840

3841 _is_select_container = True

3842

3843 element: _SB

3844

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

3846 self.element = cast(

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

3848 )

3849

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

3851 new_element = self.element._ensure_disambiguated_names()

3852 if new_element is not self.element:

3853 return SelectStatementGrouping(new_element)

3854 else:

3855 return self

3856

3857 def get_label_style(self) -> SelectLabelStyle:

3858 return self.element.get_label_style()

3859

3860 def set_label_style(

3861 self, label_style: SelectLabelStyle

3862 ) -> SelectStatementGrouping[_SB]:

3863 return SelectStatementGrouping(

3864 self.element.set_label_style(label_style)

3865 )

3866

3867 @property

3868 def select_statement(self) -> _SB:

3869 return self.element

3870

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

3872 return self

3873

3874 if TYPE_CHECKING:

3875

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

3877

3878 # def _generate_columns_plus_names(

3879 # self, anon_for_dupe_key: bool

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

3881 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3882

3883 def _generate_fromclause_column_proxies(

3884 self,

3885 subquery: FromClause,

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

3887 primary_key: ColumnSet,

3888 foreign_keys: Set[KeyedColumnElement[Any]],

3889 *,

3890 proxy_compound_columns: Optional[

3891 Iterable[Sequence[ColumnElement[Any]]]

3892 ] = None,

3893 ) -> None:

3894 self.element._generate_fromclause_column_proxies(

3895 subquery,

3896 columns,

3897 proxy_compound_columns=proxy_compound_columns,

3898 primary_key=primary_key,

3899 foreign_keys=foreign_keys,

3900 )

3901

3902 @util.ro_non_memoized_property

3903 def _all_selected_columns(self) -> _SelectIterable:

3904 return self.element._all_selected_columns

3905

3906 @util.ro_non_memoized_property

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

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

3909 representing the columns that

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

3911 :class:`_sql.TextClause` constructs.

3912

3913 .. versionadded:: 1.4

3914

3915 .. seealso::

3916

3917 :attr:`_sql.Select.selected_columns`

3918

3919 """

3920 return self.element.selected_columns

3921

3922 @util.ro_non_memoized_property

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

3924 return self.element._from_objects

3925

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

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

3928 raise NotImplementedError

3929

3930

3931class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3933 added.

3934

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

3936 :class:`_expression.CompoundSelect`

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

3938 rendering can be controlled. Compare to

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

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

3941 and is also a SELECT construct,

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

3943 only wrapped as a subquery.

3944

3945 """

3946

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

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

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

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

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

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

3953 _for_update_arg: Optional[ForUpdateArg] = None

3954

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

3956 self._label_style = _label_style

3957

3958 @_generative

3959 def with_for_update(

3960 self,

3961 *,

3962 nowait: bool = False,

3963 read: bool = False,

3964 of: Optional[_ForUpdateOfArgument] = None,

3965 skip_locked: bool = False,

3966 key_share: bool = False,

3967 ) -> Self:

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

3969 :class:`_expression.GenerativeSelect`.

3970

3971 E.g.::

3972

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

3974

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

3976 render a statement like:

3977

3978 .. sourcecode:: sql

3979

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

3981

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

3983 would produce:

3984

3985 .. sourcecode:: sql

3986

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

3988

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

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

3991 provided which allow for common database-specific

3992 variants.

3993

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

3995 Database and PostgreSQL dialects.

3996

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

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

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

4000

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

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

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

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

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

4006 table or as a column depending on backend.

4007

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

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

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

4011

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

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

4014 on the PostgreSQL dialect.

4015

4016 """

4017 self._for_update_arg = ForUpdateArg(

4018 nowait=nowait,

4019 read=read,

4020 of=of,

4021 skip_locked=skip_locked,

4022 key_share=key_share,

4023 )

4024 return self

4025

4026 def get_label_style(self) -> SelectLabelStyle:

4027 """

4028 Retrieve the current label style.

4029

4030 .. versionadded:: 1.4

4031

4032 """

4033 return self._label_style

4034

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

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

4037

4038 There are three "label styles" available,

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

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

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

4042 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4043

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

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

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

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

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

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

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

4051 that the impact of this labeling is minimal.

4052

4053 The rationale for disambiguation is mostly so that all column

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

4055 collection when a subquery is created.

4056

4057 .. versionadded:: 1.4 - the

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

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

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

4061

4062 .. seealso::

4063

4064 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4065

4066 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4067

4068 :data:`_sql.LABEL_STYLE_NONE`

4069

4070 :data:`_sql.LABEL_STYLE_DEFAULT`

4071

4072 """

4073 if self._label_style is not style:

4074 self = self._generate()

4075 self._label_style = style

4076 return self

4077

4078 @property

4079 def _group_by_clause(self) -> ClauseList:

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

4081 return ClauseList._construct_raw(

4082 operators.comma_op, self._group_by_clauses

4083 )

4084

4085 @property

4086 def _order_by_clause(self) -> ClauseList:

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

4088 return ClauseList._construct_raw(

4089 operators.comma_op, self._order_by_clauses

4090 )

4091

4092 def _offset_or_limit_clause(

4093 self,

4094 element: _LimitOffsetType,

4095 name: Optional[str] = None,

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

4097 ) -> ColumnElement[Any]:

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

4099

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

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

4102

4103 """

4104 return coercions.expect(

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

4106 )

4107

4108 @overload

4109 def _offset_or_limit_clause_asint(

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

4111 ) -> NoReturn: ...

4112

4113 @overload

4114 def _offset_or_limit_clause_asint(

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

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

4117

4118 def _offset_or_limit_clause_asint(

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

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

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

4122 integer.

4123

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

4125 parameter. Otherwise, a compilation error is raised.

4126

4127 """

4128 if clause is None:

4129 return None

4130 try:

4131 value = clause._limit_offset_value

4132 except AttributeError as err:

4133 raise exc.CompileError(

4134 "This SELECT structure does not use a simple "

4135 "integer value for %s" % attrname

4136 ) from err

4137 else:

4138 return util.asint(value)

4139

4140 @property

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

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

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

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

4145 isn't currently set to an integer.

4146

4147 """

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

4149

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

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

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

4153 """

4154 return isinstance(clause, _OffsetLimitParam)

4155

4156 @property

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

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

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

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

4161 offset isn't currently set to an integer.

4162

4163 """

4164 return self._offset_or_limit_clause_asint(

4165 self._offset_clause, "offset"

4166 )

4167

4168 @property

4169 def _has_row_limiting_clause(self) -> bool:

4170 return (

4171 self._limit_clause is not None

4172 or self._offset_clause is not None

4173 or self._fetch_clause is not None

4174 )

4175

4176 @_generative

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

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

4179 applied.

4180

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

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

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

4184 functionality.

4185

4186 .. note::

4187

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

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

4190

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

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

4193

4194 .. seealso::

4195

4196 :meth:`_sql.GenerativeSelect.fetch`

4197

4198 :meth:`_sql.GenerativeSelect.offset`

4199

4200 """

4201

4202 self._fetch_clause = self._fetch_clause_options = None

4203 self._limit_clause = self._offset_or_limit_clause(limit)

4204 return self

4205

4206 @_generative

4207 def fetch(

4208 self,

4209 count: _LimitOffsetType,

4210 with_ties: bool = False,

4211 percent: bool = False,

4212 **dialect_kw: Any,

4213 ) -> Self:

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

4215 applied.

4216

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

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

4219 select. This functionality is is currently implemented for Oracle

4220 Database, PostgreSQL, MSSQL.

4221

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

4223

4224 .. note::

4225

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

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

4228

4229 .. versionadded:: 1.4

4230

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

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

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

4234 Pass ``None`` to reset it.

4235

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

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

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

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

4240

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

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

4243

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

4245 may be accepted by dialects.

4246

4247 .. versionadded:: 2.0.41

4248

4249 .. seealso::

4250

4251 :meth:`_sql.GenerativeSelect.limit`

4252

4253 :meth:`_sql.GenerativeSelect.offset`

4254

4255 """

4256 self._validate_dialect_kwargs(dialect_kw)

4257 self._limit_clause = None

4258 if count is None:

4259 self._fetch_clause = self._fetch_clause_options = None

4260 else:

4261 self._fetch_clause = self._offset_or_limit_clause(count)

4262 self._fetch_clause_options = {

4263 "with_ties": with_ties,

4264 "percent": percent,

4265 }

4266 return self

4267

4268 @_generative

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

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

4271 applied.

4272

4273

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

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

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

4277 functionality.

4278

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

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

4281

4282 .. seealso::

4283

4284 :meth:`_sql.GenerativeSelect.limit`

4285

4286 :meth:`_sql.GenerativeSelect.fetch`

4287

4288 """

4289

4290 self._offset_clause = self._offset_or_limit_clause(offset)

4291 return self

4292

4293 @_generative

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

4295 def slice(

4296 self,

4297 start: int,

4298 stop: int,

4299 ) -> Self:

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

4301

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

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

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

4305 query.

4306

4307 For example, ::

4308

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

4310

4311 renders as

4312

4313 .. sourcecode:: sql

4314

4315 SELECT users.id AS users_id,

4316 users.name AS users_name

4317 FROM users ORDER BY users.id

4318 LIMIT ? OFFSET ?

4319 (2, 1)

4320

4321 .. note::

4322

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

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

4325

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

4327 method generalized from the ORM.

4328

4329 .. seealso::

4330

4331 :meth:`_sql.GenerativeSelect.limit`

4332

4333 :meth:`_sql.GenerativeSelect.offset`

4334

4335 :meth:`_sql.GenerativeSelect.fetch`

4336

4337 """

4338 sql_util = util.preloaded.sql_util

4339 self._fetch_clause = self._fetch_clause_options = None

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

4341 self._limit_clause, self._offset_clause, start, stop

4342 )

4343 return self

4344

4345 @_generative

4346 def order_by(

4347 self,

4348 __first: Union[

4349 Literal[None, _NoArg.NO_ARG],

4350 _ColumnExpressionOrStrLabelArgument[Any],

4351 ] = _NoArg.NO_ARG,

4352 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4353 ) -> Self:

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

4355 criteria applied.

4356

4357 e.g.::

4358

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

4360

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

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

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

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

4365

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

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

4368

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

4370 constructs

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

4372

4373 .. seealso::

4374

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

4376

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

4378

4379 """

4380

4381 if not clauses and __first is None:

4382 self._order_by_clauses = ()

4383 elif __first is not _NoArg.NO_ARG:

4384 self._order_by_clauses += tuple(

4385 coercions.expect(

4386 roles.OrderByRole, clause, apply_propagate_attrs=self

4387 )

4388 for clause in (__first,) + clauses

4389 )

4390 return self

4391

4392 @_generative

4393 def group_by(

4394 self,

4395 __first: Union[

4396 Literal[None, _NoArg.NO_ARG],

4397 _ColumnExpressionOrStrLabelArgument[Any],

4398 ] = _NoArg.NO_ARG,

4399 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4400 ) -> Self:

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

4402 criterion applied.

4403

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

4405

4406 e.g.::

4407

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

4409

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

4411 constructs

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

4413

4414 .. seealso::

4415

4416 :ref:`tutorial_group_by_w_aggregates` - in the

4417 :ref:`unified_tutorial`

4418

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

4420

4421 """ # noqa: E501

4422

4423 if not clauses and __first is None:

4424 self._group_by_clauses = ()

4425 elif __first is not _NoArg.NO_ARG:

4426 self._group_by_clauses += tuple(

4427 coercions.expect(

4428 roles.GroupByRole, clause, apply_propagate_attrs=self

4429 )

4430 for clause in (__first,) + clauses

4431 )

4432 return self

4433

4434

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

4436class CompoundSelectState(CompileState):

4437 @util.memoized_property

4438 def _label_resolve_dict(

4439 self,

4440 ) -> Tuple[

4441 Dict[str, ColumnElement[Any]],

4442 Dict[str, ColumnElement[Any]],

4443 Dict[str, ColumnElement[Any]],

4444 ]:

4445 # TODO: this is hacky and slow

4446 hacky_subquery = self.statement.subquery()

4447 hacky_subquery.named_with_column = False

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

4449 return d, d, d

4450

4451

4452class _CompoundSelectKeyword(Enum):

4453 UNION = "UNION"

4454 UNION_ALL = "UNION ALL"

4455 EXCEPT = "EXCEPT"

4456 EXCEPT_ALL = "EXCEPT ALL"

4457 INTERSECT = "INTERSECT"

4458 INTERSECT_ALL = "INTERSECT ALL"

4459

4460

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

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

4463 SELECT-based set operations.

4464

4465

4466 .. seealso::

4467

4468 :func:`_expression.union`

4469

4470 :func:`_expression.union_all`

4471

4472 :func:`_expression.intersect`

4473

4474 :func:`_expression.intersect_all`

4475

4476 :func:`_expression.except`

4477

4478 :func:`_expression.except_all`

4479

4480 """

4481

4482 __visit_name__ = "compound_select"

4483

4484 _traverse_internals: _TraverseInternalsType = (

4485 [

4486 ("selects", InternalTraversal.dp_clauseelement_list),

4487 ("_limit_clause", InternalTraversal.dp_clauseelement),

4488 ("_offset_clause", InternalTraversal.dp_clauseelement),

4489 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4490 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4491 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4492 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4493 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4494 ("keyword", InternalTraversal.dp_string),

4495 ]

4496 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4497 + HasCTE._has_ctes_traverse_internals

4498 + DialectKWArgs._dialect_kwargs_traverse_internals

4499 )

4500

4501 selects: List[SelectBase]

4502

4503 _is_from_container = True

4504 _auto_correlate = False

4505

4506 def __init__(

4507 self,

4508 keyword: _CompoundSelectKeyword,

4509 *selects: _SelectStatementForCompoundArgument[_TP],

4510 ):

4511 self.keyword = keyword

4512 self.selects = [

4513 coercions.expect(

4514 roles.CompoundElementRole, s, apply_propagate_attrs=self

4515 ).self_group(against=self)

4516 for s in selects

4517 ]

4518

4519 GenerativeSelect.__init__(self)

4520

4521 @classmethod

4522 def _create_union(

4523 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4524 ) -> CompoundSelect[_TP]:

4525 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4526

4527 @classmethod

4528 def _create_union_all(

4529 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4530 ) -> CompoundSelect[_TP]:

4531 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4532

4533 @classmethod

4534 def _create_except(

4535 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4536 ) -> CompoundSelect[_TP]:

4537 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4538

4539 @classmethod

4540 def _create_except_all(

4541 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4542 ) -> CompoundSelect[_TP]:

4543 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4544

4545 @classmethod

4546 def _create_intersect(

4547 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4548 ) -> CompoundSelect[_TP]:

4549 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4550

4551 @classmethod

4552 def _create_intersect_all(

4553 cls, *selects: _SelectStatementForCompoundArgument[_TP]

4554 ) -> CompoundSelect[_TP]:

4555 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4556

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

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

4559

4560 def self_group(

4561 self, against: Optional[OperatorType] = None

4562 ) -> GroupedElement:

4563 return SelectStatementGrouping(self)

4564

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

4566 for s in self.selects:

4567 if s.is_derived_from(fromclause):

4568 return True

4569 return False

4570

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

4572 if self._label_style is not style:

4573 self = self._generate()

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

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

4576

4577 return self

4578

4579 def _ensure_disambiguated_names(self) -> Self:

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

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

4582 self = self._generate()

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

4584

4585 return self

4586

4587 def _generate_fromclause_column_proxies(

4588 self,

4589 subquery: FromClause,

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

4591 primary_key: ColumnSet,

4592 foreign_keys: Set[KeyedColumnElement[Any]],

4593 *,

4594 proxy_compound_columns: Optional[

4595 Iterable[Sequence[ColumnElement[Any]]]

4596 ] = None,

4597 ) -> None:

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

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

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

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

4602 # generate below.

4603 select_0 = self.selects[0]

4604

4605 if self._label_style is not LABEL_STYLE_DEFAULT:

4606 select_0 = select_0.set_label_style(self._label_style)

4607

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

4609 # derived columns place a 'weight' annotation corresponding

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

4611 # that the corresponding_column() operation can resolve

4612 # conflicts

4613 extra_col_iterator = zip(

4614 *[

4615 [

4616 c._annotate(dd)

4617 for c in stmt._all_selected_columns

4618 if is_column_element(c)

4619 ]

4620 for dd, stmt in [

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

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

4623 ]

4624 ]

4625 )

4626

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

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

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

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

4631 # compound

4632 select_0._generate_fromclause_column_proxies(

4633 subquery,

4634 columns,

4635 proxy_compound_columns=extra_col_iterator,

4636 primary_key=primary_key,

4637 foreign_keys=foreign_keys,

4638 )

4639

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

4641 super()._refresh_for_new_column(column)

4642 for select in self.selects:

4643 select._refresh_for_new_column(column)

4644

4645 @util.ro_non_memoized_property

4646 def _all_selected_columns(self) -> _SelectIterable:

4647 return self.selects[0]._all_selected_columns

4648

4649 @util.ro_non_memoized_property

4650 def selected_columns(

4651 self,

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

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

4654 representing the columns that

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

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

4657

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

4659 :attr:`_expression.CompoundSelect.selected_columns`

4660 attribute returns the selected

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

4662 statements within the set operation.

4663

4664 .. seealso::

4665

4666 :attr:`_sql.Select.selected_columns`

4667

4668 .. versionadded:: 1.4

4669

4670 """

4671 return self.selects[0].selected_columns

4672

4673

4674# backwards compat

4675for elem in _CompoundSelectKeyword:

4676 setattr(CompoundSelect, elem.name, elem)

4677

4678

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

4680class SelectState(util.MemoizedSlots, CompileState):

4681 __slots__ = (

4682 "from_clauses",

4683 "froms",

4684 "columns_plus_names",

4685 "_label_resolve_dict",

4686 )

4687

4688 if TYPE_CHECKING:

4689 default_select_compile_options: CacheableOptions

4690 else:

4691

4692 class default_select_compile_options(CacheableOptions):

4693 _cache_key_traversal = []

4694

4695 if TYPE_CHECKING:

4696

4697 @classmethod

4698 def get_plugin_class(

4699 cls, statement: Executable

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

4701

4702 def __init__(

4703 self,

4704 statement: Select[Any],

4705 compiler: SQLCompiler,

4706 **kw: Any,

4707 ):

4708 self.statement = statement

4709 self.from_clauses = statement._from_obj

4710

4711 for memoized_entities in statement._memoized_select_entities:

4712 self._setup_joins(

4713 memoized_entities._setup_joins, memoized_entities._raw_columns

4714 )

4715

4716 if statement._setup_joins:

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

4718

4719 self.froms = self._get_froms(statement)

4720

4721 self.columns_plus_names = statement._generate_columns_plus_names(True)

4722

4723 @classmethod

4724 def _plugin_not_implemented(cls) -> NoReturn:

4725 raise NotImplementedError(

4726 "The default SELECT construct without plugins does not "

4727 "implement this method."

4728 )

4729

4730 @classmethod

4731 def get_column_descriptions(

4732 cls, statement: Select[Any]

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

4734 return [

4735 {

4736 "name": name,

4737 "type": element.type,

4738 "expr": element,

4739 }

4740 for _, name, _, element, _ in (

4741 statement._generate_columns_plus_names(False)

4742 )

4743 ]

4744

4745 @classmethod

4746 def from_statement(

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

4748 ) -> ExecutableReturnsRows:

4749 cls._plugin_not_implemented()

4750

4751 @classmethod

4752 def get_columns_clause_froms(

4753 cls, statement: Select[Any]

4754 ) -> List[FromClause]:

4755 return cls._normalize_froms(

4756 itertools.chain.from_iterable(

4757 element._from_objects for element in statement._raw_columns

4758 )

4759 )

4760

4761 @classmethod

4762 def _column_naming_convention(

4763 cls, label_style: SelectLabelStyle

4764 ) -> _LabelConventionCallable:

4765 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4766

4767 dedupe = label_style is not LABEL_STYLE_NONE

4768

4769 pa = prefix_anon_map()

4770 names = set()

4771

4772 def go(

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

4774 col_name: Optional[str] = None,

4775 ) -> Optional[str]:

4776 if is_text_clause(c):

4777 return None

4778 elif TYPE_CHECKING:

4779 assert is_column_element(c)

4780

4781 if not dedupe:

4782 name = c._proxy_key

4783 if name is None:

4784 name = "_no_label"

4785 return name

4786

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

4788

4789 if name is None:

4790 name = "_no_label"

4791 if name in names:

4792 return c._anon_label(name) % pa

4793 else:

4794 names.add(name)

4795 return name

4796

4797 elif name in names:

4798 return (

4799 c._anon_tq_key_label % pa

4800 if table_qualified

4801 else c._anon_key_label % pa

4802 )

4803 else:

4804 names.add(name)

4805 return name

4806

4807 return go

4808

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

4810 ambiguous_table_name_map: _AmbiguousTableNameMap

4811 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4812

4813 return self._normalize_froms(

4814 itertools.chain(

4815 self.from_clauses,

4816 itertools.chain.from_iterable(

4817 [

4818 element._from_objects

4819 for element in statement._raw_columns

4820 ]

4821 ),

4822 itertools.chain.from_iterable(

4823 [

4824 element._from_objects

4825 for element in statement._where_criteria

4826 ]

4827 ),

4828 ),

4829 check_statement=statement,

4830 ambiguous_table_name_map=ambiguous_table_name_map,

4831 )

4832

4833 @classmethod

4834 def _normalize_froms(

4835 cls,

4836 iterable_of_froms: Iterable[FromClause],

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

4838 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4839 ) -> List[FromClause]:

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

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

4842

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

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

4845 etc.

4846

4847 """

4848 seen: Set[FromClause] = set()

4849 froms: List[FromClause] = []

4850

4851 for item in iterable_of_froms:

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

4853 raise exc.InvalidRequestError(

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

4855 )

4856

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

4858 froms.append(item)

4859 seen.update(item._cloned_set)

4860

4861 if froms:

4862 toremove = set(

4863 itertools.chain.from_iterable(

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

4865 )

4866 )

4867 if toremove:

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

4869 # using a list to maintain ordering

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

4871

4872 if ambiguous_table_name_map is not None:

4873 ambiguous_table_name_map.update(

4874 (

4875 fr.name,

4876 _anonymous_label.safe_construct(

4877 hash(fr.name), fr.name

4878 ),

4879 )

4880 for item in froms

4881 for fr in item._from_objects

4882 if is_table(fr)

4883 and fr.schema

4884 and fr.name not in ambiguous_table_name_map

4885 )

4886

4887 return froms

4888

4889 def _get_display_froms(

4890 self,

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

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

4893 ) -> List[FromClause]:

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

4895

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

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

4898 may want to leave those absent if it is automatically

4899 correlating.

4900

4901 """

4902

4903 froms = self.froms

4904

4905 if self.statement._correlate:

4906 to_correlate = self.statement._correlate

4907 if to_correlate:

4908 froms = [

4909 f

4910 for f in froms

4911 if f

4912 not in _cloned_intersection(

4913 _cloned_intersection(

4914 froms, explicit_correlate_froms or ()

4915 ),

4916 to_correlate,

4917 )

4918 ]

4919

4920 if self.statement._correlate_except is not None:

4921 froms = [

4922 f

4923 for f in froms

4924 if f

4925 not in _cloned_difference(

4926 _cloned_intersection(

4927 froms, explicit_correlate_froms or ()

4928 ),

4929 self.statement._correlate_except,

4930 )

4931 ]

4932

4933 if (

4934 self.statement._auto_correlate

4935 and implicit_correlate_froms

4936 and len(froms) > 1

4937 ):

4938 froms = [

4939 f

4940 for f in froms

4941 if f

4942 not in _cloned_intersection(froms, implicit_correlate_froms)

4943 ]

4944

4945 if not len(froms):

4946 raise exc.InvalidRequestError(

4947 "Select statement '%r"

4948 "' returned no FROM clauses "

4949 "due to auto-correlation; "

4950 "specify correlate(<tables>) "

4951 "to control correlation "

4952 "manually." % self.statement

4953 )

4954

4955 return froms

4956

4957 def _memoized_attr__label_resolve_dict(

4958 self,

4959 ) -> Tuple[

4960 Dict[str, ColumnElement[Any]],

4961 Dict[str, ColumnElement[Any]],

4962 Dict[str, ColumnElement[Any]],

4963 ]:

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

4965 c._tq_label or c.key: c

4966 for c in self.statement._all_selected_columns

4967 if c._allow_label_resolve

4968 }

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

4970 c.key: c # type: ignore

4971 for c in _select_iterables(self.froms)

4972 if c._allow_label_resolve

4973 }

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

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

4976 with_cols.setdefault(key, value)

4977

4978 return with_cols, only_froms, only_cols

4979

4980 @classmethod

4981 def determine_last_joined_entity(

4982 cls, stmt: Select[Any]

4983 ) -> Optional[_JoinTargetElement]:

4984 if stmt._setup_joins:

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

4986 else:

4987 return None

4988

4989 @classmethod

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

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

4992

4993 def _setup_joins(

4994 self,

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

4996 raw_columns: List[_ColumnsClauseElement],

4997 ) -> None:

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

4999 if TYPE_CHECKING:

5000 if onclause is not None:

5001 assert isinstance(onclause, ColumnElement)

5002

5003 isouter = flags["isouter"]

5004 full = flags["full"]

5005

5006 if left is None:

5007 (

5008 left,

5009 replace_from_obj_index,

5010 ) = self._join_determine_implicit_left_side(

5011 raw_columns, left, right, onclause

5012 )

5013 else:

5014 (replace_from_obj_index) = self._join_place_explicit_left_side(

5015 left

5016 )

5017

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

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

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

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

5022 # used instead.

5023 if TYPE_CHECKING:

5024 assert isinstance(right, FromClause)

5025 if onclause is not None:

5026 assert isinstance(onclause, ColumnElement)

5027

5028 if replace_from_obj_index is not None:

5029 # splice into an existing element in the

5030 # self._from_obj list

5031 left_clause = self.from_clauses[replace_from_obj_index]

5032

5033 self.from_clauses = (

5034 self.from_clauses[:replace_from_obj_index]

5035 + (

5036 Join(

5037 left_clause,

5038 right,

5039 onclause,

5040 isouter=isouter,

5041 full=full,

5042 ),

5043 )

5044 + self.from_clauses[replace_from_obj_index + 1 :]

5045 )

5046 else:

5047 assert left is not None

5048 self.from_clauses = self.from_clauses + (

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

5050 )

5051

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

5053 def _join_determine_implicit_left_side(

5054 self,

5055 raw_columns: List[_ColumnsClauseElement],

5056 left: Optional[FromClause],

5057 right: _JoinTargetElement,

5058 onclause: Optional[ColumnElement[Any]],

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

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

5061 determine if an existing FROM or entity in this query

5062 can serve as the left hand side.

5063

5064 """

5065

5066 sql_util = util.preloaded.sql_util

5067

5068 replace_from_obj_index: Optional[int] = None

5069

5070 from_clauses = self.from_clauses

5071

5072 if from_clauses:

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

5074 from_clauses, right, onclause

5075 )

5076

5077 if len(indexes) == 1:

5078 replace_from_obj_index = indexes[0]

5079 left = from_clauses[replace_from_obj_index]

5080 else:

5081 potential = {}

5082 statement = self.statement

5083

5084 for from_clause in itertools.chain(

5085 itertools.chain.from_iterable(

5086 [element._from_objects for element in raw_columns]

5087 ),

5088 itertools.chain.from_iterable(

5089 [

5090 element._from_objects

5091 for element in statement._where_criteria

5092 ]

5093 ),

5094 ):

5095 potential[from_clause] = ()

5096

5097 all_clauses = list(potential.keys())

5098 indexes = sql_util.find_left_clause_to_join_from(

5099 all_clauses, right, onclause

5100 )

5101

5102 if len(indexes) == 1:

5103 left = all_clauses[indexes[0]]

5104

5105 if len(indexes) > 1:

5106 raise exc.InvalidRequestError(

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

5108 "from, there are multiple FROMS which can "

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

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

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

5112 "help resolve the ambiguity."

5113 )

5114 elif not indexes:

5115 raise exc.InvalidRequestError(

5116 "Don't know how to join to %r. "

5117 "Please use the .select_from() "

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

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

5120 "help resolve the ambiguity." % (right,)

5121 )

5122 return left, replace_from_obj_index

5123

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

5125 def _join_place_explicit_left_side(

5126 self, left: FromClause

5127 ) -> Optional[int]:

5128 replace_from_obj_index: Optional[int] = None

5129

5130 sql_util = util.preloaded.sql_util

5131

5132 from_clauses = list(self.statement._iterate_from_elements())

5133

5134 if from_clauses:

5135 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5136 self.from_clauses, left

5137 )

5138 else:

5139 indexes = []

5140

5141 if len(indexes) > 1:

5142 raise exc.InvalidRequestError(

5143 "Can't identify which entity in which to assign the "

5144 "left side of this join. Please use a more specific "

5145 "ON clause."

5146 )

5147

5148 # have an index, means the left side is already present in

5149 # an existing FROM in the self._from_obj tuple

5150 if indexes:

5151 replace_from_obj_index = indexes[0]

5152

5153 # no index, means we need to add a new element to the

5154 # self._from_obj tuple

5155

5156 return replace_from_obj_index

5157

5158

5159class _SelectFromElements:

5160 __slots__ = ()

5161

5162 _raw_columns: List[_ColumnsClauseElement]

5163 _where_criteria: Tuple[ColumnElement[Any], ...]

5164 _from_obj: Tuple[FromClause, ...]

5165

5166 def _iterate_from_elements(self) -> Iterator[FromClause]:

5167 # note this does not include elements

5168 # in _setup_joins

5169

5170 seen = set()

5171 for element in self._raw_columns:

5172 for fr in element._from_objects:

5173 if fr in seen:

5174 continue

5175 seen.add(fr)

5176 yield fr

5177 for element in self._where_criteria:

5178 for fr in element._from_objects:

5179 if fr in seen:

5180 continue

5181 seen.add(fr)

5182 yield fr

5183 for element in self._from_obj:

5184 if element in seen:

5185 continue

5186 seen.add(element)

5187 yield element

5188

5189

5190class _MemoizedSelectEntities(

5191 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5192):

5193 """represents partial state from a Select object, for the case

5194 where Select.columns() has redefined the set of columns/entities the

5195 statement will be SELECTing from. This object represents

5196 the entities from the SELECT before that transformation was applied,

5197 so that transformations that were made in terms of the SELECT at that

5198 time, such as join() as well as options(), can access the correct context.

5199

5200 In previous SQLAlchemy versions, this wasn't needed because these

5201 constructs calculated everything up front, like when you called join()

5202 or options(), it did everything to figure out how that would translate

5203 into specific SQL constructs that would be ready to send directly to the

5204 SQL compiler when needed. But as of

5205 1.4, all of that stuff is done in the compilation phase, during the

5206 "compile state" portion of the process, so that the work can all be

5207 cached. So it needs to be able to resolve joins/options2 based on what

5208 the list of entities was when those methods were called.

5209

5210

5211 """

5212

5213 __visit_name__ = "memoized_select_entities"

5214

5215 _traverse_internals: _TraverseInternalsType = [

5216 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5217 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5218 ("_with_options", InternalTraversal.dp_executable_options),

5219 ]

5220

5221 _is_clone_of: Optional[ClauseElement]

5222 _raw_columns: List[_ColumnsClauseElement]

5223 _setup_joins: Tuple[_SetupJoinsElement, ...]

5224 _with_options: Tuple[ExecutableOption, ...]

5225

5226 _annotations = util.EMPTY_DICT

5227

5228 def _clone(self, **kw: Any) -> Self:

5229 c = self.__class__.__new__(self.__class__)

5230 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5231

5232 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5233 return c

5234

5235 @classmethod

5236 def _generate_for_statement(cls, select_stmt: Select[Any]) -> None:

5237 if select_stmt._setup_joins or select_stmt._with_options:

5238 self = _MemoizedSelectEntities()

5239 self._raw_columns = select_stmt._raw_columns

5240 self._setup_joins = select_stmt._setup_joins

5241 self._with_options = select_stmt._with_options

5242

5243 select_stmt._memoized_select_entities += (self,)

5244 select_stmt._raw_columns = []

5245 select_stmt._setup_joins = select_stmt._with_options = ()

5246

5247

5248class Select(

5249 HasPrefixes,

5250 HasSuffixes,

5251 HasHints,

5252 HasCompileState,

5253 _SelectFromElements,

5254 GenerativeSelect,

5255 TypedReturnsRows[_TP],

5256):

5257 """Represents a ``SELECT`` statement.

5258

5259 The :class:`_sql.Select` object is normally constructed using the

5260 :func:`_sql.select` function. See that function for details.

5261

5262 .. seealso::

5263

5264 :func:`_sql.select`

5265

5266 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5267

5268 """

5269

5270 __visit_name__ = "select"

5271

5272 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5273 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5274

5275 _raw_columns: List[_ColumnsClauseElement]

5276

5277 _distinct: bool = False

5278 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5279 _correlate: Tuple[FromClause, ...] = ()

5280 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5281 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5282 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5283 _from_obj: Tuple[FromClause, ...] = ()

5284 _auto_correlate = True

5285 _is_select_statement = True

5286 _compile_options: CacheableOptions = (

5287 SelectState.default_select_compile_options

5288 )

5289

5290 _traverse_internals: _TraverseInternalsType = (

5291 [

5292 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5293 (

5294 "_memoized_select_entities",

5295 InternalTraversal.dp_memoized_select_entities,

5296 ),

5297 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5298 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5299 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5300 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5301 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5302 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5303 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5304 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5305 ("_limit_clause", InternalTraversal.dp_clauseelement),

5306 ("_offset_clause", InternalTraversal.dp_clauseelement),

5307 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5308 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5309 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5310 ("_distinct", InternalTraversal.dp_boolean),

5311 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5312 ("_label_style", InternalTraversal.dp_plain_obj),

5313 ]

5314 + HasCTE._has_ctes_traverse_internals

5315 + HasPrefixes._has_prefixes_traverse_internals

5316 + HasSuffixes._has_suffixes_traverse_internals

5317 + HasHints._has_hints_traverse_internals

5318 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5319 + Executable._executable_traverse_internals

5320 + DialectKWArgs._dialect_kwargs_traverse_internals

5321 )

5322

5323 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5324 ("_compile_options", InternalTraversal.dp_has_cache_key)

5325 ]

5326

5327 _compile_state_factory: Type[SelectState]

5328

5329 @classmethod

5330 def _create_raw_select(cls, **kw: Any) -> Select[Any]:

5331 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5332

5333 Used internally to build up :class:`.Select` constructs with

5334 pre-established state.

5335

5336 """

5337

5338 stmt = Select.__new__(Select)

5339 stmt.__dict__.update(kw)

5340 return stmt

5341

5342 def __init__(

5343 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5344 ):

5345 r"""Construct a new :class:`_expression.Select`.

5346

5347 The public constructor for :class:`_expression.Select` is the

5348 :func:`_sql.select` function.

5349

5350 """

5351 self._raw_columns = [

5352 coercions.expect(

5353 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5354 )

5355 for ent in entities

5356 ]

5357 GenerativeSelect.__init__(self)

5358

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

5360 if not self._raw_columns:

5361 return NULLTYPE

5362 elem = self._raw_columns[0]

5363 cols = list(elem._select_iterable)

5364 return cols[0].type

5365

5366 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5367 """A synonym for the :meth:`_sql.Select.where` method."""

5368

5369 return self.where(*criteria)

5370

5371 def _filter_by_zero(

5372 self,

5373 ) -> Union[

5374 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5375 ]:

5376 if self._setup_joins:

5377 meth = SelectState.get_plugin_class(

5378 self

5379 ).determine_last_joined_entity

5380 _last_joined_entity = meth(self)

5381 if _last_joined_entity is not None:

5382 return _last_joined_entity

5383

5384 if self._from_obj:

5385 return self._from_obj[0]

5386

5387 return self._raw_columns[0]

5388

5389 if TYPE_CHECKING:

5390

5391 @overload

5392 def scalar_subquery(

5393 self: Select[Tuple[_MAYBE_ENTITY]],

5394 ) -> ScalarSelect[Any]: ...

5395

5396 @overload

5397 def scalar_subquery(

5398 self: Select[Tuple[_NOT_ENTITY]],

5399 ) -> ScalarSelect[_NOT_ENTITY]: ...

5400

5401 @overload

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

5403

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

5405

5406 def filter_by(self, **kwargs: Any) -> Self:

5407 r"""apply the given filtering criterion as a WHERE clause

5408 to this select.

5409

5410 """

5411 from_entity = self._filter_by_zero()

5412

5413 clauses = [

5414 _entity_namespace_key(from_entity, key) == value

5415 for key, value in kwargs.items()

5416 ]

5417 return self.filter(*clauses)

5418

5419 @property

5420 def column_descriptions(self) -> Any:

5421 """Return a :term:`plugin-enabled` 'column descriptions' structure

5422 referring to the columns which are SELECTed by this statement.

5423

5424 This attribute is generally useful when using the ORM, as an

5425 extended structure which includes information about mapped

5426 entities is returned. The section :ref:`queryguide_inspection`

5427 contains more background.

5428

5429 For a Core-only statement, the structure returned by this accessor

5430 is derived from the same objects that are returned by the

5431 :attr:`.Select.selected_columns` accessor, formatted as a list of

5432 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5433 which indicate the column expressions to be selected::

5434

5435 >>> stmt = select(user_table)

5436 >>> stmt.column_descriptions

5437 [

5438 {

5439 'name': 'id',

5440 'type': Integer(),

5441 'expr': Column('id', Integer(), ...)},

5442 {

5443 'name': 'name',

5444 'type': String(length=30),

5445 'expr': Column('name', String(length=30), ...)}

5446 ]

5447

5448 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5449 attribute returns a structure for a Core-only set of entities,

5450 not just ORM-only entities.

5451

5452 .. seealso::

5453

5454 :attr:`.UpdateBase.entity_description` - entity information for

5455 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5456

5457 :ref:`queryguide_inspection` - ORM background

5458

5459 """

5460 meth = SelectState.get_plugin_class(self).get_column_descriptions

5461 return meth(self)

5462

5463 def from_statement(

5464 self, statement: roles.ReturnsRowsRole

5465 ) -> ExecutableReturnsRows:

5466 """Apply the columns which this :class:`.Select` would select

5467 onto another statement.

5468

5469 This operation is :term:`plugin-specific` and will raise a not

5470 supported exception if this :class:`_sql.Select` does not select from

5471 plugin-enabled entities.

5472

5473

5474 The statement is typically either a :func:`_expression.text` or

5475 :func:`_expression.select` construct, and should return the set of

5476 columns appropriate to the entities represented by this

5477 :class:`.Select`.

5478

5479 .. seealso::

5480

5481 :ref:`orm_queryguide_selecting_text` - usage examples in the

5482 ORM Querying Guide

5483

5484 """

5485 meth = SelectState.get_plugin_class(self).from_statement

5486 return meth(self, statement)

5487

5488 @_generative

5489 def join(

5490 self,

5491 target: _JoinTargetArgument,

5492 onclause: Optional[_OnClauseArgument] = None,

5493 *,

5494 isouter: bool = False,

5495 full: bool = False,

5496 ) -> Self:

5497 r"""Create a SQL JOIN against this :class:`_expression.Select`

5498 object's criterion

5499 and apply generatively, returning the newly resulting

5500 :class:`_expression.Select`.

5501

5502 E.g.::

5503

5504 stmt = select(user_table).join(

5505 address_table, user_table.c.id == address_table.c.user_id

5506 )

5507

5508 The above statement generates SQL similar to:

5509

5510 .. sourcecode:: sql

5511

5512 SELECT user.id, user.name

5513 FROM user

5514 JOIN address ON user.id = address.user_id

5515

5516 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5517 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5518 source that is within the FROM clause of the existing SELECT,

5519 and a given target :class:`_sql.FromClause`, and then adds

5520 this :class:`_sql.Join` to the FROM clause of the newly generated

5521 SELECT statement. This is completely reworked from the behavior

5522 in 1.3, which would instead create a subquery of the entire

5523 :class:`_expression.Select` and then join that subquery to the

5524 target.

5525

5526 This is a **backwards incompatible change** as the previous behavior

5527 was mostly useless, producing an unnamed subquery rejected by

5528 most databases in any case. The new behavior is modeled after

5529 that of the very successful :meth:`_orm.Query.join` method in the

5530 ORM, in order to support the functionality of :class:`_orm.Query`

5531 being available by using a :class:`_sql.Select` object with an

5532 :class:`_orm.Session`.

5533

5534 See the notes for this change at :ref:`change_select_join`.

5535

5536

5537 :param target: target table to join towards

5538

5539 :param onclause: ON clause of the join. If omitted, an ON clause

5540 is generated automatically based on the :class:`_schema.ForeignKey`

5541 linkages between the two tables, if one can be unambiguously

5542 determined, otherwise an error is raised.

5543

5544 :param isouter: if True, generate LEFT OUTER join. Same as

5545 :meth:`_expression.Select.outerjoin`.

5546

5547 :param full: if True, generate FULL OUTER join.

5548

5549 .. seealso::

5550

5551 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5552

5553 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5554

5555 :meth:`_expression.Select.join_from`

5556

5557 :meth:`_expression.Select.outerjoin`

5558

5559 """ # noqa: E501

5560 join_target = coercions.expect(

5561 roles.JoinTargetRole, target, apply_propagate_attrs=self

5562 )

5563 if onclause is not None:

5564 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5565 else:

5566 onclause_element = None

5567

5568 self._setup_joins += (

5569 (

5570 join_target,

5571 onclause_element,

5572 None,

5573 {"isouter": isouter, "full": full},

5574 ),

5575 )

5576 return self

5577

5578 def outerjoin_from(

5579 self,

5580 from_: _FromClauseArgument,

5581 target: _JoinTargetArgument,

5582 onclause: Optional[_OnClauseArgument] = None,

5583 *,

5584 full: bool = False,

5585 ) -> Self:

5586 r"""Create a SQL LEFT OUTER JOIN against this

5587 :class:`_expression.Select` object's criterion and apply generatively,

5588 returning the newly resulting :class:`_expression.Select`.

5589

5590 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5591

5592 """

5593 return self.join_from(

5594 from_, target, onclause=onclause, isouter=True, full=full

5595 )

5596

5597 @_generative

5598 def join_from(

5599 self,

5600 from_: _FromClauseArgument,

5601 target: _JoinTargetArgument,

5602 onclause: Optional[_OnClauseArgument] = None,

5603 *,

5604 isouter: bool = False,

5605 full: bool = False,

5606 ) -> Self:

5607 r"""Create a SQL JOIN against this :class:`_expression.Select`

5608 object's criterion

5609 and apply generatively, returning the newly resulting

5610 :class:`_expression.Select`.

5611

5612 E.g.::

5613

5614 stmt = select(user_table, address_table).join_from(

5615 user_table, address_table, user_table.c.id == address_table.c.user_id

5616 )

5617

5618 The above statement generates SQL similar to:

5619

5620 .. sourcecode:: sql

5621

5622 SELECT user.id, user.name, address.id, address.email, address.user_id

5623 FROM user JOIN address ON user.id = address.user_id

5624

5625 .. versionadded:: 1.4

5626

5627 :param from\_: the left side of the join, will be rendered in the

5628 FROM clause and is roughly equivalent to using the

5629 :meth:`.Select.select_from` method.

5630

5631 :param target: target table to join towards

5632

5633 :param onclause: ON clause of the join.

5634

5635 :param isouter: if True, generate LEFT OUTER join. Same as

5636 :meth:`_expression.Select.outerjoin`.

5637

5638 :param full: if True, generate FULL OUTER join.

5639

5640 .. seealso::

5641

5642 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5643

5644 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5645

5646 :meth:`_expression.Select.join`

5647

5648 """ # noqa: E501

5649

5650 # note the order of parsing from vs. target is important here, as we

5651 # are also deriving the source of the plugin (i.e. the subject mapper

5652 # in an ORM query) which should favor the "from_" over the "target"

5653

5654 from_ = coercions.expect(

5655 roles.FromClauseRole, from_, apply_propagate_attrs=self

5656 )

5657 join_target = coercions.expect(

5658 roles.JoinTargetRole, target, apply_propagate_attrs=self

5659 )

5660 if onclause is not None:

5661 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5662 else:

5663 onclause_element = None

5664

5665 self._setup_joins += (

5666 (

5667 join_target,

5668 onclause_element,

5669 from_,

5670 {"isouter": isouter, "full": full},

5671 ),

5672 )

5673 return self

5674

5675 def outerjoin(

5676 self,

5677 target: _JoinTargetArgument,

5678 onclause: Optional[_OnClauseArgument] = None,

5679 *,

5680 full: bool = False,

5681 ) -> Self:

5682 """Create a left outer join.

5683

5684 Parameters are the same as that of :meth:`_expression.Select.join`.

5685

5686 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5687 creates a :class:`_sql.Join` object between a

5688 :class:`_sql.FromClause` source that is within the FROM clause of

5689 the existing SELECT, and a given target :class:`_sql.FromClause`,

5690 and then adds this :class:`_sql.Join` to the FROM clause of the

5691 newly generated SELECT statement. This is completely reworked

5692 from the behavior in 1.3, which would instead create a subquery of

5693 the entire

5694 :class:`_expression.Select` and then join that subquery to the

5695 target.

5696

5697 This is a **backwards incompatible change** as the previous behavior

5698 was mostly useless, producing an unnamed subquery rejected by

5699 most databases in any case. The new behavior is modeled after

5700 that of the very successful :meth:`_orm.Query.join` method in the

5701 ORM, in order to support the functionality of :class:`_orm.Query`

5702 being available by using a :class:`_sql.Select` object with an

5703 :class:`_orm.Session`.

5704

5705 See the notes for this change at :ref:`change_select_join`.

5706

5707 .. seealso::

5708

5709 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5710

5711 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5712

5713 :meth:`_expression.Select.join`

5714

5715 """

5716 return self.join(target, onclause=onclause, isouter=True, full=full)

5717

5718 def get_final_froms(self) -> Sequence[FromClause]:

5719 """Compute the final displayed list of :class:`_expression.FromClause`

5720 elements.

5721

5722 This method will run through the full computation required to

5723 determine what FROM elements will be displayed in the resulting

5724 SELECT statement, including shadowing individual tables with

5725 JOIN objects, as well as full computation for ORM use cases including

5726 eager loading clauses.

5727

5728 For ORM use, this accessor returns the **post compilation**

5729 list of FROM objects; this collection will include elements such as

5730 eagerly loaded tables and joins. The objects will **not** be

5731 ORM enabled and not work as a replacement for the

5732 :meth:`_sql.Select.select_froms` collection; additionally, the

5733 method is not well performing for an ORM enabled statement as it

5734 will incur the full ORM construction process.

5735

5736 To retrieve the FROM list that's implied by the "columns" collection

5737 passed to the :class:`_sql.Select` originally, use the

5738 :attr:`_sql.Select.columns_clause_froms` accessor.

5739

5740 To select from an alternative set of columns while maintaining the

5741 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5742 pass the

5743 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5744 parameter.

5745

5746 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5747 method replaces the previous :attr:`_sql.Select.froms` accessor,

5748 which is deprecated.

5749

5750 .. seealso::

5751

5752 :attr:`_sql.Select.columns_clause_froms`

5753

5754 """

5755 compiler = self._default_compiler()

5756

5757 return self._compile_state_factory(self, compiler)._get_display_froms()

5758

5759 @property

5760 @util.deprecated(

5761 "1.4.23",

5762 "The :attr:`_expression.Select.froms` attribute is moved to "

5763 "the :meth:`_expression.Select.get_final_froms` method.",

5764 )

5765 def froms(self) -> Sequence[FromClause]:

5766 """Return the displayed list of :class:`_expression.FromClause`

5767 elements.

5768

5769

5770 """

5771 return self.get_final_froms()

5772

5773 @property

5774 def columns_clause_froms(self) -> List[FromClause]:

5775 """Return the set of :class:`_expression.FromClause` objects implied

5776 by the columns clause of this SELECT statement.

5777

5778 .. versionadded:: 1.4.23

5779

5780 .. seealso::

5781

5782 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5783 statement into account

5784

5785 :meth:`_sql.Select.with_only_columns` - makes use of this

5786 collection to set up a new FROM list

5787

5788 """

5789

5790 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5791 self

5792 )

5793

5794 @property

5795 def inner_columns(self) -> _SelectIterable:

5796 """An iterator of all :class:`_expression.ColumnElement`

5797 expressions which would

5798 be rendered into the columns clause of the resulting SELECT statement.

5799

5800 This method is legacy as of 1.4 and is superseded by the

5801 :attr:`_expression.Select.exported_columns` collection.

5802

5803 """

5804

5805 return iter(self._all_selected_columns)

5806

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

5808 if fromclause is not None and self in fromclause._cloned_set:

5809 return True

5810

5811 for f in self._iterate_from_elements():

5812 if f.is_derived_from(fromclause):

5813 return True

5814 return False

5815

5816 def _copy_internals(

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

5818 ) -> None:

5819 # Select() object has been cloned and probably adapted by the

5820 # given clone function. Apply the cloning function to internal

5821 # objects

5822

5823 # 1. keep a dictionary of the froms we've cloned, and what

5824 # they've become. This allows us to ensure the same cloned from

5825 # is used when other items such as columns are "cloned"

5826

5827 all_the_froms = set(

5828 itertools.chain(

5829 _from_objects(*self._raw_columns),

5830 _from_objects(*self._where_criteria),

5831 _from_objects(*[elem[0] for elem in self._setup_joins]),

5832 )

5833 )

5834

5835 # do a clone for the froms we've gathered. what is important here

5836 # is if any of the things we are selecting from, like tables,

5837 # were converted into Join objects. if so, these need to be

5838 # added to _from_obj explicitly, because otherwise they won't be

5839 # part of the new state, as they don't associate themselves with

5840 # their columns.

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

5842

5843 # 2. copy FROM collections, adding in joins that we've created.

5844 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5845 add_froms = (

5846 {f for f in new_froms.values() if isinstance(f, Join)}

5847 .difference(all_the_froms)

5848 .difference(existing_from_obj)

5849 )

5850

5851 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5852

5853 # 3. clone everything else, making sure we use columns

5854 # corresponding to the froms we just made.

5855 def replace(

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

5857 **kw: Any,

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

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

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

5861 return newelem

5862 return None

5863

5864 kw["replace"] = replace

5865

5866 # copy everything else. for table-ish things like correlate,

5867 # correlate_except, setup_joins, these clone normally. For

5868 # column-expression oriented things like raw_columns, where_criteria,

5869 # order by, we get this from the new froms.

5870 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5871

5872 self._reset_memoizations()

5873

5874 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5875 return itertools.chain(

5876 super().get_children(

5877 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5878 **kw,

5879 ),

5880 self._iterate_from_elements(),

5881 )

5882

5883 @_generative

5884 def add_columns(

5885 self, *entities: _ColumnsClauseArgument[Any]

5886 ) -> Select[Any]:

5887 r"""Return a new :func:`_expression.select` construct with

5888 the given entities appended to its columns clause.

5889

5890 E.g.::

5891

5892 my_select = my_select.add_columns(table.c.new_column)

5893

5894 The original expressions in the columns clause remain in place.

5895 To replace the original expressions with new ones, see the method

5896 :meth:`_expression.Select.with_only_columns`.

5897

5898 :param \*entities: column, table, or other entity expressions to be

5899 added to the columns clause

5900

5901 .. seealso::

5902

5903 :meth:`_expression.Select.with_only_columns` - replaces existing

5904 expressions rather than appending.

5905

5906 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5907 example

5908

5909 """

5910 self._reset_memoizations()

5911

5912 self._raw_columns = self._raw_columns + [

5913 coercions.expect(

5914 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5915 )

5916 for column in entities

5917 ]

5918 return self

5919

5920 def _set_entities(

5921 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5922 ) -> None:

5923 self._raw_columns = [

5924 coercions.expect(

5925 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5926 )

5927 for ent in util.to_list(entities)

5928 ]

5929

5930 @util.deprecated(

5931 "1.4",

5932 "The :meth:`_expression.Select.column` method is deprecated and will "

5933 "be removed in a future release. Please use "

5934 ":meth:`_expression.Select.add_columns`",

5935 )

5936 def column(self, column: _ColumnsClauseArgument[Any]) -> Select[Any]:

5937 """Return a new :func:`_expression.select` construct with

5938 the given column expression added to its columns clause.

5939

5940 E.g.::

5941

5942 my_select = my_select.column(table.c.new_column)

5943

5944 See the documentation for

5945 :meth:`_expression.Select.with_only_columns`

5946 for guidelines on adding /replacing the columns of a

5947 :class:`_expression.Select` object.

5948

5949 """

5950 return self.add_columns(column)

5951

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

5953 def reduce_columns(self, only_synonyms: bool = True) -> Select[Any]:

5954 """Return a new :func:`_expression.select` construct with redundantly

5955 named, equivalently-valued columns removed from the columns clause.

5956

5957 "Redundant" here means two columns where one refers to the

5958 other either based on foreign key, or via a simple equality

5959 comparison in the WHERE clause of the statement. The primary purpose

5960 of this method is to automatically construct a select statement

5961 with all uniquely-named columns, without the need to use

5962 table-qualified labels as

5963 :meth:`_expression.Select.set_label_style`

5964 does.

5965

5966 When columns are omitted based on foreign key, the referred-to

5967 column is the one that's kept. When columns are omitted based on

5968 WHERE equivalence, the first column in the columns clause is the

5969 one that's kept.

5970

5971 :param only_synonyms: when True, limit the removal of columns

5972 to those which have the same name as the equivalent. Otherwise,

5973 all columns that are equivalent to another are removed.

5974

5975 """

5976 woc: Select[Any]

5977 woc = self.with_only_columns(

5978 *util.preloaded.sql_util.reduce_columns(

5979 self._all_selected_columns,

5980 only_synonyms=only_synonyms,

5981 *(self._where_criteria + self._from_obj),

5982 )

5983 )

5984 return woc

5985

5986 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

5987

5988 # code within this block is **programmatically,

5989 # statically generated** by tools/generate_tuple_map_overloads.py

5990

5991 @overload

5992 def with_only_columns(

5993 self, __ent0: _TCCA[_T0], *, maintain_column_froms: bool = ...

5994 ) -> Select[Tuple[_T0]]: ...

5995

5996 @overload

5997 def with_only_columns(

5998 self,

5999 __ent0: _TCCA[_T0],

6000 __ent1: _TCCA[_T1],

6001 *,

6002 maintain_column_froms: bool = ...,

6003 ) -> Select[Tuple[_T0, _T1]]: ...

6004

6005 @overload

6006 def with_only_columns(

6007 self,

6008 __ent0: _TCCA[_T0],

6009 __ent1: _TCCA[_T1],

6010 __ent2: _TCCA[_T2],

6011 *,

6012 maintain_column_froms: bool = ...,

6013 ) -> Select[Tuple[_T0, _T1, _T2]]: ...

6014

6015 @overload

6016 def with_only_columns(

6017 self,

6018 __ent0: _TCCA[_T0],

6019 __ent1: _TCCA[_T1],

6020 __ent2: _TCCA[_T2],

6021 __ent3: _TCCA[_T3],

6022 *,

6023 maintain_column_froms: bool = ...,

6024 ) -> Select[Tuple[_T0, _T1, _T2, _T3]]: ...

6025

6026 @overload

6027 def with_only_columns(

6028 self,

6029 __ent0: _TCCA[_T0],

6030 __ent1: _TCCA[_T1],

6031 __ent2: _TCCA[_T2],

6032 __ent3: _TCCA[_T3],

6033 __ent4: _TCCA[_T4],

6034 *,

6035 maintain_column_froms: bool = ...,

6036 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4]]: ...

6037

6038 @overload

6039 def with_only_columns(

6040 self,

6041 __ent0: _TCCA[_T0],

6042 __ent1: _TCCA[_T1],

6043 __ent2: _TCCA[_T2],

6044 __ent3: _TCCA[_T3],

6045 __ent4: _TCCA[_T4],

6046 __ent5: _TCCA[_T5],

6047 *,

6048 maintain_column_froms: bool = ...,

6049 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5]]: ...

6050

6051 @overload

6052 def with_only_columns(

6053 self,

6054 __ent0: _TCCA[_T0],

6055 __ent1: _TCCA[_T1],

6056 __ent2: _TCCA[_T2],

6057 __ent3: _TCCA[_T3],

6058 __ent4: _TCCA[_T4],

6059 __ent5: _TCCA[_T5],

6060 __ent6: _TCCA[_T6],

6061 *,

6062 maintain_column_froms: bool = ...,

6063 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6]]: ...

6064

6065 @overload

6066 def with_only_columns(

6067 self,

6068 __ent0: _TCCA[_T0],

6069 __ent1: _TCCA[_T1],

6070 __ent2: _TCCA[_T2],

6071 __ent3: _TCCA[_T3],

6072 __ent4: _TCCA[_T4],

6073 __ent5: _TCCA[_T5],

6074 __ent6: _TCCA[_T6],

6075 __ent7: _TCCA[_T7],

6076 *,

6077 maintain_column_froms: bool = ...,

6078 ) -> Select[Tuple[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]: ...

6079

6080 # END OVERLOADED FUNCTIONS self.with_only_columns

6081

6082 @overload

6083 def with_only_columns(

6084 self,

6085 *entities: _ColumnsClauseArgument[Any],

6086 maintain_column_froms: bool = False,

6087 **__kw: Any,

6088 ) -> Select[Any]: ...

6089

6090 @_generative

6091 def with_only_columns(

6092 self,

6093 *entities: _ColumnsClauseArgument[Any],

6094 maintain_column_froms: bool = False,

6095 **__kw: Any,

6096 ) -> Select[Any]:

6097 r"""Return a new :func:`_expression.select` construct with its columns

6098 clause replaced with the given entities.

6099

6100 By default, this method is exactly equivalent to as if the original

6101 :func:`_expression.select` had been called with the given entities.

6102 E.g. a statement::

6103

6104 s = select(table1.c.a, table1.c.b)

6105 s = s.with_only_columns(table1.c.b)

6106

6107 should be exactly equivalent to::

6108

6109 s = select(table1.c.b)

6110

6111 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6112 will also dynamically alter the FROM clause of the

6113 statement if it is not explicitly stated.

6114 To maintain the existing set of FROMs including those implied by the

6115 current columns clause, add the

6116 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6117 parameter::

6118

6119 s = select(table1.c.a, table2.c.b)

6120 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6121

6122 The above parameter performs a transfer of the effective FROMs

6123 in the columns collection to the :meth:`_sql.Select.select_from`

6124 method, as though the following were invoked::

6125

6126 s = select(table1.c.a, table2.c.b)

6127 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6128

6129 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6130 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6131 collection and performs an operation equivalent to the following::

6132

6133 s = select(table1.c.a, table2.c.b)

6134 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6135

6136 :param \*entities: column expressions to be used.

6137

6138 :param maintain_column_froms: boolean parameter that will ensure the

6139 FROM list implied from the current columns clause will be transferred

6140 to the :meth:`_sql.Select.select_from` method first.

6141

6142 .. versionadded:: 1.4.23

6143

6144 """ # noqa: E501

6145

6146 if __kw:

6147 raise _no_kw()

6148

6149 # memoizations should be cleared here as of

6150 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6151 # is the case for now.

6152 self._assert_no_memoizations()

6153

6154 if maintain_column_froms:

6155 self.select_from.non_generative( # type: ignore

6156 self, *self.columns_clause_froms

6157 )

6158

6159 # then memoize the FROMs etc.

6160 _MemoizedSelectEntities._generate_for_statement(self)

6161

6162 self._raw_columns = [

6163 coercions.expect(roles.ColumnsClauseRole, c)

6164 for c in coercions._expression_collection_was_a_list(

6165 "entities", "Select.with_only_columns", entities

6166 )

6167 ]

6168 return self

6169

6170 @property

6171 def whereclause(self) -> Optional[ColumnElement[Any]]:

6172 """Return the completed WHERE clause for this

6173 :class:`_expression.Select` statement.

6174

6175 This assembles the current collection of WHERE criteria

6176 into a single :class:`_expression.BooleanClauseList` construct.

6177

6178

6179 .. versionadded:: 1.4

6180

6181 """

6182

6183 return BooleanClauseList._construct_for_whereclause(

6184 self._where_criteria

6185 )

6186

6187 _whereclause = whereclause

6188

6189 @_generative

6190 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6191 """Return a new :func:`_expression.select` construct with

6192 the given expression added to

6193 its WHERE clause, joined to the existing clause via AND, if any.

6194

6195 """

6196

6197 assert isinstance(self._where_criteria, tuple)

6198

6199 for criterion in whereclause:

6200 where_criteria: ColumnElement[Any] = coercions.expect(

6201 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6202 )

6203 self._where_criteria += (where_criteria,)

6204 return self

6205

6206 @_generative

6207 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6208 """Return a new :func:`_expression.select` construct with

6209 the given expression added to

6210 its HAVING clause, joined to the existing clause via AND, if any.

6211

6212 """

6213

6214 for criterion in having:

6215 having_criteria = coercions.expect(

6216 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6217 )

6218 self._having_criteria += (having_criteria,)

6219 return self

6220

6221 @_generative

6222 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6223 r"""Return a new :func:`_expression.select` construct which

6224 will apply DISTINCT to the SELECT statement overall.

6225

6226 E.g.::

6227

6228 from sqlalchemy import select

6229

6230 stmt = select(users_table.c.id, users_table.c.name).distinct()

6231

6232 The above would produce an statement resembling:

6233

6234 .. sourcecode:: sql

6235

6236 SELECT DISTINCT user.id, user.name FROM user

6237

6238 The method also accepts an ``*expr`` parameter which produces the

6239 PostgreSQL dialect-specific ``DISTINCT ON`` expression. Using this

6240 parameter on other backends which don't support this syntax will

6241 raise an error.

6242

6243 :param \*expr: optional column expressions. When present,

6244 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6245 construct. A deprecation warning and/or :class:`_exc.CompileError`

6246 will be raised on other backends.

6247

6248 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6249 and will raise :class:`_exc.CompileError` in a future version.

6250

6251 """

6252 if expr:

6253 self._distinct = True

6254 self._distinct_on = self._distinct_on + tuple(

6255 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6256 for e in expr

6257 )

6258 else:

6259 self._distinct = True

6260 return self

6261

6262 @_generative

6263 def select_from(self, *froms: _FromClauseArgument) -> Self:

6264 r"""Return a new :func:`_expression.select` construct with the

6265 given FROM expression(s)

6266 merged into its list of FROM objects.

6267

6268 E.g.::

6269

6270 table1 = table("t1", column("a"))

6271 table2 = table("t2", column("b"))

6272 s = select(table1.c.a).select_from(

6273 table1.join(table2, table1.c.a == table2.c.b)

6274 )

6275

6276 The "from" list is a unique set on the identity of each element,

6277 so adding an already present :class:`_schema.Table`

6278 or other selectable

6279 will have no effect. Passing a :class:`_expression.Join` that refers

6280 to an already present :class:`_schema.Table`

6281 or other selectable will have

6282 the effect of concealing the presence of that selectable as

6283 an individual element in the rendered FROM list, instead

6284 rendering it into a JOIN clause.

6285

6286 While the typical purpose of :meth:`_expression.Select.select_from`

6287 is to

6288 replace the default, derived FROM clause with a join, it can

6289 also be called with individual table elements, multiple times

6290 if desired, in the case that the FROM clause cannot be fully

6291 derived from the columns clause::

6292

6293 select(func.count("*")).select_from(table1)

6294

6295 """

6296

6297 self._from_obj += tuple(

6298 coercions.expect(

6299 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6300 )

6301 for fromclause in froms

6302 )

6303 return self

6304

6305 @_generative

6306 def correlate(

6307 self,

6308 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6309 ) -> Self:

6310 r"""Return a new :class:`_expression.Select`

6311 which will correlate the given FROM

6312 clauses to that of an enclosing :class:`_expression.Select`.

6313

6314 Calling this method turns off the :class:`_expression.Select` object's

6315 default behavior of "auto-correlation". Normally, FROM elements

6316 which appear in a :class:`_expression.Select`

6317 that encloses this one via

6318 its :term:`WHERE clause`, ORDER BY, HAVING or

6319 :term:`columns clause` will be omitted from this

6320 :class:`_expression.Select`

6321 object's :term:`FROM clause`.

6322 Setting an explicit correlation collection using the

6323 :meth:`_expression.Select.correlate`

6324 method provides a fixed list of FROM objects

6325 that can potentially take place in this process.

6326

6327 When :meth:`_expression.Select.correlate`

6328 is used to apply specific FROM clauses

6329 for correlation, the FROM elements become candidates for

6330 correlation regardless of how deeply nested this

6331 :class:`_expression.Select`

6332 object is, relative to an enclosing :class:`_expression.Select`

6333 which refers to

6334 the same FROM object. This is in contrast to the behavior of

6335 "auto-correlation" which only correlates to an immediate enclosing

6336 :class:`_expression.Select`.

6337 Multi-level correlation ensures that the link

6338 between enclosed and enclosing :class:`_expression.Select`

6339 is always via

6340 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6341 correlation to take place.

6342

6343 If ``None`` is passed, the :class:`_expression.Select`

6344 object will correlate

6345 none of its FROM entries, and all will render unconditionally

6346 in the local FROM clause.

6347

6348 :param \*fromclauses: one or more :class:`.FromClause` or other

6349 FROM-compatible construct such as an ORM mapped entity to become part

6350 of the correlate collection; alternatively pass a single value

6351 ``None`` to remove all existing correlations.

6352

6353 .. seealso::

6354

6355 :meth:`_expression.Select.correlate_except`

6356

6357 :ref:`tutorial_scalar_subquery`

6358

6359 """

6360

6361 # tests failing when we try to change how these

6362 # arguments are passed

6363

6364 self._auto_correlate = False

6365 if not fromclauses or fromclauses[0] in {None, False}:

6366 if len(fromclauses) > 1:

6367 raise exc.ArgumentError(

6368 "additional FROM objects not accepted when "

6369 "passing None/False to correlate()"

6370 )

6371 self._correlate = ()

6372 else:

6373 self._correlate = self._correlate + tuple(

6374 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6375 )

6376 return self

6377

6378 @_generative

6379 def correlate_except(

6380 self,

6381 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6382 ) -> Self:

6383 r"""Return a new :class:`_expression.Select`

6384 which will omit the given FROM

6385 clauses from the auto-correlation process.

6386

6387 Calling :meth:`_expression.Select.correlate_except` turns off the

6388 :class:`_expression.Select` object's default behavior of

6389 "auto-correlation" for the given FROM elements. An element

6390 specified here will unconditionally appear in the FROM list, while

6391 all other FROM elements remain subject to normal auto-correlation

6392 behaviors.

6393

6394 If ``None`` is passed, or no arguments are passed,

6395 the :class:`_expression.Select` object will correlate all of its

6396 FROM entries.

6397

6398 :param \*fromclauses: a list of one or more

6399 :class:`_expression.FromClause`

6400 constructs, or other compatible constructs (i.e. ORM-mapped

6401 classes) to become part of the correlate-exception collection.

6402

6403 .. seealso::

6404

6405 :meth:`_expression.Select.correlate`

6406

6407 :ref:`tutorial_scalar_subquery`

6408

6409 """

6410

6411 self._auto_correlate = False

6412 if not fromclauses or fromclauses[0] in {None, False}:

6413 if len(fromclauses) > 1:

6414 raise exc.ArgumentError(

6415 "additional FROM objects not accepted when "

6416 "passing None/False to correlate_except()"

6417 )

6418 self._correlate_except = ()

6419 else:

6420 self._correlate_except = (self._correlate_except or ()) + tuple(

6421 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6422 )

6423

6424 return self

6425

6426 @HasMemoized_ro_memoized_attribute

6427 def selected_columns(

6428 self,

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

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

6431 representing the columns that

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

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

6434

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

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

6437 within this collection cannot be directly nested inside another SELECT

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

6439 necessary parenthesization required by SQL.

6440

6441 For a :func:`_expression.select` construct, the collection here is

6442 exactly what would be rendered inside the "SELECT" statement, and the

6443 :class:`_expression.ColumnElement` objects are directly present as they

6444 were given, e.g.::

6445

6446 col1 = column("q", Integer)

6447 col2 = column("p", Integer)

6448 stmt = select(col1, col2)

6449

6450 Above, ``stmt.selected_columns`` would be a collection that contains

6451 the ``col1`` and ``col2`` objects directly. For a statement that is

6452 against a :class:`_schema.Table` or other

6453 :class:`_expression.FromClause`, the collection will use the

6454 :class:`_expression.ColumnElement` objects that are in the

6455 :attr:`_expression.FromClause.c` collection of the from element.

6456

6457 A use case for the :attr:`_sql.Select.selected_columns` collection is

6458 to allow the existing columns to be referenced when adding additional

6459 criteria, e.g.::

6460

6461 def filter_on_id(my_select, id):

6462 return my_select.where(my_select.selected_columns["id"] == id)

6463

6464

6465 stmt = select(MyModel)

6466

6467 # adds "WHERE id=:param" to the statement

6468 stmt = filter_on_id(stmt, 42)

6469

6470 .. note::

6471

6472 The :attr:`_sql.Select.selected_columns` collection does not

6473 include expressions established in the columns clause using the

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

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

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

6477 construct.

6478

6479

6480 .. versionadded:: 1.4

6481

6482 """

6483

6484 # compare to SelectState._generate_columns_plus_names, which

6485 # generates the actual names used in the SELECT string. that

6486 # method is more complex because it also renders columns that are

6487 # fully ambiguous, e.g. same column more than once.

6488 conv = cast(

6489 "Callable[[Any], str]",

6490 SelectState._column_naming_convention(self._label_style),

6491 )

6492

6493 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6494 [

6495 (conv(c), c)

6496 for c in self._all_selected_columns

6497 if is_column_element(c)

6498 ]

6499 )

6500 return cc.as_readonly()

6501

6502 @HasMemoized_ro_memoized_attribute

6503 def _all_selected_columns(self) -> _SelectIterable:

6504 meth = SelectState.get_plugin_class(self).all_selected_columns

6505 return list(meth(self))

6506

6507 def _ensure_disambiguated_names(self) -> Select[Any]:

6508 if self._label_style is LABEL_STYLE_NONE:

6509 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6510 return self

6511

6512 def _generate_fromclause_column_proxies(

6513 self,

6514 subquery: FromClause,

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

6516 primary_key: ColumnSet,

6517 foreign_keys: Set[KeyedColumnElement[Any]],

6518 *,

6519 proxy_compound_columns: Optional[

6520 Iterable[Sequence[ColumnElement[Any]]]

6521 ] = None,

6522 ) -> None:

6523 """Generate column proxies to place in the exported ``.c``

6524 collection of a subquery."""

6525

6526 if proxy_compound_columns:

6527 extra_col_iterator = proxy_compound_columns

6528 prox = [

6529 c._make_proxy(

6530 subquery,

6531 key=proxy_key,

6532 name=required_label_name,

6533 name_is_truncatable=True,

6534 compound_select_cols=extra_cols,

6535 primary_key=primary_key,

6536 foreign_keys=foreign_keys,

6537 )

6538 for (

6539 (

6540 required_label_name,

6541 proxy_key,

6542 fallback_label_name,

6543 c,

6544 repeated,

6545 ),

6546 extra_cols,

6547 ) in (

6548 zip(

6549 self._generate_columns_plus_names(False),

6550 extra_col_iterator,

6551 )

6552 )

6553 if is_column_element(c)

6554 ]

6555 else:

6556 prox = [

6557 c._make_proxy(

6558 subquery,

6559 key=proxy_key,

6560 name=required_label_name,

6561 name_is_truncatable=True,

6562 primary_key=primary_key,

6563 foreign_keys=foreign_keys,

6564 )

6565 for (

6566 required_label_name,

6567 proxy_key,

6568 fallback_label_name,

6569 c,

6570 repeated,

6571 ) in (self._generate_columns_plus_names(False))

6572 if is_column_element(c)

6573 ]

6574

6575 columns._populate_separate_keys(prox)

6576

6577 def _needs_parens_for_grouping(self) -> bool:

6578 return self._has_row_limiting_clause or bool(

6579 self._order_by_clause.clauses

6580 )

6581

6582 def self_group(

6583 self, against: Optional[OperatorType] = None

6584 ) -> Union[SelectStatementGrouping[Self], Self]:

6585 """Return a 'grouping' construct as per the

6586 :class:`_expression.ClauseElement` specification.

6587

6588 This produces an element that can be embedded in an expression. Note

6589 that this method is called automatically as needed when constructing

6590 expressions and should not require explicit use.

6591

6592 """

6593 if (

6594 isinstance(against, CompoundSelect)

6595 and not self._needs_parens_for_grouping()

6596 ):

6597 return self

6598 else:

6599 return SelectStatementGrouping(self)

6600

6601 def union(

6602 self, *other: _SelectStatementForCompoundArgument[_TP]

6603 ) -> CompoundSelect[_TP]:

6604 r"""Return a SQL ``UNION`` of this select() construct against

6605 the given selectables provided as positional arguments.

6606

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

6608 UNION.

6609

6610 .. versionchanged:: 1.4.28

6611

6612 multiple elements are now accepted.

6613

6614 :param \**kwargs: keyword arguments are forwarded to the constructor

6615 for the newly created :class:`_sql.CompoundSelect` object.

6616

6617 """

6618 return CompoundSelect._create_union(self, *other)

6619

6620 def union_all(

6621 self, *other: _SelectStatementForCompoundArgument[_TP]

6622 ) -> CompoundSelect[_TP]:

6623 r"""Return a SQL ``UNION ALL`` of this select() construct against

6624 the given selectables provided as positional arguments.

6625

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

6627 UNION.

6628

6629 .. versionchanged:: 1.4.28

6630

6631 multiple elements are now accepted.

6632

6633 :param \**kwargs: keyword arguments are forwarded to the constructor

6634 for the newly created :class:`_sql.CompoundSelect` object.

6635

6636 """

6637 return CompoundSelect._create_union_all(self, *other)

6638

6639 def except_(

6640 self, *other: _SelectStatementForCompoundArgument[_TP]

6641 ) -> CompoundSelect[_TP]:

6642 r"""Return a SQL ``EXCEPT`` of this select() construct against

6643 the given selectable provided as positional arguments.

6644

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

6646 UNION.

6647

6648 .. versionchanged:: 1.4.28

6649

6650 multiple elements are now accepted.

6651

6652 """

6653 return CompoundSelect._create_except(self, *other)

6654

6655 def except_all(

6656 self, *other: _SelectStatementForCompoundArgument[_TP]

6657 ) -> CompoundSelect[_TP]:

6658 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6659 the given selectables provided as positional arguments.

6660

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

6662 UNION.

6663

6664 .. versionchanged:: 1.4.28

6665

6666 multiple elements are now accepted.

6667

6668 """

6669 return CompoundSelect._create_except_all(self, *other)

6670

6671 def intersect(

6672 self, *other: _SelectStatementForCompoundArgument[_TP]

6673 ) -> CompoundSelect[_TP]:

6674 r"""Return a SQL ``INTERSECT`` of this select() construct against

6675 the given selectables provided as positional arguments.

6676

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

6678 UNION.

6679

6680 .. versionchanged:: 1.4.28

6681

6682 multiple elements are now accepted.

6683

6684 :param \**kwargs: keyword arguments are forwarded to the constructor

6685 for the newly created :class:`_sql.CompoundSelect` object.

6686

6687 """

6688 return CompoundSelect._create_intersect(self, *other)

6689

6690 def intersect_all(

6691 self, *other: _SelectStatementForCompoundArgument[_TP]

6692 ) -> CompoundSelect[_TP]:

6693 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6694 against the given selectables provided as positional arguments.

6695

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

6697 UNION.

6698

6699 .. versionchanged:: 1.4.28

6700

6701 multiple elements are now accepted.

6702

6703 :param \**kwargs: keyword arguments are forwarded to the constructor

6704 for the newly created :class:`_sql.CompoundSelect` object.

6705

6706 """

6707 return CompoundSelect._create_intersect_all(self, *other)

6708

6709

6710class ScalarSelect(

6711 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6712):

6713 """Represent a scalar subquery.

6714

6715

6716 A :class:`_sql.ScalarSelect` is created by invoking the

6717 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6718 then participates in other SQL expressions as a SQL column expression

6719 within the :class:`_sql.ColumnElement` hierarchy.

6720

6721 .. seealso::

6722

6723 :meth:`_sql.SelectBase.scalar_subquery`

6724

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

6726

6727 """

6728

6729 _traverse_internals: _TraverseInternalsType = [

6730 ("element", InternalTraversal.dp_clauseelement),

6731 ("type", InternalTraversal.dp_type),

6732 ]

6733

6734 _from_objects: List[FromClause] = []

6735 _is_from_container = True

6736 if not TYPE_CHECKING:

6737 _is_implicitly_boolean = False

6738 inherit_cache = True

6739

6740 element: SelectBase

6741

6742 def __init__(self, element: SelectBase) -> None:

6743 self.element = element

6744 self.type = element._scalar_type()

6745 self._propagate_attrs = element._propagate_attrs

6746

6747 def __getattr__(self, attr: str) -> Any:

6748 return getattr(self.element, attr)

6749

6750 def __getstate__(self) -> Dict[str, Any]:

6751 return {"element": self.element, "type": self.type}

6752

6753 def __setstate__(self, state: Dict[str, Any]) -> None:

6754 self.element = state["element"]

6755 self.type = state["type"]

6756

6757 @property

6758 def columns(self) -> NoReturn:

6759 raise exc.InvalidRequestError(

6760 "Scalar Select expression has no "

6761 "columns; use this object directly "

6762 "within a column-level expression."

6763 )

6764

6765 c = columns

6766

6767 @_generative

6768 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6769 """Apply a WHERE clause to the SELECT statement referred to

6770 by this :class:`_expression.ScalarSelect`.

6771

6772 """

6773 self.element = cast("Select[Any]", self.element).where(crit)

6774 return self

6775

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

6777 return self

6778

6779 if TYPE_CHECKING:

6780

6781 def _ungroup(self) -> Select[Any]: ...

6782

6783 @_generative

6784 def correlate(

6785 self,

6786 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6787 ) -> Self:

6788 r"""Return a new :class:`_expression.ScalarSelect`

6789 which will correlate the given FROM

6790 clauses to that of an enclosing :class:`_expression.Select`.

6791

6792 This method is mirrored from the :meth:`_sql.Select.correlate` method

6793 of the underlying :class:`_sql.Select`. The method applies the

6794 :meth:_sql.Select.correlate` method, then returns a new

6795 :class:`_sql.ScalarSelect` against that statement.

6796

6797 .. versionadded:: 1.4 Previously, the

6798 :meth:`_sql.ScalarSelect.correlate`

6799 method was only available from :class:`_sql.Select`.

6800

6801 :param \*fromclauses: a list of one or more

6802 :class:`_expression.FromClause`

6803 constructs, or other compatible constructs (i.e. ORM-mapped

6804 classes) to become part of the correlate collection.

6805

6806 .. seealso::

6807

6808 :meth:`_expression.ScalarSelect.correlate_except`

6809

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

6811

6812

6813 """

6814 self.element = cast("Select[Any]", self.element).correlate(

6815 *fromclauses

6816 )

6817 return self

6818

6819 @_generative

6820 def correlate_except(

6821 self,

6822 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6823 ) -> Self:

6824 r"""Return a new :class:`_expression.ScalarSelect`

6825 which will omit the given FROM

6826 clauses from the auto-correlation process.

6827

6828 This method is mirrored from the

6829 :meth:`_sql.Select.correlate_except` method of the underlying

6830 :class:`_sql.Select`. The method applies the

6831 :meth:_sql.Select.correlate_except` method, then returns a new

6832 :class:`_sql.ScalarSelect` against that statement.

6833

6834 .. versionadded:: 1.4 Previously, the

6835 :meth:`_sql.ScalarSelect.correlate_except`

6836 method was only available from :class:`_sql.Select`.

6837

6838 :param \*fromclauses: a list of one or more

6839 :class:`_expression.FromClause`

6840 constructs, or other compatible constructs (i.e. ORM-mapped

6841 classes) to become part of the correlate-exception collection.

6842

6843 .. seealso::

6844

6845 :meth:`_expression.ScalarSelect.correlate`

6846

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

6848

6849

6850 """

6851

6852 self.element = cast("Select[Any]", self.element).correlate_except(

6853 *fromclauses

6854 )

6855 return self

6856

6857

6858class Exists(UnaryExpression[bool]):

6859 """Represent an ``EXISTS`` clause.

6860

6861 See :func:`_sql.exists` for a description of usage.

6862

6863 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6864 instance by calling :meth:`_sql.SelectBase.exists`.

6865

6866 """

6867

6868 inherit_cache = True

6869 element: Union[SelectStatementGrouping[Select[Any]], ScalarSelect[Any]]

6870

6871 def __init__(

6872 self,

6873 __argument: Optional[

6874 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6875 ] = None,

6876 ):

6877 s: ScalarSelect[Any]

6878

6879 # TODO: this seems like we should be using coercions for this

6880 if __argument is None:

6881 s = Select(literal_column("*")).scalar_subquery()

6882 elif isinstance(__argument, SelectBase):

6883 s = __argument.scalar_subquery()

6884 s._propagate_attrs = __argument._propagate_attrs

6885 elif isinstance(__argument, ScalarSelect):

6886 s = __argument

6887 else:

6888 s = Select(__argument).scalar_subquery()

6889

6890 UnaryExpression.__init__(

6891 self,

6892 s,

6893 operator=operators.exists,

6894 type_=type_api.BOOLEANTYPE,

6895 wraps_column_expression=True,

6896 )

6897

6898 @util.ro_non_memoized_property

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

6900 return []

6901

6902 def _regroup(

6903 self, fn: Callable[[Select[Any]], Select[Any]]

6904 ) -> SelectStatementGrouping[Select[Any]]:

6905 element = self.element._ungroup()

6906 new_element = fn(element)

6907

6908 return_value = new_element.self_group(against=operators.exists)

6909 assert isinstance(return_value, SelectStatementGrouping)

6910 return return_value

6911

6912 def select(self) -> Select[Tuple[bool]]:

6913 r"""Return a SELECT of this :class:`_expression.Exists`.

6914

6915 e.g.::

6916

6917 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

6918

6919 This will produce a statement resembling:

6920

6921 .. sourcecode:: sql

6922

6923 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

6924

6925 .. seealso::

6926

6927 :func:`_expression.select` - general purpose

6928 method which allows for arbitrary column lists.

6929

6930 """ # noqa

6931

6932 return Select(self)

6933

6934 def correlate(

6935 self,

6936 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6937 ) -> Self:

6938 """Apply correlation to the subquery noted by this

6939 :class:`_sql.Exists`.

6940

6941 .. seealso::

6942

6943 :meth:`_sql.ScalarSelect.correlate`

6944

6945 """

6946 e = self._clone()

6947 e.element = self._regroup(

6948 lambda element: element.correlate(*fromclauses)

6949 )

6950 return e

6951

6952 def correlate_except(

6953 self,

6954 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6955 ) -> Self:

6956 """Apply correlation to the subquery noted by this

6957 :class:`_sql.Exists`.

6958

6959 .. seealso::

6960

6961 :meth:`_sql.ScalarSelect.correlate_except`

6962

6963 """

6964

6965 e = self._clone()

6966 e.element = self._regroup(

6967 lambda element: element.correlate_except(*fromclauses)

6968 )

6969 return e

6970

6971 def select_from(self, *froms: _FromClauseArgument) -> Self:

6972 """Return a new :class:`_expression.Exists` construct,

6973 applying the given

6974 expression to the :meth:`_expression.Select.select_from`

6975 method of the select

6976 statement contained.

6977

6978 .. note:: it is typically preferable to build a :class:`_sql.Select`

6979 statement first, including the desired WHERE clause, then use the

6980 :meth:`_sql.SelectBase.exists` method to produce an

6981 :class:`_sql.Exists` object at once.

6982

6983 """

6984 e = self._clone()

6985 e.element = self._regroup(lambda element: element.select_from(*froms))

6986 return e

6987

6988 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

6989 """Return a new :func:`_expression.exists` construct with the

6990 given expression added to

6991 its WHERE clause, joined to the existing clause via AND, if any.

6992

6993

6994 .. note:: it is typically preferable to build a :class:`_sql.Select`

6995 statement first, including the desired WHERE clause, then use the

6996 :meth:`_sql.SelectBase.exists` method to produce an

6997 :class:`_sql.Exists` object at once.

6998

6999 """

7000 e = self._clone()

7001 e.element = self._regroup(lambda element: element.where(*clause))

7002 return e

7003

7004

7005class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7006 """Wrap a :class:`_expression.TextClause` construct within a

7007 :class:`_expression.SelectBase`

7008 interface.

7009

7010 This allows the :class:`_expression.TextClause` object to gain a

7011 ``.c`` collection

7012 and other FROM-like capabilities such as

7013 :meth:`_expression.FromClause.alias`,

7014 :meth:`_expression.SelectBase.cte`, etc.

7015

7016 The :class:`_expression.TextualSelect` construct is produced via the

7017 :meth:`_expression.TextClause.columns`

7018 method - see that method for details.

7019

7020 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7021 class was renamed

7022 from ``TextAsFrom``, to more correctly suit its role as a

7023 SELECT-oriented object and not a FROM clause.

7024

7025 .. seealso::

7026

7027 :func:`_expression.text`

7028

7029 :meth:`_expression.TextClause.columns` - primary creation interface.

7030

7031 """

7032

7033 __visit_name__ = "textual_select"

7034

7035 _label_style = LABEL_STYLE_NONE

7036

7037 _traverse_internals: _TraverseInternalsType = (

7038 [

7039 ("element", InternalTraversal.dp_clauseelement),

7040 ("column_args", InternalTraversal.dp_clauseelement_list),

7041 ]

7042 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7043 + HasCTE._has_ctes_traverse_internals

7044 )

7045

7046 _is_textual = True

7047

7048 is_text = True

7049 is_select = True

7050

7051 def __init__(

7052 self,

7053 text: TextClause,

7054 columns: List[_ColumnExpressionArgument[Any]],

7055 positional: bool = False,

7056 ) -> None:

7057 self._init(

7058 text,

7059 # convert for ORM attributes->columns, etc

7060 [

7061 coercions.expect(roles.LabeledColumnExprRole, c)

7062 for c in columns

7063 ],

7064 positional,

7065 )

7066

7067 def _init(

7068 self,

7069 text: TextClause,

7070 columns: List[NamedColumn[Any]],

7071 positional: bool = False,

7072 ) -> None:

7073 self.element = text

7074 self.column_args = columns

7075 self.positional = positional

7076

7077 @HasMemoized_ro_memoized_attribute

7078 def selected_columns(

7079 self,

7080 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

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

7082 representing the columns that

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

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

7085

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

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

7088 within this collection cannot be directly nested inside another SELECT

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

7090 necessary parenthesization required by SQL.

7091

7092 For a :class:`_expression.TextualSelect` construct, the collection

7093 contains the :class:`_expression.ColumnElement` objects that were

7094 passed to the constructor, typically via the

7095 :meth:`_expression.TextClause.columns` method.

7096

7097

7098 .. versionadded:: 1.4

7099

7100 """

7101 return ColumnCollection(

7102 (c.key, c) for c in self.column_args

7103 ).as_readonly()

7104

7105 @util.ro_non_memoized_property

7106 def _all_selected_columns(self) -> _SelectIterable:

7107 return self.column_args

7108

7109 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7110 return self

7111

7112 def _ensure_disambiguated_names(self) -> TextualSelect:

7113 return self

7114

7115 @_generative

7116 def bindparams(

7117 self,

7118 *binds: BindParameter[Any],

7119 **bind_as_values: Any,

7120 ) -> Self:

7121 self.element = self.element.bindparams(*binds, **bind_as_values)

7122 return self

7123

7124 def _generate_fromclause_column_proxies(

7125 self,

7126 fromclause: FromClause,

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

7128 primary_key: ColumnSet,

7129 foreign_keys: Set[KeyedColumnElement[Any]],

7130 *,

7131 proxy_compound_columns: Optional[

7132 Iterable[Sequence[ColumnElement[Any]]]

7133 ] = None,

7134 ) -> None:

7135 if TYPE_CHECKING:

7136 assert isinstance(fromclause, Subquery)

7137

7138 if proxy_compound_columns:

7139 columns._populate_separate_keys(

7140 c._make_proxy(

7141 fromclause,

7142 compound_select_cols=extra_cols,

7143 primary_key=primary_key,

7144 foreign_keys=foreign_keys,

7145 )

7146 for c, extra_cols in zip(

7147 self.column_args, proxy_compound_columns

7148 )

7149 )

7150 else:

7151 columns._populate_separate_keys(

7152 c._make_proxy(

7153 fromclause,

7154 primary_key=primary_key,

7155 foreign_keys=foreign_keys,

7156 )

7157 for c in self.column_args

7158 )

7159

7160 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7161 return self.column_args[0].type

7162

7163

7164TextAsFrom = TextualSelect

7165"""Backwards compatibility with the previous name"""

7166

7167

7168class AnnotatedFromClause(Annotated):

7169 def _copy_internals(self, **kw: Any) -> None:

7170 super()._copy_internals(**kw)

7171 if kw.get("ind_cols_on_fromclause", False):

7172 ee = self._Annotated__element # type: ignore

7173

7174 self.c = ee.__class__.c.fget(self) # type: ignore

7175

7176 @util.ro_memoized_property

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

7178 """proxy the .c collection of the underlying FromClause.

7179

7180 Originally implemented in 2008 as a simple load of the .c collection

7181 when the annotated construct was created (see d3621ae961a), in modern

7182 SQLAlchemy versions this can be expensive for statements constructed

7183 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7184 it, which works just as well.

7185

7186 Two different use cases seem to require the collection either copied

7187 from the underlying one, or unique to this AnnotatedFromClause.

7188

7189 See test_selectable->test_annotated_corresponding_column

7190

7191 """

7192 ee = self._Annotated__element # type: ignore

7193 return ee.c # type: ignore