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

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

1166 FROM table1 JOIN table2 ON true

1167

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

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

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

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

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

1173

1174

1175 .. versionadded:: 1.4

1176

1177 """ # noqa: E501

1178

1179 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1182 when generating the columns clause of a SELECT statement.

1183

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

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

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

1187

1188 .. sourcecode:: pycon+sql

1189

1190 >>> from sqlalchemy import (

1191 ... table,

1192 ... column,

1193 ... select,

1194 ... true,

1195 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1196 ... )

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

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

1199 >>> print(

1200 ... select(table1, table2)

1201 ... .join(table2, true())

1202 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1203 ... )

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

1205 FROM table1 JOIN table2 ON true

1206

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

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

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

1210

1211 .. versionadded:: 1.4

1212

1213 """ # noqa: E501

1214

1215 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1216 """The default label style, refers to

1217 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1218

1219 .. versionadded:: 1.4

1220

1221 """

1222

1223 LABEL_STYLE_LEGACY_ORM = 3

1224

1225

1226(

1227 LABEL_STYLE_NONE,

1228 LABEL_STYLE_TABLENAME_PLUS_COL,

1229 LABEL_STYLE_DISAMBIGUATE_ONLY,

1230 _,

1231) = list(SelectLabelStyle)

1232

1233LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1234

1235

1236class Join(roles.DMLTableRole, FromClause):

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

1238 :class:`_expression.FromClause`

1239 elements.

1240

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

1242 is the module-level

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

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

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

1246 :class:`_schema.Table`).

1247

1248 .. seealso::

1249

1250 :func:`_expression.join`

1251

1252 :meth:`_expression.FromClause.join`

1253

1254 """

1255

1256 __visit_name__ = "join"

1257

1258 _traverse_internals: _TraverseInternalsType = [

1259 ("left", InternalTraversal.dp_clauseelement),

1260 ("right", InternalTraversal.dp_clauseelement),

1261 ("onclause", InternalTraversal.dp_clauseelement),

1262 ("isouter", InternalTraversal.dp_boolean),

1263 ("full", InternalTraversal.dp_boolean),

1264 ]

1265

1266 _is_join = True

1267

1268 left: FromClause

1269 right: FromClause

1270 onclause: Optional[ColumnElement[bool]]

1271 isouter: bool

1272 full: bool

1273

1274 def __init__(

1275 self,

1276 left: _FromClauseArgument,

1277 right: _FromClauseArgument,

1278 onclause: Optional[_OnClauseArgument] = None,

1279 isouter: bool = False,

1280 full: bool = False,

1281 ):

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

1283

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

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

1286 :class:`_expression.FromClause` object.

1287

1288 """

1289

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

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

1292 # annotated objects. test_orm.py -> test_fetch_results

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

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

1295 # callcounts for a single compilation in that particular test

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

1297 # 29200 -> 30373

1298

1299 self.left = coercions.expect(

1300 roles.FromClauseRole,

1301 left,

1302 )

1303 self.right = coercions.expect(

1304 roles.FromClauseRole,

1305 right,

1306 ).self_group()

1307

1308 if onclause is None:

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

1310 else:

1311 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1312 # not merged yet

1313 self.onclause = coercions.expect(

1314 roles.OnClauseRole, onclause

1315 ).self_group(against=operators._asbool)

1316

1317 self.isouter = isouter

1318 self.full = full

1319

1320 @util.ro_non_memoized_property

1321 def description(self) -> str:

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

1323 self.left.description,

1324 id(self.left),

1325 self.right.description,

1326 id(self.right),

1327 )

1328

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

1330 return (

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

1332 # as well

1333 hash(fromclause) == hash(self)

1334 or self.left.is_derived_from(fromclause)

1335 or self.right.is_derived_from(fromclause)

1336 )

1337

1338 def self_group(

1339 self, against: Optional[OperatorType] = None

1340 ) -> FromGrouping:

1341 return FromGrouping(self)

1342

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

1344 def _populate_column_collection(

1345 self,

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

1347 primary_key: ColumnSet,

1348 foreign_keys: Set[KeyedColumnElement[Any]],

1349 ) -> None:

1350 sqlutil = util.preloaded.sql_util

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

1352 c for c in self.right.c

1353 ]

1354

1355 primary_key.extend(

1356 sqlutil.reduce_columns(

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

1358 )

1359 )

1360 columns._populate_separate_keys(

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

1362 )

1363 foreign_keys.update(

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

1365 )

1366

1367 def _copy_internals(

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

1369 ) -> None:

1370 # see Select._copy_internals() for similar concept

1371

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

1373 # determine the new FROM clauses

1374 all_the_froms = set(

1375 itertools.chain(

1376 _from_objects(self.left),

1377 _from_objects(self.right),

1378 )

1379 )

1380

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

1382 # cache used by the clone function

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

1384

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

1386 # ColumnClause with parent table referring to those

1387 # replaced FromClause objects

1388 def replace(

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

1390 **kw: Any,

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

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

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

1394 return newelem

1395 return None

1396

1397 kw["replace"] = replace

1398

1399 # run normal _copy_internals. the clones for

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

1401 # cache

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

1403

1404 self._reset_memoizations()

1405

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

1407 super()._refresh_for_new_column(column)

1408 self.left._refresh_for_new_column(column)

1409 self.right._refresh_for_new_column(column)

1410

1411 def _match_primaries(

1412 self,

1413 left: FromClause,

1414 right: FromClause,

1415 ) -> ColumnElement[bool]:

1416 if isinstance(left, Join):

1417 left_right = left.right

1418 else:

1419 left_right = None

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

1421

1422 @classmethod

1423 def _join_condition(

1424 cls,

1425 a: FromClause,

1426 b: FromClause,

1427 *,

1428 a_subset: Optional[FromClause] = None,

1429 consider_as_foreign_keys: Optional[

1430 AbstractSet[ColumnClause[Any]]

1431 ] = None,

1432 ) -> ColumnElement[bool]:

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

1434

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

1436

1437 """

1438 constraints = cls._joincond_scan_left_right(

1439 a, a_subset, b, consider_as_foreign_keys

1440 )

1441

1442 if len(constraints) > 1:

1443 cls._joincond_trim_constraints(

1444 a, b, constraints, consider_as_foreign_keys

1445 )

1446

1447 if len(constraints) == 0:

1448 if isinstance(b, FromGrouping):

1449 hint = (

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

1451 "subquery using alias()?"

1452 )

1453 else:

1454 hint = ""

1455 raise exc.NoForeignKeysError(

1456 "Can't find any foreign key relationships "

1457 "between '%s' and '%s'.%s"

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

1459 )

1460

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

1462 if len(crit) == 1:

1463 return crit[0]

1464 else:

1465 return and_(*crit)

1466

1467 @classmethod

1468 def _can_join(

1469 cls,

1470 left: FromClause,

1471 right: FromClause,

1472 *,

1473 consider_as_foreign_keys: Optional[

1474 AbstractSet[ColumnClause[Any]]

1475 ] = None,

1476 ) -> bool:

1477 if isinstance(left, Join):

1478 left_right = left.right

1479 else:

1480 left_right = None

1481

1482 constraints = cls._joincond_scan_left_right(

1483 a=left,

1484 b=right,

1485 a_subset=left_right,

1486 consider_as_foreign_keys=consider_as_foreign_keys,

1487 )

1488

1489 return bool(constraints)

1490

1491 @classmethod

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

1493 def _joincond_scan_left_right(

1494 cls,

1495 a: FromClause,

1496 a_subset: Optional[FromClause],

1497 b: FromClause,

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

1499 ) -> collections.defaultdict[

1500 Optional[ForeignKeyConstraint],

1501 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1502 ]:

1503 sql_util = util.preloaded.sql_util

1504

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

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

1507

1508 constraints: collections.defaultdict[

1509 Optional[ForeignKeyConstraint],

1510 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1511 ] = collections.defaultdict(list)

1512

1513 for left in (a_subset, a):

1514 if left is None:

1515 continue

1516 for fk in sorted(

1517 b.foreign_keys,

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

1519 ):

1520 if (

1521 consider_as_foreign_keys is not None

1522 and fk.parent not in consider_as_foreign_keys

1523 ):

1524 continue

1525 try:

1526 col = fk.get_referent(left)

1527 except exc.NoReferenceError as nrte:

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

1529 if nrte.table_name in table_names:

1530 raise

1531 else:

1532 continue

1533

1534 if col is not None:

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

1536 if left is not b:

1537 for fk in sorted(

1538 left.foreign_keys,

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

1540 ):

1541 if (

1542 consider_as_foreign_keys is not None

1543 and fk.parent not in consider_as_foreign_keys

1544 ):

1545 continue

1546 try:

1547 col = fk.get_referent(b)

1548 except exc.NoReferenceError as nrte:

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

1550 if nrte.table_name in table_names:

1551 raise

1552 else:

1553 continue

1554

1555 if col is not None:

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

1557 if constraints:

1558 break

1559 return constraints

1560

1561 @classmethod

1562 def _joincond_trim_constraints(

1563 cls,

1564 a: FromClause,

1565 b: FromClause,

1566 constraints: Dict[Any, Any],

1567 consider_as_foreign_keys: Optional[Any],

1568 ) -> None:

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

1570 # to include just those FKCs that match exactly to

1571 # "consider_as_foreign_keys".

1572 if consider_as_foreign_keys:

1573 for const in list(constraints):

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

1575 consider_as_foreign_keys

1576 ):

1577 del constraints[const]

1578

1579 # if still multiple constraints, but

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

1581 if len(constraints) > 1:

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

1583 if len(dedupe) == 1:

1584 key = list(constraints)[0]

1585 constraints = {key: constraints[key]}

1586

1587 if len(constraints) != 1:

1588 raise exc.AmbiguousForeignKeysError(

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

1590 "tables have more than one foreign key "

1591 "constraint relationship between them. "

1592 "Please specify the 'onclause' of this "

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

1594 )

1595

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

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

1598 :class:`_expression.Join`.

1599

1600 E.g.::

1601

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

1603

1604 stmt = stmt.select()

1605

1606 The above will produce a SQL string resembling:

1607

1608 .. sourcecode:: sql

1609

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

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

1612

1613 """

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

1615

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

1617 def _anonymous_fromclause(

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

1619 ) -> TODO_Any:

1620 sqlutil = util.preloaded.sql_util

1621 if flat:

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

1623 left_name = name # will recurse

1624 else:

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

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

1627 else:

1628 left_name = name

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

1630 right_name = name # will recurse

1631 else:

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

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

1634 else:

1635 right_name = name

1636 left_a, right_a = (

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

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

1639 )

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

1641 sqlutil.ClauseAdapter(right_a)

1642 )

1643

1644 return left_a.join(

1645 right_a,

1646 adapter.traverse(self.onclause),

1647 isouter=self.isouter,

1648 full=self.full,

1649 )

1650 else:

1651 return (

1652 self.select()

1653 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1654 .correlate(None)

1655 .alias(name)

1656 )

1657

1658 @util.ro_non_memoized_property

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

1660 return itertools.chain(

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

1662 )

1663

1664 @util.ro_non_memoized_property

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

1666 self_list: List[FromClause] = [self]

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

1668

1669

1670class NoInit:

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

1672 raise NotImplementedError(

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

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

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

1676 "selectable objects."

1677 % (

1678 self.__class__.__name__,

1679 self.__class__.__name__.lower(),

1680 self.__class__.__name__.lower(),

1681 )

1682 )

1683

1684

1685class LateralFromClause(NamedFromClause):

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

1687

1688

1689# FromClause ->

1690# AliasedReturnsRows

1691# -> Alias only for FromClause

1692# -> Subquery only for SelectBase

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

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

1695# w/ non-deprecated coercion

1696# -> TableSample -> only for FromClause

1697

1698

1699class AliasedReturnsRows(NoInit, NamedFromClause):

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

1701 selectables."""

1702

1703 _is_from_container = True

1704

1705 _supports_derived_columns = False

1706

1707 element: ReturnsRows

1708

1709 _traverse_internals: _TraverseInternalsType = [

1710 ("element", InternalTraversal.dp_clauseelement),

1711 ("name", InternalTraversal.dp_anon_name),

1712 ]

1713

1714 @classmethod

1715 def _construct(

1716 cls,

1717 selectable: Any,

1718 *,

1719 name: Optional[str] = None,

1720 **kw: Any,

1721 ) -> Self:

1722 obj = cls.__new__(cls)

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

1724 return obj

1725

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

1727 self.element = coercions.expect(

1728 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1729 )

1730 self.element = selectable

1731 self._orig_name = name

1732 if name is None:

1733 if (

1734 isinstance(selectable, FromClause)

1735 and selectable.named_with_column

1736 ):

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

1738 if isinstance(name, _anonymous_label):

1739 name = None

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

1741 self.name = name

1742

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

1744 super()._refresh_for_new_column(column)

1745 self.element._refresh_for_new_column(column)

1746

1747 def _populate_column_collection(

1748 self,

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

1750 primary_key: ColumnSet,

1751 foreign_keys: Set[KeyedColumnElement[Any]],

1752 ) -> None:

1753 self.element._generate_fromclause_column_proxies(

1754 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1755 )

1756

1757 @util.ro_non_memoized_property

1758 def description(self) -> str:

1759 name = self.name

1760 if isinstance(name, _anonymous_label):

1761 return "anon_1"

1762

1763 return name

1764

1765 @util.ro_non_memoized_property

1766 def implicit_returning(self) -> bool:

1767 return self.element.implicit_returning # type: ignore

1768

1769 @property

1770 def original(self) -> ReturnsRows:

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

1772 return self.element

1773

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

1775 if fromclause in self._cloned_set:

1776 return True

1777 return self.element.is_derived_from(fromclause)

1778

1779 def _copy_internals(

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

1781 ) -> None:

1782 existing_element = self.element

1783

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

1785

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

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

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

1789 # performance.

1790 if existing_element is not self.element:

1791 self._reset_column_collection()

1792

1793 @property

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

1795 return [self]

1796

1797

1798class FromClauseAlias(AliasedReturnsRows):

1799 element: FromClause

1800

1801 @util.ro_non_memoized_property

1802 def description(self) -> str:

1803 name = self.name

1804 if isinstance(name, _anonymous_label):

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

1806

1807 return name

1808

1809

1810class Alias(roles.DMLTableRole, FromClauseAlias):

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

1812

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

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

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

1816

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

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

1819 method available

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

1821

1822 .. seealso::

1823

1824 :meth:`_expression.FromClause.alias`

1825

1826 """

1827

1828 __visit_name__ = "alias"

1829

1830 inherit_cache = True

1831

1832 element: FromClause

1833

1834 @classmethod

1835 def _factory(

1836 cls,

1837 selectable: FromClause,

1838 name: Optional[str] = None,

1839 flat: bool = False,

1840 ) -> NamedFromClause:

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

1842 name=name, flat=flat

1843 )

1844

1845

1846class TableValuedAlias(LateralFromClause, Alias):

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

1848

1849 This construct provides for a SQL function that returns columns

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

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

1852 method, e.g.:

1853

1854 .. sourcecode:: pycon+sql

1855

1856 >>> from sqlalchemy import select, func

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

1858 ... "value"

1859 ... )

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

1861 {printsql}SELECT anon_1.value

1862 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1863

1864 .. versionadded:: 1.4.0b2

1865

1866 .. seealso::

1867

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

1869

1870 """ # noqa: E501

1871

1872 __visit_name__ = "table_valued_alias"

1873

1874 _supports_derived_columns = True

1875 _render_derived = False

1876 _render_derived_w_types = False

1877 joins_implicitly = False

1878

1879 _traverse_internals: _TraverseInternalsType = [

1880 ("element", InternalTraversal.dp_clauseelement),

1881 ("name", InternalTraversal.dp_anon_name),

1882 ("_tableval_type", InternalTraversal.dp_type),

1883 ("_render_derived", InternalTraversal.dp_boolean),

1884 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1885 ]

1886

1887 def _init(

1888 self,

1889 selectable: Any,

1890 *,

1891 name: Optional[str] = None,

1892 table_value_type: Optional[TableValueType] = None,

1893 joins_implicitly: bool = False,

1894 ) -> None:

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

1896

1897 self.joins_implicitly = joins_implicitly

1898 self._tableval_type = (

1899 type_api.TABLEVALUE

1900 if table_value_type is None

1901 else table_value_type

1902 )

1903

1904 @HasMemoized.memoized_attribute

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

1906 """Return a column expression representing this

1907 :class:`_sql.TableValuedAlias`.

1908

1909 This accessor is used to implement the

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

1911 method for further details.

1912

1913 E.g.:

1914

1915 .. sourcecode:: pycon+sql

1916

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

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

1919

1920 .. seealso::

1921

1922 :meth:`_functions.FunctionElement.column_valued`

1923

1924 """

1925

1926 return TableValuedColumn(self, self._tableval_type)

1927

1928 def alias(

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

1930 ) -> TableValuedAlias:

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

1932

1933 This creates a distinct FROM object that will be distinguished

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

1935

1936 """

1937

1938 tva: TableValuedAlias = TableValuedAlias._construct(

1939 self,

1940 name=name,

1941 table_value_type=self._tableval_type,

1942 joins_implicitly=self.joins_implicitly,

1943 )

1944

1945 if self._render_derived:

1946 tva._render_derived = True

1947 tva._render_derived_w_types = self._render_derived_w_types

1948

1949 return tva

1950

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

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

1953 set, so that it renders as LATERAL.

1954

1955 .. seealso::

1956

1957 :func:`_expression.lateral`

1958

1959 """

1960 tva = self.alias(name=name)

1961 tva._is_lateral = True

1962 return tva

1963

1964 def render_derived(

1965 self,

1966 name: Optional[str] = None,

1967 with_types: bool = False,

1968 ) -> TableValuedAlias:

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

1970

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

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

1973

1974 .. sourcecode:: pycon+sql

1975

1976 >>> print(

1977 ... select(

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

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

1980 ... .render_derived()

1981 ... )

1982 ... )

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

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

1985

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

1987 the alias expression (this syntax currently applies to the

1988 PostgreSQL database):

1989

1990 .. sourcecode:: pycon+sql

1991

1992 >>> print(

1993 ... select(

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

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

1996 ... .render_derived(with_types=True)

1997 ... )

1998 ... )

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

2000 AS anon_1(a INTEGER, b VARCHAR)

2001

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

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

2004

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

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

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

2008

2009 """ # noqa: E501

2010

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

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

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

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

2015 # (just saw it happen on CI)

2016

2017 # construct against original to prevent memory growth

2018 # for repeated generations

2019 new_alias: TableValuedAlias = TableValuedAlias._construct(

2020 self.element,

2021 name=name,

2022 table_value_type=self._tableval_type,

2023 joins_implicitly=self.joins_implicitly,

2024 )

2025 new_alias._render_derived = True

2026 new_alias._render_derived_w_types = with_types

2027 return new_alias

2028

2029

2030class Lateral(FromClauseAlias, LateralFromClause):

2031 """Represent a LATERAL subquery.

2032

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

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

2035 method available

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

2037

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

2039 PostgreSQL versions provide support for this keyword.

2040

2041 .. seealso::

2042

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

2044

2045 """

2046

2047 __visit_name__ = "lateral"

