python - SQLAlchemy 公用表表达式返回值

Question

我想在 SQLAlchemy 中编写如下查询:

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

但是，下面的代码给我一个错误:

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)

输出是

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

...

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

这在 SQLAlchemy 中可行吗？如果有影响，我会使用 PostgreSQL。

Answer 1

你发现错误是对的，但它似乎仅限于在特定点生成 SQL 的字符串版本。对我来说它看起来是一个错误，它可能对 create an Issue 有意义

尽管如此，如果您可以忽略该 print 语句，其余代码将运行良好:

# 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)