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

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

1160 FROM table1 JOIN table2 ON true

1161

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

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

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

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

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

1167

1168

1169 .. versionadded:: 1.4

1170

1171 """ # noqa: E501

1172

1173 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1176 when generating the columns clause of a SELECT statement.

1177

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

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

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

1181

1182 .. sourcecode:: pycon+sql

1183

1184 >>> from sqlalchemy import (

1185 ... table,

1186 ... column,

1187 ... select,

1188 ... true,

1189 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1190 ... )

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

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

1193 >>> print(

1194 ... select(table1, table2)

1195 ... .join(table2, true())

1196 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1197 ... )

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

1199 FROM table1 JOIN table2 ON true

1200

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

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

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

1204

1205 .. versionadded:: 1.4

1206

1207 """ # noqa: E501

1208

1209 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1210 """The default label style, refers to

1211 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1212

1213 .. versionadded:: 1.4

1214

1215 """

1216

1217 LABEL_STYLE_LEGACY_ORM = 3

1218

1219

1220(

1221 LABEL_STYLE_NONE,

1222 LABEL_STYLE_TABLENAME_PLUS_COL,

1223 LABEL_STYLE_DISAMBIGUATE_ONLY,

1224 _,

1225) = list(SelectLabelStyle)

1226

1227LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1228

1229

1230class Join(roles.DMLTableRole, FromClause):

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

1232 :class:`_expression.FromClause`

1233 elements.

1234

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

1236 is the module-level

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

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

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

1240 :class:`_schema.Table`).

1241

1242 .. seealso::

1243

1244 :func:`_expression.join`

1245

1246 :meth:`_expression.FromClause.join`

1247

1248 """

1249

1250 __visit_name__ = "join"

1251

1252 _traverse_internals: _TraverseInternalsType = [

1253 ("left", InternalTraversal.dp_clauseelement),

1254 ("right", InternalTraversal.dp_clauseelement),

1255 ("onclause", InternalTraversal.dp_clauseelement),

1256 ("isouter", InternalTraversal.dp_boolean),

1257 ("full", InternalTraversal.dp_boolean),

1258 ]

1259

1260 _is_join = True

1261

1262 left: FromClause

1263 right: FromClause

1264 onclause: Optional[ColumnElement[bool]]

1265 isouter: bool

1266 full: bool

1267

1268 def __init__(

1269 self,

1270 left: _FromClauseArgument,

1271 right: _FromClauseArgument,

1272 onclause: Optional[_OnClauseArgument] = None,

1273 isouter: bool = False,

1274 full: bool = False,

1275 ):

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

1277

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

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

1280 :class:`_expression.FromClause` object.

1281

1282 """

1283

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

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

1286 # annotated objects. test_orm.py -> test_fetch_results

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

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

1289 # callcounts for a single compilation in that particular test

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

1291 # 29200 -> 30373

1292

1293 self.left = coercions.expect(

1294 roles.FromClauseRole,

1295 left,

1296 )

1297 self.right = coercions.expect(

1298 roles.FromClauseRole,

1299 right,

1300 ).self_group()

1301

1302 if onclause is None:

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

1304 else:

1305 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1306 # not merged yet

1307 self.onclause = coercions.expect(

1308 roles.OnClauseRole, onclause

1309 ).self_group(against=operators._asbool)

1310

1311 self.isouter = isouter

1312 self.full = full

1313

1314 @util.ro_non_memoized_property

1315 def description(self) -> str:

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

1317 self.left.description,

1318 id(self.left),

1319 self.right.description,

1320 id(self.right),

1321 )

1322

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

1324 return (

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

1326 # as well

1327 hash(fromclause) == hash(self)

1328 or self.left.is_derived_from(fromclause)

1329 or self.right.is_derived_from(fromclause)

1330 )

1331

1332 def self_group(

1333 self, against: Optional[OperatorType] = None

1334 ) -> FromGrouping:

1335 return FromGrouping(self)

1336

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

1338 def _populate_column_collection(

1339 self,

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

1341 primary_key: ColumnSet,

1342 foreign_keys: Set[KeyedColumnElement[Any]],

1343 ) -> None:

1344 sqlutil = util.preloaded.sql_util

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

1346 c for c in self.right.c

1347 ]

1348

1349 primary_key.extend( # type: ignore

1350 sqlutil.reduce_columns(

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

1352 )

1353 )

1354 columns._populate_separate_keys(

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

1356 )

1357 foreign_keys.update(

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

1359 )

1360

1361 def _copy_internals(

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

1363 ) -> None:

1364 # see Select._copy_internals() for similar concept

1365

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

1367 # determine the new FROM clauses

1368 all_the_froms = set(

1369 itertools.chain(

1370 _from_objects(self.left),

1371 _from_objects(self.right),

1372 )

1373 )

1374

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

1376 # cache used by the clone function

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

1378

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

1380 # ColumnClause with parent table referring to those

1381 # replaced FromClause objects

1382 def replace(

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

1384 **kw: Any,

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

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

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

1388 return newelem

1389 return None

1390

1391 kw["replace"] = replace

1392

1393 # run normal _copy_internals. the clones for

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

1395 # cache

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

1397

1398 self._reset_memoizations()

1399

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

1401 super()._refresh_for_new_column(column)

1402 self.left._refresh_for_new_column(column)

1403 self.right._refresh_for_new_column(column)

1404

1405 def _match_primaries(

1406 self,

1407 left: FromClause,

1408 right: FromClause,

1409 ) -> ColumnElement[bool]:

1410 if isinstance(left, Join):

1411 left_right = left.right

1412 else:

1413 left_right = None

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

1415

1416 @classmethod

1417 def _join_condition(

1418 cls,

1419 a: FromClause,

1420 b: FromClause,

1421 *,

1422 a_subset: Optional[FromClause] = None,

1423 consider_as_foreign_keys: Optional[

1424 AbstractSet[ColumnClause[Any]]

1425 ] = None,

1426 ) -> ColumnElement[bool]:

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

1428

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

1430

1431 """

1432 constraints = cls._joincond_scan_left_right(

1433 a, a_subset, b, consider_as_foreign_keys

1434 )

1435

1436 if len(constraints) > 1:

1437 cls._joincond_trim_constraints(

1438 a, b, constraints, consider_as_foreign_keys

1439 )

1440

1441 if len(constraints) == 0:

1442 if isinstance(b, FromGrouping):

1443 hint = (

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

1445 "subquery using alias()?"

1446 )

1447 else:

1448 hint = ""

1449 raise exc.NoForeignKeysError(

1450 "Can't find any foreign key relationships "

1451 "between '%s' and '%s'.%s"

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

1453 )

1454

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

1456 if len(crit) == 1:

1457 return crit[0]

1458 else:

1459 return and_(*crit)

1460

1461 @classmethod

1462 def _can_join(

1463 cls,

1464 left: FromClause,

1465 right: FromClause,

1466 *,

1467 consider_as_foreign_keys: Optional[

1468 AbstractSet[ColumnClause[Any]]

1469 ] = None,

1470 ) -> bool:

1471 if isinstance(left, Join):

1472 left_right = left.right

1473 else:

1474 left_right = None

1475

1476 constraints = cls._joincond_scan_left_right(

1477 a=left,

1478 b=right,

1479 a_subset=left_right,

1480 consider_as_foreign_keys=consider_as_foreign_keys,

1481 )

1482

1483 return bool(constraints)

1484

1485 @classmethod

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

1487 def _joincond_scan_left_right(

1488 cls,

1489 a: FromClause,

1490 a_subset: Optional[FromClause],

1491 b: FromClause,

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

1493 ) -> collections.defaultdict[

1494 Optional[ForeignKeyConstraint],

1495 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1496 ]:

1497 sql_util = util.preloaded.sql_util

1498

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

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

1501

1502 constraints: collections.defaultdict[

1503 Optional[ForeignKeyConstraint],

1504 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1505 ] = collections.defaultdict(list)

1506

1507 for left in (a_subset, a):

1508 if left is None:

1509 continue

1510 for fk in sorted(

1511 b.foreign_keys,

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

1513 ):

1514 if (

1515 consider_as_foreign_keys is not None

1516 and fk.parent not in consider_as_foreign_keys

1517 ):

1518 continue

1519 try:

1520 col = fk.get_referent(left)

1521 except exc.NoReferenceError as nrte:

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

1523 if nrte.table_name in table_names:

1524 raise

1525 else:

1526 continue

1527

1528 if col is not None:

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

1530 if left is not b:

1531 for fk in sorted(

1532 left.foreign_keys,

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

1534 ):

1535 if (

1536 consider_as_foreign_keys is not None

1537 and fk.parent not in consider_as_foreign_keys

1538 ):

1539 continue

1540 try:

1541 col = fk.get_referent(b)

1542 except exc.NoReferenceError as nrte:

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

1544 if nrte.table_name in table_names:

1545 raise

1546 else:

1547 continue

1548

1549 if col is not None:

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

1551 if constraints:

1552 break

1553 return constraints

1554

1555 @classmethod

1556 def _joincond_trim_constraints(

1557 cls,

1558 a: FromClause,

1559 b: FromClause,

1560 constraints: Dict[Any, Any],

1561 consider_as_foreign_keys: Optional[Any],

1562 ) -> None:

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

1564 # to include just those FKCs that match exactly to

1565 # "consider_as_foreign_keys".

1566 if consider_as_foreign_keys:

1567 for const in list(constraints):

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

1569 consider_as_foreign_keys

1570 ):

1571 del constraints[const]

1572

1573 # if still multiple constraints, but

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

1575 if len(constraints) > 1:

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

1577 if len(dedupe) == 1:

1578 key = list(constraints)[0]

1579 constraints = {key: constraints[key]}

1580

1581 if len(constraints) != 1:

1582 raise exc.AmbiguousForeignKeysError(

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

1584 "tables have more than one foreign key "

1585 "constraint relationship between them. "

1586 "Please specify the 'onclause' of this "

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

1588 )

1589

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

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

1592 :class:`_expression.Join`.

1593

1594 E.g.::

1595

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

1597

1598 stmt = stmt.select()

1599

1600 The above will produce a SQL string resembling:

1601

1602 .. sourcecode:: sql

1603

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

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

1606

1607 """

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

1609

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

1611 def _anonymous_fromclause(

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

1613 ) -> TODO_Any:

1614 sqlutil = util.preloaded.sql_util

1615 if flat:

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

1617 left_name = name # will recurse

1618 else:

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

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

1621 else:

1622 left_name = name

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

1624 right_name = name # will recurse

1625 else:

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

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

1628 else:

1629 right_name = name

1630 left_a, right_a = (

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

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

1633 )

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

1635 sqlutil.ClauseAdapter(right_a)

1636 )

1637

1638 return left_a.join(

1639 right_a,

1640 adapter.traverse(self.onclause),

1641 isouter=self.isouter,

1642 full=self.full,

1643 )

1644 else:

1645 return (

1646 self.select()

1647 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1648 .correlate(None)

1649 .alias(name)

1650 )

1651

1652 @util.ro_non_memoized_property

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

1654 return itertools.chain(

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

1656 )

1657

1658 @util.ro_non_memoized_property

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

1660 self_list: List[FromClause] = [self]

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

1662

1663

1664class NoInit:

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

1666 raise NotImplementedError(

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

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

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

1670 "selectable objects."

1671 % (

1672 self.__class__.__name__,

1673 self.__class__.__name__.lower(),

1674 self.__class__.__name__.lower(),

1675 )

1676 )

1677

1678

1679class LateralFromClause(NamedFromClause):

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

1681

1682

1683# FromClause ->

1684# AliasedReturnsRows

1685# -> Alias only for FromClause

1686# -> Subquery only for SelectBase

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

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

1689# w/ non-deprecated coercion

1690# -> TableSample -> only for FromClause

1691

1692

1693class AliasedReturnsRows(NoInit, NamedFromClause):

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

1695 selectables."""

1696

1697 _is_from_container = True

1698

1699 _supports_derived_columns = False

1700

1701 element: ReturnsRows

1702

1703 _traverse_internals: _TraverseInternalsType = [

1704 ("element", InternalTraversal.dp_clauseelement),

1705 ("name", InternalTraversal.dp_anon_name),

1706 ]

1707

1708 @classmethod

1709 def _construct(

1710 cls,

1711 selectable: Any,

1712 *,

1713 name: Optional[str] = None,

1714 **kw: Any,

1715 ) -> Self:

1716 obj = cls.__new__(cls)

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

1718 return obj

1719

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

1721 self.element = coercions.expect(

1722 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1723 )

1724 self.element = selectable

1725 self._orig_name = name

1726 if name is None:

1727 if (

1728 isinstance(selectable, FromClause)

1729 and selectable.named_with_column

1730 ):

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

1732 if isinstance(name, _anonymous_label):

1733 name = None

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

1735 self.name = name

1736

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

1738 super()._refresh_for_new_column(column)

1739 self.element._refresh_for_new_column(column)

1740

1741 def _populate_column_collection(

1742 self,

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

1744 primary_key: ColumnSet,

1745 foreign_keys: Set[KeyedColumnElement[Any]],

1746 ) -> None:

1747 self.element._generate_fromclause_column_proxies(

1748 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1749 )

1750

1751 @util.ro_non_memoized_property

1752 def description(self) -> str:

1753 name = self.name

1754 if isinstance(name, _anonymous_label):

1755 return "anon_1"

1756

1757 return name

1758

1759 @util.ro_non_memoized_property

1760 def implicit_returning(self) -> bool:

1761 return self.element.implicit_returning # type: ignore

1762

1763 @property

1764 def original(self) -> ReturnsRows:

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

1766 return self.element

1767

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

1769 if fromclause in self._cloned_set:

1770 return True

1771 return self.element.is_derived_from(fromclause)

1772

1773 def _copy_internals(

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

1775 ) -> None:

1776 existing_element = self.element

1777

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

1779

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

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

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

1783 # performance.

1784 if existing_element is not self.element:

1785 self._reset_column_collection()

1786

1787 @property

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

1789 return [self]

1790

1791

1792class FromClauseAlias(AliasedReturnsRows):

1793 element: FromClause

1794

1795 @util.ro_non_memoized_property

1796 def description(self) -> str:

1797 name = self.name

1798 if isinstance(name, _anonymous_label):

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

1800

1801 return name

1802

1803

1804class Alias(roles.DMLTableRole, FromClauseAlias):

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

1806

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

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

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

1810

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

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

1813 method available

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

1815

1816 .. seealso::

1817

1818 :meth:`_expression.FromClause.alias`

1819

1820 """

1821

1822 __visit_name__ = "alias"

1823

1824 inherit_cache = True

1825

1826 element: FromClause

1827

1828 @classmethod

1829 def _factory(

1830 cls,

1831 selectable: FromClause,

1832 name: Optional[str] = None,

1833 flat: bool = False,

1834 ) -> NamedFromClause:

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

1836 name=name, flat=flat

1837 )

1838

1839

1840class TableValuedAlias(LateralFromClause, Alias):

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

1842

1843 This construct provides for a SQL function that returns columns

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

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

1846 method, e.g.:

1847

1848 .. sourcecode:: pycon+sql

1849

1850 >>> from sqlalchemy import select, func

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

1852 ... "value"

1853 ... )

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

1855 {printsql}SELECT anon_1.value

1856 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1857

1858 .. versionadded:: 1.4.0b2

1859

1860 .. seealso::

1861

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

1863

1864 """ # noqa: E501

1865

1866 __visit_name__ = "table_valued_alias"

1867

1868 _supports_derived_columns = True

1869 _render_derived = False

1870 _render_derived_w_types = False

1871 joins_implicitly = False

1872

1873 _traverse_internals: _TraverseInternalsType = [

1874 ("element", InternalTraversal.dp_clauseelement),

1875 ("name", InternalTraversal.dp_anon_name),

1876 ("_tableval_type", InternalTraversal.dp_type),

1877 ("_render_derived", InternalTraversal.dp_boolean),

1878 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1879 ]

1880

1881 def _init(

1882 self,

1883 selectable: Any,

1884 *,

1885 name: Optional[str] = None,

1886 table_value_type: Optional[TableValueType] = None,

1887 joins_implicitly: bool = False,

1888 ) -> None:

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

1890

1891 self.joins_implicitly = joins_implicitly

1892 self._tableval_type = (

1893 type_api.TABLEVALUE

1894 if table_value_type is None

1895 else table_value_type

1896 )

1897

1898 @HasMemoized.memoized_attribute

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

1900 """Return a column expression representing this

1901 :class:`_sql.TableValuedAlias`.

1902

1903 This accessor is used to implement the

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

1905 method for further details.

1906

1907 E.g.:

1908

1909 .. sourcecode:: pycon+sql

1910

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

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

1913

1914 .. seealso::

1915

1916 :meth:`_functions.FunctionElement.column_valued`

1917

1918 """

1919

1920 return TableValuedColumn(self, self._tableval_type)

1921

1922 def alias(

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

1924 ) -> TableValuedAlias:

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

1926

1927 This creates a distinct FROM object that will be distinguished

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

1929

1930 """

1931

1932 tva: TableValuedAlias = TableValuedAlias._construct(

1933 self,

1934 name=name,

1935 table_value_type=self._tableval_type,

1936 joins_implicitly=self.joins_implicitly,

1937 )

1938

1939 if self._render_derived:

1940 tva._render_derived = True

1941 tva._render_derived_w_types = self._render_derived_w_types

1942

1943 return tva

1944

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

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

1947 set, so that it renders as LATERAL.

1948

1949 .. seealso::

1950

1951 :func:`_expression.lateral`

1952

1953 """

1954 tva = self.alias(name=name)

1955 tva._is_lateral = True

1956 return tva

1957

1958 def render_derived(

1959 self,

1960 name: Optional[str] = None,

1961 with_types: bool = False,

1962 ) -> TableValuedAlias:

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

1964

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

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

1967

1968 .. sourcecode:: pycon+sql

1969

1970 >>> print(

1971 ... select(

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

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

1974 ... .render_derived()

1975 ... )

1976 ... )

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

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

1979

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

1981 the alias expression (this syntax currently applies to the

1982 PostgreSQL database):

1983

1984 .. sourcecode:: pycon+sql

1985

1986 >>> print(

1987 ... select(

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

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

1990 ... .render_derived(with_types=True)

1991 ... )

1992 ... )

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

1994 AS anon_1(a INTEGER, b VARCHAR)

1995

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

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

1998

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

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

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

2002

2003 """ # noqa: E501

2004

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

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

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

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

2009 # (just saw it happen on CI)

2010

2011 # construct against original to prevent memory growth

2012 # for repeated generations

2013 new_alias: TableValuedAlias = TableValuedAlias._construct(

2014 self.element,

2015 name=name,

2016 table_value_type=self._tableval_type,

2017 joins_implicitly=self.joins_implicitly,

2018 )

