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

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

1174 FROM table1 JOIN table2 ON true

1175

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

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

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

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

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

1181

1182

1183 .. versionadded:: 1.4

1184

1185 """ # noqa: E501

1186

1187 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1190 when generating the columns clause of a SELECT statement.

1191

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

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

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

1195

1196 .. sourcecode:: pycon+sql

1197

1198 >>> from sqlalchemy import (

1199 ... table,

1200 ... column,

1201 ... select,

1202 ... true,

1203 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1204 ... )

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

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

1207 >>> print(

1208 ... select(table1, table2)

1209 ... .join(table2, true())

1210 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1211 ... )

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

1213 FROM table1 JOIN table2 ON true

1214

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

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

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

1218

1219 .. versionadded:: 1.4

1220

1221 """ # noqa: E501

1222

1223 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1224 """The default label style, refers to

1225 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1226

1227 .. versionadded:: 1.4

1228

1229 """

1230

1231 LABEL_STYLE_LEGACY_ORM = 3

1232

1233

1234(

1235 LABEL_STYLE_NONE,

1236 LABEL_STYLE_TABLENAME_PLUS_COL,

1237 LABEL_STYLE_DISAMBIGUATE_ONLY,

1238 _,

1239) = list(SelectLabelStyle)

1240

1241LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1242

1243

1244class Join(roles.DMLTableRole, FromClause):

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

1246 :class:`_expression.FromClause`

1247 elements.

1248

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

1250 is the module-level

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

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

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

1254 :class:`_schema.Table`).

1255

1256 .. seealso::

1257

1258 :func:`_expression.join`

1259

1260 :meth:`_expression.FromClause.join`

1261

1262 """

1263

1264 __visit_name__ = "join"

1265

1266 _traverse_internals: _TraverseInternalsType = [

1267 ("left", InternalTraversal.dp_clauseelement),

1268 ("right", InternalTraversal.dp_clauseelement),

1269 ("onclause", InternalTraversal.dp_clauseelement),

1270 ("isouter", InternalTraversal.dp_boolean),

1271 ("full", InternalTraversal.dp_boolean),

1272 ]

1273

1274 _is_join = True

1275

1276 left: FromClause

1277 right: FromClause

1278 onclause: Optional[ColumnElement[bool]]

1279 isouter: bool

1280 full: bool

1281

1282 def __init__(

1283 self,

1284 left: _FromClauseArgument,

1285 right: _FromClauseArgument,

1286 onclause: Optional[_OnClauseArgument] = None,

1287 isouter: bool = False,

1288 full: bool = False,

1289 ):

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

1291

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

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

1294 :class:`_expression.FromClause` object.

1295

1296 """

1297

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

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

1300 # annotated objects. test_orm.py -> test_fetch_results

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

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

1303 # callcounts for a single compilation in that particular test

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

1305 # 29200 -> 30373

1306

1307 self.left = coercions.expect(

1308 roles.FromClauseRole,

1309 left,

1310 )

1311 self.right = coercions.expect(

1312 roles.FromClauseRole,

1313 right,

1314 ).self_group()

1315

1316 if onclause is None:

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

1318 else:

1319 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1320 # not merged yet

1321 self.onclause = coercions.expect(

1322 roles.OnClauseRole, onclause

1323 ).self_group(against=operators._asbool)

1324

1325 self.isouter = isouter

1326 self.full = full

1327

1328 @util.ro_non_memoized_property

1329 def description(self) -> str:

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

1331 self.left.description,

1332 id(self.left),

1333 self.right.description,

1334 id(self.right),

1335 )

1336

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

1338 return (

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

1340 # as well

1341 hash(fromclause) == hash(self)

1342 or self.left.is_derived_from(fromclause)

1343 or self.right.is_derived_from(fromclause)

1344 )

1345

1346 def self_group(

1347 self, against: Optional[OperatorType] = None

1348 ) -> FromGrouping:

1349 return FromGrouping(self)

1350

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

1352 def _populate_column_collection(

1353 self,

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

1355 primary_key: ColumnSet,

1356 foreign_keys: Set[KeyedColumnElement[Any]],

1357 ) -> None:

1358 sqlutil = util.preloaded.sql_util

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

1360 c for c in self.right.c

1361 ]

1362

1363 primary_key.extend(

1364 sqlutil.reduce_columns(

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

1366 )

1367 )

1368 columns._populate_separate_keys(

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

1370 )

1371 foreign_keys.update(

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

1373 )

1374

1375 def _copy_internals(

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

1377 ) -> None:

1378 # see Select._copy_internals() for similar concept

1379

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

1381 # determine the new FROM clauses

1382 all_the_froms = set(

1383 itertools.chain(

1384 _from_objects(self.left),

1385 _from_objects(self.right),

1386 )

1387 )

1388

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

1390 # cache used by the clone function

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

1392

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

1394 # ColumnClause with parent table referring to those

1395 # replaced FromClause objects

1396 def replace(

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

1398 **kw: Any,

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

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

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

1402 return newelem

1403 return None

1404

1405 kw["replace"] = replace

1406

1407 # run normal _copy_internals. the clones for

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

1409 # cache

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

1411

1412 self._reset_memoizations()

1413

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

1415 super()._refresh_for_new_column(column)

1416 self.left._refresh_for_new_column(column)

1417 self.right._refresh_for_new_column(column)

1418

1419 def _match_primaries(

1420 self,

1421 left: FromClause,

1422 right: FromClause,

1423 ) -> ColumnElement[bool]:

1424 if isinstance(left, Join):

1425 left_right = left.right

1426 else:

1427 left_right = None

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

1429

1430 @classmethod

1431 def _join_condition(

1432 cls,

1433 a: FromClause,

1434 b: FromClause,

1435 *,

1436 a_subset: Optional[FromClause] = None,

1437 consider_as_foreign_keys: Optional[

1438 AbstractSet[ColumnClause[Any]]

1439 ] = None,

1440 ) -> ColumnElement[bool]:

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

1442

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

1444

1445 """

1446 constraints = cls._joincond_scan_left_right(

1447 a, a_subset, b, consider_as_foreign_keys

1448 )

1449

1450 if len(constraints) > 1:

1451 cls._joincond_trim_constraints(

1452 a, b, constraints, consider_as_foreign_keys

1453 )

1454

1455 if len(constraints) == 0:

1456 if isinstance(b, FromGrouping):

1457 hint = (

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

1459 "subquery using alias()?"

1460 )

1461 else:

1462 hint = ""

1463 raise exc.NoForeignKeysError(

1464 "Can't find any foreign key relationships "

1465 "between '%s' and '%s'.%s"

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

1467 )

1468

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

1470 if len(crit) == 1:

1471 return crit[0]

1472 else:

1473 return and_(*crit)

1474

1475 @classmethod

1476 def _can_join(

1477 cls,

1478 left: FromClause,

1479 right: FromClause,

1480 *,

1481 consider_as_foreign_keys: Optional[

1482 AbstractSet[ColumnClause[Any]]

1483 ] = None,

1484 ) -> bool:

1485 if isinstance(left, Join):

1486 left_right = left.right

1487 else:

1488 left_right = None

1489

1490 constraints = cls._joincond_scan_left_right(

1491 a=left,

1492 b=right,

1493 a_subset=left_right,

1494 consider_as_foreign_keys=consider_as_foreign_keys,

1495 )

1496

1497 return bool(constraints)

1498

1499 @classmethod

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

1501 def _joincond_scan_left_right(

1502 cls,

1503 a: FromClause,

1504 a_subset: Optional[FromClause],

1505 b: FromClause,

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

1507 ) -> collections.defaultdict[

1508 Optional[ForeignKeyConstraint],

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

1510 ]:

1511 sql_util = util.preloaded.sql_util

1512

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

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

1515

1516 constraints: collections.defaultdict[

1517 Optional[ForeignKeyConstraint],

1518 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1519 ] = collections.defaultdict(list)

1520

1521 for left in (a_subset, a):

1522 if left is None:

1523 continue

1524 for fk in sorted(

1525 b.foreign_keys,

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

1527 ):

1528 if (

1529 consider_as_foreign_keys is not None

1530 and fk.parent not in consider_as_foreign_keys

1531 ):

1532 continue

1533 try:

1534 col = fk.get_referent(left)

1535 except exc.NoReferenceError as nrte:

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

1537 if nrte.table_name in table_names:

1538 raise

1539 else:

1540 continue

1541

1542 if col is not None:

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

1544 if left is not b:

1545 for fk in sorted(

1546 left.foreign_keys,

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

1548 ):

1549 if (

1550 consider_as_foreign_keys is not None

1551 and fk.parent not in consider_as_foreign_keys

1552 ):

1553 continue

1554 try:

1555 col = fk.get_referent(b)

1556 except exc.NoReferenceError as nrte:

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

1558 if nrte.table_name in table_names:

1559 raise

1560 else:

1561 continue

1562

1563 if col is not None:

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

1565 if constraints:

1566 break

1567 return constraints

1568

1569 @classmethod

1570 def _joincond_trim_constraints(

1571 cls,

1572 a: FromClause,

1573 b: FromClause,

1574 constraints: Dict[Any, Any],

1575 consider_as_foreign_keys: Optional[Any],

1576 ) -> None:

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

1578 # to include just those FKCs that match exactly to

1579 # "consider_as_foreign_keys".

1580 if consider_as_foreign_keys:

1581 for const in list(constraints):

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

1583 consider_as_foreign_keys

1584 ):

1585 del constraints[const]

1586

1587 # if still multiple constraints, but

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

1589 if len(constraints) > 1:

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

1591 if len(dedupe) == 1:

1592 key = list(constraints)[0]

1593 constraints = {key: constraints[key]}

1594

1595 if len(constraints) != 1:

1596 raise exc.AmbiguousForeignKeysError(

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

1598 "tables have more than one foreign key "

1599 "constraint relationship between them. "

1600 "Please specify the 'onclause' of this "

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

1602 )

1603

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

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

1606 :class:`_expression.Join`.

1607

1608 E.g.::

1609

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

1611

1612 stmt = stmt.select()

1613

1614 The above will produce a SQL string resembling:

1615

1616 .. sourcecode:: sql

1617

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

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

1620

1621 """

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

1623

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

1625 def _anonymous_fromclause(

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

1627 ) -> TODO_Any:

1628 sqlutil = util.preloaded.sql_util

1629 if flat:

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

1631 left_name = name # will recurse

1632 else:

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

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

1635 else:

1636 left_name = name

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

1638 right_name = name # will recurse

1639 else:

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

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

1642 else:

1643 right_name = name

1644 left_a, right_a = (

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

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

1647 )

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

1649 sqlutil.ClauseAdapter(right_a)

1650 )

1651

1652 return left_a.join(

1653 right_a,

1654 adapter.traverse(self.onclause),

1655 isouter=self.isouter,

1656 full=self.full,

1657 )

1658 else:

1659 return (

1660 self.select()

1661 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1662 .correlate(None)

1663 .alias(name)

1664 )

1665

1666 @util.ro_non_memoized_property

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

1668 return itertools.chain(

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

1670 )

1671

1672 @util.ro_non_memoized_property

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

1674 self_list: List[FromClause] = [self]

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

1676

1677

1678class NoInit:

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

1680 raise NotImplementedError(

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

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

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

1684 "selectable objects."

1685 % (

1686 self.__class__.__name__,

1687 self.__class__.__name__.lower(),

1688 self.__class__.__name__.lower(),

1689 )

1690 )

1691

1692

1693class LateralFromClause(NamedFromClause):

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

1695

1696

1697# FromClause ->

1698# AliasedReturnsRows

1699# -> Alias only for FromClause

1700# -> Subquery only for SelectBase

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

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

1703# w/ non-deprecated coercion

1704# -> TableSample -> only for FromClause

1705

1706

1707class AliasedReturnsRows(NoInit, NamedFromClause):

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

1709 selectables."""

1710

1711 _is_from_container = True

1712

1713 _supports_derived_columns = False

1714

1715 element: ReturnsRows

1716

1717 _traverse_internals: _TraverseInternalsType = [

1718 ("element", InternalTraversal.dp_clauseelement),

1719 ("name", InternalTraversal.dp_anon_name),

1720 ]

1721

1722 @classmethod

1723 def _construct(

1724 cls,

1725 selectable: Any,

1726 *,

1727 name: Optional[str] = None,

1728 **kw: Any,

1729 ) -> Self:

1730 obj = cls.__new__(cls)

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

1732 return obj

1733

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

1735 self.element = coercions.expect(

1736 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1737 )

1738 self.element = selectable

1739 self._orig_name = name

1740 if name is None:

1741 if (

1742 isinstance(selectable, FromClause)

1743 and selectable.named_with_column

1744 ):

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

1746 if isinstance(name, _anonymous_label):

1747 name = None

1748 name = _anonymous_label.safe_construct(

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

1750 )

1751 self.name = name

1752

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

1754 super()._refresh_for_new_column(column)

1755 self.element._refresh_for_new_column(column)

1756

1757 def _populate_column_collection(

1758 self,

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

1760 primary_key: ColumnSet,

1761 foreign_keys: Set[KeyedColumnElement[Any]],

1762 ) -> None:

1763 self.element._generate_fromclause_column_proxies(

1764 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1765 )

1766

1767 @util.ro_non_memoized_property

1768 def description(self) -> str:

1769 name = self.name

1770 if isinstance(name, _anonymous_label):

1771 return "anon_1"

1772

1773 return name

1774

1775 @util.ro_non_memoized_property

1776 def implicit_returning(self) -> bool:

1777 return self.element.implicit_returning # type: ignore

1778

1779 @property

1780 def original(self) -> ReturnsRows:

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

1782 return self.element

1783

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

1785 if fromclause in self._cloned_set:

1786 return True

1787 return self.element.is_derived_from(fromclause)

1788

1789 def _copy_internals(

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

1791 ) -> None:

1792 existing_element = self.element

1793

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

1795

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

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

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

1799 # performance.

1800 if existing_element is not self.element:

1801 self._reset_column_collection()

1802

1803 @property

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

1805 return [self]

1806

1807

1808class FromClauseAlias(AliasedReturnsRows):

1809 element: FromClause

1810

1811 @util.ro_non_memoized_property

1812 def description(self) -> str:

1813 name = self.name

1814 if isinstance(name, _anonymous_label):

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

1816

1817 return name

1818

1819

1820class Alias(roles.DMLTableRole, FromClauseAlias):

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

1822

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

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

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

1826

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

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

1829 method available

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

1831

1832 .. seealso::

1833

1834 :meth:`_expression.FromClause.alias`

1835

1836 """

1837

1838 __visit_name__ = "alias"

1839

1840 inherit_cache = True

1841

1842 element: FromClause

1843

1844 @classmethod

1845 def _factory(

1846 cls,

1847 selectable: FromClause,

1848 name: Optional[str] = None,

1849 flat: bool = False,

1850 ) -> NamedFromClause:

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

1852 name=name, flat=flat

1853 )

1854

1855

1856class TableValuedAlias(LateralFromClause, Alias):

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

1858

1859 This construct provides for a SQL function that returns columns

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

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

1862 method, e.g.:

1863

1864 .. sourcecode:: pycon+sql

1865

1866 >>> from sqlalchemy import select, func

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

1868 ... "value"

1869 ... )

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

1871 {printsql}SELECT anon_1.value

1872 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1873

1874 .. versionadded:: 1.4.0b2

1875

1876 .. seealso::

1877

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

1879

1880 """ # noqa: E501

1881

1882 __visit_name__ = "table_valued_alias"

1883

1884 _supports_derived_columns = True

1885 _render_derived = False

1886 _render_derived_w_types = False

1887 joins_implicitly = False

1888

1889 _traverse_internals: _TraverseInternalsType = [

1890 ("element", InternalTraversal.dp_clauseelement),

1891 ("name", InternalTraversal.dp_anon_name),

1892 ("_tableval_type", InternalTraversal.dp_type),

1893 ("_render_derived", InternalTraversal.dp_boolean),

1894 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1895 ]

1896

1897 def _init(

1898 self,

1899 selectable: Any,

1900 *,

1901 name: Optional[str] = None,

1902 table_value_type: Optional[TableValueType] = None,

1903 joins_implicitly: bool = False,

1904 ) -> None:

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

1906

1907 self.joins_implicitly = joins_implicitly

1908 self._tableval_type = (

1909 type_api.TABLEVALUE

1910 if table_value_type is None

1911 else table_value_type

1912 )

1913

1914 @HasMemoized.memoized_attribute

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

1916 """Return a column expression representing this

1917 :class:`_sql.TableValuedAlias`.

1918

1919 This accessor is used to implement the

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

1921 method for further details.

1922

1923 E.g.:

1924

1925 .. sourcecode:: pycon+sql

1926

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

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

1929

1930 .. seealso::

1931

1932 :meth:`_functions.FunctionElement.column_valued`

1933

1934 """

1935

1936 return TableValuedColumn(self, self._tableval_type)

1937

1938 def alias(

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

1940 ) -> TableValuedAlias:

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

1942

1943 This creates a distinct FROM object that will be distinguished

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

1945

1946 """

1947

1948 tva: TableValuedAlias = TableValuedAlias._construct(

1949 self,

1950 name=name,

1951 table_value_type=self._tableval_type,

1952 joins_implicitly=self.joins_implicitly,

1953 )

1954

1955 if self._render_derived:

1956 tva._render_derived = True

1957 tva._render_derived_w_types = self._render_derived_w_types

1958

1959 return tva

1960

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

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

1963 set, so that it renders as LATERAL.

1964

1965 .. seealso::

1966

1967 :func:`_expression.lateral`

1968

1969 """

1970 tva = self.alias(name=name)

1971 tva._is_lateral = True

1972 return tva

1973

1974 def render_derived(

1975 self,

1976 name: Optional[str] = None,

1977 with_types: bool = False,

1978 ) -> TableValuedAlias:

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

1980

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

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

1983

1984 .. sourcecode:: pycon+sql

1985

1986 >>> print(

1987 ... select(

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

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

1990 ... .render_derived()

1991 ... )

1992 ... )

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

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

1995

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

1997 the alias expression (this syntax currently applies to the

1998 PostgreSQL database):

1999

2000 .. sourcecode:: pycon+sql

2001

2002 >>> print(

2003 ... select(

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

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

2006 ... .render_derived(with_types=True)

2007 ... )

2008 ... )

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

2010 AS anon_1(a INTEGER, b VARCHAR)

2011

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

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

2014

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

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

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

2018

2019 """ # noqa: E501

2020

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

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

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

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

2025 # (just saw it happen on CI)

2026

2027 # construct against original to prevent memory growth

2028 # for repeated generations

2029 new_alias: TableValuedAlias = TableValuedAlias._construct(

2030 self.element,

2031 name=name,

2032 table_value_type=self._tableval_type,

2033 joins_implicitly=self.joins_implicitly,

2034 )

2035 new_alias._render_derived = True

2036 new_alias._render_derived_w_types = with_types

2037 return new_alias

2038

2039

2040class Lateral(FromClauseAlias, LateralFromClause):

2041 """Represent a LATERAL subquery.

2042

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

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

2045 method available

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

2047

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

2049 PostgreSQL versions provide support for this keyword.

2050

2051 .. seealso::

2052

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

2054

2055 """

2056

2057 __visit_name__ = "lateral"

2058 _is_lateral = True

2059

2060 inherit_cache = True

2061

2062 @classmethod

2063 def _factory(

2064 cls,

2065 selectable: Union[SelectBase, _FromClauseArgument],

2066 name: Optional[str] = None,

2067 ) -> LateralFromClause:

