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 roles.OrderByRole,

4507 ] = _NoArg.NO_ARG,

4508 /,

4509 *clauses: Union[

4510 _ColumnExpressionOrStrLabelArgument[Any], roles.OrderByRole

4511 ],

4512 ) -> Self:

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

4514 criteria applied.

4515

4516 e.g.::

4517

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

4519

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

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

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

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

4524

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

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

4527

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

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

4530

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

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

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

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

4535 label matching in ORDER BY and GROUP BY expressions.

4536

4537 .. seealso::

4538

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

4540

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

4542

4543 """

4544

4545 if not clauses and __first is None:

4546 self._order_by_clauses = ()

4547 elif __first is not _NoArg.NO_ARG:

4548 self._order_by_clauses += tuple(

4549 coercions.expect(

4550 roles.OrderByRole, clause, apply_propagate_attrs=self

4551 )

4552 for clause in (__first,) + clauses

4553 )

4554 return self

4555

4556 @_generative

4557 def group_by(

4558 self,

4559 __first: Union[

4560 Literal[None, _NoArg.NO_ARG],

4561 _ColumnExpressionOrStrLabelArgument[Any],

4562 ] = _NoArg.NO_ARG,

4563 /,

4564 *clauses: _ColumnExpressionOrStrLabelArgument[Any],

4565 ) -> Self:

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

4567 criterion applied.

4568

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

4570

4571 e.g.::

4572

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

4574

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

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

4577

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

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

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

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

4582 label matching in ORDER BY and GROUP BY expressions.

4583

4584 .. seealso::

4585

4586 :ref:`tutorial_group_by_w_aggregates` - in the

4587 :ref:`unified_tutorial`

4588

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

4590

4591 """ # noqa: E501

4592

4593 if not clauses and __first is None:

4594 self._group_by_clauses = ()

4595 elif __first is not _NoArg.NO_ARG:

4596 self._group_by_clauses += tuple(

4597 coercions.expect(

4598 roles.GroupByRole, clause, apply_propagate_attrs=self

4599 )

4600 for clause in (__first,) + clauses

4601 )

4602 return self

4603

4604

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

4606class CompoundSelectState(CompileState):

4607 @util.memoized_property

4608 def _label_resolve_dict(

4609 self,

4610 ) -> Tuple[

4611 Dict[str, ColumnElement[Any]],

4612 Dict[str, ColumnElement[Any]],

4613 Dict[str, ColumnElement[Any]],

4614 ]:

4615 # TODO: this is hacky and slow

4616 hacky_subquery = self.statement.subquery()

4617 hacky_subquery.named_with_column = False

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

4619 return d, d, d

4620

4621

4622class _CompoundSelectKeyword(Enum):

4623 UNION = "UNION"

4624 UNION_ALL = "UNION ALL"

4625 EXCEPT = "EXCEPT"

4626 EXCEPT_ALL = "EXCEPT ALL"

4627 INTERSECT = "INTERSECT"

4628 INTERSECT_ALL = "INTERSECT ALL"

4629

4630

4631class CompoundSelect(

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

4633):

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

4635 SELECT-based set operations.

4636

4637

4638 .. seealso::

4639

4640 :func:`_expression.union`

4641

4642 :func:`_expression.union_all`

4643

4644 :func:`_expression.intersect`

4645

4646 :func:`_expression.intersect_all`

4647

4648 :func:`_expression.except`

4649

4650 :func:`_expression.except_all`

4651

4652 """

4653

4654 __visit_name__ = "compound_select"

4655

4656 _traverse_internals: _TraverseInternalsType = (

4657 [

4658 ("selects", InternalTraversal.dp_clauseelement_list),

4659 ("_limit_clause", InternalTraversal.dp_clauseelement),

4660 ("_offset_clause", InternalTraversal.dp_clauseelement),

4661 ("_fetch_clause", InternalTraversal.dp_clauseelement),

4662 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

4663 ("_order_by_clauses", InternalTraversal.dp_clauseelement_list),

4664 ("_group_by_clauses", InternalTraversal.dp_clauseelement_list),

4665 ("_for_update_arg", InternalTraversal.dp_clauseelement),

4666 ("keyword", InternalTraversal.dp_string),

4667 ]

4668 + SupportsCloneAnnotations._clone_annotations_traverse_internals

4669 + HasCTE._has_ctes_traverse_internals

4670 + DialectKWArgs._dialect_kwargs_traverse_internals

4671 + ExecutableStatement._executable_traverse_internals

4672 )

4673

4674 selects: List[SelectBase]

4675

4676 _is_from_container = True

4677 _auto_correlate = False

4678

4679 def __init__(

4680 self,

4681 keyword: _CompoundSelectKeyword,

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

4683 ):

4684 self.keyword = keyword

4685 self.selects = [

4686 coercions.expect(

4687 roles.CompoundElementRole, s, apply_propagate_attrs=self

4688 ).self_group(against=self)

4689 for s in selects

4690 ]

4691

4692 GenerativeSelect.__init__(self)

4693

4694 @classmethod

4695 def _create_union(

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

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

4698 return CompoundSelect(_CompoundSelectKeyword.UNION, *selects)

4699

4700 @classmethod

4701 def _create_union_all(

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

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

4704 return CompoundSelect(_CompoundSelectKeyword.UNION_ALL, *selects)

4705

4706 @classmethod

4707 def _create_except(

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

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

4710 return CompoundSelect(_CompoundSelectKeyword.EXCEPT, *selects)

4711

4712 @classmethod

4713 def _create_except_all(

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

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

4716 return CompoundSelect(_CompoundSelectKeyword.EXCEPT_ALL, *selects)

4717

4718 @classmethod

4719 def _create_intersect(

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

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

4722 return CompoundSelect(_CompoundSelectKeyword.INTERSECT, *selects)

4723

4724 @classmethod

4725 def _create_intersect_all(

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

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

4728 return CompoundSelect(_CompoundSelectKeyword.INTERSECT_ALL, *selects)

4729

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

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

4732

4733 def self_group(

4734 self, against: Optional[OperatorType] = None

4735 ) -> GroupedElement:

4736 return SelectStatementGrouping(self)

4737

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

4739 for s in self.selects:

4740 if s.is_derived_from(fromclause):

4741 return True

4742 return False

4743

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

4745 if self._label_style is not style:

4746 self = self._generate()

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

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

4749

4750 return self

4751

4752 def _ensure_disambiguated_names(self) -> Self:

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

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

4755 self = self._generate()

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

4757

4758 return self

4759

4760 def _generate_fromclause_column_proxies(

4761 self,

4762 subquery: FromClause,

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

4764 primary_key: ColumnSet,

4765 foreign_keys: Set[KeyedColumnElement[Any]],

4766 *,

4767 proxy_compound_columns: Optional[

4768 Iterable[Sequence[ColumnElement[Any]]]

4769 ] = None,

4770 ) -> None:

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

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

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

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

4775 # generate below.

4776 select_0 = self.selects[0]

4777

4778 if self._label_style is not LABEL_STYLE_DEFAULT:

4779 select_0 = select_0.set_label_style(self._label_style)

4780

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

4782 # derived columns place a 'weight' annotation corresponding

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

4784 # that the corresponding_column() operation can resolve

4785 # conflicts

4786 extra_col_iterator = zip(

4787 *[

4788 [

4789 c._annotate(dd)

4790 for c in stmt._all_selected_columns

4791 if is_column_element(c)

4792 ]

4793 for dd, stmt in [

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

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

4796 ]

4797 ]

4798 )

4799

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

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

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

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

4804 # compound

4805 select_0._generate_fromclause_column_proxies(

4806 subquery,

4807 columns,

4808 proxy_compound_columns=extra_col_iterator,

4809 primary_key=primary_key,

4810 foreign_keys=foreign_keys,

4811 )

4812

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

4814 super()._refresh_for_new_column(column)

4815 for select in self.selects:

4816 select._refresh_for_new_column(column)

4817

4818 @util.ro_non_memoized_property

4819 def _all_selected_columns(self) -> _SelectIterable:

4820 return self.selects[0]._all_selected_columns

4821

4822 @util.ro_non_memoized_property

4823 def selected_columns(

4824 self,

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

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

4827 representing the columns that

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

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

4830

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

4832 :attr:`_expression.CompoundSelect.selected_columns`

4833 attribute returns the selected

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

4835 statements within the set operation.

4836

4837 .. seealso::

4838

4839 :attr:`_sql.Select.selected_columns`

4840

4841 .. versionadded:: 1.4

4842

4843 """

4844 return self.selects[0].selected_columns

4845

4846

4847# backwards compat

4848for elem in _CompoundSelectKeyword:

4849 setattr(CompoundSelect, elem.name, elem)

4850

4851

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

4853class SelectState(util.MemoizedSlots, CompileState):

4854 __slots__ = (

4855 "from_clauses",

4856 "froms",

4857 "columns_plus_names",

4858 "_label_resolve_dict",

4859 )

4860

4861 if TYPE_CHECKING:

4862 default_select_compile_options: CacheableOptions

4863 else:

4864

4865 class default_select_compile_options(CacheableOptions):

4866 _cache_key_traversal = []

4867

4868 if TYPE_CHECKING:

4869

4870 @classmethod

