Python中SQLite 2.6.0执行查询遇OperationalError语法错误求助

错误原因 #

你的SQL查询触发OperationalError: near "(": syntax error，是因为SQLite不支持在NOT EXISTS子句中直接使用EXCEPT的嵌套写法，导致语法解析失败。

正确查询方案 #

方案1：COUNT统计匹配（直观兼容） #

通过统计水手预订的不同船只数量，判断是否等于所有船只的总数，实现需求：

answer = """
SELECT S.sname 
FROM Sailor S
JOIN Reserve R ON S.sid = R.sid
GROUP BY S.sid, S.sname
HAVING COUNT(DISTINCT R.bid) = (SELECT COUNT(*) FROM Boat)
"""

方案2：修正NOT EXISTS+EXCEPT语法 #

如果坚持使用原逻辑，需要将EXCEPT的结果包裹在带别名的子查询中：

answer = """
SELECT S.sname 
FROM Sailor S 
WHERE NOT EXISTS (
    SELECT * 
    FROM (
        SELECT B.bid FROM Boat B 
        EXCEPT 
        SELECT R.bid FROM Reserve R WHERE R.sid = S.sid
    ) AS UnreservedBoats
)
"""

替换后的代码片段 #

将原代码中的answer变量替换为上述任意一个即可，完整示例：

print('Q1: Find the name of the sailor who reserved all boats')
print('Your answer should include (sname)')

# 选用方案1的查询
answer = """
SELECT S.sname 
FROM Sailor S
JOIN Reserve R ON S.sid = R.sid
GROUP BY S.sid, S.sname
HAVING COUNT(DISTINCT R.bid) = (SELECT COUNT(*) FROM Boat)
"""

t = cur.execute(answer)
names = list(map(lambda x: x[0], t.description))
print(names)
print('--------------------')
for row in t : print(row)
print('--------------------')

内容的提问来源于stack exchange，提问作者Francisco Salazar