Friday, 2 April 2021

SQLAlchemy Common Table Expression Returning Values

I would like to write a query like the following in SQLAlchemy:

WITH first_insert AS (
   INSERT INTO names (id, name)
   VALUES (1, 'bob')
   RETURNING id
)
INSERT ...

However, the following code gives me an error:

import sqlalchemy
from sqlalchemy import insert
from sqlalchemy.sql import table, column

print(sqlalchemy.__version__)

names = table("names", column("id"), column("name"))

stmt = insert(names).values(name="bob").returning(names.c.id)
print(stmt)

stmt = stmt.cte('names_cte')
print(stmt)

The output is

1.4.2
INSERT INTO names (name) VALUES (:name) RETURNING names.id

...

AttributeError: 'CTE' object has no attribute '_returning'

Is this possible in SQLAlchemy? I am working with PostgreSQL if that makes a difference.



from SQLAlchemy Common Table Expression Returning Values

No comments:

Post a Comment