title: How can I delete rows from one table and insert into another using SQLAlchemy and Postgres RETURNING?
body: <p>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).</p>
  
  <p>The approach is to use <code>DELETE</code> with <code>RETURNING</code> and to insert the rows into the other table.</p>
  
  <p>I'm using: SQLAlchemy 1.0.12, Postgres 9.4 and Python 2.7.11.</p>
  
  <h2>Setting up the tables</h2>
  
  <p>The following SQL creates the tables and inserts a row of data:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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);
  </code></pre>
  
  <h2>Creating tables using SQLAlchemy</h2>
  
  <p>The following SQLAlchemy code creates the same tables and inserts a row of data:</p>
  
  <pre class="lang-python prettyprint-override"><code>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))
  </code></pre>
  
  <h2>Query that I would like to implement</h2>
  
  <p>This is the SQL query that I wish to run (which works on its own):</p>
  
  <pre class="lang-sql prettyprint-override"><code>with output as (delete from example1 where value_a &lt; 10 returning id, value_a)
  insert into example2 (id, value_a, value_b)
  select id, value_a, 3 from output;
  </code></pre>
  
  <h2>SQLAlchemy query so far</h2>
  
  <p>The query that I have constructed so far is:</p>
  
  <pre class="lang-python prettyprint-override"><code>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 &lt; 10,
              ).returning(
                  ExampleOne.id,
                  ExampleOne.value_a,
              )
          )
      )
  )
  
  session.execute(query)
  </code></pre>
  
  <h2>The problem</h2>
  
  <p>The error is:</p>
  
  <pre class="lang-python prettyprint-override"><code>  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
  </code></pre>
  
  <p>The problem seems to be that SQLAlchemy does not recognise the <code>DELETE ... RETURNING</code> query as a valid expression for the <code>FROM</code> part of the <code>INSERT</code> query.</p>
  
  <p>Is there a way to make this clear to SQLAlchemy or is there are a different approach to create the given query in SQLAlchemy?</p>