sqlalchemy: CTEs does not work with UPDATE statements using`engine.execute` (in Postgres)

Describe the bug I first notice this from the python archived records library, which uses sqlalchemy under the hood. We have a query that uses two CTEs and an update statement at the end, what we have noticed is the query is not working if you executed in code, but works if copied the query and executed in DataGrip (or any SQL client i presume).

Expected behavior Based on the reproduction steps below, record should be (1, 3), but it is still (1, 2), with no error given.

To Reproduce

Setup your table:

CREATE TABLE public.pg_test (
    id int,
    name varchar
);

INSERT INTO public.pg_test VALUES (1, 2);

Setup engine connection and run the following: Of course this is a very naive example, where the update statement has nothing to do with the CTE.

query = """
        WITH to_update AS (
            SELECT * FROM playground.pg_test
        )
        UPDATE playground.pg_test
        SET name = 3"""
hook.sqla_engine.execute(
        query
    )

where the hook.sqla_engine is a Postgres engine.

However if I removed the CTE, left with only the UPDATE statement, it works as expected.

Tested with SELECT queries, they work fine. Not sure about INSERT (vaguely remember it works) AND DELETE


**Versions.**
 - OS: Mac OS Catalina
 - Python: 3.7
 - SQLAlchemy: 1.3.0 and above
 - Database: Postgres

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 1
Comments: 16 (11 by maintainers)

Most upvoted comments

#4846 addresses more verbiage around implicit application level autocommit being deprecated.

zzzeek on Aug 7, 2020

Regarding why the update version succeeds while the cte version does not, it is due to the way sqlalchemy detects if it should auto-commit or not. https://github.com/sqlalchemy/sqlalchemy/blob/9a3fee2cb6b608eb5c0263cf5a7e9085f74f2e73/lib/sqlalchemy/engine/default.py#L34-L36

This regexp does not match CTE ... but does match UPDATE ...

CaselIT on Aug 7, 2020

Did a few more testing (maybe I am testing it wrongly but yeah just showing the code I tested below),

using Engine.begin() with context manager works as intended

    with hook.sqla_engine.begin() as trans:
        trans.execute("""
            WITH to_update AS (
                SELECT * FROM public.pg_test
            )
            UPDATE public.pg_test
            SET name = 31
            """
        )

using Engine.connect() it does not work, with no error message, echoed SQL is the original SQL, this result matches my experience with records package since it uses Connection.execute() as well in source, not Engine.execute.

    with hook.sqla_engine.connect() as conn:
        conn.execute(
            """
            WITH to_update AS (
                SELECT * FROM public.pg_test
            )
            UPDATE public.pg_test
            SET name = 2
            """
        )

Shadowsong27 on Aug 6, 2020

I guess you need to commit explicitly then…

By it I mean that you should probably use Engine.begin or Engine.connect instead of execute.

CaselIT on Aug 6, 2020