Python中正确编写SQL INNER JOIN及代码错误排查

需求说明 #

基于本地存储的IMDb数据库，查询是否存在所有执导作品评分均高于9分的导演。

涉及表结构 #

Crew表 #

• 字段：title_id（作品ID）、person_id（人员ID）、category（人员职位类别）、job（具体工作）、characters（饰演角色）
• 样例数据：

[('tt0000060', 'nm0005690', 'director', None, '\N'),
('tt0000060', 'nm0005658', 'cinematographer', None, '\N'),
('tt0000361', 'nm0349785', 'director', None, '\N'),
('tt0000376', 'nm0466448', 'actor', None, '\N'),
('tt0000376', 'nm0617272', 'actress', None, '["Salome"]')...

Ratings表 #

• 字段：title_id（作品ID）、rating（作品评分）、votes（评分人数）
• 样例数据：

[('tt0000060', 7.8, 59),
('tt0000361', 8.1, 10),
('tt0000376', 7.8, 6),
('tt0000417', 8.2, 38836),
('tt0000505', 8.1, 11),
('tt0000738', 7.8, 11)...

原有问题代码 #

import sqlalchemy
from sqlalchemy import create_engine, text, inspect
engine = create_engine('sqlite:///newIMDB.db')
inspector = inspect(engine)
print(inspector.get_table_names()) #['crew', 'episodes', 'people', 'ratings', 'titles']
conn = engine.connect()

stmt = text ("SELECT category,rating FROM(SELECT * FROM crew INNER JOIN ratings
ON crew.title_id = ratings.title_id)  
WHERE category=director AND  rating  > 9 LIMIT 10;" )

result = conn.execute(stmt)
result.fetchall()

代码存在的错误 #

• SQL语法错误：字符串常量未加引号
  条件category=director中，director是字符串类型的字段值，没有加单引号包裹，SQL执行时会将其识别为列名，直接抛出字段不存在的错误，正确写法为category='director'。
• Python语法错误：多行字符串未正确声明
  传入text()的SQL语句直接在双引号内换行，Python不支持普通双引号包裹的字符串未经转义直接换行，会直接触发语法报错，需要用三引号"""包裹多行SQL内容。
• 核心逻辑错误：未实现「所有作品评分均高于9分」的判断
  现有语句仅筛选单条记录中「职位为导演、单部作品评分高于9分」的条目，没有按导演IDperson_id分组统计，也没有判断导演所有作品的最低评分是否大于9，完全无法匹配需求。
• 查询字段缺失
  查询结果仅返回category和rating字段，没有返回导演唯一标识person_id，即使查询执行成功也无法定位到具体导演。

修正后参考实现 #

# 正确查询逻辑：按导演分组，筛选所有执导作品最低评分>9的导演
stmt = text("""
SELECT c.person_id, MIN(r.rating) AS min_director_rating
FROM crew c
INNER JOIN ratings r ON c.title_id = r.title_id
WHERE c.category = 'director'
GROUP BY c.person_id
HAVING MIN(r.rating) > 9
""")
result = conn.execute(stmt)
print(result.fetchall())

注：如果返回结果为空，说明不存在满足条件的导演。

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