sql - ASSERTION 错误:运行 SQL 查询时出现问题

问题 #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))\n\n#m as movie , m_director as md,Genre as g,Person as p\nquery1 ="""SELECT m.Title,p.Name,m.year\nFROM Movie m JOIN \n     M_director d\n     ON m.MID = d.MID JOIN \n     Person p\n     ON d.PID = p.PID JOIN\n     M_Genre mg\n     ON m.MID = mg.MID JOIN\n     Genre g \n     ON g.GID = mg.GID\n     WHERE g.Name LIKE \'%Comedy%\'\nAND ( m.year%4 = 0\nAND m.year % 100 <> 0\nOR  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 函数中运行时。我收到 assertion 错误。

我怎样才能解决这个问题?

回答1

您的查询有一个 LIMIT 子句,它会阻止 SQL 引擎获取所有数据。没有这个子句就再次运行它。

相似文章

最新文章