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

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

1200 FROM table1 JOIN table2 ON true

1201

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

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

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

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

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

1207

1208

1209 .. versionadded:: 1.4

1210

1211 """ # noqa: E501

1212

1213 LABEL_STYLE_DISAMBIGUATE_ONLY = 2

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

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

1216 when generating the columns clause of a SELECT statement.

1217

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

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

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

1221

1222 .. sourcecode:: pycon+sql

1223

1224 >>> from sqlalchemy import (

1225 ... table,

1226 ... column,

1227 ... select,

1228 ... true,

1229 ... LABEL_STYLE_DISAMBIGUATE_ONLY,

1230 ... )

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

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

1233 >>> print(

1234 ... select(table1, table2)

1235 ... .join(table2, true())

1236 ... .set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

1237 ... )

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

1239 FROM table1 JOIN table2 ON true

1240

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

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

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

1244

1245 .. versionadded:: 1.4

1246

1247 """ # noqa: E501

1248

1249 LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1250 """The default label style, refers to

1251 :data:`_sql.LABEL_STYLE_DISAMBIGUATE_ONLY`.

1252

1253 .. versionadded:: 1.4

1254

1255 """

1256

1257 LABEL_STYLE_LEGACY_ORM = 3

1258

1259

1260(

1261 LABEL_STYLE_NONE,

1262 LABEL_STYLE_TABLENAME_PLUS_COL,

1263 LABEL_STYLE_DISAMBIGUATE_ONLY,

1264 _,

1265) = list(SelectLabelStyle)

1266

1267LABEL_STYLE_DEFAULT = LABEL_STYLE_DISAMBIGUATE_ONLY

1268

1269

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

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

1272 :class:`_expression.FromClause`

1273 elements.

1274

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

1276 is the module-level

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

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

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

1280 :class:`_schema.Table`).

1281

1282 .. seealso::

1283

1284 :func:`_expression.join`

1285

1286 :meth:`_expression.FromClause.join`

1287

1288 """

1289

1290 __visit_name__ = "join"

1291

1292 _traverse_internals: _TraverseInternalsType = [

1293 ("left", InternalTraversal.dp_clauseelement),

1294 ("right", InternalTraversal.dp_clauseelement),

1295 ("onclause", InternalTraversal.dp_clauseelement),

1296 ("isouter", InternalTraversal.dp_boolean),

1297 ("full", InternalTraversal.dp_boolean),

1298 ]

1299

1300 _is_join = True

1301

1302 left: FromClause

1303 right: FromClause

1304 onclause: Optional[ColumnElement[bool]]

1305 isouter: bool

1306 full: bool

1307

1308 def __init__(

1309 self,

1310 left: _FromClauseArgument,

1311 right: _FromClauseArgument,

1312 onclause: Optional[_OnClauseArgument] = None,

1313 isouter: bool = False,

1314 full: bool = False,

1315 ):

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

1317

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

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

1320 :class:`_expression.FromClause` object.

1321

1322 """

1323

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

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

1326 # annotated objects. test_orm.py -> test_fetch_results

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

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

1329 # callcounts for a single compilation in that particular test

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

1331 # 29200 -> 30373

1332

1333 self.left = coercions.expect(

1334 roles.FromClauseRole,

1335 left,

1336 )

1337 self.right = coercions.expect(

1338 roles.FromClauseRole,

1339 right,

1340 ).self_group()

1341

1342 if onclause is None:

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

1344 else:

1345 # note: taken from If91f61527236fd4d7ae3cad1f24c38be921c90ba

1346 # not merged yet

1347 self.onclause = coercions.expect(

1348 roles.OnClauseRole, onclause

1349 ).self_group(against=operators._asbool)

1350

1351 self.isouter = isouter

1352 self.full = full

1353

1354 @util.ro_non_memoized_property

1355 def description(self) -> str:

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

1357 self.left.description,

1358 id(self.left),

1359 self.right.description,

1360 id(self.right),

1361 )

1362

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

1364 return (

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

1366 # as well

1367 hash(fromclause) == hash(self)

1368 or self.left.is_derived_from(fromclause)

1369 or self.right.is_derived_from(fromclause)

1370 )

1371

1372 def self_group(

1373 self, against: Optional[OperatorType] = None

1374 ) -> FromGrouping:

1375 return FromGrouping(self)

1376

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

1378 def _populate_column_collection(

1379 self,

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

1381 primary_key: ColumnSet,

1382 foreign_keys: Set[KeyedColumnElement[Any]],

1383 ) -> None:

1384 sqlutil = util.preloaded.sql_util

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

1386 c for c in self.right.c

1387 ]

1388

1389 primary_key.extend(

1390 sqlutil.reduce_columns(

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

1392 )

1393 )

1394 columns._populate_separate_keys(

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

1396 )

1397 foreign_keys.update(

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

1399 )

1400

1401 def _copy_internals(

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

1403 ) -> None:

1404 # see Select._copy_internals() for similar concept

1405

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

1407 # determine the new FROM clauses

1408 all_the_froms = set(

1409 itertools.chain(

1410 _from_objects(self.left),

1411 _from_objects(self.right),

1412 )

1413 )

1414

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

1416 # cache used by the clone function

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

1418

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

1420 # ColumnClause with parent table referring to those

1421 # replaced FromClause objects

1422 def replace(

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

1424 **kw: Any,

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

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

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

1428 return newelem

1429 return None

1430

1431 kw["replace"] = replace

1432

1433 # run normal _copy_internals. the clones for

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

1435 # cache

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

1437

1438 self._reset_memoizations()

1439

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

1441 super()._refresh_for_new_column(column)

1442 self.left._refresh_for_new_column(column)

1443 self.right._refresh_for_new_column(column)

1444

1445 def _match_primaries(

1446 self,

1447 left: FromClause,

1448 right: FromClause,

1449 ) -> ColumnElement[bool]:

1450 if isinstance(left, Join):

1451 left_right = left.right

1452 else:

1453 left_right = None

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

1455

1456 @classmethod

1457 def _join_condition(

1458 cls,

1459 a: FromClause,

1460 b: FromClause,

1461 *,

1462 a_subset: Optional[FromClause] = None,

1463 consider_as_foreign_keys: Optional[

1464 AbstractSet[ColumnClause[Any]]

1465 ] = None,

1466 ) -> ColumnElement[bool]:

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

1468

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

1470

1471 """

1472 constraints = cls._joincond_scan_left_right(

1473 a, a_subset, b, consider_as_foreign_keys

1474 )

1475

1476 if len(constraints) > 1:

1477 cls._joincond_trim_constraints(

1478 a, b, constraints, consider_as_foreign_keys

1479 )

1480

1481 if len(constraints) == 0:

1482 if isinstance(b, FromGrouping):

1483 hint = (

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

1485 "subquery using alias()?"

1486 )

1487 else:

1488 hint = ""

1489 raise exc.NoForeignKeysError(

1490 "Can't find any foreign key relationships "

1491 "between '%s' and '%s'.%s"

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

1493 )

1494

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

1496 if len(crit) == 1:

1497 return crit[0]

1498 else:

1499 return and_(*crit)

1500

1501 @classmethod

1502 def _can_join(

1503 cls,

1504 left: FromClause,

1505 right: FromClause,

1506 *,

1507 consider_as_foreign_keys: Optional[

1508 AbstractSet[ColumnClause[Any]]

1509 ] = None,

1510 ) -> bool:

1511 if isinstance(left, Join):

1512 left_right = left.right

1513 else:

1514 left_right = None

1515

1516 constraints = cls._joincond_scan_left_right(

1517 a=left,

1518 b=right,

1519 a_subset=left_right,

1520 consider_as_foreign_keys=consider_as_foreign_keys,

1521 )

1522

1523 return bool(constraints)

1524

1525 @classmethod

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

1527 def _joincond_scan_left_right(

1528 cls,

1529 a: FromClause,

1530 a_subset: Optional[FromClause],

1531 b: FromClause,

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

1533 ) -> collections.defaultdict[

1534 Optional[ForeignKeyConstraint],

1535 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1536 ]:

1537 sql_util = util.preloaded.sql_util

1538

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

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

1541

1542 constraints: collections.defaultdict[

1543 Optional[ForeignKeyConstraint],

1544 List[Tuple[ColumnClause[Any], ColumnClause[Any]]],

1545 ] = collections.defaultdict(list)

1546

1547 for left in (a_subset, a):

1548 if left is None:

1549 continue

1550 for fk in sorted(

1551 b.foreign_keys,

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

1553 ):

1554 if (

1555 consider_as_foreign_keys is not None

1556 and fk.parent not in consider_as_foreign_keys

1557 ):

1558 continue

1559 try:

1560 col = fk.get_referent(left)

1561 except exc.NoReferenceError as nrte:

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

1563 if nrte.table_name in table_names:

1564 raise

1565 else:

1566 continue

1567

1568 if col is not None:

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

1570 if left is not b:

1571 for fk in sorted(

1572 left.foreign_keys,

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

1574 ):

1575 if (

1576 consider_as_foreign_keys is not None

1577 and fk.parent not in consider_as_foreign_keys

1578 ):

1579 continue

1580 try:

1581 col = fk.get_referent(b)

1582 except exc.NoReferenceError as nrte:

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

1584 if nrte.table_name in table_names:

1585 raise

1586 else:

1587 continue

1588

1589 if col is not None:

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

1591 if constraints:

1592 break

1593 return constraints

1594

1595 @classmethod

1596 def _joincond_trim_constraints(

1597 cls,

1598 a: FromClause,

1599 b: FromClause,

1600 constraints: Dict[Any, Any],

1601 consider_as_foreign_keys: Optional[Any],

1602 ) -> None:

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

1604 # to include just those FKCs that match exactly to

1605 # "consider_as_foreign_keys".

1606 if consider_as_foreign_keys:

1607 for const in list(constraints):

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

1609 consider_as_foreign_keys

1610 ):

1611 del constraints[const]

1612

1613 # if still multiple constraints, but

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

1615 if len(constraints) > 1:

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

1617 if len(dedupe) == 1:

1618 key = list(constraints)[0]

1619 constraints = {key: constraints[key]}

1620

1621 if len(constraints) != 1:

1622 raise exc.AmbiguousForeignKeysError(

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

1624 "tables have more than one foreign key "

1625 "constraint relationship between them. "

1626 "Please specify the 'onclause' of this "

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

1628 )

1629

1630 @overload

1631 def select(

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

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

1634 @overload

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

1636

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

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

1639 :class:`_expression.Join`.

1640

1641 E.g.::

1642

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

1644

1645 stmt = stmt.select()

1646

1647 The above will produce a SQL string resembling:

1648

1649 .. sourcecode:: sql

1650

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

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

1653

1654 """

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

1656

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

1658 def _anonymous_fromclause(

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

1660 ) -> TODO_Any:

1661 sqlutil = util.preloaded.sql_util

1662 if flat:

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

1664 left_name = name # will recurse

1665 else:

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

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

1668 else:

1669 left_name = name

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

1671 right_name = name # will recurse

1672 else:

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

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

1675 else:

1676 right_name = name

1677 left_a, right_a = (

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

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

1680 )

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

1682 sqlutil.ClauseAdapter(right_a)

1683 )

1684

1685 return left_a.join(

1686 right_a,

1687 adapter.traverse(self.onclause),

1688 isouter=self.isouter,

1689 full=self.full,

1690 )

1691 else:

1692 return (

1693 self.select()

1694 .set_label_style(LABEL_STYLE_TABLENAME_PLUS_COL)

1695 .correlate(None)

1696 .alias(name)

1697 )

1698

1699 @util.ro_non_memoized_property

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

1701 return itertools.chain(

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

1703 )

1704

1705 @util.ro_non_memoized_property

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

1707 self_list: List[FromClause] = [self]

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

1709

1710 if TYPE_CHECKING:

1711

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

1713

1714

1715class NoInit:

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

1717 raise NotImplementedError(

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

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

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

1721 "selectable objects."

1722 % (

1723 self.__class__.__name__,

1724 self.__class__.__name__.lower(),

1725 self.__class__.__name__.lower(),

1726 )

1727 )

1728

1729

1730class LateralFromClause(NamedFromClause):

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

1732

1733

1734# FromClause ->

1735# AliasedReturnsRows

1736# -> Alias only for FromClause

1737# -> Subquery only for SelectBase

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

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

1740# w/ non-deprecated coercion

1741# -> TableSample -> only for FromClause

1742

1743

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

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

1746 selectables."""

1747

1748 _is_from_container = True

1749

1750 _supports_derived_columns = False

1751

1752 element: ReturnsRows

1753

1754 _traverse_internals: _TraverseInternalsType = [

1755 ("element", InternalTraversal.dp_clauseelement),

1756 ("name", InternalTraversal.dp_anon_name),

1757 ]

1758

1759 @classmethod

1760 def _construct(

1761 cls,

1762 selectable: Any,

1763 *,

1764 name: Optional[str] = None,

1765 **kw: Any,

1766 ) -> Self:

1767 obj = cls.__new__(cls)

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

1769 return obj

1770

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

1772 self.element = coercions.expect(

1773 roles.ReturnsRowsRole, selectable, apply_propagate_attrs=self

1774 )

1775 self.element = selectable

1776 self._orig_name = name

1777 if name is None:

1778 if (

1779 isinstance(selectable, FromClause)

1780 and selectable.named_with_column

1781 ):

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

1783 if isinstance(name, _anonymous_label):

1784 name = None

1785 name = _anonymous_label.safe_construct(

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

1787 )

1788 self.name = name

1789

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

1791 super()._refresh_for_new_column(column)

1792 self.element._refresh_for_new_column(column)

1793

1794 def _populate_column_collection(

1795 self,

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

1797 primary_key: ColumnSet,

1798 foreign_keys: Set[KeyedColumnElement[Any]],

1799 ) -> None:

1800 self.element._generate_fromclause_column_proxies(

1801 self, columns, primary_key=primary_key, foreign_keys=foreign_keys

1802 )

1803

1804 @util.ro_non_memoized_property

1805 def description(self) -> str:

1806 name = self.name

1807 if isinstance(name, _anonymous_label):

1808 return "anon_1"

1809

1810 return name

1811

1812 @util.ro_non_memoized_property

1813 def implicit_returning(self) -> bool:

1814 return self.element.implicit_returning # type: ignore

1815

1816 @property

1817 def original(self) -> ReturnsRows:

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

1819 return self.element

1820

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

1822 if fromclause in self._cloned_set:

1823 return True

1824 return self.element.is_derived_from(fromclause)

1825

1826 def _copy_internals(

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

1828 ) -> None:

1829 existing_element = self.element

1830

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

1832

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

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

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

1836 # performance.

1837 if existing_element is not self.element:

1838 self._reset_column_collection()

1839

1840 @property

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

1842 return [self]

1843

1844

1845class FromClauseAlias(AliasedReturnsRows[_KeyColCC_co]):

1846 element: FromClause[_KeyColCC_co]

1847

1848 @util.ro_non_memoized_property

1849 def description(self) -> str:

1850 name = self.name

1851 if isinstance(name, _anonymous_label):

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

1853

1854 return name

1855

1856

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

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

1859

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

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

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

1863

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

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

1866 method available

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

1868

1869 .. seealso::

1870

1871 :meth:`_expression.FromClause.alias`

1872

1873 """

1874

1875 __visit_name__ = "alias"

1876

1877 inherit_cache = True

1878

1879 element: FromClause[_KeyColCC_co]

1880

1881 @classmethod

1882 def _factory(

1883 cls,

1884 selectable: FromClause[_KeyColCC_co],

1885 name: Optional[str] = None,

1886 flat: bool = False,

1887 ) -> NamedFromClause[_KeyColCC_co]:

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

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

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

1891 name=name, flat=flat

1892 )

1893

1894

1895class TableValuedAlias(LateralFromClause, Alias):

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

1897

1898 This construct provides for a SQL function that returns columns

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

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

1901 method, e.g.:

1902

1903 .. sourcecode:: pycon+sql

1904

1905 >>> from sqlalchemy import select, func

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

1907 ... "value"

1908 ... )

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

1910 {printsql}SELECT anon_1.value

1911 FROM json_array_elements_text(:json_array_elements_text_1) AS anon_1

1912

1913 .. versionadded:: 1.4.0b2

1914

1915 .. seealso::

1916

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

1918

1919 """ # noqa: E501

1920

1921 __visit_name__ = "table_valued_alias"

1922

1923 _supports_derived_columns = True

1924 _render_derived = False

1925 _render_derived_w_types = False

1926 joins_implicitly = False

1927

1928 _traverse_internals: _TraverseInternalsType = [

1929 ("element", InternalTraversal.dp_clauseelement),

1930 ("name", InternalTraversal.dp_anon_name),

1931 ("_tableval_type", InternalTraversal.dp_type),

1932 ("_render_derived", InternalTraversal.dp_boolean),

1933 ("_render_derived_w_types", InternalTraversal.dp_boolean),

1934 ]

1935

1936 def _init(

1937 self,

1938 selectable: Any,

1939 *,

1940 name: Optional[str] = None,

1941 table_value_type: Optional[TableValueType] = None,

1942 joins_implicitly: bool = False,

1943 ) -> None:

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

1945

1946 self.joins_implicitly = joins_implicitly

1947 self._tableval_type = (

1948 type_api.TABLEVALUE

1949 if table_value_type is None

1950 else table_value_type

1951 )

1952

1953 @HasMemoized.memoized_attribute

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

1955 """Return a column expression representing this

1956 :class:`_sql.TableValuedAlias`.

1957

1958 This accessor is used to implement the

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

1960 method for further details.

1961

1962 E.g.:

1963

1964 .. sourcecode:: pycon+sql

1965

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

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

1968

1969 .. seealso::

1970

1971 :meth:`_functions.FunctionElement.column_valued`

1972

1973 """

1974

1975 return TableValuedColumn(self, self._tableval_type)

1976

1977 def alias(

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

1979 ) -> TableValuedAlias:

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

1981

1982 This creates a distinct FROM object that will be distinguished

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

1984

1985 """

1986

1987 tva: TableValuedAlias = TableValuedAlias._construct(

1988 self,

1989 name=name,

1990 table_value_type=self._tableval_type,

1991 joins_implicitly=self.joins_implicitly,

1992 )

1993

1994 if self._render_derived:

1995 tva._render_derived = True

1996 tva._render_derived_w_types = self._render_derived_w_types

1997

1998 return tva

1999

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

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

2002 set, so that it renders as LATERAL.

2003

2004 .. seealso::

2005

2006 :func:`_expression.lateral`

2007

2008 """

2009 tva = self.alias(name=name)

2010 tva._is_lateral = True

2011 return tva

2012

2013 def render_derived(

2014 self,

2015 name: Optional[str] = None,

2016 with_types: bool = False,

2017 ) -> TableValuedAlias:

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

2019

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

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

2022

2023 .. sourcecode:: pycon+sql

2024

2025 >>> print(

2026 ... select(

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

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

2029 ... .render_derived()

2030 ... )

2031 ... )

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

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

2034

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

2036 the alias expression (this syntax currently applies to the

2037 PostgreSQL database):

2038

2039 .. sourcecode:: pycon+sql

2040

2041 >>> print(

2042 ... select(

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

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

2045 ... .render_derived(with_types=True)

2046 ... )

2047 ... )

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

2049 AS anon_1(a INTEGER, b VARCHAR)

2050

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

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

2053

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

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

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

2057

2058 """ # noqa: E501

2059

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

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

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

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

2064 # (just saw it happen on CI)

2065

2066 # construct against original to prevent memory growth

2067 # for repeated generations

2068 new_alias: TableValuedAlias = TableValuedAlias._construct(

2069 self.element,

2070 name=name,

2071 table_value_type=self._tableval_type,

2072 joins_implicitly=self.joins_implicitly,

2073 )

2074 new_alias._render_derived = True

2075 new_alias._render_derived_w_types = with_types

2076 return new_alias

2077

