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

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

1202 FROM table1 JOIN table2 ON true

1203

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

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

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

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

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

1209

1210

1211 .. versionadded:: 1.4

1212

1213 """ # noqa: E501

1214

1215 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1218 when generating the columns clause of a SELECT statement.

1219

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

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

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

1223

1224 .. sourcecode:: pycon+sql

1225

1226 >>> from sqlalchemy import (

1227 ... table,

1228 ... column,

1229 ... select,

1230 ... true,

1231 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1232 ... )

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

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

1235 >>> print(

1236 ... select(table1, table2)

1237 ... .join(table2, true())

1238 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1239 ... )

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

1241 FROM table1 JOIN table2 ON true

1242

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

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

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

1246

1247 .. versionadded:: 1.4

1248

1249 """ # noqa: E501

1250

1251 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1252 """The default label style, refers to

1253 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1254

1255 .. versionadded:: 1.4

1256

1257 """

1258

1259 LABEL_STYLE_LEGACY_ORM = 3

1260

1261

1262(

1263 LABEL_STYLE_NONE,

1264 LABEL_STYLE_TABLENAME_PLUS_COL,

1265 LABEL_STYLE_DISAMBIGUATE_ONLY,

1266 _,

1267) = list(SelectLabelStyle)

1268

1269LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1270

1271

1272class Join(roles.DMLTableRole, FromClause[_KeyColCC_co]):

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

1274 :class:`_expression.FromClause`

1275 elements.

1276

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

1278 is the module-level

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

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

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

1282 :class:`_schema.Table`).

1283

1284 .. seealso::

1285

1286 :func:`_expression.join`

1287

1288 :meth:`_expression.FromClause.join`

1289

1290 """

1291

1292 __visit_name__ = "join"

1293

1294 _traverse_internals: _TraverseInternalsType = [

1295 ("left", InternalTraversal.dp_clauseelement),

1296 ("right", InternalTraversal.dp_clauseelement),

1297 ("onclause", InternalTraversal.dp_clauseelement),

1298 ("isouter", InternalTraversal.dp_boolean),

1299 ("full", InternalTraversal.dp_boolean),

1300 ]

1301

1302 _is_join = True

1303

1304 left: FromClause

1305 right: FromClause

1306 onclause: Optional[ColumnElement[bool]]

1307 isouter: bool

1308 full: bool

1309

1310 def __init__(

1311 self,

1312 left: _FromClauseArgument,

1313 right: _FromClauseArgument,

1314 onclause: Optional[_OnClauseArgument] = None,

1315 isouter: bool = False,

1316 full: bool = False,

1317 ):

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

1319

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

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

1322 :class:`_expression.FromClause` object.

1323

1324 """

1325

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

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

1328 # annotated objects. test_orm.py -> test_fetch_results

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

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

1331 # callcounts for a single compilation in that particular test

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

1333 # 29200 -> 30373

1334

1335 self.left = coercions.expect(

1336 roles.FromClauseRole,

1337 left,

1338 )

1339 self.right = coercions.expect(

1340 roles.FromClauseRole,

1341 right,

1342 ).self_group()

1343

1344 if onclause is None:

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

1346 else:

1347 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1348 # not merged yet

1349 self.onclause = coercions.expect(

1350 roles.OnClauseRole, onclause

1351 ).self_group(against=operators._asbool)

1352

1353 self.isouter = isouter

1354 self.full = full

1355

1356 @util.ro_non_memoized_property

1357 def description(self) -> str:

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

1359 self.left.description,

1360 id(self.left),

1361 self.right.description,

1362 id(self.right),

1363 )

1364

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

1366 return (

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

1368 # as well

1369 hash(fromclause) == hash(self)

1370 or self.left.is_derived_from(fromclause)

1371 or self.right.is_derived_from(fromclause)

1372 )

1373

1374 def self_group(

1375 self, against: Optional[OperatorType] = None

1376 ) -> FromGrouping:

1377 return FromGrouping(self)

1378

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

1380 def _populate_column_collection(

1381 self,

1382 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

1383 primary_key: ColumnSet,

1384 foreign_keys: Set[KeyedColumnElement[Any]],

1385 ) -> None:

1386 sqlutil = util.preloaded.sql_util

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

1388 c for c in self.right.c

1389 ]

1390

1391 primary_key.extend(

1392 sqlutil.reduce_columns(

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

1394 )

1395 )

1396 columns._populate_separate_keys(

1397 (col._tq_key_label, col) for col in _columns # type: ignore[misc]

1398 )

1399 foreign_keys.update(

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

1401 )

1402

1403 def _copy_internals(

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

1405 ) -> None:

1406 # see Select._copy_internals() for similar concept

1407

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

1409 # determine the new FROM clauses

1410 all_the_froms = set(

1411 itertools.chain(

1412 _from_objects(self.left),

1413 _from_objects(self.right),

1414 )

1415 )

1416

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

1418 # cache used by the clone function

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

1420

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

1422 # ColumnClause with parent table referring to those

1423 # replaced FromClause objects

1424 def replace(

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

1426 **kw: Any,

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

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

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

1430 return newelem

1431 return None

1432

1433 kw["replace"] = replace

1434

1435 # run normal _copy_internals. the clones for

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

1437 # cache

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

1439

1440 self._reset_memoizations()

1441

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

1443 super()._refresh_for_new_column(column)

1444 self.left._refresh_for_new_column(column)

1445 self.right._refresh_for_new_column(column)

1446

1447 def _match_primaries(

1448 self,

1449 left: FromClause,

1450 right: FromClause,

1451 ) -> ColumnElement[bool]:

1452 if isinstance(left, Join):

1453 left_right = left.right

1454 else:

1455 left_right = None

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

1457

1458 @classmethod

1459 def _join_condition(

1460 cls,

1461 a: FromClause,

1462 b: FromClause,

1463 *,

1464 a_subset: Optional[FromClause] = None,

1465 consider_as_foreign_keys: Optional[

1466 AbstractSet[ColumnClause[Any]]

1467 ] = None,

1468 ) -> ColumnElement[bool]:

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

1470

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

1472

1473 """

1474 constraints = cls._joincond_scan_left_right(

1475 a, a_subset, b, consider_as_foreign_keys

1476 )

1477

1478 if len(constraints) > 1:

1479 cls._joincond_trim_constraints(

1480 a, b, constraints, consider_as_foreign_keys

1481 )

1482

1483 if len(constraints) == 0:

1484 if isinstance(b, FromGrouping):

1485 hint = (

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

1487 "subquery using alias()?"

1488 )

1489 else:

1490 hint = ""

1491 raise exc.NoForeignKeysError(

1492 "Can't find any foreign key relationships "

1493 "between '%s' and '%s'.%s"

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

1495 )

1496

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

1498 if len(crit) == 1:

1499 return crit[0]

1500 else:

1501 return and_(*crit)

1502

1503 @classmethod

1504 def _can_join(

1505 cls,

1506 left: FromClause,

1507 right: FromClause,

1508 *,

1509 consider_as_foreign_keys: Optional[

1510 AbstractSet[ColumnClause[Any]]

1511 ] = None,

1512 ) -> bool:

1513 if isinstance(left, Join):

1514 left_right = left.right

1515 else:

1516 left_right = None

1517

1518 constraints = cls._joincond_scan_left_right(

1519 a=left,

1520 b=right,

1521 a_subset=left_right,

1522 consider_as_foreign_keys=consider_as_foreign_keys,

1523 )

1524

1525 return bool(constraints)

1526

1527 @classmethod

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

1529 def _joincond_scan_left_right(

1530 cls,

1531 a: FromClause,

1532 a_subset: Optional[FromClause],

1533 b: FromClause,

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

1535 ) -> collections.defaultdict[

1536 Optional[ForeignKeyConstraint],

1537 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1538 ]:

1539 sql_util = util.preloaded.sql_util

1540

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

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

1543

1544 constraints: collections.defaultdict[

1545 Optional[ForeignKeyConstraint],

1546 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1547 ] = collections.defaultdict(list)

1548

1549 for left in (a_subset, a):

1550 if left is None:

1551 continue

1552 for fk in sorted(

1553 b.foreign_keys,

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

1555 ):

1556 if (

1557 consider_as_foreign_keys is not None

1558 and fk.parent not in consider_as_foreign_keys

1559 ):

1560 continue

1561 try:

1562 col = fk.get_referent(left)

1563 except exc.NoReferenceError as nrte:

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

1565 if nrte.table_name in table_names:

1566 raise

1567 else:

1568 continue

1569

1570 if col is not None:

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

1572 if left is not b:

1573 for fk in sorted(

1574 left.foreign_keys,

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

1576 ):

1577 if (

1578 consider_as_foreign_keys is not None

1579 and fk.parent not in consider_as_foreign_keys

1580 ):

1581 continue

1582 try:

1583 col = fk.get_referent(b)

1584 except exc.NoReferenceError as nrte:

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

1586 if nrte.table_name in table_names:

1587 raise

1588 else:

1589 continue

1590

1591 if col is not None:

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

1593 if constraints:

1594 break

1595 return constraints

1596

1597 @classmethod

1598 def _joincond_trim_constraints(

1599 cls,

1600 a: FromClause,

1601 b: FromClause,

1602 constraints: Dict[Any, Any],

1603 consider_as_foreign_keys: Optional[Any],

1604 ) -> None:

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

1606 # to include just those FKCs that match exactly to

1607 # "consider_as_foreign_keys".

1608 if consider_as_foreign_keys:

1609 for const in list(constraints):

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

1611 consider_as_foreign_keys

1612 ):

1613 del constraints[const]

1614

1615 # if still multiple constraints, but

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

1617 if len(constraints) > 1:

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

1619 if len(dedupe) == 1:

1620 key = list(constraints)[0]

1621 constraints = {key: constraints[key]}

1622

1623 if len(constraints) != 1:

1624 raise exc.AmbiguousForeignKeysError(

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

1626 "tables have more than one foreign key "

1627 "constraint relationship between them. "

1628 "Please specify the 'onclause' of this "

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

1630 )

1631

1632 @overload

1633 def select(

1634 self: Join[HasRowPos[Unpack[_Ts]]], # type: ignore[type-var]

1635 ) -> Select[Unpack[_Ts]]: ...

1636 @overload

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

1638

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

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

1641 :class:`_expression.Join`.

1642

1643 E.g.::

1644

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

1646

1647 stmt = stmt.select()

1648

1649 The above will produce a SQL string resembling:

1650

1651 .. sourcecode:: sql

1652

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

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

1655

1656 """

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

1658

1659 @staticmethod

1660 def _flat_element_name(

1661 name: Optional[str], element: FromClause

1662 ) -> Optional[str]:

1663 if name and isinstance(element, NamedFromClause):

1664 if isinstance(element.name, _anonymous_label):

1665 # an anonymous name can't be embedded within a new name;

1666 # alias the element anonymously, #13583

1667 return None

1668 return f"{name}_{element.name}"

1669 else:

1670 return name

1671

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

1673 def _anonymous_fromclause(

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

1675 ) -> TODO_Any:

1676 sqlutil = util.preloaded.sql_util

1677 if flat:

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

1679 left_name = name # will recurse

1680 else:

1681 left_name = self._flat_element_name(name, self.left)

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

1683 right_name = name # will recurse

1684 else:

1685 right_name = self._flat_element_name(name, self.right)

1686 left_a, right_a = (

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

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

1689 )

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

1691 sqlutil.ClauseAdapter(right_a)

1692 )

1693

1694 return left_a.join(

1695 right_a,

1696 adapter.traverse(self.onclause),

1697 isouter=self.isouter,

1698 full=self.full,

1699 )

1700 else:

1701 return (

1702 self.select()

1703 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1704 .correlate(None)

1705 .alias(name)

1706 )

1707

1708 @util.ro_non_memoized_property

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

1710 return itertools.chain(

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

1712 )

1713

1714 @util.ro_non_memoized_property

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

1716 self_list: List[FromClause] = [self]

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

1718

1719 if TYPE_CHECKING:

1720

1721 def with_cols(self, type_: type[_TC_co]) -> Join[_TC_co]: ...

1722

1723

1724class NoInit:

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

1726 raise NotImplementedError(

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

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

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

1730 "selectable objects."

1731 % (

1732 self.__class__.__name__,

1733 self.__class__.__name__.lower(),

1734 self.__class__.__name__.lower(),

1735 )

1736 )

1737

1738

1739class LateralFromClause(NamedFromClause):

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

1741

1742

1743# FromClause ->

1744# AliasedReturnsRows

1745# -> Alias only for FromClause

1746# -> Subquery only for SelectBase

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

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

1749# w/ non-deprecated coercion

1750# -> TableSample -> only for FromClause

1751

1752

1753class AliasedReturnsRows(NoInit, NamedFromClause[_KeyColCC_co]):

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

1755 selectables."""

1756

1757 _is_from_container = True

1758

1759 _supports_derived_columns = False

1760

1761 element: ReturnsRows

1762

1763 _traverse_internals: _TraverseInternalsType = [

1764 ("element", InternalTraversal.dp_clauseelement),

1765 ("name", InternalTraversal.dp_anon_name),

1766 ]

1767

1768 @classmethod

1769 def _construct(

1770 cls,

1771 selectable: Any,

1772 *,

1773 name: Optional[str] = None,

1774 **kw: Any,

1775 ) -> Self:

1776 obj = cls.__new__(cls)

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

1778 return obj

1779

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

1781 self.element = coercions.expect(

1782 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1783 )

1784 self.element = selectable

1785 self._orig_name = name

1786 if name is None:

1787 if (

1788 isinstance(selectable, FromClause)

1789 and selectable.named_with_column

1790 ):

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

1792 if isinstance(name, _anonymous_label):

1793 name = None

1794 name = _anonymous_label.safe_construct(

1795 os.urandom(10).hex(), name or "anon"

1796 )

1797 self.name = name

1798

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

1800 super()._refresh_for_new_column(column)

1801 self.element._refresh_for_new_column(column)

1802

1803 def _populate_column_collection(

1804 self,

1805 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

1806 primary_key: ColumnSet,

1807 foreign_keys: Set[KeyedColumnElement[Any]],

1808 ) -> None:

1809 self.element._generate_fromclause_column_proxies(

1810 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1811 )

1812

1813 @util.ro_non_memoized_property

1814 def description(self) -> str:

1815 name = self.name

1816 if isinstance(name, _anonymous_label):

1817 return "anon_1"

1818

1819 return name

1820

1821 @util.ro_non_memoized_property

1822 def implicit_returning(self) -> bool:

1823 return self.element.implicit_returning # type: ignore[attr-defined, no-any-return] # noqa: E501

1824

1825 @property

1826 def original(self) -> ReturnsRows:

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

1828 return self.element

1829

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

1831 if fromclause in self._cloned_set:

1832 return True

1833 return self.element.is_derived_from(fromclause)

1834

1835 def _copy_internals(

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

1837 ) -> None:

1838 existing_element = self.element

1839

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

1841

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

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

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

1845 # performance.

1846 if existing_element is not self.element:

1847 self._reset_column_collection()

1848

1849 @property

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

1851 return [self]

1852

1853

1854class FromClauseAlias(AliasedReturnsRows[_KeyColCC_co]):

1855 element: FromClause[_KeyColCC_co]

1856

1857 @util.ro_non_memoized_property

1858 def description(self) -> str:

1859 name = self.name

1860 if isinstance(name, _anonymous_label):

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

1862

1863 return name

1864

1865

1866class Alias(roles.DMLTableRole, FromClauseAlias[_KeyColCC_co]):

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

1868

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

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

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

1872

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

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

1875 method available

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

1877

1878 .. seealso::

1879

1880 :meth:`_expression.FromClause.alias`

1881

1882 """

1883

1884 __visit_name__ = "alias"

1885

1886 inherit_cache = True

1887

1888 element: FromClause[_KeyColCC_co]

1889

1890 @classmethod

1891 def _factory(

1892 cls,

1893 selectable: FromClause[_KeyColCC_co],

1894 name: Optional[str] = None,

1895 flat: bool = False,

1896 ) -> NamedFromClause[_KeyColCC_co]:

1897 # mypy refuses to see the overload that has this returning

1898 # NamedFromClause[Any]. Pylance sees it just fine.

1899 return coercions.expect(roles.FromClauseRole, selectable).alias( # type: ignore[no-any-return] # noqa: E501

1900 name=name, flat=flat

1901 )

1902

1903

1904class TableValuedAlias(LateralFromClause, Alias):

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

1906

1907 This construct provides for a SQL function that returns columns

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

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

1910 method, e.g.:

1911

1912 .. sourcecode:: pycon+sql

1913

1914 >>> from sqlalchemy import select, func

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

1916 ... "value"

1917 ... )

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

1919 {printsql}SELECT anon_1.value

1920 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1921

1922 .. versionadded:: 1.4.0b2

1923

1924 .. seealso::

1925

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

1927

1928 """ # noqa: E501

1929

1930 __visit_name__ = "table_valued_alias"

1931

1932 _supports_derived_columns = True

1933 _render_derived = False

1934 _render_derived_w_types = False

1935 joins_implicitly = False

1936

1937 _traverse_internals: _TraverseInternalsType = [

1938 ("element", InternalTraversal.dp_clauseelement),

1939 ("name", InternalTraversal.dp_anon_name),

1940 ("_tableval_type", InternalTraversal.dp_type),

1941 ("_render_derived", InternalTraversal.dp_boolean),

1942 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1943 ]

1944

1945 def _init(

1946 self,

1947 selectable: Any,

1948 *,

1949 name: Optional[str] = None,

1950 table_value_type: Optional[TableValueType] = None,

1951 joins_implicitly: bool = False,

1952 ) -> None:

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

1954

1955 self.joins_implicitly = joins_implicitly

1956 self._tableval_type = (

1957 type_api.TABLEVALUE

1958 if table_value_type is None

1959 else table_value_type

1960 )

1961

1962 @HasMemoized.memoized_attribute

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

1964 """Return a column expression representing this

1965 :class:`_sql.TableValuedAlias`.

1966

1967 This accessor is used to implement the

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

1969 method for further details.

1970

1971 E.g.:

1972

1973 .. sourcecode:: pycon+sql

1974

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

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

1977

1978 .. seealso::

1979

1980 :meth:`_functions.FunctionElement.column_valued`

1981

1982 """

1983

1984 return TableValuedColumn(self, self._tableval_type)

1985

1986 def alias(

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

1988 ) -> TableValuedAlias:

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

1990

1991 This creates a distinct FROM object that will be distinguished

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

1993

1994 """

1995

1996 tva: TableValuedAlias = TableValuedAlias._construct(

1997 self,

1998 name=name,

1999 table_value_type=self._tableval_type,

2000 joins_implicitly=self.joins_implicitly,

2001 )

2002

2003 if self._render_derived:

2004 tva._render_derived = True

2005 tva._render_derived_w_types = self._render_derived_w_types

2006

2007 return tva

2008

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

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

2011 set, so that it renders as LATERAL.

2012

2013 .. seealso::

2014

2015 :func:`_expression.lateral`

2016

2017 """

2018 tva = self.alias(name=name)

2019 tva._is_lateral = True

2020 return tva

2021

2022 def render_derived(

2023 self,

2024 name: Optional[str] = None,

2025 with_types: bool = False,

2026 ) -> TableValuedAlias:

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

2028

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

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

2031

2032 .. sourcecode:: pycon+sql

2033

2034 >>> print(

2035 ... select(

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

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

2038 ... .render_derived()

2039 ... )

2040 ... )

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

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

2043

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

2045 the alias expression (this syntax currently applies to the

2046 PostgreSQL database):

2047

2048 .. sourcecode:: pycon+sql

2049

2050 >>> print(

2051 ... select(

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

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

2054 ... .render_derived(with_types=True)

2055 ... )

2056 ... )

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

2058 AS anon_1(a INTEGER, b VARCHAR)

2059

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

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

2062

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

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

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

2066

2067 """ # noqa: E501

2068

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

2070 # to the original object. otherwise you can have reuse of the

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

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

2073 # (just saw it happen on CI)

2074

2075 # construct against original to prevent memory growth

2076 # for repeated generations

