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

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

1165 FROM table1 JOIN table2 ON true

1166

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

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

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

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

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

1172

1173

1174 .. versionadded:: 1.4

1175

1176 """ # noqa: E501

1177

1178 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1181 when generating the columns clause of a SELECT statement.

1182

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

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

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

1186

1187 .. sourcecode:: pycon+sql

1188

1189 >>> from sqlalchemy import (

1190 ... table,

1191 ... column,

1192 ... select,

1193 ... true,

1194 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1195 ... )

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

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

1198 >>> print(

1199 ... select(table1, table2)

1200 ... .join(table2, true())

1201 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1202 ... )

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

1204 FROM table1 JOIN table2 ON true

1205

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

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

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

1209

1210 .. versionadded:: 1.4

1211

1212 """ # noqa: E501

1213

1214 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1215 """The default label style, refers to

1216 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1217

1218 .. versionadded:: 1.4

1219

1220 """

1221

1222 LABEL_STYLE_LEGACY_ORM = 3

1223

1224

1225(

1226 LABEL_STYLE_NONE,

1227 LABEL_STYLE_TABLENAME_PLUS_COL,

1228 LABEL_STYLE_DISAMBIGUATE_ONLY,

1229 _,

1230) = list(SelectLabelStyle)

1231

1232LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1233

1234

1235class Join(roles.DMLTableRole, FromClause):

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

1237 :class:`_expression.FromClause`

1238 elements.

1239

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

1241 is the module-level

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

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

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

1245 :class:`_schema.Table`).

1246

1247 .. seealso::

1248

1249 :func:`_expression.join`

1250

1251 :meth:`_expression.FromClause.join`

1252

1253 """

1254

1255 __visit_name__ = "join"

1256

1257 _traverse_internals: _TraverseInternalsType = [

1258 ("left", InternalTraversal.dp_clauseelement),

1259 ("right", InternalTraversal.dp_clauseelement),

1260 ("onclause", InternalTraversal.dp_clauseelement),

1261 ("isouter", InternalTraversal.dp_boolean),

1262 ("full", InternalTraversal.dp_boolean),

1263 ]

1264

1265 _is_join = True

1266

1267 left: FromClause

1268 right: FromClause

1269 onclause: Optional[ColumnElement[bool]]

1270 isouter: bool

1271 full: bool

1272

1273 def __init__(

1274 self,

1275 left: _FromClauseArgument,

1276 right: _FromClauseArgument,

1277 onclause: Optional[_OnClauseArgument] = None,

1278 isouter: bool = False,

1279 full: bool = False,

1280 ):

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

1282

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

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

1285 :class:`_expression.FromClause` object.

1286

1287 """

1288

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

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

1291 # annotated objects. test_orm.py -> test_fetch_results

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

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

1294 # callcounts for a single compilation in that particular test

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

1296 # 29200 -> 30373

1297

1298 self.left = coercions.expect(

1299 roles.FromClauseRole,

1300 left,

1301 )

1302 self.right = coercions.expect(

1303 roles.FromClauseRole,

1304 right,

1305 ).self_group()

1306

1307 if onclause is None:

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

1309 else:

1310 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1311 # not merged yet

1312 self.onclause = coercions.expect(

1313 roles.OnClauseRole, onclause

1314 ).self_group(against=operators._asbool)

1315

1316 self.isouter = isouter

1317 self.full = full

1318

1319 @util.ro_non_memoized_property

1320 def description(self) -> str:

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

1322 self.left.description,

1323 id(self.left),

1324 self.right.description,

1325 id(self.right),

1326 )

1327

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

1329 return (

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

1331 # as well

1332 hash(fromclause) == hash(self)

1333 or self.left.is_derived_from(fromclause)

1334 or self.right.is_derived_from(fromclause)

1335 )

1336

1337 def self_group(

1338 self, against: Optional[OperatorType] = None

1339 ) -> FromGrouping:

1340 return FromGrouping(self)

1341

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

1343 def _populate_column_collection(

1344 self,

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

1346 primary_key: ColumnSet,

1347 foreign_keys: Set[KeyedColumnElement[Any]],

1348 ) -> None:

1349 sqlutil = util.preloaded.sql_util

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

1351 c for c in self.right.c

1352 ]

1353

1354 primary_key.extend(

1355 sqlutil.reduce_columns(

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

1357 )

1358 )

1359 columns._populate_separate_keys(

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

1361 )

1362 foreign_keys.update(

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

1364 )

1365

1366 def _copy_internals(

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

1368 ) -> None:

1369 # see Select._copy_internals() for similar concept

1370

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

1372 # determine the new FROM clauses

1373 all_the_froms = set(

1374 itertools.chain(

1375 _from_objects(self.left),

1376 _from_objects(self.right),

1377 )

1378 )

1379

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

1381 # cache used by the clone function

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

1383

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

1385 # ColumnClause with parent table referring to those

1386 # replaced FromClause objects

1387 def replace(

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

1389 **kw: Any,

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

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

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

1393 return newelem

1394 return None

1395

1396 kw["replace"] = replace

1397

1398 # run normal _copy_internals. the clones for

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

1400 # cache

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

1402

1403 self._reset_memoizations()

1404

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

1406 super()._refresh_for_new_column(column)

1407 self.left._refresh_for_new_column(column)

1408 self.right._refresh_for_new_column(column)

1409

1410 def _match_primaries(

1411 self,

1412 left: FromClause,

1413 right: FromClause,

1414 ) -> ColumnElement[bool]:

1415 if isinstance(left, Join):

1416 left_right = left.right

1417 else:

1418 left_right = None

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

1420

1421 @classmethod

1422 def _join_condition(

1423 cls,

1424 a: FromClause,

1425 b: FromClause,

1426 *,

1427 a_subset: Optional[FromClause] = None,

1428 consider_as_foreign_keys: Optional[

1429 AbstractSet[ColumnClause[Any]]

1430 ] = None,

1431 ) -> ColumnElement[bool]:

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

1433

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

1435

1436 """

1437 constraints = cls._joincond_scan_left_right(

1438 a, a_subset, b, consider_as_foreign_keys

1439 )

1440

1441 if len(constraints) > 1:

1442 cls._joincond_trim_constraints(

1443 a, b, constraints, consider_as_foreign_keys

1444 )

1445

1446 if len(constraints) == 0:

1447 if isinstance(b, FromGrouping):

1448 hint = (

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

1450 "subquery using alias()?"

1451 )

1452 else:

1453 hint = ""

1454 raise exc.NoForeignKeysError(

1455 "Can't find any foreign key relationships "

1456 "between '%s' and '%s'.%s"

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

1458 )

1459

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

1461 if len(crit) == 1:

1462 return crit[0]

1463 else:

1464 return and_(*crit)

1465

1466 @classmethod

1467 def _can_join(

1468 cls,

1469 left: FromClause,

1470 right: FromClause,

1471 *,

1472 consider_as_foreign_keys: Optional[

1473 AbstractSet[ColumnClause[Any]]

1474 ] = None,

1475 ) -> bool:

1476 if isinstance(left, Join):

1477 left_right = left.right

1478 else:

1479 left_right = None

1480

1481 constraints = cls._joincond_scan_left_right(

1482 a=left,

1483 b=right,

1484 a_subset=left_right,

1485 consider_as_foreign_keys=consider_as_foreign_keys,

1486 )

1487

1488 return bool(constraints)

1489

1490 @classmethod

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

1492 def _joincond_scan_left_right(

1493 cls,

1494 a: FromClause,

1495 a_subset: Optional[FromClause],

1496 b: FromClause,

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

1498 ) -> collections.defaultdict[

1499 Optional[ForeignKeyConstraint],

1500 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1501 ]:

1502 sql_util = util.preloaded.sql_util

1503

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

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

1506

1507 constraints: collections.defaultdict[

1508 Optional[ForeignKeyConstraint],

1509 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1510 ] = collections.defaultdict(list)

1511

1512 for left in (a_subset, a):

1513 if left is None:

1514 continue

1515 for fk in sorted(

1516 b.foreign_keys,

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

1518 ):

1519 if (

1520 consider_as_foreign_keys is not None

1521 and fk.parent not in consider_as_foreign_keys

1522 ):

1523 continue

1524 try:

1525 col = fk.get_referent(left)

1526 except exc.NoReferenceError as nrte:

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

1528 if nrte.table_name in table_names:

1529 raise

1530 else:

1531 continue

1532

1533 if col is not None:

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

1535 if left is not b:

1536 for fk in sorted(

1537 left.foreign_keys,

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

1539 ):

1540 if (

1541 consider_as_foreign_keys is not None

1542 and fk.parent not in consider_as_foreign_keys

1543 ):

1544 continue

1545 try:

1546 col = fk.get_referent(b)

1547 except exc.NoReferenceError as nrte:

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

1549 if nrte.table_name in table_names:

1550 raise

1551 else:

1552 continue

1553

1554 if col is not None:

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

1556 if constraints:

1557 break

1558 return constraints

1559

1560 @classmethod

1561 def _joincond_trim_constraints(

1562 cls,

1563 a: FromClause,

1564 b: FromClause,

1565 constraints: Dict[Any, Any],

1566 consider_as_foreign_keys: Optional[Any],

1567 ) -> None:

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

1569 # to include just those FKCs that match exactly to

1570 # "consider_as_foreign_keys".

1571 if consider_as_foreign_keys:

1572 for const in list(constraints):

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

1574 consider_as_foreign_keys

1575 ):

1576 del constraints[const]

1577

1578 # if still multiple constraints, but

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

1580 if len(constraints) > 1:

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

1582 if len(dedupe) == 1:

1583 key = list(constraints)[0]

1584 constraints = {key: constraints[key]}

1585

1586 if len(constraints) != 1:

1587 raise exc.AmbiguousForeignKeysError(

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

1589 "tables have more than one foreign key "

1590 "constraint relationship between them. "

1591 "Please specify the 'onclause' of this "

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

1593 )

1594

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

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

1597 :class:`_expression.Join`.

1598

1599 E.g.::

1600

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

1602

1603 stmt = stmt.select()

1604

1605 The above will produce a SQL string resembling:

1606

1607 .. sourcecode:: sql

1608

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

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

1611

1612 """

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

1614

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

1616 def _anonymous_fromclause(

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

1618 ) -> TODO_Any:

1619 sqlutil = util.preloaded.sql_util

1620 if flat:

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

1622 left_name = name # will recurse

1623 else:

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

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

1626 else:

1627 left_name = name

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

1629 right_name = name # will recurse

1630 else:

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

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

1633 else:

1634 right_name = name

1635 left_a, right_a = (

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

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

1638 )

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

1640 sqlutil.ClauseAdapter(right_a)

1641 )

1642

1643 return left_a.join(

1644 right_a,

1645 adapter.traverse(self.onclause),

1646 isouter=self.isouter,

1647 full=self.full,

1648 )

1649 else:

1650 return (

1651 self.select()

1652 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1653 .correlate(None)

1654 .alias(name)

1655 )

1656

1657 @util.ro_non_memoized_property

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

1659 return itertools.chain(

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

1661 )

1662

1663 @util.ro_non_memoized_property

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

1665 self_list: List[FromClause] = [self]

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

1667

1668

1669class NoInit:

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

1671 raise NotImplementedError(

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

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

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

1675 "selectable objects."

1676 % (

1677 self.__class__.__name__,

1678 self.__class__.__name__.lower(),

1679 self.__class__.__name__.lower(),

1680 )

1681 )

1682

1683

1684class LateralFromClause(NamedFromClause):

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

1686

1687

1688# FromClause ->

1689# AliasedReturnsRows

1690# -> Alias only for FromClause

1691# -> Subquery only for SelectBase

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

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

1694# w/ non-deprecated coercion

1695# -> TableSample -> only for FromClause

1696

1697

1698class AliasedReturnsRows(NoInit, NamedFromClause):

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

1700 selectables."""

1701

1702 _is_from_container = True

1703

1704 _supports_derived_columns = False

1705

1706 element: ReturnsRows

1707

1708 _traverse_internals: _TraverseInternalsType = [

1709 ("element", InternalTraversal.dp_clauseelement),

1710 ("name", InternalTraversal.dp_anon_name),

1711 ]

1712

1713 @classmethod

1714 def _construct(

1715 cls,

1716 selectable: Any,

1717 *,

1718 name: Optional[str] = None,

1719 **kw: Any,

1720 ) -> Self:

1721 obj = cls.__new__(cls)

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

1723 return obj

1724

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

1726 self.element = coercions.expect(

1727 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1728 )

1729 self.element = selectable

1730 self._orig_name = name

1731 if name is None:

1732 if (

1733 isinstance(selectable, FromClause)

1734 and selectable.named_with_column

1735 ):

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

1737 if isinstance(name, _anonymous_label):

1738 name = None

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

1740 self.name = name

1741

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

1743 super()._refresh_for_new_column(column)

1744 self.element._refresh_for_new_column(column)

1745

1746 def _populate_column_collection(

1747 self,

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

1749 primary_key: ColumnSet,

1750 foreign_keys: Set[KeyedColumnElement[Any]],

1751 ) -> None:

1752 self.element._generate_fromclause_column_proxies(

1753 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1754 )

1755

1756 @util.ro_non_memoized_property

1757 def description(self) -> str:

1758 name = self.name

1759 if isinstance(name, _anonymous_label):

1760 return "anon_1"

1761

1762 return name

1763

1764 @util.ro_non_memoized_property

1765 def implicit_returning(self) -> bool:

1766 return self.element.implicit_returning # type: ignore

1767

1768 @property

1769 def original(self) -> ReturnsRows:

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

1771 return self.element

1772

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

1774 if fromclause in self._cloned_set:

1775 return True

1776 return self.element.is_derived_from(fromclause)

1777

1778 def _copy_internals(

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

1780 ) -> None:

1781 existing_element = self.element

1782

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

1784

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

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

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

1788 # performance.

1789 if existing_element is not self.element:

1790 self._reset_column_collection()

1791

1792 @property

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

1794 return [self]

1795

1796

1797class FromClauseAlias(AliasedReturnsRows):

1798 element: FromClause

1799

1800 @util.ro_non_memoized_property

1801 def description(self) -> str:

1802 name = self.name

1803 if isinstance(name, _anonymous_label):

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

1805

1806 return name

1807

1808

1809class Alias(roles.DMLTableRole, FromClauseAlias):

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

1811

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

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

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

1815

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

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

1818 method available

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

1820

1821 .. seealso::

1822

1823 :meth:`_expression.FromClause.alias`

1824

1825 """

1826

1827 __visit_name__ = "alias"

1828

1829 inherit_cache = True

1830

1831 element: FromClause

1832

1833 @classmethod

1834 def _factory(

1835 cls,

1836 selectable: FromClause,

1837 name: Optional[str] = None,

1838 flat: bool = False,

1839 ) -> NamedFromClause:

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

1841 name=name, flat=flat

1842 )

1843

1844

1845class TableValuedAlias(LateralFromClause, Alias):

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

1847

1848 This construct provides for a SQL function that returns columns

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

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

1851 method, e.g.:

1852

1853 .. sourcecode:: pycon+sql

1854

1855 >>> from sqlalchemy import select, func

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

1857 ... "value"

1858 ... )

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

1860 {printsql}SELECT anon_1.value

1861 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1862

1863 .. versionadded:: 1.4.0b2

1864

1865 .. seealso::

1866

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

1868

1869 """ # noqa: E501

1870

1871 __visit_name__ = "table_valued_alias"

1872

1873 _supports_derived_columns = True

1874 _render_derived = False

1875 _render_derived_w_types = False

1876 joins_implicitly = False

1877

1878 _traverse_internals: _TraverseInternalsType = [

1879 ("element", InternalTraversal.dp_clauseelement),

1880 ("name", InternalTraversal.dp_anon_name),

1881 ("_tableval_type", InternalTraversal.dp_type),

1882 ("_render_derived", InternalTraversal.dp_boolean),

1883 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1884 ]

1885

1886 def _init(

1887 self,

1888 selectable: Any,

1889 *,

1890 name: Optional[str] = None,

1891 table_value_type: Optional[TableValueType] = None,

1892 joins_implicitly: bool = False,

1893 ) -> None:

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

1895

1896 self.joins_implicitly = joins_implicitly

1897 self._tableval_type = (

1898 type_api.TABLEVALUE

1899 if table_value_type is None

1900 else table_value_type

1901 )

1902

1903 @HasMemoized.memoized_attribute

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

1905 """Return a column expression representing this

1906 :class:`_sql.TableValuedAlias`.

1907

1908 This accessor is used to implement the

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

1910 method for further details.

1911

1912 E.g.:

1913

1914 .. sourcecode:: pycon+sql

1915

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

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

1918

1919 .. seealso::

1920

1921 :meth:`_functions.FunctionElement.column_valued`

1922

1923 """

1924

1925 return TableValuedColumn(self, self._tableval_type)

1926

1927 def alias(

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

1929 ) -> TableValuedAlias:

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

1931

1932 This creates a distinct FROM object that will be distinguished

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

1934

1935 """

1936

1937 tva: TableValuedAlias = TableValuedAlias._construct(

1938 self,

1939 name=name,

1940 table_value_type=self._tableval_type,

1941 joins_implicitly=self.joins_implicitly,

1942 )

1943

1944 if self._render_derived:

1945 tva._render_derived = True

1946 tva._render_derived_w_types = self._render_derived_w_types

1947

1948 return tva

1949

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

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

1952 set, so that it renders as LATERAL.

1953

1954 .. seealso::

1955

1956 :func:`_expression.lateral`

1957

1958 """

1959 tva = self.alias(name=name)

1960 tva._is_lateral = True

1961 return tva

1962

1963 def render_derived(

1964 self,

1965 name: Optional[str] = None,

1966 with_types: bool = False,

1967 ) -> TableValuedAlias:

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

1969

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

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

1972

1973 .. sourcecode:: pycon+sql

1974

1975 >>> print(

1976 ... select(

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

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

1979 ... .render_derived()

1980 ... )

1981 ... )

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

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

1984

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

1986 the alias expression (this syntax currently applies to the

1987 PostgreSQL database):

1988

1989 .. sourcecode:: pycon+sql

1990

1991 >>> print(

1992 ... select(

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

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

1995 ... .render_derived(with_types=True)

1996 ... )

1997 ... )

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

1999 AS anon_1(a INTEGER, b VARCHAR)

2000

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

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

2003

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

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

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

2007

2008 """ # noqa: E501

2009

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

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

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

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

2014 # (just saw it happen on CI)

2015

2016 # construct against original to prevent memory growth

2017 # for repeated generations

2018 new_alias: TableValuedAlias = TableValuedAlias._construct(

2019 self.element,

2020 name=name,

2021 table_value_type=self._tableval_type,

2022 joins_implicitly=self.joins_implicitly,

2023 )

2024 new_alias._render_derived = True

2025 new_alias._render_derived_w_types = with_types

2026 return new_alias

2027

2028

2029class Lateral(FromClauseAlias, LateralFromClause):

2030 """Represent a LATERAL subquery.

2031

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

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

2034 method available

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

2036

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

2038 PostgreSQL versions provide support for this keyword.

2039

2040 .. seealso::

2041

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

2043

2044 """

2045

2046 __visit_name__ = "lateral"

2047 _is_lateral = True

2048

