时间戳

首页 > 解决方案 > 在 Postres SqlAlchemy 中加入 CTE（With 子句）

python - 在 Postres SqlAlchemy 中加入 CTE（With 子句）

问题描述

我正在努力WITH AS VALUES在 SqlAlchemy 中编写一个子句。

让我们假设下表

CREATE TABLE Example ("name" varchar(5), "level" varchar(5));
    
INSERT INTO Example ("name", "level") VALUES
    ('John', 'one'),
    ('Alice', 'two'),
    ('Bob', 'three')
;

在查询中，我现在想用数字替换级别名称

WITH matched_levels (level_name, level_score) as (
    values ('one', 1.0),
           ('two', 2.0),
           ('three', 3.0)
  )
select e.name, m.level_score
from Example e
  join matched_levels m on e.level = m.level_name;

-- name     level_score
-- John     1
-- Alice    2
-- Bob      3

另请参阅此 SQL fiddle。

我怎样才能在 SqlAlchemy 中写这个？

在我发现的其他 SO 问题（[1]、[2]、[3]）之后，我想出了以下内容

matching_levels = sa.select([sa.column('level_name'), sa.column('level_score')]).select_from(
    sa.text("values ('one', 1.0), ('two', 2.0), ('three', 3.0)")) \
    .cte(name='matched_levels')

result = session.query(Example).join(
    matching_levels,
    matching_levels.c.level_name == Example.level
).all()

这转化为这个不起作用的查询

WITH matched_levels AS 
(SELECT level_name, level_score 
FROM values ('one', 1.0), ('two', 2.0), ('three', 3.0))
 SELECT "Example".id AS "Example_id", "Example".name AS "Example_name", "Example".level AS "Example_level" 
FROM "Example" JOIN matched_levels ON matched_levels.level_name = "Example".level

链接

标签： pythonsqlpostgresqlsqlalchemycommon-table-expression

解决方案

根据这个答案：

您可以尝试以matching_levels这种方式重写您的查询：

matching_levels = select(Values(
            column('level_name', String),
            column('level_score', Float),
            name='temp_table').data([('one', 1.0), ('two', 2.0), ('three', 3.0)])
        ).cte('matched_levels')

result = session.query(Example).join(
    matching_levels,
    matching_levels.c.level_name == Example.level
).all()

推荐阅读