2077 new_alias: TableValuedAlias = TableValuedAlias._construct(

2078 self.element,

2079 name=name,

2080 table_value_type=self._tableval_type,

2081 joins_implicitly=self.joins_implicitly,

2082 )

2083 new_alias._render_derived = True

2084 new_alias._render_derived_w_types = with_types

2085 return new_alias

2086

2087

2088class Lateral(FromClauseAlias, LateralFromClause):

2089 """Represent a LATERAL subquery.

2090

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

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

2093 method available

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

2095

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

2097 PostgreSQL versions provide support for this keyword.

2098

2099 .. seealso::

2100

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

2102

2103 """

2104

2105 __visit_name__ = "lateral"

2106 _is_lateral = True

2107

2108 inherit_cache = True

2109

2110 @classmethod

2111 def _factory(

2112 cls,

2113 selectable: Union[SelectBase, _FromClauseArgument],

2114 name: Optional[str] = None,

2115 ) -> LateralFromClause:

2116 return coercions.expect(

2117 roles.FromClauseRole, selectable, explicit_subquery=True

2118 ).lateral(name=name)

2119

2120

2121class TableSample(FromClauseAlias):

2122 """Represent a TABLESAMPLE clause.

2123

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

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

2126 method

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

2128

2129 .. seealso::

2130

2131 :func:`_expression.tablesample`

2132

2133 """

2134

2135 __visit_name__ = "tablesample"

2136

2137 _traverse_internals: _TraverseInternalsType = (

2138 AliasedReturnsRows._traverse_internals

2139 + [

2140 ("sampling", InternalTraversal.dp_clauseelement),

2141 ("seed", InternalTraversal.dp_clauseelement),

2142 ]

2143 )

2144

2145 @classmethod

2146 def _factory(

2147 cls,

2148 selectable: _FromClauseArgument,

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

2150 name: Optional[str] = None,

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

2152 ) -> TableSample:

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

2154 sampling, name=name, seed=seed

2155 )

2156

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

2158 def _init( # type: ignore[override]

2159 self,

2160 selectable: Any,

2161 *,

2162 name: Optional[str] = None,

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

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

2165 ) -> None:

2166 assert sampling is not None

2167 functions = util.preloaded.sql_functions

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

2169 sampling = functions.func.system(sampling)

2170

2171 self.sampling: Function[Any] = sampling

2172 self.seed = seed

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

2174

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

2176 return self.sampling

2177

2178

2179class CTE(

2180 roles.DMLTableRole,

2181 roles.IsCTERole,

2182 Generative,

2183 HasPrefixes,

2184 HasSuffixes,

2185 AliasedReturnsRows[_KeyColCC_co],

2186):

2187 """Represent a Common Table Expression.

2188

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

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

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

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

2193 :class:`_sql.Update` and

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

2195 usage details on CTEs.

2196

2197 .. seealso::

2198

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

2200

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

2202

2203 """

2204

2205 __visit_name__ = "cte"

2206

2207 _traverse_internals: _TraverseInternalsType = (

2208 AliasedReturnsRows._traverse_internals

2209 + [

2210 ("_cte_alias", InternalTraversal.dp_clauseelement),

2211 ("_restates", InternalTraversal.dp_clauseelement),

2212 ("recursive", InternalTraversal.dp_boolean),

2213 ("nesting", InternalTraversal.dp_boolean),

2214 ]

2215 + HasPrefixes._has_prefixes_traverse_internals

2216 + HasSuffixes._has_suffixes_traverse_internals

2217 )

2218

2219 element: HasCTE

2220

2221 @classmethod

2222 def _factory(

2223 cls,

2224 selectable: HasCTE,

2225 name: Optional[str] = None,

2226 recursive: bool = False,

2227 ) -> CTE:

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

2229 or Common Table Expression instance.

2230

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

2232

2233 """

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

2235 name=name, recursive=recursive

2236 )

2237

2238 def _init(

2239 self,

2240 selectable: HasCTE,

2241 *,

2242 name: Optional[str] = None,

2243 recursive: bool = False,

2244 nesting: bool = False,

2245 _cte_alias: Optional[CTE[_KeyColCC_co]] = None,

2246 _restates: Optional[CTE[_KeyColCC_co]] = None,

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

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

2249 ) -> None:

2250 self.recursive = recursive

2251 self.nesting = nesting

2252 self._cte_alias = _cte_alias

2253 # Keep recursivity reference with union/union_all

2254 self._restates = _restates

2255 if _prefixes:

2256 self._prefixes = _prefixes

2257 if _suffixes:

2258 self._suffixes = _suffixes

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

2260

2261 def _populate_column_collection(

2262 self,

2263 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

2264 primary_key: ColumnSet,

2265 foreign_keys: Set[KeyedColumnElement[Any]],

2266 ) -> None:

2267 if self._cte_alias is not None:

2268 self._cte_alias._generate_fromclause_column_proxies(

2269 self,

2270 columns,

2271 primary_key=primary_key,

2272 foreign_keys=foreign_keys,

2273 )

2274 else:

2275 self.element._generate_fromclause_column_proxies(

2276 self,

2277 columns,

2278 primary_key=primary_key,

2279 foreign_keys=foreign_keys,

2280 )

2281

2282 def alias(

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

2284 ) -> CTE[_KeyColCC_co]:

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

2286 :class:`_expression.CTE`.

2287

2288 This method is a CTE-specific specialization of the

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

2290

2291 .. seealso::

2292

2293 :ref:`tutorial_using_aliases`

2294

2295 :func:`_expression.alias`

2296

2297 """

2298 return CTE._construct(

2299 self.element,

2300 name=name,

2301 recursive=self.recursive,

2302 nesting=self.nesting,

2303 # an alias of an alias refers to the original CTE, #13583

2304 _cte_alias=(

2305 self._cte_alias if self._cte_alias is not None else self

2306 ),

2307 _prefixes=self._prefixes,

2308 _suffixes=self._suffixes,

2309 )

2310

2311 def union(

2312 self, *other: _SelectStatementForCompoundArgument[Any]

2313 ) -> CTE[_KeyColCC_co]:

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

2315 of the original CTE against the given selectables provided

2316 as positional arguments.

2317

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

2319 UNION.

2320

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

2322

2323 .. seealso::

2324

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

2326

2327 """

2328 assert is_select_statement(

2329 self.element

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

2331

2332 return CTE._construct(

2333 self.element.union(*other),

2334 name=self.name,

2335 recursive=self.recursive,

2336 nesting=self.nesting,

2337 _restates=self,

2338 _prefixes=self._prefixes,

2339 _suffixes=self._suffixes,

2340 )

2341

2342 def union_all(

2343 self, *other: _SelectStatementForCompoundArgument[Any]

2344 ) -> CTE[_KeyColCC_co]:

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

2346 of the original CTE against the given selectables provided

2347 as positional arguments.

2348

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

2350 UNION.

2351

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

2353

2354 .. seealso::

2355

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

2357

2358 """

2359