2049 inherit_cache = True

2050

2051 @classmethod

2052 def _factory(

2053 cls,

2054 selectable: Union[SelectBase, _FromClauseArgument],

2055 name: Optional[str] = None,

2056 ) -> LateralFromClause:

2057 return coercions.expect(

2058 roles.FromClauseRole, selectable, explicit_subquery=True

2059 ).lateral(name=name)

2060

2061

2062class TableSample(FromClauseAlias):

2063 """Represent a TABLESAMPLE clause.

2064

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

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

2067 method

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

2069

2070 .. seealso::

2071

2072 :func:`_expression.tablesample`

2073

2074 """

2075

2076 __visit_name__ = "tablesample"

2077

2078 _traverse_internals: _TraverseInternalsType = (

2079 AliasedReturnsRows._traverse_internals

2080 + [

2081 ("sampling", InternalTraversal.dp_clauseelement),

2082 ("seed", InternalTraversal.dp_clauseelement),

2083 ]

2084 )

2085

2086 @classmethod

2087 def _factory(

2088 cls,

2089 selectable: _FromClauseArgument,

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

2091 name: Optional[str] = None,

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

2093 ) -> TableSample:

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

2095 sampling, name=name, seed=seed

2096 )

2097

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

2099 def _init( # type: ignore[override]

2100 self,

2101 selectable: Any,

2102 *,

2103 name: Optional[str] = None,

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

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

2106 ) -> None:

2107 assert sampling is not None

2108 functions = util.preloaded.sql_functions

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

2110 sampling = functions.func.system(sampling)

2111

2112 self.sampling: Function[Any] = sampling

2113 self.seed = seed

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

2115

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

2117 return self.sampling

2118

2119

2120class CTE(

2121 roles.DMLTableRole,

2122 roles.IsCTERole,

2123 Generative,

2124 HasPrefixes,

2125 HasSuffixes,

2126 AliasedReturnsRows,

2127):

2128 """Represent a Common Table Expression.

2129

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

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

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

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

2134 :class:`_sql.Update` and

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

2136 usage details on CTEs.

2137

2138 .. seealso::

2139

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

2141

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

2143

2144 """

2145

2146 __visit_name__ = "cte"

2147

2148 _traverse_internals: _TraverseInternalsType = (

2149 AliasedReturnsRows._traverse_internals

2150 + [

2151 ("_cte_alias", InternalTraversal.dp_clauseelement),

2152 ("_restates", InternalTraversal.dp_clauseelement),

2153 ("recursive", InternalTraversal.dp_boolean),

2154 ("nesting", InternalTraversal.dp_boolean),

2155 ]

2156 + HasPrefixes._has_prefixes_traverse_internals

2157 + HasSuffixes._has_suffixes_traverse_internals

2158 )

2159

2160 element: HasCTE

2161

2162 @classmethod

2163 def _factory(

2164 cls,

2165 selectable: HasCTE,

2166 name: Optional[str] = None,

2167 recursive: bool = False,

2168 ) -> CTE:

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

2170 or Common Table Expression instance.

2171

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

2173

2174 """

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

2176 name=name, recursive=recursive

2177 )

2178

2179 def _init(

2180 self,

2181 selectable: HasCTE,

2182 *,

2183 name: Optional[str] = None,

2184 recursive: bool = False,

2185 nesting: bool = False,

2186 _cte_alias: Optional[CTE] = None,

2187 _restates: Optional[CTE] = None,

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

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

2190 ) -> None:

2191 self.recursive = recursive

2192 self.nesting = nesting

2193 self._cte_alias = _cte_alias

2194 # Keep recursivity reference with union/union_all

2195 self._restates = _restates

2196 if _prefixes:

2197 self._prefixes = _prefixes

2198 if _suffixes:

2199 self._suffixes = _suffixes

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

2201

2202 def _populate_column_collection(

2203 self,

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

2205 primary_key: ColumnSet,

2206 foreign_keys: Set[KeyedColumnElement[Any]],

2207 ) -> None:

2208 if self._cte_alias is not None:

2209 self._cte_alias._generate_fromclause_column_proxies(

2210 self,

2211 columns,

2212 primary_key=primary_key,

2213 foreign_keys=foreign_keys,

2214 )

2215 else:

2216 self.element._generate_fromclause_column_proxies(

2217 self,

2218 columns,

2219 primary_key=primary_key,

2220 foreign_keys=foreign_keys,

2221 )

2222

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

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

2225 :class:`_expression.CTE`.

2226

2227 This method is a CTE-specific specialization of the

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

2229

2230 .. seealso::

2231

2232 :ref:`tutorial_using_aliases`

2233

2234 :func:`_expression.alias`

2235

2236 """

2237 return CTE._construct(

2238 self.element,

2239 name=name,

2240 recursive=self.recursive,

2241 nesting=self.nesting,

2242 _cte_alias=self,

2243 _prefixes=self._prefixes,

2244 _suffixes=self._suffixes,

2245 )

2246

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

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

2249 of the original CTE against the given selectables provided

2250 as positional arguments.

2251

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

2253 UNION.

2254

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

2256

2257 .. seealso::

2258

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

2260

2261 """

2262 assert is_select_statement(

2263 self.element

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

2265

2266 return CTE._construct(

2267 self.element.union(*other),

2268 name=self.name,

2269 recursive=self.recursive,

2270 nesting=self.nesting,

2271 _restates=self,

2272 _prefixes=self._prefixes,

2273 _suffixes=self._suffixes,

2274 )

2275

2276 def union_all(

2277 self, *other: _SelectStatementForCompoundArgument[Any]

2278 ) -> CTE:

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

2280 of the original CTE against the given selectables provided

2281 as positional arguments.

2282

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

2284 UNION.

2285

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

2287

2288 .. seealso::

2289

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

2291

2292 """

2293

