SQLite does not support the syntax used in the former query, hence the syntax error. The compound select is ordered as a whole. the latter is a union between 2 unordered selects.the former is a union between 2 selects, of which the second is ordered.The reason why SQLAlchemy compiles the query the way it does is that SELECT x FROM foo UNION ALL (SELECT x FROM bar ORDER BY x)Īnd SELECT x FROM foo UNION ALL SELECT x FROM bar ORDER BY x Īre different queries (in databases that support the syntax of the 1st query, such as Postgresql): The bottom SELECT without parentheses (and without order_by) do not a have error: WITH RECURSIVE my_category(id, parent_id, name, level) AS ( If order_by is commented, then the exception is not raised: top_query = db.session.query(Category, db.literal(0).label('level')) \ My_category.parent_id AS my_category_parent_id, The bottom SELECT with parentheses is error: WITH RECURSIVE my_category(id, parent_id, name, level) AS (Ĭategory.parent_id AS category_parent_id,įROM category JOIN my_category ON category.parent_id = my_category.id Hierarchy_query = top_query.union_all(bottom_query) join(top_query, Category.parent_id = top_query.c.id) \ Top_query = db.aliased(top_query, name='my_category')īottom_query = db.session.query(Category, (top_query.c.level 1).label('level')) \ Query: top_query = db.session.query(Category, db.literal(0).label('level')) \ Logger = logging.getLogger('sqlalchemy.engine') Name = db.Column(db.String(100), nullable=False) Parent_id = db.Column(db.Integer, nullable=True) Id = db.Column(db.Integer, primary_key=True) Reproducing the exception: from flask import FlaskĪpp.config = 'sqlite:///:memory:'Īpp.config = False OperationalError: (sqlite3.OperationalError) near "(": syntax errorĪn exception is raised when SQLAlchemy adds parentheses on time compilation SQL statement for the bottom query in the recursive query, if it uses a orderring. I'm trying to execute a my self recursive query that falls into syntax error for SQLite: SELECT substr('.',1,level*3) || name FROM under_alice I want to get a query with SQLAlchemy on the example query that is shown on the page : WITH RECURSIVE under_alice(name,level) AS (įROM org JOIN under_alice ON org.boss=under_alice.name
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |