diff options
Diffstat (limited to 'lib/sqlalchemy/sql/selectable.py')
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 184 |
1 files changed, 155 insertions, 29 deletions
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 0e5ae89e4..350e55c49 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -2121,9 +2121,23 @@ class CTE( _suffixes=self._suffixes, ) - def union(self, other): + def union(self, *other): + r"""Return a new :class:`_expression.CTE` with a SQL ``UNION`` + of the original CTE against the given selectables provided + as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. + + .. versionchanged:: 1.4.28 multiple elements are now accepted. + + .. seealso:: + + :meth:`_sql.HasCTE.cte` - examples of calling styles + + """ return CTE._construct( - self.element.union(other), + self.element.union(*other), name=self.name, recursive=self.recursive, nesting=self.nesting, @@ -2132,9 +2146,23 @@ class CTE( _suffixes=self._suffixes, ) - def union_all(self, other): + def union_all(self, *other): + r"""Return a new :class:`_expression.CTE` with a SQL ``UNION ALL`` + of the original CTE against the given selectables provided + as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. + + .. versionchanged:: 1.4.28 multiple elements are now accepted. + + .. seealso:: + + :meth:`_sql.HasCTE.cte` - examples of calling styles + + """ return CTE._construct( - self.element.union_all(other), + self.element.union_all(*other), name=self.name, recursive=self.recursive, nesting=self.nesting, @@ -2396,7 +2424,7 @@ class HasCTE(roles.HasCTERole): connection.execute(upsert) - Example 4, Nesting CTE:: + Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above):: value_a = select( literal("root").label("n") @@ -2426,6 +2454,44 @@ class HasCTE(roles.HasCTERole): SELECT value_a.n AS a, value_b.n AS b FROM value_a, value_b + Example 5, Non-Linear CTE (SQLAlchemy 1.4.28 and above):: + + edge = Table( + "edge", + metadata, + Column("id", Integer, primary_key=True), + Column("left", Integer), + Column("right", Integer), + ) + + root_node = select(literal(1).label("node")).cte( + "nodes", recursive=True + ) + + left_edge = select(edge.c.left).join( + root_node, edge.c.right == root_node.c.node + ) + right_edge = select(edge.c.right).join( + root_node, edge.c.left == root_node.c.node + ) + + subgraph_cte = root_node.union(left_edge, right_edge) + + subgraph = select(subgraph_cte) + + The above query will render 2 UNIONs inside the recursive CTE:: + + WITH RECURSIVE nodes(node) AS ( + SELECT 1 AS node + UNION + SELECT edge."left" AS "left" + FROM edge JOIN nodes ON edge."right" = nodes.node + UNION + SELECT edge."right" AS "right" + FROM edge JOIN nodes ON edge."left" = nodes.node + ) + SELECT nodes.node FROM nodes + .. seealso:: :meth:`_orm.Query.cte` - ORM version of @@ -6269,47 +6335,107 @@ class Select( else: return SelectStatementGrouping(self) - def union(self, other, **kwargs): - """Return a SQL ``UNION`` of this select() construct against - the given selectable. + def union(self, *other, **kwargs): + r"""Return a SQL ``UNION`` of this select() construct against + the given selectables provided as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. + + .. versionchanged:: 1.4.28 + + multiple elements are now accepted. + + :param \**kwargs: keyword arguments are forwarded to the constructor + for the newly created :class:`_sql.CompoundSelect` object. """ - return CompoundSelect._create_union(self, other, **kwargs) + return CompoundSelect._create_union(self, *other, **kwargs) + + def union_all(self, *other, **kwargs): + r"""Return a SQL ``UNION ALL`` of this select() construct against + the given selectables provided as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. - def union_all(self, other, **kwargs): - """Return a SQL ``UNION ALL`` of this select() construct against - the given selectable. + .. versionchanged:: 1.4.28 + + multiple elements are now accepted. + + :param \**kwargs: keyword arguments are forwarded to the constructor + for the newly created :class:`_sql.CompoundSelect` object. """ - return CompoundSelect._create_union_all(self, other, **kwargs) + return CompoundSelect._create_union_all(self, *other, **kwargs) + + def except_(self, *other, **kwargs): + r"""Return a SQL ``EXCEPT`` of this select() construct against + the given selectable provided as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. + + .. versionchanged:: 1.4.28 + + multiple elements are now accepted. - def except_(self, other, **kwargs): - """Return a SQL ``EXCEPT`` of this select() construct against - the given selectable. + :param \**kwargs: keyword arguments are forwarded to the constructor + for the newly created :class:`_sql.CompoundSelect` object. """ - return CompoundSelect._create_except(self, other, **kwargs) + return CompoundSelect._create_except(self, *other, **kwargs) - def except_all(self, other, **kwargs): - """Return a SQL ``EXCEPT ALL`` of this select() construct against - the given selectable. + def except_all(self, *other, **kwargs): + r"""Return a SQL ``EXCEPT ALL`` of this select() construct against + the given selectables provided as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. + + .. versionchanged:: 1.4.28 + + multiple elements are now accepted. + + :param \**kwargs: keyword arguments are forwarded to the constructor + for the newly created :class:`_sql.CompoundSelect` object. """ - return CompoundSelect._create_except_all(self, other, **kwargs) + return CompoundSelect._create_except_all(self, *other, **kwargs) + + def intersect(self, *other, **kwargs): + r"""Return a SQL ``INTERSECT`` of this select() construct against + the given selectables provided as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. - def intersect(self, other, **kwargs): - """Return a SQL ``INTERSECT`` of this select() construct against - the given selectable. + .. versionchanged:: 1.4.28 + + multiple elements are now accepted. + + :param \**kwargs: keyword arguments are forwarded to the constructor + for the newly created :class:`_sql.CompoundSelect` object. """ - return CompoundSelect._create_intersect(self, other, **kwargs) + return CompoundSelect._create_intersect(self, *other, **kwargs) + + def intersect_all(self, *other, **kwargs): + r"""Return a SQL ``INTERSECT ALL`` of this select() construct + against the given selectables provided as positional arguments. + + :param \*other: one or more elements with which to create a + UNION. + + .. versionchanged:: 1.4.28 + + multiple elements are now accepted. - def intersect_all(self, other, **kwargs): - """Return a SQL ``INTERSECT ALL`` of this select() construct - against the given selectable. + :param \**kwargs: keyword arguments are forwarded to the constructor + for the newly created :class:`_sql.CompoundSelect` object. """ - return CompoundSelect._create_intersect_all(self, other, **kwargs) + return CompoundSelect._create_intersect_all(self, *other, **kwargs) @property @util.deprecated_20( |