4871 def get_plugin_class(

4872 cls, statement: Executable

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

4874

4875 def __init__(

4876 self,

4877 statement: Select[Unpack[TupleAny]],

4878 compiler: SQLCompiler,

4879 **kw: Any,

4880 ):

4881 self.statement = statement

4882 self.from_clauses = statement._from_obj

4883

4884 for memoized_entities in statement._memoized_select_entities:

4885 self._setup_joins(

4886 memoized_entities._setup_joins, memoized_entities._raw_columns

4887 )

4888

4889 if statement._setup_joins:

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

4891

4892 self.froms = self._get_froms(statement)

4893

4894 self.columns_plus_names = statement._generate_columns_plus_names(True)

4895

4896 @classmethod

4897 def _plugin_not_implemented(cls) -> NoReturn:

4898 raise NotImplementedError(

4899 "The default SELECT construct without plugins does not "

4900 "implement this method."

4901 )

4902

4903 @classmethod

4904 def get_column_descriptions(

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

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

4907 return [

4908 {

4909 "name": name,

4910 "type": element.type,

4911 "expr": element,

4912 }

4913 for _, name, _, element, _ in (

4914 statement._generate_columns_plus_names(False)

4915 )

4916 ]

4917

4918 @classmethod

4919 def from_statement(

4920 cls,

4921 statement: Select[Unpack[TupleAny]],

4922 from_statement: roles.ReturnsRowsRole,

4923 ) -> ExecutableReturnsRows:

4924 cls._plugin_not_implemented()

4925

4926 @classmethod

4927 def get_columns_clause_froms(

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

4929 ) -> List[FromClause]:

4930 return cls._normalize_froms(

4931 itertools.chain.from_iterable(

4932 element._from_objects for element in statement._raw_columns

4933 )

4934 )

4935

4936 @classmethod

4937 def _column_naming_convention(

4938 cls, label_style: SelectLabelStyle

4939 ) -> _LabelConventionCallable:

4940 table_qualified = label_style is LABEL_STYLE_TABLENAME_PLUS_COL

4941

4942 dedupe = label_style is not LABEL_STYLE_NONE

4943

4944 pa = prefix_anon_map()

4945 names = set()

4946

4947 def go(

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

4949 col_name: Optional[str] = None,

4950 ) -> Optional[str]:

4951 if is_text_clause(c):

4952 return None

4953 elif TYPE_CHECKING:

4954 assert is_column_element(c)

4955

4956 if not dedupe:

4957 name = c._proxy_key

4958 if name is None:

4959 name = "_no_label"

4960 return name

4961

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

4963

4964 if name is None:

4965 name = "_no_label"

4966 if name in names:

4967 return c._anon_label(name) % pa

4968 else:

4969 names.add(name)

4970 return name

4971

4972 elif name in names:

4973 return (

4974 c._anon_tq_key_label % pa

4975 if table_qualified

4976 else c._anon_key_label % pa

4977 )

4978 else:

4979 names.add(name)

4980 return name

4981

4982 return go

4983

4984 def _get_froms(

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

4986 ) -> List[FromClause]:

4987 ambiguous_table_name_map: _AmbiguousTableNameMap

4988 self._ambiguous_table_name_map = ambiguous_table_name_map = {}

4989

4990 return self._normalize_froms(

4991 itertools.chain(

4992 self.from_clauses,

4993 itertools.chain.from_iterable(

4994 [

4995 element._from_objects

4996 for element in statement._raw_columns

4997 ]

4998 ),

4999 itertools.chain.from_iterable(

5000 [

5001 element._from_objects

5002 for element in statement._where_criteria

5003 ]

5004 ),

5005 ),

5006 check_statement=statement,

5007 ambiguous_table_name_map=ambiguous_table_name_map,

5008 )

5009

5010 @classmethod

5011 def _normalize_froms(

5012 cls,

5013 iterable_of_froms: Iterable[FromClause],

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

5015 ambiguous_table_name_map: Optional[_AmbiguousTableNameMap] = None,

5016 ) -> List[FromClause]:

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

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

5019

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

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

5022 etc.

5023

5024 """

5025 seen: Set[FromClause] = set()

5026 froms: List[FromClause] = []

5027

5028 for item in iterable_of_froms:

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

5030 raise exc.InvalidRequestError(

5031 "select() construct refers to itself as a FROM"

5032 )

5033

5034 if not seen.intersection(item._cloned_set):

5035 froms.append(item)

5036 seen.update(item._cloned_set)

5037

5038 if froms:

5039 toremove = set(

5040 itertools.chain.from_iterable(

5041 [_expand_cloned(f._hide_froms) for f in froms]

5042 )

5043 )

5044 if toremove:

5045 # filter out to FROM clauses not in the list,

5046 # using a list to maintain ordering

5047 froms = [f for f in froms if f not in toremove]

5048

5049 if ambiguous_table_name_map is not None:

5050 ambiguous_table_name_map.update(

5051 (

5052 fr.name,

5053 _anonymous_label.safe_construct(

5054 hash(fr.name), fr.name

5055 ),

5056 )

5057 for item in froms

5058 for fr in item._from_objects

5059 if is_table(fr)

5060 and fr.schema

5061 and fr.name not in ambiguous_table_name_map

5062 )

5063

5064 return froms

5065

5066 def _get_display_froms(

5067 self,

5068 explicit_correlate_froms: Optional[Sequence[FromClause]] = None,

5069 implicit_correlate_froms: Optional[Sequence[FromClause]] = None,

5070 ) -> List[FromClause]:

5071 """Return the full list of 'from' clauses to be displayed.

5072

5073 Takes into account a set of existing froms which may be

5074 rendered in the FROM clause of enclosing selects; this Select

5075 may want to leave those absent if it is automatically

5076 correlating.

5077

5078 """

5079

5080 froms = self.froms

5081

5082 if self.statement._correlate:

5083 to_correlate = self.statement._correlate

5084 if to_correlate:

5085 to_remove = _cloned_intersection(

5086 _cloned_intersection(

5087 froms, explicit_correlate_froms or ()

5088 ),

5089 to_correlate,

5090 )

5091 froms = [f for f in froms if f not in to_remove]

5092

5093 if self.statement._correlate_except is not None:

5094 to_remove = _cloned_difference(

5095 _cloned_intersection(froms, explicit_correlate_froms or ()),

5096 self.statement._correlate_except,

5097 )

5098 froms = [f for f in froms if f not in to_remove]

5099

5100 if (

5101 self.statement._auto_correlate

5102 and implicit_correlate_froms

5103 and len(froms) > 1

5104 ):

5105 to_remove = _cloned_intersection(froms, implicit_correlate_froms)

5106 froms = [f for f in froms if f not in to_remove]

5107

5108 if not len(froms):

5109 raise exc.InvalidRequestError(

5110 "Select statement '%r"

5111 "' returned no FROM clauses "

5112 "due to auto-correlation; "

5113 "specify correlate(<tables>) "

5114 "to control correlation "

5115 "manually." % self.statement

5116 )

5117

5118 return froms

5119

5120 def _memoized_attr__label_resolve_dict(

5121 self,

5122 ) -> Tuple[

5123 Dict[str, ColumnElement[Any]],

5124 Dict[str, ColumnElement[Any]],

5125 Dict[str, ColumnElement[Any]],

5126 ]:

5127 with_cols: Dict[str, ColumnElement[Any]] = {

5128 c._tq_label or c.key: c

5129 for c in self.statement._all_selected_columns

5130 if c._allow_label_resolve

5131 }

5132 only_froms: Dict[str, ColumnElement[Any]] = {

5133 c.key: c # type: ignore

5134 for c in _select_iterables(self.froms)

5135 if c._allow_label_resolve

5136 }

5137 only_cols: Dict[str, ColumnElement[Any]] = with_cols.copy()

5138 for key, value in only_froms.items():

5139 with_cols.setdefault(key, value)

5140

5141 return with_cols, only_froms, only_cols

5142

5143 @classmethod

5144 def _get_filter_by_entities(

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

5146 ) -> Collection[

5147 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5148 ]:

5149 """Return all entities to search for filter_by() attributes.

5150

5151 This includes:

5152

5153 * All joined entities from _setup_joins

5154 * Memoized entities from previous operations (e.g.,

5155 before with_only_columns)

5156 * Explicit FROM objects from _from_obj

5157 * Entities inferred from _raw_columns

5158

5159 .. versionadded:: 2.1

5160

5161 """

5162 entities: set[

5163 Union[FromClause, _JoinTargetProtocol, ColumnElement[Any]]

5164 ]

5165

5166 entities = set(

5167 join_element[0] for join_element in statement._setup_joins

5168 )

5169

5170 for memoized in statement._memoized_select_entities:

5171 entities.update(

5172 join_element[0] for join_element in memoized._setup_joins

5173 )

5174

5175 entities.update(statement._from_obj)

5176

5177 for col in statement._raw_columns:

5178 entities.update(col._from_objects)

5179

5180 return entities

5181

5182 @classmethod

5183 def all_selected_columns(

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

5185 ) -> _SelectIterable:

5186 return [c for c in _select_iterables(statement._raw_columns)]

5187

5188 def _setup_joins(

5189 self,

5190 args: Tuple[_SetupJoinsElement, ...],

5191 raw_columns: List[_ColumnsClauseElement],

5192 ) -> None:

5193 for right, onclause, left, flags in args:

5194 if TYPE_CHECKING:

5195 if onclause is not None:

5196 assert isinstance(onclause, ColumnElement)

5197

5198 explicit_left = left

5199 isouter = flags["isouter"]

5200 full = flags["full"]

5201

5202 if left is None:

5203 (

5204 left,

5205 replace_from_obj_index,

5206 ) = self._join_determine_implicit_left_side(

5207 raw_columns, left, right, onclause

5208 )

5209 else:

5210 replace_from_obj_index = self._join_place_explicit_left_side(

5211 left

5212 )

5213

5214 # these assertions can be made here, as if the right/onclause

5215 # contained ORM elements, the select() statement would have been

5216 # upgraded to an ORM select, and this method would not be called;

5217 # orm.context.ORMSelectCompileState._join() would be

5218 # used instead.

5219 if TYPE_CHECKING:

5220 assert isinstance(right, FromClause)

5221 if onclause is not None:

5222 assert isinstance(onclause, ColumnElement)

5223

5224 if replace_from_obj_index is not None:

5225 # splice into an existing element in the

5226 # self._from_obj list

5227 left_clause = self.from_clauses[replace_from_obj_index]

5228

5229 if explicit_left is not None and onclause is None:

5230 onclause = Join._join_condition(explicit_left, right)

5231

5232 self.from_clauses = (

5233 self.from_clauses[:replace_from_obj_index]

5234 + (

5235 Join(

5236 left_clause,

5237 right,

5238 onclause,

5239 isouter=isouter,

5240 full=full,

5241 ),

5242 )

5243 + self.from_clauses[replace_from_obj_index + 1 :]

5244 )

5245 else:

5246 assert left is not None

5247 self.from_clauses = self.from_clauses + (

5248 Join(left, right, onclause, isouter=isouter, full=full),

5249 )

5250

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

5252 def _join_determine_implicit_left_side(

5253 self,

5254 raw_columns: List[_ColumnsClauseElement],

5255 left: Optional[FromClause],

5256 right: _JoinTargetElement,

5257 onclause: Optional[ColumnElement[Any]],

5258 ) -> Tuple[Optional[FromClause], Optional[int]]:

5259 """When join conditions don't express the left side explicitly,

5260 determine if an existing FROM or entity in this query

5261 can serve as the left hand side.

5262

5263 """

5264

5265 sql_util = util.preloaded.sql_util

5266

5267 replace_from_obj_index: Optional[int] = None

5268

5269 from_clauses = self.from_clauses

5270

5271 if from_clauses:

5272 indexes: List[int] = sql_util.find_left_clause_to_join_from(

5273 from_clauses, right, onclause

5274 )

5275

5276 if len(indexes) == 1:

5277 replace_from_obj_index = indexes[0]

5278 left = from_clauses[replace_from_obj_index]

5279 else:

5280 potential = {}

5281 statement = self.statement

5282

5283 for from_clause in itertools.chain(

5284 itertools.chain.from_iterable(

5285 [element._from_objects for element in raw_columns]

5286 ),

5287 itertools.chain.from_iterable(

5288 [

5289 element._from_objects

5290 for element in statement._where_criteria

5291 ]

5292 ),

5293 ):

5294 potential[from_clause] = ()

5295

5296 all_clauses = list(potential.keys())

5297 indexes = sql_util.find_left_clause_to_join_from(

5298 all_clauses, right, onclause

5299 )

5300

5301 if len(indexes) == 1:

5302 left = all_clauses[indexes[0]]

5303

5304 if len(indexes) > 1:

5305 raise exc.InvalidRequestError(

5306 "Can't determine which FROM clause to join "

5307 "from, there are multiple FROMS which can "

5308 "join to this entity. Please use the .select_from() "

5309 "method to establish an explicit left side, as well as "

5310 "providing an explicit ON clause if not present already to "

5311 "help resolve the ambiguity."

5312 )

5313 elif not indexes:

5314 raise exc.InvalidRequestError(

5315 "Don't know how to join to %r. "

5316 "Please use the .select_from() "

5317 "method to establish an explicit left side, as well as "

5318 "providing an explicit ON clause if not present already to "

5319 "help resolve the ambiguity." % (right,)

5320 )

5321 return left, replace_from_obj_index

5322

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

5324 def _join_place_explicit_left_side(

5325 self, left: FromClause

5326 ) -> Optional[int]:

5327 replace_from_obj_index: Optional[int] = None

5328

5329 sql_util = util.preloaded.sql_util

5330

5331 from_clauses = list(self.statement._iterate_from_elements())

5332

5333 if from_clauses:

5334 indexes: List[int] = sql_util.find_left_clause_that_matches_given(

5335 self.from_clauses, left

5336 )

5337 else:

5338 indexes = []

5339

5340 if len(indexes) > 1:

5341 raise exc.InvalidRequestError(

5342 "Can't identify which entity in which to assign the "

5343 "left side of this join. Please use a more specific "

5344 "ON clause."

5345 )

5346

5347 # have an index, means the left side is already present in

5348 # an existing FROM in the self._from_obj tuple

5349 if indexes:

5350 replace_from_obj_index = indexes[0]

5351

5352 # no index, means we need to add a new element to the

5353 # self._from_obj tuple

5354

5355 return replace_from_obj_index

5356

5357

5358class _SelectFromElements:

5359 __slots__ = ()

5360

5361 _raw_columns: List[_ColumnsClauseElement]

5362 _where_criteria: Tuple[ColumnElement[Any], ...]

5363 _from_obj: Tuple[FromClause, ...]

5364

5365 def _iterate_from_elements(self) -> Iterator[FromClause]:

5366 # note this does not include elements

5367 # in _setup_joins

5368

5369 seen = set()

5370 for element in self._raw_columns:

5371 for fr in element._from_objects:

5372 if fr in seen:

5373 continue

5374 seen.add(fr)

5375 yield fr

5376 for element in self._where_criteria:

5377 for fr in element._from_objects:

5378 if fr in seen:

5379 continue

5380 seen.add(fr)

5381 yield fr

5382 for element in self._from_obj:

5383 if element in seen:

5384 continue

5385 seen.add(element)

5386 yield element

5387

5388

5389class _MemoizedSelectEntities(

5390 cache_key.HasCacheKey, traversals.HasCopyInternals, visitors.Traversible

5391):

5392 """represents partial state from a Select object, for the case

5393 where Select.columns() has redefined the set of columns/entities the

5394 statement will be SELECTing from. This object represents

5395 the entities from the SELECT before that transformation was applied,

5396 so that transformations that were made in terms of the SELECT at that

5397 time, such as join() as well as options(), can access the correct context.

5398

5399 In previous SQLAlchemy versions, this wasn't needed because these

5400 constructs calculated everything up front, like when you called join()

5401 or options(), it did everything to figure out how that would translate

5402 into specific SQL constructs that would be ready to send directly to the

5403 SQL compiler when needed. But as of

5404 1.4, all of that stuff is done in the compilation phase, during the

5405 "compile state" portion of the process, so that the work can all be

5406 cached. So it needs to be able to resolve joins/options2 based on what

5407 the list of entities was when those methods were called.

5408

5409

5410 """

5411

5412 __visit_name__ = "memoized_select_entities"

5413

5414 _traverse_internals: _TraverseInternalsType = [

5415 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5416 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5417 ("_with_options", InternalTraversal.dp_executable_options),

5418 ]

5419

5420 _is_clone_of: Optional[ClauseElement]

5421 _raw_columns: List[_ColumnsClauseElement]

5422 _setup_joins: Tuple[_SetupJoinsElement, ...]

5423 _with_options: Tuple[ExecutableOption, ...]

5424

5425 _annotations = util.EMPTY_DICT

5426

5427 def _clone(self, **kw: Any) -> Self:

5428 c = self.__class__.__new__(self.__class__)

5429 c.__dict__ = {k: v for k, v in self.__dict__.items()}

5430

5431 c._is_clone_of = self.__dict__.get("_is_clone_of", self)

5432 return c

5433

5434 @classmethod

5435 def _generate_for_statement(

5436 cls, select_stmt: Select[Unpack[TupleAny]]

5437 ) -> None:

5438 if select_stmt._setup_joins or select_stmt._with_options:

5439 self = _MemoizedSelectEntities()

5440 self._raw_columns = select_stmt._raw_columns

5441 self._setup_joins = select_stmt._setup_joins

5442 self._with_options = select_stmt._with_options

5443

5444 select_stmt._memoized_select_entities += (self,)

5445 select_stmt._raw_columns = []

5446 select_stmt._setup_joins = select_stmt._with_options = ()

5447

5448

5449class Select(

5450 HasPrefixes,

5451 HasSuffixes,

5452 HasHints,

5453 HasCompileState,

5454 HasSyntaxExtensions[

5455 Literal["post_select", "pre_columns", "post_criteria", "post_body"]

5456 ],

5457 _SelectFromElements,

5458 GenerativeSelect,

5459 TypedReturnsRows[Unpack[_Ts]],

5460):

5461 """Represents a ``SELECT`` statement.

5462

5463 The :class:`_sql.Select` object is normally constructed using the

5464 :func:`_sql.select` function. See that function for details.

5465

5466 Available extension points:

5467

5468 * ``post_select``: applies additional logic after the ``SELECT`` keyword.

5469 * ``pre_columns``: applies additional logic between the ``DISTINCT``

5470 keyword (if any) and the list of columns.

5471 * ``post_criteria``: applies additional logic after the ``HAVING`` clause.

5472 * ``post_body``: applies additional logic after the ``FOR UPDATE`` clause.

5473

5474 .. seealso::

5475

5476 :func:`_sql.select`

5477

5478 :ref:`tutorial_selecting_data` - in the 2.0 tutorial

5479

5480 """

5481

5482 __visit_name__ = "select"

5483

5484 _setup_joins: Tuple[_SetupJoinsElement, ...] = ()

5485 _memoized_select_entities: Tuple[TODO_Any, ...] = ()

5486

5487 _raw_columns: List[_ColumnsClauseElement]

5488

5489 _distinct: bool = False

5490 _distinct_on: Tuple[ColumnElement[Any], ...] = ()

5491 _correlate: Tuple[FromClause, ...] = ()

5492 _correlate_except: Optional[Tuple[FromClause, ...]] = None

5493 _where_criteria: Tuple[ColumnElement[Any], ...] = ()

5494 _having_criteria: Tuple[ColumnElement[Any], ...] = ()

5495 _from_obj: Tuple[FromClause, ...] = ()

5496

5497 _position_map = util.immutabledict(

5498 {

5499 "post_select": "_post_select_clause",

5500 "pre_columns": "_pre_columns_clause",

5501 "post_criteria": "_post_criteria_clause",

5502 "post_body": "_post_body_clause",

5503 }

5504 )

5505

5506 _post_select_clause: Optional[ClauseElement] = None

5507 """extension point for a ClauseElement that will be compiled directly

5508 after the SELECT keyword.

5509

5510 .. versionadded:: 2.1

5511

5512 """

5513

5514 _pre_columns_clause: Optional[ClauseElement] = None

5515 """extension point for a ClauseElement that will be compiled directly

5516 before the "columns" clause; after DISTINCT (if present).

5517

5518 .. versionadded:: 2.1

5519

5520 """

5521

5522 _post_criteria_clause: Optional[ClauseElement] = None

5523 """extension point for a ClauseElement that will be compiled directly

5524 after "criteria", following the HAVING clause but before ORDER BY.

5525

5526 .. versionadded:: 2.1

5527

5528 """

5529

5530 _post_body_clause: Optional[ClauseElement] = None

5531 """extension point for a ClauseElement that will be compiled directly

5532 after the "body", following the ORDER BY, LIMIT, and FOR UPDATE sections

5533 of the SELECT.

5534

5535 .. versionadded:: 2.1

5536

5537 """

5538

5539 _auto_correlate = True

5540 _is_select_statement = True

5541 _compile_options: CacheableOptions = (

5542 SelectState.default_select_compile_options

5543 )

5544

5545 _traverse_internals: _TraverseInternalsType = (

5546 [

5547 ("_raw_columns", InternalTraversal.dp_clauseelement_list),

5548 (

5549 "_memoized_select_entities",

5550 InternalTraversal.dp_memoized_select_entities,

5551 ),

5552 ("_from_obj", InternalTraversal.dp_clauseelement_list),

5553 ("_where_criteria", InternalTraversal.dp_clauseelement_tuple),

5554 ("_having_criteria", InternalTraversal.dp_clauseelement_tuple),

5555 ("_order_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5556 ("_group_by_clauses", InternalTraversal.dp_clauseelement_tuple),

5557 ("_setup_joins", InternalTraversal.dp_setup_join_tuple),

5558 ("_correlate", InternalTraversal.dp_clauseelement_tuple),

5559 ("_correlate_except", InternalTraversal.dp_clauseelement_tuple),

5560 ("_limit_clause", InternalTraversal.dp_clauseelement),

5561 ("_offset_clause", InternalTraversal.dp_clauseelement),

5562 ("_fetch_clause", InternalTraversal.dp_clauseelement),

5563 ("_fetch_clause_options", InternalTraversal.dp_plain_dict),

5564 ("_for_update_arg", InternalTraversal.dp_clauseelement),

5565 ("_distinct", InternalTraversal.dp_boolean),

5566 ("_distinct_on", InternalTraversal.dp_clauseelement_tuple),

5567 ("_label_style", InternalTraversal.dp_plain_obj),

5568 ("_post_select_clause", InternalTraversal.dp_clauseelement),

5569 ("_pre_columns_clause", InternalTraversal.dp_clauseelement),

5570 ("_post_criteria_clause", InternalTraversal.dp_clauseelement),

5571 ("_post_body_clause", InternalTraversal.dp_clauseelement),

5572 ]

5573 + HasCTE._has_ctes_traverse_internals

5574 + HasPrefixes._has_prefixes_traverse_internals

5575 + HasSuffixes._has_suffixes_traverse_internals

5576 + HasHints._has_hints_traverse_internals

5577 + SupportsCloneAnnotations._clone_annotations_traverse_internals

5578 + ExecutableStatement._executable_traverse_internals

5579 + DialectKWArgs._dialect_kwargs_traverse_internals

5580 )

5581

5582 _cache_key_traversal: _CacheKeyTraversalType = _traverse_internals + [

5583 ("_compile_options", InternalTraversal.dp_has_cache_key)

5584 ]

5585

5586 _compile_state_factory: Type[SelectState]

5587

5588 @classmethod

5589 def _create_raw_select(cls, **kw: Any) -> Select[Unpack[TupleAny]]:

5590 """Create a :class:`.Select` using raw ``__new__`` with no coercions.

5591

5592 Used internally to build up :class:`.Select` constructs with

5593 pre-established state.

5594

5595 """

5596

5597 stmt = Select.__new__(Select)

5598 stmt.__dict__.update(kw)

5599 return stmt

5600

5601 def __init__(

5602 self, *entities: _ColumnsClauseArgument[Any], **dialect_kw: Any

5603 ):

5604 r"""Construct a new :class:`_expression.Select`.

5605

5606 The public constructor for :class:`_expression.Select` is the

5607 :func:`_sql.select` function.

5608

5609 """

5610 self._raw_columns = [

5611 coercions.expect(

5612 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

5613 )

5614 for ent in entities

5615 ]

5616 GenerativeSelect.__init__(self)

5617

5618 def _apply_syntax_extension_to_self(

5619 self, extension: SyntaxExtension

5620 ) -> None:

5621 extension.apply_to_select(self)

5622

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

5624 if not self._raw_columns:

5625 return NULLTYPE

5626 elem = self._raw_columns[0]

5627 cols = list(elem._select_iterable)

5628 return cols[0].type

5629

5630 def filter(self, *criteria: _ColumnExpressionArgument[bool]) -> Self:

5631 """A synonym for the :meth:`_sql.Select.where` method."""

5632

5633 return self.where(*criteria)

5634

5635 if TYPE_CHECKING:

5636

5637 @overload

5638 def scalar_subquery(

5639 self: Select[_MAYBE_ENTITY],

5640 ) -> ScalarSelect[Any]: ...

5641

5642 @overload

5643 def scalar_subquery(

5644 self: Select[_NOT_ENTITY],

5645 ) -> ScalarSelect[_NOT_ENTITY]: ...

5646

5647 @overload

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

5649

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

5651

5652 def filter_by(self, **kwargs: Any) -> Self:

5653 r"""Apply the given filtering criterion as a WHERE clause

5654 to this select, using keyword expressions.

5655

5656 E.g.::

5657

5658 stmt = select(User).filter_by(name="some name")

5659

5660 Multiple criteria may be specified as comma separated; the effect

5661 is that they will be joined together using the :func:`.and_`

5662 function::

5663

5664 stmt = select(User).filter_by(name="some name", id=5)

5665

5666 The keyword expressions are extracted by searching across **all

5667 entities present in the FROM clause** of the statement. If a

5668 keyword name is present in more than one entity,

5669 :class:`_exc.AmbiguousColumnError` is raised. In this case, use

5670 :meth:`_sql.Select.filter` or :meth:`_sql.Select.where` with

5671 explicit column references::

5672

5673 # both User and Address have an 'id' attribute

5674 stmt = select(User).join(Address).filter_by(id=5)

5675 # raises AmbiguousColumnError

5676

5677 # use filter() with explicit qualification instead

5678 stmt = select(User).join(Address).filter(Address.id == 5)

5679

5680 .. versionchanged:: 2.1

5681

5682 :meth:`_sql.Select.filter_by` now searches across all FROM clause

5683 entities rather than only searching the last joined entity or first

5684 FROM entity. This allows the method to locate attributes

5685 unambiguously across multiple joined tables. The new

5686 :class:`_exc.AmbiguousColumnError` is raised when an attribute name

5687 is present in more than one entity.

5688

5689 See :ref:`change_8601` for migration notes.

5690

5691 .. seealso::

5692

5693 :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial`

5694

5695 :meth:`_sql.Select.filter` - filter on SQL expressions.

5696

5697 :meth:`_sql.Select.where` - filter on SQL expressions.

5698

5699 """

5700 # Get all entities via plugin system

5701 all_entities = SelectState.get_plugin_class(

5702 self

5703 )._get_filter_by_entities(self)

5704

5705 clauses = [

5706 _entity_namespace_key_search_all(all_entities, key) == value

5707 for key, value in kwargs.items()

5708 ]

5709 return self.filter(*clauses)

5710

5711 @property

5712 def column_descriptions(self) -> Any:

5713 """Return a :term:`plugin-enabled` 'column descriptions' structure

5714 referring to the columns which are SELECTed by this statement.

5715

5716 This attribute is generally useful when using the ORM, as an

5717 extended structure which includes information about mapped

5718 entities is returned. The section :ref:`queryguide_inspection`

5719 contains more background.

5720

5721 For a Core-only statement, the structure returned by this accessor

5722 is derived from the same objects that are returned by the

5723 :attr:`.Select.selected_columns` accessor, formatted as a list of

5724 dictionaries which contain the keys ``name``, ``type`` and ``expr``,

5725 which indicate the column expressions to be selected::

5726

5727 >>> stmt = select(user_table)

5728 >>> stmt.column_descriptions

5729 [

5730 {

5731 'name': 'id',

5732 'type': Integer(),

5733 'expr': Column('id', Integer(), ...)},

5734 {

5735 'name': 'name',

5736 'type': String(length=30),

5737 'expr': Column('name', String(length=30), ...)}

5738 ]

5739

5740 .. versionchanged:: 1.4.33 The :attr:`.Select.column_descriptions`

5741 attribute returns a structure for a Core-only set of entities,

5742 not just ORM-only entities.

5743

5744 .. seealso::

5745

5746 :attr:`.UpdateBase.entity_description` - entity information for

5747 an :func:`.insert`, :func:`.update`, or :func:`.delete`

5748

5749 :ref:`queryguide_inspection` - ORM background

5750

5751 """

5752 meth = SelectState.get_plugin_class(self).get_column_descriptions

5753 return meth(self)

5754

5755 def from_statement(

5756 self, statement: roles.ReturnsRowsRole

5757 ) -> ExecutableReturnsRows:

5758 """Apply the columns which this :class:`.Select` would select

5759 onto another statement.

5760

5761 This operation is :term:`plugin-specific` and will raise a not

5762 supported exception if this :class:`_sql.Select` does not select from

5763 plugin-enabled entities.

5764

5765

5766 The statement is typically either a :func:`_expression.text` or

5767 :func:`_expression.select` construct, and should return the set of

5768 columns appropriate to the entities represented by this

5769 :class:`.Select`.

5770

5771 .. seealso::

5772

5773 :ref:`orm_queryguide_selecting_text` - usage examples in the

5774 ORM Querying Guide

5775

5776 """

5777 meth = SelectState.get_plugin_class(self).from_statement

5778 return meth(self, statement)

5779

5780 @_generative

5781 def join(

5782 self,

5783 target: _JoinTargetArgument,

5784 onclause: Optional[_OnClauseArgument] = None,

5785 *,

5786 isouter: bool = False,

5787 full: bool = False,

5788 ) -> Self:

5789 r"""Create a SQL JOIN against this :class:`_expression.Select`

5790 object's criterion

5791 and apply generatively, returning the newly resulting

5792 :class:`_expression.Select`.

5793

5794 E.g.::

5795

5796 stmt = select(user_table).join(

5797 address_table, user_table.c.id == address_table.c.user_id

5798 )

5799

5800 The above statement generates SQL similar to:

5801

5802 .. sourcecode:: sql

5803

5804 SELECT user.id, user.name

5805 FROM user

5806 JOIN address ON user.id = address.user_id

5807

5808 .. versionchanged:: 1.4 :meth:`_expression.Select.join` now creates

5809 a :class:`_sql.Join` object between a :class:`_sql.FromClause`

5810 source that is within the FROM clause of the existing SELECT,

5811 and a given target :class:`_sql.FromClause`, and then adds

5812 this :class:`_sql.Join` to the FROM clause of the newly generated

5813 SELECT statement. This is completely reworked from the behavior

5814 in 1.3, which would instead create a subquery of the entire

5815 :class:`_expression.Select` and then join that subquery to the

5816 target.

5817

5818 This is a **backwards incompatible change** as the previous behavior

5819 was mostly useless, producing an unnamed subquery rejected by

5820 most databases in any case. The new behavior is modeled after

5821 that of the very successful :meth:`_orm.Query.join` method in the

5822 ORM, in order to support the functionality of :class:`_orm.Query`

5823 being available by using a :class:`_sql.Select` object with an

5824 :class:`_orm.Session`.

5825

5826 See the notes for this change at :ref:`change_select_join`.

5827

5828

5829 :param target: target table to join towards

5830

5831 :param onclause: ON clause of the join. If omitted, an ON clause

5832 is generated automatically based on the :class:`_schema.ForeignKey`

5833 linkages between the two tables, if one can be unambiguously

5834 determined, otherwise an error is raised.

5835

5836 :param isouter: if True, generate LEFT OUTER join. Same as

5837 :meth:`_expression.Select.outerjoin`.

5838

5839 :param full: if True, generate FULL OUTER join.

5840

5841 .. seealso::

5842

5843 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5844

5845 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5846

5847 :meth:`_expression.Select.join_from`

5848

5849 :meth:`_expression.Select.outerjoin`

5850

5851 """ # noqa: E501

5852 join_target = coercions.expect(

5853 roles.JoinTargetRole, target, apply_propagate_attrs=self

5854 )

5855 if onclause is not None:

5856 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5857 else:

5858 onclause_element = None

5859

5860 self._setup_joins += (

5861 (

5862 join_target,

5863 onclause_element,

5864 None,

5865 {"isouter": isouter, "full": full},

5866 ),

5867 )

5868 return self

5869

5870 def outerjoin_from(

5871 self,

5872 from_: _FromClauseArgument,

5873 target: _JoinTargetArgument,

5874 onclause: Optional[_OnClauseArgument] = None,

5875 *,

5876 full: bool = False,

5877 ) -> Self:

5878 r"""Create a SQL LEFT OUTER JOIN against this

5879 :class:`_expression.Select` object's criterion and apply generatively,

5880 returning the newly resulting :class:`_expression.Select`.

5881

5882 Usage is the same as that of :meth:`_selectable.Select.join_from`.

5883

5884 """

5885 return self.join_from(

5886 from_, target, onclause=onclause, isouter=True, full=full

5887 )

5888

5889 @_generative

5890 def join_from(

5891 self,

5892 from_: _FromClauseArgument,

5893 target: _JoinTargetArgument,

5894 onclause: Optional[_OnClauseArgument] = None,

5895 *,

5896 isouter: bool = False,

5897 full: bool = False,

5898 ) -> Self:

5899 r"""Create a SQL JOIN against this :class:`_expression.Select`

5900 object's criterion

5901 and apply generatively, returning the newly resulting

5902 :class:`_expression.Select`.

5903

5904 E.g.::

5905

5906 stmt = select(user_table, address_table).join_from(

5907 user_table, address_table, user_table.c.id == address_table.c.user_id

5908 )

5909

5910 The above statement generates SQL similar to:

5911

5912 .. sourcecode:: sql

5913

5914 SELECT user.id, user.name, address.id, address.email, address.user_id

5915 FROM user JOIN address ON user.id = address.user_id

5916

5917 .. versionadded:: 1.4

5918

5919 :param from\_: the left side of the join, will be rendered in the

5920 FROM clause and is roughly equivalent to using the

5921 :meth:`.Select.select_from` method.

5922

5923 :param target: target table to join towards

5924

5925 :param onclause: ON clause of the join.

5926

5927 :param isouter: if True, generate LEFT OUTER join. Same as

5928 :meth:`_expression.Select.outerjoin`.

5929

5930 :param full: if True, generate FULL OUTER join.

5931

5932 .. seealso::

5933

5934 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

5935

5936 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

5937

5938 :meth:`_expression.Select.join`

5939

5940 """ # noqa: E501

5941

5942 # note the order of parsing from vs. target is important here, as we

5943 # are also deriving the source of the plugin (i.e. the subject mapper

5944 # in an ORM query) which should favor the "from_" over the "target"

5945

5946 from_ = coercions.expect(

5947 roles.FromClauseRole, from_, apply_propagate_attrs=self

5948 )

5949 join_target = coercions.expect(

5950 roles.JoinTargetRole, target, apply_propagate_attrs=self

5951 )

5952 if onclause is not None:

5953 onclause_element = coercions.expect(roles.OnClauseRole, onclause)

5954 else:

5955 onclause_element = None

5956

5957 self._setup_joins += (

5958 (

5959 join_target,

5960 onclause_element,

5961 from_,

5962 {"isouter": isouter, "full": full},

5963 ),

5964 )

5965 return self

5966

5967 def outerjoin(

5968 self,

5969 target: _JoinTargetArgument,

5970 onclause: Optional[_OnClauseArgument] = None,

5971 *,

5972 full: bool = False,

5973 ) -> Self:

5974 """Create a left outer join.

5975

5976 Parameters are the same as that of :meth:`_expression.Select.join`.

5977

5978 .. versionchanged:: 1.4 :meth:`_expression.Select.outerjoin` now

5979 creates a :class:`_sql.Join` object between a

5980 :class:`_sql.FromClause` source that is within the FROM clause of

5981 the existing SELECT, and a given target :class:`_sql.FromClause`,

5982 and then adds this :class:`_sql.Join` to the FROM clause of the

5983 newly generated SELECT statement. This is completely reworked

5984 from the behavior in 1.3, which would instead create a subquery of

5985 the entire

5986 :class:`_expression.Select` and then join that subquery to the

5987 target.

5988

5989 This is a **backwards incompatible change** as the previous behavior

5990 was mostly useless, producing an unnamed subquery rejected by

5991 most databases in any case. The new behavior is modeled after

5992 that of the very successful :meth:`_orm.Query.join` method in the

5993 ORM, in order to support the functionality of :class:`_orm.Query`

5994 being available by using a :class:`_sql.Select` object with an

5995 :class:`_orm.Session`.

5996

5997 See the notes for this change at :ref:`change_select_join`.

5998

5999 .. seealso::

6000

6001 :ref:`tutorial_select_join` - in the :doc:`/tutorial/index`

6002

6003 :ref:`orm_queryguide_joins` - in the :ref:`queryguide_toplevel`

6004

6005 :meth:`_expression.Select.join`

6006

6007 """

6008 return self.join(target, onclause=onclause, isouter=True, full=full)

6009

6010 def get_final_froms(self) -> Sequence[FromClause]:

6011 """Compute the final displayed list of :class:`_expression.FromClause`

6012 elements.

6013

6014 This method will run through the full computation required to

6015 determine what FROM elements will be displayed in the resulting

6016 SELECT statement, including shadowing individual tables with

6017 JOIN objects, as well as full computation for ORM use cases including

6018 eager loading clauses.

6019

6020 For ORM use, this accessor returns the **post compilation**

6021 list of FROM objects; this collection will include elements such as

6022 eagerly loaded tables and joins. The objects will **not** be

6023 ORM enabled and not work as a replacement for the

6024 :meth:`_sql.Select.select_froms` collection; additionally, the

6025 method is not well performing for an ORM enabled statement as it

6026 will incur the full ORM construction process.

6027

6028 To retrieve the FROM list that's implied by the "columns" collection

6029 passed to the :class:`_sql.Select` originally, use the

6030 :attr:`_sql.Select.columns_clause_froms` accessor.

6031

6032 To select from an alternative set of columns while maintaining the

6033 FROM list, use the :meth:`_sql.Select.with_only_columns` method and

6034 pass the

6035 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6036 parameter.

6037

6038 .. versionadded:: 1.4.23 - the :meth:`_sql.Select.get_final_froms`

6039 method replaces the previous :attr:`_sql.Select.froms` accessor,

6040 which is deprecated.

6041

6042 .. seealso::

6043

6044 :attr:`_sql.Select.columns_clause_froms`

6045

6046 """

6047 compiler = self._default_compiler()

6048

6049 return self._compile_state_factory(self, compiler)._get_display_froms()

6050

6051 @property

6052 @util.deprecated(

6053 "1.4.23",

6054 "The :attr:`_expression.Select.froms` attribute is moved to "

6055 "the :meth:`_expression.Select.get_final_froms` method.",

6056 )

6057 def froms(self) -> Sequence[FromClause]:

6058 """Return the displayed list of :class:`_expression.FromClause`

6059 elements.

6060

6061

6062 """

6063 return self.get_final_froms()

6064

6065 @property

6066 def columns_clause_froms(self) -> List[FromClause]:

6067 """Return the set of :class:`_expression.FromClause` objects implied

6068 by the columns clause of this SELECT statement.

6069

6070 .. versionadded:: 1.4.23

6071

6072 .. seealso::

6073

6074 :attr:`_sql.Select.froms` - "final" FROM list taking the full

6075 statement into account

6076

6077 :meth:`_sql.Select.with_only_columns` - makes use of this

6078 collection to set up a new FROM list

6079

6080 """

6081

6082 return SelectState.get_plugin_class(self).get_columns_clause_froms(

6083 self

6084 )

6085

6086 @property

6087 def inner_columns(self) -> _SelectIterable:

6088 """An iterator of all :class:`_expression.ColumnElement`

6089 expressions which would

6090 be rendered into the columns clause of the resulting SELECT statement.

6091

6092 This method is legacy as of 1.4 and is superseded by the

6093 :attr:`_expression.Select.exported_columns` collection.

6094

6095 """

6096

6097 return iter(self._all_selected_columns)

6098

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

6100 if fromclause is not None and self in fromclause._cloned_set:

6101 return True

6102

6103 for f in self._iterate_from_elements():

6104 if f.is_derived_from(fromclause):

6105 return True

6106 return False

6107

6108 def _copy_internals(

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

6110 ) -> None:

6111 # Select() object has been cloned and probably adapted by the

6112 # given clone function. Apply the cloning function to internal

6113 # objects

6114

6115 # 1. keep a dictionary of the froms we've cloned, and what

6116 # they've become. This allows us to ensure the same cloned from

6117 # is used when other items such as columns are "cloned"

6118

6119 all_the_froms = set(

6120 itertools.chain(

6121 _from_objects(*self._raw_columns),

6122 _from_objects(*self._where_criteria),

6123 _from_objects(*[elem[0] for elem in self._setup_joins]),

6124 )

6125 )

6126

6127 # do a clone for the froms we've gathered. what is important here

6128 # is if any of the things we are selecting from, like tables,

6129 # were converted into Join objects. if so, these need to be

6130 # added to _from_obj explicitly, because otherwise they won't be

6131 # part of the new state, as they don't associate themselves with

6132 # their columns.

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

6134

6135 # 2. copy FROM collections, adding in joins that we've created.

6136 existing_from_obj = [clone(f, **kw) for f in self._from_obj]

6137 add_froms = (

6138 {f for f in new_froms.values() if isinstance(f, Join)}

6139 .difference(all_the_froms)

6140 .difference(existing_from_obj)

6141 )

6142

6143 self._from_obj = tuple(existing_from_obj) + tuple(add_froms)

6144

6145 # 3. clone everything else, making sure we use columns

6146 # corresponding to the froms we just made.

6147 def replace(

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

6149 **kw: Any,

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

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

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

6153 return newelem

6154 return None

6155

6156 kw["replace"] = replace

6157

6158 # copy everything else. for table-ish things like correlate,

6159 # correlate_except, setup_joins, these clone normally. For

6160 # column-expression oriented things like raw_columns, where_criteria,

6161 # order by, we get this from the new froms.

6162 super()._copy_internals(clone=clone, omit_attrs=("_from_obj",), **kw)

6163

6164 self._reset_memoizations()

6165

6166 def get_children(self, **kw: Any) -> Iterable[ClauseElement]:

6167 return itertools.chain(

6168 super().get_children(

6169 omit_attrs=("_from_obj", "_correlate", "_correlate_except"),

6170 **kw,

6171 ),

6172 self._iterate_from_elements(),

6173 )

6174

6175 @_generative

6176 def add_columns(

6177 self, *entities: _ColumnsClauseArgument[Any]

6178 ) -> Select[Unpack[TupleAny]]:

6179 r"""Return a new :func:`_expression.select` construct with

6180 the given entities appended to its columns clause.

6181

6182 E.g.::

6183

6184 my_select = my_select.add_columns(table.c.new_column)

6185

6186 The original expressions in the columns clause remain in place.

6187 To replace the original expressions with new ones, see the method

6188 :meth:`_expression.Select.with_only_columns`.

6189

6190 :param \*entities: column, table, or other entity expressions to be

6191 added to the columns clause

6192

6193 .. seealso::

6194

6195 :meth:`_expression.Select.with_only_columns` - replaces existing

6196 expressions rather than appending.

6197

6198 :ref:`orm_queryguide_select_multiple_entities` - ORM-centric

6199 example

6200

6201 """

6202 self._reset_memoizations()

6203

6204 self._raw_columns = self._raw_columns + [

6205 coercions.expect(

6206 roles.ColumnsClauseRole, column, apply_propagate_attrs=self

6207 )

6208 for column in entities

6209 ]

6210 return self

6211

6212 def _set_entities(

6213 self, entities: Iterable[_ColumnsClauseArgument[Any]]

6214 ) -> None:

6215 self._raw_columns = [

6216 coercions.expect(

6217 roles.ColumnsClauseRole, ent, apply_propagate_attrs=self

6218 )

6219 for ent in util.to_list(entities)

6220 ]

6221

6222 @util.deprecated(

6223 "1.4",

6224 "The :meth:`_expression.Select.column` method is deprecated and will "

6225 "be removed in a future release. Please use "

6226 ":meth:`_expression.Select.add_columns`",

6227 )

6228 def column(

6229 self, column: _ColumnsClauseArgument[Any]

6230 ) -> Select[Unpack[TupleAny]]:

6231 """Return a new :func:`_expression.select` construct with

6232 the given column expression added to its columns clause.

6233

6234 E.g.::

6235

6236 my_select = my_select.column(table.c.new_column)

6237

6238 See the documentation for

6239 :meth:`_expression.Select.with_only_columns`

6240 for guidelines on adding /replacing the columns of a

6241 :class:`_expression.Select` object.

6242

6243 """

6244 return self.add_columns(column)

6245

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

6247 def reduce_columns(

6248 self, only_synonyms: bool = True

6249 ) -> Select[Unpack[TupleAny]]:

6250 """Return a new :func:`_expression.select` construct with redundantly

6251 named, equivalently-valued columns removed from the columns clause.

6252

6253 "Redundant" here means two columns where one refers to the

6254 other either based on foreign key, or via a simple equality

6255 comparison in the WHERE clause of the statement. The primary purpose

6256 of this method is to automatically construct a select statement

6257 with all uniquely-named columns, without the need to use

6258 table-qualified labels as

6259 :meth:`_expression.Select.set_label_style`

6260 does.

6261

6262 When columns are omitted based on foreign key, the referred-to

6263 column is the one that's kept. When columns are omitted based on

6264 WHERE equivalence, the first column in the columns clause is the

6265 one that's kept.

6266

6267 :param only_synonyms: when True, limit the removal of columns

6268 to those which have the same name as the equivalent. Otherwise,

6269 all columns that are equivalent to another are removed.

6270

6271 """

6272 woc: Select[Unpack[TupleAny]]

6273 woc = self.with_only_columns(

6274 *util.preloaded.sql_util.reduce_columns(

6275 self._all_selected_columns,

6276 only_synonyms=only_synonyms,

6277 *(self._where_criteria + self._from_obj),

6278 )

6279 )

6280 return woc

6281

6282 # START OVERLOADED FUNCTIONS self.with_only_columns Select 1-8 ", *, maintain_column_froms: bool =..." # noqa: E501

6283

6284 # code within this block is **programmatically,

6285 # statically generated** by tools/generate_tuple_map_overloads.py

6286

6287 @overload

6288 def with_only_columns(

6289 self, __ent0: _TCCA[_T0], /, *, maintain_column_froms: bool = ...

6290 ) -> Select[_T0]: ...

6291

6292 @overload

6293 def with_only_columns(

6294 self,

6295 __ent0: _TCCA[_T0],

6296 __ent1: _TCCA[_T1],

6297 /,

6298 *,

6299 maintain_column_froms: bool = ...,

6300 ) -> Select[_T0, _T1]: ...

6301

6302 @overload

6303 def with_only_columns(

6304 self,

6305 __ent0: _TCCA[_T0],

6306 __ent1: _TCCA[_T1],

6307 __ent2: _TCCA[_T2],

6308 /,

6309 *,

6310 maintain_column_froms: bool = ...,

6311 ) -> Select[_T0, _T1, _T2]: ...

6312

6313 @overload

6314 def with_only_columns(

6315 self,

6316 __ent0: _TCCA[_T0],

6317 __ent1: _TCCA[_T1],

6318 __ent2: _TCCA[_T2],

6319 __ent3: _TCCA[_T3],

6320 /,

6321 *,

6322 maintain_column_froms: bool = ...,

6323 ) -> Select[_T0, _T1, _T2, _T3]: ...

6324

6325 @overload

6326 def with_only_columns(

6327 self,

6328 __ent0: _TCCA[_T0],

6329 __ent1: _TCCA[_T1],

6330 __ent2: _TCCA[_T2],

6331 __ent3: _TCCA[_T3],

6332 __ent4: _TCCA[_T4],

6333 /,

6334 *,

6335 maintain_column_froms: bool = ...,

6336 ) -> Select[_T0, _T1, _T2, _T3, _T4]: ...

6337

6338 @overload

6339 def with_only_columns(

6340 self,

6341 __ent0: _TCCA[_T0],

6342 __ent1: _TCCA[_T1],

6343 __ent2: _TCCA[_T2],

6344 __ent3: _TCCA[_T3],

6345 __ent4: _TCCA[_T4],

6346 __ent5: _TCCA[_T5],

6347 /,

6348 *,

6349 maintain_column_froms: bool = ...,

6350 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5]: ...

6351

6352 @overload

6353 def with_only_columns(

6354 self,

6355 __ent0: _TCCA[_T0],

6356 __ent1: _TCCA[_T1],

6357 __ent2: _TCCA[_T2],

6358 __ent3: _TCCA[_T3],

6359 __ent4: _TCCA[_T4],

6360 __ent5: _TCCA[_T5],

6361 __ent6: _TCCA[_T6],

6362 /,

6363 *,

6364 maintain_column_froms: bool = ...,

6365 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6]: ...

6366

6367 @overload

6368 def with_only_columns(

6369 self,

6370 __ent0: _TCCA[_T0],

6371 __ent1: _TCCA[_T1],

6372 __ent2: _TCCA[_T2],

6373 __ent3: _TCCA[_T3],

6374 __ent4: _TCCA[_T4],

6375 __ent5: _TCCA[_T5],

6376 __ent6: _TCCA[_T6],

6377 __ent7: _TCCA[_T7],

6378 /,

6379 *entities: _ColumnsClauseArgument[Any],

6380 maintain_column_froms: bool = ...,

6381 ) -> Select[_T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7, Unpack[TupleAny]]: ...

6382

6383 # END OVERLOADED FUNCTIONS self.with_only_columns

6384

6385 @overload

6386 def with_only_columns(

6387 self,

6388 *entities: _ColumnsClauseArgument[Any],

6389 maintain_column_froms: bool = False,

6390 **__kw: Any,

6391 ) -> Select[Unpack[TupleAny]]: ...

6392

6393 @_generative

6394 def with_only_columns(

6395 self,

6396 *entities: _ColumnsClauseArgument[Any],

6397 maintain_column_froms: bool = False,

6398 **__kw: Any,

6399 ) -> Select[Unpack[TupleAny]]:

6400 r"""Return a new :func:`_expression.select` construct with its columns

6401 clause replaced with the given entities.

6402

6403 By default, this method is exactly equivalent to as if the original

6404 :func:`_expression.select` had been called with the given entities.

6405 E.g. a statement::

6406

6407 s = select(table1.c.a, table1.c.b)

6408 s = s.with_only_columns(table1.c.b)

6409

6410 should be exactly equivalent to::

6411

6412 s = select(table1.c.b)

6413

6414 In this mode of operation, :meth:`_sql.Select.with_only_columns`

6415 will also dynamically alter the FROM clause of the

6416 statement if it is not explicitly stated.

6417 To maintain the existing set of FROMs including those implied by the

6418 current columns clause, add the

6419 :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6420 parameter::

6421

6422 s = select(table1.c.a, table2.c.b)

6423 s = s.with_only_columns(table1.c.a, maintain_column_froms=True)

6424

6425 The above parameter performs a transfer of the effective FROMs

6426 in the columns collection to the :meth:`_sql.Select.select_from`

6427 method, as though the following were invoked::

6428

6429 s = select(table1.c.a, table2.c.b)

6430 s = s.select_from(table1, table2).with_only_columns(table1.c.a)

6431

6432 The :paramref:`_sql.Select.with_only_columns.maintain_column_froms`

6433 parameter makes use of the :attr:`_sql.Select.columns_clause_froms`

6434 collection and performs an operation equivalent to the following::

6435

6436 s = select(table1.c.a, table2.c.b)

6437 s = s.select_from(*s.columns_clause_froms).with_only_columns(table1.c.a)

6438

6439 :param \*entities: column expressions to be used.

6440

6441 :param maintain_column_froms: boolean parameter that will ensure the

6442 FROM list implied from the current columns clause will be transferred

6443 to the :meth:`_sql.Select.select_from` method first.

6444

6445 .. versionadded:: 1.4.23

6446

6447 """ # noqa: E501

6448

6449 if __kw:

6450 raise _no_kw()

6451

6452 # memoizations should be cleared here as of

6453 # I95c560ffcbfa30b26644999412fb6a385125f663 , asserting this

6454 # is the case for now.

6455 self._assert_no_memoizations()

6456

6457 if maintain_column_froms:

6458 self.select_from.non_generative( # type: ignore

6459 self, *self.columns_clause_froms

6460 )

6461

6462 # then memoize the FROMs etc.

6463 _MemoizedSelectEntities._generate_for_statement(self)

6464

6465 self._raw_columns = [

6466 coercions.expect(roles.ColumnsClauseRole, c)

6467 for c in coercions._expression_collection_was_a_list(

6468 "entities", "Select.with_only_columns", entities

6469 )

6470 ]

6471 return self

6472

6473 @property

6474 def whereclause(self) -> Optional[ColumnElement[Any]]:

6475 """Return the completed WHERE clause for this

6476 :class:`_expression.Select` statement.

6477

6478 This assembles the current collection of WHERE criteria

6479 into a single :class:`_expression.BooleanClauseList` construct.

6480

6481

6482 .. versionadded:: 1.4

6483

6484 """

6485

6486 return BooleanClauseList._construct_for_whereclause(

6487 self._where_criteria

6488 )

6489

6490 _whereclause = whereclause

6491

6492 @_generative

6493 def where(self, *whereclause: _ColumnExpressionArgument[bool]) -> Self:

6494 """Return a new :func:`_expression.select` construct with

6495 the given expression added to

6496 its WHERE clause, joined to the existing clause via AND, if any.

6497

6498 """

6499

6500 assert isinstance(self._where_criteria, tuple)

6501

6502 for criterion in whereclause:

6503 where_criteria: ColumnElement[Any] = coercions.expect(

6504 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6505 )

6506 self._where_criteria += (where_criteria,)

6507 return self

6508

6509 @_generative

6510 def having(self, *having: _ColumnExpressionArgument[bool]) -> Self:

6511 """Return a new :func:`_expression.select` construct with

6512 the given expression added to

6513 its HAVING clause, joined to the existing clause via AND, if any.

6514

6515 """

6516

6517 for criterion in having:

6518 having_criteria = coercions.expect(

6519 roles.WhereHavingRole, criterion, apply_propagate_attrs=self

6520 )

6521 self._having_criteria += (having_criteria,)

6522 return self

6523

6524 @_generative

6525 def distinct(self, *expr: _ColumnExpressionArgument[Any]) -> Self:

6526 r"""Return a new :func:`_expression.select` construct which

6527 will apply DISTINCT to the SELECT statement overall.

6528

6529 E.g.::

6530

6531 from sqlalchemy import select

6532

6533 stmt = select(users_table.c.id, users_table.c.name).distinct()

6534

6535 The above would produce an statement resembling:

6536

6537 .. sourcecode:: sql

6538

6539 SELECT DISTINCT user.id, user.name FROM user

6540

6541 The method also historically accepted an ``*expr`` parameter which

6542 produced the PostgreSQL dialect-specific ``DISTINCT ON`` expression.

6543 This is now replaced using the :func:`_postgresql.distinct_on`

6544 extension::

6545

6546 from sqlalchemy import select

6547 from sqlalchemy.dialects.postgresql import distinct_on

6548

6549 stmt = select(users_table).ext(distinct_on(users_table.c.name))

6550

6551 Using this parameter on other backends which don't support this

6552 syntax will raise an error.

6553

6554 :param \*expr: optional column expressions. When present,

6555 the PostgreSQL dialect will render a ``DISTINCT ON (<expressions>)``

6556 construct. A deprecation warning and/or :class:`_exc.CompileError`

6557 will be raised on other backends.

6558

6559 .. deprecated:: 2.1 Passing expressions to

6560 :meth:`_sql.Select.distinct` is deprecated, use

6561 :func:`_postgresql.distinct_on` instead.

6562

6563 .. deprecated:: 1.4 Using \*expr in other dialects is deprecated

6564 and will raise :class:`_exc.CompileError` in a future version.

6565

6566 .. seealso::

6567

6568 :func:`_postgresql.distinct_on`

6569

6570 :meth:`.ext`

6571 """

6572 self._distinct = True

6573 if expr:

6574 warn_deprecated(

6575 "Passing expression to ``distinct`` to generate a "

6576 "DISTINCT ON clause is deprecated. Use instead the "

6577 "``postgresql.distinct_on`` function as an extension.",

6578 "2.1",

6579 )

6580 self._distinct_on = self._distinct_on + tuple(

6581 coercions.expect(roles.ByOfRole, e, apply_propagate_attrs=self)

6582 for e in expr

6583 )

6584 return self

6585

6586 @_generative

6587 def select_from(self, *froms: _FromClauseArgument) -> Self:

6588 r"""Return a new :func:`_expression.select` construct with the

6589 given FROM expression(s)

6590 merged into its list of FROM objects.

6591

6592 E.g.::

6593

6594 table1 = table("t1", column("a"))

6595 table2 = table("t2", column("b"))

6596 s = select(table1.c.a).select_from(

6597 table1.join(table2, table1.c.a == table2.c.b)

6598 )

6599

6600 The "from" list is a unique set on the identity of each element,

6601 so adding an already present :class:`_schema.Table`

6602 or other selectable

6603 will have no effect. Passing a :class:`_expression.Join` that refers

6604 to an already present :class:`_schema.Table`

6605 or other selectable will have

6606 the effect of concealing the presence of that selectable as

6607 an individual element in the rendered FROM list, instead

6608 rendering it into a JOIN clause.

6609

6610 While the typical purpose of :meth:`_expression.Select.select_from`

6611 is to

6612 replace the default, derived FROM clause with a join, it can

6613 also be called with individual table elements, multiple times

6614 if desired, in the case that the FROM clause cannot be fully

6615 derived from the columns clause::

6616

6617 select(func.count("*")).select_from(table1)

6618

6619 """

6620

6621 self._from_obj += tuple(

6622 coercions.expect(

6623 roles.FromClauseRole, fromclause, apply_propagate_attrs=self

6624 )

6625 for fromclause in froms

6626 )

6627 return self

6628

6629 @_generative

6630 def correlate(

6631 self,

6632 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6633 ) -> Self:

6634 r"""Return a new :class:`_expression.Select`

6635 which will correlate the given FROM

6636 clauses to that of an enclosing :class:`_expression.Select`.

6637

6638 Calling this method turns off the :class:`_expression.Select` object's

6639 default behavior of "auto-correlation". Normally, FROM elements

6640 which appear in a :class:`_expression.Select`

6641 that encloses this one via

6642 its :term:`WHERE clause`, ORDER BY, HAVING or

6643 :term:`columns clause` will be omitted from this

6644 :class:`_expression.Select`

6645 object's :term:`FROM clause`.

6646 Setting an explicit correlation collection using the

6647 :meth:`_expression.Select.correlate`

6648 method provides a fixed list of FROM objects

6649 that can potentially take place in this process.

6650

6651 When :meth:`_expression.Select.correlate`

6652 is used to apply specific FROM clauses

6653 for correlation, the FROM elements become candidates for

6654 correlation regardless of how deeply nested this

6655 :class:`_expression.Select`

6656 object is, relative to an enclosing :class:`_expression.Select`

6657 which refers to

6658 the same FROM object. This is in contrast to the behavior of

6659 "auto-correlation" which only correlates to an immediate enclosing

6660 :class:`_expression.Select`.

6661 Multi-level correlation ensures that the link

6662 between enclosed and enclosing :class:`_expression.Select`

6663 is always via

6664 at least one WHERE/ORDER BY/HAVING/columns clause in order for

6665 correlation to take place.

6666

6667 If ``None`` is passed, the :class:`_expression.Select`

6668 object will correlate

6669 none of its FROM entries, and all will render unconditionally

6670 in the local FROM clause.

6671

6672 :param \*fromclauses: one or more :class:`.FromClause` or other

6673 FROM-compatible construct such as an ORM mapped entity to become part

6674 of the correlate collection; alternatively pass a single value

6675 ``None`` to remove all existing correlations.

6676

6677 .. seealso::

6678

6679 :meth:`_expression.Select.correlate_except`

6680

6681 :ref:`tutorial_scalar_subquery`

6682

6683 """

6684

6685 # tests failing when we try to change how these

6686 # arguments are passed

6687

6688 self._auto_correlate = False

6689 if not fromclauses or fromclauses[0] in {None, False}:

6690 if len(fromclauses) > 1:

6691 raise exc.ArgumentError(

6692 "additional FROM objects not accepted when "

6693 "passing None/False to correlate()"

6694 )

6695 self._correlate = ()

6696 else:

6697 self._correlate = self._correlate + tuple(

6698 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6699 )

6700 return self

6701

6702 @_generative

6703 def correlate_except(

6704 self,

6705 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

6706 ) -> Self:

6707 r"""Return a new :class:`_expression.Select`

6708 which will omit the given FROM

6709 clauses from the auto-correlation process.

6710

6711 Calling :meth:`_expression.Select.correlate_except` turns off the

6712 :class:`_expression.Select` object's default behavior of

6713 "auto-correlation" for the given FROM elements. An element

6714 specified here will unconditionally appear in the FROM list, while

6715 all other FROM elements remain subject to normal auto-correlation

6716 behaviors.

6717

6718 If ``None`` is passed, or no arguments are passed,

6719 the :class:`_expression.Select` object will correlate all of its

6720 FROM entries.

6721

6722 :param \*fromclauses: a list of one or more

6723 :class:`_expression.FromClause`

6724 constructs, or other compatible constructs (i.e. ORM-mapped

6725 classes) to become part of the correlate-exception collection.

6726

6727 .. seealso::

6728

6729 :meth:`_expression.Select.correlate`

6730

6731 :ref:`tutorial_scalar_subquery`

6732

6733 """

6734

6735 self._auto_correlate = False

6736 if not fromclauses or fromclauses[0] in {None, False}:

6737 if len(fromclauses) > 1:

6738 raise exc.ArgumentError(

6739 "additional FROM objects not accepted when "

6740 "passing None/False to correlate_except()"

6741 )

6742 self._correlate_except = ()

6743 else:

6744 self._correlate_except = (self._correlate_except or ()) + tuple(

6745 coercions.expect(roles.FromClauseRole, f) for f in fromclauses

6746 )

6747

6748 return self

6749

6750 @HasMemoized_ro_memoized_attribute

6751 def selected_columns(

6752 self,

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

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

6755 representing the columns that

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

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

6758

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

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

6761 within this collection cannot be directly nested inside another SELECT

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

6763 necessary parenthesization required by SQL.

6764

6765 For a :func:`_expression.select` construct, the collection here is

6766 exactly what would be rendered inside the "SELECT" statement, and the

6767 :class:`_expression.ColumnElement` objects are directly present as they

6768 were given, e.g.::

6769

6770 col1 = column("q", Integer)

6771 col2 = column("p", Integer)

6772 stmt = select(col1, col2)

6773

6774 Above, ``stmt.selected_columns`` would be a collection that contains

6775 the ``col1`` and ``col2`` objects directly. For a statement that is

6776 against a :class:`_schema.Table` or other

6777 :class:`_expression.FromClause`, the collection will use the

6778 :class:`_expression.ColumnElement` objects that are in the

6779 :attr:`_expression.FromClause.c` collection of the from element.

6780

6781 A use case for the :attr:`_sql.Select.selected_columns` collection is

6782 to allow the existing columns to be referenced when adding additional

6783 criteria, e.g.::

6784

6785 def filter_on_id(my_select, id):

6786 return my_select.where(my_select.selected_columns["id"] == id)

6787

6788

6789 stmt = select(MyModel)

6790

6791 # adds "WHERE id=:param" to the statement

6792 stmt = filter_on_id(stmt, 42)

6793

6794 .. note::

6795

6796 The :attr:`_sql.Select.selected_columns` collection does not

6797 include expressions established in the columns clause using the

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

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

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

6801 construct.

6802

6803

6804 .. versionadded:: 1.4

6805

6806 """

6807

6808 # compare to SelectState._generate_columns_plus_names, which

6809 # generates the actual names used in the SELECT string. that

6810 # method is more complex because it also renders columns that are

6811 # fully ambiguous, e.g. same column more than once.

6812 conv = cast(

6813 "Callable[[Any], str]",

6814 SelectState._column_naming_convention(self._label_style),

6815 )

6816

6817 cc: WriteableColumnCollection[str, ColumnElement[Any]] = (

6818 WriteableColumnCollection(

6819 [

6820 (conv(c), c)

6821 for c in self._all_selected_columns

6822 if is_column_element(c)

6823 ]

6824 )

6825 )

6826 return cc.as_readonly()

6827

6828 @HasMemoized_ro_memoized_attribute

6829 def _all_selected_columns(self) -> _SelectIterable:

6830 meth = SelectState.get_plugin_class(self).all_selected_columns

6831 return list(meth(self))

6832

6833 def _ensure_disambiguated_names(self) -> Select[Unpack[TupleAny]]:

6834 if self._label_style is LABEL_STYLE_NONE:

6835 self = self.set_label_style(LABEL_STYLE_DISAMBIGUATE_ONLY)

6836 return self

6837

6838 def _generate_fromclause_column_proxies(

6839 self,

6840 subquery: FromClause,

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

6842 primary_key: ColumnSet,

6843 foreign_keys: Set[KeyedColumnElement[Any]],

6844 *,

6845 proxy_compound_columns: Optional[

6846 Iterable[Sequence[ColumnElement[Any]]]

6847 ] = None,

6848 ) -> None:

6849 """Generate column proxies to place in the exported ``.c``

6850 collection of a subquery."""

6851

6852 if proxy_compound_columns:

6853 extra_col_iterator = proxy_compound_columns

6854 prox = [

6855 c._make_proxy(

6856 subquery,

6857 key=proxy_key,

6858 name=required_label_name,

6859 name_is_truncatable=True,

6860 compound_select_cols=extra_cols,

6861 primary_key=primary_key,

6862 foreign_keys=foreign_keys,

6863 )

6864 for (

6865 (

6866 required_label_name,

6867 proxy_key,

6868 fallback_label_name,

6869 c,

6870 repeated,

6871 ),

6872 extra_cols,

6873 ) in (

6874 zip(

6875 self._generate_columns_plus_names(False),

6876 extra_col_iterator,

6877 )

6878 )

6879 if is_column_element(c)

6880 ]

6881 else:

6882 prox = [

6883 c._make_proxy(

6884 subquery,

6885 key=proxy_key,

6886 name=required_label_name,

6887 name_is_truncatable=True,

6888 primary_key=primary_key,

6889 foreign_keys=foreign_keys,

6890 )

6891 for (

6892 required_label_name,

6893 proxy_key,

6894 fallback_label_name,

6895 c,

6896 repeated,

6897 ) in (self._generate_columns_plus_names(False))

6898 if is_column_element(c)

6899 ]

6900

6901 columns._populate_separate_keys(prox)

6902

6903 def _needs_parens_for_grouping(self) -> bool:

6904 return self._has_row_limiting_clause or bool(

6905 self._order_by_clause.clauses

6906 )

6907

6908 def self_group(

6909 self, against: Optional[OperatorType] = None

6910 ) -> Union[SelectStatementGrouping[Self], Self]:

6911 """Return a 'grouping' construct as per the

6912 :class:`_expression.ClauseElement` specification.

6913

6914 This produces an element that can be embedded in an expression. Note

6915 that this method is called automatically as needed when constructing

6916 expressions and should not require explicit use.

6917

6918 """

6919 if (

6920 isinstance(against, CompoundSelect)

6921 and not self._needs_parens_for_grouping()

6922 ):

6923 return self

6924 else:

6925 return SelectStatementGrouping(self)

6926

6927 def union(

6928 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6930 r"""Return a SQL ``UNION`` of this select() construct against

6931 the given selectables provided as positional arguments.

6932

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

6934 UNION.

6935

6936 .. versionchanged:: 1.4.28

6937

6938 multiple elements are now accepted.

6939

6940 :param \**kwargs: keyword arguments are forwarded to the constructor

6941 for the newly created :class:`_sql.CompoundSelect` object.

6942

6943 """

6944 return CompoundSelect._create_union(self, *other)

6945

6946 def union_all(

6947 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6949 r"""Return a SQL ``UNION ALL`` of this select() construct against

6950 the given selectables provided as positional arguments.

6951

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

6953 UNION.

6954

6955 .. versionchanged:: 1.4.28

6956

6957 multiple elements are now accepted.

6958

6959 :param \**kwargs: keyword arguments are forwarded to the constructor

6960 for the newly created :class:`_sql.CompoundSelect` object.

6961

6962 """

6963 return CompoundSelect._create_union_all(self, *other)

6964

6965 def except_(

6966 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6968 r"""Return a SQL ``EXCEPT`` of this select() construct against

6969 the given selectable provided as positional arguments.

6970

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

6972 UNION.

6973

6974 .. versionchanged:: 1.4.28

6975

6976 multiple elements are now accepted.

6977

6978 """

6979 return CompoundSelect._create_except(self, *other)

6980

6981 def except_all(

6982 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

6984 r"""Return a SQL ``EXCEPT ALL`` of this select() construct against

6985 the given selectables provided as positional arguments.

6986

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

6988 UNION.

6989

6990 .. versionchanged:: 1.4.28

6991

6992 multiple elements are now accepted.

6993

6994 """

6995 return CompoundSelect._create_except_all(self, *other)

6996

6997 def intersect(

6998 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

7000 r"""Return a SQL ``INTERSECT`` of this select() construct against

7001 the given selectables provided as positional arguments.

7002

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

7004 UNION.

7005

7006 .. versionchanged:: 1.4.28

7007

7008 multiple elements are now accepted.

7009

7010 :param \**kwargs: keyword arguments are forwarded to the constructor

7011 for the newly created :class:`_sql.CompoundSelect` object.

7012

7013 """

7014 return CompoundSelect._create_intersect(self, *other)

7015

7016 def intersect_all(

7017 self, *other: _SelectStatementForCompoundArgument[Unpack[_Ts]]

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

7019 r"""Return a SQL ``INTERSECT ALL`` of this select() construct

7020 against the given selectables provided as positional arguments.

7021

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

7023 UNION.

7024

7025 .. versionchanged:: 1.4.28

7026

7027 multiple elements are now accepted.

7028

7029 :param \**kwargs: keyword arguments are forwarded to the constructor

7030 for the newly created :class:`_sql.CompoundSelect` object.

7031

7032 """

7033 return CompoundSelect._create_intersect_all(self, *other)

7034

7035

7036class ScalarSelect(

7037 roles.InElementRole, Generative, GroupedElement, ColumnElement[_T]

7038):

7039 """Represent a scalar subquery.

7040

7041

7042 A :class:`_sql.ScalarSelect` is created by invoking the

7043 :meth:`_sql.SelectBase.scalar_subquery` method. The object

7044 then participates in other SQL expressions as a SQL column expression

7045 within the :class:`_sql.ColumnElement` hierarchy.

7046

7047 .. seealso::

7048

7049 :meth:`_sql.SelectBase.scalar_subquery`

7050

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

7052

7053 """

7054

7055 _traverse_internals: _TraverseInternalsType = [

7056 ("element", InternalTraversal.dp_clauseelement),

7057 ("type", InternalTraversal.dp_type),

7058 ]

7059

7060 _from_objects: List[FromClause] = []

7061 _is_from_container = True

7062 if not TYPE_CHECKING:

7063 _is_implicitly_boolean = False

7064 inherit_cache = True

7065

7066 element: SelectBase

7067

7068 def __init__(self, element: SelectBase) -> None:

7069 self.element = element

7070 self.type = element._scalar_type()

7071 self._propagate_attrs = element._propagate_attrs

7072

7073 def __getattr__(self, attr: str) -> Any:

7074 return getattr(self.element, attr)

7075

7076 def __getstate__(self) -> Dict[str, Any]:

7077 return {"element": self.element, "type": self.type}

7078

7079 def __setstate__(self, state: Dict[str, Any]) -> None:

7080 self.element = state["element"]

7081 self.type = state["type"]

7082

7083 @property

7084 def columns(self) -> NoReturn:

7085 raise exc.InvalidRequestError(

7086 "Scalar Select expression has no "

7087 "columns; use this object directly "

7088 "within a column-level expression."

7089 )

7090

7091 c = columns

7092

7093 @_generative

7094 def where(self, crit: _ColumnExpressionArgument[bool]) -> Self:

7095 """Apply a WHERE clause to the SELECT statement referred to

7096 by this :class:`_expression.ScalarSelect`.

7097

7098 """

7099 self.element = cast("Select[Unpack[TupleAny]]", self.element).where(

7100 crit

7101 )

7102 return self

7103

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

7105 return self

7106

7107 def _ungroup(self) -> Self:

7108 return self

7109

7110 @_generative

7111 def correlate(

7112 self,

7113 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7114 ) -> Self:

7115 r"""Return a new :class:`_expression.ScalarSelect`

7116 which will correlate the given FROM

7117 clauses to that of an enclosing :class:`_expression.Select`.

7118

7119 This method is mirrored from the :meth:`_sql.Select.correlate` method

7120 of the underlying :class:`_sql.Select`. The method applies the

7121 :meth:_sql.Select.correlate` method, then returns a new

7122 :class:`_sql.ScalarSelect` against that statement.

7123

7124 .. versionadded:: 1.4 Previously, the

7125 :meth:`_sql.ScalarSelect.correlate`

7126 method was only available from :class:`_sql.Select`.

7127

7128 :param \*fromclauses: a list of one or more

7129 :class:`_expression.FromClause`

7130 constructs, or other compatible constructs (i.e. ORM-mapped

7131 classes) to become part of the correlate collection.

7132

7133 .. seealso::

7134

7135 :meth:`_expression.ScalarSelect.correlate_except`

7136

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

7138

7139

7140 """

7141 self.element = cast(

7142 "Select[Unpack[TupleAny]]", self.element

7143 ).correlate(*fromclauses)

7144 return self

7145

7146 @_generative

7147 def correlate_except(

7148 self,

7149 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7150 ) -> Self:

7151 r"""Return a new :class:`_expression.ScalarSelect`

7152 which will omit the given FROM

7153 clauses from the auto-correlation process.

7154

7155 This method is mirrored from the

7156 :meth:`_sql.Select.correlate_except` method of the underlying

7157 :class:`_sql.Select`. The method applies the

7158 :meth:_sql.Select.correlate_except` method, then returns a new

7159 :class:`_sql.ScalarSelect` against that statement.

7160

7161 .. versionadded:: 1.4 Previously, the

7162 :meth:`_sql.ScalarSelect.correlate_except`

7163 method was only available from :class:`_sql.Select`.

7164

7165 :param \*fromclauses: a list of one or more

7166 :class:`_expression.FromClause`

7167 constructs, or other compatible constructs (i.e. ORM-mapped

7168 classes) to become part of the correlate-exception collection.

7169

7170 .. seealso::

7171

7172 :meth:`_expression.ScalarSelect.correlate`

7173

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

7175

7176

7177 """

7178

7179 self.element = cast(

7180 "Select[Unpack[TupleAny]]", self.element

7181 ).correlate_except(*fromclauses)

7182 return self

7183

7184

7185class Exists(UnaryExpression[bool]):

7186 """Represent an ``EXISTS`` clause.

7187

7188 See :func:`_sql.exists` for a description of usage.

7189

7190 An ``EXISTS`` clause can also be constructed from a :func:`_sql.select`

7191 instance by calling :meth:`_sql.SelectBase.exists`.

7192

7193 """

7194

7195 inherit_cache = True

7196

7197 def __init__(

7198 self,

7199 __argument: Optional[

7200 Union[_ColumnsClauseArgument[Any], SelectBase, ScalarSelect[Any]]

7201 ] = None,

7202 /,

7203 ):

7204 s: ScalarSelect[Any]

7205

7206 # TODO: this seems like we should be using coercions for this

7207 if __argument is None:

7208 s = Select(literal_column("*")).scalar_subquery()

7209 elif isinstance(__argument, SelectBase):

7210 s = __argument.scalar_subquery()

7211 s._propagate_attrs = __argument._propagate_attrs

7212 elif isinstance(__argument, ScalarSelect):

7213 s = __argument

7214 else:

7215 s = Select(__argument).scalar_subquery()

7216

7217 UnaryExpression.__init__(

7218 self,

7219 s,

7220 operator=operators.exists,

7221 type_=type_api.BOOLEANTYPE,

7222 )

7223

7224 @util.ro_non_memoized_property

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

7226 return []

7227

7228 def _regroup(

7229 self,

7230 fn: Callable[[Select[Unpack[TupleAny]]], Select[Unpack[TupleAny]]],

7231 ) -> ScalarSelect[Any]:

7232

7233 assert isinstance(self.element, ScalarSelect)

7234 element = self.element.element

7235 if not isinstance(element, Select):

7236 raise exc.InvalidRequestError(

7237 "Can only apply this operation to a plain SELECT construct"

7238 )

7239 new_element = fn(element)

7240

7241 return_value = new_element.scalar_subquery()

7242 return return_value

7243

7244 def select(self) -> Select[bool]:

7245 r"""Return a SELECT of this :class:`_expression.Exists`.

7246

7247 e.g.::

7248

7249 stmt = exists(some_table.c.id).where(some_table.c.id == 5).select()

7250

7251 This will produce a statement resembling:

7252

7253 .. sourcecode:: sql

7254

7255 SELECT EXISTS (SELECT id FROM some_table WHERE some_table = :param) AS anon_1

7256

7257 .. seealso::

7258

7259 :func:`_expression.select` - general purpose

7260 method which allows for arbitrary column lists.

7261

7262 """ # noqa

7263

7264 return Select(self)

7265

7266 def correlate(

7267 self,

7268 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7269 ) -> Self:

7270 """Apply correlation to the subquery noted by this

7271 :class:`_sql.Exists`.

7272

7273 .. seealso::

7274

7275 :meth:`_sql.ScalarSelect.correlate`

7276

7277 """

7278 e = self._clone()

7279 e.element = self._regroup(

7280 lambda element: element.correlate(*fromclauses)

7281 )

7282 return e

7283

7284 def correlate_except(

7285 self,

7286 *fromclauses: Union[Literal[None, False], _FromClauseArgument],

7287 ) -> Self:

7288 """Apply correlation to the subquery noted by this

7289 :class:`_sql.Exists`.

7290

7291 .. seealso::

7292

7293 :meth:`_sql.ScalarSelect.correlate_except`

7294

7295 """

7296 e = self._clone()

7297 e.element = self._regroup(

7298 lambda element: element.correlate_except(*fromclauses)

7299 )

7300 return e

7301

7302 def select_from(self, *froms: _FromClauseArgument) -> Self:

7303 """Return a new :class:`_expression.Exists` construct,

7304 applying the given

7305 expression to the :meth:`_expression.Select.select_from`

7306 method of the select

7307 statement contained.

7308

7309 .. note:: it is typically preferable to build a :class:`_sql.Select`

7310 statement first, including the desired WHERE clause, then use the

7311 :meth:`_sql.SelectBase.exists` method to produce an

7312 :class:`_sql.Exists` object at once.

7313

7314 """

7315 e = self._clone()

7316 e.element = self._regroup(lambda element: element.select_from(*froms))

7317 return e

7318

7319 def where(self, *clause: _ColumnExpressionArgument[bool]) -> Self:

7320 """Return a new :func:`_expression.exists` construct with the

7321 given expression added to

7322 its WHERE clause, joined to the existing clause via AND, if any.

7323

7324

7325 .. note:: it is typically preferable to build a :class:`_sql.Select`

7326 statement first, including the desired WHERE clause, then use the

7327 :meth:`_sql.SelectBase.exists` method to produce an

7328 :class:`_sql.Exists` object at once.

7329

7330 """

7331 e = self._clone()

7332 e.element = self._regroup(lambda element: element.where(*clause))

7333 return e

7334

7335

7336class TextualSelect(SelectBase, ExecutableReturnsRows, Generative):

7337 """Wrap a :class:`_expression.TextClause` construct within a

7338 :class:`_expression.SelectBase`

7339 interface.

7340

7341 This allows the :class:`_expression.TextClause` object to gain a

7342 ``.c`` collection

7343 and other FROM-like capabilities such as

7344 :meth:`_expression.FromClause.alias`,

7345 :meth:`_expression.SelectBase.cte`, etc.

7346

7347 The :class:`_expression.TextualSelect` construct is produced via the

7348 :meth:`_expression.TextClause.columns`

7349 method - see that method for details.

7350

7351 .. versionchanged:: 1.4 the :class:`_expression.TextualSelect`

7352 class was renamed

7353 from ``TextAsFrom``, to more correctly suit its role as a

7354 SELECT-oriented object and not a FROM clause.

7355

7356 .. seealso::

7357

7358 :func:`_expression.text`

7359

7360 :meth:`_expression.TextClause.columns` - primary creation interface.

7361

7362 """

7363

7364 __visit_name__ = "textual_select"

7365

7366 _label_style = LABEL_STYLE_NONE

7367

7368 _traverse_internals: _TraverseInternalsType = (

7369 [

7370 ("element", InternalTraversal.dp_clauseelement),

7371 ("column_args", InternalTraversal.dp_clauseelement_list),

7372 ]

7373 + SupportsCloneAnnotations._clone_annotations_traverse_internals

7374 + HasCTE._has_ctes_traverse_internals

7375 + ExecutableStatement._executable_traverse_internals

7376 )

7377

7378 _is_textual = True

7379

7380 is_text = True

7381 is_select = True

7382

7383 def __init__(

7384 self,

7385 text: TextClause,

7386 columns: List[_ColumnExpressionArgument[Any]],

7387 positional: bool = False,

7388 ) -> None:

7389 self._init(

7390 text,

7391 # convert for ORM attributes->columns, etc

7392 [

7393 coercions.expect(roles.LabeledColumnExprRole, c)

7394 for c in columns

7395 ],

7396 positional,

7397 )

7398

7399 def _init(

7400 self,

7401 text: AbstractTextClause,

7402 columns: List[NamedColumn[Any]],

7403 positional: bool = False,

7404 ) -> None:

7405 self.element = text

7406 self.column_args = columns

7407 self.positional = positional

7408

7409 @HasMemoized_ro_memoized_attribute

7410 def selected_columns(

7411 self,

7412 ) -> ColumnCollection[str, KeyedColumnElement[Any]]:

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

7414 representing the columns that

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

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

7417

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

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

7420 within this collection cannot be directly nested inside another SELECT

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

7422 necessary parenthesization required by SQL.

7423

7424 For a :class:`_expression.TextualSelect` construct, the collection

7425 contains the :class:`_expression.ColumnElement` objects that were

7426 passed to the constructor, typically via the

7427 :meth:`_expression.TextClause.columns` method.

7428

7429

7430 .. versionadded:: 1.4

7431

7432 """

7433 return WriteableColumnCollection(

7434 (c.key, c) for c in self.column_args

7435 ).as_readonly()

7436

7437 @util.ro_non_memoized_property

7438 def _all_selected_columns(self) -> _SelectIterable:

7439 return self.column_args

7440

7441 def set_label_style(self, style: SelectLabelStyle) -> TextualSelect:

7442 return self

7443

7444 def _ensure_disambiguated_names(self) -> TextualSelect:

7445 return self

7446

7447 @_generative

7448 def bindparams(

7449 self,

7450 *binds: BindParameter[Any],

7451 **bind_as_values: Any,

7452 ) -> Self:

7453 self.element = self.element.bindparams(*binds, **bind_as_values)

7454 return self

7455

7456 def _generate_fromclause_column_proxies(

7457 self,

7458 fromclause: FromClause,

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

7460 primary_key: ColumnSet,

7461 foreign_keys: Set[KeyedColumnElement[Any]],

7462 *,

7463 proxy_compound_columns: Optional[

7464 Iterable[Sequence[ColumnElement[Any]]]

7465 ] = None,

7466 ) -> None:

7467 if TYPE_CHECKING:

7468 assert isinstance(fromclause, Subquery)

7469

7470 if proxy_compound_columns:

7471 columns._populate_separate_keys(

7472 c._make_proxy(

7473 fromclause,

7474 compound_select_cols=extra_cols,

7475 primary_key=primary_key,

7476 foreign_keys=foreign_keys,

7477 )

7478 for c, extra_cols in zip(

7479 self.column_args, proxy_compound_columns

7480 )

7481 )

7482 else:

7483 columns._populate_separate_keys(

7484 c._make_proxy(

7485 fromclause,

7486 primary_key=primary_key,

7487 foreign_keys=foreign_keys,

7488 )

7489 for c in self.column_args

7490 )

7491

7492 def _scalar_type(self) -> Union[TypeEngine[Any], Any]:

7493 return self.column_args[0].type

7494

7495

7496TextAsFrom = TextualSelect

7497"""Backwards compatibility with the previous name"""

7498

7499

7500class AnnotatedFromClause(Annotated):

7501 def _copy_internals(

7502 self,

7503 _annotations_traversal: bool = False,

7504 ind_cols_on_fromclause: bool = False,

7505 **kw: Any,

7506 ) -> None:

7507 super()._copy_internals(**kw)

7508

7509 # passed from annotations._shallow_annotate(), _deep_annotate(), etc.

7510 # the traversals used by annotations for these cases are not currently

7511 # designed around expecting that inner elements inside of

7512 # AnnotatedFromClause's element are also deep copied, so skip for these

7513 # cases. in other cases such as plain visitors.cloned_traverse(), we

7514 # expect this to happen. see issue #12915

7515 if not _annotations_traversal:

7516 ee = self._Annotated__element # type: ignore

7517 ee._copy_internals(**kw)

7518

7519 if ind_cols_on_fromclause:

7520 # passed from annotations._deep_annotate(). See that function

7521 # for notes

7522 ee = self._Annotated__element # type: ignore

7523 self.c = ee.__class__.c.fget(self) # type: ignore

7524

7525 @util.ro_memoized_property

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

7527 """proxy the .c collection of the underlying FromClause.

7528

7529 Originally implemented in 2008 as a simple load of the .c collection

7530 when the annotated construct was created (see d3621ae961a), in modern

7531 SQLAlchemy versions this can be expensive for statements constructed

7532 with ORM aliases. So for #8796 SQLAlchemy 2.0 we instead proxy

7533 it, which works just as well.

7534

7535 Two different use cases seem to require the collection either copied

7536 from the underlying one, or unique to this AnnotatedFromClause.

7537

7538 See test_selectable->test_annotated_corresponding_column

7539

7540 """

7541 ee = self._Annotated__element # type: ignore

7542 return ee.c # type: ignore