def test_multiple_btwn_and_in(self):
tablename = "codetran_collegedata.collegescorecard"
select_cols = ["school_name"]
filter_dict = {
"is_in": {
"school.institutional_characteristics.level": ["4-year"],
"singlesex.or.coed": ["Single-Sex: Women", "Co-Educational"]
},
"is_btwn": {
"latest.admissions.act_scores.midpoint.cumulative": {
"min": 14,
"max": 34,
"inclusive": True
},
"latest.student.size": {
"min": 0,
"max": 35000,
"inclusive": True
}
}
}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = 'SELECT `school_name` FROM codetran_collegedata.collegescorecard WHERE `latest_admissions_act_scores_midpoint_cumulative` BETWEEN 14 AND 34 AND `latest_student_size` BETWEEN 0 AND 35000 AND `school_institutional_characteristics_level` IN ("4-year") AND `singlesex_or_coed` IN ("Single-Sex: Women", "Co-Educational")'
self.assertEqual(actualSQL, expectedSQL)
def test_nested_empty_in_with_nonempty_in_and_nonempty_btwn(self):
tablename = "myTable"
select_cols = ["school_name"]
filter_dict = {
"is_in": {
"school.region_id": [],
"school.ownership": ["Private nonprofit"],
"school.degrees_awarded.highest": [],
"school.institutional_characteristics.level": [],
"school.minority_serving.historically_black": [],
"singlesex.or.coed": []
},
"is_btwn": {
"latest.student.size": {
"min": 0,
"max": 50000,
"inclusive": "true"
}
}
}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = 'SELECT `school_name` FROM myTable WHERE `latest_student_size` BETWEEN 0 AND 50000 AND `school_ownership` IN ("Private nonprofit")'
self.assertEqual(actualSQL, expectedSQL)
def post(self):
args = College_Data.parser.parse_args()
# build query
tablename = sql_info.get_full_table_name()
select_col = sql_info.get_select_cols()
q = build_query(tablename, select_col, args['filter_dict'])
return {"table": query_to_json(q)}
def test_empty(self):
tablename = "myTable"
select_cols = ["school_name"]
filter_dict = {"is_in": {}, "is_btwn": {}}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = "SELECT `school_name` FROM myTable"
self.assertEqual(actualSQL, expectedSQL)
def test_empty_multiple_select(self):
tablename = "myTable"
select_cols = ["school_name", "midpoint.act.score"]
filter_dict = {"is_in": {}, "is_btwn": {}}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = "SELECT `school_name`, `midpoint_act_score` FROM myTable"
self.assertEqual(actualSQL, expectedSQL)
def test_in(self):
tablename = "myTable"
select_cols = ["school_name"]
filter_dict = {
"is_in": {
"school.region_id": ["Mid East (DE, DC, MD, NJ, NY, PA)"]
},
"is_btwn": {}
}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = 'SELECT `school_name` FROM myTable WHERE `school_region_id` IN ("Mid East (DE, DC, MD, NJ, NY, PA)")'
self.assertEqual(actualSQL, expectedSQL)
def test_btwn_one_condition(self):
tablename = "myTable"
select_cols = ["school_name"]
filter_dict = {
"is_in": {},
"is_btwn": {
"midpoint.act.score": {
"min": 28,
"max": 34,
"inclusive": True
}
}
}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = "SELECT `school_name` FROM myTable WHERE `midpoint_act_score` BETWEEN 28 AND 34"
self.assertEqual(actualSQL, expectedSQL)
def test_nested_empty_in(self):
tablename = "myTable"
select_cols = ["school_name"]
filter_dict = {
"is_in": {
"school.region_id": [],
"school.ownership": [],
"school.degrees_awarded.highest": [],
"school.institutional_characteristics.level": [],
"school.minority_serving.historically_black": [],
"singlesex.or.coed": []
},
"is_btwn": {}
}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = "SELECT `school_name` FROM myTable"
self.assertEqual(actualSQL, expectedSQL)
def test_btwn_and_in(self):
tablename = "myTable"
select_cols = ["school_name"]
filter_dict = {
"is_in": {
"school.region_id": ["Mid East (DE, DC, MD, NJ, NY, PA)"]
},
"is_btwn": {
"midpoint.act.score": {
"min": 28,
"max": 34,
"inclusive": True
}
}
}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = 'SELECT `school_name` FROM myTable WHERE `midpoint_act_score` BETWEEN 28 AND 34 AND `school_region_id` IN ("Mid East (DE, DC, MD, NJ, NY, PA)")'
self.assertEqual(actualSQL, expectedSQL)
def test_btwn_two_conditions(self):
tablename = "myTable"
select_cols = ["school_name"]
filter_dict = {
"is_in": {},
"is_btwn": {
"latest.admissions.act_scores.midpoint.cumulative": {
"min": 0,
"max": 36,
"inclusive": True
},
"latest.student.size": {
"min": 0,
"max": 50000,
"inclusive": True
}
}
}
actualSQL = qb.build_query(tablename, select_cols, filter_dict)
expectedSQL = "SELECT `school_name` FROM myTable WHERE `latest_admissions_act_scores_midpoint_cumulative` BETWEEN 0 AND 36 AND `latest_student_size` BETWEEN 0 AND 50000"
self.assertEqual(actualSQL, expectedSQL)