2068 return coercions.expect(

2069 roles.FromClauseRole, selectable, explicit_subquery=True

2070 ).lateral(name=name)

2071

2072

2073class TableSample(FromClauseAlias):

2074 """Represent a TABLESAMPLE clause.

2075

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

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

2078 method

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

2080

2081 .. seealso::

2082

2083 :func:`_expression.tablesample`

2084

2085 """

2086

2087 __visit_name__ = "tablesample"

2088

2089 _traverse_internals: _TraverseInternalsType = (

2090 AliasedReturnsRows._traverse_internals

2091 + [

2092 ("sampling", InternalTraversal.dp_clauseelement),

2093 ("seed", InternalTraversal.dp_clauseelement),

2094 ]

2095 )

2096

2097 @classmethod

2098 def _factory(

2099 cls,

2100 selectable: _FromClauseArgument,

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

2102 name: Optional[str] = None,

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

2104 ) -> TableSample:

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

2106 sampling, name=name, seed=seed

2107 )

2108

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

2110 def _init( # type: ignore[override]

2111 self,

2112 selectable: Any,

2113 *,

2114 name: Optional[str] = None,

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

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

2117 ) -> None:

2118 assert sampling is not None

2119 functions = util.preloaded.sql_functions

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

2121 sampling = functions.func.system(sampling)

2122

2123 self.sampling: Function[Any] = sampling

2124 self.seed = seed

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

2126

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

2128 return self.sampling

2129

2130

2131class CTE(

2132 roles.DMLTableRole,

2133 roles.IsCTERole,

2134 Generative,

2135 HasPrefixes,

2136 HasSuffixes,

2137 AliasedReturnsRows,

2138):

2139 """Represent a Common Table Expression.

2140

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

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

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

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

2145 :class:`_sql.Update` and

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

2147 usage details on CTEs.

2148

2149 .. seealso::

2150

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

2152

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

2154

2155 """

2156

2157 __visit_name__ = "cte"

2158

2159 _traverse_internals: _TraverseInternalsType = (

2160 AliasedReturnsRows._traverse_internals

2161 + [

2162 ("_cte_alias", InternalTraversal.dp_clauseelement),

2163 ("_restates", InternalTraversal.dp_clauseelement),

2164 ("recursive", InternalTraversal.dp_boolean),

2165 ("nesting", InternalTraversal.dp_boolean),

2166 ]

2167 + HasPrefixes._has_prefixes_traverse_internals

2168 + HasSuffixes._has_suffixes_traverse_internals

2169 )

2170

2171 element: HasCTE

2172

2173 @classmethod

2174 def _factory(

2175 cls,

2176 selectable: HasCTE,

2177 name: Optional[str] = None,

2178 recursive: bool = False,

2179 ) -> CTE:

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

2181 or Common Table Expression instance.

2182

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

2184

2185 """

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

2187 name=name, recursive=recursive

2188 )

2189

2190 def _init(

2191 self,

2192 selectable: HasCTE,

2193 *,

2194 name: Optional[str] = None,

2195 recursive: bool = False,

2196 nesting: bool = False,

2197 _cte_alias: Optional[CTE] = None,

2198 _restates: Optional[CTE] = None,

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

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

2201 ) -> None:

2202 self.recursive = recursive

2203 self.nesting = nesting

2204 self._cte_alias = _cte_alias

2205 # Keep recursivity reference with union/union_all

2206 self._restates = _restates

2207 if _prefixes:

2208 self._prefixes = _prefixes

2209 if _suffixes:

2210 self._suffixes = _suffixes

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

2212

2213 def _populate_column_collection(

2214 self,

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

2216 primary_key: ColumnSet,

2217 foreign_keys: Set[KeyedColumnElement[Any]],

2218 ) -> None:

2219 if self._cte_alias is not None:

2220 self._cte_alias._generate_fromclause_column_proxies(

2221 self,

2222 columns,

2223 primary_key=primary_key,

2224 foreign_keys=foreign_keys,

2225 )

2226 else:

2227 self.element._generate_fromclause_column_proxies(

2228 self,

2229 columns,

2230 primary_key=primary_key,

2231 foreign_keys=foreign_keys,

2232 )

2233

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

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

2236 :class:`_expression.CTE`.

2237

2238 This method is a CTE-specific specialization of the

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

2240

2241 .. seealso::

2242

2243 :ref:`tutorial_using_aliases`

2244

2245 :func:`_expression.alias`

2246

2247 """

2248 return CTE._construct(

2249 self.element,

2250 name=name,

2251 recursive=self.recursive,

2252 nesting=self.nesting,

2253 _cte_alias=self,

2254 _prefixes=self._prefixes,

2255 _suffixes=self._suffixes,

2256 )

2257

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

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

2260 of the original CTE against the given selectables provided

2261 as positional arguments.

2262

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

2264 UNION.

2265

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

2267

2268 .. seealso::

2269

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

2271

2272 """

2273 assert is_select_statement(

2274 self.element

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

2276

2277 return CTE._construct(

2278 self.element.union(*other),

2279 name=self.name,

2280 recursive=self.recursive,

2281 nesting=self.nesting,

2282 _restates=self,

2283 _prefixes=self._prefixes,

2284 _suffixes=self._suffixes,

2285 )

2286

2287 def union_all(

2288 self, *other: _SelectStatementForCompoundArgument[Any]

2289 ) -> CTE:

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

2291 of the original CTE against the given selectables provided

2292 as positional arguments.

2293

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

2295 UNION.

2296

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

2298

2299 .. seealso::

2300

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

2302

2303 """

2304

