CODEWITHSUNDEEP
pythonsqlpostgresqlsqlalchemycommon-table-expression
Johannes Jasper
Johannes Jasper

Reputation: 921

Join CTE (With clause) in Postres SqlAlchemy

I am struggling to write a WITH AS VALUES clause in SqlAlchemy.

Let's assume the following table

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

In a query I would now like to substititute the level names with numbers

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

See also this SQL fiddle.

How can I write this in SqlAlchemy?

Following other SO questions I found ([1], [2], [3]) I came up with the following

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

which translates to this non-functioning query

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

Links

Upvotes: 1

Views: 1632

Answers (1)

Digoya
Digoya

Reputation: 131

According to this answer :

You can try to rewrite your matching_levels query in that way:

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

Upvotes: 1

Related Questions