Bug
Links
def cte(self, name: 'Optional[str]' = None, recursive: 'bool' = False, nesting: 'bool' = False) -> 'CTE'
Return a new :class:`_expression.CTE`,
or Common Table Expression instance.
Common table expressions are a SQL standard whereby SELECT
statements can draw upon secondary statements specified along
with the primary statement, using a clause called "WITH".
Special semantics regarding UNION can also be employed to
allow "recursive" queries, where a SELECT statement can draw
upon the set of rows that have previously been selected.
CTEs can also be applied to DML constructs UPDATE, INSERT
and DELETE on some databases, both as a source of CTE rows
when combined with RETURNING, as well as a consumer of
CTE rows.
SQLAlchemy detects :class:`_expression.CTE` objects, which are treated
similarly to :class:`_expression.Alias` objects, as special elements
to be delivered to the FROM clause of the statement as well
as to a WITH clause at the top of the statement.
For special prefixes such as PostgreSQL "MATERIALIZED" and
"NOT MATERIALIZED", the :meth:`_expression.CTE.prefix_with`
method may be
used to establish these.
.. versionchanged:: 1.3.13 Added support for prefixes.
In particular - MATERIALIZED and NOT MATERIALIZED.
:param name: name given to the common table expression. Like
:meth:`_expression.FromClause.alias`, the name can be left as
``None`` in which case an anonymous symbol will be used at query
compile time.
:param recursive: if ``True``, will render ``WITH RECURSIVE``.
A recursive common table expression is intended to be used in
conjunction with UNION ALL in order to derive rows
from those already selected.
:param nesting: if ``True``, will render the CTE locally to the
statement in which it is referenced. For more complex scenarios,
the :meth:`.HasCTE.add_cte` method using the
:paramref:`.HasCTE.add_cte.nest_here`
parameter may also be used to more carefully
control the exact placement of a particular CTE.
.. versionadded:: 1.4.24
.. seealso::
:meth:`.HasCTE.add_cte`
The following examples include two from PostgreSQL's documentation at
https://www.postgresql.org/docs/current/static/queries-with.html,
as well as additional examples.
Example 1, non recursive::
from sqlalchemy import (
Table,
Column,
String,
Integer,
MetaData,
select,
func,
)
metadata = MetaData()
orders = Table(
"orders",
metadata,
Column("region", String),
Column("amount", Integer),
Column("product", String),
Column("quantity", Integer),
)
regional_sales = (
select(orders.c.region, func.sum(orders.c.amount).label("total_sales"))
.group_by(orders.c.region)
.cte("regional_sales")
)
top_regions = (
select(regional_sales.c.region)
.where(
regional_sales.c.total_sales
> select(func.sum(regional_sales.c.total_sales) / 10)
)
.cte("top_regions")
)
statement = (
select(
orders.c.region,
orders.c.product,
func.sum(orders.c.quantity).label("product_units"),
func.sum(orders.c.amount).label("product_sales"),
)
.where(orders.c.region.in_(select(top_regions.c.region)))
.group_by(orders.c.region, orders.c.product)
)
result = conn.execute(statement).fetchall()
Example 2, WITH RECURSIVE::
from sqlalchemy import (
Table,
Column,
String,
Integer,
MetaData,
select,
func,
)
metadata = MetaData()
parts = Table(
"parts",
metadata,
Column("part", String),
Column("sub_part", String),
Column("quantity", Integer),
)
included_parts = (
select(parts.c.sub_part, parts.c.part, parts.c.quantity)
.where(parts.c.part == "our part")
.cte(recursive=True)
)
incl_alias = included_parts.alias()
parts_alias = parts.alias()
included_parts = included_parts.union_all(
select(
parts_alias.c.sub_part, parts_alias.c.part, parts_alias.c.quantity
).where(parts_alias.c.part == incl_alias.c.sub_part)
)
statement = select(
included_parts.c.sub_part,
func.sum(included_parts.c.quantity).label("total_quantity"),
).group_by(included_parts.c.sub_part)
result = conn.execute(statement).fetchall()
Example 3, an upsert using UPDATE and INSERT with CTEs::
from datetime import date
from sqlalchemy import (
MetaData,
Table,
Column,
Integer,
Date,
select,
literal,
and_,
exists,
)
metadata = MetaData()
visitors = Table(
"visitors",
metadata,
Column("product_id", Integer, primary_key=True),
Column("date", Date, primary_key=True),
Column("count", Integer),
)
# add 5 visitors for the product_id == 1
product_id = 1
day = date.today()
count = 5
update_cte = (
visitors.update()
.where(
and_(visitors.c.product_id == product_id, visitors.c.date == day)
)
.values(count=visitors.c.count + count)
.returning(literal(1))
.cte("update_cte")
)
upsert = visitors.insert().from_select(
[visitors.c.product_id, visitors.c.date, visitors.c.count],
select(literal(product_id), literal(day), literal(count)).where(
~exists(update_cte.select())
),
)
connection.execute(upsert)
Example 4, Nesting CTE (SQLAlchemy 1.4.24 and above)::
value_a = select(literal("root").label("n")).cte("value_a")
# A nested CTE with the same name as the root one
value_a_nested = select(literal("nesting").label("n")).cte(
"value_a", nesting=True
)
# Nesting CTEs takes ascendency locally
# over the CTEs at a higher level
value_b = select(value_a_nested.c.n).cte("value_b")
value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("b"))
The above query will render the second CTE nested inside the first,
shown with inline parameters below as:
.. sourcecode:: sql
WITH
value_a AS
(SELECT 'root' AS n),
value_b AS
(WITH value_a AS
(SELECT 'nesting' AS n)
SELECT value_a.n AS n FROM value_a)
SELECT value_a.n AS a, value_b.n AS b
FROM value_a, value_b
The same CTE can be set up using the :meth:`.HasCTE.add_cte` method
as follows (SQLAlchemy 2.0 and above)::
value_a = select(literal("root").label("n")).cte("value_a")
# A nested CTE with the same name as the root one
value_a_nested = select(literal("nesting").label("n")).cte("value_a")
# Nesting CTEs takes ascendency locally
# over the CTEs at a higher level
value_b = (
select(value_a_nested.c.n)
.add_cte(value_a_nested, nest_here=True)
.cte("value_b")
)
value_ab = select(value_a.c.n.label("a"), value_b.c.n.label("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:
.. sourcecode:: sql
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
:meth:`_expression.HasCTE.cte`.
Améliorations / Corrections
Vous avez des améliorations (ou des corrections) à proposer pour ce document : je vous remerçie par avance de m'en faire part, cela m'aide à améliorer le site.
Emplacement :
Description des améliorations :