2305 assert is_select_statement(

2306 self.element

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

2308

2309 return CTE._construct(

2310 self.element.union_all(*other),

2311 name=self.name,

2312 recursive=self.recursive,

2313 nesting=self.nesting,

2314 _restates=self,

2315 _prefixes=self._prefixes,

2316 _suffixes=self._suffixes,

2317 )

2318

2319 def _get_reference_cte(self) -> CTE:

2320 """

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

2322 Updated CTEs should still refer to the original CTE.

2323 This function returns this reference identifier.

2324 """

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

2326

2327

2328class _CTEOpts(NamedTuple):

2329 nesting: bool

2330

2331

2332class _ColumnsPlusNames(NamedTuple):

2333 required_label_name: Optional[str]

2334 """

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

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

2337 """

2338

2339 proxy_key: Optional[str]

2340 """

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

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

2343 select.selected_columns collection

2344 """

2345

2346 fallback_label_name: Optional[str]

2347 """

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

2349 we have to render a label even though

2350 required_label_name was not given

2351 """

2352

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

2354 """

2355 the ColumnElement itself

2356 """

2357

2358 repeated: bool

2359 """

2360 True if this is a duplicate of a previous column

2361 in the list of columns

2362 """

2363

2364

2365class SelectsRows(ReturnsRows):

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

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

2368

2369 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2370

2371 def _generate_columns_plus_names(

2372 self,

2373 anon_for_dupe_key: bool,

2374 cols: Optional[_SelectIterable] = None,

2375 ) -> List[_ColumnsPlusNames]:

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

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

2378 on a :class:`.FromClause`.

2379

2380 This is distinct from the _column_naming_convention generator that's

2381 intended for population of the Select.selected_columns collection,

2382 different rules. the collection returned here calls upon the

2383 _column_naming_convention as well.

2384

2385 """

2386

2387 if cols is None:

2388 cols = self._all_selected_columns

2389

2390 key_naming_convention = SelectState._column_naming_convention(

2391 self._label_style

2392 )

2393

2394 names = {}

2395

2396 result: List[_ColumnsPlusNames] = []

2397 result_append = result.append

2398

2399 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2400 label_style_none = self._label_style is LABEL_STYLE_NONE

2401

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

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

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

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

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

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

2408 dedupe_hash = 1

2409

2410 for c in cols:

2411 repeated = False

2412

2413 if not c._render_label_in_columns_clause:

2414 effective_name = required_label_name = fallback_label_name = (

2415 None

2416 )

2417 elif label_style_none:

2418 if TYPE_CHECKING:

2419 assert is_column_element(c)

2420

2421 effective_name = required_label_name = None

2422 fallback_label_name = c._non_anon_label or c._anon_name_label

2423 else:

2424 if TYPE_CHECKING:

2425 assert is_column_element(c)

2426

2427 if table_qualified:

2428 required_label_name = effective_name = (

2429 fallback_label_name

2430 ) = c._tq_label

2431 else:

2432 effective_name = fallback_label_name = c._non_anon_label

2433 required_label_name = None

2434

2435 if effective_name is None:

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

2437 # not need _expression_label but it isn't

2438 # giving us a clue when to use anon_label instead

2439 expr_label = c._expression_label

2440 if expr_label is None:

2441 repeated = c._anon_name_label in names

2442 names[c._anon_name_label] = c

2443 effective_name = required_label_name = None

2444

2445 if repeated:

2446 # here, "required_label_name" is sent as

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

2448 if table_qualified:

2449 fallback_label_name = (

2450 c._dedupe_anon_tq_label_idx(dedupe_hash)

2451 )

2452 dedupe_hash += 1

2453 else:

2454 fallback_label_name = c._dedupe_anon_label_idx(

2455 dedupe_hash

2456 )

2457 dedupe_hash += 1

2458 else:

2459 fallback_label_name = c._anon_name_label

2460 else:

2461 required_label_name = effective_name = (

2462 fallback_label_name

2463 ) = expr_label

2464

2465 if effective_name is not None:

2466 if TYPE_CHECKING:

2467 assert is_column_element(c)

2468

2469 if effective_name in names:

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

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

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

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

2474 # different column under the same name. apply

2475 # disambiguating label

2476 if table_qualified:

2477 required_label_name = fallback_label_name = (

2478 c._anon_tq_label

2479 )

2480 else:

2481 required_label_name = fallback_label_name = (

2482 c._anon_name_label

2483 )

2484

2485 if anon_for_dupe_key and required_label_name in names:

2486 # here, c._anon_tq_label is definitely unique to

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

2488 # this should always be true.

2489 # this is also an infrequent codepath because

2490 # you need two levels of duplication to be here

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

2492

2493 # the column under the disambiguating label is

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

2495 # subsequent occurrences of the column so that the

2496 # original stays non-ambiguous

2497 if table_qualified:

2498 required_label_name = fallback_label_name = (

2499 c._dedupe_anon_tq_label_idx(dedupe_hash)

2500 )

2501 dedupe_hash += 1

2502 else:

2503 required_label_name = fallback_label_name = (

2504 c._dedupe_anon_label_idx(dedupe_hash)

2505 )

2506 dedupe_hash += 1

2507 repeated = True

2508 else:

2509 names[required_label_name] = c

2510 elif anon_for_dupe_key:

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

2512 # label so that the original stays non-ambiguous

2513 if table_qualified:

2514 required_label_name = fallback_label_name = (

2515 c._dedupe_anon_tq_label_idx(dedupe_hash)

2516 )

2517 dedupe_hash += 1

2518 else:

2519 required_label_name = fallback_label_name = (

2520 c._dedupe_anon_label_idx(dedupe_hash)

2521 )

2522 dedupe_hash += 1

2523 repeated = True

2524 else:

2525 names[effective_name] = c

2526

2527 result_append(

2528 _ColumnsPlusNames(

2529 required_label_name,

2530 key_naming_convention(c),

2531 fallback_label_name,

2532 c,

2533 repeated,

2534 )

2535 )

2536

2537 return result

2538

2539

2540class HasCTE(roles.HasCTERole, SelectsRows):

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

2542

2543 _has_ctes_traverse_internals: _TraverseInternalsType = [

2544 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2545 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2546 ]

2547

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

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

2550

2551 name_cte_columns: bool = False

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

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

2554

2555 .. versionadded:: 2.0.42

2556

2557 """

2558

2559 @_generative

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

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

2562

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

2564 the parent statement such that they will each be unconditionally

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

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

2567

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

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

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

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

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

2573 statement.

2574

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

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

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

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

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

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

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

2582 larger statement.

2583

2584 E.g.::

2585

2586 from sqlalchemy import table, column, select

2587

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

2589

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

2591

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

2593

2594 Would render:

2595

2596 .. sourcecode:: sql

2597

2598 WITH anon_1 AS (

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

2600 )

2601 SELECT t.c1, t.c2

2602 FROM t

2603

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

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

2606 statement.

2607

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

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

2610

2611 from sqlalchemy import table, column

2612 from sqlalchemy.dialects.postgresql import insert

2613

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

2615

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

2617

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

2619 update_statement = insert_stmt.on_conflict_do_update(

2620 index_elements=[t.c.c1],

2621 set_={

2622 "c1": insert_stmt.excluded.c1,

2623 "c2": insert_stmt.excluded.c2,

2624 },

2625 ).add_cte(delete_statement_cte)

2626

2627 print(update_statement)

2628

2629 The above statement renders as:

2630

2631 .. sourcecode:: sql

2632

2633 WITH deletions AS (

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

2635 )

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

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

2638

2639 .. versionadded:: 1.4.21

2640

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

2642

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

2644

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

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

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

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

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

2650 this statement when this flag is given.

2651

2652 .. versionadded:: 2.0

2653

2654 .. seealso::

2655

2656 :paramref:`.HasCTE.cte.nesting`

2657

2658

2659 """ # noqa: E501

2660 opt = _CTEOpts(nest_here)

2661 for cte in ctes:

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

2663 self._independent_ctes += (cte,)

2664 self._independent_ctes_opts += (opt,)

2665 return self

2666

2667 def cte(

2668 self,

2669 name: Optional[str] = None,

2670 recursive: bool = False,

2671 nesting: bool = False,

2672 ) -> CTE:

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

2674 or Common Table Expression instance.

2675

2676 Common table expressions are a SQL standard whereby SELECT

2677 statements can draw upon secondary statements specified along

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

2679 Special semantics regarding UNION can also be employed to

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

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

2682

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

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

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

2686 CTE rows.

2687

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

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

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

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

2692

2693 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2695 method may be

2696 used to establish these.

2697

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

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

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

2701 compile time.

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

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

2704 conjunction with UNION ALL in order to derive rows

2705 from those already selected.

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

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

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

2709 :paramref:`.HasCTE.add_cte.nest_here`

2710 parameter may also be used to more carefully

2711 control the exact placement of a particular CTE.

2712

2713 .. versionadded:: 1.4.24

2714

2715 .. seealso::

2716

2717 :meth:`.HasCTE.add_cte`

2718

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

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

2721 as well as additional examples.

2722

2723 Example 1, non recursive::

2724

2725 from sqlalchemy import (

2726 Table,

2727 Column,

2728 String,

2729 Integer,

2730 MetaData,

2731 select,

2732 func,

2733 )

2734

2735 metadata = MetaData()

2736

2737 orders = Table(

2738 "orders",

2739 metadata,

2740 Column("region", String),

2741 Column("amount", Integer),

2742 Column("product", String),

2743 Column("quantity", Integer),

2744 )

2745

2746 regional_sales = (

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

2748 .group_by(orders.c.region)

2749 .cte("regional_sales")

2750 )

2751

2752

2753 top_regions = (

2754 select(regional_sales.c.region)

2755 .where(

2756 regional_sales.c.total_sales

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

2758 )

2759 .cte("top_regions")

2760 )

2761

2762 statement = (

2763 select(

2764 orders.c.region,

2765 orders.c.product,

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

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

2768 )

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

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

2771 )

2772

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

2774

2775 Example 2, WITH RECURSIVE::

2776

2777 from sqlalchemy import (

2778 Table,

2779 Column,

2780 String,

2781 Integer,

2782 MetaData,

2783 select,

2784 func,

2785 )

2786

2787 metadata = MetaData()

2788

2789 parts = Table(

2790 "parts",

2791 metadata,

2792 Column("part", String),

2793 Column("sub_part", String),

2794 Column("quantity", Integer),

2795 )

2796

2797 included_parts = (

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

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

2800 .cte(recursive=True)

2801 )

2802

2803

2804 incl_alias = included_parts.alias()

2805 parts_alias = parts.alias()

2806 included_parts = included_parts.union_all(

2807 select(

2808 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2810 )

2811

2812 statement = select(

2813 included_parts.c.sub_part,

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

2815 ).group_by(included_parts.c.sub_part)

2816

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

2818

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

2820

2821 from datetime import date

2822 from sqlalchemy import (

2823 MetaData,

2824 Table,

2825 Column,

2826 Integer,

2827 Date,

2828 select,

2829 literal,

2830 and_,

2831 exists,

2832 )

2833

2834 metadata = MetaData()

2835

2836 visitors = Table(

2837 "visitors",

2838 metadata,

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

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

2841 Column("count", Integer),

2842 )

2843

2844 # add 5 visitors for the product_id == 1

2845 product_id = 1

2846 day = date.today()

2847 count = 5

2848

2849 update_cte = (

2850 visitors.update()

2851 .where(

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

2853 )

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

2855 .returning(literal(1))

2856 .cte("update_cte")

2857 )

2858

2859 upsert = visitors.insert().from_select(

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

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

2862 ~exists(update_cte.select())

2863 ),

2864 )

2865

2866 connection.execute(upsert)

2867

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

2869

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

2871

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

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

2874 "value_a", nesting=True

2875 )

2876

2877 # Nesting CTEs takes ascendency locally

2878 # over the CTEs at a higher level

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

2880

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

2882

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

2884 shown with inline parameters below as:

2885

2886 .. sourcecode:: sql

2887

2888 WITH

2889 value_a AS

2890 (SELECT 'root' AS n),

2891 value_b AS

2892 (WITH value_a AS

2893 (SELECT 'nesting' AS n)

2894 SELECT value_a.n AS n FROM value_a)

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

2896 FROM value_a, value_b

2897

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

2899 as follows (SQLAlchemy 2.0 and above)::

2900

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

2902

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

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

2905

2906 # Nesting CTEs takes ascendency locally

2907 # over the CTEs at a higher level

2908 value_b = (

2909 select(value_a_nested.c.n)

2910 .add_cte(value_a_nested, nest_here=True)

2911 .cte("value_b")

2912 )

2913

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

2915

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

2917

2918 edge = Table(

2919 "edge",

2920 metadata,

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

2922 Column("left", Integer),

2923 Column("right", Integer),

2924 )

2925

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

2927

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

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

2930 )

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

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

2933 )

2934

2935 subgraph_cte = root_node.union(left_edge, right_edge)

2936

2937 subgraph = select(subgraph_cte)

2938

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

2940

2941 .. sourcecode:: sql

2942

2943 WITH RECURSIVE nodes(node) AS (

2944 SELECT 1 AS node

2945 UNION

2946 SELECT edge."left" AS "left"

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

2948 UNION

2949 SELECT edge."right" AS "right"

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

2951 )

2952 SELECT nodes.node FROM nodes

2953

2954 .. seealso::

2955

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

2957 :meth:`_expression.HasCTE.cte`.

2958

2959 """ # noqa: E501

2960 return CTE._construct(

2961 self, name=name, recursive=recursive, nesting=nesting

2962 )

2963

2964

2965class Subquery(AliasedReturnsRows):

2966 """Represent a subquery of a SELECT.

2967

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

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

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

2971 :class:`_expression.SelectBase` subclass

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

2973 :class:`_expression.CompoundSelect`, and

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

2975 it represents the

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

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

2978

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

2980 :class:`_expression.Alias`

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

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

2983 :class:`_expression.Alias` always

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

2985 :class:`.Subquery`

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

2987

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

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

2990 statement.

2991

2992 """

2993

2994 __visit_name__ = "subquery"

2995

2996 _is_subquery = True

2997

2998 inherit_cache = True

2999

3000 element: SelectBase

3001

3002 @classmethod

3003 def _factory(

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

3005 ) -> Subquery:

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

3007

3008 return coercions.expect(

3009 roles.SelectStatementRole, selectable

3010 ).subquery(name=name)

3011

3012 @util.deprecated(

3013 "1.4",

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

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

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

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

3018 ":func:`_expression.select` "

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

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

3021 )

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

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

3024

3025

3026class FromGrouping(GroupedElement, FromClause):

3027 """Represent a grouping of a FROM clause"""

3028

3029 _traverse_internals: _TraverseInternalsType = [

3030 ("element", InternalTraversal.dp_clauseelement)

3031 ]

3032

3033 element: FromClause

3034

3035 def __init__(self, element: FromClause):

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

3037

3038 @util.ro_non_memoized_property

3039 def columns(

3040 self,

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

3042 return self.element.columns

3043

3044 @util.ro_non_memoized_property

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

3046 return self.element.columns

3047

3048 @property

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

3050 return self.element.primary_key

3051

3052 @property

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

3054 return self.element.foreign_keys

3055

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

3057 return self.element.is_derived_from(fromclause)

3058

3059 def alias(

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

3061 ) -> NamedFromGrouping:

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

3063

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

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

3066

3067 @util.ro_non_memoized_property

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

3069 return self.element._hide_froms

3070

3071 @util.ro_non_memoized_property

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

3073 return self.element._from_objects

3074

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

3076 return {"element": self.element}

3077

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

3079 self.element = state["element"]

3080

3081 if TYPE_CHECKING:

3082

3083 def self_group(

3084 self, against: Optional[OperatorType] = None

3085 ) -> Self: ...

3086

3087

3088class NamedFromGrouping(FromGrouping, NamedFromClause):

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

3090

3091 .. versionadded:: 2.0

3092

3093 """

3094

3095 inherit_cache = True

3096

3097 if TYPE_CHECKING:

3098

3099 def self_group(

3100 self, against: Optional[OperatorType] = None

3101 ) -> Self: ...

3102

3103

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

3105 """Represents a minimal "table" construct.

3106

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

3108 collection of columns, which are typically produced

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

3110

3111 from sqlalchemy import table, column

3112

3113 user = table(

3114 "user",

3115 column("id"),

3116 column("name"),

3117 column("description"),

3118 )

3119

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

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

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

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

3124

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

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

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

3128 It's useful

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

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

3131 is not on hand.

3132

3133 """

3134

3135 __visit_name__ = "table"

3136

3137 _traverse_internals: _TraverseInternalsType = [

3138 (

3139 "columns",

3140 InternalTraversal.dp_fromclause_canonical_column_collection,

3141 ),

3142 ("name", InternalTraversal.dp_string),

3143 ("schema", InternalTraversal.dp_string),

3144 ]

3145

3146 _is_table = True

3147

3148 fullname: str

3149

3150 implicit_returning = False

3151 """:class:`_expression.TableClause`

3152 doesn't support having a primary key or column

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

3154

3155 _columns: DedupeColumnCollection[ColumnClause[Any]]

3156

3157 @util.ro_memoized_property

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

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

3160 return None

3161

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

3163 super().__init__()

3164 self.name = name

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

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

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

3168 for c in columns:

3169 self.append_column(c)

3170

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

3172 if schema is not None:

3173 self.schema = schema

3174 if self.schema is not None:

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

3176 else:

3177 self.fullname = self.name

3178 if kw:

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

3180

3181 if TYPE_CHECKING:

3182

3183 @util.ro_non_memoized_property

3184 def columns(

3185 self,

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

3187

3188 @util.ro_non_memoized_property

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

3190

3191 def __str__(self) -> str:

3192 if self.schema is not None:

3193 return self.schema + "." + self.name

3194 else:

3195 return self.name

3196

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

3198 pass

3199

3200 @util.ro_memoized_property

3201 def description(self) -> str:

3202 return self.name

3203

3204 def _insert_col_impl(

3205 self,

3206 c: ColumnClause[Any],

3207 *,

3208 index: Optional[int] = None,

3209 ) -> None:

3210 existing = c.table

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

3212 raise exc.ArgumentError(

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

3214 % (c.key, existing)

3215 )

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

3217 c.table = self

3218

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

3220 self._insert_col_impl(c)

3221

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

3223 self._insert_col_impl(c, index=index)

3224

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

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

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

3228 :class:`_expression.TableClause`.

3229

3230 E.g.::

3231

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

3233

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

3235

3236 """

3237

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

3239

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

3241 def update(self) -> Update:

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

3243 :class:`_expression.TableClause`.

3244

3245 E.g.::

3246

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

3248

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

3250

3251 """

3252 return util.preloaded.sql_dml.Update(

3253 self,

3254 )

3255

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

3257 def delete(self) -> Delete:

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

3259 :class:`_expression.TableClause`.

3260

3261 E.g.::

3262

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

3264

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

3266

3267 """

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

3269

3270 @util.ro_non_memoized_property

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

3272 return [self]

3273

3274

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

3276

3277

3278class ForUpdateArg(ClauseElement):

3279 _traverse_internals: _TraverseInternalsType = [

3280 ("of", InternalTraversal.dp_clauseelement_list),

3281 ("nowait", InternalTraversal.dp_boolean),

3282 ("read", InternalTraversal.dp_boolean),

3283 ("skip_locked", InternalTraversal.dp_boolean),

3284 ("key_share", InternalTraversal.dp_boolean),

3285 ]

3286

3287 of: Optional[Sequence[ClauseElement]]

3288 nowait: bool

3289 read: bool

3290 skip_locked: bool

3291

3292 @classmethod

3293 def _from_argument(

3294 cls, with_for_update: ForUpdateParameter

3295 ) -> Optional[ForUpdateArg]:

3296 if isinstance(with_for_update, ForUpdateArg):

3297 return with_for_update

3298 elif with_for_update in (None, False):

3299 return None

3300 elif with_for_update is True:

3301 return ForUpdateArg()

3302 else:

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

3304

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

3306 return (

3307 isinstance(other, ForUpdateArg)

3308 and other.nowait == self.nowait

3309 and other.read == self.read

3310 and other.skip_locked == self.skip_locked

3311 and other.key_share == self.key_share

3312 and other.of is self.of

3313 )

3314

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

3316 return not self.__eq__(other)

3317

3318 def __hash__(self) -> int:

3319 return id(self)

3320

3321 def __init__(

3322 self,

3323 *,

3324 nowait: bool = False,

3325 read: bool = False,

3326 of: Optional[_ForUpdateOfArgument] = None,

3327 skip_locked: bool = False,

3328 key_share: bool = False,

3329 ):

3330 """Represents arguments specified to

3331 :meth:`_expression.Select.for_update`.

3332

3333 """

3334

3335 self.nowait = nowait

3336 self.read = read

3337 self.skip_locked = skip_locked

3338 self.key_share = key_share

3339 if of is not None:

3340 self.of = [

3341 coercions.expect(roles.ColumnsClauseRole, elem)

3342 for elem in util.to_list(of)

3343 ]

3344 else:

3345 self.of = None

3346

3347

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

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

3350 in a statement.

3351

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

3353 :func:`_expression.values` function.

3354

3355 .. versionadded:: 1.4

3356

3357 """

3358

3359 __visit_name__ = "values"

3360

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

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

3363

3364 _unnamed: bool

3365 _traverse_internals: _TraverseInternalsType = [

3366 ("_column_args", InternalTraversal.dp_clauseelement_list),

3367 ("_data", InternalTraversal.dp_dml_multi_values),

3368 ("name", InternalTraversal.dp_string),

3369 ("literal_binds", InternalTraversal.dp_boolean),

3370 ] + HasCTE._has_ctes_traverse_internals

3371

3372 name_cte_columns = True

3373

3374 def __init__(

3375 self,

3376 *columns: _OnlyColumnArgument[Any],

3377 name: Optional[str] = None,

3378 literal_binds: bool = False,

3379 ):

3380 super().__init__()

3381 self._column_args = tuple(

3382 coercions.expect(roles.LabeledColumnExprRole, col)

3383 for col in columns

3384 )

3385

3386 if name is None:

3387 self._unnamed = True

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

3389 else:

3390 self._unnamed = False

3391 self.name = name

3392 self.literal_binds = literal_binds

3393 self.named_with_column = not self._unnamed

3394

3395 @property

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

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

3398

3399 @util.ro_non_memoized_property

3400 def _all_selected_columns(self) -> _SelectIterable:

3401 return self._column_args

3402

3403 @_generative

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

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

3406 construct that is a copy of this

3407 one with the given name.

3408

3409 This method is a VALUES-specific specialization of the

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

3411

3412 .. seealso::

3413

3414 :ref:`tutorial_using_aliases`

3415

3416 :func:`_expression.alias`

3417

3418 """

3419 non_none_name: str

3420

3421 if name is None:

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

3423 else:

3424 non_none_name = name

3425

3426 self.name = non_none_name

3427 self.named_with_column = True

3428 self._unnamed = False

3429 return self

3430

3431 @_generative

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

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

3434 so that

3435 it renders as LATERAL.

3436

3437 .. seealso::

3438

3439 :func:`_expression.lateral`

3440

3441 """

3442 non_none_name: str

3443

3444 if name is None:

3445 non_none_name = self.name

3446 else:

3447 non_none_name = name

3448

3449 self._is_lateral = True

3450 self.name = non_none_name

3451 self._unnamed = False

3452 return self

3453

3454 @_generative

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

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

3457 adding the given data to the data list.

3458

3459 E.g.::

3460

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

3462

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

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

3465 constructor.

3466

3467 """

3468

3469 self._data += (values,)

3470 return self

3471

3472 def scalar_values(self) -> ScalarValues:

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

3474 COLUMN element in a statement.

3475

3476 .. versionadded:: 2.0.0b4

3477

3478 """

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

3480

3481 def _populate_column_collection(

3482 self,

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

3484 primary_key: ColumnSet,

3485 foreign_keys: Set[KeyedColumnElement[Any]],

3486 ) -> None:

3487 for c in self._column_args:

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

3489 _, c = c._make_proxy(

3490 self, primary_key=primary_key, foreign_keys=foreign_keys

3491 )

3492 else:

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

3494 # no memoizations of other FROM clauses.

3495 # see test_values.py -> test_auto_proxy_select_direct_col

3496 c._reset_memoizations()

3497 columns.add(c)

3498 c.table = self

3499

3500 @util.ro_non_memoized_property

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

3502 return [self]

3503

3504

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

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

3507 COLUMN element in a statement.

3508

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

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

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

3512 an ``IN`` or ``NOT IN`` condition.

3513

3514 .. versionadded:: 2.0.0b4

3515

3516 """

3517

3518 __visit_name__ = "scalar_values"

3519

3520 _traverse_internals: _TraverseInternalsType = [

3521 ("_column_args", InternalTraversal.dp_clauseelement_list),

3522 ("_data", InternalTraversal.dp_dml_multi_values),

3523 ("literal_binds", InternalTraversal.dp_boolean),

3524 ]

3525

3526 def __init__(

3527 self,

3528 columns: Sequence[NamedColumn[Any]],

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

3530 literal_binds: bool,

3531 ):

3532 super().__init__()

3533 self._column_args = columns

3534 self._data = data

3535 self.literal_binds = literal_binds

3536

3537 @property

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

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

3540

3541 def __clause_element__(self) -> ScalarValues:

3542 return self

3543

3544 if TYPE_CHECKING:

3545

3546 def self_group(

3547 self, against: Optional[OperatorType] = None

3548 ) -> Self: ...

3549

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

3551

3552

3553class SelectBase(

3554 roles.SelectStatementRole,

3555 roles.DMLSelectRole,

3556 roles.CompoundElementRole,

3557 roles.InElementRole,

3558 HasCTE,

3559 SupportsCloneAnnotations,

3560 Selectable,

3561):

3562 """Base class for SELECT statements.

3563

3564

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

3566 :class:`_expression.CompoundSelect` and

3567 :class:`_expression.TextualSelect`.

3568

3569

3570 """

3571

3572 _is_select_base = True

3573 is_select = True

3574

3575 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3576

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

3578 self._reset_memoizations()

3579

3580 @util.ro_non_memoized_property

3581 def selected_columns(

3582 self,

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

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

3585 representing the columns that

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

3587

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

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

3590 within this collection cannot be directly nested inside another SELECT

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

3592 necessary parenthesization required by SQL.

3593

3594 .. note::

3595

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

3597 include expressions established in the columns clause using the

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

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

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

3601 construct.

3602

3603 .. seealso::

3604

3605 :attr:`_sql.Select.selected_columns`

3606

3607 .. versionadded:: 1.4

3608

3609 """

3610 raise NotImplementedError()

3611

3612 def _generate_fromclause_column_proxies(

3613 self,

3614 subquery: FromClause,

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

3616 primary_key: ColumnSet,

3617 foreign_keys: Set[KeyedColumnElement[Any]],

3618 *,

3619 proxy_compound_columns: Optional[

3620 Iterable[Sequence[ColumnElement[Any]]]

3621 ] = None,

3622 ) -> None:

3623 raise NotImplementedError()

3624

3625 @util.ro_non_memoized_property

3626 def _all_selected_columns(self) -> _SelectIterable:

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

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

3629 constructs.

3630

3631 .. versionadded:: 1.4.12

3632

3633 .. seealso::

3634

3635 :attr:`_sql.SelectBase.exported_columns`

3636

3637 """

3638 raise NotImplementedError()

3639

3640 @property

3641 def exported_columns(

3642 self,

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

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

3645 that represents the "exported"

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

3647 :class:`_sql.TextClause` constructs.

3648

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

3650 object are synonymous

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

3652

3653 .. versionadded:: 1.4

3654

3655 .. seealso::

3656

3657 :attr:`_expression.Select.exported_columns`

3658

3659 :attr:`_expression.Selectable.exported_columns`

3660

3661 :attr:`_expression.FromClause.exported_columns`

3662

3663

3664 """

3665 return self.selected_columns.as_readonly()

3666

3667 def get_label_style(self) -> SelectLabelStyle:

3668 """

3669 Retrieve the current label style.

3670

3671 Implemented by subclasses.

3672

3673 """

3674 raise NotImplementedError()

3675

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

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

3678

3679 Implemented by subclasses.

3680

3681 """

3682

3683 raise NotImplementedError()

3684

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

3686 raise NotImplementedError()

3687

3688 @util.deprecated(

3689 "1.4",

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

3691 "method is deprecated and will be "

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

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

3694 )

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

3696 return self.scalar_subquery()

3697

3698 def exists(self) -> Exists:

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

3700 which can be used as a column expression.

3701

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

3703

3704 .. seealso::

3705

3706 :func:`_sql.exists`

3707

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

3709

3710 .. versionadded:: 1.4

3711

3712 """

3713 return Exists(self)

3714

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

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

3717 used as a column expression.

3718

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

3720

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

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

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

3724 an enclosing SELECT.

3725

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

3727 subquery that can be produced using the

3728 :meth:`_expression.SelectBase.subquery`

3729 method.

3730

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

3732 :meth:`_expression.SelectBase.scalar_subquery`.

3733

3734 .. seealso::

3735

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

3737

3738 """

3739 if self._label_style is not LABEL_STYLE_NONE:

3740 self = self.set_label_style(LABEL_STYLE_NONE)

3741

3742 return ScalarSelect(self)

3743

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

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

3746 subquery with a label.

3747

3748 .. seealso::

3749

3750 :meth:`_expression.SelectBase.scalar_subquery`.

3751

3752 """

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

3754

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

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

3757

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

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

3760

3761 .. seealso::

3762

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

3764

3765 """

3766 return Lateral._factory(self, name)

3767

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

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

3770

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

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

3773 SELECT statement.

3774

3775 Given a SELECT statement such as::

3776

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

3778

3779 The above statement might look like:

3780

3781 .. sourcecode:: sql

3782

3783 SELECT table.id, table.name FROM table

3784

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

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

3787 a named sub-element::

3788

3789 subq = stmt.subquery()

3790 new_stmt = select(subq)

3791

3792 The above renders as:

3793

3794 .. sourcecode:: sql

3795

3796 SELECT anon_1.id, anon_1.name

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

3798

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

3800 is equivalent to calling

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

3802 method on a FROM object; however,

3803 as a :class:`_expression.SelectBase`

3804 object is not directly FROM object,

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

3806 method provides clearer semantics.

3807

3808 .. versionadded:: 1.4

3809

3810 """

3811

3812 return Subquery._construct(

3813 self._ensure_disambiguated_names(), name=name

3814 )

3815

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

3817 def into(

3818 self,

3819 target: str,

3820 *,

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

3822 schema: Optional[str] = None,

3823 temporary: bool = False,

3824 if_not_exists: bool = False,

3825 ) -> CreateTableAs:

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

3827

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

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

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

3831

3832 Supported on all included backends, the construct emits

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

3834 emits a ``SELECT..INTO`` statement.

3835

3836 e.g.::

3837

3838 from sqlalchemy import select

3839

3840 # Create a new table from a SELECT

3841 stmt = (

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

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

3844 .into("active_users")

3845 )

3846

3847 with engine.begin() as conn:

3848 conn.execute(stmt)

3849

3850 # With optional flags

3851 stmt = (

3852 select(users.c.id)

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

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

3855 )

3856

3857 .. versionadded:: 2.1

3858

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

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

3861

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

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

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

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

3866 is created.

3867

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

3869

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

3871

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

3873

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

3875

3876 .. seealso::

3877

3878 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3879

3880 :class:`_schema.CreateTableAs`

3881

3882 """

3883 sql_ddl = util.preloaded.sql_ddl

3884

3885 return sql_ddl.CreateTableAs(

3886 self,

3887 target,

3888 metadata=metadata,

3889 schema=schema,

3890 temporary=temporary,

3891 if_not_exists=if_not_exists,

3892 )

3893

3894 def _ensure_disambiguated_names(self) -> Self:

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

3896 disambiguated in some way, if possible.

3897

3898 """

3899

3900 raise NotImplementedError()

3901

3902 def alias(

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

3904 ) -> Subquery:

3905 """Return a named subquery against this

3906 :class:`_expression.SelectBase`.

3907

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

3909 :class:`_expression.FromClause`),

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

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

3912 :class:`_expression.FromClause`.

3913

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

3915 method is now

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

3917

3918 """

3919 return self.subquery(name=name)

3920

3921

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

3923

3924

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

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

3927

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

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

3930 compound selects.

3931

3932 """

3933

3934 __visit_name__ = "select_statement_grouping"

3935 _traverse_internals: _TraverseInternalsType = [

3936 ("element", InternalTraversal.dp_clauseelement)

3937 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3938

3939 _is_select_container = True

3940

3941 element: _SB

3942

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

3944 self.element = cast(

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

3946 )

3947

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

3949 new_element = self.element._ensure_disambiguated_names()

3950 if new_element is not self.element:

3951 return SelectStatementGrouping(new_element)

3952 else:

3953 return self

3954

3955 def get_label_style(self) -> SelectLabelStyle:

3956 return self.element.get_label_style()

3957

3958 def set_label_style(

3959 self, label_style: SelectLabelStyle

3960 ) -> SelectStatementGrouping[_SB]:

3961 return SelectStatementGrouping(

3962 self.element.set_label_style(label_style)

3963 )

3964

3965 @property

3966 def select_statement(self) -> _SB:

3967 return self.element

3968

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

3970 return self

3971

3972 if TYPE_CHECKING:

3973

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

3975

3976 # def _generate_columns_plus_names(

3977 # self, anon_for_dupe_key: bool

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

3979 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

3980

3981 def _generate_fromclause_column_proxies(

3982 self,

3983 subquery: FromClause,

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

3985 primary_key: ColumnSet,

3986 foreign_keys: Set[KeyedColumnElement[Any]],

3987 *,

3988 proxy_compound_columns: Optional[

3989 Iterable[Sequence[ColumnElement[Any]]]

3990 ] = None,

3991 ) -> None:

3992 self.element._generate_fromclause_column_proxies(

3993 subquery,

3994 columns,

3995 proxy_compound_columns=proxy_compound_columns,

3996 primary_key=primary_key,

3997 foreign_keys=foreign_keys,

3998 )

3999

4000 @util.ro_non_memoized_property

4001 def _all_selected_columns(self) -> _SelectIterable:

4002 return self.element._all_selected_columns

4003

4004 @util.ro_non_memoized_property

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

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

4007 representing the columns that

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

4009 :class:`_sql.TextClause` constructs.

4010

4011 .. versionadded:: 1.4

4012

4013 .. seealso::

4014

4015 :attr:`_sql.Select.selected_columns`

4016

4017 """

4018 return self.element.selected_columns

4019

4020 @util.ro_non_memoized_property

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

4022 return self.element._from_objects

4023

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

4025 return self.element._scalar_type()

4026

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

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

4029 raise NotImplementedError

4030

4031

4032class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4034 added.

4035

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

4037 :class:`_expression.CompoundSelect`

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

4039 rendering can be controlled. Compare to

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

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

4042 and is also a SELECT construct,

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

4044 only wrapped as a subquery.

4045

4046 """

4047

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

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

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

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

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

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

4054 _for_update_arg: Optional[ForUpdateArg] = None

4055

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

4057 self._label_style = _label_style

4058

4059 @_generative

4060 def with_for_update(

4061 self,

4062 *,

4063 nowait: bool = False,

4064 read: bool = False,

4065 of: Optional[_ForUpdateOfArgument] = None,

4066 skip_locked: bool = False,

4067 key_share: bool = False,

4068 ) -> Self:

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

4070 :class:`_expression.GenerativeSelect`.

4071

4072 E.g.::

4073

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

4075

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

4077 render a statement like:

4078

4079 .. sourcecode:: sql

4080

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

4082

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

4084 would produce:

4085

4086 .. sourcecode:: sql

4087

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

4089

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

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

4092 provided which allow for common database-specific

4093 variants.

4094

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

4096 Database and PostgreSQL dialects.

4097

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

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

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

4101

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

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

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

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

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

4107 table or as a column depending on backend.

4108

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

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

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

4112

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

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

4115 on the PostgreSQL dialect.

4116

4117 """

4118 self._for_update_arg = ForUpdateArg(

4119 nowait=nowait,

4120 read=read,

4121 of=of,

4122 skip_locked=skip_locked,

4123 key_share=key_share,

4124 )

4125 return self

4126

4127 def get_label_style(self) -> SelectLabelStyle:

4128 """

4129 Retrieve the current label style.

4130

4131 .. versionadded:: 1.4

4132

4133 """

4134 return self._label_style

4135

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

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

4138

4139 There are three "label styles" available,

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

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

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

4143 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4144

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

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

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

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

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

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

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

4152 that the impact of this labeling is minimal.

4153

4154 The rationale for disambiguation is mostly so that all column

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

4156 collection when a subquery is created.

4157

4158 .. versionadded:: 1.4 - the

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

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

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

4162

4163 .. seealso::

4164

4165 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4166

4167 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4168

4169 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4170

4171 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4172

4173 """

4174 if self._label_style is not style:

4175 self = self._generate()

4176 self._label_style = style

4177 return self

4178

4179 @property

4180 def _group_by_clause(self) -> ClauseList:

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

4182 return ClauseList._construct_raw(

4183 operators.comma_op, self._group_by_clauses

4184 )

4185

4186 @property

4187 def _order_by_clause(self) -> ClauseList:

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

4189 return ClauseList._construct_raw(

4190 operators.comma_op, self._order_by_clauses

4191 )

4192

4193 def _offset_or_limit_clause(

4194 self,

4195 element: _LimitOffsetType,

4196 name: Optional[str] = None,

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

4198 ) -> ColumnElement[Any]:

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

4200

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

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

4203

4204 """

4205 return coercions.expect(

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

4207 )

4208

4209 @overload

4210 def _offset_or_limit_clause_asint(

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

4212 ) -> NoReturn: ...

4213

4214 @overload

4215 def _offset_or_limit_clause_asint(

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

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

4218

4219 def _offset_or_limit_clause_asint(

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

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

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

4223 integer.

4224

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

4226 parameter. Otherwise, a compilation error is raised.

4227

4228 """

4229 if clause is None:

4230 return None

4231 try:

4232 value = clause._limit_offset_value

4233 except AttributeError as err:

4234 raise exc.CompileError(

4235 "This SELECT structure does not use a simple "

4236 "integer value for %s" % attrname

4237 ) from err

4238 else:

4239 return util.asint(value)

4240

4241 @property

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

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

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

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

4246 isn't currently set to an integer.

4247

4248 """

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

4250

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

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

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

4254 """

4255 return isinstance(clause, _OffsetLimitParam)

4256

4257 @property

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

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

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

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

4262 offset isn't currently set to an integer.

4263

4264 """

4265 return self._offset_or_limit_clause_asint(

4266 self._offset_clause, "offset"

4267 )

4268

4269 @property

4270 def _has_row_limiting_clause(self) -> bool:

4271 return (

4272 self._limit_clause is not None

4273 or self._offset_clause is not None

4274 or self._fetch_clause is not None

4275 )

4276

4277 @_generative

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

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

4280 applied.

4281

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

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

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

4285 functionality.

4286

4287 .. note::

4288

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

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

4291

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

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

4294

4295 .. seealso::

4296

4297 :meth:`_sql.GenerativeSelect.fetch`

4298

4299 :meth:`_sql.GenerativeSelect.offset`

4300

4301 """

4302

4303 self._fetch_clause = self._fetch_clause_options = None

4304 self._limit_clause = self._offset_or_limit_clause(limit)

4305 return self

4306

4307 @_generative

4308 def fetch(

4309 self,

4310 count: _LimitOffsetType,

4311 with_ties: bool = False,

4312 percent: bool = False,

4313 **dialect_kw: Any,

4314 ) -> Self:

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

4316 applied.

4317

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

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

4320 select. This functionality is is currently implemented for Oracle

4321 Database, PostgreSQL, MSSQL.

4322

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

4324

4325 .. note::

4326

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

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

4329

4330 .. versionadded:: 1.4

4331

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

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

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

4335 Pass ``None`` to reset it.

4336

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

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

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

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

4341

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

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

4344

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

4346 may be accepted by dialects.

4347

4348 .. versionadded:: 2.0.41

4349

4350 .. seealso::

4351

4352 :meth:`_sql.GenerativeSelect.limit`

4353

4354 :meth:`_sql.GenerativeSelect.offset`

4355

4356 """

4357 self._validate_dialect_kwargs(dialect_kw)

4358 self._limit_clause = None

4359 if count is None:

4360 self._fetch_clause = self._fetch_clause_options = None

4361 else:

4362 self._fetch_clause = self._offset_or_limit_clause(count)

4363 self._fetch_clause_options = {

4364 "with_ties": with_ties,

4365 "percent": percent,

4366 }

4367 return self

4368

4369 @_generative

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

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

4372 applied.

4373

4374

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

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

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

4378 functionality.

4379

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

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

4382

4383 .. seealso::

4384

4385 :meth:`_sql.GenerativeSelect.limit`

4386

4387 :meth:`_sql.GenerativeSelect.fetch`

4388

4389 """

4390

4391 self._offset_clause = self._offset_or_limit_clause(offset)

4392 return self

4393

4394 @_generative

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

4396 def slice(

4397 self,

4398 start: int,

4399 stop: int,

4400 ) -> Self:

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

4402

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

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

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

4406 query.

4407

4408 For example, ::

4409

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

4411

4412 renders as

4413

4414 .. sourcecode:: sql

4415

4416 SELECT users.id AS users_id,

4417 users.name AS users_name

4418 FROM users ORDER BY users.id

4419 LIMIT ? OFFSET ?

4420 (2, 1)

4421

4422 .. note::

4423

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

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

4426

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

4428 method generalized from the ORM.

4429

4430 .. seealso::

4431

4432 :meth:`_sql.GenerativeSelect.limit`

4433

4434 :meth:`_sql.GenerativeSelect.offset`

4435

4436 :meth:`_sql.GenerativeSelect.fetch`

4437

4438 """

4439 sql_util = util.preloaded.sql_util

4440 self._fetch_clause = self._fetch_clause_options = None

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

4442 self._limit_clause, self._offset_clause, start, stop

4443 )

4444 return self

4445

4446 @_generative

4447 def order_by(

4448 self,

4449 __first: Union[

4450 Literal[None, _NoArg.NO_ARG],

4451 _ColumnExpressionOrStrLabelArgument[Any],

4452 ] = _NoArg.NO_ARG,

4453 /,

4454 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4455 ) -> Self:

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

4457 criteria applied.

4458

4459 e.g.::

4460

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

4462

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

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

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

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

4467

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

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

4470

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

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

4473

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

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

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

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

4478 label matching in ORDER BY and GROUP BY expressions.

4479

4480 .. seealso::

4481

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

4483

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

4485

4486 """

4487

4488 if not clauses and __first is None:

4489 self._order_by_clauses = ()

4490 elif __first is not _NoArg.NO_ARG:

4491 self._order_by_clauses += tuple(

4492 coercions.expect(

4493 roles.OrderByRole, clause, apply_propagate_attrs=self

4494 )

4495 for clause in (__first,) + clauses

4496 )

4497 return self

4498

4499 @_generative

4500 def group_by(

4501 self,

4502 __first: Union[

4503 Literal[None, _NoArg.NO_ARG],

4504 _ColumnExpressionOrStrLabelArgument[Any],

4505 ] = _NoArg.NO_ARG,

4506 /,

4507 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4508 ) -> Self:

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

4510 criterion applied.

4511

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

4513

4514 e.g.::

4515

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

4517

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

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

4520

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

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

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

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

4525 label matching in ORDER BY and GROUP BY expressions.

4526

4527 .. seealso::

4528

4529 :ref:`tutorial_group_by_w_aggregates` - in the

4530 :ref:`unified_tutorial`

4531

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

4533

4534 """ # noqa: E501

4535

4536 if not clauses and __first is None:

4537 self._group_by_clauses = ()

4538 elif __first is not _NoArg.NO_ARG:

4539 self._group_by_clauses += tuple(

4540 coercions.expect(

4541 roles.GroupByRole, clause, apply_propagate_attrs=self

4542 )

4543 for clause in (__first,) + clauses

4544 )

4545 return self

4546

4547

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

4549class CompoundSelectState(CompileState):

4550 @util.memoized_property

4551 def _label_resolve_dict(

4552 self,

4553 ) -> Tuple[

4554 Dict[str, ColumnElement[Any]],

4555 Dict[str, ColumnElement[Any]],

4556 Dict[str, ColumnElement[Any]],

4557 ]:

4558 # TODO: this is hacky and slow

4559 hacky_subquery = self.statement.subquery()

4560 hacky_subquery.named_with_column = False

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

4562 return d, d, d

4563

4564

4565class _CompoundSelectKeyword(Enum):

4566 UNION = "UNION"

4567 UNION_ALL = "UNION ALL"

4568 EXCEPT = "EXCEPT"

4569 EXCEPT_ALL = "EXCEPT ALL"

4570 INTERSECT = "INTERSECT"

4571 INTERSECT_ALL = "INTERSECT ALL"

4572

4573

4574class CompoundSelect(

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

4576):

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

4578 SELECT-based set operations.

4579

4580

4581 .. seealso::

4582

4583 :func:`_expression.union`

4584

4585 :func:`_expression.union_all`

4586

4587 :func:`_expression.intersect`

4588

4589 :func:`_expression.intersect_all`

4590

4591 :func:`_expression.except`

4592

4593 :func:`_expression.except_all`

4594

4595 """

4596

4597 __visit_name__ = "compound_select"

4598

4599 _traverse_internals: _TraverseInternalsType = (

4600 [

4601 ("selects", InternalTraversal.dp_clauseelement_list),

4602 ("_limit_clause", InternalTraversal.dp_clauseelement),

4603 ("_offset_clause", InternalTraversal.dp_clauseelement),

4604 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4605 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4606 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4607 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4608 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4609 ("keyword", InternalTraversal.dp_string),

4610 ]

4611 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4612 + HasCTE._has_ctes_traverse_internals

4613 + DialectKWArgs._dialect_kwargs_traverse_internals

4614 + ExecutableStatement._executable_traverse_internals

4615 )

4616

4617 selects: List[SelectBase]

4618

4619 _is_from_container = True

4620 _auto_correlate = False

4621

4622 def __init__(

4623 self,

4624 keyword: _CompoundSelectKeyword,

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

4626 ):

4627 self.keyword = keyword

4628 self.selects = [

4629 coercions.expect(

4630 roles.CompoundElementRole, s, apply_propagate_attrs=self

4631 ).self_group(against=self)

4632 for s in selects

4633 ]

4634

4635 GenerativeSelect.__init__(self)

4636

4637 @classmethod

4638 def _create_union(

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

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

4641 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4642

4643 @classmethod

4644 def _create_union_all(

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

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

4647 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4648

4649 @classmethod

4650 def _create_except(

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

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

4653 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4654

4655 @classmethod

4656 def _create_except_all(

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

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

4659 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4660

4661 @classmethod

4662 def _create_intersect(

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

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

4665 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4666

4667 @classmethod

4668 def _create_intersect_all(

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

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

4671 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4672

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

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

4675

4676 def self_group(

4677 self, against: Optional[OperatorType] = None

4678 ) -> GroupedElement:

4679 return SelectStatementGrouping(self)

4680

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

4682 for s in self.selects:

4683 if s.is_derived_from(fromclause):

4684 return True

4685 return False

4686

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

4688 if self._label_style is not style:

4689 self = self._generate()

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

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

4692

4693 return self

4694

4695 def _ensure_disambiguated_names(self) -> Self:

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

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

4698 self = self._generate()

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

4700

4701 return self

4702

4703 def _generate_fromclause_column_proxies(

4704 self,

4705 subquery: FromClause,

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

4707 primary_key: ColumnSet,

4708 foreign_keys: Set[KeyedColumnElement[Any]],

4709 *,

4710 proxy_compound_columns: Optional[

4711 Iterable[Sequence[ColumnElement[Any]]]

4712 ] = None,

4713 ) -> None:

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

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

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

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

4718 # generate below.

4719 select_0 = self.selects[0]

4720

4721 if self._label_style is not LABEL_STYLE_DEFAULT:

4722 select_0 = select_0.set_label_style(self._label_style)

4723

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

4725 # derived columns place a 'weight' annotation corresponding

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

4727 # that the corresponding_column() operation can resolve

4728 # conflicts

4729 extra_col_iterator = zip(

4730 *[

4731 [

4732 c._annotate(dd)

4733 for c in stmt._all_selected_columns

4734 if is_column_element(c)

4735 ]

4736 for dd, stmt in [

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

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

4739 ]

4740 ]

4741 )

4742

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

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

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

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

4747 # compound

4748 select_0._generate_fromclause_column_proxies(

4749 subquery,

4750 columns,

4751 proxy_compound_columns=extra_col_iterator,

4752 primary_key=primary_key,

4753 foreign_keys=foreign_keys,

4754 )

4755

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

4757 super()._refresh_for_new_column(column)

4758 for select in self.selects:

4759 select._refresh_for_new_column(column)

4760

4761 @util.ro_non_memoized_property

4762 def _all_selected_columns(self) -> _SelectIterable:

4763 return self.selects[0]._all_selected_columns

4764

4765 @util.ro_non_memoized_property

4766 def selected_columns(

4767 self,

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

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

4770 representing the columns that

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

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

4773

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

4775 :attr:`_expression.CompoundSelect.selected_columns`

4776 attribute returns the selected

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

4778 statements within the set operation.

4779

4780 .. seealso::

4781

4782 :attr:`_sql.Select.selected_columns`

4783

4784 .. versionadded:: 1.4

4785

4786 """

4787 return self.selects[0].selected_columns

4788

4789

4790# backwards compat

4791for elem in _CompoundSelectKeyword:

4792 setattr(CompoundSelect, elem.name, elem)

4793

4794

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

4796class SelectState(util.MemoizedSlots, CompileState):

4797 __slots__ = (

4798 "from_clauses",

4799 "froms",

4800 "columns_plus_names",

4801 "_label_resolve_dict",

4802 )

4803

4804 if TYPE_CHECKING:

4805 default_select_compile_options: CacheableOptions

4806 else:

4807

4808 class default_select_compile_options(CacheableOptions):

4809 _cache_key_traversal = []

4810

4811 if TYPE_CHECKING:

4812

4813 @classmethod

4814 def get_plugin_class(

4815 cls, statement: Executable

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

4817

4818 def __init__(

4819 self,

4820 statement: Select[Unpack[TupleAny]],

4821 compiler: SQLCompiler,

4822 **kw: Any,

4823 ):

4824 self.statement = statement

4825 self.from_clauses = statement._from_obj

4826

4827 for memoized_entities in statement._memoized_select_entities:

4828 self._setup_joins(

4829 memoized_entities._setup_joins, memoized_entities._raw_columns

4830 )

4831

4832 if statement._setup_joins:

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

4834

4835 self.froms = self._get_froms(statement)

4836

4837 self.columns_plus_names = statement._generate_columns_plus_names(True)

4838

4839 @classmethod

4840 def _plugin_not_implemented(cls) -> NoReturn:

4841 raise NotImplementedError(

4842 "The default SELECT construct without plugins does not "

4843 "implement this method."

4844 )

4845

4846 @classmethod

4847 def get_column_descriptions(

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

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

4850 return [

4851 {

4852 "name": name,

4853 "type": element.type,

4854 "expr": element,

4855 }

4856 for _, name, _, element, _ in (

4857 statement._generate_columns_plus_names(False)

4858 )

4859 ]

4860

4861 @classmethod

4862 def from_statement(

4863 cls,

4864 statement: Select[Unpack[TupleAny]],

4865 from_statement: roles.ReturnsRowsRole,

4866 ) -> ExecutableReturnsRows:

4867 cls._plugin_not_implemented()

4868

4869 @classmethod

4870 def get_columns_clause_froms(

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

4872 ) -> List[FromClause]:

4873 return cls._normalize_froms(

4874 itertools.chain.from_iterable(

4875 element._from_objects for element in statement._raw_columns

4876 )

4877 )

4878

4879 @classmethod

4880 def _column_naming_convention(

4881 cls, label_style: SelectLabelStyle

4882 ) -> _LabelConventionCallable:

4883 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4884

4885 dedupe = label_style is not LABEL_STYLE_NONE

4886

4887 pa = prefix_anon_map()

4888 names = set()

4889

4890 def go(

4891 c: Union[ColumnElement[Any], AbstractTextClause],

4892 col_name: Optional[str] = None,

4893 ) -> Optional[str]:

4894 if is_text_clause(c):

4895 return None

4896 elif TYPE_CHECKING:

4897 assert is_column_element(c)

4898

4899 if not dedupe:

4900 name = c._proxy_key

4901 if name is None:

4902 name = "_no_label"

4903 return name

4904

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

4906

4907 if name is None:

4908 name = "_no_label"

4909 if name in names:

4910 return c._anon_label(name) % pa

4911 else:

4912 names.add(name)

4913 return name

4914

4915 elif name in names:

4916 return (

4917 c._anon_tq_key_label % pa

4918 if table_qualified

4919 else c._anon_key_label % pa

4920 )

4921 else:

4922 names.add(name)

4923 return name

4924

4925 return go

4926

4927 def _get_froms(

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

4929 ) -> List[FromClause]:

4930 ambiguous_table_name_map: _AmbiguousTableNameMap

4931 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4932

4933 return self._normalize_froms(

4934 itertools.chain(

4935 self.from_clauses,

4936 itertools.chain.from_iterable(

4937 [

4938 element._from_objects

4939 for element in statement._raw_columns

4940 ]

4941 ),

4942 itertools.chain.from_iterable(

4943 [

4944 element._from_objects

4945 for element in statement._where_criteria

4946 ]

4947 ),

4948 ),

4949 check_statement=statement,

4950 ambiguous_table_name_map=ambiguous_table_name_map,

4951 )

4952

4953 @classmethod

4954 def _normalize_froms(

4955 cls,

4956 iterable_of_froms: Iterable[FromClause],

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

4958 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

4959 ) -> List[FromClause]:

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

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

4962

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

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

4965 etc.

4966

4967 """

4968 seen: Set[FromClause] = set()

4969 froms: List[FromClause] = []

4970

4971 for item in iterable_of_froms:

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

4973 raise exc.InvalidRequestError(

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

4975 )

4976

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

4978 froms.append(item)

4979 seen.update(item._cloned_set)

4980

4981 if froms:

4982 toremove = set(

4983 itertools.chain.from_iterable(

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

4985 )

4986 )

4987 if toremove:

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

4989 # using a list to maintain ordering

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

4991

4992 if ambiguous_table_name_map is not None:

4993 ambiguous_table_name_map.update(

4994 (

4995 fr.name,

4996 _anonymous_label.safe_construct(

4997 hash(fr.name), fr.name

4998 ),

4999 )

5000 for item in froms

5001 for fr in item._from_objects

5002 if is_table(fr)

5003 and fr.schema

5004 and fr.name not in ambiguous_table_name_map

5005 )

5006

5007 return froms

5008

5009 def _get_display_froms(

5010 self,

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

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

5013 ) -> List[FromClause]:

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

5015

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

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

5018 may want to leave those absent if it is automatically

5019 correlating.

5020

5021 """

5022

5023 froms = self.froms

5024

5025 if self.statement._correlate:

5026 to_correlate = self.statement._correlate

5027 if to_correlate:

5028 froms = [

5029 f

5030 for f in froms

5031 if f

5032 not in _cloned_intersection(

5033 _cloned_intersection(

5034 froms, explicit_correlate_froms or ()

5035 ),

5036 to_correlate,

5037 )

5038 ]

5039

5040 if self.statement._correlate_except is not None:

5041 froms = [

5042 f

5043 for f in froms

5044 if f

5045 not in _cloned_difference(

5046 _cloned_intersection(

5047 froms, explicit_correlate_froms or ()

5048 ),

5049 self.statement._correlate_except,

5050 )

5051 ]

5052

5053 if (

5054 self.statement._auto_correlate

5055 and implicit_correlate_froms

5056 and len(froms) > 1

5057 ):

5058 froms = [

5059 f

5060 for f in froms

5061 if f

5062 not in _cloned_intersection(froms, implicit_correlate_froms)

5063 ]

5064

5065 if not len(froms):

5066 raise exc.InvalidRequestError(

5067 "Select statement '%r"

5068 "' returned no FROM clauses "

5069 "due to auto-correlation; "

5070 "specify correlate(<tables>) "

5071 "to control correlation "

5072 "manually." % self.statement

5073 )

5074

5075 return froms

5076

5077 def _memoized_attr__label_resolve_dict(

5078 self,

5079 ) -> Tuple[

5080 Dict[str, ColumnElement[Any]],

5081 Dict[str, ColumnElement[Any]],

5082 Dict[str, ColumnElement[Any]],

5083 ]:

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

5085 c._tq_label or c.key: c

5086 for c in self.statement._all_selected_columns

5087 if c._allow_label_resolve

5088 }

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

5090 c.key: c # type: ignore

5091 for c in _select_iterables(self.froms)

5092 if c._allow_label_resolve

5093 }

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

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

5096 with_cols.setdefault(key, value)

5097

5098 return with_cols, only_froms, only_cols

5099

5100 @classmethod

5101 def _get_filter_by_entities(

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

5103 ) -> Collection[

5104 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5105 ]:

5106 """Return all entities to search for filter_by() attributes.

5107

5108 This includes:

5109

5110 * All joined entities from _setup_joins

5111 * Memoized entities from previous operations (e.g.,

5112 before with_only_columns)

5113 * Explicit FROM objects from _from_obj

5114 * Entities inferred from _raw_columns

5115

5116 .. versionadded:: 2.1

5117

5118 """

5119 entities: set[

5120 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5121 ]

5122

5123 entities = set(

5124 join_element[0] for join_element in statement._setup_joins

5125 )

5126

5127 for memoized in statement._memoized_select_entities:

5128 entities.update(

5129 join_element[0] for join_element in memoized._setup_joins

5130 )

5131

5132 entities.update(statement._from_obj)

5133

5134 for col in statement._raw_columns:

5135 entities.update(col._from_objects)

5136

5137 return entities

5138

5139 @classmethod

5140 def all_selected_columns(

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

5142 ) -> _SelectIterable:

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

5144

5145 def _setup_joins(

5146 self,

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

5148 raw_columns: List[_ColumnsClauseElement],

5149 ) -> None:

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

5151 if TYPE_CHECKING:

5152 if onclause is not None:

5153 assert isinstance(onclause, ColumnElement)

5154

5155 explicit_left = left

5156 isouter = flags["isouter"]

5157 full = flags["full"]

5158

5159 if left is None:

5160 (

5161 left,

5162 replace_from_obj_index,

5163 ) = self._join_determine_implicit_left_side(

5164 raw_columns, left, right, onclause

5165 )

5166 else:

5167 (replace_from_obj_index) = self._join_place_explicit_left_side(

5168 left

5169 )

5170

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

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

5173 # upgraded to an ORM select, and this method would not be called;

5174 # orm.context.ORMSelectCompileState._join() would be

5175 # used instead.

5176 if TYPE_CHECKING:

5177 assert isinstance(right, FromClause)

5178 if onclause is not None:

5179 assert isinstance(onclause, ColumnElement)

5180

5181 if replace_from_obj_index is not None:

5182 # splice into an existing element in the

5183 # self._from_obj list

5184 left_clause = self.from_clauses[replace_from_obj_index]

5185

5186 if explicit_left is not None and onclause is None:

5187 onclause = Join._join_condition(explicit_left, right)

5188

5189 self.from_clauses = (

5190 self.from_clauses[:replace_from_obj_index]

5191 + (

5192 Join(

5193 left_clause,

5194 right,

5195 onclause,

5196 isouter=isouter,

5197 full=full,

5198 ),

5199 )

5200 + self.from_clauses[replace_from_obj_index + 1 :]

5201 )

5202 else:

5203 assert left is not None

5204 self.from_clauses = self.from_clauses + (

5205 Join(left, right, onclause, isouter=isouter, full=full),

5206 )

5207

5208 @util.preload_module("sqlalchemy.sql.util")

5209 def _join_determine_implicit_left_side(

5210 self,

5211 raw_columns: List[_ColumnsClauseElement],

5212 left: Optional[FromClause],

5213 right: _JoinTargetElement,

5214 onclause: Optional[ColumnElement[Any]],

5215 ) -> Tuple[Optional[FromClause], Optional[int]]:

5216 """When join conditions don't express the left side explicitly,

5217 determine if an existing FROM or entity in this query

5218 can serve as the left hand side.

5219

5220 """

5221

5222 sql_util = util.preloaded.sql_util

5223

5224 replace_from_obj_index: Optional[int] = None

5225

5226 from_clauses = self.from_clauses

5227

5228 if from_clauses:

5229 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5230 from_clauses, right, onclause

5231 )

5232

5233 if len(indexes) == 1:

5234 replace_from_obj_index = indexes[0]

5235 left = from_clauses[replace_from_obj_index]

5236 else:

5237 potential = {}

5238 statement = self.statement

5239

5240 for from_clause in itertools.chain(

5241 itertools.chain.from_iterable(

5242 [element._from_objects for element in raw_columns]

5243 ),

5244 itertools.chain.from_iterable(

5245 [

5246 element._from_objects

5247 for element in statement._where_criteria

5248 ]

5249 ),

5250 ):

5251 potential[from_clause] = ()

5252

5253 all_clauses = list(potential.keys())

5254 indexes = sql_util.find_left_clause_to_join_from(

5255 all_clauses, right, onclause

5256 )

5257

5258 if len(indexes) == 1:

5259 left = all_clauses[indexes[0]]

5260

5261 if len(indexes) > 1:

5262 raise exc.InvalidRequestError(

5263 "Can't determine which FROM clause to join "

5264 "from, there are multiple FROMS which can "

5265 "join to this entity. Please use the .select_from() "

5266 "method to establish an explicit left side, as well as "

5267 "providing an explicit ON clause if not present already to "

5268 "help resolve the ambiguity."

5269 )

5270 elif not indexes:

5271 raise exc.InvalidRequestError(

5272 "Don't know how to join to %r. "

5273 "Please use the .select_from() "

5274 "method to establish an explicit left side, as well as "

5275 "providing an explicit ON clause if not present already to "

5276 "help resolve the ambiguity." % (right,)

5277 )

5278 return left, replace_from_obj_index

5279

5280 @util.preload_module("sqlalchemy.sql.util")

5281 def _join_place_explicit_left_side(

5282 self, left: FromClause

5283 ) -> Optional[int]:

5284 replace_from_obj_index: Optional[int] = None

5285

5286 sql_util = util.preloaded.sql_util

5287

5288 from_clauses = list(self.statement._iterate_from_elements())

5289

5290 if from_clauses:

5291 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5292 self.from_clauses, left

5293 )

5294 else:

5295 indexes = []

5296

5297 if len(indexes) > 1:

5298 raise exc.InvalidRequestError(

5299 "Can't identify which entity in which to assign the "

5300 "left side of this join. Please use a more specific "

5301 "ON clause."

5302 )

5303

5304 # have an index, means the left side is already present in

5305 # an existing FROM in the self._from_obj tuple

5306 if indexes:

5307 replace_from_obj_index = indexes[0]

5308

5309 # no index, means we need to add a new element to the

5310 # self._from_obj tuple

5311

5312 return replace_from_obj_index

5313

5314

5315class _SelectFromElements:

5316 __slots__ = ()

5317

5318 _raw_columns: List[_ColumnsClauseElement]

5319 _where_criteria: Tuple[ColumnElement[Any], ...]

5320 _from_obj: Tuple[FromClause, ...]

5321

5322 def _iterate_from_elements(self) -> Iterator[FromClause]:

5323 # note this does not include elements

5324 # in _setup_joins

5325

5326 seen = set()

5327 for element in self._raw_columns:

5328 for fr in element._from_objects:

5329 if fr in seen:

5330 continue

5331 seen.add(fr)

5332 yield fr

5333 for element in self._where_criteria:

5334 for fr in element._from_objects:

5335 if fr in seen:

5336 continue

5337 seen.add(fr)

5338 yield fr

5339 for element in self._from_obj:

5340 if element in seen:

5341 continue

5342 seen.add(element)

5343 yield element

5344

5345

5346class _MemoizedSelectEntities(

5347 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5348):

5349 """represents partial state from a Select object, for the case

5350 where Select.columns() has redefined the set of columns/entities the

5351 statement will be SELECTing from. This object represents

5352 the entities from the SELECT before that transformation was applied,

5353 so that transformations that were made in terms of the SELECT at that

5354 time, such as join() as well as options(), can access the correct context.

5355

5356 In previous SQLAlchemy versions, this wasn't needed because these

5357 constructs calculated everything up front, like when you called join()

5358 or options(), it did everything to figure out how that would translate

5359 into specific SQL constructs that would be ready to send directly to the

5360 SQL compiler when needed. But as of

5361 1.4, all of that stuff is done in the compilation phase, during the

5362 "compile state" portion of the process, so that the work can all be

5363 cached. So it needs to be able to resolve joins/options2 based on what

5364 the list of entities was when those methods were called.

5365

5366

5367 """

5368

5369 __visit_name__ = "memoized_select_entities"

5370

5371 _traverse_internals: _TraverseInternalsType = [

5372 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5373 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5374 ("_with_options", InternalTraversal.dp_executable_options),

5375 ]

5376

5377 _is_clone_of: Optional[ClauseElement]

5378 _raw_columns: List[_ColumnsClauseElement]

5379 _setup_joins: Tuple[_SetupJoinsElement, ...]

5380 _with_options: Tuple[ExecutableOption, ...]

5381

5382 _annotations = util.EMPTY_DICT

5383

5384 def _clone(self, **kw: Any) -> Self:

5385 c = self.__class__.__new__(self.__class__)

5386 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5387

5388 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5389 return c

5390

5391 @classmethod

5392 def _generate_for_statement(

5393 cls, select_stmt: Select[Unpack[TupleAny]]

5394 ) -> None:

5395 if select_stmt._setup_joins or select_stmt._with_options:

5396 self = _MemoizedSelectEntities()

5397 self._raw_columns = select_stmt._raw_columns

5398 self._setup_joins = select_stmt._setup_joins

5399 self._with_options = select_stmt._with_options

5400

5401 select_stmt._memoized_select_entities += (self,)

5402 select_stmt._raw_columns = []

5403 select_stmt._setup_joins = select_stmt._with_options = ()

5404

5405

5406class Select(

5407 HasPrefixes,

5408 HasSuffixes,

5409 HasHints,

5410 HasCompileState,

5411 HasSyntaxExtensions[

5412 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5413 ],

5414 _SelectFromElements,

5415 GenerativeSelect,

5416 TypedReturnsRows[Unpack[_Ts]],

5417):

5418 """Represents a ``SELECT`` statement.

5419

5420 The :class:`_sql.Select` object is normally constructed using the

5421 :func:`_sql.select` function. See that function for details.

5422

5423 Available extension points:

5424

5425 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5426 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5427 keyword (if any) and the list of columns.

5428 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5429 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5430

5431 .. seealso::

5432

5433 :func:`_sql.select`

5434

5435 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5436

5437 """

5438

5439 __visit_name__ = "select"

5440

5441 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5442 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5443

5444 _raw_columns: List[_ColumnsClauseElement]

5445

5446 _distinct: bool = False

5447 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5448 _correlate: Tuple[FromClause, ...] = ()

5449 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5450 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5451 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5452 _from_obj: Tuple[FromClause, ...] = ()

5453

5454 _position_map = util.immutabledict(

5455 {

5456 "post_select": "_post_select_clause",

5457 "pre_columns": "_pre_columns_clause",

5458 "post_criteria": "_post_criteria_clause",

5459 "post_body": "_post_body_clause",

5460 }

5461 )

5462

5463 _post_select_clause: Optional[ClauseElement] = None

5464 """extension point for a ClauseElement that will be compiled directly

5465 after the SELECT keyword.

5466

5467 .. versionadded:: 2.1

5468

5469 """

5470

5471 _pre_columns_clause: Optional[ClauseElement] = None

5472 """extension point for a ClauseElement that will be compiled directly

5473 before the "columns" clause; after DISTINCT (if present).

5474

5475 .. versionadded:: 2.1

5476

5477 """

5478

5479 _post_criteria_clause: Optional[ClauseElement] = None

5480 """extension point for a ClauseElement that will be compiled directly

5481 after "criteria", following the HAVING clause but before ORDER BY.

5482

5483 .. versionadded:: 2.1

5484

5485 """

5486

5487 _post_body_clause: Optional[ClauseElement] = None

5488 """extension point for a ClauseElement that will be compiled directly

5489 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5490 of the SELECT.

5491

5492 .. versionadded:: 2.1

5493

5494 """

5495

5496 _auto_correlate = True

5497 _is_select_statement = True

5498 _compile_options: CacheableOptions = (

5499 SelectState.default_select_compile_options

5500 )

5501

5502 _traverse_internals: _TraverseInternalsType = (

5503 [

5504 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5505 (

5506 "_memoized_select_entities",

5507 InternalTraversal.dp_memoized_select_entities,

5508 ),

5509 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5510 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5511 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5512 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5513 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5514 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5515 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5516 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5517 ("_limit_clause", InternalTraversal.dp_clauseelement),

5518 ("_offset_clause", InternalTraversal.dp_clauseelement),

5519 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5520 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5521 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5522 ("_distinct", InternalTraversal.dp_boolean),

5523 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5524 ("_label_style", InternalTraversal.dp_plain_obj),

5525 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5526 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5527 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5528 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5529 ]

5530 + HasCTE._has_ctes_traverse_internals

5531 + HasPrefixes._has_prefixes_traverse_internals

5532 + HasSuffixes._has_suffixes_traverse_internals

5533 + HasHints._has_hints_traverse_internals

5534 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5535 + ExecutableStatement._executable_traverse_internals

5536 + DialectKWArgs._dialect_kwargs_traverse_internals

5537 )

5538

5539 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5540 ("_compile_options", InternalTraversal.dp_has_cache_key)

5541 ]

5542

5543 _compile_state_factory: Type[SelectState]

5544

5545 @classmethod

5546 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5547 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5548

5549 Used internally to build up :class:`.Select` constructs with

5550 pre-established state.

5551

5552 """

5553

5554 stmt = Select.__new__(Select)

5555 stmt.__dict__.update(kw)

5556 return stmt

5557

5558 def __init__(

5559 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5560 ):

5561 r"""Construct a new :class:`_expression.Select`.

5562

5563 The public constructor for :class:`_expression.Select` is the

5564 :func:`_sql.select` function.

5565

5566 """

5567 self._raw_columns = [

5568 coercions.expect(

5569 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5570 )

5571 for ent in entities

5572 ]

5573 GenerativeSelect.__init__(self)

5574

5575 def _apply_syntax_extension_to_self(

5576 self, extension: SyntaxExtension

5577 ) -> None:

5578 extension.apply_to_select(self)

5579

5580 def _scalar_type(self) -> TypeEngine[Any]:

5581 if not self._raw_columns:

5582 return NULLTYPE

5583 elem = self._raw_columns[0]

5584 cols = list(elem._select_iterable)

5585 return cols[0].type

5586

5587 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5588 """A synonym for the :meth:`_sql.Select.where` method."""

5589

5590 return self.where(*criteria)

5591

5592 if TYPE_CHECKING:

5593

5594 @overload

5595 def scalar_subquery(

5596 self: Select[_MAYBE_ENTITY],

5597 ) -> ScalarSelect[Any]: ...

5598

5599 @overload

5600 def scalar_subquery(

5601 self: Select[_NOT_ENTITY],

5602 ) -> ScalarSelect[_NOT_ENTITY]: ...

5603

5604 @overload

5605 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5606

5607 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5608

5609 def filter_by(self, **kwargs: Any) -> Self:

5610 r"""Apply the given filtering criterion as a WHERE clause

5611 to this select, using keyword expressions.

5612

5613 E.g.::

5614

5615 stmt = select(User).filter_by(name="some name")

5616

5617 Multiple criteria may be specified as comma separated; the effect

5618 is that they will be joined together using the :func:`.and_`

5619 function::

5620

5621 stmt = select(User).filter_by(name="some name", id=5)

5622

5623 The keyword expressions are extracted by searching across **all

5624 entities present in the FROM clause** of the statement. If a

5625 keyword name is present in more than one entity,

5626 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5627 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5628 explicit column references::

5629

5630 # both User and Address have an 'id' attribute

5631 stmt = select(User).join(Address).filter_by(id=5)

5632 # raises AmbiguousColumnError

5633

5634 # use filter() with explicit qualification instead

5635 stmt = select(User).join(Address).filter(Address.id == 5)

5636

5637 .. versionchanged:: 2.1

5638

5639 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5640 entities rather than only searching the last joined entity or first

5641 FROM entity. This allows the method to locate attributes

5642 unambiguously across multiple joined tables. The new

5643 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5644 is present in more than one entity.

5645

5646 See :ref:`change_8601` for migration notes.

5647

5648 .. seealso::

5649

5650 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

5651

5652 :meth:`_sql.Select.filter` - filter on SQL expressions.

5653

5654 :meth:`_sql.Select.where` - filter on SQL expressions.

5655

5656 """

5657 # Get all entities via plugin system

5658 all_entities = SelectState.get_plugin_class(

5659 self

5660 )._get_filter_by_entities(self)

5661

5662 clauses = [

5663 _entity_namespace_key_search_all(all_entities, key) == value

5664 for key, value in kwargs.items()

5665 ]

5666 return self.filter(*clauses)

5667

5668 @property

5669 def column_descriptions(self) -> Any:

5670 """Return a :term:`plugin-enabled` 'column descriptions' structure

5671 referring to the columns which are SELECTed by this statement.

5672

5673 This attribute is generally useful when using the ORM, as an

5674 extended structure which includes information about mapped

5675 entities is returned. The section :ref:`queryguide_inspection`

5676 contains more background.

5677

5678 For a Core-only statement, the structure returned by this accessor

5679 is derived from the same objects that are returned by the

5680 :attr:`.Select.selected_columns` accessor, formatted as a list of

5681 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5682 which indicate the column expressions to be selected::

5683

5684 >>> stmt = select(user_table)

5685 >>> stmt.column_descriptions

5686 [

5687 {

5688 'name': 'id',

5689 'type': Integer(),

5690 'expr': Column('id', Integer(), ...)},

5691 {

5692 'name': 'name',

5693 'type': String(length=30),

5694 'expr': Column('name', String(length=30), ...)}

5695 ]

5696

5697 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5698 attribute returns a structure for a Core-only set of entities,

5699 not just ORM-only entities.

5700

5701 .. seealso::

5702

5703 :attr:`.UpdateBase.entity_description` - entity information for

5704 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5705

5706 :ref:`queryguide_inspection` - ORM background

5707

5708 """

5709 meth = SelectState.get_plugin_class(self).get_column_descriptions

5710 return meth(self)

5711

5712 def from_statement(

5713 self, statement: roles.ReturnsRowsRole

5714 ) -> ExecutableReturnsRows:

5715 """Apply the columns which this :class:`.Select` would select

5716 onto another statement.

5717

5718 This operation is :term:`plugin-specific` and will raise a not

5719 supported exception if this :class:`_sql.Select` does not select from

5720 plugin-enabled entities.

5721

5722

5723 The statement is typically either a :func:`_expression.text` or

5724 :func:`_expression.select` construct, and should return the set of

5725 columns appropriate to the entities represented by this

5726 :class:`.Select`.

5727

5728 .. seealso::

5729

5730 :ref:`orm_queryguide_selecting_text` - usage examples in the

5731 ORM Querying Guide

5732

5733 """

5734 meth = SelectState.get_plugin_class(self).from_statement

5735 return meth(self, statement)

5736

5737 @_generative

5738 def join(

5739 self,

5740 target: _JoinTargetArgument,

5741 onclause: Optional[_OnClauseArgument] = None,

5742 *,

5743 isouter: bool = False,

5744 full: bool = False,

5745 ) -> Self:

5746 r"""Create a SQL JOIN against this :class:`_expression.Select`

5747 object's criterion

5748 and apply generatively, returning the newly resulting

5749 :class:`_expression.Select`.

5750

5751 E.g.::

5752

5753 stmt = select(user_table).join(

5754 address_table, user_table.c.id == address_table.c.user_id

5755 )

5756

5757 The above statement generates SQL similar to:

5758

5759 .. sourcecode:: sql

5760

5761 SELECT user.id, user.name

5762 FROM user

5763 JOIN address ON user.id = address.user_id

5764

5765 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5766 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5767 source that is within the FROM clause of the existing SELECT,

5768 and a given target :class:`_sql.FromClause`, and then adds

5769 this :class:`_sql.Join` to the FROM clause of the newly generated

5770 SELECT statement. This is completely reworked from the behavior

5771 in 1.3, which would instead create a subquery of the entire

5772 :class:`_expression.Select` and then join that subquery to the

5773 target.

5774

5775 This is a **backwards incompatible change** as the previous behavior

5776 was mostly useless, producing an unnamed subquery rejected by

5777 most databases in any case. The new behavior is modeled after

5778 that of the very successful :meth:`_orm.Query.join` method in the

5779 ORM, in order to support the functionality of :class:`_orm.Query`

5780 being available by using a :class:`_sql.Select` object with an

5781 :class:`_orm.Session`.

5782

5783 See the notes for this change at :ref:`change_select_join`.

5784

5785

5786 :param target: target table to join towards

5787

5788 :param onclause: ON clause of the join. If omitted, an ON clause

5789 is generated automatically based on the :class:`_schema.ForeignKey`

5790 linkages between the two tables, if one can be unambiguously

5791 determined, otherwise an error is raised.

5792

5793 :param isouter: if True, generate LEFT OUTER join. Same as

5794 :meth:`_expression.Select.outerjoin`.

5795

5796 :param full: if True, generate FULL OUTER join.

5797

5798 .. seealso::

5799

5800 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5801

5802 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5803

5804 :meth:`_expression.Select.join_from`

5805

5806 :meth:`_expression.Select.outerjoin`

5807

5808 """ # noqa: E501

5809 join_target = coercions.expect(

5810 roles.JoinTargetRole, target, apply_propagate_attrs=self

5811 )

5812 if onclause is not None:

5813 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5814 else:

5815 onclause_element = None

5816

5817 self._setup_joins += (

5818 (

5819 join_target,

5820 onclause_element,

5821 None,

5822 {"isouter": isouter, "full": full},

5823 ),

5824 )

5825 return self

5826

5827 def outerjoin_from(

5828 self,

5829 from_: _FromClauseArgument,

5830 target: _JoinTargetArgument,

5831 onclause: Optional[_OnClauseArgument] = None,

5832 *,

5833 full: bool = False,

5834 ) -> Self:

5835 r"""Create a SQL LEFT OUTER JOIN against this

5836 :class:`_expression.Select` object's criterion and apply generatively,

5837 returning the newly resulting :class:`_expression.Select`.

5838

5839 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5840

5841 """

5842 return self.join_from(

5843 from_, target, onclause=onclause, isouter=True, full=full

5844 )

5845

5846 @_generative

5847 def join_from(

5848 self,

5849 from_: _FromClauseArgument,

5850 target: _JoinTargetArgument,

5851 onclause: Optional[_OnClauseArgument] = None,

5852 *,

5853 isouter: bool = False,

5854 full: bool = False,

5855 ) -> Self:

5856 r"""Create a SQL JOIN against this :class:`_expression.Select`

5857 object's criterion

5858 and apply generatively, returning the newly resulting

5859 :class:`_expression.Select`.

5860

5861 E.g.::

5862

5863 stmt = select(user_table, address_table).join_from(

5864 user_table, address_table, user_table.c.id == address_table.c.user_id

5865 )

5866

5867 The above statement generates SQL similar to:

5868

5869 .. sourcecode:: sql

5870

5871 SELECT user.id, user.name, address.id, address.email, address.user_id

5872 FROM user JOIN address ON user.id = address.user_id

5873

5874 .. versionadded:: 1.4

5875

5876 :param from\_: the left side of the join, will be rendered in the

5877 FROM clause and is roughly equivalent to using the

5878 :meth:`.Select.select_from` method.

5879

5880 :param target: target table to join towards

5881

5882 :param onclause: ON clause of the join.

5883

5884 :param isouter: if True, generate LEFT OUTER join. Same as

5885 :meth:`_expression.Select.outerjoin`.

5886

5887 :param full: if True, generate FULL OUTER join.

5888

5889 .. seealso::

5890

5891 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5892

5893 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5894

5895 :meth:`_expression.Select.join`

5896

5897 """ # noqa: E501

5898

5899 # note the order of parsing from vs. target is important here, as we

5900 # are also deriving the source of the plugin (i.e. the subject mapper

5901 # in an ORM query) which should favor the "from_" over the "target"

5902

5903 from_ = coercions.expect(

5904 roles.FromClauseRole, from_, apply_propagate_attrs=self

5905 )

5906 join_target = coercions.expect(

5907 roles.JoinTargetRole, target, apply_propagate_attrs=self

5908 )

5909 if onclause is not None:

5910 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5911 else:

5912 onclause_element = None

5913

5914 self._setup_joins += (

5915 (

5916 join_target,

5917 onclause_element,

5918 from_,

5919 {"isouter": isouter, "full": full},

5920 ),

5921 )

5922 return self

5923

5924 def outerjoin(

5925 self,

5926 target: _JoinTargetArgument,

5927 onclause: Optional[_OnClauseArgument] = None,

5928 *,

5929 full: bool = False,

5930 ) -> Self:

5931 """Create a left outer join.

5932

5933 Parameters are the same as that of :meth:`_expression.Select.join`.

5934

5935 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5936 creates a :class:`_sql.Join` object between a

5937 :class:`_sql.FromClause` source that is within the FROM clause of

5938 the existing SELECT, and a given target :class:`_sql.FromClause`,

5939 and then adds this :class:`_sql.Join` to the FROM clause of the

5940 newly generated SELECT statement. This is completely reworked

5941 from the behavior in 1.3, which would instead create a subquery of

5942 the entire

5943 :class:`_expression.Select` and then join that subquery to the

5944 target.

5945

5946 This is a **backwards incompatible change** as the previous behavior

5947 was mostly useless, producing an unnamed subquery rejected by

5948 most databases in any case. The new behavior is modeled after

5949 that of the very successful :meth:`_orm.Query.join` method in the

5950 ORM, in order to support the functionality of :class:`_orm.Query`

5951 being available by using a :class:`_sql.Select` object with an

5952 :class:`_orm.Session`.

5953

5954 See the notes for this change at :ref:`change_select_join`.

5955

5956 .. seealso::

5957

5958 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5959

5960 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5961

5962 :meth:`_expression.Select.join`

5963

5964 """

5965 return self.join(target, onclause=onclause, isouter=True, full=full)

5966

5967 def get_final_froms(self) -> Sequence[FromClause]:

5968 """Compute the final displayed list of :class:`_expression.FromClause`

5969 elements.

5970

5971 This method will run through the full computation required to

5972 determine what FROM elements will be displayed in the resulting

5973 SELECT statement, including shadowing individual tables with

5974 JOIN objects, as well as full computation for ORM use cases including

5975 eager loading clauses.

5976

5977 For ORM use, this accessor returns the **post compilation**

5978 list of FROM objects; this collection will include elements such as

5979 eagerly loaded tables and joins. The objects will **not** be

5980 ORM enabled and not work as a replacement for the

5981 :meth:`_sql.Select.select_froms` collection; additionally, the

5982 method is not well performing for an ORM enabled statement as it

5983 will incur the full ORM construction process.

5984

5985 To retrieve the FROM list that's implied by the "columns" collection

5986 passed to the :class:`_sql.Select` originally, use the

5987 :attr:`_sql.Select.columns_clause_froms` accessor.

5988

5989 To select from an alternative set of columns while maintaining the

5990 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

5991 pass the

5992 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

5993 parameter.

5994

5995 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

5996 method replaces the previous :attr:`_sql.Select.froms` accessor,

5997 which is deprecated.

5998

5999 .. seealso::

6000

6001 :attr:`_sql.Select.columns_clause_froms`

6002

6003 """

6004 compiler = self._default_compiler()

6005

6006 return self._compile_state_factory(self, compiler)._get_display_froms()

6007

6008 @property

6009 @util.deprecated(

6010 "1.4.23",

6011 "The :attr:`_expression.Select.froms` attribute is moved to "

6012 "the :meth:`_expression.Select.get_final_froms` method.",

6013 )

6014 def froms(self) -> Sequence[FromClause]:

6015 """Return the displayed list of :class:`_expression.FromClause`

6016 elements.

6017

6018

6019 """

6020 return self.get_final_froms()

6021

6022 @property

6023 def columns_clause_froms(self) -> List[FromClause]:

6024 """Return the set of :class:`_expression.FromClause` objects implied

6025 by the columns clause of this SELECT statement.

6026

6027 .. versionadded:: 1.4.23

6028

6029 .. seealso::

6030

6031 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6032 statement into account

6033

6034 :meth:`_sql.Select.with_only_columns` - makes use of this

6035 collection to set up a new FROM list

6036

6037 """

6038

6039 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6040 self

6041 )

6042

6043 @property

6044 def inner_columns(self) -> _SelectIterable:

6045 """An iterator of all :class:`_expression.ColumnElement`

6046 expressions which would

6047 be rendered into the columns clause of the resulting SELECT statement.

6048

6049 This method is legacy as of 1.4 and is superseded by the

6050 :attr:`_expression.Select.exported_columns` collection.

6051

6052 """

6053

6054 return iter(self._all_selected_columns)

6055

6056 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

6057 if fromclause is not None and self in fromclause._cloned_set:

6058 return True

6059

6060 for f in self._iterate_from_elements():

6061 if f.is_derived_from(fromclause):

6062 return True

6063 return False

6064

6065 def _copy_internals(

6066 self, clone: _CloneCallableType = _clone, **kw: Any

6067 ) -> None:

6068 # Select() object has been cloned and probably adapted by the

6069 # given clone function. Apply the cloning function to internal

6070 # objects

6071

6072 # 1. keep a dictionary of the froms we've cloned, and what

6073 # they've become. This allows us to ensure the same cloned from

6074 # is used when other items such as columns are "cloned"

6075

6076 all_the_froms = set(

6077 itertools.chain(

6078 _from_objects(*self._raw_columns),

6079 _from_objects(*self._where_criteria),

6080 _from_objects(*[elem[0] for elem in self._setup_joins]),

6081 )

6082 )

6083

6084 # do a clone for the froms we've gathered. what is important here

6085 # is if any of the things we are selecting from, like tables,

6086 # were converted into Join objects. if so, these need to be

6087 # added to _from_obj explicitly, because otherwise they won't be

6088 # part of the new state, as they don't associate themselves with

6089 # their columns.

6090 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6091

6092 # 2. copy FROM collections, adding in joins that we've created.

6093 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6094 add_froms = (

6095 {f for f in new_froms.values() if isinstance(f, Join)}

6096 .difference(all_the_froms)

6097 .difference(existing_from_obj)

6098 )

6099

6100 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6101

6102 # 3. clone everything else, making sure we use columns

6103 # corresponding to the froms we just made.

6104 def replace(

6105 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6106 **kw: Any,

6107 ) -> Optional[KeyedColumnElement[Any]]:

6108 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6109 newelem = new_froms[obj.table].corresponding_column(obj)

6110 return newelem

6111 return None

6112

6113 kw["replace"] = replace

6114

6115 # copy everything else. for table-ish things like correlate,

6116 # correlate_except, setup_joins, these clone normally. For

6117 # column-expression oriented things like raw_columns, where_criteria,

6118 # order by, we get this from the new froms.

6119 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6120

6121 self._reset_memoizations()

6122

6123 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6124 return itertools.chain(

6125 super().get_children(

6126 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6127 **kw,

6128 ),

6129 self._iterate_from_elements(),

6130 )

6131

6132 @_generative

6133 def add_columns(

6134 self, *entities: _ColumnsClauseArgument[Any]

6135 ) -> Select[Unpack[TupleAny]]:

6136 r"""Return a new :func:`_expression.select` construct with

6137 the given entities appended to its columns clause.

6138

6139 E.g.::

6140

6141 my_select = my_select.add_columns(table.c.new_column)

6142

6143 The original expressions in the columns clause remain in place.

6144 To replace the original expressions with new ones, see the method

6145 :meth:`_expression.Select.with_only_columns`.

6146

6147 :param \*entities: column, table, or other entity expressions to be

6148 added to the columns clause

6149

6150 .. seealso::

6151

6152 :meth:`_expression.Select.with_only_columns` - replaces existing

6153 expressions rather than appending.

6154

6155 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6156 example

6157

6158 """

6159 self._reset_memoizations()

6160

6161 self._raw_columns = self._raw_columns + [

6162 coercions.expect(

6163 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6164 )

6165 for column in entities

6166 ]

6167 return self

6168

6169 def _set_entities(

6170 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6171 ) -> None:

6172 self._raw_columns = [

6173 coercions.expect(

6174 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6175 )

6176 for ent in util.to_list(entities)

6177 ]

6178

6179 @util.deprecated(

6180 "1.4",

6181 "The :meth:`_expression.Select.column` method is deprecated and will "

6182 "be removed in a future release. Please use "

6183 ":meth:`_expression.Select.add_columns`",

6184 )

6185 def column(

6186 self, column: _ColumnsClauseArgument[Any]

6187 ) -> Select[Unpack[TupleAny]]:

6188 """Return a new :func:`_expression.select` construct with

6189 the given column expression added to its columns clause.

6190

6191 E.g.::

6192

6193 my_select = my_select.column(table.c.new_column)

6194

6195 See the documentation for

6196 :meth:`_expression.Select.with_only_columns`

6197 for guidelines on adding /replacing the columns of a

6198 :class:`_expression.Select` object.

6199

6200 """

6201 return self.add_columns(column)

6202

6203 @util.preload_module("sqlalchemy.sql.util")

6204 def reduce_columns(

6205 self, only_synonyms: bool = True

6206 ) -> Select[Unpack[TupleAny]]:

6207 """Return a new :func:`_expression.select` construct with redundantly

6208 named, equivalently-valued columns removed from the columns clause.

6209

6210 "Redundant" here means two columns where one refers to the

6211 other either based on foreign key, or via a simple equality

6212 comparison in the WHERE clause of the statement. The primary purpose

6213 of this method is to automatically construct a select statement

6214 with all uniquely-named columns, without the need to use

6215 table-qualified labels as

6216 :meth:`_expression.Select.set_label_style`

6217 does.

6218

6219 When columns are omitted based on foreign key, the referred-to

6220 column is the one that's kept. When columns are omitted based on

6221 WHERE equivalence, the first column in the columns clause is the

6222 one that's kept.

6223

6224 :param only_synonyms: when True, limit the removal of columns

6225 to those which have the same name as the equivalent. Otherwise,

6226 all columns that are equivalent to another are removed.

6227

6228 """

6229 woc: Select[Unpack[TupleAny]]

6230 woc = self.with_only_columns(

6231 *util.preloaded.sql_util.reduce_columns(

6232 self._all_selected_columns,

6233 only_synonyms=only_synonyms,

6234 *(self._where_criteria + self._from_obj),

6235 )

6236 )

6237 return woc

6238

6239 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6240

6241 # code within this block is **programmatically,

6242 # statically generated** by tools/generate_tuple_map_overloads.py

6243

6244 @overload

6245 def with_only_columns(

6246 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6247 ) -> Select[_T0]: ...

6248

6249 @overload

6250 def with_only_columns(

6251 self,

6252 __ent0: _TCCA[_T0],

6253 __ent1: _TCCA[_T1],

6254 /,

6255 *,

6256 maintain_column_froms: bool = ...,

6257 ) -> Select[_T0, _T1]: ...

6258

6259 @overload

6260 def with_only_columns(

6261 self,

6262 __ent0: _TCCA[_T0],

6263 __ent1: _TCCA[_T1],

6264 __ent2: _TCCA[_T2],

6265 /,

6266 *,

6267 maintain_column_froms: bool = ...,

6268 ) -> Select[_T0, _T1, _T2]: ...

6269

6270 @overload

6271 def with_only_columns(

6272 self,

6273 __ent0: _TCCA[_T0],

6274 __ent1: _TCCA[_T1],

6275 __ent2: _TCCA[_T2],

6276 __ent3: _TCCA[_T3],

6277 /,

6278 *,

6279 maintain_column_froms: bool = ...,

6280 ) -> Select[_T0, _T1, _T2, _T3]: ...

6281

6282 @overload

6283 def with_only_columns(

6284 self,

6285 __ent0: _TCCA[_T0],

6286 __ent1: _TCCA[_T1],

6287 __ent2: _TCCA[_T2],

6288 __ent3: _TCCA[_T3],

6289 __ent4: _TCCA[_T4],

6290 /,

6291 *,

6292 maintain_column_froms: bool = ...,

6293 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6294

6295 @overload

6296 def with_only_columns(

6297 self,

6298 __ent0: _TCCA[_T0],

6299 __ent1: _TCCA[_T1],

6300 __ent2: _TCCA[_T2],

6301 __ent3: _TCCA[_T3],

6302 __ent4: _TCCA[_T4],

6303 __ent5: _TCCA[_T5],

6304 /,

6305 *,

6306 maintain_column_froms: bool = ...,

6307 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6308

6309 @overload

6310 def with_only_columns(

6311 self,

6312 __ent0: _TCCA[_T0],

6313 __ent1: _TCCA[_T1],

6314 __ent2: _TCCA[_T2],

6315 __ent3: _TCCA[_T3],

6316 __ent4: _TCCA[_T4],

6317 __ent5: _TCCA[_T5],

6318 __ent6: _TCCA[_T6],

6319 /,

6320 *,

6321 maintain_column_froms: bool = ...,

6322 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6323

6324 @overload

6325 def with_only_columns(

6326 self,

6327 __ent0: _TCCA[_T0],

6328 __ent1: _TCCA[_T1],

6329 __ent2: _TCCA[_T2],

6330 __ent3: _TCCA[_T3],

6331 __ent4: _TCCA[_T4],

6332 __ent5: _TCCA[_T5],

6333 __ent6: _TCCA[_T6],

6334 __ent7: _TCCA[_T7],

6335 /,

6336 *entities: _ColumnsClauseArgument[Any],

6337 maintain_column_froms: bool = ...,

6338 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6339

6340 # END OVERLOADED FUNCTIONS self.with_only_columns

6341

6342 @overload

6343 def with_only_columns(

6344 self,

6345 *entities: _ColumnsClauseArgument[Any],

6346 maintain_column_froms: bool = False,

6347 **__kw: Any,

6348 ) -> Select[Unpack[TupleAny]]: ...

6349

6350 @_generative

6351 def with_only_columns(

6352 self,

6353 *entities: _ColumnsClauseArgument[Any],

6354 maintain_column_froms: bool = False,

6355 **__kw: Any,

6356 ) -> Select[Unpack[TupleAny]]:

6357 r"""Return a new :func:`_expression.select` construct with its columns

6358 clause replaced with the given entities.

6359

6360 By default, this method is exactly equivalent to as if the original

6361 :func:`_expression.select` had been called with the given entities.

6362 E.g. a statement::

6363

6364 s = select(table1.c.a, table1.c.b)

6365 s = s.with_only_columns(table1.c.b)

6366

6367 should be exactly equivalent to::

6368

6369 s = select(table1.c.b)

6370

6371 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6372 will also dynamically alter the FROM clause of the

6373 statement if it is not explicitly stated.

6374 To maintain the existing set of FROMs including those implied by the

6375 current columns clause, add the

6376 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6377 parameter::

6378

6379 s = select(table1.c.a, table2.c.b)

6380 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6381

6382 The above parameter performs a transfer of the effective FROMs

6383 in the columns collection to the :meth:`_sql.Select.select_from`

6384 method, as though the following were invoked::

6385

6386 s = select(table1.c.a, table2.c.b)

6387 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6388

6389 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6390 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6391 collection and performs an operation equivalent to the following::

6392

6393 s = select(table1.c.a, table2.c.b)

6394 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6395

6396 :param \*entities: column expressions to be used.

6397

6398 :param maintain_column_froms: boolean parameter that will ensure the

6399 FROM list implied from the current columns clause will be transferred

6400 to the :meth:`_sql.Select.select_from` method first.

6401

6402 .. versionadded:: 1.4.23

6403

6404 """ # noqa: E501

6405

6406 if __kw:

6407 raise _no_kw()

6408

6409 # memoizations should be cleared here as of

6410 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6411 # is the case for now.

6412 self._assert_no_memoizations()

6413

6414 if maintain_column_froms:

6415 self.select_from.non_generative( # type: ignore

6416 self, *self.columns_clause_froms

6417 )

6418

6419 # then memoize the FROMs etc.

6420 _MemoizedSelectEntities._generate_for_statement(self)

6421

6422 self._raw_columns = [

6423 coercions.expect(roles.ColumnsClauseRole, c)

6424 for c in coercions._expression_collection_was_a_list(

6425 "entities", "Select.with_only_columns", entities

6426 )

6427 ]

6428 return self

6429

6430 @property

6431 def whereclause(self) -> Optional[ColumnElement[Any]]:

6432 """Return the completed WHERE clause for this

6433 :class:`_expression.Select` statement.

6434

6435 This assembles the current collection of WHERE criteria

6436 into a single :class:`_expression.BooleanClauseList` construct.

6437

6438

6439 .. versionadded:: 1.4

6440

6441 """

6442

6443 return BooleanClauseList._construct_for_whereclause(

6444 self._where_criteria

6445 )

6446

6447 _whereclause = whereclause

6448

6449 @_generative

6450 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6451 """Return a new :func:`_expression.select` construct with

6452 the given expression added to

6453 its WHERE clause, joined to the existing clause via AND, if any.

6454

6455 """

6456

6457 assert isinstance(self._where_criteria, tuple)

6458

6459 for criterion in whereclause:

6460 where_criteria: ColumnElement[Any] = coercions.expect(

6461 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6462 )

6463 self._where_criteria += (where_criteria,)

6464 return self

6465

6466 @_generative

6467 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6468 """Return a new :func:`_expression.select` construct with

6469 the given expression added to

6470 its HAVING clause, joined to the existing clause via AND, if any.

6471

6472 """

6473

6474 for criterion in having:

6475 having_criteria = coercions.expect(

6476 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6477 )

6478 self._having_criteria += (having_criteria,)

6479 return self

6480

6481 @_generative

6482 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6483 r"""Return a new :func:`_expression.select` construct which

6484 will apply DISTINCT to the SELECT statement overall.

6485

6486 E.g.::

6487

6488 from sqlalchemy import select

6489

6490 stmt = select(users_table.c.id, users_table.c.name).distinct()

6491

6492 The above would produce an statement resembling:

6493

6494 .. sourcecode:: sql

6495

6496 SELECT DISTINCT user.id, user.name FROM user

6497

6498 The method also historically accepted an ``*expr`` parameter which

6499 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6500 This is now replaced using the :func:`_postgresql.distinct_on`

6501 extension::

6502

6503 from sqlalchemy import select

6504 from sqlalchemy.dialects.postgresql import distinct_on

6505

6506 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6507

6508 Using this parameter on other backends which don't support this

6509 syntax will raise an error.

6510

6511 :param \*expr: optional column expressions. When present,

6512 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6513 construct. A deprecation warning and/or :class:`_exc.CompileError`

6514 will be raised on other backends.

6515

6516 .. deprecated:: 2.1 Passing expressions to

6517 :meth:`_sql.Select.distinct` is deprecated, use

6518 :func:`_postgresql.distinct_on` instead.

6519

6520 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6521 and will raise :class:`_exc.CompileError` in a future version.

6522

6523 .. seealso::

6524

6525 :func:`_postgresql.distinct_on`

6526

6527 :meth:`_sql.HasSyntaxExtensions.ext`

6528 """

6529 self._distinct = True

6530 if expr:

6531 warn_deprecated(

6532 "Passing expression to ``distinct`` to generate a "

6533 "DISTINCT ON clause is deprecated. Use instead the "

6534 "``postgresql.distinct_on`` function as an extension.",

6535 "2.1",

6536 )

6537 self._distinct_on = self._distinct_on + tuple(

6538 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6539 for e in expr

6540 )

6541 return self

6542

6543 @_generative

6544 def select_from(self, *froms: _FromClauseArgument) -> Self:

6545 r"""Return a new :func:`_expression.select` construct with the

6546 given FROM expression(s)

6547 merged into its list of FROM objects.

6548

6549 E.g.::

6550

6551 table1 = table("t1", column("a"))

6552 table2 = table("t2", column("b"))

6553 s = select(table1.c.a).select_from(

6554 table1.join(table2, table1.c.a == table2.c.b)

6555 )

6556

6557 The "from" list is a unique set on the identity of each element,

6558 so adding an already present :class:`_schema.Table`

6559 or other selectable

6560 will have no effect. Passing a :class:`_expression.Join` that refers

6561 to an already present :class:`_schema.Table`

6562 or other selectable will have

6563 the effect of concealing the presence of that selectable as

6564 an individual element in the rendered FROM list, instead

6565 rendering it into a JOIN clause.

6566

6567 While the typical purpose of :meth:`_expression.Select.select_from`

6568 is to

6569 replace the default, derived FROM clause with a join, it can

6570 also be called with individual table elements, multiple times

6571 if desired, in the case that the FROM clause cannot be fully

6572 derived from the columns clause::

6573

6574 select(func.count("*")).select_from(table1)

6575

6576 """

6577

6578 self._from_obj += tuple(

6579 coercions.expect(

6580 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6581 )

6582 for fromclause in froms

6583 )

6584 return self

6585

6586 @_generative

6587 def correlate(

6588 self,

6589 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6590 ) -> Self:

6591 r"""Return a new :class:`_expression.Select`

6592 which will correlate the given FROM

6593 clauses to that of an enclosing :class:`_expression.Select`.

6594

6595 Calling this method turns off the :class:`_expression.Select` object's

6596 default behavior of "auto-correlation". Normally, FROM elements

6597 which appear in a :class:`_expression.Select`

6598 that encloses this one via

6599 its :term:`WHERE clause`, ORDER BY, HAVING or

6600 :term:`columns clause` will be omitted from this

6601 :class:`_expression.Select`

6602 object's :term:`FROM clause`.

6603 Setting an explicit correlation collection using the

6604 :meth:`_expression.Select.correlate`

6605 method provides a fixed list of FROM objects

6606 that can potentially take place in this process.

6607

6608 When :meth:`_expression.Select.correlate`

6609 is used to apply specific FROM clauses

6610 for correlation, the FROM elements become candidates for

6611 correlation regardless of how deeply nested this

6612 :class:`_expression.Select`

6613 object is, relative to an enclosing :class:`_expression.Select`

6614 which refers to

6615 the same FROM object. This is in contrast to the behavior of

6616 "auto-correlation" which only correlates to an immediate enclosing

6617 :class:`_expression.Select`.

6618 Multi-level correlation ensures that the link

6619 between enclosed and enclosing :class:`_expression.Select`

6620 is always via

6621 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6622 correlation to take place.

6623

6624 If ``None`` is passed, the :class:`_expression.Select`

6625 object will correlate

6626 none of its FROM entries, and all will render unconditionally

6627 in the local FROM clause.

6628

6629 :param \*fromclauses: one or more :class:`.FromClause` or other

6630 FROM-compatible construct such as an ORM mapped entity to become part

6631 of the correlate collection; alternatively pass a single value

6632 ``None`` to remove all existing correlations.

6633

6634 .. seealso::

6635

6636 :meth:`_expression.Select.correlate_except`

6637

6638 :ref:`tutorial_scalar_subquery`

6639

6640 """

6641

6642 # tests failing when we try to change how these

6643 # arguments are passed

6644

6645 self._auto_correlate = False

6646 if not fromclauses or fromclauses[0] in {None, False}:

6647 if len(fromclauses) > 1:

6648 raise exc.ArgumentError(

6649 "additional FROM objects not accepted when "

6650 "passing None/False to correlate()"

6651 )

6652 self._correlate = ()

6653 else:

6654 self._correlate = self._correlate + tuple(

6655 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6656 )

6657 return self

6658

6659 @_generative

6660 def correlate_except(

6661 self,

6662 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6663 ) -> Self:

6664 r"""Return a new :class:`_expression.Select`

6665 which will omit the given FROM

6666 clauses from the auto-correlation process.

6667

6668 Calling :meth:`_expression.Select.correlate_except` turns off the

6669 :class:`_expression.Select` object's default behavior of

6670 "auto-correlation" for the given FROM elements. An element

6671 specified here will unconditionally appear in the FROM list, while

6672 all other FROM elements remain subject to normal auto-correlation

6673 behaviors.

6674

6675 If ``None`` is passed, or no arguments are passed,

6676 the :class:`_expression.Select` object will correlate all of its

6677 FROM entries.

6678

6679 :param \*fromclauses: a list of one or more

6680 :class:`_expression.FromClause`

6681 constructs, or other compatible constructs (i.e. ORM-mapped

6682 classes) to become part of the correlate-exception collection.

6683

6684 .. seealso::

6685

6686 :meth:`_expression.Select.correlate`

6687

6688 :ref:`tutorial_scalar_subquery`

6689

6690 """

6691

6692 self._auto_correlate = False

6693 if not fromclauses or fromclauses[0] in {None, False}:

6694 if len(fromclauses) > 1:

6695 raise exc.ArgumentError(

6696 "additional FROM objects not accepted when "

6697 "passing None/False to correlate_except()"

6698 )

6699 self._correlate_except = ()

6700 else:

6701 self._correlate_except = (self._correlate_except or ()) + tuple(

6702 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6703 )

6704

6705 return self

6706

6707 @HasMemoized_ro_memoized_attribute

6708 def selected_columns(

6709 self,

6710 ) -> ColumnCollection[str, ColumnElement[Any]]:

6711 """A :class:`_expression.ColumnCollection`

6712 representing the columns that

6713 this SELECT statement or similar construct returns in its result set,

6714 not including :class:`_sql.TextClause` constructs.

6715

6716 This collection differs from the :attr:`_expression.FromClause.columns`

6717 collection of a :class:`_expression.FromClause` in that the columns

6718 within this collection cannot be directly nested inside another SELECT

6719 statement; a subquery must be applied first which provides for the

6720 necessary parenthesization required by SQL.

6721

6722 For a :func:`_expression.select` construct, the collection here is

6723 exactly what would be rendered inside the "SELECT" statement, and the

6724 :class:`_expression.ColumnElement` objects are directly present as they

6725 were given, e.g.::

6726

6727 col1 = column("q", Integer)

6728 col2 = column("p", Integer)

6729 stmt = select(col1, col2)

6730

6731 Above, ``stmt.selected_columns`` would be a collection that contains

6732 the ``col1`` and ``col2`` objects directly. For a statement that is

6733 against a :class:`_schema.Table` or other

6734 :class:`_expression.FromClause`, the collection will use the

6735 :class:`_expression.ColumnElement` objects that are in the

6736 :attr:`_expression.FromClause.c` collection of the from element.

6737

6738 A use case for the :attr:`_sql.Select.selected_columns` collection is

6739 to allow the existing columns to be referenced when adding additional

6740 criteria, e.g.::

6741

6742 def filter_on_id(my_select, id):

6743 return my_select.where(my_select.selected_columns["id"] == id)

6744

6745

6746 stmt = select(MyModel)

6747

6748 # adds "WHERE id=:param" to the statement

6749 stmt = filter_on_id(stmt, 42)

6750

6751 .. note::

6752

6753 The :attr:`_sql.Select.selected_columns` collection does not

6754 include expressions established in the columns clause using the

6755 :func:`_sql.text` construct; these are silently omitted from the

6756 collection. To use plain textual column expressions inside of a

6757 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6758 construct.

6759

6760

6761 .. versionadded:: 1.4

6762

6763 """

6764

6765 # compare to SelectState._generate_columns_plus_names, which

6766 # generates the actual names used in the SELECT string. that

6767 # method is more complex because it also renders columns that are

6768 # fully ambiguous, e.g. same column more than once.

6769 conv = cast(

6770 "Callable[[Any], str]",

6771 SelectState._column_naming_convention(self._label_style),

6772 )

6773

6774 cc: ColumnCollection[str, ColumnElement[Any]] = ColumnCollection(

6775 [

6776 (conv(c), c)

6777 for c in self._all_selected_columns

6778 if is_column_element(c)

6779 ]

6780 )

6781 return cc.as_readonly()

6782

6783 @HasMemoized_ro_memoized_attribute

6784 def _all_selected_columns(self) -> _SelectIterable:

6785 meth = SelectState.get_plugin_class(self).all_selected_columns

6786 return list(meth(self))

6787

6788 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6789 if self._label_style is LABEL_STYLE_NONE:

6790 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6791 return self

6792

6793 def _generate_fromclause_column_proxies(

6794 self,

6795 subquery: FromClause,

6796 columns: ColumnCollection[str, KeyedColumnElement[Any]],

6797 primary_key: ColumnSet,

6798 foreign_keys: Set[KeyedColumnElement[Any]],

6799 *,

6800 proxy_compound_columns: Optional[

6801 Iterable[Sequence[ColumnElement[Any]]]

6802 ] = None,

6803 ) -> None:

6804 """Generate column proxies to place in the exported ``.c``

6805 collection of a subquery."""

6806

6807 if proxy_compound_columns:

6808 extra_col_iterator = proxy_compound_columns

6809 prox = [

6810 c._make_proxy(

6811 subquery,

6812 key=proxy_key,

6813 name=required_label_name,

6814 name_is_truncatable=True,

6815 compound_select_cols=extra_cols,

6816 primary_key=primary_key,

6817 foreign_keys=foreign_keys,

6818 )

6819 for (

6820 (

6821 required_label_name,

6822 proxy_key,

6823 fallback_label_name,

6824 c,

6825 repeated,

6826 ),

6827 extra_cols,

6828 ) in (

6829 zip(

6830 self._generate_columns_plus_names(False),

6831 extra_col_iterator,

6832 )

6833 )

6834 if is_column_element(c)

6835 ]

6836 else:

6837 prox = [

6838 c._make_proxy(

6839 subquery,

6840 key=proxy_key,

6841 name=required_label_name,

6842 name_is_truncatable=True,

6843 primary_key=primary_key,

6844 foreign_keys=foreign_keys,

6845 )

6846 for (

6847 required_label_name,

6848 proxy_key,

6849 fallback_label_name,

6850 c,

6851 repeated,

6852 ) in (self._generate_columns_plus_names(False))

6853 if is_column_element(c)

6854 ]

6855

6856 columns._populate_separate_keys(prox)

6857

6858 def _needs_parens_for_grouping(self) -> bool:

6859 return self._has_row_limiting_clause or bool(

6860 self._order_by_clause.clauses

6861 )

6862

6863 def self_group(

6864 self, against: Optional[OperatorType] = None

6865 ) -> Union[SelectStatementGrouping[Self], Self]:

6866 """Return a 'grouping' construct as per the

6867 :class:`_expression.ClauseElement` specification.

6868

6869 This produces an element that can be embedded in an expression. Note

6870 that this method is called automatically as needed when constructing

6871 expressions and should not require explicit use.

6872

6873 """

6874 if (

6875 isinstance(against, CompoundSelect)

6876 and not self._needs_parens_for_grouping()

6877 ):

6878 return self

6879 else:

6880 return SelectStatementGrouping(self)

6881

6882 def union(

6883 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6884 ) -> CompoundSelect[Unpack[_Ts]]:

6885 r"""Return a SQL ``UNION`` of this select() construct against

6886 the given selectables provided as positional arguments.

6887

6888 :param \*other: one or more elements with which to create a

6889 UNION.

6890

6891 .. versionchanged:: 1.4.28

6892

6893 multiple elements are now accepted.

6894

6895 :param \**kwargs: keyword arguments are forwarded to the constructor

6896 for the newly created :class:`_sql.CompoundSelect` object.

6897

6898 """

6899 return CompoundSelect._create_union(self, *other)

6900

6901 def union_all(

6902 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6903 ) -> CompoundSelect[Unpack[_Ts]]:

6904 r"""Return a SQL ``UNION ALL`` of this select() construct against

6905 the given selectables provided as positional arguments.

6906

6907 :param \*other: one or more elements with which to create a

6908 UNION.

6909

6910 .. versionchanged:: 1.4.28

6911

6912 multiple elements are now accepted.

6913

6914 :param \**kwargs: keyword arguments are forwarded to the constructor

6915 for the newly created :class:`_sql.CompoundSelect` object.

6916

6917 """

6918 return CompoundSelect._create_union_all(self, *other)

6919

6920 def except_(

6921 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6922 ) -> CompoundSelect[Unpack[_Ts]]:

6923 r"""Return a SQL ``EXCEPT`` of this select() construct against

6924 the given selectable provided as positional arguments.

6925

6926 :param \*other: one or more elements with which to create a

6927 UNION.

6928

6929 .. versionchanged:: 1.4.28

6930

6931 multiple elements are now accepted.

6932

6933 """

6934 return CompoundSelect._create_except(self, *other)

6935

6936 def except_all(

6937 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6938 ) -> CompoundSelect[Unpack[_Ts]]:

6939 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6940 the given selectables provided as positional arguments.

6941

6942 :param \*other: one or more elements with which to create a

6943 UNION.

6944

6945 .. versionchanged:: 1.4.28

6946

6947 multiple elements are now accepted.

6948

6949 """

6950 return CompoundSelect._create_except_all(self, *other)

6951

6952 def intersect(

6953 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6954 ) -> CompoundSelect[Unpack[_Ts]]:

6955 r"""Return a SQL ``INTERSECT`` of this select() construct against

6956 the given selectables provided as positional arguments.

6957

6958 :param \*other: one or more elements with which to create a

6959 UNION.

6960

6961 .. versionchanged:: 1.4.28

6962

6963 multiple elements are now accepted.

6964

6965 :param \**kwargs: keyword arguments are forwarded to the constructor

6966 for the newly created :class:`_sql.CompoundSelect` object.

6967

6968 """

6969 return CompoundSelect._create_intersect(self, *other)

6970

6971 def intersect_all(

6972 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6973 ) -> CompoundSelect[Unpack[_Ts]]:

6974 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

6975 against the given selectables provided as positional arguments.

6976

6977 :param \*other: one or more elements with which to create a

6978 UNION.

6979

6980 .. versionchanged:: 1.4.28

6981

6982 multiple elements are now accepted.

6983

6984 :param \**kwargs: keyword arguments are forwarded to the constructor

6985 for the newly created :class:`_sql.CompoundSelect` object.

6986

6987 """

6988 return CompoundSelect._create_intersect_all(self, *other)

6989

6990

6991class ScalarSelect(

6992 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

6993):

6994 """Represent a scalar subquery.

6995

6996

6997 A :class:`_sql.ScalarSelect` is created by invoking the

6998 :meth:`_sql.SelectBase.scalar_subquery` method. The object

6999 then participates in other SQL expressions as a SQL column expression

7000 within the :class:`_sql.ColumnElement` hierarchy.

7001

7002 .. seealso::

7003

7004 :meth:`_sql.SelectBase.scalar_subquery`

7005

7006 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7007

7008 """

7009

7010 _traverse_internals: _TraverseInternalsType = [

7011 ("element", InternalTraversal.dp_clauseelement),

7012 ("type", InternalTraversal.dp_type),

7013 ]

7014

7015 _from_objects: List[FromClause] = []

7016 _is_from_container = True

7017 if not TYPE_CHECKING:

7018 _is_implicitly_boolean = False

7019 inherit_cache = True

7020

7021 element: SelectBase

7022

7023 def __init__(self, element: SelectBase) -> None:

7024 self.element = element

7025 self.type = element._scalar_type()

7026 self._propagate_attrs = element._propagate_attrs

7027

7028 def __getattr__(self, attr: str) -> Any:

7029 return getattr(self.element, attr)

7030

7031 def __getstate__(self) -> Dict[str, Any]:

7032 return {"element": self.element, "type": self.type}

7033

7034 def __setstate__(self, state: Dict[str, Any]) -> None:

7035 self.element = state["element"]

7036 self.type = state["type"]

7037

7038 @property

7039 def columns(self) -> NoReturn:

7040 raise exc.InvalidRequestError(

7041 "Scalar Select expression has no "

7042 "columns; use this object directly "

7043 "within a column-level expression."

7044 )

7045

7046 c = columns

7047

7048 @_generative

7049 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7050 """Apply a WHERE clause to the SELECT statement referred to

7051 by this :class:`_expression.ScalarSelect`.

7052

7053 """

7054 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7055 crit

7056 )

7057 return self

7058

7059 def self_group(self, against: Optional[OperatorType] = None) -> Self:

7060 return self

7061

7062 def _ungroup(self) -> Self:

7063 return self

7064

7065 @_generative

7066 def correlate(

7067 self,

7068 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7069 ) -> Self:

7070 r"""Return a new :class:`_expression.ScalarSelect`

7071 which will correlate the given FROM

7072 clauses to that of an enclosing :class:`_expression.Select`.

7073

7074 This method is mirrored from the :meth:`_sql.Select.correlate` method

7075 of the underlying :class:`_sql.Select`. The method applies the

7076 :meth:_sql.Select.correlate` method, then returns a new

7077 :class:`_sql.ScalarSelect` against that statement.

7078

7079 .. versionadded:: 1.4 Previously, the

7080 :meth:`_sql.ScalarSelect.correlate`

7081 method was only available from :class:`_sql.Select`.

7082

7083 :param \*fromclauses: a list of one or more

7084 :class:`_expression.FromClause`

7085 constructs, or other compatible constructs (i.e. ORM-mapped

7086 classes) to become part of the correlate collection.

7087

7088 .. seealso::

7089

7090 :meth:`_expression.ScalarSelect.correlate_except`

7091

7092 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7093

7094

7095 """

7096 self.element = cast(

7097 "Select[Unpack[TupleAny]]", self.element

7098 ).correlate(*fromclauses)

7099 return self

7100

7101 @_generative

7102 def correlate_except(

7103 self,

7104 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7105 ) -> Self:

7106 r"""Return a new :class:`_expression.ScalarSelect`

7107 which will omit the given FROM

7108 clauses from the auto-correlation process.

7109

7110 This method is mirrored from the

7111 :meth:`_sql.Select.correlate_except` method of the underlying

7112 :class:`_sql.Select`. The method applies the

7113 :meth:_sql.Select.correlate_except` method, then returns a new

7114 :class:`_sql.ScalarSelect` against that statement.

7115

7116 .. versionadded:: 1.4 Previously, the

7117 :meth:`_sql.ScalarSelect.correlate_except`

7118 method was only available from :class:`_sql.Select`.

7119

7120 :param \*fromclauses: a list of one or more

7121 :class:`_expression.FromClause`

7122 constructs, or other compatible constructs (i.e. ORM-mapped

7123 classes) to become part of the correlate-exception collection.

7124

7125 .. seealso::

7126

7127 :meth:`_expression.ScalarSelect.correlate`

7128

7129 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7130

7131

7132 """

7133

7134 self.element = cast(

7135 "Select[Unpack[TupleAny]]", self.element

7136 ).correlate_except(*fromclauses)

7137 return self

7138

7139

7140class Exists(UnaryExpression[bool]):

7141 """Represent an ``EXISTS`` clause.

7142

7143 See :func:`_sql.exists` for a description of usage.

7144

7145 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7146 instance by calling :meth:`_sql.SelectBase.exists`.

7147

7148 """

7149

7150 inherit_cache = True

7151

7152 def __init__(

7153 self,

7154 __argument: Optional[

7155 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7156 ] = None,

7157 /,

7158 ):

7159 s: ScalarSelect[Any]

7160

7161 # TODO: this seems like we should be using coercions for this

7162 if __argument is None:

7163 s = Select(literal_column("*")).scalar_subquery()

7164 elif isinstance(__argument, SelectBase):

7165 s = __argument.scalar_subquery()

7166 s._propagate_attrs = __argument._propagate_attrs

7167 elif isinstance(__argument, ScalarSelect):

7168 s = __argument

7169 else:

7170 s = Select(__argument).scalar_subquery()

7171

7172 UnaryExpression.__init__(

7173 self,

7174 s,

7175 operator=operators.exists,

7176 type_=type_api.BOOLEANTYPE,

7177 )

7178

7179 @util.ro_non_memoized_property

7180 def _from_objects(self) -> List[FromClause]:

7181 return []

7182

7183 def _regroup(

7184 self,

7185 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7186 ) -> ScalarSelect[Any]:

7187

7188 assert isinstance(self.element, ScalarSelect)

7189 element = self.element.element

7190 if not isinstance(element, Select):

7191 raise exc.InvalidRequestError(

7192 "Can only apply this operation to a plain SELECT construct"

7193 )

7194 new_element = fn(element)

7195

7196 return_value = new_element.scalar_subquery()

7197 return return_value

7198

7199 def select(self) -> Select[bool]:

7200 r"""Return a SELECT of this :class:`_expression.Exists`.

7201

7202 e.g.::

7203

7204 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7205

7206 This will produce a statement resembling:

7207

7208 .. sourcecode:: sql

7209

7210 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7211

7212 .. seealso::

7213

7214 :func:`_expression.select` - general purpose

7215 method which allows for arbitrary column lists.

7216

7217 """ # noqa

7218

7219 return Select(self)

7220

7221 def correlate(

7222 self,

7223 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7224 ) -> Self:

7225 """Apply correlation to the subquery noted by this

7226 :class:`_sql.Exists`.

7227

7228 .. seealso::

7229

7230 :meth:`_sql.ScalarSelect.correlate`

7231

7232 """

7233 e = self._clone()

7234 e.element = self._regroup(

7235 lambda element: element.correlate(*fromclauses)

7236 )

7237 return e

7238

7239 def correlate_except(

7240 self,

7241 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7242 ) -> Self:

7243 """Apply correlation to the subquery noted by this

7244 :class:`_sql.Exists`.

7245

7246 .. seealso::

7247

7248 :meth:`_sql.ScalarSelect.correlate_except`

7249

7250 """

7251 e = self._clone()

7252 e.element = self._regroup(

7253 lambda element: element.correlate_except(*fromclauses)

7254 )

7255 return e

7256

7257 def select_from(self, *froms: _FromClauseArgument) -> Self:

7258 """Return a new :class:`_expression.Exists` construct,

7259 applying the given

7260 expression to the :meth:`_expression.Select.select_from`

7261 method of the select

7262 statement contained.

7263

7264 .. note:: it is typically preferable to build a :class:`_sql.Select`

7265 statement first, including the desired WHERE clause, then use the

7266 :meth:`_sql.SelectBase.exists` method to produce an

7267 :class:`_sql.Exists` object at once.

7268

7269 """

7270 e = self._clone()

7271 e.element = self._regroup(lambda element: element.select_from(*froms))

7272 return e

7273

7274 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7275 """Return a new :func:`_expression.exists` construct with the

7276 given expression added to

7277 its WHERE clause, joined to the existing clause via AND, if any.

7278

7279

7280 .. note:: it is typically preferable to build a :class:`_sql.Select`

7281 statement first, including the desired WHERE clause, then use the

7282 :meth:`_sql.SelectBase.exists` method to produce an

7283 :class:`_sql.Exists` object at once.

7284

7285 """

7286 e = self._clone()

7287 e.element = self._regroup(lambda element: element.where(*clause))

7288 return e

7289

7290

7291class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7292 """Wrap a :class:`_expression.TextClause` construct within a

7293 :class:`_expression.SelectBase`

7294 interface.

7295

7296 This allows the :class:`_expression.TextClause` object to gain a

7297 ``.c`` collection

7298 and other FROM-like capabilities such as

7299 :meth:`_expression.FromClause.alias`,

7300 :meth:`_expression.SelectBase.cte`, etc.

7301

7302 The :class:`_expression.TextualSelect` construct is produced via the

7303 :meth:`_expression.TextClause.columns`

7304 method - see that method for details.

7305

7306 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7307 class was renamed

7308 from ``TextAsFrom``, to more correctly suit its role as a

7309 SELECT-oriented object and not a FROM clause.

7310

7311 .. seealso::

7312

7313 :func:`_expression.text`

7314

7315 :meth:`_expression.TextClause.columns` - primary creation interface.

7316

7317 """

7318

7319 __visit_name__ = "textual_select"

7320

7321 _label_style = LABEL_STYLE_NONE

7322

7323 _traverse_internals: _TraverseInternalsType = (

7324 [

7325 ("element", InternalTraversal.dp_clauseelement),

7326 ("column_args", InternalTraversal.dp_clauseelement_list),

7327 ]

7328 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7329 + HasCTE._has_ctes_traverse_internals

7330 + ExecutableStatement._executable_traverse_internals

7331 )

7332

7333 _is_textual = True

7334

7335 is_text = True

7336 is_select = True

7337

7338 def __init__(

7339 self,

7340 text: TextClause,

7341 columns: List[_ColumnExpressionArgument[Any]],

7342 positional: bool = False,

7343 ) -> None:

7344 self._init(

7345 text,

7346 # convert for ORM attributes->columns, etc

7347 [

7348 coercions.expect(roles.LabeledColumnExprRole, c)

7349 for c in columns

7350 ],

7351 positional,

7352 )

7353

7354 def _init(

7355 self,

7356 text: AbstractTextClause,

7357 columns: List[NamedColumn[Any]],

7358 positional: bool = False,

7359 ) -> None:

7360 self.element = text

7361 self.column_args = columns

7362 self.positional = positional

7363

7364 @HasMemoized_ro_memoized_attribute

7365 def selected_columns(

7366 self,

7367 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7368 """A :class:`_expression.ColumnCollection`

7369 representing the columns that

7370 this SELECT statement or similar construct returns in its result set,

7371 not including :class:`_sql.TextClause` constructs.

7372

7373 This collection differs from the :attr:`_expression.FromClause.columns`

7374 collection of a :class:`_expression.FromClause` in that the columns

7375 within this collection cannot be directly nested inside another SELECT

7376 statement; a subquery must be applied first which provides for the

7377 necessary parenthesization required by SQL.

7378

7379 For a :class:`_expression.TextualSelect` construct, the collection

7380 contains the :class:`_expression.ColumnElement` objects that were

7381 passed to the constructor, typically via the

7382 :meth:`_expression.TextClause.columns` method.

7383

7384

7385 .. versionadded:: 1.4

7386

7387 """

7388 return ColumnCollection(

7389 (c.key, c) for c in self.column_args

7390 ).as_readonly()

7391

7392 @util.ro_non_memoized_property

7393 def _all_selected_columns(self) -> _SelectIterable:

7394 return self.column_args

7395

7396 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7397 return self

7398

7399 def _ensure_disambiguated_names(self) -> TextualSelect:

7400 return self

7401

7402 @_generative

7403 def bindparams(

7404 self,

7405 *binds: BindParameter[Any],

7406 **bind_as_values: Any,

7407 ) -> Self:

7408 self.element = self.element.bindparams(*binds, **bind_as_values)

7409 return self

7410

7411 def _generate_fromclause_column_proxies(

7412 self,

7413 fromclause: FromClause,

7414 columns: ColumnCollection[str, KeyedColumnElement[Any]],

7415 primary_key: ColumnSet,

7416 foreign_keys: Set[KeyedColumnElement[Any]],

7417 *,

7418 proxy_compound_columns: Optional[

7419 Iterable[Sequence[ColumnElement[Any]]]

7420 ] = None,

7421 ) -> None:

7422 if TYPE_CHECKING:

7423 assert isinstance(fromclause, Subquery)

7424

7425 if proxy_compound_columns:

7426 columns._populate_separate_keys(

7427 c._make_proxy(

7428 fromclause,

7429 compound_select_cols=extra_cols,

7430 primary_key=primary_key,

7431 foreign_keys=foreign_keys,

7432 )

7433 for c, extra_cols in zip(

7434 self.column_args, proxy_compound_columns

7435 )

7436 )

7437 else:

7438 columns._populate_separate_keys(

7439 c._make_proxy(

7440 fromclause,

7441 primary_key=primary_key,

7442 foreign_keys=foreign_keys,

7443 )

7444 for c in self.column_args

7445 )

7446

7447 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7448 return self.column_args[0].type

7449

7450

7451TextAsFrom = TextualSelect

7452"""Backwards compatibility with the previous name"""

7453

7454

7455class AnnotatedFromClause(Annotated):

7456 def _copy_internals(

7457 self,

7458 _annotations_traversal: bool = False,

7459 ind_cols_on_fromclause: bool = False,

7460 **kw: Any,

7461 ) -> None:

7462 super()._copy_internals(**kw)

7463

7464 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7465 # the traversals used by annotations for these cases are not currently

7466 # designed around expecting that inner elements inside of

7467 # AnnotatedFromClause's element are also deep copied, so skip for these

7468 # cases. in other cases such as plain visitors.cloned_traverse(), we

7469 # expect this to happen. see issue #12915

7470 if not _annotations_traversal:

7471 ee = self._Annotated__element # type: ignore

7472 ee._copy_internals(**kw)

7473

7474 if ind_cols_on_fromclause:

7475 # passed from annotations._deep_annotate(). See that function

7476 # for notes

7477 ee = self._Annotated__element # type: ignore

7478 self.c = ee.__class__.c.fget(self) # type: ignore

7479

7480 @util.ro_memoized_property

7481 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7482 """proxy the .c collection of the underlying FromClause.

7483

7484 Originally implemented in 2008 as a simple load of the .c collection

7485 when the annotated construct was created (see d3621ae961a), in modern

7486 SQLAlchemy versions this can be expensive for statements constructed

7487 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7488 it, which works just as well.

7489

7490 Two different use cases seem to require the collection either copied

7491 from the underlying one, or unique to this AnnotatedFromClause.

7492

7493 See test_selectable->test_annotated_corresponding_column

7494

7495 """

7496 ee = self._Annotated__element # type: ignore

7497 return ee.c # type: ignore