2019 new_alias._render_derived = True

2020 new_alias._render_derived_w_types = with_types

2021 return new_alias

2022

2023

2024class Lateral(FromClauseAlias, LateralFromClause):

2025 """Represent a LATERAL subquery.

2026

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

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

2029 method available

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

2031

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

2033 PostgreSQL versions provide support for this keyword.

2034

2035 .. seealso::

2036

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

2038

2039 """

2040

2041 __visit_name__ = "lateral"

2042 _is_lateral = True

2043

2044 inherit_cache = True

2045

2046 @classmethod

2047 def _factory(

2048 cls,

2049 selectable: Union[SelectBase, _FromClauseArgument],

2050 name: Optional[str] = None,

2051 ) -> LateralFromClause:

2052 return coercions.expect(

2053 roles.FromClauseRole, selectable, explicit_subquery=True

2054 ).lateral(name=name)

2055

2056

2057class TableSample(FromClauseAlias):

2058 """Represent a TABLESAMPLE clause.

2059

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

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

2062 method

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

2064

2065 .. seealso::

2066

2067 :func:`_expression.tablesample`

2068

2069 """

2070

2071 __visit_name__ = "tablesample"

2072

2073 _traverse_internals: _TraverseInternalsType = (

2074 AliasedReturnsRows._traverse_internals

2075 + [

2076 ("sampling", InternalTraversal.dp_clauseelement),

2077 ("seed", InternalTraversal.dp_clauseelement),

2078 ]

2079 )

2080

2081 @classmethod

2082 def _factory(

2083 cls,

2084 selectable: _FromClauseArgument,

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

2086 name: Optional[str] = None,

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

2088 ) -> TableSample:

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

2090 sampling, name=name, seed=seed

2091 )

2092

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

2094 def _init( # type: ignore[override]

2095 self,

2096 selectable: Any,

2097 *,

2098 name: Optional[str] = None,

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

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

2101 ) -> None:

2102 assert sampling is not None

2103 functions = util.preloaded.sql_functions

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

2105 sampling = functions.func.system(sampling)

2106

2107 self.sampling: Function[Any] = sampling

2108 self.seed = seed

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

2110

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

2112 return self.sampling

2113

2114

2115class CTE(

2116 roles.DMLTableRole,

2117 roles.IsCTERole,

2118 Generative,

2119 HasPrefixes,

2120 HasSuffixes,

2121 AliasedReturnsRows,

2122):

2123 """Represent a Common Table Expression.

2124

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

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

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

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

2129 :class:`_sql.Update` and

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

2131 usage details on CTEs.

2132

2133 .. seealso::

2134

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

2136

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

2138

2139 """

2140

2141 __visit_name__ = "cte"

2142

2143 _traverse_internals: _TraverseInternalsType = (

2144 AliasedReturnsRows._traverse_internals

2145 + [

2146 ("_cte_alias", InternalTraversal.dp_clauseelement),

2147 ("_restates", InternalTraversal.dp_clauseelement),

2148 ("recursive", InternalTraversal.dp_boolean),

2149 ("nesting", InternalTraversal.dp_boolean),

2150 ]

2151 + HasPrefixes._has_prefixes_traverse_internals

2152 + HasSuffixes._has_suffixes_traverse_internals

2153 )

2154

2155 element: HasCTE

2156

2157 @classmethod

2158 def _factory(

2159 cls,

2160 selectable: HasCTE,

2161 name: Optional[str] = None,

2162 recursive: bool = False,

2163 ) -> CTE:

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

2165 or Common Table Expression instance.

2166

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

2168

2169 """

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

2171 name=name, recursive=recursive

2172 )

2173

2174 def _init(

2175 self,

2176 selectable: Select[Unpack[TupleAny]],

2177 *,

2178 name: Optional[str] = None,

2179 recursive: bool = False,

2180 nesting: bool = False,

2181 _cte_alias: Optional[CTE] = None,

2182 _restates: Optional[CTE] = None,

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

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

2185 ) -> None:

2186 self.recursive = recursive

2187 self.nesting = nesting

2188 self._cte_alias = _cte_alias

2189 # Keep recursivity reference with union/union_all

2190 self._restates = _restates

2191 if _prefixes:

2192 self._prefixes = _prefixes

2193 if _suffixes:

2194 self._suffixes = _suffixes

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

2196

2197 def _populate_column_collection(

2198 self,

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

2200 primary_key: ColumnSet,

2201 foreign_keys: Set[KeyedColumnElement[Any]],

2202 ) -> None:

2203 if self._cte_alias is not None:

2204 self._cte_alias._generate_fromclause_column_proxies(

2205 self,

2206 columns,

2207 primary_key=primary_key,

2208 foreign_keys=foreign_keys,

2209 )

2210 else:

2211 self.element._generate_fromclause_column_proxies(

2212 self,

2213 columns,

2214 primary_key=primary_key,

2215 foreign_keys=foreign_keys,

2216 )

2217

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

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

2220 :class:`_expression.CTE`.

2221

2222 This method is a CTE-specific specialization of the

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

2224

2225 .. seealso::

2226

2227 :ref:`tutorial_using_aliases`

2228

2229 :func:`_expression.alias`

2230

2231 """

2232 return CTE._construct(

2233 self.element,

2234 name=name,

2235 recursive=self.recursive,

2236 nesting=self.nesting,

2237 _cte_alias=self,

2238 _prefixes=self._prefixes,

2239 _suffixes=self._suffixes,

2240 )

2241

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

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

2244 of the original CTE against the given selectables provided

2245 as positional arguments.

2246

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

2248 UNION.

2249

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

2251

2252 .. seealso::

2253

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

2255

2256 """

2257 assert is_select_statement(

2258 self.element

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

2260

2261 return CTE._construct(

2262 self.element.union(*other),

2263 name=self.name,

2264 recursive=self.recursive,

2265 nesting=self.nesting,

2266 _restates=self,

2267 _prefixes=self._prefixes,

2268 _suffixes=self._suffixes,

2269 )

2270

2271 def union_all(

2272 self, *other: _SelectStatementForCompoundArgument[Any]

2273 ) -> CTE:

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

2275 of the original CTE against the given selectables provided

2276 as positional arguments.

2277

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

2279 UNION.

2280

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

2282

2283 .. seealso::

2284

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

2286

2287 """

2288

2289 assert is_select_statement(

2290 self.element

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

2292

2293 return CTE._construct(

2294 self.element.union_all(*other),

2295 name=self.name,

2296 recursive=self.recursive,

2297 nesting=self.nesting,

2298 _restates=self,

2299 _prefixes=self._prefixes,

2300 _suffixes=self._suffixes,

2301 )

2302

2303 def _get_reference_cte(self) -> CTE:

2304 """

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

2306 Updated CTEs should still refer to the original CTE.

2307 This function returns this reference identifier.

2308 """

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

2310

2311

2312class _CTEOpts(NamedTuple):

2313 nesting: bool

2314

2315

2316class _ColumnsPlusNames(NamedTuple):

2317 required_label_name: Optional[str]

2318 """

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

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

2321 """

2322

2323 proxy_key: Optional[str]

2324 """

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

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

2327 select.selected_columns collection

2328 """

2329

2330 fallback_label_name: Optional[str]

2331 """

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

2333 we have to render a label even though

2334 required_label_name was not given

2335 """

2336

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

2338 """

2339 the ColumnElement itself

2340 """

2341

2342 repeated: bool

2343 """

2344 True if this is a duplicate of a previous column

2345 in the list of columns

2346 """

2347

2348

2349class SelectsRows(ReturnsRows):

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

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

2352

2353 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2354

2355 def _generate_columns_plus_names(

2356 self,

2357 anon_for_dupe_key: bool,

2358 cols: Optional[_SelectIterable] = None,

2359 ) -> List[_ColumnsPlusNames]:

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

2361 the compiler.

2362

2363 This is distinct from the _column_naming_convention generator that's

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

2365 different rules. the collection returned here calls upon the

2366 _column_naming_convention as well.

2367

2368 """

2369

2370 if cols is None:

2371 cols = self._all_selected_columns

2372

2373 key_naming_convention = SelectState._column_naming_convention(

2374 self._label_style

2375 )

2376

2377 names = {}

2378

2379 result: List[_ColumnsPlusNames] = []

2380 result_append = result.append

2381

2382 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2383 label_style_none = self._label_style is LABEL_STYLE_NONE

2384

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

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

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

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

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

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

2391 dedupe_hash = 1

2392

2393 for c in cols:

2394 repeated = False

2395

2396 if not c._render_label_in_columns_clause:

2397 effective_name = required_label_name = fallback_label_name = (

2398 None

2399 )

2400 elif label_style_none:

2401 if TYPE_CHECKING:

2402 assert is_column_element(c)

2403

2404 effective_name = required_label_name = None

2405 fallback_label_name = c._non_anon_label or c._anon_name_label

2406 else:

2407 if TYPE_CHECKING:

2408 assert is_column_element(c)

2409

2410 if table_qualified:

2411 required_label_name = effective_name = (

2412 fallback_label_name

2413 ) = c._tq_label

2414 else:

2415 effective_name = fallback_label_name = c._non_anon_label

2416 required_label_name = None

2417

2418 if effective_name is None:

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

2420 # not need _expression_label but it isn't

2421 # giving us a clue when to use anon_label instead

2422 expr_label = c._expression_label

2423 if expr_label is None:

2424 repeated = c._anon_name_label in names

2425 names[c._anon_name_label] = c

2426 effective_name = required_label_name = None

2427

2428 if repeated:

2429 # here, "required_label_name" is sent as

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

2431 if table_qualified:

2432 fallback_label_name = (

2433 c._dedupe_anon_tq_label_idx(dedupe_hash)

2434 )

2435 dedupe_hash += 1

2436 else:

2437 fallback_label_name = c._dedupe_anon_label_idx(

2438 dedupe_hash

2439 )

2440 dedupe_hash += 1

2441 else:

2442 fallback_label_name = c._anon_name_label

2443 else:

2444 required_label_name = effective_name = (

2445 fallback_label_name

2446 ) = expr_label

2447

2448 if effective_name is not None:

2449 if TYPE_CHECKING:

2450 assert is_column_element(c)

2451

2452 if effective_name in names:

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

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

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

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

2457 # different column under the same name. apply

2458 # disambiguating label

2459 if table_qualified:

2460 required_label_name = fallback_label_name = (

2461 c._anon_tq_label

2462 )

2463 else:

2464 required_label_name = fallback_label_name = (

2465 c._anon_name_label

2466 )

2467

2468 if anon_for_dupe_key and required_label_name in names:

2469 # here, c._anon_tq_label is definitely unique to

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

2471 # this should always be true.

2472 # this is also an infrequent codepath because

2473 # you need two levels of duplication to be here

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

2475

2476 # the column under the disambiguating label is

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

2478 # subsequent occurrences of the column so that the

2479 # original stays non-ambiguous

2480 if table_qualified:

2481 required_label_name = fallback_label_name = (

2482 c._dedupe_anon_tq_label_idx(dedupe_hash)

2483 )

2484 dedupe_hash += 1

2485 else:

2486 required_label_name = fallback_label_name = (

2487 c._dedupe_anon_label_idx(dedupe_hash)

2488 )

2489 dedupe_hash += 1

2490 repeated = True

2491 else:

2492 names[required_label_name] = c

2493 elif anon_for_dupe_key:

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

2495 # label so that the original stays non-ambiguous

2496 if table_qualified:

2497 required_label_name = fallback_label_name = (

2498 c._dedupe_anon_tq_label_idx(dedupe_hash)

2499 )

2500 dedupe_hash += 1

2501 else:

2502 required_label_name = fallback_label_name = (

2503 c._dedupe_anon_label_idx(dedupe_hash)

2504 )

2505 dedupe_hash += 1

2506 repeated = True

2507 else:

2508 names[effective_name] = c

2509

2510 result_append(

2511 _ColumnsPlusNames(

2512 required_label_name,

2513 key_naming_convention(c),

2514 fallback_label_name,

2515 c,

2516 repeated,

2517 )

2518 )

2519

2520 return result

2521

2522

2523class HasCTE(roles.HasCTERole, SelectsRows):

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

2525

2526 _has_ctes_traverse_internals: _TraverseInternalsType = [

2527 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2528 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2529 ]

2530

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

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

2533

2534 @_generative

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

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

2537

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

2539 the parent statement such that they will each be unconditionally

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

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

2542

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

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

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

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

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

2548 statement.

2549

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

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

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

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

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

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

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

2557 larger statement.

2558

2559 E.g.::

2560

2561 from sqlalchemy import table, column, select

2562

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

2564

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

2566

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

2568

2569 Would render:

2570

2571 .. sourcecode:: sql

2572

2573 WITH anon_1 AS (

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

2575 )

2576 SELECT t.c1, t.c2

2577 FROM t

2578

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

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

2581 statement.

2582

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

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

2585

2586 from sqlalchemy import table, column

2587 from sqlalchemy.dialects.postgresql import insert

2588

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

2590

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

2592

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

2594 update_statement = insert_stmt.on_conflict_do_update(

2595 index_elements=[t.c.c1],

2596 set_={

2597 "c1": insert_stmt.excluded.c1,

2598 "c2": insert_stmt.excluded.c2,

2599 },

2600 ).add_cte(delete_statement_cte)

2601

2602 print(update_statement)

2603

2604 The above statement renders as:

2605

2606 .. sourcecode:: sql

2607

2608 WITH deletions AS (

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

2610 )

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

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

2613

2614 .. versionadded:: 1.4.21

2615

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

2617

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

2619

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

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

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

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

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

2625 this statement when this flag is given.

2626

2627 .. versionadded:: 2.0

2628

2629 .. seealso::

2630

2631 :paramref:`.HasCTE.cte.nesting`

2632

2633

2634 """ # noqa: E501

2635 opt = _CTEOpts(nest_here)

2636 for cte in ctes:

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

2638 self._independent_ctes += (cte,)

2639 self._independent_ctes_opts += (opt,)

2640 return self

2641

2642 def cte(

2643 self,

2644 name: Optional[str] = None,

2645 recursive: bool = False,

2646 nesting: bool = False,

2647 ) -> CTE:

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

2649 or Common Table Expression instance.

2650

2651 Common table expressions are a SQL standard whereby SELECT

2652 statements can draw upon secondary statements specified along

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

2654 Special semantics regarding UNION can also be employed to

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

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

2657

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

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

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

2661 CTE rows.

2662

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

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

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

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

2667

2668 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2670 method may be

2671 used to establish these.

2672

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

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

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

2676 compile time.

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

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

2679 conjunction with UNION ALL in order to derive rows

2680 from those already selected.

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

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

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

2684 :paramref:`.HasCTE.add_cte.nest_here`

2685 parameter may also be used to more carefully

2686 control the exact placement of a particular CTE.

2687

2688 .. versionadded:: 1.4.24

2689

2690 .. seealso::

2691

2692 :meth:`.HasCTE.add_cte`

2693

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

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

2696 as well as additional examples.

2697

2698 Example 1, non recursive::

2699

2700 from sqlalchemy import (

2701 Table,

2702 Column,

2703 String,

2704 Integer,

2705 MetaData,

2706 select,

2707 func,

2708 )

2709

2710 metadata = MetaData()

2711

2712 orders = Table(

2713 "orders",

2714 metadata,

2715 Column("region", String),

2716 Column("amount", Integer),

2717 Column("product", String),

2718 Column("quantity", Integer),

2719 )

2720

2721 regional_sales = (

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

2723 .group_by(orders.c.region)

2724 .cte("regional_sales")

2725 )

2726

2727

2728 top_regions = (

2729 select(regional_sales.c.region)

2730 .where(

2731 regional_sales.c.total_sales

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

2733 )

2734 .cte("top_regions")

2735 )

2736

2737 statement = (

2738 select(

2739 orders.c.region,

2740 orders.c.product,

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

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

2743 )

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

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

2746 )

2747

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

2749

2750 Example 2, WITH RECURSIVE::

2751

2752 from sqlalchemy import (

2753 Table,

2754 Column,

2755 String,

2756 Integer,

2757 MetaData,

2758 select,

2759 func,

2760 )

2761

2762 metadata = MetaData()

2763

2764 parts = Table(

2765 "parts",

2766 metadata,

2767 Column("part", String),

2768 Column("sub_part", String),

2769 Column("quantity", Integer),

2770 )

2771

2772 included_parts = (

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

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

2775 .cte(recursive=True)

2776 )

2777

2778

2779 incl_alias = included_parts.alias()

2780 parts_alias = parts.alias()

2781 included_parts = included_parts.union_all(

2782 select(

2783 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2785 )

2786

2787 statement = select(

2788 included_parts.c.sub_part,

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

2790 ).group_by(included_parts.c.sub_part)

2791

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

2793

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

2795

2796 from datetime import date

2797 from sqlalchemy import (

2798 MetaData,

2799 Table,

2800 Column,

2801 Integer,

2802 Date,

2803 select,

2804 literal,

2805 and_,

2806 exists,

2807 )

2808

2809 metadata = MetaData()

2810

2811 visitors = Table(

2812 "visitors",

2813 metadata,

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

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

2816 Column("count", Integer),

2817 )

2818

2819 # add 5 visitors for the product_id == 1

2820 product_id = 1

2821 day = date.today()

2822 count = 5

2823

2824 update_cte = (

2825 visitors.update()

2826 .where(

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

2828 )

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

2830 .returning(literal(1))

2831 .cte("update_cte")

2832 )

2833

2834 upsert = visitors.insert().from_select(

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

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

2837 ~exists(update_cte.select())

2838 ),

2839 )

2840

2841 connection.execute(upsert)

2842

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

2844

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

2846

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

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

2849 "value_a", nesting=True

2850 )

2851

2852 # Nesting CTEs takes ascendency locally

2853 # over the CTEs at a higher level

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

2855

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

2857

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

2859 shown with inline parameters below as:

2860

2861 .. sourcecode:: sql

2862

2863 WITH

2864 value_a AS

2865 (SELECT 'root' AS n),

2866 value_b AS

2867 (WITH value_a AS

2868 (SELECT 'nesting' AS n)

2869 SELECT value_a.n AS n FROM value_a)

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

2871 FROM value_a, value_b

2872

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

2874 as follows (SQLAlchemy 2.0 and above)::

2875

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

2877

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

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

2880

2881 # Nesting CTEs takes ascendency locally

2882 # over the CTEs at a higher level

2883 value_b = (

2884 select(value_a_nested.c.n)

2885 .add_cte(value_a_nested, nest_here=True)

2886 .cte("value_b")

2887 )

2888

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

2890

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

2892