2078

2079class Lateral(FromClauseAlias, LateralFromClause):

2080 """Represent a LATERAL subquery.

2081

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

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

2084 method available

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

2086

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

2088 PostgreSQL versions provide support for this keyword.

2089

2090 .. seealso::

2091

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

2093

2094 """

2095

2096 __visit_name__ = "lateral"

2097 _is_lateral = True

2098

2099 inherit_cache = True

2100

2101 @classmethod

2102 def _factory(

2103 cls,

2104 selectable: Union[SelectBase, _FromClauseArgument],

2105 name: Optional[str] = None,

2106 ) -> LateralFromClause:

2107 return coercions.expect(

2108 roles.FromClauseRole, selectable, explicit_subquery=True

2109 ).lateral(name=name)

2110

2111

2112class TableSample(FromClauseAlias):

2113 """Represent a TABLESAMPLE clause.

2114

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

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

2117 method

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

2119

2120 .. seealso::

2121

2122 :func:`_expression.tablesample`

2123

2124 """

2125

2126 __visit_name__ = "tablesample"

2127

2128 _traverse_internals: _TraverseInternalsType = (

2129 AliasedReturnsRows._traverse_internals

2130 + [

2131 ("sampling", InternalTraversal.dp_clauseelement),

2132 ("seed", InternalTraversal.dp_clauseelement),

2133 ]

2134 )

2135

2136 @classmethod

2137 def _factory(

2138 cls,

2139 selectable: _FromClauseArgument,

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

2141 name: Optional[str] = None,

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

2143 ) -> TableSample:

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

2145 sampling, name=name, seed=seed

2146 )

2147

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

2149 def _init( # type: ignore[override]

2150 self,

2151 selectable: Any,

2152 *,

2153 name: Optional[str] = None,

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

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

2156 ) -> None:

2157 assert sampling is not None

2158 functions = util.preloaded.sql_functions

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

2160 sampling = functions.func.system(sampling)

2161

2162 self.sampling: Function[Any] = sampling

2163 self.seed = seed

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

2165

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

2167 return self.sampling

2168

2169

2170class CTE(

2171 roles.DMLTableRole,

2172 roles.IsCTERole,

2173 Generative,

2174 HasPrefixes,

2175 HasSuffixes,

2176 AliasedReturnsRows[_KeyColCC_co],

2177):

2178 """Represent a Common Table Expression.

2179

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

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

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

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

2184 :class:`_sql.Update` and

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

2186 usage details on CTEs.

2187

2188 .. seealso::

2189

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

2191

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

2193

2194 """

2195

2196 __visit_name__ = "cte"

2197

2198 _traverse_internals: _TraverseInternalsType = (

2199 AliasedReturnsRows._traverse_internals

2200 + [

2201 ("_cte_alias", InternalTraversal.dp_clauseelement),

2202 ("_restates", InternalTraversal.dp_clauseelement),

2203 ("recursive", InternalTraversal.dp_boolean),

2204 ("nesting", InternalTraversal.dp_boolean),

2205 ]

2206 + HasPrefixes._has_prefixes_traverse_internals

2207 + HasSuffixes._has_suffixes_traverse_internals

2208 )

2209

2210 element: HasCTE

2211

2212 @classmethod

2213 def _factory(

2214 cls,

2215 selectable: HasCTE,

2216 name: Optional[str] = None,

2217 recursive: bool = False,

2218 ) -> CTE:

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

2220 or Common Table Expression instance.

2221

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

2223

2224 """

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

2226 name=name, recursive=recursive

2227 )

2228

2229 def _init(

2230 self,

2231 selectable: HasCTE,

2232 *,

2233 name: Optional[str] = None,

2234 recursive: bool = False,

2235 nesting: bool = False,

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

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

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

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

2240 ) -> None:

2241 self.recursive = recursive

2242 self.nesting = nesting

2243 self._cte_alias = _cte_alias

2244 # Keep recursivity reference with union/union_all

2245 self._restates = _restates

2246 if _prefixes:

2247 self._prefixes = _prefixes

2248 if _suffixes:

2249 self._suffixes = _suffixes

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

2251

2252 def _populate_column_collection(

2253 self,

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

2255 primary_key: ColumnSet,

2256 foreign_keys: Set[KeyedColumnElement[Any]],

2257 ) -> None:

2258 if self._cte_alias is not None:

2259 self._cte_alias._generate_fromclause_column_proxies(

2260 self,

2261 columns,

2262 primary_key=primary_key,

2263 foreign_keys=foreign_keys,

2264 )

2265 else:

2266 self.element._generate_fromclause_column_proxies(

2267 self,

2268 columns,

2269 primary_key=primary_key,

2270 foreign_keys=foreign_keys,

2271 )

2272

2273 def alias(

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

2275 ) -> CTE[_KeyColCC_co]:

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

2277 :class:`_expression.CTE`.

2278

2279 This method is a CTE-specific specialization of the

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

2281

2282 .. seealso::

2283

2284 :ref:`tutorial_using_aliases`

2285

2286 :func:`_expression.alias`

2287

2288 """

2289 return CTE._construct(

2290 self.element,

2291 name=name,

2292 recursive=self.recursive,

2293 nesting=self.nesting,

2294 _cte_alias=self,

2295 _prefixes=self._prefixes,

2296 _suffixes=self._suffixes,

2297 )

2298

2299 def union(

2300 self, *other: _SelectStatementForCompoundArgument[Any]

2301 ) -> CTE[_KeyColCC_co]:

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

2303 of the original CTE against the given selectables provided

2304 as positional arguments.

2305

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

2307 UNION.

2308

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

2310

2311 .. seealso::

2312

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

2314

2315 """

2316 assert is_select_statement(

2317 self.element

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

2319

2320 return CTE._construct(

2321 self.element.union(*other),

2322 name=self.name,

2323 recursive=self.recursive,

2324 nesting=self.nesting,

2325 _restates=self,

2326 _prefixes=self._prefixes,

2327 _suffixes=self._suffixes,

2328 )

2329

2330 def union_all(

2331 self, *other: _SelectStatementForCompoundArgument[Any]

2332 ) -> CTE[_KeyColCC_co]:

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

2334 of the original CTE against the given selectables provided

2335 as positional arguments.

2336

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

2338 UNION.

2339

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

2341

2342 .. seealso::

2343

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

2345

2346 """

2347

