我正在尝试重现在SqlAlchemy
docs页面上找到的查询。在中设置完Part
课程后models.py
,这就是我所拥有的:
from app import app, db, models
from models import *
@app.route('/')
def test():
included_parts = db.session.query(
Part.sub_part,
Part.part,
Part.quantity).\
filter(Part.part=="our part").\
cte(name="included_parts", recursive=True)
incl_alias = db.aliased(included_parts, name="pr")
parts_alias = db.aliased(Part, name="p")
included_parts = included_parts.union_all(
db.session.query(
parts_alias.sub_part,
parts_alias.part,
parts_alias.quantity).\
filter(parts_alias.part==incl_alias.c.sub_part)
)
q = db.session.query(
included_parts.c.sub_part,
db.func.sum(included_parts.c.quantity).
label('total_quantity')
).\
group_by(included_parts.c.sub_part).all()
return 'test complete'
但这给出了一个错误:
OperationalError: (sqlite3.OperationalError) near "WITH": syntax error [SQL: u'WITH RECURSIVE included_parts(sub_part, part, quantity) AS \n(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity \nFROM parts \nWHERE parts.part = ? UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM parts AS p, included_parts AS pr \nWHERE p.part = pr.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part'] [parameters: ('our part',)]
生成的查询(从错误消息粘贴的副本)如下所示:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS \n(SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity \nFROM parts \nWHERE parts.part = ? UNION ALL SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity \nFROM parts AS p, included_parts AS pr \nWHERE p.part = pr.sub_part)\n SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity \nFROM included_parts GROUP BY included_parts.sub_part
格式化(为了便于阅读,在不同位置使用换行符):
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT parts.sub_part AS sub_part, parts.part AS part, parts.quantity AS quantity
FROM parts
WHERE parts.part = ?
UNION ALL
SELECT p.sub_part AS p_sub_part, p.part AS p_part, p.quantity AS p_quantity
FROM parts AS p, included_parts AS pr
WHERE p.part = pr.sub_part
)
SELECT included_parts.sub_part AS included_parts_sub_part, sum(included_parts.quantity) AS total_quantity
FROM included_parts
GROUP BY included_parts.sub_part
并且,为了进行比较,这是sqlalchemy文档链接到的纯PostgreSQL查询:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part
我可以看到的Postgre查询(我认为应该可以正常工作)与生成的唯一区别是:
SELECT parts.sub_part AS sub_part
vs SELECT sub_part
)UNION ALL
和之间没有换行符SELECT
)但是,据我所知,这两个都不应该导致语法错误…我也尝试过将Postgre查询作为原始SQL执行(尽管SQLAlchemy显然使用sqlite3,但仍然):
query = db.engine.execute(\
'''WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'
UNION ALL
SELECT p.sub_part, p.part, p.quantity
FROM included_parts pr, parts p
WHERE p.part = pr.sub_part
)
SELECT sub_part, SUM(quantity) as total_quantity
FROM included_parts
GROUP BY sub_part''').all()
但是我仍然收到语法错误。
OperationalError: (sqlite3.OperationalError) near "WITH": syntax error [SQL: "WITH RECURSIVE included_parts(sub_part, part, quantity) AS (\nSELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'\nUNION ALL\nSELECT p.sub_part, p.part, p.quantity\nFROM included_parts pr, parts p\nWHERE p.part = pr.sub_part\n)\nSELECT sub_part, SUM(quantity) as total_quantity\nFROM included_parts\nGROUP BY sub_part"]
我还尝试重新格式化生成的查询,并将其作为原始SQL执行,并获得相似的结果。
最后,我尝试在SQLite中编写查询并执行它:
WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
SELECT sub_part, part, quantity FROM parts WHERE part="our_product"
UNION ALL
SELECT parts.sub_part, parts.part, parts.quantity FROM parts, included_parts WHERE parts.part=included_parts.sub_part
)
SELECT sub_part, SUM(quantity) AS total_quantity
FROM included_parts
GROUP BY sub_part
这也会引发语法错误。
在这一点上,我不太确定该怎么做…看起来即使是WITH
具有正确语法的查询仍然会引发错误。我知道根据文档和此处的(_http://stackoverflow.com/a/24780445)答案,Sqlalchemy支持递归CTE查询。老实说,我不知道为什么所有这些查询都被认为语法错误。我的python代码实际上与docs中的示例相同。
我需要安装一些东西WITH RECURSIVE
才能在SQLAlchemy中工作吗?我的语法实际上是错误的吗?在这里几乎迷失了,任何帮助将不胜感激。
(编辑以使其回到首页。我希望我能说我取得了一些进展,但是我现在不知道该怎么办。这是PostgreSQL vs
SQLite问题吗?有人有可行的示例吗?我可以尝试做的事情?)
问题是您正在运行旧版本的sqlite3(如#sqlalchemy所述);您拥有3.8.2,并且在3.8.3中添加了CTE支持。
我在下面的代码中的Foreach循环中遇到错误,有人能帮我修复错误吗。。。
问题内容: 我在数据库中存储了一组依赖项。我正在寻找直接或间接依赖于当前对象的所有对象。由于对象可以依赖零个或多个其他对象,因此完全可以合理地认为对象1被对象9两次依赖(9依赖于4和5,这两个都依赖于1)。我想获取不依赖复制的所有依赖于当前对象的对象的列表。 如果存在循环,这将变得更加复杂。没有循环,一个人可以使用DISTINCT,尽管多次经过长链仅在末尾剔除它们仍然是一个问题。但是,对于循环,重
你好,我有以下问题 查询在我的localhost(XAMMP)中运行良好,但在其他任何地方它都会给我这个错误 示例:http://sqlfiddle.com/#!9/e19e8c/2
问题内容: 此查询生成从1到4的数字。 但是,如果我对此进行修改, 它给 错误:“ z”处或附近的语法错误 我在这里做错了什么? 问题答案: 我认为这是因为RECURSIVE是WITH语句的修饰符,而不是常用表表达式的属性,因此您可以像这样使用它:
问题内容: 不知道如何在这个标题上加上标题! 我有以下数据: 我希望能够根据以下标准将所有引用分为“活动”,“失效”或“新”类别: 起始日期小于参考月份的最后日期,失效日期在上个月的最后一天之后,并且value_received> 0; 开始日期在参考月份之内; 失效日期在参考月份之内。 然后将这些定义应用到连续13个月的每个引用中(因此从Now追溯到2010年7月),以便每个月我可以看到每个组中
问题内容: 在这个sqlfiddle中… http://sqlfiddle.com/#!6/b6587/6 我收到以下错误…。 声明终止。在语句完成之前,最大递归100已用尽。 我知道CTE第二选择的where子句中需要进行“终止检查”。即使您取消注释WHERE子句,我也会遇到相同的错误。 我只是想了解1)为什么根本需要它……毕竟每个订单行都与每个客户行都有关系,2)由于需要“终止检查”,因此该示