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(

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: HasCTE,

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, as well as tokens used to populate the .c. collection

2362 on a :class:`.FromClause`.

2363

2364 This is distinct from the _column_naming_convention generator that's

2365 intended for population of the Select.selected_columns collection,

2366 different rules. the collection returned here calls upon the

2367 _column_naming_convention as well.

2368

2369 """

2370

2371 if cols is None:

2372 cols = self._all_selected_columns

2373

2374 key_naming_convention = SelectState._column_naming_convention(

2375 self._label_style

2376 )

2377

2378 names = {}

2379

2380 result: List[_ColumnsPlusNames] = []

2381 result_append = result.append

2382

2383 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2384 label_style_none = self._label_style is LABEL_STYLE_NONE

2385

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

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

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

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

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

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

2392 dedupe_hash = 1

2393

2394 for c in cols:

2395 repeated = False

2396

2397 if not c._render_label_in_columns_clause:

2398 effective_name = required_label_name = fallback_label_name = (

2399 None

2400 )

2401 elif label_style_none:

2402 if TYPE_CHECKING:

2403 assert is_column_element(c)

2404

2405 effective_name = required_label_name = None

2406 fallback_label_name = c._non_anon_label or c._anon_name_label

2407 else:

2408 if TYPE_CHECKING:

2409 assert is_column_element(c)

2410

2411 if table_qualified:

2412 required_label_name = effective_name = (

2413 fallback_label_name

2414 ) = c._tq_label

2415 else:

2416 effective_name = fallback_label_name = c._non_anon_label

2417 required_label_name = None

2418

2419 if effective_name is None:

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

2421 # not need _expression_label but it isn't

2422 # giving us a clue when to use anon_label instead

2423 expr_label = c._expression_label

2424 if expr_label is None:

2425 repeated = c._anon_name_label in names

2426 names[c._anon_name_label] = c

2427 effective_name = required_label_name = None

2428

2429 if repeated:

2430 # here, "required_label_name" is sent as

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

2432 if table_qualified:

2433 fallback_label_name = (

2434 c._dedupe_anon_tq_label_idx(dedupe_hash)

2435 )

2436 dedupe_hash += 1

2437 else:

2438 fallback_label_name = c._dedupe_anon_label_idx(

2439 dedupe_hash

2440 )

2441 dedupe_hash += 1

2442 else:

2443 fallback_label_name = c._anon_name_label

2444 else:

2445 required_label_name = effective_name = (

2446 fallback_label_name

2447 ) = expr_label

2448

2449 if effective_name is not None:

2450 if TYPE_CHECKING:

2451 assert is_column_element(c)

2452

2453 if effective_name in names:

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

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

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

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

2458 # different column under the same name. apply

2459 # disambiguating label

2460 if table_qualified:

2461 required_label_name = fallback_label_name = (

2462 c._anon_tq_label

2463 )

2464 else:

2465 required_label_name = fallback_label_name = (

2466 c._anon_name_label

2467 )

2468

2469 if anon_for_dupe_key and required_label_name in names:

2470 # here, c._anon_tq_label is definitely unique to

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

2472 # this should always be true.

2473 # this is also an infrequent codepath because

2474 # you need two levels of duplication to be here

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

2476

2477 # the column under the disambiguating label is

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

2479 # subsequent occurrences of the column so that the

2480 # original stays non-ambiguous

2481 if table_qualified:

2482 required_label_name = fallback_label_name = (

2483 c._dedupe_anon_tq_label_idx(dedupe_hash)

2484 )

2485 dedupe_hash += 1

2486 else:

2487 required_label_name = fallback_label_name = (

2488 c._dedupe_anon_label_idx(dedupe_hash)

2489 )

2490 dedupe_hash += 1

2491 repeated = True

2492 else:

2493 names[required_label_name] = c

2494 elif anon_for_dupe_key:

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

2496 # label so that the original stays non-ambiguous

2497 if table_qualified:

2498 required_label_name = fallback_label_name = (

2499 c._dedupe_anon_tq_label_idx(dedupe_hash)

2500 )

2501 dedupe_hash += 1

2502 else:

2503 required_label_name = fallback_label_name = (

2504 c._dedupe_anon_label_idx(dedupe_hash)

2505 )

2506 dedupe_hash += 1

2507 repeated = True

2508 else:

2509 names[effective_name] = c

2510

2511 result_append(

2512 _ColumnsPlusNames(

2513 required_label_name,

2514 key_naming_convention(c),

2515 fallback_label_name,

2516 c,

2517 repeated,

2518 )

2519 )

2520

2521 return result

2522

2523

2524class HasCTE(roles.HasCTERole, SelectsRows):

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

2526

2527 _has_ctes_traverse_internals: _TraverseInternalsType = [

2528 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2529 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2530 ]

2531

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

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

2534

2535 name_cte_columns: bool = False

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

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

2538

2539 .. versionadded:: 2.0.42

2540

2541 """

2542

2543 @_generative

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

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

2546

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

2548 the parent statement such that they will each be unconditionally

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

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

2551

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

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

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

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

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

2557 statement.

2558

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

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

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

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

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

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

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

2566 larger statement.

2567

2568 E.g.::

2569

2570 from sqlalchemy import table, column, select

2571

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

2573

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

2575

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

2577

2578 Would render:

2579

2580 .. sourcecode:: sql

2581

2582 WITH anon_1 AS (

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

2584 )

2585 SELECT t.c1, t.c2

2586 FROM t

2587

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

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

2590 statement.

2591

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

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

2594

2595 from sqlalchemy import table, column

2596 from sqlalchemy.dialects.postgresql import insert

2597

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

2599

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

2601

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

2603 update_statement = insert_stmt.on_conflict_do_update(

2604 index_elements=[t.c.c1],

2605 set_={

2606 "c1": insert_stmt.excluded.c1,

2607 "c2": insert_stmt.excluded.c2,

2608 },

2609 ).add_cte(delete_statement_cte)

2610

2611 print(update_statement)

2612

2613 The above statement renders as:

2614

2615 .. sourcecode:: sql

2616

2617 WITH deletions AS (

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

2619 )

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

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

2622

2623 .. versionadded:: 1.4.21

2624

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

2626

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

2628

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

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

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

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

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

2634 this statement when this flag is given.

2635

2636 .. versionadded:: 2.0

2637

2638 .. seealso::

2639

2640 :paramref:`.HasCTE.cte.nesting`

2641

2642

2643 """ # noqa: E501

2644 opt = _CTEOpts(nest_here)

2645 for cte in ctes:

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

2647 self._independent_ctes += (cte,)

2648 self._independent_ctes_opts += (opt,)

2649 return self

2650

2651 def cte(

2652 self,

2653 name: Optional[str] = None,

2654 recursive: bool = False,

2655 nesting: bool = False,

2656 ) -> CTE:

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

2658 or Common Table Expression instance.

2659

2660 Common table expressions are a SQL standard whereby SELECT

2661 statements can draw upon secondary statements specified along

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

2663 Special semantics regarding UNION can also be employed to

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

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

2666

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

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

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

2670 CTE rows.

2671

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

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

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

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

2676

2677 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2679 method may be

2680 used to establish these.

2681

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

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

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

2685 compile time.

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

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

2688 conjunction with UNION ALL in order to derive rows

2689 from those already selected.

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

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

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

2693 :paramref:`.HasCTE.add_cte.nest_here`

2694 parameter may also be used to more carefully

2695 control the exact placement of a particular CTE.

2696

2697 .. versionadded:: 1.4.24

2698

2699 .. seealso::

2700

2701 :meth:`.HasCTE.add_cte`

2702

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

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

2705 as well as additional examples.

2706

2707 Example 1, non recursive::

2708

2709 from sqlalchemy import (

2710 Table,

2711 Column,

2712 String,

2713 Integer,

2714 MetaData,

2715 select,

2716 func,

2717 )

2718

2719 metadata = MetaData()

2720

2721 orders = Table(

2722 "orders",

2723 metadata,

2724 Column("region", String),

2725 Column("amount", Integer),

2726 Column("product", String),

2727 Column("quantity", Integer),

2728 )

2729

2730 regional_sales = (

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

2732 .group_by(orders.c.region)

2733 .cte("regional_sales")

2734 )

2735

2736

2737 top_regions = (

2738 select(regional_sales.c.region)

2739 .where(

2740 regional_sales.c.total_sales

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

2742 )

2743 .cte("top_regions")

2744 )

2745

2746 statement = (

2747 select(

2748 orders.c.region,

2749 orders.c.product,

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

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

2752 )

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

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

2755 )

2756

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

2758

2759 Example 2, WITH RECURSIVE::

2760

2761 from sqlalchemy import (

2762 Table,

2763 Column,

2764 String,

2765 Integer,

2766 MetaData,

2767 select,

2768 func,

2769 )

2770

2771 metadata = MetaData()

2772

2773 parts = Table(

2774 "parts",

2775 metadata,

2776 Column("part", String),

2777 Column("sub_part", String),

2778 Column("quantity", Integer),

2779 )

2780

2781 included_parts = (

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

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

2784 .cte(recursive=True)

2785 )

2786

2787

2788 incl_alias = included_parts.alias()

2789 parts_alias = parts.alias()

2790 included_parts = included_parts.union_all(

2791 select(

2792 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2794 )

2795

2796 statement = select(

2797 included_parts.c.sub_part,

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

2799 ).group_by(included_parts.c.sub_part)

2800

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

2802

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

2804

2805 from datetime import date

2806 from sqlalchemy import (

2807 MetaData,

2808 Table,

2809 Column,

2810 Integer,

2811 Date,

2812 select,

2813 literal,

2814 and_,

2815 exists,

2816 )

2817

2818 metadata = MetaData()

2819

2820 visitors = Table(

2821 "visitors",

2822 metadata,

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

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

2825 Column("count", Integer),

2826 )

2827

2828 # add 5 visitors for the product_id == 1

2829 product_id = 1

2830 day = date.today()

2831 count = 5

2832

2833 update_cte = (

2834 visitors.update()

2835 .where(

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

2837 )

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

2839 .returning(literal(1))

2840 .cte("update_cte")

2841 )

2842

2843 upsert = visitors.insert().from_select(

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

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

2846 ~exists(update_cte.select())

2847 ),

2848 )

2849

2850 connection.execute(upsert)

2851

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

2853

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

2855

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

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

2858 "value_a", nesting=True

2859 )

2860

2861 # Nesting CTEs takes ascendency locally

2862 # over the CTEs at a higher level

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

2864

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

2866

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

2868 shown with inline parameters below as:

2869

2870 .. sourcecode:: sql

2871

2872 WITH

2873 value_a AS

2874 (SELECT 'root' AS n),

2875 value_b AS

2876 (WITH value_a AS

2877 (SELECT 'nesting' AS n)

2878 SELECT value_a.n AS n FROM value_a)

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

2880 FROM value_a, value_b

2881

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

2883 as follows (SQLAlchemy 2.0 and above)::

2884

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

2886

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

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

2889

2890 # Nesting CTEs takes ascendency locally

2891 # over the CTEs at a higher level

2892 value_b = (

2893 select(value_a_nested.c.n)

2894 .add_cte(value_a_nested, nest_here=True)

2895 .cte("value_b")

2896 )

2897

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

2899

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

2901

2902 edge = Table(

2903 "edge",

2904 metadata,

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

2906 Column("left", Integer),

2907 Column("right", Integer),

2908 )

2909

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

2911

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

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

2914 )

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

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

2917 )

2918

2919 subgraph_cte = root_node.union(left_edge, right_edge)

2920

2921 subgraph = select(subgraph_cte)

2922

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

2924

2925 .. sourcecode:: sql

2926

2927 WITH RECURSIVE nodes(node) AS (

2928 SELECT 1 AS node

2929 UNION

2930 SELECT edge."left" AS "left"

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

2932 UNION

2933 SELECT edge."right" AS "right"

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

2935 )

2936 SELECT nodes.node FROM nodes

2937

2938 .. seealso::

2939

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

2941 :meth:`_expression.HasCTE.cte`.

2942

2943 """ # noqa: E501

2944 return CTE._construct(

2945 self, name=name, recursive=recursive, nesting=nesting

2946 )

2947

2948

2949class Subquery(AliasedReturnsRows):

2950 """Represent a subquery of a SELECT.

2951

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

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

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

2955 :class:`_expression.SelectBase` subclass

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

2957 :class:`_expression.CompoundSelect`, and

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

2959 it represents the

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

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

2962

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

2964 :class:`_expression.Alias`

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

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

2967 :class:`_expression.Alias` always

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

2969 :class:`.Subquery`

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

2971

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

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

2974 statement.

2975

2976 """

2977

2978 __visit_name__ = "subquery"

2979

2980 _is_subquery = True

2981

2982 inherit_cache = True

2983

2984 element: SelectBase

2985

2986 @classmethod

2987 def _factory(

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

2989 ) -> Subquery:

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

2991

2992 return coercions.expect(

2993 roles.SelectStatementRole, selectable

2994 ).subquery(name=name)

2995

2996 @util.deprecated(

2997 "1.4",

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

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

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

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

3002 ":func:`_expression.select` "

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

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

3005 )

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

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

3008

3009

3010class FromGrouping(GroupedElement, FromClause):

3011 """Represent a grouping of a FROM clause"""

3012

3013 _traverse_internals: _TraverseInternalsType = [

3014 ("element", InternalTraversal.dp_clauseelement)

3015 ]

3016

3017 element: FromClause

3018

3019 def __init__(self, element: FromClause):

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

3021

3022 @util.ro_non_memoized_property

3023 def columns(

3024 self,

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

3026 return self.element.columns

3027

3028 @util.ro_non_memoized_property

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

3030 return self.element.columns

3031

3032 @property

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

3034 return self.element.primary_key

3035

3036 @property

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

3038 return self.element.foreign_keys

3039

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

3041 return self.element.is_derived_from(fromclause)

3042

3043 def alias(

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

3045 ) -> NamedFromGrouping:

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

3047

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

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

3050

3051 @util.ro_non_memoized_property

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

3053 return self.element._hide_froms

3054

3055 @util.ro_non_memoized_property

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

3057 return self.element._from_objects

3058

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

3060 return {"element": self.element}

3061

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

3063 self.element = state["element"]

3064

3065 if TYPE_CHECKING:

3066

3067 def self_group(

3068 self, against: Optional[OperatorType] = None

3069 ) -> Self: ...

3070

3071

3072class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3074

3075 .. versionadded:: 2.0

3076

3077 """

3078

3079 inherit_cache = True

3080

3081 if TYPE_CHECKING:

3082

3083 def self_group(

3084 self, against: Optional[OperatorType] = None

3085 ) -> Self: ...

3086

3087

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

3089 """Represents a minimal "table" construct.

3090

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

3092 collection of columns, which are typically produced

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

3094

3095 from sqlalchemy import table, column

3096

3097 user = table(

3098 "user",

3099 column("id"),

3100 column("name"),

3101 column("description"),

3102 )

3103

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

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

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

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

3108

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

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

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

3112 It's useful

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

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

3115 is not on hand.

3116

3117 """

3118

3119 __visit_name__ = "table"

3120

3121 _traverse_internals: _TraverseInternalsType = [

3122 (

3123 "columns",

3124 InternalTraversal.dp_fromclause_canonical_column_collection,

3125 ),

3126 ("name", InternalTraversal.dp_string),

3127 ("schema", InternalTraversal.dp_string),

3128 ]

3129

3130 _is_table = True

3131

3132 fullname: str

3133

3134 implicit_returning = False

3135 """:class:`_expression.TableClause`

3136 doesn't support having a primary key or column

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

3138

3139 _columns: DedupeColumnCollection[ColumnClause[Any]]

3140

3141 @util.ro_memoized_property

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

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

3144 return None

3145

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

3147 super().__init__()

3148 self.name = name

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

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

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

3152 for c in columns:

3153 self.append_column(c)

3154

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

3156 if schema is not None:

3157 self.schema = schema

3158 if self.schema is not None:

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

3160 else:

3161 self.fullname = self.name

3162 if kw:

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

3164

3165 if TYPE_CHECKING:

3166

3167 @util.ro_non_memoized_property

3168 def columns(

3169 self,

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

3171

3172 @util.ro_non_memoized_property

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

3174

3175 def __str__(self) -> str:

3176 if self.schema is not None:

3177 return self.schema + "." + self.name

3178 else:

3179 return self.name

3180

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

3182 pass

3183

3184 @util.ro_memoized_property

3185 def description(self) -> str:

3186 return self.name

3187

3188 def _insert_col_impl(

3189 self,

3190 c: ColumnClause[Any],

3191 *,

3192 index: Optional[int] = None,

3193 ) -> None:

3194 existing = c.table

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

3196 raise exc.ArgumentError(

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

3198 % (c.key, existing)

3199 )

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

3201 c.table = self

3202

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

3204 self._insert_col_impl(c)

3205

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

3207 self._insert_col_impl(c, index=index)

3208

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

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

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

3212 :class:`_expression.TableClause`.

3213

3214 E.g.::

3215

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

3217

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

3219

3220 """

3221

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

3223

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

3225 def update(self) -> Update:

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

3227 :class:`_expression.TableClause`.

3228

3229 E.g.::

3230

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

3232

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

3234

3235 """

3236 return util.preloaded.sql_dml.Update(

3237 self,

3238 )

3239

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

3241 def delete(self) -> Delete:

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

3243 :class:`_expression.TableClause`.

3244

3245 E.g.::

3246

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

3248

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

3250

3251 """

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

3253

3254 @util.ro_non_memoized_property

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

3256 return [self]

3257

3258

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

3260

3261

3262class ForUpdateArg(ClauseElement):

3263 _traverse_internals: _TraverseInternalsType = [

3264 ("of", InternalTraversal.dp_clauseelement_list),

3265 ("nowait", InternalTraversal.dp_boolean),

3266 ("read", InternalTraversal.dp_boolean),

3267 ("skip_locked", InternalTraversal.dp_boolean),

3268 ("key_share", InternalTraversal.dp_boolean),

3269 ]

3270

3271 of: Optional[Sequence[ClauseElement]]

3272 nowait: bool

3273 read: bool

3274 skip_locked: bool

3275

3276 @classmethod

3277 def _from_argument(

3278 cls, with_for_update: ForUpdateParameter

3279 ) -> Optional[ForUpdateArg]:

3280 if isinstance(with_for_update, ForUpdateArg):

3281 return with_for_update

3282 elif with_for_update in (None, False):

3283 return None

3284 elif with_for_update is True:

3285 return ForUpdateArg()

3286 else:

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

3288

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

3290 return (

3291 isinstance(other, ForUpdateArg)

3292 and other.nowait == self.nowait

3293 and other.read == self.read

3294 and other.skip_locked == self.skip_locked

3295 and other.key_share == self.key_share

3296 and other.of is self.of

3297 )

3298

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

3300 return not self.__eq__(other)

3301

3302 def __hash__(self) -> int:

3303 return id(self)

3304

3305 def __init__(

3306 self,

3307 *,

3308 nowait: bool = False,

3309 read: bool = False,

3310 of: Optional[_ForUpdateOfArgument] = None,

3311 skip_locked: bool = False,

3312 key_share: bool = False,

3313 ):

3314 """Represents arguments specified to

3315 :meth:`_expression.Select.for_update`.

3316

3317 """

3318

3319 self.nowait = nowait

3320 self.read = read

3321 self.skip_locked = skip_locked

3322 self.key_share = key_share

3323 if of is not None:

3324 self.of = [

3325 coercions.expect(roles.ColumnsClauseRole, elem)

3326 for elem in util.to_list(of)

3327 ]

3328 else:

3329 self.of = None

3330

3331

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

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

3334 in a statement.

3335

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

3337 :func:`_expression.values` function.

3338

3339 .. versionadded:: 1.4

3340

3341 """

3342

3343 __visit_name__ = "values"

3344

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

3346

3347 _unnamed: bool

3348 _traverse_internals: _TraverseInternalsType = [

3349 ("_column_args", InternalTraversal.dp_clauseelement_list),

3350 ("_data", InternalTraversal.dp_dml_multi_values),

3351 ("name", InternalTraversal.dp_string),

3352 ("literal_binds", InternalTraversal.dp_boolean),

3353 ] + HasCTE._has_ctes_traverse_internals

3354

3355 name_cte_columns = True

3356

3357 def __init__(

3358 self,

3359 *columns: ColumnClause[Any],

3360 name: Optional[str] = None,

3361 literal_binds: bool = False,

3362 ):

3363 super().__init__()

3364 self._column_args = columns

3365

3366 if name is None:

3367 self._unnamed = True

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

3369 else:

3370 self._unnamed = False

3371 self.name = name

3372 self.literal_binds = literal_binds

3373 self.named_with_column = not self._unnamed

3374

3375 @property

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

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

3378

3379 @util.ro_non_memoized_property

3380 def _all_selected_columns(self) -> _SelectIterable:

3381 return self._column_args

3382

3383 @_generative

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

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

3386 construct that is a copy of this

3387 one with the given name.

3388

3389 This method is a VALUES-specific specialization of the

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

3391

3392 .. seealso::

3393

3394 :ref:`tutorial_using_aliases`

3395

3396 :func:`_expression.alias`

3397

3398 """

3399 non_none_name: str

3400

3401 if name is None:

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

3403 else:

3404 non_none_name = name

3405

3406 self.name = non_none_name

3407 self.named_with_column = True

3408 self._unnamed = False

3409 return self

3410

3411 @_generative

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

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

3414 so that

3415 it renders as LATERAL.

3416

3417 .. seealso::

3418

3419 :func:`_expression.lateral`

3420

3421 """

3422 non_none_name: str

3423

3424 if name is None:

3425 non_none_name = self.name

3426 else:

3427 non_none_name = name

3428

3429 self._is_lateral = True

3430 self.name = non_none_name

3431 self._unnamed = False

3432 return self

3433

3434 @_generative

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

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

3437 adding the given data to the data list.

3438

3439 E.g.::

3440

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

3442

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

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

3445 constructor.

3446

3447 """

3448

3449 self._data += (values,)

3450 return self

3451

3452 def scalar_values(self) -> ScalarValues:

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

3454 COLUMN element in a statement.

3455

3456 .. versionadded:: 2.0.0b4

3457

3458 """

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

3460

3461 def _populate_column_collection(

3462 self,

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

3464 primary_key: ColumnSet,

3465 foreign_keys: Set[KeyedColumnElement[Any]],

3466 ) -> None:

3467 for c in self._column_args:

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

3469 _, c = c._make_proxy(

3470 self, primary_key=primary_key, foreign_keys=foreign_keys

3471 )

3472 else:

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

3474 # no memoizations of other FROM clauses.

3475 # see test_values.py -> test_auto_proxy_select_direct_col

3476 c._reset_memoizations()

3477 columns.add(c)

3478 c.table = self

3479

3480 @util.ro_non_memoized_property

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

3482 return [self]

3483

3484

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

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

3487 COLUMN element in a statement.

3488

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

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

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

3492 an ``IN`` or ``NOT IN`` condition.

3493

3494 .. versionadded:: 2.0.0b4

3495

3496 """

3497

3498 __visit_name__ = "scalar_values"

3499

3500 _traverse_internals: _TraverseInternalsType = [

3501 ("_column_args", InternalTraversal.dp_clauseelement_list),

3502 ("_data", InternalTraversal.dp_dml_multi_values),

3503 ("literal_binds", InternalTraversal.dp_boolean),

3504 ]

3505

3506 def __init__(

3507 self,

3508 columns: Sequence[ColumnClause[Any]],

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

3510 literal_binds: bool,

3511 ):

3512 super().__init__()

3513 self._column_args = columns

3514 self._data = data

3515 self.literal_binds = literal_binds

3516

3517 @property

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

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

3520

3521 def __clause_element__(self) -> ScalarValues:

3522 return self

3523

3524 if TYPE_CHECKING:

3525

3526 def self_group(

3527 self, against: Optional[OperatorType] = None

3528 ) -> Self: ...

3529

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

3531

3532

3533class SelectBase(

3534 roles.SelectStatementRole,

3535 roles.DMLSelectRole,

3536 roles.CompoundElementRole,

3537 roles.InElementRole,

3538 HasCTE,

3539 SupportsCloneAnnotations,

3540 Selectable,

3541):

3542 """Base class for SELECT statements.

3543

3544

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

3546 :class:`_expression.CompoundSelect` and

3547 :class:`_expression.TextualSelect`.

3548

3549

3550 """

3551

3552 _is_select_base = True

3553 is_select = True

3554

3555 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3556

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

3558 self._reset_memoizations()

3559

3560 @util.ro_non_memoized_property

3561 def selected_columns(

3562 self,

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

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

3565 representing the columns that

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

3567

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

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

3570 within this collection cannot be directly nested inside another SELECT

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

3572 necessary parenthesization required by SQL.

3573

3574 .. note::

3575

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

3577 include expressions established in the columns clause using the

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

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

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

3581 construct.

3582

3583 .. seealso::

3584

3585 :attr:`_sql.Select.selected_columns`

3586

3587 .. versionadded:: 1.4

3588

3589 """

3590 raise NotImplementedError()

3591

3592 def _generate_fromclause_column_proxies(

3593 self,

3594 subquery: FromClause,

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

3596 primary_key: ColumnSet,

3597 foreign_keys: Set[KeyedColumnElement[Any]],

3598 *,

3599 proxy_compound_columns: Optional[

3600 Iterable[Sequence[ColumnElement[Any]]]

3601 ] = None,

3602 ) -> None:

3603 raise NotImplementedError()

3604

3605 @util.ro_non_memoized_property

3606 def _all_selected_columns(self) -> _SelectIterable:

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

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

3609 constructs.

3610

3611 .. versionadded:: 1.4.12

3612

3613 .. seealso::

3614

3615 :attr:`_sql.SelectBase.exported_columns`

3616

3617 """

3618 raise NotImplementedError()

3619

3620 @property

3621 def exported_columns(

3622 self,

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

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

3625 that represents the "exported"

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

3627 :class:`_sql.TextClause` constructs.

3628

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

3630 object are synonymous

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

3632

3633 .. versionadded:: 1.4

3634

3635 .. seealso::

3636

3637 :attr:`_expression.Select.exported_columns`

3638

3639 :attr:`_expression.Selectable.exported_columns`

3640

3641 :attr:`_expression.FromClause.exported_columns`

3642

3643

3644 """

3645 return self.selected_columns.as_readonly()

3646

3647 def get_label_style(self) -> SelectLabelStyle:

3648 """

3649 Retrieve the current label style.

3650

3651 Implemented by subclasses.

3652

3653 """

3654 raise NotImplementedError()

3655

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

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

3658

3659 Implemented by subclasses.

3660

3661 """

3662

3663 raise NotImplementedError()

3664

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

3666 raise NotImplementedError()

3667

3668 @util.deprecated(

3669 "1.4",

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

3671 "method is deprecated and will be "

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

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

3674 )

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

3676 return self.scalar_subquery()

3677

3678 def exists(self) -> Exists:

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

3680 which can be used as a column expression.

3681

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

3683

3684 .. seealso::

3685

3686 :func:`_sql.exists`

3687

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

3689

3690 .. versionadded:: 1.4

3691

3692 """

3693 return Exists(self)

3694

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

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

3697 used as a column expression.

3698

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

3700

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

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

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

3704 an enclosing SELECT.

3705

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

3707 subquery that can be produced using the

3708 :meth:`_expression.SelectBase.subquery`

3709 method.

3710

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

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

3713

3714 .. seealso::

3715

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

3717

3718 """

3719 if self._label_style is not LABEL_STYLE_NONE:

3720 self = self.set_label_style(LABEL_STYLE_NONE)

3721

3722 return ScalarSelect(self)

3723

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

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

3726 subquery with a label.

3727

3728 .. seealso::

3729

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

3731

3732 """

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

3734

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

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

3737

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

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

3740

3741 .. seealso::

3742

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

3744

3745 """

3746 return Lateral._factory(self, name)

3747

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

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

3750

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

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

3753 SELECT statement.

3754

3755 Given a SELECT statement such as::

3756

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

3758

3759 The above statement might look like:

3760

3761 .. sourcecode:: sql

3762

3763 SELECT table.id, table.name FROM table

3764

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

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

3767 a named sub-element::

3768

3769 subq = stmt.subquery()

3770 new_stmt = select(subq)

3771

3772 The above renders as:

3773

3774 .. sourcecode:: sql

3775

3776 SELECT anon_1.id, anon_1.name

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

3778

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

3780 is equivalent to calling

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

3782 method on a FROM object; however,

3783 as a :class:`_expression.SelectBase`

3784 object is not directly FROM object,

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

3786 method provides clearer semantics.

3787

3788 .. versionadded:: 1.4

3789

3790 """

3791

3792 return Subquery._construct(

3793 self._ensure_disambiguated_names(), name=name

3794 )

3795

3796 def _ensure_disambiguated_names(self) -> Self:

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

3798 disambiguated in some way, if possible.

3799

3800 """

3801

3802 raise NotImplementedError()

3803

3804 def alias(

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

3806 ) -> Subquery:

3807 """Return a named subquery against this

3808 :class:`_expression.SelectBase`.

3809

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

3811 :class:`_expression.FromClause`),

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

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

3814 :class:`_expression.FromClause`.

3815

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

3817 method is now

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

3819

3820 """

3821 return self.subquery(name=name)

3822

3823

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

3825

3826

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

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

3829

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

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

3832 compound selects.

3833

3834 """

3835

3836 __visit_name__ = "select_statement_grouping"

3837 _traverse_internals: _TraverseInternalsType = [

3838 ("element", InternalTraversal.dp_clauseelement)

3839 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3840

3841 _is_select_container = True

3842

3843 element: _SB

3844

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

3846 self.element = cast(

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

3848 )

3849

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

3851 new_element = self.element._ensure_disambiguated_names()

3852 if new_element is not self.element:

3853 return SelectStatementGrouping(new_element)

3854 else:

3855 return self

3856

3857 def get_label_style(self) -> SelectLabelStyle:

3858 return self.element.get_label_style()

3859

3860 def set_label_style(

3861 self, label_style: SelectLabelStyle

3862 ) -> SelectStatementGrouping[_SB]:

3863 return SelectStatementGrouping(

3864 self.element.set_label_style(label_style)

3865 )

3866

3867 @property

3868 def select_statement(self) -> _SB:

3869 return self.element

3870

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

3872 return self

3873

3874 if TYPE_CHECKING:

3875

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

3877

3878 # def _generate_columns_plus_names(

3879 # self, anon_for_dupe_key: bool

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

3881 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3882

3883 def _generate_fromclause_column_proxies(

3884 self,

3885 subquery: FromClause,

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

3887 primary_key: ColumnSet,

3888 foreign_keys: Set[KeyedColumnElement[Any]],

3889 *,

3890 proxy_compound_columns: Optional[

3891 Iterable[Sequence[ColumnElement[Any]]]

3892 ] = None,

3893 ) -> None:

3894 self.element._generate_fromclause_column_proxies(

3895 subquery,

3896 columns,

3897 proxy_compound_columns=proxy_compound_columns,

3898 primary_key=primary_key,

3899 foreign_keys=foreign_keys,

3900 )

3901

3902 @util.ro_non_memoized_property

3903 def _all_selected_columns(self) -> _SelectIterable:

3904 return self.element._all_selected_columns

3905

3906 @util.ro_non_memoized_property

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

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

3909 representing the columns that

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

3911 :class:`_sql.TextClause` constructs.

3912

3913 .. versionadded:: 1.4

3914

3915 .. seealso::

3916

3917 :attr:`_sql.Select.selected_columns`

3918

3919 """

3920 return self.element.selected_columns

3921

3922 @util.ro_non_memoized_property

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

3924 return self.element._from_objects

3925

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

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

3928 raise NotImplementedError

3929

3930

3931class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

3933 added.

3934

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

3936 :class:`_expression.CompoundSelect`

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

3938 rendering can be controlled. Compare to

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

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

3941 and is also a SELECT construct,

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

3943 only wrapped as a subquery.

3944

3945 """

3946

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

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

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

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

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

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

3953 _for_update_arg: Optional[ForUpdateArg] = None

3954

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

3956 self._label_style = _label_style

3957

3958 @_generative

3959 def with_for_update(

3960 self,

3961 *,

3962 nowait: bool = False,

3963 read: bool = False,

3964 of: Optional[_ForUpdateOfArgument] = None,

3965 skip_locked: bool = False,

3966 key_share: bool = False,

3967 ) -> Self:

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

3969 :class:`_expression.GenerativeSelect`.

3970

3971 E.g.::

3972

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

3974

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

3976 render a statement like:

3977

3978 .. sourcecode:: sql

3979

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

3981

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

3983 would produce:

3984

3985 .. sourcecode:: sql

3986

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

3988

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

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

3991 provided which allow for common database-specific

3992 variants.

3993

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

3995 Database and PostgreSQL dialects.

3996

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

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

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

4000

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

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

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

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

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

4006 table or as a column depending on backend.

4007

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

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

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

4011

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

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

4014 on the PostgreSQL dialect.

4015

4016 """

4017 self._for_update_arg = ForUpdateArg(

4018 nowait=nowait,

4019 read=read,

4020 of=of,

4021 skip_locked=skip_locked,

4022 key_share=key_share,

4023 )

4024 return self

4025

4026 def get_label_style(self) -> SelectLabelStyle:

4027 """

4028 Retrieve the current label style.

4029

4030 .. versionadded:: 1.4

4031

4032 """

4033 return self._label_style

4034

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

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

4037

4038 There are three "label styles" available,

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

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

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

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

4043

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

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

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

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

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

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

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

4051 that the impact of this labeling is minimal.

4052

4053 The rationale for disambiguation is mostly so that all column

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

4055 collection when a subquery is created.

4056

4057 .. versionadded:: 1.4 - the

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

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

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

4061

4062 .. seealso::

4063

4064 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4065

4066 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4067

4068 :data:`_sql.LABEL_STYLE_NONE`

4069

4070 :data:`_sql.LABEL_STYLE_DEFAULT`

4071

4072 """

4073 if self._label_style is not style:

4074 self = self._generate()

4075 self._label_style = style

4076 return self

4077

4078 @property

4079 def _group_by_clause(self) -> ClauseList:

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

4081 return ClauseList._construct_raw(

4082 operators.comma_op, self._group_by_clauses

4083 )

4084

4085 @property

4086 def _order_by_clause(self) -> ClauseList:

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

4088 return ClauseList._construct_raw(

4089 operators.comma_op, self._order_by_clauses

4090 )

4091

4092 def _offset_or_limit_clause(

4093 self,

4094 element: _LimitOffsetType,

4095 name: Optional[str] = None,

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

4097 ) -> ColumnElement[Any]:

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

4099

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

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

4102

4103 """

4104 return coercions.expect(

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

4106 )

4107

4108 @overload

4109 def _offset_or_limit_clause_asint(

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

4111 ) -> NoReturn: ...

4112

4113 @overload

4114 def _offset_or_limit_clause_asint(

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

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

4117

4118 def _offset_or_limit_clause_asint(

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

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

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

4122 integer.

4123

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

4125 parameter. Otherwise, a compilation error is raised.

4126

4127 """

4128 if clause is None:

4129 return None

4130 try:

4131 value = clause._limit_offset_value

4132 except AttributeError as err:

4133 raise exc.CompileError(

4134 "This SELECT structure does not use a simple "

4135 "integer value for %s" % attrname

4136 ) from err

4137 else:

4138 return util.asint(value)

4139

4140 @property

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

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

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

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

4145 isn't currently set to an integer.

4146

4147 """

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

4149

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

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

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

4153 """

4154 return isinstance(clause, _OffsetLimitParam)

4155

4156 @property

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

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

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

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

4161 offset isn't currently set to an integer.

4162

4163 """

4164 return self._offset_or_limit_clause_asint(

4165 self._offset_clause, "offset"

4166 )

4167

4168 @property

4169 def _has_row_limiting_clause(self) -> bool:

4170 return (

4171 self._limit_clause is not None

4172 or self._offset_clause is not None

4173 or self._fetch_clause is not None

4174 )

4175

4176 @_generative

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

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

4179 applied.

4180

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

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

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

4184 functionality.

4185

4186 .. note::

4187

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

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

4190

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

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

4193

4194 .. seealso::

4195

4196 :meth:`_sql.GenerativeSelect.fetch`

4197

4198 :meth:`_sql.GenerativeSelect.offset`

4199

4200 """

4201

4202 self._fetch_clause = self._fetch_clause_options = None

4203 self._limit_clause = self._offset_or_limit_clause(limit)

4204 return self

4205

4206 @_generative

4207 def fetch(

4208 self,

4209 count: _LimitOffsetType,

4210 with_ties: bool = False,

4211 percent: bool = False,

4212 **dialect_kw: Any,

4213 ) -> Self:

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

4215 applied.

4216

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

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

4219 select. This functionality is is currently implemented for Oracle

4220 Database, PostgreSQL, MSSQL.

4221

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

4223

4224 .. note::

4225

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

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

4228

4229 .. versionadded:: 1.4

4230

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

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

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

4234 Pass ``None`` to reset it.

4235

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

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

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

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

4240

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

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

4243

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

4245 may be accepted by dialects.

4246

4247 .. versionadded:: 2.0.41

4248

4249 .. seealso::

4250

4251 :meth:`_sql.GenerativeSelect.limit`

4252

4253 :meth:`_sql.GenerativeSelect.offset`

4254

4255 """

4256 self._validate_dialect_kwargs(dialect_kw)

4257 self._limit_clause = None

4258 if count is None:

4259 self._fetch_clause = self._fetch_clause_options = None

4260 else:

4261 self._fetch_clause = self._offset_or_limit_clause(count)

4262 self._fetch_clause_options = {

4263 "with_ties": with_ties,

4264 "percent": percent,

4265 }

4266 return self

4267

4268 @_generative

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

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

4271 applied.

4272

4273

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

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

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

4277 functionality.

4278

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

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

4281

4282 .. seealso::

4283

4284 :meth:`_sql.GenerativeSelect.limit`

4285

4286 :meth:`_sql.GenerativeSelect.fetch`

4287

4288 """

4289

4290 self._offset_clause = self._offset_or_limit_clause(offset)

4291 return self

4292

4293 @_generative

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

4295 def slice(

4296 self,

4297 start: int,

4298 stop: int,

4299 ) -> Self:

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

4301

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

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

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

4305 query.

4306

4307 For example, ::

4308

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

4310

4311 renders as

4312

4313 .. sourcecode:: sql

4314

4315 SELECT users.id AS users_id,

4316 users.name AS users_name

4317 FROM users ORDER BY users.id

4318 LIMIT ? OFFSET ?

4319 (2, 1)

4320

4321 .. note::

4322

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

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

4325

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

4327 method generalized from the ORM.

4328

4329 .. seealso::

4330

4331 :meth:`_sql.GenerativeSelect.limit`

4332

4333 :meth:`_sql.GenerativeSelect.offset`

4334

4335 :meth:`_sql.GenerativeSelect.fetch`

4336

4337 """

4338 sql_util = util.preloaded.sql_util

4339 self._fetch_clause = self._fetch_clause_options = None

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

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

4342 )

4343 return self

4344

4345 @_generative

4346 def order_by(

4347 self,

4348 __first: Union[

4349 Literal[None, _NoArg.NO_ARG],

4350 _ColumnExpressionOrStrLabelArgument[Any],

4351 ] = _NoArg.NO_ARG,

4352 /,

4353 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4354 ) -> Self:

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

4356 criteria applied.

4357

4358 e.g.::

4359

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

4361

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

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

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

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

4366

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

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

4369

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

4371 constructs

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

4373

4374 .. seealso::

4375

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

4377

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

4379

4380 """

4381

4382 if not clauses and __first is None:

4383 self._order_by_clauses = ()

4384 elif __first is not _NoArg.NO_ARG:

4385 self._order_by_clauses += tuple(

4386 coercions.expect(

4387 roles.OrderByRole, clause, apply_propagate_attrs=self

4388 )

4389 for clause in (__first,) + clauses

4390 )

4391 return self

4392

4393 @_generative

4394 def group_by(

4395 self,

4396 __first: Union[

4397 Literal[None, _NoArg.NO_ARG],

4398 _ColumnExpressionOrStrLabelArgument[Any],

4399 ] = _NoArg.NO_ARG,

4400 /,

4401 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4402 ) -> Self:

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

4404 criterion applied.

4405

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

4407

4408 e.g.::

4409

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

4411

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

4413 constructs

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

4415

4416 .. seealso::

4417

4418 :ref:`tutorial_group_by_w_aggregates` - in the

4419 :ref:`unified_tutorial`

4420

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

4422

4423 """ # noqa: E501

4424

4425 if not clauses and __first is None:

4426 self._group_by_clauses = ()

4427 elif __first is not _NoArg.NO_ARG:

4428 self._group_by_clauses += tuple(

4429 coercions.expect(

4430 roles.GroupByRole, clause, apply_propagate_attrs=self

4431 )

4432 for clause in (__first,) + clauses

4433 )

4434 return self

4435

4436

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

4438class CompoundSelectState(CompileState):

4439 @util.memoized_property

4440 def _label_resolve_dict(

4441 self,

4442 ) -> Tuple[

4443 Dict[str, ColumnElement[Any]],

4444 Dict[str, ColumnElement[Any]],

4445 Dict[str, ColumnElement[Any]],

4446 ]:

4447 # TODO: this is hacky and slow

4448 hacky_subquery = self.statement.subquery()

4449 hacky_subquery.named_with_column = False

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

4451 return d, d, d

4452

4453

4454class _CompoundSelectKeyword(Enum):

4455 UNION = "UNION"

4456 UNION_ALL = "UNION ALL"

4457 EXCEPT = "EXCEPT"

4458 EXCEPT_ALL = "EXCEPT ALL"

4459 INTERSECT = "INTERSECT"

4460 INTERSECT_ALL = "INTERSECT ALL"

4461

4462

4463class CompoundSelect(

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

4465):

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

4467 SELECT-based set operations.

4468

4469

4470 .. seealso::

4471

4472 :func:`_expression.union`

4473

4474 :func:`_expression.union_all`

4475

4476 :func:`_expression.intersect`

4477

4478 :func:`_expression.intersect_all`

4479

4480 :func:`_expression.except`

4481

4482 :func:`_expression.except_all`

4483

4484 """

4485

4486 __visit_name__ = "compound_select"

4487

4488 _traverse_internals: _TraverseInternalsType = (

4489 [

4490 ("selects", InternalTraversal.dp_clauseelement_list),

4491 ("_limit_clause", InternalTraversal.dp_clauseelement),

4492 ("_offset_clause", InternalTraversal.dp_clauseelement),

4493 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4494 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4495 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4496 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4497 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4498 ("keyword", InternalTraversal.dp_string),

4499 ]

4500 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4501 + HasCTE._has_ctes_traverse_internals

4502 + DialectKWArgs._dialect_kwargs_traverse_internals

4503 )

4504

4505 selects: List[SelectBase]

4506

4507 _is_from_container = True

4508 _auto_correlate = False

4509

4510 def __init__(

4511 self,

4512 keyword: _CompoundSelectKeyword,

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

4514 ):

4515 self.keyword = keyword

4516 self.selects = [

4517 coercions.expect(

4518 roles.CompoundElementRole, s, apply_propagate_attrs=self

4519 ).self_group(against=self)

4520 for s in selects

4521 ]

4522

4523 GenerativeSelect.__init__(self)

4524

4525 @classmethod

4526 def _create_union(

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

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

4529 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4530

4531 @classmethod

4532 def _create_union_all(

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

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

4535 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4536

4537 @classmethod

4538 def _create_except(

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

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

4541 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4542

4543 @classmethod

4544 def _create_except_all(

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

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

4547 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4548

4549 @classmethod

4550 def _create_intersect(

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

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

4553 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4554

4555 @classmethod

4556 def _create_intersect_all(

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

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

4559 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4560

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

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

4563

4564 def self_group(

4565 self, against: Optional[OperatorType] = None

4566 ) -> GroupedElement:

4567 return SelectStatementGrouping(self)

4568

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

4570 for s in self.selects:

4571 if s.is_derived_from(fromclause):

4572 return True

4573 return False

4574

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

4576 if self._label_style is not style:

4577 self = self._generate()

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

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

4580

4581 return self

4582

4583 def _ensure_disambiguated_names(self) -> Self:

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

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

4586 self = self._generate()

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

4588

4589 return self

4590

4591 def _generate_fromclause_column_proxies(

4592 self,

4593 subquery: FromClause,

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

4595 primary_key: ColumnSet,

4596 foreign_keys: Set[KeyedColumnElement[Any]],

4597 *,

4598 proxy_compound_columns: Optional[

4599 Iterable[Sequence[ColumnElement[Any]]]

4600 ] = None,

4601 ) -> None:

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

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

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

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

4606 # generate below.

4607 select_0 = self.selects[0]

4608

4609 if self._label_style is not LABEL_STYLE_DEFAULT:

4610 select_0 = select_0.set_label_style(self._label_style)

4611

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

4613 # derived columns place a 'weight' annotation corresponding

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

4615 # that the corresponding_column() operation can resolve

4616 # conflicts

4617 extra_col_iterator = zip(

4618 *[

4619 [

4620 c._annotate(dd)

4621 for c in stmt._all_selected_columns

4622 if is_column_element(c)

4623 ]

4624 for dd, stmt in [

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

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

4627 ]

4628 ]

4629 )

4630

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

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

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

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

4635 # compound

4636 select_0._generate_fromclause_column_proxies(

4637 subquery,

4638 columns,

4639 proxy_compound_columns=extra_col_iterator,

4640 primary_key=primary_key,

4641 foreign_keys=foreign_keys,

4642 )

4643

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

4645 super()._refresh_for_new_column(column)

4646 for select in self.selects:

4647 select._refresh_for_new_column(column)

4648

4649 @util.ro_non_memoized_property

4650 def _all_selected_columns(self) -> _SelectIterable:

4651 return self.selects[0]._all_selected_columns

4652

4653 @util.ro_non_memoized_property

4654 def selected_columns(

4655 self,

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

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

4658 representing the columns that

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

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

4661

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

4663 :attr:`_expression.CompoundSelect.selected_columns`

4664 attribute returns the selected

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

4666 statements within the set operation.

4667

4668 .. seealso::

4669

4670 :attr:`_sql.Select.selected_columns`

4671

4672 .. versionadded:: 1.4

4673

4674 """

4675 return self.selects[0].selected_columns

4676

4677

4678# backwards compat

4679for elem in _CompoundSelectKeyword:

4680 setattr(CompoundSelect, elem.name, elem)

4681

4682

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

4684class SelectState(util.MemoizedSlots, CompileState):

4685 __slots__ = (

4686 "from_clauses",

4687 "froms",

4688 "columns_plus_names",

4689 "_label_resolve_dict",

4690 )

4691

4692 if TYPE_CHECKING:

4693 default_select_compile_options: CacheableOptions

4694 else:

4695

4696 class default_select_compile_options(CacheableOptions):

4697 _cache_key_traversal = []

4698

4699 if TYPE_CHECKING:

4700

4701 @classmethod

4702 def get_plugin_class(

4703 cls, statement: Executable

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

4705

4706 def __init__(

4707 self,

4708 statement: Select[Unpack[TupleAny]],

4709 compiler: SQLCompiler,

4710 **kw: Any,

4711 ):

4712 self.statement = statement

4713 self.from_clauses = statement._from_obj

4714

4715 for memoized_entities in statement._memoized_select_entities:

4716 self._setup_joins(

4717 memoized_entities._setup_joins, memoized_entities._raw_columns

4718 )

4719

4720 if statement._setup_joins:

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

4722

4723 self.froms = self._get_froms(statement)

4724

4725 self.columns_plus_names = statement._generate_columns_plus_names(True)

4726

4727 @classmethod

4728 def _plugin_not_implemented(cls) -> NoReturn:

4729 raise NotImplementedError(

4730 "The default SELECT construct without plugins does not "

4731 "implement this method."

4732 )

4733

4734 @classmethod

4735 def get_column_descriptions(

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

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

4738 return [

4739 {

4740 "name": name,

4741 "type": element.type,

4742 "expr": element,

4743 }

4744 for _, name, _, element, _ in (

4745 statement._generate_columns_plus_names(False)

4746 )

4747 ]

4748

4749 @classmethod

4750 def from_statement(

4751 cls,

4752 statement: Select[Unpack[TupleAny]],

4753 from_statement: roles.ReturnsRowsRole,

4754 ) -> ExecutableReturnsRows:

4755 cls._plugin_not_implemented()

4756

4757 @classmethod

4758 def get_columns_clause_froms(

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

4760 ) -> List[FromClause]:

4761 return cls._normalize_froms(

4762 itertools.chain.from_iterable(

4763 element._from_objects for element in statement._raw_columns

4764 )

4765 )

4766

4767 @classmethod

4768 def _column_naming_convention(

4769 cls, label_style: SelectLabelStyle

4770 ) -> _LabelConventionCallable:

4771 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4772

4773 dedupe = label_style is not LABEL_STYLE_NONE

4774

4775 pa = prefix_anon_map()

4776 names = set()

4777

4778 def go(

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

4780 col_name: Optional[str] = None,

4781 ) -> Optional[str]:

4782 if is_text_clause(c):

4783 return None

4784 elif TYPE_CHECKING:

4785 assert is_column_element(c)

4786

4787 if not dedupe:

4788 name = c._proxy_key

4789 if name is None:

4790 name = "_no_label"

4791 return name

4792

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

4794

4795 if name is None:

4796 name = "_no_label"

4797 if name in names:

4798 return c._anon_label(name) % pa

4799 else:

4800 names.add(name)

4801 return name

4802

4803 elif name in names:

4804 return (

4805 c._anon_tq_key_label % pa

4806 if table_qualified

4807 else c._anon_key_label % pa

4808 )

4809 else:

4810 names.add(name)

4811 return name

4812

4813 return go

4814

4815 def _get_froms(

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

4817 ) -> List[FromClause]:

4818 ambiguous_table_name_map: _AmbiguousTableNameMap

4819 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4820

4821 return self._normalize_froms(

4822 itertools.chain(

4823 self.from_clauses,

4824 itertools.chain.from_iterable(

4825 [

4826 element._from_objects

4827 for element in statement._raw_columns

4828 ]

4829 ),

4830 itertools.chain.from_iterable(

4831 [

4832 element._from_objects

4833 for element in statement._where_criteria

4834 ]

4835 ),

4836 ),

4837 check_statement=statement,

4838 ambiguous_table_name_map=ambiguous_table_name_map,

4839 )

4840

4841 @classmethod

4842 def _normalize_froms(

4843 cls,

4844 iterable_of_froms: Iterable[FromClause],

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

4846 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4847 ) -> List[FromClause]:

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

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

4850

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

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

4853 etc.

4854

4855 """

4856 seen: Set[FromClause] = set()

4857 froms: List[FromClause] = []

4858

4859 for item in iterable_of_froms:

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

4861 raise exc.InvalidRequestError(

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

4863 )

4864

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

4866 froms.append(item)

4867 seen.update(item._cloned_set)

4868

4869 if froms:

4870 toremove = set(

4871 itertools.chain.from_iterable(

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

4873 )

4874 )

4875 if toremove:

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

4877 # using a list to maintain ordering

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

4879

4880 if ambiguous_table_name_map is not None:

4881 ambiguous_table_name_map.update(

4882 (

4883 fr.name,

4884 _anonymous_label.safe_construct(

4885 hash(fr.name), fr.name

4886 ),

4887 )

4888 for item in froms

4889 for fr in item._from_objects

4890 if is_table(fr)

4891 and fr.schema

4892 and fr.name not in ambiguous_table_name_map

4893 )

4894

4895 return froms

4896

4897 def _get_display_froms(

4898 self,

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

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

4901 ) -> List[FromClause]:

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

4903

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

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

4906 may want to leave those absent if it is automatically

4907 correlating.

4908

4909 """

4910

4911 froms = self.froms

4912

4913 if self.statement._correlate:

4914 to_correlate = self.statement._correlate

4915 if to_correlate:

4916 froms = [

4917 f

4918 for f in froms

4919 if f

4920 not in _cloned_intersection(

4921 _cloned_intersection(

4922 froms, explicit_correlate_froms or ()

4923 ),

4924 to_correlate,

4925 )

4926 ]

4927

4928 if self.statement._correlate_except is not None:

4929 froms = [

4930 f

4931 for f in froms

4932 if f

4933 not in _cloned_difference(

4934 _cloned_intersection(

4935 froms, explicit_correlate_froms or ()

4936 ),

4937 self.statement._correlate_except,

4938 )

4939 ]

4940

4941 if (

4942 self.statement._auto_correlate

4943 and implicit_correlate_froms

4944 and len(froms) > 1

4945 ):

4946 froms = [

4947 f

4948 for f in froms

4949 if f

4950 not in _cloned_intersection(froms, implicit_correlate_froms)

4951 ]

4952

4953 if not len(froms):

4954 raise exc.InvalidRequestError(

4955 "Select statement '%r"

4956 "' returned no FROM clauses "

4957 "due to auto-correlation; "

4958 "specify correlate(<tables>) "

4959 "to control correlation "

4960 "manually." % self.statement

4961 )

4962

4963 return froms

4964

4965 def _memoized_attr__label_resolve_dict(

4966 self,

4967 ) -> Tuple[

4968 Dict[str, ColumnElement[Any]],

4969 Dict[str, ColumnElement[Any]],

4970 Dict[str, ColumnElement[Any]],

4971 ]:

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

4973 c._tq_label or c.key: c

4974 for c in self.statement._all_selected_columns

4975 if c._allow_label_resolve

4976 }

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

4978 c.key: c # type: ignore

4979 for c in _select_iterables(self.froms)

4980 if c._allow_label_resolve

4981 }

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

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

4984 with_cols.setdefault(key, value)

4985

4986 return with_cols, only_froms, only_cols

4987

4988 @classmethod

4989 def determine_last_joined_entity(

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

4991 ) -> Optional[_JoinTargetElement]:

4992 if stmt._setup_joins:

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

4994 else:

4995 return None

4996

4997 @classmethod

4998 def all_selected_columns(

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

5000 ) -> _SelectIterable:

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

5002

5003 def _setup_joins(

5004 self,

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

5006 raw_columns: List[_ColumnsClauseElement],

5007 ) -> None:

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

5009 if TYPE_CHECKING:

5010 if onclause is not None:

5011 assert isinstance(onclause, ColumnElement)

5012

5013 isouter = flags["isouter"]

5014 full = flags["full"]

5015

5016 if left is None:

5017 (

5018 left,

5019 replace_from_obj_index,

5020 ) = self._join_determine_implicit_left_side(

5021 raw_columns, left, right, onclause

5022 )

5023 else:

5024 (replace_from_obj_index) = self._join_place_explicit_left_side(

5025 left

5026 )

5027

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

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

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

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

5032 # used instead.

5033 if TYPE_CHECKING:

5034 assert isinstance(right, FromClause)

5035 if onclause is not None:

5036 assert isinstance(onclause, ColumnElement)

5037

5038 if replace_from_obj_index is not None:

5039 # splice into an existing element in the

5040 # self._from_obj list

5041 left_clause = self.from_clauses[replace_from_obj_index]

5042

5043 self.from_clauses = (

5044 self.from_clauses[:replace_from_obj_index]

5045 + (

5046 Join(

5047 left_clause,

5048 right,

5049 onclause,

5050 isouter=isouter,

5051 full=full,

5052 ),

5053 )

5054 + self.from_clauses[replace_from_obj_index + 1 :]

5055 )

5056 else:

5057 assert left is not None

5058 self.from_clauses = self.from_clauses + (

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

5060 )

5061

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

5063 def _join_determine_implicit_left_side(

5064 self,

5065 raw_columns: List[_ColumnsClauseElement],

5066 left: Optional[FromClause],

5067 right: _JoinTargetElement,

5068 onclause: Optional[ColumnElement[Any]],

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

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

5071 determine if an existing FROM or entity in this query

5072 can serve as the left hand side.

5073

5074 """

5075

5076 sql_util = util.preloaded.sql_util

5077

5078 replace_from_obj_index: Optional[int] = None

5079

5080 from_clauses = self.from_clauses

5081

5082 if from_clauses:

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

5084 from_clauses, right, onclause

5085 )

5086

5087 if len(indexes) == 1:

5088 replace_from_obj_index = indexes[0]

5089 left = from_clauses[replace_from_obj_index]

5090 else:

5091 potential = {}

5092 statement = self.statement

5093

5094 for from_clause in itertools.chain(

5095 itertools.chain.from_iterable(

5096 [element._from_objects for element in raw_columns]

5097 ),

5098 itertools.chain.from_iterable(

5099 [

5100 element._from_objects

5101 for element in statement._where_criteria

5102 ]

5103 ),

5104 ):

5105 potential[from_clause] = ()

5106

5107 all_clauses = list(potential.keys())

5108 indexes = sql_util.find_left_clause_to_join_from(

5109 all_clauses, right, onclause

5110 )

5111

5112 if len(indexes) == 1:

5113 left = all_clauses[indexes[0]]

5114

5115 if len(indexes) > 1:

5116 raise exc.InvalidRequestError(

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

5118 "from, there are multiple FROMS which can "

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

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

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

5122 "help resolve the ambiguity."

5123 )

5124 elif not indexes:

5125 raise exc.InvalidRequestError(

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

5127 "Please use the .select_from() "

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

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

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

5131 )

5132 return left, replace_from_obj_index

5133

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

5135 def _join_place_explicit_left_side(

5136 self, left: FromClause

5137 ) -> Optional[int]:

5138 replace_from_obj_index: Optional[int] = None

5139

5140 sql_util = util.preloaded.sql_util

5141

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

5143

5144 if from_clauses:

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

5146 self.from_clauses, left

5147 )

5148 else:

5149 indexes = []

5150

5151 if len(indexes) > 1:

5152 raise exc.InvalidRequestError(

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

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

5155 "ON clause."

5156 )

5157

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

5159 # an existing FROM in the self._from_obj tuple

5160 if indexes:

5161 replace_from_obj_index = indexes[0]

5162

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

5164 # self._from_obj tuple

5165

5166 return replace_from_obj_index

5167

5168

5169class _SelectFromElements:

5170 __slots__ = ()

5171

5172 _raw_columns: List[_ColumnsClauseElement]

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

5174 _from_obj: Tuple[FromClause, ...]

5175

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

5177 # note this does not include elements

5178 # in _setup_joins

5179

5180 seen = set()

5181 for element in self._raw_columns:

5182 for fr in element._from_objects:

5183 if fr in seen:

5184 continue

5185 seen.add(fr)

5186 yield fr

5187 for element in self._where_criteria:

5188 for fr in element._from_objects:

5189 if fr in seen:

5190 continue

5191 seen.add(fr)

5192 yield fr

5193 for element in self._from_obj:

5194 if element in seen:

5195 continue

5196 seen.add(element)

5197 yield element

5198

5199

5200class _MemoizedSelectEntities(

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

5202):

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

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

5205 statement will be SELECTing from. This object represents

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

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

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

5209

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

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

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

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

5214 SQL compiler when needed. But as of

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

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

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

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

5219

5220

5221 """

5222

5223 __visit_name__ = "memoized_select_entities"

5224

5225 _traverse_internals: _TraverseInternalsType = [

5226 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5227 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5228 ("_with_options", InternalTraversal.dp_executable_options),

5229 ]

5230

5231 _is_clone_of: Optional[ClauseElement]

5232 _raw_columns: List[_ColumnsClauseElement]

5233 _setup_joins: Tuple[_SetupJoinsElement, ...]

5234 _with_options: Tuple[ExecutableOption, ...]

5235

5236 _annotations = util.EMPTY_DICT

5237

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

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

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

5241

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

5243 return c

5244

5245 @classmethod

5246 def _generate_for_statement(

5247 cls, select_stmt: Select[Unpack[TupleAny]]

5248 ) -> None:

5249 if select_stmt._setup_joins or select_stmt._with_options:

5250 self = _MemoizedSelectEntities()

5251 self._raw_columns = select_stmt._raw_columns

5252 self._setup_joins = select_stmt._setup_joins

5253 self._with_options = select_stmt._with_options

5254

5255 select_stmt._memoized_select_entities += (self,)

5256 select_stmt._raw_columns = []

5257 select_stmt._setup_joins = select_stmt._with_options = ()

5258

5259

5260class Select(

5261 HasPrefixes,

5262 HasSuffixes,

5263 HasHints,

5264 HasCompileState,

5265 HasSyntaxExtensions[

5266 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5267 ],

5268 _SelectFromElements,

5269 GenerativeSelect,

5270 TypedReturnsRows[Unpack[_Ts]],

5271):

5272 """Represents a ``SELECT`` statement.

5273

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

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

5276

5277 Available extension points:

5278

5279 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5280 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5281 keyword (if any) and the list of columns.

5282 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5283 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5284

5285 .. seealso::

5286

5287 :func:`_sql.select`

5288

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

5290

5291 """

5292

5293 __visit_name__ = "select"

5294

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

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

5297

5298 _raw_columns: List[_ColumnsClauseElement]

5299

5300 _distinct: bool = False

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

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

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

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

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

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

5307

5308 _position_map = util.immutabledict(

5309 {

5310 "post_select": "_post_select_clause",

5311 "pre_columns": "_pre_columns_clause",

5312 "post_criteria": "_post_criteria_clause",

5313 "post_body": "_post_body_clause",

5314 }

5315 )

5316

5317 _post_select_clause: Optional[ClauseElement] = None

5318 """extension point for a ClauseElement that will be compiled directly

5319 after the SELECT keyword.

5320

5321 .. versionadded:: 2.1

5322

5323 """

5324

5325 _pre_columns_clause: Optional[ClauseElement] = None

5326 """extension point for a ClauseElement that will be compiled directly

5327 before the "columns" clause; after DISTINCT (if present).

5328

5329 .. versionadded:: 2.1

5330

5331 """

5332

5333 _post_criteria_clause: Optional[ClauseElement] = None

5334 """extension point for a ClauseElement that will be compiled directly

5335 after "criteria", following the HAVING clause but before ORDER BY.

5336

5337 .. versionadded:: 2.1

5338

5339 """

5340

5341 _post_body_clause: Optional[ClauseElement] = None

5342 """extension point for a ClauseElement that will be compiled directly

5343 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5344 of the SELECT.

5345

5346 .. versionadded:: 2.1

5347

5348 """

5349

5350 _auto_correlate = True

5351 _is_select_statement = True

5352 _compile_options: CacheableOptions = (

5353 SelectState.default_select_compile_options

5354 )

5355

5356 _traverse_internals: _TraverseInternalsType = (

5357 [

5358 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5359 (

5360 "_memoized_select_entities",

5361 InternalTraversal.dp_memoized_select_entities,

5362 ),

5363 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5364 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5365 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5366 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5367 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5368 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5369 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5370 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5371 ("_limit_clause", InternalTraversal.dp_clauseelement),

5372 ("_offset_clause", InternalTraversal.dp_clauseelement),

5373 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5374 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5375 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5376 ("_distinct", InternalTraversal.dp_boolean),

5377 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5378 ("_label_style", InternalTraversal.dp_plain_obj),

5379 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5380 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5381 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5382 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5383 ]

5384 + HasCTE._has_ctes_traverse_internals

5385 + HasPrefixes._has_prefixes_traverse_internals

5386 + HasSuffixes._has_suffixes_traverse_internals

5387 + HasHints._has_hints_traverse_internals

5388 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5389 + Executable._executable_traverse_internals

5390 + DialectKWArgs._dialect_kwargs_traverse_internals

5391 )

5392

5393 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5394 ("_compile_options", InternalTraversal.dp_has_cache_key)

5395 ]

5396

5397 _compile_state_factory: Type[SelectState]

5398

5399 @classmethod

5400 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

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

5402

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

5404 pre-established state.

5405

5406 """

5407

5408 stmt = Select.__new__(Select)

5409 stmt.__dict__.update(kw)

5410 return stmt

5411

5412 def __init__(

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

5414 ):

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

5416

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

5418 :func:`_sql.select` function.

5419

5420 """

5421 self._raw_columns = [

5422 coercions.expect(

5423 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5424 )

5425 for ent in entities

5426 ]

5427 GenerativeSelect.__init__(self)

5428

5429 def _apply_syntax_extension_to_self(

5430 self, extension: SyntaxExtension

5431 ) -> None:

5432 extension.apply_to_select(self)

5433

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

5435 if not self._raw_columns:

5436 return NULLTYPE

5437 elem = self._raw_columns[0]

5438 cols = list(elem._select_iterable)

5439 return cols[0].type

5440

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

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

5443

5444 return self.where(*criteria)

5445

5446 def _filter_by_zero(

5447 self,

5448 ) -> Union[

5449 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5450 ]:

5451 if self._setup_joins:

5452 meth = SelectState.get_plugin_class(

5453 self

5454 ).determine_last_joined_entity

5455 _last_joined_entity = meth(self)

5456 if _last_joined_entity is not None:

5457 return _last_joined_entity

5458

5459 if self._from_obj:

5460 return self._from_obj[0]

5461

5462 return self._raw_columns[0]

5463

5464 if TYPE_CHECKING:

5465

5466 @overload

5467 def scalar_subquery(

5468 self: Select[_MAYBE_ENTITY],

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

5470

5471 @overload

5472 def scalar_subquery(

5473 self: Select[_NOT_ENTITY],

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

5475

5476 @overload

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

5478

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

5480

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

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

5483 to this select.

5484

5485 """

5486 from_entity = self._filter_by_zero()

5487

5488 clauses = [

5489 _entity_namespace_key(from_entity, key) == value

5490 for key, value in kwargs.items()

5491 ]

5492 return self.filter(*clauses)

5493

5494 @property

5495 def column_descriptions(self) -> Any:

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

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

5498

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

5500 extended structure which includes information about mapped

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

5502 contains more background.

5503

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

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

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

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

5508 which indicate the column expressions to be selected::

5509

5510 >>> stmt = select(user_table)

5511 >>> stmt.column_descriptions

5512 [

5513 {

5514 'name': 'id',

5515 'type': Integer(),

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

5517 {

5518 'name': 'name',

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

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

5521 ]

5522

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

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

5525 not just ORM-only entities.

5526

5527 .. seealso::

5528

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

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

5531

5532 :ref:`queryguide_inspection` - ORM background

5533

5534 """

5535 meth = SelectState.get_plugin_class(self).get_column_descriptions

5536 return meth(self)

5537

5538 def from_statement(

5539 self, statement: roles.ReturnsRowsRole

5540 ) -> ExecutableReturnsRows:

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

5542 onto another statement.

5543

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

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

5546 plugin-enabled entities.

5547

5548

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

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

5551 columns appropriate to the entities represented by this

5552 :class:`.Select`.

5553

5554 .. seealso::

5555

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

5557 ORM Querying Guide

5558

5559 """

5560 meth = SelectState.get_plugin_class(self).from_statement

5561 return meth(self, statement)

5562

5563 @_generative

5564 def join(

5565 self,

5566 target: _JoinTargetArgument,

5567 onclause: Optional[_OnClauseArgument] = None,

5568 *,

5569 isouter: bool = False,

5570 full: bool = False,

5571 ) -> Self:

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

5573 object's criterion

5574 and apply generatively, returning the newly resulting

5575 :class:`_expression.Select`.

5576

5577 E.g.::

5578

5579 stmt = select(user_table).join(

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

5581 )

5582

5583 The above statement generates SQL similar to:

5584

5585 .. sourcecode:: sql

5586

5587 SELECT user.id, user.name

5588 FROM user

5589 JOIN address ON user.id = address.user_id

5590

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

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

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

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

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

5596 SELECT statement. This is completely reworked from the behavior

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

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

5599 target.

5600

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

5602 was mostly useless, producing an unnamed subquery rejected by

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

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

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

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

5607 :class:`_orm.Session`.

5608

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

5610

5611

5612 :param target: target table to join towards

5613

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

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

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

5617 determined, otherwise an error is raised.

5618

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

5620 :meth:`_expression.Select.outerjoin`.

5621

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

5623

5624 .. seealso::

5625

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

5627

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

5629

5630 :meth:`_expression.Select.join_from`

5631

5632 :meth:`_expression.Select.outerjoin`

5633

5634 """ # noqa: E501

5635 join_target = coercions.expect(

5636 roles.JoinTargetRole, target, apply_propagate_attrs=self

5637 )

5638 if onclause is not None:

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

5640 else:

5641 onclause_element = None

5642

5643 self._setup_joins += (

5644 (

5645 join_target,

5646 onclause_element,

5647 None,

5648 {"isouter": isouter, "full": full},

5649 ),

5650 )

5651 return self

5652

5653 def outerjoin_from(

5654 self,

5655 from_: _FromClauseArgument,

5656 target: _JoinTargetArgument,

5657 onclause: Optional[_OnClauseArgument] = None,

5658 *,

5659 full: bool = False,

5660 ) -> Self:

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

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

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

5664

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

5666

5667 """

5668 return self.join_from(

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

5670 )

5671

5672 @_generative

5673 def join_from(

5674 self,

5675 from_: _FromClauseArgument,

5676 target: _JoinTargetArgument,

5677 onclause: Optional[_OnClauseArgument] = None,

5678 *,

5679 isouter: bool = False,

5680 full: bool = False,

5681 ) -> Self:

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

5683 object's criterion

5684 and apply generatively, returning the newly resulting

5685 :class:`_expression.Select`.

5686

5687 E.g.::

5688

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

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

5691 )

5692

5693 The above statement generates SQL similar to:

5694

5695 .. sourcecode:: sql

5696

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

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

5699

5700 .. versionadded:: 1.4

5701

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

5703 FROM clause and is roughly equivalent to using the

5704 :meth:`.Select.select_from` method.

5705

5706 :param target: target table to join towards

5707

5708 :param onclause: ON clause of the join.

5709

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

5711 :meth:`_expression.Select.outerjoin`.

5712

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

5714

5715 .. seealso::

5716

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

5718

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

5720

5721 :meth:`_expression.Select.join`

5722

5723 """ # noqa: E501

5724

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

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

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

5728

5729 from_ = coercions.expect(

5730 roles.FromClauseRole, from_, apply_propagate_attrs=self

5731 )

5732 join_target = coercions.expect(

5733 roles.JoinTargetRole, target, apply_propagate_attrs=self

5734 )

5735 if onclause is not None:

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

5737 else:

5738 onclause_element = None

5739

5740 self._setup_joins += (

5741 (

5742 join_target,

5743 onclause_element,

5744 from_,

5745 {"isouter": isouter, "full": full},

5746 ),

5747 )

5748 return self

5749

5750 def outerjoin(

5751 self,

5752 target: _JoinTargetArgument,

5753 onclause: Optional[_OnClauseArgument] = None,

5754 *,

5755 full: bool = False,

5756 ) -> Self:

5757 """Create a left outer join.

5758

5759 Parameters are the same as that of :meth:`_expression.Select.join`.

5760

5761 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5762 creates a :class:`_sql.Join` object between a

5763 :class:`_sql.FromClause` source that is within the FROM clause of

5764 the existing SELECT, and a given target :class:`_sql.FromClause`,

5765 and then adds this :class:`_sql.Join` to the FROM clause of the

5766 newly generated SELECT statement. This is completely reworked

5767 from the behavior in 1.3, which would instead create a subquery of

5768 the entire

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

5770 target.

5771

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

5773 was mostly useless, producing an unnamed subquery rejected by

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

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

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

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

5778 :class:`_orm.Session`.

5779

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

5781

5782 .. seealso::

5783

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

5785

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

5787

5788 :meth:`_expression.Select.join`

5789

5790 """

5791 return self.join(target, onclause=onclause, isouter=True, full=full)

5792

5793 def get_final_froms(self) -> Sequence[FromClause]:

5794 """Compute the final displayed list of :class:`_expression.FromClause`

5795 elements.

5796

5797 This method will run through the full computation required to

5798 determine what FROM elements will be displayed in the resulting

5799 SELECT statement, including shadowing individual tables with

5800 JOIN objects, as well as full computation for ORM use cases including

5801 eager loading clauses.

5802

5803 For ORM use, this accessor returns the **post compilation**

5804 list of FROM objects; this collection will include elements such as

5805 eagerly loaded tables and joins. The objects will **not** be

5806 ORM enabled and not work as a replacement for the

5807 :meth:`_sql.Select.select_froms` collection; additionally, the

5808 method is not well performing for an ORM enabled statement as it

5809 will incur the full ORM construction process.

5810

5811 To retrieve the FROM list that's implied by the "columns" collection

5812 passed to the :class:`_sql.Select` originally, use the

5813 :attr:`_sql.Select.columns_clause_froms` accessor.

5814

5815 To select from an alternative set of columns while maintaining the

5816 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5817 pass the

5818 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5819 parameter.

5820

5821 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5822 method replaces the previous :attr:`_sql.Select.froms` accessor,

5823 which is deprecated.

5824

5825 .. seealso::

5826

5827 :attr:`_sql.Select.columns_clause_froms`

5828

5829 """

5830 compiler = self._default_compiler()

5831

5832 return self._compile_state_factory(self, compiler)._get_display_froms()

5833

5834 @property

5835 @util.deprecated(

5836 "1.4.23",

5837 "The :attr:`_expression.Select.froms` attribute is moved to "

5838 "the :meth:`_expression.Select.get_final_froms` method.",

5839 )

5840 def froms(self) -> Sequence[FromClause]:

5841 """Return the displayed list of :class:`_expression.FromClause`

5842 elements.

5843

5844

5845 """

5846 return self.get_final_froms()

5847

5848 @property

5849 def columns_clause_froms(self) -> List[FromClause]:

5850 """Return the set of :class:`_expression.FromClause` objects implied

5851 by the columns clause of this SELECT statement.

5852

5853 .. versionadded:: 1.4.23

5854

5855 .. seealso::

5856

5857 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5858 statement into account

5859

5860 :meth:`_sql.Select.with_only_columns` - makes use of this

5861 collection to set up a new FROM list

5862

5863 """

5864

5865 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5866 self

5867 )

5868

5869 @property

5870 def inner_columns(self) -> _SelectIterable:

5871 """An iterator of all :class:`_expression.ColumnElement`

5872 expressions which would

5873 be rendered into the columns clause of the resulting SELECT statement.

5874

5875 This method is legacy as of 1.4 and is superseded by the

5876 :attr:`_expression.Select.exported_columns` collection.

5877

5878 """

5879

5880 return iter(self._all_selected_columns)

5881

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

5883 if fromclause is not None and self in fromclause._cloned_set:

5884 return True

5885

5886 for f in self._iterate_from_elements():

5887 if f.is_derived_from(fromclause):

5888 return True

5889 return False

5890

5891 def _copy_internals(

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

5893 ) -> None:

5894 # Select() object has been cloned and probably adapted by the

5895 # given clone function. Apply the cloning function to internal

5896 # objects

5897

5898 # 1. keep a dictionary of the froms we've cloned, and what

5899 # they've become. This allows us to ensure the same cloned from

5900 # is used when other items such as columns are "cloned"

5901

5902 all_the_froms = set(

5903 itertools.chain(

5904 _from_objects(*self._raw_columns),

5905 _from_objects(*self._where_criteria),

5906 _from_objects(*[elem[0] for elem in self._setup_joins]),

5907 )

5908 )

5909

5910 # do a clone for the froms we've gathered. what is important here

5911 # is if any of the things we are selecting from, like tables,

5912 # were converted into Join objects. if so, these need to be

5913 # added to _from_obj explicitly, because otherwise they won't be

5914 # part of the new state, as they don't associate themselves with

5915 # their columns.

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

5917

5918 # 2. copy FROM collections, adding in joins that we've created.

5919 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

5920 add_froms = (

5921 {f for f in new_froms.values() if isinstance(f, Join)}

5922 .difference(all_the_froms)

5923 .difference(existing_from_obj)

5924 )

5925

5926 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

5927

5928 # 3. clone everything else, making sure we use columns

5929 # corresponding to the froms we just made.

5930 def replace(

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

5932 **kw: Any,

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

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

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

5936 return newelem

5937 return None

5938

5939 kw["replace"] = replace

5940

5941 # copy everything else. for table-ish things like correlate,

5942 # correlate_except, setup_joins, these clone normally. For

5943 # column-expression oriented things like raw_columns, where_criteria,

5944 # order by, we get this from the new froms.

5945 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

5946

5947 self._reset_memoizations()

5948

5949 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

5950 return itertools.chain(

5951 super().get_children(

5952 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

5953 **kw,

5954 ),

5955 self._iterate_from_elements(),

5956 )

5957

5958 @_generative

5959 def add_columns(

5960 self, *entities: _ColumnsClauseArgument[Any]

5961 ) -> Select[Unpack[TupleAny]]:

5962 r"""Return a new :func:`_expression.select` construct with

5963 the given entities appended to its columns clause.

5964

5965 E.g.::

5966

5967 my_select = my_select.add_columns(table.c.new_column)

5968

5969 The original expressions in the columns clause remain in place.

5970 To replace the original expressions with new ones, see the method

5971 :meth:`_expression.Select.with_only_columns`.

5972

5973 :param \*entities: column, table, or other entity expressions to be

5974 added to the columns clause

5975

5976 .. seealso::

5977

5978 :meth:`_expression.Select.with_only_columns` - replaces existing

5979 expressions rather than appending.

5980

5981 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

5982 example

5983

5984 """

5985 self._reset_memoizations()

5986

5987 self._raw_columns = self._raw_columns + [

5988 coercions.expect(

5989 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

5990 )

5991 for column in entities

5992 ]

5993 return self

5994

5995 def _set_entities(

5996 self, entities: Iterable[_ColumnsClauseArgument[Any]]

5997 ) -> None:

5998 self._raw_columns = [

5999 coercions.expect(

6000 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6001 )

6002 for ent in util.to_list(entities)

6003 ]

6004

6005 @util.deprecated(

6006 "1.4",

6007 "The :meth:`_expression.Select.column` method is deprecated and will "

6008 "be removed in a future release. Please use "

6009 ":meth:`_expression.Select.add_columns`",

6010 )

6011 def column(

6012 self, column: _ColumnsClauseArgument[Any]

6013 ) -> Select[Unpack[TupleAny]]:

6014 """Return a new :func:`_expression.select` construct with

6015 the given column expression added to its columns clause.

6016

6017 E.g.::

6018

6019 my_select = my_select.column(table.c.new_column)

6020

6021 See the documentation for

6022 :meth:`_expression.Select.with_only_columns`

6023 for guidelines on adding /replacing the columns of a

6024 :class:`_expression.Select` object.

6025

6026 """

6027 return self.add_columns(column)

6028

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

6030 def reduce_columns(

6031 self, only_synonyms: bool = True

6032 ) -> Select[Unpack[TupleAny]]:

6033 """Return a new :func:`_expression.select` construct with redundantly

6034 named, equivalently-valued columns removed from the columns clause.

6035

6036 "Redundant" here means two columns where one refers to the

6037 other either based on foreign key, or via a simple equality

6038 comparison in the WHERE clause of the statement. The primary purpose

6039 of this method is to automatically construct a select statement

6040 with all uniquely-named columns, without the need to use

6041 table-qualified labels as

6042 :meth:`_expression.Select.set_label_style`

6043 does.

6044

6045 When columns are omitted based on foreign key, the referred-to

6046 column is the one that's kept. When columns are omitted based on

6047 WHERE equivalence, the first column in the columns clause is the

6048 one that's kept.

6049

6050 :param only_synonyms: when True, limit the removal of columns

6051 to those which have the same name as the equivalent. Otherwise,

6052 all columns that are equivalent to another are removed.

6053

6054 """

6055 woc: Select[Unpack[TupleAny]]

6056 woc = self.with_only_columns(

6057 *util.preloaded.sql_util.reduce_columns(

6058 self._all_selected_columns,

6059 only_synonyms=only_synonyms,

6060 *(self._where_criteria + self._from_obj),

6061 )

6062 )

6063 return woc

6064

6065 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6066

6067 # code within this block is **programmatically,

6068 # statically generated** by tools/generate_tuple_map_overloads.py

6069

6070 @overload

6071 def with_only_columns(

6072 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6073 ) -> Select[_T0]: ...

6074

6075 @overload

6076 def with_only_columns(

6077 self,

6078 __ent0: _TCCA[_T0],

6079 __ent1: _TCCA[_T1],

6080 /,

6081 *,

6082 maintain_column_froms: bool = ...,

6083 ) -> Select[_T0, _T1]: ...

6084

6085 @overload

6086 def with_only_columns(

6087 self,

6088 __ent0: _TCCA[_T0],

6089 __ent1: _TCCA[_T1],

6090 __ent2: _TCCA[_T2],

6091 /,

6092 *,

6093 maintain_column_froms: bool = ...,

6094 ) -> Select[_T0, _T1, _T2]: ...

6095

6096 @overload

6097 def with_only_columns(

6098 self,

6099 __ent0: _TCCA[_T0],

6100 __ent1: _TCCA[_T1],

6101 __ent2: _TCCA[_T2],

6102 __ent3: _TCCA[_T3],

6103 /,

6104 *,

6105 maintain_column_froms: bool = ...,

6106 ) -> Select[_T0, _T1, _T2, _T3]: ...

6107

6108 @overload

6109 def with_only_columns(

6110 self,

6111 __ent0: _TCCA[_T0],

6112 __ent1: _TCCA[_T1],

6113 __ent2: _TCCA[_T2],

6114 __ent3: _TCCA[_T3],

6115 __ent4: _TCCA[_T4],

6116 /,

6117 *,

6118 maintain_column_froms: bool = ...,

6119 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6120

6121 @overload

6122 def with_only_columns(

6123 self,

6124 __ent0: _TCCA[_T0],

6125 __ent1: _TCCA[_T1],

6126 __ent2: _TCCA[_T2],

6127 __ent3: _TCCA[_T3],

6128 __ent4: _TCCA[_T4],

6129 __ent5: _TCCA[_T5],

6130 /,

6131 *,

6132 maintain_column_froms: bool = ...,

6133 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

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 /,

6146 *,

6147 maintain_column_froms: bool = ...,

6148 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6149

6150 @overload

6151 def with_only_columns(

6152 self,

6153 __ent0: _TCCA[_T0],

6154 __ent1: _TCCA[_T1],

6155 __ent2: _TCCA[_T2],

6156 __ent3: _TCCA[_T3],

6157 __ent4: _TCCA[_T4],

6158 __ent5: _TCCA[_T5],

6159 __ent6: _TCCA[_T6],

6160 __ent7: _TCCA[_T7],

6161 /,

6162 *entities: _ColumnsClauseArgument[Any],

6163 maintain_column_froms: bool = ...,

6164 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6165

6166 # END OVERLOADED FUNCTIONS self.with_only_columns

6167

6168 @overload

6169 def with_only_columns(

6170 self,

6171 *entities: _ColumnsClauseArgument[Any],

6172 maintain_column_froms: bool = False,

6173 **__kw: Any,

6174 ) -> Select[Unpack[TupleAny]]: ...

6175

6176 @_generative

6177 def with_only_columns(

6178 self,

6179 *entities: _ColumnsClauseArgument[Any],

6180 maintain_column_froms: bool = False,

6181 **__kw: Any,

6182 ) -> Select[Unpack[TupleAny]]:

6183 r"""Return a new :func:`_expression.select` construct with its columns

6184 clause replaced with the given entities.

6185

6186 By default, this method is exactly equivalent to as if the original

6187 :func:`_expression.select` had been called with the given entities.

6188 E.g. a statement::

6189

6190 s = select(table1.c.a, table1.c.b)

6191 s = s.with_only_columns(table1.c.b)

6192

6193 should be exactly equivalent to::

6194

6195 s = select(table1.c.b)

6196

6197 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6198 will also dynamically alter the FROM clause of the

6199 statement if it is not explicitly stated.

6200 To maintain the existing set of FROMs including those implied by the

6201 current columns clause, add the

6202 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6203 parameter::

6204

6205 s = select(table1.c.a, table2.c.b)

6206 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6207

6208 The above parameter performs a transfer of the effective FROMs

6209 in the columns collection to the :meth:`_sql.Select.select_from`

6210 method, as though the following were invoked::

6211

6212 s = select(table1.c.a, table2.c.b)

6213 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6214

6215 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6216 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6217 collection and performs an operation equivalent to the following::

6218

6219 s = select(table1.c.a, table2.c.b)

6220 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6221

6222 :param \*entities: column expressions to be used.

6223

6224 :param maintain_column_froms: boolean parameter that will ensure the

6225 FROM list implied from the current columns clause will be transferred

6226 to the :meth:`_sql.Select.select_from` method first.

6227

6228 .. versionadded:: 1.4.23

6229

6230 """ # noqa: E501

6231

6232 if __kw:

6233 raise _no_kw()

6234

6235 # memoizations should be cleared here as of

6236 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6237 # is the case for now.

6238 self._assert_no_memoizations()

6239

6240 if maintain_column_froms:

6241 self.select_from.non_generative( # type: ignore

6242 self, *self.columns_clause_froms

6243 )

6244

6245 # then memoize the FROMs etc.

6246 _MemoizedSelectEntities._generate_for_statement(self)

6247

6248 self._raw_columns = [

6249 coercions.expect(roles.ColumnsClauseRole, c)

6250 for c in coercions._expression_collection_was_a_list(

6251 "entities", "Select.with_only_columns", entities

6252 )

6253 ]

6254 return self

6255

6256 @property

6257 def whereclause(self) -> Optional[ColumnElement[Any]]:

6258 """Return the completed WHERE clause for this

6259 :class:`_expression.Select` statement.

6260

6261 This assembles the current collection of WHERE criteria

6262 into a single :class:`_expression.BooleanClauseList` construct.

6263

6264

6265 .. versionadded:: 1.4

6266

6267 """

6268

6269 return BooleanClauseList._construct_for_whereclause(

6270 self._where_criteria

6271 )

6272

6273 _whereclause = whereclause

6274

6275 @_generative

6276 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6277 """Return a new :func:`_expression.select` construct with

6278 the given expression added to

6279 its WHERE clause, joined to the existing clause via AND, if any.

6280

6281 """

6282

6283 assert isinstance(self._where_criteria, tuple)

6284

6285 for criterion in whereclause:

6286 where_criteria: ColumnElement[Any] = coercions.expect(

6287 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6288 )

6289 self._where_criteria += (where_criteria,)

6290 return self

6291

6292 @_generative

6293 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6294 """Return a new :func:`_expression.select` construct with

6295 the given expression added to

6296 its HAVING clause, joined to the existing clause via AND, if any.

6297

6298 """

6299

6300 for criterion in having:

6301 having_criteria = coercions.expect(

6302 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6303 )

6304 self._having_criteria += (having_criteria,)

6305 return self

6306

6307 @_generative

6308 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6309 r"""Return a new :func:`_expression.select` construct which

6310 will apply DISTINCT to the SELECT statement overall.

6311

6312 E.g.::

6313

6314 from sqlalchemy import select

6315

6316 stmt = select(users_table.c.id, users_table.c.name).distinct()

6317

6318 The above would produce an statement resembling:

6319

6320 .. sourcecode:: sql

6321

6322 SELECT DISTINCT user.id, user.name FROM user

6323

6324 The method also historically accepted an ``*expr`` parameter which

6325 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6326 This is now replaced using the :func:`_postgresql.distinct_on`

6327 extension::

6328

6329 from sqlalchemy import select

6330 from sqlalchemy.dialects.postgresql import distinct_on

6331

6332 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6333

6334 Using this parameter on other backends which don't support this

6335 syntax will raise an error.

6336

6337 :param \*expr: optional column expressions. When present,

6338 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6339 construct. A deprecation warning and/or :class:`_exc.CompileError`

6340 will be raised on other backends.

6341

6342 .. deprecated:: 2.1 Passing expressions to

6343 :meth:`_sql.Select.distinct` is deprecated, use

6344 :func:`_postgresql.distinct_on` instead.

6345

6346 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6347 and will raise :class:`_exc.CompileError` in a future version.

6348

6349 .. seealso::

6350

6351 :func:`_postgresql.distinct_on`

6352

6353 :meth:`_sql.HasSyntaxExtensions.ext`

6354 """

6355 self._distinct = True

6356 if expr:

6357 warn_deprecated(

6358 "Passing expression to ``distinct`` to generate a "

6359 "DISTINCT ON clause is deprecated. Use instead the "

6360 "``postgresql.distinct_on`` function as an extension.",

6361 "2.1",

6362 )

6363 self._distinct_on = self._distinct_on + tuple(

6364 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6365 for e in expr

6366 )

6367 return self

6368

6369 @_generative

6370 def select_from(self, *froms: _FromClauseArgument) -> Self:

6371 r"""Return a new :func:`_expression.select` construct with the

6372 given FROM expression(s)

6373 merged into its list of FROM objects.

6374

6375 E.g.::

6376

6377 table1 = table("t1", column("a"))

6378 table2 = table("t2", column("b"))

6379 s = select(table1.c.a).select_from(

6380 table1.join(table2, table1.c.a == table2.c.b)

6381 )

6382

6383 The "from" list is a unique set on the identity of each element,

6384 so adding an already present :class:`_schema.Table`

6385 or other selectable

6386 will have no effect. Passing a :class:`_expression.Join` that refers

6387 to an already present :class:`_schema.Table`

6388 or other selectable will have

6389 the effect of concealing the presence of that selectable as

6390 an individual element in the rendered FROM list, instead

6391 rendering it into a JOIN clause.

6392

6393 While the typical purpose of :meth:`_expression.Select.select_from`

6394 is to

6395 replace the default, derived FROM clause with a join, it can

6396 also be called with individual table elements, multiple times

6397 if desired, in the case that the FROM clause cannot be fully

6398 derived from the columns clause::

6399

6400 select(func.count("*")).select_from(table1)

6401

6402 """

6403

6404 self._from_obj += tuple(

6405 coercions.expect(

6406 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6407 )

6408 for fromclause in froms

6409 )

6410 return self

6411

6412 @_generative

6413 def correlate(

6414 self,

6415 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6416 ) -> Self:

6417 r"""Return a new :class:`_expression.Select`

6418 which will correlate the given FROM

6419 clauses to that of an enclosing :class:`_expression.Select`.

6420

6421 Calling this method turns off the :class:`_expression.Select` object's

6422 default behavior of "auto-correlation". Normally, FROM elements

6423 which appear in a :class:`_expression.Select`

6424 that encloses this one via

6425 its :term:`WHERE clause`, ORDER BY, HAVING or

6426 :term:`columns clause` will be omitted from this

6427 :class:`_expression.Select`

6428 object's :term:`FROM clause`.

6429 Setting an explicit correlation collection using the

6430 :meth:`_expression.Select.correlate`

6431 method provides a fixed list of FROM objects

6432 that can potentially take place in this process.

6433

6434 When :meth:`_expression.Select.correlate`

6435 is used to apply specific FROM clauses

6436 for correlation, the FROM elements become candidates for

6437 correlation regardless of how deeply nested this

6438 :class:`_expression.Select`

6439 object is, relative to an enclosing :class:`_expression.Select`

6440 which refers to

6441 the same FROM object. This is in contrast to the behavior of

6442 "auto-correlation" which only correlates to an immediate enclosing

6443 :class:`_expression.Select`.

6444 Multi-level correlation ensures that the link

6445 between enclosed and enclosing :class:`_expression.Select`

6446 is always via

6447 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6448 correlation to take place.

6449

6450 If ``None`` is passed, the :class:`_expression.Select`

6451 object will correlate

6452 none of its FROM entries, and all will render unconditionally

6453 in the local FROM clause.

6454

6455 :param \*fromclauses: one or more :class:`.FromClause` or other

6456 FROM-compatible construct such as an ORM mapped entity to become part

6457 of the correlate collection; alternatively pass a single value

6458 ``None`` to remove all existing correlations.

6459

6460 .. seealso::

6461

6462 :meth:`_expression.Select.correlate_except`

6463

6464 :ref:`tutorial_scalar_subquery`

6465

6466 """

6467

6468 # tests failing when we try to change how these

6469 # arguments are passed

6470

6471 self._auto_correlate = False

6472 if not fromclauses or fromclauses[0] in {None, False}:

6473 if len(fromclauses) > 1:

6474 raise exc.ArgumentError(

6475 "additional FROM objects not accepted when "

6476 "passing None/False to correlate()"

6477 )

6478 self._correlate = ()

6479 else:

6480 self._correlate = self._correlate + tuple(

6481 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6482 )

6483 return self

6484

6485 @_generative

6486 def correlate_except(

6487 self,

6488 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6489 ) -> Self:

6490 r"""Return a new :class:`_expression.Select`

6491 which will omit the given FROM

6492 clauses from the auto-correlation process.

6493

6494 Calling :meth:`_expression.Select.correlate_except` turns off the

6495 :class:`_expression.Select` object's default behavior of

6496 "auto-correlation" for the given FROM elements. An element

6497 specified here will unconditionally appear in the FROM list, while

6498 all other FROM elements remain subject to normal auto-correlation

6499 behaviors.

6500

6501 If ``None`` is passed, or no arguments are passed,

6502 the :class:`_expression.Select` object will correlate all of its

6503 FROM entries.

6504

6505 :param \*fromclauses: a list of one or more

6506 :class:`_expression.FromClause`

6507 constructs, or other compatible constructs (i.e. ORM-mapped

6508 classes) to become part of the correlate-exception collection.

6509

6510 .. seealso::

6511

6512 :meth:`_expression.Select.correlate`

6513

6514 :ref:`tutorial_scalar_subquery`

6515

6516 """

6517

6518 self._auto_correlate = False

6519 if not fromclauses or fromclauses[0] in {None, False}:

6520 if len(fromclauses) > 1:

6521 raise exc.ArgumentError(

6522 "additional FROM objects not accepted when "

6523 "passing None/False to correlate_except()"

6524 )

6525 self._correlate_except = ()

6526 else:

6527 self._correlate_except = (self._correlate_except or ()) + tuple(

6528 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6529 )

6530

6531 return self

6532

6533 @HasMemoized_ro_memoized_attribute

6534 def selected_columns(

6535 self,

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

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

6538 representing the columns that

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

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

6541

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

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

6544 within this collection cannot be directly nested inside another SELECT

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

6546 necessary parenthesization required by SQL.

6547

6548 For a :func:`_expression.select` construct, the collection here is

6549 exactly what would be rendered inside the "SELECT" statement, and the

6550 :class:`_expression.ColumnElement` objects are directly present as they

6551 were given, e.g.::

6552

6553 col1 = column("q", Integer)

6554 col2 = column("p", Integer)

6555 stmt = select(col1, col2)

6556

6557 Above, ``stmt.selected_columns`` would be a collection that contains

6558 the ``col1`` and ``col2`` objects directly. For a statement that is

6559 against a :class:`_schema.Table` or other

6560 :class:`_expression.FromClause`, the collection will use the

6561 :class:`_expression.ColumnElement` objects that are in the

6562 :attr:`_expression.FromClause.c` collection of the from element.

6563

6564 A use case for the :attr:`_sql.Select.selected_columns` collection is

6565 to allow the existing columns to be referenced when adding additional

6566 criteria, e.g.::

6567

6568 def filter_on_id(my_select, id):

6569 return my_select.where(my_select.selected_columns["id"] == id)

6570

6571

6572 stmt = select(MyModel)

6573

6574 # adds "WHERE id=:param" to the statement

6575 stmt = filter_on_id(stmt, 42)

6576

6577 .. note::

6578

6579 The :attr:`_sql.Select.selected_columns` collection does not

6580 include expressions established in the columns clause using the

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

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

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

6584 construct.

6585

6586

6587 .. versionadded:: 1.4

6588

6589 """

6590

6591 # compare to SelectState._generate_columns_plus_names, which

6592 # generates the actual names used in the SELECT string. that

6593 # method is more complex because it also renders columns that are

6594 # fully ambiguous, e.g. same column more than once.

6595 conv = cast(

6596 "Callable[[Any], str]",

6597 SelectState._column_naming_convention(self._label_style),

6598 )

6599

6600 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6601 [

6602 (conv(c), c)

6603 for c in self._all_selected_columns

6604 if is_column_element(c)

6605 ]

6606 )

6607 return cc.as_readonly()

6608

6609 @HasMemoized_ro_memoized_attribute

6610 def _all_selected_columns(self) -> _SelectIterable:

6611 meth = SelectState.get_plugin_class(self).all_selected_columns

6612 return list(meth(self))

6613

6614 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6615 if self._label_style is LABEL_STYLE_NONE:

6616 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6617 return self

6618

6619 def _generate_fromclause_column_proxies(

6620 self,

6621 subquery: FromClause,

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

6623 primary_key: ColumnSet,

6624 foreign_keys: Set[KeyedColumnElement[Any]],

6625 *,

6626 proxy_compound_columns: Optional[

6627 Iterable[Sequence[ColumnElement[Any]]]

6628 ] = None,

6629 ) -> None:

6630 """Generate column proxies to place in the exported ``.c``

6631 collection of a subquery."""

6632

6633 if proxy_compound_columns:

6634 extra_col_iterator = proxy_compound_columns

6635 prox = [

6636 c._make_proxy(

6637 subquery,

6638 key=proxy_key,

6639 name=required_label_name,

6640 name_is_truncatable=True,

6641 compound_select_cols=extra_cols,

6642 primary_key=primary_key,

6643 foreign_keys=foreign_keys,

6644 )

6645 for (

6646 (

6647 required_label_name,

6648 proxy_key,

6649 fallback_label_name,

6650 c,

6651 repeated,

6652 ),

6653 extra_cols,

6654 ) in (

6655 zip(

6656 self._generate_columns_plus_names(False),

6657 extra_col_iterator,

6658 )

6659 )

6660 if is_column_element(c)

6661 ]

6662 else:

6663 prox = [

6664 c._make_proxy(

6665 subquery,

6666 key=proxy_key,

6667 name=required_label_name,

6668 name_is_truncatable=True,

6669 primary_key=primary_key,

6670 foreign_keys=foreign_keys,

6671 )

6672 for (

6673 required_label_name,

6674 proxy_key,

6675 fallback_label_name,

6676 c,

6677 repeated,

6678 ) in (self._generate_columns_plus_names(False))

6679 if is_column_element(c)

6680 ]

6681

6682 columns._populate_separate_keys(prox)

6683

6684 def _needs_parens_for_grouping(self) -> bool:

6685 return self._has_row_limiting_clause or bool(

6686 self._order_by_clause.clauses

6687 )

6688

6689 def self_group(

6690 self, against: Optional[OperatorType] = None

6691 ) -> Union[SelectStatementGrouping[Self], Self]:

6692 """Return a 'grouping' construct as per the

6693 :class:`_expression.ClauseElement` specification.

6694

6695 This produces an element that can be embedded in an expression. Note

6696 that this method is called automatically as needed when constructing

6697 expressions and should not require explicit use.

6698

6699 """

6700 if (

6701 isinstance(against, CompoundSelect)

6702 and not self._needs_parens_for_grouping()

6703 ):

6704 return self

6705 else:

6706 return SelectStatementGrouping(self)

6707

6708 def union(

6709 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6711 r"""Return a SQL ``UNION`` of this select() construct against

6712 the given selectables provided as positional arguments.

6713

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

6715 UNION.

6716

6717 .. versionchanged:: 1.4.28

6718

6719 multiple elements are now accepted.

6720

6721 :param \**kwargs: keyword arguments are forwarded to the constructor

6722 for the newly created :class:`_sql.CompoundSelect` object.

6723

6724 """

6725 return CompoundSelect._create_union(self, *other)

6726

6727 def union_all(

6728 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6730 r"""Return a SQL ``UNION ALL`` of this select() construct against

6731 the given selectables provided as positional arguments.

6732

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

6734 UNION.

6735

6736 .. versionchanged:: 1.4.28

6737

6738 multiple elements are now accepted.

6739

6740 :param \**kwargs: keyword arguments are forwarded to the constructor

6741 for the newly created :class:`_sql.CompoundSelect` object.

6742

6743 """

6744 return CompoundSelect._create_union_all(self, *other)

6745

6746 def except_(

6747 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6749 r"""Return a SQL ``EXCEPT`` of this select() construct against

6750 the given selectable provided as positional arguments.

6751

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

6753 UNION.

6754

6755 .. versionchanged:: 1.4.28

6756

6757 multiple elements are now accepted.

6758

6759 """

6760 return CompoundSelect._create_except(self, *other)

6761

6762 def except_all(

6763 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6765 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6766 the given selectables provided as positional arguments.

6767

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

6769 UNION.

6770

6771 .. versionchanged:: 1.4.28

6772

6773 multiple elements are now accepted.

6774

6775 """

6776 return CompoundSelect._create_except_all(self, *other)

6777

6778 def intersect(

6779 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6781 r"""Return a SQL ``INTERSECT`` of this select() construct against

6782 the given selectables provided as positional arguments.

6783

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

6785 UNION.

6786

6787 .. versionchanged:: 1.4.28

6788

6789 multiple elements are now accepted.

6790

6791 :param \**kwargs: keyword arguments are forwarded to the constructor

6792 for the newly created :class:`_sql.CompoundSelect` object.

6793

6794 """

6795 return CompoundSelect._create_intersect(self, *other)

6796

6797 def intersect_all(

6798 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6800 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6801 against the given selectables provided as positional arguments.

6802

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

6804 UNION.

6805

6806 .. versionchanged:: 1.4.28

6807

6808 multiple elements are now accepted.

6809

6810 :param \**kwargs: keyword arguments are forwarded to the constructor

6811 for the newly created :class:`_sql.CompoundSelect` object.

6812

6813 """

6814 return CompoundSelect._create_intersect_all(self, *other)

6815

6816

6817class ScalarSelect(

6818 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6819):

6820 """Represent a scalar subquery.

6821

6822

6823 A :class:`_sql.ScalarSelect` is created by invoking the

6824 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6825 then participates in other SQL expressions as a SQL column expression

6826 within the :class:`_sql.ColumnElement` hierarchy.

6827

6828 .. seealso::

6829

6830 :meth:`_sql.SelectBase.scalar_subquery`

6831

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

6833

6834 """

6835

6836 _traverse_internals: _TraverseInternalsType = [

6837 ("element", InternalTraversal.dp_clauseelement),

6838 ("type", InternalTraversal.dp_type),

6839 ]

6840

6841 _from_objects: List[FromClause] = []

6842 _is_from_container = True

6843 if not TYPE_CHECKING:

6844 _is_implicitly_boolean = False

6845 inherit_cache = True

6846

6847 element: SelectBase

6848

6849 def __init__(self, element: SelectBase) -> None:

6850 self.element = element

6851 self.type = element._scalar_type()

6852 self._propagate_attrs = element._propagate_attrs

6853

6854 def __getattr__(self, attr: str) -> Any:

6855 return getattr(self.element, attr)

6856

6857 def __getstate__(self) -> Dict[str, Any]:

6858 return {"element": self.element, "type": self.type}

6859

6860 def __setstate__(self, state: Dict[str, Any]) -> None:

6861 self.element = state["element"]

6862 self.type = state["type"]

6863

6864 @property

6865 def columns(self) -> NoReturn:

6866 raise exc.InvalidRequestError(

6867 "Scalar Select expression has no "

6868 "columns; use this object directly "

6869 "within a column-level expression."

6870 )

6871

6872 c = columns

6873

6874 @_generative

6875 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6876 """Apply a WHERE clause to the SELECT statement referred to

6877 by this :class:`_expression.ScalarSelect`.

6878

6879 """

6880 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6881 crit

6882 )

6883 return self

6884

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

6886 return self

6887

6888 def _ungroup(self) -> Self:

6889 return self

6890

6891 @_generative

6892 def correlate(

6893 self,

6894 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6895 ) -> Self:

6896 r"""Return a new :class:`_expression.ScalarSelect`

6897 which will correlate the given FROM

6898 clauses to that of an enclosing :class:`_expression.Select`.

6899

6900 This method is mirrored from the :meth:`_sql.Select.correlate` method

6901 of the underlying :class:`_sql.Select`. The method applies the

6902 :meth:_sql.Select.correlate` method, then returns a new

6903 :class:`_sql.ScalarSelect` against that statement.

6904

6905 .. versionadded:: 1.4 Previously, the

6906 :meth:`_sql.ScalarSelect.correlate`

6907 method was only available from :class:`_sql.Select`.

6908

6909 :param \*fromclauses: a list of one or more

6910 :class:`_expression.FromClause`

6911 constructs, or other compatible constructs (i.e. ORM-mapped

6912 classes) to become part of the correlate collection.

6913

6914 .. seealso::

6915

6916 :meth:`_expression.ScalarSelect.correlate_except`

6917

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

6919

6920

6921 """

6922 self.element = cast(

6923 "Select[Unpack[TupleAny]]", self.element

6924 ).correlate(*fromclauses)

6925 return self

6926

6927 @_generative

6928 def correlate_except(

6929 self,

6930 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6931 ) -> Self:

6932 r"""Return a new :class:`_expression.ScalarSelect`

6933 which will omit the given FROM

6934 clauses from the auto-correlation process.

6935

6936 This method is mirrored from the

6937 :meth:`_sql.Select.correlate_except` method of the underlying

6938 :class:`_sql.Select`. The method applies the

6939 :meth:_sql.Select.correlate_except` method, then returns a new

6940 :class:`_sql.ScalarSelect` against that statement.

6941

6942 .. versionadded:: 1.4 Previously, the

6943 :meth:`_sql.ScalarSelect.correlate_except`

6944 method was only available from :class:`_sql.Select`.

6945

6946 :param \*fromclauses: a list of one or more

6947 :class:`_expression.FromClause`

6948 constructs, or other compatible constructs (i.e. ORM-mapped

6949 classes) to become part of the correlate-exception collection.

6950

6951 .. seealso::

6952

6953 :meth:`_expression.ScalarSelect.correlate`

6954

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

6956

6957

6958 """

6959

6960 self.element = cast(

6961 "Select[Unpack[TupleAny]]", self.element

6962 ).correlate_except(*fromclauses)

6963 return self

6964

6965

6966class Exists(UnaryExpression[bool]):

6967 """Represent an ``EXISTS`` clause.

6968

6969 See :func:`_sql.exists` for a description of usage.

6970

6971 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

6972 instance by calling :meth:`_sql.SelectBase.exists`.

6973

6974 """

6975

6976 inherit_cache = True

6977

6978 def __init__(

6979 self,

6980 __argument: Optional[

6981 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

6982 ] = None,

6983 /,

6984 ):

6985 s: ScalarSelect[Any]

6986

6987 # TODO: this seems like we should be using coercions for this

6988 if __argument is None:

6989 s = Select(literal_column("*")).scalar_subquery()

6990 elif isinstance(__argument, SelectBase):

6991 s = __argument.scalar_subquery()

6992 s._propagate_attrs = __argument._propagate_attrs

6993 elif isinstance(__argument, ScalarSelect):

6994 s = __argument

6995 else:

6996 s = Select(__argument).scalar_subquery()

6997

6998 UnaryExpression.__init__(

6999 self,

7000 s,

7001 operator=operators.exists,

7002 type_=type_api.BOOLEANTYPE,

7003 )

7004

7005 @util.ro_non_memoized_property

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

7007 return []

7008

7009 def _regroup(

7010 self,

7011 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7012 ) -> ScalarSelect[Any]:

7013

7014 assert isinstance(self.element, ScalarSelect)

7015 element = self.element.element

7016 if not isinstance(element, Select):

7017 raise exc.InvalidRequestError(

7018 "Can only apply this operation to a plain SELECT construct"

7019 )

7020 new_element = fn(element)

7021

7022 return_value = new_element.scalar_subquery()

7023 return return_value

7024

7025 def select(self) -> Select[bool]:

7026 r"""Return a SELECT of this :class:`_expression.Exists`.

7027

7028 e.g.::

7029

7030 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7031

7032 This will produce a statement resembling:

7033

7034 .. sourcecode:: sql

7035

7036 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7037

7038 .. seealso::

7039

7040 :func:`_expression.select` - general purpose

7041 method which allows for arbitrary column lists.

7042

7043 """ # noqa

7044

7045 return Select(self)

7046

7047 def correlate(

7048 self,

7049 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7050 ) -> Self:

7051 """Apply correlation to the subquery noted by this

7052 :class:`_sql.Exists`.

7053

7054 .. seealso::

7055

7056 :meth:`_sql.ScalarSelect.correlate`

7057

7058 """

7059 e = self._clone()

7060 e.element = self._regroup(

7061 lambda element: element.correlate(*fromclauses)

7062 )

7063 return e

7064

7065 def correlate_except(

7066 self,

7067 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7068 ) -> Self:

7069 """Apply correlation to the subquery noted by this

7070 :class:`_sql.Exists`.

7071

7072 .. seealso::

7073

7074 :meth:`_sql.ScalarSelect.correlate_except`

7075

7076 """

7077 e = self._clone()

7078 e.element = self._regroup(

7079 lambda element: element.correlate_except(*fromclauses)

7080 )

7081 return e

7082

7083 def select_from(self, *froms: _FromClauseArgument) -> Self:

7084 """Return a new :class:`_expression.Exists` construct,

7085 applying the given

7086 expression to the :meth:`_expression.Select.select_from`

7087 method of the select

7088 statement contained.

7089

7090 .. note:: it is typically preferable to build a :class:`_sql.Select`

7091 statement first, including the desired WHERE clause, then use the

7092 :meth:`_sql.SelectBase.exists` method to produce an

7093 :class:`_sql.Exists` object at once.

7094

7095 """

7096 e = self._clone()

7097 e.element = self._regroup(lambda element: element.select_from(*froms))

7098 return e

7099

7100 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7101 """Return a new :func:`_expression.exists` construct with the

7102 given expression added to

7103 its WHERE clause, joined to the existing clause via AND, if any.

7104

7105

7106 .. note:: it is typically preferable to build a :class:`_sql.Select`

7107 statement first, including the desired WHERE clause, then use the

7108 :meth:`_sql.SelectBase.exists` method to produce an

7109 :class:`_sql.Exists` object at once.

7110

7111 """

7112 e = self._clone()

7113 e.element = self._regroup(lambda element: element.where(*clause))

7114 return e

7115

7116

7117class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7118 """Wrap a :class:`_expression.TextClause` construct within a

7119 :class:`_expression.SelectBase`

7120 interface.

7121

7122 This allows the :class:`_expression.TextClause` object to gain a

7123 ``.c`` collection

7124 and other FROM-like capabilities such as

7125 :meth:`_expression.FromClause.alias`,

7126 :meth:`_expression.SelectBase.cte`, etc.

7127

7128 The :class:`_expression.TextualSelect` construct is produced via the

7129 :meth:`_expression.TextClause.columns`

7130 method - see that method for details.

7131

7132 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7133 class was renamed

7134 from ``TextAsFrom``, to more correctly suit its role as a

7135 SELECT-oriented object and not a FROM clause.

7136

7137 .. seealso::

7138

7139 :func:`_expression.text`

7140

7141 :meth:`_expression.TextClause.columns` - primary creation interface.

7142

7143 """

7144

7145 __visit_name__ = "textual_select"

7146

7147 _label_style = LABEL_STYLE_NONE

7148

7149 _traverse_internals: _TraverseInternalsType = (

7150 [

7151 ("element", InternalTraversal.dp_clauseelement),

7152 ("column_args", InternalTraversal.dp_clauseelement_list),

7153 ]

7154 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7155 + HasCTE._has_ctes_traverse_internals

7156 )

7157

7158 _is_textual = True

7159

7160 is_text = True

7161 is_select = True

7162

7163 def __init__(

7164 self,

7165 text: TextClause,

7166 columns: List[_ColumnExpressionArgument[Any]],

7167 positional: bool = False,

7168 ) -> None:

7169 self._init(

7170 text,

7171 # convert for ORM attributes->columns, etc

7172 [

7173 coercions.expect(roles.LabeledColumnExprRole, c)

7174 for c in columns

7175 ],

7176 positional,

7177 )

7178

7179 def _init(

7180 self,

7181 text: TextClause,

7182 columns: List[NamedColumn[Any]],

7183 positional: bool = False,

7184 ) -> None:

7185 self.element = text

7186 self.column_args = columns

7187 self.positional = positional

7188

7189 @HasMemoized_ro_memoized_attribute

7190 def selected_columns(

7191 self,

7192 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

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

7194 representing the columns that

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

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

7197

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

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

7200 within this collection cannot be directly nested inside another SELECT

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

7202 necessary parenthesization required by SQL.

7203

7204 For a :class:`_expression.TextualSelect` construct, the collection

7205 contains the :class:`_expression.ColumnElement` objects that were

7206 passed to the constructor, typically via the

7207 :meth:`_expression.TextClause.columns` method.

7208

7209

7210 .. versionadded:: 1.4

7211

7212 """

7213 return ColumnCollection(

7214 (c.key, c) for c in self.column_args

7215 ).as_readonly()

7216

7217 @util.ro_non_memoized_property

7218 def _all_selected_columns(self) -> _SelectIterable:

7219 return self.column_args

7220

7221 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7222 return self

7223

7224 def _ensure_disambiguated_names(self) -> TextualSelect:

7225 return self

7226

7227 @_generative

7228 def bindparams(

7229 self,

7230 *binds: BindParameter[Any],

7231 **bind_as_values: Any,

7232 ) -> Self:

7233 self.element = self.element.bindparams(*binds, **bind_as_values)

7234 return self

7235

7236 def _generate_fromclause_column_proxies(

7237 self,

7238 fromclause: FromClause,

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

7240 primary_key: ColumnSet,

7241 foreign_keys: Set[KeyedColumnElement[Any]],

7242 *,

7243 proxy_compound_columns: Optional[

7244 Iterable[Sequence[ColumnElement[Any]]]

7245 ] = None,

7246 ) -> None:

7247 if TYPE_CHECKING:

7248 assert isinstance(fromclause, Subquery)

7249

7250 if proxy_compound_columns:

7251 columns._populate_separate_keys(

7252 c._make_proxy(

7253 fromclause,

7254 compound_select_cols=extra_cols,

7255 primary_key=primary_key,

7256 foreign_keys=foreign_keys,

7257 )

7258 for c, extra_cols in zip(

7259 self.column_args, proxy_compound_columns

7260 )

7261 )

7262 else:

7263 columns._populate_separate_keys(

7264 c._make_proxy(

7265 fromclause,

7266 primary_key=primary_key,

7267 foreign_keys=foreign_keys,

7268 )

7269 for c in self.column_args

7270 )

7271

7272 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7273 return self.column_args[0].type

7274

7275

7276TextAsFrom = TextualSelect

7277"""Backwards compatibility with the previous name"""

7278

7279

7280class AnnotatedFromClause(Annotated):

7281 def _copy_internals(self, **kw: Any) -> None:

7282 super()._copy_internals(**kw)

7283 if kw.get("ind_cols_on_fromclause", False):

7284 ee = self._Annotated__element # type: ignore

7285

7286 self.c = ee.__class__.c.fget(self) # type: ignore

7287

7288 @util.ro_memoized_property

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

7290 """proxy the .c collection of the underlying FromClause.

7291

7292 Originally implemented in 2008 as a simple load of the .c collection

7293 when the annotated construct was created (see d3621ae961a), in modern

7294 SQLAlchemy versions this can be expensive for statements constructed

7295 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7296 it, which works just as well.

7297

7298 Two different use cases seem to require the collection either copied

7299 from the underlying one, or unique to this AnnotatedFromClause.

7300

7301 See test_selectable->test_annotated_corresponding_column

7302

7303 """

7304 ee = self._Annotated__element # type: ignore

7305 return ee.c # type: ignore