2893 edge = Table(

2894 "edge",

2895 metadata,

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

2897 Column("left", Integer),

2898 Column("right", Integer),

2899 )

2900

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

2902

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

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

2905 )

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

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

2908 )

2909

2910 subgraph_cte = root_node.union(left_edge, right_edge)

2911

2912 subgraph = select(subgraph_cte)

2913

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

2915

2916 .. sourcecode:: sql

2917

2918 WITH RECURSIVE nodes(node) AS (

2919 SELECT 1 AS node

2920 UNION

2921 SELECT edge."left" AS "left"

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

2923 UNION

2924 SELECT edge."right" AS "right"

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

2926 )

2927 SELECT nodes.node FROM nodes

2928

2929 .. seealso::

2930

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

2932 :meth:`_expression.HasCTE.cte`.

2933

2934 """ # noqa: E501

2935 return CTE._construct(

2936 self, name=name, recursive=recursive, nesting=nesting

2937 )

2938

2939

2940class Subquery(AliasedReturnsRows):

2941 """Represent a subquery of a SELECT.

2942

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

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

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

2946 :class:`_expression.SelectBase` subclass

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

2948 :class:`_expression.CompoundSelect`, and

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

2950 it represents the

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

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

2953

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

2955 :class:`_expression.Alias`

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

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

2958 :class:`_expression.Alias` always

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

2960 :class:`.Subquery`

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

2962

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

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

2965 statement.

2966

2967 """

2968

2969 __visit_name__ = "subquery"

2970

2971 _is_subquery = True

2972

2973 inherit_cache = True

2974

2975 element: SelectBase

2976

2977 @classmethod

2978 def _factory(

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

2980 ) -> Subquery:

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

2982

2983 return coercions.expect(

2984 roles.SelectStatementRole, selectable

2985 ).subquery(name=name)

2986

2987 @util.deprecated(

2988 "1.4",

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

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

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

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

2993 ":func:`_expression.select` "

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

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

2996 )

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

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

2999

3000

3001class FromGrouping(GroupedElement, FromClause):

3002 """Represent a grouping of a FROM clause"""

3003

3004 _traverse_internals: _TraverseInternalsType = [

3005 ("element", InternalTraversal.dp_clauseelement)

3006 ]

3007

3008 element: FromClause

3009

3010 def __init__(self, element: FromClause):

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

3012

3013 @util.ro_non_memoized_property

3014 def columns(

3015 self,

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

3017 return self.element.columns

3018

3019 @util.ro_non_memoized_property

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

3021 return self.element.columns

3022

3023 @property

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

3025 return self.element.primary_key

3026

3027 @property

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

3029 return self.element.foreign_keys

3030

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

3032 return self.element.is_derived_from(fromclause)

3033

3034 def alias(

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

3036 ) -> NamedFromGrouping:

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

3038

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

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

3041

3042 @util.ro_non_memoized_property

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

3044 return self.element._hide_froms

3045

3046 @util.ro_non_memoized_property

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

3048 return self.element._from_objects

3049

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

3051 return {"element": self.element}

3052

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

3054 self.element = state["element"]

3055

3056 if TYPE_CHECKING:

3057

3058 def self_group(

3059 self, against: Optional[OperatorType] = None

3060 ) -> Self: ...

3061

3062

3063class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3065

3066 .. versionadded:: 2.0

3067

3068 """

3069

3070 inherit_cache = True

3071

3072 if TYPE_CHECKING:

3073

3074 def self_group(

3075 self, against: Optional[OperatorType] = None

3076 ) -> Self: ...

3077

3078

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

3080 """Represents a minimal "table" construct.

3081

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

3083 collection of columns, which are typically produced

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

3085

3086 from sqlalchemy import table, column

3087

3088 user = table(

3089 "user",

3090 column("id"),

3091 column("name"),

3092 column("description"),

3093 )

3094

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

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

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

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

3099

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

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

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

3103 It's useful

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

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

3106 is not on hand.

3107

3108 """

3109

3110 __visit_name__ = "table"

3111

3112 _traverse_internals: _TraverseInternalsType = [

3113 (

3114 "columns",

3115 InternalTraversal.dp_fromclause_canonical_column_collection,

3116 ),

3117 ("name", InternalTraversal.dp_string),

3118 ("schema", InternalTraversal.dp_string),

3119 ]

3120

3121 _is_table = True

3122

3123 fullname: str

3124

3125 implicit_returning = False

3126 """:class:`_expression.TableClause`

3127 doesn't support having a primary key or column

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

3129

3130 _columns: DedupeColumnCollection[ColumnClause[Any]]

3131

3132 @util.ro_memoized_property

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

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

3135 return None

3136

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

3138 super().__init__()

3139 self.name = name

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

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

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

3143 for c in columns:

3144 self.append_column(c)

3145

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

3147 if schema is not None:

3148 self.schema = schema

3149 if self.schema is not None:

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

3151 else:

3152 self.fullname = self.name

3153 if kw:

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

3155

3156 if TYPE_CHECKING:

3157

3158 @util.ro_non_memoized_property

3159 def columns(

3160 self,

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

3162

3163 @util.ro_non_memoized_property

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

3165

3166 def __str__(self) -> str:

3167 if self.schema is not None:

3168 return self.schema + "." + self.name

3169 else:

3170 return self.name

3171

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

3173 pass

3174

3175 @util.ro_memoized_property

3176 def description(self) -> str:

3177 return self.name

3178

3179 def _insert_col_impl(

3180 self,

3181 c: ColumnClause[Any],

3182 *,

3183 index: Optional[int] = None,

3184 ) -> None:

3185 existing = c.table

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

3187 raise exc.ArgumentError(

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

3189 % (c.key, existing)

3190 )

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

3192 c.table = self

3193

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

3195 self._insert_col_impl(c)

3196

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

3198 self._insert_col_impl(c, index=index)

3199

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

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

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

3203 :class:`_expression.TableClause`.

3204

3205 E.g.::

3206

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

3208

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

3210

3211 """

3212

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

3214

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

3216 def update(self) -> Update:

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

3218 :class:`_expression.TableClause`.

3219

3220 E.g.::

3221

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

3223

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

3225

3226 """

3227 return util.preloaded.sql_dml.Update(

3228 self,

3229 )

3230

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

3232 def delete(self) -> Delete:

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

3234 :class:`_expression.TableClause`.

3235

3236 E.g.::

3237

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

3239

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

3241

3242 """

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

3244

3245 @util.ro_non_memoized_property

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

3247 return [self]

3248

3249

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

3251

3252

3253class ForUpdateArg(ClauseElement):

3254 _traverse_internals: _TraverseInternalsType = [

3255 ("of", InternalTraversal.dp_clauseelement_list),

3256 ("nowait", InternalTraversal.dp_boolean),

3257 ("read", InternalTraversal.dp_boolean),

3258 ("skip_locked", InternalTraversal.dp_boolean),

3259 ("key_share", InternalTraversal.dp_boolean),

3260 ]

3261

3262 of: Optional[Sequence[ClauseElement]]

3263 nowait: bool

3264 read: bool

3265 skip_locked: bool

3266

3267 @classmethod

3268 def _from_argument(

3269 cls, with_for_update: ForUpdateParameter

3270 ) -> Optional[ForUpdateArg]:

3271 if isinstance(with_for_update, ForUpdateArg):

3272 return with_for_update

3273 elif with_for_update in (None, False):

3274 return None

3275 elif with_for_update is True:

3276 return ForUpdateArg()

3277 else:

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

3279

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

3281 return (

3282 isinstance(other, ForUpdateArg)

3283 and other.nowait == self.nowait

3284 and other.read == self.read

3285 and other.skip_locked == self.skip_locked

3286 and other.key_share == self.key_share

3287 and other.of is self.of

3288 )

3289

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

3291 return not self.__eq__(other)

3292

3293 def __hash__(self) -> int:

3294 return id(self)

3295

3296 def __init__(

3297 self,

3298 *,

3299 nowait: bool = False,

3300 read: bool = False,

3301 of: Optional[_ForUpdateOfArgument] = None,

3302 skip_locked: bool = False,

3303 key_share: bool = False,

3304 ):

3305 """Represents arguments specified to

3306 :meth:`_expression.Select.for_update`.

3307

3308 """

3309

3310 self.nowait = nowait

3311 self.read = read

3312 self.skip_locked = skip_locked

3313 self.key_share = key_share

3314 if of is not None:

3315 self.of = [

3316 coercions.expect(roles.ColumnsClauseRole, elem)

3317 for elem in util.to_list(of)

3318 ]

3319 else:

3320 self.of = None

3321

3322

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

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

3325 in a statement.

3326

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

3328 :func:`_expression.values` function.

3329

3330 .. versionadded:: 1.4

3331

3332 """

3333

3334 __visit_name__ = "values"

3335

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

3337

3338 _unnamed: bool

3339 _traverse_internals: _TraverseInternalsType = [

3340 ("_column_args", InternalTraversal.dp_clauseelement_list),

3341 ("_data", InternalTraversal.dp_dml_multi_values),

3342 ("name", InternalTraversal.dp_string),

3343 ("literal_binds", InternalTraversal.dp_boolean),

3344 ]

3345

3346 def __init__(

3347 self,

3348 *columns: ColumnClause[Any],

3349 name: Optional[str] = None,

3350 literal_binds: bool = False,

3351 ):

3352 super().__init__()

3353 self._column_args = columns

3354

3355 if name is None:

3356 self._unnamed = True

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

3358 else:

3359 self._unnamed = False

3360 self.name = name

3361 self.literal_binds = literal_binds

3362 self.named_with_column = not self._unnamed

3363

3364 @property

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

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

3367

3368 @_generative

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

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

3371 construct that is a copy of this

3372 one with the given name.

3373

3374 This method is a VALUES-specific specialization of the

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

3376

3377 .. seealso::

3378

3379 :ref:`tutorial_using_aliases`

3380

3381 :func:`_expression.alias`

3382

3383 """

3384 non_none_name: str

3385

3386 if name is None:

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

3388 else:

3389 non_none_name = name

3390

3391 self.name = non_none_name

3392 self.named_with_column = True

3393 self._unnamed = False

3394 return self

3395

3396 @_generative

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

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

3399 so that

3400 it renders as LATERAL.

3401

3402 .. seealso::

3403

3404 :func:`_expression.lateral`

3405

3406 """

3407 non_none_name: str

3408

3409 if name is None:

3410 non_none_name = self.name

3411 else:

3412 non_none_name = name

3413

3414 self._is_lateral = True

3415 self.name = non_none_name

3416 self._unnamed = False

3417 return self

3418

3419 @_generative

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

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

3422 adding the given data to the data list.

3423

3424 E.g.::

3425

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

3427

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

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

3430 constructor.

3431

3432 """

3433

3434 self._data += (values,)

3435 return self

3436

3437 def scalar_values(self) -> ScalarValues:

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

3439 COLUMN element in a statement.

3440

3441 .. versionadded:: 2.0.0b4

3442

3443 """

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

3445

3446 def _populate_column_collection(

3447 self,

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

3449 primary_key: ColumnSet,

3450 foreign_keys: Set[KeyedColumnElement[Any]],

3451 ) -> None:

3452 for c in self._column_args:

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

3454 _, c = c._make_proxy(

3455 self, primary_key=primary_key, foreign_keys=foreign_keys

3456 )

3457 else:

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

3459 # no memoizations of other FROM clauses.

3460 # see test_values.py -> test_auto_proxy_select_direct_col

3461 c._reset_memoizations()

3462 columns.add(c)

3463 c.table = self

3464

3465 @util.ro_non_memoized_property

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

3467 return [self]

3468

3469

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

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

3472 COLUMN element in a statement.

3473

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

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

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

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

3478

3479 .. versionadded:: 2.0.0b4

3480

3481 """

3482

3483 __visit_name__ = "scalar_values"

3484

3485 _traverse_internals: _TraverseInternalsType = [

3486 ("_column_args", InternalTraversal.dp_clauseelement_list),

3487 ("_data", InternalTraversal.dp_dml_multi_values),

3488 ("literal_binds", InternalTraversal.dp_boolean),

3489 ]

3490

3491 def __init__(

3492 self,

3493 columns: Sequence[ColumnClause[Any]],

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

3495 literal_binds: bool,

3496 ):

3497 super().__init__()

3498 self._column_args = columns

3499 self._data = data

3500 self.literal_binds = literal_binds

3501

3502 @property

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

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

3505

3506 def __clause_element__(self) -> ScalarValues:

3507 return self

3508

3509 if TYPE_CHECKING:

3510

3511 def self_group(

3512 self, against: Optional[OperatorType] = None

3513 ) -> Self: ...

3514

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

3516

3517

3518class SelectBase(

3519 roles.SelectStatementRole,

3520 roles.DMLSelectRole,

3521 roles.CompoundElementRole,

3522 roles.InElementRole,

3523 HasCTE,

3524 SupportsCloneAnnotations,

3525 Selectable,

3526):

3527 """Base class for SELECT statements.

3528

3529

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

3531 :class:`_expression.CompoundSelect` and

3532 :class:`_expression.TextualSelect`.

3533

3534

3535 """

3536

3537 _is_select_base = True

3538 is_select = True

3539

3540 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3541

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

3543 self._reset_memoizations()

3544

3545 @util.ro_non_memoized_property