2294 assert is_select_statement(

2295 self.element

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

2297

2298 return CTE._construct(

2299 self.element.union_all(*other),

2300 name=self.name,

2301 recursive=self.recursive,

2302 nesting=self.nesting,

2303 _restates=self,

2304 _prefixes=self._prefixes,

2305 _suffixes=self._suffixes,

2306 )

2307

2308 def _get_reference_cte(self) -> CTE:

2309 """

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

2311 Updated CTEs should still refer to the original CTE.

2312 This function returns this reference identifier.

2313 """

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

2315

2316

2317class _CTEOpts(NamedTuple):

2318 nesting: bool

2319

2320

2321class _ColumnsPlusNames(NamedTuple):

2322 required_label_name: Optional[str]

2323 """

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

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

2326 """

2327

2328 proxy_key: Optional[str]

2329 """

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

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

2332 select.selected_columns collection

2333 """

2334

2335 fallback_label_name: Optional[str]

2336 """

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

2338 we have to render a label even though

2339 required_label_name was not given

2340 """

2341

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

2343 """

2344 the ColumnElement itself

2345 """

2346

2347 repeated: bool

2348 """

2349 True if this is a duplicate of a previous column

2350 in the list of columns

2351 """

2352

2353

2354class SelectsRows(ReturnsRows):

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

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

2357

2358 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2359

2360 def _generate_columns_plus_names(

2361 self,

2362 anon_for_dupe_key: bool,

2363 cols: Optional[_SelectIterable] = None,

2364 ) -> List[_ColumnsPlusNames]:

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

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

2367 on a :class:`.FromClause`.

2368

2369 This is distinct from the _column_naming_convention generator that's

2370 intended for population of the Select.selected_columns collection,

2371 different rules. the collection returned here calls upon the

2372 _column_naming_convention as well.

2373

2374 """

2375

2376 if cols is None:

2377 cols = self._all_selected_columns

2378

2379 key_naming_convention = SelectState._column_naming_convention(

2380 self._label_style

2381 )

2382

2383 names = {}

2384

2385 result: List[_ColumnsPlusNames] = []

2386 result_append = result.append

2387

2388 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2389 label_style_none = self._label_style is LABEL_STYLE_NONE

2390

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

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

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

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

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

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

2397 dedupe_hash = 1

2398

2399 for c in cols:

2400 repeated = False

2401

2402 if not c._render_label_in_columns_clause:

2403 effective_name = required_label_name = fallback_label_name = (

2404 None

2405 )

2406 elif label_style_none:

2407 if TYPE_CHECKING:

2408 assert is_column_element(c)

2409

2410 effective_name = required_label_name = None

2411 fallback_label_name = c._non_anon_label or c._anon_name_label

2412 else:

2413 if TYPE_CHECKING:

2414 assert is_column_element(c)

2415

2416 if table_qualified:

2417 required_label_name = effective_name = (

2418 fallback_label_name

2419 ) = c._tq_label

2420 else:

2421 effective_name = fallback_label_name = c._non_anon_label

2422 required_label_name = None

2423

2424 if effective_name is None:

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

2426 # not need _expression_label but it isn't

2427 # giving us a clue when to use anon_label instead

2428 expr_label = c._expression_label

2429 if expr_label is None:

2430 repeated = c._anon_name_label in names

2431 names[c._anon_name_label] = c

2432 effective_name = required_label_name = None

2433

2434 if repeated:

2435 # here, "required_label_name" is sent as

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

2437 if table_qualified:

2438 fallback_label_name = (

2439 c._dedupe_anon_tq_label_idx(dedupe_hash)

2440 )

2441 dedupe_hash += 1

2442 else:

2443 fallback_label_name = c._dedupe_anon_label_idx(

2444 dedupe_hash

2445 )

2446 dedupe_hash += 1

2447 else:

2448 fallback_label_name = c._anon_name_label

2449 else:

2450 required_label_name = effective_name = (

2451 fallback_label_name

2452 ) = expr_label

2453

2454 if effective_name is not None:

2455 if TYPE_CHECKING:

2456 assert is_column_element(c)

2457

2458 if effective_name in names:

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

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

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

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

2463 # different column under the same name. apply

2464 # disambiguating label

2465 if table_qualified:

2466 required_label_name = fallback_label_name = (

2467 c._anon_tq_label

2468 )

2469 else:

2470 required_label_name = fallback_label_name = (

2471 c._anon_name_label

2472 )

2473

2474 if anon_for_dupe_key and required_label_name in names:

2475 # here, c._anon_tq_label is definitely unique to

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

2477 # this should always be true.

2478 # this is also an infrequent codepath because

2479 # you need two levels of duplication to be here

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

2481

2482 # the column under the disambiguating label is

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

2484 # subsequent occurrences of the column so that the

2485 # original stays non-ambiguous

2486 if table_qualified:

2487 required_label_name = fallback_label_name = (

2488 c._dedupe_anon_tq_label_idx(dedupe_hash)

2489 )

2490 dedupe_hash += 1

2491 else:

2492 required_label_name = fallback_label_name = (

2493 c._dedupe_anon_label_idx(dedupe_hash)

2494 )

2495 dedupe_hash += 1

2496 repeated = True

2497 else:

2498 names[required_label_name] = c

2499 elif anon_for_dupe_key:

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

2501 # label so that the original stays non-ambiguous

2502 if table_qualified:

2503 required_label_name = fallback_label_name = (

2504 c._dedupe_anon_tq_label_idx(dedupe_hash)

2505 )

2506 dedupe_hash += 1

2507 else:

2508 required_label_name = fallback_label_name = (

2509 c._dedupe_anon_label_idx(dedupe_hash)

2510 )

2511 dedupe_hash += 1

2512 repeated = True

2513 else:

2514 names[effective_name] = c

2515

2516 result_append(

2517 _ColumnsPlusNames(

2518 required_label_name,

2519 key_naming_convention(c),

2520 fallback_label_name,

2521 c,

2522 repeated,

2523 )

2524 )

2525

2526 return result

2527

2528

2529class HasCTE(roles.HasCTERole, SelectsRows):

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

2531

2532 _has_ctes_traverse_internals: _TraverseInternalsType = [

2533 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2534 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2535 ]

2536

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

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

2539

2540 name_cte_columns: bool = False

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

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

2543

2544 .. versionadded:: 2.0.42

2545

2546 """

2547

2548 @_generative

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

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

2551

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

2553 the parent statement such that they will each be unconditionally

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

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

2556

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

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

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

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

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

2562 statement.

2563

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

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

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

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

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

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

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

2571 larger statement.

2572

2573 E.g.::

2574

2575 from sqlalchemy import table, column, select

2576

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

2578

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

2580

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

2582

2583 Would render:

2584

2585 .. sourcecode:: sql

2586

2587 WITH anon_1 AS (

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

2589 )

2590 SELECT t.c1, t.c2

2591 FROM t

2592

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

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

2595 statement.

2596

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

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

2599

2600 from sqlalchemy import table, column

2601 from sqlalchemy.dialects.postgresql import insert

2602

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

2604

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

2606

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

2608 update_statement = insert_stmt.on_conflict_do_update(

2609 index_elements=[t.c.c1],

2610 set_={

2611 "c1": insert_stmt.excluded.c1,

2612 "c2": insert_stmt.excluded.c2,

2613 },

2614 ).add_cte(delete_statement_cte)

2615

2616 print(update_statement)

2617

2618 The above statement renders as:

2619

2620 .. sourcecode:: sql

2621

2622 WITH deletions AS (

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

2624 )

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

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

2627

2628 .. versionadded:: 1.4.21

2629

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

2631

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

2633

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

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

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

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

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

2639 this statement when this flag is given.

2640

2641 .. versionadded:: 2.0

2642

2643 .. seealso::

2644

2645 :paramref:`.HasCTE.cte.nesting`

2646

2647

2648 """ # noqa: E501

2649 opt = _CTEOpts(nest_here)

2650 for cte in ctes:

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

2652 self._independent_ctes += (cte,)

2653 self._independent_ctes_opts += (opt,)

2654 return self

2655

2656 def cte(

2657 self,

2658 name: Optional[str] = None,

2659 recursive: bool = False,

2660 nesting: bool = False,

2661 ) -> CTE:

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

2663 or Common Table Expression instance.

2664

2665 Common table expressions are a SQL standard whereby SELECT

2666 statements can draw upon secondary statements specified along

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

2668 Special semantics regarding UNION can also be employed to

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

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

2671

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

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

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

2675 CTE rows.

2676

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

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

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

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

2681

2682 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2684 method may be

2685 used to establish these.

2686

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

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

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

2690 compile time.

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

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

2693 conjunction with UNION ALL in order to derive rows

2694 from those already selected.

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

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

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

2698 :paramref:`.HasCTE.add_cte.nest_here`

2699 parameter may also be used to more carefully

2700 control the exact placement of a particular CTE.

2701

2702 .. versionadded:: 1.4.24

2703

2704 .. seealso::

2705

2706 :meth:`.HasCTE.add_cte`

2707

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

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

2710 as well as additional examples.

2711

2712 Example 1, non recursive::

2713

2714 from sqlalchemy import (

2715 Table,

2716 Column,

2717 String,

2718 Integer,

2719 MetaData,

2720 select,

2721 func,

2722 )

2723

2724 metadata = MetaData()

2725

2726 orders = Table(

2727 "orders",

2728 metadata,

2729 Column("region", String),

2730 Column("amount", Integer),

2731 Column("product", String),

2732 Column("quantity", Integer),

2733 )

2734

2735 regional_sales = (

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

2737 .group_by(orders.c.region)

2738 .cte("regional_sales")

2739 )

2740

2741

2742 top_regions = (

2743 select(regional_sales.c.region)

2744 .where(

2745 regional_sales.c.total_sales

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

2747 )

2748 .cte("top_regions")

2749 )

2750

2751 statement = (

2752 select(

2753 orders.c.region,

2754 orders.c.product,

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

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

2757 )

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

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

2760 )

2761

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

2763

2764 Example 2, WITH RECURSIVE::

2765

2766 from sqlalchemy import (

2767 Table,

2768 Column,

2769 String,

2770 Integer,

2771 MetaData,

2772 select,

2773 func,

2774 )

2775

2776 metadata = MetaData()

2777

2778 parts = Table(

2779 "parts",

2780 metadata,

2781 Column("part", String),

2782 Column("sub_part", String),

2783 Column("quantity", Integer),

2784 )

2785

2786 included_parts = (

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

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

2789 .cte(recursive=True)

2790 )

2791

2792

2793 incl_alias = included_parts.alias()

2794 parts_alias = parts.alias()

2795 included_parts = included_parts.union_all(

2796 select(

2797 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2799 )

2800

2801 statement = select(

2802 included_parts.c.sub_part,

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

2804 ).group_by(included_parts.c.sub_part)

2805

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

2807

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

2809

2810 from datetime import date

2811 from sqlalchemy import (

2812 MetaData,

2813 Table,

2814 Column,

2815 Integer,

2816 Date,

2817 select,

2818 literal,

2819 and_,

2820 exists,

2821 )

2822

2823 metadata = MetaData()

2824

2825 visitors = Table(

2826 "visitors",

2827 metadata,

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

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

2830 Column("count", Integer),

2831 )

2832

2833 # add 5 visitors for the product_id == 1

2834 product_id = 1

2835 day = date.today()

2836 count = 5

2837

2838 update_cte = (

2839 visitors.update()

2840 .where(

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

2842 )

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

2844 .returning(literal(1))

2845 .cte("update_cte")

2846 )

2847

2848 upsert = visitors.insert().from_select(

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

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

2851 ~exists(update_cte.select())

2852 ),

2853 )

2854

2855 connection.execute(upsert)

2856

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

2858

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

2860

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

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

2863 "value_a", nesting=True

2864 )

2865

2866 # Nesting CTEs takes ascendency locally

2867 # over the CTEs at a higher level

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

2869

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

2871

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

2873 shown with inline parameters below as:

2874

2875 .. sourcecode:: sql

2876

2877 WITH

2878 value_a AS

2879 (SELECT 'root' AS n),

2880 value_b AS

2881 (WITH value_a AS

2882 (SELECT 'nesting' AS n)

2883 SELECT value_a.n AS n FROM value_a)

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

2885 FROM value_a, value_b

2886

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

2888 as follows (SQLAlchemy 2.0 and above)::

2889

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

2891

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

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

2894

2895 # Nesting CTEs takes ascendency locally

2896 # over the CTEs at a higher level

2897 value_b = (

2898 select(value_a_nested.c.n)

2899 .add_cte(value_a_nested, nest_here=True)

2900 .cte("value_b")

2901 )

2902

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

2904

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

2906

2907 edge = Table(

2908 "edge",

2909 metadata,

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

2911 Column("left", Integer),

2912 Column("right", Integer),

2913 )

2914

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

2916

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

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

2919 )

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

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

2922 )

2923

2924 subgraph_cte = root_node.union(left_edge, right_edge)

2925

2926 subgraph = select(subgraph_cte)

2927

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

2929

2930 .. sourcecode:: sql

2931

2932 WITH RECURSIVE nodes(node) AS (

2933 SELECT 1 AS node

2934 UNION

2935 SELECT edge."left" AS "left"

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

2937 UNION

2938 SELECT edge."right" AS "right"

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

2940 )

2941 SELECT nodes.node FROM nodes

2942

2943 .. seealso::

2944

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

2946 :meth:`_expression.HasCTE.cte`.

2947

2948 """ # noqa: E501

2949 return CTE._construct(

2950 self, name=name, recursive=recursive, nesting=nesting

2951 )

2952

2953

2954class Subquery(AliasedReturnsRows):

2955 """Represent a subquery of a SELECT.

2956

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

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

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

2960 :class:`_expression.SelectBase` subclass

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

2962 :class:`_expression.CompoundSelect`, and

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

2964 it represents the

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

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

2967

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

2969 :class:`_expression.Alias`

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

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

2972 :class:`_expression.Alias` always

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

2974 :class:`.Subquery`

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

2976

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

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

2979 statement.

2980

2981 """

2982

2983 __visit_name__ = "subquery"

2984

2985 _is_subquery = True

2986

2987 inherit_cache = True

2988

2989 element: SelectBase

2990

2991 @classmethod

2992 def _factory(

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

2994 ) -> Subquery:

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

2996

2997 return coercions.expect(

2998 roles.SelectStatementRole, selectable

2999 ).subquery(name=name)

3000

3001 @util.deprecated(

3002 "1.4",

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

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

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

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

3007 ":func:`_expression.select` "

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

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

3010 )

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

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

3013

3014

3015class FromGrouping(GroupedElement, FromClause):

3016 """Represent a grouping of a FROM clause"""

3017

3018 _traverse_internals: _TraverseInternalsType = [

3019 ("element", InternalTraversal.dp_clauseelement)

3020 ]

3021

3022 element: FromClause

3023

3024 def __init__(self, element: FromClause):

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

3026

3027 @util.ro_non_memoized_property

3028 def columns(

3029 self,

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

3031 return self.element.columns

3032

3033 @util.ro_non_memoized_property

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

3035 return self.element.columns

3036

3037 @property

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

3039 return self.element.primary_key

3040

3041 @property

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

3043 return self.element.foreign_keys

3044

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

3046 return self.element.is_derived_from(fromclause)

3047

3048 def alias(

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

3050 ) -> NamedFromGrouping:

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

3052

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

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

3055

3056 @util.ro_non_memoized_property

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

3058 return self.element._hide_froms

3059

3060 @util.ro_non_memoized_property

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

3062 return self.element._from_objects

3063

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

3065 return {"element": self.element}

3066

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

3068 self.element = state["element"]

3069

3070 if TYPE_CHECKING:

3071

3072 def self_group(

3073 self, against: Optional[OperatorType] = None

3074 ) -> Self: ...

3075

3076

3077class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3079

3080 .. versionadded:: 2.0

3081

3082 """

3083

3084 inherit_cache = True

3085

3086 if TYPE_CHECKING:

3087

3088 def self_group(

3089 self, against: Optional[OperatorType] = None

3090 ) -> Self: ...

3091

3092

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

3094 """Represents a minimal "table" construct.

3095

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

3097 collection of columns, which are typically produced

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

3099

3100 from sqlalchemy import table, column

3101

3102 user = table(

3103 "user",

3104 column("id"),

3105 column("name"),

3106 column("description"),

3107 )

3108

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

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

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

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

3113

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

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

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

3117 It's useful

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

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

3120 is not on hand.

3121

3122 """

3123

3124 __visit_name__ = "table"

3125

3126 _traverse_internals: _TraverseInternalsType = [

3127 (

3128 "columns",

3129 InternalTraversal.dp_fromclause_canonical_column_collection,

3130 ),

3131 ("name", InternalTraversal.dp_string),

3132 ("schema", InternalTraversal.dp_string),

3133 ]

3134

3135 _is_table = True

3136

3137 fullname: str

3138

3139 implicit_returning = False

3140 """:class:`_expression.TableClause`

3141 doesn't support having a primary key or column

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

3143

3144 _columns: DedupeColumnCollection[ColumnClause[Any]]

3145

3146 @util.ro_memoized_property

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

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

3149 return None

3150

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

3152 super().__init__()

3153 self.name = name

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

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

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

3157 for c in columns:

3158 self.append_column(c)

3159

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

3161 if schema is not None:

3162 self.schema = schema

3163 if self.schema is not None:

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

3165 else:

3166 self.fullname = self.name

3167 if kw:

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

3169

3170 if TYPE_CHECKING:

3171

3172 @util.ro_non_memoized_property

3173 def columns(

3174 self,

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

3176

3177 @util.ro_non_memoized_property

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

3179

3180 def __str__(self) -> str:

3181 if self.schema is not None:

3182 return self.schema + "." + self.name

3183 else:

3184 return self.name

3185

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

3187 pass

3188

3189 @util.ro_memoized_property

3190 def description(self) -> str:

3191 return self.name

3192

3193 def _insert_col_impl(

3194 self,

3195 c: ColumnClause[Any],

3196 *,

3197 index: Optional[int] = None,

3198 ) -> None:

3199 existing = c.table

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

3201 raise exc.ArgumentError(

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

3203 % (c.key, existing)

3204 )

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

3206 c.table = self

3207

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

3209 self._insert_col_impl(c)

3210

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

3212 self._insert_col_impl(c, index=index)

3213

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

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

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

3217 :class:`_expression.TableClause`.

3218

3219 E.g.::

3220

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

3222

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

3224

3225 """

3226

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

3228

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

3230 def update(self) -> Update:

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

3232 :class:`_expression.TableClause`.

3233

3234 E.g.::

3235

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

3237

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

3239

3240 """

3241 return util.preloaded.sql_dml.Update(

3242 self,

3243 )

3244

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

3246 def delete(self) -> Delete:

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

3248 :class:`_expression.TableClause`.

3249

3250 E.g.::

3251

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

3253

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

3255

3256 """

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

3258

3259 @util.ro_non_memoized_property

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

3261 return [self]

3262

3263

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

3265

3266

3267class ForUpdateArg(ClauseElement):

3268 _traverse_internals: _TraverseInternalsType = [

3269 ("of", InternalTraversal.dp_clauseelement_list),

3270 ("nowait", InternalTraversal.dp_boolean),

3271 ("read", InternalTraversal.dp_boolean),

3272 ("skip_locked", InternalTraversal.dp_boolean),

3273 ("key_share", InternalTraversal.dp_boolean),

3274 ]

3275

3276 of: Optional[Sequence[ClauseElement]]

3277 nowait: bool

3278 read: bool

3279 skip_locked: bool

3280

3281 @classmethod

3282 def _from_argument(

3283 cls, with_for_update: ForUpdateParameter

3284 ) -> Optional[ForUpdateArg]:

3285 if isinstance(with_for_update, ForUpdateArg):

3286 return with_for_update

3287 elif with_for_update in (None, False):

3288 return None

3289 elif with_for_update is True:

3290 return ForUpdateArg()

3291 else:

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

3293

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

3295 return (

3296 isinstance(other, ForUpdateArg)

3297 and other.nowait == self.nowait

3298 and other.read == self.read

3299 and other.skip_locked == self.skip_locked

3300 and other.key_share == self.key_share

3301 and other.of is self.of

3302 )

3303

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

3305 return not self.__eq__(other)

3306

3307 def __hash__(self) -> int:

3308 return id(self)

3309

3310 def __init__(

3311 self,

3312 *,

3313 nowait: bool = False,

3314 read: bool = False,

3315 of: Optional[_ForUpdateOfArgument] = None,

3316 skip_locked: bool = False,

3317 key_share: bool = False,

3318 ):

3319 """Represents arguments specified to

3320 :meth:`_expression.Select.for_update`.

3321

3322 """

3323

3324 self.nowait = nowait

3325 self.read = read

3326 self.skip_locked = skip_locked

3327 self.key_share = key_share

3328 if of is not None:

3329 self.of = [

3330 coercions.expect(roles.ColumnsClauseRole, elem)

3331 for elem in util.to_list(of)

3332 ]

3333 else:

3334 self.of = None

3335

3336

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

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

3339 in a statement.

3340

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

3342 :func:`_expression.values` function.

3343

3344 .. versionadded:: 1.4

3345

3346 """

3347

3348 __visit_name__ = "values"

3349

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

3351

3352 _unnamed: bool

3353 _traverse_internals: _TraverseInternalsType = [

3354 ("_column_args", InternalTraversal.dp_clauseelement_list),

3355 ("_data", InternalTraversal.dp_dml_multi_values),

3356 ("name", InternalTraversal.dp_string),

3357 ("literal_binds", InternalTraversal.dp_boolean),

3358 ] + HasCTE._has_ctes_traverse_internals

3359

3360 name_cte_columns = True

3361

3362 def __init__(

3363 self,

3364 *columns: ColumnClause[Any],

3365 name: Optional[str] = None,

3366 literal_binds: bool = False,

3367 ):

3368 super().__init__()

3369 self._column_args = columns

3370

3371 if name is None:

3372 self._unnamed = True

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

3374 else:

3375 self._unnamed = False

3376 self.name = name

3377 self.literal_binds = literal_binds

3378 self.named_with_column = not self._unnamed

3379

3380 @property

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

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

3383

3384 @util.ro_non_memoized_property

3385 def _all_selected_columns(self) -> _SelectIterable:

3386 return self._column_args

3387

3388 @_generative

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

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

3391 construct that is a copy of this

3392 one with the given name.

3393

3394 This method is a VALUES-specific specialization of the

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

3396

3397 .. seealso::

3398

3399 :ref:`tutorial_using_aliases`

3400

3401 :func:`_expression.alias`

3402

3403 """

3404 non_none_name: str

3405

3406 if name is None:

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

3408 else:

3409 non_none_name = name

3410

3411 self.name = non_none_name

3412 self.named_with_column = True

3413 self._unnamed = False

3414 return self

3415

3416 @_generative

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

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

3419 so that

3420 it renders as LATERAL.

3421

3422 .. seealso::

3423

3424 :func:`_expression.lateral`

3425

3426 """

3427 non_none_name: str

3428

3429 if name is None:

3430 non_none_name = self.name

3431 else:

3432 non_none_name = name

3433

3434 self._is_lateral = True

3435 self.name = non_none_name

3436 self._unnamed = False

3437 return self

3438

3439 @_generative

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

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

3442 adding the given data to the data list.

3443

3444 E.g.::

3445

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

3447

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

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

3450 constructor.

3451

3452 """

3453

3454 self._data += (values,)

3455 return self

3456

3457 def scalar_values(self) -> ScalarValues:

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

3459 COLUMN element in a statement.

3460

3461 .. versionadded:: 2.0.0b4

3462

3463 """

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

3465

3466 def _populate_column_collection(

3467 self,

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

3469 primary_key: ColumnSet,

3470 foreign_keys: Set[KeyedColumnElement[Any]],

3471 ) -> None:

3472 for c in self._column_args:

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

3474 _, c = c._make_proxy(

3475 self, primary_key=primary_key, foreign_keys=foreign_keys

3476 )

3477 else:

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

3479 # no memoizations of other FROM clauses.

3480 # see test_values.py -> test_auto_proxy_select_direct_col

3481 c._reset_memoizations()

3482 columns.add(c)

3483 c.table = self

3484

3485 @util.ro_non_memoized_property

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

3487 return [self]

3488

3489

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

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

3492 COLUMN element in a statement.

3493

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

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

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

3497 an ``IN`` or ``NOT IN`` condition.

3498

3499 .. versionadded:: 2.0.0b4

3500

3501 """

3502

3503 __visit_name__ = "scalar_values"

3504

3505 _traverse_internals: _TraverseInternalsType = [

3506 ("_column_args", InternalTraversal.dp_clauseelement_list),

3507 ("_data", InternalTraversal.dp_dml_multi_values),

3508 ("literal_binds", InternalTraversal.dp_boolean),

3509 ]

3510

3511 def __init__(

3512 self,

3513 columns: Sequence[ColumnClause[Any]],

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

3515 literal_binds: bool,

3516 ):

3517 super().__init__()

3518 self._column_args = columns

3519 self._data = data

3520 self.literal_binds = literal_binds

3521

3522 @property

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

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

3525

3526 def __clause_element__(self) -> ScalarValues:

3527 return self

3528

3529 if TYPE_CHECKING:

3530

3531 def self_group(

3532 self, against: Optional[OperatorType] = None

3533 ) -> Self: ...

3534

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

3536

3537

3538class SelectBase(

3539 roles.SelectStatementRole,

3540 roles.DMLSelectRole,

3541 roles.CompoundElementRole,

3542 roles.InElementRole,

3543 HasCTE,

3544 SupportsCloneAnnotations,

3545 Selectable,

3546):

3547 """Base class for SELECT statements.

3548

3549

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

3551 :class:`_expression.CompoundSelect` and

3552 :class:`_expression.TextualSelect`.

3553

3554

3555 """

3556

3557 _is_select_base = True

3558 is_select = True

3559

3560 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3561

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

3563 self._reset_memoizations()

3564

3565 @util.ro_non_memoized_property

3566 def selected_columns(

3567 self,

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

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

3570 representing the columns that

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

3572

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

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

3575 within this collection cannot be directly nested inside another SELECT

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

3577 necessary parenthesization required by SQL.

3578

3579 .. note::

3580

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

3582 include expressions established in the columns clause using the

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

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

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

3586 construct.

3587

3588 .. seealso::

3589

3590 :attr:`_sql.Select.selected_columns`

3591

3592 .. versionadded:: 1.4

3593

3594 """

3595 raise NotImplementedError()

3596

3597 def _generate_fromclause_column_proxies(

3598 self,

3599 subquery: FromClause,

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

3601 primary_key: ColumnSet,

3602 foreign_keys: Set[KeyedColumnElement[Any]],

3603 *,

3604 proxy_compound_columns: Optional[

3605 Iterable[Sequence[ColumnElement[Any]]]

3606 ] = None,

3607 ) -> None:

3608 raise NotImplementedError()

3609

3610 @util.ro_non_memoized_property

3611 def _all_selected_columns(self) -> _SelectIterable:

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

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

3614 constructs.

3615

3616 .. versionadded:: 1.4.12

3617

3618 .. seealso::

3619

3620 :attr:`_sql.SelectBase.exported_columns`

3621

3622 """

3623 raise NotImplementedError()

3624

3625 @property

3626 def exported_columns(

3627 self,

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

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

3630 that represents the "exported"

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

3632 :class:`_sql.TextClause` constructs.

3633

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

3635 object are synonymous

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

3637

3638 .. versionadded:: 1.4

3639

3640 .. seealso::

3641

3642 :attr:`_expression.Select.exported_columns`

3643

3644 :attr:`_expression.Selectable.exported_columns`

3645

3646 :attr:`_expression.FromClause.exported_columns`

3647

3648

3649 """

3650 return self.selected_columns.as_readonly()

3651

3652 def get_label_style(self) -> SelectLabelStyle:

3653 """

3654 Retrieve the current label style.

3655

3656 Implemented by subclasses.

3657

3658 """

3659 raise NotImplementedError()

3660

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

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

3663

3664 Implemented by subclasses.

3665

3666 """

3667

3668 raise NotImplementedError()

3669

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

3671 raise NotImplementedError()

3672

3673 @util.deprecated(

3674 "1.4",

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

3676 "method is deprecated and will be "

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

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

3679 )

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

3681 return self.scalar_subquery()

3682

3683 def exists(self) -> Exists:

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

3685 which can be used as a column expression.

3686

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

3688

3689 .. seealso::

3690

3691 :func:`_sql.exists`

3692

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

3694

3695 .. versionadded:: 1.4

3696

3697 """

3698 return Exists(self)

3699

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

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

3702 used as a column expression.

3703

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

3705

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

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

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

3709 an enclosing SELECT.

3710

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

3712 subquery that can be produced using the

3713 :meth:`_expression.SelectBase.subquery`

3714 method.

3715

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

3717 :meth:`_expression.SelectBase.scalar_subquery`.

3718

3719 .. seealso::

3720

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

3722

3723 """

3724 if self._label_style is not LABEL_STYLE_NONE:

3725 self = self.set_label_style(LABEL_STYLE_NONE)

3726

3727 return ScalarSelect(self)

3728

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

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

3731 subquery with a label.

3732

3733 .. seealso::

3734

3735 :meth:`_expression.SelectBase.scalar_subquery`.

3736

3737 """

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

3739

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

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

3742

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

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

3745

3746 .. seealso::

3747

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

3749

3750 """

3751 return Lateral._factory(self, name)

3752

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

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

3755

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

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

3758 SELECT statement.

3759

3760 Given a SELECT statement such as::

3761

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

3763

3764 The above statement might look like:

3765

3766 .. sourcecode:: sql

3767

3768 SELECT table.id, table.name FROM table

3769

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

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

3772 a named sub-element::

3773

3774 subq = stmt.subquery()

3775 new_stmt = select(subq)

3776

3777 The above renders as:

3778

3779 .. sourcecode:: sql

3780

3781 SELECT anon_1.id, anon_1.name

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

3783

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

3785 is equivalent to calling

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

3787 method on a FROM object; however,

3788 as a :class:`_expression.SelectBase`

3789 object is not directly FROM object,

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

3791 method provides clearer semantics.

3792

3793 .. versionadded:: 1.4

3794

3795 """

3796

3797 return Subquery._construct(

3798 self._ensure_disambiguated_names(), name=name

3799 )

3800

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

3802 def into(

3803 self,

3804 target: str,

3805 *,

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

3807 schema: Optional[str] = None,

3808 temporary: bool = False,

3809 if_not_exists: bool = False,

3810 ) -> CreateTableAs:

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

3812

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

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

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

3816

3817 Supported on all included backends, the construct emits

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

3819 emits a ``SELECT..INTO`` statement.

3820

3821 e.g.::

3822

3823 from sqlalchemy import select

3824

3825 # Create a new table from a SELECT

3826 stmt = (

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

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

3829 .into("active_users")

3830 )

3831

3832 with engine.begin() as conn:

3833 conn.execute(stmt)

3834

3835 # With optional flags

3836 stmt = (

3837 select(users.c.id)

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

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

3840 )

3841

3842 .. versionadded:: 2.1

3843

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

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

3846

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

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

3849 :attr:`.table` attribute will be associated with this

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

3851 is created.

3852

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

3854

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

3856

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

3858

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

3860

3861 .. seealso::

3862

3863 :ref:`tutorial_create_table_as` - in the :ref:`unified_tutorial`

3864

3865 :class:`_schema.CreateTableAs`

3866

3867 """

3868 sql_ddl = util.preloaded.sql_ddl

3869

3870 return sql_ddl.CreateTableAs(

3871 self,

3872 target,

3873 metadata=metadata,

3874 schema=schema,

3875 temporary=temporary,

3876 if_not_exists=if_not_exists,

3877 )

3878

3879 def _ensure_disambiguated_names(self) -> Self:

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

3881 disambiguated in some way, if possible.

3882

3883 """

3884

3885 raise NotImplementedError()

3886

3887 def alias(

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

3889 ) -> Subquery:

3890 """Return a named subquery against this

3891 :class:`_expression.SelectBase`.

3892

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

3894 :class:`_expression.FromClause`),

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

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

3897 :class:`_expression.FromClause`.

3898

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

3900 method is now

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

3902

3903 """

3904 return self.subquery(name=name)

3905

3906

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

3908

3909

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

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

3912

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

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

3915 compound selects.

3916

3917 """

3918

3919 __visit_name__ = "select_statement_grouping"

3920 _traverse_internals: _TraverseInternalsType = [

3921 ("element", InternalTraversal.dp_clauseelement)

3922 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3923

3924 _is_select_container = True

3925

3926 element: _SB

3927

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

3929 self.element = cast(

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

3931 )

3932

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

3934 new_element = self.element._ensure_disambiguated_names()

3935 if new_element is not self.element:

3936 return SelectStatementGrouping(new_element)

3937 else:

3938 return self

3939

3940 def get_label_style(self) -> SelectLabelStyle:

3941 return self.element.get_label_style()

3942

3943 def set_label_style(

3944 self, label_style: SelectLabelStyle

3945 ) -> SelectStatementGrouping[_SB]:

3946 return SelectStatementGrouping(

3947 self.element.set_label_style(label_style)

3948 )

3949

3950 @property

3951 def select_statement(self) -> _SB:

3952 return self.element

3953

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

3955 return self

3956

3957 if TYPE_CHECKING:

3958

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

3960

3961 # def _generate_columns_plus_names(

3962 # self, anon_for_dupe_key: bool

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

3964 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3965

3966 def _generate_fromclause_column_proxies(

3967 self,

3968 subquery: FromClause,

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

3970 primary_key: ColumnSet,

3971 foreign_keys: Set[KeyedColumnElement[Any]],

3972 *,

3973 proxy_compound_columns: Optional[

3974 Iterable[Sequence[ColumnElement[Any]]]

3975 ] = None,

3976 ) -> None:

3977 self.element._generate_fromclause_column_proxies(

3978 subquery,

3979 columns,

3980 proxy_compound_columns=proxy_compound_columns,

3981 primary_key=primary_key,

3982 foreign_keys=foreign_keys,

3983 )

3984

3985 @util.ro_non_memoized_property

3986 def _all_selected_columns(self) -> _SelectIterable:

3987 return self.element._all_selected_columns

3988

3989 @util.ro_non_memoized_property

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

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

3992 representing the columns that

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

3994 :class:`_sql.TextClause` constructs.

3995

3996 .. versionadded:: 1.4

3997

3998 .. seealso::

3999

4000 :attr:`_sql.Select.selected_columns`

4001

4002 """

4003 return self.element.selected_columns

4004

4005 @util.ro_non_memoized_property

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

4007 return self.element._from_objects

4008

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

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

4011 raise NotImplementedError

4012

4013

4014class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4016 added.

4017

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

4019 :class:`_expression.CompoundSelect`

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

4021 rendering can be controlled. Compare to

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

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

4024 and is also a SELECT construct,

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

4026 only wrapped as a subquery.

4027

4028 """

4029

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

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

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

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

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

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

4036 _for_update_arg: Optional[ForUpdateArg] = None

4037

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

4039 self._label_style = _label_style

4040

4041 @_generative

4042 def with_for_update(

4043 self,

4044 *,

4045 nowait: bool = False,

4046 read: bool = False,

4047 of: Optional[_ForUpdateOfArgument] = None,

4048 skip_locked: bool = False,

4049 key_share: bool = False,

4050 ) -> Self:

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

4052 :class:`_expression.GenerativeSelect`.

4053

4054 E.g.::

4055

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

4057

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

4059 render a statement like:

4060

4061 .. sourcecode:: sql

4062

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

4064

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

4066 would produce:

4067

4068 .. sourcecode:: sql

4069

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

4071

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

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

4074 provided which allow for common database-specific

4075 variants.

4076

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

4078 Database and PostgreSQL dialects.

4079

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

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

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

4083

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

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

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

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

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

4089 table or as a column depending on backend.

4090

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

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

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

4094

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

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

4097 on the PostgreSQL dialect.

4098

4099 """

4100 self._for_update_arg = ForUpdateArg(

4101 nowait=nowait,

4102 read=read,

4103 of=of,

4104 skip_locked=skip_locked,

4105 key_share=key_share,

4106 )

4107 return self

4108

4109 def get_label_style(self) -> SelectLabelStyle:

4110 """

4111 Retrieve the current label style.

4112

4113 .. versionadded:: 1.4

4114

4115 """

4116 return self._label_style

4117

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

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

4120

4121 There are three "label styles" available,

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

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

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

4125 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4126

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

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

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

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

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

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

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

4134 that the impact of this labeling is minimal.

4135

4136 The rationale for disambiguation is mostly so that all column

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

4138 collection when a subquery is created.

4139

4140 .. versionadded:: 1.4 - the

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

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

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

4144

4145 .. seealso::

4146

4147 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`

4148

4149 :data:`_sql.LABEL_STYLE_TABLENAME_PLUS_COL`

4150

4151 :data:`_sql.LABEL_STYLE_NONE`

4152

4153 :data:`_sql.LABEL_STYLE_DEFAULT`

4154

4155 """

4156 if self._label_style is not style:

4157 self = self._generate()

4158 self._label_style = style

4159 return self

4160

4161 @property

4162 def _group_by_clause(self) -> ClauseList:

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

4164 return ClauseList._construct_raw(

4165 operators.comma_op, self._group_by_clauses

4166 )

4167

4168 @property

4169 def _order_by_clause(self) -> ClauseList:

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

4171 return ClauseList._construct_raw(

4172 operators.comma_op, self._order_by_clauses

4173 )

4174

4175 def _offset_or_limit_clause(

4176 self,

4177 element: _LimitOffsetType,

4178 name: Optional[str] = None,

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

4180 ) -> ColumnElement[Any]:

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

4182

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

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

4185

4186 """

4187 return coercions.expect(

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

4189 )

4190

4191 @overload

4192 def _offset_or_limit_clause_asint(

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

4194 ) -> NoReturn: ...

4195

4196 @overload

4197 def _offset_or_limit_clause_asint(

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

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

4200

4201 def _offset_or_limit_clause_asint(

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

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

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

4205 integer.

4206

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

4208 parameter. Otherwise, a compilation error is raised.

4209

4210 """

4211 if clause is None:

4212 return None

4213 try:

4214 value = clause._limit_offset_value

4215 except AttributeError as err:

4216 raise exc.CompileError(

4217 "This SELECT structure does not use a simple "

4218 "integer value for %s" % attrname

4219 ) from err

4220 else:

4221 return util.asint(value)

4222

4223 @property

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

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

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

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

4228 isn't currently set to an integer.

4229

4230 """

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

4232

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

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

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

4236 """

4237 return isinstance(clause, _OffsetLimitParam)

4238

4239 @property

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

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

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

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

4244 offset isn't currently set to an integer.

4245

4246 """

4247 return self._offset_or_limit_clause_asint(

4248 self._offset_clause, "offset"

4249 )

4250

4251 @property

4252 def _has_row_limiting_clause(self) -> bool:

4253 return (

4254 self._limit_clause is not None

4255 or self._offset_clause is not None

4256 or self._fetch_clause is not None

4257 )

4258

4259 @_generative

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

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

4262 applied.

4263

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

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

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

4267 functionality.

4268

4269 .. note::

4270

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

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

4273

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

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

4276

4277 .. seealso::

4278

4279 :meth:`_sql.GenerativeSelect.fetch`

4280

4281 :meth:`_sql.GenerativeSelect.offset`

4282

4283 """

4284

4285 self._fetch_clause = self._fetch_clause_options = None

4286 self._limit_clause = self._offset_or_limit_clause(limit)

4287 return self

4288

4289 @_generative

4290 def fetch(

4291 self,

4292 count: _LimitOffsetType,

4293 with_ties: bool = False,

4294 percent: bool = False,

4295 **dialect_kw: Any,

4296 ) -> Self:

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

4298 applied.

4299

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

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

4302 select. This functionality is is currently implemented for Oracle

4303 Database, PostgreSQL, MSSQL.

4304

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

4306

4307 .. note::

4308

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

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

4311

4312 .. versionadded:: 1.4

4313

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

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

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

4317 Pass ``None`` to reset it.

4318

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

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

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

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

4323

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

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

4326

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

4328 may be accepted by dialects.

4329

4330 .. versionadded:: 2.0.41

4331

4332 .. seealso::

4333

4334 :meth:`_sql.GenerativeSelect.limit`

4335

4336 :meth:`_sql.GenerativeSelect.offset`

4337

4338 """

4339 self._validate_dialect_kwargs(dialect_kw)

4340 self._limit_clause = None

4341 if count is None:

4342 self._fetch_clause = self._fetch_clause_options = None

4343 else:

4344 self._fetch_clause = self._offset_or_limit_clause(count)

4345 self._fetch_clause_options = {

4346 "with_ties": with_ties,

4347 "percent": percent,

4348 }

4349 return self

4350

4351 @_generative

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

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

4354 applied.

4355

4356

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

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

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

4360 functionality.

4361

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

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

4364

4365 .. seealso::

4366

4367 :meth:`_sql.GenerativeSelect.limit`

4368

4369 :meth:`_sql.GenerativeSelect.fetch`

4370

4371 """

4372

4373 self._offset_clause = self._offset_or_limit_clause(offset)

4374 return self

4375

4376 @_generative

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

4378 def slice(

4379 self,

4380 start: int,

4381 stop: int,

4382 ) -> Self:

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

4384

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

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

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

4388 query.

4389

4390 For example, ::

4391

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

4393

4394 renders as

4395

4396 .. sourcecode:: sql

4397

4398 SELECT users.id AS users_id,

4399 users.name AS users_name

4400 FROM users ORDER BY users.id

4401 LIMIT ? OFFSET ?

4402 (2, 1)

4403

4404 .. note::

4405

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

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

4408

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

4410 method generalized from the ORM.

4411

4412 .. seealso::

4413

4414 :meth:`_sql.GenerativeSelect.limit`

4415

4416 :meth:`_sql.GenerativeSelect.offset`

4417

4418 :meth:`_sql.GenerativeSelect.fetch`

4419

4420 """

4421 sql_util = util.preloaded.sql_util

4422 self._fetch_clause = self._fetch_clause_options = None

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

4424 self._limit_clause, self._offset_clause, start, stop

4425 )

4426 return self

4427

4428 @_generative

4429 def order_by(

4430 self,

4431 __first: Union[

4432 Literal[None, _NoArg.NO_ARG],

4433 _ColumnExpressionOrStrLabelArgument[Any],

4434 ] = _NoArg.NO_ARG,

4435 /,

4436 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4437 ) -> Self:

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

4439 criteria applied.

4440

4441 e.g.::

4442

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

4444

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

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

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

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

4449

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

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

4452

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

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

4455

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

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

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

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

4460 label matching in ORDER BY and GROUP BY expressions.

4461

4462 .. seealso::

4463

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

4465

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

4467

4468 """

4469

4470 if not clauses and __first is None:

4471 self._order_by_clauses = ()

4472 elif __first is not _NoArg.NO_ARG:

4473 self._order_by_clauses += tuple(

4474 coercions.expect(

4475 roles.OrderByRole, clause, apply_propagate_attrs=self

4476 )

4477 for clause in (__first,) + clauses

4478 )

4479 return self

4480

4481 @_generative

4482 def group_by(

4483 self,

4484 __first: Union[

4485 Literal[None, _NoArg.NO_ARG],

4486 _ColumnExpressionOrStrLabelArgument[Any],

4487 ] = _NoArg.NO_ARG,

4488 /,

4489 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4490 ) -> Self:

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

4492 criterion applied.

4493

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

4495

4496 e.g.::

4497

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

4499

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

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

4502

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

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

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

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

4507 label matching in ORDER BY and GROUP BY expressions.

4508

4509 .. seealso::

4510

4511 :ref:`tutorial_group_by_w_aggregates` - in the

4512 :ref:`unified_tutorial`

4513

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

4515

4516 """ # noqa: E501

4517

4518 if not clauses and __first is None:

4519 self._group_by_clauses = ()

4520 elif __first is not _NoArg.NO_ARG:

4521 self._group_by_clauses += tuple(

4522 coercions.expect(

4523 roles.GroupByRole, clause, apply_propagate_attrs=self

4524 )

4525 for clause in (__first,) + clauses

4526 )

4527 return self

4528

4529

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

4531class CompoundSelectState(CompileState):

4532 @util.memoized_property

4533 def _label_resolve_dict(

4534 self,

4535 ) -> Tuple[

4536 Dict[str, ColumnElement[Any]],

4537 Dict[str, ColumnElement[Any]],

4538 Dict[str, ColumnElement[Any]],

4539 ]:

4540 # TODO: this is hacky and slow

4541 hacky_subquery = self.statement.subquery()

4542 hacky_subquery.named_with_column = False

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

4544 return d, d, d

4545

4546

4547class _CompoundSelectKeyword(Enum):

4548 UNION = "UNION"

4549 UNION_ALL = "UNION ALL"

4550 EXCEPT = "EXCEPT"

4551 EXCEPT_ALL = "EXCEPT ALL"

4552 INTERSECT = "INTERSECT"

4553 INTERSECT_ALL = "INTERSECT ALL"

4554

4555

4556class CompoundSelect(

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

4558):

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

4560 SELECT-based set operations.

4561

4562

4563 .. seealso::

4564

4565 :func:`_expression.union`

4566

4567 :func:`_expression.union_all`

4568

4569 :func:`_expression.intersect`

4570

4571 :func:`_expression.intersect_all`

4572

4573 :func:`_expression.except`

4574

4575 :func:`_expression.except_all`

4576

4577 """

4578

4579 __visit_name__ = "compound_select"

4580

4581 _traverse_internals: _TraverseInternalsType = (

4582 [

4583 ("selects", InternalTraversal.dp_clauseelement_list),

4584 ("_limit_clause", InternalTraversal.dp_clauseelement),

4585 ("_offset_clause", InternalTraversal.dp_clauseelement),

4586 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4587 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4588 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4589 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4590 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4591 ("keyword", InternalTraversal.dp_string),

4592 ]

4593 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4594 + HasCTE._has_ctes_traverse_internals

4595 + DialectKWArgs._dialect_kwargs_traverse_internals

4596 + Executable._executable_traverse_internals

4597 )

4598

4599 selects: List[SelectBase]

4600

4601 _is_from_container = True

4602 _auto_correlate = False

4603

4604 def __init__(

4605 self,

4606 keyword: _CompoundSelectKeyword,

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

4608 ):

4609 self.keyword = keyword

4610 self.selects = [

4611 coercions.expect(

4612 roles.CompoundElementRole, s, apply_propagate_attrs=self

4613 ).self_group(against=self)

4614 for s in selects

4615 ]

4616

4617 GenerativeSelect.__init__(self)

4618

4619 @classmethod

4620 def _create_union(

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

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

4623 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4624

4625 @classmethod

4626 def _create_union_all(

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

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

4629 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4630

4631 @classmethod

4632 def _create_except(

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

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

4635 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4636

4637 @classmethod

4638 def _create_except_all(

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

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

4641 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4642

4643 @classmethod

4644 def _create_intersect(

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

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

4647 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4648

4649 @classmethod

4650 def _create_intersect_all(

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

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

4653 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4654

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

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

4657

4658 def self_group(

4659 self, against: Optional[OperatorType] = None

4660 ) -> GroupedElement:

4661 return SelectStatementGrouping(self)

4662

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

4664 for s in self.selects:

4665 if s.is_derived_from(fromclause):

4666 return True

4667 return False

4668

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

4670 if self._label_style is not style:

4671 self = self._generate()

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

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

4674

4675 return self

4676

4677 def _ensure_disambiguated_names(self) -> Self:

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

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

4680 self = self._generate()

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

4682

4683 return self

4684

4685 def _generate_fromclause_column_proxies(

4686 self,

4687 subquery: FromClause,

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

4689 primary_key: ColumnSet,

4690 foreign_keys: Set[KeyedColumnElement[Any]],

4691 *,

4692 proxy_compound_columns: Optional[

4693 Iterable[Sequence[ColumnElement[Any]]]

4694 ] = None,

4695 ) -> None:

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

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

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

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

4700 # generate below.

4701 select_0 = self.selects[0]

4702

4703 if self._label_style is not LABEL_STYLE_DEFAULT:

4704 select_0 = select_0.set_label_style(self._label_style)

4705

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

4707 # derived columns place a 'weight' annotation corresponding

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

4709 # that the corresponding_column() operation can resolve

4710 # conflicts

4711 extra_col_iterator = zip(

4712 *[

4713 [

4714 c._annotate(dd)

4715 for c in stmt._all_selected_columns

4716 if is_column_element(c)

4717 ]

4718 for dd, stmt in [

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

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

4721 ]

4722 ]

4723 )

4724

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

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

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

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

4729 # compound

4730 select_0._generate_fromclause_column_proxies(

4731 subquery,

4732 columns,

4733 proxy_compound_columns=extra_col_iterator,

4734 primary_key=primary_key,

4735 foreign_keys=foreign_keys,

4736 )

4737

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

4739 super()._refresh_for_new_column(column)

4740 for select in self.selects:

4741 select._refresh_for_new_column(column)

4742

4743 @util.ro_non_memoized_property

4744 def _all_selected_columns(self) -> _SelectIterable:

4745 return self.selects[0]._all_selected_columns

4746

4747 @util.ro_non_memoized_property

4748 def selected_columns(

4749 self,

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

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

4752 representing the columns that

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

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

4755

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

4757 :attr:`_expression.CompoundSelect.selected_columns`

4758 attribute returns the selected

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

4760 statements within the set operation.

4761

4762 .. seealso::

4763

4764 :attr:`_sql.Select.selected_columns`

4765

4766 .. versionadded:: 1.4

4767

4768 """

4769 return self.selects[0].selected_columns

4770

4771

4772# backwards compat

4773for elem in _CompoundSelectKeyword:

4774 setattr(CompoundSelect, elem.name, elem)

4775

4776

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

4778class SelectState(util.MemoizedSlots, CompileState):

4779 __slots__ = (

4780 "from_clauses",

4781 "froms",

4782 "columns_plus_names",

4783 "_label_resolve_dict",

4784 )

4785

4786 if TYPE_CHECKING:

4787 default_select_compile_options: CacheableOptions

4788 else:

4789

4790 class default_select_compile_options(CacheableOptions):

4791 _cache_key_traversal = []

4792

4793 if TYPE_CHECKING:

4794

4795 @classmethod

4796 def get_plugin_class(

4797 cls, statement: Executable

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

4799

4800 def __init__(

4801 self,

4802 statement: Select[Unpack[TupleAny]],

4803 compiler: SQLCompiler,

4804 **kw: Any,

4805 ):

4806 self.statement = statement

4807 self.from_clauses = statement._from_obj

4808

4809 for memoized_entities in statement._memoized_select_entities:

4810 self._setup_joins(

4811 memoized_entities._setup_joins, memoized_entities._raw_columns

4812 )

4813

4814 if statement._setup_joins:

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

4816

4817 self.froms = self._get_froms(statement)

4818

4819 self.columns_plus_names = statement._generate_columns_plus_names(True)

4820

4821 @classmethod

4822 def _plugin_not_implemented(cls) -> NoReturn:

4823 raise NotImplementedError(

4824 "The default SELECT construct without plugins does not "

4825 "implement this method."

4826 )

4827

4828 @classmethod

4829 def get_column_descriptions(

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

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

4832 return [

4833 {

4834 "name": name,

4835 "type": element.type,

4836 "expr": element,

4837 }

4838 for _, name, _, element, _ in (

4839 statement._generate_columns_plus_names(False)

4840 )

4841 ]

4842

4843 @classmethod

4844 def from_statement(

4845 cls,

4846 statement: Select[Unpack[TupleAny]],

4847 from_statement: roles.ReturnsRowsRole,

4848 ) -> ExecutableReturnsRows:

4849 cls._plugin_not_implemented()

4850

4851 @classmethod

4852 def get_columns_clause_froms(

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

4854 ) -> List[FromClause]:

4855 return cls._normalize_froms(

4856 itertools.chain.from_iterable(

4857 element._from_objects for element in statement._raw_columns

4858 )

4859 )

4860

4861 @classmethod

4862 def _column_naming_convention(

4863 cls, label_style: SelectLabelStyle

4864 ) -> _LabelConventionCallable:

4865 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4866

4867 dedupe = label_style is not LABEL_STYLE_NONE

4868

4869 pa = prefix_anon_map()

4870 names = set()

4871

4872 def go(

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

4874 col_name: Optional[str] = None,

4875 ) -> Optional[str]:

4876 if is_text_clause(c):

4877 return None

4878 elif TYPE_CHECKING:

4879 assert is_column_element(c)

4880

4881 if not dedupe:

4882 name = c._proxy_key

4883 if name is None:

4884 name = "_no_label"

4885 return name

4886

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

4888

4889 if name is None:

4890 name = "_no_label"

4891 if name in names:

4892 return c._anon_label(name) % pa

4893 else:

4894 names.add(name)

4895 return name

4896

4897 elif name in names:

4898 return (

4899 c._anon_tq_key_label % pa

4900 if table_qualified

4901 else c._anon_key_label % pa

4902 )

4903 else:

4904 names.add(name)

4905 return name

4906

4907 return go

4908

4909 def _get_froms(

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

4911 ) -> List[FromClause]:

4912 ambiguous_table_name_map: _AmbiguousTableNameMap

4913 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4914

4915 return self._normalize_froms(

4916 itertools.chain(

4917 self.from_clauses,

4918 itertools.chain.from_iterable(

4919 [

4920 element._from_objects

4921 for element in statement._raw_columns

4922 ]

4923 ),

4924 itertools.chain.from_iterable(

4925 [

4926 element._from_objects

4927 for element in statement._where_criteria

4928 ]

4929 ),

4930 ),

4931 check_statement=statement,

4932 ambiguous_table_name_map=ambiguous_table_name_map,

4933 )

4934

4935 @classmethod

4936 def _normalize_froms(

4937 cls,

4938 iterable_of_froms: Iterable[FromClause],

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

4940 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4941 ) -> List[FromClause]:

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

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

4944

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

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

4947 etc.

4948

4949 """

4950 seen: Set[FromClause] = set()

4951 froms: List[FromClause] = []

4952

4953 for item in iterable_of_froms:

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

4955 raise exc.InvalidRequestError(

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

4957 )

4958

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

4960 froms.append(item)

4961 seen.update(item._cloned_set)

4962

4963 if froms:

4964 toremove = set(

4965 itertools.chain.from_iterable(

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

4967 )

4968 )

4969 if toremove:

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

4971 # using a list to maintain ordering

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

4973

4974 if ambiguous_table_name_map is not None:

4975 ambiguous_table_name_map.update(

4976 (

4977 fr.name,

4978 _anonymous_label.safe_construct(

4979 hash(fr.name), fr.name

4980 ),

4981 )

4982 for item in froms

4983 for fr in item._from_objects

4984 if is_table(fr)

4985 and fr.schema

4986 and fr.name not in ambiguous_table_name_map

4987 )

4988

4989 return froms

4990

4991 def _get_display_froms(

4992 self,

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

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

4995 ) -> List[FromClause]:

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

4997

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

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

5000 may want to leave those absent if it is automatically

5001 correlating.

5002

5003 """

5004

5005 froms = self.froms

5006

5007 if self.statement._correlate:

5008 to_correlate = self.statement._correlate

5009 if to_correlate:

5010 froms = [

5011 f

5012 for f in froms

5013 if f

5014 not in _cloned_intersection(

5015 _cloned_intersection(

5016 froms, explicit_correlate_froms or ()

5017 ),

5018 to_correlate,

5019 )

5020 ]

5021

5022 if self.statement._correlate_except is not None:

5023 froms = [

5024 f

5025 for f in froms

5026 if f

5027 not in _cloned_difference(

5028 _cloned_intersection(

5029 froms, explicit_correlate_froms or ()

5030 ),

5031 self.statement._correlate_except,

5032 )

5033 ]

5034

5035 if (

5036 self.statement._auto_correlate

5037 and implicit_correlate_froms

5038 and len(froms) > 1

5039 ):

5040 froms = [

5041 f

5042 for f in froms

5043 if f

5044 not in _cloned_intersection(froms, implicit_correlate_froms)

5045 ]

5046

5047 if not len(froms):

5048 raise exc.InvalidRequestError(

5049 "Select statement '%r"

5050 "' returned no FROM clauses "

5051 "due to auto-correlation; "

5052 "specify correlate(<tables>) "

5053 "to control correlation "

5054 "manually." % self.statement

5055 )

5056

5057 return froms

5058

5059 def _memoized_attr__label_resolve_dict(

5060 self,

5061 ) -> Tuple[

5062 Dict[str, ColumnElement[Any]],

5063 Dict[str, ColumnElement[Any]],

5064 Dict[str, ColumnElement[Any]],

5065 ]:

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

5067 c._tq_label or c.key: c

5068 for c in self.statement._all_selected_columns

5069 if c._allow_label_resolve

5070 }

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

5072 c.key: c # type: ignore

5073 for c in _select_iterables(self.froms)

5074 if c._allow_label_resolve

5075 }

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

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

5078 with_cols.setdefault(key, value)

5079

5080 return with_cols, only_froms, only_cols

5081

5082 @classmethod

5083 def determine_last_joined_entity(

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

5085 ) -> Optional[_JoinTargetElement]:

5086 if stmt._setup_joins:

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

5088 else:

5089 return None

5090

5091 @classmethod

5092 def all_selected_columns(

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

5094 ) -> _SelectIterable:

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

5096

5097 def _setup_joins(

5098 self,

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

5100 raw_columns: List[_ColumnsClauseElement],

5101 ) -> None:

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

5103 if TYPE_CHECKING:

5104 if onclause is not None:

5105 assert isinstance(onclause, ColumnElement)

5106

5107 explicit_left = left

5108 isouter = flags["isouter"]

5109 full = flags["full"]

5110

5111 if left is None:

5112 (

5113 left,

5114 replace_from_obj_index,

5115 ) = self._join_determine_implicit_left_side(

5116 raw_columns, left, right, onclause

5117 )

5118 else:

5119 (replace_from_obj_index) = self._join_place_explicit_left_side(

5120 left

5121 )

5122

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

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

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

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

5127 # used instead.

5128 if TYPE_CHECKING:

5129 assert isinstance(right, FromClause)

5130 if onclause is not None:

5131 assert isinstance(onclause, ColumnElement)

5132

5133 if replace_from_obj_index is not None:

5134 # splice into an existing element in the

5135 # self._from_obj list

5136 left_clause = self.from_clauses[replace_from_obj_index]

5137

5138 if explicit_left is not None and onclause is None:

5139 onclause = Join._join_condition(explicit_left, right)

5140

5141 self.from_clauses = (

5142 self.from_clauses[:replace_from_obj_index]

5143 + (

5144 Join(

5145 left_clause,

5146 right,

5147 onclause,

5148 isouter=isouter,

5149 full=full,

5150 ),

5151 )

5152 + self.from_clauses[replace_from_obj_index + 1 :]

5153 )

5154 else:

5155 assert left is not None

5156 self.from_clauses = self.from_clauses + (

5157 Join(left, right, onclause, isouter=isouter, full=full),

5158 )

5159

5160 @util.preload_module("sqlalchemy.sql.util")

5161 def _join_determine_implicit_left_side(

5162 self,

5163 raw_columns: List[_ColumnsClauseElement],

5164 left: Optional[FromClause],

5165 right: _JoinTargetElement,

5166 onclause: Optional[ColumnElement[Any]],

5167 ) -> Tuple[Optional[FromClause], Optional[int]]:

5168 """When join conditions don't express the left side explicitly,

5169 determine if an existing FROM or entity in this query

5170 can serve as the left hand side.

5171

5172 """

5173

5174 sql_util = util.preloaded.sql_util

5175

5176 replace_from_obj_index: Optional[int] = None

5177

5178 from_clauses = self.from_clauses

5179

5180 if from_clauses:

5181 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5182 from_clauses, right, onclause

5183 )

5184

5185 if len(indexes) == 1:

5186 replace_from_obj_index = indexes[0]

5187 left = from_clauses[replace_from_obj_index]

5188 else:

5189 potential = {}

5190 statement = self.statement

5191

5192 for from_clause in itertools.chain(

5193 itertools.chain.from_iterable(

5194 [element._from_objects for element in raw_columns]

5195 ),

5196 itertools.chain.from_iterable(

5197 [

5198 element._from_objects

5199 for element in statement._where_criteria

5200 ]

5201 ),

5202 ):

5203 potential[from_clause] = ()

5204

5205 all_clauses = list(potential.keys())

5206 indexes = sql_util.find_left_clause_to_join_from(

5207 all_clauses, right, onclause

5208 )

5209

5210 if len(indexes) == 1:

5211 left = all_clauses[indexes[0]]

5212

5213 if len(indexes) > 1:

5214 raise exc.InvalidRequestError(

5215 "Can't determine which FROM clause to join "

5216 "from, there are multiple FROMS which can "

5217 "join to this entity. Please use the .select_from() "

5218 "method to establish an explicit left side, as well as "

5219 "providing an explicit ON clause if not present already to "

5220 "help resolve the ambiguity."

5221 )

5222 elif not indexes:

5223 raise exc.InvalidRequestError(

5224 "Don't know how to join to %r. "

5225 "Please use the .select_from() "

5226 "method to establish an explicit left side, as well as "

5227 "providing an explicit ON clause if not present already to "

5228 "help resolve the ambiguity." % (right,)

5229 )

5230 return left, replace_from_obj_index

5231

5232 @util.preload_module("sqlalchemy.sql.util")

5233 def _join_place_explicit_left_side(

5234 self, left: FromClause

5235 ) -> Optional[int]:

5236 replace_from_obj_index: Optional[int] = None

5237

5238 sql_util = util.preloaded.sql_util

5239

5240 from_clauses = list(self.statement._iterate_from_elements())

5241

5242 if from_clauses:

5243 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5244 self.from_clauses, left

5245 )

5246 else:

5247 indexes = []

5248

5249 if len(indexes) > 1:

5250 raise exc.InvalidRequestError(

5251 "Can't identify which entity in which to assign the "

5252 "left side of this join. Please use a more specific "

5253 "ON clause."

5254 )

5255

5256 # have an index, means the left side is already present in

5257 # an existing FROM in the self._from_obj tuple

5258 if indexes:

5259 replace_from_obj_index = indexes[0]

5260

5261 # no index, means we need to add a new element to the

5262 # self._from_obj tuple

5263

5264 return replace_from_obj_index

5265

5266

5267class _SelectFromElements:

5268 __slots__ = ()

5269

5270 _raw_columns: List[_ColumnsClauseElement]

5271 _where_criteria: Tuple[ColumnElement[Any], ...]

5272 _from_obj: Tuple[FromClause, ...]

5273

5274 def _iterate_from_elements(self) -> Iterator[FromClause]:

5275 # note this does not include elements

5276 # in _setup_joins

5277

5278 seen = set()

5279 for element in self._raw_columns:

5280 for fr in element._from_objects:

5281 if fr in seen:

5282 continue

5283 seen.add(fr)

5284 yield fr

5285 for element in self._where_criteria:

5286 for fr in element._from_objects:

5287 if fr in seen:

5288 continue

5289 seen.add(fr)

5290 yield fr

5291 for element in self._from_obj:

5292 if element in seen:

5293 continue

5294 seen.add(element)

5295 yield element

5296

5297

5298class _MemoizedSelectEntities(

5299 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5300):

5301 """represents partial state from a Select object, for the case

5302 where Select.columns() has redefined the set of columns/entities the

5303 statement will be SELECTing from. This object represents

5304 the entities from the SELECT before that transformation was applied,

5305 so that transformations that were made in terms of the SELECT at that

5306 time, such as join() as well as options(), can access the correct context.

5307

5308 In previous SQLAlchemy versions, this wasn't needed because these

5309 constructs calculated everything up front, like when you called join()

5310 or options(), it did everything to figure out how that would translate

5311 into specific SQL constructs that would be ready to send directly to the

5312 SQL compiler when needed. But as of

5313 1.4, all of that stuff is done in the compilation phase, during the

5314 "compile state" portion of the process, so that the work can all be

5315 cached. So it needs to be able to resolve joins/options2 based on what

5316 the list of entities was when those methods were called.

5317

5318

5319 """

5320

5321 __visit_name__ = "memoized_select_entities"

5322

5323 _traverse_internals: _TraverseInternalsType = [

5324 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5325 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5326 ("_with_options", InternalTraversal.dp_executable_options),

5327 ]

5328

5329 _is_clone_of: Optional[ClauseElement]

5330 _raw_columns: List[_ColumnsClauseElement]

5331 _setup_joins: Tuple[_SetupJoinsElement, ...]

5332 _with_options: Tuple[ExecutableOption, ...]

5333

5334 _annotations = util.EMPTY_DICT

5335

5336 def _clone(self, **kw: Any) -> Self:

5337 c = self.__class__.__new__(self.__class__)

5338 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5339

5340 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5341 return c

5342

5343 @classmethod

5344 def _generate_for_statement(

5345 cls, select_stmt: Select[Unpack[TupleAny]]

5346 ) -> None:

5347 if select_stmt._setup_joins or select_stmt._with_options:

5348 self = _MemoizedSelectEntities()

5349 self._raw_columns = select_stmt._raw_columns

5350 self._setup_joins = select_stmt._setup_joins

5351 self._with_options = select_stmt._with_options

5352

5353 select_stmt._memoized_select_entities += (self,)

5354 select_stmt._raw_columns = []

5355 select_stmt._setup_joins = select_stmt._with_options = ()

5356

5357

5358class Select(

5359 HasPrefixes,

5360 HasSuffixes,

5361 HasHints,

5362 HasCompileState,

5363 HasSyntaxExtensions[

5364 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5365 ],

5366 _SelectFromElements,

5367 GenerativeSelect,

5368 TypedReturnsRows[Unpack[_Ts]],

5369):

5370 """Represents a ``SELECT`` statement.

5371

5372 The :class:`_sql.Select` object is normally constructed using the

5373 :func:`_sql.select` function. See that function for details.

5374

5375 Available extension points:

5376

5377 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5378 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5379 keyword (if any) and the list of columns.

5380 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5381 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5382

5383 .. seealso::

5384

5385 :func:`_sql.select`

5386

5387 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5388

5389 """

5390

5391 __visit_name__ = "select"

5392

5393 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5394 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5395

5396 _raw_columns: List[_ColumnsClauseElement]

5397

5398 _distinct: bool = False

5399 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5400 _correlate: Tuple[FromClause, ...] = ()

5401 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5402 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5403 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5404 _from_obj: Tuple[FromClause, ...] = ()

5405

5406 _position_map = util.immutabledict(

5407 {

5408 "post_select": "_post_select_clause",

5409 "pre_columns": "_pre_columns_clause",

5410 "post_criteria": "_post_criteria_clause",

5411 "post_body": "_post_body_clause",

5412 }

5413 )

5414

5415 _post_select_clause: Optional[ClauseElement] = None

5416 """extension point for a ClauseElement that will be compiled directly

5417 after the SELECT keyword.

5418

5419 .. versionadded:: 2.1

5420

5421 """

5422

5423 _pre_columns_clause: Optional[ClauseElement] = None

5424 """extension point for a ClauseElement that will be compiled directly

5425 before the "columns" clause; after DISTINCT (if present).

5426

5427 .. versionadded:: 2.1

5428

5429 """

5430

5431 _post_criteria_clause: Optional[ClauseElement] = None

5432 """extension point for a ClauseElement that will be compiled directly

5433 after "criteria", following the HAVING clause but before ORDER BY.

5434

5435 .. versionadded:: 2.1

5436

5437 """

5438

5439 _post_body_clause: Optional[ClauseElement] = None

5440 """extension point for a ClauseElement that will be compiled directly

5441 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5442 of the SELECT.

5443

5444 .. versionadded:: 2.1

5445

5446 """

5447

5448 _auto_correlate = True

5449 _is_select_statement = True

5450 _compile_options: CacheableOptions = (

5451 SelectState.default_select_compile_options

5452 )

5453

5454 _traverse_internals: _TraverseInternalsType = (

5455 [

5456 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5457 (

5458 "_memoized_select_entities",

5459 InternalTraversal.dp_memoized_select_entities,

5460 ),

5461 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5462 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5463 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5464 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5465 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5466 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5467 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5468 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5469 ("_limit_clause", InternalTraversal.dp_clauseelement),

5470 ("_offset_clause", InternalTraversal.dp_clauseelement),

5471 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5472 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5473 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5474 ("_distinct", InternalTraversal.dp_boolean),

5475 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5476 ("_label_style", InternalTraversal.dp_plain_obj),

5477 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5478 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5479 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5480 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5481 ]

5482 + HasCTE._has_ctes_traverse_internals

5483 + HasPrefixes._has_prefixes_traverse_internals

5484 + HasSuffixes._has_suffixes_traverse_internals

5485 + HasHints._has_hints_traverse_internals

5486 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5487 + Executable._executable_traverse_internals

5488 + DialectKWArgs._dialect_kwargs_traverse_internals

5489 )

5490

5491 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5492 ("_compile_options", InternalTraversal.dp_has_cache_key)

5493 ]

5494

5495 _compile_state_factory: Type[SelectState]

5496

5497 @classmethod

5498 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5499 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5500

5501 Used internally to build up :class:`.Select` constructs with

5502 pre-established state.

5503

5504 """

5505

5506 stmt = Select.__new__(Select)

5507 stmt.__dict__.update(kw)

5508 return stmt

5509

5510 def __init__(

5511 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5512 ):

5513 r"""Construct a new :class:`_expression.Select`.

5514

5515 The public constructor for :class:`_expression.Select` is the

5516 :func:`_sql.select` function.

5517

5518 """

5519 self._raw_columns = [

5520 coercions.expect(

5521 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5522 )

5523 for ent in entities

5524 ]

5525 GenerativeSelect.__init__(self)

5526

5527 def _apply_syntax_extension_to_self(

5528 self, extension: SyntaxExtension

5529 ) -> None:

5530 extension.apply_to_select(self)

5531

5532 def _scalar_type(self) -> TypeEngine[Any]:

5533 if not self._raw_columns:

5534 return NULLTYPE

5535 elem = self._raw_columns[0]

5536 cols = list(elem._select_iterable)

5537 return cols[0].type

5538

5539 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5540 """A synonym for the :meth:`_sql.Select.where` method."""

5541

5542 return self.where(*criteria)

5543

5544 def _filter_by_zero(

5545 self,

5546 ) -> Union[

5547 FromClause, _JoinTargetProtocol, ColumnElement[Any], TextClause

5548 ]:

5549 if self._setup_joins:

5550 meth = SelectState.get_plugin_class(

5551 self

5552 ).determine_last_joined_entity

5553 _last_joined_entity = meth(self)

5554 if _last_joined_entity is not None:

5555 return _last_joined_entity

5556

5557 if self._from_obj:

5558 return self._from_obj[0]

5559

5560 return self._raw_columns[0]

5561

5562 if TYPE_CHECKING:

5563

5564 @overload

5565 def scalar_subquery(

5566 self: Select[_MAYBE_ENTITY],

5567 ) -> ScalarSelect[Any]: ...

5568

5569 @overload

5570 def scalar_subquery(

5571 self: Select[_NOT_ENTITY],

5572 ) -> ScalarSelect[_NOT_ENTITY]: ...

5573

5574 @overload

5575 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5576

5577 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5578

5579 def filter_by(self, **kwargs: Any) -> Self:

5580 r"""apply the given filtering criterion as a WHERE clause

5581 to this select.

5582

5583 """

5584 from_entity = self._filter_by_zero()

5585

5586 clauses = [

5587 _entity_namespace_key(from_entity, key) == value

5588 for key, value in kwargs.items()

5589 ]

5590 return self.filter(*clauses)

5591

5592 @property

5593 def column_descriptions(self) -> Any:

5594 """Return a :term:`plugin-enabled` 'column descriptions' structure

5595 referring to the columns which are SELECTed by this statement.

5596

5597 This attribute is generally useful when using the ORM, as an

5598 extended structure which includes information about mapped

5599 entities is returned. The section :ref:`queryguide_inspection`

5600 contains more background.

5601

5602 For a Core-only statement, the structure returned by this accessor

5603 is derived from the same objects that are returned by the

5604 :attr:`.Select.selected_columns` accessor, formatted as a list of

5605 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5606 which indicate the column expressions to be selected::

5607

5608 >>> stmt = select(user_table)

5609 >>> stmt.column_descriptions

5610 [

5611 {

5612 'name': 'id',

5613 'type': Integer(),

5614 'expr': Column('id', Integer(), ...)},

5615 {

5616 'name': 'name',

5617 'type': String(length=30),

5618 'expr': Column('name', String(length=30), ...)}

5619 ]

5620

5621 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5622 attribute returns a structure for a Core-only set of entities,

5623 not just ORM-only entities.

5624

5625 .. seealso::

5626

5627 :attr:`.UpdateBase.entity_description` - entity information for

5628 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5629

5630 :ref:`queryguide_inspection` - ORM background

5631

5632 """

5633 meth = SelectState.get_plugin_class(self).get_column_descriptions

5634 return meth(self)

5635

5636 def from_statement(

5637 self, statement: roles.ReturnsRowsRole

5638 ) -> ExecutableReturnsRows:

5639 """Apply the columns which this :class:`.Select` would select

5640 onto another statement.

5641

5642 This operation is :term:`plugin-specific` and will raise a not

5643 supported exception if this :class:`_sql.Select` does not select from

5644 plugin-enabled entities.

5645

5646

5647 The statement is typically either a :func:`_expression.text` or

5648 :func:`_expression.select` construct, and should return the set of

5649 columns appropriate to the entities represented by this

5650 :class:`.Select`.

5651

5652 .. seealso::

5653

5654 :ref:`orm_queryguide_selecting_text` - usage examples in the

5655 ORM Querying Guide

5656

5657 """

5658 meth = SelectState.get_plugin_class(self).from_statement

5659 return meth(self, statement)

5660

5661 @_generative

5662 def join(

5663 self,

5664 target: _JoinTargetArgument,

5665 onclause: Optional[_OnClauseArgument] = None,

5666 *,

5667 isouter: bool = False,

5668 full: bool = False,

5669 ) -> Self:

5670 r"""Create a SQL JOIN against this :class:`_expression.Select`

5671 object's criterion

5672 and apply generatively, returning the newly resulting

5673 :class:`_expression.Select`.

5674

5675 E.g.::

5676

5677 stmt = select(user_table).join(

5678 address_table, user_table.c.id == address_table.c.user_id

5679 )

5680

5681 The above statement generates SQL similar to:

5682

5683 .. sourcecode:: sql

5684

5685 SELECT user.id, user.name

5686 FROM user

5687 JOIN address ON user.id = address.user_id

5688

5689 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5690 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5691 source that is within the FROM clause of the existing SELECT,

5692 and a given target :class:`_sql.FromClause`, and then adds

5693 this :class:`_sql.Join` to the FROM clause of the newly generated

5694 SELECT statement. This is completely reworked from the behavior

5695 in 1.3, which would instead create a subquery of the entire

5696 :class:`_expression.Select` and then join that subquery to the

5697 target.

5698

5699 This is a **backwards incompatible change** as the previous behavior

5700 was mostly useless, producing an unnamed subquery rejected by

5701 most databases in any case. The new behavior is modeled after

5702 that of the very successful :meth:`_orm.Query.join` method in the

5703 ORM, in order to support the functionality of :class:`_orm.Query`

5704 being available by using a :class:`_sql.Select` object with an

5705 :class:`_orm.Session`.

5706

5707 See the notes for this change at :ref:`change_select_join`.

5708

5709

5710 :param target: target table to join towards

5711

5712 :param onclause: ON clause of the join. If omitted, an ON clause

5713 is generated automatically based on the :class:`_schema.ForeignKey`

5714 linkages between the two tables, if one can be unambiguously

5715 determined, otherwise an error is raised.

5716

5717 :param isouter: if True, generate LEFT OUTER join. Same as

5718 :meth:`_expression.Select.outerjoin`.

5719

5720 :param full: if True, generate FULL OUTER join.

5721

5722 .. seealso::

5723

5724 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5725

5726 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5727

5728 :meth:`_expression.Select.join_from`

5729

5730 :meth:`_expression.Select.outerjoin`

5731

5732 """ # noqa: E501

5733 join_target = coercions.expect(

5734 roles.JoinTargetRole, target, apply_propagate_attrs=self

5735 )

5736 if onclause is not None:

5737 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5738 else:

5739 onclause_element = None

5740

5741 self._setup_joins += (

5742 (

5743 join_target,

5744 onclause_element,

5745 None,

5746 {"isouter": isouter, "full": full},

5747 ),

5748 )

5749 return self

5750

5751 def outerjoin_from(

5752 self,

5753 from_: _FromClauseArgument,

5754 target: _JoinTargetArgument,

5755 onclause: Optional[_OnClauseArgument] = None,

5756 *,

5757 full: bool = False,

5758 ) -> Self:

5759 r"""Create a SQL LEFT OUTER JOIN against this

5760 :class:`_expression.Select` object's criterion and apply generatively,

5761 returning the newly resulting :class:`_expression.Select`.

5762

5763 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5764

5765 """

5766 return self.join_from(

5767 from_, target, onclause=onclause, isouter=True, full=full

5768 )

5769

5770 @_generative

5771 def join_from(

5772 self,

5773 from_: _FromClauseArgument,

5774 target: _JoinTargetArgument,

5775 onclause: Optional[_OnClauseArgument] = None,

5776 *,

5777 isouter: bool = False,

5778 full: bool = False,

5779 ) -> Self:

5780 r"""Create a SQL JOIN against this :class:`_expression.Select`

5781 object's criterion

5782 and apply generatively, returning the newly resulting

5783 :class:`_expression.Select`.

5784

5785 E.g.::

5786

5787 stmt = select(user_table, address_table).join_from(

5788 user_table, address_table, user_table.c.id == address_table.c.user_id

5789 )

5790

5791 The above statement generates SQL similar to:

5792

5793 .. sourcecode:: sql

5794

5795 SELECT user.id, user.name, address.id, address.email, address.user_id

5796 FROM user JOIN address ON user.id = address.user_id

5797

5798 .. versionadded:: 1.4

5799

5800 :param from\_: the left side of the join, will be rendered in the

5801 FROM clause and is roughly equivalent to using the

5802 :meth:`.Select.select_from` method.

5803

5804 :param target: target table to join towards

5805

5806 :param onclause: ON clause of the join.

5807

5808 :param isouter: if True, generate LEFT OUTER join. Same as

5809 :meth:`_expression.Select.outerjoin`.

5810

5811 :param full: if True, generate FULL OUTER join.

5812

5813 .. seealso::

5814

5815 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5816

5817 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5818

5819 :meth:`_expression.Select.join`

5820

5821 """ # noqa: E501

5822

5823 # note the order of parsing from vs. target is important here, as we

5824 # are also deriving the source of the plugin (i.e. the subject mapper

5825 # in an ORM query) which should favor the "from_" over the "target"

5826

5827 from_ = coercions.expect(

5828 roles.FromClauseRole, from_, apply_propagate_attrs=self

5829 )

5830 join_target = coercions.expect(

5831 roles.JoinTargetRole, target, apply_propagate_attrs=self

5832 )

5833 if onclause is not None:

5834 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5835 else:

5836 onclause_element = None

5837

5838 self._setup_joins += (

5839 (

5840 join_target,

5841 onclause_element,

5842 from_,

5843 {"isouter": isouter, "full": full},

5844 ),

5845 )

5846 return self

5847

5848 def outerjoin(

5849 self,

5850 target: _JoinTargetArgument,

5851 onclause: Optional[_OnClauseArgument] = None,

5852 *,

5853 full: bool = False,

5854 ) -> Self:

5855 """Create a left outer join.

5856

5857 Parameters are the same as that of :meth:`_expression.Select.join`.

5858

5859 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5860 creates a :class:`_sql.Join` object between a

5861 :class:`_sql.FromClause` source that is within the FROM clause of

5862 the existing SELECT, and a given target :class:`_sql.FromClause`,

5863 and then adds this :class:`_sql.Join` to the FROM clause of the

5864 newly generated SELECT statement. This is completely reworked

5865 from the behavior in 1.3, which would instead create a subquery of

5866 the entire

5867 :class:`_expression.Select` and then join that subquery to the

5868 target.

5869

5870 This is a **backwards incompatible change** as the previous behavior

5871 was mostly useless, producing an unnamed subquery rejected by

5872 most databases in any case. The new behavior is modeled after

5873 that of the very successful :meth:`_orm.Query.join` method in the

5874 ORM, in order to support the functionality of :class:`_orm.Query`

5875 being available by using a :class:`_sql.Select` object with an

5876 :class:`_orm.Session`.

5877

5878 See the notes for this change at :ref:`change_select_join`.

5879

5880 .. seealso::

5881

5882 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5883

5884 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5885

5886 :meth:`_expression.Select.join`

5887

5888 """

5889 return self.join(target, onclause=onclause, isouter=True, full=full)

5890

5891 def get_final_froms(self) -> Sequence[FromClause]:

5892 """Compute the final displayed list of :class:`_expression.FromClause`

5893 elements.

5894

5895 This method will run through the full computation required to

5896 determine what FROM elements will be displayed in the resulting

5897 SELECT statement, including shadowing individual tables with

5898 JOIN objects, as well as full computation for ORM use cases including

5899 eager loading clauses.

5900

5901 For ORM use, this accessor returns the **post compilation**

5902 list of FROM objects; this collection will include elements such as

5903 eagerly loaded tables and joins. The objects will **not** be

5904 ORM enabled and not work as a replacement for the

5905 :meth:`_sql.Select.select_froms` collection; additionally, the

5906 method is not well performing for an ORM enabled statement as it

5907 will incur the full ORM construction process.

5908

5909 To retrieve the FROM list that's implied by the "columns" collection

5910 passed to the :class:`_sql.Select` originally, use the

5911 :attr:`_sql.Select.columns_clause_froms` accessor.

5912

5913 To select from an alternative set of columns while maintaining the

5914 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5915 pass the

5916 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5917 parameter.

5918

5919 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5920 method replaces the previous :attr:`_sql.Select.froms` accessor,

5921 which is deprecated.

5922

5923 .. seealso::

5924

5925 :attr:`_sql.Select.columns_clause_froms`

5926

5927 """

5928 compiler = self._default_compiler()

5929

5930 return self._compile_state_factory(self, compiler)._get_display_froms()

5931

5932 @property

5933 @util.deprecated(

5934 "1.4.23",

5935 "The :attr:`_expression.Select.froms` attribute is moved to "

5936 "the :meth:`_expression.Select.get_final_froms` method.",

5937 )

5938 def froms(self) -> Sequence[FromClause]:

5939 """Return the displayed list of :class:`_expression.FromClause`

5940 elements.

5941

5942

5943 """

5944 return self.get_final_froms()

5945

5946 @property

5947 def columns_clause_froms(self) -> List[FromClause]:

5948 """Return the set of :class:`_expression.FromClause` objects implied

5949 by the columns clause of this SELECT statement.

5950

5951 .. versionadded:: 1.4.23

5952

5953 .. seealso::

5954

5955 :attr:`_sql.Select.froms` - "final" FROM list taking the full

5956 statement into account

5957

5958 :meth:`_sql.Select.with_only_columns` - makes use of this

5959 collection to set up a new FROM list

5960

5961 """

5962

5963 return SelectState.get_plugin_class(self).get_columns_clause_froms(

5964 self

5965 )

5966

5967 @property

5968 def inner_columns(self) -> _SelectIterable:

5969 """An iterator of all :class:`_expression.ColumnElement`

5970 expressions which would

5971 be rendered into the columns clause of the resulting SELECT statement.

5972

5973 This method is legacy as of 1.4 and is superseded by the

5974 :attr:`_expression.Select.exported_columns` collection.

5975

5976 """

5977

5978 return iter(self._all_selected_columns)

5979

5980 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

5981 if fromclause is not None and self in fromclause._cloned_set:

5982 return True

5983

5984 for f in self._iterate_from_elements():

5985 if f.is_derived_from(fromclause):

5986 return True

5987 return False

5988

5989 def _copy_internals(

5990 self, clone: _CloneCallableType = _clone, **kw: Any

5991 ) -> None:

5992 # Select() object has been cloned and probably adapted by the

5993 # given clone function. Apply the cloning function to internal

5994 # objects

5995

5996 # 1. keep a dictionary of the froms we've cloned, and what

5997 # they've become. This allows us to ensure the same cloned from

5998 # is used when other items such as columns are "cloned"

5999

6000 all_the_froms = set(

6001 itertools.chain(

6002 _from_objects(*self._raw_columns),

6003 _from_objects(*self._where_criteria),

6004 _from_objects(*[elem[0] for elem in self._setup_joins]),

6005 )

6006 )

6007

6008 # do a clone for the froms we've gathered. what is important here

6009 # is if any of the things we are selecting from, like tables,

6010 # were converted into Join objects. if so, these need to be

6011 # added to _from_obj explicitly, because otherwise they won't be

6012 # part of the new state, as they don't associate themselves with

6013 # their columns.

6014 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6015

6016 # 2. copy FROM collections, adding in joins that we've created.

6017 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6018 add_froms = (

6019 {f for f in new_froms.values() if isinstance(f, Join)}

6020 .difference(all_the_froms)

6021 .difference(existing_from_obj)

6022 )

6023

6024 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6025

6026 # 3. clone everything else, making sure we use columns

6027 # corresponding to the froms we just made.

6028 def replace(

6029 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6030 **kw: Any,

6031 ) -> Optional[KeyedColumnElement[Any]]:

6032 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6033 newelem = new_froms[obj.table].corresponding_column(obj)

6034 return newelem

6035 return None

6036

6037 kw["replace"] = replace

6038

6039 # copy everything else. for table-ish things like correlate,

6040 # correlate_except, setup_joins, these clone normally. For

6041 # column-expression oriented things like raw_columns, where_criteria,

6042 # order by, we get this from the new froms.

6043 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6044

6045 self._reset_memoizations()

6046

6047 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6048 return itertools.chain(

6049 super().get_children(

6050 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6051 **kw,

6052 ),

6053 self._iterate_from_elements(),

6054 )

6055

6056 @_generative

6057 def add_columns(

6058 self, *entities: _ColumnsClauseArgument[Any]

6059 ) -> Select[Unpack[TupleAny]]:

6060 r"""Return a new :func:`_expression.select` construct with

6061 the given entities appended to its columns clause.

6062

6063 E.g.::

6064

6065 my_select = my_select.add_columns(table.c.new_column)

6066

6067 The original expressions in the columns clause remain in place.

6068 To replace the original expressions with new ones, see the method

6069 :meth:`_expression.Select.with_only_columns`.

6070

6071 :param \*entities: column, table, or other entity expressions to be

6072 added to the columns clause

6073

6074 .. seealso::

6075

6076 :meth:`_expression.Select.with_only_columns` - replaces existing

6077 expressions rather than appending.

6078

6079 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6080 example

6081

6082 """

6083 self._reset_memoizations()

6084

6085 self._raw_columns = self._raw_columns + [

6086 coercions.expect(

6087 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6088 )

6089 for column in entities

6090 ]

6091 return self

6092

6093 def _set_entities(

6094 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6095 ) -> None:

6096 self._raw_columns = [

6097 coercions.expect(

6098 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6099 )

6100 for ent in util.to_list(entities)

6101 ]

6102

6103 @util.deprecated(

6104 "1.4",

6105 "The :meth:`_expression.Select.column` method is deprecated and will "

6106 "be removed in a future release. Please use "

6107 ":meth:`_expression.Select.add_columns`",

6108 )

6109 def column(

6110 self, column: _ColumnsClauseArgument[Any]

6111 ) -> Select[Unpack[TupleAny]]:

6112 """Return a new :func:`_expression.select` construct with

6113 the given column expression added to its columns clause.

6114

6115 E.g.::

6116

6117 my_select = my_select.column(table.c.new_column)

6118

6119 See the documentation for

6120 :meth:`_expression.Select.with_only_columns`

6121 for guidelines on adding /replacing the columns of a

6122 :class:`_expression.Select` object.

6123

6124 """

6125 return self.add_columns(column)

6126

6127 @util.preload_module("sqlalchemy.sql.util")

6128 def reduce_columns(

6129 self, only_synonyms: bool = True

6130 ) -> Select[Unpack[TupleAny]]:

6131 """Return a new :func:`_expression.select` construct with redundantly

6132 named, equivalently-valued columns removed from the columns clause.

6133

6134 "Redundant" here means two columns where one refers to the

6135 other either based on foreign key, or via a simple equality

6136 comparison in the WHERE clause of the statement. The primary purpose

6137 of this method is to automatically construct a select statement

6138 with all uniquely-named columns, without the need to use

6139 table-qualified labels as

6140 :meth:`_expression.Select.set_label_style`

6141 does.

6142

6143 When columns are omitted based on foreign key, the referred-to

6144 column is the one that's kept. When columns are omitted based on

6145 WHERE equivalence, the first column in the columns clause is the

6146 one that's kept.

6147

6148 :param only_synonyms: when True, limit the removal of columns

6149 to those which have the same name as the equivalent. Otherwise,

6150 all columns that are equivalent to another are removed.

6151

6152 """

6153 woc: Select[Unpack[TupleAny]]

6154 woc = self.with_only_columns(

6155 *util.preloaded.sql_util.reduce_columns(

6156 self._all_selected_columns,

6157 only_synonyms=only_synonyms,

6158 *(self._where_criteria + self._from_obj),

6159 )

6160 )

6161 return woc

6162

6163 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6164

6165 # code within this block is **programmatically,

6166 # statically generated** by tools/generate_tuple_map_overloads.py

6167

6168 @overload

6169 def with_only_columns(

6170 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6171 ) -> Select[_T0]: ...

6172

6173 @overload

6174 def with_only_columns(

6175 self,

6176 __ent0: _TCCA[_T0],

6177 __ent1: _TCCA[_T1],

6178 /,

6179 *,

6180 maintain_column_froms: bool = ...,

6181 ) -> Select[_T0, _T1]: ...

6182

6183 @overload

6184 def with_only_columns(

6185 self,

6186 __ent0: _TCCA[_T0],

6187 __ent1: _TCCA[_T1],

6188 __ent2: _TCCA[_T2],

6189 /,

6190 *,

6191 maintain_column_froms: bool = ...,

6192 ) -> Select[_T0, _T1, _T2]: ...

6193

6194 @overload

6195 def with_only_columns(

6196 self,

6197 __ent0: _TCCA[_T0],

6198 __ent1: _TCCA[_T1],

6199 __ent2: _TCCA[_T2],

6200 __ent3: _TCCA[_T3],

6201 /,

6202 *,

6203 maintain_column_froms: bool = ...,

6204 ) -> Select[_T0, _T1, _T2, _T3]: ...

6205

6206 @overload

6207 def with_only_columns(

6208 self,

6209 __ent0: _TCCA[_T0],

6210 __ent1: _TCCA[_T1],

6211 __ent2: _TCCA[_T2],

6212 __ent3: _TCCA[_T3],

6213 __ent4: _TCCA[_T4],

6214 /,

6215 *,

6216 maintain_column_froms: bool = ...,

6217 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6218

6219 @overload

6220 def with_only_columns(

6221 self,

6222 __ent0: _TCCA[_T0],

6223 __ent1: _TCCA[_T1],

6224 __ent2: _TCCA[_T2],

6225 __ent3: _TCCA[_T3],

6226 __ent4: _TCCA[_T4],

6227 __ent5: _TCCA[_T5],

6228 /,

6229 *,

6230 maintain_column_froms: bool = ...,

6231 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6232

6233 @overload

6234 def with_only_columns(

6235 self,

6236 __ent0: _TCCA[_T0],

6237 __ent1: _TCCA[_T1],

6238 __ent2: _TCCA[_T2],

6239 __ent3: _TCCA[_T3],

6240 __ent4: _TCCA[_T4],

6241 __ent5: _TCCA[_T5],

6242 __ent6: _TCCA[_T6],

6243 /,

6244 *,

6245 maintain_column_froms: bool = ...,

6246 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6247

6248 @overload

6249 def with_only_columns(

6250 self,

6251 __ent0: _TCCA[_T0],

6252 __ent1: _TCCA[_T1],

6253 __ent2: _TCCA[_T2],

6254 __ent3: _TCCA[_T3],

6255 __ent4: _TCCA[_T4],

6256 __ent5: _TCCA[_T5],

6257 __ent6: _TCCA[_T6],

6258 __ent7: _TCCA[_T7],

6259 /,

6260 *entities: _ColumnsClauseArgument[Any],

6261 maintain_column_froms: bool = ...,

6262 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6263

6264 # END OVERLOADED FUNCTIONS self.with_only_columns

6265

6266 @overload

6267 def with_only_columns(

6268 self,

6269 *entities: _ColumnsClauseArgument[Any],

6270 maintain_column_froms: bool = False,

6271 **__kw: Any,

6272 ) -> Select[Unpack[TupleAny]]: ...

6273

6274 @_generative

6275 def with_only_columns(

6276 self,

6277 *entities: _ColumnsClauseArgument[Any],

6278 maintain_column_froms: bool = False,

6279 **__kw: Any,

6280 ) -> Select[Unpack[TupleAny]]:

6281 r"""Return a new :func:`_expression.select` construct with its columns

6282 clause replaced with the given entities.

6283

6284 By default, this method is exactly equivalent to as if the original

6285 :func:`_expression.select` had been called with the given entities.

6286 E.g. a statement::

6287

6288 s = select(table1.c.a, table1.c.b)

6289 s = s.with_only_columns(table1.c.b)

6290

6291 should be exactly equivalent to::

6292

6293 s = select(table1.c.b)

6294

6295 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6296 will also dynamically alter the FROM clause of the

6297 statement if it is not explicitly stated.

6298 To maintain the existing set of FROMs including those implied by the

6299 current columns clause, add the

6300 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6301 parameter::

6302

6303 s = select(table1.c.a, table2.c.b)

6304 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6305

6306 The above parameter performs a transfer of the effective FROMs

6307 in the columns collection to the :meth:`_sql.Select.select_from`

6308 method, as though the following were invoked::

6309

6310 s = select(table1.c.a, table2.c.b)

6311 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6312

6313 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6314 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6315 collection and performs an operation equivalent to the following::

6316

6317 s = select(table1.c.a, table2.c.b)

6318 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6319

6320 :param \*entities: column expressions to be used.

6321

6322 :param maintain_column_froms: boolean parameter that will ensure the

6323 FROM list implied from the current columns clause will be transferred

6324 to the :meth:`_sql.Select.select_from` method first.

6325

6326 .. versionadded:: 1.4.23

6327

6328 """ # noqa: E501

6329

6330 if __kw:

6331 raise _no_kw()

6332

6333 # memoizations should be cleared here as of

6334 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6335 # is the case for now.

6336 self._assert_no_memoizations()

6337

6338 if maintain_column_froms:

6339 self.select_from.non_generative( # type: ignore

6340 self, *self.columns_clause_froms

6341 )

6342

6343 # then memoize the FROMs etc.

6344 _MemoizedSelectEntities._generate_for_statement(self)

6345

6346 self._raw_columns = [

6347 coercions.expect(roles.ColumnsClauseRole, c)

6348 for c in coercions._expression_collection_was_a_list(

6349 "entities", "Select.with_only_columns", entities

6350 )

6351 ]

6352 return self

6353

6354 @property

6355 def whereclause(self) -> Optional[ColumnElement[Any]]:

6356 """Return the completed WHERE clause for this

6357 :class:`_expression.Select` statement.

6358

6359 This assembles the current collection of WHERE criteria

6360 into a single :class:`_expression.BooleanClauseList` construct.

6361

6362

6363 .. versionadded:: 1.4

6364

6365 """

6366

6367 return BooleanClauseList._construct_for_whereclause(

6368 self._where_criteria

6369 )

6370

6371 _whereclause = whereclause

6372

6373 @_generative

6374 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6375 """Return a new :func:`_expression.select` construct with

6376 the given expression added to

6377 its WHERE clause, joined to the existing clause via AND, if any.

6378

6379 """

6380

6381 assert isinstance(self._where_criteria, tuple)

6382

6383 for criterion in whereclause:

6384 where_criteria: ColumnElement[Any] = coercions.expect(

6385 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6386 )

6387 self._where_criteria += (where_criteria,)

6388 return self

6389

6390 @_generative

6391 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6392 """Return a new :func:`_expression.select` construct with

6393 the given expression added to

6394 its HAVING clause, joined to the existing clause via AND, if any.

6395

6396 """

6397

6398 for criterion in having:

6399 having_criteria = coercions.expect(

6400 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6401 )

6402 self._having_criteria += (having_criteria,)

6403 return self

6404

6405 @_generative

6406 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6407 r"""Return a new :func:`_expression.select` construct which

6408 will apply DISTINCT to the SELECT statement overall.

6409

6410 E.g.::

6411

6412 from sqlalchemy import select

6413

6414 stmt = select(users_table.c.id, users_table.c.name).distinct()

6415

6416 The above would produce an statement resembling:

6417

6418 .. sourcecode:: sql

6419

6420 SELECT DISTINCT user.id, user.name FROM user

6421

6422 The method also historically accepted an ``*expr`` parameter which

6423 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6424 This is now replaced using the :func:`_postgresql.distinct_on`

6425 extension::

6426

6427 from sqlalchemy import select

6428 from sqlalchemy.dialects.postgresql import distinct_on

6429

6430 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6431

6432 Using this parameter on other backends which don't support this

6433 syntax will raise an error.

6434

6435 :param \*expr: optional column expressions. When present,

6436 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6437 construct. A deprecation warning and/or :class:`_exc.CompileError`

6438 will be raised on other backends.

6439

6440 .. deprecated:: 2.1 Passing expressions to

6441 :meth:`_sql.Select.distinct` is deprecated, use

6442 :func:`_postgresql.distinct_on` instead.

6443

6444 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6445 and will raise :class:`_exc.CompileError` in a future version.

6446

6447 .. seealso::

6448

6449 :func:`_postgresql.distinct_on`

6450

6451 :meth:`_sql.HasSyntaxExtensions.ext`

6452 """

6453 self._distinct = True

6454 if expr:

6455 warn_deprecated(

6456 "Passing expression to ``distinct`` to generate a "

6457 "DISTINCT ON clause is deprecated. Use instead the "

6458 "``postgresql.distinct_on`` function as an extension.",

6459 "2.1",

6460 )

6461 self._distinct_on = self._distinct_on + tuple(

6462 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6463 for e in expr

6464 )

6465 return self

6466

6467 @_generative

6468 def select_from(self, *froms: _FromClauseArgument) -> Self:

6469 r"""Return a new :func:`_expression.select` construct with the

6470 given FROM expression(s)

6471 merged into its list of FROM objects.

6472

6473 E.g.::

6474

6475 table1 = table("t1", column("a"))

6476 table2 = table("t2", column("b"))

6477 s = select(table1.c.a).select_from(

6478 table1.join(table2, table1.c.a == table2.c.b)

6479 )

6480

6481 The "from" list is a unique set on the identity of each element,

6482 so adding an already present :class:`_schema.Table`

6483 or other selectable

6484 will have no effect. Passing a :class:`_expression.Join` that refers

6485 to an already present :class:`_schema.Table`

6486 or other selectable will have

6487 the effect of concealing the presence of that selectable as

6488 an individual element in the rendered FROM list, instead

6489 rendering it into a JOIN clause.

6490

6491 While the typical purpose of :meth:`_expression.Select.select_from`

6492 is to

6493 replace the default, derived FROM clause with a join, it can

6494 also be called with individual table elements, multiple times

6495 if desired, in the case that the FROM clause cannot be fully

6496 derived from the columns clause::

6497

6498 select(func.count("*")).select_from(table1)

6499

6500 """

6501

6502 self._from_obj += tuple(

6503 coercions.expect(

6504 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6505 )

6506 for fromclause in froms

6507 )

6508 return self

6509

6510 @_generative

6511 def correlate(

6512 self,

6513 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6514 ) -> Self:

6515 r"""Return a new :class:`_expression.Select`

6516 which will correlate the given FROM

6517 clauses to that of an enclosing :class:`_expression.Select`.

6518

6519 Calling this method turns off the :class:`_expression.Select` object's

6520 default behavior of "auto-correlation". Normally, FROM elements

6521 which appear in a :class:`_expression.Select`

6522 that encloses this one via

6523 its :term:`WHERE clause`, ORDER BY, HAVING or

6524 :term:`columns clause` will be omitted from this

6525 :class:`_expression.Select`

6526 object's :term:`FROM clause`.

6527 Setting an explicit correlation collection using the

6528 :meth:`_expression.Select.correlate`

6529 method provides a fixed list of FROM objects

6530 that can potentially take place in this process.

6531

6532 When :meth:`_expression.Select.correlate`

6533 is used to apply specific FROM clauses

6534 for correlation, the FROM elements become candidates for

6535 correlation regardless of how deeply nested this

6536 :class:`_expression.Select`

6537 object is, relative to an enclosing :class:`_expression.Select`

6538 which refers to

6539 the same FROM object. This is in contrast to the behavior of

6540 "auto-correlation" which only correlates to an immediate enclosing

6541 :class:`_expression.Select`.

6542 Multi-level correlation ensures that the link

6543 between enclosed and enclosing :class:`_expression.Select`

6544 is always via

6545 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6546 correlation to take place.

6547

6548 If ``None`` is passed, the :class:`_expression.Select`

6549 object will correlate

6550 none of its FROM entries, and all will render unconditionally

6551 in the local FROM clause.

6552

6553 :param \*fromclauses: one or more :class:`.FromClause` or other

6554 FROM-compatible construct such as an ORM mapped entity to become part

6555 of the correlate collection; alternatively pass a single value

6556 ``None`` to remove all existing correlations.

6557

6558 .. seealso::

6559

6560 :meth:`_expression.Select.correlate_except`

6561

6562 :ref:`tutorial_scalar_subquery`

6563

6564 """

6565

6566 # tests failing when we try to change how these

6567 # arguments are passed

6568

6569 self._auto_correlate = False

6570 if not fromclauses or fromclauses[0] in {None, False}:

6571 if len(fromclauses) > 1:

6572 raise exc.ArgumentError(

6573 "additional FROM objects not accepted when "

6574 "passing None/False to correlate()"

6575 )

6576 self._correlate = ()

6577 else:

6578 self._correlate = self._correlate + tuple(

6579 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6580 )

6581 return self

6582

6583 @_generative

6584 def correlate_except(

6585 self,

6586 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6587 ) -> Self:

6588 r"""Return a new :class:`_expression.Select`

6589 which will omit the given FROM

6590 clauses from the auto-correlation process.

6591

6592 Calling :meth:`_expression.Select.correlate_except` turns off the

6593 :class:`_expression.Select` object's default behavior of

6594 "auto-correlation" for the given FROM elements. An element

6595 specified here will unconditionally appear in the FROM list, while

6596 all other FROM elements remain subject to normal auto-correlation

6597 behaviors.

6598

6599 If ``None`` is passed, or no arguments are passed,

6600 the :class:`_expression.Select` object will correlate all of its

6601 FROM entries.

6602

6603 :param \*fromclauses: a list of one or more

6604 :class:`_expression.FromClause`

6605 constructs, or other compatible constructs (i.e. ORM-mapped

6606 classes) to become part of the correlate-exception collection.

6607

6608 .. seealso::

6609

6610 :meth:`_expression.Select.correlate`

6611

6612 :ref:`tutorial_scalar_subquery`

6613

6614 """

6615

6616 self._auto_correlate = False

6617 if not fromclauses or fromclauses[0] in {None, False}:

6618 if len(fromclauses) > 1:

6619 raise exc.ArgumentError(

6620 "additional FROM objects not accepted when "

6621 "passing None/False to correlate_except()"

6622 )

6623 self._correlate_except = ()

6624 else:

6625 self._correlate_except = (self._correlate_except or ()) + tuple(

6626 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6627 )

6628

6629 return self

6630

6631 @HasMemoized_ro_memoized_attribute

6632 def selected_columns(

6633 self,

6634 ) -> ColumnCollection[str, ColumnElement[Any]]:

6635 """A :class:`_expression.ColumnCollection`

6636 representing the columns that

6637 this SELECT statement or similar construct returns in its result set,

6638 not including :class:`_sql.TextClause` constructs.

6639

6640 This collection differs from the :attr:`_expression.FromClause.columns`

6641 collection of a :class:`_expression.FromClause` in that the columns

6642 within this collection cannot be directly nested inside another SELECT

6643 statement; a subquery must be applied first which provides for the

6644 necessary parenthesization required by SQL.

6645

6646 For a :func:`_expression.select` construct, the collection here is

6647 exactly what would be rendered inside the "SELECT" statement, and the

6648 :class:`_expression.ColumnElement` objects are directly present as they

6649 were given, e.g.::

6650

6651 col1 = column("q", Integer)

6652 col2 = column("p", Integer)

6653 stmt = select(col1, col2)

6654

6655 Above, ``stmt.selected_columns`` would be a collection that contains

6656 the ``col1`` and ``col2`` objects directly. For a statement that is

6657 against a :class:`_schema.Table` or other

6658 :class:`_expression.FromClause`, the collection will use the

6659 :class:`_expression.ColumnElement` objects that are in the

6660 :attr:`_expression.FromClause.c` collection of the from element.

6661

6662 A use case for the :attr:`_sql.Select.selected_columns` collection is

6663 to allow the existing columns to be referenced when adding additional

6664 criteria, e.g.::

6665

6666 def filter_on_id(my_select, id):

6667 return my_select.where(my_select.selected_columns["id"] == id)

6668

6669

6670 stmt = select(MyModel)

6671

6672 # adds "WHERE id=:param" to the statement

6673 stmt = filter_on_id(stmt, 42)

6674

6675 .. note::

6676

6677 The :attr:`_sql.Select.selected_columns` collection does not

6678 include expressions established in the columns clause using the

6679 :func:`_sql.text` construct; these are silently omitted from the

6680 collection. To use plain textual column expressions inside of a

6681 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6682 construct.

6683

6684

6685 .. versionadded:: 1.4

6686

6687 """

6688

6689 # compare to SelectState._generate_columns_plus_names, which

6690 # generates the actual names used in the SELECT string. that

6691 # method is more complex because it also renders columns that are

6692 # fully ambiguous, e.g. same column more than once.

6693 conv = cast(

6694 "Callable[[Any], str]",

6695 SelectState._column_naming_convention(self._label_style),

6696 )

6697

6698 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6699 [

6700 (conv(c), c)

6701 for c in self._all_selected_columns

6702 if is_column_element(c)

6703 ]

6704 )

6705 return cc.as_readonly()

6706

6707 @HasMemoized_ro_memoized_attribute

6708 def _all_selected_columns(self) -> _SelectIterable:

6709 meth = SelectState.get_plugin_class(self).all_selected_columns

6710 return list(meth(self))

6711

6712 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6713 if self._label_style is LABEL_STYLE_NONE:

6714 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6715 return self

6716

6717 def _generate_fromclause_column_proxies(

6718 self,

6719 subquery: FromClause,

6720 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6721 primary_key: ColumnSet,

6722 foreign_keys: Set[KeyedColumnElement[Any]],

6723 *,

6724 proxy_compound_columns: Optional[

6725 Iterable[Sequence[ColumnElement[Any]]]

6726 ] = None,

6727 ) -> None:

6728 """Generate column proxies to place in the exported ``.c``

6729 collection of a subquery."""

6730

6731 if proxy_compound_columns:

6732 extra_col_iterator = proxy_compound_columns

6733 prox = [

6734 c._make_proxy(

6735 subquery,

6736 key=proxy_key,

6737 name=required_label_name,

6738 name_is_truncatable=True,

6739 compound_select_cols=extra_cols,

6740 primary_key=primary_key,

6741 foreign_keys=foreign_keys,

6742 )

6743 for (

6744 (

6745 required_label_name,

6746 proxy_key,

6747 fallback_label_name,

6748 c,

6749 repeated,

6750 ),

6751 extra_cols,

6752 ) in (

6753 zip(

6754 self._generate_columns_plus_names(False),

6755 extra_col_iterator,

6756 )

6757 )

6758 if is_column_element(c)

6759 ]

6760 else:

6761 prox = [

6762 c._make_proxy(

6763 subquery,

6764 key=proxy_key,

6765 name=required_label_name,

6766 name_is_truncatable=True,

6767 primary_key=primary_key,

6768 foreign_keys=foreign_keys,

6769 )

6770 for (

6771 required_label_name,

6772 proxy_key,

6773 fallback_label_name,

6774 c,

6775 repeated,

6776 ) in (self._generate_columns_plus_names(False))

6777 if is_column_element(c)

6778 ]

6779

6780 columns._populate_separate_keys(prox)

6781

6782 def _needs_parens_for_grouping(self) -> bool:

6783 return self._has_row_limiting_clause or bool(

6784 self._order_by_clause.clauses

6785 )

6786

6787 def self_group(

6788 self, against: Optional[OperatorType] = None

6789 ) -> Union[SelectStatementGrouping[Self], Self]:

6790 """Return a 'grouping' construct as per the

6791 :class:`_expression.ClauseElement` specification.

6792

6793 This produces an element that can be embedded in an expression. Note

6794 that this method is called automatically as needed when constructing

6795 expressions and should not require explicit use.

6796

6797 """

6798 if (

6799 isinstance(against, CompoundSelect)

6800 and not self._needs_parens_for_grouping()

6801 ):

6802 return self

6803 else:

6804 return SelectStatementGrouping(self)

6805

6806 def union(

6807 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6808 ) -> CompoundSelect[Unpack[_Ts]]:

6809 r"""Return a SQL ``UNION`` of this select() construct against

6810 the given selectables provided as positional arguments.

6811

6812 :param \*other: one or more elements with which to create a

6813 UNION.

6814

6815 .. versionchanged:: 1.4.28

6816

6817 multiple elements are now accepted.

6818

6819 :param \**kwargs: keyword arguments are forwarded to the constructor

6820 for the newly created :class:`_sql.CompoundSelect` object.

6821

6822 """

6823 return CompoundSelect._create_union(self, *other)

6824

6825 def union_all(

6826 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6827 ) -> CompoundSelect[Unpack[_Ts]]:

6828 r"""Return a SQL ``UNION ALL`` of this select() construct against

6829 the given selectables provided as positional arguments.

6830

6831 :param \*other: one or more elements with which to create a

6832 UNION.

6833

6834 .. versionchanged:: 1.4.28

6835

6836 multiple elements are now accepted.

6837

6838 :param \**kwargs: keyword arguments are forwarded to the constructor

6839 for the newly created :class:`_sql.CompoundSelect` object.

6840

6841 """

6842 return CompoundSelect._create_union_all(self, *other)

6843

6844 def except_(

6845 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6846 ) -> CompoundSelect[Unpack[_Ts]]:

6847 r"""Return a SQL ``EXCEPT`` of this select() construct against

6848 the given selectable provided as positional arguments.

6849

6850 :param \*other: one or more elements with which to create a

6851 UNION.

6852

6853 .. versionchanged:: 1.4.28

6854

6855 multiple elements are now accepted.

6856

6857 """

6858 return CompoundSelect._create_except(self, *other)

6859

6860 def except_all(

6861 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6862 ) -> CompoundSelect[Unpack[_Ts]]:

6863 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6864 the given selectables provided as positional arguments.

6865

6866 :param \*other: one or more elements with which to create a

6867 UNION.

6868

6869 .. versionchanged:: 1.4.28

6870

6871 multiple elements are now accepted.

6872

6873 """

6874 return CompoundSelect._create_except_all(self, *other)

6875

6876 def intersect(

6877 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6878 ) -> CompoundSelect[Unpack[_Ts]]:

6879 r"""Return a SQL ``INTERSECT`` of this select() construct against

6880 the given selectables provided as positional arguments.

6881

6882 :param \*other: one or more elements with which to create a

6883 UNION.

6884

6885 .. versionchanged:: 1.4.28

6886

6887 multiple elements are now accepted.

6888

6889 :param \**kwargs: keyword arguments are forwarded to the constructor

6890 for the newly created :class:`_sql.CompoundSelect` object.

6891

6892 """

6893 return CompoundSelect._create_intersect(self, *other)

6894

6895 def intersect_all(

6896 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6897 ) -> CompoundSelect[Unpack[_Ts]]:

6898 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6899 against the given selectables provided as positional arguments.

6900

6901 :param \*other: one or more elements with which to create a

6902 UNION.

6903

6904 .. versionchanged:: 1.4.28

6905

6906 multiple elements are now accepted.

6907

6908 :param \**kwargs: keyword arguments are forwarded to the constructor

6909 for the newly created :class:`_sql.CompoundSelect` object.

6910

6911 """

6912 return CompoundSelect._create_intersect_all(self, *other)

6913

6914

6915class ScalarSelect(

6916 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6917):

6918 """Represent a scalar subquery.

6919

6920

6921 A :class:`_sql.ScalarSelect` is created by invoking the

6922 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6923 then participates in other SQL expressions as a SQL column expression

6924 within the :class:`_sql.ColumnElement` hierarchy.

6925

6926 .. seealso::

6927

6928 :meth:`_sql.SelectBase.scalar_subquery`

6929

6930 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

6931

6932 """

6933

6934 _traverse_internals: _TraverseInternalsType = [

6935 ("element", InternalTraversal.dp_clauseelement),

6936 ("type", InternalTraversal.dp_type),

6937 ]

6938

6939 _from_objects: List[FromClause] = []

6940 _is_from_container = True

6941 if not TYPE_CHECKING:

6942 _is_implicitly_boolean = False

6943 inherit_cache = True

6944

6945 element: SelectBase

6946

6947 def __init__(self, element: SelectBase) -> None:

6948 self.element = element

6949 self.type = element._scalar_type()

6950 self._propagate_attrs = element._propagate_attrs

6951

6952 def __getattr__(self, attr: str) -> Any:

6953 return getattr(self.element, attr)

6954

6955 def __getstate__(self) -> Dict[str, Any]:

6956 return {"element": self.element, "type": self.type}

6957

6958 def __setstate__(self, state: Dict[str, Any]) -> None:

6959 self.element = state["element"]

6960 self.type = state["type"]

6961

6962 @property

6963 def columns(self) -> NoReturn:

6964 raise exc.InvalidRequestError(

6965 "Scalar Select expression has no "

6966 "columns; use this object directly "

6967 "within a column-level expression."

6968 )

6969

6970 c = columns

6971

6972 @_generative

6973 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

6974 """Apply a WHERE clause to the SELECT statement referred to

6975 by this :class:`_expression.ScalarSelect`.

6976

6977 """

6978 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

6979 crit

6980 )

6981 return self

6982

6983 def self_group(self, against: Optional[OperatorType] = None) -> Self:

6984 return self

6985

6986 def _ungroup(self) -> Self:

6987 return self

6988

6989 @_generative

6990 def correlate(

6991 self,

6992 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6993 ) -> Self:

6994 r"""Return a new :class:`_expression.ScalarSelect`

6995 which will correlate the given FROM

6996 clauses to that of an enclosing :class:`_expression.Select`.

6997

6998 This method is mirrored from the :meth:`_sql.Select.correlate` method

6999 of the underlying :class:`_sql.Select`. The method applies the

7000 :meth:_sql.Select.correlate` method, then returns a new

7001 :class:`_sql.ScalarSelect` against that statement.

7002

7003 .. versionadded:: 1.4 Previously, the

7004 :meth:`_sql.ScalarSelect.correlate`

7005 method was only available from :class:`_sql.Select`.

7006

7007 :param \*fromclauses: a list of one or more

7008 :class:`_expression.FromClause`

7009 constructs, or other compatible constructs (i.e. ORM-mapped

7010 classes) to become part of the correlate collection.

7011

7012 .. seealso::

7013

7014 :meth:`_expression.ScalarSelect.correlate_except`

7015

7016 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7017

7018

7019 """

7020 self.element = cast(

7021 "Select[Unpack[TupleAny]]", self.element

7022 ).correlate(*fromclauses)

7023 return self

7024

7025 @_generative

7026 def correlate_except(

7027 self,

7028 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7029 ) -> Self:

7030 r"""Return a new :class:`_expression.ScalarSelect`

7031 which will omit the given FROM

7032 clauses from the auto-correlation process.

7033

7034 This method is mirrored from the

7035 :meth:`_sql.Select.correlate_except` method of the underlying

7036 :class:`_sql.Select`. The method applies the

7037 :meth:_sql.Select.correlate_except` method, then returns a new

7038 :class:`_sql.ScalarSelect` against that statement.

7039

7040 .. versionadded:: 1.4 Previously, the

7041 :meth:`_sql.ScalarSelect.correlate_except`

7042 method was only available from :class:`_sql.Select`.

7043

7044 :param \*fromclauses: a list of one or more

7045 :class:`_expression.FromClause`

7046 constructs, or other compatible constructs (i.e. ORM-mapped

7047 classes) to become part of the correlate-exception collection.

7048

7049 .. seealso::

7050

7051 :meth:`_expression.ScalarSelect.correlate`

7052

7053 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7054

7055

7056 """

7057

7058 self.element = cast(

7059 "Select[Unpack[TupleAny]]", self.element

7060 ).correlate_except(*fromclauses)

7061 return self

7062

7063

7064class Exists(UnaryExpression[bool]):

7065 """Represent an ``EXISTS`` clause.

7066

7067 See :func:`_sql.exists` for a description of usage.

7068

7069 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7070 instance by calling :meth:`_sql.SelectBase.exists`.

7071

7072 """

7073

7074 inherit_cache = True

7075

7076 def __init__(

7077 self,

7078 __argument: Optional[

7079 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7080 ] = None,

7081 /,

7082 ):

7083 s: ScalarSelect[Any]

7084

7085 # TODO: this seems like we should be using coercions for this

7086 if __argument is None:

7087 s = Select(literal_column("*")).scalar_subquery()

7088 elif isinstance(__argument, SelectBase):

7089 s = __argument.scalar_subquery()

7090 s._propagate_attrs = __argument._propagate_attrs

7091 elif isinstance(__argument, ScalarSelect):

7092 s = __argument

7093 else:

7094 s = Select(__argument).scalar_subquery()

7095

7096 UnaryExpression.__init__(

7097 self,

7098 s,

7099 operator=operators.exists,

7100 type_=type_api.BOOLEANTYPE,

7101 )

7102

7103 @util.ro_non_memoized_property

7104 def _from_objects(self) -> List[FromClause]:

7105 return []

7106

7107 def _regroup(

7108 self,

7109 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7110 ) -> ScalarSelect[Any]:

7111

7112 assert isinstance(self.element, ScalarSelect)

7113 element = self.element.element

7114 if not isinstance(element, Select):

7115 raise exc.InvalidRequestError(

7116 "Can only apply this operation to a plain SELECT construct"

7117 )

7118 new_element = fn(element)

7119

7120 return_value = new_element.scalar_subquery()

7121 return return_value

7122

7123 def select(self) -> Select[bool]:

7124 r"""Return a SELECT of this :class:`_expression.Exists`.

7125

7126 e.g.::

7127

7128 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7129

7130 This will produce a statement resembling:

7131

7132 .. sourcecode:: sql

7133

7134 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7135

7136 .. seealso::

7137

7138 :func:`_expression.select` - general purpose

7139 method which allows for arbitrary column lists.

7140

7141 """ # noqa

7142

7143 return Select(self)

7144

7145 def correlate(

7146 self,

7147 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7148 ) -> Self:

7149 """Apply correlation to the subquery noted by this

7150 :class:`_sql.Exists`.

7151

7152 .. seealso::

7153

7154 :meth:`_sql.ScalarSelect.correlate`

7155

7156 """

7157 e = self._clone()

7158 e.element = self._regroup(

7159 lambda element: element.correlate(*fromclauses)

7160 )

7161 return e

7162

7163 def correlate_except(

7164 self,

7165 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7166 ) -> Self:

7167 """Apply correlation to the subquery noted by this

7168 :class:`_sql.Exists`.

7169

7170 .. seealso::

7171

7172 :meth:`_sql.ScalarSelect.correlate_except`

7173

7174 """

7175 e = self._clone()

7176 e.element = self._regroup(

7177 lambda element: element.correlate_except(*fromclauses)

7178 )

7179 return e

7180

7181 def select_from(self, *froms: _FromClauseArgument) -> Self:

7182 """Return a new :class:`_expression.Exists` construct,

7183 applying the given

7184 expression to the :meth:`_expression.Select.select_from`

7185 method of the select

7186 statement contained.

7187

7188 .. note:: it is typically preferable to build a :class:`_sql.Select`

7189 statement first, including the desired WHERE clause, then use the

7190 :meth:`_sql.SelectBase.exists` method to produce an

7191 :class:`_sql.Exists` object at once.

7192

7193 """

7194 e = self._clone()

7195 e.element = self._regroup(lambda element: element.select_from(*froms))

7196 return e

7197

7198 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7199 """Return a new :func:`_expression.exists` construct with the

7200 given expression added to

7201 its WHERE clause, joined to the existing clause via AND, if any.

7202

7203

7204 .. note:: it is typically preferable to build a :class:`_sql.Select`

7205 statement first, including the desired WHERE clause, then use the

7206 :meth:`_sql.SelectBase.exists` method to produce an

7207 :class:`_sql.Exists` object at once.

7208

7209 """

7210 e = self._clone()

7211 e.element = self._regroup(lambda element: element.where(*clause))

7212 return e

7213

7214

7215class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7216 """Wrap a :class:`_expression.TextClause` construct within a

7217 :class:`_expression.SelectBase`

7218 interface.

7219

7220 This allows the :class:`_expression.TextClause` object to gain a

7221 ``.c`` collection

7222 and other FROM-like capabilities such as

7223 :meth:`_expression.FromClause.alias`,

7224 :meth:`_expression.SelectBase.cte`, etc.

7225

7226 The :class:`_expression.TextualSelect` construct is produced via the

7227 :meth:`_expression.TextClause.columns`

7228 method - see that method for details.

7229

7230 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7231 class was renamed

7232 from ``TextAsFrom``, to more correctly suit its role as a

7233 SELECT-oriented object and not a FROM clause.

7234

7235 .. seealso::

7236

7237 :func:`_expression.text`

7238

7239 :meth:`_expression.TextClause.columns` - primary creation interface.

7240

7241 """

7242

7243 __visit_name__ = "textual_select"

7244

7245 _label_style = LABEL_STYLE_NONE

7246

7247 _traverse_internals: _TraverseInternalsType = (

7248 [

7249 ("element", InternalTraversal.dp_clauseelement),

7250 ("column_args", InternalTraversal.dp_clauseelement_list),

7251 ]

7252 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7253 + HasCTE._has_ctes_traverse_internals

7254 + Executable._executable_traverse_internals

7255 )

7256

7257 _is_textual = True

7258

7259 is_text = True

7260 is_select = True

7261

7262 def __init__(

7263 self,

7264 text: TextClause,

7265 columns: List[_ColumnExpressionArgument[Any]],

7266 positional: bool = False,

7267 ) -> None:

7268 self._init(

7269 text,

7270 # convert for ORM attributes->columns, etc

7271 [

7272 coercions.expect(roles.LabeledColumnExprRole, c)

7273 for c in columns

7274 ],

7275 positional,

7276 )

7277

7278 def _init(

7279 self,

7280 text: TextClause,

7281 columns: List[NamedColumn[Any]],

7282 positional: bool = False,

7283 ) -> None:

7284 self.element = text

7285 self.column_args = columns

7286 self.positional = positional

7287

7288 @HasMemoized_ro_memoized_attribute

7289 def selected_columns(

7290 self,

7291 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7292 """A :class:`_expression.ColumnCollection`

7293 representing the columns that

7294 this SELECT statement or similar construct returns in its result set,

7295 not including :class:`_sql.TextClause` constructs.

7296

7297 This collection differs from the :attr:`_expression.FromClause.columns`

7298 collection of a :class:`_expression.FromClause` in that the columns

7299 within this collection cannot be directly nested inside another SELECT

7300 statement; a subquery must be applied first which provides for the

7301 necessary parenthesization required by SQL.

7302

7303 For a :class:`_expression.TextualSelect` construct, the collection

7304 contains the :class:`_expression.ColumnElement` objects that were

7305 passed to the constructor, typically via the

7306 :meth:`_expression.TextClause.columns` method.

7307

7308

7309 .. versionadded:: 1.4

7310

7311 """

7312 return ColumnCollection(

7313 (c.key, c) for c in self.column_args

7314 ).as_readonly()

7315

7316 @util.ro_non_memoized_property

7317 def _all_selected_columns(self) -> _SelectIterable:

7318 return self.column_args

7319

7320 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7321 return self

7322

7323 def _ensure_disambiguated_names(self) -> TextualSelect:

7324 return self

7325

7326 @_generative

7327 def bindparams(

7328 self,

7329 *binds: BindParameter[Any],

7330 **bind_as_values: Any,

7331 ) -> Self:

7332 self.element = self.element.bindparams(*binds, **bind_as_values)

7333 return self

7334

7335 def _generate_fromclause_column_proxies(

7336 self,

7337 fromclause: FromClause,

7338 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7339 primary_key: ColumnSet,

7340 foreign_keys: Set[KeyedColumnElement[Any]],

7341 *,

7342 proxy_compound_columns: Optional[

7343 Iterable[Sequence[ColumnElement[Any]]]

7344 ] = None,

7345 ) -> None:

7346 if TYPE_CHECKING:

7347 assert isinstance(fromclause, Subquery)

7348

7349 if proxy_compound_columns:

7350 columns._populate_separate_keys(

7351 c._make_proxy(

7352 fromclause,

7353 compound_select_cols=extra_cols,

7354 primary_key=primary_key,

7355 foreign_keys=foreign_keys,

7356 )

7357 for c, extra_cols in zip(

7358 self.column_args, proxy_compound_columns

7359 )

7360 )

7361 else:

7362 columns._populate_separate_keys(

7363 c._make_proxy(

7364 fromclause,

7365 primary_key=primary_key,

7366 foreign_keys=foreign_keys,

7367 )

7368 for c in self.column_args

7369 )

7370

7371 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7372 return self.column_args[0].type

7373

7374

7375TextAsFrom = TextualSelect

7376"""Backwards compatibility with the previous name"""

7377

7378

7379class AnnotatedFromClause(Annotated):

7380 def _copy_internals(

7381 self,

7382 _annotations_traversal: bool = False,

7383 ind_cols_on_fromclause: bool = False,

7384 **kw: Any,

7385 ) -> None:

7386 super()._copy_internals(**kw)

7387

7388 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7389 # the traversals used by annotations for these cases are not currently

7390 # designed around expecting that inner elements inside of

7391 # AnnotatedFromClause's element are also deep copied, so skip for these

7392 # cases. in other cases such as plain visitors.cloned_traverse(), we

7393 # expect this to happen. see issue #12915

7394 if not _annotations_traversal:

7395 ee = self._Annotated__element # type: ignore

7396 ee._copy_internals(**kw)

7397

7398 if ind_cols_on_fromclause:

7399 # passed from annotations._deep_annotate(). See that function

7400 # for notes

7401 ee = self._Annotated__element # type: ignore

7402 self.c = ee.__class__.c.fget(self) # type: ignore

7403

7404 @util.ro_memoized_property

7405 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7406 """proxy the .c collection of the underlying FromClause.

7407

7408 Originally implemented in 2008 as a simple load of the .c collection

7409 when the annotated construct was created (see d3621ae961a), in modern

7410 SQLAlchemy versions this can be expensive for statements constructed

7411 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7412 it, which works just as well.

7413

7414 Two different use cases seem to require the collection either copied

7415 from the underlying one, or unique to this AnnotatedFromClause.

7416

7417 See test_selectable->test_annotated_corresponding_column

7418

7419 """

7420 ee = self._Annotated__element # type: ignore

7421 return ee.c # type: ignore