2048 _is_lateral = True

2049

2050 inherit_cache = True

2051

2052 @classmethod

2053 def _factory(

2054 cls,

2055 selectable: Union[SelectBase, _FromClauseArgument],

2056 name: Optional[str] = None,

2057 ) -> LateralFromClause:

2058 return coercions.expect(

2059 roles.FromClauseRole, selectable, explicit_subquery=True

2060 ).lateral(name=name)

2061

2062

2063class TableSample(FromClauseAlias):

2064 """Represent a TABLESAMPLE clause.

2065

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

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

2068 method

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

2070

2071 .. seealso::

2072

2073 :func:`_expression.tablesample`

2074

2075 """

2076

2077 __visit_name__ = "tablesample"

2078

2079 _traverse_internals: _TraverseInternalsType = (

2080 AliasedReturnsRows._traverse_internals

2081 + [

2082 ("sampling", InternalTraversal.dp_clauseelement),

2083 ("seed", InternalTraversal.dp_clauseelement),

2084 ]

2085 )

2086

2087 @classmethod

2088 def _factory(

2089 cls,

2090 selectable: _FromClauseArgument,

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

2092 name: Optional[str] = None,

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

2094 ) -> TableSample:

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

2096 sampling, name=name, seed=seed

2097 )

2098

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

2100 def _init( # type: ignore[override]

2101 self,

2102 selectable: Any,

2103 *,

2104 name: Optional[str] = None,

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

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

2107 ) -> None:

2108 assert sampling is not None

2109 functions = util.preloaded.sql_functions

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

2111 sampling = functions.func.system(sampling)

2112

2113 self.sampling: Function[Any] = sampling

2114 self.seed = seed

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

2116

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

2118 return self.sampling

2119

2120

2121class CTE(

2122 roles.DMLTableRole,

2123 roles.IsCTERole,

2124 Generative,

2125 HasPrefixes,

2126 HasSuffixes,

2127 AliasedReturnsRows,

2128):

2129 """Represent a Common Table Expression.

2130

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

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

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

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

2135 :class:`_sql.Update` and

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

2137 usage details on CTEs.

2138

2139 .. seealso::

2140

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

2142

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

2144

2145 """

2146

2147 __visit_name__ = "cte"

2148

2149 _traverse_internals: _TraverseInternalsType = (

2150 AliasedReturnsRows._traverse_internals

2151 + [

2152 ("_cte_alias", InternalTraversal.dp_clauseelement),

2153 ("_restates", InternalTraversal.dp_clauseelement),

2154 ("recursive", InternalTraversal.dp_boolean),

2155 ("nesting", InternalTraversal.dp_boolean),

2156 ]

2157 + HasPrefixes._has_prefixes_traverse_internals

2158 + HasSuffixes._has_suffixes_traverse_internals

2159 )

2160

2161 element: HasCTE

2162

2163 @classmethod

2164 def _factory(

2165 cls,

2166 selectable: HasCTE,

2167 name: Optional[str] = None,

2168 recursive: bool = False,

2169 ) -> CTE:

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

2171 or Common Table Expression instance.

2172

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

2174

2175 """

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

2177 name=name, recursive=recursive

2178 )

2179

2180 def _init(

2181 self,

2182 selectable: HasCTE,

2183 *,

2184 name: Optional[str] = None,

2185 recursive: bool = False,

2186 nesting: bool = False,

2187 _cte_alias: Optional[CTE] = None,

2188 _restates: Optional[CTE] = None,

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

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

2191 ) -> None:

2192 self.recursive = recursive

2193 self.nesting = nesting

2194 self._cte_alias = _cte_alias

2195 # Keep recursivity reference with union/union_all

2196 self._restates = _restates

2197 if _prefixes:

2198 self._prefixes = _prefixes

2199 if _suffixes:

2200 self._suffixes = _suffixes

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

2202

2203 def _populate_column_collection(

2204 self,

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

2206 primary_key: ColumnSet,

2207 foreign_keys: Set[KeyedColumnElement[Any]],

2208 ) -> None:

2209 if self._cte_alias is not None:

2210 self._cte_alias._generate_fromclause_column_proxies(

2211 self,

2212 columns,

2213 primary_key=primary_key,

2214 foreign_keys=foreign_keys,

2215 )

2216 else:

2217 self.element._generate_fromclause_column_proxies(

2218 self,

2219 columns,

2220 primary_key=primary_key,

2221 foreign_keys=foreign_keys,

2222 )

2223

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

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

2226 :class:`_expression.CTE`.

2227

2228 This method is a CTE-specific specialization of the

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

2230

2231 .. seealso::

2232

2233 :ref:`tutorial_using_aliases`

2234

2235 :func:`_expression.alias`

2236

2237 """

2238 return CTE._construct(

2239 self.element,

2240 name=name,

2241 recursive=self.recursive,

2242 nesting=self.nesting,

2243 _cte_alias=self,

2244 _prefixes=self._prefixes,

2245 _suffixes=self._suffixes,

2246 )

2247

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

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

2250 of the original CTE against the given selectables provided

2251 as positional arguments.

2252

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

2254 UNION.

2255

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

2257

2258 .. seealso::

2259

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

2261

2262 """

2263 assert is_select_statement(

2264 self.element

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

2266

2267 return CTE._construct(

2268 self.element.union(*other),

2269 name=self.name,

2270 recursive=self.recursive,

2271 nesting=self.nesting,

2272 _restates=self,

2273 _prefixes=self._prefixes,

2274 _suffixes=self._suffixes,

2275 )

2276

2277 def union_all(

2278 self, *other: _SelectStatementForCompoundArgument[Any]

2279 ) -> CTE:

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

2281 of the original CTE against the given selectables provided

2282 as positional arguments.

2283

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

2285 UNION.

2286

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

2288

2289 .. seealso::

2290

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

2292

2293 """

2294