3546 def selected_columns(

3547 self,

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

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

3550 representing the columns that

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

3552

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

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

3555 within this collection cannot be directly nested inside another SELECT

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

3557 necessary parenthesization required by SQL.

3558

3559 .. note::

3560

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

3562 include expressions established in the columns clause using the

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

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

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

3566 construct.

3567

3568 .. seealso::

3569

3570 :attr:`_sql.Select.selected_columns`

3571

3572 .. versionadded:: 1.4

3573

3574 """

3575 raise NotImplementedError()

3576

3577 def _generate_fromclause_column_proxies(

3578 self,

3579 subquery: FromClause,

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

3581 primary_key: ColumnSet,

3582 foreign_keys: Set[KeyedColumnElement[Any]],

3583 *,

3584 proxy_compound_columns: Optional[

3585 Iterable[Sequence[ColumnElement[Any]]]

3586 ] = None,

3587 ) -> None:

3588 raise NotImplementedError()

3589

3590 @util.ro_non_memoized_property

3591 def _all_selected_columns(self) -> _SelectIterable:

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

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

3594 constructs.

3595

3596 .. versionadded:: 1.4.12

3597

3598 .. seealso::

3599

3600 :attr:`_sql.SelectBase.exported_columns`

3601

3602 """

3603 raise NotImplementedError()

3604

3605 @property

3606 def exported_columns(

3607 self,

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

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

3610 that represents the "exported"

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

3612 :class:`_sql.TextClause` constructs.

3613

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

3615 object are synonymous

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

3617

3618 .. versionadded:: 1.4

3619

3620 .. seealso::

3621

3622 :attr:`_expression.Select.exported_columns`

3623

3624 :attr:`_expression.Selectable.exported_columns`

3625

3626 :attr:`_expression.FromClause.exported_columns`

3627

3628

3629 """

3630 return self.selected_columns.as_readonly()

3631

3632 def get_label_style(self) -> SelectLabelStyle:

3633 """

3634 Retrieve the current label style.

3635

3636 Implemented by subclasses.

3637

3638 """

3639 raise NotImplementedError()

3640

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

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

3643

3644 Implemented by subclasses.

3645

3646 """

3647

3648 raise NotImplementedError()

3649

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

3651 raise NotImplementedError()

3652

3653 @util.deprecated(

3654 "1.4",

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

3656 "method is deprecated and will be "

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

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

3659 )

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

3661 return self.scalar_subquery()

3662

3663 def exists(self) -> Exists:

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

3665 which can be used as a column expression.

3666

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

3668

3669 .. seealso::

3670

3671 :func:`_sql.exists`

3672

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

3674

3675 .. versionadded:: 1.4

3676

3677 """

3678 return Exists(self)

3679

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

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

3682 used as a column expression.

3683

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

3685

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

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

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

3689 an enclosing SELECT.

3690

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

3692 subquery that can be produced using the

3693 :meth:`_expression.SelectBase.subquery`

3694 method.

3695

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

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

3698

3699 .. seealso::

3700

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

3702

3703 """

3704 if self._label_style is not LABEL_STYLE_NONE:

3705 self = self.set_label_style(LABEL_STYLE_NONE)

3706

3707 return ScalarSelect(self)

3708

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

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

3711 subquery with a label.

3712

3713 .. seealso::

3714

3715 :meth:`_expression.SelectBase.scalar_subquery`.

3716

3717 """

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

3719

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

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

3722

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

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

3725

3726 .. seealso::

3727

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

3729

3730 """

3731 return Lateral._factory(self, name)

3732

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

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

3735

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

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

3738 SELECT statement.

3739

3740 Given a SELECT statement such as::

3741

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

3743

3744 The above statement might look like:

3745

3746 .. sourcecode:: sql

3747

3748 SELECT table.id, table.name FROM table

3749

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

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

3752 a named sub-element::

3753

3754 subq = stmt.subquery()

3755 new_stmt = select(subq)

3756

3757 The above renders as:

3758

3759 .. sourcecode:: sql

3760

3761 SELECT anon_1.id, anon_1.name

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

3763

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

3765 is equivalent to calling

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

3767 method on a FROM object; however,

3768 as a :class:`_expression.SelectBase`

3769 object is not directly FROM object,

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

3771 method provides clearer semantics.

3772

3773 .. versionadded:: 1.4

3774

3775 """

3776

3777 return Subquery._construct(

3778 self._ensure_disambiguated_names(), name=name

3779 )

3780

3781 def _ensure_disambiguated_names(self) -> Self:

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

3783 disambiguated in some way, if possible.

3784

3785 """

3786

3787 raise NotImplementedError()

3788

3789 def alias(

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

3791 ) -> Subquery:

3792 """Return a named subquery against this

3793 :class:`_expression.SelectBase`.

3794

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

3796 :class:`_expression.FromClause`),

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

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

3799 :class:`_expression.FromClause`.

3800

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

3802 method is now

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

3804

3805 """

3806 return self.subquery(name=name)

3807

3808

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

3810

3811

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

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

3814

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

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

3817 compound selects.

3818

3819 """

3820

3821 __visit_name__ = "select_statement_grouping"

3822 _traverse_internals: _TraverseInternalsType = [

3823 ("element", InternalTraversal.dp_clauseelement)

3824 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3825

3826 _is_select_container = True

3827

3828 element: _SB

3829

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

3831 self.element = cast(

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

3833 )

3834

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

3836 new_element = self.element._ensure_disambiguated_names()

3837 if new_element is not self.element:

3838 return SelectStatementGrouping(new_element)

3839 else:

3840 return self

3841

3842 def get_label_style(self) -> SelectLabelStyle:

3843 return self.element.get_label_style()

3844

3845 def set_label_style(

3846 self, label_style: SelectLabelStyle

3847 ) -> SelectStatementGrouping[_SB]:

3848 return SelectStatementGrouping(

3849 self.element.set_label_style(label_style)

3850 )

3851

3852 @property

3853 def select_statement(self) -> _SB:

3854 return self.element

3855

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

3857 return self

3858

3859 if TYPE_CHECKING:

3860

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

3862

3863 # def _generate_columns_plus_names(

3864 # self, anon_for_dupe_key: bool

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

3866 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3867

3868 def _generate_fromclause_column_proxies(

3869 self,

3870 subquery: FromClause,

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

3872 primary_key: ColumnSet,

3873 foreign_keys: Set[KeyedColumnElement[Any]],

3874 *,

3875 proxy_compound_columns: Optional[

3876 Iterable[Sequence[ColumnElement[Any]]]

3877 ] = None,

3878 ) -> None:

3879 self.element._generate_fromclause_column_proxies(

3880 subquery,

3881 columns,

3882 proxy_compound_columns=proxy_compound_columns,

3883 primary_key=primary_key,

3884 foreign_keys=foreign_keys,

3885 )

3886

3887 @util.ro_non_memoized_property

3888 def _all_selected_columns(self) -> _SelectIterable:

3889 return self.element._all_selected_columns

3890

3891 @util.ro_non_memoized_property

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

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

3894 representing the columns that

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

3896 :class:`_sql.TextClause` constructs.

3897

3898 .. versionadded:: 1.4

3899

3900 .. seealso::

3901

3902 :attr:`_sql.Select.selected_columns`

3903

3904 """

3905 return self.element.selected_columns

3906

3907 @util.ro_non_memoized_property

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

3909 return self.element._from_objects

3910

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

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

3913 raise NotImplementedError

3914

3915

3916class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3918 added.

3919

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

3921 :class:`_expression.CompoundSelect`

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

3923 rendering can be controlled. Compare to

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

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

3926 and is also a SELECT construct,

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

3928 only wrapped as a subquery.

3929

3930 """

3931

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

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

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

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

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

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

3938 _for_update_arg: Optional[ForUpdateArg] = None

3939

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

3941 self._label_style = _label_style

3942

3943 @_generative

3944 def with_for_update(

3945 self,

3946 *,

3947 nowait: bool = False,

3948 read: bool = False,

3949 of: Optional[_ForUpdateOfArgument] = None,

3950 skip_locked: bool = False,

3951 key_share: bool = False,

3952 ) -> Self:

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

3954 :class:`_expression.GenerativeSelect`.

3955

3956 E.g.::

3957

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

3959

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

3961 render a statement like:

3962

3963 .. sourcecode:: sql

3964

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

3966

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

3968 would produce:

3969

3970 .. sourcecode:: sql

3971

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

3973

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

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

3976 provided which allow for common database-specific

3977 variants.

3978

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

3980 Database and PostgreSQL dialects.

3981

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

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

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

3985

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

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

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

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

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

3991 table or as a column depending on backend.

3992

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

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

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

3996

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

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

3999 on the PostgreSQL dialect.

4000

4001 """

4002 self._for_update_arg = ForUpdateArg(

4003 nowait=nowait,

4004 read=read,

4005 of=of,

4006 skip_locked=skip_locked,

4007 key_share=key_share,

4008 )

4009 return self

4010

4011 def get_label_style(self) -> SelectLabelStyle:

4012 """

4013 Retrieve the current label style.

4014

4015 .. versionadded:: 1.4

4016

4017 """

4018 return self._label_style

4019

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

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

4022

4023 There are three "label styles" available,

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

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

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

4027 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4028

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

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

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

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

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

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

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

4036 that the impact of this labeling is minimal.

4037

4038 The rationale for disambiguation is mostly so that all column

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

4040 collection when a subquery is created.

4041

4042 .. versionadded:: 1.4 - the

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

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

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

4046

4047 .. seealso::

4048

4049 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4050

4051 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4052

4053 :data:`_sql.LABEL_STYLE_NONE`

4054

4055 :data:`_sql.LABEL_STYLE_DEFAULT`

4056

4057 """

4058 if self._label_style is not style:

4059 self = self._generate()

4060 self._label_style = style

4061 return self

4062

4063 @property

4064 def _group_by_clause(self) -> ClauseList:

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

4066 return ClauseList._construct_raw(

4067 operators.comma_op, self._group_by_clauses

4068 )

4069

4070 @property

4071 def _order_by_clause(self) -> ClauseList:

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

4073 return ClauseList._construct_raw(

4074 operators.comma_op, self._order_by_clauses

4075 )

4076

4077 def _offset_or_limit_clause(

4078 self,

4079 element: _LimitOffsetType,

4080 name: Optional[str] = None,

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

4082 ) -> ColumnElement[Any]:

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

4084

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

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

4087

4088 """

4089 return coercions.expect(

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

4091 )

4092

4093 @overload

4094 def _offset_or_limit_clause_asint(

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

4096 ) -> NoReturn: ...

4097

4098 @overload

4099 def _offset_or_limit_clause_asint(

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

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

4102

4103 def _offset_or_limit_clause_asint(

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

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

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

4107 integer.

4108

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

4110 parameter. Otherwise, a compilation error is raised.

4111

4112 """

4113 if clause is None:

4114 return None

4115 try:

4116 value = clause._limit_offset_value

4117 except AttributeError as err:

4118 raise exc.CompileError(

4119 "This SELECT structure does not use a simple "

4120 "integer value for %s" % attrname

4121 ) from err

4122 else:

4123 return util.asint(value)

4124

4125 @property

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

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

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

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

4130 isn't currently set to an integer.

4131

4132 """

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

4134

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

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

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

4138 """

4139 return isinstance(clause, _OffsetLimitParam)

4140

4141 @property

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

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

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

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

4146 offset isn't currently set to an integer.

4147

4148 """

4149 return self._offset_or_limit_clause_asint(

4150 self._offset_clause, "offset"

4151 )

4152

4153 @property

4154 def _has_row_limiting_clause(self) -> bool:

4155 return (

4156 self._limit_clause is not None

4157 or self._offset_clause is not None

4158 or self._fetch_clause is not None

4159 )

4160

4161 @_generative

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

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

4164 applied.

4165

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

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

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

4169 functionality.

4170

4171 .. note::

4172

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

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

4175

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

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

4178

4179 .. seealso::

4180

4181 :meth:`_sql.GenerativeSelect.fetch`

4182

4183 :meth:`_sql.GenerativeSelect.offset`

4184

4185 """

4186

4187 self._fetch_clause = self._fetch_clause_options = None

4188 self._limit_clause = self._offset_or_limit_clause(limit)

4189 return self

4190

4191 @_generative

4192 def fetch(

4193 self,

4194 count: _LimitOffsetType,

4195 with_ties: bool = False,

4196 percent: bool = False,

4197 **dialect_kw: Any,

4198 ) -> Self:

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

4200 applied.

4201

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

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

4204 select. This functionality is is currently implemented for Oracle

4205 Database, PostgreSQL, MSSQL.

4206

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

4208

4209 .. note::

4210

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

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

4213

4214 .. versionadded:: 1.4

4215

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

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

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

4219 Pass ``None`` to reset it.

4220

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

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

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

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

4225

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

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

4228

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

4230 may be accepted by dialects.

4231

4232 .. versionadded:: 2.0.41

4233

4234 .. seealso::

4235

4236 :meth:`_sql.GenerativeSelect.limit`

4237

4238 :meth:`_sql.GenerativeSelect.offset`

4239

4240 """

4241 self._validate_dialect_kwargs(dialect_kw)

4242 self._limit_clause = None

4243 if count is None:

4244 self._fetch_clause = self._fetch_clause_options = None

4245 else:

4246 self._fetch_clause = self._offset_or_limit_clause(count)

4247 self._fetch_clause_options = {

4248 "with_ties": with_ties,

4249 "percent": percent,

4250 }

4251 return self

4252

4253 @_generative

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

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

4256 applied.

4257

4258

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

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

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

4262 functionality.

4263

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

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

4266

4267 .. seealso::

4268

4269 :meth:`_sql.GenerativeSelect.limit`

4270

4271 :meth:`_sql.GenerativeSelect.fetch`

4272

4273 """

4274

4275 self._offset_clause = self._offset_or_limit_clause(offset)

4276 return self

4277

4278 @_generative

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

4280 def slice(

4281 self,

4282 start: int,

4283 stop: int,

4284 ) -> Self:

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

4286

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

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

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

4290 query.

4291

4292 For example, ::

4293

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

4295

4296 renders as

4297

4298 .. sourcecode:: sql

4299

4300 SELECT users.id AS users_id,

4301 users.name AS users_name

4302 FROM users ORDER BY users.id

4303 LIMIT ? OFFSET ?

4304 (2, 1)

4305

4306 .. note::

4307

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

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

4310

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

4312 method generalized from the ORM.

4313

4314 .. seealso::

4315

4316 :meth:`_sql.GenerativeSelect.limit`

4317

4318 :meth:`_sql.GenerativeSelect.offset`

4319

4320 :meth:`_sql.GenerativeSelect.fetch`

4321

4322 """

4323 sql_util = util.preloaded.sql_util

4324 self._fetch_clause = self._fetch_clause_options = None

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

4326 self._limit_clause, self._offset_clause, start, stop

4327 )

4328 return self

4329

4330 @_generative

4331 def order_by(

4332 self,

4333 __first: Union[

4334 Literal[None, _NoArg.NO_ARG],

4335 _ColumnExpressionOrStrLabelArgument[Any],

4336 ] = _NoArg.NO_ARG,

4337 /,

4338 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4339 ) -> Self:

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

4341 criteria applied.

4342

4343 e.g.::

4344

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

4346

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

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

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

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

4351

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

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

4354

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

4356 constructs

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

4358

4359 .. seealso::

4360

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

4362

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

4364

4365 """

4366

4367 if not clauses and __first is None:

4368 self._order_by_clauses = ()

4369 elif __first is not _NoArg.NO_ARG:

4370 self._order_by_clauses += tuple(

4371 coercions.expect(

4372 roles.OrderByRole, clause, apply_propagate_attrs=self

4373 )

4374 for clause in (__first,) + clauses

4375 )

4376 return self

4377

4378 @_generative

4379 def group_by(

4380 self,

4381 __first: Union[

4382 Literal[None, _NoArg.NO_ARG],

4383 _ColumnExpressionOrStrLabelArgument[Any],

4384 ] = _NoArg.NO_ARG,

4385 /,

4386 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4387 ) -> Self:

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

4389 criterion applied.

4390

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

4392

4393 e.g.::

4394

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

4396

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

4398 constructs

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

4400

4401 .. seealso::

4402

4403 :ref:`tutorial_group_by_w_aggregates` - in the

4404 :ref:`unified_tutorial`

4405

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

4407

4408 """ # noqa: E501

4409

4410 if not clauses and __first is None:

4411 self._group_by_clauses = ()

4412 elif __first is not _NoArg.NO_ARG:

4413 self._group_by_clauses += tuple(

4414 coercions.expect(

4415 roles.GroupByRole, clause, apply_propagate_attrs=self

4416 )

4417 for clause in (__first,) + clauses

4418 )

4419 return self

4420

4421

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

4423class CompoundSelectState(CompileState):

4424 @util.memoized_property

4425 def _label_resolve_dict(

4426 self,

4427 ) -> Tuple[

4428 Dict[str, ColumnElement[Any]],

4429 Dict[str, ColumnElement[Any]],

4430 Dict[str, ColumnElement[Any]],

4431 ]:

4432 # TODO: this is hacky and slow

4433 hacky_subquery = self.statement.subquery()

4434 hacky_subquery.named_with_column = False

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

4436 return d, d, d

4437

4438

4439class _CompoundSelectKeyword(Enum):

4440 UNION = "UNION"

4441 UNION_ALL = "UNION ALL"

4442 EXCEPT = "EXCEPT"

4443 EXCEPT_ALL = "EXCEPT ALL"

4444 INTERSECT = "INTERSECT"

4445 INTERSECT_ALL = "INTERSECT ALL"

4446

4447

4448class CompoundSelect(

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

4450):

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

4452 SELECT-based set operations.

4453

4454

4455 .. seealso::

4456

4457 :func:`_expression.union`

4458

4459 :func:`_expression.union_all`

4460

4461 :func:`_expression.intersect`

4462

4463 :func:`_expression.intersect_all`

4464

4465 :func:`_expression.except`

4466

4467 :func:`_expression.except_all`

4468

4469 """

4470

4471 __visit_name__ = "compound_select"

4472

4473 _traverse_internals: _TraverseInternalsType = (

4474 [

4475 ("selects", InternalTraversal.dp_clauseelement_list),

4476 ("_limit_clause", InternalTraversal.dp_clauseelement),

4477 ("_offset_clause", InternalTraversal.dp_clauseelement),

4478 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4479 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4480 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4481 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4482 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4483 ("keyword", InternalTraversal.dp_string),

4484 ]

4485 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4486 + HasCTE._has_ctes_traverse_internals

4487 + DialectKWArgs._dialect_kwargs_traverse_internals

4488 )

4489

4490 selects: List[SelectBase]

4491

4492 _is_from_container = True

4493 _auto_correlate = False

4494

4495 def __init__(

4496 self,

4497 keyword: _CompoundSelectKeyword,

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

4499 ):

4500 self.keyword = keyword

4501 self.selects = [

4502 coercions.expect(

4503 roles.CompoundElementRole, s, apply_propagate_attrs=self

4504 ).self_group(against=self)

4505 for s in selects

4506 ]

4507

4508 GenerativeSelect.__init__(self)

4509

4510 @classmethod

4511 def _create_union(

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

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

4514 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4515

4516 @classmethod

4517 def _create_union_all(

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

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

4520 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4521

4522 @classmethod

4523 def _create_except(

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

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

4526 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4527

4528 @classmethod

4529 def _create_except_all(

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

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

4532 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4533

4534 @classmethod

4535 def _create_intersect(

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

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

4538 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4539

4540 @classmethod

4541 def _create_intersect_all(

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

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

4544 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4545

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

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

4548

4549 def self_group(

4550 self, against: Optional[OperatorType] = None

4551 ) -> GroupedElement:

4552 return SelectStatementGrouping(self)

4553

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

4555 for s in self.selects:

4556 if s.is_derived_from(fromclause):

4557 return True

4558 return False

4559

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

4561 if self._label_style is not style:

4562 self = self._generate()

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

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

4565

4566 return self

4567

4568 def _ensure_disambiguated_names(self) -> Self:

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

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

4571 self = self._generate()

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

4573

4574 return self

4575

4576 def _generate_fromclause_column_proxies(

4577 self,

4578 subquery: FromClause,

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

4580 primary_key: ColumnSet,

4581 foreign_keys: Set[KeyedColumnElement[Any]],

4582 *,

4583 proxy_compound_columns: Optional[

4584 Iterable[Sequence[ColumnElement[Any]]]

4585 ] = None,

4586 ) -> None:

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

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

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

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

4591 # generate below.

4592 select_0 = self.selects[0]

4593

4594 if self._label_style is not LABEL_STYLE_DEFAULT:

4595 select_0 = select_0.set_label_style(self._label_style)

4596

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

4598 # derived columns place a 'weight' annotation corresponding

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

4600 # that the corresponding_column() operation can resolve

4601 # conflicts

4602 extra_col_iterator = zip(

4603 *[

4604 [

4605 c._annotate(dd)

4606 for c in stmt._all_selected_columns

4607 if is_column_element(c)

4608 ]

4609 for dd, stmt in [

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

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

4612 ]

4613 ]

4614 )

4615

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

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

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

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

4620 # compound

4621 select_0._generate_fromclause_column_proxies(

4622 subquery,

4623 columns,

4624 proxy_compound_columns=extra_col_iterator,

4625 primary_key=primary_key,

4626 foreign_keys=foreign_keys,

4627 )

4628

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

4630 super()._refresh_for_new_column(column)

4631 for select in self.selects:

4632 select._refresh_for_new_column(column)

4633

4634 @util.ro_non_memoized_property

4635 def _all_selected_columns(self) -> _SelectIterable:

4636 return self.selects[0]._all_selected_columns

4637

4638 @util.ro_non_memoized_property

4639 def selected_columns(

4640 self,

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

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

4643 representing the columns that

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

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

4646

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

4648 :attr:`_expression.CompoundSelect.selected_columns`

4649 attribute returns the selected

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

4651 statements within the set operation.

4652

4653 .. seealso::

4654

4655 :attr:`_sql.Select.selected_columns`

4656

4657 .. versionadded:: 1.4

4658

4659 """

4660 return self.selects[0].selected_columns

4661

4662

4663# backwards compat

4664for elem in _CompoundSelectKeyword:

4665 setattr(CompoundSelect, elem.name, elem)

4666

4667

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

4669class SelectState(util.MemoizedSlots, CompileState):

4670 __slots__ = (

4671 "from_clauses",

4672 "froms",

4673 "columns_plus_names",

4674 "_label_resolve_dict",

4675 )

4676

4677 if TYPE_CHECKING:

4678 default_select_compile_options: CacheableOptions

4679 else:

4680

4681 class default_select_compile_options(CacheableOptions):

4682 _cache_key_traversal = []

4683

4684 if TYPE_CHECKING:

4685

4686 @classmethod

4687 def get_plugin_class(

4688 cls, statement: Executable

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

4690

4691 def __init__(

4692 self,

4693 statement: Select[Unpack[TupleAny]],

4694 compiler: SQLCompiler,

4695 **kw: Any,

4696 ):

4697 self.statement = statement

4698 self.from_clauses = statement._from_obj

4699

4700 for memoized_entities in statement._memoized_select_entities:

4701 self._setup_joins(

4702 memoized_entities._setup_joins, memoized_entities._raw_columns

4703 )

4704

4705 if statement._setup_joins:

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

4707

4708 self.froms = self._get_froms(statement)

4709

4710 self.columns_plus_names = statement._generate_columns_plus_names(True)

4711

4712 @classmethod

4713 def _plugin_not_implemented(cls) -> NoReturn:

4714 raise NotImplementedError(

4715 "The default SELECT construct without plugins does not "

4716 "implement this method."

4717 )

4718

4719 @classmethod

4720 def get_column_descriptions(

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

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

4723 return [

4724 {

4725 "name": name,

4726 "type": element.type,

4727 "expr": element,

4728 }

4729 for _, name, _, element, _ in (

4730 statement._generate_columns_plus_names(False)

4731 )

4732 ]

4733

4734 @classmethod

4735 def from_statement(

4736 cls,

4737 statement: Select[Unpack[TupleAny]],

4738 from_statement: roles.ReturnsRowsRole,

4739 ) -> ExecutableReturnsRows:

4740 cls._plugin_not_implemented()

4741

4742 @classmethod

4743 def get_columns_clause_froms(

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

4745 ) -> List[FromClause]:

4746 return cls._normalize_froms(

4747 itertools.chain.from_iterable(

4748 element._from_objects for element in statement._raw_columns

4749 )

4750 )

4751

4752 @classmethod

4753 def _column_naming_convention(

4754 cls, label_style: SelectLabelStyle

4755 ) -> _LabelConventionCallable:

4756 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4757

4758 dedupe = label_style is not LABEL_STYLE_NONE

4759

4760 pa = prefix_anon_map()

4761 names = set()

4762

4763 def go(

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

4765 col_name: Optional[str] = None,

4766 ) -> Optional[str]:

4767 if is_text_clause(c):

4768 return None

4769 elif TYPE_CHECKING:

4770 assert is_column_element(c)

4771

4772 if not dedupe:

4773 name = c._proxy_key

4774 if name is None:

4775 name = "_no_label"

4776 return name

4777

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

4779

4780 if name is None:

4781 name = "_no_label"

4782 if name in names:

4783 return c._anon_label(name) % pa

4784 else:

4785 names.add(name)

4786 return name

4787

4788 elif name in names:

4789 return (

4790 c._anon_tq_key_label % pa

4791 if table_qualified

4792 else c._anon_key_label % pa

4793 )

4794 else:

4795 names.add(name)

4796 return name

4797

4798 return go

4799

4800 def _get_froms(

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

4802 ) -> List[FromClause]:

4803 ambiguous_table_name_map: _AmbiguousTableNameMap

4804 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4805

4806 return self._normalize_froms(

4807 itertools.chain(

4808 self.from_clauses,

4809 itertools.chain.from_iterable(

4810 [

4811 element._from_objects

4812 for element in statement._raw_columns

4813 ]

4814 ),

4815 itertools.chain.from_iterable(

4816 [

4817 element._from_objects

4818 for element in statement._where_criteria

4819 ]

4820 ),

4821 ),

4822 check_statement=statement,

4823 ambiguous_table_name_map=ambiguous_table_name_map,

4824 )

4825

4826 @classmethod

4827 def _normalize_froms(

4828 cls,

4829 iterable_of_froms: Iterable[FromClause],

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

4831 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4832 ) -> List[FromClause]:

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

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

4835

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

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

4838 etc.

4839

4840 """

4841 seen: Set[FromClause] = set()

4842 froms: List[FromClause] = []

4843

4844 for item in iterable_of_froms:

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

4846 raise exc.InvalidRequestError(

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

4848 )

4849

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

4851 froms.append(item)

4852 seen.update(item._cloned_set)

4853

4854 if froms:

4855 toremove = set(

4856 itertools.chain.from_iterable(

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

4858 )

4859 )

4860 if toremove:

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

4862 # using a list to maintain ordering

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

4864

4865 if ambiguous_table_name_map is not None:

4866 ambiguous_table_name_map.update(

4867 (

4868 fr.name,

4869 _anonymous_label.safe_construct(

4870 hash(fr.name), fr.name

4871 ),

4872 )

4873 for item in froms

4874 for fr in item._from_objects

4875 if is_table(fr)

4876 and fr.schema

4877 and fr.name not in ambiguous_table_name_map

4878 )

4879

4880 return froms

4881

4882 def _get_display_froms(

4883 self,

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

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

4886 ) -> List[FromClause]:

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

4888

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

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

4891 may want to leave those absent if it is automatically

4892 correlating.

4893

4894 """

4895

4896 froms = self.froms

4897

4898 if self.statement._correlate:

4899 to_correlate = self.statement._correlate

4900 if to_correlate:

4901 froms = [

4902 f

4903 for f in froms

4904 if f

4905 not in _cloned_intersection(

4906 _cloned_intersection(

4907 froms, explicit_correlate_froms or ()

4908 ),

4909 to_correlate,

4910 )

4911 ]

4912

4913 if self.statement._correlate_except is not None:

4914 froms = [

4915 f

4916 for f in froms

4917 if f

4918 not in _cloned_difference(

4919 _cloned_intersection(

4920 froms, explicit_correlate_froms or ()

4921 ),

4922 self.statement._correlate_except,

4923 )

4924 ]

4925

4926 if (

4927 self.statement._auto_correlate

4928 and implicit_correlate_froms

4929 and len(froms) > 1

4930 ):

4931 froms = [

4932 f

4933 for f in froms

4934 if f

4935 not in _cloned_intersection(froms, implicit_correlate_froms)

4936 ]

4937

4938 if not len(froms):

4939 raise exc.InvalidRequestError(

4940 "Select statement '%r"

4941 "' returned no FROM clauses "

4942 "due to auto-correlation; "

4943 "specify correlate(<tables>) "

4944 "to control correlation "

4945 "manually." % self.statement

4946 )

4947

4948 return froms

4949

4950 def _memoized_attr__label_resolve_dict(

4951 self,

4952 ) -> Tuple[

4953 Dict[str, ColumnElement[Any]],

4954 Dict[str, ColumnElement[Any]],

4955 Dict[str, ColumnElement[Any]],

4956 ]:

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

4958 c._tq_label or c.key: c

4959 for c in self.statement._all_selected_columns

4960 if c._allow_label_resolve

4961 }

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

4963 c.key: c # type: ignore

4964 for c in _select_iterables(self.froms)

4965 if c._allow_label_resolve

4966 }

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

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

4969 with_cols.setdefault(key, value)

4970

4971 return with_cols, only_froms, only_cols

4972

4973 @classmethod

4974 def determine_last_joined_entity(

4975 cls, stmt: Select[Unpack[TupleAny]]

4976 ) -> Optional[_JoinTargetElement]:

4977 if stmt._setup_joins:

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

4979 else:

4980 return None

4981

4982 @classmethod

4983 def all_selected_columns(

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

4985 ) -> _SelectIterable:

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

4987

4988 def _setup_joins(

4989 self,

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

4991 raw_columns: List[_ColumnsClauseElement],

4992 ) -> None:

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

4994 if TYPE_CHECKING:

4995 if onclause is not None:

4996 assert isinstance(onclause, ColumnElement)

4997

4998 isouter = flags["isouter"]

4999 full = flags["full"]

5000

5001 if left is None:

5002 (

5003 left,

5004 replace_from_obj_index,

5005 ) = self._join_determine_implicit_left_side(

5006 raw_columns, left, right, onclause

5007 )

5008 else:

5009 (replace_from_obj_index) = self._join_place_explicit_left_side(

5010 left

5011 )

5012

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

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

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

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

5017 # used instead.

5018 if TYPE_CHECKING:

5019 assert isinstance(right, FromClause)

5020 if onclause is not None:

5021 assert isinstance(onclause, ColumnElement)

5022

5023 if replace_from_obj_index is not None:

5024 # splice into an existing element in the

5025 # self._from_obj list

5026 left_clause = self.from_clauses[replace_from_obj_index]

5027

5028 self.from_clauses = (

5029 self.from_clauses[:replace_from_obj_index]

5030 + (

5031 Join(

5032 left_clause,

5033 right,

5034 onclause,

5035 isouter=isouter,

5036 full=full,

5037 ),

5038 )

5039 + self.from_clauses[replace_from_obj_index + 1 :]

5040 )

5041 else:

5042 assert left is not None

5043 self.from_clauses = self.from_clauses + (

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

5045 )

5046

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

5048 def _join_determine_implicit_left_side(

5049 self,

5050 raw_columns: List[_ColumnsClauseElement],

5051 left: Optional[FromClause],

5052 right: _JoinTargetElement,

5053 onclause: Optional[ColumnElement[Any]],

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

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

5056 determine if an existing FROM or entity in this query

5057 can serve as the left hand side.

5058

5059 """

5060

5061 sql_util = util.preloaded.sql_util

5062

5063 replace_from_obj_index: Optional[int] = None

5064

5065 from_clauses = self.from_clauses

5066

5067 if from_clauses:

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

5069 from_clauses, right, onclause

5070 )

5071

5072 if len(indexes) == 1:

5073 replace_from_obj_index = indexes[0]

5074 left = from_clauses[replace_from_obj_index]

5075 else:

5076 potential = {}

5077 statement = self.statement

5078

5079 for from_clause in itertools.chain(

5080 itertools.chain.from_iterable(

5081 [element._from_objects for element in raw_columns]

5082 ),

5083 itertools.chain.from_iterable(

5084 [

5085 element._from_objects

5086 for element in statement._where_criteria

5087 ]

5088 ),

5089 ):

5090 potential[from_clause] = ()

5091

5092 all_clauses = list(potential.keys())

5093 indexes = sql_util.find_left_clause_to_join_from(

5094 all_clauses, right, onclause

5095 )

5096

5097 if len(indexes) == 1:

5098 left = all_clauses[indexes[0]]

5099

5100 if len(indexes) > 1:

5101 raise exc.InvalidRequestError(

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

5103 "from, there are multiple FROMS which can "

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

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

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

5107 "help resolve the ambiguity."

5108 )

5109 elif not indexes:

5110 raise exc.InvalidRequestError(

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

5112 "Please use the .select_from() "

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

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

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

5116 )

5117 return left, replace_from_obj_index

5118

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

5120 def _join_place_explicit_left_side(

5121 self, left: FromClause

5122 ) -> Optional[int]:

5123 replace_from_obj_index: Optional[int] = None

5124

5125 sql_util = util.preloaded.sql_util

5126

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

5128

5129 if from_clauses:

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

5131 self.from_clauses, left

5132 )

5133 else:

5134 indexes = []

5135

5136 if len(indexes) > 1:

5137 raise exc.InvalidRequestError(

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

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

5140 "ON clause."

5141 )

5142

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

5144 # an existing FROM in the self._from_obj tuple

5145 if indexes:

5146 replace_from_obj_index = indexes[0]

5147

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

5149 # self._from_obj tuple

5150

5151 return replace_from_obj_index

5152

5153

5154class _SelectFromElements:

5155 __slots__ = ()

5156

5157 _raw_columns: List[_ColumnsClauseElement]

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

5159 _from_obj: Tuple[FromClause, ...]

5160

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

5162 # note this does not include elements

5163 # in _setup_joins

5164

5165 seen = set()

5166 for element in self._raw_columns:

5167 for fr in element._from_objects:

5168 if fr in seen:

5169 continue

5170 seen.add(fr)

5171 yield fr

5172 for element in self._where_criteria:

5173 for fr in element._from_objects:

5174 if fr in seen:

5175 continue

5176 seen.add(fr)

5177 yield fr

5178 for element in self._from_obj:

5179 if element in seen:

5180 continue

5181 seen.add(element)

5182 yield element

5183

5184

5185class _MemoizedSelectEntities(

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

5187):

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

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

5190 statement will be SELECTing from. This object represents

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

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

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

5194

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

5196 constructs calculated everything up front, like when you called join()

5197 or options(), it did everything to figure out how that would translate

5198 into specific SQL constructs that would be ready to send directly to the

5199 SQL compiler when needed. But as of

5200 1.4, all of that stuff is done in the compilation phase, during the

5201 "compile state" portion of the process, so that the work can all be

5202 cached. So it needs to be able to resolve joins/options2 based on what

5203 the list of entities was when those methods were called.

5204

5205

5206 """

5207

5208 __visit_name__ = "memoized_select_entities"

5209

5210 _traverse_internals: _TraverseInternalsType = [

5211 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5212 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5213 ("_with_options", InternalTraversal.dp_executable_options),

5214 ]

5215

5216 _is_clone_of: Optional[ClauseElement]

5217 _raw_columns: List[_ColumnsClauseElement]

5218 _setup_joins: Tuple[_SetupJoinsElement, ...]

5219 _with_options: Tuple[ExecutableOption, ...]

5220

5221 _annotations = util.EMPTY_DICT

5222

5223 def _clone(self, **kw: Any) -> Self:

5224 c = self.__class__.__new__(self.__class__)

5225 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5226

5227 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5228 return c

5229

5230 @classmethod

5231 def _generate_for_statement(

5232 cls, select_stmt: Select[Unpack[TupleAny]]

5233 ) -> None:

5234 if select_stmt._setup_joins or select_stmt._with_options:

5235 self = _MemoizedSelectEntities()

5236 self._raw_columns = select_stmt._raw_columns

5237 self._setup_joins = select_stmt._setup_joins

5238 self._with_options = select_stmt._with_options

5239

5240 select_stmt._memoized_select_entities += (self,)

5241 select_stmt._raw_columns = []

5242 select_stmt._setup_joins = select_stmt._with_options = ()

5243

5244

5245class Select(

5246 HasPrefixes,

5247 HasSuffixes,

5248 HasHints,

5249 HasCompileState,

5250 HasSyntaxExtensions[

5251 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5252 ],

5253 _SelectFromElements,

5254 GenerativeSelect,

5255 TypedReturnsRows[Unpack[_Ts]],

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 Available extension points:

5263

5264 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5265 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5266 keyword (if any) and the list of columns.

5267 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5268 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5269

5270 .. seealso::

5271

5272 :func:`_sql.select`

5273

5274 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5275

5276 """

5277

5278 __visit_name__ = "select"

5279

5280 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5281 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5282

5283 _raw_columns: List[_ColumnsClauseElement]

5284

5285 _distinct: bool = False

5286 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5287 _correlate: Tuple[FromClause, ...] = ()

5288 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5289 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5290 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5291 _from_obj: Tuple[FromClause, ...] = ()

5292

5293 _position_map = util.immutabledict(

5294 {

5295 "post_select": "_post_select_clause",

5296 "pre_columns": "_pre_columns_clause",

5297 "post_criteria": "_post_criteria_clause",

5298 "post_body": "_post_body_clause",

5299 }

5300 )

5301

5302 _post_select_clause: Optional[ClauseElement] = None

5303 """extension point for a ClauseElement that will be compiled directly

5304 after the SELECT keyword.

5305

5306 .. versionadded:: 2.1

5307

5308 """

5309

5310 _pre_columns_clause: Optional[ClauseElement] = None

5311 """extension point for a ClauseElement that will be compiled directly

5312 before the "columns" clause; after DISTINCT (if present).

5313

5314 .. versionadded:: 2.1

5315

5316 """

5317

5318 _post_criteria_clause: Optional[ClauseElement] = None

5319 """extension point for a ClauseElement that will be compiled directly

5320 after "criteria", following the HAVING clause but before ORDER BY.

5321

5322 .. versionadded:: 2.1

5323

5324 """

5325

5326 _post_body_clause: Optional[ClauseElement] = None

5327 """extension point for a ClauseElement that will be compiled directly

5328 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5329 of the SELECT.

5330

5331 .. versionadded:: 2.1

5332

5333 """

5334

5335 _auto_correlate = True

5336 _is_select_statement = True

5337 _compile_options: CacheableOptions = (

5338 SelectState.default_select_compile_options

5339 )

5340

5341 _traverse_internals: _TraverseInternalsType = (

5342 [

5343 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5344 (

5345 "_memoized_select_entities",

5346 InternalTraversal.dp_memoized_select_entities,

5347 ),

5348 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5349 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5350 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5351 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5352 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5353 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5354 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5355 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5356 ("_limit_clause", InternalTraversal.dp_clauseelement),

5357 ("_offset_clause", InternalTraversal.dp_clauseelement),

5358 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5359 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5360 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5361 ("_distinct", InternalTraversal.dp_boolean),

5362 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5363 ("_label_style", InternalTraversal.dp_plain_obj),

5364 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5365 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5366 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5367 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5368 ]

5369 + HasCTE._has_ctes_traverse_internals

5370 + HasPrefixes._has_prefixes_traverse_internals

5371 + HasSuffixes._has_suffixes_traverse_internals

5372 + HasHints._has_hints_traverse_internals

5373 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5374 + Executable._executable_traverse_internals

5375 + DialectKWArgs._dialect_kwargs_traverse_internals

5376 )

5377

5378 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5379 ("_compile_options", InternalTraversal.dp_has_cache_key)

5380 ]

5381

5382 _compile_state_factory: Type[SelectState]

5383

5384 @classmethod

5385 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5386 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5387

5388 Used internally to build up :class:`.Select` constructs with

5389 pre-established state.

5390

5391 """

5392

5393 stmt = Select.__new__(Select)

5394 stmt.__dict__.update(kw)

5395 return stmt

5396

5397 def __init__(

5398 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5399 ):

5400 r"""Construct a new :class:`_expression.Select`.

5401

5402 The public constructor for :class:`_expression.Select` is the

5403 :func:`_sql.select` function.

5404

5405 """

5406 self._raw_columns = [

5407 coercions.expect(

5408 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5409 )

5410 for ent in entities

5411 ]

5412 GenerativeSelect.__init__(self)

5413

5414 def _apply_syntax_extension_to_self(

5415 self, extension: SyntaxExtension

5416 ) -> None:

5417 extension.apply_to_select(self)

5418

5419 def _scalar_type(self) -> TypeEngine[Any]:

5420 if not self._raw_columns:

5421 return NULLTYPE

5422 elem = self._raw_columns[0]

5423 cols = list(elem._select_iterable)

5424 return cols[0].type

5425

5426 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5427 """A synonym for the :meth:`_sql.Select.where` method."""

5428

5429 return self.where(*criteria)

5430

5431 def _filter_by_zero(

5432 self,

5433 ) -> Union[

5434 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5435 ]:

5436 if self._setup_joins:

5437 meth = SelectState.get_plugin_class(

5438 self

5439 ).determine_last_joined_entity

5440 _last_joined_entity = meth(self)

5441 if _last_joined_entity is not None:

5442 return _last_joined_entity

5443

5444 if self._from_obj:

5445 return self._from_obj[0]

5446

5447 return self._raw_columns[0]

5448

5449 if TYPE_CHECKING:

5450

5451 @overload

5452 def scalar_subquery(

5453 self: Select[_MAYBE_ENTITY],

5454 ) -> ScalarSelect[Any]: ...

5455

5456 @overload

5457 def scalar_subquery(

5458 self: Select[_NOT_ENTITY],

5459 ) -> ScalarSelect[_NOT_ENTITY]: ...

5460

5461 @overload

5462 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5463

5464 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5465

5466 def filter_by(self, **kwargs: Any) -> Self:

5467 r"""apply the given filtering criterion as a WHERE clause

5468 to this select.

5469

5470 """

5471 from_entity = self._filter_by_zero()

5472

5473 clauses = [

5474 _entity_namespace_key(from_entity, key) == value

5475 for key, value in kwargs.items()

5476 ]

5477 return self.filter(*clauses)

5478

5479 @property

5480 def column_descriptions(self) -> Any:

5481 """Return a :term:`plugin-enabled` 'column descriptions' structure

5482 referring to the columns which are SELECTed by this statement.

5483

5484 This attribute is generally useful when using the ORM, as an

5485 extended structure which includes information about mapped

5486 entities is returned. The section :ref:`queryguide_inspection`

5487 contains more background.

5488

5489 For a Core-only statement, the structure returned by this accessor

5490 is derived from the same objects that are returned by the

5491 :attr:`.Select.selected_columns` accessor, formatted as a list of

5492 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5493 which indicate the column expressions to be selected::

5494

5495 >>> stmt = select(user_table)

5496 >>> stmt.column_descriptions

5497 [

5498 {

5499 'name': 'id',

5500 'type': Integer(),

5501 'expr': Column('id', Integer(), ...)},

5502 {

5503 'name': 'name',

5504 'type': String(length=30),

5505 'expr': Column('name', String(length=30), ...)}

5506 ]

5507

5508 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5509 attribute returns a structure for a Core-only set of entities,

5510 not just ORM-only entities.

5511

5512 .. seealso::

5513

5514 :attr:`.UpdateBase.entity_description` - entity information for

5515 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5516

5517 :ref:`queryguide_inspection` - ORM background

5518

5519 """

5520 meth = SelectState.get_plugin_class(self).get_column_descriptions

5521 return meth(self)

5522

5523 def from_statement(

5524 self, statement: roles.ReturnsRowsRole

5525 ) -> ExecutableReturnsRows:

5526 """Apply the columns which this :class:`.Select` would select

5527 onto another statement.

5528

5529 This operation is :term:`plugin-specific` and will raise a not

5530 supported exception if this :class:`_sql.Select` does not select from

5531 plugin-enabled entities.

5532

5533

5534 The statement is typically either a :func:`_expression.text` or

5535 :func:`_expression.select` construct, and should return the set of

5536 columns appropriate to the entities represented by this

5537 :class:`.Select`.

5538

5539 .. seealso::

5540

5541 :ref:`orm_queryguide_selecting_text` - usage examples in the

5542 ORM Querying Guide

5543

5544 """

5545 meth = SelectState.get_plugin_class(self).from_statement

5546 return meth(self, statement)

5547

5548 @_generative

5549 def join(

5550 self,

5551 target: _JoinTargetArgument,

5552 onclause: Optional[_OnClauseArgument] = None,

5553 *,

5554 isouter: bool = False,

5555 full: bool = False,

5556 ) -> Self:

5557 r"""Create a SQL JOIN against this :class:`_expression.Select`

5558 object's criterion

5559 and apply generatively, returning the newly resulting

5560 :class:`_expression.Select`.

5561

5562 E.g.::

5563

5564 stmt = select(user_table).join(

5565 address_table, user_table.c.id == address_table.c.user_id

5566 )

5567

5568 The above statement generates SQL similar to:

5569

5570 .. sourcecode:: sql

5571

5572 SELECT user.id, user.name

5573 FROM user

5574 JOIN address ON user.id = address.user_id

5575

5576 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5577 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5578 source that is within the FROM clause of the existing SELECT,

5579 and a given target :class:`_sql.FromClause`, and then adds

5580 this :class:`_sql.Join` to the FROM clause of the newly generated

5581 SELECT statement. This is completely reworked from the behavior

5582 in 1.3, which would instead create a subquery of the entire

5583 :class:`_expression.Select` and then join that subquery to the

5584 target.

5585

5586 This is a **backwards incompatible change** as the previous behavior

5587 was mostly useless, producing an unnamed subquery rejected by

5588 most databases in any case. The new behavior is modeled after

5589 that of the very successful :meth:`_orm.Query.join` method in the

5590 ORM, in order to support the functionality of :class:`_orm.Query`

5591 being available by using a :class:`_sql.Select` object with an

5592 :class:`_orm.Session`.

5593

5594 See the notes for this change at :ref:`change_select_join`.

5595

5596

5597 :param target: target table to join towards

5598

5599 :param onclause: ON clause of the join. If omitted, an ON clause

5600 is generated automatically based on the :class:`_schema.ForeignKey`

5601 linkages between the two tables, if one can be unambiguously

5602 determined, otherwise an error is raised.

5603

5604 :param isouter: if True, generate LEFT OUTER join. Same as

5605 :meth:`_expression.Select.outerjoin`.

5606

5607 :param full: if True, generate FULL OUTER join.

5608

5609 .. seealso::

5610

5611 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5612

5613 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5614

5615 :meth:`_expression.Select.join_from`

5616

5617 :meth:`_expression.Select.outerjoin`

5618

5619 """ # noqa: E501

5620 join_target = coercions.expect(

5621 roles.JoinTargetRole, target, apply_propagate_attrs=self

5622 )

5623 if onclause is not None:

5624 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5625 else:

5626 onclause_element = None

5627

5628 self._setup_joins += (

5629 (

5630 join_target,

5631 onclause_element,

5632 None,

5633 {"isouter": isouter, "full": full},

5634 ),

5635 )

5636 return self

5637

5638 def outerjoin_from(

5639 self,

5640 from_: _FromClauseArgument,

5641 target: _JoinTargetArgument,

5642 onclause: Optional[_OnClauseArgument] = None,

5643 *,

5644 full: bool = False,

5645 ) -> Self:

5646 r"""Create a SQL LEFT OUTER JOIN against this

5647 :class:`_expression.Select` object's criterion and apply generatively,

5648 returning the newly resulting :class:`_expression.Select`.

5649

5650 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5651

5652 """

5653 return self.join_from(

5654 from_, target, onclause=onclause, isouter=True, full=full

5655 )

5656

5657 @_generative

5658 def join_from(

5659 self,

5660 from_: _FromClauseArgument,

5661 target: _JoinTargetArgument,

5662 onclause: Optional[_OnClauseArgument] = None,

5663 *,

5664 isouter: bool = False,

5665 full: bool = False,

5666 ) -> Self:

5667 r"""Create a SQL JOIN against this :class:`_expression.Select`

5668 object's criterion

5669 and apply generatively, returning the newly resulting

5670 :class:`_expression.Select`.

5671

5672 E.g.::

5673

5674 stmt = select(user_table, address_table).join_from(

5675 user_table, address_table, user_table.c.id == address_table.c.user_id

5676 )

5677

5678 The above statement generates SQL similar to:

5679

5680 .. sourcecode:: sql

5681

5682 SELECT user.id, user.name, address.id, address.email, address.user_id

5683 FROM user JOIN address ON user.id = address.user_id

5684

5685 .. versionadded:: 1.4

5686

5687 :param from\_: the left side of the join, will be rendered in the

5688 FROM clause and is roughly equivalent to using the

5689 :meth:`.Select.select_from` method.

5690

5691 :param target: target table to join towards

5692

5693 :param onclause: ON clause of the join.

5694

5695 :param isouter: if True, generate LEFT OUTER join. Same as

5696 :meth:`_expression.Select.outerjoin`.

5697

5698 :param full: if True, generate FULL OUTER join.

5699

5700 .. seealso::

5701

5702 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5703

5704 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5705

5706 :meth:`_expression.Select.join`

5707

5708 """ # noqa: E501

5709

5710 # note the order of parsing from vs. target is important here, as we

5711 # are also deriving the source of the plugin (i.e. the subject mapper

5712 # in an ORM query) which should favor the "from_" over the "target"

5713

5714 from_ = coercions.expect(

5715 roles.FromClauseRole, from_, apply_propagate_attrs=self

5716 )

5717 join_target = coercions.expect(

5718 roles.JoinTargetRole, target, apply_propagate_attrs=self

5719 )

5720 if onclause is not None:

5721 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5722 else:

5723 onclause_element = None

5724

5725 self._setup_joins += (

5726 (

5727 join_target,

5728 onclause_element,

5729 from_,

5730 {"isouter": isouter, "full": full},

5731 ),

5732 )

5733 return self

5734

5735 def outerjoin(

5736 self,

5737 target: _JoinTargetArgument,

5738 onclause: Optional[_OnClauseArgument] = None,

5739 *,

5740 full: bool = False,

5741 ) -> Self:

5742 """Create a left outer join.

5743

5744 Parameters are the same as that of :meth:`_expression.Select.join`.

5745

5746 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5747 creates a :class:`_sql.Join` object between a

5748 :class:`_sql.FromClause` source that is within the FROM clause of

5749 the existing SELECT, and a given target :class:`_sql.FromClause`,

5750 and then adds this :class:`_sql.Join` to the FROM clause of the

5751 newly generated SELECT statement. This is completely reworked

5752 from the behavior in 1.3, which would instead create a subquery of

5753 the entire

5754 :class:`_expression.Select` and then join that subquery to the

5755 target.

5756

5757 This is a **backwards incompatible change** as the previous behavior

5758 was mostly useless, producing an unnamed subquery rejected by

5759 most databases in any case. The new behavior is modeled after

5760 that of the very successful :meth:`_orm.Query.join` method in the

5761 ORM, in order to support the functionality of :class:`_orm.Query`

5762 being available by using a :class:`_sql.Select` object with an

5763 :class:`_orm.Session`.

5764

5765 See the notes for this change at :ref:`change_select_join`.

5766

5767 .. seealso::

5768

5769 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5770

5771 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5772

5773 :meth:`_expression.Select.join`

5774

5775 """

5776 return self.join(target, onclause=onclause, isouter=True, full=full)

5777

5778 def get_final_froms(self) -> Sequence[FromClause]:

5779 """Compute the final displayed list of :class:`_expression.FromClause`

5780 elements.

5781

5782 This method will run through the full computation required to

5783 determine what FROM elements will be displayed in the resulting

5784 SELECT statement, including shadowing individual tables with

5785 JOIN objects, as well as full computation for ORM use cases including

5786 eager loading clauses.

5787

5788 For ORM use, this accessor returns the **post compilation**

5789 list of FROM objects; this collection will include elements such as

5790 eagerly loaded tables and joins. The objects will **not** be

5791 ORM enabled and not work as a replacement for the

5792 :meth:`_sql.Select.select_froms` collection; additionally, the

5793 method is not well performing for an ORM enabled statement as it

5794 will incur the full ORM construction process.

5795

5796 To retrieve the FROM list that's implied by the "columns" collection

5797 passed to the :class:`_sql.Select` originally, use the

5798 :attr:`_sql.Select.columns_clause_froms` accessor.

5799

5800 To select from an alternative set of columns while maintaining the

5801 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5802 pass the

5803 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5804 parameter.

5805

5806 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5807 method replaces the previous :attr:`_sql.Select.froms` accessor,

5808 which is deprecated.

5809

5810 .. seealso::

5811

5812 :attr:`_sql.Select.columns_clause_froms`

5813

5814 """

5815 compiler = self._default_compiler()

5816

5817 return self._compile_state_factory(self, compiler)._get_display_froms()

5818

5819 @property

5820 @util.deprecated(

5821 "1.4.23",

5822 "The :attr:`_expression.Select.froms` attribute is moved to "

5823 "the :meth:`_expression.Select.get_final_froms` method.",

5824 )

5825 def froms(self) -> Sequence[FromClause]:

5826 """Return the displayed list of :class:`_expression.FromClause`

5827 elements.

5828

5829

5830 """

5831 return self.get_final_froms()

5832

5833 @property

5834 def columns_clause_froms(self) -> List[FromClause]:

5835 """Return the set of :class:`_expression.FromClause` objects implied

5836 by the columns clause of this SELECT statement.

5837

5838 .. versionadded:: 1.4.23

5839

5840 .. seealso::

5841

5842 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5843 statement into account

5844

5845 :meth:`_sql.Select.with_only_columns` - makes use of this

5846 collection to set up a new FROM list

5847

5848 """

5849

5850 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5851 self

5852 )

5853

5854 @property

5855 def inner_columns(self) -> _SelectIterable:

5856 """An iterator of all :class:`_expression.ColumnElement`

5857 expressions which would

5858 be rendered into the columns clause of the resulting SELECT statement.

5859

5860 This method is legacy as of 1.4 and is superseded by the

5861 :attr:`_expression.Select.exported_columns` collection.

5862

5863 """

5864

5865 return iter(self._all_selected_columns)

5866

5867 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

5868 if fromclause is not None and self in fromclause._cloned_set:

5869 return True

5870

5871 for f in self._iterate_from_elements():

5872 if f.is_derived_from(fromclause):

5873 return True

5874 return False

5875

5876 def _copy_internals(

5877 self, clone: _CloneCallableType = _clone, **kw: Any

5878 ) -> None:

5879 # Select() object has been cloned and probably adapted by the

5880 # given clone function. Apply the cloning function to internal

5881 # objects

5882

5883 # 1. keep a dictionary of the froms we've cloned, and what

5884 # they've become. This allows us to ensure the same cloned from

5885 # is used when other items such as columns are "cloned"

5886

5887 all_the_froms = set(

5888 itertools.chain(

5889 _from_objects(*self._raw_columns),

5890 _from_objects(*self._where_criteria),

5891 _from_objects(*[elem[0] for elem in self._setup_joins]),

5892 )

5893 )

5894

5895 # do a clone for the froms we've gathered. what is important here

5896 # is if any of the things we are selecting from, like tables,

5897 # were converted into Join objects. if so, these need to be

5898 # added to _from_obj explicitly, because otherwise they won't be

5899 # part of the new state, as they don't associate themselves with

5900 # their columns.

5901 new_froms = {f: clone(f, **kw) for f in all_the_froms}

5902

5903 # 2. copy FROM collections, adding in joins that we've created.

5904 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5905 add_froms = (

5906 {f for f in new_froms.values() if isinstance(f, Join)}

5907 .difference(all_the_froms)

5908 .difference(existing_from_obj)

5909 )

5910

5911 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5912

5913 # 3. clone everything else, making sure we use columns

5914 # corresponding to the froms we just made.

5915 def replace(

5916 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

5917 **kw: Any,

5918 ) -> Optional[KeyedColumnElement[Any]]:

5919 if isinstance(obj, ColumnClause) and obj.table in new_froms:

5920 newelem = new_froms[obj.table].corresponding_column(obj)

5921 return newelem

5922 return None

5923

5924 kw["replace"] = replace

5925

5926 # copy everything else. for table-ish things like correlate,

5927 # correlate_except, setup_joins, these clone normally. For

5928 # column-expression oriented things like raw_columns, where_criteria,

5929 # order by, we get this from the new froms.

5930 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5931

5932 self._reset_memoizations()

5933

5934 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5935 return itertools.chain(

5936 super().get_children(

5937 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5938 **kw,

5939 ),

5940 self._iterate_from_elements(),

5941 )

5942

5943 @_generative

5944 def add_columns(

5945 self, *entities: _ColumnsClauseArgument[Any]

5946 ) -> Select[Unpack[TupleAny]]:

5947 r"""Return a new :func:`_expression.select` construct with

5948 the given entities appended to its columns clause.

5949

5950 E.g.::

5951

5952 my_select = my_select.add_columns(table.c.new_column)

5953

5954 The original expressions in the columns clause remain in place.

5955 To replace the original expressions with new ones, see the method

5956 :meth:`_expression.Select.with_only_columns`.

5957

5958 :param \*entities: column, table, or other entity expressions to be

5959 added to the columns clause

5960

5961 .. seealso::

5962

5963 :meth:`_expression.Select.with_only_columns` - replaces existing

5964 expressions rather than appending.

5965

5966 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5967 example

5968

5969 """

5970 self._reset_memoizations()

5971

5972 self._raw_columns = self._raw_columns + [

5973 coercions.expect(

5974 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5975 )

5976 for column in entities

5977 ]

5978 return self

5979

5980 def _set_entities(

5981 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5982 ) -> None:

5983 self._raw_columns = [

5984 coercions.expect(

5985 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5986 )

5987 for ent in util.to_list(entities)

5988 ]

5989

5990 @util.deprecated(

5991 "1.4",

5992 "The :meth:`_expression.Select.column` method is deprecated and will "

5993 "be removed in a future release. Please use "

5994 ":meth:`_expression.Select.add_columns`",

5995 )

5996 def column(

5997 self, column: _ColumnsClauseArgument[Any]

5998 ) -> Select[Unpack[TupleAny]]:

5999 """Return a new :func:`_expression.select` construct with

6000 the given column expression added to its columns clause.

6001

6002 E.g.::

6003

6004 my_select = my_select.column(table.c.new_column)

6005

6006 See the documentation for

6007 :meth:`_expression.Select.with_only_columns`

6008 for guidelines on adding /replacing the columns of a

6009 :class:`_expression.Select` object.

6010

6011 """

6012 return self.add_columns(column)

6013

6014 @util.preload_module("sqlalchemy.sql.util")

6015 def reduce_columns(

6016 self, only_synonyms: bool = True

6017 ) -> Select[Unpack[TupleAny]]:

6018 """Return a new :func:`_expression.select` construct with redundantly

6019 named, equivalently-valued columns removed from the columns clause.

6020

6021 "Redundant" here means two columns where one refers to the

6022 other either based on foreign key, or via a simple equality

6023 comparison in the WHERE clause of the statement. The primary purpose

6024 of this method is to automatically construct a select statement

6025 with all uniquely-named columns, without the need to use

6026 table-qualified labels as

6027 :meth:`_expression.Select.set_label_style`

6028 does.

6029

6030 When columns are omitted based on foreign key, the referred-to

6031 column is the one that's kept. When columns are omitted based on

6032 WHERE equivalence, the first column in the columns clause is the

6033 one that's kept.

6034

6035 :param only_synonyms: when True, limit the removal of columns

6036 to those which have the same name as the equivalent. Otherwise,

6037 all columns that are equivalent to another are removed.

6038

6039 """

6040 woc: Select[Unpack[TupleAny]]

6041 woc = self.with_only_columns(

6042 *util.preloaded.sql_util.reduce_columns(

6043 self._all_selected_columns,

6044 only_synonyms=only_synonyms,

6045 *(self._where_criteria + self._from_obj),

6046 )

6047 )

6048 return woc

6049

6050 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6051

6052 # code within this block is **programmatically,

6053 # statically generated** by tools/generate_tuple_map_overloads.py

6054

6055 @overload

6056 def with_only_columns(

6057 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6058 ) -> Select[_T0]: ...

6059

6060 @overload

6061 def with_only_columns(

6062 self,

6063 __ent0: _TCCA[_T0],

6064 __ent1: _TCCA[_T1],

6065 /,

6066 *,

6067 maintain_column_froms: bool = ...,

6068 ) -> Select[_T0, _T1]: ...

6069

6070 @overload

6071 def with_only_columns(

6072 self,

6073 __ent0: _TCCA[_T0],

6074 __ent1: _TCCA[_T1],

6075 __ent2: _TCCA[_T2],

6076 /,

6077 *,

6078 maintain_column_froms: bool = ...,

6079 ) -> Select[_T0, _T1, _T2]: ...

6080

6081 @overload

6082 def with_only_columns(

6083 self,

6084 __ent0: _TCCA[_T0],

6085 __ent1: _TCCA[_T1],

6086 __ent2: _TCCA[_T2],

6087 __ent3: _TCCA[_T3],

6088 /,

6089 *,

6090 maintain_column_froms: bool = ...,

6091 ) -> Select[_T0, _T1, _T2, _T3]: ...

6092

6093 @overload

6094 def with_only_columns(

6095 self,

6096 __ent0: _TCCA[_T0],

6097 __ent1: _TCCA[_T1],

6098 __ent2: _TCCA[_T2],

6099 __ent3: _TCCA[_T3],

6100 __ent4: _TCCA[_T4],

6101 /,

6102 *,

6103 maintain_column_froms: bool = ...,

6104 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6105

6106 @overload

6107 def with_only_columns(

6108 self,

6109 __ent0: _TCCA[_T0],

6110 __ent1: _TCCA[_T1],

6111 __ent2: _TCCA[_T2],

6112 __ent3: _TCCA[_T3],

6113 __ent4: _TCCA[_T4],

6114 __ent5: _TCCA[_T5],

6115 /,

6116 *,

6117 maintain_column_froms: bool = ...,

6118 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6119

6120 @overload

6121 def with_only_columns(

6122 self,

6123 __ent0: _TCCA[_T0],

6124 __ent1: _TCCA[_T1],

6125 __ent2: _TCCA[_T2],

6126 __ent3: _TCCA[_T3],

6127 __ent4: _TCCA[_T4],

6128 __ent5: _TCCA[_T5],

6129 __ent6: _TCCA[_T6],

6130 /,

6131 *,

6132 maintain_column_froms: bool = ...,

6133 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6134

6135 @overload

6136 def with_only_columns(

6137 self,

6138 __ent0: _TCCA[_T0],

6139 __ent1: _TCCA[_T1],

6140 __ent2: _TCCA[_T2],

6141 __ent3: _TCCA[_T3],

6142 __ent4: _TCCA[_T4],

6143 __ent5: _TCCA[_T5],

6144 __ent6: _TCCA[_T6],

6145 __ent7: _TCCA[_T7],

6146 /,

6147 *entities: _ColumnsClauseArgument[Any],

6148 maintain_column_froms: bool = ...,

6149 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6150

6151 # END OVERLOADED FUNCTIONS self.with_only_columns

6152

6153 @overload

6154 def with_only_columns(

6155 self,

6156 *entities: _ColumnsClauseArgument[Any],

6157 maintain_column_froms: bool = False,

6158 **__kw: Any,

6159 ) -> Select[Unpack[TupleAny]]: ...

6160

6161 @_generative

6162 def with_only_columns(

6163 self,

6164 *entities: _ColumnsClauseArgument[Any],

6165 maintain_column_froms: bool = False,

6166 **__kw: Any,

6167 ) -> Select[Unpack[TupleAny]]:

6168 r"""Return a new :func:`_expression.select` construct with its columns

6169 clause replaced with the given entities.

6170

6171 By default, this method is exactly equivalent to as if the original

6172 :func:`_expression.select` had been called with the given entities.

6173 E.g. a statement::

6174

6175 s = select(table1.c.a, table1.c.b)

6176 s = s.with_only_columns(table1.c.b)

6177

6178 should be exactly equivalent to::

6179

6180 s = select(table1.c.b)

6181

6182 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6183 will also dynamically alter the FROM clause of the

6184 statement if it is not explicitly stated.

6185 To maintain the existing set of FROMs including those implied by the

6186 current columns clause, add the

6187 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6188 parameter::

6189

6190 s = select(table1.c.a, table2.c.b)

6191 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6192

6193 The above parameter performs a transfer of the effective FROMs

6194 in the columns collection to the :meth:`_sql.Select.select_from`

6195 method, as though the following were invoked::

6196

6197 s = select(table1.c.a, table2.c.b)

6198 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6199

6200 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6201 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6202 collection and performs an operation equivalent to the following::

6203

6204 s = select(table1.c.a, table2.c.b)

6205 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6206

6207 :param \*entities: column expressions to be used.

6208

6209 :param maintain_column_froms: boolean parameter that will ensure the

6210 FROM list implied from the current columns clause will be transferred

6211 to the :meth:`_sql.Select.select_from` method first.

6212

6213 .. versionadded:: 1.4.23

6214

6215 """ # noqa: E501

6216

6217 if __kw:

6218 raise _no_kw()

6219

6220 # memoizations should be cleared here as of

6221 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6222 # is the case for now.

6223 self._assert_no_memoizations()

6224

6225 if maintain_column_froms:

6226 self.select_from.non_generative( # type: ignore

6227 self, *self.columns_clause_froms

6228 )

6229

6230 # then memoize the FROMs etc.

6231 _MemoizedSelectEntities._generate_for_statement(self)

6232

6233 self._raw_columns = [

6234 coercions.expect(roles.ColumnsClauseRole, c)

6235 for c in coercions._expression_collection_was_a_list(

6236 "entities", "Select.with_only_columns", entities

6237 )

6238 ]

6239 return self

6240

6241 @property

6242 def whereclause(self) -> Optional[ColumnElement[Any]]:

6243 """Return the completed WHERE clause for this

6244 :class:`_expression.Select` statement.

6245

6246 This assembles the current collection of WHERE criteria

6247 into a single :class:`_expression.BooleanClauseList` construct.

6248

6249

6250 .. versionadded:: 1.4

6251

6252 """

6253

6254 return BooleanClauseList._construct_for_whereclause(

6255 self._where_criteria

6256 )

6257

6258 _whereclause = whereclause

6259

6260 @_generative

6261 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6262 """Return a new :func:`_expression.select` construct with

6263 the given expression added to

6264 its WHERE clause, joined to the existing clause via AND, if any.

6265

6266 """

6267

6268 assert isinstance(self._where_criteria, tuple)

6269

6270 for criterion in whereclause:

6271 where_criteria: ColumnElement[Any] = coercions.expect(

6272 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6273 )

6274 self._where_criteria += (where_criteria,)

6275 return self

6276

6277 @_generative

6278 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6279 """Return a new :func:`_expression.select` construct with

6280 the given expression added to

6281 its HAVING clause, joined to the existing clause via AND, if any.

6282

6283 """

6284

6285 for criterion in having:

6286 having_criteria = coercions.expect(

6287 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6288 )

6289 self._having_criteria += (having_criteria,)

6290 return self

6291

6292 @_generative

6293 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6294 r"""Return a new :func:`_expression.select` construct which

6295 will apply DISTINCT to the SELECT statement overall.

6296

6297 E.g.::

6298

6299 from sqlalchemy import select

6300

6301 stmt = select(users_table.c.id, users_table.c.name).distinct()

6302

6303 The above would produce an statement resembling:

6304

6305 .. sourcecode:: sql

6306

6307 SELECT DISTINCT user.id, user.name FROM user

6308

6309 The method also historically accepted an ``*expr`` parameter which

6310 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6311 This is now replaced using the :func:`_postgresql.distinct_on`

6312 extension::

6313

6314 from sqlalchemy import select

6315 from sqlalchemy.dialects.postgresql import distinct_on

6316

6317 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6318

6319 Using this parameter on other backends which don't support this

6320 syntax will raise an error.

6321

6322 :param \*expr: optional column expressions. When present,

6323 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6324 construct. A deprecation warning and/or :class:`_exc.CompileError`

6325 will be raised on other backends.

6326

6327 .. deprecated:: 2.1 Passing expressions to

6328 :meth:`_sql.Select.distinct` is deprecated, use

6329 :func:`_postgresql.distinct_on` instead.

6330

6331 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6332 and will raise :class:`_exc.CompileError` in a future version.

6333

6334 .. seealso::

6335

6336 :func:`_postgresql.distinct_on`

6337

6338 :meth:`_sql.HasSyntaxExtensions.ext`

6339 """

6340 self._distinct = True

6341 if expr:

6342 warn_deprecated(

6343 "Passing expression to ``distinct`` to generate a "

6344 "DISTINCT ON clause is deprecated. Use instead the "

6345 "``postgresql.distinct_on`` function as an extension.",

6346 "2.1",

6347 )

6348 self._distinct_on = self._distinct_on + tuple(

6349 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6350 for e in expr

6351 )

6352 return self

6353

6354 @_generative

6355 def select_from(self, *froms: _FromClauseArgument) -> Self:

6356 r"""Return a new :func:`_expression.select` construct with the

6357 given FROM expression(s)

6358 merged into its list of FROM objects.

6359

6360 E.g.::

6361

6362 table1 = table("t1", column("a"))

6363 table2 = table("t2", column("b"))

6364 s = select(table1.c.a).select_from(

6365 table1.join(table2, table1.c.a == table2.c.b)

6366 )

6367

6368 The "from" list is a unique set on the identity of each element,

6369 so adding an already present :class:`_schema.Table`

6370 or other selectable

6371 will have no effect. Passing a :class:`_expression.Join` that refers

6372 to an already present :class:`_schema.Table`

6373 or other selectable will have

6374 the effect of concealing the presence of that selectable as

6375 an individual element in the rendered FROM list, instead

6376 rendering it into a JOIN clause.

6377

6378 While the typical purpose of :meth:`_expression.Select.select_from`

6379 is to

6380 replace the default, derived FROM clause with a join, it can

6381 also be called with individual table elements, multiple times

6382 if desired, in the case that the FROM clause cannot be fully

6383 derived from the columns clause::

6384

6385 select(func.count("*")).select_from(table1)

6386

6387 """

6388

6389 self._from_obj += tuple(

6390 coercions.expect(

6391 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6392 )

6393 for fromclause in froms

6394 )

6395 return self

6396

6397 @_generative

6398 def correlate(

6399 self,

6400 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6401 ) -> Self:

6402 r"""Return a new :class:`_expression.Select`

6403 which will correlate the given FROM

6404 clauses to that of an enclosing :class:`_expression.Select`.

6405

6406 Calling this method turns off the :class:`_expression.Select` object's

6407 default behavior of "auto-correlation". Normally, FROM elements

6408 which appear in a :class:`_expression.Select`

6409 that encloses this one via

6410 its :term:`WHERE clause`, ORDER BY, HAVING or

6411 :term:`columns clause` will be omitted from this

6412 :class:`_expression.Select`

6413 object's :term:`FROM clause`.

6414 Setting an explicit correlation collection using the

6415 :meth:`_expression.Select.correlate`

6416 method provides a fixed list of FROM objects

6417 that can potentially take place in this process.

6418

6419 When :meth:`_expression.Select.correlate`

6420 is used to apply specific FROM clauses

6421 for correlation, the FROM elements become candidates for

6422 correlation regardless of how deeply nested this

6423 :class:`_expression.Select`

6424 object is, relative to an enclosing :class:`_expression.Select`

6425 which refers to

6426 the same FROM object. This is in contrast to the behavior of

6427 "auto-correlation" which only correlates to an immediate enclosing

6428 :class:`_expression.Select`.

6429 Multi-level correlation ensures that the link

6430 between enclosed and enclosing :class:`_expression.Select`

6431 is always via

6432 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6433 correlation to take place.

6434

6435 If ``None`` is passed, the :class:`_expression.Select`

6436 object will correlate

6437 none of its FROM entries, and all will render unconditionally

6438 in the local FROM clause.

6439

6440 :param \*fromclauses: one or more :class:`.FromClause` or other

6441 FROM-compatible construct such as an ORM mapped entity to become part

6442 of the correlate collection; alternatively pass a single value

6443 ``None`` to remove all existing correlations.

6444

6445 .. seealso::

6446

6447 :meth:`_expression.Select.correlate_except`

6448

6449 :ref:`tutorial_scalar_subquery`

6450

6451 """

6452

6453 # tests failing when we try to change how these

6454 # arguments are passed

6455

6456 self._auto_correlate = False

6457 if not fromclauses or fromclauses[0] in {None, False}:

6458 if len(fromclauses) > 1:

6459 raise exc.ArgumentError(

6460 "additional FROM objects not accepted when "

6461 "passing None/False to correlate()"

6462 )

6463 self._correlate = ()

6464 else:

6465 self._correlate = self._correlate + tuple(

6466 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6467 )

6468 return self

6469

6470 @_generative

6471 def correlate_except(

6472 self,

6473 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6474 ) -> Self:

6475 r"""Return a new :class:`_expression.Select`

6476 which will omit the given FROM

6477 clauses from the auto-correlation process.

6478

6479 Calling :meth:`_expression.Select.correlate_except` turns off the

6480 :class:`_expression.Select` object's default behavior of

6481 "auto-correlation" for the given FROM elements. An element

6482 specified here will unconditionally appear in the FROM list, while

6483 all other FROM elements remain subject to normal auto-correlation

6484 behaviors.

6485

6486 If ``None`` is passed, or no arguments are passed,

6487 the :class:`_expression.Select` object will correlate all of its

6488 FROM entries.

6489

6490 :param \*fromclauses: a list of one or more

6491 :class:`_expression.FromClause`

6492 constructs, or other compatible constructs (i.e. ORM-mapped

6493 classes) to become part of the correlate-exception collection.

6494

6495 .. seealso::

6496

6497 :meth:`_expression.Select.correlate`

6498

6499 :ref:`tutorial_scalar_subquery`

6500

6501 """

6502

6503 self._auto_correlate = False

6504 if not fromclauses or fromclauses[0] in {None, False}:

6505 if len(fromclauses) > 1:

6506 raise exc.ArgumentError(

6507 "additional FROM objects not accepted when "

6508 "passing None/False to correlate_except()"

6509 )

6510 self._correlate_except = ()

6511 else:

6512 self._correlate_except = (self._correlate_except or ()) + tuple(

6513 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6514 )

6515

6516 return self

6517

6518 @HasMemoized_ro_memoized_attribute

6519 def selected_columns(

6520 self,

6521 ) -> ColumnCollection[str, ColumnElement[Any]]:

6522 """A :class:`_expression.ColumnCollection`

6523 representing the columns that

6524 this SELECT statement or similar construct returns in its result set,

6525 not including :class:`_sql.TextClause` constructs.

6526

6527 This collection differs from the :attr:`_expression.FromClause.columns`

6528 collection of a :class:`_expression.FromClause` in that the columns

6529 within this collection cannot be directly nested inside another SELECT

6530 statement; a subquery must be applied first which provides for the

6531 necessary parenthesization required by SQL.

6532

6533 For a :func:`_expression.select` construct, the collection here is

6534 exactly what would be rendered inside the "SELECT" statement, and the

6535 :class:`_expression.ColumnElement` objects are directly present as they

6536 were given, e.g.::

6537

6538 col1 = column("q", Integer)

6539 col2 = column("p", Integer)

6540 stmt = select(col1, col2)

6541

6542 Above, ``stmt.selected_columns`` would be a collection that contains

6543 the ``col1`` and ``col2`` objects directly. For a statement that is

6544 against a :class:`_schema.Table` or other

6545 :class:`_expression.FromClause`, the collection will use the

6546 :class:`_expression.ColumnElement` objects that are in the

6547 :attr:`_expression.FromClause.c` collection of the from element.

6548

6549 A use case for the :attr:`_sql.Select.selected_columns` collection is

6550 to allow the existing columns to be referenced when adding additional

6551 criteria, e.g.::

6552

6553 def filter_on_id(my_select, id):

6554 return my_select.where(my_select.selected_columns["id"] == id)

6555

6556

6557 stmt = select(MyModel)

6558

6559 # adds "WHERE id=:param" to the statement

6560 stmt = filter_on_id(stmt, 42)

6561

6562 .. note::

6563

6564 The :attr:`_sql.Select.selected_columns` collection does not

6565 include expressions established in the columns clause using the

6566 :func:`_sql.text` construct; these are silently omitted from the

6567 collection. To use plain textual column expressions inside of a

6568 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6569 construct.

6570

6571

6572 .. versionadded:: 1.4

6573

6574 """

6575

6576 # compare to SelectState._generate_columns_plus_names, which

6577 # generates the actual names used in the SELECT string. that

6578 # method is more complex because it also renders columns that are

6579 # fully ambiguous, e.g. same column more than once.

6580 conv = cast(

6581 "Callable[[Any], str]",

6582 SelectState._column_naming_convention(self._label_style),

6583 )

6584

6585 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6586 [

6587 (conv(c), c)

6588 for c in self._all_selected_columns

6589 if is_column_element(c)

6590 ]

6591 )

6592 return cc.as_readonly()

6593

6594 @HasMemoized_ro_memoized_attribute

6595 def _all_selected_columns(self) -> _SelectIterable:

6596 meth = SelectState.get_plugin_class(self).all_selected_columns

6597 return list(meth(self))

6598

6599 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6600 if self._label_style is LABEL_STYLE_NONE:

6601 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6602 return self

6603

6604 def _generate_fromclause_column_proxies(

6605 self,

6606 subquery: FromClause,

6607 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6608 primary_key: ColumnSet,

6609 foreign_keys: Set[KeyedColumnElement[Any]],

6610 *,

6611 proxy_compound_columns: Optional[

6612 Iterable[Sequence[ColumnElement[Any]]]

6613 ] = None,

6614 ) -> None:

6615 """Generate column proxies to place in the exported ``.c``

6616 collection of a subquery."""

6617

6618 if proxy_compound_columns:

6619 extra_col_iterator = proxy_compound_columns

6620 prox = [

6621 c._make_proxy(

6622 subquery,

6623 key=proxy_key,

6624 name=required_label_name,

6625 name_is_truncatable=True,

6626 compound_select_cols=extra_cols,

6627 primary_key=primary_key,

6628 foreign_keys=foreign_keys,

6629 )

6630 for (

6631 (

6632 required_label_name,

6633 proxy_key,

6634 fallback_label_name,

6635 c,

6636 repeated,

6637 ),

6638 extra_cols,

6639 ) in (

6640 zip(

6641 self._generate_columns_plus_names(False),

6642 extra_col_iterator,

6643 )

6644 )

6645 if is_column_element(c)

6646 ]

6647 else:

6648 prox = [

6649 c._make_proxy(

6650 subquery,

6651 key=proxy_key,

6652 name=required_label_name,

6653 name_is_truncatable=True,

6654 primary_key=primary_key,

6655 foreign_keys=foreign_keys,

6656 )

6657 for (

6658 required_label_name,

6659 proxy_key,

6660 fallback_label_name,

6661 c,

6662 repeated,

6663 ) in (self._generate_columns_plus_names(False))

6664 if is_column_element(c)

6665 ]

6666

6667 columns._populate_separate_keys(prox)

6668

6669 def _needs_parens_for_grouping(self) -> bool:

6670 return self._has_row_limiting_clause or bool(

6671 self._order_by_clause.clauses

6672 )

6673

6674 def self_group(

6675 self, against: Optional[OperatorType] = None

6676 ) -> Union[SelectStatementGrouping[Self], Self]:

6677 """Return a 'grouping' construct as per the

6678 :class:`_expression.ClauseElement` specification.

6679

6680 This produces an element that can be embedded in an expression. Note

6681 that this method is called automatically as needed when constructing

6682 expressions and should not require explicit use.

6683

6684 """

6685 if (

6686 isinstance(against, CompoundSelect)

6687 and not self._needs_parens_for_grouping()

6688 ):

6689 return self

6690 else:

6691 return SelectStatementGrouping(self)

6692

6693 def union(

6694 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6695 ) -> CompoundSelect[Unpack[_Ts]]:

6696 r"""Return a SQL ``UNION`` of this select() construct against

6697 the given selectables provided as positional arguments.

6698

6699 :param \*other: one or more elements with which to create a

6700 UNION.

6701

6702 .. versionchanged:: 1.4.28

6703

6704 multiple elements are now accepted.

6705

6706 :param \**kwargs: keyword arguments are forwarded to the constructor

6707 for the newly created :class:`_sql.CompoundSelect` object.

6708

6709 """

6710 return CompoundSelect._create_union(self, *other)

6711

6712 def union_all(

6713 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6714 ) -> CompoundSelect[Unpack[_Ts]]:

6715 r"""Return a SQL ``UNION ALL`` of this select() construct against

6716 the given selectables provided as positional arguments.

6717

6718 :param \*other: one or more elements with which to create a

6719 UNION.

6720

6721 .. versionchanged:: 1.4.28

6722

6723 multiple elements are now accepted.

6724

6725 :param \**kwargs: keyword arguments are forwarded to the constructor

6726 for the newly created :class:`_sql.CompoundSelect` object.

6727

6728 """

6729 return CompoundSelect._create_union_all(self, *other)

6730

6731 def except_(

6732 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6733 ) -> CompoundSelect[Unpack[_Ts]]:

6734 r"""Return a SQL ``EXCEPT`` of this select() construct against

6735 the given selectable provided as positional arguments.

6736

6737 :param \*other: one or more elements with which to create a

6738 UNION.

6739

6740 .. versionchanged:: 1.4.28

6741

6742 multiple elements are now accepted.

6743

6744 """

6745 return CompoundSelect._create_except(self, *other)

6746

6747 def except_all(

6748 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6749 ) -> CompoundSelect[Unpack[_Ts]]:

6750 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6751 the given selectables provided as positional arguments.

6752

6753 :param \*other: one or more elements with which to create a

6754 UNION.

6755

6756 .. versionchanged:: 1.4.28

6757

6758 multiple elements are now accepted.

6759

6760 """

6761 return CompoundSelect._create_except_all(self, *other)

6762

6763 def intersect(

6764 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6765 ) -> CompoundSelect[Unpack[_Ts]]:

6766 r"""Return a SQL ``INTERSECT`` of this select() construct against

6767 the given selectables provided as positional arguments.

6768

6769 :param \*other: one or more elements with which to create a

6770 UNION.

6771

6772 .. versionchanged:: 1.4.28

6773

6774 multiple elements are now accepted.

6775

6776 :param \**kwargs: keyword arguments are forwarded to the constructor

6777 for the newly created :class:`_sql.CompoundSelect` object.

6778

6779 """

6780 return CompoundSelect._create_intersect(self, *other)

6781

6782 def intersect_all(

6783 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6784 ) -> CompoundSelect[Unpack[_Ts]]:

6785 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6786 against the given selectables provided as positional arguments.

6787

6788 :param \*other: one or more elements with which to create a

6789 UNION.

6790

6791 .. versionchanged:: 1.4.28

6792

6793 multiple elements are now accepted.

6794

6795 :param \**kwargs: keyword arguments are forwarded to the constructor

6796 for the newly created :class:`_sql.CompoundSelect` object.

6797

6798 """

6799 return CompoundSelect._create_intersect_all(self, *other)

6800

6801

6802class ScalarSelect(

6803 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6804):

6805 """Represent a scalar subquery.

6806

6807

6808 A :class:`_sql.ScalarSelect` is created by invoking the

6809 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6810 then participates in other SQL expressions as a SQL column expression

6811 within the :class:`_sql.ColumnElement` hierarchy.

6812

6813 .. seealso::

6814

6815 :meth:`_sql.SelectBase.scalar_subquery`

6816

6817 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6818

6819 """

6820

6821 _traverse_internals: _TraverseInternalsType = [

6822 ("element", InternalTraversal.dp_clauseelement),

6823 ("type", InternalTraversal.dp_type),

6824 ]

6825

6826 _from_objects: List[FromClause] = []

6827 _is_from_container = True

6828 if not TYPE_CHECKING:

6829 _is_implicitly_boolean = False

6830 inherit_cache = True

6831

6832 element: SelectBase

6833

6834 def __init__(self, element: SelectBase) -> None:

6835 self.element = element

6836 self.type = element._scalar_type()

6837 self._propagate_attrs = element._propagate_attrs

6838

6839 def __getattr__(self, attr: str) -> Any:

6840 return getattr(self.element, attr)

6841

6842 def __getstate__(self) -> Dict[str, Any]:

6843 return {"element": self.element, "type": self.type}

6844

6845 def __setstate__(self, state: Dict[str, Any]) -> None:

6846 self.element = state["element"]

6847 self.type = state["type"]

6848

6849 @property

6850 def columns(self) -> NoReturn:

6851 raise exc.InvalidRequestError(

6852 "Scalar Select expression has no "

6853 "columns; use this object directly "

6854 "within a column-level expression."

6855 )

6856

6857 c = columns

6858

6859 @_generative

6860 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6861 """Apply a WHERE clause to the SELECT statement referred to

6862 by this :class:`_expression.ScalarSelect`.

6863

6864 """

6865 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6866 crit

6867 )

6868 return self

6869

6870 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6871 return self

6872

6873 def _ungroup(self) -> Self:

6874 return self

6875

6876 @_generative

6877 def correlate(

6878 self,

6879 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6880 ) -> Self:

6881 r"""Return a new :class:`_expression.ScalarSelect`

6882 which will correlate the given FROM

6883 clauses to that of an enclosing :class:`_expression.Select`.

6884

6885 This method is mirrored from the :meth:`_sql.Select.correlate` method

6886 of the underlying :class:`_sql.Select`. The method applies the

6887 :meth:_sql.Select.correlate` method, then returns a new

6888 :class:`_sql.ScalarSelect` against that statement.

6889

6890 .. versionadded:: 1.4 Previously, the

6891 :meth:`_sql.ScalarSelect.correlate`

6892 method was only available from :class:`_sql.Select`.

6893

6894 :param \*fromclauses: a list of one or more

6895 :class:`_expression.FromClause`

6896 constructs, or other compatible constructs (i.e. ORM-mapped

6897 classes) to become part of the correlate collection.

6898

6899 .. seealso::

6900

6901 :meth:`_expression.ScalarSelect.correlate_except`

6902

6903 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6904

6905

6906 """

6907 self.element = cast(

6908 "Select[Unpack[TupleAny]]", self.element

6909 ).correlate(*fromclauses)

6910 return self

6911

6912 @_generative

6913 def correlate_except(

6914 self,

6915 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6916 ) -> Self:

6917 r"""Return a new :class:`_expression.ScalarSelect`

6918 which will omit the given FROM

6919 clauses from the auto-correlation process.

6920

6921 This method is mirrored from the

6922 :meth:`_sql.Select.correlate_except` method of the underlying

6923 :class:`_sql.Select`. The method applies the

6924 :meth:_sql.Select.correlate_except` method, then returns a new

6925 :class:`_sql.ScalarSelect` against that statement.

6926

6927 .. versionadded:: 1.4 Previously, the

6928 :meth:`_sql.ScalarSelect.correlate_except`

6929 method was only available from :class:`_sql.Select`.

6930

6931 :param \*fromclauses: a list of one or more

6932 :class:`_expression.FromClause`

6933 constructs, or other compatible constructs (i.e. ORM-mapped

6934 classes) to become part of the correlate-exception collection.

6935

6936 .. seealso::

6937

6938 :meth:`_expression.ScalarSelect.correlate`

6939

6940 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6941

6942

6943 """

6944

6945 self.element = cast(

6946 "Select[Unpack[TupleAny]]", self.element

6947 ).correlate_except(*fromclauses)

6948 return self

6949

6950

6951class Exists(UnaryExpression[bool]):

6952 """Represent an ``EXISTS`` clause.

6953

6954 See :func:`_sql.exists` for a description of usage.

6955

6956 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6957 instance by calling :meth:`_sql.SelectBase.exists`.

6958

6959 """

6960

6961 inherit_cache = True

6962

6963 def __init__(

6964 self,

6965 __argument: Optional[

6966 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6967 ] = None,

6968 /,

6969 ):

6970 s: ScalarSelect[Any]

6971

6972 # TODO: this seems like we should be using coercions for this

6973 if __argument is None:

6974 s = Select(literal_column("*")).scalar_subquery()

6975 elif isinstance(__argument, SelectBase):

6976 s = __argument.scalar_subquery()

6977 s._propagate_attrs = __argument._propagate_attrs

6978 elif isinstance(__argument, ScalarSelect):

6979 s = __argument

6980 else:

6981 s = Select(__argument).scalar_subquery()

6982

6983 UnaryExpression.__init__(

6984 self,

6985 s,

6986 operator=operators.exists,

6987 type_=type_api.BOOLEANTYPE,

6988 )

6989

6990 @util.ro_non_memoized_property

6991 def _from_objects(self) -> List[FromClause]:

6992 return []

6993

6994 def _regroup(

6995 self,

6996 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

6997 ) -> ScalarSelect[Any]:

6998

6999 assert isinstance(self.element, ScalarSelect)

7000 element = self.element.element

7001 if not isinstance(element, Select):

7002 raise exc.InvalidRequestError(

7003 "Can only apply this operation to a plain SELECT construct"

7004 )

7005 new_element = fn(element)

7006

7007 return_value = new_element.scalar_subquery()

7008 return return_value

7009

7010 def select(self) -> Select[bool]:

7011 r"""Return a SELECT of this :class:`_expression.Exists`.

7012

7013 e.g.::

7014

7015 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7016

7017 This will produce a statement resembling:

7018

7019 .. sourcecode:: sql

7020

7021 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7022

7023 .. seealso::

7024

7025 :func:`_expression.select` - general purpose

7026 method which allows for arbitrary column lists.

7027

7028 """ # noqa

7029

7030 return Select(self)

7031

7032 def correlate(

7033 self,

7034 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7035 ) -> Self:

7036 """Apply correlation to the subquery noted by this

7037 :class:`_sql.Exists`.

7038

7039 .. seealso::

7040

7041 :meth:`_sql.ScalarSelect.correlate`

7042

7043 """

7044 e = self._clone()

7045 e.element = self._regroup(

7046 lambda element: element.correlate(*fromclauses)

7047 )

7048 return e

7049

7050 def correlate_except(

7051 self,

7052 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7053 ) -> Self:

7054 """Apply correlation to the subquery noted by this

7055 :class:`_sql.Exists`.

7056

7057 .. seealso::

7058

7059 :meth:`_sql.ScalarSelect.correlate_except`

7060

7061 """

7062 e = self._clone()

7063 e.element = self._regroup(

7064 lambda element: element.correlate_except(*fromclauses)

7065 )

7066 return e

7067

7068 def select_from(self, *froms: _FromClauseArgument) -> Self:

7069 """Return a new :class:`_expression.Exists` construct,

7070 applying the given

7071 expression to the :meth:`_expression.Select.select_from`

7072 method of the select

7073 statement contained.

7074

7075 .. note:: it is typically preferable to build a :class:`_sql.Select`

7076 statement first, including the desired WHERE clause, then use the

7077 :meth:`_sql.SelectBase.exists` method to produce an

7078 :class:`_sql.Exists` object at once.

7079

7080 """

7081 e = self._clone()

7082 e.element = self._regroup(lambda element: element.select_from(*froms))

7083 return e

7084

7085 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7086 """Return a new :func:`_expression.exists` construct with the

7087 given expression added to

7088 its WHERE clause, joined to the existing clause via AND, if any.

7089

7090

7091 .. note:: it is typically preferable to build a :class:`_sql.Select`

7092 statement first, including the desired WHERE clause, then use the

7093 :meth:`_sql.SelectBase.exists` method to produce an

7094 :class:`_sql.Exists` object at once.

7095

7096 """

7097 e = self._clone()

7098 e.element = self._regroup(lambda element: element.where(*clause))

7099 return e

7100

7101

7102class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7103 """Wrap a :class:`_expression.TextClause` construct within a

7104 :class:`_expression.SelectBase`

7105 interface.

7106

7107 This allows the :class:`_expression.TextClause` object to gain a

7108 ``.c`` collection

7109 and other FROM-like capabilities such as

7110 :meth:`_expression.FromClause.alias`,

7111 :meth:`_expression.SelectBase.cte`, etc.

7112

7113 The :class:`_expression.TextualSelect` construct is produced via the

7114 :meth:`_expression.TextClause.columns`

7115 method - see that method for details.

7116

7117 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7118 class was renamed

7119 from ``TextAsFrom``, to more correctly suit its role as a

7120 SELECT-oriented object and not a FROM clause.

7121

7122 .. seealso::

7123

7124 :func:`_expression.text`

7125

7126 :meth:`_expression.TextClause.columns` - primary creation interface.

7127

7128 """

7129

7130 __visit_name__ = "textual_select"

7131

7132 _label_style = LABEL_STYLE_NONE

7133

7134 _traverse_internals: _TraverseInternalsType = (

7135 [

7136 ("element", InternalTraversal.dp_clauseelement),

7137 ("column_args", InternalTraversal.dp_clauseelement_list),

7138 ]

7139 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7140 + HasCTE._has_ctes_traverse_internals

7141 )

7142

7143 _is_textual = True

7144

7145 is_text = True

7146 is_select = True

7147

7148 def __init__(

7149 self,

7150 text: TextClause,

7151 columns: List[_ColumnExpressionArgument[Any]],

7152 positional: bool = False,

7153 ) -> None:

7154 self._init(

7155 text,

7156 # convert for ORM attributes->columns, etc

7157 [

7158 coercions.expect(roles.LabeledColumnExprRole, c)

7159 for c in columns

7160 ],

7161 positional,

7162 )

7163

7164 def _init(

7165 self,

7166 text: TextClause,

7167 columns: List[NamedColumn[Any]],

7168 positional: bool = False,

7169 ) -> None:

7170 self.element = text

7171 self.column_args = columns

7172 self.positional = positional

7173

7174 @HasMemoized_ro_memoized_attribute

7175 def selected_columns(

7176 self,

7177 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7178 """A :class:`_expression.ColumnCollection`

7179 representing the columns that

7180 this SELECT statement or similar construct returns in its result set,

7181 not including :class:`_sql.TextClause` constructs.

7182

7183 This collection differs from the :attr:`_expression.FromClause.columns`

7184 collection of a :class:`_expression.FromClause` in that the columns

7185 within this collection cannot be directly nested inside another SELECT

7186 statement; a subquery must be applied first which provides for the

7187 necessary parenthesization required by SQL.

7188

7189 For a :class:`_expression.TextualSelect` construct, the collection

7190 contains the :class:`_expression.ColumnElement` objects that were

7191 passed to the constructor, typically via the

7192 :meth:`_expression.TextClause.columns` method.

7193

7194

7195 .. versionadded:: 1.4

7196

7197 """

7198 return ColumnCollection(

7199 (c.key, c) for c in self.column_args

7200 ).as_readonly()

7201

7202 @util.ro_non_memoized_property

7203 def _all_selected_columns(self) -> _SelectIterable:

7204 return self.column_args

7205

7206 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7207 return self

7208

7209 def _ensure_disambiguated_names(self) -> TextualSelect:

7210 return self

7211

7212 @_generative

7213 def bindparams(

7214 self,

7215 *binds: BindParameter[Any],

7216 **bind_as_values: Any,

7217 ) -> Self:

7218 self.element = self.element.bindparams(*binds, **bind_as_values)

7219 return self

7220

7221 def _generate_fromclause_column_proxies(

7222 self,

7223 fromclause: FromClause,

7224 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7225 primary_key: ColumnSet,

7226 foreign_keys: Set[KeyedColumnElement[Any]],

7227 *,

7228 proxy_compound_columns: Optional[

7229 Iterable[Sequence[ColumnElement[Any]]]

7230 ] = None,

7231 ) -> None:

7232 if TYPE_CHECKING:

7233 assert isinstance(fromclause, Subquery)

7234

7235 if proxy_compound_columns:

7236 columns._populate_separate_keys(

7237 c._make_proxy(

7238 fromclause,

7239 compound_select_cols=extra_cols,

7240 primary_key=primary_key,

7241 foreign_keys=foreign_keys,

7242 )

7243 for c, extra_cols in zip(

7244 self.column_args, proxy_compound_columns

7245 )

7246 )

7247 else:

7248 columns._populate_separate_keys(

7249 c._make_proxy(

7250 fromclause,

7251 primary_key=primary_key,

7252 foreign_keys=foreign_keys,

7253 )

7254 for c in self.column_args

7255 )

7256

7257 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7258 return self.column_args[0].type

7259

7260

7261TextAsFrom = TextualSelect

7262"""Backwards compatibility with the previous name"""

7263

7264

7265class AnnotatedFromClause(Annotated):

7266 def _copy_internals(self, **kw: Any) -> None:

7267 super()._copy_internals(**kw)

7268 if kw.get("ind_cols_on_fromclause", False):

7269 ee = self._Annotated__element # type: ignore

7270

7271 self.c = ee.__class__.c.fget(self) # type: ignore

7272

7273 @util.ro_memoized_property

7274 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7275 """proxy the .c collection of the underlying FromClause.

7276

7277 Originally implemented in 2008 as a simple load of the .c collection

7278 when the annotated construct was created (see d3621ae961a), in modern

7279 SQLAlchemy versions this can be expensive for statements constructed

7280 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7281 it, which works just as well.

7282

7283 Two different use cases seem to require the collection either copied

7284 from the underlying one, or unique to this AnnotatedFromClause.

7285

7286 See test_selectable->test_annotated_corresponding_column

7287

7288 """

7289 ee = self._Annotated__element # type: ignore

7290 return ee.c # type: ignore