<p dir="auto"><strong>Migrated issue, originally created by Konsta Vesterinen (<a href="https://github.com/kvesteri">@kvesteri</a>)</strong></p>
<p dir="auto">I'm trying to do atomic SELECT or INSERT as explained here: <a href="http://stackoverflow.com/questions/6722344/select-or-insert-a-row-in-one-command/6722460#6722460" rel="nofollow">http://stackoverflow.com/questions/6722344/select-or-insert-a-row-in-one-command/6722460#6722460</a></p>
<p dir="auto">I'v been able to construct the query, however when I try to execute this query it gives me:</p>
<p dir="auto">AttributeError: 'CompoundSelect' object has no attribute '_returning'</p>
<p dir="auto">Here is a minimal test case for reproducing the issue:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import CTE, exists

Model = declarative_base()


dns = 'postgres://postgres@localhost/my_test_db'

engine = create_engine(dns)
# engine.echo = True
connection = engine.connect()


class MyModel(Model):
    __tablename__ = 'my_model'

    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    key = sa.Column(sa.Unicode(255), nullable=False)


sa.orm.configure_mappers()
Model.metadata.create_all(connection)

table = MyModel.__table__
value = 'some_key'

select = (
    sa.select(['*'], from_obj=table)
    .where(table.c.key == value)
)
insert = CTE(
    table.insert()
    .from_select(
        [table.c.key],
        sa.select([sa.text(value)])
        .where(~ exists(select))
    )
    .returning(sa.text('*')),
    name='new_row'
)
query = sa.select(['*'], from_obj=insert).union(select)
print connection.execute(query).fetchall()"><pre class="notranslate"><code class="notranslate">import sqlalchemy as sa
from sqlalchemy import create_engine
from sqlalchemy.dialects import postgresql
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import CTE, exists

Model = declarative_base()


dns = 'postgres://postgres@localhost/my_test_db'

engine = create_engine(dns)
# engine.echo = True
connection = engine.connect()


class MyModel(Model):
    __tablename__ = 'my_model'

    id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
    key = sa.Column(sa.Unicode(255), nullable=False)


sa.orm.configure_mappers()
Model.metadata.create_all(connection)

table = MyModel.__table__
value = 'some_key'

select = (
    sa.select(['*'], from_obj=table)
    .where(table.c.key == value)
)
insert = CTE(
    table.insert()
    .from_select(
        [table.c.key],
        sa.select([sa.text(value)])
        .where(~ exists(select))
    )
    .returning(sa.text('*')),
    name='new_row'
)
query = sa.select(['*'], from_obj=insert).union(select)
print connection.execute(query).fetchall()
</code></pre></div>
<p dir="auto">Edit: Another problem is that the CTE isn't adding parenthesis around the insert statement whereas it should be (otherwise PostgreSQL doesn't understand the query).</p>
<p dir="auto"><strong>Migrated issue, originally created by Michael Bayer (<a href="https://github.com/zzzeek">@zzzeek</a>)</strong></p>
<p dir="auto">apparently its all possible now.  might as well make a new syntax bonanza out of it:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="WITH upsert AS (
  UPDATE metric k SET k.count = k.count + 5
  WHERE event = &quot;foo&quot; AND interval = &quot;D&quot; and date = &quot;whatever&quot;
  RETURNING k.*
)
INSERT INTO metric (event, interval, date, count) 
SELECT (&quot;foo&quot;, &quot;D&quot;, &quot;whatever&quot;, 5)
WHERE NOT EXISTS (
  SELECT 1 FROM upsert
);"><pre class="notranslate"><code class="notranslate">WITH upsert AS (
  UPDATE metric k SET k.count = k.count + 5
  WHERE event = "foo" AND interval = "D" and date = "whatever"
  RETURNING k.*
)
INSERT INTO metric (event, interval, date, count) 
SELECT ("foo", "D", "whatever", 5)
WHERE NOT EXISTS (
  SELECT 1 FROM upsert
);
</code></pre></div>
<p dir="auto"><a href="http://www.postgresql.org/docs/9.1/static/sql-insert.html" rel="nofollow">http://www.postgresql.org/docs/9.1/static/sql-insert.html</a></p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/2551/ticket_2551.patch">ticket_2551.patch</a> | <a href="../wiki/imported_issue_attachments/2551/CTE.sqlalchemy">CTE.sqlalchemy</a></p>