Vous êtes un professionnel et vous avez besoin d'une formation ? Coder avec une
Intelligence Artificielle Voir le programme détaillé

Classe « HasCTE »

Méthode sqlalchemy.HasCTE.add_cte

Signature de la méthode add_cte

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

Description

help(HasCTE.add_cte)

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

This method will associate the given :class:`_sql.CTE` constructs with
the parent statement such that they will each be unconditionally
rendered in the WITH clause of the final statement, even if not
referenced elsewhere within the statement or any sub-selects.

The optional :paramref:`.HasCTE.add_cte.nest_here` parameter when set
to True will have the effect that each given :class:`_sql.CTE` will
render in a WITH clause rendered directly along with this statement,
rather than being moved to the top of the ultimate rendered statement,
even if this statement is rendered as a subquery within a larger
statement.

This method has two general uses. One is to embed CTE statements that
serve some purpose without being referenced explicitly, such as the use
case of embedding a DML statement such as an INSERT or UPDATE as a CTE
inline with a primary statement that may draw from its results
indirectly.  The other is to provide control over the exact placement
of a particular series of CTE constructs that should remain rendered
directly in terms of a particular statement that may be nested in a
larger statement.

E.g.::

    from sqlalchemy import table, column, select

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

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

    stmt = select(t).add_cte(ins)

Would render:

.. sourcecode:: sql

    WITH anon_1 AS (
        INSERT INTO t (c1, c2) VALUES (:param_1, :param_2)
    )
    SELECT t.c1, t.c2
    FROM t

Above, the "anon_1" CTE is not referenced in the SELECT
statement, however still accomplishes the task of running an INSERT
statement.

Similarly in a DML-related context, using the PostgreSQL
:class:`_postgresql.Insert` construct to generate an "upsert"::

    from sqlalchemy import table, column
    from sqlalchemy.dialects.postgresql import insert

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

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

    insert_stmt = insert(t).values({"c1": 1, "c2": 2})
    update_statement = insert_stmt.on_conflict_do_update(
        index_elements=[t.c.c1],
        set_={
            "c1": insert_stmt.excluded.c1,
            "c2": insert_stmt.excluded.c2,
        },
    ).add_cte(delete_statement_cte)

    print(update_statement)

The above statement renders as:

.. sourcecode:: sql

    WITH deletions AS (
        DELETE FROM t WHERE t.c1 < %(c1_1)s
    )
    INSERT INTO t (c1, c2) VALUES (%(c1)s, %(c2)s)
    ON CONFLICT (c1) DO UPDATE SET c1 = excluded.c1, c2 = excluded.c2

.. versionadded:: 1.4.21

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

 .. versionchanged:: 2.0  Multiple CTE instances are accepted

:param nest_here: if True, the given CTE or CTEs will be rendered
 as though they specified the :paramref:`.HasCTE.cte.nesting` flag
 to ``True`` when they were added to this :class:`.HasCTE`.
 Assuming the given CTEs are not referenced in an outer-enclosing
 statement as well, the CTEs given should render at the level of
 this statement when this flag is given.

 .. versionadded:: 2.0

 .. seealso::

    :paramref:`.HasCTE.cte.nesting`

Vous êtes un professionnel et vous avez besoin d'une formation ? Coder avec une
Intelligence Artificielle Voir le programme détaillé