`cteq_alias.union_all(...`
Also, you do not need to create cteq_alias, you can use cteq, like in your
query, but you have to replace cteq_alias.c.id with cteq.c.id
ср, 13 июл. 2016 г. в 15:20, Jonathan Underwood <
jonathan.underw...@gmail.com>:
> Hi,
>
> I am struggling to get a simple recursive CTE query to work with
> sqlalchemy 1.0.14, sqlite backend (3.8.10.2) and pysqlite 2.8.2, python
> 2.7.12. Below is a reproducer:
>
> from sqlalchemy.ext.declarative import declarative_base
> from sqlalchemy import Column, String
> from sqlalchemy import orm
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
>
> Base = declarative_base()
>
> class Obj(Base):
> __tablename__ = 'obj'
> id = Column(String, primary_key=True)
> parent_id = Column(String)
>
>
> engine = create_engine('sqlite:///')
> Base.metadata.create_all(engine)
> Session = sessionmaker(bind=engine)
> Session.configure(bind=engine)
> session = Session()
>
> a = Obj(id=u'1234')
> b = Obj(id=u'2345', parent_id='1234')
> c = Obj(id=u'3456', parent_id='1234')
> d = Obj(id=u'5678', parent_id='3456')
>
> session.add(a)
> session.add(b)
> session.add(c)
> session.add(d)
>
> session.commit()
>
> cteq = session.query(Obj).filter(Obj.parent_id == a.id).cte(recursive=True,
> name='cteq_n')
> cteq_alias = orm.aliased(cteq, name='cteq_alias_n')
> rekey_alias = orm.aliased(Obj, name='rekey_alias_n')
> r = cteq.union_all(
> session.query(rekey_alias).filter(rekey_alias.parent_id ==
> cteq_alias.c.id)
> )
>
> objs = session.query(Obj).select_from(r).all()
>
> print objs.all()
>
>
> which results in..
>
> sqlalchemy.exc.OperationalError: (pysqlite2.dbapi2.OperationalError) no
> such table: cteq_alias_n [SQL: u'WITH RECURSIVE cteq_n(id, parent_id) AS
> \n(SELECT obj.id AS id, obj.parent_id AS parent_id \nFROM obj \nWHERE
> obj.parent_id = ? UNION ALL SELECT rekey_alias_n.id AS rekey_alias_n_id,
> rekey_alias_n.parent_id AS rekey_alias_n_parent_id \nFROM obj AS
> rekey_alias_n, cteq_alias_n AS cteq_alias_n \nWHERE rekey_alias_n.parent_id
> = cteq_alias_n.id)\n SELECT obj.id AS obj_id, obj.parent_id AS
> obj_parent_id \nFROM obj, cteq_n'] [parameters: (u'1234',)]
>
>
> Any pointers gratefully received! I must be missing something stupid.
>
> Thanks,
> Jonathan
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
Антон
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
