- edited description
CompoundSelect not supporting CTEs with inserts
Issue #3193
duplicate
I'm trying to do atomic SELECT or INSERT as explained here: http://stackoverflow.com/questions/6722344/select-or-insert-a-row-in-one-command/6722460#6722460
I'v been able to construct the query, however when I try to execute this query it gives me:
AttributeError: 'CompoundSelect' object has no attribute '_returning'
Here is a minimal test case for reproducing the issue:
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()
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).
Comments (4)
-
reporter -
repo owner - changed status to duplicate
Duplicate of
#2551. -
repo owner second dupe on that issue :). my favorite button :)
-
reporter Ah my bad! Should have checked the issue list first.
- Log in to comment