def test_group_by(tips):
result = sandals.sql("SELECT sex, count(*) FROM tips GROUP BY sex;",
locals())
expected = tips.groupby('sex').size()
assert result.columns == ["sex"]
assert result["sex"]["Female"] == expected["Female"]
assert result["sex"]["Male"] == expected["Male"]
def test_order_by_desc(tips):
result = sandals.sql(
"SELECT * FROM tips ORDER BY tips.size DESC, total_bill LIMIT 3",
locals())
expected = tips.sort(["size", "total_bill"], ascending=[0, 1])[:3]
assert result.shape == expected.shape
assert result.iloc[0]["total_bill"] == expected.iloc[0]["total_bill"]
def test_group_by_with_multiple_functions(tips):
result = sandals.sql(
"SELECT tips.day, AVG(tip), COUNT(*) FROM tips GROUP BY tips.day;",
locals())
expected = tips.groupby('day').agg({'tip': np.mean, 'day': np.size})
assert result.shape == expected.shape
assert result["tip"]["Fri"] == expected["tip"]["Fri"]
assert result["day"]["Sun"] == expected["day"]["Sun"]
def test_group_by_multiple_columns(tips):
result = sandals.sql(
"""SELECT smoker, tips.day, COUNT(*), AVG(tip)
FROM tips GROUP BY smoker, tips.day;""", locals())
expected = tips.groupby(['smoker', 'day']).agg({'tip': [np.size, np.mean]})
assert result.shape == expected.shape
assert list(result["smoker"]) == list(expected["tip"]["size"])
assert list(result["tip"]) == list(expected["tip"]["mean"])
def test_where_is_null():
frame = pd.DataFrame({
'col1': ['A', 'B', np.NaN, 'C', 'D'],
'col2': ['F', np.NaN, 'G', 'H', 'I']
})
result = sandals.sql("SELECT * FROM frame WHERE col2 IS NULL;", locals())
expected = frame[frame['col2'].isnull()]
assert result.shape == expected.shape
def test_select_with_limit(tips):
result = sandals.sql("SELECT * FROM tips LIMIT 5", locals())
assert result.shape[0] == 5
assert result.shape[1] == tips.shape[1]
def test_select_is_case_insensitive(tips):
result = sandals.sql("select * from tips;", locals())
assert result.shape == tips.shape
def test_select_should_accept_line_break(tips):
result = sandals.sql("SELECT * \n FROM tips;", locals())
assert result.shape == tips.shape
def test_where_with_and(tips):
result = sandals.sql(
"SELECT * FROM tips WHERE time = 'Dinner' AND tip > 5.00;", locals())
expected = tips[(tips["tip"] > 5) & (tips["time"] == "Dinner")]
assert result.shape == expected.shape
def test_where_with_eq(tips):
result = sandals.sql('SELECT * FROM tips WHERE tip = 5', locals())
assert result.shape == tips[tips["tip"] == 5].shape
def test_where_with_wrong_table_name(tips):
result = sandals.sql("SELECT * FROM tips WHERE asdf.time = 'Dinner'",
locals())
def test_where_with_double_quote(tips):
result = sandals.sql('SELECT * FROM tips WHERE time = "Dinner"', locals())
assert result.shape == tips[tips["time"] == "Dinner"].shape
def test_qualified_column_selection(tips):
result = sandals.sql("SELECT tips.total_bill, sex FROM tips", locals())
assert list(result.columns.values) == ["total_bill", "sex"]
assert result.shape[0] == tips.shape[0]
def test_select_with_limit_is_case_insensitive(tips):
result = sandals.sql("SELECT * from tips limit 5", locals())
assert result.shape[0] == 5
assert result.shape[1] == tips.shape[1]
def test_where_with_or(tips):
result = sandals.sql(
"SELECT * FROM tips WHERE tip >= 5 OR total_bill > 45;", locals())
expected = tips[(tips["tip"] >= 5) | (tips["total_bill"] > 45)]
assert result.shape == expected.shape
def test_select(tips):
result = sandals.sql("SELECT * FROM tips", locals())
assert result.shape == tips.shape
def test_where_with_special_col_name(tips):
result = sandals.sql(
"SELECT * FROM tips WHERE tips.size >= 5 OR total_bill > 45;",
locals())
expected = tips[(tips["size"] >= 5) | (tips["total_bill"] > 45)]
assert result.shape == expected.shape
def test_single_column_selection(tips):
result = sandals.sql("SELECT sex FROM tips", locals())
assert list(result.columns.values) == ["sex"]
assert result.shape[0] == tips.shape[0]
def test_select_should_accept_trailing_semicolon(tips):
result = sandals.sql("SELECT * FROM tips;", locals())
assert result.shape == tips.shape
def test_qualified_column_selection_with_wrong_table_name(tips):
result = sandals.sql("SELECT asdf.total_bill, sex FROM tips", locals())
def test_order_by(tips):
result = sandals.sql("SELECT * FROM tips ORDER BY total_bill LIMIT 3",
locals())
expected = tips.sort("total_bill")[:3]
assert result.shape == expected.shape
assert result.iloc[0]["total_bill"] == expected.iloc[0]["total_bill"]
def test_where_with_table_name(tips):
result = sandals.sql("SELECT * FROM tips WHERE tips.time = 'Dinner'",
locals())
assert result.shape == tips[tips["time"] == "Dinner"].shape
def test_order_by_desc(tips):
result = sandals.sql("SELECT * FROM tips ORDER BY total_bill DESC LIMIT 3",
locals())
expected = tips.sort("total_bill", ascending=False)[:3]
assert result.shape == expected.shape
assert result.iloc[0]["total_bill"] == expected.iloc[0]["total_bill"]
def test_where_with_greater_than(tips):
result = sandals.sql('SELECT * FROM tips WHERE tip > 5.0', locals())
assert result.shape == tips[tips["tip"] > 5.0].shape
def test_where_with_less_or_eq_than(tips):
result = sandals.sql('SELECT * FROM tips WHERE tip <= 5.0', locals())
assert result.shape == tips[tips["tip"] <= 5.0].shape
