SQLAlchemy: WITH new_values (id, field) as ( values (..., ...) )

Question

I'm trying to use SQLAlchemy core to create a query which hits only once the DB(postgres) and inserts a row if it's not already present.

Here is part of the query

WITH new_values (id, field) as (
  values 
     (1, 1),
     (2, 12),
     (3, 13),
     (4, 14)
)
INSERT INTO table1 (id, field)
SELECT id, field
FROM new_values
WHERE NOT EXISTS (SELECT 1 
          FROM table1 as up 
          WHERE up.id = new_values.id);

I'm confused about:

• How do I create the CTE expression with the values specified (I only found examples with sub queries)
• How do I SELECT 1 ?
• How do I add the where in the insert?

I'd like to use the core API and avoid direct execution.

PS: The full query I would like to put in a single transaction is

WITH upsert AS (
   UPDATE table2
   SET field1=field1+1
   WHERE id=1234 AND field2=42
   RETURNING *
)
INSERT INTO table2 (id, field1, field2)
SELECT 1234, 1, 42
WHERE NOT EXISTS (SELECT 1 FROM upsert);

WITH new_values (id, field) as (
  values 
     (1, 1),
     (2, 12),
     (3, 13),
     (4, 14)
)
INSERT INTO table1 (id, field)
SELECT id, field
FROM new_values
WHERE NOT EXISTS (SELECT 1 
          FROM table1 as up 
          WHERE up.id = new_values.id);

EDIT: The model

table1 = Table('table1', metadata,
    Column('id', Integer, primary_key=True),
    Column('field', Integer))

table2 = Table('table2', metadata,
    Column('id', Integer, primary_key=True),
    Column('field1', Integer),
    Column('field2', Integer))

Answer 1

This Postgres syntax is not (yet) directly supported by SQLAlchemy. There is an issue filed -- several years ago, actually, but it has recent history and an increase in priority. See https://bitbucket.org/zzzeek/sqlalchemy/issues/2551/apparently-inserts-and-update-delete-can

Alternatively, text() items that have columns() defined can have cte() called on them, so I thought for a while that this might work if the update were converted into plain sql with bind params. However, it is hard to get SQLAlchemy to correctly place the with at the top level of the query when the CTE is associated with a select_from of an insert. In short, until the above issue is resolved I think you are better off not trying to use this approach with SQLAlchemy core -- either use SQL or search for a different upsert method.