2348 assert is_select_statement(

2349 self.element

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

2351

2352 return CTE._construct(

2353 self.element.union_all(*other),

2354 name=self.name,

2355 recursive=self.recursive,

2356 nesting=self.nesting,

2357 _restates=self,

2358 _prefixes=self._prefixes,

2359 _suffixes=self._suffixes,

2360 )

2361

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

2363 """

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

2365 Updated CTEs should still refer to the original CTE.

2366 This function returns this reference identifier.

2367 """

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

2369

2370 if TYPE_CHECKING:

2371

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

2373

2374

2375class _CTEOpts(NamedTuple):

2376 nesting: bool

2377

2378

2379class _ColumnsPlusNames(NamedTuple):

2380 required_label_name: Optional[str]

2381 """

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

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

2384 """

2385

2386 proxy_key: Optional[str]

2387 """

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

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

2390 select.selected_columns collection

2391 """

2392

2393 fallback_label_name: Optional[str]

2394 """

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

2396 we have to render a label even though

2397 required_label_name was not given

2398 """

2399

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

2401 """

2402 the ColumnElement itself

2403 """

2404

2405 repeated: bool

2406 """

2407 True if this is a duplicate of a previous column

2408 in the list of columns

2409 """

2410

2411

2412class SelectsRows(ReturnsRows):

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

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

2415

2416 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

2417

2418 def _generate_columns_plus_names(

2419 self,

2420 anon_for_dupe_key: bool,

2421 cols: Optional[_SelectIterable] = None,

2422 ) -> List[_ColumnsPlusNames]:

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

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

2425 on a :class:`.FromClause`.

2426

2427 This is distinct from the _column_naming_convention generator that's

2428 intended for population of the Select.selected_columns collection,

2429 different rules. the collection returned here calls upon the

2430 _column_naming_convention as well.

2431

2432 """

2433

2434 if cols is None:

2435 cols = self._all_selected_columns

2436

2437 key_naming_convention = SelectState._column_naming_convention(

2438 self._label_style

2439 )

2440

2441 names = {}

2442

2443 result: List[_ColumnsPlusNames] = []

2444 result_append = result.append

2445

2446 table_qualified = self._label_style is LABEL_STYLE_TABLENAME_PLUS_COL

2447 label_style_none = self._label_style is LABEL_STYLE_NONE

2448

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

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

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

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

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

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

2455 dedupe_hash = 1

2456

2457 for c in cols:

2458 repeated = False

2459

2460 if not c._render_label_in_columns_clause:

2461 effective_name = required_label_name = fallback_label_name = (

2462 None

2463 )

2464 elif label_style_none:

2465 if TYPE_CHECKING:

2466 assert is_column_element(c)

2467

2468 effective_name = required_label_name = None

2469 fallback_label_name = c._non_anon_label or c._anon_name_label

2470 else:

2471 if TYPE_CHECKING:

2472 assert is_column_element(c)

2473

2474 if table_qualified:

2475 required_label_name = effective_name = (

2476 fallback_label_name

2477 ) = c._tq_label

2478 else:

2479 effective_name = fallback_label_name = c._non_anon_label

2480 required_label_name = None

2481

2482 if effective_name is None:

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

2484 # not need _expression_label but it isn't

2485 # giving us a clue when to use anon_label instead

2486 expr_label = c._expression_label

2487 if expr_label is None:

2488 repeated = c._anon_name_label in names

2489 names[c._anon_name_label] = c

2490 effective_name = required_label_name = None

2491

2492 if repeated:

2493 # here, "required_label_name" is sent as

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

2495 if table_qualified:

2496 fallback_label_name = (

2497 c._dedupe_anon_tq_label_idx(dedupe_hash)

2498 )

2499 dedupe_hash += 1

2500 else:

2501 fallback_label_name = c._dedupe_anon_label_idx(

2502 dedupe_hash

2503 )

2504 dedupe_hash += 1

2505 else:

2506 fallback_label_name = c._anon_name_label

2507 else:

2508 required_label_name = effective_name = (

2509 fallback_label_name

2510 ) = expr_label

2511

2512 if effective_name is not None:

2513 if TYPE_CHECKING:

2514 assert is_column_element(c)

2515

2516 if effective_name in names:

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

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

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

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

2521 # different column under the same name. apply

2522 # disambiguating label

2523 if table_qualified:

2524 required_label_name = fallback_label_name = (

2525 c._anon_tq_label

2526 )

2527 else:

2528 required_label_name = fallback_label_name = (

2529 c._anon_name_label

2530 )

2531

2532 if anon_for_dupe_key and required_label_name in names:

2533 # here, c._anon_tq_label is definitely unique to

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

2535 # this should always be true.

2536 # this is also an infrequent codepath because

2537 # you need two levels of duplication to be here

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

2539

2540 # the column under the disambiguating label is

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

2542 # subsequent occurrences of the column so that the

2543 # original stays non-ambiguous

2544 if table_qualified:

2545 required_label_name = fallback_label_name = (

2546 c._dedupe_anon_tq_label_idx(dedupe_hash)

2547 )

2548 dedupe_hash += 1

2549 else:

2550 required_label_name = fallback_label_name = (

2551 c._dedupe_anon_label_idx(dedupe_hash)

2552 )

2553 dedupe_hash += 1

2554 repeated = True

2555 else:

2556 names[required_label_name] = c

2557 elif anon_for_dupe_key:

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

2559 # label so that the original stays non-ambiguous

2560 if table_qualified:

2561 required_label_name = fallback_label_name = (

2562 c._dedupe_anon_tq_label_idx(dedupe_hash)

2563 )

2564 dedupe_hash += 1

2565 else:

2566 required_label_name = fallback_label_name = (

2567 c._dedupe_anon_label_idx(dedupe_hash)

2568 )

2569 dedupe_hash += 1

2570 repeated = True

2571 else:

2572 names[effective_name] = c

2573

2574 result_append(

2575 _ColumnsPlusNames(

2576 required_label_name,

2577 key_naming_convention(c),

2578 fallback_label_name,

2579 c,

2580 repeated,

2581 )

2582 )

2583

2584 return result

2585

2586

2587class HasCTE(roles.HasCTERole, SelectsRows):

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

2589

2590 _has_ctes_traverse_internals: _TraverseInternalsType = [

2591 ("_independent_ctes", InternalTraversal.dp_clauseelement_list),

2592 ("_independent_ctes_opts", InternalTraversal.dp_plain_obj),

2593 ]

2594

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

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

2597

2598 name_cte_columns: bool = False

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

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

2601

2602 .. versionadded:: 2.0.42

2603

2604 """

2605

2606 @_generative

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

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

2609

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

2611 the parent statement such that they will each be unconditionally

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

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

2614

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

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

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

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

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

2620 statement.

2621

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

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

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

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

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

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

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

2629 larger statement.

2630

2631 E.g.::

2632

2633 from sqlalchemy import table, column, select

2634

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

2636

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

2638

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

2640

2641 Would render:

2642

2643 .. sourcecode:: sql

2644

2645 WITH anon_1 AS (

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

2647 )

2648 SELECT t.c1, t.c2

2649 FROM t

2650

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

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

2653 statement.

2654

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

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

2657

2658 from sqlalchemy import table, column

2659 from sqlalchemy.dialects.postgresql import insert

2660

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

2662

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

2664

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

2666 update_statement = insert_stmt.on_conflict_do_update(

2667 index_elements=[t.c.c1],

2668 set_={

2669 "c1": insert_stmt.excluded.c1,

2670 "c2": insert_stmt.excluded.c2,

2671 },

2672 ).add_cte(delete_statement_cte)

2673

2674 print(update_statement)

2675

2676 The above statement renders as:

2677

2678 .. sourcecode:: sql

2679

2680 WITH deletions AS (

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

2682 )

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

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

2685

2686 .. versionadded:: 1.4.21

2687

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

2689

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

2691

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

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

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

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

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

2697 this statement when this flag is given.

2698

2699 .. versionadded:: 2.0

2700

2701 .. seealso::

2702

2703 :paramref:`.HasCTE.cte.nesting`

2704

2705

2706 """ # noqa: E501

2707 opt = _CTEOpts(nest_here)

2708 for cte in ctes:

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

2710 self._independent_ctes += (cte,)

2711 self._independent_ctes_opts += (opt,)

2712 return self

2713

2714 def cte(

2715 self,

2716 name: Optional[str] = None,

2717 recursive: bool = False,

2718 nesting: bool = False,

2719 ) -> CTE:

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

2721 or Common Table Expression instance.

2722

2723 Common table expressions are a SQL standard whereby SELECT

2724 statements can draw upon secondary statements specified along

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

2726 Special semantics regarding UNION can also be employed to

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

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

2729

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

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

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

2733 CTE rows.

2734

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

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

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

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

2739

2740 For special prefixes such as PostgreSQL "MATERIALIZED" and

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

2742 method may be

2743 used to establish these.

2744

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

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

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

2748 compile time.

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

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

2751 conjunction with UNION ALL in order to derive rows

2752 from those already selected.

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

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

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

2756 :paramref:`.HasCTE.add_cte.nest_here`

2757 parameter may also be used to more carefully

2758 control the exact placement of a particular CTE.

2759

2760 .. versionadded:: 1.4.24

2761

2762 .. seealso::

2763

2764 :meth:`.HasCTE.add_cte`

2765

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

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

2768 as well as additional examples.

2769

2770 Example 1, non recursive::

2771

2772 from sqlalchemy import (

2773 Table,

2774 Column,

2775 String,

2776 Integer,

2777 MetaData,

2778 select,

2779 func,

2780 )

2781

2782 metadata = MetaData()

2783

2784 orders = Table(

2785 "orders",

2786 metadata,

2787 Column("region", String),

2788 Column("amount", Integer),

2789 Column("product", String),

2790 Column("quantity", Integer),

2791 )

2792

2793 regional_sales = (

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

2795 .group_by(orders.c.region)

2796 .cte("regional_sales")

2797 )

2798

2799

2800 top_regions = (

2801 select(regional_sales.c.region)

2802 .where(

2803 regional_sales.c.total_sales

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

2805 )

2806 .cte("top_regions")

2807 )

2808

2809 statement = (

2810 select(

2811 orders.c.region,

2812 orders.c.product,

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

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

2815 )

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

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

2818 )

2819

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

2821

2822 Example 2, WITH RECURSIVE::

2823

2824 from sqlalchemy import (

2825 Table,

2826 Column,

2827 String,

2828 Integer,

2829 MetaData,

2830 select,

2831 func,

2832 )

2833

2834 metadata = MetaData()

2835

2836 parts = Table(

2837 "parts",

2838 metadata,

2839 Column("part", String),

2840 Column("sub_part", String),

2841 Column("quantity", Integer),

2842 )

2843

2844 included_parts = (

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

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

2847 .cte(recursive=True)

2848 )

2849

2850

2851 incl_alias = included_parts.alias()

2852 parts_alias = parts.alias()

2853 included_parts = included_parts.union_all(

2854 select(

2855 parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity

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

2857 )

2858

2859 statement = select(

2860 included_parts.c.sub_part,

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

2862 ).group_by(included_parts.c.sub_part)

2863

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

2865

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

2867

2868 from datetime import date

2869 from sqlalchemy import (

2870 MetaData,

2871 Table,

2872 Column,

2873 Integer,

2874 Date,

2875 select,

2876 literal,

2877 and_,

2878 exists,

2879 )

2880

2881 metadata = MetaData()

2882

2883 visitors = Table(

2884 "visitors",

2885 metadata,

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

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

2888 Column("count", Integer),

2889 )

2890

2891 # add 5 visitors for the product_id == 1

2892 product_id = 1

2893 day = date.today()

2894 count = 5

2895

2896 update_cte = (

2897 visitors.update()

2898 .where(

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

2900 )

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

2902 .returning(literal(1))

2903 .cte("update_cte")

2904 )

2905

2906 upsert = visitors.insert().from_select(

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

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

2909 ~exists(update_cte.select())

2910 ),

2911 )

2912

2913 connection.execute(upsert)

2914

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

2916

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

2918

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

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

2921 "value_a", nesting=True

2922 )

2923

2924 # Nesting CTEs takes ascendency locally

2925 # over the CTEs at a higher level

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

2927

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

2929

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

2931 shown with inline parameters below as:

2932

2933 .. sourcecode:: sql

2934

2935 WITH

2936 value_a AS

2937 (SELECT 'root' AS n),

2938 value_b AS

2939 (WITH value_a AS

2940 (SELECT 'nesting' AS n)

2941 SELECT value_a.n AS n FROM value_a)

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

2943 FROM value_a, value_b

2944

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

2946 as follows (SQLAlchemy 2.0 and above)::

2947

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

2949

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

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

2952

2953 # Nesting CTEs takes ascendency locally

2954 # over the CTEs at a higher level

2955 value_b = (

2956 select(value_a_nested.c.n)

2957 .add_cte(value_a_nested, nest_here=True)

2958 .cte("value_b")

2959 )

2960

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

2962

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

2964

2965 edge = Table(

2966 "edge",

2967 metadata,

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

2969 Column("left", Integer),

2970 Column("right", Integer),

2971 )

2972

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

2974

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

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

2977 )

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

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

2980 )

2981

2982 subgraph_cte = root_node.union(left_edge, right_edge)

2983

2984 subgraph = select(subgraph_cte)

2985

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

2987

2988 .. sourcecode:: sql

2989

2990 WITH RECURSIVE nodes(node) AS (

2991 SELECT 1 AS node

2992 UNION

2993 SELECT edge."left" AS "left"

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

2995 UNION

2996 SELECT edge."right" AS "right"

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

2998 )

2999 SELECT nodes.node FROM nodes

3000

3001 .. seealso::

3002

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

3004 :meth:`_expression.HasCTE.cte`.

3005

3006 """ # noqa: E501

3007 return CTE._construct(

3008 self, name=name, recursive=recursive, nesting=nesting

3009 )

3010

3011

3012class Subquery(AliasedReturnsRows[_KeyColCC_co]):

3013 """Represent a subquery of a SELECT.

3014

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

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

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

3018 :class:`_expression.SelectBase` subclass

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

3020 :class:`_expression.CompoundSelect`, and

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

3022 it represents the

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

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

3025

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

3027 :class:`_expression.Alias`

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

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

3030 :class:`_expression.Alias` always

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

3032 :class:`.Subquery`

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

3034

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

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

3037 statement.

3038

3039 """

3040

3041 __visit_name__ = "subquery"

3042

3043 _is_subquery = True

3044

3045 inherit_cache = True

3046

3047 element: SelectBase

3048

3049 @classmethod

3050 def _factory(

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

3052 ) -> Subquery:

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

3054

3055 return coercions.expect(

3056 roles.SelectStatementRole, selectable

3057 ).subquery(name=name)

3058

3059 @util.deprecated(

3060 "1.4",

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

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

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

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

3065 ":func:`_expression.select` "

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

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

3068 )

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

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

3071

3072

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

3074 """Represent a grouping of a FROM clause"""

3075

3076 _traverse_internals: _TraverseInternalsType = [

3077 ("element", InternalTraversal.dp_clauseelement)

3078 ]

3079

3080 element: FromClause[_KeyColCC_co]

3081

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

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

3084

3085 @util.ro_non_memoized_property

3086 def columns(self) -> _KeyColCC_co:

3087 return self.element.columns

3088

3089 @util.ro_non_memoized_property

3090 def c(self) -> _KeyColCC_co:

3091 return self.element.columns

3092

3093 @property

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

3095 return self.element.primary_key

3096

3097 @property

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

3099 return self.element.foreign_keys

3100

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

3102 return self.element.is_derived_from(fromclause)

3103

3104 def alias(

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

3106 ) -> NamedFromGrouping[_KeyColCC_co]:

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

3108

3109 def _anonymous_fromclause(

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

3111 ) -> FromGrouping:

3112 return FromGrouping(

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

3114 )

3115

3116 @util.ro_non_memoized_property

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

3118 return self.element._hide_froms

3119

3120 @util.ro_non_memoized_property

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

3122 return self.element._from_objects

3123

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

3125 return {"element": self.element}

3126

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

3128 self.element = state["element"]

3129

3130 if TYPE_CHECKING:

3131

3132 def self_group(

3133 self, against: Optional[OperatorType] = None

3134 ) -> Self: ...

3135

3136

3137class NamedFromGrouping(

3138 FromGrouping[_KeyColCC_co], NamedFromClause[_KeyColCC_co]

3139):

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

3141

3142 .. versionadded:: 2.0

3143

3144 """

3145

3146 inherit_cache = True

3147

3148 if TYPE_CHECKING:

3149

3150 def self_group(

3151 self, against: Optional[OperatorType] = None

3152 ) -> Self: ...

3153

3154

3155class TableClause(

3156 roles.DMLTableRole, Immutable, NamedFromClause[_ColClauseCC_co]

3157):

3158 """Represents a minimal "table" construct.

3159

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

3161 collection of columns, which are typically produced

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

3163

3164 from sqlalchemy import table, column

3165

3166 user = table(

3167 "user",

3168 column("id"),

3169 column("name"),

3170 column("description"),

3171 )

3172

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

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

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

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

3177

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

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

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

3181 It's useful

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

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

3184 is not on hand.

3185

3186 """

3187

3188 __visit_name__ = "table"

3189

3190 _traverse_internals: _TraverseInternalsType = [

3191 (

3192 "columns",

3193 InternalTraversal.dp_fromclause_canonical_column_collection,

3194 ),

3195 ("name", InternalTraversal.dp_string),

3196 ("schema", InternalTraversal.dp_string),

3197 ]

3198

3199 _is_table = True

3200

3201 fullname: str

3202

3203 implicit_returning = False

3204 """:class:`_expression.TableClause`

3205 doesn't support having a primary key or column

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

3207

3208 _columns: DedupeColumnCollection[ColumnClause[Any]]

3209

3210 @util.ro_memoized_property

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

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

3213 return None

3214

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

3216 super().__init__()

3217 self.name = name

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

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

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

3221 for c in columns:

3222 self.append_column(c)

3223

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

3225 if schema is not None:

3226 self.schema = schema

3227 if self.schema is not None:

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

3229 else:

3230 self.fullname = self.name

3231 if kw:

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

3233

3234 if TYPE_CHECKING:

3235

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

3237

3238 def __str__(self) -> str:

3239 if self.schema is not None:

3240 return self.schema + "." + self.name

3241 else:

3242 return self.name

3243

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

3245 pass

3246

3247 @util.ro_memoized_property

3248 def description(self) -> str:

3249 return self.name

3250

3251 def _insert_col_impl(

3252 self,

3253 c: ColumnClause[Any],

3254 *,

3255 index: Optional[int] = None,

3256 ) -> None:

3257 existing = c.table

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

3259 raise exc.ArgumentError(

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

3261 % (c.key, existing)

3262 )

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

3264 c.table = self

3265

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

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

3268 self._insert_col_impl(column)

3269

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

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

3272 a specific position.

3273

3274 .. versionadded:: 2.1

3275

3276 """

3277 self._insert_col_impl(column, index=index)

3278

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

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

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

3282 :class:`_expression.TableClause`.

3283

3284 E.g.::

3285

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

3287

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

3289

3290 """

3291

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

3293

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

3295 def update(self) -> Update:

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

3297 :class:`_expression.TableClause`.

3298

3299 E.g.::

3300

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

3302

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

3304

3305 """

3306 return util.preloaded.sql_dml.Update(

3307 self,

3308 )

3309

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

3311 def delete(self) -> Delete:

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

3313 :class:`_expression.TableClause`.

3314

3315 E.g.::

3316

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

3318

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

3320

3321 """

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

3323

3324 @util.ro_non_memoized_property

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

3326 return [self]

3327

3328

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

3330

3331

3332class ForUpdateArg(ClauseElement):

3333 _traverse_internals: _TraverseInternalsType = [

3334 ("of", InternalTraversal.dp_clauseelement_list),

3335 ("nowait", InternalTraversal.dp_boolean),

3336 ("read", InternalTraversal.dp_boolean),

3337 ("skip_locked", InternalTraversal.dp_boolean),

3338 ("key_share", InternalTraversal.dp_boolean),

3339 ]

3340

3341 of: Optional[Sequence[ClauseElement]]

3342 nowait: bool

3343 read: bool

3344 skip_locked: bool

3345

3346 @classmethod

3347 def _from_argument(

3348 cls, with_for_update: ForUpdateParameter

3349 ) -> Optional[ForUpdateArg]:

3350 if isinstance(with_for_update, ForUpdateArg):

3351 return with_for_update

3352 elif with_for_update in (None, False):

3353 return None

3354 elif with_for_update is True:

3355 return ForUpdateArg()

3356 else:

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

3358

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

3360 return (

3361 isinstance(other, ForUpdateArg)

3362 and other.nowait == self.nowait

3363 and other.read == self.read

3364 and other.skip_locked == self.skip_locked

3365 and other.key_share == self.key_share

3366 and other.of is self.of

3367 )

3368

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

3370 return not self.__eq__(other)

3371

3372 def __hash__(self) -> int:

3373 return id(self)

3374

3375 def __init__(

3376 self,

3377 *,

3378 nowait: bool = False,

3379 read: bool = False,

3380 of: Optional[_ForUpdateOfArgument] = None,

3381 skip_locked: bool = False,

3382 key_share: bool = False,

3383 ):

3384 """Represents arguments specified to

3385 :meth:`_expression.Select.for_update`.

3386

3387 """

3388

3389 self.nowait = nowait

3390 self.read = read

3391 self.skip_locked = skip_locked

3392 self.key_share = key_share

3393 if of is not None:

3394 self.of = [

3395 coercions.expect(roles.ColumnsClauseRole, elem)

3396 for elem in util.to_list(of)

3397 ]

3398 else:

3399 self.of = None

3400

3401

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

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

3404 in a statement.

3405

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

3407 :func:`_expression.values` function.

3408

3409 .. versionadded:: 1.4

3410

3411 """

3412

3413 __visit_name__ = "values"

3414

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

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

3417

3418 _unnamed: bool

3419 _traverse_internals: _TraverseInternalsType = [

3420 ("_column_args", InternalTraversal.dp_clauseelement_list),

3421 ("_data", InternalTraversal.dp_dml_multi_values),

3422 ("name", InternalTraversal.dp_string),

3423 ("literal_binds", InternalTraversal.dp_boolean),

3424 ] + HasCTE._has_ctes_traverse_internals

3425

3426 name_cte_columns = True

3427

3428 def __init__(

3429 self,

3430 *columns: _OnlyColumnArgument[Any],

3431 name: Optional[str] = None,

3432 literal_binds: bool = False,

3433 ):

3434 super().__init__()

3435 self._column_args = tuple(

3436 coercions.expect(roles.LabeledColumnExprRole, col)

3437 for col in columns

3438 )

3439

3440 if name is None:

3441 self._unnamed = True

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

3443 else:

3444 self._unnamed = False

3445 self.name = name

3446 self.literal_binds = literal_binds

3447 self.named_with_column = not self._unnamed

3448

3449 @property

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

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

3452

3453 @util.ro_non_memoized_property

3454 def _all_selected_columns(self) -> _SelectIterable:

3455 return self._column_args

3456

3457 @_generative

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

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

3460 construct that is a copy of this

3461 one with the given name.

3462

3463 This method is a VALUES-specific specialization of the

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

3465

3466 .. seealso::

3467

3468 :ref:`tutorial_using_aliases`

3469

3470 :func:`_expression.alias`

3471

3472 """

3473 non_none_name: str

3474

3475 if name is None:

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

3477 else:

3478 non_none_name = name

3479

3480 self.name = non_none_name

3481 self.named_with_column = True

3482 self._unnamed = False

3483 return self

3484

3485 @_generative

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

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

3488 so that

3489 it renders as LATERAL.

3490

3491 .. seealso::

3492

3493 :func:`_expression.lateral`

3494

3495 """

3496 non_none_name: str

3497

3498 if name is None:

3499 non_none_name = self.name

3500 else:

3501 non_none_name = name

3502

3503 self._is_lateral = True

3504 self.name = non_none_name

3505 self._unnamed = False

3506 return self

3507

3508 @_generative

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

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

3511 adding the given data to the data list.

3512

3513 E.g.::

3514

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

3516

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

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

3519 constructor.

3520

3521 """

3522

3523 self._data += (values,)

3524 return self

3525

3526 def scalar_values(self) -> ScalarValues:

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

3528 COLUMN element in a statement.

3529

3530 .. versionadded:: 2.0.0b4

3531

3532 """

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

3534

3535 def _populate_column_collection(

3536 self,

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

3538 primary_key: ColumnSet,

3539 foreign_keys: Set[KeyedColumnElement[Any]],

3540 ) -> None:

3541 for c in self._column_args:

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

3543 _, c = c._make_proxy(

3544 self, primary_key=primary_key, foreign_keys=foreign_keys

3545 )

3546 else:

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

3548 # no memoizations of other FROM clauses.

3549 # see test_values.py -> test_auto_proxy_select_direct_col

3550 c._reset_memoizations()

3551 columns.add(c)

3552 c.table = self

3553

3554 @util.ro_non_memoized_property

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

3556 return [self]

3557

3558

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

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

3561 COLUMN element in a statement.

3562

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

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

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

3566 an ``IN`` or ``NOT IN`` condition.

3567

3568 .. versionadded:: 2.0.0b4

3569

3570 """

3571

3572 __visit_name__ = "scalar_values"

3573

3574 _traverse_internals: _TraverseInternalsType = [

3575 ("_column_args", InternalTraversal.dp_clauseelement_list),

3576 ("_data", InternalTraversal.dp_dml_multi_values),

3577 ("literal_binds", InternalTraversal.dp_boolean),

3578 ]

3579

3580 def __init__(

3581 self,

3582 columns: Sequence[NamedColumn[Any]],

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

3584 literal_binds: bool,

3585 ):

3586 super().__init__()

3587 self._column_args = columns

3588 self._data = data

3589 self.literal_binds = literal_binds

3590

3591 @property

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

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

3594

3595 def __clause_element__(self) -> ScalarValues:

3596 return self

3597

3598 if TYPE_CHECKING:

3599

3600 def self_group(

3601 self, against: Optional[OperatorType] = None

3602 ) -> Self: ...

3603

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

3605

3606

3607class SelectBase(

3608 roles.SelectStatementRole,

3609 roles.DMLSelectRole,

3610 roles.CompoundElementRole,

3611 roles.InElementRole,

3612 HasCTE,

3613 SupportsCloneAnnotations,

3614 Selectable,

3615):

3616 """Base class for SELECT statements.

3617

3618

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

3620 :class:`_expression.CompoundSelect` and

3621 :class:`_expression.TextualSelect`.

3622

3623

3624 """

3625

3626 _is_select_base = True

3627 is_select = True

3628

3629 _label_style: SelectLabelStyle = LABEL_STYLE_NONE

3630

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

3632 self._reset_memoizations()

3633

3634 @util.ro_non_memoized_property

3635 def selected_columns(

3636 self,

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

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

3639 representing the columns that

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

3641

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

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

3644 within this collection cannot be directly nested inside another SELECT

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

3646 necessary parenthesization required by SQL.

3647

3648 .. note::

3649

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

3651 include expressions established in the columns clause using the

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

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

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

3655 construct.

3656

3657 .. seealso::

3658

3659 :attr:`_sql.Select.selected_columns`

3660

3661 .. versionadded:: 1.4

3662

3663 """

3664 raise NotImplementedError()

3665

3666 def _generate_fromclause_column_proxies(

3667 self,

3668 subquery: FromClause,

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

3670 primary_key: ColumnSet,

3671 foreign_keys: Set[KeyedColumnElement[Any]],

3672 *,

3673 proxy_compound_columns: Optional[

3674 Iterable[Sequence[ColumnElement[Any]]]

3675 ] = None,

3676 ) -> None:

3677 raise NotImplementedError()

3678

3679 @util.ro_non_memoized_property

3680 def _all_selected_columns(self) -> _SelectIterable:

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

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

3683 constructs.

3684

3685 .. versionadded:: 1.4.12

3686

3687 .. seealso::

3688

3689 :attr:`_sql.SelectBase.exported_columns`

3690

3691 """

3692 raise NotImplementedError()

3693

3694 @property

3695 def exported_columns(

3696 self,

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

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

3699 that represents the "exported"

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

3701 :class:`_sql.TextClause` constructs.

3702

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

3704 object are synonymous

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

3706

3707 .. versionadded:: 1.4

3708

3709 .. seealso::

3710

3711 :attr:`_expression.Select.exported_columns`

3712

3713 :attr:`_expression.Selectable.exported_columns`

3714

3715 :attr:`_expression.FromClause.exported_columns`

3716

3717

3718 """

3719 return self.selected_columns._as_readonly()

3720

3721 def get_label_style(self) -> SelectLabelStyle:

3722 """

3723 Retrieve the current label style.

3724

3725 Implemented by subclasses.

3726

3727 """

3728 raise NotImplementedError()

3729

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

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

3732

3733 Implemented by subclasses.

3734

3735 """

3736

3737 raise NotImplementedError()

3738

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

3740 raise NotImplementedError()

3741

3742 @util.deprecated(

3743 "1.4",

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

3745 "method is deprecated and will be "

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

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

3748 )

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

3750 return self.scalar_subquery()

3751

3752 def exists(self) -> Exists:

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

3754 which can be used as a column expression.

3755

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

3757

3758 .. seealso::

3759

3760 :func:`_sql.exists`

3761

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

3763

3764 .. versionadded:: 1.4

3765

3766 """

3767 return Exists(self)

3768

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

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

3771 used as a column expression.

3772

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

3774

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

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

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

3778 an enclosing SELECT.

3779

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

3781 subquery that can be produced using the

3782 :meth:`_expression.SelectBase.subquery`

3783 method.

3784

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

3786 :meth:`_expression.SelectBase.scalar_subquery`.

3787

3788 .. seealso::

3789

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

3791

3792 """

3793 if self._label_style is not LABEL_STYLE_NONE:

3794 self = self.set_label_style(LABEL_STYLE_NONE)

3795

3796 return ScalarSelect(self)

3797

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

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

3800 subquery with a label.

3801

3802 .. seealso::

3803

3804 :meth:`_expression.SelectBase.scalar_subquery`.

3805

3806 """

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

3808

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

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

3811

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

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

3814

3815 .. seealso::

3816

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

3818

3819 """

3820 return Lateral._factory(self, name)

3821

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

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

3824

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

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

3827 SELECT statement.

3828

3829 Given a SELECT statement such as::

3830

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

3832

3833 The above statement might look like:

3834

3835 .. sourcecode:: sql

3836

3837 SELECT table.id, table.name FROM table

3838

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

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

3841 a named sub-element::

3842

3843 subq = stmt.subquery()

3844 new_stmt = select(subq)

3845

3846 The above renders as:

3847

3848 .. sourcecode:: sql

3849

3850 SELECT anon_1.id, anon_1.name

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

3852

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

3854 is equivalent to calling

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

3856 method on a FROM object; however,

3857 as a :class:`_expression.SelectBase`

3858 object is not directly FROM object,

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

3860 method provides clearer semantics.

3861

3862 .. versionadded:: 1.4

3863

3864 """

3865

3866 return Subquery._construct(

3867 self._ensure_disambiguated_names(), name=name

3868 )

3869

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

3871 def into(

3872 self,

3873 target: str,

3874 *,

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

3876 schema: Optional[str] = None,

3877 temporary: bool = False,

3878 if_not_exists: bool = False,

3879 ) -> CreateTableAs:

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

3881

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

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

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

3885

3886 Supported on all included backends, the construct emits

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

3888 emits a ``SELECT..INTO`` statement.

3889

3890 e.g.::

3891

3892 from sqlalchemy import select

3893

3894 # Create a new table from a SELECT

3895 stmt = (

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

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

3898 .into("active_users")

3899 )

3900

3901 with engine.begin() as conn:

3902 conn.execute(stmt)

3903

3904 # With optional flags

3905 stmt = (

3906 select(users.c.id)

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

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

3909 )

3910

3911 .. versionadded:: 2.1

3912

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

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

3915

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

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

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

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

3920 is created.

3921

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

3923

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

3925

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

3927

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

3929

3930 .. seealso::

3931

3932 :ref:`metadata_create_table_as` - in :ref:`metadata_toplevel`

3933

3934 :class:`_schema.CreateTableAs`

3935

3936 """

3937 sql_ddl = util.preloaded.sql_ddl

3938

3939 return sql_ddl.CreateTableAs(

3940 self,

3941 target,

3942 metadata=metadata,

3943 schema=schema,

3944 temporary=temporary,

3945 if_not_exists=if_not_exists,

3946 )

3947

3948 def _ensure_disambiguated_names(self) -> Self:

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

3950 disambiguated in some way, if possible.

3951

3952 """

3953

3954 raise NotImplementedError()

3955

3956 def alias(

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

3958 ) -> Subquery:

3959 """Return a named subquery against this

3960 :class:`_expression.SelectBase`.

3961

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

3963 :class:`_expression.FromClause`),

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

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

3966 :class:`_expression.FromClause`.

3967

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

3969 method is now

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

3971

3972 """

3973 return self.subquery(name=name)

3974

3975

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

3977

3978

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

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

3981

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

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

3984 compound selects.

3985

3986 """

3987

3988 __visit_name__ = "select_statement_grouping"

3989 _traverse_internals: _TraverseInternalsType = [

3990 ("element", InternalTraversal.dp_clauseelement)

3991 ] + SupportsCloneAnnotations._clone_annotations_traverse_internals

3992

3993 _is_select_container = True

3994

3995 element: _SB

3996

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

3998 self.element = cast(

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

4000 )

4001

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

4003 new_element = self.element._ensure_disambiguated_names()

4004 if new_element is not self.element:

4005 return SelectStatementGrouping(new_element)

4006 else:

4007 return self

4008

4009 def get_label_style(self) -> SelectLabelStyle:

4010 return self.element.get_label_style()

4011

4012 def set_label_style(

4013 self, label_style: SelectLabelStyle

4014 ) -> SelectStatementGrouping[_SB]:

4015 return SelectStatementGrouping(

4016 self.element.set_label_style(label_style)

4017 )

4018

4019 @property

4020 def select_statement(self) -> _SB:

4021 return self.element

4022

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

4024 return self

4025

4026 if TYPE_CHECKING:

4027

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

4029

4030 # def _generate_columns_plus_names(

4031 # self, anon_for_dupe_key: bool

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

4033 # return self.element._generate_columns_plus_names(anon_for_dupe_key)

4034

4035 def _generate_fromclause_column_proxies(

4036 self,

4037 subquery: FromClause,

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

4039 primary_key: ColumnSet,

4040 foreign_keys: Set[KeyedColumnElement[Any]],

4041 *,

4042 proxy_compound_columns: Optional[

4043 Iterable[Sequence[ColumnElement[Any]]]

4044 ] = None,

4045 ) -> None:

4046 self.element._generate_fromclause_column_proxies(

4047 subquery,

4048 columns,

4049 proxy_compound_columns=proxy_compound_columns,

4050 primary_key=primary_key,

4051 foreign_keys=foreign_keys,

4052 )

4053

4054 @util.ro_non_memoized_property

4055 def _all_selected_columns(self) -> _SelectIterable:

4056 return self.element._all_selected_columns

4057

4058 @util.ro_non_memoized_property

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

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

4061 representing the columns that

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

4063 :class:`_sql.TextClause` constructs.

4064

4065 .. versionadded:: 1.4

4066

4067 .. seealso::

4068

4069 :attr:`_sql.Select.selected_columns`

4070

4071 """

4072 return self.element.selected_columns

4073

4074 @util.ro_non_memoized_property

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

4076 return self.element._from_objects

4077

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

4079 return self.element._scalar_type()

4080

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

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

4083 raise NotImplementedError

4084

4085

4086class GenerativeSelect(DialectKWArgs, SelectBase, Generative):

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

4088 added.

4089

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

4091 :class:`_expression.CompoundSelect`

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

4093 rendering can be controlled. Compare to

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

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

4096 and is also a SELECT construct,

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

4098 only wrapped as a subquery.

4099

4100 """

4101

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

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

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

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

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

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

4108 _for_update_arg: Optional[ForUpdateArg] = None

4109

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

4111 self._label_style = _label_style

4112

4113 @_generative

4114 def with_for_update(

4115 self,

4116 *,

4117 nowait: bool = False,

4118 read: bool = False,

4119 of: Optional[_ForUpdateOfArgument] = None,

4120 skip_locked: bool = False,

4121 key_share: bool = False,

4122 ) -> Self:

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

4124 :class:`_expression.GenerativeSelect`.

4125

4126 E.g.::

4127

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

4129

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

4131 render a statement like:

4132

4133 .. sourcecode:: sql

4134

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

4136

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

4138 would produce:

4139

4140 .. sourcecode:: sql

4141

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

4143

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

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

4146 provided which allow for common database-specific

4147 variants.

4148

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

4150 Database and PostgreSQL dialects.

4151

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

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

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

4155

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

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

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

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

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

4161 table or as a column depending on backend.

4162

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

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

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

4166

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

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

4169 on the PostgreSQL dialect.

4170

4171 """

4172 self._for_update_arg = ForUpdateArg(

4173 nowait=nowait,

4174 read=read,

4175 of=of,

4176 skip_locked=skip_locked,

4177 key_share=key_share,

4178 )

4179 return self

4180

4181 def get_label_style(self) -> SelectLabelStyle:

4182 """

4183 Retrieve the current label style.

4184

4185 .. versionadded:: 1.4

4186

4187 """

4188 return self._label_style

4189

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

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

4192

4193 There are three "label styles" available,

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

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

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

4197 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`.

4198

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

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

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

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

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

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

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

4206 that the impact of this labeling is minimal.

4207

4208 The rationale for disambiguation is mostly so that all column

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

4210 collection when a subquery is created.

4211

4212 .. versionadded:: 1.4 - the

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

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

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

4216

4217 .. seealso::

4218

4219 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DISAMBIGUATE_ONLY`

4220

4221 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_TABLENAME_PLUS_COL`

4222

4223 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_NONE`

4224

4225 :attr:`_sql.SelectLabelStyle.LABEL_STYLE_DEFAULT`

4226

4227 """

4228 if self._label_style is not style:

4229 self = self._generate()

4230 self._label_style = style

4231 return self

4232

4233 @property

4234 def _group_by_clause(self) -> ClauseList:

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

4236 return ClauseList._construct_raw(

4237 operators.comma_op, self._group_by_clauses

4238 )

4239

4240 @property

4241 def _order_by_clause(self) -> ClauseList:

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

4243 return ClauseList._construct_raw(

4244 operators.comma_op, self._order_by_clauses

4245 )

4246

4247 def _offset_or_limit_clause(

4248 self,

4249 element: _LimitOffsetType,

4250 name: Optional[str] = None,

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

4252 ) -> ColumnElement[Any]:

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

4254

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

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

4257

4258 """

4259 return coercions.expect(

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

4261 )

4262

4263 @overload

4264 def _offset_or_limit_clause_asint(

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

4266 ) -> NoReturn: ...

4267

4268 @overload

4269 def _offset_or_limit_clause_asint(

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

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

4272

4273 def _offset_or_limit_clause_asint(

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

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

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

4277 integer.

4278

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

4280 parameter. Otherwise, a compilation error is raised.

4281

4282 """

4283 if clause is None:

4284 return None

4285 try:

4286 value = clause._limit_offset_value

4287 except AttributeError as err:

4288 raise exc.CompileError(

4289 "This SELECT structure does not use a simple "

4290 "integer value for %s" % attrname

4291 ) from err

4292 else:

4293 return util.asint(value)

4294

4295 @property

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

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

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

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

4300 isn't currently set to an integer.

4301

4302 """

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

4304

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

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

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

4308 """

4309 return isinstance(clause, _OffsetLimitParam)

4310

4311 @property

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

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

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

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

4316 offset isn't currently set to an integer.

4317

4318 """

4319 return self._offset_or_limit_clause_asint(

4320 self._offset_clause, "offset"

4321 )

4322

4323 @property

4324 def _has_row_limiting_clause(self) -> bool:

4325 return (

4326 self._limit_clause is not None

4327 or self._offset_clause is not None

4328 or self._fetch_clause is not None

4329 )

4330

4331 @_generative

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

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

4334 applied.

4335

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

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

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

4339 functionality.

4340

4341 .. note::

4342

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

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

4345

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

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

4348

4349 .. seealso::

4350

4351 :meth:`_sql.GenerativeSelect.fetch`

4352

4353 :meth:`_sql.GenerativeSelect.offset`

4354

4355 """

4356

4357 self._fetch_clause = self._fetch_clause_options = None

4358 self._limit_clause = self._offset_or_limit_clause(limit)

4359 return self

4360

4361 @_generative

4362 def fetch(

4363 self,

4364 count: _LimitOffsetType,

4365 with_ties: bool = False,

4366 percent: bool = False,

4367 **dialect_kw: Any,

4368 ) -> Self:

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

4370 applied.

4371

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

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

4374 select. This functionality is is currently implemented for Oracle

4375 Database, PostgreSQL, MSSQL.

4376

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

4378

4379 .. note::

4380

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

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

4383

4384 .. versionadded:: 1.4

4385

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

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

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

4389 Pass ``None`` to reset it.

4390

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

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

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

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

4395

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

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

4398

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

4400 may be accepted by dialects.

4401

4402 .. versionadded:: 2.0.41

4403

4404 .. seealso::

4405

4406 :meth:`_sql.GenerativeSelect.limit`

4407

4408 :meth:`_sql.GenerativeSelect.offset`

4409

4410 """

4411 self._validate_dialect_kwargs(dialect_kw)

4412 self._limit_clause = None

4413 if count is None:

4414 self._fetch_clause = self._fetch_clause_options = None

4415 else:

4416 self._fetch_clause = self._offset_or_limit_clause(count)

4417 self._fetch_clause_options = {

4418 "with_ties": with_ties,

4419 "percent": percent,

4420 }

4421 return self

4422

4423 @_generative

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

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

4426 applied.

4427

4428

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

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

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

4432 functionality.

4433

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

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

4436

4437 .. seealso::

4438

4439 :meth:`_sql.GenerativeSelect.limit`

4440

4441 :meth:`_sql.GenerativeSelect.fetch`

4442

4443 """

4444

4445 self._offset_clause = self._offset_or_limit_clause(offset)

4446 return self

4447

4448 @_generative

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

4450 def slice(

4451 self,

4452 start: int,

4453 stop: int,

4454 ) -> Self:

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

4456

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

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

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

4460 query.

4461

4462 For example, ::

4463

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

4465

4466 renders as

4467

4468 .. sourcecode:: sql

4469

4470 SELECT users.id AS users_id,

4471 users.name AS users_name

4472 FROM users ORDER BY users.id

4473 LIMIT ? OFFSET ?

4474 (2, 1)

4475

4476 .. note::

4477

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

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

4480

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

4482 method generalized from the ORM.

4483

4484 .. seealso::

4485

4486 :meth:`_sql.GenerativeSelect.limit`

4487

4488 :meth:`_sql.GenerativeSelect.offset`

4489

4490 :meth:`_sql.GenerativeSelect.fetch`

4491

4492 """

4493 sql_util = util.preloaded.sql_util

4494 self._fetch_clause = self._fetch_clause_options = None

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

4496 self._limit_clause, self._offset_clause, start, stop

4497 )

4498 return self

4499

4500 @_generative

4501 def order_by(

4502 self,

4503 __first: Union[

4504 Literal[None, _NoArg.NO_ARG],

4505 _ColumnExpressionOrStrLabelArgument[Any],

4506 ] = _NoArg.NO_ARG,

4507 /,

4508 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4509 ) -> Self:

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

4511 criteria applied.

4512

4513 e.g.::

4514

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

4516

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

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

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

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

4521

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

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

4524

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

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

4527

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

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

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

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

4532 label matching in ORDER BY and GROUP BY expressions.

4533

4534 .. seealso::

4535

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

4537

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

4539

4540 """

4541

4542 if not clauses and __first is None:

4543 self._order_by_clauses = ()

4544 elif __first is not _NoArg.NO_ARG:

4545 self._order_by_clauses += tuple(

4546 coercions.expect(

4547 roles.OrderByRole, clause, apply_propagate_attrs=self

4548 )

4549 for clause in (__first,) + clauses

4550 )

4551 return self

4552

4553 @_generative

4554 def group_by(

4555 self,

4556 __first: Union[

4557 Literal[None, _NoArg.NO_ARG],

4558 _ColumnExpressionOrStrLabelArgument[Any],

4559 ] = _NoArg.NO_ARG,

4560 /,

4561 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4562 ) -> Self:

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

4564 criterion applied.

4565

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

4567

4568 e.g.::

4569

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

4571

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

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

4574

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

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

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

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

4579 label matching in ORDER BY and GROUP BY expressions.

4580

4581 .. seealso::

4582

4583 :ref:`tutorial_group_by_w_aggregates` - in the

4584 :ref:`unified_tutorial`

4585

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

4587

4588 """ # noqa: E501

4589

4590 if not clauses and __first is None:

4591 self._group_by_clauses = ()

4592 elif __first is not _NoArg.NO_ARG:

4593 self._group_by_clauses += tuple(

4594 coercions.expect(

4595 roles.GroupByRole, clause, apply_propagate_attrs=self

4596 )

4597 for clause in (__first,) + clauses

4598 )

4599 return self

4600

4601

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

4603class CompoundSelectState(CompileState):

4604 @util.memoized_property

4605 def _label_resolve_dict(

4606 self,

4607 ) -> Tuple[

4608 Dict[str, ColumnElement[Any]],

4609 Dict[str, ColumnElement[Any]],

4610 Dict[str, ColumnElement[Any]],

4611 ]:

4612 # TODO: this is hacky and slow

4613 hacky_subquery = self.statement.subquery()

4614 hacky_subquery.named_with_column = False

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

4616 return d, d, d

4617

4618

4619class _CompoundSelectKeyword(Enum):

4620 UNION = "UNION"

4621 UNION_ALL = "UNION ALL"

4622 EXCEPT = "EXCEPT"

4623 EXCEPT_ALL = "EXCEPT ALL"

4624 INTERSECT = "INTERSECT"

4625 INTERSECT_ALL = "INTERSECT ALL"

4626

4627

4628class CompoundSelect(

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

4630):

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

4632 SELECT-based set operations.

4633

4634

4635 .. seealso::

4636

4637 :func:`_expression.union`

4638

4639 :func:`_expression.union_all`

4640

4641 :func:`_expression.intersect`

4642

4643 :func:`_expression.intersect_all`

4644

4645 :func:`_expression.except`

4646

4647 :func:`_expression.except_all`

4648

4649 """

4650

4651 __visit_name__ = "compound_select"

4652

4653 _traverse_internals: _TraverseInternalsType = (

4654 [

4655 ("selects", InternalTraversal.dp_clauseelement_list),

4656 ("_limit_clause", InternalTraversal.dp_clauseelement),

4657 ("_offset_clause", InternalTraversal.dp_clauseelement),

4658 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4659 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4660 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4661 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4662 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4663 ("keyword", InternalTraversal.dp_string),

4664 ]

4665 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4666 + HasCTE._has_ctes_traverse_internals

4667 + DialectKWArgs._dialect_kwargs_traverse_internals

4668 + ExecutableStatement._executable_traverse_internals

4669 )

4670

4671 selects: List[SelectBase]

4672

4673 _is_from_container = True

4674 _auto_correlate = False

4675

4676 def __init__(

4677 self,

4678 keyword: _CompoundSelectKeyword,

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

4680 ):

4681 self.keyword = keyword

4682 self.selects = [

4683 coercions.expect(

4684 roles.CompoundElementRole, s, apply_propagate_attrs=self

4685 ).self_group(against=self)

4686 for s in selects

4687 ]

4688

4689 GenerativeSelect.__init__(self)

4690

4691 @classmethod

4692 def _create_union(

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

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

4695 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4696

4697 @classmethod

4698 def _create_union_all(

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

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

4701 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4702

4703 @classmethod

4704 def _create_except(

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

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

4707 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4708

4709 @classmethod

4710 def _create_except_all(

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

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

4713 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4714

4715 @classmethod

4716 def _create_intersect(

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

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

4719 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4720

4721 @classmethod

4722 def _create_intersect_all(

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

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

4725 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4726

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

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

4729

4730 def self_group(

4731 self, against: Optional[OperatorType] = None

4732 ) -> GroupedElement:

4733 return SelectStatementGrouping(self)

4734

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

4736 for s in self.selects:

4737 if s.is_derived_from(fromclause):

4738 return True

4739 return False

4740

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

4742 if self._label_style is not style:

4743 self = self._generate()

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

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

4746

4747 return self

4748

4749 def _ensure_disambiguated_names(self) -> Self:

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

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

4752 self = self._generate()

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

4754

4755 return self

4756

4757 def _generate_fromclause_column_proxies(

4758 self,

4759 subquery: FromClause,

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

4761 primary_key: ColumnSet,

4762 foreign_keys: Set[KeyedColumnElement[Any]],

4763 *,

4764 proxy_compound_columns: Optional[

4765 Iterable[Sequence[ColumnElement[Any]]]

4766 ] = None,

4767 ) -> None:

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

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

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

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

4772 # generate below.

4773 select_0 = self.selects[0]

4774

4775 if self._label_style is not LABEL_STYLE_DEFAULT:

4776 select_0 = select_0.set_label_style(self._label_style)

4777

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

4779 # derived columns place a 'weight' annotation corresponding

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

4781 # that the corresponding_column() operation can resolve

4782 # conflicts

4783 extra_col_iterator = zip(

4784 *[

4785 [

4786 c._annotate(dd)

4787 for c in stmt._all_selected_columns

4788 if is_column_element(c)

4789 ]

4790 for dd, stmt in [

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

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

4793 ]

4794 ]

4795 )

4796

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

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

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

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

4801 # compound

4802 select_0._generate_fromclause_column_proxies(

4803 subquery,

4804 columns,

4805 proxy_compound_columns=extra_col_iterator,

4806 primary_key=primary_key,

4807 foreign_keys=foreign_keys,

4808 )

4809

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

4811 super()._refresh_for_new_column(column)

4812 for select in self.selects:

4813 select._refresh_for_new_column(column)

4814

4815 @util.ro_non_memoized_property

4816 def _all_selected_columns(self) -> _SelectIterable:

4817 return self.selects[0]._all_selected_columns

4818

4819 @util.ro_non_memoized_property

4820 def selected_columns(

4821 self,

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

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

4824 representing the columns that

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

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

4827

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

4829 :attr:`_expression.CompoundSelect.selected_columns`

4830 attribute returns the selected

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

4832 statements within the set operation.

4833

4834 .. seealso::

4835

4836 :attr:`_sql.Select.selected_columns`

4837

4838 .. versionadded:: 1.4

4839

4840 """

4841 return self.selects[0].selected_columns

4842

4843

4844# backwards compat

4845for elem in _CompoundSelectKeyword:

4846 setattr(CompoundSelect, elem.name, elem)

4847

4848

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

4850class SelectState(util.MemoizedSlots, CompileState):

4851 __slots__ = (

4852 "from_clauses",

4853 "froms",

4854 "columns_plus_names",

4855 "_label_resolve_dict",

4856 )

4857

4858 if TYPE_CHECKING:

4859 default_select_compile_options: CacheableOptions

4860 else:

4861

4862 class default_select_compile_options(CacheableOptions):

4863 _cache_key_traversal = []

4864

4865 if TYPE_CHECKING:

4866

4867 @classmethod

4868 def get_plugin_class(

4869 cls, statement: Executable

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

4871

4872 def __init__(

4873 self,

4874 statement: Select[Unpack[TupleAny]],

4875 compiler: SQLCompiler,

4876 **kw: Any,

4877 ):

4878 self.statement = statement

4879 self.from_clauses = statement._from_obj

4880

4881 for memoized_entities in statement._memoized_select_entities:

4882 self._setup_joins(

4883 memoized_entities._setup_joins, memoized_entities._raw_columns

4884 )

4885

4886 if statement._setup_joins:

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

4888

4889 self.froms = self._get_froms(statement)

4890

4891 self.columns_plus_names = statement._generate_columns_plus_names(True)

4892

4893 @classmethod

4894 def _plugin_not_implemented(cls) -> NoReturn:

4895 raise NotImplementedError(

4896 "The default SELECT construct without plugins does not "

4897 "implement this method."

4898 )

4899

4900 @classmethod

4901 def get_column_descriptions(

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

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

4904 return [

4905 {

4906 "name": name,

4907 "type": element.type,

4908 "expr": element,

4909 }

4910 for _, name, _, element, _ in (

4911 statement._generate_columns_plus_names(False)

4912 )

4913 ]

4914

4915 @classmethod

4916 def from_statement(

4917 cls,

4918 statement: Select[Unpack[TupleAny]],

4919 from_statement: roles.ReturnsRowsRole,

4920 ) -> ExecutableReturnsRows:

4921 cls._plugin_not_implemented()

4922

4923 @classmethod

4924 def get_columns_clause_froms(

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

4926 ) -> List[FromClause]:

4927 return cls._normalize_froms(

4928 itertools.chain.from_iterable(

4929 element._from_objects for element in statement._raw_columns

4930 )

4931 )

4932

4933 @classmethod

4934 def _column_naming_convention(

4935 cls, label_style: SelectLabelStyle

4936 ) -> _LabelConventionCallable:

4937 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4938

4939 dedupe = label_style is not LABEL_STYLE_NONE

4940

4941 pa = prefix_anon_map()

4942 names = set()

4943

4944 def go(

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

4946 col_name: Optional[str] = None,

4947 ) -> Optional[str]:

4948 if is_text_clause(c):

4949 return None

4950 elif TYPE_CHECKING:

4951 assert is_column_element(c)

4952

4953 if not dedupe:

4954 name = c._proxy_key

4955 if name is None:

4956 name = "_no_label"

4957 return name

4958

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

4960

4961 if name is None:

4962 name = "_no_label"

4963 if name in names:

4964 return c._anon_label(name) % pa

4965 else:

4966 names.add(name)

4967 return name

4968

4969 elif name in names:

4970 return (

4971 c._anon_tq_key_label % pa

4972 if table_qualified

4973 else c._anon_key_label % pa

4974 )

4975 else:

4976 names.add(name)

4977 return name

4978

4979 return go

4980

4981 def _get_froms(

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

4983 ) -> List[FromClause]:

4984 ambiguous_table_name_map: _AmbiguousTableNameMap

4985 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4986

4987 return self._normalize_froms(

4988 itertools.chain(

4989 self.from_clauses,

4990 itertools.chain.from_iterable(

4991 [

4992 element._from_objects

4993 for element in statement._raw_columns

4994 ]

4995 ),

4996 itertools.chain.from_iterable(

4997 [

4998 element._from_objects

4999 for element in statement._where_criteria

5000 ]

5001 ),

5002 ),

5003 check_statement=statement,

5004 ambiguous_table_name_map=ambiguous_table_name_map,

5005 )

5006

5007 @classmethod

5008 def _normalize_froms(

5009 cls,

5010 iterable_of_froms: Iterable[FromClause],

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

5012 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

5013 ) -> List[FromClause]:

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

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

5016

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

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

5019 etc.

5020

5021 """

5022 seen: Set[FromClause] = set()

5023 froms: List[FromClause] = []

5024

5025 for item in iterable_of_froms:

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

5027 raise exc.InvalidRequestError(

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

5029 )

5030

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

5032 froms.append(item)

5033 seen.update(item._cloned_set)

5034

5035 if froms:

5036 toremove = set(

5037 itertools.chain.from_iterable(

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

5039 )

5040 )

5041 if toremove:

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

5043 # using a list to maintain ordering

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

5045

5046 if ambiguous_table_name_map is not None:

5047 ambiguous_table_name_map.update(

5048 (

5049 fr.name,

5050 _anonymous_label.safe_construct(

5051 hash(fr.name), fr.name

5052 ),

5053 )

5054 for item in froms

5055 for fr in item._from_objects

5056 if is_table(fr)

5057 and fr.schema

5058 and fr.name not in ambiguous_table_name_map

5059 )

5060

5061 return froms

5062

5063 def _get_display_froms(

5064 self,

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

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

5067 ) -> List[FromClause]:

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

5069

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

5071 rendered in the FROM clause of enclosing selects; this Select

5072 may want to leave those absent if it is automatically

5073 correlating.

5074

5075 """

5076

5077 froms = self.froms

5078

5079 if self.statement._correlate:

5080 to_correlate = self.statement._correlate

5081 if to_correlate:

5082 froms = [

5083 f

5084 for f in froms

5085 if f

5086 not in _cloned_intersection(

5087 _cloned_intersection(

5088 froms, explicit_correlate_froms or ()

5089 ),

5090 to_correlate,

5091 )

5092 ]

5093

5094 if self.statement._correlate_except is not None:

5095 froms = [

5096 f

5097 for f in froms

5098 if f

5099 not in _cloned_difference(

5100 _cloned_intersection(

5101 froms, explicit_correlate_froms or ()

5102 ),

5103 self.statement._correlate_except,

5104 )

5105 ]

5106

5107 if (

5108 self.statement._auto_correlate

5109 and implicit_correlate_froms

5110 and len(froms) > 1

5111 ):

5112 froms = [

5113 f

5114 for f in froms

5115 if f

5116 not in _cloned_intersection(froms, implicit_correlate_froms)

5117 ]

5118

5119 if not len(froms):

5120 raise exc.InvalidRequestError(

5121 "Select statement '%r"

5122 "' returned no FROM clauses "

5123 "due to auto-correlation; "

5124 "specify correlate(<tables>) "

5125 "to control correlation "

5126 "manually." % self.statement

5127 )

5128

5129 return froms

5130

5131 def _memoized_attr__label_resolve_dict(

5132 self,

5133 ) -> Tuple[

5134 Dict[str, ColumnElement[Any]],

5135 Dict[str, ColumnElement[Any]],

5136 Dict[str, ColumnElement[Any]],

5137 ]:

5138 with_cols: Dict[str, ColumnElement[Any]] = {

5139 c._tq_label or c.key: c

5140 for c in self.statement._all_selected_columns

5141 if c._allow_label_resolve

5142 }

5143 only_froms: Dict[str, ColumnElement[Any]] = {

5144 c.key: c # type: ignore

5145 for c in _select_iterables(self.froms)

5146 if c._allow_label_resolve

5147 }

5148 only_cols: Dict[str, ColumnElement[Any]] = with_cols.copy()

5149 for key, value in only_froms.items():

5150 with_cols.setdefault(key, value)

5151

5152 return with_cols, only_froms, only_cols

5153

5154 @classmethod

5155 def _get_filter_by_entities(

5156 cls, statement: Select[Unpack[TupleAny]]

5157 ) -> Collection[

5158 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5159 ]:

5160 """Return all entities to search for filter_by() attributes.

5161

5162 This includes:

5163

5164 * All joined entities from _setup_joins

5165 * Memoized entities from previous operations (e.g.,

5166 before with_only_columns)

5167 * Explicit FROM objects from _from_obj

5168 * Entities inferred from _raw_columns

5169

5170 .. versionadded:: 2.1

5171

5172 """

5173 entities: set[

5174 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5175 ]

5176

5177 entities = set(

5178 join_element[0] for join_element in statement._setup_joins

5179 )

5180

5181 for memoized in statement._memoized_select_entities:

5182 entities.update(

5183 join_element[0] for join_element in memoized._setup_joins

5184 )

5185

5186 entities.update(statement._from_obj)

5187

5188 for col in statement._raw_columns:

5189 entities.update(col._from_objects)

5190

5191 return entities

5192

5193 @classmethod

5194 def all_selected_columns(

5195 cls, statement: Select[Unpack[TupleAny]]

5196 ) -> _SelectIterable:

5197 return [c for c in _select_iterables(statement._raw_columns)]

5198

5199 def _setup_joins(

5200 self,

5201 args: Tuple[_SetupJoinsElement, ...],

5202 raw_columns: List[_ColumnsClauseElement],

5203 ) -> None:

5204 for right, onclause, left, flags in args:

5205 if TYPE_CHECKING:

5206 if onclause is not None:

5207 assert isinstance(onclause, ColumnElement)

5208

5209 explicit_left = left

5210 isouter = flags["isouter"]

5211 full = flags["full"]

5212

5213 if left is None:

5214 (

5215 left,

5216 replace_from_obj_index,

5217 ) = self._join_determine_implicit_left_side(

5218 raw_columns, left, right, onclause

5219 )

5220 else:

5221 (replace_from_obj_index) = self._join_place_explicit_left_side(

5222 left

5223 )

5224

5225 # these assertions can be made here, as if the right/onclause

5226 # contained ORM elements, the select() statement would have been

5227 # upgraded to an ORM select, and this method would not be called;

5228 # orm.context.ORMSelectCompileState._join() would be

5229 # used instead.

5230 if TYPE_CHECKING:

5231 assert isinstance(right, FromClause)

5232 if onclause is not None:

5233 assert isinstance(onclause, ColumnElement)

5234

5235 if replace_from_obj_index is not None:

5236 # splice into an existing element in the

5237 # self._from_obj list

5238 left_clause = self.from_clauses[replace_from_obj_index]

5239

5240 if explicit_left is not None and onclause is None:

5241 onclause = Join._join_condition(explicit_left, right)

5242

5243 self.from_clauses = (

5244 self.from_clauses[:replace_from_obj_index]

5245 + (

5246 Join(

5247 left_clause,

5248 right,

5249 onclause,

5250 isouter=isouter,

5251 full=full,

5252 ),

5253 )

5254 + self.from_clauses[replace_from_obj_index + 1 :]

5255 )

5256 else:

5257 assert left is not None

5258 self.from_clauses = self.from_clauses + (

5259 Join(left, right, onclause, isouter=isouter, full=full),

5260 )

5261

5262 @util.preload_module("sqlalchemy.sql.util")

5263 def _join_determine_implicit_left_side(

5264 self,

5265 raw_columns: List[_ColumnsClauseElement],

5266 left: Optional[FromClause],

5267 right: _JoinTargetElement,

5268 onclause: Optional[ColumnElement[Any]],

5269 ) -> Tuple[Optional[FromClause], Optional[int]]:

5270 """When join conditions don't express the left side explicitly,

5271 determine if an existing FROM or entity in this query

5272 can serve as the left hand side.

5273

5274 """

5275

5276 sql_util = util.preloaded.sql_util

5277

5278 replace_from_obj_index: Optional[int] = None

5279

5280 from_clauses = self.from_clauses

5281

5282 if from_clauses:

5283 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5284 from_clauses, right, onclause

5285 )

5286

5287 if len(indexes) == 1:

5288 replace_from_obj_index = indexes[0]

5289 left = from_clauses[replace_from_obj_index]

5290 else:

5291 potential = {}

5292 statement = self.statement

5293

5294 for from_clause in itertools.chain(

5295 itertools.chain.from_iterable(

5296 [element._from_objects for element in raw_columns]

5297 ),

5298 itertools.chain.from_iterable(

5299 [

5300 element._from_objects

5301 for element in statement._where_criteria

5302 ]

5303 ),

5304 ):

5305 potential[from_clause] = ()

5306

5307 all_clauses = list(potential.keys())

5308 indexes = sql_util.find_left_clause_to_join_from(

5309 all_clauses, right, onclause

5310 )

5311

5312 if len(indexes) == 1:

5313 left = all_clauses[indexes[0]]

5314

5315 if len(indexes) > 1:

5316 raise exc.InvalidRequestError(

5317 "Can't determine which FROM clause to join "

5318 "from, there are multiple FROMS which can "

5319 "join to this entity. Please use the .select_from() "

5320 "method to establish an explicit left side, as well as "

5321 "providing an explicit ON clause if not present already to "

5322 "help resolve the ambiguity."

5323 )

5324 elif not indexes:

5325 raise exc.InvalidRequestError(

5326 "Don't know how to join to %r. "

5327 "Please use the .select_from() "

5328 "method to establish an explicit left side, as well as "

5329 "providing an explicit ON clause if not present already to "

5330 "help resolve the ambiguity." % (right,)

5331 )

5332 return left, replace_from_obj_index

5333

5334 @util.preload_module("sqlalchemy.sql.util")

5335 def _join_place_explicit_left_side(

5336 self, left: FromClause

5337 ) -> Optional[int]:

5338 replace_from_obj_index: Optional[int] = None

5339

5340 sql_util = util.preloaded.sql_util

5341

5342 from_clauses = list(self.statement._iterate_from_elements())

5343

5344 if from_clauses:

5345 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5346 self.from_clauses, left

5347 )

5348 else:

5349 indexes = []

5350

5351 if len(indexes) > 1:

5352 raise exc.InvalidRequestError(

5353 "Can't identify which entity in which to assign the "

5354 "left side of this join. Please use a more specific "

5355 "ON clause."

5356 )

5357

5358 # have an index, means the left side is already present in

5359 # an existing FROM in the self._from_obj tuple

5360 if indexes:

5361 replace_from_obj_index = indexes[0]

5362

5363 # no index, means we need to add a new element to the

5364 # self._from_obj tuple

5365

5366 return replace_from_obj_index

5367

5368

5369class _SelectFromElements:

5370 __slots__ = ()

5371

5372 _raw_columns: List[_ColumnsClauseElement]

5373 _where_criteria: Tuple[ColumnElement[Any], ...]

5374 _from_obj: Tuple[FromClause, ...]

5375

5376 def _iterate_from_elements(self) -> Iterator[FromClause]:

5377 # note this does not include elements

5378 # in _setup_joins

5379

5380 seen = set()

5381 for element in self._raw_columns:

5382 for fr in element._from_objects:

5383 if fr in seen:

5384 continue

5385 seen.add(fr)

5386 yield fr

5387 for element in self._where_criteria:

5388 for fr in element._from_objects:

5389 if fr in seen:

5390 continue

5391 seen.add(fr)

5392 yield fr

5393 for element in self._from_obj:

5394 if element in seen:

5395 continue

5396 seen.add(element)

5397 yield element

5398

5399

5400class _MemoizedSelectEntities(

5401 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5402):

5403 """represents partial state from a Select object, for the case

5404 where Select.columns() has redefined the set of columns/entities the

5405 statement will be SELECTing from. This object represents

5406 the entities from the SELECT before that transformation was applied,

5407 so that transformations that were made in terms of the SELECT at that

5408 time, such as join() as well as options(), can access the correct context.

5409

5410 In previous SQLAlchemy versions, this wasn't needed because these

5411 constructs calculated everything up front, like when you called join()

5412 or options(), it did everything to figure out how that would translate

5413 into specific SQL constructs that would be ready to send directly to the

5414 SQL compiler when needed. But as of

5415 1.4, all of that stuff is done in the compilation phase, during the

5416 "compile state" portion of the process, so that the work can all be

5417 cached. So it needs to be able to resolve joins/options2 based on what

5418 the list of entities was when those methods were called.

5419

5420

5421 """

5422

5423 __visit_name__ = "memoized_select_entities"

5424

5425 _traverse_internals: _TraverseInternalsType = [

5426 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5427 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5428 ("_with_options", InternalTraversal.dp_executable_options),

5429 ]

5430

5431 _is_clone_of: Optional[ClauseElement]

5432 _raw_columns: List[_ColumnsClauseElement]

5433 _setup_joins: Tuple[_SetupJoinsElement, ...]

5434 _with_options: Tuple[ExecutableOption, ...]

5435

5436 _annotations = util.EMPTY_DICT

5437

5438 def _clone(self, **kw: Any) -> Self:

5439 c = self.__class__.__new__(self.__class__)

5440 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5441

5442 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5443 return c

5444

5445 @classmethod

5446 def _generate_for_statement(

5447 cls, select_stmt: Select[Unpack[TupleAny]]

5448 ) -> None:

5449 if select_stmt._setup_joins or select_stmt._with_options:

5450 self = _MemoizedSelectEntities()

5451 self._raw_columns = select_stmt._raw_columns

5452 self._setup_joins = select_stmt._setup_joins

5453 self._with_options = select_stmt._with_options

5454

5455 select_stmt._memoized_select_entities += (self,)

5456 select_stmt._raw_columns = []

5457 select_stmt._setup_joins = select_stmt._with_options = ()

5458

5459

5460class Select(

5461 HasPrefixes,

5462 HasSuffixes,

5463 HasHints,

5464 HasCompileState,

5465 HasSyntaxExtensions[

5466 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5467 ],

5468 _SelectFromElements,

5469 GenerativeSelect,

5470 TypedReturnsRows[Unpack[_Ts]],

5471):

5472 """Represents a ``SELECT`` statement.

5473

5474 The :class:`_sql.Select` object is normally constructed using the

5475 :func:`_sql.select` function. See that function for details.

5476

5477 Available extension points:

5478

5479 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5480 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5481 keyword (if any) and the list of columns.

5482 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5483 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5484

5485 .. seealso::

5486

5487 :func:`_sql.select`

5488

5489 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5490

5491 """

5492

5493 __visit_name__ = "select"

5494

5495 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5496 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5497

5498 _raw_columns: List[_ColumnsClauseElement]

5499

5500 _distinct: bool = False

5501 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5502 _correlate: Tuple[FromClause, ...] = ()

5503 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5504 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5505 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5506 _from_obj: Tuple[FromClause, ...] = ()

5507

5508 _position_map = util.immutabledict(

5509 {

5510 "post_select": "_post_select_clause",

5511 "pre_columns": "_pre_columns_clause",

5512 "post_criteria": "_post_criteria_clause",

5513 "post_body": "_post_body_clause",

5514 }

5515 )

5516

5517 _post_select_clause: Optional[ClauseElement] = None

5518 """extension point for a ClauseElement that will be compiled directly

5519 after the SELECT keyword.

5520

5521 .. versionadded:: 2.1

5522

5523 """

5524

5525 _pre_columns_clause: Optional[ClauseElement] = None

5526 """extension point for a ClauseElement that will be compiled directly

5527 before the "columns" clause; after DISTINCT (if present).

5528

5529 .. versionadded:: 2.1

5530

5531 """

5532

5533 _post_criteria_clause: Optional[ClauseElement] = None

5534 """extension point for a ClauseElement that will be compiled directly

5535 after "criteria", following the HAVING clause but before ORDER BY.

5536

5537 .. versionadded:: 2.1

5538

5539 """

5540

5541 _post_body_clause: Optional[ClauseElement] = None

5542 """extension point for a ClauseElement that will be compiled directly

5543 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5544 of the SELECT.

5545

5546 .. versionadded:: 2.1

5547

5548 """

5549

5550 _auto_correlate = True

5551 _is_select_statement = True

5552 _compile_options: CacheableOptions = (

5553 SelectState.default_select_compile_options

5554 )

5555

5556 _traverse_internals: _TraverseInternalsType = (

5557 [

5558 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5559 (

5560 "_memoized_select_entities",

5561 InternalTraversal.dp_memoized_select_entities,

5562 ),

5563 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5564 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5565 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5566 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5567 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5568 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5569 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5570 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5571 ("_limit_clause", InternalTraversal.dp_clauseelement),

5572 ("_offset_clause", InternalTraversal.dp_clauseelement),

5573 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5574 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5575 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5576 ("_distinct", InternalTraversal.dp_boolean),

5577 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5578 ("_label_style", InternalTraversal.dp_plain_obj),

5579 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5580 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5581 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5582 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5583 ]

5584 + HasCTE._has_ctes_traverse_internals

5585 + HasPrefixes._has_prefixes_traverse_internals

5586 + HasSuffixes._has_suffixes_traverse_internals

5587 + HasHints._has_hints_traverse_internals

5588 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5589 + ExecutableStatement._executable_traverse_internals

5590 + DialectKWArgs._dialect_kwargs_traverse_internals

5591 )

5592

5593 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5594 ("_compile_options", InternalTraversal.dp_has_cache_key)

5595 ]

5596

5597 _compile_state_factory: Type[SelectState]

5598

5599 @classmethod

5600 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5601 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5602

5603 Used internally to build up :class:`.Select` constructs with

5604 pre-established state.

5605

5606 """

5607

5608 stmt = Select.__new__(Select)

5609 stmt.__dict__.update(kw)

5610 return stmt

5611

5612 def __init__(

5613 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5614 ):

5615 r"""Construct a new :class:`_expression.Select`.

5616

5617 The public constructor for :class:`_expression.Select` is the

5618 :func:`_sql.select` function.

5619

5620 """

5621 self._raw_columns = [

5622 coercions.expect(

5623 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5624 )

5625 for ent in entities

5626 ]

5627 GenerativeSelect.__init__(self)

5628

5629 def _apply_syntax_extension_to_self(

5630 self, extension: SyntaxExtension

5631 ) -> None:

5632 extension.apply_to_select(self)

5633

5634 def _scalar_type(self) -> TypeEngine[Any]:

5635 if not self._raw_columns:

5636 return NULLTYPE

5637 elem = self._raw_columns[0]

5638 cols = list(elem._select_iterable)

5639 return cols[0].type

5640

5641 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5642 """A synonym for the :meth:`_sql.Select.where` method."""

5643

5644 return self.where(*criteria)

5645

5646 if TYPE_CHECKING:

5647

5648 @overload

5649 def scalar_subquery(

5650 self: Select[_MAYBE_ENTITY],

5651 ) -> ScalarSelect[Any]: ...

5652

5653 @overload

5654 def scalar_subquery(

5655 self: Select[_NOT_ENTITY],

5656 ) -> ScalarSelect[_NOT_ENTITY]: ...

5657

5658 @overload

5659 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5660

5661 def scalar_subquery(self) -> ScalarSelect[Any]: ...

5662

5663 def filter_by(self, **kwargs: Any) -> Self:

5664 r"""Apply the given filtering criterion as a WHERE clause

5665 to this select, using keyword expressions.

5666

5667 E.g.::

5668

5669 stmt = select(User).filter_by(name="some name")

5670

5671 Multiple criteria may be specified as comma separated; the effect

5672 is that they will be joined together using the :func:`.and_`

5673 function::

5674

5675 stmt = select(User).filter_by(name="some name", id=5)

5676

5677 The keyword expressions are extracted by searching across **all

5678 entities present in the FROM clause** of the statement. If a

5679 keyword name is present in more than one entity,

5680 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5681 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5682 explicit column references::

5683

5684 # both User and Address have an 'id' attribute

5685 stmt = select(User).join(Address).filter_by(id=5)

5686 # raises AmbiguousColumnError

5687

5688 # use filter() with explicit qualification instead

5689 stmt = select(User).join(Address).filter(Address.id == 5)

5690

5691 .. versionchanged:: 2.1

5692

5693 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5694 entities rather than only searching the last joined entity or first

5695 FROM entity. This allows the method to locate attributes

5696 unambiguously across multiple joined tables. The new

5697 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5698 is present in more than one entity.

5699

5700 See :ref:`change_8601` for migration notes.

5701

5702 .. seealso::

5703

5704 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

5705

5706 :meth:`_sql.Select.filter` - filter on SQL expressions.

5707

5708 :meth:`_sql.Select.where` - filter on SQL expressions.

5709

5710 """

5711 # Get all entities via plugin system

5712 all_entities = SelectState.get_plugin_class(

5713 self

5714 )._get_filter_by_entities(self)

5715

5716 clauses = [

5717 _entity_namespace_key_search_all(all_entities, key) == value

5718 for key, value in kwargs.items()

5719 ]

5720 return self.filter(*clauses)

5721

5722 @property

5723 def column_descriptions(self) -> Any:

5724 """Return a :term:`plugin-enabled` 'column descriptions' structure

5725 referring to the columns which are SELECTed by this statement.

5726

5727 This attribute is generally useful when using the ORM, as an

5728 extended structure which includes information about mapped

5729 entities is returned. The section :ref:`queryguide_inspection`

5730 contains more background.

5731

5732 For a Core-only statement, the structure returned by this accessor

5733 is derived from the same objects that are returned by the

5734 :attr:`.Select.selected_columns` accessor, formatted as a list of

5735 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5736 which indicate the column expressions to be selected::

5737

5738 >>> stmt = select(user_table)

5739 >>> stmt.column_descriptions

5740 [

5741 {

5742 'name': 'id',

5743 'type': Integer(),

5744 'expr': Column('id', Integer(), ...)},

5745 {

5746 'name': 'name',

5747 'type': String(length=30),

5748 'expr': Column('name', String(length=30), ...)}

5749 ]

5750

5751 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5752 attribute returns a structure for a Core-only set of entities,

5753 not just ORM-only entities.

5754

5755 .. seealso::

5756

5757 :attr:`.UpdateBase.entity_description` - entity information for

5758 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5759

5760 :ref:`queryguide_inspection` - ORM background

5761

5762 """

5763 meth = SelectState.get_plugin_class(self).get_column_descriptions

5764 return meth(self)

5765

5766 def from_statement(

5767 self, statement: roles.ReturnsRowsRole

5768 ) -> ExecutableReturnsRows:

5769 """Apply the columns which this :class:`.Select` would select

5770 onto another statement.

5771

5772 This operation is :term:`plugin-specific` and will raise a not

5773 supported exception if this :class:`_sql.Select` does not select from

5774 plugin-enabled entities.

5775

5776

5777 The statement is typically either a :func:`_expression.text` or

5778 :func:`_expression.select` construct, and should return the set of

5779 columns appropriate to the entities represented by this

5780 :class:`.Select`.

5781

5782 .. seealso::

5783

5784 :ref:`orm_queryguide_selecting_text` - usage examples in the

5785 ORM Querying Guide

5786

5787 """

5788 meth = SelectState.get_plugin_class(self).from_statement

5789 return meth(self, statement)

5790

5791 @_generative

5792 def join(

5793 self,

5794 target: _JoinTargetArgument,

5795 onclause: Optional[_OnClauseArgument] = None,

5796 *,

5797 isouter: bool = False,

5798 full: bool = False,

5799 ) -> Self:

5800 r"""Create a SQL JOIN against this :class:`_expression.Select`

5801 object's criterion

5802 and apply generatively, returning the newly resulting

5803 :class:`_expression.Select`.

5804

5805 E.g.::

5806

5807 stmt = select(user_table).join(

5808 address_table, user_table.c.id == address_table.c.user_id

5809 )

5810

5811 The above statement generates SQL similar to:

5812

5813 .. sourcecode:: sql

5814

5815 SELECT user.id, user.name

5816 FROM user

5817 JOIN address ON user.id = address.user_id

5818

5819 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5820 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5821 source that is within the FROM clause of the existing SELECT,

5822 and a given target :class:`_sql.FromClause`, and then adds

5823 this :class:`_sql.Join` to the FROM clause of the newly generated

5824 SELECT statement. This is completely reworked from the behavior

5825 in 1.3, which would instead create a subquery of the entire

5826 :class:`_expression.Select` and then join that subquery to the

5827 target.

5828

5829 This is a **backwards incompatible change** as the previous behavior

5830 was mostly useless, producing an unnamed subquery rejected by

5831 most databases in any case. The new behavior is modeled after

5832 that of the very successful :meth:`_orm.Query.join` method in the

5833 ORM, in order to support the functionality of :class:`_orm.Query`

5834 being available by using a :class:`_sql.Select` object with an

5835 :class:`_orm.Session`.

5836

5837 See the notes for this change at :ref:`change_select_join`.

5838

5839

5840 :param target: target table to join towards

5841

5842 :param onclause: ON clause of the join. If omitted, an ON clause

5843 is generated automatically based on the :class:`_schema.ForeignKey`

5844 linkages between the two tables, if one can be unambiguously

5845 determined, otherwise an error is raised.

5846

5847 :param isouter: if True, generate LEFT OUTER join. Same as

5848 :meth:`_expression.Select.outerjoin`.

5849

5850 :param full: if True, generate FULL OUTER join.

5851

5852 .. seealso::

5853

5854 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5855

5856 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5857

5858 :meth:`_expression.Select.join_from`

5859

5860 :meth:`_expression.Select.outerjoin`

5861

5862 """ # noqa: E501

5863 join_target = coercions.expect(

5864 roles.JoinTargetRole, target, apply_propagate_attrs=self

5865 )

5866 if onclause is not None:

5867 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5868 else:

5869 onclause_element = None

5870

5871 self._setup_joins += (

5872 (

5873 join_target,

5874 onclause_element,

5875 None,

5876 {"isouter": isouter, "full": full},

5877 ),

5878 )

5879 return self

5880

5881 def outerjoin_from(

5882 self,

5883 from_: _FromClauseArgument,

5884 target: _JoinTargetArgument,

5885 onclause: Optional[_OnClauseArgument] = None,

5886 *,

5887 full: bool = False,

5888 ) -> Self:

5889 r"""Create a SQL LEFT OUTER JOIN against this

5890 :class:`_expression.Select` object's criterion and apply generatively,

5891 returning the newly resulting :class:`_expression.Select`.

5892

5893 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5894

5895 """

5896 return self.join_from(

5897 from_, target, onclause=onclause, isouter=True, full=full

5898 )

5899

5900 @_generative

5901 def join_from(

5902 self,

5903 from_: _FromClauseArgument,

5904 target: _JoinTargetArgument,

5905 onclause: Optional[_OnClauseArgument] = None,

5906 *,

5907 isouter: bool = False,

5908 full: bool = False,

5909 ) -> Self:

5910 r"""Create a SQL JOIN against this :class:`_expression.Select`

5911 object's criterion

5912 and apply generatively, returning the newly resulting

5913 :class:`_expression.Select`.

5914

5915 E.g.::

5916

5917 stmt = select(user_table, address_table).join_from(

5918 user_table, address_table, user_table.c.id == address_table.c.user_id

5919 )

5920

5921 The above statement generates SQL similar to:

5922

5923 .. sourcecode:: sql

5924

5925 SELECT user.id, user.name, address.id, address.email, address.user_id

5926 FROM user JOIN address ON user.id = address.user_id

5927

5928 .. versionadded:: 1.4

5929

5930 :param from\_: the left side of the join, will be rendered in the

5931 FROM clause and is roughly equivalent to using the

5932 :meth:`.Select.select_from` method.

5933

5934 :param target: target table to join towards

5935

5936 :param onclause: ON clause of the join.

5937

5938 :param isouter: if True, generate LEFT OUTER join. Same as

5939 :meth:`_expression.Select.outerjoin`.

5940

5941 :param full: if True, generate FULL OUTER join.

5942

5943 .. seealso::

5944

5945 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5946

5947 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5948

5949 :meth:`_expression.Select.join`

5950

5951 """ # noqa: E501

5952

5953 # note the order of parsing from vs. target is important here, as we

5954 # are also deriving the source of the plugin (i.e. the subject mapper

5955 # in an ORM query) which should favor the "from_" over the "target"

5956

5957 from_ = coercions.expect(

5958 roles.FromClauseRole, from_, apply_propagate_attrs=self

5959 )

5960 join_target = coercions.expect(

5961 roles.JoinTargetRole, target, apply_propagate_attrs=self

5962 )

5963 if onclause is not None:

5964 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5965 else:

5966 onclause_element = None

5967

5968 self._setup_joins += (

5969 (

5970 join_target,

5971 onclause_element,

5972 from_,

5973 {"isouter": isouter, "full": full},

5974 ),

5975 )

5976 return self

5977

5978 def outerjoin(

5979 self,

5980 target: _JoinTargetArgument,

5981 onclause: Optional[_OnClauseArgument] = None,

5982 *,

5983 full: bool = False,

5984 ) -> Self:

5985 """Create a left outer join.

5986

5987 Parameters are the same as that of :meth:`_expression.Select.join`.

5988

5989 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5990 creates a :class:`_sql.Join` object between a

5991 :class:`_sql.FromClause` source that is within the FROM clause of

5992 the existing SELECT, and a given target :class:`_sql.FromClause`,

5993 and then adds this :class:`_sql.Join` to the FROM clause of the

5994 newly generated SELECT statement. This is completely reworked

5995 from the behavior in 1.3, which would instead create a subquery of

5996 the entire

5997 :class:`_expression.Select` and then join that subquery to the

5998 target.

5999

6000 This is a **backwards incompatible change** as the previous behavior

6001 was mostly useless, producing an unnamed subquery rejected by

6002 most databases in any case. The new behavior is modeled after

6003 that of the very successful :meth:`_orm.Query.join` method in the

6004 ORM, in order to support the functionality of :class:`_orm.Query`

6005 being available by using a :class:`_sql.Select` object with an

6006 :class:`_orm.Session`.

6007

6008 See the notes for this change at :ref:`change_select_join`.

6009

6010 .. seealso::

6011

6012 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

6013

6014 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

6015

6016 :meth:`_expression.Select.join`

6017

6018 """

6019 return self.join(target, onclause=onclause, isouter=True, full=full)

6020

6021 def get_final_froms(self) -> Sequence[FromClause]:

6022 """Compute the final displayed list of :class:`_expression.FromClause`

6023 elements.

6024

6025 This method will run through the full computation required to

6026 determine what FROM elements will be displayed in the resulting

6027 SELECT statement, including shadowing individual tables with

6028 JOIN objects, as well as full computation for ORM use cases including

6029 eager loading clauses.

6030

6031 For ORM use, this accessor returns the **post compilation**

6032 list of FROM objects; this collection will include elements such as

6033 eagerly loaded tables and joins. The objects will **not** be

6034 ORM enabled and not work as a replacement for the

6035 :meth:`_sql.Select.select_froms` collection; additionally, the

6036 method is not well performing for an ORM enabled statement as it

6037 will incur the full ORM construction process.

6038

6039 To retrieve the FROM list that's implied by the "columns" collection

6040 passed to the :class:`_sql.Select` originally, use the

6041 :attr:`_sql.Select.columns_clause_froms` accessor.

6042

6043 To select from an alternative set of columns while maintaining the

6044 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

6045 pass the

6046 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6047 parameter.

6048

6049 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

6050 method replaces the previous :attr:`_sql.Select.froms` accessor,

6051 which is deprecated.

6052

6053 .. seealso::

6054

6055 :attr:`_sql.Select.columns_clause_froms`

6056

6057 """

6058 compiler = self._default_compiler()

6059

6060 return self._compile_state_factory(self, compiler)._get_display_froms()

6061

6062 @property

6063 @util.deprecated(

6064 "1.4.23",

6065 "The :attr:`_expression.Select.froms` attribute is moved to "

6066 "the :meth:`_expression.Select.get_final_froms` method.",

6067 )

6068 def froms(self) -> Sequence[FromClause]:

6069 """Return the displayed list of :class:`_expression.FromClause`

6070 elements.

6071

6072

6073 """

6074 return self.get_final_froms()

6075

6076 @property

6077 def columns_clause_froms(self) -> List[FromClause]:

6078 """Return the set of :class:`_expression.FromClause` objects implied

6079 by the columns clause of this SELECT statement.

6080

6081 .. versionadded:: 1.4.23

6082

6083 .. seealso::

6084

6085 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6086 statement into account

6087

6088 :meth:`_sql.Select.with_only_columns` - makes use of this

6089 collection to set up a new FROM list

6090

6091 """

6092

6093 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6094 self

6095 )

6096

6097 @property

6098 def inner_columns(self) -> _SelectIterable:

6099 """An iterator of all :class:`_expression.ColumnElement`

6100 expressions which would

6101 be rendered into the columns clause of the resulting SELECT statement.

6102

6103 This method is legacy as of 1.4 and is superseded by the

6104 :attr:`_expression.Select.exported_columns` collection.

6105

6106 """

6107

6108 return iter(self._all_selected_columns)

6109

6110 def is_derived_from(self, fromclause: Optional[FromClause]) -> bool:

6111 if fromclause is not None and self in fromclause._cloned_set:

6112 return True

6113

6114 for f in self._iterate_from_elements():

6115 if f.is_derived_from(fromclause):

6116 return True

6117 return False

6118

6119 def _copy_internals(

6120 self, clone: _CloneCallableType = _clone, **kw: Any

6121 ) -> None:

6122 # Select() object has been cloned and probably adapted by the

6123 # given clone function. Apply the cloning function to internal

6124 # objects

6125

6126 # 1. keep a dictionary of the froms we've cloned, and what

6127 # they've become. This allows us to ensure the same cloned from

6128 # is used when other items such as columns are "cloned"

6129

6130 all_the_froms = set(

6131 itertools.chain(

6132 _from_objects(*self._raw_columns),

6133 _from_objects(*self._where_criteria),

6134 _from_objects(*[elem[0] for elem in self._setup_joins]),

6135 )

6136 )

6137

6138 # do a clone for the froms we've gathered. what is important here

6139 # is if any of the things we are selecting from, like tables,

6140 # were converted into Join objects. if so, these need to be

6141 # added to _from_obj explicitly, because otherwise they won't be

6142 # part of the new state, as they don't associate themselves with

6143 # their columns.

6144 new_froms = {f: clone(f, **kw) for f in all_the_froms}

6145

6146 # 2. copy FROM collections, adding in joins that we've created.

6147 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6148 add_froms = (

6149 {f for f in new_froms.values() if isinstance(f, Join)}

6150 .difference(all_the_froms)

6151 .difference(existing_from_obj)

6152 )

6153

6154 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6155

6156 # 3. clone everything else, making sure we use columns

6157 # corresponding to the froms we just made.

6158 def replace(

6159 obj: Union[BinaryExpression[Any], ColumnClause[Any]],

6160 **kw: Any,

6161 ) -> Optional[KeyedColumnElement[Any]]:

6162 if isinstance(obj, ColumnClause) and obj.table in new_froms:

6163 newelem = new_froms[obj.table].corresponding_column(obj)

6164 return newelem

6165 return None

6166

6167 kw["replace"] = replace

6168

6169 # copy everything else. for table-ish things like correlate,

6170 # correlate_except, setup_joins, these clone normally. For

6171 # column-expression oriented things like raw_columns, where_criteria,

6172 # order by, we get this from the new froms.

6173 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6174

6175 self._reset_memoizations()

6176

6177 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6178 return itertools.chain(

6179 super().get_children(

6180 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6181 **kw,

6182 ),

6183 self._iterate_from_elements(),

6184 )

6185

6186 @_generative

6187 def add_columns(

6188 self, *entities: _ColumnsClauseArgument[Any]

6189 ) -> Select[Unpack[TupleAny]]:

6190 r"""Return a new :func:`_expression.select` construct with

6191 the given entities appended to its columns clause.

6192

6193 E.g.::

6194

6195 my_select = my_select.add_columns(table.c.new_column)

6196

6197 The original expressions in the columns clause remain in place.

6198 To replace the original expressions with new ones, see the method

6199 :meth:`_expression.Select.with_only_columns`.

6200

6201 :param \*entities: column, table, or other entity expressions to be

6202 added to the columns clause

6203

6204 .. seealso::

6205

6206 :meth:`_expression.Select.with_only_columns` - replaces existing

6207 expressions rather than appending.

6208

6209 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6210 example

6211

6212 """

6213 self._reset_memoizations()

6214

6215 self._raw_columns = self._raw_columns + [

6216 coercions.expect(

6217 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6218 )

6219 for column in entities

6220 ]

6221 return self

6222

6223 def _set_entities(

6224 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6225 ) -> None:

6226 self._raw_columns = [

6227 coercions.expect(

6228 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6229 )

6230 for ent in util.to_list(entities)

6231 ]

6232

6233 @util.deprecated(

6234 "1.4",

6235 "The :meth:`_expression.Select.column` method is deprecated and will "

6236 "be removed in a future release. Please use "

6237 ":meth:`_expression.Select.add_columns`",

6238 )

6239 def column(

6240 self, column: _ColumnsClauseArgument[Any]

6241 ) -> Select[Unpack[TupleAny]]:

6242 """Return a new :func:`_expression.select` construct with

6243 the given column expression added to its columns clause.

6244

6245 E.g.::

6246

6247 my_select = my_select.column(table.c.new_column)

6248

6249 See the documentation for

6250 :meth:`_expression.Select.with_only_columns`

6251 for guidelines on adding /replacing the columns of a

6252 :class:`_expression.Select` object.

6253

6254 """

6255 return self.add_columns(column)

6256

6257 @util.preload_module("sqlalchemy.sql.util")

6258 def reduce_columns(

6259 self, only_synonyms: bool = True

6260 ) -> Select[Unpack[TupleAny]]:

6261 """Return a new :func:`_expression.select` construct with redundantly

6262 named, equivalently-valued columns removed from the columns clause.

6263

6264 "Redundant" here means two columns where one refers to the

6265 other either based on foreign key, or via a simple equality

6266 comparison in the WHERE clause of the statement. The primary purpose

6267 of this method is to automatically construct a select statement

6268 with all uniquely-named columns, without the need to use

6269 table-qualified labels as

6270 :meth:`_expression.Select.set_label_style`

6271 does.

6272

6273 When columns are omitted based on foreign key, the referred-to

6274 column is the one that's kept. When columns are omitted based on

6275 WHERE equivalence, the first column in the columns clause is the

6276 one that's kept.

6277

6278 :param only_synonyms: when True, limit the removal of columns

6279 to those which have the same name as the equivalent. Otherwise,

6280 all columns that are equivalent to another are removed.

6281

6282 """

6283 woc: Select[Unpack[TupleAny]]

6284 woc = self.with_only_columns(

6285 *util.preloaded.sql_util.reduce_columns(

6286 self._all_selected_columns,

6287 only_synonyms=only_synonyms,

6288 *(self._where_criteria + self._from_obj),

6289 )

6290 )

6291 return woc

6292

6293 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6294

6295 # code within this block is **programmatically,

6296 # statically generated** by tools/generate_tuple_map_overloads.py

6297

6298 @overload

6299 def with_only_columns(

6300 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6301 ) -> Select[_T0]: ...

6302

6303 @overload

6304 def with_only_columns(

6305 self,

6306 __ent0: _TCCA[_T0],

6307 __ent1: _TCCA[_T1],

6308 /,

6309 *,

6310 maintain_column_froms: bool = ...,

6311 ) -> Select[_T0, _T1]: ...

6312

6313 @overload

6314 def with_only_columns(

6315 self,

6316 __ent0: _TCCA[_T0],

6317 __ent1: _TCCA[_T1],

6318 __ent2: _TCCA[_T2],

6319 /,

6320 *,

6321 maintain_column_froms: bool = ...,

6322 ) -> Select[_T0, _T1, _T2]: ...

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 /,

6332 *,

6333 maintain_column_froms: bool = ...,

6334 ) -> Select[_T0, _T1, _T2, _T3]: ...

6335

6336 @overload

6337 def with_only_columns(

6338 self,

6339 __ent0: _TCCA[_T0],

6340 __ent1: _TCCA[_T1],

6341 __ent2: _TCCA[_T2],

6342 __ent3: _TCCA[_T3],

6343 __ent4: _TCCA[_T4],

6344 /,

6345 *,

6346 maintain_column_froms: bool = ...,

6347 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6348

6349 @overload

6350 def with_only_columns(

6351 self,

6352 __ent0: _TCCA[_T0],

6353 __ent1: _TCCA[_T1],

6354 __ent2: _TCCA[_T2],

6355 __ent3: _TCCA[_T3],

6356 __ent4: _TCCA[_T4],

6357 __ent5: _TCCA[_T5],

6358 /,

6359 *,

6360 maintain_column_froms: bool = ...,

6361 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6362

6363 @overload

6364 def with_only_columns(

6365 self,

6366 __ent0: _TCCA[_T0],

6367 __ent1: _TCCA[_T1],

6368 __ent2: _TCCA[_T2],

6369 __ent3: _TCCA[_T3],

6370 __ent4: _TCCA[_T4],

6371 __ent5: _TCCA[_T5],

6372 __ent6: _TCCA[_T6],

6373 /,

6374 *,

6375 maintain_column_froms: bool = ...,

6376 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6377

6378 @overload

6379 def with_only_columns(

6380 self,

6381 __ent0: _TCCA[_T0],

6382 __ent1: _TCCA[_T1],

6383 __ent2: _TCCA[_T2],

6384 __ent3: _TCCA[_T3],

6385 __ent4: _TCCA[_T4],

6386 __ent5: _TCCA[_T5],

6387 __ent6: _TCCA[_T6],

6388 __ent7: _TCCA[_T7],

6389 /,

6390 *entities: _ColumnsClauseArgument[Any],

6391 maintain_column_froms: bool = ...,

6392 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6393

6394 # END OVERLOADED FUNCTIONS self.with_only_columns

6395

6396 @overload

6397 def with_only_columns(

6398 self,

6399 *entities: _ColumnsClauseArgument[Any],

6400 maintain_column_froms: bool = False,

6401 **__kw: Any,

6402 ) -> Select[Unpack[TupleAny]]: ...

6403

6404 @_generative

6405 def with_only_columns(

6406 self,

6407 *entities: _ColumnsClauseArgument[Any],

6408 maintain_column_froms: bool = False,

6409 **__kw: Any,

6410 ) -> Select[Unpack[TupleAny]]:

6411 r"""Return a new :func:`_expression.select` construct with its columns

6412 clause replaced with the given entities.

6413

6414 By default, this method is exactly equivalent to as if the original

6415 :func:`_expression.select` had been called with the given entities.

6416 E.g. a statement::

6417

6418 s = select(table1.c.a, table1.c.b)

6419 s = s.with_only_columns(table1.c.b)

6420

6421 should be exactly equivalent to::

6422

6423 s = select(table1.c.b)

6424

6425 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6426 will also dynamically alter the FROM clause of the

6427 statement if it is not explicitly stated.

6428 To maintain the existing set of FROMs including those implied by the

6429 current columns clause, add the

6430 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6431 parameter::

6432

6433 s = select(table1.c.a, table2.c.b)

6434 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6435

6436 The above parameter performs a transfer of the effective FROMs

6437 in the columns collection to the :meth:`_sql.Select.select_from`

6438 method, as though the following were invoked::

6439

6440 s = select(table1.c.a, table2.c.b)

6441 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6442

6443 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6444 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6445 collection and performs an operation equivalent to the following::

6446

6447 s = select(table1.c.a, table2.c.b)

6448 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6449

6450 :param \*entities: column expressions to be used.

6451

6452 :param maintain_column_froms: boolean parameter that will ensure the

6453 FROM list implied from the current columns clause will be transferred

6454 to the :meth:`_sql.Select.select_from` method first.

6455

6456 .. versionadded:: 1.4.23

6457

6458 """ # noqa: E501

6459

6460 if __kw:

6461 raise _no_kw()

6462

6463 # memoizations should be cleared here as of

6464 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6465 # is the case for now.

6466 self._assert_no_memoizations()

6467

6468 if maintain_column_froms:

6469 self.select_from.non_generative( # type: ignore

6470 self, *self.columns_clause_froms

6471 )

6472

6473 # then memoize the FROMs etc.

6474 _MemoizedSelectEntities._generate_for_statement(self)

6475

6476 self._raw_columns = [

6477 coercions.expect(roles.ColumnsClauseRole, c)

6478 for c in coercions._expression_collection_was_a_list(

6479 "entities", "Select.with_only_columns", entities

6480 )

6481 ]

6482 return self

6483

6484 @property

6485 def whereclause(self) -> Optional[ColumnElement[Any]]:

6486 """Return the completed WHERE clause for this

6487 :class:`_expression.Select` statement.

6488

6489 This assembles the current collection of WHERE criteria

6490 into a single :class:`_expression.BooleanClauseList` construct.

6491

6492

6493 .. versionadded:: 1.4

6494

6495 """

6496

6497 return BooleanClauseList._construct_for_whereclause(

6498 self._where_criteria

6499 )

6500

6501 _whereclause = whereclause

6502

6503 @_generative

6504 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6505 """Return a new :func:`_expression.select` construct with

6506 the given expression added to

6507 its WHERE clause, joined to the existing clause via AND, if any.

6508

6509 """

6510

6511 assert isinstance(self._where_criteria, tuple)

6512

6513 for criterion in whereclause:

6514 where_criteria: ColumnElement[Any] = coercions.expect(

6515 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6516 )

6517 self._where_criteria += (where_criteria,)

6518 return self

6519

6520 @_generative

6521 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6522 """Return a new :func:`_expression.select` construct with

6523 the given expression added to

6524 its HAVING clause, joined to the existing clause via AND, if any.

6525

6526 """

6527

6528 for criterion in having:

6529 having_criteria = coercions.expect(

6530 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6531 )

6532 self._having_criteria += (having_criteria,)

6533 return self

6534

6535 @_generative

6536 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6537 r"""Return a new :func:`_expression.select` construct which

6538 will apply DISTINCT to the SELECT statement overall.

6539

6540 E.g.::

6541

6542 from sqlalchemy import select

6543

6544 stmt = select(users_table.c.id, users_table.c.name).distinct()

6545

6546 The above would produce an statement resembling:

6547

6548 .. sourcecode:: sql

6549

6550 SELECT DISTINCT user.id, user.name FROM user

6551

6552 The method also historically accepted an ``*expr`` parameter which

6553 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6554 This is now replaced using the :func:`_postgresql.distinct_on`

6555 extension::

6556

6557 from sqlalchemy import select

6558 from sqlalchemy.dialects.postgresql import distinct_on

6559

6560 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6561

6562 Using this parameter on other backends which don't support this

6563 syntax will raise an error.

6564

6565 :param \*expr: optional column expressions. When present,

6566 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6567 construct. A deprecation warning and/or :class:`_exc.CompileError`

6568 will be raised on other backends.

6569

6570 .. deprecated:: 2.1 Passing expressions to

6571 :meth:`_sql.Select.distinct` is deprecated, use

6572 :func:`_postgresql.distinct_on` instead.

6573

6574 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6575 and will raise :class:`_exc.CompileError` in a future version.

6576

6577 .. seealso::

6578

6579 :func:`_postgresql.distinct_on`

6580

6581 :meth:`.ext`

6582 """

6583 self._distinct = True

6584 if expr:

6585 warn_deprecated(

6586 "Passing expression to ``distinct`` to generate a "

6587 "DISTINCT ON clause is deprecated. Use instead the "

6588 "``postgresql.distinct_on`` function as an extension.",

6589 "2.1",

6590 )

6591 self._distinct_on = self._distinct_on + tuple(

6592 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6593 for e in expr

6594 )

6595 return self

6596

6597 @_generative

6598 def select_from(self, *froms: _FromClauseArgument) -> Self:

6599 r"""Return a new :func:`_expression.select` construct with the

6600 given FROM expression(s)

6601 merged into its list of FROM objects.

6602

6603 E.g.::

6604

6605 table1 = table("t1", column("a"))

6606 table2 = table("t2", column("b"))

6607 s = select(table1.c.a).select_from(

6608 table1.join(table2, table1.c.a == table2.c.b)

6609 )

6610

6611 The "from" list is a unique set on the identity of each element,

6612 so adding an already present :class:`_schema.Table`

6613 or other selectable

6614 will have no effect. Passing a :class:`_expression.Join` that refers

6615 to an already present :class:`_schema.Table`

6616 or other selectable will have

6617 the effect of concealing the presence of that selectable as

6618 an individual element in the rendered FROM list, instead

6619 rendering it into a JOIN clause.

6620

6621 While the typical purpose of :meth:`_expression.Select.select_from`

6622 is to

6623 replace the default, derived FROM clause with a join, it can

6624 also be called with individual table elements, multiple times

6625 if desired, in the case that the FROM clause cannot be fully

6626 derived from the columns clause::

6627

6628 select(func.count("*")).select_from(table1)

6629

6630 """

6631

6632 self._from_obj += tuple(

6633 coercions.expect(

6634 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6635 )

6636 for fromclause in froms

6637 )

6638 return self

6639

6640 @_generative

6641 def correlate(

6642 self,

6643 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6644 ) -> Self:

6645 r"""Return a new :class:`_expression.Select`

6646 which will correlate the given FROM

6647 clauses to that of an enclosing :class:`_expression.Select`.

6648

6649 Calling this method turns off the :class:`_expression.Select` object's

6650 default behavior of "auto-correlation". Normally, FROM elements

6651 which appear in a :class:`_expression.Select`

6652 that encloses this one via

6653 its :term:`WHERE clause`, ORDER BY, HAVING or

6654 :term:`columns clause` will be omitted from this

6655 :class:`_expression.Select`

6656 object's :term:`FROM clause`.

6657 Setting an explicit correlation collection using the

6658 :meth:`_expression.Select.correlate`

6659 method provides a fixed list of FROM objects

6660 that can potentially take place in this process.

6661

6662 When :meth:`_expression.Select.correlate`

6663 is used to apply specific FROM clauses

6664 for correlation, the FROM elements become candidates for

6665 correlation regardless of how deeply nested this

6666 :class:`_expression.Select`

6667 object is, relative to an enclosing :class:`_expression.Select`

6668 which refers to

6669 the same FROM object. This is in contrast to the behavior of

6670 "auto-correlation" which only correlates to an immediate enclosing

6671 :class:`_expression.Select`.

6672 Multi-level correlation ensures that the link

6673 between enclosed and enclosing :class:`_expression.Select`

6674 is always via

6675 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6676 correlation to take place.

6677

6678 If ``None`` is passed, the :class:`_expression.Select`

6679 object will correlate

6680 none of its FROM entries, and all will render unconditionally

6681 in the local FROM clause.

6682

6683 :param \*fromclauses: one or more :class:`.FromClause` or other

6684 FROM-compatible construct such as an ORM mapped entity to become part

6685 of the correlate collection; alternatively pass a single value

6686 ``None`` to remove all existing correlations.

6687

6688 .. seealso::

6689

6690 :meth:`_expression.Select.correlate_except`

6691

6692 :ref:`tutorial_scalar_subquery`

6693

6694 """

6695

6696 # tests failing when we try to change how these

6697 # arguments are passed

6698

6699 self._auto_correlate = False

6700 if not fromclauses or fromclauses[0] in {None, False}:

6701 if len(fromclauses) > 1:

6702 raise exc.ArgumentError(

6703 "additional FROM objects not accepted when "

6704 "passing None/False to correlate()"

6705 )

6706 self._correlate = ()

6707 else:

6708 self._correlate = self._correlate + tuple(

6709 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6710 )

6711 return self

6712

6713 @_generative

6714 def correlate_except(

6715 self,

6716 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6717 ) -> Self:

6718 r"""Return a new :class:`_expression.Select`

6719 which will omit the given FROM

6720 clauses from the auto-correlation process.

6721

6722 Calling :meth:`_expression.Select.correlate_except` turns off the

6723 :class:`_expression.Select` object's default behavior of

6724 "auto-correlation" for the given FROM elements. An element

6725 specified here will unconditionally appear in the FROM list, while

6726 all other FROM elements remain subject to normal auto-correlation

6727 behaviors.

6728

6729 If ``None`` is passed, or no arguments are passed,

6730 the :class:`_expression.Select` object will correlate all of its

6731 FROM entries.

6732

6733 :param \*fromclauses: a list of one or more

6734 :class:`_expression.FromClause`

6735 constructs, or other compatible constructs (i.e. ORM-mapped

6736 classes) to become part of the correlate-exception collection.

6737

6738 .. seealso::

6739

6740 :meth:`_expression.Select.correlate`

6741

6742 :ref:`tutorial_scalar_subquery`

6743

6744 """

6745

6746 self._auto_correlate = False

6747 if not fromclauses or fromclauses[0] in {None, False}:

6748 if len(fromclauses) > 1:

6749 raise exc.ArgumentError(

6750 "additional FROM objects not accepted when "

6751 "passing None/False to correlate_except()"

6752 )

6753 self._correlate_except = ()

6754 else:

6755 self._correlate_except = (self._correlate_except or ()) + tuple(

6756 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6757 )

6758

6759 return self

6760

6761 @HasMemoized_ro_memoized_attribute

6762 def selected_columns(

6763 self,

6764 ) -> ColumnCollection[str, ColumnElement[Any]]:

6765 """A :class:`_expression.ColumnCollection`

6766 representing the columns that

6767 this SELECT statement or similar construct returns in its result set,

6768 not including :class:`_sql.TextClause` constructs.

6769

6770 This collection differs from the :attr:`_expression.FromClause.columns`

6771 collection of a :class:`_expression.FromClause` in that the columns

6772 within this collection cannot be directly nested inside another SELECT

6773 statement; a subquery must be applied first which provides for the

6774 necessary parenthesization required by SQL.

6775

6776 For a :func:`_expression.select` construct, the collection here is

6777 exactly what would be rendered inside the "SELECT" statement, and the

6778 :class:`_expression.ColumnElement` objects are directly present as they

6779 were given, e.g.::

6780

6781 col1 = column("q", Integer)

6782 col2 = column("p", Integer)

6783 stmt = select(col1, col2)

6784

6785 Above, ``stmt.selected_columns`` would be a collection that contains

6786 the ``col1`` and ``col2`` objects directly. For a statement that is

6787 against a :class:`_schema.Table` or other

6788 :class:`_expression.FromClause`, the collection will use the

6789 :class:`_expression.ColumnElement` objects that are in the

6790 :attr:`_expression.FromClause.c` collection of the from element.

6791

6792 A use case for the :attr:`_sql.Select.selected_columns` collection is

6793 to allow the existing columns to be referenced when adding additional

6794 criteria, e.g.::

6795

6796 def filter_on_id(my_select, id):

6797 return my_select.where(my_select.selected_columns["id"] == id)

6798

6799

6800 stmt = select(MyModel)

6801

6802 # adds "WHERE id=:param" to the statement

6803 stmt = filter_on_id(stmt, 42)

6804

6805 .. note::

6806

6807 The :attr:`_sql.Select.selected_columns` collection does not

6808 include expressions established in the columns clause using the

6809 :func:`_sql.text` construct; these are silently omitted from the

6810 collection. To use plain textual column expressions inside of a

6811 :class:`_sql.Select` construct, use the :func:`_sql.literal_column`

6812 construct.

6813

6814

6815 .. versionadded:: 1.4

6816

6817 """

6818

6819 # compare to SelectState._generate_columns_plus_names, which

6820 # generates the actual names used in the SELECT string. that

6821 # method is more complex because it also renders columns that are

6822 # fully ambiguous, e.g. same column more than once.

6823 conv = cast(

6824 "Callable[[Any], str]",

6825 SelectState._column_naming_convention(self._label_style),

6826 )

6827

6828 cc: WriteableColumnCollection[str, ColumnElement[Any]] = (

6829 WriteableColumnCollection(

6830 [

6831 (conv(c), c)

6832 for c in self._all_selected_columns

6833 if is_column_element(c)

6834 ]

6835 )

6836 )

6837 return cc.as_readonly()

6838

6839 @HasMemoized_ro_memoized_attribute

6840 def _all_selected_columns(self) -> _SelectIterable:

6841 meth = SelectState.get_plugin_class(self).all_selected_columns

6842 return list(meth(self))

6843

6844 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6845 if self._label_style is LABEL_STYLE_NONE:

6846 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6847 return self

6848

6849 def _generate_fromclause_column_proxies(

6850 self,

6851 subquery: FromClause,

6852 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

6853 primary_key: ColumnSet,

6854 foreign_keys: Set[KeyedColumnElement[Any]],

6855 *,

6856 proxy_compound_columns: Optional[

6857 Iterable[Sequence[ColumnElement[Any]]]

6858 ] = None,

6859 ) -> None:

6860 """Generate column proxies to place in the exported ``.c``

6861 collection of a subquery."""

6862

6863 if proxy_compound_columns:

6864 extra_col_iterator = proxy_compound_columns

6865 prox = [

6866 c._make_proxy(

6867 subquery,

6868 key=proxy_key,

6869 name=required_label_name,

6870 name_is_truncatable=True,

6871 compound_select_cols=extra_cols,

6872 primary_key=primary_key,

6873 foreign_keys=foreign_keys,

6874 )

6875 for (

6876 (

6877 required_label_name,

6878 proxy_key,

6879 fallback_label_name,

6880 c,

6881 repeated,

6882 ),

6883 extra_cols,

6884 ) in (

6885 zip(

6886 self._generate_columns_plus_names(False),

6887 extra_col_iterator,

6888 )

6889 )

6890 if is_column_element(c)

6891 ]

6892 else:

6893 prox = [

6894 c._make_proxy(

6895 subquery,

6896 key=proxy_key,

6897 name=required_label_name,

6898 name_is_truncatable=True,

6899 primary_key=primary_key,

6900 foreign_keys=foreign_keys,

6901 )

6902 for (

6903 required_label_name,

6904 proxy_key,

6905 fallback_label_name,

6906 c,

6907 repeated,

6908 ) in (self._generate_columns_plus_names(False))

6909 if is_column_element(c)

6910 ]

6911

6912 columns._populate_separate_keys(prox)

6913

6914 def _needs_parens_for_grouping(self) -> bool:

6915 return self._has_row_limiting_clause or bool(

6916 self._order_by_clause.clauses

6917 )

6918

6919 def self_group(

6920 self, against: Optional[OperatorType] = None

6921 ) -> Union[SelectStatementGrouping[Self], Self]:

6922 """Return a 'grouping' construct as per the

6923 :class:`_expression.ClauseElement` specification.

6924

6925 This produces an element that can be embedded in an expression. Note

6926 that this method is called automatically as needed when constructing

6927 expressions and should not require explicit use.

6928

6929 """

6930 if (

6931 isinstance(against, CompoundSelect)

6932 and not self._needs_parens_for_grouping()

6933 ):

6934 return self

6935 else:

6936 return SelectStatementGrouping(self)

6937

6938 def union(

6939 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6940 ) -> CompoundSelect[Unpack[_Ts]]:

6941 r"""Return a SQL ``UNION`` of this select() construct against

6942 the given selectables provided as positional arguments.

6943

6944 :param \*other: one or more elements with which to create a

6945 UNION.

6946

6947 .. versionchanged:: 1.4.28

6948

6949 multiple elements are now accepted.

6950

6951 :param \**kwargs: keyword arguments are forwarded to the constructor

6952 for the newly created :class:`_sql.CompoundSelect` object.

6953

6954 """

6955 return CompoundSelect._create_union(self, *other)

6956

6957 def union_all(

6958 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6959 ) -> CompoundSelect[Unpack[_Ts]]:

6960 r"""Return a SQL ``UNION ALL`` of this select() construct against

6961 the given selectables provided as positional arguments.

6962

6963 :param \*other: one or more elements with which to create a

6964 UNION.

6965

6966 .. versionchanged:: 1.4.28

6967

6968 multiple elements are now accepted.

6969

6970 :param \**kwargs: keyword arguments are forwarded to the constructor

6971 for the newly created :class:`_sql.CompoundSelect` object.

6972

6973 """

6974 return CompoundSelect._create_union_all(self, *other)

6975

6976 def except_(

6977 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6978 ) -> CompoundSelect[Unpack[_Ts]]:

6979 r"""Return a SQL ``EXCEPT`` of this select() construct against

6980 the given selectable provided as positional arguments.

6981

6982 :param \*other: one or more elements with which to create a

6983 UNION.

6984

6985 .. versionchanged:: 1.4.28

6986

6987 multiple elements are now accepted.

6988

6989 """

6990 return CompoundSelect._create_except(self, *other)

6991

6992 def except_all(

6993 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

6994 ) -> CompoundSelect[Unpack[_Ts]]:

6995 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6996 the given selectables provided as positional arguments.

6997

6998 :param \*other: one or more elements with which to create a

6999 UNION.

7000

7001 .. versionchanged:: 1.4.28

7002

7003 multiple elements are now accepted.

7004

7005 """

7006 return CompoundSelect._create_except_all(self, *other)

7007

7008 def intersect(

7009 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7010 ) -> CompoundSelect[Unpack[_Ts]]:

7011 r"""Return a SQL ``INTERSECT`` of this select() construct against

7012 the given selectables provided as positional arguments.

7013

7014 :param \*other: one or more elements with which to create a

7015 UNION.

7016

7017 .. versionchanged:: 1.4.28

7018

7019 multiple elements are now accepted.

7020

7021 :param \**kwargs: keyword arguments are forwarded to the constructor

7022 for the newly created :class:`_sql.CompoundSelect` object.

7023

7024 """

7025 return CompoundSelect._create_intersect(self, *other)

7026

7027 def intersect_all(

7028 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

7029 ) -> CompoundSelect[Unpack[_Ts]]:

7030 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

7031 against the given selectables provided as positional arguments.

7032

7033 :param \*other: one or more elements with which to create a

7034 UNION.

7035

7036 .. versionchanged:: 1.4.28

7037

7038 multiple elements are now accepted.

7039

7040 :param \**kwargs: keyword arguments are forwarded to the constructor

7041 for the newly created :class:`_sql.CompoundSelect` object.

7042

7043 """

7044 return CompoundSelect._create_intersect_all(self, *other)

7045

7046

7047class ScalarSelect(

7048 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

7049):

7050 """Represent a scalar subquery.

7051

7052

7053 A :class:`_sql.ScalarSelect` is created by invoking the

7054 :meth:`_sql.SelectBase.scalar_subquery` method. The object

7055 then participates in other SQL expressions as a SQL column expression

7056 within the :class:`_sql.ColumnElement` hierarchy.

7057

7058 .. seealso::

7059

7060 :meth:`_sql.SelectBase.scalar_subquery`

7061

7062 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7063

7064 """

7065

7066 _traverse_internals: _TraverseInternalsType = [

7067 ("element", InternalTraversal.dp_clauseelement),

7068 ("type", InternalTraversal.dp_type),

7069 ]

7070

7071 _from_objects: List[FromClause] = []

7072 _is_from_container = True

7073 if not TYPE_CHECKING:

7074 _is_implicitly_boolean = False

7075 inherit_cache = True

7076

7077 element: SelectBase

7078

7079 def __init__(self, element: SelectBase) -> None:

7080 self.element = element

7081 self.type = element._scalar_type()

7082 self._propagate_attrs = element._propagate_attrs

7083

7084 def __getattr__(self, attr: str) -> Any:

7085 return getattr(self.element, attr)

7086

7087 def __getstate__(self) -> Dict[str, Any]:

7088 return {"element": self.element, "type": self.type}

7089

7090 def __setstate__(self, state: Dict[str, Any]) -> None:

7091 self.element = state["element"]

7092 self.type = state["type"]

7093

7094 @property

7095 def columns(self) -> NoReturn:

7096 raise exc.InvalidRequestError(

7097 "Scalar Select expression has no "

7098 "columns; use this object directly "

7099 "within a column-level expression."

7100 )

7101

7102 c = columns

7103

7104 @_generative

7105 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7106 """Apply a WHERE clause to the SELECT statement referred to

7107 by this :class:`_expression.ScalarSelect`.

7108

7109 """

7110 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7111 crit

7112 )

7113 return self

7114

7115 def self_group(self, against: Optional[OperatorType] = None) -> Self:

7116 return self

7117

7118 def _ungroup(self) -> Self:

7119 return self

7120

7121 @_generative

7122 def correlate(

7123 self,

7124 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7125 ) -> Self:

7126 r"""Return a new :class:`_expression.ScalarSelect`

7127 which will correlate the given FROM

7128 clauses to that of an enclosing :class:`_expression.Select`.

7129

7130 This method is mirrored from the :meth:`_sql.Select.correlate` method

7131 of the underlying :class:`_sql.Select`. The method applies the

7132 :meth:_sql.Select.correlate` method, then returns a new

7133 :class:`_sql.ScalarSelect` against that statement.

7134

7135 .. versionadded:: 1.4 Previously, the

7136 :meth:`_sql.ScalarSelect.correlate`

7137 method was only available from :class:`_sql.Select`.

7138

7139 :param \*fromclauses: a list of one or more

7140 :class:`_expression.FromClause`

7141 constructs, or other compatible constructs (i.e. ORM-mapped

7142 classes) to become part of the correlate collection.

7143

7144 .. seealso::

7145

7146 :meth:`_expression.ScalarSelect.correlate_except`

7147

7148 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7149

7150

7151 """

7152 self.element = cast(

7153 "Select[Unpack[TupleAny]]", self.element

7154 ).correlate(*fromclauses)

7155 return self

7156

7157 @_generative

7158 def correlate_except(

7159 self,

7160 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7161 ) -> Self:

7162 r"""Return a new :class:`_expression.ScalarSelect`

7163 which will omit the given FROM

7164 clauses from the auto-correlation process.

7165

7166 This method is mirrored from the

7167 :meth:`_sql.Select.correlate_except` method of the underlying

7168 :class:`_sql.Select`. The method applies the

7169 :meth:_sql.Select.correlate_except` method, then returns a new

7170 :class:`_sql.ScalarSelect` against that statement.

7171

7172 .. versionadded:: 1.4 Previously, the

7173 :meth:`_sql.ScalarSelect.correlate_except`

7174 method was only available from :class:`_sql.Select`.

7175

7176 :param \*fromclauses: a list of one or more

7177 :class:`_expression.FromClause`

7178 constructs, or other compatible constructs (i.e. ORM-mapped

7179 classes) to become part of the correlate-exception collection.

7180

7181 .. seealso::

7182

7183 :meth:`_expression.ScalarSelect.correlate`

7184

7185 :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial

7186

7187

7188 """

7189

7190 self.element = cast(

7191 "Select[Unpack[TupleAny]]", self.element

7192 ).correlate_except(*fromclauses)

7193 return self

7194

7195

7196class Exists(UnaryExpression[bool]):

7197 """Represent an ``EXISTS`` clause.

7198

7199 See :func:`_sql.exists` for a description of usage.

7200

7201 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7202 instance by calling :meth:`_sql.SelectBase.exists`.

7203

7204 """

7205

7206 inherit_cache = True

7207

7208 def __init__(

7209 self,

7210 __argument: Optional[

7211 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7212 ] = None,

7213 /,

7214 ):

7215 s: ScalarSelect[Any]

7216

7217 # TODO: this seems like we should be using coercions for this

7218 if __argument is None:

7219 s = Select(literal_column("*")).scalar_subquery()

7220 elif isinstance(__argument, SelectBase):

7221 s = __argument.scalar_subquery()

7222 s._propagate_attrs = __argument._propagate_attrs

7223 elif isinstance(__argument, ScalarSelect):

7224 s = __argument

7225 else:

7226 s = Select(__argument).scalar_subquery()

7227

7228 UnaryExpression.__init__(

7229 self,

7230 s,

7231 operator=operators.exists,

7232 type_=type_api.BOOLEANTYPE,

7233 )

7234

7235 @util.ro_non_memoized_property

7236 def _from_objects(self) -> List[FromClause]:

7237 return []

7238

7239 def _regroup(

7240 self,

7241 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7242 ) -> ScalarSelect[Any]:

7243

7244 assert isinstance(self.element, ScalarSelect)

7245 element = self.element.element

7246 if not isinstance(element, Select):

7247 raise exc.InvalidRequestError(

7248 "Can only apply this operation to a plain SELECT construct"

7249 )

7250 new_element = fn(element)

7251

7252 return_value = new_element.scalar_subquery()

7253 return return_value

7254

7255 def select(self) -> Select[bool]:

7256 r"""Return a SELECT of this :class:`_expression.Exists`.

7257

7258 e.g.::

7259

7260 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7261

7262 This will produce a statement resembling:

7263

7264 .. sourcecode:: sql

7265

7266 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7267

7268 .. seealso::

7269

7270 :func:`_expression.select` - general purpose

7271 method which allows for arbitrary column lists.

7272

7273 """ # noqa

7274

7275 return Select(self)

7276

7277 def correlate(

7278 self,

7279 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7280 ) -> Self:

7281 """Apply correlation to the subquery noted by this

7282 :class:`_sql.Exists`.

7283

7284 .. seealso::

7285

7286 :meth:`_sql.ScalarSelect.correlate`

7287

7288 """

7289 e = self._clone()

7290 e.element = self._regroup(

7291 lambda element: element.correlate(*fromclauses)

7292 )

7293 return e

7294

7295 def correlate_except(

7296 self,

7297 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7298 ) -> Self:

7299 """Apply correlation to the subquery noted by this

7300 :class:`_sql.Exists`.

7301

7302 .. seealso::

7303

7304 :meth:`_sql.ScalarSelect.correlate_except`

7305

7306 """

7307 e = self._clone()

7308 e.element = self._regroup(

7309 lambda element: element.correlate_except(*fromclauses)

7310 )

7311 return e

7312

7313 def select_from(self, *froms: _FromClauseArgument) -> Self:

7314 """Return a new :class:`_expression.Exists` construct,

7315 applying the given

7316 expression to the :meth:`_expression.Select.select_from`

7317 method of the select

7318 statement contained.

7319

7320 .. note:: it is typically preferable to build a :class:`_sql.Select`

7321 statement first, including the desired WHERE clause, then use the

7322 :meth:`_sql.SelectBase.exists` method to produce an

7323 :class:`_sql.Exists` object at once.

7324

7325 """

7326 e = self._clone()

7327 e.element = self._regroup(lambda element: element.select_from(*froms))

7328 return e

7329

7330 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7331 """Return a new :func:`_expression.exists` construct with the

7332 given expression added to

7333 its WHERE clause, joined to the existing clause via AND, if any.

7334

7335

7336 .. note:: it is typically preferable to build a :class:`_sql.Select`

7337 statement first, including the desired WHERE clause, then use the

7338 :meth:`_sql.SelectBase.exists` method to produce an

7339 :class:`_sql.Exists` object at once.

7340

7341 """

7342 e = self._clone()

7343 e.element = self._regroup(lambda element: element.where(*clause))

7344 return e

7345

7346

7347class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7348 """Wrap a :class:`_expression.TextClause` construct within a

7349 :class:`_expression.SelectBase`

7350 interface.

7351

7352 This allows the :class:`_expression.TextClause` object to gain a

7353 ``.c`` collection

7354 and other FROM-like capabilities such as

7355 :meth:`_expression.FromClause.alias`,

7356 :meth:`_expression.SelectBase.cte`, etc.

7357

7358 The :class:`_expression.TextualSelect` construct is produced via the

7359 :meth:`_expression.TextClause.columns`

7360 method - see that method for details.

7361

7362 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7363 class was renamed

7364 from ``TextAsFrom``, to more correctly suit its role as a

7365 SELECT-oriented object and not a FROM clause.

7366

7367 .. seealso::

7368

7369 :func:`_expression.text`

7370

7371 :meth:`_expression.TextClause.columns` - primary creation interface.

7372

7373 """

7374

7375 __visit_name__ = "textual_select"

7376

7377 _label_style = LABEL_STYLE_NONE

7378

7379 _traverse_internals: _TraverseInternalsType = (

7380 [

7381 ("element", InternalTraversal.dp_clauseelement),

7382 ("column_args", InternalTraversal.dp_clauseelement_list),

7383 ]

7384 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7385 + HasCTE._has_ctes_traverse_internals

7386 + ExecutableStatement._executable_traverse_internals

7387 )

7388

7389 _is_textual = True

7390

7391 is_text = True

7392 is_select = True

7393

7394 def __init__(

7395 self,

7396 text: TextClause,

7397 columns: List[_ColumnExpressionArgument[Any]],

7398 positional: bool = False,

7399 ) -> None:

7400 self._init(

7401 text,

7402 # convert for ORM attributes->columns, etc

7403 [

7404 coercions.expect(roles.LabeledColumnExprRole, c)

7405 for c in columns

7406 ],

7407 positional,

7408 )

7409

7410 def _init(

7411 self,

7412 text: AbstractTextClause,

7413 columns: List[NamedColumn[Any]],

7414 positional: bool = False,

7415 ) -> None:

7416 self.element = text

7417 self.column_args = columns

7418 self.positional = positional

7419

7420 @HasMemoized_ro_memoized_attribute

7421 def selected_columns(

7422 self,

7423 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

7424 """A :class:`_expression.ColumnCollection`

7425 representing the columns that

7426 this SELECT statement or similar construct returns in its result set,

7427 not including :class:`_sql.TextClause` constructs.

7428

7429 This collection differs from the :attr:`_expression.FromClause.columns`

7430 collection of a :class:`_expression.FromClause` in that the columns

7431 within this collection cannot be directly nested inside another SELECT

7432 statement; a subquery must be applied first which provides for the

7433 necessary parenthesization required by SQL.

7434

7435 For a :class:`_expression.TextualSelect` construct, the collection

7436 contains the :class:`_expression.ColumnElement` objects that were

7437 passed to the constructor, typically via the

7438 :meth:`_expression.TextClause.columns` method.

7439

7440

7441 .. versionadded:: 1.4

7442

7443 """

7444 return WriteableColumnCollection(

7445 (c.key, c) for c in self.column_args

7446 ).as_readonly()

7447

7448 @util.ro_non_memoized_property

7449 def _all_selected_columns(self) -> _SelectIterable:

7450 return self.column_args

7451

7452 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7453 return self

7454

7455 def _ensure_disambiguated_names(self) -> TextualSelect:

7456 return self

7457

7458 @_generative

7459 def bindparams(

7460 self,

7461 *binds: BindParameter[Any],

7462 **bind_as_values: Any,

7463 ) -> Self:

7464 self.element = self.element.bindparams(*binds, **bind_as_values)

7465 return self

7466

7467 def _generate_fromclause_column_proxies(

7468 self,

7469 fromclause: FromClause,

7470 columns: WriteableColumnCollection[str, KeyedColumnElement[Any]],

7471 primary_key: ColumnSet,

7472 foreign_keys: Set[KeyedColumnElement[Any]],

7473 *,

7474 proxy_compound_columns: Optional[

7475 Iterable[Sequence[ColumnElement[Any]]]

7476 ] = None,

7477 ) -> None:

7478 if TYPE_CHECKING:

7479 assert isinstance(fromclause, Subquery)

7480

7481 if proxy_compound_columns:

7482 columns._populate_separate_keys(

7483 c._make_proxy(

7484 fromclause,

7485 compound_select_cols=extra_cols,

7486 primary_key=primary_key,

7487 foreign_keys=foreign_keys,

7488 )

7489 for c, extra_cols in zip(

7490 self.column_args, proxy_compound_columns

7491 )

7492 )

7493 else:

7494 columns._populate_separate_keys(

7495 c._make_proxy(

7496 fromclause,

7497 primary_key=primary_key,

7498 foreign_keys=foreign_keys,

7499 )

7500 for c in self.column_args

7501 )

7502

7503 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7504 return self.column_args[0].type

7505

7506

7507TextAsFrom = TextualSelect

7508"""Backwards compatibility with the previous name"""

7509

7510

7511class AnnotatedFromClause(Annotated):

7512 def _copy_internals(

7513 self,

7514 _annotations_traversal: bool = False,

7515 ind_cols_on_fromclause: bool = False,

7516 **kw: Any,

7517 ) -> None:

7518 super()._copy_internals(**kw)

7519

7520 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7521 # the traversals used by annotations for these cases are not currently

7522 # designed around expecting that inner elements inside of

7523 # AnnotatedFromClause's element are also deep copied, so skip for these

7524 # cases. in other cases such as plain visitors.cloned_traverse(), we

7525 # expect this to happen. see issue #12915

7526 if not _annotations_traversal:

7527 ee = self._Annotated__element # type: ignore

7528 ee._copy_internals(**kw)

7529

7530 if ind_cols_on_fromclause:

7531 # passed from annotations._deep_annotate(). See that function

7532 # for notes

7533 ee = self._Annotated__element # type: ignore

7534 self.c = ee.__class__.c.fget(self) # type: ignore

7535

7536 @util.ro_memoized_property

7537 def c(self) -> ReadOnlyColumnCollection[str, KeyedColumnElement[Any]]:

7538 """proxy the .c collection of the underlying FromClause.

7539

7540 Originally implemented in 2008 as a simple load of the .c collection

7541 when the annotated construct was created (see d3621ae961a), in modern

7542 SQLAlchemy versions this can be expensive for statements constructed

7543 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7544 it, which works just as well.

7545

7546 Two different use cases seem to require the collection either copied

7547 from the underlying one, or unique to this AnnotatedFromClause.

7548

7549 See test_selectable->test_annotated_corresponding_column

7550

7551 """

7552 ee = self._Annotated__element # type: ignore

7553 return ee.c # type: ignore