On Wednesday, July 13, 2016 at 2:35:06 PM UTC+1, Антонио Антуан wrote:
>
> `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
>
>
Thanks - you're right. That's weird though, as it contradicts the
documentation. Anyway, I've found it all works as expected, simply by not
bothering to create the aliases:
cteq = session.query(Obj).filter(Obj.parent_id == a.id).cte(recursive=True,
name='cteq_n')
r = cteq.union_all(
session.query(Obj).filter(Obj.parent_id == cteq.c.id)
)
objs = session.query(Obj).select_from(r).all()
seems to do the job. I've actually ran the example given in the docs (Query
API section), and that fails in the same way I reported in my original mail
too.
Cheers,
Jonathan
> ср, 13 июл. 2016 г. в 15:20, Jonathan Underwood <jonathan....@gmail.com
> <javascript:>>:
>
>> 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+...@googlegroups.com <javascript:>.
>> To post to this group, send email to sqlal...@googlegroups.com
>> <javascript:>.
>> 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.
