def test52():
t = RDBDataTable("Teams", key_columns=None, debug=True)
result = t.find_by_primary_key(
['BOS', '2004'], field_list=['teamID', 'yearID', 'Rank', 'W', 'WSWin'])
print("Result = ", result)
def test6():
t = RDBDataTable("Batting", key_columns=None, debug=True)
# print(t)
result = t._get_primary_key()
print("Result = ", json.dumps(result, indent=2))
def test3():
t = RDBDataTable("People", ['playerID'])
tmp = { "nameLast": "Williams", "nameFirst": "Ted"}
result = t.find_by_template(tmp, field_list=['playerID', 'nameLast', 'throws'])
print("Result = ", result)
def test7():
t = RDBDataTable("People", key_columns=None, debug=True)
# print(t)
new_PERSON = {"playerID": "dff201:", "nameLast": "Ferguson", "throws": "R"}
result = t.insert(new_person)
print("Result = ", json.dumps(result, indent=2))
def test5():
t = RDBDataTable("People", key_columns=None, debug=True)
# print(t)
result = t.find_by_primary_key(
['willite01'], field_list=['playerID', 'nameLast', 'throws'])
print("Result = ", result)
def test_find_by_primary_key():
t = RDBDataTable('People',
key_columns=['playerID'],
connect_info=None,
debug=False)
x = t.find_by_primary_key(
['willite01'], ['playerID', 'nameLast', 'nameFirst', 'throws', 'bats'])
print('find_by_primary_key result = ', x)
def test4():
t = RDBDataTable("People", ['playerID'], debug=True)
# print(t)
tmp = {"nameLast": "Williams", "nameFirst": "Ted"}
#column that doesn't exist
result = t.find_by_template(tmp,
field_list=['playerID', 'nameLast', 'color'])
print("Result = ", result)
def test2():
t = RDBDataTable("People", ['playerID'])
tmp = { "nameLast": "Williams", "nameFirst": "Ted"}
result = t._template_to_where_clause(tmp)
print("WC = ", str(result))
q = "select * from People " + result[0]
print("Query = ", q)
result = t._run_q(cnx=None, q=q, args=result[1], commit=True, fetch=True)
print("Query result = ", json.dumps(result, indent=2))
def test82():
t = RDBDataTable("People", key_columns=None, debug=True)
new_person = {"playerID": "dff201", "nameLast": "Ferguson", "throws": "R"}
result = t.insert(new_person)
print("Result = ", json.dumps(result, indent=2))
tmp = {'playerID': 'dff201'}
new_c = {"nameFirst": "donald", "bats": "R"}
r1 = t.update_by_template(tmp, new_c)
print("\n\nAfter update, Q returns ", r1)
def test8():
t = RDBDataTable("People", key_columns=None, debug=True)
new_person = {"playerID": "dff201", "nameLast": "Ferguson", "throws": "R"}
print("Result = ", json.dumps(result, indent=2))
tmp = {'plyaerID': 'dff201'}
r1 = t.find_by_template(tmp)
print("After insert, Q returns ", r1)
result = t.delete_by_template({'plyaerID': 'dff201'})
r1 = t.find_by_template(tmp)
print("After delete, Q returns ", result)
def test9():
t = RDBDataTable("People", key_columns=['playerID'], debug=True)
new_person = {
"playerID": "dff201",
"nameLast": "Ferguson",
"throws": "R"
}
result = t.insert(new_person)
print("\n\nResult = ", json.dumps(result, indent=2))
tmp = {'\n\nplayerID': 'dff201'}
r1 = t.find_by_template(tmp)
print("After insert, Q returns ", r1)
result = t.delete_by_key(key_fields=['dff201'])
print("\n\nAfter delete, Q returns ", result)
def testTop10():
#print(t)
q = "SELECT Batting.playerID, " + \
"(SELECT People.nameFirst FROM People WHERE People.playerID=Batting.playerID) as first_name, " + \
"(SELECT People.nameLast FROM People WHERE People.playerID=Batting.playerID) as last_name, " + \
"sum(Batting.H) /sum(Batting.AB) as career_average, " + \
"sum(Batting.H) as career_hits, sum(Batting.AB) as career_at_bats , min(Batting.yearID) as first_year, " + \
"max(Batting.yearID) as last_year " + \
"FROM Batting GROUP BY playerId HAVING career_at_bats > 200 AND last_year >1960 " + \
"ORDER BY career_average DESC LIMIT 10"
print("Query = ", q)
t = RDBDataTable("People", ['playerID'], debug=True)
result = t._run_q(q=q)
print("Query result = ", json.dumps(result, indent=2, default=str))
def setUp(self):
self.cnx = {
'host': 'localhost',
'user': '******',
'password': '******',
'db': 'demographics',
'port': 3306
}
worker = RDBDataTable('stats_by_zip', ['zipcode'], self.cnx)
worker._run_q('drop table if exists demographics.unit_test')
worker._run_q(
'CREATE TABLE demographics.unit_test LIKE demographics.stats_by_zip'
)
worker._run_q(
'INSERT INTO demographics.unit_test Select * from demographics.stats_by_zip'
)
self.worker_test = RDBDataTable('demographics.unit_test', ['zipcode'],
self.cnx)
def test11():
t = RDBDataTable("People", key_columns=['playerID'], debug=True)
new_person = {
"playerID": "dff201",
"nameLast": "Ferguson",
"throws": "R"
}
result = t.insert(new_person)
print("Result = ", json.dumps(result, indent=2))
new_c = {
"nameFirst": "Donald",
"bats": "R"
}
r1 = t.update_by_key(key_fields=['dff201'], new_values=new_c)
print("\n\nAfter update, Q returns ", r1)
result = t.delete_by_template({'playerID': 'dff201'})
print("\n\nAfter delete, Q returns ", result)
def test_query():
t = RDBDataTable("Batting", ['playerID'])
q = """SELECT
Batting.playerID,
(SELECT People.nameFirst FROM People WHERE People.playerID=Batting.playerID) as first_name,
(SELECT People.nameLast FROM People WHERE People.playerID=Batting.playerID) as last_name,
sum(Batting.h)/sum(batting.ab) as career_average,
sum(Batting.h) as career_hits,
sum(Batting.ab) as career_at_bats,
min(Batting.yearID) as first_year,
max(Batting.yearID) as last_year
FROM
Batting
GROUP BY
playerId
HAVING
career_at_bats > 200 AND last_year >= 1960
ORDER BY
career_average DESC
LIMIT 10; """
result = t._run_q(q)
print("Result = ", json.dumps(result, indent=2))
from RDBDataTable import RDBDataTable
import json
# Initialize the Batting table
batting_rdbtable = RDBDataTable("batting", "Batting.csv", ["playerID", "teamID", "yearID", "stint"])
# Test find_by_primary_key
# If the fields is None
ans = batting_rdbtable.find_by_primary_key(["abbeych01", "WAS", "1897", "1"])
print(json.dumps(ans, indent=2))
# If the fields is not None
ans = batting_rdbtable.find_by_primary_key(["abbeych01", "WAS", "1897", "1"], ["playerID", "lgID", "G", "BB", "SH"])
print(json.dumps(ans, indent=2))
# If the fields has values that don't exist in csv file, in the case, I use "CCCCC" to test my program
ans = batting_rdbtable.find_by_primary_key(["abbeych01", "WAS", "1897", "1"], ["playerID", "lgID", "G", "BB", "CCCCC"])
print(json.dumps(ans, indent=2))
# Test find_by_template
# If the input t is correct and fields is None
ans = batting_rdbtable.find_by_template({"teamID": "CHN", "yearID": "2006"})
print(json.dumps(ans, indent=2))
# If the input t is correct and fields is not None
ans = batting_rdbtable.find_by_template({"teamID": "CHN", "yearID": "2006"}, ["playerID", "yearID", "teamID",
"RBI", "G"])
print(json.dumps(ans, indent=2))
# If the input t is incorrect and fields is correct, In this case, "teamIs" is not a attribute in csv
ans = batting_rdbtable.find_by_template({"teamIs": "CHN", "yearID": "2006"}, ["playerID", "yearID", "teamID",
"RBI", "G"])
print(json.dumps(ans, indent=2))
# If the input t is correct and fields is incorrect, In this case, "playerIs" is not a attribute in csv
ans = batting_rdbtable.find_by_template({"teamID": "CHN", "yearID": "2006"}, ["playerIs", "yearID", "teamID",
def test_wrong_keys_in_init_raises_error(self):
with self.assertRaises(ValueError):
RDBDataTable('demographics.unit_test', ['zipcode', 'cnt_female'],
self.cnx)
class TestStringMethods(unittest.TestCase):
def setUp(self):
self.cnx = {
'host': 'localhost',
'user': '******',
'password': '******',
'db': 'demographics',
'port': 3306
}
worker = RDBDataTable('stats_by_zip', ['zipcode'], self.cnx)
worker._run_q('drop table if exists demographics.unit_test')
worker._run_q(
'CREATE TABLE demographics.unit_test LIKE demographics.stats_by_zip'
)
worker._run_q(
'INSERT INTO demographics.unit_test Select * from demographics.stats_by_zip'
)
self.worker_test = RDBDataTable('demographics.unit_test', ['zipcode'],
self.cnx)
def test_wrong_keys_in_init_raises_error(self):
with self.assertRaises(ValueError):
RDBDataTable('demographics.unit_test', ['zipcode', 'cnt_female'],
self.cnx)
def test_wrong_keys_in_query_raises_error(self):
with self.assertRaises(ValueError):
self.worker_test.find_by_primary_key([10003, 1])
def test_find_by_keys_works(self):
self.assertEqual(
self.worker_test.find_by_primary_key([10004],
field_list=['zipcode'])._rows,
self.worker_test._run_q(
"Select zipcode from demographics.unit_test where zipcode = 10004"
))
def test_find_by_template_works(self):
self.assertEqual(
self.worker_test.find_by_template({
'zipcode': 10004
},
field_list=['cnt_participants'
])._rows,
[{
'cnt_participants': 0
}])
def test_delete_by_template_works(self):
self.worker_test.delete_by_template({'zipcode': 10004})
out = self.worker_test.find_by_template({'zipcode': 10004})
self.assertEqual(None, out)
def test_delete_by_key_works(self):
self.worker_test.delete_by_key([10005])
out = self.worker_test.find_by_primary_key([10005])
self.assertEqual(None, out)
def test_update_by_template_works(self):
self.worker_test.update_by_template({'zipcode': 10006},
{'cnt_participants': 3})
out = self.worker_test.find_by_template(
{'zipcode': 10006}, field_list=['cnt_participants'])
self.assertEqual([{'cnt_participants': 3}], out._rows)
def test_update_by_key_works(self):
self.worker_test.update_by_key([10006], {'cnt_participants': 6})
out = self.worker_test.find_by_primary_key(
[10006], field_list=['cnt_participants'])
self.assertEqual([{'cnt_participants': 6}], out._rows)
def test_none_returned_on_no_result_query(self):
self.assertEqual(self.worker_test.find_by_primary_key([-99999]), None)
def test_invalid_field_name(self):
with self.assertRaises(ValueError):
self.worker_test.find_by_template({'zipcode': 10004},
field_list=['test'])
def test_not_implemented_error(self):
with self.assertRaises(NotImplementedError):
self.worker_test.find_by_template(
{
'zipcode': 10004
}, field_list=['zipcode']).find_by_primary_key([-99999])
def tearDown(self):
self.worker_test._run_q('drop table if exists demographics.unit_test')
def test6():
t = RDBDataTable("People", key_columns=None, debug=True)
result = t._get_primary_key()
print("\n\nResult = ", json.dumps(result, indent=2))
def test1():
t = RDBDataTable("People", ['playerID'])
print(t)