断言错误:运行SQL查询时出现问题

Question

问题1

列出所有在闰年执导喜剧电影的导演。(您需要检查类型是否为"喜剧"，年份是否为闰年)您的查询应该返回导演名称，电影名称和年份。

%%time
def grader_1(q1):
q1_results  = pd.read_sql_query(q1,conn)
print(q1_results.head(10))
assert (q1_results.shape == (232,3))
#m as movie , m_director as md,Genre as g,Person as p
query1 ="""SELECT m.Title,p.Name,m.year
FROM Movie m JOIN 
M_director d
ON m.MID = d.MID JOIN 
Person p
ON d.PID = p.PID JOIN
M_Genre mg
ON m.MID = mg.MID JOIN
Genre g 
ON g.GID = mg.GID
WHERE g.Name LIKE '%Comedy%'
AND ( m.year%4 = 0
AND m.year % 100 <> 0
OR  m.year % 400 = 0 ) LIMIT 2"""
grader_1(query1)

错误:

title           Name  year
0                         Mastizaade   Milap Zaveri  2016
1  Harold & Kumar Go to White Castle   Danny Leiner  2004
---------------------------------------------------------------------------
AssertionError                            Traceback (most recent call last)
<ipython-input-17-a942fcc98f72> in <module>()
----> 1 get_ipython().run_cell_magic('time', '', 'def grader_1(q1):n    q1_results  = pd.read_sql_query(q1,conn)n    print(q1_results.head(10))n    assert (q1_results.shape == (232,3))nn#m as movie , m_director as md,Genre as g,Person as pnquery1 ="""SELECT m.Title,p.Name,m.yearnFROM Movie m JOIN n     M_director dn     ON m.MID = d.MID JOIN n     Person pn     ON d.PID = p.PID JOINn     M_Genre mgn     ON m.MID = mg.MID JOINn     Genre g n     ON g.GID = mg.GIDn     WHERE g.Name LIKE '%Comedy%'nAND ( m.year%4 = 0nAND m.year % 100 <> 0nOR  m.year % 400 = 0 ) LIMIT 2"""ngrader_1(query1)')
2 frames
<decorator-gen-53> in time(self, line, cell, local_ns)
/usr/local/lib/python3.7/dist-packages/IPython/core/magics/execution.py in time(self, line, cell, local_ns)
1191         else:
1192             st = clock2()
-> 1193             exec(code, glob, local_ns)
1194             end = clock2()
1195             out = None
<timed exec> in <module>()
<timed exec> in grader_1(q1)
AssertionError: 

我在没有grad_1函数的IMDB DATASET上运行了这个SQL查询，我能够运行这个查询。然而，当我尝试在grader_1函数内运行时。我得到断言错误。

我该如何解决这个问题?

Answer 1

您的查询有一个LIMIT子句，它阻止SQL引擎获取所有数据。请不使用此子句再运行一次。

Answer 2

query1 = """ SELECT M.title,Pe.Name,M.year FROM Movie M JOIN M_Director MD              ON M.MID = MD.MID JOIN M_Genre MG ON M.MID = MG.MID JOIN Genre Ge ON MG.GID = Ge.GID JOIN Person Pe ON MD.PID = Pe.PID WHERE Ge.Name LIKE '%Comedy%' AND CAST(SUBSTR(TRIM(M.year),-4) AS INTEGER) % 4 = 0 AND (CAST(SUBSTR(TRIM(M.year),-4) AS INTEGER) % 100 <> 0 OR CAST(SUBSTR(TRIM(M.year),-4) AS INTEGER) % 400 = 0)                       """

运行此查询，所有问题都解决了。