2295 assert is_select_statement(

2296 self.element

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

2298

2299 return CTE._construct(

2300 self.element.union_all(*other),

2301 name=self.name,

2302 recursive=self.recursive,

2303 nesting=self.nesting,

2304 _restates=self,

2305 _prefixes=self._prefixes,

2306 _suffixes=self._suffixes,

2307 )

2308

2309 def _get_reference_cte(self) -> CTE:

2310 """

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

2312 Updated CTEs should still refer to the original CTE.

2313 This function returns this reference identifier.

2314 """

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

2316

2317

2318class _CTEOpts(NamedTuple):

2319 nesting: bool

2320

2321

2322class _ColumnsPlusNames(NamedTuple):

2323 required_label_name: Optional[str]

2324 """

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

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

2327 """

2328

2329 proxy_key: Optional[str]

2330 """

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

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

2333 select.selected_columns collection

2334 """

2335

2336 fallback_label_name: Optional[str]

2337 """

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

2339 we have to render a label even though

2340 required_label_name was not given

2341 """

2342

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

2344 """

2345 the ColumnElement itself

2346 """

2347

2348 repeated: bool

2349 """

2350 True if this is a duplicate of a previous column

2351 in the list of columns

2352 """

2353

2354

2355class SelectsRows(ReturnsRows):

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

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

2358

2359 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2360

2361 def _generate_columns_plus_names(

2362 self,

2363 anon_for_dupe_key: bool,

2364 cols: Optional[_SelectIterable] = None,

2365 ) -> List[_ColumnsPlusNames]:

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

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

2368 on a :class:`.FromClause`.

2369

2370 This is distinct from the _column_naming_convention generator that's

2371 intended for population of the Select.selected_columns collection,

2372 different rules. the collection returned here calls upon the

2373 _column_naming_convention as well.

2374

2375 """

2376

2377 if cols is None:

2378 cols = self._all_selected_columns

2379

2380 key_naming_convention = SelectState._column_naming_convention(

2381 self._label_style

2382 )

2383

2384 names = {}

2385

2386 result: List[_ColumnsPlusNames] = []

2387 result_append = result.append

2388

2389 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2390 label_style_none = self._label_style is LABEL_STYLE_NONE

2391

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

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

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

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

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

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

2398 dedupe_hash = 1

2399

2400 for c in cols:

2401 repeated = False

2402

2403 if not c._render_label_in_columns_clause:

2404 effective_name = required_label_name = fallback_label_name = (

2405 None

2406 )

2407 elif label_style_none:

2408 if TYPE_CHECKING:

2409 assert is_column_element(c)

2410

2411 effective_name = required_label_name = None

2412 fallback_label_name = c._non_anon_label or c._anon_name_label

2413 else:

2414 if TYPE_CHECKING:

2415 assert is_column_element(c)

2416

2417 if table_qualified:

2418 required_label_name = effective_name = (

2419 fallback_label_name

2420 ) = c._tq_label

2421 else:

2422 effective_name = fallback_label_name = c._non_anon_label

2423 required_label_name = None

2424

2425 if effective_name is None:

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

2427 # not need _expression_label but it isn't

2428 # giving us a clue when to use anon_label instead

2429 expr_label = c._expression_label

2430 if expr_label is None:

2431 repeated = c._anon_name_label in names

2432 names[c._anon_name_label] = c

2433 effective_name = required_label_name = None

2434

2435 if repeated:

2436 # here, "required_label_name" is sent as

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

2438 if table_qualified:

2439 fallback_label_name = (

2440 c._dedupe_anon_tq_label_idx(dedupe_hash)

2441 )

2442 dedupe_hash += 1

2443 else:

2444 fallback_label_name = c._dedupe_anon_label_idx(

2445 dedupe_hash

2446 )

2447 dedupe_hash += 1

2448 else:

2449 fallback_label_name = c._anon_name_label

2450 else:

2451 required_label_name = effective_name = (

2452 fallback_label_name

2453 ) = expr_label

2454

2455 if effective_name is not None:

2456 if TYPE_CHECKING:

2457 assert is_column_element(c)

2458

2459 if effective_name in names:

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

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

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

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

2464 # different column under the same name. apply

2465 # disambiguating label

2466 if table_qualified:

2467 required_label_name = fallback_label_name = (

2468 c._anon_tq_label

2469 )

2470 else:

2471 required_label_name = fallback_label_name = (

2472 c._anon_name_label

2473 )

2474

2475 if anon_for_dupe_key and required_label_name in names:

2476 # here, c._anon_tq_label is definitely unique to

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

2478 # this should always be true.

2479 # this is also an infrequent codepath because

2480 # you need two levels of duplication to be here

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

2482

2483 # the column under the disambiguating label is

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

2485 # subsequent occurrences of the column so that the

2486 # original stays non-ambiguous

2487 if table_qualified:

2488 required_label_name = fallback_label_name = (

2489 c._dedupe_anon_tq_label_idx(dedupe_hash)

2490 )

2491 dedupe_hash += 1

2492 else:

2493 required_label_name = fallback_label_name = (

2494 c._dedupe_anon_label_idx(dedupe_hash)

2495 )

2496 dedupe_hash += 1

2497 repeated = True

2498 else:

2499 names[required_label_name] = c

2500 elif anon_for_dupe_key:

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

2502 # label so that the original stays non-ambiguous

2503 if table_qualified:

2504 required_label_name = fallback_label_name = (

2505 c._dedupe_anon_tq_label_idx(dedupe_hash)

2506 )

2507 dedupe_hash += 1

2508 else:

2509 required_label_name = fallback_label_name = (

2510 c._dedupe_anon_label_idx(dedupe_hash)

2511 )

2512 dedupe_hash += 1

2513 repeated = True

2514 else:

2515 names[effective_name] = c

2516

2517 result_append(

2518 _ColumnsPlusNames(

2519 required_label_name,

2520 key_naming_convention(c),

2521 fallback_label_name,

2522 c,

2523 repeated,

2524 )

2525 )

2526

2527 return result

2528

2529

2530class HasCTE(roles.HasCTERole, SelectsRows):

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

2532

2533 _has_ctes_traverse_internals: _TraverseInternalsType = [

2534 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2535 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2536 ]

2537

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

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

2540

2541 name_cte_columns: bool = False

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

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

2544

2545 .. versionadded:: 2.0.42

2546

2547 """

2548

2549 @_generative

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

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

2552

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

2554 the parent statement such that they will each be unconditionally

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

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

2557

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

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

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

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

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

2563 statement.

2564

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

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

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

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

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

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

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

2572 larger statement.

2573

2574 E.g.::

2575

2576 from sqlalchemy import table, column, select

2577

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

2579

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

2581

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

2583

2584 Would render:

2585

2586 .. sourcecode:: sql

2587

2588 WITH anon_1 AS (

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

2590 )

2591 SELECT t.c1, t.c2

2592 FROM t

2593

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

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

2596 statement.

2597

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

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

2600

2601 from sqlalchemy import table, column

2602 from sqlalchemy.dialects.postgresql import insert

2603

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

2605

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

2607

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

2609 update_statement = insert_stmt.on_conflict_do_update(

2610 index_elements=[t.c.c1],

2611 set_={

2612 "c1": insert_stmt.excluded.c1,

2613 "c2": insert_stmt.excluded.c2,

2614 },

2615 ).add_cte(delete_statement_cte)

2616

2617 print(update_statement)

2618

2619 The above statement renders as:

2620

2621 .. sourcecode:: sql

2622

2623 WITH deletions AS (

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

2625 )

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

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

2628

2629 .. versionadded:: 1.4.21

2630

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

2632

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

2634

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

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

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

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

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

2640 this statement when this flag is given.

2641

2642 .. versionadded:: 2.0

2643

2644 .. seealso::

2645

2646 :paramref:`.HasCTE.cte.nesting`

2647

2648

2649 """ # noqa: E501

2650 opt = _CTEOpts(nest_here)

2651 for cte in ctes:

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

2653 self._independent_ctes += (cte,)

2654 self._independent_ctes_opts += (opt,)

2655 return self

2656

2657 def cte(

2658 self,

2659 name: Optional[str] = None,

2660 recursive: bool = False,

2661 nesting: bool = False,

2662 ) -> CTE:

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

2664 or Common Table Expression instance.

2665

2666 Common table expressions are a SQL standard whereby SELECT

2667 statements can draw upon secondary statements specified along

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

2669 Special semantics regarding UNION can also be employed to

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

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

2672

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

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

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

2676 CTE rows.

2677

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

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

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

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

2682

2683 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2685 method may be

2686 used to establish these.

2687

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

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

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

2691 compile time.

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

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

2694 conjunction with UNION ALL in order to derive rows

2695 from those already selected.

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

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

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

2699 :paramref:`.HasCTE.add_cte.nest_here`

2700 parameter may also be used to more carefully

2701 control the exact placement of a particular CTE.

2702

2703 .. versionadded:: 1.4.24

2704

2705 .. seealso::

2706

2707 :meth:`.HasCTE.add_cte`

2708

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

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

2711 as well as additional examples.

2712

2713 Example 1, non recursive::

2714

2715 from sqlalchemy import (

2716 Table,

2717 Column,

2718 String,

2719 Integer,

2720 MetaData,

2721 select,

2722 func,

2723 )

2724

2725 metadata = MetaData()

2726

2727 orders = Table(

2728 "orders",

2729 metadata,

2730 Column("region", String),

2731 Column("amount", Integer),

2732 Column("product", String),

2733 Column("quantity", Integer),

2734 )

2735

2736 regional_sales = (

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

2738 .group_by(orders.c.region)

2739 .cte("regional_sales")

2740 )

2741

2742

2743 top_regions = (

2744 select(regional_sales.c.region)

2745 .where(

2746 regional_sales.c.total_sales

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

2748 )

2749 .cte("top_regions")

2750 )

2751

2752 statement = (

2753 select(

2754 orders.c.region,

2755 orders.c.product,

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

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

2758 )

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

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

2761 )

2762

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

2764

2765 Example 2, WITH RECURSIVE::

2766

2767 from sqlalchemy import (

2768 Table,

2769 Column,

2770 String,

2771 Integer,

2772 MetaData,

2773 select,

2774 func,

2775 )

2776

2777 metadata = MetaData()

2778

2779 parts = Table(

2780 "parts",

2781 metadata,

2782 Column("part", String),

2783 Column("sub_part", String),

2784 Column("quantity", Integer),

2785 )

2786

2787 included_parts = (

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

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

2790 .cte(recursive=True)

2791 )

2792

2793

2794 incl_alias = included_parts.alias()

2795 parts_alias = parts.alias()

2796 included_parts = included_parts.union_all(

2797 select(

2798 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2800 )

2801

2802 statement = select(

2803 included_parts.c.sub_part,

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

2805 ).group_by(included_parts.c.sub_part)

2806

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

2808

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

2810

2811 from datetime import date

2812 from sqlalchemy import (

2813 MetaData,

2814 Table,

2815 Column,

2816 Integer,

2817 Date,

2818 select,

2819 literal,

2820 and_,

2821 exists,

2822 )

2823

2824 metadata = MetaData()

2825

2826 visitors = Table(

2827 "visitors",

2828 metadata,

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

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

2831 Column("count", Integer),

2832 )

2833

2834 # add 5 visitors for the product_id == 1

2835 product_id = 1

2836 day = date.today()

2837 count = 5

2838

2839 update_cte = (

2840 visitors.update()

2841 .where(

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

2843 )

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

2845 .returning(literal(1))

2846 .cte("update_cte")

2847 )

2848

2849 upsert = visitors.insert().from_select(

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

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

2852 ~exists(update_cte.select())

2853 ),

2854 )

2855

2856 connection.execute(upsert)

2857

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

2859

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

2861

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

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

2864 "value_a", nesting=True

2865 )

2866

2867 # Nesting CTEs takes ascendency locally

2868 # over the CTEs at a higher level

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

2870

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

2872

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

2874 shown with inline parameters below as:

2875

2876 .. sourcecode:: sql

2877

2878 WITH

2879 value_a AS

2880 (SELECT 'root' AS n),

2881 value_b AS

2882 (WITH value_a AS

2883 (SELECT 'nesting' AS n)

2884 SELECT value_a.n AS n FROM value_a)

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

2886 FROM value_a, value_b

2887

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

2889 as follows (SQLAlchemy 2.0 and above)::

2890

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

2892

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

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

2895

2896 # Nesting CTEs takes ascendency locally

2897 # over the CTEs at a higher level

2898 value_b = (

2899 select(value_a_nested.c.n)

2900 .add_cte(value_a_nested, nest_here=True)

2901 .cte("value_b")

2902 )

2903

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

2905

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

2907

2908 edge = Table(

2909 "edge",

2910 metadata,

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

2912 Column("left", Integer),

2913 Column("right", Integer),

2914 )

2915

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

2917

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

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

2920 )

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

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

2923 )

2924

2925 subgraph_cte = root_node.union(left_edge, right_edge)

2926

2927 subgraph = select(subgraph_cte)

2928

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

2930

2931 .. sourcecode:: sql

2932

2933 WITH RECURSIVE nodes(node) AS (

2934 SELECT 1 AS node

2935 UNION

2936 SELECT edge."left" AS "left"

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

2938 UNION

2939 SELECT edge."right" AS "right"

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

2941 )

2942 SELECT nodes.node FROM nodes

2943

2944 .. seealso::

2945

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

2947 :meth:`_expression.HasCTE.cte`.

2948

2949 """ # noqa: E501

2950 return CTE._construct(

2951 self, name=name, recursive=recursive, nesting=nesting

2952 )

2953

2954

2955class Subquery(AliasedReturnsRows):

2956 """Represent a subquery of a SELECT.

2957

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

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

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

2961 :class:`_expression.SelectBase` subclass

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

2963 :class:`_expression.CompoundSelect`, and

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

2965 it represents the

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

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

2968

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

2970 :class:`_expression.Alias`

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

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

2973 :class:`_expression.Alias` always

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

2975 :class:`.Subquery`

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

2977

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

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

2980 statement.

2981

2982 """

2983

2984 __visit_name__ = "subquery"

2985

2986 _is_subquery = True

2987

2988 inherit_cache = True

2989

2990 element: SelectBase

2991

2992 @classmethod

2993 def _factory(

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

2995 ) -> Subquery:

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

2997

2998 return coercions.expect(

2999 roles.SelectStatementRole, selectable

3000 ).subquery(name=name)

3001

3002 @util.deprecated(

3003 "1.4",

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

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

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

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

3008 ":func:`_expression.select` "

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

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

3011 )

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

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

3014

3015

3016class FromGrouping(GroupedElement, FromClause):

3017 """Represent a grouping of a FROM clause"""

3018

3019 _traverse_internals: _TraverseInternalsType = [

3020 ("element", InternalTraversal.dp_clauseelement)

3021 ]

3022

3023 element: FromClause

3024

3025 def __init__(self, element: FromClause):

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

3027

3028 @util.ro_non_memoized_property

3029 def columns(

3030 self,

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

3032 return self.element.columns

3033

3034 @util.ro_non_memoized_property

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

3036 return self.element.columns

3037

3038 @property

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

3040 return self.element.primary_key

3041

3042 @property

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

3044 return self.element.foreign_keys

3045

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

3047 return self.element.is_derived_from(fromclause)

3048

3049 def alias(

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

3051 ) -> NamedFromGrouping:

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

3053

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

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

3056

3057 @util.ro_non_memoized_property

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

3059 return self.element._hide_froms

3060

3061 @util.ro_non_memoized_property

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

3063 return self.element._from_objects

3064

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

3066 return {"element": self.element}

3067

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

3069 self.element = state["element"]

3070

3071 if TYPE_CHECKING:

3072

3073 def self_group(

3074 self, against: Optional[OperatorType] = None

3075 ) -> Self: ...

3076

3077

3078class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3080

3081 .. versionadded:: 2.0

3082

3083 """

3084

3085 inherit_cache = True

3086

3087 if TYPE_CHECKING:

3088

3089 def self_group(

3090 self, against: Optional[OperatorType] = None

3091 ) -> Self: ...

3092

3093

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

3095 """Represents a minimal "table" construct.

3096

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

3098 collection of columns, which are typically produced

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

3100

3101 from sqlalchemy import table, column

3102

3103 user = table(

3104 "user",

3105 column("id"),

3106 column("name"),

3107 column("description"),

3108 )

3109

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

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

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

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

3114

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

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

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

3118 It's useful

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

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

3121 is not on hand.

3122

3123 """

3124

3125 __visit_name__ = "table"

3126

3127 _traverse_internals: _TraverseInternalsType = [

3128 (

3129 "columns",

3130 InternalTraversal.dp_fromclause_canonical_column_collection,

3131 ),

3132 ("name", InternalTraversal.dp_string),

3133 ("schema", InternalTraversal.dp_string),

3134 ]

3135

3136 _is_table = True

3137

3138 fullname: str

3139

3140 implicit_returning = False

3141 """:class:`_expression.TableClause`

3142 doesn't support having a primary key or column

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

3144

3145 _columns: DedupeColumnCollection[ColumnClause[Any]]

3146

3147 @util.ro_memoized_property

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

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

3150 return None

3151

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

3153 super().__init__()

3154 self.name = name

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

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

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

3158 for c in columns:

3159 self.append_column(c)

3160

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

3162 if schema is not None:

3163 self.schema = schema

3164 if self.schema is not None:

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

3166 else:

3167 self.fullname = self.name

3168 if kw:

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

3170

3171 if TYPE_CHECKING:

3172

3173 @util.ro_non_memoized_property

3174 def columns(

3175 self,

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

3177

3178 @util.ro_non_memoized_property

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

3180

3181 def __str__(self) -> str:

3182 if self.schema is not None:

3183 return self.schema + "." + self.name

3184 else:

3185 return self.name

3186

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

3188 pass

3189

3190 @util.ro_memoized_property

3191 def description(self) -> str:

3192 return self.name

3193

3194 def _insert_col_impl(

3195 self,

3196 c: ColumnClause[Any],

3197 *,

3198 index: Optional[int] = None,

3199 ) -> None:

3200 existing = c.table

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

3202 raise exc.ArgumentError(

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

3204 % (c.key, existing)

3205 )

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

3207 c.table = self

3208

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

3210 self._insert_col_impl(c)

3211

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

3213 self._insert_col_impl(c, index=index)

3214

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

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

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

3218 :class:`_expression.TableClause`.

3219

3220 E.g.::

3221

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

3223

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

3225

3226 """

3227

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

3229

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

3231 def update(self) -> Update:

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

3233 :class:`_expression.TableClause`.

3234

3235 E.g.::

3236

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

3238

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

3240

3241 """

3242 return util.preloaded.sql_dml.Update(

3243 self,

3244 )

3245

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

3247 def delete(self) -> Delete:

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

3249 :class:`_expression.TableClause`.

3250

3251 E.g.::

3252

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

3254

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

3256

3257 """

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

3259

3260 @util.ro_non_memoized_property

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

3262 return [self]

3263

3264

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

3266

3267

3268class ForUpdateArg(ClauseElement):

3269 _traverse_internals: _TraverseInternalsType = [

3270 ("of", InternalTraversal.dp_clauseelement_list),

3271 ("nowait", InternalTraversal.dp_boolean),

3272 ("read", InternalTraversal.dp_boolean),

3273 ("skip_locked", InternalTraversal.dp_boolean),

3274 ("key_share", InternalTraversal.dp_boolean),

3275 ]

3276

3277 of: Optional[Sequence[ClauseElement]]

3278 nowait: bool

3279 read: bool

3280 skip_locked: bool

3281

3282 @classmethod

3283 def _from_argument(

3284 cls, with_for_update: ForUpdateParameter

3285 ) -> Optional[ForUpdateArg]:

3286 if isinstance(with_for_update, ForUpdateArg):

3287 return with_for_update

3288 elif with_for_update in (None, False):

3289 return None

3290 elif with_for_update is True:

3291 return ForUpdateArg()

3292 else:

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

3294

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

3296 return (

3297 isinstance(other, ForUpdateArg)

3298 and other.nowait == self.nowait

3299 and other.read == self.read

3300 and other.skip_locked == self.skip_locked

3301 and other.key_share == self.key_share

3302 and other.of is self.of

3303 )

3304

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

3306 return not self.__eq__(other)

3307

3308 def __hash__(self) -> int:

3309 return id(self)

3310

3311 def __init__(

3312 self,

3313 *,

3314 nowait: bool = False,

3315 read: bool = False,

3316 of: Optional[_ForUpdateOfArgument] = None,

3317 skip_locked: bool = False,

3318 key_share: bool = False,

3319 ):

3320 """Represents arguments specified to

3321 :meth:`_expression.Select.for_update`.

3322

3323 """

3324

3325 self.nowait = nowait

3326 self.read = read

3327 self.skip_locked = skip_locked

3328 self.key_share = key_share

3329 if of is not None:

3330 self.of = [

3331 coercions.expect(roles.ColumnsClauseRole, elem)

3332 for elem in util.to_list(of)

3333 ]

3334 else:

3335 self.of = None

3336

3337

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

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

3340 in a statement.

3341

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

3343 :func:`_expression.values` function.

3344

3345 .. versionadded:: 1.4

3346

3347 """

3348

3349 __visit_name__ = "values"

3350

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

3352

3353 _unnamed: bool

3354 _traverse_internals: _TraverseInternalsType = [

3355 ("_column_args", InternalTraversal.dp_clauseelement_list),

3356 ("_data", InternalTraversal.dp_dml_multi_values),

3357 ("name", InternalTraversal.dp_string),

3358 ("literal_binds", InternalTraversal.dp_boolean),

3359 ] + HasCTE._has_ctes_traverse_internals

3360

3361 name_cte_columns = True

3362

3363 def __init__(

3364 self,

3365 *columns: ColumnClause[Any],

3366 name: Optional[str] = None,

3367 literal_binds: bool = False,

3368 ):

3369 super().__init__()

3370 self._column_args = columns

3371

3372 if name is None:

3373 self._unnamed = True

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

3375 else:

3376 self._unnamed = False

3377 self.name = name

3378 self.literal_binds = literal_binds

3379 self.named_with_column = not self._unnamed

3380

3381 @property

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

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

3384

3385 @util.ro_non_memoized_property

3386 def _all_selected_columns(self) -> _SelectIterable:

3387 return self._column_args

3388

3389 @_generative

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

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

3392 construct that is a copy of this

3393 one with the given name.

3394

3395 This method is a VALUES-specific specialization of the

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

3397

3398 .. seealso::

3399

3400 :ref:`tutorial_using_aliases`

3401

3402 :func:`_expression.alias`

3403

3404 """

3405 non_none_name: str

3406

3407 if name is None:

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

3409 else:

3410 non_none_name = name

3411

3412 self.name = non_none_name

3413 self.named_with_column = True

3414 self._unnamed = False

3415 return self

3416

3417 @_generative

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

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

3420 so that

3421 it renders as LATERAL.

3422

3423 .. seealso::

3424

3425 :func:`_expression.lateral`

3426

3427 """

3428 non_none_name: str

3429

3430 if name is None:

3431 non_none_name = self.name

3432 else:

3433 non_none_name = name

3434

3435 self._is_lateral = True

3436 self.name = non_none_name

3437 self._unnamed = False

3438 return self

3439

3440 @_generative

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

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

3443 adding the given data to the data list.

3444

3445 E.g.::

3446

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

3448

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

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

3451 constructor.

3452

3453 """

3454

3455 self._data += (values,)

3456 return self

3457

3458 def scalar_values(self) -> ScalarValues:

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

3460 COLUMN element in a statement.

3461

3462 .. versionadded:: 2.0.0b4

3463

3464 """

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

3466

3467 def _populate_column_collection(

3468 self,

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

3470 primary_key: ColumnSet,

3471 foreign_keys: Set[KeyedColumnElement[Any]],

3472 ) -> None:

3473 for c in self._column_args:

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

3475 _, c = c._make_proxy(

3476 self, primary_key=primary_key, foreign_keys=foreign_keys

3477 )

3478 else:

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

3480 # no memoizations of other FROM clauses.

3481 # see test_values.py -> test_auto_proxy_select_direct_col

3482 c._reset_memoizations()

3483 columns.add(c)

3484 c.table = self

3485

3486 @util.ro_non_memoized_property

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

3488 return [self]

3489

3490

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

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

3493 COLUMN element in a statement.

3494

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

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

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

3498 an ``IN`` or ``NOT IN`` condition.

3499

3500 .. versionadded:: 2.0.0b4

3501

3502 """

3503

3504 __visit_name__ = "scalar_values"

3505

3506 _traverse_internals: _TraverseInternalsType = [

3507 ("_column_args", InternalTraversal.dp_clauseelement_list),

3508 ("_data", InternalTraversal.dp_dml_multi_values),

3509 ("literal_binds", InternalTraversal.dp_boolean),

3510 ]

3511

3512 def __init__(

3513 self,

3514 columns: Sequence[ColumnClause[Any]],

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

3516 literal_binds: bool,

3517 ):

3518 super().__init__()

3519 self._column_args = columns

3520 self._data = data

3521 self.literal_binds = literal_binds

3522

3523 @property

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

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

3526

3527 def __clause_element__(self) -> ScalarValues:

3528 return self

3529

3530 if TYPE_CHECKING:

3531

3532 def self_group(

3533 self, against: Optional[OperatorType] = None

3534 ) -> Self: ...

3535

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

3537

3538

3539class SelectBase(

3540 roles.SelectStatementRole,

3541 roles.DMLSelectRole,

3542 roles.CompoundElementRole,

3543 roles.InElementRole,

3544 HasCTE,

3545 SupportsCloneAnnotations,

3546 Selectable,

3547):

3548 """Base class for SELECT statements.

3549

3550

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

3552 :class:`_expression.CompoundSelect` and

3553 :class:`_expression.TextualSelect`.

3554

3555

3556 """

3557

3558 _is_select_base = True

3559 is_select = True

3560

3561 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3562

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

3564 self._reset_memoizations()

3565

3566 @util.ro_non_memoized_property

3567 def selected_columns(

3568 self,

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

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

3571 representing the columns that

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

3573

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

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

3576 within this collection cannot be directly nested inside another SELECT

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

3578 necessary parenthesization required by SQL.

3579

3580 .. note::

3581

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

3583 include expressions established in the columns clause using the

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

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

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

3587 construct.

3588

3589 .. seealso::

3590

3591 :attr:`_sql.Select.selected_columns`

3592

3593 .. versionadded:: 1.4

3594

3595 """

3596 raise NotImplementedError()

3597

3598 def _generate_fromclause_column_proxies(

3599 self,

3600 subquery: FromClause,

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

3602 primary_key: ColumnSet,

3603 foreign_keys: Set[KeyedColumnElement[Any]],

3604 *,

3605 proxy_compound_columns: Optional[

3606 Iterable[Sequence[ColumnElement[Any]]]

3607 ] = None,

3608 ) -> None:

3609 raise NotImplementedError()

3610

3611 @util.ro_non_memoized_property

3612 def _all_selected_columns(self) -> _SelectIterable:

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

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

3615 constructs.

3616

3617 .. versionadded:: 1.4.12

3618

3619 .. seealso::

3620

3621 :attr:`_sql.SelectBase.exported_columns`

3622

3623 """

3624 raise NotImplementedError()

3625

3626 @property

3627 def exported_columns(

3628 self,

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

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

3631 that represents the "exported"

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

3633 :class:`_sql.TextClause` constructs.

3634

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

3636 object are synonymous

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

3638

3639 .. versionadded:: 1.4

3640

3641 .. seealso::

3642

3643 :attr:`_expression.Select.exported_columns`

3644

3645 :attr:`_expression.Selectable.exported_columns`

3646

3647 :attr:`_expression.FromClause.exported_columns`

3648

3649

3650 """

3651 return self.selected_columns.as_readonly()

3652

3653 def get_label_style(self) -> SelectLabelStyle:

3654 """

3655 Retrieve the current label style.

3656

3657 Implemented by subclasses.

3658

3659 """

3660 raise NotImplementedError()

3661

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

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

3664

3665 Implemented by subclasses.

3666

3667 """

3668

3669 raise NotImplementedError()

3670

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

3672 raise NotImplementedError()

3673

3674 @util.deprecated(

3675 "1.4",

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

3677 "method is deprecated and will be "

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

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

3680 )

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

3682 return self.scalar_subquery()

3683

3684 def exists(self) -> Exists:

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

3686 which can be used as a column expression.

3687

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

3689

3690 .. seealso::

3691

3692 :func:`_sql.exists`

3693

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

3695

3696 .. versionadded:: 1.4

3697

3698 """

3699 return Exists(self)

3700

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

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

3703 used as a column expression.

3704

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

3706

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

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

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

3710 an enclosing SELECT.

3711

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

3713 subquery that can be produced using the

3714 :meth:`_expression.SelectBase.subquery`

3715 method.

3716

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

3718 :meth:`_expression.SelectBase.scalar_subquery`.

3719

3720 .. seealso::

3721

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

3723

3724 """

3725 if self._label_style is not LABEL_STYLE_NONE:

3726 self = self.set_label_style(LABEL_STYLE_NONE)

3727

3728 return ScalarSelect(self)

3729

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

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

3732 subquery with a label.

3733

3734 .. seealso::

3735

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

3737

3738 """

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

3740

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

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

3743

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

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

3746

3747 .. seealso::

3748

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

3750

3751 """

3752 return Lateral._factory(self, name)

3753

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

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

3756

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

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

3759 SELECT statement.

3760

3761 Given a SELECT statement such as::

3762

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

3764

3765 The above statement might look like:

3766

3767 .. sourcecode:: sql

3768

3769 SELECT table.id, table.name FROM table

3770

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

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

3773 a named sub-element::

3774

3775 subq = stmt.subquery()

3776 new_stmt = select(subq)

3777

3778 The above renders as:

3779

3780 .. sourcecode:: sql

3781

3782 SELECT anon_1.id, anon_1.name

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

3784

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

3786 is equivalent to calling

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

3788 method on a FROM object; however,

3789 as a :class:`_expression.SelectBase`

3790 object is not directly FROM object,

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

3792 method provides clearer semantics.

3793

3794 .. versionadded:: 1.4

3795

3796 """

3797

3798 return Subquery._construct(

3799 self._ensure_disambiguated_names(), name=name

3800 )

3801

3802 @util.preload_module("sqlalchemy.sql.ddl")

3803 def into(

3804 self,

3805 target: str,

3806 *,

3807 metadata: Optional["MetaData"] = None,

3808 schema: Optional[str] = None,

3809 temporary: bool = False,

3810 if_not_exists: bool = False,

3811 ) -> CreateTableAs:

3812 """Create a :class:`_schema.CreateTableAs` construct from this SELECT.

3813

3814 This method provides a convenient way to create a ``CREATE TABLE ...

3815 AS`` statement from a SELECT, as well as compound SELECTs like UNION.

3816 The new table will be created with columns matching the SELECT list.

3817

3818 Supported on all included backends, the construct emits

3819 ``CREATE TABLE...AS`` for all backends except SQL Server, which instead

3820 emits a ``SELECT..INTO`` statement.

3821

3822 e.g.::

3823

3824 from sqlalchemy import select

3825

3826 # Create a new table from a SELECT

3827 stmt = (

3828 select(users.c.id, users.c.name)

3829 .where(users.c.status == "active")

3830 .into("active_users")

3831 )

3832

3833 with engine.begin() as conn:

3834 conn.execute(stmt)

3835

3836 # With optional flags

3837 stmt = (

3838 select(users.c.id)

3839 .where(users.c.status == "inactive")

3840 .into("inactive_users", schema="analytics", if_not_exists=True)

3841 )

3842

3843 .. versionadded:: 2.1

3844

3845 :param target: Name of the table to create as a string. Must be

3846 unqualified; use the ``schema`` parameter for qualification.

3847

3848 :param metadata: :class:`_schema.MetaData`, optional

3849 If provided, the :class:`_schema.Table` object available via the

3850 :attr:`.CreateTableAs.table` attribute will be associated with this

3851 :class:`.MetaData`. Otherwise, a new, empty :class:`.MetaData`

3852 is created.

3853

3854 :param schema: Optional schema name for the new table.

3855

3856 :param temporary: If True, create a temporary table where supported

3857

3858 :param if_not_exists: If True, add IF NOT EXISTS clause where supported

3859

3860 :return: A :class:`_schema.CreateTableAs` construct.

3861

3862 .. seealso::

3863

3864 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3865

3866 :class:`_schema.CreateTableAs`

3867

3868 """

3869 sql_ddl = util.preloaded.sql_ddl

3870

3871 return sql_ddl.CreateTableAs(

3872 self,

3873 target,

3874 metadata=metadata,

3875 schema=schema,

3876 temporary=temporary,

3877 if_not_exists=if_not_exists,

3878 )

3879

3880 def _ensure_disambiguated_names(self) -> Self:

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

3882 disambiguated in some way, if possible.

3883

3884 """

3885

3886 raise NotImplementedError()

3887

3888 def alias(

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

3890 ) -> Subquery:

3891 """Return a named subquery against this

3892 :class:`_expression.SelectBase`.

3893

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

3895 :class:`_expression.FromClause`),

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

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

3898 :class:`_expression.FromClause`.

3899

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

3901 method is now

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

3903

3904 """

3905 return self.subquery(name=name)

3906

3907

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

3909

3910

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

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

3913

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

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

3916 compound selects.

3917

3918 """

3919

3920 __visit_name__ = "select_statement_grouping"

3921 _traverse_internals: _TraverseInternalsType = [

3922 ("element", InternalTraversal.dp_clauseelement)

3923 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3924

3925 _is_select_container = True

3926

3927 element: _SB

3928

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

3930 self.element = cast(

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

3932 )

3933

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

3935 new_element = self.element._ensure_disambiguated_names()

3936 if new_element is not self.element:

3937 return SelectStatementGrouping(new_element)

3938 else:

3939 return self

3940

3941 def get_label_style(self) -> SelectLabelStyle:

3942 return self.element.get_label_style()

3943

3944 def set_label_style(

3945 self, label_style: SelectLabelStyle

3946 ) -> SelectStatementGrouping[_SB]:

3947 return SelectStatementGrouping(

3948 self.element.set_label_style(label_style)

3949 )

3950

3951 @property

3952 def select_statement(self) -> _SB:

3953 return self.element

3954

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

3956 return self

3957

3958 if TYPE_CHECKING:

3959

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

3961

3962 # def _generate_columns_plus_names(

3963 # self, anon_for_dupe_key: bool

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

3965 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3966

3967 def _generate_fromclause_column_proxies(

3968 self,

3969 subquery: FromClause,

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

3971 primary_key: ColumnSet,

3972 foreign_keys: Set[KeyedColumnElement[Any]],

3973 *,

3974 proxy_compound_columns: Optional[

3975 Iterable[Sequence[ColumnElement[Any]]]

3976 ] = None,

3977 ) -> None:

3978 self.element._generate_fromclause_column_proxies(

3979 subquery,

3980 columns,

3981 proxy_compound_columns=proxy_compound_columns,

3982 primary_key=primary_key,

3983 foreign_keys=foreign_keys,

3984 )

3985

3986 @util.ro_non_memoized_property

3987 def _all_selected_columns(self) -> _SelectIterable:

3988 return self.element._all_selected_columns

3989

3990 @util.ro_non_memoized_property

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

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

3993 representing the columns that

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

3995 :class:`_sql.TextClause` constructs.

3996

3997 .. versionadded:: 1.4

3998

3999 .. seealso::

4000

4001 :attr:`_sql.Select.selected_columns`

4002

4003 """

4004 return self.element.selected_columns

4005

4006 @util.ro_non_memoized_property

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

4008 return self.element._from_objects

4009

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

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

4012 raise NotImplementedError

4013

4014

4015class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4017 added.

4018

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

4020 :class:`_expression.CompoundSelect`

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

4022 rendering can be controlled. Compare to

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

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

4025 and is also a SELECT construct,

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

4027 only wrapped as a subquery.

4028

4029 """

4030

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

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

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

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

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

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

4037 _for_update_arg: Optional[ForUpdateArg] = None

4038

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

4040 self._label_style = _label_style

4041

4042 @_generative

4043 def with_for_update(

4044 self,

4045 *,

4046 nowait: bool = False,

4047 read: bool = False,

4048 of: Optional[_ForUpdateOfArgument] = None,

4049 skip_locked: bool = False,

4050 key_share: bool = False,

4051 ) -> Self:

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

4053 :class:`_expression.GenerativeSelect`.

4054

4055 E.g.::

4056

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

4058

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

4060 render a statement like:

4061

4062 .. sourcecode:: sql

4063

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

4065

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

4067 would produce:

4068

4069 .. sourcecode:: sql

4070

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

4072

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

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

4075 provided which allow for common database-specific

4076 variants.

4077

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

4079 Database and PostgreSQL dialects.

4080

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

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

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

4084

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

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

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

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

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

4090 table or as a column depending on backend.

4091

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

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

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

4095

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

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

4098 on the PostgreSQL dialect.

4099

4100 """

4101 self._for_update_arg = ForUpdateArg(

4102 nowait=nowait,

4103 read=read,

4104 of=of,

4105 skip_locked=skip_locked,

4106 key_share=key_share,

4107 )

4108 return self

4109

4110 def get_label_style(self) -> SelectLabelStyle:

4111 """

4112 Retrieve the current label style.

4113

4114 .. versionadded:: 1.4

4115

4116 """

4117 return self._label_style

4118

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

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

4121

4122 There are three "label styles" available,

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

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

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

4126 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4127

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

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

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

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

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

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

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

4135 that the impact of this labeling is minimal.

4136

4137 The rationale for disambiguation is mostly so that all column

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

4139 collection when a subquery is created.

4140

4141 .. versionadded:: 1.4 - the

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

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

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

4145

4146 .. seealso::

4147

4148 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4149

4150 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4151

4152 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4153

4154 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4155

4156 """

4157 if self._label_style is not style:

4158 self = self._generate()

4159 self._label_style = style

4160 return self

4161

4162 @property

4163 def _group_by_clause(self) -> ClauseList:

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

4165 return ClauseList._construct_raw(

4166 operators.comma_op, self._group_by_clauses

4167 )

4168

4169 @property

4170 def _order_by_clause(self) -> ClauseList:

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

4172 return ClauseList._construct_raw(

4173 operators.comma_op, self._order_by_clauses

4174 )

4175

4176 def _offset_or_limit_clause(

4177 self,

4178 element: _LimitOffsetType,

4179 name: Optional[str] = None,

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

4181 ) -> ColumnElement[Any]:

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

4183

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

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

4186

4187 """

4188 return coercions.expect(

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

4190 )

4191

4192 @overload

4193 def _offset_or_limit_clause_asint(

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

4195 ) -> NoReturn: ...

4196

4197 @overload

4198 def _offset_or_limit_clause_asint(

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

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

4201

4202 def _offset_or_limit_clause_asint(

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

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

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

4206 integer.

4207

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

4209 parameter. Otherwise, a compilation error is raised.

4210

4211 """

4212 if clause is None:

4213 return None

4214 try:

4215 value = clause._limit_offset_value

4216 except AttributeError as err:

4217 raise exc.CompileError(

4218 "This SELECT structure does not use a simple "

4219 "integer value for %s" % attrname

4220 ) from err

4221 else:

4222 return util.asint(value)

4223

4224 @property

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

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

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

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

4229 isn't currently set to an integer.

4230

4231 """

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

4233

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

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

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

4237 """

4238 return isinstance(clause, _OffsetLimitParam)

4239

4240 @property

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

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

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

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

4245 offset isn't currently set to an integer.

4246

4247 """

4248 return self._offset_or_limit_clause_asint(

4249 self._offset_clause, "offset"

4250 )

4251

4252 @property

4253 def _has_row_limiting_clause(self) -> bool:

4254 return (

4255 self._limit_clause is not None

4256 or self._offset_clause is not None

4257 or self._fetch_clause is not None

4258 )

4259

4260 @_generative

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

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

4263 applied.

4264

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

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

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

4268 functionality.

4269

4270 .. note::

4271

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

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

4274

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

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

4277

4278 .. seealso::

4279

4280 :meth:`_sql.GenerativeSelect.fetch`

4281

4282 :meth:`_sql.GenerativeSelect.offset`

4283

4284 """

4285

4286 self._fetch_clause = self._fetch_clause_options = None

4287 self._limit_clause = self._offset_or_limit_clause(limit)

4288 return self

4289

4290 @_generative

4291 def fetch(

4292 self,

4293 count: _LimitOffsetType,

4294 with_ties: bool = False,

4295 percent: bool = False,

4296 **dialect_kw: Any,

4297 ) -> Self:

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

4299 applied.

4300

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

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

4303 select. This functionality is is currently implemented for Oracle

4304 Database, PostgreSQL, MSSQL.

4305

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

4307

4308 .. note::

4309

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

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

4312

4313 .. versionadded:: 1.4

4314

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

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

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

4318 Pass ``None`` to reset it.

4319

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

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

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

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

4324

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

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

4327

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

4329 may be accepted by dialects.

4330

4331 .. versionadded:: 2.0.41

4332

4333 .. seealso::

4334

4335 :meth:`_sql.GenerativeSelect.limit`

4336

4337 :meth:`_sql.GenerativeSelect.offset`

4338

4339 """

4340 self._validate_dialect_kwargs(dialect_kw)

4341 self._limit_clause = None

4342 if count is None:

4343 self._fetch_clause = self._fetch_clause_options = None

4344 else:

4345 self._fetch_clause = self._offset_or_limit_clause(count)

4346 self._fetch_clause_options = {

4347 "with_ties": with_ties,

4348 "percent": percent,

4349 }

4350 return self

4351

4352 @_generative

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

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

4355 applied.

4356

4357

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

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

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

4361 functionality.

4362

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

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

4365

4366 .. seealso::

4367

4368 :meth:`_sql.GenerativeSelect.limit`

4369

4370 :meth:`_sql.GenerativeSelect.fetch`

4371

4372 """

4373

4374 self._offset_clause = self._offset_or_limit_clause(offset)

4375 return self

4376

4377 @_generative

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

4379 def slice(

4380 self,

4381 start: int,

4382 stop: int,

4383 ) -> Self:

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

4385

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

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

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

4389 query.

4390

4391 For example, ::

4392

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

4394

4395 renders as

4396

4397 .. sourcecode:: sql

4398

4399 SELECT users.id AS users_id,

4400 users.name AS users_name

4401 FROM users ORDER BY users.id

4402 LIMIT ? OFFSET ?

4403 (2, 1)

4404

4405 .. note::

4406

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

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

4409

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

4411 method generalized from the ORM.

4412

4413 .. seealso::

4414

4415 :meth:`_sql.GenerativeSelect.limit`

4416

4417 :meth:`_sql.GenerativeSelect.offset`

4418

4419 :meth:`_sql.GenerativeSelect.fetch`

4420

4421 """

4422 sql_util = util.preloaded.sql_util

4423 self._fetch_clause = self._fetch_clause_options = None

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

4425 self._limit_clause, self._offset_clause, start, stop

4426 )

4427 return self

4428

4429 @_generative

4430 def order_by(

4431 self,

4432 __first: Union[

4433 Literal[None, _NoArg.NO_ARG],

4434 _ColumnExpressionOrStrLabelArgument[Any],

4435 ] = _NoArg.NO_ARG,

4436 /,

4437 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4438 ) -> Self:

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

4440 criteria applied.

4441

4442 e.g.::

4443

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

4445

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

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

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

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

4450

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

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

4453

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

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

4456

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

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

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

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

4461 label matching in ORDER BY and GROUP BY expressions.

4462

4463 .. seealso::

4464

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

4466

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

4468

4469 """

4470

4471 if not clauses and __first is None:

4472 self._order_by_clauses = ()

4473 elif __first is not _NoArg.NO_ARG:

4474 self._order_by_clauses += tuple(

4475 coercions.expect(

4476 roles.OrderByRole, clause, apply_propagate_attrs=self

4477 )

4478 for clause in (__first,) + clauses

4479 )

4480 return self

4481

4482 @_generative

4483 def group_by(

4484 self,

4485 __first: Union[

4486 Literal[None, _NoArg.NO_ARG],

4487 _ColumnExpressionOrStrLabelArgument[Any],

4488 ] = _NoArg.NO_ARG,

4489 /,

4490 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4491 ) -> Self:

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

4493 criterion applied.

4494

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

4496

4497 e.g.::

4498

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

4500

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

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

4503

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

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

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

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

4508 label matching in ORDER BY and GROUP BY expressions.

4509

4510 .. seealso::

4511

4512 :ref:`tutorial_group_by_w_aggregates` - in the

4513 :ref:`unified_tutorial`

4514

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

4516

4517 """ # noqa: E501

4518

4519 if not clauses and __first is None:

4520 self._group_by_clauses = ()

4521 elif __first is not _NoArg.NO_ARG:

4522 self._group_by_clauses += tuple(

4523 coercions.expect(

4524 roles.GroupByRole, clause, apply_propagate_attrs=self

4525 )

4526 for clause in (__first,) + clauses

4527 )

4528 return self

4529

4530

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

4532class CompoundSelectState(CompileState):

4533 @util.memoized_property

4534 def _label_resolve_dict(

4535 self,

4536 ) -> Tuple[

4537 Dict[str, ColumnElement[Any]],

4538 Dict[str, ColumnElement[Any]],

4539 Dict[str, ColumnElement[Any]],

4540 ]:

4541 # TODO: this is hacky and slow

4542 hacky_subquery = self.statement.subquery()

4543 hacky_subquery.named_with_column = False

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

4545 return d, d, d

4546

4547

4548class _CompoundSelectKeyword(Enum):

4549 UNION = "UNION"

4550 UNION_ALL = "UNION ALL"

4551 EXCEPT = "EXCEPT"

4552 EXCEPT_ALL = "EXCEPT ALL"

4553 INTERSECT = "INTERSECT"

4554 INTERSECT_ALL = "INTERSECT ALL"

4555

4556

4557class CompoundSelect(

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

4559):

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

4561 SELECT-based set operations.

4562

4563

4564 .. seealso::

4565

4566 :func:`_expression.union`

4567

4568 :func:`_expression.union_all`

4569

4570 :func:`_expression.intersect`

4571

4572 :func:`_expression.intersect_all`

4573

4574 :func:`_expression.except`

4575

4576 :func:`_expression.except_all`

4577

4578 """

4579

4580 __visit_name__ = "compound_select"

4581

4582 _traverse_internals: _TraverseInternalsType = (

4583 [

4584 ("selects", InternalTraversal.dp_clauseelement_list),

4585 ("_limit_clause", InternalTraversal.dp_clauseelement),

4586 ("_offset_clause", InternalTraversal.dp_clauseelement),

4587 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4588 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4589 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4590 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4591 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4592 ("keyword", InternalTraversal.dp_string),

4593 ]

4594 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4595 + HasCTE._has_ctes_traverse_internals

4596 + DialectKWArgs._dialect_kwargs_traverse_internals

4597 + ExecutableStatement._executable_traverse_internals

4598 )

4599

4600 selects: List[SelectBase]

4601

4602 _is_from_container = True

4603 _auto_correlate = False

4604

4605 def __init__(

4606 self,

4607 keyword: _CompoundSelectKeyword,

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

4609 ):

4610 self.keyword = keyword

4611 self.selects = [

4612 coercions.expect(

4613 roles.CompoundElementRole, s, apply_propagate_attrs=self

4614 ).self_group(against=self)

4615 for s in selects

4616 ]

4617

4618 GenerativeSelect.__init__(self)

4619

4620 @classmethod

4621 def _create_union(

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

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

4624 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4625

4626 @classmethod

4627 def _create_union_all(

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

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

4630 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4631

4632 @classmethod

4633 def _create_except(

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

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

4636 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4637

4638 @classmethod

4639 def _create_except_all(

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

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

4642 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4643

4644 @classmethod

4645 def _create_intersect(

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

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

4648 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4649

4650 @classmethod

4651 def _create_intersect_all(

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

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

4654 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4655

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

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

4658

4659 def self_group(

4660 self, against: Optional[OperatorType] = None

4661 ) -> GroupedElement:

4662 return SelectStatementGrouping(self)

4663

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

4665 for s in self.selects:

4666 if s.is_derived_from(fromclause):

4667 return True

4668 return False

4669

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

4671 if self._label_style is not style:

4672 self = self._generate()

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

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

4675

4676 return self

4677

4678 def _ensure_disambiguated_names(self) -> Self:

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

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

4681 self = self._generate()

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

4683

4684 return self

4685

4686 def _generate_fromclause_column_proxies(

4687 self,

4688 subquery: FromClause,

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

4690 primary_key: ColumnSet,

4691 foreign_keys: Set[KeyedColumnElement[Any]],

4692 *,

4693 proxy_compound_columns: Optional[

4694 Iterable[Sequence[ColumnElement[Any]]]

4695 ] = None,

4696 ) -> None:

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

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

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

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

4701 # generate below.

4702 select_0 = self.selects[0]

4703

4704 if self._label_style is not LABEL_STYLE_DEFAULT:

4705 select_0 = select_0.set_label_style(self._label_style)

4706

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

4708 # derived columns place a 'weight' annotation corresponding

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

4710 # that the corresponding_column() operation can resolve

4711 # conflicts

4712 extra_col_iterator = zip(

4713 *[

4714 [

4715 c._annotate(dd)

4716 for c in stmt._all_selected_columns

4717 if is_column_element(c)

4718 ]

4719 for dd, stmt in [

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

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

4722 ]

4723 ]

4724 )

4725

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

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

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

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

4730 # compound

4731 select_0._generate_fromclause_column_proxies(

4732 subquery,

4733 columns,

4734 proxy_compound_columns=extra_col_iterator,

4735 primary_key=primary_key,

4736 foreign_keys=foreign_keys,

4737 )

4738

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

4740 super()._refresh_for_new_column(column)

4741 for select in self.selects:

4742 select._refresh_for_new_column(column)

4743

4744 @util.ro_non_memoized_property

4745 def _all_selected_columns(self) -> _SelectIterable:

4746 return self.selects[0]._all_selected_columns

4747

4748 @util.ro_non_memoized_property

4749 def selected_columns(

4750 self,

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

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

4753 representing the columns that

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

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

4756

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

4758 :attr:`_expression.CompoundSelect.selected_columns`

4759 attribute returns the selected

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

4761 statements within the set operation.

4762

4763 .. seealso::

4764

4765 :attr:`_sql.Select.selected_columns`

4766

4767 .. versionadded:: 1.4

4768

4769 """

4770 return self.selects[0].selected_columns

4771

4772

4773# backwards compat

4774for elem in _CompoundSelectKeyword:

4775 setattr(CompoundSelect, elem.name, elem)

4776

4777

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

4779class SelectState(util.MemoizedSlots, CompileState):

4780 __slots__ = (

4781 "from_clauses",

4782 "froms",

4783 "columns_plus_names",

4784 "_label_resolve_dict",

4785 )

4786

4787 if TYPE_CHECKING:

4788 default_select_compile_options: CacheableOptions

4789 else:

4790

4791 class default_select_compile_options(CacheableOptions):

4792 _cache_key_traversal = []

4793

4794 if TYPE_CHECKING:

4795

4796 @classmethod

4797 def get_plugin_class(

4798 cls, statement: Executable

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

4800

4801 def __init__(

4802 self,

4803 statement: Select[Unpack[TupleAny]],

4804 compiler: SQLCompiler,

4805 **kw: Any,

4806 ):

4807 self.statement = statement

4808 self.from_clauses = statement._from_obj

4809

4810 for memoized_entities in statement._memoized_select_entities:

4811 self._setup_joins(

4812 memoized_entities._setup_joins, memoized_entities._raw_columns

4813 )

4814

4815 if statement._setup_joins:

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

4817

4818 self.froms = self._get_froms(statement)

4819

4820 self.columns_plus_names = statement._generate_columns_plus_names(True)

4821

4822 @classmethod

4823 def _plugin_not_implemented(cls) -> NoReturn:

4824 raise NotImplementedError(

4825 "The default SELECT construct without plugins does not "

4826 "implement this method."

4827 )

4828

4829 @classmethod

4830 def get_column_descriptions(

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

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

4833 return [

4834 {

4835 "name": name,

4836 "type": element.type,

4837 "expr": element,

4838 }

4839 for _, name, _, element, _ in (

4840 statement._generate_columns_plus_names(False)

4841 )

4842 ]

4843

4844 @classmethod

4845 def from_statement(

4846 cls,

4847 statement: Select[Unpack[TupleAny]],

4848 from_statement: roles.ReturnsRowsRole,

4849 ) -> ExecutableReturnsRows:

4850 cls._plugin_not_implemented()

4851

4852 @classmethod

4853 def get_columns_clause_froms(

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

4855 ) -> List[FromClause]:

4856 return cls._normalize_froms(

4857 itertools.chain.from_iterable(

4858 element._from_objects for element in statement._raw_columns

4859 )

4860 )

4861

4862 @classmethod

4863 def _column_naming_convention(

4864 cls, label_style: SelectLabelStyle

4865 ) -> _LabelConventionCallable:

4866 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4867

4868 dedupe = label_style is not LABEL_STYLE_NONE

4869

4870 pa = prefix_anon_map()

4871 names = set()

4872

4873 def go(

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

4875 col_name: Optional[str] = None,

4876 ) -> Optional[str]:

4877 if is_text_clause(c):

4878 return None

4879 elif TYPE_CHECKING:

4880 assert is_column_element(c)

4881

4882 if not dedupe:

4883 name = c._proxy_key

4884 if name is None:

4885 name = "_no_label"

4886 return name

4887

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

4889

4890 if name is None:

4891 name = "_no_label"

4892 if name in names:

4893 return c._anon_label(name) % pa

4894 else:

4895 names.add(name)

4896 return name

4897

4898 elif name in names:

4899 return (

4900 c._anon_tq_key_label % pa

4901 if table_qualified

4902 else c._anon_key_label % pa

4903 )

4904 else:

4905 names.add(name)

4906 return name

4907

4908 return go

4909

4910 def _get_froms(

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

4912 ) -> List[FromClause]:

4913 ambiguous_table_name_map: _AmbiguousTableNameMap

4914 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4915

4916 return self._normalize_froms(

4917 itertools.chain(

4918 self.from_clauses,

4919 itertools.chain.from_iterable(

4920 [

4921 element._from_objects

4922 for element in statement._raw_columns

4923 ]

4924 ),

4925 itertools.chain.from_iterable(

4926 [

4927 element._from_objects

4928 for element in statement._where_criteria

4929 ]

4930 ),

4931 ),

4932 check_statement=statement,

4933 ambiguous_table_name_map=ambiguous_table_name_map,

4934 )

4935

4936 @classmethod

4937 def _normalize_froms(

4938 cls,

4939 iterable_of_froms: Iterable[FromClause],

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

4941 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4942 ) -> List[FromClause]:

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

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

4945

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

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

4948 etc.

4949

4950 """

4951 seen: Set[FromClause] = set()

4952 froms: List[FromClause] = []

4953

4954 for item in iterable_of_froms:

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

4956 raise exc.InvalidRequestError(

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

4958 )

4959

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

4961 froms.append(item)

4962 seen.update(item._cloned_set)

4963

4964 if froms:

4965 toremove = set(

4966 itertools.chain.from_iterable(

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

4968 )

4969 )

4970 if toremove:

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

4972 # using a list to maintain ordering

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

4974

4975 if ambiguous_table_name_map is not None:

4976 ambiguous_table_name_map.update(

4977 (

4978 fr.name,

4979 _anonymous_label.safe_construct(

4980 hash(fr.name), fr.name

4981 ),

4982 )

4983 for item in froms

4984 for fr in item._from_objects

4985 if is_table(fr)

4986 and fr.schema

4987 and fr.name not in ambiguous_table_name_map

4988 )

4989

4990 return froms

4991

4992 def _get_display_froms(

4993 self,

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

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

4996 ) -> List[FromClause]:

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

4998

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

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

5001 may want to leave those absent if it is automatically

5002 correlating.

5003

5004 """

5005

5006 froms = self.froms

5007

5008 if self.statement._correlate:

5009 to_correlate = self.statement._correlate

5010 if to_correlate:

5011 froms = [

5012 f

5013 for f in froms

5014 if f

5015 not in _cloned_intersection(

5016 _cloned_intersection(

5017 froms, explicit_correlate_froms or ()

5018 ),

5019 to_correlate,

5020 )

5021 ]

5022

5023 if self.statement._correlate_except is not None:

5024 froms = [

5025 f

5026 for f in froms

5027 if f

5028 not in _cloned_difference(

5029 _cloned_intersection(

5030 froms, explicit_correlate_froms or ()

5031 ),

5032 self.statement._correlate_except,

5033 )

5034 ]

5035

5036 if (

5037 self.statement._auto_correlate

5038 and implicit_correlate_froms

5039 and len(froms) > 1

5040 ):

5041 froms = [

5042 f

5043 for f in froms

5044 if f

5045 not in _cloned_intersection(froms, implicit_correlate_froms)

5046 ]

5047

5048 if not len(froms):

5049 raise exc.InvalidRequestError(

5050 "Select statement '%r"

5051 "' returned no FROM clauses "

5052 "due to auto-correlation; "

5053 "specify correlate(<tables>) "

5054 "to control correlation "

5055 "manually." % self.statement

5056 )

5057

5058 return froms

5059

5060 def _memoized_attr__label_resolve_dict(

5061 self,

5062 ) -> Tuple[

5063 Dict[str, ColumnElement[Any]],

5064 Dict[str, ColumnElement[Any]],

5065 Dict[str, ColumnElement[Any]],

5066 ]:

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

5068 c._tq_label or c.key: c

5069 for c in self.statement._all_selected_columns

5070 if c._allow_label_resolve

5071 }

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

5073 c.key: c # type: ignore

5074 for c in _select_iterables(self.froms)

5075 if c._allow_label_resolve

5076 }

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

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

5079 with_cols.setdefault(key, value)

5080

5081 return with_cols, only_froms, only_cols

5082

5083 @classmethod

5084 def determine_last_joined_entity(

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

5086 ) -> Optional[_JoinTargetElement]:

5087 if stmt._setup_joins:

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

5089 else:

5090 return None

5091

5092 @classmethod

5093 def all_selected_columns(

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

5095 ) -> _SelectIterable:

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

5097

5098 def _setup_joins(

5099 self,

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

5101 raw_columns: List[_ColumnsClauseElement],

5102 ) -> None:

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

5104 if TYPE_CHECKING:

5105 if onclause is not None:

5106 assert isinstance(onclause, ColumnElement)

5107

5108 explicit_left = left

5109 isouter = flags["isouter"]

5110 full = flags["full"]

5111

5112 if left is None:

5113 (

5114 left,

5115 replace_from_obj_index,

5116 ) = self._join_determine_implicit_left_side(

5117 raw_columns, left, right, onclause

5118 )

5119 else:

5120 (replace_from_obj_index) = self._join_place_explicit_left_side(

5121 left

5122 )

5123

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

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

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

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

5128 # used instead.

5129 if TYPE_CHECKING:

5130 assert isinstance(right, FromClause)

5131 if onclause is not None:

5132 assert isinstance(onclause, ColumnElement)

5133

5134 if replace_from_obj_index is not None:

5135 # splice into an existing element in the

5136 # self._from_obj list

5137 left_clause = self.from_clauses[replace_from_obj_index]

5138

5139 if explicit_left is not None and onclause is None:

5140 onclause = Join._join_condition(explicit_left, right)

5141

5142 self.from_clauses = (

5143 self.from_clauses[:replace_from_obj_index]

5144 + (

5145 Join(

5146 left_clause,

5147 right,

5148 onclause,

5149 isouter=isouter,

5150 full=full,

5151 ),

5152 )

5153 + self.from_clauses[replace_from_obj_index + 1 :]

5154 )

5155 else:

5156 assert left is not None

5157 self.from_clauses = self.from_clauses + (

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

5159 )

5160

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

5162 def _join_determine_implicit_left_side(

5163 self,

5164 raw_columns: List[_ColumnsClauseElement],

5165 left: Optional[FromClause],

5166 right: _JoinTargetElement,

5167 onclause: Optional[ColumnElement[Any]],

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

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

5170 determine if an existing FROM or entity in this query

5171 can serve as the left hand side.

5172

5173 """

5174

5175 sql_util = util.preloaded.sql_util

5176

5177 replace_from_obj_index: Optional[int] = None

5178

5179 from_clauses = self.from_clauses

5180

5181 if from_clauses:

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

5183 from_clauses, right, onclause

5184 )

5185

5186 if len(indexes) == 1:

5187 replace_from_obj_index = indexes[0]

5188 left = from_clauses[replace_from_obj_index]

5189 else:

5190 potential = {}

5191 statement = self.statement

5192

5193 for from_clause in itertools.chain(

5194 itertools.chain.from_iterable(

5195 [element._from_objects for element in raw_columns]

5196 ),

5197 itertools.chain.from_iterable(

5198 [

5199 element._from_objects

5200 for element in statement._where_criteria

5201 ]

5202 ),

5203 ):

5204 potential[from_clause] = ()

5205

5206 all_clauses = list(potential.keys())

5207 indexes = sql_util.find_left_clause_to_join_from(

5208 all_clauses, right, onclause

5209 )

5210

5211 if len(indexes) == 1:

5212 left = all_clauses[indexes[0]]

5213

5214 if len(indexes) > 1:

5215 raise exc.InvalidRequestError(

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

5217 "from, there are multiple FROMS which can "

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

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

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

5221 "help resolve the ambiguity."

5222 )

5223 elif not indexes:

5224 raise exc.InvalidRequestError(

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

5226 "Please use the .select_from() "

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

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

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

5230 )

5231 return left, replace_from_obj_index

5232

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

5234 def _join_place_explicit_left_side(

5235 self, left: FromClause

5236 ) -> Optional[int]:

5237 replace_from_obj_index: Optional[int] = None

5238

5239 sql_util = util.preloaded.sql_util

5240

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

5242

5243 if from_clauses:

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

5245 self.from_clauses, left

5246 )

5247 else:

5248 indexes = []

5249

5250 if len(indexes) > 1:

5251 raise exc.InvalidRequestError(

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

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

5254 "ON clause."

5255 )

5256

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

5258 # an existing FROM in the self._from_obj tuple

5259 if indexes:

5260 replace_from_obj_index = indexes[0]

5261

5262 # no index, means we need to add a new element to the

5263 # self._from_obj tuple

5264

5265 return replace_from_obj_index

5266

5267

5268class _SelectFromElements:

5269 __slots__ = ()

5270

5271 _raw_columns: List[_ColumnsClauseElement]

5272 _where_criteria: Tuple[ColumnElement[Any], ...]

5273 _from_obj: Tuple[FromClause, ...]

5274

5275 def _iterate_from_elements(self) -> Iterator[FromClause]:

5276 # note this does not include elements

5277 # in _setup_joins

5278

5279 seen = set()

5280 for element in self._raw_columns:

5281 for fr in element._from_objects:

5282 if fr in seen:

5283 continue

5284 seen.add(fr)

5285 yield fr

5286 for element in self._where_criteria:

5287 for fr in element._from_objects:

5288 if fr in seen:

5289 continue

5290 seen.add(fr)

5291 yield fr

5292 for element in self._from_obj:

5293 if element in seen:

5294 continue

5295 seen.add(element)

5296 yield element

5297

5298

5299class _MemoizedSelectEntities(

5300 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5301):

5302 """represents partial state from a Select object, for the case

5303 where Select.columns() has redefined the set of columns/entities the

5304 statement will be SELECTing from. This object represents

5305 the entities from the SELECT before that transformation was applied,

5306 so that transformations that were made in terms of the SELECT at that

5307 time, such as join() as well as options(), can access the correct context.

5308

5309 In previous SQLAlchemy versions, this wasn't needed because these

5310 constructs calculated everything up front, like when you called join()

5311 or options(), it did everything to figure out how that would translate

5312 into specific SQL constructs that would be ready to send directly to the

5313 SQL compiler when needed. But as of

5314 1.4, all of that stuff is done in the compilation phase, during the

5315 "compile state" portion of the process, so that the work can all be

5316 cached. So it needs to be able to resolve joins/options2 based on what

5317 the list of entities was when those methods were called.

5318

5319

5320 """

5321

5322 __visit_name__ = "memoized_select_entities"

5323

5324 _traverse_internals: _TraverseInternalsType = [

5325 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5326 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5327 ("_with_options", InternalTraversal.dp_executable_options),

5328 ]

5329

5330 _is_clone_of: Optional[ClauseElement]

5331 _raw_columns: List[_ColumnsClauseElement]

5332 _setup_joins: Tuple[_SetupJoinsElement, ...]

5333 _with_options: Tuple[ExecutableOption, ...]

5334

5335 _annotations = util.EMPTY_DICT

5336

5337 def _clone(self, **kw: Any) -> Self:

5338 c = self.__class__.__new__(self.__class__)

5339 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5340

5341 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5342 return c

5343

5344 @classmethod

5345 def _generate_for_statement(

5346 cls, select_stmt: Select[Unpack[TupleAny]]

5347 ) -> None:

5348 if select_stmt._setup_joins or select_stmt._with_options:

5349 self = _MemoizedSelectEntities()

5350 self._raw_columns = select_stmt._raw_columns

5351 self._setup_joins = select_stmt._setup_joins

5352 self._with_options = select_stmt._with_options

5353

5354 select_stmt._memoized_select_entities += (self,)

5355 select_stmt._raw_columns = []

5356 select_stmt._setup_joins = select_stmt._with_options = ()

5357

5358

5359class Select(

5360 HasPrefixes,

5361 HasSuffixes,

5362 HasHints,

5363 HasCompileState,

5364 HasSyntaxExtensions[

5365 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5366 ],

5367 _SelectFromElements,

5368 GenerativeSelect,

5369 TypedReturnsRows[Unpack[_Ts]],

5370):

5371 """Represents a ``SELECT`` statement.

5372

5373 The :class:`_sql.Select` object is normally constructed using the

5374 :func:`_sql.select` function. See that function for details.

5375

5376 Available extension points:

5377

5378 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5379 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5380 keyword (if any) and the list of columns.

5381 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5382 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5383

5384 .. seealso::

5385

5386 :func:`_sql.select`

5387

5388 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5389

5390 """

5391

5392 __visit_name__ = "select"

5393

5394 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5395 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5396

5397 _raw_columns: List[_ColumnsClauseElement]

5398

5399 _distinct: bool = False

5400 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5401 _correlate: Tuple[FromClause, ...] = ()

5402 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5403 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5404 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5405 _from_obj: Tuple[FromClause, ...] = ()

5406

5407 _position_map = util.immutabledict(

5408 {

5409 "post_select": "_post_select_clause",

5410 "pre_columns": "_pre_columns_clause",

5411 "post_criteria": "_post_criteria_clause",

5412 "post_body": "_post_body_clause",

5413 }

5414 )

5415

5416 _post_select_clause: Optional[ClauseElement] = None

5417 """extension point for a ClauseElement that will be compiled directly

5418 after the SELECT keyword.

5419

5420 .. versionadded:: 2.1

5421

5422 """

5423

5424 _pre_columns_clause: Optional[ClauseElement] = None

5425 """extension point for a ClauseElement that will be compiled directly

5426 before the "columns" clause; after DISTINCT (if present).

5427

5428 .. versionadded:: 2.1

5429

5430 """

5431

5432 _post_criteria_clause: Optional[ClauseElement] = None

5433 """extension point for a ClauseElement that will be compiled directly

5434 after "criteria", following the HAVING clause but before ORDER BY.

5435

5436 .. versionadded:: 2.1

5437

5438 """

5439

5440 _post_body_clause: Optional[ClauseElement] = None

5441 """extension point for a ClauseElement that will be compiled directly

5442 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5443 of the SELECT.

5444

5445 .. versionadded:: 2.1

5446

5447 """

5448

5449 _auto_correlate = True

5450 _is_select_statement = True

5451 _compile_options: CacheableOptions = (

5452 SelectState.default_select_compile_options

5453 )

5454

5455 _traverse_internals: _TraverseInternalsType = (

5456 [

5457 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5458 (

5459 "_memoized_select_entities",

5460 InternalTraversal.dp_memoized_select_entities,

5461 ),

5462 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5463 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5464 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5465 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5466 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5467 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5468 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5469 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5470 ("_limit_clause", InternalTraversal.dp_clauseelement),

5471 ("_offset_clause", InternalTraversal.dp_clauseelement),

5472 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5473 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5474 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5475 ("_distinct", InternalTraversal.dp_boolean),

5476 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5477 ("_label_style", InternalTraversal.dp_plain_obj),

5478 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5479 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5480 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5481 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5482 ]

5483 + HasCTE._has_ctes_traverse_internals

5484 + HasPrefixes._has_prefixes_traverse_internals

5485 + HasSuffixes._has_suffixes_traverse_internals

5486 + HasHints._has_hints_traverse_internals

5487 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5488 + ExecutableStatement._executable_traverse_internals

5489 + DialectKWArgs._dialect_kwargs_traverse_internals

5490 )

5491

5492 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5493 ("_compile_options", InternalTraversal.dp_has_cache_key)

5494 ]

5495

5496 _compile_state_factory: Type[SelectState]

5497

5498 @classmethod

5499 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5500 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5501

5502 Used internally to build up :class:`.Select` constructs with

5503 pre-established state.

5504

5505 """

5506

5507 stmt = Select.__new__(Select)

5508 stmt.__dict__.update(kw)

5509 return stmt

5510

5511 def __init__(

5512 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5513 ):

5514 r"""Construct a new :class:`_expression.Select`.

5515

5516 The public constructor for :class:`_expression.Select` is the

5517 :func:`_sql.select` function.

5518

5519 """

5520 self._raw_columns = [

5521 coercions.expect(

5522 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5523 )

5524 for ent in entities

5525 ]

5526 GenerativeSelect.__init__(self)

5527

5528 def _apply_syntax_extension_to_self(

5529 self, extension: SyntaxExtension

5530 ) -> None:

5531 extension.apply_to_select(self)

5532

5533 def _scalar_type(self) -> TypeEngine[Any]:

5534 if not self._raw_columns:

5535 return NULLTYPE

5536 elem = self._raw_columns[0]

5537 cols = list(elem._select_iterable)

5538 return cols[0].type

5539

5540 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5541 """A synonym for the :meth:`_sql.Select.where` method."""

5542

5543 return self.where(*criteria)

5544

5545 def _filter_by_zero(

5546 self,

5547 ) -> Union[

5548 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5549 ]:

5550 if self._setup_joins:

5551 meth = SelectState.get_plugin_class(

5552 self

5553 ).determine_last_joined_entity

5554 _last_joined_entity = meth(self)

5555 if _last_joined_entity is not None:

5556 return _last_joined_entity

5557

5558 if self._from_obj:

5559 return self._from_obj[0]

5560

5561 return self._raw_columns[0]

5562

5563 if TYPE_CHECKING:

5564

5565 @overload

5566 def scalar_subquery(

5567 self: Select[_MAYBE_ENTITY],

5568 ) -> ScalarSelect[Any]: ...

5569

5570 @overload

5571 def scalar_subquery(

5572 self: Select[_NOT_ENTITY],

5573 ) -> ScalarSelect[_NOT_ENTITY]: ...

5574

5575 @overload

5576 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5577

5578 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5579

5580 def filter_by(self, **kwargs: Any) -> Self:

5581 r"""apply the given filtering criterion as a WHERE clause

5582 to this select.

5583

5584 """

5585 from_entity = self._filter_by_zero()

5586

5587 clauses = [

5588 _entity_namespace_key(from_entity, key) == value

5589 for key, value in kwargs.items()

5590 ]

5591 return self.filter(*clauses)

5592

5593 @property

5594 def column_descriptions(self) -> Any:

5595 """Return a :term:`plugin-enabled` 'column descriptions' structure

5596 referring to the columns which are SELECTed by this statement.

5597

5598 This attribute is generally useful when using the ORM, as an

5599 extended structure which includes information about mapped

5600 entities is returned. The section :ref:`queryguide_inspection`

5601 contains more background.

5602

5603 For a Core-only statement, the structure returned by this accessor

5604 is derived from the same objects that are returned by the

5605 :attr:`.Select.selected_columns` accessor, formatted as a list of

5606 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5607 which indicate the column expressions to be selected::

5608

5609 >>> stmt = select(user_table)

5610 >>> stmt.column_descriptions

5611 [

5612 {

5613 'name': 'id',

5614 'type': Integer(),

5615 'expr': Column('id', Integer(), ...)},

5616 {

5617 'name': 'name',

5618 'type': String(length=30),

5619 'expr': Column('name', String(length=30), ...)}

5620 ]

5621

5622 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5623 attribute returns a structure for a Core-only set of entities,

5624 not just ORM-only entities.

5625

5626 .. seealso::

5627

5628 :attr:`.UpdateBase.entity_description` - entity information for

5629 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5630

5631 :ref:`queryguide_inspection` - ORM background

5632

5633 """

5634 meth = SelectState.get_plugin_class(self).get_column_descriptions

5635 return meth(self)

5636

5637 def from_statement(

5638 self, statement: roles.ReturnsRowsRole

5639 ) -> ExecutableReturnsRows:

5640 """Apply the columns which this :class:`.Select` would select

5641 onto another statement.

5642

5643 This operation is :term:`plugin-specific` and will raise a not

5644 supported exception if this :class:`_sql.Select` does not select from

5645 plugin-enabled entities.

5646

5647

5648 The statement is typically either a :func:`_expression.text` or

5649 :func:`_expression.select` construct, and should return the set of

5650 columns appropriate to the entities represented by this

5651 :class:`.Select`.

5652

5653 .. seealso::

5654

5655 :ref:`orm_queryguide_selecting_text` - usage examples in the

5656 ORM Querying Guide

5657

5658 """

5659 meth = SelectState.get_plugin_class(self).from_statement

5660 return meth(self, statement)

5661

5662 @_generative

5663 def join(

5664 self,

5665 target: _JoinTargetArgument,

5666 onclause: Optional[_OnClauseArgument] = None,

5667 *,

5668 isouter: bool = False,

5669 full: bool = False,

5670 ) -> Self:

5671 r"""Create a SQL JOIN against this :class:`_expression.Select`

5672 object's criterion

5673 and apply generatively, returning the newly resulting

5674 :class:`_expression.Select`.

5675

5676 E.g.::

5677

5678 stmt = select(user_table).join(

5679 address_table, user_table.c.id == address_table.c.user_id

5680 )

5681

5682 The above statement generates SQL similar to:

5683

5684 .. sourcecode:: sql

5685

5686 SELECT user.id, user.name

5687 FROM user

5688 JOIN address ON user.id = address.user_id

5689

5690 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5691 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5692 source that is within the FROM clause of the existing SELECT,

5693 and a given target :class:`_sql.FromClause`, and then adds

5694 this :class:`_sql.Join` to the FROM clause of the newly generated

5695 SELECT statement. This is completely reworked from the behavior

5696 in 1.3, which would instead create a subquery of the entire

5697 :class:`_expression.Select` and then join that subquery to the

5698 target.

5699

5700 This is a **backwards incompatible change** as the previous behavior

5701 was mostly useless, producing an unnamed subquery rejected by

5702 most databases in any case. The new behavior is modeled after

5703 that of the very successful :meth:`_orm.Query.join` method in the

5704 ORM, in order to support the functionality of :class:`_orm.Query`

5705 being available by using a :class:`_sql.Select` object with an

5706 :class:`_orm.Session`.

5707

5708 See the notes for this change at :ref:`change_select_join`.

5709

5710

5711 :param target: target table to join towards

5712

5713 :param onclause: ON clause of the join. If omitted, an ON clause

5714 is generated automatically based on the :class:`_schema.ForeignKey`

5715 linkages between the two tables, if one can be unambiguously

5716 determined, otherwise an error is raised.

5717

5718 :param isouter: if True, generate LEFT OUTER join. Same as

5719 :meth:`_expression.Select.outerjoin`.

5720

5721 :param full: if True, generate FULL OUTER join.

5722

5723 .. seealso::

5724

5725 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5726

5727 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5728

5729 :meth:`_expression.Select.join_from`

5730

5731 :meth:`_expression.Select.outerjoin`

5732

5733 """ # noqa: E501

5734 join_target = coercions.expect(

5735 roles.JoinTargetRole, target, apply_propagate_attrs=self

5736 )

5737 if onclause is not None:

5738 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5739 else:

5740 onclause_element = None

5741

5742 self._setup_joins += (

5743 (

5744 join_target,

5745 onclause_element,

5746 None,

5747 {"isouter": isouter, "full": full},

5748 ),

5749 )

5750 return self

5751

5752 def outerjoin_from(

5753 self,

5754 from_: _FromClauseArgument,

5755 target: _JoinTargetArgument,

5756 onclause: Optional[_OnClauseArgument] = None,

5757 *,

5758 full: bool = False,

5759 ) -> Self:

5760 r"""Create a SQL LEFT OUTER JOIN against this

5761 :class:`_expression.Select` object's criterion and apply generatively,

5762 returning the newly resulting :class:`_expression.Select`.

5763

5764 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5765

5766 """

5767 return self.join_from(

5768 from_, target, onclause=onclause, isouter=True, full=full

5769 )

5770

5771 @_generative

5772 def join_from(

5773 self,

5774 from_: _FromClauseArgument,

5775 target: _JoinTargetArgument,

5776 onclause: Optional[_OnClauseArgument] = None,

5777 *,

5778 isouter: bool = False,

5779 full: bool = False,

5780 ) -> Self:

5781 r"""Create a SQL JOIN against this :class:`_expression.Select`

5782 object's criterion

5783 and apply generatively, returning the newly resulting

5784 :class:`_expression.Select`.

5785

5786 E.g.::

5787

5788 stmt = select(user_table, address_table).join_from(

5789 user_table, address_table, user_table.c.id == address_table.c.user_id

5790 )

5791

5792 The above statement generates SQL similar to:

5793

5794 .. sourcecode:: sql

5795

5796 SELECT user.id, user.name, address.id, address.email, address.user_id

5797 FROM user JOIN address ON user.id = address.user_id

5798

5799 .. versionadded:: 1.4

5800

5801 :param from\_: the left side of the join, will be rendered in the

5802 FROM clause and is roughly equivalent to using the

5803 :meth:`.Select.select_from` method.

5804

5805 :param target: target table to join towards

5806

5807 :param onclause: ON clause of the join.

5808

5809 :param isouter: if True, generate LEFT OUTER join. Same as

5810 :meth:`_expression.Select.outerjoin`.

5811

5812 :param full: if True, generate FULL OUTER join.

5813

5814 .. seealso::

5815

5816 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5817

5818 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5819

5820 :meth:`_expression.Select.join`

5821

5822 """ # noqa: E501

5823

5824 # note the order of parsing from vs. target is important here, as we

5825 # are also deriving the source of the plugin (i.e. the subject mapper

5826 # in an ORM query) which should favor the "from_" over the "target"

5827

5828 from_ = coercions.expect(

5829 roles.FromClauseRole, from_, apply_propagate_attrs=self

5830 )

5831 join_target = coercions.expect(

5832 roles.JoinTargetRole, target, apply_propagate_attrs=self

5833 )

5834 if onclause is not None:

5835 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5836 else:

5837 onclause_element = None

5838

5839 self._setup_joins += (

5840 (

5841 join_target,

5842 onclause_element,

5843 from_,

5844 {"isouter": isouter, "full": full},

5845 ),

5846 )

5847 return self

5848

5849 def outerjoin(

5850 self,

5851 target: _JoinTargetArgument,

5852 onclause: Optional[_OnClauseArgument] = None,

5853 *,

5854 full: bool = False,

5855 ) -> Self:

5856 """Create a left outer join.

5857

5858 Parameters are the same as that of :meth:`_expression.Select.join`.

5859

5860 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5861 creates a :class:`_sql.Join` object between a

5862 :class:`_sql.FromClause` source that is within the FROM clause of

5863 the existing SELECT, and a given target :class:`_sql.FromClause`,

5864 and then adds this :class:`_sql.Join` to the FROM clause of the

5865 newly generated SELECT statement. This is completely reworked

5866 from the behavior in 1.3, which would instead create a subquery of

5867 the entire

5868 :class:`_expression.Select` and then join that subquery to the

5869 target.

5870

5871 This is a **backwards incompatible change** as the previous behavior

5872 was mostly useless, producing an unnamed subquery rejected by

5873 most databases in any case. The new behavior is modeled after

5874 that of the very successful :meth:`_orm.Query.join` method in the

5875 ORM, in order to support the functionality of :class:`_orm.Query`

5876 being available by using a :class:`_sql.Select` object with an

5877 :class:`_orm.Session`.

5878

5879 See the notes for this change at :ref:`change_select_join`.

5880

5881 .. seealso::

5882

5883 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5884

5885 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5886

5887 :meth:`_expression.Select.join`

5888

5889 """

5890 return self.join(target, onclause=onclause, isouter=True, full=full)

5891

5892 def get_final_froms(self) -> Sequence[FromClause]:

5893 """Compute the final displayed list of :class:`_expression.FromClause`

5894 elements.

5895

5896 This method will run through the full computation required to

5897 determine what FROM elements will be displayed in the resulting

5898 SELECT statement, including shadowing individual tables with

5899 JOIN objects, as well as full computation for ORM use cases including

5900 eager loading clauses.

5901

5902 For ORM use, this accessor returns the **post compilation**

5903 list of FROM objects; this collection will include elements such as

5904 eagerly loaded tables and joins. The objects will **not** be

5905 ORM enabled and not work as a replacement for the

5906 :meth:`_sql.Select.select_froms` collection; additionally, the

5907 method is not well performing for an ORM enabled statement as it

5908 will incur the full ORM construction process.

5909

5910 To retrieve the FROM list that's implied by the "columns" collection

5911 passed to the :class:`_sql.Select` originally, use the

5912 :attr:`_sql.Select.columns_clause_froms` accessor.

5913

5914 To select from an alternative set of columns while maintaining the

5915 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5916 pass the

5917 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5918 parameter.

5919

5920 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5921 method replaces the previous :attr:`_sql.Select.froms` accessor,

5922 which is deprecated.

5923

5924 .. seealso::

5925

5926 :attr:`_sql.Select.columns_clause_froms`

5927

5928 """

5929 compiler = self._default_compiler()

5930

5931 return self._compile_state_factory(self, compiler)._get_display_froms()

5932

5933 @property

5934 @util.deprecated(

5935 "1.4.23",

5936 "The :attr:`_expression.Select.froms` attribute is moved to "

5937 "the :meth:`_expression.Select.get_final_froms` method.",

5938 )

5939 def froms(self) -> Sequence[FromClause]:

5940 """Return the displayed list of :class:`_expression.FromClause`

5941 elements.

5942

5943

5944 """

5945 return self.get_final_froms()

5946

5947 @property

5948 def columns_clause_froms(self) -> List[FromClause]:

5949 """Return the set of :class:`_expression.FromClause` objects implied

5950 by the columns clause of this SELECT statement.

5951

5952 .. versionadded:: 1.4.23

5953

5954 .. seealso::

5955

5956 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5957 statement into account

5958

5959 :meth:`_sql.Select.with_only_columns` - makes use of this

5960 collection to set up a new FROM list

5961

5962 """

5963

5964 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5965 self

5966 )

5967

5968 @property

5969 def inner_columns(self) -> _SelectIterable:

5970 """An iterator of all :class:`_expression.ColumnElement`

5971 expressions which would

5972 be rendered into the columns clause of the resulting SELECT statement.

5973

5974 This method is legacy as of 1.4 and is superseded by the

5975 :attr:`_expression.Select.exported_columns` collection.

5976

5977 """

5978

5979 return iter(self._all_selected_columns)

5980

5981 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

5982 if fromclause is not None and self in fromclause._cloned_set:

5983 return True

5984

5985 for f in self._iterate_from_elements():

5986 if f.is_derived_from(fromclause):

5987 return True

5988 return False

5989

5990 def _copy_internals(

5991 self, clone: _CloneCallableType = _clone, **kw: Any

5992 ) -> None:

5993 # Select() object has been cloned and probably adapted by the

5994 # given clone function. Apply the cloning function to internal

5995 # objects

5996

5997 # 1. keep a dictionary of the froms we've cloned, and what

5998 # they've become. This allows us to ensure the same cloned from

5999 # is used when other items such as columns are "cloned"

6000

6001 all_the_froms = set(

6002 itertools.chain(

6003 _from_objects(*self._raw_columns),

6004 _from_objects(*self._where_criteria),

6005 _from_objects(*[elem[0] for elem in self._setup_joins]),

6006 )

6007 )

6008

6009 # do a clone for the froms we've gathered. what is important here

6010 # is if any of the things we are selecting from, like tables,

6011 # were converted into Join objects. if so, these need to be

6012 # added to _from_obj explicitly, because otherwise they won't be

6013 # part of the new state, as they don't associate themselves with

6014 # their columns.

6015 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6016

6017 # 2. copy FROM collections, adding in joins that we've created.

6018 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6019 add_froms = (

6020 {f for f in new_froms.values() if isinstance(f, Join)}

6021 .difference(all_the_froms)

6022 .difference(existing_from_obj)

6023 )

6024

6025 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6026

6027 # 3. clone everything else, making sure we use columns

6028 # corresponding to the froms we just made.

6029 def replace(

6030 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6031 **kw: Any,

6032 ) -> Optional[KeyedColumnElement[Any]]:

6033 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6034 newelem = new_froms[obj.table].corresponding_column(obj)

6035 return newelem

6036 return None

6037

6038 kw["replace"] = replace

6039

6040 # copy everything else. for table-ish things like correlate,

6041 # correlate_except, setup_joins, these clone normally. For

6042 # column-expression oriented things like raw_columns, where_criteria,

6043 # order by, we get this from the new froms.

6044 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6045

6046 self._reset_memoizations()

6047

6048 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6049 return itertools.chain(

6050 super().get_children(

6051 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6052 **kw,

6053 ),

6054 self._iterate_from_elements(),

6055 )

6056

6057 @_generative

6058 def add_columns(

6059 self, *entities: _ColumnsClauseArgument[Any]

6060 ) -> Select[Unpack[TupleAny]]:

6061 r"""Return a new :func:`_expression.select` construct with

6062 the given entities appended to its columns clause.

6063

6064 E.g.::

6065

6066 my_select = my_select.add_columns(table.c.new_column)

6067

6068 The original expressions in the columns clause remain in place.

6069 To replace the original expressions with new ones, see the method

6070 :meth:`_expression.Select.with_only_columns`.

6071

6072 :param \*entities: column, table, or other entity expressions to be

6073 added to the columns clause

6074

6075 .. seealso::

6076

6077 :meth:`_expression.Select.with_only_columns` - replaces existing

6078 expressions rather than appending.

6079

6080 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6081 example

6082

6083 """

6084 self._reset_memoizations()

6085

6086 self._raw_columns = self._raw_columns + [

6087 coercions.expect(

6088 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6089 )

6090 for column in entities

6091 ]

6092 return self

6093

6094 def _set_entities(

6095 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6096 ) -> None:

6097 self._raw_columns = [

6098 coercions.expect(

6099 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6100 )

6101 for ent in util.to_list(entities)

6102 ]

6103

6104 @util.deprecated(

6105 "1.4",

6106 "The :meth:`_expression.Select.column` method is deprecated and will "

6107 "be removed in a future release. Please use "

6108 ":meth:`_expression.Select.add_columns`",

6109 )

6110 def column(

6111 self, column: _ColumnsClauseArgument[Any]

6112 ) -> Select[Unpack[TupleAny]]:

6113 """Return a new :func:`_expression.select` construct with

6114 the given column expression added to its columns clause.

6115

6116 E.g.::

6117

6118 my_select = my_select.column(table.c.new_column)

6119

6120 See the documentation for

6121 :meth:`_expression.Select.with_only_columns`

6122 for guidelines on adding /replacing the columns of a

6123 :class:`_expression.Select` object.

6124

6125 """

6126 return self.add_columns(column)

6127

6128 @util.preload_module("sqlalchemy.sql.util")

6129 def reduce_columns(

6130 self, only_synonyms: bool = True

6131 ) -> Select[Unpack[TupleAny]]:

6132 """Return a new :func:`_expression.select` construct with redundantly

6133 named, equivalently-valued columns removed from the columns clause.

6134

6135 "Redundant" here means two columns where one refers to the

6136 other either based on foreign key, or via a simple equality

6137 comparison in the WHERE clause of the statement. The primary purpose

6138 of this method is to automatically construct a select statement

6139 with all uniquely-named columns, without the need to use

6140 table-qualified labels as

6141 :meth:`_expression.Select.set_label_style`

6142 does.

6143

6144 When columns are omitted based on foreign key, the referred-to

6145 column is the one that's kept. When columns are omitted based on

6146 WHERE equivalence, the first column in the columns clause is the

6147 one that's kept.

6148

6149 :param only_synonyms: when True, limit the removal of columns

6150 to those which have the same name as the equivalent. Otherwise,

6151 all columns that are equivalent to another are removed.

6152

6153 """

6154 woc: Select[Unpack[TupleAny]]

6155 woc = self.with_only_columns(

6156 *util.preloaded.sql_util.reduce_columns(

6157 self._all_selected_columns,

6158 only_synonyms=only_synonyms,

6159 *(self._where_criteria + self._from_obj),

6160 )

6161 )

6162 return woc

6163

6164 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6165

6166 # code within this block is **programmatically,

6167 # statically generated** by tools/generate_tuple_map_overloads.py

6168

6169 @overload

6170 def with_only_columns(

6171 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6172 ) -> Select[_T0]: ...

6173

6174 @overload

6175 def with_only_columns(

6176 self,

6177 __ent0: _TCCA[_T0],

6178 __ent1: _TCCA[_T1],

6179 /,

6180 *,

6181 maintain_column_froms: bool = ...,

6182 ) -> Select[_T0, _T1]: ...

6183

6184 @overload

6185 def with_only_columns(

6186 self,

6187 __ent0: _TCCA[_T0],

6188 __ent1: _TCCA[_T1],

6189 __ent2: _TCCA[_T2],

6190 /,

6191 *,

6192 maintain_column_froms: bool = ...,

6193 ) -> Select[_T0, _T1, _T2]: ...

6194

6195 @overload

6196 def with_only_columns(

6197 self,

6198 __ent0: _TCCA[_T0],

6199 __ent1: _TCCA[_T1],

6200 __ent2: _TCCA[_T2],

6201 __ent3: _TCCA[_T3],

6202 /,

6203 *,

6204 maintain_column_froms: bool = ...,

6205 ) -> Select[_T0, _T1, _T2, _T3]: ...

6206

6207 @overload

6208 def with_only_columns(

6209 self,

6210 __ent0: _TCCA[_T0],

6211 __ent1: _TCCA[_T1],

6212 __ent2: _TCCA[_T2],

6213 __ent3: _TCCA[_T3],

6214 __ent4: _TCCA[_T4],

6215 /,

6216 *,

6217 maintain_column_froms: bool = ...,

6218 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6219

6220 @overload

6221 def with_only_columns(

6222 self,

6223 __ent0: _TCCA[_T0],

6224 __ent1: _TCCA[_T1],

6225 __ent2: _TCCA[_T2],

6226 __ent3: _TCCA[_T3],

6227 __ent4: _TCCA[_T4],

6228 __ent5: _TCCA[_T5],

6229 /,

6230 *,

6231 maintain_column_froms: bool = ...,

6232 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6233

6234 @overload

6235 def with_only_columns(

6236 self,

6237 __ent0: _TCCA[_T0],

6238 __ent1: _TCCA[_T1],

6239 __ent2: _TCCA[_T2],

6240 __ent3: _TCCA[_T3],

6241 __ent4: _TCCA[_T4],

6242 __ent5: _TCCA[_T5],

6243 __ent6: _TCCA[_T6],

6244 /,

6245 *,

6246 maintain_column_froms: bool = ...,

6247 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6248

6249 @overload

6250 def with_only_columns(

6251 self,

6252 __ent0: _TCCA[_T0],

6253 __ent1: _TCCA[_T1],

6254 __ent2: _TCCA[_T2],

6255 __ent3: _TCCA[_T3],

6256 __ent4: _TCCA[_T4],

6257 __ent5: _TCCA[_T5],

6258 __ent6: _TCCA[_T6],

6259 __ent7: _TCCA[_T7],

6260 /,

6261 *entities: _ColumnsClauseArgument[Any],

6262 maintain_column_froms: bool = ...,

6263 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6264

6265 # END OVERLOADED FUNCTIONS self.with_only_columns

6266

6267 @overload

6268 def with_only_columns(

6269 self,

6270 *entities: _ColumnsClauseArgument[Any],

6271 maintain_column_froms: bool = False,

6272 **__kw: Any,

6273 ) -> Select[Unpack[TupleAny]]: ...

6274

6275 @_generative

6276 def with_only_columns(

6277 self,

6278 *entities: _ColumnsClauseArgument[Any],

6279 maintain_column_froms: bool = False,

6280 **__kw: Any,

6281 ) -> Select[Unpack[TupleAny]]:

6282 r"""Return a new :func:`_expression.select` construct with its columns

6283 clause replaced with the given entities.

6284

6285 By default, this method is exactly equivalent to as if the original

6286 :func:`_expression.select` had been called with the given entities.

6287 E.g. a statement::

6288

6289 s = select(table1.c.a, table1.c.b)

6290 s = s.with_only_columns(table1.c.b)

6291

6292 should be exactly equivalent to::

6293

6294 s = select(table1.c.b)

6295

6296 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6297 will also dynamically alter the FROM clause of the

6298 statement if it is not explicitly stated.

6299 To maintain the existing set of FROMs including those implied by the

6300 current columns clause, add the

6301 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6302 parameter::

6303

6304 s = select(table1.c.a, table2.c.b)

6305 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6306

6307 The above parameter performs a transfer of the effective FROMs

6308 in the columns collection to the :meth:`_sql.Select.select_from`

6309 method, as though the following were invoked::

6310

6311 s = select(table1.c.a, table2.c.b)

6312 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6313

6314 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6315 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6316 collection and performs an operation equivalent to the following::

6317

6318 s = select(table1.c.a, table2.c.b)

6319 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6320

6321 :param \*entities: column expressions to be used.

6322

6323 :param maintain_column_froms: boolean parameter that will ensure the

6324 FROM list implied from the current columns clause will be transferred

6325 to the :meth:`_sql.Select.select_from` method first.

6326

6327 .. versionadded:: 1.4.23

6328

6329 """ # noqa: E501

6330

6331 if __kw:

6332 raise _no_kw()

6333

6334 # memoizations should be cleared here as of

6335 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6336 # is the case for now.

6337 self._assert_no_memoizations()

6338

6339 if maintain_column_froms:

6340 self.select_from.non_generative( # type: ignore

6341 self, *self.columns_clause_froms

6342 )

6343

6344 # then memoize the FROMs etc.

6345 _MemoizedSelectEntities._generate_for_statement(self)

6346

6347 self._raw_columns = [

6348 coercions.expect(roles.ColumnsClauseRole, c)

6349 for c in coercions._expression_collection_was_a_list(

6350 "entities", "Select.with_only_columns", entities

6351 )

6352 ]

6353 return self

6354

6355 @property

6356 def whereclause(self) -> Optional[ColumnElement[Any]]:

6357 """Return the completed WHERE clause for this

6358 :class:`_expression.Select` statement.

6359

6360 This assembles the current collection of WHERE criteria

6361 into a single :class:`_expression.BooleanClauseList` construct.

6362

6363

6364 .. versionadded:: 1.4

6365

6366 """

6367

6368 return BooleanClauseList._construct_for_whereclause(

6369 self._where_criteria

6370 )

6371

6372 _whereclause = whereclause

6373

6374 @_generative

6375 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6376 """Return a new :func:`_expression.select` construct with

6377 the given expression added to

6378 its WHERE clause, joined to the existing clause via AND, if any.

6379

6380 """

6381

6382 assert isinstance(self._where_criteria, tuple)

6383

6384 for criterion in whereclause:

6385 where_criteria: ColumnElement[Any] = coercions.expect(

6386 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6387 )

6388 self._where_criteria += (where_criteria,)

6389 return self

6390

6391 @_generative

6392 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6393 """Return a new :func:`_expression.select` construct with

6394 the given expression added to

6395 its HAVING clause, joined to the existing clause via AND, if any.

6396

6397 """

6398

6399 for criterion in having:

6400 having_criteria = coercions.expect(

6401 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6402 )

6403 self._having_criteria += (having_criteria,)

6404 return self

6405

6406 @_generative

6407 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6408 r"""Return a new :func:`_expression.select` construct which

6409 will apply DISTINCT to the SELECT statement overall.

6410

6411 E.g.::

6412

6413 from sqlalchemy import select

6414

6415 stmt = select(users_table.c.id, users_table.c.name).distinct()

6416

6417 The above would produce an statement resembling:

6418

6419 .. sourcecode:: sql

6420

6421 SELECT DISTINCT user.id, user.name FROM user

6422

6423 The method also historically accepted an ``*expr`` parameter which

6424 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6425 This is now replaced using the :func:`_postgresql.distinct_on`

6426 extension::

6427

6428 from sqlalchemy import select

6429 from sqlalchemy.dialects.postgresql import distinct_on

6430

6431 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6432

6433 Using this parameter on other backends which don't support this

6434 syntax will raise an error.

6435

6436 :param \*expr: optional column expressions. When present,

6437 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6438 construct. A deprecation warning and/or :class:`_exc.CompileError`

6439 will be raised on other backends.

6440

6441 .. deprecated:: 2.1 Passing expressions to

6442 :meth:`_sql.Select.distinct` is deprecated, use

6443 :func:`_postgresql.distinct_on` instead.

6444

6445 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6446 and will raise :class:`_exc.CompileError` in a future version.

6447

6448 .. seealso::

6449

6450 :func:`_postgresql.distinct_on`

6451

6452 :meth:`_sql.HasSyntaxExtensions.ext`

6453 """

6454 self._distinct = True

6455 if expr:

6456 warn_deprecated(

6457 "Passing expression to ``distinct`` to generate a "

6458 "DISTINCT ON clause is deprecated. Use instead the "

6459 "``postgresql.distinct_on`` function as an extension.",

6460 "2.1",

6461 )

6462 self._distinct_on = self._distinct_on + tuple(

6463 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6464 for e in expr

6465 )

6466 return self

6467

6468 @_generative

6469 def select_from(self, *froms: _FromClauseArgument) -> Self:

6470 r"""Return a new :func:`_expression.select` construct with the

6471 given FROM expression(s)

6472 merged into its list of FROM objects.

6473

6474 E.g.::

6475

6476 table1 = table("t1", column("a"))

6477 table2 = table("t2", column("b"))

6478 s = select(table1.c.a).select_from(

6479 table1.join(table2, table1.c.a == table2.c.b)

6480 )

6481

6482 The "from" list is a unique set on the identity of each element,

6483 so adding an already present :class:`_schema.Table`

6484 or other selectable

6485 will have no effect. Passing a :class:`_expression.Join` that refers

6486 to an already present :class:`_schema.Table`

6487 or other selectable will have

6488 the effect of concealing the presence of that selectable as

6489 an individual element in the rendered FROM list, instead

6490 rendering it into a JOIN clause.

6491

6492 While the typical purpose of :meth:`_expression.Select.select_from`

6493 is to

6494 replace the default, derived FROM clause with a join, it can

6495 also be called with individual table elements, multiple times

6496 if desired, in the case that the FROM clause cannot be fully

6497 derived from the columns clause::

6498

6499 select(func.count("*")).select_from(table1)

6500

6501 """

6502

6503 self._from_obj += tuple(

6504 coercions.expect(

6505 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6506 )

6507 for fromclause in froms

6508 )

6509 return self

6510

6511 @_generative

6512 def correlate(

6513 self,

6514 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6515 ) -> Self:

6516 r"""Return a new :class:`_expression.Select`

6517 which will correlate the given FROM

6518 clauses to that of an enclosing :class:`_expression.Select`.

6519

6520 Calling this method turns off the :class:`_expression.Select` object's

6521 default behavior of "auto-correlation". Normally, FROM elements

6522 which appear in a :class:`_expression.Select`

6523 that encloses this one via

6524 its :term:`WHERE clause`, ORDER BY, HAVING or

6525 :term:`columns clause` will be omitted from this

6526 :class:`_expression.Select`

6527 object's :term:`FROM clause`.

6528 Setting an explicit correlation collection using the

6529 :meth:`_expression.Select.correlate`

6530 method provides a fixed list of FROM objects

6531 that can potentially take place in this process.

6532

6533 When :meth:`_expression.Select.correlate`

6534 is used to apply specific FROM clauses

6535 for correlation, the FROM elements become candidates for

6536 correlation regardless of how deeply nested this

6537 :class:`_expression.Select`

6538 object is, relative to an enclosing :class:`_expression.Select`

6539 which refers to

6540 the same FROM object. This is in contrast to the behavior of

6541 "auto-correlation" which only correlates to an immediate enclosing

6542 :class:`_expression.Select`.

6543 Multi-level correlation ensures that the link

6544 between enclosed and enclosing :class:`_expression.Select`

6545 is always via

6546 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6547 correlation to take place.

6548

6549 If ``None`` is passed, the :class:`_expression.Select`

6550 object will correlate

6551 none of its FROM entries, and all will render unconditionally

6552 in the local FROM clause.

6553

6554 :param \*fromclauses: one or more :class:`.FromClause` or other

6555 FROM-compatible construct such as an ORM mapped entity to become part

6556 of the correlate collection; alternatively pass a single value

6557 ``None`` to remove all existing correlations.

6558

6559 .. seealso::

6560

6561 :meth:`_expression.Select.correlate_except`

6562

6563 :ref:`tutorial_scalar_subquery`

6564

6565 """

6566

6567 # tests failing when we try to change how these

6568 # arguments are passed

6569

6570 self._auto_correlate = False

6571 if not fromclauses or fromclauses[0] in {None, False}:

6572 if len(fromclauses) > 1:

6573 raise exc.ArgumentError(

6574 "additional FROM objects not accepted when "

6575 "passing None/False to correlate()"

6576 )

6577 self._correlate = ()

6578 else:

6579 self._correlate = self._correlate + tuple(

6580 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6581 )

6582 return self

6583

6584 @_generative

6585 def correlate_except(

6586 self,

6587 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6588 ) -> Self:

6589 r"""Return a new :class:`_expression.Select`

6590 which will omit the given FROM

6591 clauses from the auto-correlation process.

6592

6593 Calling :meth:`_expression.Select.correlate_except` turns off the

6594 :class:`_expression.Select` object's default behavior of

6595 "auto-correlation" for the given FROM elements. An element

6596 specified here will unconditionally appear in the FROM list, while

6597 all other FROM elements remain subject to normal auto-correlation

6598 behaviors.

6599

6600 If ``None`` is passed, or no arguments are passed,

6601 the :class:`_expression.Select` object will correlate all of its

6602 FROM entries.

6603

6604 :param \*fromclauses: a list of one or more

6605 :class:`_expression.FromClause`

6606 constructs, or other compatible constructs (i.e. ORM-mapped

6607 classes) to become part of the correlate-exception collection.

6608

6609 .. seealso::

6610

6611 :meth:`_expression.Select.correlate`

6612

6613 :ref:`tutorial_scalar_subquery`

6614

6615 """

6616

6617 self._auto_correlate = False

6618 if not fromclauses or fromclauses[0] in {None, False}:

6619 if len(fromclauses) > 1:

6620 raise exc.ArgumentError(

6621 "additional FROM objects not accepted when "

6622 "passing None/False to correlate_except()"

6623 )

6624 self._correlate_except = ()

6625 else:

6626 self._correlate_except = (self._correlate_except or ()) + tuple(

6627 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6628 )

6629

6630 return self

6631

6632 @HasMemoized_ro_memoized_attribute

6633 def selected_columns(

6634 self,

6635 ) -> ColumnCollection[str, ColumnElement[Any]]:

6636 """A :class:`_expression.ColumnCollection`

6637 representing the columns that

6638 this SELECT statement or similar construct returns in its result set,

6639 not including :class:`_sql.TextClause` constructs.

6640

6641 This collection differs from the :attr:`_expression.FromClause.columns`

6642 collection of a :class:`_expression.FromClause` in that the columns

6643 within this collection cannot be directly nested inside another SELECT

6644 statement; a subquery must be applied first which provides for the

6645 necessary parenthesization required by SQL.

6646

6647 For a :func:`_expression.select` construct, the collection here is

6648 exactly what would be rendered inside the "SELECT" statement, and the

6649 :class:`_expression.ColumnElement` objects are directly present as they

6650 were given, e.g.::

6651

6652 col1 = column("q", Integer)

6653 col2 = column("p", Integer)

6654 stmt = select(col1, col2)

6655

6656 Above, ``stmt.selected_columns`` would be a collection that contains

6657 the ``col1`` and ``col2`` objects directly. For a statement that is

6658 against a :class:`_schema.Table` or other

6659 :class:`_expression.FromClause`, the collection will use the

6660 :class:`_expression.ColumnElement` objects that are in the

6661 :attr:`_expression.FromClause.c` collection of the from element.

6662

6663 A use case for the :attr:`_sql.Select.selected_columns` collection is

6664 to allow the existing columns to be referenced when adding additional

6665 criteria, e.g.::

6666

6667 def filter_on_id(my_select, id):

6668 return my_select.where(my_select.selected_columns["id"] == id)

6669

6670

6671 stmt = select(MyModel)

6672

6673 # adds "WHERE id=:param" to the statement

6674 stmt = filter_on_id(stmt, 42)

6675

6676 .. note::

6677

6678 The :attr:`_sql.Select.selected_columns` collection does not

6679 include expressions established in the columns clause using the

6680 :func:`_sql.text` construct; these are silently omitted from the

6681 collection. To use plain textual column expressions inside of a

6682 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6683 construct.

6684

6685

6686 .. versionadded:: 1.4

6687

6688 """

6689

6690 # compare to SelectState._generate_columns_plus_names, which

6691 # generates the actual names used in the SELECT string. that

6692 # method is more complex because it also renders columns that are

6693 # fully ambiguous, e.g. same column more than once.

6694 conv = cast(

6695 "Callable[[Any], str]",

6696 SelectState._column_naming_convention(self._label_style),

6697 )

6698

6699 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6700 [

6701 (conv(c), c)

6702 for c in self._all_selected_columns

6703 if is_column_element(c)

6704 ]

6705 )

6706 return cc.as_readonly()

6707

6708 @HasMemoized_ro_memoized_attribute

6709 def _all_selected_columns(self) -> _SelectIterable:

6710 meth = SelectState.get_plugin_class(self).all_selected_columns

6711 return list(meth(self))

6712

6713 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6714 if self._label_style is LABEL_STYLE_NONE:

6715 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6716 return self

6717

6718 def _generate_fromclause_column_proxies(

6719 self,

6720 subquery: FromClause,

6721 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6722 primary_key: ColumnSet,

6723 foreign_keys: Set[KeyedColumnElement[Any]],

6724 *,

6725 proxy_compound_columns: Optional[

6726 Iterable[Sequence[ColumnElement[Any]]]

6727 ] = None,

6728 ) -> None:

6729 """Generate column proxies to place in the exported ``.c``

6730 collection of a subquery."""

6731

6732 if proxy_compound_columns:

6733 extra_col_iterator = proxy_compound_columns

6734 prox = [

6735 c._make_proxy(

6736 subquery,

6737 key=proxy_key,

6738 name=required_label_name,

6739 name_is_truncatable=True,

6740 compound_select_cols=extra_cols,

6741 primary_key=primary_key,

6742 foreign_keys=foreign_keys,

6743 )

6744 for (

6745 (

6746 required_label_name,

6747 proxy_key,

6748 fallback_label_name,

6749 c,

6750 repeated,

6751 ),

6752 extra_cols,

6753 ) in (

6754 zip(

6755 self._generate_columns_plus_names(False),

6756 extra_col_iterator,

6757 )

6758 )

6759 if is_column_element(c)

6760 ]

6761 else:

6762 prox = [

6763 c._make_proxy(

6764 subquery,

6765 key=proxy_key,

6766 name=required_label_name,

6767 name_is_truncatable=True,

6768 primary_key=primary_key,

6769 foreign_keys=foreign_keys,

6770 )

6771 for (

6772 required_label_name,

6773 proxy_key,

6774 fallback_label_name,

6775 c,

6776 repeated,

6777 ) in (self._generate_columns_plus_names(False))

6778 if is_column_element(c)

6779 ]

6780

6781 columns._populate_separate_keys(prox)

6782

6783 def _needs_parens_for_grouping(self) -> bool:

6784 return self._has_row_limiting_clause or bool(

6785 self._order_by_clause.clauses

6786 )

6787

6788 def self_group(

6789 self, against: Optional[OperatorType] = None

6790 ) -> Union[SelectStatementGrouping[Self], Self]:

6791 """Return a 'grouping' construct as per the

6792 :class:`_expression.ClauseElement` specification.

6793

6794 This produces an element that can be embedded in an expression. Note

6795 that this method is called automatically as needed when constructing

6796 expressions and should not require explicit use.

6797

6798 """

6799 if (

6800 isinstance(against, CompoundSelect)

6801 and not self._needs_parens_for_grouping()

6802 ):

6803 return self

6804 else:

6805 return SelectStatementGrouping(self)

6806

6807 def union(

6808 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6809 ) -> CompoundSelect[Unpack[_Ts]]:

6810 r"""Return a SQL ``UNION`` of this select() construct against

6811 the given selectables provided as positional arguments.

6812

6813 :param \*other: one or more elements with which to create a

6814 UNION.

6815

6816 .. versionchanged:: 1.4.28

6817

6818 multiple elements are now accepted.

6819

6820 :param \**kwargs: keyword arguments are forwarded to the constructor

6821 for the newly created :class:`_sql.CompoundSelect` object.

6822

6823 """

6824 return CompoundSelect._create_union(self, *other)

6825

6826 def union_all(

6827 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6828 ) -> CompoundSelect[Unpack[_Ts]]:

6829 r"""Return a SQL ``UNION ALL`` of this select() construct against

6830 the given selectables provided as positional arguments.

6831

6832 :param \*other: one or more elements with which to create a

6833 UNION.

6834

6835 .. versionchanged:: 1.4.28

6836

6837 multiple elements are now accepted.

6838

6839 :param \**kwargs: keyword arguments are forwarded to the constructor

6840 for the newly created :class:`_sql.CompoundSelect` object.

6841

6842 """

6843 return CompoundSelect._create_union_all(self, *other)

6844

6845 def except_(

6846 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6847 ) -> CompoundSelect[Unpack[_Ts]]:

6848 r"""Return a SQL ``EXCEPT`` of this select() construct against

6849 the given selectable provided as positional arguments.

6850

6851 :param \*other: one or more elements with which to create a

6852 UNION.

6853

6854 .. versionchanged:: 1.4.28

6855

6856 multiple elements are now accepted.

6857

6858 """

6859 return CompoundSelect._create_except(self, *other)

6860

6861 def except_all(

6862 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6863 ) -> CompoundSelect[Unpack[_Ts]]:

6864 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6865 the given selectables provided as positional arguments.

6866

6867 :param \*other: one or more elements with which to create a

6868 UNION.

6869

6870 .. versionchanged:: 1.4.28

6871

6872 multiple elements are now accepted.

6873

6874 """

6875 return CompoundSelect._create_except_all(self, *other)

6876

6877 def intersect(

6878 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6879 ) -> CompoundSelect[Unpack[_Ts]]:

6880 r"""Return a SQL ``INTERSECT`` of this select() construct against

6881 the given selectables provided as positional arguments.

6882

6883 :param \*other: one or more elements with which to create a

6884 UNION.

6885

6886 .. versionchanged:: 1.4.28

6887

6888 multiple elements are now accepted.

6889

6890 :param \**kwargs: keyword arguments are forwarded to the constructor

6891 for the newly created :class:`_sql.CompoundSelect` object.

6892

6893 """

6894 return CompoundSelect._create_intersect(self, *other)

6895

6896 def intersect_all(

6897 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6898 ) -> CompoundSelect[Unpack[_Ts]]:

6899 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6900 against the given selectables provided as positional arguments.

6901

6902 :param \*other: one or more elements with which to create a

6903 UNION.

6904

6905 .. versionchanged:: 1.4.28

6906

6907 multiple elements are now accepted.

6908

6909 :param \**kwargs: keyword arguments are forwarded to the constructor

6910 for the newly created :class:`_sql.CompoundSelect` object.

6911

6912 """

6913 return CompoundSelect._create_intersect_all(self, *other)

6914

6915

6916class ScalarSelect(

6917 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6918):

6919 """Represent a scalar subquery.

6920

6921

6922 A :class:`_sql.ScalarSelect` is created by invoking the

6923 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6924 then participates in other SQL expressions as a SQL column expression

6925 within the :class:`_sql.ColumnElement` hierarchy.

6926

6927 .. seealso::

6928

6929 :meth:`_sql.SelectBase.scalar_subquery`

6930

6931 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6932

6933 """

6934

6935 _traverse_internals: _TraverseInternalsType = [

6936 ("element", InternalTraversal.dp_clauseelement),

6937 ("type", InternalTraversal.dp_type),

6938 ]

6939

6940 _from_objects: List[FromClause] = []

6941 _is_from_container = True

6942 if not TYPE_CHECKING:

6943 _is_implicitly_boolean = False

6944 inherit_cache = True

6945

6946 element: SelectBase

6947

6948 def __init__(self, element: SelectBase) -> None:

6949 self.element = element

6950 self.type = element._scalar_type()

6951 self._propagate_attrs = element._propagate_attrs

6952

6953 def __getattr__(self, attr: str) -> Any:

6954 return getattr(self.element, attr)

6955

6956 def __getstate__(self) -> Dict[str, Any]:

6957 return {"element": self.element, "type": self.type}

6958

6959 def __setstate__(self, state: Dict[str, Any]) -> None:

6960 self.element = state["element"]

6961 self.type = state["type"]

6962

6963 @property

6964 def columns(self) -> NoReturn:

6965 raise exc.InvalidRequestError(

6966 "Scalar Select expression has no "

6967 "columns; use this object directly "

6968 "within a column-level expression."

6969 )

6970

6971 c = columns

6972

6973 @_generative

6974 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6975 """Apply a WHERE clause to the SELECT statement referred to

6976 by this :class:`_expression.ScalarSelect`.

6977

6978 """

6979 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6980 crit

6981 )

6982 return self

6983

6984 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6985 return self

6986

6987 def _ungroup(self) -> Self:

6988 return self

6989

6990 @_generative

6991 def correlate(

6992 self,

6993 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6994 ) -> Self:

6995 r"""Return a new :class:`_expression.ScalarSelect`

6996 which will correlate the given FROM

6997 clauses to that of an enclosing :class:`_expression.Select`.

6998

6999 This method is mirrored from the :meth:`_sql.Select.correlate` method

7000 of the underlying :class:`_sql.Select`. The method applies the

7001 :meth:_sql.Select.correlate` method, then returns a new

7002 :class:`_sql.ScalarSelect` against that statement.

7003

7004 .. versionadded:: 1.4 Previously, the

7005 :meth:`_sql.ScalarSelect.correlate`

7006 method was only available from :class:`_sql.Select`.

7007

7008 :param \*fromclauses: a list of one or more

7009 :class:`_expression.FromClause`

7010 constructs, or other compatible constructs (i.e. ORM-mapped

7011 classes) to become part of the correlate collection.

7012

7013 .. seealso::

7014

7015 :meth:`_expression.ScalarSelect.correlate_except`

7016

7017 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7018

7019

7020 """

7021 self.element = cast(

7022 "Select[Unpack[TupleAny]]", self.element

7023 ).correlate(*fromclauses)

7024 return self

7025

7026 @_generative

7027 def correlate_except(

7028 self,

7029 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7030 ) -> Self:

7031 r"""Return a new :class:`_expression.ScalarSelect`

7032 which will omit the given FROM

7033 clauses from the auto-correlation process.

7034

7035 This method is mirrored from the

7036 :meth:`_sql.Select.correlate_except` method of the underlying

7037 :class:`_sql.Select`. The method applies the

7038 :meth:_sql.Select.correlate_except` method, then returns a new

7039 :class:`_sql.ScalarSelect` against that statement.

7040

7041 .. versionadded:: 1.4 Previously, the

7042 :meth:`_sql.ScalarSelect.correlate_except`

7043 method was only available from :class:`_sql.Select`.

7044

7045 :param \*fromclauses: a list of one or more

7046 :class:`_expression.FromClause`

7047 constructs, or other compatible constructs (i.e. ORM-mapped

7048 classes) to become part of the correlate-exception collection.

7049

7050 .. seealso::

7051

7052 :meth:`_expression.ScalarSelect.correlate`

7053

7054 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7055

7056

7057 """

7058

7059 self.element = cast(

7060 "Select[Unpack[TupleAny]]", self.element

7061 ).correlate_except(*fromclauses)

7062 return self

7063

7064

7065class Exists(UnaryExpression[bool]):

7066 """Represent an ``EXISTS`` clause.

7067

7068 See :func:`_sql.exists` for a description of usage.

7069

7070 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7071 instance by calling :meth:`_sql.SelectBase.exists`.

7072

7073 """

7074

7075 inherit_cache = True

7076

7077 def __init__(

7078 self,

7079 __argument: Optional[

7080 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7081 ] = None,

7082 /,

7083 ):

7084 s: ScalarSelect[Any]

7085

7086 # TODO: this seems like we should be using coercions for this

7087 if __argument is None:

7088 s = Select(literal_column("*")).scalar_subquery()

7089 elif isinstance(__argument, SelectBase):

7090 s = __argument.scalar_subquery()

7091 s._propagate_attrs = __argument._propagate_attrs

7092 elif isinstance(__argument, ScalarSelect):

7093 s = __argument

7094 else:

7095 s = Select(__argument).scalar_subquery()

7096

7097 UnaryExpression.__init__(

7098 self,

7099 s,

7100 operator=operators.exists,

7101 type_=type_api.BOOLEANTYPE,

7102 )

7103

7104 @util.ro_non_memoized_property

7105 def _from_objects(self) -> List[FromClause]:

7106 return []

7107

7108 def _regroup(

7109 self,

7110 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7111 ) -> ScalarSelect[Any]:

7112

7113 assert isinstance(self.element, ScalarSelect)

7114 element = self.element.element

7115 if not isinstance(element, Select):

7116 raise exc.InvalidRequestError(

7117 "Can only apply this operation to a plain SELECT construct"

7118 )

7119 new_element = fn(element)

7120

7121 return_value = new_element.scalar_subquery()

7122 return return_value

7123

7124 def select(self) -> Select[bool]:

7125 r"""Return a SELECT of this :class:`_expression.Exists`.

7126

7127 e.g.::

7128

7129 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7130

7131 This will produce a statement resembling:

7132

7133 .. sourcecode:: sql

7134

7135 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7136

7137 .. seealso::

7138

7139 :func:`_expression.select` - general purpose

7140 method which allows for arbitrary column lists.

7141

7142 """ # noqa

7143

7144 return Select(self)

7145

7146 def correlate(

7147 self,

7148 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7149 ) -> Self:

7150 """Apply correlation to the subquery noted by this

7151 :class:`_sql.Exists`.

7152

7153 .. seealso::

7154

7155 :meth:`_sql.ScalarSelect.correlate`

7156

7157 """

7158 e = self._clone()

7159 e.element = self._regroup(

7160 lambda element: element.correlate(*fromclauses)

7161 )

7162 return e

7163

7164 def correlate_except(

7165 self,

7166 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7167 ) -> Self:

7168 """Apply correlation to the subquery noted by this

7169 :class:`_sql.Exists`.

7170

7171 .. seealso::

7172

7173 :meth:`_sql.ScalarSelect.correlate_except`

7174

7175 """

7176 e = self._clone()

7177 e.element = self._regroup(

7178 lambda element: element.correlate_except(*fromclauses)

7179 )

7180 return e

7181

7182 def select_from(self, *froms: _FromClauseArgument) -> Self:

7183 """Return a new :class:`_expression.Exists` construct,

7184 applying the given

7185 expression to the :meth:`_expression.Select.select_from`

7186 method of the select

7187 statement contained.

7188

7189 .. note:: it is typically preferable to build a :class:`_sql.Select`

7190 statement first, including the desired WHERE clause, then use the

7191 :meth:`_sql.SelectBase.exists` method to produce an

7192 :class:`_sql.Exists` object at once.

7193

7194 """

7195 e = self._clone()

7196 e.element = self._regroup(lambda element: element.select_from(*froms))

7197 return e

7198

7199 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7200 """Return a new :func:`_expression.exists` construct with the

7201 given expression added to

7202 its WHERE clause, joined to the existing clause via AND, if any.

7203

7204

7205 .. note:: it is typically preferable to build a :class:`_sql.Select`

7206 statement first, including the desired WHERE clause, then use the

7207 :meth:`_sql.SelectBase.exists` method to produce an

7208 :class:`_sql.Exists` object at once.

7209

7210 """

7211 e = self._clone()

7212 e.element = self._regroup(lambda element: element.where(*clause))

7213 return e

7214

7215

7216class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7217 """Wrap a :class:`_expression.TextClause` construct within a

7218 :class:`_expression.SelectBase`

7219 interface.

7220

7221 This allows the :class:`_expression.TextClause` object to gain a

7222 ``.c`` collection

7223 and other FROM-like capabilities such as

7224 :meth:`_expression.FromClause.alias`,

7225 :meth:`_expression.SelectBase.cte`, etc.

7226

7227 The :class:`_expression.TextualSelect` construct is produced via the

7228 :meth:`_expression.TextClause.columns`

7229 method - see that method for details.

7230

7231 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7232 class was renamed

7233 from ``TextAsFrom``, to more correctly suit its role as a

7234 SELECT-oriented object and not a FROM clause.

7235

7236 .. seealso::

7237

7238 :func:`_expression.text`

7239

7240 :meth:`_expression.TextClause.columns` - primary creation interface.

7241

7242 """

7243

7244 __visit_name__ = "textual_select"

7245

7246 _label_style = LABEL_STYLE_NONE

7247

7248 _traverse_internals: _TraverseInternalsType = (

7249 [

7250 ("element", InternalTraversal.dp_clauseelement),

7251 ("column_args", InternalTraversal.dp_clauseelement_list),

7252 ]

7253 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7254 + HasCTE._has_ctes_traverse_internals

7255 + ExecutableStatement._executable_traverse_internals

7256 )

7257

7258 _is_textual = True

7259

7260 is_text = True

7261 is_select = True

7262

7263 def __init__(

7264 self,

7265 text: TextClause,

7266 columns: List[_ColumnExpressionArgument[Any]],

7267 positional: bool = False,

7268 ) -> None:

7269 self._init(

7270 text,

7271 # convert for ORM attributes->columns, etc

7272 [

7273 coercions.expect(roles.LabeledColumnExprRole, c)

7274 for c in columns

7275 ],

7276 positional,

7277 )

7278

7279 def _init(

7280 self,

7281 text: TextClause,

7282 columns: List[NamedColumn[Any]],

7283 positional: bool = False,

7284 ) -> None:

7285 self.element = text

7286 self.column_args = columns

7287 self.positional = positional

7288

7289 @HasMemoized_ro_memoized_attribute

7290 def selected_columns(

7291 self,

7292 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7293 """A :class:`_expression.ColumnCollection`

7294 representing the columns that

7295 this SELECT statement or similar construct returns in its result set,

7296 not including :class:`_sql.TextClause` constructs.

7297

7298 This collection differs from the :attr:`_expression.FromClause.columns`

7299 collection of a :class:`_expression.FromClause` in that the columns

7300 within this collection cannot be directly nested inside another SELECT

7301 statement; a subquery must be applied first which provides for the

7302 necessary parenthesization required by SQL.

7303

7304 For a :class:`_expression.TextualSelect` construct, the collection

7305 contains the :class:`_expression.ColumnElement` objects that were

7306 passed to the constructor, typically via the

7307 :meth:`_expression.TextClause.columns` method.

7308

7309

7310 .. versionadded:: 1.4

7311

7312 """

7313 return ColumnCollection(

7314 (c.key, c) for c in self.column_args

7315 ).as_readonly()

7316

7317 @util.ro_non_memoized_property

7318 def _all_selected_columns(self) -> _SelectIterable:

7319 return self.column_args

7320

7321 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7322 return self

7323

7324 def _ensure_disambiguated_names(self) -> TextualSelect:

7325 return self

7326

7327 @_generative

7328 def bindparams(

7329 self,

7330 *binds: BindParameter[Any],

7331 **bind_as_values: Any,

7332 ) -> Self:

7333 self.element = self.element.bindparams(*binds, **bind_as_values)

7334 return self

7335

7336 def _generate_fromclause_column_proxies(

7337 self,

7338 fromclause: FromClause,

7339 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7340 primary_key: ColumnSet,

7341 foreign_keys: Set[KeyedColumnElement[Any]],

7342 *,

7343 proxy_compound_columns: Optional[

7344 Iterable[Sequence[ColumnElement[Any]]]

7345 ] = None,

7346 ) -> None:

7347 if TYPE_CHECKING:

7348 assert isinstance(fromclause, Subquery)

7349

7350 if proxy_compound_columns:

7351 columns._populate_separate_keys(

7352 c._make_proxy(

7353 fromclause,

7354 compound_select_cols=extra_cols,

7355 primary_key=primary_key,

7356 foreign_keys=foreign_keys,

7357 )

7358 for c, extra_cols in zip(

7359 self.column_args, proxy_compound_columns

7360 )

7361 )

7362 else:

7363 columns._populate_separate_keys(

7364 c._make_proxy(

7365 fromclause,

7366 primary_key=primary_key,

7367 foreign_keys=foreign_keys,

7368 )

7369 for c in self.column_args

7370 )

7371

7372 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7373 return self.column_args[0].type

7374

7375

7376TextAsFrom = TextualSelect

7377"""Backwards compatibility with the previous name"""

7378

7379

7380class AnnotatedFromClause(Annotated):

7381 def _copy_internals(

7382 self,

7383 _annotations_traversal: bool = False,

7384 ind_cols_on_fromclause: bool = False,

7385 **kw: Any,

7386 ) -> None:

7387 super()._copy_internals(**kw)

7388

7389 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7390 # the traversals used by annotations for these cases are not currently

7391 # designed around expecting that inner elements inside of

7392 # AnnotatedFromClause's element are also deep copied, so skip for these

7393 # cases. in other cases such as plain visitors.cloned_traverse(), we

7394 # expect this to happen. see issue #12915

7395 if not _annotations_traversal:

7396 ee = self._Annotated__element # type: ignore

7397 ee._copy_internals(**kw)

7398

7399 if ind_cols_on_fromclause:

7400 # passed from annotations._deep_annotate(). See that function

7401 # for notes

7402 ee = self._Annotated__element # type: ignore

7403 self.c = ee.__class__.c.fget(self) # type: ignore

7404

7405 @util.ro_memoized_property

7406 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7407 """proxy the .c collection of the underlying FromClause.

7408

7409 Originally implemented in 2008 as a simple load of the .c collection

7410 when the annotated construct was created (see d3621ae961a), in modern

7411 SQLAlchemy versions this can be expensive for statements constructed

7412 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7413 it, which works just as well.

7414

7415 Two different use cases seem to require the collection either copied

7416 from the underlying one, or unique to this AnnotatedFromClause.

7417

7418 See test_selectable->test_annotated_corresponding_column

7419

7420 """

7421 ee = self._Annotated__element # type: ignore

7422 return ee.c # type: ignore