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.