2360 assert is_select_statement(

2361 self.element

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

2363

2364 return CTE._construct(

2365 self.element.union_all(*other),

2366 name=self.name,

2367 recursive=self.recursive,

2368 nesting=self.nesting,

2369 _restates=self,

2370 _prefixes=self._prefixes,

2371 _suffixes=self._suffixes,

2372 )

2373

2374 def _get_reference_cte(self) -> CTE[_KeyColCC_co]:

2375 """

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

2377 Updated CTEs should still refer to the original CTE.

2378 This function returns this reference identifier.

2379 """

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

2381

2382 if TYPE_CHECKING:

2383

2384 def with_cols(self, type_: type[_TC_co]) -> CTE[_TC_co]: ...

2385

2386

2387class _CTEOpts(NamedTuple):

2388 nesting: bool

2389

2390

2391class _ColumnsPlusNames(NamedTuple):

2392 required_label_name: Optional[str]

2393 """

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

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

2396 """

2397

2398 proxy_key: Optional[str]

2399 """

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

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

2402 select.selected_columns collection

2403 """

2404

2405 fallback_label_name: Optional[str]

2406 """

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

2408 we have to render a label even though

2409 required_label_name was not given

2410 """

2411

2412 column: Union[ColumnElement[Any], AbstractTextClause]

2413 """

2414 the ColumnElement itself

2415 """

2416

2417 repeated: bool

2418 """

2419 True if this is a duplicate of a previous column

2420 in the list of columns

2421 """

2422

2423

2424class SelectsRows(ReturnsRows):

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

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

2427

2428 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2429

2430 def _generate_columns_plus_names(

2431 self,

2432 anon_for_dupe_key: bool,

2433 cols: Optional[_SelectIterable] = None,

2434 ) -> List[_ColumnsPlusNames]:

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

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

2437 on a :class:`.FromClause`.

2438

2439 This is distinct from the _column_naming_convention generator that's

2440 intended for population of the Select.selected_columns collection,

2441 different rules. the collection returned here calls upon the

2442 _column_naming_convention as well.

2443

2444 """

2445

2446 if cols is None:

2447 cols = self._all_selected_columns

2448

2449 key_naming_convention = SelectState._column_naming_convention(

2450 self._label_style

2451 )

2452

2453 names = {}

2454

2455 result: List[_ColumnsPlusNames] = []

2456 result_append = result.append

2457

2458 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2459 label_style_none = self._label_style is LABEL_STYLE_NONE

2460

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

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

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

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

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

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

2467 dedupe_hash = 1

2468

2469 for c in cols:

2470 repeated = False

2471

2472 if not c._render_label_in_columns_clause:

2473 effective_name = required_label_name = fallback_label_name = (

2474 None

2475 )

2476 elif label_style_none:

2477 if TYPE_CHECKING:

2478 assert is_column_element(c)

2479

2480 effective_name = required_label_name = None

2481 fallback_label_name = c._non_anon_label or c._anon_name_label

2482 else:

2483 if TYPE_CHECKING:

2484 assert is_column_element(c)

2485

2486 if table_qualified:

2487 required_label_name = effective_name = (

2488 fallback_label_name

2489 ) = c._tq_label

2490 else:

2491 effective_name = fallback_label_name = c._non_anon_label

2492 required_label_name = None

2493

2494 if effective_name is None:

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

2496 # not need _expression_label but it isn't

2497 # giving us a clue when to use anon_label instead

2498 expr_label = c._expression_label

2499 if expr_label is None:

2500 repeated = c._anon_name_label in names

2501 names[c._anon_name_label] = c

2502 effective_name = required_label_name = None

2503

2504 if repeated:

2505 # here, "required_label_name" is sent as

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

2507 if table_qualified:

2508 fallback_label_name = (

2509 c._dedupe_anon_tq_label_idx(dedupe_hash)

2510 )

2511 dedupe_hash += 1

2512 else:

2513 fallback_label_name = c._dedupe_anon_label_idx(

2514 dedupe_hash

2515 )

2516 dedupe_hash += 1

2517 else:

2518 fallback_label_name = c._anon_name_label

2519 else:

2520 required_label_name = effective_name = (

2521 fallback_label_name

2522 ) = expr_label

2523

2524 if effective_name is not None:

2525 if TYPE_CHECKING:

2526 assert is_column_element(c)

2527

2528 if effective_name in names:

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

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

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

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

2533 # different column under the same name. apply

2534 # disambiguating label

2535 if table_qualified:

2536 required_label_name = fallback_label_name = (

2537 c._anon_tq_label

2538 )

2539 else:

2540 required_label_name = fallback_label_name = (

2541 c._anon_name_label

2542 )

2543

2544 if anon_for_dupe_key and required_label_name in names:

2545 # here, c._anon_tq_label is definitely unique to

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

2547 # this should always be true.

2548 # this is also an infrequent codepath because

2549 # you need two levels of duplication to be here

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

2551

2552 # the column under the disambiguating label is

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

2554 # subsequent occurrences of the column so that the

2555 # original stays non-ambiguous

2556 if table_qualified:

2557 required_label_name = fallback_label_name = (

2558 c._dedupe_anon_tq_label_idx(dedupe_hash)

2559 )

2560 dedupe_hash += 1

2561 else:

2562 required_label_name = fallback_label_name = (

2563 c._dedupe_anon_label_idx(dedupe_hash)

2564 )

2565 dedupe_hash += 1

2566 repeated = True

2567 else:

2568 names[required_label_name] = c

2569 elif anon_for_dupe_key:

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

2571 # label so that the original stays non-ambiguous

2572 if table_qualified:

2573 required_label_name = fallback_label_name = (

2574 c._dedupe_anon_tq_label_idx(dedupe_hash)

2575 )

2576 dedupe_hash += 1

2577 else:

2578 required_label_name = fallback_label_name = (

2579 c._dedupe_anon_label_idx(dedupe_hash)

2580 )

2581 dedupe_hash += 1

2582 repeated = True

2583 else:

2584 names[effective_name] = c

2585

2586 result_append(

2587 _ColumnsPlusNames(

2588 required_label_name,

2589 key_naming_convention(c),

2590 fallback_label_name,

2591 c,

2592 repeated,

2593 )

2594 )

2595

2596 return result

2597

2598

2599class HasCTE(roles.HasCTERole, SelectsRows):

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

2601

2602 _has_ctes_traverse_internals: _TraverseInternalsType = [

2603 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2604 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2605 ]

2606

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

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

2609

2610 name_cte_columns: bool = False

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

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

2613

2614 .. versionadded:: 2.0.42

2615

2616 """

2617

2618 @_generative

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

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

2621

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

2623 the parent statement such that they will each be unconditionally

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

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

2626

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

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

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

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

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

2632 statement.

2633

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

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

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

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

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

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

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

2641 larger statement.

2642

2643 E.g.::

2644

2645 from sqlalchemy import table, column, select

2646

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

2648

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

2650

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

2652

2653 Would render:

2654

2655 .. sourcecode:: sql

2656

2657 WITH anon_1 AS (

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

2659 )

2660 SELECT t.c1, t.c2

2661 FROM t

2662

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

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

2665 statement.

2666

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

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

2669

2670 from sqlalchemy import table, column

2671 from sqlalchemy.dialects.postgresql import insert

2672

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

2674

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

2676

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

2678 update_statement = insert_stmt.on_conflict_do_update(

2679 index_elements=[t.c.c1],

2680 set_={

2681 "c1": insert_stmt.excluded.c1,

2682 "c2": insert_stmt.excluded.c2,

2683 },

2684 ).add_cte(delete_statement_cte)

2685

2686 print(update_statement)

2687

2688 The above statement renders as:

2689

2690 .. sourcecode:: sql

2691

2692 WITH deletions AS (

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

2694 )

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

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

2697

2698 .. versionadded:: 1.4.21

2699

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

2701

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

2703

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

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

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

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

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

2709 this statement when this flag is given.

2710

2711 .. versionadded:: 2.0

2712

2713 .. seealso::

2714

2715 :paramref:`.HasCTE.cte.nesting`

2716

2717

2718 """ # noqa: E501

2719 opt = _CTEOpts(nest_here)

2720 for cte in ctes:

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

2722 self._independent_ctes += (cte,)

2723 self._independent_ctes_opts += (opt,)

2724 return self

2725

2726 def cte(

2727 self,

2728 name: Optional[str] = None,

2729 recursive: bool = False,

2730 nesting: bool = False,

2731 ) -> CTE:

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

2733 or Common Table Expression instance.

2734

2735 Common table expressions are a SQL standard whereby SELECT

2736 statements can draw upon secondary statements specified along

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

2738 Special semantics regarding UNION can also be employed to

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

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

2741

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

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

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

2745 CTE rows.

2746

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

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

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

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

2751

2752 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2754 method may be

2755 used to establish these.

2756

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

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

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

2760 compile time.

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

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

2763 conjunction with UNION ALL in order to derive rows

2764 from those already selected.

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

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

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

2768 :paramref:`.HasCTE.add_cte.nest_here`

2769 parameter may also be used to more carefully

2770 control the exact placement of a particular CTE.

2771

2772 .. versionadded:: 1.4.24

2773

2774 .. seealso::

2775

2776 :meth:`.HasCTE.add_cte`

2777

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

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

2780 as well as additional examples.

2781

2782 Example 1, non recursive::

2783

2784 from sqlalchemy import (

2785 Table,

2786 Column,

2787 String,

2788 Integer,

2789 MetaData,

2790 select,

2791 func,

2792 )

2793

2794 metadata = MetaData()

2795

2796 orders = Table(

2797 "orders",

2798 metadata,

2799 Column("region", String),

2800 Column("amount", Integer),

2801 Column("product", String),

2802 Column("quantity", Integer),

2803 )

2804

2805 regional_sales = (

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

2807 .group_by(orders.c.region)

2808 .cte("regional_sales")

2809 )

2810

2811

2812 top_regions = (

2813 select(regional_sales.c.region)

2814 .where(

2815 regional_sales.c.total_sales

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

2817 )

2818 .cte("top_regions")

2819 )

2820

2821 statement = (

2822 select(

2823 orders.c.region,

2824 orders.c.product,

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

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

2827 )

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

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

2830 )

2831

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

2833

2834 Example 2, WITH RECURSIVE::

2835

2836 from sqlalchemy import (

2837 Table,

2838 Column,

2839 String,

2840 Integer,

2841 MetaData,

2842 select,

2843 func,

2844 )

2845

2846 metadata = MetaData()

2847

2848 parts = Table(

2849 "parts",

2850 metadata,

2851 Column("part", String),

2852 Column("sub_part", String),

2853 Column("quantity", Integer),

2854 )

2855

2856 included_parts = (

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

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

2859 .cte(recursive=True)

2860 )

2861

2862

2863 incl_alias = included_parts.alias()

2864 parts_alias = parts.alias()

2865 included_parts = included_parts.union_all(

2866 select(

2867 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2869 )

2870

2871 statement = select(

2872 included_parts.c.sub_part,

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

2874 ).group_by(included_parts.c.sub_part)

2875

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

2877

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

2879

2880 from datetime import date

2881 from sqlalchemy import (

2882 MetaData,

2883 Table,

2884 Column,

2885 Integer,

2886 Date,

2887 select,

2888 literal,

2889 and_,

2890 exists,

2891 )

2892

2893 metadata = MetaData()

2894

2895 visitors = Table(

2896 "visitors",

2897 metadata,

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

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

2900 Column("count", Integer),

2901 )

2902

2903 # add 5 visitors for the product_id == 1

2904 product_id = 1

2905 day = date.today()

2906 count = 5

2907

2908 update_cte = (

2909 visitors.update()

2910 .where(

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

2912 )

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

2914 .returning(literal(1))

2915 .cte("update_cte")

2916 )

2917

2918 upsert = visitors.insert().from_select(

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

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

2921 ~exists(update_cte.select())

2922 ),

2923 )

2924

2925 connection.execute(upsert)

2926

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

2928

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

2930

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

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

2933 "value_a", nesting=True

2934 )

2935

2936 # Nesting CTEs takes ascendency locally

2937 # over the CTEs at a higher level

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

2939

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

2941

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

2943 shown with inline parameters below as:

2944

2945 .. sourcecode:: sql

2946

2947 WITH

2948 value_a AS

2949 (SELECT 'root' AS n),

2950 value_b AS

2951 (WITH value_a AS

2952 (SELECT 'nesting' AS n)

2953 SELECT value_a.n AS n FROM value_a)

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

2955 FROM value_a, value_b

2956

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

2958 as follows (SQLAlchemy 2.0 and above)::

2959

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

2961

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

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

2964

2965 # Nesting CTEs takes ascendency locally

2966 # over the CTEs at a higher level

2967 value_b = (

2968 select(value_a_nested.c.n)

2969 .add_cte(value_a_nested, nest_here=True)

2970 .cte("value_b")

2971 )

2972

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

2974

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

2976

2977 edge = Table(

2978 "edge",

2979 metadata,

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

2981 Column("left", Integer),

2982 Column("right", Integer),

2983 )

2984

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

2986

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

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

2989 )

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

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

2992 )

2993

2994 subgraph_cte = root_node.union(left_edge, right_edge)

2995

2996 subgraph = select(subgraph_cte)

2997

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

2999

3000 .. sourcecode:: sql

3001

3002 WITH RECURSIVE nodes(node) AS (

3003 SELECT 1 AS node

3004 UNION

3005 SELECT edge."left" AS "left"

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

3007 UNION

3008 SELECT edge."right" AS "right"

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

3010 )

3011 SELECT nodes.node FROM nodes

3012

3013 .. seealso::

3014

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

3016 :meth:`_expression.HasCTE.cte`.

3017

3018 """ # noqa: E501

3019 return CTE._construct(

3020 self, name=name, recursive=recursive, nesting=nesting

3021 )

3022

3023

3024class Subquery(AliasedReturnsRows[_KeyColCC_co]):

3025 """Represent a subquery of a SELECT.

3026

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

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

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

3030 :class:`_expression.SelectBase` subclass

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

3032 :class:`_expression.CompoundSelect`, and

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

3034 it represents the

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

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

3037

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

3039 :class:`_expression.Alias`

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

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

3042 :class:`_expression.Alias` always

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

3044 :class:`.Subquery`

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

3046

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

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

3049 statement.

3050

3051 """

3052

3053 __visit_name__ = "subquery"

3054

3055 _is_subquery = True

3056

3057 inherit_cache = True

3058

3059 element: SelectBase

3060

3061 @classmethod

3062 def _factory(

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

3064 ) -> Subquery:

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

3066

3067 return coercions.expect(

3068 roles.SelectStatementRole, selectable

3069 ).subquery(name=name)

3070

3071 @util.deprecated(

3072 "1.4",

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

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

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

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

3077 ":func:`_expression.select` "

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

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

3080 )

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

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

3083

3084

3085class FromGrouping(GroupedElement, FromClause[_KeyColCC_co]):

3086 """Represent a grouping of a FROM clause"""

3087

3088 _traverse_internals: _TraverseInternalsType = [

3089 ("element", InternalTraversal.dp_clauseelement)

3090 ]

3091

3092 element: FromClause[_KeyColCC_co]

3093

3094 def __init__(self, element: FromClause[_KeyColCC_co]):

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

3096

3097 @util.ro_non_memoized_property

3098 def columns(self) -> _KeyColCC_co:

3099 return self.element.columns

3100

3101 @util.ro_non_memoized_property

3102 def c(self) -> _KeyColCC_co:

3103 return self.element.columns

3104

3105 @property

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

3107 return self.element.primary_key

3108

3109 @property

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

3111 return self.element.foreign_keys

3112

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

3114 return self.element.is_derived_from(fromclause)

3115

3116 def alias(

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

3118 ) -> NamedFromGrouping[_KeyColCC_co]:

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

3120

3121 def _anonymous_fromclause(

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

3123 ) -> FromGrouping:

3124 return FromGrouping(

3125 self.element._anonymous_fromclause(name=name, flat=flat)

3126 )

3127

3128 @util.ro_non_memoized_property

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

3130 return self.element._hide_froms

3131

3132 @util.ro_non_memoized_property

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

3134 return self.element._from_objects

3135

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

3137 return {"element": self.element}

3138

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

3140 self.element = state["element"]

3141

3142 if TYPE_CHECKING:

3143

3144 def self_group(

3145 self, against: Optional[OperatorType] = None

3146 ) -> Self: ...

3147

3148

3149class NamedFromGrouping(

3150 FromGrouping[_KeyColCC_co], NamedFromClause[_KeyColCC_co]

3151):

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

3153

3154 .. versionadded:: 2.0

3155

3156 """

3157

3158 inherit_cache = True

3159

3160 if TYPE_CHECKING:

3161

3162 def self_group(

3163 self, against: Optional[OperatorType] = None

3164 ) -> Self: ...

3165

3166

3167class TableClause(

3168 roles.DMLTableRole, Immutable, NamedFromClause[_ColClauseCC_co]

3169):

3170 """Represents a minimal "table" construct.

3171

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

3173 collection of columns, which are typically produced

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

3175

3176 from sqlalchemy import table, column

3177

3178 user = table(

3179 "user",

3180 column("id"),

3181 column("name"),

3182 column("description"),

3183 )

3184

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

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

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

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

3189

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

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

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

3193 It's useful

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

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

3196 is not on hand.

3197

3198 """

3199

3200 __visit_name__ = "table"

3201

3202 _traverse_internals: _TraverseInternalsType = [

3203 (

3204 "columns",

3205 InternalTraversal.dp_fromclause_canonical_column_collection,

3206 ),

3207 ("name", InternalTraversal.dp_string),

3208 ("schema", InternalTraversal.dp_string),

3209 ]

3210

3211 _is_table = True

3212

3213 fullname: str

3214

3215 implicit_returning = False

3216 """:class:`_expression.TableClause`

3217 doesn't support having a primary key or column

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

3219

3220 _columns: DedupeColumnCollection[ColumnClause[Any]]

3221

3222 @util.ro_memoized_property

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

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

3225 return None

3226

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

3228 super().__init__()

3229 self.name = name

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

3231 self.primary_key = ColumnSet() # type: ignore[misc]

3232 self.foreign_keys = set() # type: ignore[misc]

3233 for c in columns:

3234 self.append_column(c)

3235

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

3237 if schema is not None:

3238 self.schema = schema

3239 if self.schema is not None:

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

3241 else:

3242 self.fullname = self.name

3243 if kw:

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

3245

3246 if TYPE_CHECKING:

3247

3248 def with_cols(self, type_: type[_TC_co]) -> TableClause[_TC_co]: ...

3249

3250 def __str__(self) -> str:

3251 if self.schema is not None:

3252 return self.schema + "." + self.name

3253 else:

3254 return self.name

3255

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

3257 pass

3258

3259 @util.ro_memoized_property

3260 def description(self) -> str:

3261 return self.name

3262

3263 def _insert_col_impl(

3264 self,

3265 c: ColumnClause[Any],

3266 *,

3267 index: Optional[int] = None,

3268 ) -> None:

3269 existing = c.table

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

3271 raise exc.ArgumentError(

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

3273 % (c.key, existing)

3274 )

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

3276 c.table = self

3277

3278 def append_column(self, column: ColumnClause[Any]) -> None:

3279 """Append a :class:`.ColumnClause` to this :class:`.TableClause`."""

3280 self._insert_col_impl(column)

3281

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

3283 """Insert a :class:`.ColumnClause` to this :class:`.TableClause` at

3284 a specific position.

3285

3286 .. versionadded:: 2.1

3287

3288 """

3289 self._insert_col_impl(column, index=index)

3290

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

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

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

3294 :class:`_expression.TableClause`.

3295

3296 E.g.::

3297

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

3299

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

3301

3302 """

3303

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

3305

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

3307 def update(self) -> Update:

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

3309 :class:`_expression.TableClause`.

3310

3311 E.g.::

3312

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

3314

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

3316

3317 """

3318 return util.preloaded.sql_dml.Update(

3319 self,

3320 )

3321

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

3323 def delete(self) -> Delete:

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

3325 :class:`_expression.TableClause`.

3326

3327 E.g.::

3328

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

3330

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

3332

3333 """

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

3335

3336 @util.ro_non_memoized_property

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

3338 return [self]

3339

3340

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

3342

3343

3344class ForUpdateArg(ClauseElement):

3345 _traverse_internals: _TraverseInternalsType = [

3346 ("of", InternalTraversal.dp_clauseelement_list),

3347 ("nowait", InternalTraversal.dp_boolean),

3348 ("read", InternalTraversal.dp_boolean),

3349 ("skip_locked", InternalTraversal.dp_boolean),

3350 ("key_share", InternalTraversal.dp_boolean),

3351 ]

3352

3353 of: Optional[Sequence[ClauseElement]]

3354 nowait: bool

3355 read: bool

3356 skip_locked: bool

3357

3358 @classmethod

3359 def _from_argument(

3360 cls, with_for_update: ForUpdateParameter

3361 ) -> Optional[ForUpdateArg]:

3362 if isinstance(with_for_update, ForUpdateArg):

3363 return with_for_update

3364 elif with_for_update in (None, False):

3365 return None

3366 elif with_for_update is True:

3367 return ForUpdateArg()

3368 else:

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

3370

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

3372 return (

3373 isinstance(other, ForUpdateArg)

3374 and other.nowait == self.nowait

3375 and other.read == self.read

3376 and other.skip_locked == self.skip_locked

3377 and other.key_share == self.key_share

3378 and other.of is self.of

3379 )

3380

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

3382 return not self.__eq__(other)

3383

3384 def __hash__(self) -> int:

3385 return id(self)

3386

3387 def __init__(

3388 self,

3389 *,

3390 nowait: bool = False,

3391 read: bool = False,

3392 of: Optional[_ForUpdateOfArgument] = None,

3393 skip_locked: bool = False,

3394 key_share: bool = False,

3395 ):

3396 """Represents arguments specified to

3397 :meth:`_expression.Select.for_update`.

3398

3399 """

3400

3401 self.nowait = nowait

3402 self.read = read

3403 self.skip_locked = skip_locked

3404 self.key_share = key_share

3405 if of is not None:

3406 self.of = [

3407 coercions.expect(roles.ColumnsClauseRole, elem)

3408 for elem in util.to_list(of)

3409 ]

3410 else:

3411 self.of = None

3412

3413

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

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

3416 in a statement.

3417

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

3419 :func:`_expression.values` function.

3420

3421 .. versionadded:: 1.4

3422

3423 """

3424

3425 __visit_name__ = "values"

3426

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

3428 _column_args: Tuple[NamedColumn[Any], ...]

3429

3430 _unnamed: bool

3431 _traverse_internals: _TraverseInternalsType = [

3432 ("_column_args", InternalTraversal.dp_clauseelement_list),

3433 ("_data", InternalTraversal.dp_dml_multi_values),

3434 ("name", InternalTraversal.dp_string),

3435 ("literal_binds", InternalTraversal.dp_boolean),

3436 ] + HasCTE._has_ctes_traverse_internals

3437

3438 name_cte_columns = True

3439

3440 def __init__(

3441 self,

3442 *columns: _OnlyColumnArgument[Any],

3443 name: Optional[str] = None,

3444 literal_binds: bool = False,

3445 ):

3446 super().__init__()

3447 self._column_args = tuple(

3448 coercions.expect(roles.LabeledColumnExprRole, col)

3449 for col in columns

3450 )

3451

3452 if name is None:

3453 self._unnamed = True

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

3455 else:

3456 self._unnamed = False

3457 self.name = name

3458 self.literal_binds = literal_binds

3459 self.named_with_column = not self._unnamed

3460

3461 @property

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

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

3464

3465 @util.ro_non_memoized_property

3466 def _all_selected_columns(self) -> _SelectIterable:

3467 return self._column_args

3468

3469 @_generative

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

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

3472 construct that is a copy of this

3473 one with the given name.

3474

3475 This method is a VALUES-specific specialization of the

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

3477

3478 .. seealso::

3479

3480 :ref:`tutorial_using_aliases`

3481

3482 :func:`_expression.alias`

3483

3484 """

3485 non_none_name: str

3486

3487 if name is None:

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

3489 else:

3490 non_none_name = name

3491

3492 self.name = non_none_name

3493 self.named_with_column = True

3494 self._unnamed = False

3495 return self

3496

3497 @_generative

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

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

3500 so that

3501 it renders as LATERAL.

3502

3503 .. seealso::

3504

3505 :func:`_expression.lateral`

3506

3507 """

3508 non_none_name: str

3509

3510 if name is None:

3511 non_none_name = self.name

3512 else:

3513 non_none_name = name

3514

3515 self._is_lateral = True

3516 self.name = non_none_name

3517 self._unnamed = False

3518 return self

3519

3520 @_generative

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

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

3523 adding the given data to the data list.

3524

3525 E.g.::

3526

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

3528

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

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

3531 constructor.

3532

3533 """

3534

3535 self._data += (values,)

3536 return self

3537

3538 def scalar_values(self) -> ScalarValues:

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

3540 COLUMN element in a statement.

3541

3542 .. versionadded:: 2.0.0b4

3543

3544 """

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

3546

3547 def _populate_column_collection(

3548 self,

3549 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

3550 primary_key: ColumnSet,

3551 foreign_keys: Set[KeyedColumnElement[Any]],

3552 ) -> None:

3553 for c in self._column_args:

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

3555 _, c = c._make_proxy(

3556 self, primary_key=primary_key, foreign_keys=foreign_keys

3557 )

3558 else:

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

3560 # no memoizations of other FROM clauses.

3561 # see test_values.py -> test_auto_proxy_select_direct_col

3562 c._reset_memoizations()

3563 columns.add(c)

3564 c.table = self

3565

3566 @util.ro_non_memoized_property

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

3568 return [self]

3569

3570

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

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

3573 COLUMN element in a statement.

3574

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

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

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

3578 an ``IN`` or ``NOT IN`` condition.

3579

3580 .. versionadded:: 2.0.0b4

3581

3582 """

3583

3584 __visit_name__ = "scalar_values"

3585

3586 _traverse_internals: _TraverseInternalsType = [

3587 ("_column_args", InternalTraversal.dp_clauseelement_list),

3588 ("_data", InternalTraversal.dp_dml_multi_values),

3589 ("literal_binds", InternalTraversal.dp_boolean),

3590 ]

3591

3592 def __init__(

3593 self,

3594 columns: Sequence[NamedColumn[Any]],

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

3596 literal_binds: bool,

3597 ):

3598 super().__init__()

3599 self._column_args = columns

3600 self._data = data

3601 self.literal_binds = literal_binds

3602

3603 @property

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

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

3606

3607 def __clause_element__(self) -> ScalarValues:

3608 return self

3609

3610 if TYPE_CHECKING:

3611

3612 def self_group(

3613 self, against: Optional[OperatorType] = None

3614 ) -> Self: ...

3615

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

3617

3618

3619class SelectBase(

3620 roles.SelectStatementRole,

3621 roles.DMLSelectRole,

3622 roles.CompoundElementRole,

3623 roles.InElementRole,

3624 HasCTE,

3625 SupportsCloneAnnotations,

3626 Selectable,

3627):

3628 """Base class for SELECT statements.

3629

3630

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

3632 :class:`_expression.CompoundSelect` and

3633 :class:`_expression.TextualSelect`.

3634

3635

3636 """

3637

3638 _is_select_base = True

3639 is_select = True

3640

3641 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3642

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

3644 self._reset_memoizations()

3645

3646 @util.ro_non_memoized_property

3647 def selected_columns(

3648 self,

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

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

3651 representing the columns that

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

3653

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

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

3656 within this collection cannot be directly nested inside another SELECT

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

3658 necessary parenthesization required by SQL.

3659

3660 .. note::

3661

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

3663 include expressions established in the columns clause using the

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

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

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

3667 construct.

3668

3669 .. seealso::

3670

3671 :attr:`_sql.Select.selected_columns`

3672

3673 .. versionadded:: 1.4

3674

3675 """

3676 raise NotImplementedError()

3677

3678 def _generate_fromclause_column_proxies(

3679 self,

3680 subquery: FromClause,

3681 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

3682 primary_key: ColumnSet,

3683 foreign_keys: Set[KeyedColumnElement[Any]],

3684 *,

3685 proxy_compound_columns: Optional[

3686 Iterable[Sequence[ColumnElement[Any]]]

3687 ] = None,

3688 ) -> None:

3689 raise NotImplementedError()

3690

3691 @util.ro_non_memoized_property

3692 def _all_selected_columns(self) -> _SelectIterable:

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

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

3695 constructs.

3696

3697 .. versionadded:: 1.4.12

3698

3699 .. seealso::

3700

3701 :attr:`_sql.SelectBase.exported_columns`

3702

3703 """

3704 raise NotImplementedError()

3705

3706 @property

3707 def exported_columns(

3708 self,

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

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

3711 that represents the "exported"

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

3713 :class:`_sql.TextClause` constructs.

3714

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

3716 object are synonymous

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

3718

3719 .. versionadded:: 1.4

3720

3721 .. seealso::

3722

3723 :attr:`_expression.Select.exported_columns`

3724

3725 :attr:`_expression.Selectable.exported_columns`

3726

3727 :attr:`_expression.FromClause.exported_columns`

3728

3729

3730 """

3731 return self.selected_columns._as_readonly()

3732

3733 def get_label_style(self) -> SelectLabelStyle:

3734 """

3735 Retrieve the current label style.

3736

3737 Implemented by subclasses.

3738

3739 """

3740 raise NotImplementedError()

3741

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

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

3744

3745 Implemented by subclasses.

3746

3747 """

3748

3749 raise NotImplementedError()

3750

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

3752 raise NotImplementedError()

3753

3754 @util.deprecated(

3755 "1.4",

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

3757 "method is deprecated and will be "

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

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

3760 )

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

3762 return self.scalar_subquery()

3763

3764 def exists(self) -> Exists:

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

3766 which can be used as a column expression.

3767

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

3769

3770 .. seealso::

3771

3772 :func:`_sql.exists`

3773

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

3775

3776 .. versionadded:: 1.4

3777

3778 """

3779 return Exists(self)

3780

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

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

3783 used as a column expression.

3784

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

3786

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

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

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

3790 an enclosing SELECT.

3791

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

3793 subquery that can be produced using the

3794 :meth:`_expression.SelectBase.subquery`

3795 method.

3796

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

3798 :meth:`_expression.SelectBase.scalar_subquery`.

3799

3800 .. seealso::

3801

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

3803

3804 """

3805 if self._label_style is not LABEL_STYLE_NONE:

3806 self = self.set_label_style(LABEL_STYLE_NONE)

3807

3808 return ScalarSelect(self)

3809

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

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

3812 subquery with a label.

3813

3814 .. seealso::

3815

3816 :meth:`_expression.SelectBase.scalar_subquery`.

3817

3818 """

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

3820

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

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

3823

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

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

3826

3827 .. seealso::

3828

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

3830

3831 """

3832 return Lateral._factory(self, name)

3833

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

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

3836

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

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

3839 SELECT statement.

3840

3841 Given a SELECT statement such as::

3842

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

3844

3845 The above statement might look like:

3846

3847 .. sourcecode:: sql

3848

3849 SELECT table.id, table.name FROM table

3850

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

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

3853 a named sub-element::

3854

3855 subq = stmt.subquery()

3856 new_stmt = select(subq)

3857

3858 The above renders as:

3859

3860 .. sourcecode:: sql

3861

3862 SELECT anon_1.id, anon_1.name

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

3864

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

3866 is equivalent to calling

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

3868 method on a FROM object; however,

3869 as a :class:`_expression.SelectBase`

3870 object is not directly FROM object,

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

3872 method provides clearer semantics.

3873

3874 .. versionadded:: 1.4

3875

3876 """

3877

3878 return Subquery._construct(

3879 self._ensure_disambiguated_names(), name=name

3880 )

3881

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

3883 def into(

3884 self,

3885 target: str,

3886 *,

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

3888 schema: Optional[str] = None,

3889 temporary: bool = False,

3890 if_not_exists: bool = False,

3891 ) -> CreateTableAs:

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

3893

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

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

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

3897

3898 Supported on all included backends, the construct emits

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

3900 emits a ``SELECT..INTO`` statement.

3901

3902 e.g.::

3903

3904 from sqlalchemy import select

3905

3906 # Create a new table from a SELECT

3907 stmt = (

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

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

3910 .into("active_users")

3911 )

3912

3913 with engine.begin() as conn:

3914 conn.execute(stmt)

3915

3916 # With optional flags

3917 stmt = (

3918 select(users.c.id)

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

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

3921 )

3922

3923 .. versionadded:: 2.1

3924

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

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

3927

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

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

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

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

3932 is created.

3933

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

3935

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

3937

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

3939

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

3941

3942 .. seealso::

3943

3944 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3945

3946 :class:`_schema.CreateTableAs`

3947

3948 """

3949 sql_ddl = util.preloaded.sql_ddl

3950

3951 return sql_ddl.CreateTableAs(

3952 self,

3953 target,

3954 metadata=metadata,

3955 schema=schema,

3956 temporary=temporary,

3957 if_not_exists=if_not_exists,

3958 )

3959

3960 def _ensure_disambiguated_names(self) -> Self:

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

3962 disambiguated in some way, if possible.

3963

3964 """

3965

3966 raise NotImplementedError()

3967

3968 def alias(

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

3970 ) -> Subquery:

3971 """Return a named subquery against this

3972 :class:`_expression.SelectBase`.

3973

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

3975 :class:`_expression.FromClause`),

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

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

3978 :class:`_expression.FromClause`.

3979

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

3981 method is now

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

3983

3984 """

3985 return self.subquery(name=name)

3986

3987

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

3989

3990

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

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

3993

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

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

3996 compound selects.

3997

3998 """

3999

4000 __visit_name__ = "select_statement_grouping"

4001 _traverse_internals: _TraverseInternalsType = [

4002 ("element", InternalTraversal.dp_clauseelement)

4003 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

4004

4005 _is_select_container = True

4006

4007 element: _SB

4008

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

4010 self.element = cast(

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

4012 )

4013

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

4015 new_element = self.element._ensure_disambiguated_names()

4016 if new_element is not self.element:

4017 return SelectStatementGrouping(new_element)

4018 else:

4019 return self

4020

4021 def get_label_style(self) -> SelectLabelStyle:

4022 return self.element.get_label_style()

4023

4024 def set_label_style(

4025 self, label_style: SelectLabelStyle

4026 ) -> SelectStatementGrouping[_SB]:

4027 return SelectStatementGrouping(

4028 self.element.set_label_style(label_style)

4029 )

4030

4031 @property

4032 def select_statement(self) -> _SB:

4033 return self.element

4034

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

4036 return self

4037

4038 if TYPE_CHECKING:

4039

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

4041

4042 # def _generate_columns_plus_names(

4043 # self, anon_for_dupe_key: bool

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

4045 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

4046

4047 def _generate_fromclause_column_proxies(

4048 self,

4049 subquery: FromClause,

4050 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

4051 primary_key: ColumnSet,

4052 foreign_keys: Set[KeyedColumnElement[Any]],

4053 *,

4054 proxy_compound_columns: Optional[

4055 Iterable[Sequence[ColumnElement[Any]]]

4056 ] = None,

4057 ) -> None:

4058 self.element._generate_fromclause_column_proxies(

4059 subquery,

4060 columns,

4061 proxy_compound_columns=proxy_compound_columns,

4062 primary_key=primary_key,

4063 foreign_keys=foreign_keys,

4064 )

4065

4066 @util.ro_non_memoized_property

4067 def _all_selected_columns(self) -> _SelectIterable:

4068 return self.element._all_selected_columns

4069

4070 @util.ro_non_memoized_property

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

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

4073 representing the columns that

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

4075 :class:`_sql.TextClause` constructs.

4076

4077 .. versionadded:: 1.4

4078

4079 .. seealso::

4080

4081 :attr:`_sql.Select.selected_columns`

4082

4083 """

4084 return self.element.selected_columns

4085

4086 @util.ro_non_memoized_property

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

4088 return self.element._from_objects

4089

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

4091 return self.element._scalar_type()

4092

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

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

4095 raise NotImplementedError

4096

4097

4098class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4100 added.

4101

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

4103 :class:`_expression.CompoundSelect`

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

4105 rendering can be controlled. Compare to

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

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

4108 and is also a SELECT construct,

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

4110 only wrapped as a subquery.

4111

4112 """

4113

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

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

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

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

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

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

4120 _for_update_arg: Optional[ForUpdateArg] = None

4121

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

4123 self._label_style = _label_style

4124

4125 @_generative

4126 def with_for_update(

4127 self,

4128 *,

4129 nowait: bool = False,

4130 read: bool = False,

4131 of: Optional[_ForUpdateOfArgument] = None,

4132 skip_locked: bool = False,

4133 key_share: bool = False,

4134 ) -> Self:

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

4136 :class:`_expression.GenerativeSelect`.

4137

4138 E.g.::

4139

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

4141

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

4143 render a statement like:

4144

4145 .. sourcecode:: sql

4146

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

4148

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

4150 would produce:

4151

4152 .. sourcecode:: sql

4153

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

4155

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

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

4158 provided which allow for common database-specific

4159 variants.

4160

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

4162 Database and PostgreSQL dialects.

4163

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

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

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

4167

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

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

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

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

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

4173 table or as a column depending on backend.

4174

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

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

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

4178

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

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

4181 on the PostgreSQL dialect.

4182

4183 """

4184 self._for_update_arg = ForUpdateArg(

4185 nowait=nowait,

4186 read=read,

4187 of=of,

4188 skip_locked=skip_locked,

4189 key_share=key_share,

4190 )

4191 return self

4192

4193 def get_label_style(self) -> SelectLabelStyle:

4194 """

4195 Retrieve the current label style.

4196

4197 .. versionadded:: 1.4

4198

4199 """

4200 return self._label_style

4201

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

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

4204

4205 There are three "label styles" available,

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

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

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

4209 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4210

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

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

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

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

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

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

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

4218 that the impact of this labeling is minimal.

4219

4220 The rationale for disambiguation is mostly so that all column

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

4222 collection when a subquery is created.

4223

4224 .. versionadded:: 1.4 - the

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

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

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

4228

4229 .. seealso::

4230

4231 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4232

4233 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4234

4235 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4236

4237 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4238

4239 """

4240 if self._label_style is not style:

4241 self = self._generate()

4242 self._label_style = style

4243 return self

4244

4245 @property

4246 def _group_by_clause(self) -> ClauseList:

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

4248 return ClauseList._construct_raw(

4249 operators.comma_op, self._group_by_clauses

4250 )

4251

4252 @property

4253 def _order_by_clause(self) -> ClauseList:

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

4255 return ClauseList._construct_raw(

4256 operators.comma_op, self._order_by_clauses

4257 )

4258

4259 def _offset_or_limit_clause(

4260 self,

4261 element: _LimitOffsetType,

4262 name: Optional[str] = None,

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

4264 ) -> ColumnElement[Any]:

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

4266

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

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

4269

4270 """

4271 return coercions.expect(

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

4273 )

4274

4275 @overload

4276 def _offset_or_limit_clause_asint(

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

4278 ) -> NoReturn: ...

4279

4280 @overload

4281 def _offset_or_limit_clause_asint(

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

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

4284

4285 def _offset_or_limit_clause_asint(

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

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

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

4289 integer.

4290

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

4292 parameter. Otherwise, a compilation error is raised.

4293

4294 """

4295 if clause is None:

4296 return None

4297 try:

4298 value = clause._limit_offset_value

4299 except AttributeError as err:

4300 raise exc.CompileError(

4301 "This SELECT structure does not use a simple "

4302 "integer value for %s" % attrname

4303 ) from err

4304 else:

4305 return util.asint(value)

4306

4307 @property

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

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

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

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

4312 isn't currently set to an integer.

4313

4314 """

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

4316

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

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

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

4320 """

4321 return isinstance(clause, _OffsetLimitParam)

4322

4323 @property

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

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

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

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

4328 offset isn't currently set to an integer.

4329

4330 """

4331 return self._offset_or_limit_clause_asint(

4332 self._offset_clause, "offset"

4333 )

4334

4335 @property

4336 def _has_row_limiting_clause(self) -> bool:

4337 return (

4338 self._limit_clause is not None

4339 or self._offset_clause is not None

4340 or self._fetch_clause is not None

4341 )

4342

4343 @_generative

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

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

4346 applied.

4347

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

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

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

4351 functionality.

4352

4353 .. note::

4354

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

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

4357

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

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

4360

4361 .. seealso::

4362

4363 :meth:`_sql.GenerativeSelect.fetch`

4364

4365 :meth:`_sql.GenerativeSelect.offset`

4366

4367 """

4368

4369 self._fetch_clause = self._fetch_clause_options = None

4370 self._limit_clause = self._offset_or_limit_clause(limit)

4371 return self

4372

4373 @_generative

4374 def fetch(

4375 self,

4376 count: _LimitOffsetType,

4377 with_ties: bool = False,

4378 percent: bool = False,

4379 **dialect_kw: Any,

4380 ) -> Self:

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

4382 applied.

4383

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

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

4386 select. This functionality is is currently implemented for Oracle

4387 Database, PostgreSQL, MSSQL.

4388

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

4390

4391 .. note::

4392

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

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

4395

4396 .. versionadded:: 1.4

4397

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

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

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

4401 Pass ``None`` to reset it.

4402

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

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

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

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

4407

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

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

4410

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

4412 may be accepted by dialects.

4413

4414 .. versionadded:: 2.0.41

4415

4416 .. seealso::

4417

4418 :meth:`_sql.GenerativeSelect.limit`

4419

4420 :meth:`_sql.GenerativeSelect.offset`

4421

4422 """

4423 self._validate_dialect_kwargs(dialect_kw)

4424 self._limit_clause = None

4425 if count is None:

4426 self._fetch_clause = self._fetch_clause_options = None

4427 else:

4428 self._fetch_clause = self._offset_or_limit_clause(count)

4429 self._fetch_clause_options = {

4430 "with_ties": with_ties,

4431 "percent": percent,

4432 }

4433 return self

4434

4435 @_generative

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

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

4438 applied.

4439

4440

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

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

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

4444 functionality.

4445

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

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

4448

4449 .. seealso::

4450

4451 :meth:`_sql.GenerativeSelect.limit`

4452

4453 :meth:`_sql.GenerativeSelect.fetch`

4454

4455 """

4456

4457 self._offset_clause = self._offset_or_limit_clause(offset)

4458 return self

4459

4460 @_generative

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

4462 def slice(

4463 self,

4464 start: int,

4465 stop: int,

4466 ) -> Self:

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

4468

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

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

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

4472 query.

4473

4474 For example, ::

4475

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

4477

4478 renders as

4479

4480 .. sourcecode:: sql

4481

4482 SELECT users.id AS users_id,

4483 users.name AS users_name

4484 FROM users ORDER BY users.id

4485 LIMIT ? OFFSET ?

4486 (2, 1)

4487

4488 .. note::

4489

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

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

4492

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

4494 method generalized from the ORM.

4495

4496 .. seealso::

4497

4498 :meth:`_sql.GenerativeSelect.limit`

4499

4500 :meth:`_sql.GenerativeSelect.offset`

4501

4502 :meth:`_sql.GenerativeSelect.fetch`

4503

4504 """

4505 sql_util = util.preloaded.sql_util

4506 self._fetch_clause = self._fetch_clause_options = None

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

4508 self._limit_clause, self._offset_clause, start, stop

4509 )

4510 return self

4511

4512 @_generative

4513 def order_by(

4514 self,

4515 __first: Union[

4516 Literal[None, _NoArg.NO_ARG],

4517 _ColumnExpressionOrStrLabelArgument[Any],

4518 roles.OrderByRole,

4519 ] = _NoArg.NO_ARG,

4520 /,

4521 *clauses: Union[

4522 _ColumnExpressionOrStrLabelArgument[Any], roles.OrderByRole

4523 ],

4524 ) -> Self:

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

4526 criteria applied.

4527

4528 e.g.::

4529

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

4531

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

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

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

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

4536

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

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

4539

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

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

4542

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

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

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

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

4547 label matching in ORDER BY and GROUP BY expressions.

4548

4549 .. seealso::

4550

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

4552

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

4554

4555 """

4556

4557 if not clauses and __first is None:

4558 self._order_by_clauses = ()

4559 elif __first is not _NoArg.NO_ARG:

4560 self._order_by_clauses += tuple(

4561 coercions.expect(

4562 roles.OrderByRole, clause, apply_propagate_attrs=self

4563 )

4564 for clause in (__first,) + clauses

4565 )

4566 return self

4567

4568 @_generative

4569 def group_by(

4570 self,

4571 __first: Union[

4572 Literal[None, _NoArg.NO_ARG],

4573 _ColumnExpressionOrStrLabelArgument[Any],

4574 ] = _NoArg.NO_ARG,

4575 /,

4576 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4577 ) -> Self:

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

4579 criterion applied.

4580

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

4582

4583 e.g.::

4584

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

4586

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

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

4589

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

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

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

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

4594 label matching in ORDER BY and GROUP BY expressions.

4595

4596 .. seealso::

4597

4598 :ref:`tutorial_group_by_w_aggregates` - in the

4599 :ref:`unified_tutorial`

4600

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

4602

4603 """ # noqa: E501

4604

4605 if not clauses and __first is None:

4606 self._group_by_clauses = ()

4607 elif __first is not _NoArg.NO_ARG:

4608 self._group_by_clauses += tuple(

4609 coercions.expect(

4610 roles.GroupByRole, clause, apply_propagate_attrs=self

4611 )

4612 for clause in (__first,) + clauses

4613 )

4614 return self

4615

4616

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

4618class CompoundSelectState(CompileState):

4619 @util.memoized_property

4620 def _label_resolve_dict(

4621 self,

4622 ) -> Tuple[

4623 Dict[str, ColumnElement[Any]],

4624 Dict[str, ColumnElement[Any]],

4625 Dict[str, ColumnElement[Any]],

4626 ]:

4627 # TODO: this is hacky and slow

4628 hacky_subquery = self.statement.subquery()

4629 hacky_subquery.named_with_column = False

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

4631 return d, d, d

4632

4633

4634class _CompoundSelectKeyword(Enum):

4635 UNION = "UNION"

4636 UNION_ALL = "UNION ALL"

4637 EXCEPT = "EXCEPT"

4638 EXCEPT_ALL = "EXCEPT ALL"

4639 INTERSECT = "INTERSECT"

4640 INTERSECT_ALL = "INTERSECT ALL"

4641

4642

4643class CompoundSelect(

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

4645):

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

4647 SELECT-based set operations.

4648

4649

4650 .. seealso::

4651

4652 :func:`_expression.union`

4653

4654 :func:`_expression.union_all`

4655

4656 :func:`_expression.intersect`

4657

4658 :func:`_expression.intersect_all`

4659

4660 :func:`_expression.except`

4661

4662 :func:`_expression.except_all`

4663

4664 """

4665

4666 __visit_name__ = "compound_select"

4667

4668 _traverse_internals: _TraverseInternalsType = (

4669 [

4670 ("selects", InternalTraversal.dp_clauseelement_list),

4671 ("_limit_clause", InternalTraversal.dp_clauseelement),

4672 ("_offset_clause", InternalTraversal.dp_clauseelement),

4673 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4674 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4675 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4676 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4677 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4678 ("keyword", InternalTraversal.dp_string),

4679 ]

4680 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4681 + HasCTE._has_ctes_traverse_internals

4682 + DialectKWArgs._dialect_kwargs_traverse_internals

4683 + ExecutableStatement._executable_traverse_internals

4684 )

4685

4686 selects: List[SelectBase]

4687

4688 _is_from_container = True

4689 _auto_correlate = False

4690

4691 def __init__(

4692 self,

4693 keyword: _CompoundSelectKeyword,

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

4695 ):

4696 self.keyword = keyword

4697 self.selects = [

4698 coercions.expect(

4699 roles.CompoundElementRole, s, apply_propagate_attrs=self

4700 ).self_group(against=self)

4701 for s in selects

4702 ]

4703

4704 GenerativeSelect.__init__(self)

4705

4706 @classmethod

4707 def _create_union(

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

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

4710 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4711

4712 @classmethod

4713 def _create_union_all(

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

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

4716 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4717

4718 @classmethod

4719 def _create_except(

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

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

4722 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4723

4724 @classmethod

4725 def _create_except_all(

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

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

4728 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4729

4730 @classmethod

4731 def _create_intersect(

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

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

4734 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4735

4736 @classmethod

4737 def _create_intersect_all(

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

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

4740 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4741

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

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

4744

4745 def self_group(

4746 self, against: Optional[OperatorType] = None

4747 ) -> GroupedElement:

4748 return SelectStatementGrouping(self)

4749

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

4751 for s in self.selects:

4752 if s.is_derived_from(fromclause):

4753 return True

4754 return False

4755

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

4757 if self._label_style is not style:

4758 self = self._generate()

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

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

4761

4762 return self

4763

4764 def _ensure_disambiguated_names(self) -> Self:

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

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

4767 self = self._generate()

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

4769

4770 return self

4771

4772 def _generate_fromclause_column_proxies(

4773 self,

4774 subquery: FromClause,

4775 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

4776 primary_key: ColumnSet,

4777 foreign_keys: Set[KeyedColumnElement[Any]],

4778 *,

4779 proxy_compound_columns: Optional[

4780 Iterable[Sequence[ColumnElement[Any]]]

4781 ] = None,

4782 ) -> None:

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

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

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

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

4787 # generate below.

4788 select_0 = self.selects[0]

4789

4790 if self._label_style is not LABEL_STYLE_DEFAULT:

4791 select_0 = select_0.set_label_style(self._label_style)

4792

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

4794 # derived columns place a 'weight' annotation corresponding

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

4796 # that the corresponding_column() operation can resolve

4797 # conflicts

4798 extra_col_iterator = zip(

4799 *[

4800 [

4801 c._annotate(dd)

4802 for c in stmt._all_selected_columns

4803 if is_column_element(c)

4804 ]

4805 for dd, stmt in [

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

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

4808 ]

4809 ]

4810 )

4811

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

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

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

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

4816 # compound

4817 select_0._generate_fromclause_column_proxies(

4818 subquery,

4819 columns,

4820 proxy_compound_columns=extra_col_iterator,

4821 primary_key=primary_key,

4822 foreign_keys=foreign_keys,

4823 )

4824

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

4826 super()._refresh_for_new_column(column)

4827 for select in self.selects:

4828 select._refresh_for_new_column(column)

4829

4830 @util.ro_non_memoized_property

4831 def _all_selected_columns(self) -> _SelectIterable:

4832 return self.selects[0]._all_selected_columns

4833

4834 @util.ro_non_memoized_property

4835 def selected_columns(

4836 self,

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

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

4839 representing the columns that

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

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

4842

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

4844 :attr:`_expression.CompoundSelect.selected_columns`

4845 attribute returns the selected

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

4847 statements within the set operation.

4848

4849 .. seealso::

4850

4851 :attr:`_sql.Select.selected_columns`

4852

4853 .. versionadded:: 1.4

4854

4855 """

4856 return self.selects[0].selected_columns

4857

4858

4859# backwards compat

4860for elem in _CompoundSelectKeyword:

4861 setattr(CompoundSelect, elem.name, elem)

4862

4863

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

4865class SelectState(util.MemoizedSlots, CompileState):

4866 __slots__ = (

4867 "from_clauses",

4868 "froms",

4869 "columns_plus_names",

4870 "_label_resolve_dict",

4871 )

4872

4873 if TYPE_CHECKING:

4874 default_select_compile_options: CacheableOptions

4875 else:

4876

4877 class default_select_compile_options(CacheableOptions):

4878 _cache_key_traversal = []

4879

4880 if TYPE_CHECKING:

4881

4882 @classmethod

4883 def get_plugin_class(

4884 cls, statement: Executable

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

4886

4887 def __init__(

4888 self,

4889 statement: Select[Unpack[TupleAny]],

4890 compiler: SQLCompiler,

4891 **kw: Any,

4892 ):

4893 self.statement = statement

4894 self.from_clauses = statement._from_obj

4895

4896 for memoized_entities in statement._memoized_select_entities:

4897 self._setup_joins(

4898 memoized_entities._setup_joins, memoized_entities._raw_columns

4899 )

4900

4901 if statement._setup_joins:

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

4903

4904 self.froms = self._get_froms(statement)

4905

4906 self.columns_plus_names = statement._generate_columns_plus_names(True)

4907

4908 @classmethod

4909 def _plugin_not_implemented(cls) -> NoReturn:

4910 raise NotImplementedError(

4911 "The default SELECT construct without plugins does not "

4912 "implement this method."

4913 )

4914

4915 @classmethod

4916 def get_column_descriptions(

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

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

4919 return [

4920 {

4921 "name": name,

4922 "type": element.type,

4923 "expr": element,

4924 }

4925 for _, name, _, element, _ in (

4926 statement._generate_columns_plus_names(False)

4927 )

4928 ]

4929

4930 @classmethod

4931 def from_statement(

4932 cls,

4933 statement: Select[Unpack[TupleAny]],

4934 from_statement: roles.ReturnsRowsRole,

4935 ) -> ExecutableReturnsRows:

4936 cls._plugin_not_implemented()

4937

4938 @classmethod

4939 def get_columns_clause_froms(

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

4941 ) -> List[FromClause]:

4942 return cls._normalize_froms(

4943 itertools.chain.from_iterable(

4944 element._from_objects for element in statement._raw_columns

4945 )

4946 )

4947

4948 @classmethod

4949 def _column_naming_convention(

4950 cls, label_style: SelectLabelStyle

4951 ) -> _LabelConventionCallable:

4952 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4953

4954 dedupe = label_style is not LABEL_STYLE_NONE

4955

4956 pa = prefix_anon_map()

4957 names = set()

4958

4959 def go(

4960 c: Union[ColumnElement[Any], AbstractTextClause],

4961 col_name: Optional[str] = None,

4962 ) -> Optional[str]:

4963 if is_text_clause(c):

4964 return None

4965 elif TYPE_CHECKING:

4966 assert is_column_element(c)

4967

4968 if not dedupe:

4969 name = c._proxy_key

4970 if name is None:

4971 name = "_no_label"

4972 return name

4973

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

4975

4976 if name is None:

4977 name = "_no_label"

4978 if name in names:

4979 return c._anon_label(name) % pa

4980 else:

4981 names.add(name)

4982 return name

4983

4984 elif name in names:

4985 return (

4986 c._anon_tq_key_label % pa

4987 if table_qualified

4988 else c._anon_key_label % pa

4989 )

4990 else:

4991 names.add(name)

4992 return name

4993

4994 return go

4995

4996 def _get_froms(

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

4998 ) -> List[FromClause]:

4999 ambiguous_table_name_map: _AmbiguousTableNameMap

5000 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

5001

5002 return self._normalize_froms(

5003 itertools.chain(

5004 self.from_clauses,

5005 itertools.chain.from_iterable(

5006 [

5007 element._from_objects

5008 for element in statement._raw_columns

5009 ]

5010 ),

5011 itertools.chain.from_iterable(

5012 [

5013 element._from_objects

5014 for element in statement._where_criteria

5015 ]

5016 ),

5017 ),

5018 check_statement=statement,

5019 ambiguous_table_name_map=ambiguous_table_name_map,

5020 )

5021

5022 @classmethod

5023 def _normalize_froms(

5024 cls,

5025 iterable_of_froms: Iterable[FromClause],

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

5027 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

5028 ) -> List[FromClause]:

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

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

5031

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

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

5034 etc.

5035

5036 """

5037 seen: Set[FromClause] = set()

5038 froms: List[FromClause] = []

5039

5040 for item in iterable_of_froms:

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

5042 raise exc.InvalidRequestError(

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

5044 )

5045

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

5047 froms.append(item)

5048 seen.update(item._cloned_set)

5049

5050 if froms:

5051 toremove = set(

5052 itertools.chain.from_iterable(

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

5054 )

5055 )

5056 if toremove:

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

5058 # using a list to maintain ordering

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

5060

5061 if ambiguous_table_name_map is not None:

5062 ambiguous_table_name_map.update(

5063 (

5064 fr.name,

5065 _anonymous_label.safe_construct(

5066 hash(fr.name), fr.name

5067 ),

5068 )

5069 for item in froms

5070 for fr in item._from_objects

5071 if is_table(fr)

5072 and fr.schema

5073 and fr.name not in ambiguous_table_name_map

5074 )

5075

5076 return froms

5077

5078 def _get_display_froms(

5079 self,

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

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

5082 ) -> List[FromClause]:

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

5084

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

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

5087 may want to leave those absent if it is automatically

5088 correlating.

5089

5090 """

5091

5092 froms = self.froms

5093

5094 if self.statement._correlate:

5095 to_correlate = self.statement._correlate

5096 if to_correlate:

5097 to_remove = _cloned_intersection(

5098 _cloned_intersection(

5099 froms, explicit_correlate_froms or ()

5100 ),

5101 to_correlate,

5102 )

5103 froms = [f for f in froms if f not in to_remove]

5104

5105 if self.statement._correlate_except is not None:

5106 to_remove = _cloned_difference(

5107 _cloned_intersection(froms, explicit_correlate_froms or ()),

5108 self.statement._correlate_except,

5109 )

5110 froms = [f for f in froms if f not in to_remove]

5111

5112 if (

5113 self.statement._auto_correlate

5114 and implicit_correlate_froms

5115 and len(froms) > 1

5116 ):

5117 to_remove = _cloned_intersection(froms, implicit_correlate_froms)

5118 froms = [f for f in froms if f not in to_remove]

5119

5120 if not len(froms):

5121 raise exc.InvalidRequestError(

5122 "Select statement '%r"

5123 "' returned no FROM clauses "

5124 "due to auto-correlation; "

5125 "specify correlate(<tables>) "

5126 "to control correlation "

5127 "manually." % self.statement

5128 )

5129

5130 return froms

5131

5132 def _memoized_attr__label_resolve_dict(

5133 self,

5134 ) -> Tuple[

5135 Dict[str, ColumnElement[Any]],

5136 Dict[str, ColumnElement[Any]],

5137 Dict[str, ColumnElement[Any]],

5138 ]:

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

5140 c._tq_label or c.key: c

5141 for c in self.statement._all_selected_columns

5142 if c._allow_label_resolve

5143 }

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

5145 c.key: c # type: ignore[misc]

5146 for c in _select_iterables(self.froms)

5147 if c._allow_label_resolve

5148 }

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

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

5151 with_cols.setdefault(key, value)

5152

5153 return with_cols, only_froms, only_cols

5154

5155 @classmethod

5156 def _get_filter_by_entities(

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

5158 ) -> Collection[

5159 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5160 ]:

5161 """Return all entities to search for filter_by() attributes.

5162

5163 This includes:

5164

5165 * All joined entities from _setup_joins

5166 * Memoized entities from previous operations (e.g.,

5167 before with_only_columns)

5168 * Explicit FROM objects from _from_obj

5169 * Entities inferred from _raw_columns

5170

5171 .. versionadded:: 2.1

5172

5173 """

5174 entities: set[

5175 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5176 ]

5177

5178 entities = set(

5179 join_element[0] for join_element in statement._setup_joins

5180 )

5181

5182 for memoized in statement._memoized_select_entities:

5183 entities.update(

5184 join_element[0] for join_element in memoized._setup_joins

5185 )

5186

5187 entities.update(statement._from_obj)

5188

5189 for col in statement._raw_columns:

5190 entities.update(col._from_objects)

5191

5192 return entities

5193

5194 @classmethod

5195 def all_selected_columns(

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

5197 ) -> _SelectIterable:

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

5199

5200 def _setup_joins(

5201 self,

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

5203 raw_columns: List[_ColumnsClauseElement],

5204 ) -> None:

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

5206 if TYPE_CHECKING:

5207 if onclause is not None:

5208 assert isinstance(onclause, ColumnElement)

5209

5210 explicit_left = left

5211 isouter = flags["isouter"]

5212 full = flags["full"]

5213

5214 if left is None:

5215 (

5216 left,

5217 replace_from_obj_index,

5218 ) = self._join_determine_implicit_left_side(

5219 raw_columns, left, right, onclause

5220 )

5221 else:

5222 replace_from_obj_index = self._join_place_explicit_left_side(

5223 left

5224 )

5225

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

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

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

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

5230 # used instead.

5231 if TYPE_CHECKING:

5232 assert isinstance(right, FromClause)

5233 if onclause is not None:

5234 assert isinstance(onclause, ColumnElement)

5235

5236 if replace_from_obj_index is not None:

5237 # splice into an existing element in the

5238 # self._from_obj list

5239 left_clause = self.from_clauses[replace_from_obj_index]

5240

5241 if explicit_left is not None and onclause is None:

5242 onclause = Join._join_condition(explicit_left, right)

5243

5244 self.from_clauses = (

5245 self.from_clauses[:replace_from_obj_index]

5246 + (

5247 Join(

5248 left_clause,

5249 right,

5250 onclause,

5251 isouter=isouter,

5252 full=full,

5253 ),

5254 )

5255 + self.from_clauses[replace_from_obj_index + 1 :]

5256 )

5257 else:

5258 assert left is not None

5259 self.from_clauses = self.from_clauses + (

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

5261 )

5262

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

5264 def _join_determine_implicit_left_side(

5265 self,

5266 raw_columns: List[_ColumnsClauseElement],

5267 left: Optional[FromClause],

5268 right: _JoinTargetElement,

5269 onclause: Optional[ColumnElement[Any]],

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

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

5272 determine if an existing FROM or entity in this query

5273 can serve as the left hand side.

5274

5275 """

5276

5277 sql_util = util.preloaded.sql_util

5278

5279 replace_from_obj_index: Optional[int] = None

5280

5281 from_clauses = self.from_clauses

5282

5283 if from_clauses:

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

5285 from_clauses, right, onclause

5286 )

5287

5288 if len(indexes) == 1:

5289 replace_from_obj_index = indexes[0]

5290 left = from_clauses[replace_from_obj_index]

5291 else:

5292 potential = {}

5293 statement = self.statement

5294

5295 for from_clause in itertools.chain(

5296 itertools.chain.from_iterable(

5297 [element._from_objects for element in raw_columns]

5298 ),

5299 itertools.chain.from_iterable(

5300 [

5301 element._from_objects

5302 for element in statement._where_criteria

5303 ]

5304 ),

5305 ):

5306 potential[from_clause] = ()

5307

5308 all_clauses = list(potential.keys())

5309 indexes = sql_util.find_left_clause_to_join_from(

5310 all_clauses, right, onclause

5311 )

5312

5313 if len(indexes) == 1:

5314 left = all_clauses[indexes[0]]

5315

5316 if len(indexes) > 1:

5317 raise exc.InvalidRequestError(

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

5319 "from, there are multiple FROMS which can "

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

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

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

5323 "help resolve the ambiguity."

5324 )

5325 elif not indexes:

5326 raise exc.InvalidRequestError(

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

5328 "Please use the .select_from() "

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

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

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

5332 )

5333 return left, replace_from_obj_index

5334

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

5336 def _join_place_explicit_left_side(

5337 self, left: FromClause

5338 ) -> Optional[int]:

5339 replace_from_obj_index: Optional[int] = None

5340

5341 sql_util = util.preloaded.sql_util

5342

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

5344

5345 if from_clauses:

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

5347 self.from_clauses, left

5348 )

5349 else:

5350 indexes = []

5351

5352 if len(indexes) > 1:

5353 raise exc.InvalidRequestError(

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

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

5356 "ON clause."

5357 )

5358

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

5360 # an existing FROM in the self._from_obj tuple

5361 if indexes:

5362 replace_from_obj_index = indexes[0]

5363

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

5365 # self._from_obj tuple

5366

5367 return replace_from_obj_index

5368

5369

5370class _SelectFromElements:

5371 __slots__ = ()

5372

5373 _raw_columns: List[_ColumnsClauseElement]

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

5375 _from_obj: Tuple[FromClause, ...]

5376

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

5378 # note this does not include elements

5379 # in _setup_joins

5380

5381 seen = set()

5382 for element in self._raw_columns:

5383 for fr in element._from_objects:

5384 if fr in seen:

5385 continue

5386 seen.add(fr)

5387 yield fr

5388 for element in self._where_criteria:

5389 for fr in element._from_objects:

5390 if fr in seen:

5391 continue

5392 seen.add(fr)

5393 yield fr

5394 for element in self._from_obj:

5395 if element in seen:

5396 continue

5397 seen.add(element)

5398 yield element

5399

5400

5401class _MemoizedSelectEntities(

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

5403):

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

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

5406 statement will be SELECTing from. This object represents

5407 the entities from the SELECT before that transformation was applied,

5408 so that transformations that were made in terms of the SELECT at that

5409 time, such as join() as well as options(), can access the correct context.

5410

5411 In previous SQLAlchemy versions, this wasn't needed because these

5412 constructs calculated everything up front, like when you called join()

5413 or options(), it did everything to figure out how that would translate

5414 into specific SQL constructs that would be ready to send directly to the

5415 SQL compiler when needed. But as of

5416 1.4, all of that stuff is done in the compilation phase, during the

5417 "compile state" portion of the process, so that the work can all be

5418 cached. So it needs to be able to resolve joins/options2 based on what

5419 the list of entities was when those methods were called.

5420

5421

5422 """

5423

5424 __visit_name__ = "memoized_select_entities"

5425

5426 _traverse_internals: _TraverseInternalsType = [

5427 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5428 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5429 ("_with_options", InternalTraversal.dp_executable_options),

5430 ]

5431

5432 _is_clone_of: Optional[ClauseElement]

5433 _raw_columns: List[_ColumnsClauseElement]

5434 _setup_joins: Tuple[_SetupJoinsElement, ...]

5435 _with_options: Tuple[ExecutableOption, ...]

5436

5437 _annotations = util.EMPTY_DICT

5438

5439 def _clone(self, **kw: Any) -> Self:

5440 c = self.__class__.__new__(self.__class__)

5441 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5442

5443 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5444 return c

5445

5446 @classmethod

5447 def _generate_for_statement(

5448 cls, select_stmt: Select[Unpack[TupleAny]]

5449 ) -> None:

5450 if select_stmt._setup_joins or select_stmt._with_options:

5451 self = _MemoizedSelectEntities()

5452 self._raw_columns = select_stmt._raw_columns

5453 self._setup_joins = select_stmt._setup_joins

5454 self._with_options = select_stmt._with_options

5455

5456 select_stmt._memoized_select_entities += (self,)

5457 select_stmt._raw_columns = []

5458 select_stmt._setup_joins = select_stmt._with_options = ()

5459

5460

5461class Select(

5462 HasPrefixes,

5463 HasSuffixes,

5464 HasHints,

5465 HasCompileState,

5466 HasSyntaxExtensions[

5467 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5468 ],

5469 _SelectFromElements,

5470 GenerativeSelect,

5471 TypedReturnsRows[Unpack[_Ts]],

5472):

5473 """Represents a ``SELECT`` statement.

5474

5475 The :class:`_sql.Select` object is normally constructed using the

5476 :func:`_sql.select` function. See that function for details.

5477

5478 Available extension points:

5479

5480 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5481 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5482 keyword (if any) and the list of columns.

5483 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5484 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5485

5486 .. seealso::

5487

5488 :func:`_sql.select`

5489

5490 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5491

5492 """

5493

5494 __visit_name__ = "select"

5495

5496 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5497 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5498

5499 _raw_columns: List[_ColumnsClauseElement]

5500

5501 _distinct: bool = False

5502 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5503 _correlate: Tuple[FromClause, ...] = ()

5504 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5505 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5506 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5507 _from_obj: Tuple[FromClause, ...] = ()

5508

5509 _position_map = util.immutabledict(

5510 {

5511 "post_select": "_post_select_clause",

5512 "pre_columns": "_pre_columns_clause",

5513 "post_criteria": "_post_criteria_clause",

5514 "post_body": "_post_body_clause",

5515 }

5516 )

5517

5518 _post_select_clause: Optional[ClauseElement] = None

5519 """extension point for a ClauseElement that will be compiled directly

5520 after the SELECT keyword.

5521

5522 .. versionadded:: 2.1

5523

5524 """

5525

5526 _pre_columns_clause: Optional[ClauseElement] = None

5527 """extension point for a ClauseElement that will be compiled directly

5528 before the "columns" clause; after DISTINCT (if present).

5529

5530 .. versionadded:: 2.1

5531

5532 """

5533

5534 _post_criteria_clause: Optional[ClauseElement] = None

5535 """extension point for a ClauseElement that will be compiled directly

5536 after "criteria", following the HAVING clause but before ORDER BY.

5537

5538 .. versionadded:: 2.1

5539

5540 """

5541

5542 _post_body_clause: Optional[ClauseElement] = None

5543 """extension point for a ClauseElement that will be compiled directly

5544 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5545 of the SELECT.

5546

5547 .. versionadded:: 2.1

5548

5549 """

5550

5551 _auto_correlate = True

5552 _is_select_statement = True

5553 _compile_options: CacheableOptions = (

5554 SelectState.default_select_compile_options

5555 )

5556

5557 _traverse_internals: _TraverseInternalsType = (

5558 [

5559 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5560 (

5561 "_memoized_select_entities",

5562 InternalTraversal.dp_memoized_select_entities,

5563 ),

5564 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5565 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5566 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5567 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5568 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5569 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5570 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5571 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5572 ("_limit_clause", InternalTraversal.dp_clauseelement),

5573 ("_offset_clause", InternalTraversal.dp_clauseelement),

5574 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5575 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5576 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5577 ("_distinct", InternalTraversal.dp_boolean),

5578 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5579 ("_label_style", InternalTraversal.dp_plain_obj),

5580 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5581 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5582 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5583 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5584 ]

5585 + HasCTE._has_ctes_traverse_internals

5586 + HasPrefixes._has_prefixes_traverse_internals

5587 + HasSuffixes._has_suffixes_traverse_internals

5588 + HasHints._has_hints_traverse_internals

5589 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5590 + ExecutableStatement._executable_traverse_internals

5591 + DialectKWArgs._dialect_kwargs_traverse_internals

5592 )

5593

5594 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5595 ("_compile_options", InternalTraversal.dp_has_cache_key)

5596 ]

5597

5598 _compile_state_factory: Type[SelectState]

5599

5600 @classmethod

5601 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5602 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5603

5604 Used internally to build up :class:`.Select` constructs with

5605 pre-established state.

5606

5607 """

5608

5609 stmt = Select.__new__(Select)

5610 stmt.__dict__.update(kw)

5611 return stmt

5612

5613 def __init__(

5614 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5615 ):

5616 r"""Construct a new :class:`_expression.Select`.

5617

5618 The public constructor for :class:`_expression.Select` is the

5619 :func:`_sql.select` function.

5620

5621 """

5622 self._raw_columns = [

5623 coercions.expect(

5624 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5625 )

5626 for ent in entities

5627 ]

5628 GenerativeSelect.__init__(self)

5629

5630 def _apply_syntax_extension_to_self(

5631 self, extension: SyntaxExtension

5632 ) -> None:

5633 extension.apply_to_select(self)

5634

5635 def _scalar_type(self) -> TypeEngine[Any]:

5636 if not self._raw_columns:

5637 return NULLTYPE

5638 elem = self._raw_columns[0]

5639 cols = list(elem._select_iterable)

5640 return cols[0].type

5641

5642 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5643 """A synonym for the :meth:`_sql.Select.where` method."""

5644

5645 return self.where(*criteria)

5646

5647 if TYPE_CHECKING:

5648

5649 @overload

5650 def scalar_subquery(

5651 self: Select[_MAYBE_ENTITY],

5652 ) -> ScalarSelect[Any]: ...

5653

5654 @overload

5655 def scalar_subquery(

5656 self: Select[_NOT_ENTITY],

5657 ) -> ScalarSelect[_NOT_ENTITY]: ...

5658

5659 @overload

5660 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5661

5662 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5663

5664 def filter_by(self, **kwargs: Any) -> Self:

5665 r"""Apply the given filtering criterion as a WHERE clause

5666 to this select, using keyword expressions.

5667

5668 E.g.::

5669

5670 stmt = select(User).filter_by(name="some name")

5671

5672 Multiple criteria may be specified as comma separated; the effect

5673 is that they will be joined together using the :func:`.and_`

5674 function::

5675

5676 stmt = select(User).filter_by(name="some name", id=5)

5677

5678 The keyword expressions are extracted by searching across **all

5679 entities present in the FROM clause** of the statement. If a

5680 keyword name is present in more than one entity,

5681 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5682 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5683 explicit column references::

5684

5685 # both User and Address have an 'id' attribute

5686 stmt = select(User).join(Address).filter_by(id=5)

5687 # raises AmbiguousColumnError

5688

5689 # use filter() with explicit qualification instead

5690 stmt = select(User).join(Address).filter(Address.id == 5)

5691

5692 .. versionchanged:: 2.1

5693

5694 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5695 entities rather than only searching the last joined entity or first

5696 FROM entity. This allows the method to locate attributes

5697 unambiguously across multiple joined tables. The new

5698 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5699 is present in more than one entity.

5700

5701 See :ref:`change_8601` for migration notes.

5702

5703 .. seealso::

5704

5705 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

5706

5707 :meth:`_sql.Select.filter` - filter on SQL expressions.

5708

5709 :meth:`_sql.Select.where` - filter on SQL expressions.

5710

5711 """

5712 # Get all entities via plugin system

5713 all_entities = SelectState.get_plugin_class(

5714 self

5715 )._get_filter_by_entities(self)

5716

5717 clauses = [

5718 _entity_namespace_key_search_all(all_entities, key) == value

5719 for key, value in kwargs.items()

5720 ]

5721 return self.filter(*clauses)

5722

5723 @property

5724 def column_descriptions(self) -> Any:

5725 """Return a :term:`plugin-enabled` 'column descriptions' structure

5726 referring to the columns which are SELECTed by this statement.

5727

5728 This attribute is generally useful when using the ORM, as an

5729 extended structure which includes information about mapped

5730 entities is returned. The section :ref:`queryguide_inspection`

5731 contains more background.

5732

5733 For a Core-only statement, the structure returned by this accessor

5734 is derived from the same objects that are returned by the

5735 :attr:`.Select.selected_columns` accessor, formatted as a list of

5736 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5737 which indicate the column expressions to be selected::

5738

5739 >>> stmt = select(user_table)

5740 >>> stmt.column_descriptions

5741 [

5742 {

5743 'name': 'id',

5744 'type': Integer(),

5745 'expr': Column('id', Integer(), ...)},

5746 {

5747 'name': 'name',

5748 'type': String(length=30),

5749 'expr': Column('name', String(length=30), ...)}

5750 ]

5751

5752 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5753 attribute returns a structure for a Core-only set of entities,

5754 not just ORM-only entities.

5755

5756 .. seealso::

5757

5758 :attr:`.UpdateBase.entity_description` - entity information for

5759 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5760

5761 :ref:`queryguide_inspection` - ORM background

5762

5763 """

5764 meth = SelectState.get_plugin_class(self).get_column_descriptions

5765 return meth(self)

5766

5767 def from_statement(

5768 self, statement: roles.ReturnsRowsRole

5769 ) -> ExecutableReturnsRows:

5770 """Apply the columns which this :class:`.Select` would select

5771 onto another statement.

5772

5773 This operation is :term:`plugin-specific` and will raise a not

5774 supported exception if this :class:`_sql.Select` does not select from

5775 plugin-enabled entities.

5776

5777

5778 The statement is typically either a :func:`_expression.text` or

5779 :func:`_expression.select` construct, and should return the set of

5780 columns appropriate to the entities represented by this

5781 :class:`.Select`.

5782

5783 .. seealso::

5784

5785 :ref:`orm_queryguide_selecting_text` - usage examples in the

5786 ORM Querying Guide

5787

5788 """

5789 meth = SelectState.get_plugin_class(self).from_statement

5790 return meth(self, statement)

5791

5792 @_generative

5793 def join(

5794 self,

5795 target: _JoinTargetArgument,

5796 onclause: Optional[_OnClauseArgument] = None,

5797 *,

5798 isouter: bool = False,

5799 full: bool = False,

5800 ) -> Self:

5801 r"""Create a SQL JOIN against this :class:`_expression.Select`

5802 object's criterion

5803 and apply generatively, returning the newly resulting

5804 :class:`_expression.Select`.

5805

5806 E.g.::

5807

5808 stmt = select(user_table).join(

5809 address_table, user_table.c.id == address_table.c.user_id

5810 )

5811

5812 The above statement generates SQL similar to:

5813

5814 .. sourcecode:: sql

5815

5816 SELECT user.id, user.name

5817 FROM user

5818 JOIN address ON user.id = address.user_id

5819

5820 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5821 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5822 source that is within the FROM clause of the existing SELECT,

5823 and a given target :class:`_sql.FromClause`, and then adds

5824 this :class:`_sql.Join` to the FROM clause of the newly generated

5825 SELECT statement. This is completely reworked from the behavior

5826 in 1.3, which would instead create a subquery of the entire

5827 :class:`_expression.Select` and then join that subquery to the

5828 target.

5829

5830 This is a **backwards incompatible change** as the previous behavior

5831 was mostly useless, producing an unnamed subquery rejected by

5832 most databases in any case. The new behavior is modeled after

5833 that of the very successful :meth:`_orm.Query.join` method in the

5834 ORM, in order to support the functionality of :class:`_orm.Query`

5835 being available by using a :class:`_sql.Select` object with an

5836 :class:`_orm.Session`.

5837

5838 See the notes for this change at :ref:`change_select_join`.

5839

5840

5841 :param target: target table to join towards

5842

5843 :param onclause: ON clause of the join. If omitted, an ON clause

5844 is generated automatically based on the :class:`_schema.ForeignKey`

5845 linkages between the two tables, if one can be unambiguously

5846 determined, otherwise an error is raised.

5847

5848 :param isouter: if True, generate LEFT OUTER join. Same as

5849 :meth:`_expression.Select.outerjoin`.

5850

5851 :param full: if True, generate FULL OUTER join.

5852

5853 .. seealso::

5854

5855 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5856

5857 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5858

5859 :meth:`_expression.Select.join_from`

5860

5861 :meth:`_expression.Select.outerjoin`

5862

5863 """ # noqa: E501

5864 join_target = coercions.expect(

5865 roles.JoinTargetRole, target, apply_propagate_attrs=self

5866 )

5867 if onclause is not None:

5868 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5869 else:

5870 onclause_element = None

5871

5872 self._setup_joins += (

5873 (

5874 join_target,

5875 onclause_element,

5876 None,

5877 {"isouter": isouter, "full": full},

5878 ),

5879 )

5880 return self

5881

5882 def outerjoin_from(

5883 self,

5884 from_: _FromClauseArgument,

5885 target: _JoinTargetArgument,

5886 onclause: Optional[_OnClauseArgument] = None,

5887 *,

5888 full: bool = False,

5889 ) -> Self:

5890 r"""Create a SQL LEFT OUTER JOIN against this

5891 :class:`_expression.Select` object's criterion and apply generatively,

5892 returning the newly resulting :class:`_expression.Select`.

5893

5894 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5895

5896 """

5897 return self.join_from(

5898 from_, target, onclause=onclause, isouter=True, full=full

5899 )

5900

5901 @_generative

5902 def join_from(

5903 self,

5904 from_: _FromClauseArgument,

5905 target: _JoinTargetArgument,

5906 onclause: Optional[_OnClauseArgument] = None,

5907 *,

5908 isouter: bool = False,

5909 full: bool = False,

5910 ) -> Self:

5911 r"""Create a SQL JOIN against this :class:`_expression.Select`

5912 object's criterion

5913 and apply generatively, returning the newly resulting

5914 :class:`_expression.Select`.

5915

5916 E.g.::

5917

5918 stmt = select(user_table, address_table).join_from(

5919 user_table, address_table, user_table.c.id == address_table.c.user_id

5920 )

5921

5922 The above statement generates SQL similar to:

5923

5924 .. sourcecode:: sql

5925

5926 SELECT user.id, user.name, address.id, address.email, address.user_id

5927 FROM user JOIN address ON user.id = address.user_id

5928

5929 .. versionadded:: 1.4

5930

5931 :param from\_: the left side of the join, will be rendered in the

5932 FROM clause and is roughly equivalent to using the

5933 :meth:`.Select.select_from` method.

5934

5935 :param target: target table to join towards

5936

5937 :param onclause: ON clause of the join.

5938

5939 :param isouter: if True, generate LEFT OUTER join. Same as

5940 :meth:`_expression.Select.outerjoin`.

5941

5942 :param full: if True, generate FULL OUTER join.

5943

5944 .. seealso::

5945

5946 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5947

5948 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5949

5950 :meth:`_expression.Select.join`

5951

5952 """ # noqa: E501

5953

5954 # note the order of parsing from vs. target is important here, as we

5955 # are also deriving the source of the plugin (i.e. the subject mapper

5956 # in an ORM query) which should favor the "from_" over the "target"

5957

5958 from_ = coercions.expect(

5959 roles.FromClauseRole, from_, apply_propagate_attrs=self

5960 )

5961 join_target = coercions.expect(

5962 roles.JoinTargetRole, target, apply_propagate_attrs=self

5963 )

5964 if onclause is not None:

5965 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5966 else:

5967 onclause_element = None

5968

5969 self._setup_joins += (

5970 (

5971 join_target,

5972 onclause_element,

5973 from_,

5974 {"isouter": isouter, "full": full},

5975 ),

5976 )

5977 return self

5978

5979 def outerjoin(

5980 self,

5981 target: _JoinTargetArgument,

5982 onclause: Optional[_OnClauseArgument] = None,

5983 *,

5984 full: bool = False,

5985 ) -> Self:

5986 """Create a left outer join.

5987

5988 Parameters are the same as that of :meth:`_expression.Select.join`.

5989

5990 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5991 creates a :class:`_sql.Join` object between a

5992 :class:`_sql.FromClause` source that is within the FROM clause of

5993 the existing SELECT, and a given target :class:`_sql.FromClause`,

5994 and then adds this :class:`_sql.Join` to the FROM clause of the

5995 newly generated SELECT statement. This is completely reworked

5996 from the behavior in 1.3, which would instead create a subquery of

5997 the entire

5998 :class:`_expression.Select` and then join that subquery to the

5999 target.

6000

6001 This is a **backwards incompatible change** as the previous behavior

6002 was mostly useless, producing an unnamed subquery rejected by

6003 most databases in any case. The new behavior is modeled after

6004 that of the very successful :meth:`_orm.Query.join` method in the

6005 ORM, in order to support the functionality of :class:`_orm.Query`

6006 being available by using a :class:`_sql.Select` object with an

6007 :class:`_orm.Session`.

6008

6009 See the notes for this change at :ref:`change_select_join`.

6010

6011 .. seealso::

6012

6013 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

6014

6015 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

6016

6017 :meth:`_expression.Select.join`

6018

6019 """

6020 return self.join(target, onclause=onclause, isouter=True, full=full)

6021

6022 def get_final_froms(self) -> Sequence[FromClause]:

6023 """Compute the final displayed list of :class:`_expression.FromClause`

6024 elements.

6025

6026 This method will run through the full computation required to

6027 determine what FROM elements will be displayed in the resulting

6028 SELECT statement, including shadowing individual tables with

6029 JOIN objects, as well as full computation for ORM use cases including

6030 eager loading clauses.

6031

6032 For ORM use, this accessor returns the **post compilation**

6033 list of FROM objects; this collection will include elements such as

6034 eagerly loaded tables and joins. The objects will **not** be

6035 ORM enabled and not work as a replacement for the

6036 :meth:`_sql.Select.select_froms` collection; additionally, the

6037 method is not well performing for an ORM enabled statement as it

6038 will incur the full ORM construction process.

6039

6040 To retrieve the FROM list that's implied by the "columns" collection

6041 passed to the :class:`_sql.Select` originally, use the

6042 :attr:`_sql.Select.columns_clause_froms` accessor.

6043

6044 To select from an alternative set of columns while maintaining the

6045 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

6046 pass the

6047 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6048 parameter.

6049

6050 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

6051 method replaces the previous :attr:`_sql.Select.froms` accessor,

6052 which is deprecated.

6053

6054 .. seealso::

6055

6056 :attr:`_sql.Select.columns_clause_froms`

6057

6058 """

6059 compiler = self._default_compiler()

6060

6061 return self._compile_state_factory(self, compiler)._get_display_froms()

6062

6063 @property

6064 @util.deprecated(

6065 "1.4.23",

6066 "The :attr:`_expression.Select.froms` attribute is moved to "

6067 "the :meth:`_expression.Select.get_final_froms` method.",

6068 )

6069 def froms(self) -> Sequence[FromClause]:

6070 """Return the displayed list of :class:`_expression.FromClause`

6071 elements.

6072

6073

6074 """

6075 return self.get_final_froms()

6076

6077 @property

6078 def columns_clause_froms(self) -> List[FromClause]:

6079 """Return the set of :class:`_expression.FromClause` objects implied

6080 by the columns clause of this SELECT statement.

6081

6082 .. versionadded:: 1.4.23

6083

6084 .. seealso::

6085

6086 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6087 statement into account

6088

6089 :meth:`_sql.Select.with_only_columns` - makes use of this

6090 collection to set up a new FROM list

6091

6092 """

6093

6094 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6095 self

6096 )

6097

6098 @property

6099 def inner_columns(self) -> _SelectIterable:

6100 """An iterator of all :class:`_expression.ColumnElement`

6101 expressions which would

6102 be rendered into the columns clause of the resulting SELECT statement.

6103

6104 This method is legacy as of 1.4 and is superseded by the

6105 :attr:`_expression.Select.exported_columns` collection.

6106

6107 """

6108

6109 return iter(self._all_selected_columns)

6110

6111 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

6112 if fromclause is not None and self in fromclause._cloned_set:

6113 return True

6114

6115 for f in self._iterate_from_elements():

6116 if f.is_derived_from(fromclause):

6117 return True

6118 return False

6119

6120 def _copy_internals(

6121 self, clone: _CloneCallableType = _clone, **kw: Any

6122 ) -> None:

6123 # Select() object has been cloned and probably adapted by the

6124 # given clone function. Apply the cloning function to internal

6125 # objects

6126

6127 # 1. keep a dictionary of the froms we've cloned, and what

6128 # they've become. This allows us to ensure the same cloned from

6129 # is used when other items such as columns are "cloned"

6130

6131 all_the_froms = set(

6132 itertools.chain(

6133 _from_objects(*self._raw_columns),

6134 _from_objects(*self._where_criteria),

6135 _from_objects(*[elem[0] for elem in self._setup_joins]),

6136 )

6137 )

6138

6139 # do a clone for the froms we've gathered. what is important here

6140 # is if any of the things we are selecting from, like tables,

6141 # were converted into Join objects. if so, these need to be

6142 # added to _from_obj explicitly, because otherwise they won't be

6143 # part of the new state, as they don't associate themselves with

6144 # their columns.

6145 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6146

6147 # 2. copy FROM collections, adding in joins that we've created.

6148 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6149 add_froms = (

6150 {f for f in new_froms.values() if isinstance(f, Join)}

6151 .difference(all_the_froms)

6152 .difference(existing_from_obj)

6153 )

6154

6155 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6156

6157 # 3. clone everything else, making sure we use columns

6158 # corresponding to the froms we just made.

6159 def replace(

6160 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6161 **kw: Any,

6162 ) -> Optional[KeyedColumnElement[Any]]:

6163 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6164 newelem = new_froms[obj.table].corresponding_column(obj)

6165 return newelem

6166 return None

6167

6168 kw["replace"] = replace

6169

6170 # copy everything else. for table-ish things like correlate,

6171 # correlate_except, setup_joins, these clone normally. For

6172 # column-expression oriented things like raw_columns, where_criteria,

6173 # order by, we get this from the new froms.

6174 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6175

6176 self._reset_memoizations()

6177

6178 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6179 return itertools.chain(

6180 super().get_children(

6181 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6182 **kw,

6183 ),

6184 self._iterate_from_elements(),

6185 )

6186

6187 @_generative

6188 def add_columns(

6189 self, *entities: _ColumnsClauseArgument[Any]

6190 ) -> Select[Unpack[TupleAny]]:

6191 r"""Return a new :func:`_expression.select` construct with

6192 the given entities appended to its columns clause.

6193

6194 E.g.::

6195

6196 my_select = my_select.add_columns(table.c.new_column)

6197

6198 The original expressions in the columns clause remain in place.

6199 To replace the original expressions with new ones, see the method

6200 :meth:`_expression.Select.with_only_columns`.

6201

6202 :param \*entities: column, table, or other entity expressions to be

6203 added to the columns clause

6204

6205 .. seealso::

6206

6207 :meth:`_expression.Select.with_only_columns` - replaces existing

6208 expressions rather than appending.

6209

6210 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6211 example

6212

6213 """

6214 self._reset_memoizations()

6215

6216 self._raw_columns = self._raw_columns + [

6217 coercions.expect(

6218 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6219 )

6220 for column in entities

6221 ]

6222 return self

6223

6224 def _set_entities(

6225 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6226 ) -> None:

6227 self._raw_columns = [

6228 coercions.expect(

6229 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6230 )

6231 for ent in util.to_list(entities)

6232 ]

6233

6234 @util.deprecated(

6235 "1.4",

6236 "The :meth:`_expression.Select.column` method is deprecated and will "

6237 "be removed in a future release. Please use "

6238 ":meth:`_expression.Select.add_columns`",

6239 )

6240 def column(

6241 self, column: _ColumnsClauseArgument[Any]

6242 ) -> Select[Unpack[TupleAny]]:

6243 """Return a new :func:`_expression.select` construct with

6244 the given column expression added to its columns clause.

6245

6246 E.g.::

6247

6248 my_select = my_select.column(table.c.new_column)

6249

6250 See the documentation for

6251 :meth:`_expression.Select.with_only_columns`

6252 for guidelines on adding /replacing the columns of a

6253 :class:`_expression.Select` object.

6254

6255 """

6256 return self.add_columns(column)

6257

6258 @util.preload_module("sqlalchemy.sql.util")

6259 def reduce_columns(

6260 self, only_synonyms: bool = True

6261 ) -> Select[Unpack[TupleAny]]:

6262 """Return a new :func:`_expression.select` construct with redundantly

6263 named, equivalently-valued columns removed from the columns clause.

6264

6265 "Redundant" here means two columns where one refers to the

6266 other either based on foreign key, or via a simple equality

6267 comparison in the WHERE clause of the statement. The primary purpose

6268 of this method is to automatically construct a select statement

6269 with all uniquely-named columns, without the need to use

6270 table-qualified labels as

6271 :meth:`_expression.Select.set_label_style`

6272 does.

6273

6274 When columns are omitted based on foreign key, the referred-to

6275 column is the one that's kept. When columns are omitted based on

6276 WHERE equivalence, the first column in the columns clause is the

6277 one that's kept.

6278

6279 :param only_synonyms: when True, limit the removal of columns

6280 to those which have the same name as the equivalent. Otherwise,

6281 all columns that are equivalent to another are removed.

6282

6283 """

6284 woc: Select[Unpack[TupleAny]]

6285 woc = self.with_only_columns(

6286 *util.preloaded.sql_util.reduce_columns(

6287 self._all_selected_columns,

6288 only_synonyms=only_synonyms,

6289 *(self._where_criteria + self._from_obj),

6290 )

6291 )

6292 return woc

6293

6294 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6295

6296 # code within this block is **programmatically,

6297 # statically generated** by tools/generate_tuple_map_overloads.py

6298

6299 @overload

6300 def with_only_columns(

6301 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6302 ) -> Select[_T0]: ...

6303

6304 @overload

6305 def with_only_columns(

6306 self,

6307 __ent0: _TCCA[_T0],

6308 __ent1: _TCCA[_T1],

6309 /,

6310 *,

6311 maintain_column_froms: bool = ...,

6312 ) -> Select[_T0, _T1]: ...

6313

6314 @overload

6315 def with_only_columns(

6316 self,

6317 __ent0: _TCCA[_T0],

6318 __ent1: _TCCA[_T1],

6319 __ent2: _TCCA[_T2],

6320 /,

6321 *,

6322 maintain_column_froms: bool = ...,

6323 ) -> Select[_T0, _T1, _T2]: ...

6324

6325 @overload

6326 def with_only_columns(

6327 self,

6328 __ent0: _TCCA[_T0],

6329 __ent1: _TCCA[_T1],

6330 __ent2: _TCCA[_T2],

6331 __ent3: _TCCA[_T3],

6332 /,

6333 *,

6334 maintain_column_froms: bool = ...,

6335 ) -> Select[_T0, _T1, _T2, _T3]: ...

6336

6337 @overload

6338 def with_only_columns(

6339 self,

6340 __ent0: _TCCA[_T0],

6341 __ent1: _TCCA[_T1],

6342 __ent2: _TCCA[_T2],

6343 __ent3: _TCCA[_T3],

6344 __ent4: _TCCA[_T4],

6345 /,

6346 *,

6347 maintain_column_froms: bool = ...,

6348 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6349

6350 @overload

6351 def with_only_columns(

6352 self,

6353 __ent0: _TCCA[_T0],

6354 __ent1: _TCCA[_T1],

6355 __ent2: _TCCA[_T2],

6356 __ent3: _TCCA[_T3],

6357 __ent4: _TCCA[_T4],

6358 __ent5: _TCCA[_T5],

6359 /,

6360 *,

6361 maintain_column_froms: bool = ...,

6362 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6363

6364 @overload

6365 def with_only_columns(

6366 self,

6367 __ent0: _TCCA[_T0],

6368 __ent1: _TCCA[_T1],

6369 __ent2: _TCCA[_T2],

6370 __ent3: _TCCA[_T3],

6371 __ent4: _TCCA[_T4],

6372 __ent5: _TCCA[_T5],

6373 __ent6: _TCCA[_T6],

6374 /,

6375 *,

6376 maintain_column_froms: bool = ...,

6377 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6378

6379 @overload

6380 def with_only_columns(

6381 self,

6382 __ent0: _TCCA[_T0],

6383 __ent1: _TCCA[_T1],

6384 __ent2: _TCCA[_T2],

6385 __ent3: _TCCA[_T3],

6386 __ent4: _TCCA[_T4],

6387 __ent5: _TCCA[_T5],

6388 __ent6: _TCCA[_T6],

6389 __ent7: _TCCA[_T7],

6390 /,

6391 *entities: _ColumnsClauseArgument[Any],

6392 maintain_column_froms: bool = ...,

6393 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6394

6395 # END OVERLOADED FUNCTIONS self.with_only_columns

6396

6397 @overload

6398 def with_only_columns(

6399 self,

6400 *entities: _ColumnsClauseArgument[Any],

6401 maintain_column_froms: bool = False,

6402 **__kw: Any,

6403 ) -> Select[Unpack[TupleAny]]: ...

6404

6405 @_generative

6406 def with_only_columns(

6407 self,

6408 *entities: _ColumnsClauseArgument[Any],

6409 maintain_column_froms: bool = False,

6410 **__kw: Any,

6411 ) -> Select[Unpack[TupleAny]]:

6412 r"""Return a new :func:`_expression.select` construct with its columns

6413 clause replaced with the given entities.

6414

6415 By default, this method is exactly equivalent to as if the original

6416 :func:`_expression.select` had been called with the given entities.

6417 E.g. a statement::

6418

6419 s = select(table1.c.a, table1.c.b)

6420 s = s.with_only_columns(table1.c.b)

6421

6422 should be exactly equivalent to::

6423

6424 s = select(table1.c.b)

6425

6426 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6427 will also dynamically alter the FROM clause of the

6428 statement if it is not explicitly stated.

6429 To maintain the existing set of FROMs including those implied by the

6430 current columns clause, add the

6431 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6432 parameter::

6433

6434 s = select(table1.c.a, table2.c.b)

6435 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6436

6437 The above parameter performs a transfer of the effective FROMs

6438 in the columns collection to the :meth:`_sql.Select.select_from`

6439 method, as though the following were invoked::

6440

6441 s = select(table1.c.a, table2.c.b)

6442 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6443

6444 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6445 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6446 collection and performs an operation equivalent to the following::

6447

6448 s = select(table1.c.a, table2.c.b)

6449 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6450

6451 :param \*entities: column expressions to be used.

6452

6453 :param maintain_column_froms: boolean parameter that will ensure the

6454 FROM list implied from the current columns clause will be transferred

6455 to the :meth:`_sql.Select.select_from` method first.

6456

6457 .. versionadded:: 1.4.23

6458

6459 """ # noqa: E501

6460

6461 if __kw:

6462 raise _no_kw()

6463

6464 # memoizations should be cleared here as of

6465 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6466 # is the case for now.

6467 self._assert_no_memoizations()

6468

6469 if maintain_column_froms:

6470 self.select_from.non_generative( # type: ignore[attr-defined]

6471 self, *self.columns_clause_froms

6472 )

6473

6474 # then memoize the FROMs etc.

6475 _MemoizedSelectEntities._generate_for_statement(self)

6476

6477 self._raw_columns = [

6478 coercions.expect(roles.ColumnsClauseRole, c)

6479 for c in coercions._expression_collection_was_a_list(

6480 "entities", "Select.with_only_columns", entities

6481 )

6482 ]

6483 return self

6484

6485 @property

6486 def whereclause(self) -> Optional[ColumnElement[Any]]:

6487 """Return the completed WHERE clause for this

6488 :class:`_expression.Select` statement.

6489

6490 This assembles the current collection of WHERE criteria

6491 into a single :class:`_expression.BooleanClauseList` construct.

6492

6493

6494 .. versionadded:: 1.4

6495

6496 """

6497

6498 return BooleanClauseList._construct_for_whereclause(

6499 self._where_criteria

6500 )

6501

6502 _whereclause = whereclause

6503

6504 @_generative

6505 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6506 """Return a new :func:`_expression.select` construct with

6507 the given expression added to

6508 its WHERE clause, joined to the existing clause via AND, if any.

6509

6510 """

6511

6512 assert isinstance(self._where_criteria, tuple)

6513

6514 for criterion in whereclause:

6515 where_criteria: ColumnElement[Any] = coercions.expect(

6516 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6517 )

6518 self._where_criteria += (where_criteria,)

6519 return self

6520

6521 @_generative

6522 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6523 """Return a new :func:`_expression.select` construct with

6524 the given expression added to

6525 its HAVING clause, joined to the existing clause via AND, if any.

6526

6527 """

6528

6529 for criterion in having:

6530 having_criteria = coercions.expect(

6531 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6532 )

6533 self._having_criteria += (having_criteria,)

6534 return self

6535

6536 @_generative

6537 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6538 r"""Return a new :func:`_expression.select` construct which

6539 will apply DISTINCT to the SELECT statement overall.

6540

6541 E.g.::

6542

6543 from sqlalchemy import select

6544

6545 stmt = select(users_table.c.id, users_table.c.name).distinct()

6546

6547 The above would produce an statement resembling:

6548

6549 .. sourcecode:: sql

6550

6551 SELECT DISTINCT user.id, user.name FROM user

6552

6553 The method also historically accepted an ``*expr`` parameter which

6554 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6555 This is now replaced using the :func:`_postgresql.distinct_on`

6556 extension::

6557

6558 from sqlalchemy import select

6559 from sqlalchemy.dialects.postgresql import distinct_on

6560

6561 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6562

6563 Using this parameter on other backends which don't support this

6564 syntax will raise an error.

6565

6566 :param \*expr: optional column expressions. When present,

6567 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6568 construct. A deprecation warning and/or :class:`_exc.CompileError`

6569 will be raised on other backends.

6570

6571 .. deprecated:: 2.1 Passing expressions to

6572 :meth:`_sql.Select.distinct` is deprecated, use

6573 :func:`_postgresql.distinct_on` instead.

6574

6575 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6576 and will raise :class:`_exc.CompileError` in a future version.

6577

6578 .. seealso::

6579

6580 :func:`_postgresql.distinct_on`

6581

6582 :meth:`.ext`

6583 """

6584 self._distinct = True

6585 if expr:

6586 warn_deprecated(

6587 "Passing expression to ``distinct`` to generate a "

6588 "DISTINCT ON clause is deprecated. Use instead the "

6589 "``postgresql.distinct_on`` function as an extension.",

6590 "2.1",

6591 )

6592 self._distinct_on = self._distinct_on + tuple(

6593 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6594 for e in expr

6595 )

6596 return self

6597

6598 @_generative

6599 def select_from(self, *froms: _FromClauseArgument) -> Self:

6600 r"""Return a new :func:`_expression.select` construct with the

6601 given FROM expression(s)

6602 merged into its list of FROM objects.

6603

6604 E.g.::

6605

6606 table1 = table("t1", column("a"))

6607 table2 = table("t2", column("b"))

6608 s = select(table1.c.a).select_from(

6609 table1.join(table2, table1.c.a == table2.c.b)

6610 )

6611

6612 The "from" list is a unique set on the identity of each element,

6613 so adding an already present :class:`_schema.Table`

6614 or other selectable

6615 will have no effect. Passing a :class:`_expression.Join` that refers

6616 to an already present :class:`_schema.Table`

6617 or other selectable will have

6618 the effect of concealing the presence of that selectable as

6619 an individual element in the rendered FROM list, instead

6620 rendering it into a JOIN clause.

6621

6622 While the typical purpose of :meth:`_expression.Select.select_from`

6623 is to

6624 replace the default, derived FROM clause with a join, it can

6625 also be called with individual table elements, multiple times

6626 if desired, in the case that the FROM clause cannot be fully

6627 derived from the columns clause::

6628

6629 select(func.count("*")).select_from(table1)

6630

6631 """

6632

6633 self._from_obj += tuple(

6634 coercions.expect(

6635 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6636 )

6637 for fromclause in froms

6638 )

6639 return self

6640

6641 @_generative

6642 def correlate(

6643 self,

6644 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6645 ) -> Self:

6646 r"""Return a new :class:`_expression.Select`

6647 which will correlate the given FROM

6648 clauses to that of an enclosing :class:`_expression.Select`.

6649

6650 Calling this method turns off the :class:`_expression.Select` object's

6651 default behavior of "auto-correlation". Normally, FROM elements

6652 which appear in a :class:`_expression.Select`

6653 that encloses this one via

6654 its :term:`WHERE clause`, ORDER BY, HAVING or

6655 :term:`columns clause` will be omitted from this

6656 :class:`_expression.Select`

6657 object's :term:`FROM clause`.

6658 Setting an explicit correlation collection using the

6659 :meth:`_expression.Select.correlate`

6660 method provides a fixed list of FROM objects

6661 that can potentially take place in this process.

6662

6663 When :meth:`_expression.Select.correlate`

6664 is used to apply specific FROM clauses

6665 for correlation, the FROM elements become candidates for

6666 correlation regardless of how deeply nested this

6667 :class:`_expression.Select`

6668 object is, relative to an enclosing :class:`_expression.Select`

6669 which refers to

6670 the same FROM object. This is in contrast to the behavior of

6671 "auto-correlation" which only correlates to an immediate enclosing

6672 :class:`_expression.Select`.

6673 Multi-level correlation ensures that the link

6674 between enclosed and enclosing :class:`_expression.Select`

6675 is always via

6676 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6677 correlation to take place.

6678

6679 If ``None`` is passed, the :class:`_expression.Select`

6680 object will correlate

6681 none of its FROM entries, and all will render unconditionally

6682 in the local FROM clause.

6683

6684 :param \*fromclauses: one or more :class:`.FromClause` or other

6685 FROM-compatible construct such as an ORM mapped entity to become part

6686 of the correlate collection; alternatively pass a single value

6687 ``None`` to remove all existing correlations.

6688

6689 .. seealso::

6690

6691 :meth:`_expression.Select.correlate_except`

6692

6693 :ref:`tutorial_scalar_subquery`

6694

6695 """

6696

6697 # tests failing when we try to change how these

6698 # arguments are passed

6699

6700 self._auto_correlate = False

6701 if not fromclauses or fromclauses[0] in {None, False}:

6702 if len(fromclauses) > 1:

6703 raise exc.ArgumentError(

6704 "additional FROM objects not accepted when "

6705 "passing None/False to correlate()"

6706 )

6707 self._correlate = ()

6708 else:

6709 self._correlate = self._correlate + tuple(

6710 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6711 )

6712 return self

6713

6714 @_generative

6715 def correlate_except(

6716 self,

6717 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6718 ) -> Self:

6719 r"""Return a new :class:`_expression.Select`

6720 which will omit the given FROM

6721 clauses from the auto-correlation process.

6722

6723 Calling :meth:`_expression.Select.correlate_except` turns off the

6724 :class:`_expression.Select` object's default behavior of

6725 "auto-correlation" for the given FROM elements. An element

6726 specified here will unconditionally appear in the FROM list, while

6727 all other FROM elements remain subject to normal auto-correlation

6728 behaviors.

6729

6730 If ``None`` is passed, or no arguments are passed,

6731 the :class:`_expression.Select` object will correlate all of its

6732 FROM entries.

6733

6734 :param \*fromclauses: a list of one or more

6735 :class:`_expression.FromClause`

6736 constructs, or other compatible constructs (i.e. ORM-mapped

6737 classes) to become part of the correlate-exception collection.

6738

6739 .. seealso::

6740

6741 :meth:`_expression.Select.correlate`

6742

6743 :ref:`tutorial_scalar_subquery`

6744

6745 """

6746

6747 self._auto_correlate = False

6748 if not fromclauses or fromclauses[0] in {None, False}:

6749 if len(fromclauses) > 1:

6750 raise exc.ArgumentError(

6751 "additional FROM objects not accepted when "

6752 "passing None/False to correlate_except()"

6753 )

6754 self._correlate_except = ()

6755 else:

6756 self._correlate_except = (self._correlate_except or ()) + tuple(

6757 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6758 )

6759

6760 return self

6761

6762 @HasMemoized_ro_memoized_attribute

6763 def selected_columns(

6764 self,

6765 ) -> ColumnCollection[str, ColumnElement[Any]]:

6766 """A :class:`_expression.ColumnCollection`

6767 representing the columns that

6768 this SELECT statement or similar construct returns in its result set,

6769 not including :class:`_sql.TextClause` constructs.

6770

6771 This collection differs from the :attr:`_expression.FromClause.columns`

6772 collection of a :class:`_expression.FromClause` in that the columns

6773 within this collection cannot be directly nested inside another SELECT

6774 statement; a subquery must be applied first which provides for the

6775 necessary parenthesization required by SQL.

6776

6777 For a :func:`_expression.select` construct, the collection here is

6778 exactly what would be rendered inside the "SELECT" statement, and the

6779 :class:`_expression.ColumnElement` objects are directly present as they

6780 were given, e.g.::

6781

6782 col1 = column("q", Integer)

6783 col2 = column("p", Integer)

6784 stmt = select(col1, col2)

6785

6786 Above, ``stmt.selected_columns`` would be a collection that contains

6787 the ``col1`` and ``col2`` objects directly. For a statement that is

6788 against a :class:`_schema.Table` or other

6789 :class:`_expression.FromClause`, the collection will use the

6790 :class:`_expression.ColumnElement` objects that are in the

6791 :attr:`_expression.FromClause.c` collection of the from element.

6792

6793 A use case for the :attr:`_sql.Select.selected_columns` collection is

6794 to allow the existing columns to be referenced when adding additional

6795 criteria, e.g.::

6796

6797 def filter_on_id(my_select, id):

6798 return my_select.where(my_select.selected_columns["id"] == id)

6799

6800

6801 stmt = select(MyModel)

6802

6803 # adds "WHERE id=:param" to the statement

6804 stmt = filter_on_id(stmt, 42)

6805

6806 .. note::

6807

6808 The :attr:`_sql.Select.selected_columns` collection does not

6809 include expressions established in the columns clause using the

6810 :func:`_sql.text` construct; these are silently omitted from the

6811 collection. To use plain textual column expressions inside of a

6812 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6813 construct.

6814

6815

6816 .. versionadded:: 1.4

6817

6818 """

6819

6820 # compare to SelectState._generate_columns_plus_names, which

6821 # generates the actual names used in the SELECT string. that

6822 # method is more complex because it also renders columns that are

6823 # fully ambiguous, e.g. same column more than once.

6824 conv = cast(

6825 "Callable[[Any], str]",

6826 SelectState._column_naming_convention(self._label_style),

6827 )

6828

6829 cc: WriteableColumnCollection[str, ColumnElement[Any]] = (

6830 WriteableColumnCollection(

6831 [

6832 (conv(c), c)

6833 for c in self._all_selected_columns

6834 if is_column_element(c)

6835 ]

6836 )

6837 )

6838 return cc.as_readonly()

6839

6840 @HasMemoized_ro_memoized_attribute

6841 def _all_selected_columns(self) -> _SelectIterable:

6842 meth = SelectState.get_plugin_class(self).all_selected_columns

6843 return list(meth(self))

6844

6845 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6846 if self._label_style is LABEL_STYLE_NONE:

6847 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6848 return self

6849

6850 def _generate_fromclause_column_proxies(

6851 self,

6852 subquery: FromClause,

6853 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

6854 primary_key: ColumnSet,

6855 foreign_keys: Set[KeyedColumnElement[Any]],

6856 *,

6857 proxy_compound_columns: Optional[

6858 Iterable[Sequence[ColumnElement[Any]]]

6859 ] = None,

6860 ) -> None:

6861 """Generate column proxies to place in the exported ``.c``

6862 collection of a subquery."""

6863

6864 if proxy_compound_columns:

6865 extra_col_iterator = proxy_compound_columns

6866 prox = [

6867 c._make_proxy(

6868 subquery,

6869 key=proxy_key,

6870 name=required_label_name,

6871 name_is_truncatable=True,

6872 compound_select_cols=extra_cols,

6873 primary_key=primary_key,

6874 foreign_keys=foreign_keys,

6875 )

6876 for (

6877 (

6878 required_label_name,

6879 proxy_key,

6880 fallback_label_name,

6881 c,

6882 repeated,

6883 ),

6884 extra_cols,

6885 ) in (

6886 zip(

6887 self._generate_columns_plus_names(False),

6888 extra_col_iterator,

6889 )

6890 )

6891 if is_column_element(c)

6892 ]

6893 else:

6894 prox = [

6895 c._make_proxy(

6896 subquery,

6897 key=proxy_key,

6898 name=required_label_name,

6899 name_is_truncatable=True,

6900 primary_key=primary_key,

6901 foreign_keys=foreign_keys,

6902 )

6903 for (

6904 required_label_name,

6905 proxy_key,

6906 fallback_label_name,

6907 c,

6908 repeated,

6909 ) in (self._generate_columns_plus_names(False))

6910 if is_column_element(c)

6911 ]

6912

6913 columns._populate_separate_keys(prox)

6914

6915 def _needs_parens_for_grouping(self) -> bool:

6916 return self._has_row_limiting_clause or bool(

6917 self._order_by_clause.clauses

6918 )

6919

6920 def self_group(

6921 self, against: Optional[OperatorType] = None

6922 ) -> Union[SelectStatementGrouping[Self], Self]:

6923 """Return a 'grouping' construct as per the

6924 :class:`_expression.ClauseElement` specification.

6925

6926 This produces an element that can be embedded in an expression. Note

6927 that this method is called automatically as needed when constructing

6928 expressions and should not require explicit use.

6929

6930 """

6931 if (

6932 isinstance(against, CompoundSelect)

6933 and not self._needs_parens_for_grouping()

6934 ):

6935 return self

6936 else:

6937 return SelectStatementGrouping(self)

6938

6939 def union(

6940 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6941 ) -> CompoundSelect[Unpack[_Ts]]:

6942 r"""Return a SQL ``UNION`` of this select() construct against

6943 the given selectables provided as positional arguments.

6944

6945 :param \*other: one or more elements with which to create a

6946 UNION.

6947

6948 .. versionchanged:: 1.4.28

6949

6950 multiple elements are now accepted.

6951

6952 :param \**kwargs: keyword arguments are forwarded to the constructor

6953 for the newly created :class:`_sql.CompoundSelect` object.

6954

6955 """

6956 return CompoundSelect._create_union(self, *other)

6957

6958 def union_all(

6959 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6960 ) -> CompoundSelect[Unpack[_Ts]]:

6961 r"""Return a SQL ``UNION ALL`` of this select() construct against

6962 the given selectables provided as positional arguments.

6963

6964 :param \*other: one or more elements with which to create a

6965 UNION.

6966

6967 .. versionchanged:: 1.4.28

6968

6969 multiple elements are now accepted.

6970

6971 :param \**kwargs: keyword arguments are forwarded to the constructor

6972 for the newly created :class:`_sql.CompoundSelect` object.

6973

6974 """

6975 return CompoundSelect._create_union_all(self, *other)

6976

6977 def except_(

6978 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6979 ) -> CompoundSelect[Unpack[_Ts]]:

6980 r"""Return a SQL ``EXCEPT`` of this select() construct against

6981 the given selectable provided as positional arguments.

6982

6983 :param \*other: one or more elements with which to create a

6984 UNION.

6985

6986 .. versionchanged:: 1.4.28

6987

6988 multiple elements are now accepted.

6989

6990 """

6991 return CompoundSelect._create_except(self, *other)

6992

6993 def except_all(

6994 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6995 ) -> CompoundSelect[Unpack[_Ts]]:

6996 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6997 the given selectables provided as positional arguments.

6998

6999 :param \*other: one or more elements with which to create a

7000 UNION.

7001

7002 .. versionchanged:: 1.4.28

7003

7004 multiple elements are now accepted.

7005

7006 """

7007 return CompoundSelect._create_except_all(self, *other)

7008

7009 def intersect(

7010 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7011 ) -> CompoundSelect[Unpack[_Ts]]:

7012 r"""Return a SQL ``INTERSECT`` of this select() construct against

7013 the given selectables provided as positional arguments.

7014

7015 :param \*other: one or more elements with which to create a

7016 UNION.

7017

7018 .. versionchanged:: 1.4.28

7019

7020 multiple elements are now accepted.

7021

7022 :param \**kwargs: keyword arguments are forwarded to the constructor

7023 for the newly created :class:`_sql.CompoundSelect` object.

7024

7025 """

7026 return CompoundSelect._create_intersect(self, *other)

7027

7028 def intersect_all(

7029 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7030 ) -> CompoundSelect[Unpack[_Ts]]:

7031 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

7032 against the given selectables provided as positional arguments.

7033

7034 :param \*other: one or more elements with which to create a

7035 UNION.

7036

7037 .. versionchanged:: 1.4.28

7038

7039 multiple elements are now accepted.

7040

7041 :param \**kwargs: keyword arguments are forwarded to the constructor

7042 for the newly created :class:`_sql.CompoundSelect` object.

7043

7044 """

7045 return CompoundSelect._create_intersect_all(self, *other)

7046

7047

7048class ScalarSelect(

7049 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

7050):

7051 """Represent a scalar subquery.

7052

7053

7054 A :class:`_sql.ScalarSelect` is created by invoking the

7055 :meth:`_sql.SelectBase.scalar_subquery` method. The object

7056 then participates in other SQL expressions as a SQL column expression

7057 within the :class:`_sql.ColumnElement` hierarchy.

7058

7059 .. seealso::

7060

7061 :meth:`_sql.SelectBase.scalar_subquery`

7062

7063 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7064

7065 """

7066

7067 _traverse_internals: _TraverseInternalsType = [

7068 ("element", InternalTraversal.dp_clauseelement),

7069 ("type", InternalTraversal.dp_type),

7070 ]

7071

7072 _from_objects: List[FromClause] = []

7073 _is_from_container = True

7074 if not TYPE_CHECKING:

7075 _is_implicitly_boolean = False

7076 inherit_cache = True

7077

7078 element: SelectBase

7079

7080 def __init__(self, element: SelectBase) -> None:

7081 self.element = element

7082 self.type = element._scalar_type()

7083 self._propagate_attrs = element._propagate_attrs

7084

7085 def __getattr__(self, attr: str) -> Any:

7086 return getattr(self.element, attr)

7087

7088 def __getstate__(self) -> Dict[str, Any]:

7089 return {"element": self.element, "type": self.type}

7090

7091 def __setstate__(self, state: Dict[str, Any]) -> None:

7092 self.element = state["element"]

7093 self.type = state["type"]

7094

7095 @property

7096 def columns(self) -> NoReturn:

7097 raise exc.InvalidRequestError(

7098 "Scalar Select expression has no "

7099 "columns; use this object directly "

7100 "within a column-level expression."

7101 )

7102

7103 c = columns

7104

7105 @_generative

7106 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7107 """Apply a WHERE clause to the SELECT statement referred to

7108 by this :class:`_expression.ScalarSelect`.

7109

7110 """

7111 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7112 crit

7113 )

7114 return self

7115

7116 def self_group(self, against: Optional[OperatorType] = None) -> Self:

7117 return self

7118

7119 def _ungroup(self) -> Self:

7120 return self

7121

7122 @_generative

7123 def correlate(

7124 self,

7125 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7126 ) -> Self:

7127 r"""Return a new :class:`_expression.ScalarSelect`

7128 which will correlate the given FROM

7129 clauses to that of an enclosing :class:`_expression.Select`.

7130

7131 This method is mirrored from the :meth:`_sql.Select.correlate` method

7132 of the underlying :class:`_sql.Select`. The method applies the

7133 :meth:_sql.Select.correlate` method, then returns a new

7134 :class:`_sql.ScalarSelect` against that statement.

7135

7136 .. versionadded:: 1.4 Previously, the

7137 :meth:`_sql.ScalarSelect.correlate`

7138 method was only available from :class:`_sql.Select`.

7139

7140 :param \*fromclauses: a list of one or more

7141 :class:`_expression.FromClause`

7142 constructs, or other compatible constructs (i.e. ORM-mapped

7143 classes) to become part of the correlate collection.

7144

7145 .. seealso::

7146

7147 :meth:`_expression.ScalarSelect.correlate_except`

7148

7149 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7150

7151

7152 """

7153 self.element = cast(

7154 "Select[Unpack[TupleAny]]", self.element

7155 ).correlate(*fromclauses)

7156 return self

7157

7158 @_generative

7159 def correlate_except(

7160 self,

7161 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7162 ) -> Self:

7163 r"""Return a new :class:`_expression.ScalarSelect`

7164 which will omit the given FROM

7165 clauses from the auto-correlation process.

7166

7167 This method is mirrored from the

7168 :meth:`_sql.Select.correlate_except` method of the underlying

7169 :class:`_sql.Select`. The method applies the

7170 :meth:_sql.Select.correlate_except` method, then returns a new

7171 :class:`_sql.ScalarSelect` against that statement.

7172

7173 .. versionadded:: 1.4 Previously, the

7174 :meth:`_sql.ScalarSelect.correlate_except`

7175 method was only available from :class:`_sql.Select`.

7176

7177 :param \*fromclauses: a list of one or more

7178 :class:`_expression.FromClause`

7179 constructs, or other compatible constructs (i.e. ORM-mapped

7180 classes) to become part of the correlate-exception collection.

7181

7182 .. seealso::

7183

7184 :meth:`_expression.ScalarSelect.correlate`

7185

7186 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7187

7188

7189 """

7190

7191 self.element = cast(

7192 "Select[Unpack[TupleAny]]", self.element

7193 ).correlate_except(*fromclauses)

7194 return self

7195

7196

7197class Exists(UnaryExpression[bool]):

7198 """Represent an ``EXISTS`` clause.

7199

7200 See :func:`_sql.exists` for a description of usage.

7201

7202 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7203 instance by calling :meth:`_sql.SelectBase.exists`.

7204

7205 """

7206

7207 inherit_cache = True

7208

7209 def __init__(

7210 self,

7211 __argument: Optional[

7212 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7213 ] = None,

7214 /,

7215 ):

7216 s: ScalarSelect[Any]

7217

7218 # TODO: this seems like we should be using coercions for this

7219 if __argument is None:

7220 s = Select(literal_column("*")).scalar_subquery()

7221 elif isinstance(__argument, SelectBase):

7222 s = __argument.scalar_subquery()

7223 s._propagate_attrs = __argument._propagate_attrs

7224 elif isinstance(__argument, ScalarSelect):

7225 s = __argument

7226 else:

7227 s = Select(__argument).scalar_subquery()

7228

7229 UnaryExpression.__init__(

7230 self,

7231 s,

7232 operator=operators.exists,

7233 type_=type_api.BOOLEANTYPE,

7234 )

7235

7236 @util.ro_non_memoized_property

7237 def _from_objects(self) -> List[FromClause]:

7238 return []

7239

7240 def _regroup(

7241 self,

7242 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7243 ) -> ScalarSelect[Any]:

7244

7245 assert isinstance(self.element, ScalarSelect)

7246 element = self.element.element

7247 if not isinstance(element, Select):

7248 raise exc.InvalidRequestError(

7249 "Can only apply this operation to a plain SELECT construct"

7250 )

7251 new_element = fn(element)

7252

7253 return_value = new_element.scalar_subquery()

7254 return return_value

7255

7256 def select(self) -> Select[bool]:

7257 r"""Return a SELECT of this :class:`_expression.Exists`.

7258

7259 e.g.::

7260

7261 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7262

7263 This will produce a statement resembling:

7264

7265 .. sourcecode:: sql

7266

7267 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7268

7269 .. seealso::

7270

7271 :func:`_expression.select` - general purpose

7272 method which allows for arbitrary column lists.

7273

7274 """ # noqa

7275

7276 return Select(self)

7277

7278 def correlate(

7279 self,

7280 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7281 ) -> Self:

7282 """Apply correlation to the subquery noted by this

7283 :class:`_sql.Exists`.

7284

7285 .. seealso::

7286

7287 :meth:`_sql.ScalarSelect.correlate`

7288

7289 """

7290 e = self._clone()

7291 e.element = self._regroup(

7292 lambda element: element.correlate(*fromclauses)

7293 )

7294 return e

7295

7296 def correlate_except(

7297 self,

7298 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7299 ) -> Self:

7300 """Apply correlation to the subquery noted by this

7301 :class:`_sql.Exists`.

7302

7303 .. seealso::

7304

7305 :meth:`_sql.ScalarSelect.correlate_except`

7306

7307 """

7308 e = self._clone()

7309 e.element = self._regroup(

7310 lambda element: element.correlate_except(*fromclauses)

7311 )

7312 return e

7313

7314 def select_from(self, *froms: _FromClauseArgument) -> Self:

7315 """Return a new :class:`_expression.Exists` construct,

7316 applying the given

7317 expression to the :meth:`_expression.Select.select_from`

7318 method of the select

7319 statement contained.

7320

7321 .. note:: it is typically preferable to build a :class:`_sql.Select`

7322 statement first, including the desired WHERE clause, then use the

7323 :meth:`_sql.SelectBase.exists` method to produce an

7324 :class:`_sql.Exists` object at once.

7325

7326 """

7327 e = self._clone()

7328 e.element = self._regroup(lambda element: element.select_from(*froms))

7329 return e

7330

7331 def with_hint(

7332 self,

7333 selectable: _FromClauseArgument,

7334 text: str,

7335 dialect_name: str = "*",

7336 ) -> Self:

7337 r"""Return a new :class:`_expression.Exists` construct, applying

7338 the given arguments to the

7339 :meth:`_expression.Select.with_hint` method of the select

7340 statement contained.

7341

7342 The hint is therefore rendered against the SELECT that's enclosed

7343 by the EXISTS expression. This includes :class:`_expression.Exists`

7344 objects that were generated by ORM constructs such as

7345 :meth:`_orm.PropComparator.any` and

7346 :meth:`_orm.PropComparator.has`::

7347

7348 stmt = select(User).where(

7349 User.addresses.any().with_hint(

7350 Address.__table__, "WITH (NOLOCK)", "mssql"

7351 )

7352 )

7353

7354 The above statement renders as:

7355

7356 .. sourcecode:: sql

7357

7358 SELECT users.id, users.name

7359 FROM users

7360 WHERE EXISTS (SELECT 1

7361 FROM addresses WITH (NOLOCK)

7362 WHERE users.id = addresses.user_id)

7363

7364 .. versionadded:: 2.1

7365

7366 .. seealso::

7367

7368 :meth:`_expression.Select.with_hint`

7369

7370 :meth:`_expression.Exists.with_statement_hint`

7371

7372 """

7373 e = self._clone()

7374 e.element = self._regroup(

7375 lambda element: element.with_hint(selectable, text, dialect_name)

7376 )

7377 return e

7378

7379 def with_statement_hint(self, text: str, dialect_name: str = "*") -> Self:

7380 r"""Return a new :class:`_expression.Exists` construct, applying

7381 the given arguments to the

7382 :meth:`_expression.Select.with_statement_hint` method of the select

7383 statement contained.

7384

7385 The hint is therefore rendered at the trailing end of the SELECT

7386 that's enclosed by the EXISTS expression, rather than at the end of

7387 the enclosing statement. As with

7388 :meth:`_expression.Exists.with_hint`, this includes

7389 :class:`_expression.Exists` objects that were generated by ORM

7390 constructs such as :meth:`_orm.PropComparator.any` and

7391 :meth:`_orm.PropComparator.has`::

7392

7393 has_address = User.addresses.any().with_statement_hint(

7394 "WITH (NOLOCK)", "mssql"

7395 )

7396 stmt = select(User).where(has_address)

7397

7398 The above statement renders as:

7399

7400 .. sourcecode:: sql

7401

7402 SELECT users.id, users.name

7403 FROM users

7404 WHERE EXISTS (SELECT 1

7405 FROM addresses

7406 WHERE users.id = addresses.user_id WITH (NOLOCK))

7407

7408 .. versionadded:: 2.1

7409

7410 .. seealso::

7411

7412 :meth:`_expression.Select.with_statement_hint`

7413

7414 :meth:`_expression.Exists.with_hint`

7415

7416 """

7417 e = self._clone()

7418 e.element = self._regroup(

7419 lambda element: element.with_statement_hint(text, dialect_name)

7420 )

7421 return e

7422

7423 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7424 """Return a new :func:`_expression.exists` construct with the

7425 given expression added to

7426 its WHERE clause, joined to the existing clause via AND, if any.

7427

7428

7429 .. note:: it is typically preferable to build a :class:`_sql.Select`

7430 statement first, including the desired WHERE clause, then use the

7431 :meth:`_sql.SelectBase.exists` method to produce an

7432 :class:`_sql.Exists` object at once.

7433

7434 """

7435 e = self._clone()

7436 e.element = self._regroup(lambda element: element.where(*clause))

7437 return e

7438

7439

7440class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7441 """Wrap a :class:`_expression.TextClause` construct within a

7442 :class:`_expression.SelectBase`

7443 interface.

7444

7445 This allows the :class:`_expression.TextClause` object to gain a

7446 ``.c`` collection

7447 and other FROM-like capabilities such as

7448 :meth:`_expression.FromClause.alias`,

7449 :meth:`_expression.SelectBase.cte`, etc.

7450

7451 The :class:`_expression.TextualSelect` construct is produced via the

7452 :meth:`_expression.TextClause.columns`

7453 method - see that method for details.

7454

7455 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7456 class was renamed

7457 from ``TextAsFrom``, to more correctly suit its role as a

7458 SELECT-oriented object and not a FROM clause.

7459

7460 .. seealso::

7461

7462 :func:`_expression.text`

7463

7464 :meth:`_expression.TextClause.columns` - primary creation interface.

7465

7466 """

7467

7468 __visit_name__ = "textual_select"

7469

7470 _label_style = LABEL_STYLE_NONE

7471

7472 _traverse_internals: _TraverseInternalsType = (

7473 [

7474 ("element", InternalTraversal.dp_clauseelement),

7475 ("column_args", InternalTraversal.dp_clauseelement_list),

7476 ]

7477 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7478 + HasCTE._has_ctes_traverse_internals

7479 + ExecutableStatement._executable_traverse_internals

7480 )

7481

7482 _is_textual = True

7483

7484 is_text = True

7485 is_select = True

7486

7487 def __init__(

7488 self,

7489 text: TextClause,

7490 columns: List[_ColumnExpressionArgument[Any]],

7491 positional: bool = False,

7492 ) -> None:

7493 self._init(

7494 text,

7495 # convert for ORM attributes->columns, etc

7496 [

7497 coercions.expect(roles.LabeledColumnExprRole, c)

7498 for c in columns

7499 ],

7500 positional,

7501 )

7502

7503 def _init(

7504 self,

7505 text: AbstractTextClause,

7506 columns: List[NamedColumn[Any]],

7507 positional: bool = False,

7508 ) -> None:

7509 self.element = text

7510 self.column_args = columns

7511 self.positional = positional

7512

7513 @HasMemoized_ro_memoized_attribute

7514 def selected_columns(

7515 self,

7516 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7517 """A :class:`_expression.ColumnCollection`

7518 representing the columns that

7519 this SELECT statement or similar construct returns in its result set,

7520 not including :class:`_sql.TextClause` constructs.

7521

7522 This collection differs from the :attr:`_expression.FromClause.columns`

7523 collection of a :class:`_expression.FromClause` in that the columns

7524 within this collection cannot be directly nested inside another SELECT

7525 statement; a subquery must be applied first which provides for the

7526 necessary parenthesization required by SQL.

7527

7528 For a :class:`_expression.TextualSelect` construct, the collection

7529 contains the :class:`_expression.ColumnElement` objects that were

7530 passed to the constructor, typically via the

7531 :meth:`_expression.TextClause.columns` method.

7532

7533

7534 .. versionadded:: 1.4

7535

7536 """

7537 return WriteableColumnCollection(

7538 (c.key, c) for c in self.column_args

7539 ).as_readonly()

7540

7541 @util.ro_non_memoized_property

7542 def _all_selected_columns(self) -> _SelectIterable:

7543 return self.column_args

7544

7545 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7546 return self

7547

7548 def _ensure_disambiguated_names(self) -> TextualSelect:

7549 return self

7550

7551 @_generative

7552 def bindparams(

7553 self,

7554 *binds: BindParameter[Any],

7555 **bind_as_values: Any,

7556 ) -> Self:

7557 self.element = self.element.bindparams(*binds, **bind_as_values)

7558 return self

7559

7560 def _generate_fromclause_column_proxies(

7561 self,

7562 fromclause: FromClause,

7563 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

7564 primary_key: ColumnSet,

7565 foreign_keys: Set[KeyedColumnElement[Any]],

7566 *,

7567 proxy_compound_columns: Optional[

7568 Iterable[Sequence[ColumnElement[Any]]]

7569 ] = None,

7570 ) -> None:

7571 if TYPE_CHECKING:

7572 assert isinstance(fromclause, Subquery)

7573

7574 if proxy_compound_columns:

7575 columns._populate_separate_keys(

7576 c._make_proxy(

7577 fromclause,

7578 compound_select_cols=extra_cols,

7579 primary_key=primary_key,

7580 foreign_keys=foreign_keys,

7581 )

7582 for c, extra_cols in zip(

7583 self.column_args, proxy_compound_columns

7584 )

7585 )

7586 else:

7587 columns._populate_separate_keys(

7588 c._make_proxy(

7589 fromclause,

7590 primary_key=primary_key,

7591 foreign_keys=foreign_keys,

7592 )

7593 for c in self.column_args

7594 )

7595

7596 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7597 return self.column_args[0].type

7598

7599

7600TextAsFrom = TextualSelect

7601"""Backwards compatibility with the previous name"""

7602

7603

7604class AnnotatedFromClause(Annotated):

7605 def _copy_internals(

7606 self,

7607 _annotations_traversal: bool = False,

7608 ind_cols_on_fromclause: bool = False,

7609 **kw: Any,

7610 ) -> None:

7611 super()._copy_internals(**kw)

7612

7613 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7614 # the traversals used by annotations for these cases are not currently

7615 # designed around expecting that inner elements inside of

7616 # AnnotatedFromClause's element are also deep copied, so skip for these

7617 # cases. in other cases such as plain visitors.cloned_traverse(), we

7618 # expect this to happen. see issue #12915

7619 if not _annotations_traversal:

7620 ee = self._Annotated__element # type: ignore[attr-defined]

7621 ee._copy_internals(**kw)

7622

7623 if ind_cols_on_fromclause:

7624 # passed from annotations._deep_annotate(). See that function

7625 # for notes

7626 ee = self._Annotated__element # type: ignore[attr-defined]

7627 self.c = ee.__class__.c.fget(self) # type: ignore[misc]

7628

7629 @util.ro_memoized_property

7630 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7631 """proxy the .c collection of the underlying FromClause.

7632

7633 Originally implemented in 2008 as a simple load of the .c collection

7634 when the annotated construct was created (see d3621ae961a), in modern

7635 SQLAlchemy versions this can be expensive for statements constructed

7636 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7637 it, which works just as well.

7638

7639 Two different use cases seem to require the collection either copied

7640 from the underlying one, or unique to this AnnotatedFromClause.

7641

7642 See test_selectable->test_annotated_corresponding_column

7643

7644 """

7645 ee = self._Annotated__element # type: ignore[attr-defined]

7646 return ee.c # type: ignore[no-any-return]