SQLAlchemy Common Table Expression Returning Values

Question

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.

Why not use sqlalchemy's lastrowid? Ref: docs.sqlalchemy.org/en/14/core/… — Murtuza Z
– Murtuza Z, Commented Mar 29, 2021 at 5:52
In general I may be inserting several rows simultaneously within a single query — yoyoy
– yoyoy, Commented Mar 29, 2021 at 5:56
If the id is autogenerated on the DB side, why not change your insert statement to include only the name column? (Change names to the following names = table("names", column("name")) Asking out of curiosity, not a suggestion... — Yaakov Bressler
– Yaakov Bressler, Commented Apr 6, 2021 at 15:48

van · Accepted Answer · 2021-03-31 14:05:17Z

You are right to find an error, but it seems to be limited only to the generation of the string version of the SQL at that particular point. To me it looks to be a bug, and it might make sense to create an Issue for it.

Nonetheless, if you can ignore that print statement, the rest of the code works quite well:

# added for dialect specific query printing
from sqlalchemy.dialects import postgresql
def print_stmt(stmt):
    print(stmt.compile(dialect=postgresql.dialect()))


# defined also second table "balls" for testing of INSERTs
names = table("names", column("id"), column("name"))
balls = table("balls", column("id"), column("name"))


# added multi-row insert to test    
stmt = insert(names).values([{"name": "bob"}, {"name": "alice"}]).returning(names.c.id, names.c.name)
# print(stmt)  # does not work with 'default' dialect for multi-row insert
print_stmt(stmt)


stmt = stmt.cte('names_cte')
# INDEED: putting together the string version of the statement generates an error,
# BUT: when further used, the error (which I think is a bug) is not in a way.
# print(stmt)


# insert from CET
inse = insert(balls).from_select(["id", "name"], stmt)
print_stmt(inse)

res = engine.execute(inse)
print(res.rowcount)


# test data is in the database    
q = session.query(balls)
for b in q:
    print(b)

Collectives™ on Stack Overflow

SQLAlchemy Common Table Expression Returning Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related