How can I delete rows from one table and insert into another using SQLAlchemy and Postgres RETURNING?

Question

I would like to move rows from one table to another using SQLAlchemy with a Postgres database (there are other questions on Stack Overflow about moving data but they don't focus on using SQLAlchemy for this).

The approach is to use DELETE with RETURNING and to insert the rows into the other table.

I'm using: SQLAlchemy 1.0.12, Postgres 9.4 and Python 2.7.11.

Setting up the tables

The following SQL creates the tables and inserts a row of data:

create table example1 (
    id integer,
    value_a integer,
    value_b integer,
    CONSTRAINT example1_pkey PRIMARY KEY (id)
);
create table example2 (
    id integer,
    value_a integer,
    value_b integer,
    CONSTRAINT example2_pkey PRIMARY KEY (id)
);

insert into example1 values (18, 1, 9);

Creating tables using SQLAlchemy

The following SQLAlchemy code creates the same tables and inserts a row of data:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class ExampleOne(Base):
    __tablename__ = 'example1'

    id = Column(Integer, primary_key=True)
    value_a = Column(Integer)
    value_b = Column(Integer)


class ExampleTwo(Base):
    __tablename__ = 'example2'

    id = Column(Integer, primary_key=True)
    value_a = Column(Integer)
    value_b = Column(Integer)


Base.metadata.create_all(session.bind)

with session.begin():
    session.add(ExampleOne(id=18, value_a=1, value_b=9))

Query that I would like to implement

This is the SQL query that I wish to run (which works on its own):

with output as (delete from example1 where value_a < 10 returning id, value_a)
insert into example2 (id, value_a, value_b)
select id, value_a, 3 from output;

SQLAlchemy query so far

The query that I have constructed so far is:

query = insert(ExampleTwo, inline=True).from_select(
    ['id', 'value_a', 'value_b'],
    select(
        ['id', 'value_a', literal(3)]
    ).where(
        select([
            'id', 'value_a',
        ]).select_from(
            delete(ExampleOne).where(
                ExampleOne.value_a < 10,
            ).returning(
                ExampleOne.id,
                ExampleOne.value_a,
            )
        )
    )
)

session.execute(query)

The problem

The error is:

  File ".../lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 41, in _interpret_as_from
    raise exc.ArgumentError("FROM expression expected")
sqlalchemy.exc.ArgumentError: FROM expression expected

The problem seems to be that SQLAlchemy does not recognise the DELETE ... RETURNING query as a valid expression for the FROM part of the INSERT query.

Is there a way to make this clear to SQLAlchemy or is there are a different approach to create the given query in SQLAlchemy?

Answer 1

You need to make the delete expression into a CTE, as your raw SQL calls for:

>>> output = delete(ExampleOne).where(
...     ExampleOne.value_a < 10,
... ).returning(
...     ExampleOne.id,
...     ExampleOne.value_a,
... ).cte('output')
>>> query = insert(ExampleTwo, inline=True).from_select(
...     ['id', 'value_a', 'value_b'],
...     select(
...         ['id', 'value_a', literal(3)]
...     ).select_from(output)
... )
>>> query.compile(engine)
WITH output AS 
(DELETE FROM example1 WHERE example1.value_a < %(value_a_1)s RETURNING example1.id, example1.value_a)
 INSERT INTO example2 (id, value_a, value_b) SELECT id, value_a, %(param_1)s AS anon_1 
FROM output

Unfortunately, .cte only works on delete expressions in SQLAlchemy 1.1, which is currently unreleased, so you'll have to install SQLAlchemy from the source repo to make this work:

pip install -e git+https://bitbucket.org/zzzeek/sqlalchemy#egg=sqlalchemy