[Sqlalchemy-tickets] Issue #3193: CompoundSelect not supporting CTEs with inserts (zzzeek/sqlalchem
Brought to you by:
zzzeek
|
From: Konsta V. <iss...@bi...> - 2014-09-10 10:41:15
|
New issue 3193: CompoundSelect not supporting CTEs with inserts https://bitbucket.org/zzzeek/sqlalchemy/issue/3193/compoundselect-not-supporting-ctes-with Konsta Vesterinen: 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: ``` #!python 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() ``` |